.Net程序员学用Oracle系列(7):视图、函数、存储过程、包

Oracle 数据库提供了表、索引、视图、函数、存储过程、包/包体、序列、触发器、作业等数十种模式对象。实际上除表以外,其它对象都不是(关系型数据库)应用所必须的,大部分对象都是在某种特殊场景下才会被用到。

1、视图

1.1、创建、删除及调用普通视图

创建视图:普通 Oracle 视图不方便注释,写在头部的注释执行之后再打开定义就没了。后来我发现把注释写到尾部就不会被清除,但总感觉怪怪的,工作中我也是偶尔才这么用,暂时还不知道是否有其它影响。

CREATE OR REPLACE VIEW v_staff2 AS
SELECT t1.staff_id,t1.staff_name,DECODE(t1.gender,1,'男',0,'女','未知') gender,
EXTRACT(YEAR FROM SYSDATE)-EXTRACT(YEAR FROM t1.birthday) age,t2.enum_name dept_name
FROM demo.t_staff t1,demo.t_field_enum t2
WHERE t1.dept_code=t2.enum_code AND t2.field_code='DEPT' AND t1.is_disabled=0
-- 在职员工基本信息
;

删除视图:深入了解请参考《Oracle Database SQL Reference:DROP VIEW》

DROP VIEW v_staff2;

调用视图:调用视图的 CRUD 语法与调用表的基本相同,只是会多一些限制,这里就不赘述了。

1.2、高级视图介绍

如果从视图的性质和用途等角度,还可以将视图区分为:带约束的视图、可更新的视图、连接视图、只读视图、对象视图、基于 XMLType 表的视图等。如要进一步了解 Oracle 视图可参考:《Oracle Database Concepts:Overview of Views》《.Net程序员学用Oracle系列(23):视图理论、物化视图》《.Net程序员学用Oracle系列(24):数据字典、死锁》

创建只读视图:更多类型视图的创建语法参见《Oracle Database SQL Reference:CREATE VIEW》

CREATE OR REPLACE VIEW v_staff3(staff_id,staff_name,gender,age) AS
SELECT t1.staff_id,t1.staff_name,DECODE(t1.gender,1,'男',0,'女','未知'),
EXTRACT(YEAR FROM SYSDATE)-EXTRACT(YEAR FROM t1.birthday)
FROM demo.t_staff t1
WHERE t1.is_disabled=0
WITH READ ONLY;

2、函数

2.1、系统函数介绍

Oracle 数据库中内置了大量 SQL 函数,一般把这些函数称之为系统函数。在使用系统函数时,如果给定参数的数据类型和预期数据类型不同,则 Oracle 会在执行系统函数之前尝试将该参数转换为预期的数据类型。万一转换失败则可能会直接报错。如果你使用 NULL 做为参数,那么系统函数将自动返回 NULL,仅有的几个不遵守此规则的函数是:CONCATNVLREPLACEREGEXP_REPLACE

鉴于系统函数过多,这里不便展开,本人写了三篇专门介绍系统函数的文章,分别是:《.Net程序员学用Oracle系列(9):系统函数(上)》《.Net程序员学用Oracle系列(10):系统函数(中)》《.Net程序员学用Oracle系列(11):系统函数(下)》。如果你还想进一步了解 Oracle 的系统函数,请参考:《Oracle Database SQL Reference:SQL Functions》

2.2、创建、删除及调用自定义函数

创建自定义函数:深入了解请参考《Oracle Database SQL Reference:CREATE FUNCTION》

CREATE OR REPLACE FUNCTION fn_today2
RETURN DATE IS
v_today DATE;
BEGIN
v_today:=TO_DATE('2017-01-10','yyyy-mm-dd');
RETURN v_today;
END;

删除自定义函数:

DROP VIEW v_staff2;

调用自定义函数:

示例(方式一):

SELECT fn_today res FROM DUAL; -- res:2017-01-10

示例(方式二):

BEGIN
DBMS_OUTPUT.PUT_LINE('res:'||TO_CHAR(fn_today,'yyyy-mm-dd')); -- res:2017-01-10
END;

示例(方式三):

DECLARE
v_today DATE;
BEGIN
v_today:=fn_today;
DBMS_OUTPUT.PUT_LINE('res:'||TO_CHAR(fn_today,'yyyy-mm-dd')); -- res:2017-01-10
END;

3、存储过程

存储过程是存放在数据字典中的程序块,它可以在不同用户和应用程序间共享,并可实现程序的优化和重用。

3.1、创建、修改及删除存储过程

语法:

CREATE [OR REPLACE] PROCEDURE [SCHEMA.]procedureName[(param1 mode1 dataType1,...n)]
IS|AS
var1 type1;
var2 type2;
...
BEGIN
statements; /* 过程体,要执行的操作 */
END;

其中,mode1 表示参数的类型,跟方法的参数一样,有 in、out 和 in out 三种类型,dataType1 表示参数的数据类型。

创建存储过程:

创建一个带自制事物的存储过程,示例:

CREATE OR REPLACE PROCEDURE sp_sync_staff90
AS
v_sql VARCHAR2(200); -- SQL语句
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
v_sql:='TRUNCATE TABLE t_staff_young'; -- 清空 90 后职员表
EXECUTE IMMEDIATE v_sql; -- PLSQL 中不能直接执行 DDL 语句
COMMIT; INSERT INTO t_staff_young
SELECT t1.staff_id,t1.staff_name,t1.dept_code,t1.gender
FROM t_staff t1
WHERE t1.birthday>=TO_DATE('1990-01-01','yyyy-mm-dd');
COMMIT;
END;

创建一个带返回值的存储过程,示例:

CREATE OR REPLACE PROCEDURE sp_staff_status
(
p_staff_id NUMBER, -- 职员ID
p_result OUT VARCHAR2 -- 返回职员状态信息
)
AS
v_staff_status NUMBER(1);
BEGIN
IF (p_staff_id IS NULL OR p_staff_id<0) THEN
p_result:='查无此员工!';
ELSE
SELECT t.is_disabled INTO v_staff_status FROM demo.t_staff t WHERE t.staff_id=p_staff_id;
END IF; -- 如果用户没有对应权限则给出具体提示
IF v_staff_status=0 THEN
p_result:='该员工在职!';
ELSE
p_result:='该员工已离职!';
END IF;
END;

创建存储过程的时候有一个小细节需要注意一下,那就是参数名不能与程序中要访问的库表的字段重名;不过即使重名了也还是能通过编译,但等到实际调用的时候就会出问题了,因为 Oracle 会把这个参数当成库表中的字段,即便你在程序中用表别名限定了实际的字段,Oracle 也还是识别不了。如果不知道这个细节的话,是很难解决复杂存储过程中这类问题的,所以写存储过程的时候还是要遵照《.Net程序员学用Oracle系列(3):数据库编程规范》,养成一个好的编码习惯,避免因坏习惯而入坑,白白浪费时间。

删除存储过程:

DROP PROCEDURE sp_sync_staff90;

3.2、调用存储过程

在 PLSQL 的 SQL 窗口中调用无参存储过程的示例:

BEGIN
sp_sync_staff90; -- 这里必须加分号,否则有语法错误
END;

在 PLSQL 的 SQL 窗口中调用有参数存储过程的示例:

DECLARE res VARCHAR2(100);
BEGIN
sp_staff_status(2,res);
DBMS_OUTPUT.PUT_LINE('res:'||res); -- res:该员工在职!
END;

在命令窗口调用存储过程的语法:

exec[ute] procedureName[(param1,...n)]

4、包/包体

Oracle 中的包就像是一个容器,可以将一组存储过程、函数、变量、常量和游标等 PL/SQL 程序设计元素放到一起。包由包规范和包体两个部分组成,包规范用于定义公用的常量、变量、存储过程和函数,包体用于存放存储过程和函数的定义。包头可以没有包体而独立存在。

关于包的好处,网上大多是从程序模块化管理的角度来阐述的,比如说方便查询和维护存储过程和函数等等。我本人从未系统学过 Oracle,原本也不知道 Oracle 中包的存在,后来为了写一个触发器来实现在一个批量操作(忘记是新增、修改或删除)之后,同时更新另一个表中两个不同纬度字段,更新条件里还需要对第一个表中的数据做聚合。

如果我没记错的话,应该是添加可以更新,修改和删除更新不了,如果更新语句执行之后立即提交,语法上又通不过,怎么写都不行。后来我分析这个操作本身就是矛盾的,于是我把我的分析给经理讲了一下,他也觉得我说的有道理。接下来我们开始查资料、找解决方案,经理先找到包变量的用法,并做了个测试认为可行,于是我依葫芦画瓢,把已经删掉或改过的数据外键保存到包变量中,再到触发器中去取,结果还真行得通。

4.1、创建包/包体

创建一个包含包变量的包规范,示例:

CREATE OR REPLACE PACKAGE pkg_staff AS -- 这里还可以用 IS 代替 AS
staff_id NUMBER(10); -- 包变量,职员ID
staff_name VARCHAR2(20); -- 包变量,职员名称
END;

创建一个包含函数的包规范pkg_case,示例:

CREATE OR REPLACE PACKAGE pkg_case AS
FUNCTION fn_today RETURN DATE; -- 定义函数 fn_today
END;

创建包规范pkg_case的包体,示例:

CREATE OR REPLACE PACKAGE BODY pkg_case AS
FUNCTION fn_today
RETURN DATE IS
v_today DATE;
BEGIN
v_today:=TO_DATE('2017-01-10','yyyy-mm-dd');
RETURN v_today;
END;
END;

注意:必须先创建包规范,然后再创建包体。

4.2、调用包/包体

调用包里的数据库对象与调用普通的数据库对象方式相同,唯一的区别就是要带上包名前缀(包名.包成员名)。如要调用pkg_case包里的fn_today函数,示例:

SELECT pkg_case.fn_today FROM DUAL; -- res:2017-01-10

4.3、删除包/包体

DROP PACKAGE BODY pkg_case; -- 仅删除 pkg_case 包的包体
DROP PACKAGE pkg_case; -- 同时删除包和包体

5、总结

本文主要介绍了视图、函数、存储过程和包/包体 4 个比较常用的模式对象的创建、删除及调用的 PL/SQL 写法,并做了少量理论分析以便读者能更好的理解。

细心的读者可能已经发现了,上文中所有创建对象定义的语句中均包含OR REPLACE子句,该子句的作用是用来修改对象定义的,所以重新执行上文中创建对象的语句,就相当于在修改对象定义。事实上创建对象定义的语句中可以不要OR REPLACE子句,那该语句就只能创建对象而不能修改对象了。

当模式对象所引用的基对象发生变化时,就可能会导致该对象编译无效,如果该对象此时被调用,那么 Oracle 数据库就会在运行时隐式的重新编译它。其实,Oracle 提供了显示重新编译对象的功能,可以在对象被引用之前手动重新编译它,以防止相关的运行时编译错误和性能开销。

ALTER某张表时,表示修改表定义,但上文所介绍的四个对象不同,ALTER它们则表示重新编译它们。示例:

ALTER VIEW demo.v_staff COMPILE;              -- 编译 demo.v_staff 视图
ALTER FUNCTION demo.fn_now COMPILE; -- 编译 demo.fn_now 函数
ALTER PROCEDURE demo.sp_staff_status COMPILE; -- 编译 demo.sp_staff_status 存储过程
ALTER PACKAGE demo.pkg_staff COMPILE; -- 编译 demo.pkg_staff 包规范和包体
ALTER PACKAGE demo.pkg_staff COMPILE PACKAGE; -- 仅编译 demo.pkg_staff 包规范
ALTER PACKAGE demo.pkg_staff COMPILE BODY; -- 仅编译 demo.pkg_staff 包体
ALTER TRIGGER demo.trg_staff_id COMPILE; -- 编译 demo.trg_staff_id 触发器

本文链接http://www.cnblogs.com/hanzongze/p/oracle-view-procedure.html

版权声明:本文为博客园博主 韩宗泽 原创,作者保留署名权!欢迎通过转载、演绎或其它传播方式来使用本文,但必须在明显位置给出作者署名和本文链接!本人初写博客,水平有限,若有不当之处,敬请批评指正,谢谢!

上一篇:程序员需要有多懒 ?- cocos2d-x 数学函数、常用宏粗整理


下一篇:IWMS后台上传文章,嵌入视频,调用优酷通用代码