存储过程，传入值为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;

 

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