Some notes of Learning Oralce.
函数
例:
创建一个通过雇员编号返回雇员名称的函数 GET_EMP_NAME。1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18CREATE OR REPLACE FUNCTION GET_EMP_NAME(P_EMPNO NUMBER DEFAULT 7788)
RETURN VARCHAR2
AS
V_ENAME VARCHAR2(10);
BEGIN
SELECT ENAME INTO V_ENAME FROM EMP WHERE EMPNO=P_EMPNO;
RETURN(V_ENAME);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('没有该编号雇员!');
RETURN (NULL);
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('有重复雇员编号!');
RETURN (NULL);
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('发生其他错误!');
RETURN (NULL);
END;
调用:1
2
3
4BEGIN
DBMS_OUTPUT.PUT_LINE('雇员7369的名称是:'|| GET_EMP_NAME(7369));
DBMS_OUTPUT.PUT_LINE('雇员7839的名称是:'|| GET_EMP_NAME(7839));
END;
删除函数格式:1
DROP FUNCTION [函数名];
包
概念
包是用来存储相关程序结构的对象,它存储于数据字典中。
包由两个分离的部分组成:包头( PACKAGE ) 和包体 ( PACKAGE BODY )。
- 包头是包的说明部分,是对外的操作接口,对应用是可见的;
- 包体是包的代码和实现部分,对应用来说是不可见的黑盒。
说明部分可以出现在包的三个不同的部分:出现在包头中的称为公有元素,出现在包体中的称为私有元素,出现在包体的过程(或函数)中的称为局部变量。
在包体中出现的过程或函数,如果需要对外公用,就必须在包头中说明,包头中的说明应该和包体中的说明一致。
包的优点:
组织过程或函数,解决重名问题,增加了安全性,方便授权,减少了多次访问过程或函数的 I/O 次数等
创建包和包体
包由包头和包体两部分组成,包的创建应该先创建包头部分,然后创建包体部分。
创建、删除和编译包的权限同创建、删除和编译存储过程的权限相同。
在 SQL*Plus 环境下,包和包体可以分别编译,也可以一起编译。
- 如果分别编译,则要先编译包头,后编译包体。
- 如果在一起编译,则包头写在前,包体在后,中间用“/”分隔。
例:
创建管理雇员信息的包 EMPLOYE ,它具有从 EMP 表获得雇员信息,修改雇员名称,修改雇员工资和写回 EMP 表的功能。1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60CREATE OR REPLACE PACKAGE EMPLOYE --包头部分
IS
PROCEDURE SHOW_DETAIL;
PROCEDURE GET_EMPLOYE(P_EMPNO NUMBER);
PROCEDURE SAVE_EMPLOYE;
PROCEDURE CHANGE_NAME(P_NEWNAME VARCHAR2);
PROCEDURE CHANGE_SAL(P_NEWSAL NUMBER);
END EMPLOYE;
/
CREATE OR REPLACE PACKAGE BODY EMPLOYE
--包体部分
IS
EMPLOYE EMP%ROWTYPE;
--变量,以下过程或函数都可以调用
-------------- 显示雇员信息 ---------------
PROCEDURE SHOW_DETAIL
AS
BEGIN
DBMS_OUTPUT.PUT_LINE(‘----- 雇员信息 -----’);
DBMS_OUTPUT.PUT_LINE('雇员编号:'||EMPLOYE.EMPNO);
DBMS_OUTPUT.PUT_LINE('雇员名称:'||EMPLOYE.ENAME);
DBMS_OUTPUT.PUT_LINE('雇员职务:'||EMPLOYE.JOB);
DBMS_OUTPUT.PUT_LINE('雇员工资:'||EMPLOYE.SAL);
DBMS_OUTPUT.PUT_LINE('部门编号:'||EMPLOYE.DEPTNO);
END SHOW_DETAIL;
------ 从EMP表取得一个雇员 ------
PROCEDURE GET_EMPLOYE(P_EMPNO NUMBER)
AS
BEGIN
SELECT * INTO EMPLOYE FROM EMP WHERE EMPNO=P_EMPNO;
DBMS_OUTPUT.PUT_LINE('获取雇员'||EMPLOYE.ENAME||'信息成功');
END GET_EMPLOYE;
------- 保存雇员到EMP表 ---------
PROCEDURE SAVE_EMPLOYE
AS
BEGIN
UPDATE EMP SET ENAME=EMPLOYE.ENAME, SAL=EMPLOYE.SAL WHERE EMPNO =
EMPLOYE.EMPNO;
DBMS_OUTPUT.PUT_LINE('雇员信息保存完成!');
END SAVE_EMPLOYE;
------------ 修改雇员名称 ------------
PROCEDURE CHANGE_NAME(P_NEWNAME VARCHAR2)
AS
BEGIN
EMPLOYE.ENAME:=P_NEWNAME;
DBMS_OUTPUT.PUT_LINE('修改名称完成!');
END CHANGE_NAME;
------------ 修改雇员工资 ------------
PROCEDURE CHANGE_SAL(P_NEWSAL NUMBER)
AS
BEGIN
EMPLOYE.SAL:=P_NEWSAL;
DBMS_OUTPUT.PUT_LINE('修改工资完成!');
END CHANGE_SAL;
END EMPLOYE;
调用:
获取雇员7788的信息:1
2SET SERVEROUTPUT ON
EXECUTE EMPLOYE.GET_EMPLOYE(7788);
显示雇员信息:1
EXECUTE EMPLOYE.SHOW_DETAIL;
修改雇员工资:1
EXECUTE EMPLOYE.CHANGE_SAL(3800);
将修改的雇员信息存入EMP表1
EXECUTE EMPLOYE.SAVE_EMPLOYE;