转：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;