oracle 动态SQL

动态SQL的书写：
1.本地动态SQL（Native Dynamic SQL），用于建立和执行SQL语句，使用EXECUTE IMMEDIATE命令来执行动态SQL语句。
2.包DBMS_SQL也可用于执行动态SQL。

CREATE OR REPLACE PROCEDURE  CreateTable(tablename VARCHAR2)
IS
BEGIN
    EXECUTE IMMEDIATE  'CREATE TABLE ' || tablespace || '(name VARCHAR(20),producer VARCHAR(30),price NUMBER)' ;
END;
--存储过程 CreateTable的输入参数是表的名字，根据表名的不同，动态创建不同的表。

处理TCL语句：（事务控制语句）
可以在PL/SQL代码中直接执行COOMMIT或ROLLBACK等事务控制语句，也可以在动态SQL中执行事务控制语句。

 1 DECLARE
 2 SQLstring VARCHAR2(1000);
 3 BEGIN
 4     SQLstring := 'COMMIT';
 5     EXECUTE IMMEDIATE 'INSERT INTO phone_info  VALUES(''N97'',''诺基亚'',400)';
 6     EXECUTE IMMEDIATE SQLstring;
 7 END;
 8 --查询执行结果：
 9 SELECT * FROM phone_info;
10 
11 NAME                 PRODUCER                            PRICE
12 -------------------- ------------------------------ ----------
13 N97                  诺基亚                                400

处理DML语句
普通的DML语句，不带占位符，不带USING子句：

 1 BEGIN
 2    EXECUTE IMMEDIATE 'INSERT INTO phone_info VALUES(''V800'',''中兴'',500)';
 3    COMMIT;
 4 END;
 5 --结果：
 6 SQL> SELECT * FROM phone_info;
 7 
 8 NAME                 PRODUCER                            PRICE
 9 -------------------- ------------------------------ ----------
10 N97                  诺基亚                                400
11 V800                 中兴                                  500
12 
13 已选择2行。

有占位符的DML，使用USING子句传入值

 1 DECLARE
 2     v_phone_name  VARCHAR2(20) := 'C510c';
 3     v_producer VARCHAR2(20) := '爱立信';
 4     v_price NUMBER := 1000;
 5 BEGIN
 6     EXECUTE IMMEDIATE 'INSERT INTO phone_info VALUES(:a, :b,:c)' 
 7     USING v_phone_name,v_producer,v_price;
 8 COMMIT;
 9 END;
10 
11 SQL> SELECT * FROM phone_info;
12 
13 NAME                 PRODUCER                            PRICE
14 -------------------- ------------------------------ ----------
15 N97                  诺基亚                                400
16 V800                 中兴                                  500
17 C510c                爱立信                               1000

执行有返回值的DML：

 1 DECLARE
 2     SQLstring VARCHAR2(1000);
 3     v_phone_name  VARCHAR2(20);
 4     v_producer VARCHAR2(20);
 5     v_price NUMBER := 400;
 6 BEGIN
 7     SQLstring := 'UPDATE phone_info SET price = 499 WHERE price = :a RETURNING name,producer,price INTO :b,:c,:d';
 8     EXECUTE IMMEDIATE SQLstring USING v_price RETURNING INTO v_phone_name,v_producer,v_price;
 9     COMMIT;
10     DBMS_OUTPUT.PUT_LINE(v_phone_name||'  '||v_producer||'   '||v_price);
11 END;

1 --结果： price为400的已经更新为499.
2 SQL> SELECT * FROM phone_info;
3 
4 NAME                 PRODUCER                            PRICE
5 -------------------- ------------------------------ ----------
6 N97                  诺基亚                                499
7 V800                 中兴                                  500
8 C510c                爱立信                               1000

在动态SQL中调用存储过程或者函数：

 1 --定义procedure，
 2 CREATE  OR REPLACE  PROCEDURE  P_call (p1 IN VARCHAR2 := NULL ) AS
 3 BEGIN
 4     DBMS_OUTPUT.PUT_LINE(p1 || 'is from PROCEDURE P_call');
 5 END P_call;
 6 在动态SQL中调用procedure，
 7 BEGIN
 8     EXECUTE IMMEDIATE ' CALL P_call(''hihihi '')';
 9 END;
10   4  /
11 hihihi is from PROCEDURE P_call
12 
13 PL/SQL 过程已成功完成。

在动态SQL中调用函数：

 1 CREATE OR REPLACE  FUNCTION F_call (p1 IN VARCHAR2)
 2 RETURN VARCHAR2 AS
 3 BEGIN
 4     RETURN p1 || ' is from FUNCTION F_call';
 5 END F_call;
 6 
 7 DECLARE 
 8 V_return VARCHAR2(50);
 9 functiong_Result VARCHAR2(50);
10 BEGIN
11     EXECUTE IMMEDIATE 'CALL F_call(''hello Daisy'') INTO :functiong_Result'
12     USING OUT V_return;
13 DBMS_OUTPUT.PUT_LINE(V_return);
14 END;
15   8  /
16 hello Daisy is from FUNCTION F_call

处理单行查询
利用动态SQL，在PL/SQL中，可以对表进行查询，通过INTO子句，可以把查询结果保存在变量中。

1 DECLARE 
2     v_maxprice NUMBER;
3     v_producer VARCHAR2(20) := '诺基亚';
4 BEGIN
5     EXECUTE IMMEDIATE 'SELECT max(price) FROM phone_info  WHERE producer= :a' 
6     INTO v_maxprice     
7     USING v_producer;
8     dbms_output.put_line(v_maxprice);
9 END;

处理多行查询：
两种方法，一是使用带有子句BULK COLLECT INTO的EXECUTE IMMEDIATE语句
二是使用BULK COLLECT INTO 处理多行查询。
BULK COLLECT可以将查询结果一次性的加载到集合中，而不是通过游标一条条地处理，这样的处理速度慢，可以在SELECT INTO 、FETCH INTO 、RETURNING INTO

语句中使用BULK COLLECT，所有的INTO变量都必须是集合（COLLECTIONN）

 1 CREATE OR REPLACE  TYPE phone_type AS OBJECT
 2 (--定义新类型对象 
 3 name VARCHAR2(20),
 4 producer VARCHAR2(20),
 5 price NUMBER
 6 );
 7 
 8 CREATE  OR REPLACE  TYPE  phone_tab AS TABLE OF phone_type;--定义新类型
 9 
10 DECLARE
11     v_rec phone_tab;
12 BEGIN
13     EXECUTE IMMEDIATE 'SELECT phone_type (name,producer,price) FROM phone_info' BULK COLLECT INTO v_rec;
14       FOR i IN v_rec.FIRST ..v_rec.LAST LOOP
15         DBMS_OUTPUT.PUT_LINE(v_rec(i).name || '  ' ||v_rec(i).producer||'  ' ||v_rec(i).price);
16     END LOOP;
17 END;

 1 DECLARE 
 2     TYPE ref_type IS REF CURSOR;
 3     v_cursor  ref_type;
 4     v_record phone_info%ROWTYPE;
 5     sqlstring  VARCHAR2(200);
 6 BEGIN
 7     sqlstring := 'SELECT * FROM phone_info WHERE price > :p';
 8     OPEN v_cursor FOR sqlstring
 9         USING 450;
10     LOOP
11         FETCH v_cursor
12             INTO  v_record;
13     DBMS_OUTPUT.PUT_LINE('the record which >'||:p||' :  '||v_record.name || '     '||v_record.price );
14     EXIT WHEN v_cursor%NOTFOUND;
15     CLOSE v_cursor;
16 END;
17 --该procedure把手机价格大于500的打印出来。

DBMS_SQL包提供一个接口，用于执行动态SQL（DDL和DML），DBMS_SQL定义了一个实体叫做游标ID，游标ID是一个PL/SQL整型数，通过游标ID可以对游标进行操作。
包DBMS_SQL提供了一系列的过程和函数，用于协同执行动态SQL，这些函数和过程是：

 1 ---------------------------函数-------------------
 2 OPEN_CURSOR，打开一个动态游标，并返回一个整型；
 3 EXECUTE(c IN INTEGER),执行游标，并返回处理的函数（INSERT、DELETE、UPDATE有意义）；
 4 FETCH_ROWS（c IN INTEGER）循环对游标中取数据。
 5 ------------------------过程----------------------
 6 CLOSE_CURSOR(c IN OUT INTEGER) --关闭游标，
 7 PARSE（c IN INTEGER,statement IN VARCHAR2,language_flag IN INTEGER)--.对动态游标所提供的SQL语句进行解析，参数c表示有效，statement为SQL语句，language_flag为解析SQL语句所用的版本，一般是V6、NATIVE、V7。若被解析的是DDL语句，则解析完成后被立即执行。
 8 DEFINE_COLUMN(c IN INTEGER, position IN INTEGER,column ANY DATATYPE,[column_size IN INTEGER]),--定义从游标中选出的列，c为游标，position为对应动态SQL中的位置（从1开始），column为该值所对应的变量，可以为任何类型，column_size定义列的宽度，如果列的类型为字符型，必须定义宽度，这个语句只用于带有SELECT语句。
 9 COLUMN_VALUE(c IN INTEGER,position IN INTEGER,VALUE),--将所取得的游标数据赋值到相应的变量，c为游标，position为位置，VALUE则为对应的变量。
10 BIND_VARIABLE（c IN INTEGER, name IN VARCHAR2,VALUE)--定义动态SQL语句（DML）中所对应字段的值。c为游标，name为字段名称，VALUE为字段的值。

DBMS_SQL执行DDL语句的过程是：打开游标-->SQL语句解析-->关闭游标。

 1 CREATE OR REPLACE PROCEDURE  CreateTable(tablename VARCHAR2)
 2 IS
 3 SQL_string VARCHAR2(1000);--存放SQL语句
 4 V_cur integer;--存放游标
 5 BEGIN
 6 SQL_string := 'CREATE TABLE '||tablename || '(name VARCHAR2(20))';
 7 V_cur :=dbms_sql.open_cursor;
 8 dbms_sql.parse(V_cur,SQL_string,DBMS_SQL.NATIVE);
 9 dbms_sql.close_cursor(V_cur);
10 END;
11 SQL> EXECUTE CreateTable('MYTB');
12 
13 PL/SQL 过程已成功完成。
14 
15 已用时间:  00: 00: 00.17
16 SQL> desc MYTB;
17  名称
18  -----------------------------------------
19 ------------------------------------------
20  NAME

利用DBMS_SQL执行SELECT语句：

 1 DECLARE
 2     V_cursor NUMBER;
 3     SQLstring VARCHAR2(1000);
 4     v_phone_name  VARCHAR2(20);
 5     v_producer VARCHAR2(20);
 6     v_price NUMBER := 400;
 7     v_count INT;
 8 BEGIN
 9     SQLstring := 'SELECT name,producer,price FROM phone_info WHERE price >:p';
10     V_cursor := dbms_sql.open_cursor;--open the cursor;
11     dbms_sql.parse(V_cursor,SQLstring,dbms_sql.native);--parse the sql statement;
12     dbms_sql.bind_variable(V_cursor,'p',v_price);
13     
14     dbms_sql.define_column(v_cursor,1,v_phone_name,20);
15     dbms_sql.define_column(v_cursor,2,v_producer,20);
16     dbms_sql.define_column(v_cursor,3,v_price);
17     v_count := dbms_sql.EXECUTE(V_cursor);
18 
19     LOOP
20         EXIT WHEN DBMS_sql.fetch_rows(V_cursor) <= 0;
21         dbms_sql.column_value(V_cursor,1,v_phone_name);
22         dbms_sql.column_value(V_cursor,2,v_producer);
23         dbms_sql.column_value(V_cursor,3,v_price);
24         dbms_output.put_line(v_phone_name|| '   '||v_producer ||'  '||v_price);
25     END LOOP;
26     dbms_sql.close_cursor(V_cursor);
27 END;
28 --把price >400的都打印出来了。

利用DBMS_SQL执行DML语句
过程：打开游标--》解析动态SQL--》绑定输入参数—》执行动态SQL--》关闭游标。

 1 DECLARE 
 2     v_cursor NUMBER;
 3     sqlstring VARCHAR2(200);
 4     v_phone_name VARCHAR2(20);
 5     v_producer VARCHAR2(20);
 6     v_price NUMBER;
 7     v_count    INT;
 8 BEGIN
 9     sqlstring := 'INSERT INTO phone_info VALUES(:a,:b,:c)'; ---:a,:b,:c
10     v_phone_name := 'iphone';
11     v_producer := 'APPLE';
12     v_price := 3999;
13     v_cursor :=dbms_sql.open_cursor;
14     dbms_sql.parse(v_cursor,sqlstring,dbms_sql.native);
15     dbms_sql.bind_variable (v_cursor,':a',v_phone_name);
16     dbms_sql.bind_variable (v_cursor,':b',v_producer);
17     dbms_sql.bind_variable (v_cursor,':c',v_price);
18     v_count := dbms_sql.EXECUTE(v_cursor);
19     dbms_sql.close_cursor(v_cursor);
20     dbms_output.put_line('INSERT '||to_char(v_count)||'  row');
21     COMMIT;
22 END;