源码PL/SQL从入门到精通第十三章子程序Part1

对于PL/SQL这种基本上面向过程的语言来讲,子程序(过程和函数)就是其核心所在了。Bro码友部落

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

在“Divide and conquer”(分而治之)的编程世界中,子程序就是程序员手中的兵器,用的越熟练越好。Bro码友部落

--代码 13.1 创建过程示例CREATE OR REPLACE PROCEDURE newdept ( p_deptno dept.deptno%TYPE, --部门编号 p_dname dept.dname%TYPE,--部门名称 p_locdept.loc%TYPE--位置)AS v_deptcount NUMBER;--保存是否存在员工编号BEGIN SELECT COUNT (*) INTO v_deptcount FROM deptWHERE 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 THENDBMS_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_deptcountNUMBER(4);--保存是否存在员工编号 e_duplication_dept EXCEPTION;BEGIN SELECT COUNT (*) INTO v_deptcount FROM deptWHERE 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;--提交事务EXCEPTIONWHEN e_duplication_dept THENROLLBACK;raise_application_error (-20002, '出现了相同的员工记录');END;SHOW ERRORS;SELECT * FROM emp;--代码 13.4 Getraisedsalary函数示例CREATE OR REPLACE FUNCTION getraisedsalary (p_empno emp.empno%TYPE) RETURN NUMBERIS v_jobemp.job%TYPE;--职位变量 v_salemp.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 '职员' THENv_salaryratio := 1.09;WHEN '销售人员' THENv_salaryratio := 1.11;WHEN '经理' THENv_salaryratio := 1.18;ELSEv_salaryratio := 1.05; END CASE; IF v_salaryratio <> 1--如果有调薪的可能 THENRETURN ROUND(v_sal * v_salaryratio,2);--返回调薪后的薪资 ELSERETURN v_sal;--否则不返回薪资 END IF;EXCEPTION WHEN NO_DATA_FOUND THENRETURN 0;--如果没找到原工记录,返回0END;--代码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, textFROM user_source WHERE NAME = 'RAISESALARY'ORDER BY line;--查看过程的编译错误SELECT line, POSITION, textFROM 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 THENRAISE_APPLICATION_ERROR(-20001,'数据库中存在相同名称的部门编号!'); END IF; INSERT INTO dept VALUES(p_deptno,p_dname,p_loc); --在过程体中使用形式参数 COMMIT;END;--实参和形参必须类型兼容,否则报错BEGIN insertdept('ABC','行政部','德克萨斯');EXCEPTION WHEN OTHERS THENDBMS_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 THENRAISE_APPLICATION_ERROR(-20001,'数据库中存在相同名称的部门编号!'); END IF; INSERT INTO dept VALUES(p_deptno,p_dname,p_loc); --在过程体中使用形式参数 COMMIT;END;--以下过程的编译错误查询不到SELECT line, POSITION, textFROM 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--定义一个员工加薪后的薪资的输出变量)ASv_sal NUMBER(10,2);--定义本地局部变量v_job VARCHAR2(10);BEGINp_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;ELSEp_raisedSalary:=v_sal*1.1;--否则赋原来的薪资值UPDATE emp SET sal=p_raisedSalary WHERE empno=p_empno;END IF;EXCEPTIONWHEN NO_DATA_FOUND THEN--异常处理语句块DBMS_OUTPUT.put_line('没有找到该员工的记录');END;SELECT * FROM emp;DECLAREv_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='销售人员' THENv_sal:=p_salary*1.18; ELSIF p_job='经理' THENv_sal:=p_salary*1.19; ELSEv_sal:=p_salary*1.1; END IF; p_salary:=v_sal;--将调整后的结果赋给输入输出参数END calcRaisedSalary;Bro码友部落
??Bro码友部落