数据库版本:10.2.0.4
create table zsj_objs as select * from dba_objects;
*************************
方法一
使用本地动态SQL实现:
create or replace function f_zsj_cursor(v_owner varchar2,v_object_type varchar2,v_object_id number,v_created date) return sys_refcursor
is
v_cursor sys_refcursor;
v_sql varchar2(32000);
type t_strlist is table of varchar2(100);
v_strlist t_strlist:=t_strlist();
v_nls_date_format NLS_SESSION_PARAMETERS.value%type;
begin
v_sql:='select * from zsj_objs where ';
if(v_owner is not null) then
v_strlist.extend();
v_strlist(v_strlist.count):=upper(v_owner);
v_sql:=v_sql||'owner=:'||v_strlist.count||' and ';
end if;
if(v_object_type is not null) then
v_strlist.extend();
v_strlist(v_strlist.count):=upper(v_object_type);
v_sql:=v_sql||'object_type=:'||v_strlist.count||' and ';
end if;
if(v_object_id is not null) then
v_strlist.extend();
v_strlist(v_strlist.count):=v_object_id;
v_sql:=v_sql||'object_id>=:'||v_strlist.count||' and ';
end if;
if(v_created is not null) then
v_strlist.extend();
v_strlist(v_strlist.count):=to_char(v_created,'yyyy-mm-dd hh24:mi:ss');
v_sql:=v_sql||'created>=:'||v_strlist.count;
end if;
v_sql:=regexp_replace(v_sql,' and $');
v_sql:=regexp_replace(v_sql,' where $');
SELECT VALUE into v_nls_date_format
FROM NLS_SESSION_PARAMETERS
WHERE PARAMETER='NLS_DATE_FORMAT';
execute immediate 'alter session set nls_date_format=''yyyy-mm-dd hh24:mi:ss''';
--下面这部分是静态的,而不是动态的,在最大绑定变量数量可知的情况下,代码冗长些,但至少还可以实现,但如果最大绑定变量数量都不确定的话,这里的代码实现不了
case v_strlist.count
when 0 then
open v_cursor for v_sql;
when 1 then
open v_cursor for v_sql using v_strlist(1);
when 2 then
open v_cursor for v_sql using v_strlist(1),v_strlist(2);
when 3 then
open v_cursor for v_sql using v_strlist(1),v_strlist(2),v_strlist(3);
when 4 then
open v_cursor for v_sql using v_strlist(1),v_strlist(2),v_strlist(3),v_strlist(4);
end case;
execute immediate 'alter session set nls_date_format='''||v_nls_date_format||'''';
/*不是太明白nls_变量是open cursor时起作用还是fetch cursor时起作用,如果是fetch cursor时起作用的话,也只能是在调用这个函数的客户端关闭这个引用游标之后把这个nls_变量改回原来的设置值了*/
return v_cursor;
/*这里object_id,created列上的索引都是可供使用的,因为这些列上并不会应用任何表达式的
where object_id>='50000' 实际上执行的是where object_id>=to_number('50000')
where created>='2009-07-26 12:00:00' 实际上执行的是where created>=to_date(' 2009-07-26 12:00:00','syyyy-mm-dd hh24:mi:ss')
*/
end;
/
********************************
方法二
使用dbms_sql实现
使用dbms_sql实现的话,确实要灵活得多.但在11g前,dbms_sql好像还不支持sys_refcursor,所以如果要使用dbms_sql实现上面的逻辑的话,我现在只能使用一个临时表了,不知道是否还有好的实现方式.
CREATE GLOBAL temporary TABLE zsj_objs_temp
ON COMMIT PRESERVE rows
AS SELECT * FROM zsj_objs WHERE 1=0;
create or replace function f_zsj_cursor2(v_owner varchar2,v_object_type varchar2,v_object_id number,v_created date) return sys_refcursor
is
v_cursor sys_refcursor;
v_sql varchar2(32000);
type t_strlist is table of varchar2(100);
v_strlist t_strlist:=t_strlist();
v_nls_date_format NLS_SESSION_PARAMETERS.value%type;
l_theCursor INTEGER;
l_status INTEGER;
BEGIN
DELETE FROM zsj_objs_temp;
v_sql:='insert into zsj_objs_temp select * from zsj_objs where ';
if(v_owner is not null) then
v_strlist.extend();
v_strlist(v_strlist.count):=upper(v_owner);
v_sql:=v_sql||'owner=:'||v_strlist.count||' and ';
end if;
if(v_object_type is not null) then
v_strlist.extend();
v_strlist(v_strlist.count):=upper(v_object_type);
v_sql:=v_sql||'object_type=:'||v_strlist.count||' and ';
end if;
if(v_object_id is not null) then
v_strlist.extend();
v_strlist(v_strlist.count):=v_object_id;
v_sql:=v_sql||'object_id>=:'||v_strlist.count||' and ';
end if;
if(v_created is not null) then
v_strlist.extend();
v_strlist(v_strlist.count):=to_char(v_created,'yyyy-mm-dd hh24:mi:ss');
v_sql:=v_sql||'created>=:'||v_strlist.count;
end if;
v_sql:=regexp_replace(v_sql,' and $');
v_sql:=regexp_replace(v_sql,' where $');
SELECT VALUE into v_nls_date_format
FROM NLS_SESSION_PARAMETERS
WHERE PARAMETER='NLS_DATE_FORMAT';
execute immediate 'alter session set nls_date_format=''yyyy-mm-dd hh24:mi:ss''';
l_theCursor:=Dbms_Sql.OPEN_cursor;
Dbms_Sql.parse(c=>l_theCursor,STATEMENT=>v_sql,language_flag=>Dbms_Sql.native);
FOR i IN 1..v_strlist.Count
LOOP
Dbms_Sql.bind_variable(c=>l_theCursor,NAME=>':'||i,Value=>v_strlist(i));
END LOOP;
l_status:=Dbms_Sql.EXECUTE(l_theCursor);
COMMIT;
execute immediate 'alter session set nls_date_format='''||v_nls_date_format||''''; --alter session操作不会commit的
OPEN v_cursor FOR SELECT * FROM zsj_objs_temp;
return v_cursor;
end;
/
下面这个执行报错,因为顺序不对,应该是解析后才能绑定变量的.
CREATE OR REPLACE function f_zsj_cursor3(v_owner varchar2,v_object_type varchar2,v_object_id number,v_created date) return sys_refcursor
is
v_cursor sys_refcursor;
v_sql varchar2(32000);
l_theCursor INTEGER;
l_status INTEGER;
v_var_num INTEGER:=0;
BEGIN
DELETE FROM zsj_objs_temp;
v_sql:='insert into zsj_objs_temp select * from zsj_objs where ';
if(v_owner is not null) then
v_var_num:=v_var_num+1;
v_sql:=v_sql||'owner=:'||v_var_num||' and ';
Dbms_Sql.bind_variable(c=>l_theCursor,NAME=>':'||v_var_num,Value=>upper(v_owner));
end if;
if(v_object_type is not null) then
v_var_num:=v_var_num+1;
v_sql:=v_sql||'object_type=:'||v_var_num||' and ';
Dbms_Sql.bind_variable(c=>l_theCursor,NAME=>':'||v_var_num,Value=>upper(v_object_type));
end if;
if(v_object_id is not null) then
v_var_num:=v_var_num+1;
v_sql:=v_sql||'object_id>=:'||v_var_num||' and ';
Dbms_Sql.bind_variable(c=>l_theCursor,NAME=>':'||v_var_num,Value=>v_object_id);
end if;
if(v_created is not null) then
v_var_num:=v_var_num+1;
v_sql:=v_sql||'created>=:'||v_var_num;
Dbms_Sql.bind_variable(c=>l_theCursor,NAME=>':'||v_var_num,Value=>v_created);
end if;
v_sql:=regexp_replace(v_sql,' and $');
v_sql:=regexp_replace(v_sql,' where $');
l_theCursor:=Dbms_Sql.OPEN_cursor;
Dbms_Sql.parse(c=>l_theCursor,STATEMENT=>v_sql,language_flag=>Dbms_Sql.native);
l_status:=Dbms_Sql.EXECUTE(l_theCursor);
COMMIT;
OPEN v_cursor FOR SELECT * FROM zsj_objs_temp;
return v_cursor;
end;
/
但我觉得代码还是冗长了些,是否可以像我上面那种错误的方法一样实现的简洁些,不要每个if都要写两遍
create or replace function f_zsj_cursor4(v_owner varchar2,v_object_type varchar2,v_object_id number,v_created date) return sys_refcursor
is
v_cursor sys_refcursor;
v_sql varchar2(32000);
l_theCursor INTEGER;
l_status INTEGER;
v_var_num INTEGER:=0;
BEGIN
DELETE FROM zsj_objs_temp;
v_sql:='insert into zsj_objs_temp select * from zsj_objs where ';
if(v_owner is not null) then
v_var_num:=v_var_num+1;
v_sql:=v_sql||'owner=:'||v_var_num||' and ';
end if;
if(v_object_type is not null) then
v_var_num:=v_var_num+1;
v_sql:=v_sql||'object_type=:'||v_var_num||' and ';
end if;
if(v_object_id is not null) then
v_var_num:=v_var_num+1;
v_sql:=v_sql||'object_id>=:'||v_var_num||' and ';
end if;
if(v_created is not null) then
v_var_num:=v_var_num+1;
v_sql:=v_sql||'created>=:'||v_var_num;
end if;
v_sql:=regexp_replace(v_sql,' and $');
v_sql:=regexp_replace(v_sql,' where $');
l_theCursor:=Dbms_Sql.OPEN_cursor;
Dbms_Sql.parse(c=>l_theCursor,STATEMENT=>v_sql,language_flag=>Dbms_Sql.native);
v_var_num:=0;
if(v_owner is not null) then
v_var_num:=v_var_num+1;
Dbms_Sql.bind_variable(c=>l_theCursor,NAME=>':'||v_var_num,Value=>upper(v_owner));
end if;
if(v_object_type is not null) then
v_var_num:=v_var_num+1;
Dbms_Sql.bind_variable(c=>l_theCursor,NAME=>':'||v_var_num,Value=>upper(v_object_type));
end if;
if(v_object_id is not null) then
v_var_num:=v_var_num+1;
Dbms_Sql.bind_variable(c=>l_theCursor,NAME=>':'||v_var_num,Value=>v_object_id);
end if;
if(v_created is not null) then
v_var_num:=v_var_num+1;
Dbms_Sql.bind_variable(c=>l_theCursor,NAME=>':'||v_var_num,Value=>v_created);
end if;
l_status:=Dbms_Sql.EXECUTE(l_theCursor);
COMMIT;
OPEN v_cursor FOR SELECT * FROM zsj_objs_temp;
return v_cursor;
end;
/
***************************************
先是授权:
sys> grant create any context to btocuser;
Grant succeeded.
btocuser> create context zsj_cursor_context using f_zsj_cursor5;
-- 创建context,并与f_zsj_cursor5关联起来(只能在这个名称的包,函数,过程里使用dbms_session.set_context来设置它的属性值)
CREATE OR REPLACE function f_zsj_cursor5(v_owner varchar2,v_object_type varchar2,v_object_id number,v_created date) return sys_refcursor
is
v_cursor sys_refcursor;
v_sql varchar2(32000);
v_nls_date_format NLS_SESSION_PARAMETERS.value%type;
v_var_num INTEGER:=0;
begin
v_sql:='select * from zsj_objs where ';
if(v_owner is not null) THEN
v_var_num:=v_var_num+1;
v_sql:=v_sql||'owner=sys_context(''zsj_cursor_context'',''n'||v_var_num||''') and ';
dbms_session.set_context('zsj_cursor_context','n'||v_var_num,upper(v_owner));
end if;
if(v_object_type is not null) THEN
v_var_num:=v_var_num+1;
v_sql:=v_sql||'object_type=sys_context(''zsj_cursor_context'',''n'||v_var_num||''') and ';
dbms_session.set_context('zsj_cursor_context','n'||v_var_num,upper(v_object_type));
end if;
if(v_object_id is not null) then
v_var_num:=v_var_num+1;
v_sql:=v_sql||'object_id>=sys_context(''zsj_cursor_context'',''n'||v_var_num||''') and ';
dbms_session.set_context('zsj_cursor_context','n'||v_var_num,v_object_id);
end if;
if(v_created is not null) THEN
v_var_num:=v_var_num+1;
v_sql:=v_sql||'created>=sys_context(''zsj_cursor_context'',''n'||v_var_num||''')';
dbms_session.set_context('zsj_cursor_context','n'||v_var_num,To_Char(v_created,'yyyy-mm-dd hh24:mi:ss'));
end if;
v_sql:=regexp_replace(v_sql,' and $');
v_sql:=regexp_replace(v_sql,' where $');
SELECT VALUE into v_nls_date_format FROM NLS_SESSION_PARAMETERS WHERE PARAMETER='NLS_DATE_FORMAT';
execute immediate 'alter session set nls_date_format=''yyyy-mm-dd hh24:mi:ss''';
open v_cursor for v_sql;
execute immediate 'alter session set nls_date_format='''||v_nls_date_format||'''';
return v_cursor;
end;
/
上面这个实现里也存在着和f_zsj_cursor同样的注意点:
就是dbms_session.set_context设置的属性值都是varchar2的,sys_context的返回值都是varchar2的.
所以需要一些特殊设置,可以在你传入的值端进行隐式的类型转换,而不是在表字段端进行类型转换.
**********************************
这里不讨论绑定变量实现的优劣.
CREATE OR REPLACE TYPE t_strtable IS TABLE OF VARCHAR2(256);
CREATE OR REPLACE FUNCTION seperate_str(p_str IN VARCHAR2) RETURN t_strTable
IS
l_str LONG DEFAULT p_str||',';
l_n NUMBER;
l_data t_strTable:=t_strTable();
BEGIN
LOOP
l_n:=InStr(l_str,',');
EXIT WHEN (Nvl(l_n,0)=0);
l_data.extend();
l_data(l_data.count):=LTrim(RTrim(SubStr(l_str,1,l_n-1)));
l_str:=SubStr(l_str,l_n+1);
END LOOP;
RETURN l_data;
END;
/
create or replace function f_zsj_cursor8(v_owners varchar2,v_object_type varchar2,v_object_id number,v_created date) return sys_refcursor
is
--v_owners可能是sys 或者sys,system,scott之类的形式
v_cursor sys_refcursor;
v_sql varchar2(32000);
type t_strlist is table of varchar2(100);
v_strlist t_strlist:=t_strlist();
v_nls_date_format NLS_SESSION_PARAMETERS.value%type;
begin
v_sql:='select * from zsj_objs where ';
if(v_owners is not null) then
--这里一个owner和多个(两个及两个以上)owner分开处理了
if( instr(v_owners,',')>0 ) then
--这里假定owner的数据分布是均匀的,且典型输入情况下是输入5个owner连成的串,所以使用了cardinality(5)的提示
v_strlist.extend();
v_strlist(v_strlist.count):=upper(v_owners);
v_sql:=v_sql||'owner in(select /*+ cardinality(5)*/column_value from TABLE(seperate_str(:'||v_strlist.count||'))) and ';
else
v_strlist.extend();
v_strlist(v_strlist.count):=upper(v_owners);
v_sql:=v_sql||'owner=:'||v_strlist.count||' and ';
end if;
end if;
if(v_object_type is not null) then
v_strlist.extend();
v_strlist(v_strlist.count):=upper(v_object_type);
v_sql:=v_sql||'object_type=:'||v_strlist.count||' and ';
end if;
if(v_object_id is not null) then
v_strlist.extend();
v_strlist(v_strlist.count):=v_object_id;
v_sql:=v_sql||'object_id>=:'||v_strlist.count||' and ';
end if;
if(v_created is not null) then
v_strlist.extend();
v_strlist(v_strlist.count):=to_char(v_created,'yyyy-mm-dd hh24:mi:ss');
v_sql:=v_sql||'created>=:'||v_strlist.count;
end if;
v_sql:=regexp_replace(v_sql,' and $');
v_sql:=regexp_replace(v_sql,' where $');
SELECT VALUE into v_nls_date_format
execute immediate 'alter session set nls_date_format=''yyyy-mm-dd hh24:mi:ss''';
case v_strlist.count
when 0 then
open v_cursor for v_sql;
when 1 then
open v_cursor for v_sql using v_strlist(1);
when 2 then
open v_cursor for v_sql using v_strlist(1),v_strlist(2);
when 3 then
open v_cursor for v_sql using v_strlist(1),v_strlist(2),v_strlist(3);
when 4 then
open v_cursor for v_sql using v_strlist(1),v_strlist(2),v_strlist(3),v_strlist(4);
end case;
execute immediate 'alter session set nls_date_format='''||v_nls_date_format||'''';
return v_cursor;
end;
/