{aa29aa} 本文大纲
除表以外,Oracle 还提供了视图、函数、存储过程、包 / 包体、序列、触发器、作业等对象,以满足数据库业务需要。对大多数应用程序而言,这些对象都只会少量用用,本文也只介绍创建和删除这些对象的基本 SQL 语法,不会深入探讨这些对象的详细语法和具体应用。
实际操作过程中,我发现 Oracle 视图有一个问题,就是不大方便注释,每次写好的注释执行之后再打开视图定义所有注释就全都没了。后来我发现把注释写到末尾就不会被清除,但这样总感觉乖乖的,而且我没见谁这么用过,我也很少用,目前还不知道有没有其它问题。创建视图示例:
- CREATE OR REPLACE VIEW v_staff2 AS SELECT t1.staff_id,
- t1.staff_name,
- t1.dept_code,
- t2.enum_name dept_name,
- t1.gender,
- t1.birthday,
- EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM t1.birthday) age,
- t1.edu_bg,
- t1.base_salary,
- t1.post_salary,
- base_salary + post_salary fixed_salary FROM demo.t_staff t1 LEFT JOIN demo.t_field_enum t2 ON t1.dept_code = t2.enum_code AND t2.field_code = 'DEPT'WHERE t1.is_disabled = 0--在职员工档案视图;
查询视图的方法与查询表的方法完全相同,就不再赘述。
- 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;
在 Oracle 中调用函数,大概有三种方式。如调用函数 fn_today,示例:
- -- 方式一
- 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;
创建一个带自制事物过程,示例:
- 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;
在 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;
我有到网上去查询 Oracle 中包的用途,结论是:它就像一个容器,可以将一组逻辑相关过程、函数、变量、常量和游标等 PLSQL 程序设计元素放到一起。包由包规范和包体两个部分组成,包规范用于定义公用的常量、变量、过程和函数,包体用户存放过程和函数的定义。
关于包的好处,网上大多是从程序模块化管理的角度来阐述的,比如说方便查询和维护过程和函数等等。我本人从未系统学过 Oracle,原本也不知道 Oracle 中包的存在,后来为写一个触发器来实现在一个批量操作(可能是新增、修改或删除)之后,同时更新另一个表中两个不同字段,更新的条件也不同,条件里需要对第一个表中的数据做聚合操作。如果我没记错的话,应该是添加可以更新,修改和删除不可以更新,如果更新语句执行之后立即提交,语法上又通不过(后来我分析这本身也是矛盾的),总之怎么写都不对。我告诉经理这条路可能走不通,然后把我的分析给他讲了一下,他也觉得我说的有道理。接下来我们开始查资料、找解决方案,经理先找到包变量的用法,并做了个测试认为可行,于是我也照模画样,把已经删掉或改过的数据外键保存到包变量中,再到触发器中去取,发现果然可行,我也这么偶然的接触到 Oracle 中的包。
创建一个包含包变量的包规范,示例:
- CREATE OR REPLACE PACKAGE pkg_staff AS -- 这里还可以用 IS 代替 AS
- staff_id NUMBER(10); -- 包变量,职员ID
- staff_name VARCHAR2(20); -- 包变量,职员名称
- END;
创建一个包含函数的包规范,示例:
- 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;
注意:必须先创建包规范,然后再创建包体。
调用包里的数据库对象与调用普通的数据库对象方式类似,唯一的区别就是要带上包名前缀(包名. 包成员名),调用 pkg_case 包里的 fn_today 函数,示例:
- SELECT pkg_case.fn_today FROM DUAL; -- res:2017-01-10
删除视图、函数、过程、包等数据库对象的语法相似,示例:
- DROP VIEW v_staff2;
- DROP FUNCTION fn_today2;
- DROP PROCEDURE sp_sync_staff90;
- DROP PACKAGE [BODY] pkg_staff;
视图、函数、过程、包等数据库对象依赖于基础表的存在而存在,当基础表进行了结构上的修改后,有可能会对这些数据库对象产生影响,有时候这些对象甚至会直接挂掉(在 PLSQL Developer 里看的话,对象名左上角会有一个红色的星号),所以需要对相关数据库对象重新进行编译。
只要拥有 ALTER ANY PROCEDURE 权限,就可以编译视图、函数、过程、包等数据库对象,编译语法大致相似,示例示例
- ALTER VIEW v_staff COMPILE; -- 编译 v_staff 视图
- ALTER FUNCTION fn_today COMPILE; -- 编译 fn_today 函数
- ALTER PROCEDURE sp_staff_status COMPILE; -- 编译 sp_staff_status 过程
- ALTER PACKAGE pkg_case COMPILE [PACKAGE|BODY]; -- 编译 pkg_case 包
有时候挂掉的过程太多,如果挨个手动编译效率太低,可以用下面这个过程实现批量编译。
- CREATE OR REPLACE PROCEDURE compile_invalid_views(
- p_owner VARCHAR2 -- 所有者(schema)名称
- )
- -- 功能:批量编译某个用户下所有状态无效的过程
- AS
- v_sql VARCHAR2(400);
- BEGIN
- FOR invalid_procs IN(
- SELECT t.object_name FROM all_objects t
- WHERE t.status='INVALID' AND t.object_type='PROCEDURE' AND t.owner=UPPER(p_owner) -- 这里不能加分号,加了会有语法错误
- ) LOOP
- v_sql:='ALTER PROCEDURE '||invalid_procs.object_name||' COMPILE';
- BEGIN
- EXECUTE IMMEDIATE v_sql;
- EXCEPTION
- WHEN OTHERS THEN
- dbms_output.put_line(SQLERRM);
- END;
- END LOOP;
- END;
如果要编译其它类型对象,只需把过程中的'PROCEDURE'换成对应类型即可。
来源: