PL/SQL 基础---一些高阶用法
来源:互联网 发布:淘宝产品视频制作 编辑:程序博客网 时间:2024/06/08 03:33
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 形式参数数据拷贝到实际参数变量中**。
--NOCOPYCREATE 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: 20000nocopy_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;--结果是:KingUPDATE_NAMEINSERT_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') ISBEGIN 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 ---------BEGINCOMMIT;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 --------- BEGINCOMMIT;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):事务按照之前的条件重新查询时,返回的结果集中包含其他已提交事务插入的满足条件的新数据。
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
ORACLE
TIMESTAMP和DATE可实现隐式转换
如果DATE类型不保存时间,默认是00:00:00, 具体需不需要时间需要程序主动获取,返回的结果中是包含时间的;
- PL/SQL 基础---一些高阶用法
- PL/SQL 之三 游标基础用法
- SQL-PL/SQL基础
- ORACLE PL/SQL 基础
- PL/SQL基础
- PL/SQL语言基础
- PL/SQL语言基础
- PL/SQL语言基础
- ORACLE PL/SQL 基础
- PL/SQL基础小结
- PL/SQL 基础
- PL/SQL基础学习
- PL/SQL编程基础
- oracle pl/sql 基础
- PL/SQL基础
- PL/SQL --> 语言基础
- PL/SQL基础(一)
- oracle pl/sql 基础
- 常用的Meta标签写法和作用
- 如何在CSDN博客中的所贴的代码进行【代码块】显示
- QWebView 释放问题-Qt5.4.2版本
- 执行java 程序时需要引入第三方jar包的解决办法
- 微信公众号技巧之《按手印》
- PL/SQL 基础---一些高阶用法
- mysql-ubuntu14.04 设置编码格式
- Codeforces Round 383
- sql语句创建数据库和数据库表
- 找出一个二维数组中的鞍点 即该位置上的元素在该行上最大,在该列上最小。也可能没有鞍点。
- Android 清除缓存
- 子元素浮动后父级元素高度自适应高度
- HTML5之地理位置
- Tomcat学习总结(11)——Linux下的Tomcat安全优化