Oracle学习教程：动态SQL与游标详解

转：http://www.lmwlove.com/ae/ID143

动态 SQL

动态 SQL是指在PL/SQL程序执行时生成的 SQL 语句
编译程序对动态 SQL 不做处理，而是在程序运行时动态构造语句、对语句进行语法分析并执行
DDL 语句命令和会话控制语句不能在 PL/SQL 中直接使用，但是可以通过动态 SQL 来执行
执行动态 SQL 的语法：

EXECUTE IMMEDIATE dynamic_sql_string
[INTO  define_variable_list]
[USING bind_argument_list];

示例：

DECLARE
  sql_stmt VARCHAR2(200);
  emp_id NUMBER(4) := 7566;
  emp_rec emp%ROWTYPE;
BEGIN
EXECUTE IMMEDIATE
‘CREATE TABLE COM (id NUMBER,com NUMBER)’;

sql_stmt := 'SELECT * FROM emp WHERE empno = :id';
EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id;
END;

游标简介

游标是指逐行处理查询结果，以编程的方式访问数据
游标的类型：
1,隐式游标.2,显式游标.3,REF 游标
注意：REF 游标用于处理运行时才能确定的动态 SQL 查询的结果

隐式游标
在PL/SQL中使用DML语句时自动创建隐式游标,隐式游标自动声明、打开和关闭，其名为 SQL.
通过检查隐式游标的属性可以获得最近执行的DML 语句的信息.
隐式游标的属性有：
%FOUND – SQL 语句影响了一行或多行时为 TRUE
%NOTFOUND – SQL 语句没有影响任何行时为TRUE
%ROWCOUNT – SQL 语句影响的行数
%ISOPEN  - 游标是否打开，隐式游标始终为FALSE

示例1：只有在 DML 语句影响一行或多行时，才返回 True

SQL > SET SERVEROUTPUT ON
SQL > BEGIN
 UPDATE toys SET toyprice=270
 WHERE toyid= 'P005';
 IF SQL%FOUND THEN
  DBMS_OUTPUT.PUT_LINE(‘表已更新');
 END IF;
 END;
 /

示例2：如果 DML 语句不影响任何行，则返回 True

SQL > SET SERVEROUTPUT ON
SQL > DECLARE
   v_TOYID TOYS.ID%type := '&TOYID';
   v_TOYNAME TOYS.NAME%Type := '&TOYNAME';
 BEGIN
   UPDATE TOYS SET NAME = v_TOYNAME
   WHERE toyid=v_TOYID;
   IF SQL%NOTFOUND THEN
  DBMS_OUTPUT.PUT_LINE('编号未找到。');
  ELSE
  DBMS_OUTPUT.PUT_LINE(‘表已更新');
 END IF;
 END;
/

示例3：返回 DML 语句影响的行数

SQL > SET SERVEROUTPUT ON
SQL > BEGIN
 UPDATE emp
 SET ename= 'Robert'
 WHERE empno=‘7369';
 DBMS_OUTPUT.PUT_LINE (SQL%ROWCOUNT);
 END;
 /

SELECT INTO 语句
示例1：如果没有与SELECT INTO语句中的条件匹配的行，将引发NO_DATA_FOUND异常

SQL > SET SERVEROUTPUT ON
SQL > DECLARE
 empid VARCHAR2(10);
 desig VARCHAR2(10);
 BEGIN
 empid:= '&Employeeid';
 SELECT designation INTO desig
 FROM employee WHERE empno=empid;
 EXCEPTION
 WHEN NO_DATA_FOUND THEN
   DBMS_OUTPUT.PUT_LINE('职员未找到');
 END;
 /

示例2：如果 SELECT INTO 语句返回多个值，将引发TOO_MANY_ROWS异常

SQL > SET SERVEROUTPUT ON
SQL > DECLARE
 empid VARCHAR2(10);
 BEGIN
 SELECT empno INTO empid FROM emp;
 EXCEPTION
 WHEN TOO_MANY_ROWS THEN
   DBMS_OUTPUT.PUT_LINE('该查询提取多行');
 END;
 /

显式游标

显式游标在 PL/SQL 块的声明部分定义查询，该查询可以返回多行
显式游标的操作过程：

任务2：查询符合条件的多个员工的信息使用显式游标

SQL > SET SERVEROUTPUT ON
SQL > DECLARE
 v_ename EMP.ENAME%TYPE;
 v_salary EMP.SAL%TYPE;
--声明游标
 CURSOR c_emp IS SELECT ename,sal FROM emp;
 BEGIN
--打开游标
   OPEN c_emp;
 LOOP
--提取行
   FETCH c_emp INTO v_ename,v_salary;
   EXIT WHEN c_emp%NOTFOUND;
   DBMS_OUTPUT.PUT_LINE('Salary of Employee '|| v_ename || ' is: '|| v_salary);
  end loop;
--关闭游标
CLOSE  c_emp;
 END;
/

任务3 ：统计不同部门的员工信息 使用带参数的显式游标

SQL > SET SERVEROUTPUT ON
SQL > DECLARE
 dept_code emp.deptno%TYPE;
 emp_code  emp.empno%TYPE;
 emp_name  emp.ename%TYPE;
  CURSOR emp_cur (deptparam NUMBER) IS
   SELECT empno, ename FROM emp
   WHERE deptno= deptparam;
 BEGIN
  dept_code := &部门编号;
  OPEN emp_cur(dept_code);
  LOOP
   FETCH emp_cur INTO emp_code, emp_name;
   EXIT WHEN emp_cur%NOTFOUND;
   DBMS_OUTPUT.PUT_LINE(emp_code||' '|| emp_name);
    END LOOP;
  CLOSE emp_cur;
 END;

使用显式游标更新行
允许使用游标删除或更新活动集中的行
声明游标时必须使用 SELECT … FOR UPDATE语句 

CURSOR <cursor_name > IS
   SELECT statement FOR UPDATE;

 UPDATE <table_name >
 SET <set_clause >
 WHERE CURRENT OF <cursor_name >
--删除的语法
DELETE FROM <table_name >
WHERE CURRENT OF <cursor_name >

任务4：修改或删除满足条件的多个用户信息 使用显式游标更新行
例：查询EMP表中部门号为20的员工目前的薪水。 SQL > select empno,deptno,sal from emp where deptno=20;

例：利用UPDATE语句和WHERE条件中的CURRENT OF子句，将EMP表中部门号为20的员工提高10%的薪水。

SQL > SET SERVEROUTPUT ON
SQL > declare
cursor salcur(depno number) is
select sal from emp where deptno =depno for update of sal;
 new_sal number;
   begin
 for currentsal in salcur(20) loop
 new_sal:=currentsal.sal;
update emp set sal=1.1*new_sal where current of salcur;
   end loop;  end;
 /

循环游标
循环游标用于简化游标处理代码,当用户需要从游标中提取所有记录时使用,循环游标的语法如下：

FOR < record_name> IN
 < corsor_name[(parameter[,parameter]...)]
 | (query_difinition>
 LOOP
 <executable statements >
 END LOOP;

示例：

SQL > SET SERVER OUTPUT ON
SQL >  DECLARE
 CURSOR c_dept IS SELECT deptno,dname FROM dept ORDER BY deptno;
 CURSOR c_emp (p_dept VARCHAR2) IS
  SELECT ename,sal  FROM emp  WHERE deptno=p_dept  ORDER BY ename;
 v_tot_salary EMP.SAL%TYPE;
 BEGIN
   FOR r_dept IN c_dept LOOP
   DBMS_OUTPUT.PUT_LINE('Department:'|| r_dept.deptno||'-'||r_dept.dname);
   v_tot_salary:=0;
 FOR r_emp IN c_emp(r_dept.deptno) LOOP
DBMS_OUTPUT.PUT_LINE('Name: ' || r_emp.ename || ' salary: ' || r_emp.sal);
v_tot_salary:=v_tot_salary+r_emp.sal;
 END LOOP;
 DBMS_OUTPUT.PUT_LINE('Toltal Salary for dept:'|| v_tot_salary);
  END LOOP;
END;

REF 游标和游标变量
REF 游标和游标变量用于处理运行时动态执行的 SQL 查询，创建游标变量需要两个步骤：
1，声明 REF 游标类型
2，声明 REF 游标类型的变量
用于声明 REF 游标类型的语法为：

 TYPE <ref_cursor_name > IS REF CURSOR
 [RETURN <return_type >];

打开游标变量的语法如下：

OPEN cursor_name FOR select_statement;

声明强类型的 REF 游标

Type?emp_refcur_t?is?ref?cursor return?employee%rowtype;
order_cur ?emp_refcur_t;

声明弱类型的 REF 游标

TYPE my_ctype IS REF CURSOR;
stud_cur my_ctype;

示例：

SQL > DECLARE
--声明REF游标类型
TYPE toys_curtype IS REF CURSOR
  RETURN toys%ROWTYPE;
--声明游标变量
 toys_curvar toys_curtype;
toys_rec toys%ROWTYPE;
 BEGIN
   OPEN toys_curvar FOR
  SELECT * FROM toys;
   FETCH toys_curvar INTO toys_rec;
   ...
   CLOSE toys_curvar;
 END;

游标变量的优点和限制

游标变量的功能强大，可以简化数据处理。游标变量的优点有：
1,可从不同的 SELECT 语句中提取结果集
2,可以作为过程的参数进行传递
3,可以引用游标的所有属性
4,可以进行赋值运算

使用游标变量的限制：
1,不能在程序包中声明游标变量
2,FOR UPDATE子句不能与游标变量一起使用
3,不能使用比较运算符

使用游标变量执行动态 SQL
示例：

DECLARE
  r_emp emp%ROWTYPE;
  TYPE c_type IS REF CURSOR;
  cur c_type;
  p_salary NUMBER;
BEGIN
  p_salary := 2500;
  OPEN cur FOR 'select * from emp where sal >:1
order by sal desc'
  USING p_salary;
  DBMS_OUTPUT.PUT_LINE('薪水大于'|| p_salary ||'的员工有：');
  LOOP
FETCH cur INTO r_emp;
EXIT WHEN cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('编号：'|| r_emp.empno
  || ' 姓名：' || r_emp.ename|| ' 薪水：' || r_emp.sal );
  END LOOP;
  CLOSE cur;
END;