记录关于存储过程动态SQL及传参

记录关于存储过程动态SQL及传参
参考
http://t.csdn.cn/rWuko
http://t.csdn.cn/V5asX

CREATE OR REPLACE PROCEDURE "SYSDBA"."DMDB_SQL"("NAME" IN VARCHAR(100),"XB" IN VARCHAR(100),"BH" IN VARCHAR(100))
AUTHID DEFINER       as  
--创建存储过程，给当前会话设置备注，查询会话信息
v_1 varchar2(100);
v_2 varchar2(100);
v_3 varchar2(100);
str_sql varchar2(500); 
begin
v_1:="NAME";   --传参相关
v_2:="XB";
v_3:="BH";

str_sql := 'CREATE
        TABLE "SYSDBA"."TABLE003"
        (  "SESS_ID001" VARCHAR2(50),
            "NAME"       VARCHAR2(50),
              "XB"      VARCHAR2(50),
                "BH"      VARCHAR2(50)     )';   --创建临时表
  execute immediate str_sql ;   --立即执行str_sql  
 str_sql := 'INSERT INTO table003(SESS_ID001,NAME,XB,BH)  VALUES ((select SESS_ID from v$sessions where sess_id = DBMS_SESSION.UNIQUE_SESSION_ID),:1, :2,:3)'; 
  EXECUTE IMMEDIATE str_sql USING v_1, v_2,v_3;    --传参相关
  str_sql := 'select *  FROM table003 a,v$sessions s where a.SESS_ID001 =s.SESS_ID';   --查询结果
  execute immediate str_sql ;   --立即执行str_sql 
     str_sql  := 'drop table  table003 ';    --删除临时表
  execute immediate str_sql ;   --立即执行str_sql
commit;
end;

--    call "SYSDBA"."DMDB_SQL"('张三','男','编号1');