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

于 2016-07-21 15:04:43 发布
阅读量1.8k 收藏 1
点赞数
分类专栏： 数据库（DB） 文章标签： oracle 11g PLSQL 调试 源码 基础
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/hpdlzu80100/article/details/51982927
版权

对于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;


预见未来to50
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值