存储过程，传到值为sql语句的笔记

存储过程，传入值为sql语句的笔记。

一般的存储过程的传入参数是具体的值，过程中利用传入的值去计算或是操作。

工作中往往会碰到一些偏向动态的过程操作，例如根据传来的sql语句改变游标。

首先游标中的字段是要确定的，无论传递什么样的sql进来，所查询出来的游标内容是一定的。

如果有了上述的保障，就可以行动了。

首先是创建一个package，这个package种需要声明一个cursor Type，用作动态游标。

具体命令如下

create or replace package pkg_systeminfo
as
type systeminfo is ref cursor; --定义一个新的游标类型
type record_type is record(     -- 定义新游标里的内容
    tid   varchar2(200),
    tname varchar2(200),
    rtime date,
    rid varchar2(200));
end;

接着去写我们的procedure

create or replace procedure prc_systeminfo(sqlstr in varchar2 )

is

--define
sysid varchar2(200);
sysname varchar2(200);
reg_time date;
reg_id varchar2(200);
reg_name varchar2(200);
usercount number(20);
answercount number(20);
v_time varchar2(200);
v_usercount varchar2(200);
v_answercount varchar2(200);
n_flag number(4);
cur pkg_systeminfo.systeminfo; --定义游标
systype pkg_systeminfo.record_type; --定义游标容器

begin
    sysid :='';
    sysname :='';
    reg_id:='';
    reg_name:='';
    usercount:=0;
    answercount:=0;
    v_time:='';
    v_usercount:='';
    v_answercount:='';
    n_flag:=0;
    open cur for sqlstr; --打开游标并加入传进的sql
       loop
         <<flag>>    --返回点
         fetch cur into systype; --压入数据
         IF cur%NOTFOUND THEN
            EXIT;
            ELSE
                sysid:=systype.tid;
                sysname:=systype.tname;
                reg_id:=systype.rid;
                select NVL(count(1),0) into n_flag from pt_user_detail t where t.id = reg_id;
                if n_flag>0 then
                select NVL(t.name,'') into reg_name from pt_user_detail t where t.id = reg_id;
                reg_time:=systype.rtime;
                select to_char(reg_time ,'yyyy-MM-dd HH:mi:ss') into v_time from dual;
                select NVL(count(1),0) into usercount from kasai_objects_users_roles t where t.id_object = sysid;
                select to_char(usercount) into v_usercount from dual;
                select NVL(count(1),0) into answercount from common_surveyuser x,kasai_objects_users_roles y where x.buser=y.id_user and y.id_object=sysid;
                select to_char(answercount) into v_answercount from dual;
                insert into tb_test_chunf values(sysname,v_time,reg_name,v_usercount,v_answercount);
                else
                  goto flag;   --不满足条件转向下次循环，类似java的continue
                end if;
          end if;
          end loop;
          close cur; --关闭
          commit;
end prc_systeminfo;

以上标记黑色加粗字体是比较重要的。

存储过程，传到值为sql语句的笔记

相关推荐