Dynamic SQL笔记

动态SQL语句（在PL/SQL中使用动态SQL可以更加灵活处理数据，但是性能不及静态SQL
        当PL/SQL功能确定时应使用静态SQL语句）
Dynamic SQL Statement
    |
    |
    |___DDL(EXECUTE IMMEDIATE)
    |
    |
    |___DCL(EXECUTE IMMEDIATE)
    |
    |       _______':' (EXECUTE IMMEDIATE dml_stat USING &1;)
    |      |
    |      |
    |___DML|_______RETURNING (EXECUTE IMMEDIATE dml_stat RETURNING INTO v_var1;)
    |      |        (DML中INTO子句后必须使用占位符':')
    |      |
    |      |_______':'+RETURNING(...USING &1,&2..RETURNING INTO v_var1,v_va2,v_var3...)
    |
    |
    |
    |       _______单行(dql_stat varcahr2(100) :='select * from emp where empno=:eno';)
    |      |        (EXECUTE IMMEDIATE dql_stat into emp_record USING &eno;)
    |      |
    |___DQL|
    |      |_______多行(dql_stat varcahr2(100) :='select * from emp where deptno=:dno';)
    |                (定义游标 open emp_cursor for dql_stat using &dno;)
    |
    |             ___forall i in 1..ename_table.count
    |            |    execute immediate dml_stat using ename_table(i);
    |            |
    |            |
    |            |___execute immediate dql_stat bulk collect into
    |___批量绑定 |    ename_table,job_table using &dno;
                 |   
                 |
                 |___execute immediate dml_stat using &dno returning
                 |    bulk collect into ename_table;
                 |
                 |
                 |___open emp_cursor for dql_stat using &dno;
                      fetch emp_cursor bulk collect into ename_table;
                      close emp_cursor;


========================动态DML（：＋returning）========================
DECLARE
        V_ENAME SCOTT.T1.ENAME%TYPE;
        V_DEPTNO SCOTT.T1.DEPTNO%TYPE;
        DML_STAT VARCHAR2(100) :='UPDATE T1 SET ENAME=INITCAP(ENAME) WHERE EMPNO=:EMPNO RETURNING ENAME,DEPTNO INTO :NAME,:SALARY';
BEGIN
        EXECUTE IMMEDIATE DML_STAT USING &1 RETURNING INTO V_ENAME,V_DEPTNO;
        DBMS_OUTPUT.PUT_LINE(V_ENAME||' >> '||V_DEPTNO);
END;
/

=========================动态DML上的批量绑定=============================
declare
        type ename_table_type is table of emp.ename%type;
        ename_table ename_table_type;
        dml_stat varchar2(100) :='update t1 set sal=sal*10 where ename=:ename';
begin
        ename_table :=ename_table_type('SCOTT','KING','MILLER');
        forall i in 1..ename_table.count
        execute immediate dml_stat using ename_table(i);
end;
/

=======================在DML返回子句中使用批量绑定===========================
  1 declare
  2         type ename_table_type is table of scott.emp.ename%type;
  3         ename_table ename_table_type :=ename_table_type(null,null);
  4         type sal_table_type is table of scott.emp.sal%type;
  5         sal_table sal_table_type :=sal_table_type(1,2);
  6         dml_stat varchar2(100) :='update emp set comm=9999 where deptno=:deptno returning             ename,sal into :name,:salary';
  7 begin
  8         execute immediate dml_stat using &1 returning bulk collect into ename_t                      able,sal_table;
  9         for i in ename_table.first..ename_table.last loop
 10                 dbms_output.put_line(ename_table(i)||' >> '||sal_table(i));
 11         end loop;
 12 end;
 13 /

=======================批量绑定处理多行查询===========================
  1 declare
  2         type ename_table_type is table of scott.emp.ename%type;
  3         type hiredate_table_type is table of scott.emp.hiredate%type;
  4         ename_table ename_table_type :=ename_table_type(null);
  5         hiredate_table hiredate_table_type :=hiredate_table_type(null);
  6         dql_stat varchar2(100) :='select ename,hiredate from emp where job=:job';
  7 begin
  8         EXECUTE IMMEDIATE DQL_STAT BULK COLLECT INTO ENAME_TABLE,HIREDATE_TABLE USING             '&1';
  9         for i in ename_table.first..ename_table.last loop
 10         dbms_output.put_line(ename_table(i)||' >> '||hiredate_table(i));
 11         end loop;
 12 end;
 13 /

=================在FETCH语句中使用批量提取(还可以使用LIMIT限制提取行数)=====================
  1 declare
  2         type cursor_type is ref cursor;
  3         emp_cursor cursor_type;
  4         type ename_table_type is table of scott.emp.ename%type;
  5         type hiredate_table_type is table of scott.emp.hiredate%type;
  6         ename_table ename_table_type :=ename_table_type(null);
  7         hiredate_table hiredate_table_type :=hiredate_table_type(null);
  8         dql_stat varchar2(100) :='select ename,hiredate from emp where job=:job';
  9 begin
 10         open emp_cursor for dql_stat using '&1';
 11         fetch emp_cursor bulk collect into ename_table,hiredate_table;
 12         close emp_cursor;
 13         for i in ename_table.first..ename_table.last loop
 14         dbms_output.put_line(ename_table(i)||' >> '||hiredate_table(i));
 15         end loop;
 16 end;
 17 /