《Oracle 11g数据库管理与开发基础教程 教学课件 ppt 作者 袁鹏飞 第15章》由会员分享，可在线阅读，更多相关《Oracle 11g数据库管理与开发基础教程 教学课件 ppt 作者 袁鹏飞 第15章（27页珍藏版）》请在金锄头文库上搜索。
1、,Oracle 11g数据库管理与开发基础教程,授课教师： 职务：,第15章 动态SQL操作,课程描述 在PL/SQL程序块中可以直接使用SQL语句，但是仅限DML语句，这是因为PL/SQL采用早期绑定变量的方式进行编译。如果要使用DDL语句和系统控制语句，就只能通过动态SQL来实现。本章将介绍动态SQL的基本概念，以及在PL/SQL中使用动态SQL的方法。,本章知识点,动态SQL的基本概念 PL/SQL中使用动态SQL,15.1 动态SQL,PL/SQL程序中可以使用的SQL语句分为两种：静态SQL语句和动态SQL语句。 静态SQL语句，即在PL/SQL块中的SQL语句在编译时被绑定确定下来
2、，执行过程中不能再改变操作对象。 动态SQL是指在PL/SQL块编译时SQL语句是不确定的，编译程序不对动态SQL语句部分进行处理，而是在程序运行时动态地创建语句，对语句进行语法分析并执行该语句。,当PL/SQL程序中必须执行DDL语句，或者用户不确定在编译时SQL语句的全部文本、输入/输出变量的数量以及类型时，使用动态SQL。 PL/SQL提供了两种实现动态SQL的方法： 本地动态SQL：这是PL/SQL的特色之一，用来创建以及执行动态SQL语句； DBMS_SQL包：这是Oracle提供的包，用于创建、执行和描述动态SQL语句。,15.2 本地动态SQL,本地动态SQL的处理又分为两种方法
3、： 调用EXECUTE IMMEDIATE语句； 调用OPEN-FOR、FETCH和CLOSE语句。,15.2.1 动态SQL处理方法一,本地动态SQL方法一通过调用EXECUTE IMMEDIATE语句来处理动态SQL语句，该语句的语法格式为： EXECUTE IMMEDIATE dynamic_sql_stmt INTO variable1 ,variable2, | record USING IN | OUT | IN OUT bind_argument1,bind_argument2, RETURNING | RETURN into_clause;,其中： dynamic_sql_st
4、mt：要执行的SQL语句文本； INTO子句：提供已定义的变量列表，用于保存SELECT语句的返回结果，当动态SQL语句为返回单行查询结果的SELECT语句时，就会使用这个子句； USING子句：列出绑定参数列表，这些参数会传递到动态SQL语句；与存储过程中的参数模式一样，在EXECUTE IMMEDIATE语句内，IN、OUT和IN OUT指出所传递的参数模式；如果不指定任何模式，USING子句中所列出的参数均是输入参数； RETURING INTO或RETURN INTO子句：包含绑定参数列表，用于将SQL语句执行的返回值保存到相应的变量中；实际上，如果在EXECUTE IMMEDIATE
5、语句中使用该子句返回值，那么在USING子句中就没必要使用OUT参数返回值了。,例1，在PL/SQL块中使用动态SQL实现数据库表的动态删除、重建和查询操作。 SCOTTorcl_dbs DECLARE 2 sql_stmt1 VARCHAR2(200); 3 sql_stmt2 VARCHAR2(200); 4 dept_no emp.deptno%TYPE; 5 table_name VARCHAR2(20); 6 salary emp.sal%TYPE; 7 empcount NUMBER(4); 8 BEGIN 9 EXECUTE IMMEDIATE DROP TABLE myemp;
6、 10 dept_no := 11 -根据用户输入，构建动态SQL的DDL语句文本,12 sql_stmt1 := CREATE TABLE myemp | 13 AS SELECT * FROM emp | 14 WHERE deptno = | dept_no; 15 EXECUTE IMMEDIATE sql_stmt1; 16 -用户可以选择从emp表或myemp表查询 17 table_name := 28 /,注意：sql_stmt2定义的动态SQL文本中使用了占位符“:1”，它相当于过程 或函数的形式参数，使用“:”作为前缀。当执行动态SQL语句时，用 USING子句中的绑定变量
7、的值替换该占位符。,本地动态SQL中使用绑定变量需要注意以下两点： DDL语句不接受绑定参数。 模式对象名称不能作为绑定参数传入。,15.2.3 动态SQL处理方法二,如果动态SELECT语句返回多行数据，就需要借助游标来实现对动态SQL的处理。 这种处理方法分为以下3个步骤： 打开游标变量 循环获取每一行数据 关闭游标变量,打开游标变量 用OPEN FOR语句将一个游标变量和动态SQL语句关联起来，并可选择使用其USING子句，在运行时向绑定参数传值。 OPEN FOR语句的语法格式为： OPEN cursor_variable FOR select_statement | dynamic_
8、string USING IN | OUT | IN OUT bind_argument;,其中， cursor_variable：游标变量名； select_statement：可为返回多行的SELECT语句； dynamic_string：包含SELECT语句的字符串变量； USING子句：用来为游标查询语句中的绑定参数传值。,循环获取每一行数据 打开游标变量后，PL/SQL基于游标查询语句建立游标结果集合，接下来使用FETCH语句从查询结果集中读取一条记录，把它保存到PL/SQL语句块所定义的变量中，然后把游标指针移动到下一条记录。 FETCH语句的语法格式为： FETCH cursor
9、_variable INTO variable , variable, | record;,关闭游标变量 当处理完动态SQL语句返回的游标结果集合后，要使用CLOSE语句来关闭游标变量。 语法格式为： CLOSE cursor_variable;,例2，根据用户输入的部门号，检索员工信息。 SCOTTorcl_dbs DECLARE 2 TYPE c_emp_typ IS REF CURSOR; 3 v_emp_cursor c_emp_typ; 4 v_emp_record emp%ROWTYPE; 5 v_sql VARCHAR2(200); 6 v_deptno NUMBER(2); 7
10、 BEGIN 8 -执行时由用户输入部门编号 9 v_deptno := ,13 LOOP 14 FETCH v_emp_cursor INTO v_emp_record; 15 -根据游标属性的返回值控制循环是否继续 16 EXIT WHEN v_emp_cursor%NOTFOUND; 17 DBMS_OUTPUT.PUT_LINE (v_emp_record.empno | | 18 v_emp_record.ename | | v_emp_record.sal ); 19 END LOOP; 20 CLOSE v_emp_cursor; 21 EXCEPTION 22 WHEN OTH
11、ERS THEN 23 IF v_emp_cursor%ISOPEN THEN 24 CLOSE v_emp_cursor; 25 END IF; 26 DBMS_OUTPUT.PUT_LINE (SQLERRM); 27 END ; 28 /,15.3 DBMS_SQL包,如果在编写代码时不清楚SELECT列表，或者动态SQL语句所需要的输入/输出参数的数量以及类型时，就不能使用本地动态SQL的方法来处理动态SQL，此时需要通过DBMS_SQL包中的过程和函数来执行动态SQL语句。 DBMS_SQL包定义一个实体（entity）：SQL游标号，可以在调用之间传递和存储它。 DBMS_SQL包
12、提供了一个接口，使得用户能够在PL/SQL中通过动态SQL来解析所有DML和DDL语句。,使用DBMS_SQL包实现动态SQL的处理步骤如下： 打开游标 解析 绑定变量或数组 动态定义绑定列变量 执行 提取记录行 定义返回值的变量或列 关闭游标,提示：上述8个步骤中，只有查询语句才执行第（4）步、第（6）步和第（7）步。 例1，依据用户的要求删除相应的员工记录。 创建一个存储过程： SCOTTorcl_dbs CREATE OR REPLACE PROCEDURE del_info (p_sql VARCHAR2) 2 AS 3 cursor_name INTEGER; 4 v_ret INT
13、EGER; 5 BEGIN 6 cursor_name := DBMS_SQL.OPEN_CURSOR; 7 DBMS_SQL.PARSE(cursor_name, p_sql, DBMS_SQL.NATIVE); 8 v_ret := DBMS_SQL.EXECUTE (cursor_name);,9 DBMS_SQL.CLOSE_CURSOR (cursor_name); 10 DBMS_OUTPUT.PUT_LINE(本次操作共处理了 | v_ret | 行记录); 11 END del_info; 12 / 调用该存储过程执行动态SQL，以删除员工信息： SCOTTorcl_dbs E
14、XECUTE del_info(DELETE FROM emp WHERE ename LIKE S%); 执行结果为： 本次操作共处理了1行记录 PL/SQL 过程已成功完成。,例2，从books表中检索出书价高于指定价格（由过程参数p_price指定）的图书记录，然后插入到指定的临时表（表名由d_table参数指定，该表需要事先创建）中。 BOOKS_PUBorcl_dbs CREATE OR REPLACE PROCEDURE query_info ( 2 p_price IN NUMBER, d_table IN VARCHAR2) 3 IS 4 s_cursor_name INTEG
15、ER; 5 d_cursor_name INTEGER; 6 v_ret INTEGER; 7 b_id VARCHAR2(10); 8 b_name VARCHAR2(60); 9 b_author VARHCAR2(50); 10 b_price NUMBER(8, 2);,11 BEGIN 12 s_cursor_name := DBMS_SQL.OPEN_CURSOR; 13 DBMS_SQL.PARSE (s_cursor_name, SELECT * FROM books | 14 WHERE price :x, DBMS_SQL.NATIVE); 15 DBMS_SQL.BIND
16、_VARIABLE(s_cursor_name, :x, p_price); 16 17 DBMS_SQL.DEFINE_COLUMN(s_cursor_name, 1, b_id, 10); 18 DBMS_SQL.DEFINE_COLUMN(s_cursor_name, 2, b_name, 60); 19 DBMS_SQL.DEFINE_COLUMN(s_cursor_name, 3, b_author, 50); 20 DBMS_SQL.DEFINE_COLUMN(s_cursor_name, 4, b_price); 21 v_ret:=DBMS_SQL.EXECUTE(s_cursor_n
