開發動態SQL 學習筆記

當編寫動態SQL程序時，根據葯處理SQL的不同，可以使用三种不同類型的動態SQL方法：

（1）使用EXECUTE IMMEDIATE語句

（2）使用OPEN  FOR，FETCH和CLOSE語句

（3）使用批量動態SQL

一、處理非查詢語句

1.使用EXECUTE IMMEDIATE處理DDL操作

SQL> create or replace procedure drop_table(table_name varchar2)
  2  is
  3  sql_statement varchar2(100);
  4  begin
  5  sql_statement:='drop table '||table_name;
  6  execute immediate sql_statement;
  7  end;
  8  /

Procedure created.

SQL> exec drop_table('TEST1');

PL/SQL procedure successfully completed.

2.使用EXECUTE IMMEDIATE處理DCL操作

SQL> conn system/system
Connected.
SQL> create or replace procedure grant_sys_prive(priv varchar2,username varchar2)
  2  is
  3  sql_statement varchar2(100);
  4  begin
  5  sql_statement:='grant '||priv||' to '||username;
  6  execute immediate sql_statement;
  7  end;
  8  /

Procedure created.

SQL> exec grant_sys_priv('create session','SCOTT');

PL/SQL procedure successfully completed.

3.使用EXECUTE IMMEDIATE處理DML操作

  3.1 處理無占位符和RETURNING子句的DML語句

  SQL> declare
  2  sql_stat varchar2(100);
  3  begin
  4  sql_stat:='update emp set sal=sal*1.1 where deptno=30';
  5  execute immediate sql_stat;
  6  end;
  7  /

PL/SQL procedure successfully completed.

  3.2 處理包含占位符的DML語句

  1  declare
  2  sql_stat varchar2(100);
  3  begin
  4  sql_stat:='update emp set sal=sal*(1+:percent/100)'
  5  ||' where deptno=:dno';
  6  execute immediate sql_stat using &percent,&dno;
  7* end;
SQL> /
Enter value for percent: 15
Enter value for dno: 30
old   6: execute immediate sql_stat using &percent,&dno;
new   6: execute immediate sql_stat using 15,30;

PL/SQL procedure successfully completed.

  3.3 處理包含RETURNING子句的DML語句

  1  declare
  2  salary number;
  3  sql_stat varchar2(100);
  4  begin
  5  sql_stat:='update emp set sal=sal*(1+:percent/100)'
  6  ||' where empno=:eno returning sal into :salary';
  7  execute immediate sql_stat using &percent,&eno
  8  returning into salary;
  9  dbms_output.put_line('NEW SALARY:'||salary);
 10* end;
SQL> /
Enter value for percent: 15
Enter value for eno: 7788
old   7: execute immediate sql_stat using &percent,&eno
new   7: execute immediate sql_stat using 15,7788
NEW SALARY:3967.5

PL/SQL procedure successfully completed.

4.使用EXECUTE IMMEDIATE處理單行查詢

SQL> declare
  2  sql_stat varchar2(100);
  3  emp_record emp%rowtype;
  4  begin
  5  sql_stat:='select * from emp where empno=:eno';
  6  execute immediate sql_stat into emp_record using &eno;
  7  dbms_output.put_line('Employee : '||emp_record.ename||'''s salary is : '||emp_record.sal);
  8  end;
  9  /
Enter value for eno: 7369
old   6: execute immediate sql_stat into emp_record using &eno;
new   6: execute immediate sql_stat into emp_record using 7369;
Employee : SMITH's salary is : 920

PL/SQL procedure successfully completed.

二、處理多行查詢語句

使用OPEN  FOR，FETCH和CLOSE語句

  1  declare
  2  type empcurtype is ref cursor;
  3  emp_cur empcurtype;
  4  emp_record emp%rowtype;
  5  sql_stat varchar2(200);
  6  begin
  7  sql_stat:='select * from emp where deptno=:dno';
  8  open emp_cur for sql_stat using &dno;
  9  loop
 10  fetch emp_cur into emp_record;
 11  exit when emp_cur%notfound;
 12  dbms_output.put_line('Employee:'||emp_record.ename||',salary:'||emp_record.sal);
 13  end loop;
 14* end;
SQL> /
Enter value for dno: 10
old   8: open emp_cur for sql_stat using &dno;
new   8: open emp_cur for sql_stat using 10;
Employee:CLARK,salary:2450
Employee:KING,salary:5000
Employee:MILLER,salary:1300

PL/SQL procedure successfully completed.

三、在動態SQL中使用BULK子句

1.在EXECUTE IMMEDIATE語句中使用BULK子句

  1.1 使用BULK子句處理DML語句返回子句

  SQL> declare
  2  type ename_table_type is table of emp.ename%type
  3  index by binary_integer;
  4  type sal_table_type  is table of emp.sal%type
  5  index by binary_integer;
  6  ename_table ename_table_type;
  7  sal_table sal_table_type;
  8  sql_stat varchar(200);
  9  begin
 10  sql_stat:='update emp set sal=sal*(1+:percent/100)'
 11  ||' where deptno=:dno'
 12  ||' returning ename,sal into :name,:salary';
 13  execute immediate sql_stat using &percent,&dno
 14  returning bulk collect into ename_table,sal_table;
 15  for i in 1..ename_table.count loop
 16  dbms_output.put_line('employee name is :'||ename_table(i)
 17          ||'  employee salary is :'||sal_table(i));
 18  end loop;
 19  end;
 20  /
Enter value for percent: 15
Enter value for dno: 20
old  13: execute immediate sql_stat using &percent,&dno
new  13: execute immediate sql_stat using 15,20
employee name is :SMITH  employee salary is :1058
employee name is :JONES  employee salary is :3934.44
employee name is :SCOTT  employee salary is :3967.5
employee name is :ADAMS  employee salary is :1454.75
employee name is :FORD  employee salary is :3967.5
employee name is :NAME  employee salary is :3967.5

PL/SQL procedure successfully completed.

  1.2 使用BULK子句處理多行查詢

  1  declare
  2  type ename_table_type is table of emp.ename%type
  3  index by binary_integer;
  4  ename_table ename_table_type;
  5  sql_stat varchar2(100);
  6  begin
  7  sql_stat:='select ename from emp where deptno=:dno';
  8  execute immediate sql_stat bulk collect into ename_table using &dno;
  9  for i in 1..ename_table.count loop
 10    dbms_output.put_line('Employee''s name is '||ename_table(i));
 11  end loop;
 12* end;
SQL> /
Enter value for dno: 10
old   8: execute immediate sql_stat bulk collect into ename_table using &dno;
new   8: execute immediate sql_stat bulk collect into ename_table using 10;
Employee's name is CLARK
Employee's name is KING
Employee's name is MILLER

PL/SQL procedure successfully completed.

2.在FETCH語句中使用BULK子句

SQL> set pagesize 300
SQL> set linesize 300
SQL> set timing on
SQL> set serveroutput on size 5000

  1  declare
  2  type empcurtype is ref cursor;
  3  empcur empcurtype;
  4  type ename_table_type is table of emp.ename%type
  5  index by binary_integer;
  6  ename_table ename_table_type;
  7  sql_stat varchar2(200);
  8  begin
  9  sql_stat:='select ename from emp where job=:tt';
 10  open empcur for sql_stat using '&job';
 11  fetch empcur bulk collect into ename_table;
 12  for i in 1..ename_table.count loop
 13   dbms_output.put_line(ename_table(i));
 14  end loop;
 15* end;
SQL> /
Enter value for job: CLERK
old  10: open empcur for sql_stat using '&job';
new  10: open empcur for sql_stat using 'CLERK';
SMITH
ADAMS
JAMES
MILLER

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.03

3.在FORALL語句中使用BULK子句

   1   declare
  2   type ename_table_type is table of emp.ename%type;
  3   type sal_table_type is table of emp.sal%type;
  4   ename_table ename_table_type;
  5   sal_table sal_table_type;
  6   sql_stat varchar2(100);
  7   begin
  8   ename_table:=ename_table_type('SCOTT','SMITH','CLARK');
  9   sql_stat:='update emp set sal=sal*1.1 where ename=:name'
 10   ||' returning sal into :salary';
 11   forall i in 1..ename_table.count
 12    execute immediate sql_stat using ename_table(i)
 13    returning bulk collect into sal_table;
 14   for j in 1..ename_table.count loop
 15    dbms_output.put_line('Employee: '||ename_table(j)||'''s new salary is '||sal_table(j));
 16   end loop;
 17*  end;
SQL> /
Employee: SCOTT's new salary is 3795
Employee: SMITH's new salary is 880
Employee: CLARK's new salary is 2695

PL/SQL procedure successfully completed.