1、视图
在实际操作过程中,本人发现 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
-- 在职员工档案视图
;
查询视图的方法与查询表的方法完全相同,就不再赘述。
2、函数
2.1、创建函数
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;
2.2、调用函数
在 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;
3、存储过程
3.1、创建存储过程
创建一个带自制事物的存储过程,示例:
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 也还是识别不了。如果不知道这个细节的话,是很难解决复杂存储过程中这类问题的,所以写存储过程的时候还是要遵照数据库编程规范,养成一个好的编码习惯,避免因坏习惯而入坑,白白浪费时间。
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;
4、包
我有到网上去查询 Oracle 中包的用途,结论是:它就像一个容器,可以将一组逻辑相关存储过程、函数、变量、常量和游标等 PLSQL 程序设计元素放到一起。包由包规范和包体两个部分组成,包规范用于定义公用的常量、变量、存储过程和函数,包体用户存放存储过程和函数的定义。
关于包的好处,网上大多是从程序模块化管理的角度来阐述的,比如说方便查询和维护存储过程和函数等等。我本人从未系统学过 Oracle,原本也不知道 Oracle 中包的存在,后来为写一个触发器来实现在一个批量操作(可能是新增、修改或删除)之后,同时更新另一个表中两个不同字段,更新的条件也不同,条件里需要对第一个表中的数据做聚合操作。如果我没记错的话,应该是添加可以更新,修改和删除不可以更新,如果更新语句执行之后立即提交,语法上又通不过(后来我分析这本身也是矛盾的),总之怎么写都不对。我告诉经理这条路可能走不通,然后把我的分析给他讲了一下,他也觉得我说的有道理。接下来我们开始查资料、找解决方案,经理先找到包变量的用法,并做了个测试认为可行,于是我也照模画样,把已经删掉或改过的数据外键保存到包变量中,再到触发器中去取,发现果然可行,我也这么偶然的接触到 Oracle 中的包。
4.1、创建包/包体
创建一个包含包变量的包规范,示例:
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;
注意:必须先创建包规范,然后再创建包体。
4.2、调用包/包体
调用包里的数据库对象与调用普通的数据库对象方式类似,唯一的区别就是要带上包名前缀(包名.包成员名),调用 pkg_case 包里的 fn_today 函数,示例:
SELECT pkg_case.fn_today FROM DUAL; -- res:2017-01-10
5、总结
5.1、删除数据库对象
删除视图、函数、存储过程、包等数据库对象的语法相似,示例:
DROP VIEW v_staff2;
DROP FUNCTION fn_today2;
DROP PROCEDURE sp_sync_staff90;
DROP PACKAGE [BODY] pkg_staff;