九、PL/SQL之本地动态SQL

---

前言

程序运行时才能确定的SQL语句如SQL语句所使用的表和数据列也不是预先知道的或者用户所需的数据排序和分组方式，以构建各种报告…,由于SQL语句是动态产生的，所以被称为动态的。

---

一、EXECUTE IMMEDIATE

动态SQL语句：
‘SELECT first_name, last_name FROM stu WHERE stuid = :stu_id’
无法预先知道需要查询学生ID的值，需要使用绑定参数 :stu_id来实现指定。绑定参数是未声明标识符的占位符，名称的前缀是冒号。PL/SQL不会区分如下两个语句
‘SELECT first_name, last_name FROM stu WHERE stuid = :stu_id’
‘SELECT first_name, last_name FROM stu WHERE stuid = :id’

为处理动态SQL语句，需要使用EXECUTE IMMEDIATE，或者OPEN-FOR、FETCH和CLOSE语句。EXECUTE IMMEDIATE被用于单行SELECT语句、所有的DML语句和DDL语句。OPEN-FOR、FETCH和CLOSE语句用于多行SELECT语句以及引用游标。

1.1 EXECUTE IMMEDIATE语句

语法如下：
EXECUTE IMMEDIATE dynamic_sql_string
[INTO defined_variable1, defined_variable2, …]
[USING [in|out|in out] bind_argument1, bind_argument2,…]
[{RETURNING|RETURN} field1, field2, … INTO bind_argument1, bind_argument2, …]

DECLARE
    sql_stmt    VARCHAR2(1000);
    plsql_block  VARCHAR2(1000);
    v_ooag011 ooag_t.ooag011%TYPE;
    v_total_ooag NUMBER;
    v_new_ooag011 ooag_t.ooag011%TYPE;
    v_ooag001 ooag_t.ooag001%TYPE := '00261' ;
BEGIN
    --用法一：
    sql_stmt := 'CREATE TABLE ooag_t_bak' ||
                ' AS SELECT ooag001,ooag011 FROM ooag_t WHERE ooagent = 70 ' ||
                ' AND ooag001 = ' || v_ooag001;
    EXECUTE IMMEDIATE sql_stmt;
    
    --用法二:
    EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ooag_t_bak'
    INTO v_total_ooag;
    DBMS_OUTPUT.PUT_LINE(v_total_ooag);
    
    --用法三:
    plsql_block := 'DECLARE '                                                       ||
                   ' v_date DATE; '                                                 ||
                   'BEGIN'                                                          ||
                   '    SELECT SYSDATE INTO v_date FROM DUAL; '                     ||
                   '    DBMS_OUTPUT.PUT_LINE(TO_CHAR(v_date,''YYYY-MM-DD''));'      ||
                   'END;';
    EXECUTE IMMEDIATE plsql_block;
    -- 下面这句里的:1表一需要一个绑定变量
    -- 字符串中需要'就用双引号如下面的赵老四
    sql_stmt := ' UPDATE ooag_t_bak SET ooag011 = ''赵老四'' WHERE ooag001 = :1 ' ||
    -- :2表示需要别一个变量来接收
                ' RETURNING ooag011 INTO :2';
    EXECUTE IMMEDIATE sql_stmt USING v_ooag001 RETURNING INTO  v_new_ooag011;
    DBMS_OUTPUT.PUT_LINE('NEW ooag011 IS:'||v_new_ooag011);
END;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

二、OPEN-FOR、FETCH和CLOSE语句

OPEN_FOR、FETCH和CLOSE语句被用于处理多行查询或者游标。

2.1 打开游标

对于动态SQL，OPEN-FOR语句有个可选的USING子句，使用它可以在运行时向绑定参数传值。
语法：
OPEN cursor_variable FOR dynamic_sql_string
[USING bind_argument1, bind_argument2, …]
cursor_variable是一种弱类型的REF CURSOR变量，并且dynamic_SQL_string是包含多行查询的字符串

DECLARE
    TYPE ooag_cur_type IS REF CURSOR;
    -- 定义游标变量，在运行时,ooag_cur变量与SELECT语句建立关联
    ooag_cur ooag_cur_type;
    v_ooagent ooag_t.ooagent%TYPE := &ooagent;
    v_ooag001  ooag_t.ooag001%TYPE;
    v_ooag011  ooag_t.ooag011%TYPE;
BEGIN
    -- 打开游标
    OPEN ooag_cur FOR 
        ' SELECT ooag001,ooag011 FROM ooag_t WHERE ooagent = :1 '
    USING v_ooagent;
    ......
END
1
2
3
4
5
6
7
8
9
10
11
12
13
14

2.2 游标检索

DECLARE
    TYPE ooag_cur_type IS REF CURSOR;
    -- 定义游标变量，在运行时,ooag_cur变量与SELECT语句建立关联
    ooag_cur ooag_cur_type;
    v_ooagent ooag_t.ooagent%TYPE := &ooagent;
    v_ooag001  ooag_t.ooag001%TYPE;
    v_ooag011  ooag_t.ooag011%TYPE;
BEGIN
    OPEN ooag_cur FOR 
        ' SELECT ooag001,ooag011 FROM ooag_t WHERE ooagent = :1 '
    USING v_ooagent;
    
    --游标检索
    LOOP 
        -- INTO子句中的变量数量必须对应到游标所返回的列数，INTO子句中变量与游标列必须是类型兼容的。
        FETCH ooag_cur INTO v_ooag001,v_ooag011;
        EXIT WHERE ooag_cur%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('v_ooag001='||v_ooag001||',v_ooag011='||v_ooag011);
    END LOOP;
END
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

2.3 关闭游标

DECLARE
    TYPE ooag_cur_type IS REF CURSOR;
    -- 定义游标变量，在运行时,ooag_cur变量与SELECT语句建立关联
    ooag_cur ooag_cur_type;
    v_ooagent ooag_t.ooagent%TYPE := &ooagent;
    v_ooag001  ooag_t.ooag001%TYPE;
    v_ooag011  ooag_t.ooag011%TYPE;
BEGIN
    OPEN ooag_cur FOR 
        ' SELECT ooag001,ooag011 FROM ooag_t WHERE ooagent = :1 '
    USING v_ooagent;
    
    -- 游标检索
    LOOP 
        -- INTO子句中的变量数量必须对应到游标所返回的列数，INTO子句中变量与游标列必须是类型兼容的。
        FETCH ooag_cur INTO v_ooag001,v_ooag011;
        EXIT WHEN ooag_cur%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('v_ooag001='||v_ooag001||',v_ooag011='||v_ooag011);
    END LOOP;
    -- 关闭游标
    CLOSE ooag_cur;
EXCEPTION
    WHEN OTHERS THEN
        IF ooag_cur%ISOPEN THEN
            CLOSE ooag_cur;
        END IF;
END;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27