oracle 11g PL/SQL Programming学习十

最新推荐文章于 2024-01-24 13:46:21 发布

Cryking

最新推荐文章于 2024-01-24 13:46:21 发布

阅读量1.6k

点赞数 2

分类专栏： oracle PLSQL学习系列

本文链接：https://blog.csdn.net/edcvf3/article/details/9052781

版权

oracle 同时被 2 个专栏收录

92 篇文章 3 订阅

订阅专栏

PLSQL学习系列

26 篇文章 13 订阅

订阅专栏

----------------------------------------------------------------------------
-----------------PL/SQL学习笔记系列 By Cryking-----------------
------------------------转载请注明出处,谢谢!------------------------

在NDS中使用PL/SQL块来完成更新CLOB字段,注意USING子句中的参数传递模式应与存储过程传递参数的模式一致.

00:22:39 SCOTT@orcl> --建立PL/SQL存储过程,更新CLOB字段为空
00:41:02 SCOTT@orcl> CREATE OR REPLACE PROCEDURE get_clob(item_title_in VARCHAR2,
00:41:02   2                                       item_desc_out IN OUT CLOB) IS
00:41:02   3  BEGIN
00:41:02   4    UPDATE item
00:41:02   5       SET item_desc = empty_clob()
00:41:02   6     WHERE item_title = item_title_in
00:41:02   7    RETURNING item_desc INTO item_desc_out;
00:41:02   8  END get_clob;
00:41:03   9  /


Procedure created.


Elapsed: 00:00:00.27
00:41:05 SCOTT@orcl> DECLARE
00:42:50   2    -- Define explicit record structure.
00:42:50   3    target CLOB;
00:42:50   4    source VARCHAR2(2000) := 'CryKing!';
00:42:50   5    movie  VARCHAR2(60) := 'TEST_TITLE';
00:42:50   6    stmt   VARCHAR2(2000);
00:42:50   7  BEGIN
00:42:50   8    -- Set statement
00:42:50   9    stmt := 'BEGIN ' || ' get_clob(:input,:output); ' || 'END;';
00:42:50  10    EXECUTE IMMEDIATE stmt
00:42:50  11      USING movie, IN OUT target;
00:42:50  12    dbms_lob.writeappend(target, LENGTH(source), source);
00:42:50  13  END;
00:42:51  14  /


PL/SQL procedure successfully completed.


Elapsed: 00:00:00.02
00:42:53 SCOTT@orcl> select * from item;


   ITEM_ID ITEM_TITLE ITEM_DESC
---------- ---------- ------------------
         1 TEST_TITLE CryKing!


1 row selected.


Elapsed: 00:00:00.01

你不能在执行动态PL/SQL块时使用RETURING INTO子句,因为RETURING INTO子句只能在有INSERT,UPDATE,DELETE语句时使用.

不定个数的输入参数的动态语句
此时需要使用DBMS_SQL包
示例:

11:29:29 SCOTT@orcl> create table emp_bak as select * from emp;


表已创建。


已用时间:  00: 00: 02.39
11:38:39 SCOTT@orcl> DECLARE
11:38:40   2    -- Declare explicit record structure and table of structure.
11:38:40   3    TYPE emp_record IS RECORD(
11:38:40   4       empno VARCHAR2(60)
11:38:40   5      ,ename VARCHAR2(60));
11:38:40   6    TYPE emp_table IS TABLE OF emp_record;
11:38:40   7    -- Declare dynamic variables.
11:38:40   8    emp_cursor SYS_REFCURSOR;
11:38:40   9    emp_rows   emp_table;
11:38:40  10    -- Declare DBMS_SQL variables.
11:38:40  11    c    INTEGER := dbms_sql.open_cursor;
11:38:40  12    fdbk INTEGER;
11:38:40  13    -- Declare local variables.
11:38:40  14    counter      NUMBER := 1;
11:38:40  15    column_names DBMS_SQL.VARCHAR2_TABLE;
11:38:40  16    emp_nos      DBMS_SQL.NUMBER_TABLE;
11:38:40  17    stmt         VARCHAR2(2000);
11:38:40  18    substmt      VARCHAR2(2000) := '';
11:38:40  19  BEGIN
11:38:40  20    -- Find the rows that meet the criteria.
11:38:40  21    FOR i IN (SELECT 'emp_nos' AS column_names
11:38:40  22                    ,empno
11:38:40  23                FROM emp_bak
11:38:40  24               WHERE REGEXP_LIKE(ename, '^S'))
11:38:40  25    LOOP
11:38:40  26      column_names(counter) := counter;
11:38:40  27      emp_nos(counter) := i.empno;
11:38:40  28      counter := counter + 1;
11:38:40  29    END LOOP;
11:38:40  30    -- Dynamically create substatement.
11:38:40  31    IF emp_nos.COUNT = 1 THEN
11:38:40  32      substmt := 'WHERE empno IN (:emp_nos)';
11:38:40  33    ELSE
11:38:40  34      substmt := 'WHERE empno IN (';
11:38:40  35      FOR i IN 1 .. emp_nos.COUNT
11:38:40  36      LOOP
11:38:40  37        IF i = 1 THEN
11:38:40  38          substmt := substmt || ':' || i;
11:38:40  39        ELSE
11:38:40  40          substmt := substmt || ',:' || i;
11:38:40  41        END IF;
11:38:40  42      END LOOP;
11:38:40  43      substmt := substmt || ')';
11:38:40  44    END IF;
11:38:40  45    -- Set statement.
11:38:40  46    stmt := 'SELECT empno, ename ' || 'FROM emp_bak ' || substmt;
11:38:40  47    --输出查看
11:38:40  48    dbms_output.put_line('stmt:' || stmt);
11:38:40  49    -- Parse the statement with DBMS_SQL.
11:38:40  50    dbms_sql.parse(c, stmt, dbms_sql.native);
11:38:40  51    -- Bind the bind variable name and value.
11:38:40  52    FOR i IN 1 .. emp_nos.COUNT
11:38:40  53    LOOP
11:38:40  54      dbms_sql.bind_variable(c, column_names(i), emp_nos(i));
11:38:40  55    END LOOP;
11:38:40  56    -- Execute using DBMS_SQL.
11:38:40  57    fdbk := dbms_sql.execute(c);
11:38:40  58    -- Convert the cursor to NDS.
11:38:40  59    emp_cursor := dbms_sql.to_refcursor(c);
11:38:40  60    -- Open and read dynamic cursor, then close it.
11:38:40  61    FETCH emp_cursor BULK COLLECT
11:38:40  62      INTO emp_rows;
11:38:40  63    FOR i IN 1 .. emp_rows.COUNT
11:38:40  64    LOOP
11:38:40  65      dbms_output.put_line('[' || emp_rows(i).empno || '][' || emp_rows(i)
11:38:40  66                           .ename || ']');
11:38:40  67    END LOOP;
11:38:40  68    -- Close the System Reference Cursor.
11:38:40  69    CLOSE emp_cursor;
11:38:40  70  END;
11:38:40  71  /
stmt:SELECT empno, ename FROM emp_bak WHERE empno IN (:1,:2)
[7369][SMITH]
[7788][SCOTT]


PL/SQL 过程已成功完成。


已用时间:  00: 00: 00.01

上面的示例动态的构建了一个sql语句(SELECT empno, ename FROM emp_bak WHERE empno IN (:1,:2)).
使用循环来绑定所有的绑定变量(:1,:2,:3...)到emp_nos关联数组(emp_nos存放的是满足查询条件的主键empno).
还使用了DBMS_SQL.TO_REFCURSOR函数将标准的DBMS_SQL游标转换为弱类型的系统游标.这里会自动关闭DBMS_SQL游标.如果
你在DBMS_SQL.TO_REFCURSOR之后再执行DBMS_SQL.close_cursor你将会获得错误ORA-29471.

DBMS_SQL包
oracle公司从oracle 7开始引进DBMS_SQL包.它提供了一种方法来存储用来动态构建SQL语句的对象代码.
在DBMS_SQL之前,你不能存储SQL语句,除非存在相同定义的表.
在8i的时候,DBMS_SQL开始支持集合类型.
DBMS_SQL仍有一个主要的特性是NDS没有的.那就是当返回字段的数据类型及数量未知时它能动态管理语句.
它由两个存储来完成:DESCRIBE_COLUMNS和DESCRIBE_COLUMNS2.
像NDS一样,DBMS_SQL也支持字符串的拼接和绑定变量.
DBMS_SQL包是SYS模式拥有的.有时有必要先给SYSTEM用户赋予权限.然后你可以给单独的用户再赋予使用权限.
如:

00:31:33 SYS@ORCL> GRANT EXECUTE ON dbms_sql TO system WITH GRANT OPTION;


Grant succeeded.


Elapsed: 00:00:00.08

使用DBMS_SQL包来执行动态的DDL语句

--注意本例运行在SQL*PLUS下,并使用了替换变量
00:41:21 SCOTT@orcl> DECLARE
00:41:48   2    -- Define local DBMS_SQL variables, and open cursor.
00:41:48   3    c    INTEGER := dbms_sql.open_cursor;
00:41:48   4    fdbk INTEGER;
00:41:48   5    stmt VARCHAR2(2000);
00:41:48   6  BEGIN
00:41:48   7    -- Use a loop to check whether to drop a sequence.
00:41:48   8    FOR i IN (SELECT null
00:41:48   9                FROM user_objects
00:41:48  10               WHERE object_name = '&&1') LOOP
00:41:48  11      -- Build, parse, and execute SQL statement, then close cursor.
00:41:48  12      stmt := 'DROP TABLE &&1';
00:41:48  13      dbms_sql.parse(c, stmt, DBMS_SQL.NATIVE);
00:41:48  14      fdbk := dbms_sql.execute(c);
00:41:48  15      dbms_sql.close_cursor(c);
00:41:48  16      dbms_output.put_line('Dropped TABLE [&&1].');
00:41:48  17    END LOOP;
00:41:48  18  END;
00:41:48  19  /
Enter value for 1: NOTEXISTS_TAB


PL/SQL procedure successfully completed.


Elapsed: 00:00:00.07
00:46:20 SCOTT@orcl> UNDEFINE 1
00:46:23 SCOTT@orcl> /
Enter value for 1: EMP_BAK
Dropped TABLE [EMP_BAK].


PL/SQL procedure successfully completed.


Elapsed: 00:00:00.27

使用DBMS_SQL包来执行动态的DML语句
动态的DML语句经常作为字符串在运行时创建.
示例:

--注意dbms_application_info包的使用
01:02:36 SCOTT@orcl> EXEC dbms_application_info.set_client_info('CRY');


PL/SQL procedure successfully completed.


Elapsed: 00:00:00.06
01:03:54 SCOTT@orcl> DECLARE
01:05:12   2    -- Define local DBMS_SQL variables, and open cursor.
01:05:12   3    c     INTEGER := dbms_sql.open_cursor;
01:05:12   4    fdbk  INTEGER;
01:05:12   5    stmt1 VARCHAR2(2000);
01:05:12   6    stmt2 VARCHAR2(20) := '-1'')';
01:05:12   7    -- V$SESSION.CLIENT_INFO variable.
01:05:12   8    client VARCHAR2(64);
01:05:12   9  BEGIN
01:05:12  10    stmt1 := 'INSERT INTO EMP_BAK(EMPNO,ENAME) VALUES ' || '( 123,''TEST_';
01:05:12  11    -- Get the current CLIENT_INFO value and conditionally append to string.
01:05:12  12    dbms_application_info.read_client_info(client);
01:05:12  13    IF client IS NOT NULL THEN
01:05:12  14      stmt1 := stmt1 || client || ''')';
01:05:12  15    ELSE
01:05:12  16      stmt1 := stmt1 || stmt2;
01:05:12  17    END IF;
01:05:12  18    DBMS_OUTPUT.put_line('stmt1:'||stmt1);
01:05:12  19    -- Build, parse, and execute SQL statement, then close cursor.
01:05:12  20    dbms_sql.parse(c, stmt1, dbms_sql.native);
01:05:12  21    fdbk := dbms_sql.execute(c);
01:05:12  22    dbms_sql.close_cursor(c);
01:05:12  23    dbms_output.put_line('Rows Inserted [' || fdbk || ']');
01:05:12  24  END;
01:05:13  25  /
stmt1:INSERT INTO EMP_BAK(EMPNO,ENAME) VALUES ( 123,'TEST_CRY')
Rows Inserted [1]


PL/SQL procedure successfully completed.


Elapsed: 00:00:00.02

使用BIND_VARIABLE来绑定标量,使用BIND_ARRAY来绑定嵌套表.
你也可以使用DBMS_SQL来执行PL/SQL块.

示例:

--使用DBMS_SQL来绑定变量
01:05:14 SCOTT@orcl> CREATE OR REPLACE PROCEDURE insert_empbak(empno  number,
01:19:03   2                                            ename  VARCHAR2,
01:19:03   3                                            deptno number := NULL) IS
01:19:03   4    -- Define local DBMS_SQL variables.
01:19:03   5    c    INTEGER := dbms_sql.open_cursor;
01:19:03   6    fdbk INTEGER;
01:19:03   7    stmt VARCHAR2(2000);
01:19:03   8    -- Variable to get OUT parameter value.
01:19:03   9    client VARCHAR2(64);
01:19:03  10  BEGIN
01:19:03  11    stmt := 'INSERT INTO emp_bak(empno,ename,deptno) VALUES ( :empno,:ename,:deptno)';
01:19:03  12    -- Call and dynamically set the session for the CLIENT_INFO value.
01:19:03  13    dbms_application_info.read_client_info(client);
01:19:03  14    IF client IS NOT NULL THEN
01:19:03  15      client := client;
01:19:03  16    ELSE
01:19:03  17      client := '-1';
01:19:03  18    END IF;
01:19:03  19    -- Parse and execute the statement.
01:19:03  20    dbms_sql.parse(c, stmt, dbms_sql.native);
01:19:03  21    dbms_sql.bind_variable(c, 'empno', empno);
01:19:03  22    dbms_sql.bind_variable(c, 'ename', ename || client);
01:19:03  23    dbms_sql.bind_variable(c, 'deptno', deptno);
01:19:03  24    fdbk := dbms_sql.execute(c);
01:19:03  25    dbms_sql.close_cursor(c);
01:19:03  26    dbms_output.put_line('Rows Inserted [' || fdbk || ']');
01:19:03  27  END insert_empbak;
01:19:04  28  /


Procedure created.


Elapsed: 00:00:00.30
01:20:07 SCOTT@orcl> BEGIN INSERT_EMPBAK(321,'KING_',33); END;
01:20:21   2  /
Rows Inserted [1]


PL/SQL procedure successfully completed.


Elapsed: 00:00:00.04
01:20:40 SCOTT@orcl> SELECT EMPNO,ENAME,DEPTNO FROM EMP_BAK WHERE DEPTNO=33;


     EMPNO ENAME          DEPTNO
---------- ---------- ----------
       321 KING_CRY           33


1 row selected.


Elapsed: 00:00:00.00

你应当注意在上面动态的DML语句中,已经没有单引号来界定传入的字符值了.
这是因为值和数据类型已经绑定到语句了.

--使用DBMS_SQL包处理返回一行数据的动态SELECT语句
01:46:08 SCOTT@orcl> DECLARE
01:50:49   2    c         INTEGER := dbms_sql.open_cursor;
01:50:49   3    fdbk      INTEGER;
01:50:49   4    statement VARCHAR2(2000);
01:50:49   5    v_s       VARCHAR2(1) := 'S';
01:50:49   6    v_ename   VARCHAR2(60);
01:50:49   7    v_empno   number(4);
01:50:49   8  BEGIN
01:50:49   9    -- Build and parse SQL statement.
01:50:49  10    statement := 'SELECT empno, ename ' ||
01:50:49  11                 'FROM emp_bak WHERE substr(ename,0,1) = :s AND ROWNUM=1';
01:50:49  12    dbms_output.put_line('statement:'||statement);
01:50:49  13    dbms_sql.parse(c, statement, dbms_sql.native);
01:50:49  14    -- Define column mapping, execute statement, and copy results.
01:50:49  15    dbms_sql.define_column(c, 1, v_empno); -- Define OUT mode variable.
01:50:49  16    dbms_sql.define_column(c, 2, v_ename, 60); -- Define OUT mode variable.
01:50:49  17    dbms_sql.bind_variable(c, 's', v_s); -- Bind IN mode variable.
01:50:49  18    fdbk := dbms_sql.execute_and_fetch(c);
01:50:49  19    dbms_sql.column_value(c, 1, v_empno); -- Copy query column to variable.
01:50:49  20    dbms_sql.column_value(c, 2, v_ename); -- Copy query column to variable.
01:50:49  21    -- Print return value and close cursor.
01:50:49  22    dbms_output.put_line('[' || v_empno || '][' || NVL(v_ename, 'None') || ']');
01:50:49  23    dbms_sql.close_cursor(c);
01:50:49  24  END;
01:50:50  25  /
statement:SELECT empno, ename FROM emp_bak WHERE substr(ename,0,1) = :s AND ROWNUM=1
[7369][SMITH]


PL/SQL procedure successfully completed.


Elapsed: 00:00:00.06

--使用DBMS_SQL包处理返回多行数据的动态SELECT语句
01:50:51 SCOTT@orcl> DECLARE
01:53:54   2    c         INTEGER := dbms_sql.open_cursor;
01:53:54   3    fdbk      INTEGER;
01:53:54   4    statement VARCHAR2(2000);
01:53:54   5    v_s       VARCHAR2(1) := 'A';
01:53:54   6    v_ename   VARCHAR2(60);
01:53:54   7    v_empno   number(4);
01:53:54   8  BEGIN
01:53:54   9    -- Build and parse SQL statement.
01:53:54  10    statement := 'SELECT empno, ename ' ||
01:53:54  11                 'FROM emp_bak WHERE substr(ename,0,1) = :s ';
01:53:54  12    dbms_output.put_line('statement:'||statement);
01:53:54  13    dbms_sql.parse(c, statement, dbms_sql.native);
01:53:54  14    -- Define column mapping, execute statement, and copy results.
01:53:54  15    dbms_sql.define_column(c, 1, v_empno); -- Define OUT mode variable.
01:53:54  16    dbms_sql.define_column(c, 2, v_ename, 60); -- Define OUT mode variable.
01:53:54  17    dbms_sql.bind_variable(c, 's', v_s); -- Bind IN mode variable.
01:53:54  18    fdbk := dbms_sql.execute(c);
01:53:54  19    LOOP
01:53:54  20      EXIT WHEN dbms_sql.fetch_rows(c) = 0;
01:53:54  21    dbms_sql.column_value(c, 1, v_empno); -- Copy query column to variable.
01:53:54  22    dbms_sql.column_value(c, 2, v_ename); -- Copy query column to variable.
01:53:54  23    -- Print return value and close cursor.
01:53:54  24    dbms_output.put_line('[' || v_empno || '][' || NVL(v_ename, 'None') || ']');
01:53:54  25    END LOOP;
01:53:54  26    dbms_sql.close_cursor(c);
01:53:54  27  END;
01:53:55  28  /
statement:SELECT empno, ename FROM emp_bak WHERE substr(ename,0,1) = :s
[7499][ALLEN]
[7876][ADAMS]


PL/SQL procedure successfully completed.