对于PL/SQL这种基本上面向过程的语言来讲，子程序（过程和函数）就是其核心所在了。

整个解决方案可通过组织、调用由过程和函数构成的模块来实现。

在“Divide and conquer”（分而治之）的编程世界中，子程序就是程序员手中的兵器，用的越熟练越好。

--代码 13.1 创建过程示例 CREATE OR REPLACE PROCEDURE newdept ( p_deptno dept.deptno%TYPE, --部门编号 p_dname dept.dname%TYPE, --部门名称 p_loc dept.loc%TYPE --位置 ) AS v_deptcount NUMBER; --保存是否存在员工编号 BEGIN SELECT COUNT (*) INTO v_deptcount FROM dept WHERE deptno = p_deptno; --查询在dept表中是否存在部门编号 IF v_deptcount > 0 --如果存在相同的员工记录 THEN --抛出异常 raise_application_error (-20002, '出现了相同的部门记录'); END IF; INSERT INTO dept(deptno, dname, loc) VALUES (p_deptno, p_dname, p_loc);--插入记录 COMMIT; --提交事务 END; SELECT * FROM dept; --代码13.2 调用过程示例 BEGIN newdept(10,'成本科','深圳'); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line('产生了错误：'||SQLERRM); END; --查询Oracle中命名块的列表 SELECT object_type 对象类型, object_name 对象名称, status 状态 FROM user_objects WHERE object_type IN ('PACKAGE', 'PACKAGE BODY', 'FUNCTION', 'PROCEDURE') ORDER BY object_type, status, object_name; --代码13.3 创建NewDept过程 CREATE OR REPLACE PROCEDURE newdept ( p_deptno IN NUMBER, --部门编号 p_dname IN VARCHAR2, --部门名称 p_loc IN VARCHAR2 --位置 ) AS v_deptcount NUMBER(4); --保存是否存在员工编号 e_duplication_dept EXCEPTION; BEGIN SELECT COUNT (*) INTO v_deptcount FROM dept WHERE deptno = p_deptno; --查询在dept表中是否存在部门编号 IF v_deptcount > 0 --如果存在相同的员工记录 THEN --抛出异常 RAISE e_duplication_dept; END IF; INSERT INTO dept(deptno, dname, loc) VALUES (p_deptno, p_dname, p_loc);--插入记录 COMMIT; --提交事务 EXCEPTION WHEN e_duplication_dept THEN ROLLBACK; raise_application_error (-20002, '出现了相同的员工记录'); END; SHOW ERRORS; SELECT * FROM emp; --代码 13.4 Getraisedsalary函数示例 CREATE OR REPLACE FUNCTION getraisedsalary (p_empno emp.empno%TYPE) RETURN NUMBER IS v_job emp.job%TYPE; --职位变量 v_sal emp.sal%TYPE; --薪资变量 v_salaryratio NUMBER (10, 2); --调薪比率 BEGIN --获取员工表中的薪资信息 SELECT job, sal INTO v_job, v_sal FROM emp WHERE empno = p_empno; CASE v_job --根据不同的职位获取调薪比率 WHEN '职员' THEN v_salaryratio := 1.09; WHEN '销售人员' THEN v_salaryratio := 1.11; WHEN '经理' THEN v_salaryratio := 1.18; ELSE v_salaryratio := 1.05; END CASE; IF v_salaryratio <> 1 --如果有调薪的可能 THEN RETURN ROUND(v_sal * v_salaryratio,2); --返回调薪后的薪资 ELSE RETURN v_sal; --否则不返回薪资 END IF; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN 0; --如果没找到原工记录，返回0 END; --代码13.5 函数调用示例 DECLARE v_raisedsal NUMBER(10,2); --定义保存调薪记录的临时文件 BEGIN --调用函数获取调薪后的记录 DBMS_OUTPUT.PUT_LINE('7369员工调薪记录：'||getraisedsalary(7369)); v_raisedsal:=getraisedsalary(7521); DBMS_OUTPUT.PUT_LINE('7521员工调薪记录：'||getraisedsalary(7521)); END; SELECT * FROM emp; --代码13.6 在过程中使用Return语句 CREATE OR REPLACE PROCEDURE RaiseSalary( p_empno emp.empno%TYPE --员工编号参数 ) AS v_job emp.job%TYPE; --局部的职位变量 v_sal emp.sal%TYPE; --局部的薪资变量 BEGIN --查询员工信息 SELECT job,sal INTO v_job,v_sal FROM emp WHERE empno=p_empno; IF v_job<>'职员' THEN --仅为职员加薪 RETURN; --如果不是职员，则退出 ELSIF v_sal>3000 THEN --如果职员薪资大于3000,则退出 RETURN; ELSE --否则更新薪资记录 UPDATE emp set sal=ROUND(sal*1.12,2) WHERE empno=p_empno; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN --异常处理 DBMS_OUTPUT.PUT_LINE('没有找到员工记录'); END; --查看过程和函数列表 SELECT object_name, created, last_ddl_time, status FROM user_objects WHERE object_type IN ('FUNCTION','PROCEDURE'); --查看过程的源代码 SELECT line, text FROM user_source WHERE NAME = 'RAISESALARY' ORDER BY line; --查看过程的编译错误 SELECT line, POSITION, text FROM user_errors WHERE NAME = 'RAISESALARY' ORDER BY SEQUENCE; --删除子程序（函数或过程） DROP FUNCTION getraisedsalary ; DROP PROCEDURE NewDept; SELECT * FROM dept; --代码13.7 Insert过程示例 CREATE OR REPLACE PROCEDURE insertdept( p_deptno NUMBER, --定义形式参数 p_dname VARCHAR2, p_loc VARCHAR2 ) AS v_count NUMBER(10); BEGIN SELECT COUNT(deptno) INTO v_count FROM dept WHERE deptno=p_deptno; IF v_count>1 THEN RAISE_APPLICATION_ERROR(-20001,'数据库中存在相同名称的部门编号！'); END IF; INSERT INTO dept VALUES(p_deptno,p_dname,p_loc); --在过程体中使用形式参数 COMMIT; END; --实参和形参必须类型兼容，否则报错 BEGIN insertdept('ABC','行政部','德克萨斯'); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line(SQLCODE||' '||SQLERRM); END; --代码13.8 使用In模式 CREATE OR REPLACE PROCEDURE insertdept( p_deptno IN NUMBER:=55, --定义形式参数，并赋初值 p_dname IN VARCHAR2, p_loc IN VARCHAR2 ) AS v_count NUMBER(10); BEGIN --p_dname:='市场策略部'; --错误，不能对IN模式参数进行赋值 SELECT COUNT(deptno) INTO v_count FROM dept WHERE deptno=p_deptno; IF v_count>1 THEN RAISE_APPLICATION_ERROR(-20001,'数据库中存在相同名称的部门编号！'); END IF; INSERT INTO dept VALUES(p_deptno,p_dname,p_loc); --在过程体中使用形式参数 COMMIT; END; --以下过程的编译错误查询不到 SELECT line, POSITION, text FROM user_errors WHERE NAME = 'insertdept' ORDER BY SEQUENCE; --调用过程时传递的参数称为实参 BEGIN insertdept(55,'勤运部','西北'); END; select * from dept; --代码 13.9 使用out模式 CREATE OR REPLACE PROCEDURE OutRaiseSalary( p_empno IN NUMBER, p_raisedSalary OUT NUMBER --定义一个员工加薪后的薪资的输出变量 ) AS v_sal NUMBER(10,2); --定义本地局部变量 v_job VARCHAR2(10); BEGIN p_raisedSalary:=0; --变量赋初值 SELECT sal,job INTO v_sal,v_job FROM emp WHERE empno=p_empno; --查询员工信息 IF v_job='职员' THEN --仅对职员加薪 p_raisedSalary:=v_sal*1.12; --对OUT模式的参数进行赋值是合法的 UPDATE emp SET sal=p_raisedSalary WHERE empno=p_empno; ELSE p_raisedSalary:=v_sal*1.1; --否则赋原来的薪资值 UPDATE emp SET sal=p_raisedSalary WHERE empno=p_empno; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN --异常处理语句块 DBMS_OUTPUT.put_line('没有找到该员工的记录'); END; SELECT * FROM emp; DECLARE v_raisedsalary NUMBER(10,2); --定义一个变量保存输出值 BEGIN v_raisedsalary:=100; --这个赋值在传入到OutRaiseSalary后会被忽略 OutRaiseSalary(5093,v_raisedsalary); --调用函数 DBMS_OUTPUT.put_line(v_raisedsalary); --显示输出参数的值 END; --代码13.10 使用In Out模式 CREATE OR REPLACE PROCEDURE calcRaisedSalary( p_job IN VARCHAR2, p_salary IN OUT NUMBER --定义输入输出参数 ) AS v_sal NUMBER(10,2); --保存调整后的薪资值 BEGIN if p_job='职员' THEN --根据不同的job进行薪资的调整 v_sal:=p_salary*1.12; ELSIF p_job='销售人员' THEN v_sal:=p_salary*1.18; ELSIF p_job='经理' THEN v_sal:=p_salary*1.19; ELSE v_sal:=p_salary*1.1; END IF; p_salary:=v_sal; --将调整后的结果赋给输入输出参数 END calcRaisedSalary;
??