PL/SQL 基础---一些高阶用法

PL/SQL 基础—一些高阶用法

主要讨论PL/SQL中一些不常见的用法；

1.动态SQL

有的时候我们希望游标对应的SQL是可以改变的，而不是在游标声明中指定，这时需要使用游标变量

TYPE REF_CURSOR IS REF CURSOR;
l_cursor REF_CURSOR;

...
sql_str := '.....:1,:2';

OPEN l_cursor FOR sql_str 
    USING parameter1, parameter2;

DECLARE
  TYPE ref_cursor IS REF CURSOR;
  l_cur         ref_cursor;
  l_employee_id NUMBER(10);
  l_dync_sql    VARCHAR(100);
  l_name        VARCHAR(30);

BEGIN
  l_employee_id := 150;
  l_dync_sql    := 'SELECT e.LAST_NAME FROM EMPLOYEES e WHERE e.EMPLOYEE_ID > :1';
  OPEN l_cur FOR l_dync_sql
    USING l_employee_id;

  LOOP
    FETCH l_cur
      INTO l_name;
    EXIT WHEN l_cur%NOTFOUND;
    dbms_output.put_line(l_name);
  END LOOP;

END;

除此之外还可以通过EXECUTE IMMEDIATE或DBMS_SQL包来完成SQL语句的动态执行；

-- EXECUTE IMMEDIATE 带参数和输出

DECLARE
  l_name        VARCHAR(30);
  l_employee_id NUMBER(10);
  l_dync_sql    VARCHAR(100);

BEGIN
  l_employee_id := 100;
  l_dync_sql    := 'SELECT e.LAST_NAME FROM EMPLOYEES e WHERE e.EMPLOYEE_ID = :1';
  EXECUTE IMMEDIATE l_dync_sql
    INTO l_name
    USING l_employee_id;

  dbms_output.put_line(l_name);

END;


-- DBMS_SQL包

DECLARE
  l_name        VARCHAR2(30);
  l_employee_id NUMBER(10);
  l_dync_sql    VARCHAR2(100);
  l_cursor      NUMBER;
  l_row         NUMBER;

BEGIN
  l_employee_id := 100;
  l_dync_sql    := 'SELECT e.LAST_NAME  FROM EMPLOYEES e WHERE e.EMPLOYEE_ID > :2';

  l_cursor := dbms_sql.open_cursor;

  dbms_sql.parse(l_cursor, l_dync_sql, dbms_sql.native);

  dbms_sql.define_column(l_cursor, 1, l_name, 30);
  dbms_sql.bind_variable(l_cursor, ':2', l_employee_id);

  l_row := dbms_sql.execute(l_cursor);
  IF dbms_sql.fetch_rows(l_cursor) > 0 THEN
    dbms_sql.column_value(l_cursor, 1, l_name);
    IF dbms_sql.is_open(l_cursor) THEN
      dbms_sql.close_cursor(l_cursor);
    END IF;
  END IF;

  dbms_output.put_line(l_name);

END;

2.参数声明中使用NOCOPY

NOCOPY 是一个可选的标示 hint，用来告诉 PLSQL 编译器传递的是变量的引用，而
不是变量真实的值。 NOCOPY 都是用在具有 OUT 或 IN OUT 参数的存储过程中， 使
用 NOCOPY 可以获取更好的程序性能，但是如果在没有很好处理异常的程序中使用
的话也是会有一定的问题需要注意。

在这里我们需要弄清楚一个概念， 就是在声明 FUNCTION 和
PROCEDURE 的时候所定义的参数称为形式参数， 应用程序在调用的时候传递的参数
称为实际参数， 实际参数和形式参数时间的数据传递只有两种方式，传址法和传值法。
传址法就是在调用函数或者存储过程的时候， 将实际参数的地址指针传递给形式参数，
使得形式参数和实际参数指向内存中的同一个区域，从而实现参数数据的传递。
传值法就是在调用函数或者存储过程的时候， 将实际参数的值拷贝给形式参数， 而不
是通过实际参数的地址。 **默认的情况是， OUT 和 IN OUT 参数都是采用传值法， 在调
用的时候将实际参数数据拷贝到 OUT 和 IN OUT 参数中， 在当程序正常运行并且退出
的时候， 又将 OUT 和 IN OUT 形式参数数据拷贝到实际参数变量中**。

--NOCOPY
CREATE OR REPLACE PACKAGE BODY cux_plsql_test IS
  test_exception EXCEPTION; --自定义异常
  --定义存储过程，未使用 NOCOPY 标示形式参数
  PROCEDURE copy_parameter(p_number IN OUT NUMBER) IS
  BEGIN
    p_number := 10000;
    RAISE test_exceptio