源码-Oracle数据库管理-第十三章-子程序和包-Part 1（定义子程序）

这部分有个意外是收获：子程序的调试。之前没玩过，体验了一把。

--第13章 子程序和包
--13.1 定义子程序
--13.1.1 什么是子程序
--代码13.1 创建过程添加新员工
CREATE OR REPLACE PROCEDURE AddNewEmp(p_empno  emp.empno%TYPE,          --员工编号
                                      p_ename  emp.ename%TYPE,          --员工名称
                                      p_job    emp.job%TYPE,            --员工职位
                                      p_sal    emp.sal%TYPE,            --员工薪资
                                      p_deptno emp.deptno%TYPE:=20)     --员工部门
AS  
BEGIN
  --判断传入的p_empno参数是否大于0,如果小于0则抛出异常
  IF p_empno<0 THEN
    RAISE_APPLICATION_ERROR(-20001, '员工编号必须大于0');
  END IF;
  --调用INSERT语句向emp表中插入新的员工
  INSERT INTO emp
    (empno, ename, job, sal, deptno)
  VALUES
    (p_empno, p_ename, p_job, p_sal, p_deptno);
END AddNewEmp;




--调用子程序代码
BEGIN
  AddNewEmp(8230,'李四友','分析员',3000,20);
END;


--子程序的调试
--略

--13.1.3 创建过程
--代码13.2 创建过程添加新部门
CREATE OR REPLACE PROCEDURE newdept (
       p_deptno   NUMBER,          --部门编号
       p_dname   VARCHAR2,         --部门名称
       p_loc     VARCHAR2          --位置
    ) 
AS                                 --变量定义部分
  ERROR_DEPTNO  EXCEPTION;         --定义一个自定义的异常
  v_deptcnt INTEGER;               --定义一个计数器整数
BEGIN
  IF p_deptno<=0 THEN              --判断传入的参数是否赋了正确的值
     RAISE ERROR_DEPTNO;
  END IF;
  --查询传入的部门编号是否在dept表中存在
  SELECT COUNT(deptno) into v_deptcnt FROM dept WHERE deptno=p_deptno;
  --如果已经存在
  IF v_deptcnt>0 THEN
  --调用UPDATE语句更新dept表
     UPDATE dept SET dname=p_dname,loc=p_loc WHERE deptno=p_deptno;
  ELSE
  --调用INSERT语句向dept表中插入一条新记录
     INSERT INTO dept VALUES(p_deptno,p_dname,p_loc);
  END IF; 
EXCEPTION                           --异常处理部分
  WHEN ERROR_DEPTNO THEN            --抛出自定义的异常
     RAISE_APPLICATION_ERROR(-20001,'请输入正确的部门编号');
END;  



BEGIN
   newdept(70,'电脑部','深圳');
   newdept(80,'运维部','东京');  
END;


--创建函数
--代码13.3 创建函数返回部门人数
CREATE OR REPLACE FUNCTION get_dept_count(p_deptno IN NUMBER) RETURN NUMBER 
 IS
  v_dept_cnt INTEGER; --定义函数本地变量
  NO_DEPTNO EXCEPTION;
BEGIN
  --查询传入的参数p_deptno是否是一个有效的deptno
  SELECT COUNT(*) INTO v_dept_cnt FROM dept WHERE deptno=p_deptno;
  --如果不存在该deptno则抛出一个异常
  IF v_dept_cnt = 0 THEN
    RAISE NO_DEPTNO;         --抛出自定义异常
  ELSE                       --否则查询该部门所具有的员工人数
    SELECT COUNT(*) INTO v_dept_cnt FROM emp WHERE deptno = p_deptno;
    RETURN v_dept_cnt;       --使用RETURN语句返回员工人数
  END IF;
EXCEPTION           
  WHEN NO_DEPTNO THEN       --捕捉自定义异常
    DBMS_OUTPUT.put_line('不存在的部门编号！');
    RETURN -1;              --输出异常消息并返回值
END;


DECLARE
  v_cnt INTEGER;
BEGIN
  v_cnt:=get_dept_count(20);
  DBMS_OUTPUT.put_line('部门30的员工人数是：'||get_dept_count(30));
  DBMS_OUTPUT.put_line('部门-20的员工人数是：'||get_dept_count(-20));
END;