将一段SQL转换为Oracle函数

问题描述：

我的表"COMMA_SEPERATED"看起来像这样

My table "COMMA_SEPERATED" looks something like this

ID  NAME CITY
--- ---- -----------------------------
1   RAJ  CHENNAI, HYDERABAD, JABALPUR
2   SAM  BHOPAL,PUNE

我想将每个城市分开作为新记录，所以我的SQL是:(工作正常)

I want to separate each City as a new record so my SQL is: (working fine)

SELECT id, TRIM(CITY_NEW)
FROM COMMA_SEPERATED, xmltable
(
    'if (contains($X,",")) then ora:tokenize($X,"\,") else $X'
    passing city AS X
    columns CITY_NEW varchar2(4000) path '.'
);

我想将这段SQL转换为一个函数，以便我可以像这样简单地调用该函数

I want to convert this piece of SQL into a function so that i can simply call the function like this

SELECT id, split_function(city) FROM COMMA_SEPERATED

输出:

1   CHENNAI
1   HYDERABAD
1   JABALPUR
2   BHOPAL
2   PUNE

任何人都可以帮忙吗?我是PL/SQL的新手.

Can anyone help on how to do that? I am very new to PL/SQL.

答

除了@Alex所显示的内容外，您还可以创建object并获取通过function返回的object.见下文:

Apart what @Alex showed, you can also create an object and get the object returned via function. See below:

--Created an object to hold your result columns
create or replace type Obj IS OBJECT (id number, city varchar2(20));
/
--Table of object
create or replace type var_obj is table of Obj;

/
--Function with return as with Object type.
create or replace function splt_fnct
return var_obj
as
var var_obj:=var_obj();

begin
Select obj(col,col1)
bulk collect into var
from (
Select  distinct  col , regexp_substr(col1,'[^,]+',1,level) col1
from tbl
connect by regexp_substr(col1,'[^,]+',1,level) is not null
order by 1);

return var;
end;

/
--Selecting result   

 select *  from table(splt_fnct);

我正在尝试@Alex解决方案，但出现如下所示的错误:

I was trying with @Alex solution and got some error as shown below:

create or replace function splt_fnct(input_strng varchar2)
return var_obj
as
var var_obj:=var_obj();

begin
Select obj(col,col1)
bulk collect into var
from (
select tbl.col, t.rslt --<--This column name should the same as used in colmns clause in the below query. Its giving error "invalid column". How to handle this case.
FROM tbl, xmltable
(
    'if (contains($X,",")) then ora:tokenize($X,"\,") else $X'
    passing col1 AS X
    columns input_strng varchar2(4000) path '.'
) t
);
return var;
end;

/

根据@Alex建议进行更正:

Correction as per @Alex suggestion:

create or replace function splt_fnct(input_strng varchar2)
return var_obj
as
var var_obj:=var_obj();

begin

select obj(tbl.col, t.rslt) 
bulk collect into var
FROM tbl, xmltable
(
    'if (contains($X,",")) then ora:tokenize($X,"\,") else $X'
    passing input_strng AS X
    columns rslt  varchar2(4000) path '.'
) t;

return var;
end;

/

将一段SQL转换为Oracle函数

相关推荐