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_exception; --抛出异常，未进行处理
  END copy_parameter;
  --定义存储过程，使用 NOCOPY 标示形式参数
  PROCEDURE nocopy_parameter(p_number IN OUT NOCOPY NUMBER) IS
  BEGIN
    p_number := 10000;
    RAISE test_exception; --抛出异常，未进行处理HAND 技术顾问培训
  END nocopy_parameter;
  PROCEDURE test IS
    l_number NUMBER;
  BEGIN
    l_number := 20000; --赋值实际参数
    copy_parameter(p_number => l_number); --实际参数传递给形式参数，这种方式为传值法
  EXCEPTION
    WHEN test_exception THEN
      dbms_output.put_line('copy_parameter: ' || l_number);
      BEGIN
        dbms_output.new_line;
        l_number := 20000; --重新赋值实际参数
        nocopy_parameter(l_number); --实际参数传递给形式参数，这种方式为传址法
      EXCEPTION
        WHEN test_exception THEN
          dbms_output.put_line('nocopy_parameter: ' || l_number);
      END;
  END test;
END cux_plsql_test;


--执行 test 结果如下
copy_parameter: 20000
nocopy_parameter: 10000

实际使用NOCOPY需要特别注意，因为在程序处理过程中很有可能遇到异常，而此时的变量值却发生了改变，那这个值是属于错误值；

3.除了SELECT…INTO…还有RETURNING…INTO…

前面介绍了SELECT…INTO…用于将查询结果写入到变量中，其实对于DML语句（DELETE、UPDATE、INSERT）同样有类似的语句；

-- RETURNING...INTO...

DECLARE
   l_new_name VARCHAR(30);

BEGIN
  DELETE FROM EMPLOYEES e WHERE e.EMPLOYEE_ID = 100
  RETURNING e.LAST_NAME INTO l_new_name;
  dbms_output.put_line(l_new_name);
  ROLLBACK;

  UPDATE EMPLOYEES e SET e.LAST_NAME = 'UPDATE_NAME' WHERE e.EMPLOYEE_ID = 100
  RETURNING e.LAST_NAME INTO l_new_name;
  dbms_output.put_line(l_new_name);
  ROLLBACK;

  INSERT INTO EMPLOYEES e VALUES(999,'NEW_NAME','INSERT_NAME','EMAIL','PHONENUM',SYSDATE,'AD_VP',1000,0,100,90)
  RETURNING e.LAST_NAME INTO l_new_name;
  dbms_output.put_line(l_new_name);
  ROLLBACK; 
END;

--结果是：
King
UPDATE_NAME
INSERT_NAME

之所以会有这样的语句，是英文PLSQL对数据的以上操作都是先将数据读取到内存中，处理完成后再写入（同步）到数据库中；

4.AUTHID PLSQL程序执行过程的权限问题

AUTHID 子句用来指示 Oracle 服务器在运行程序的时候是以调用者的用户权限
(invoker’s rights)，还是以创建者的用户权限(Owner rights)。使用 AUTHID 子句就有
两种形式：
AUTHID CURRENT_USER 指示程序运行的时候以调用者的用户权限执行；
AUTHID DEFINER 指示程序运行的时候以创建者的用户权限执行。
默认的情况是 AUTHID DEFINER。

---SYS用户创建
CREATE OR REPLACE PROCEDURE query_db_character AUTHID CURRENT_USER IS
       l_character_name VARCHAR2(30);
BEGIN
  SELECT p.value$  INTO l_character_name FROM props$ p WHERE p.name = 'NLS_CHARACTERSET';
  dbms_output.put_line(l_character_name);
END;

grant execute on query_db_character to 用户名;

--一般用户调用
BEGIN
  SYS.query_db_character();
END;

--提示：表或视图不存在

--使用AUTHID DEFINER则出现正常结果；

4.带默认值的参数传递

在函数或者存储过程中使用参数默认值，使用规则是附无默认值的参数，然后依次赋值有默认值参数；

CREATE OR REPLACE PROCEDURE PRO__TEST_P1(v1 NUMBER DEFAULT 3000, v2 NUMBER, v3 NUMBER DEFAULT 100 ,  c1 VARCHAR2 DEFAULT 'NAME') IS
BEGIN
  dbms_output.put_line(c1);
  dbms_output.put_line(GREATEST(v1,v2,v3));
END;

5.PLSQL中事务处理

定义

SET TRANSACTION {{
    READ {ONLY | WRITE}  --事务类型
    | ISOLATION LEVEL  {SERIALIZABLE | READ COMMITTED} --设置事务的隔离级别
    | USE ROLLBACK SEGMENT rollback_segment}   -- 针对当前事务指定合适的回滚段
    [NAME 'test']| NAME 'test'}     --事务名

事务类型： READ ONLY/READ WRITE

READ ONLY:事务内部不能执行DML操作，且读取到的数据为开始事务时刻的数据（即其他事务（SESSION）提交的修改对此事务没有影响）；

--- SESSION 1 ---------
BEGIN
COMMIT;
SET TRANSACTION READ ONLY;
END;

SELECT LAST_NAME   FROM EMPLOYEES WHERE EMPLOYEE_ID = 100;

--- SESSION 2 --------
UPDATE EMPLOYEES e SET e.LAST_NAME = 'NEW——NAME' WHERE e.EMPLOYEE_ID = 100;
COMMIT;

--- SESSION 1 --------
SELECT LAST_NAME   FROM EMPLOYEES WHERE EMPLOYEE_ID = 100;
---查询结果不变

READ WRITE :事务内部可以执行DML操作，且读取到的数据为最新的数据（提交的更改能够查询），此事务内做的修改需要提交才能被其他事务查询；

--- SESSION --------- 
BEGIN
COMMIT;
SET TRANSACTION READ WRITE;
END;


--- SESSION 2 --------
UPDATE EMPLOYEES e SET e.LAST_NAME = 'NEW——NAME' WHERE e.EMPLOYEE_ID = 100;
COMMIT;


--- SESSION 1 --------
SELECT LAST_NAME   FROM EMPLOYEES WHERE EMPLOYEE_ID = 100;
---查询结果改变

UPDATE EMPLOYEES SET LAST_NAME = 'CHANGED' WHERE EMPLOYEE_ID = 100;

--- SESSION 2 --------
SELECT LAST_NAME   FROM EMPLOYEES WHERE EMPLOYEE_ID = 100;
---查询结果不变

--- SESSION 1 --------
COMMIT;

--- SESSION 2 --------
SELECT LAST_NAME   FROM EMPLOYEES WHERE EMPLOYEE_ID = 100;
---查询结果改变


>注：
>PLSQL默认事务为Read Write
>调用 SET TRANSACTION XXX 时， 在前面加COMMIT，保证他在事务的第一条语句；

事务隔离级别
如果未指定事务类型，可以通过设置事务隔离级别来对解决数据访问冲突，大概有四种类型：
在 Oracle 的事务处理中只允许使用两个隔离级别 SERIALIZABLE 和 READ COMMITTED

未提交读(read uncommitted)
提交读(read committed)
重复读(repeatable read)
序列化(serializable)

SQL92定义了的这四种事务隔离级别(transaction isolation level)，主要是为了在并发事务执行时阻止下列现象发生：
脏读：事务读取了被其他事务写入但未提交的数据。

不可重复读：一个事务再次读取其之前曾经读取过的数据时，发现数据已被其他已提交的事务修改或删除。

幻象读（phantom read）：事务按照之前的条件重新查询时，返回的结果集中包含其他已提交事务插入的满足条件的新数据。

Oracle事务隔离级别	脏读	不可重复读	幻象读	DML操作
READ COMMITTED	不允许	允许	允许	允许
SERIALIZABLE	不允许	不允许	不允许	允许
–	–	–	–	–
Read Write	不允许	允许	允许	允许
Read only	不允许	不允许	不允许	不允许

6.Oracle的自治事务

Oracle 的自治事务(AUTONOMOUS TRANSACTION)简称 AT，自治事务在程序的开
发过程中是个很有用的功能， 它是有主事务调用，但是又独立于主事务。即，内部事务（代码块）的事务提交和回滚不影响主事务；

可以定义自治事务的对象：PLSQL 程序块、程序包函数和存储过程、SQL Object 的方法、数据库触发器

CREATE OR REPLACE PACKAGE  transaction_test_pkg  IS
       PROCEDURE sub_transaction;
       PROCEDURE main_transaction;
END;

CREATE OR REPLACE PACKAGE BODY transaction_test_pkg IS
  PROCEDURE sub_transaction IS
    PRAGMA AUTONOMOUS_TRANSACTION; --声明自治事务
  BEGIN
    --DML 语句
    UPDATE EMPLOYEES SET LAST_NAME = 'INNER' WHERE EMPLOYEE_ID = 101;   -- 防止死锁
    COMMIT;  -- 必须COMMIT,否则报错
  END sub_transaction;
  PROCEDURE main_transaction IS
  BEGIN
    --DML 语句
    UPDATE EMPLOYEES SET LAST_NAME = 'OUTER' WHERE EMPLOYEE_ID = 100; 
    sub_transaction;
    ROLLBACK;
  END main_transaction;
END transaction_test_pkg;


--执行
BEGIN
  transaction_test_pkg.main_transaction;
END;


SELECT LAST_NAME   FROM EMPLOYEES WHERE EMPLOYEE_ID IN (100,101);

--结果：INNER写入到数据库

附：Oracle中DATE与MySQL时间类型的区别

MySQL

类型	格式	范围	备注
datetime	YYYY-MM-DD HH:MM:SS	1000-01-01 00:00:00 ~ 9999-12-31 23:59:59	-
timestamp	YYYY-MM-DD HH:MM:SS	1970-01-01 00:00:01 ~ 2038	默认情况下，在 insert, update 数据时，timestamp 列会自动以当前时间
date	YYYY-MM-DD	1000-01-01~ 9999-12-31	-
year	YYYY	1901 ~ 2155	-

ORACLE

类型	格式	范围	备注
timestamp	YYYY-MM-DD HH:MM:SS	1970-01-01 00:00:01 ~ 2038	不会自动写入系统时间
date	YYYY-MM-DD	1000-01-01~ 9999-12-31	不指定日期直接写入，则为服务器时间

TIMESTAMP和DATE可实现隐式转换

如果DATE类型不保存时间，默认是00:00:00， 具体需不需要时间需要程序主动获取，返回的结果中是包含时间的；