Oracle 存储过程

创建存储过程

/*
===========================================================
|        创建存储过程
============================================================
*/
CREATE OR REPLACE PROCEDURE ADD_EMPLOYEE
   (ENO    NUMBER, --输入参数，雇员编号
    NAME   VARCHAR2, --输入参数，雇员名称
    SALARY NUMBER, --输入参数，雇员薪水
    DNO    NUMBER,--输入参数，雇员部门编号
    JOB    VARCHAR2 DEFAULT 'CLERK' --输入参数，雇员工种默认'CLERK'
) IS
BEGIN
   INSERT INTO EMPLOYEE
               (EMPNO, ENAME, SAL, JOB, DEPTNO)
        VALUES
               (ENO, NAME, SALARY, JOB, DNO);
END;
/*
===========================================================
|        创建存储过程——增加异常处理
============================================================
*/
CREATE OR REPLACE PROCEDURE ADD_EMPLOYEE
   (ENO    NUMBER, --输入参数，雇员编号
    NAME   VARCHAR2, --输入参数，雇员名称
    SALARY NUMBER, --输入参数，雇员薪水
    DNO    NUMBER, --输入参数，雇员部门编号
    JOB    VARCHAR2 DEFAULT 'CLERK' --输入参数，雇员工种默认'CLERK'
) IS
BEGIN
   IF LENGTH(eno)<4 THEN
      raise_application_error(-20000,'员工编号不能小于4位数。');
   END IF;
   INSERT INTO EMPLOYEE
               (EMPNO, ENAME, SAL, JOB, DEPTNO)
        VALUES
               (ENO, NAME, SALARY, JOB, DNO);
EXCEPTION
   WHEN OTHERS THEN
   dbms_output.put_line(SQLERRM);
   RAISE;
END;

--查看源代码
select text from user_source where name='ADD_EMPLOYEE';

DROP PROCEDURE ADD_EMPLOYEE;

 

规范化存储过程

/*
===========================================================
|         按照推荐规则修改。
============================================================
*/CREATE OR REPLACE PROCEDURE add_employee1(
    eno employee.empno%type,                  --输入参数,雇员编号
    name employee.ename%type,                --输入参数,雇员名称
    salary employee.sal%type,                --输入参数,雇员薪水
    job employee.job%type DEFAULT 'CLERK',        --输入参数,雇员工种默认'CLERK'
    dno employee.deptno%type,                 --输入参数,雇员部门编号
    on_Flag OUT number,       --执行状态
    os_Msg OUT VARCHAR2       --提示信息
)
IS
   e_eno EXCEPTION;
BEGIN
   IF LENGTH(eno)<4 THEN
      RAISE e_eno;
   END IF;
   INSERT INTO employee (empno,ename,sal,job,deptno)VALUES (eno,name,salary,job, dno);
   on_Flag:=1;
   os_Msg:='添加成功';
EXCEPTION
   WHEN  e_eno THEN
      on_Flag:=-1;
      os_Msg:='员工编号不能小于4位数。';
   WHEN DUP_VAL_ON_INDEX THEN
      on_Flag:=-2;
      os_Msg:='该雇员已存在。';
   WHEN OTHERS THEN
    --  on_Flag:=-3;
     -- os_Msg:='其他错误，与管理员联系。';
      on_Flag:=SQLCODE;
      os_Msg:=SQLERRM;
END;

DECLARE
   on_Flag NUMBER;
   os_Msg VARCHAR2(100);     
BEGIN
   --按位置传递参数
   add_employee1(2112,'MARY',2000,'MANAGER',10,on_Flag,os_Msg);
   dbms_output.put_line(on_Flag||os_Msg);
END;

 

调用参数

CREATE OR REPLACE PROCEDURE P(
   A        PLS_INTEGER, -- IN by default
   B     IN PLS_INTEGER,
   C    OUT PLS_INTEGER,
   D IN OUT NUMBER
) IS
BEGIN
   dbms_output.put_line(c);
   dbms_output.put_line(d);
   C := A + 10; -- Assign value to OUT parameter
   D := 10/B; -- Assign value to IN OUT parameter
END;


DECLARE 
   aa CONSTANT PLS_INTEGER := 1; 
   bb PLS_INTEGER := 2; 
   cc PLS_INTEGER := 3; 
   dd NUMBER := 4; 
BEGIN
   p(aa,bb,cc,dd);
   DBMS_OUTPUT.PUT_LINE('After invoking procedure p:');
   DBMS_OUTPUT.PUT_LINE('aa = '||aa); 
   DBMS_OUTPUT.PUT_LINE('bb = '||bb); 
   DBMS_OUTPUT.PUT_LINE('cc = '||cc); 
   DBMS_OUTPUT.PUT_LINE('dd = '||dd);
END;

 

调用存储过程

/*
===========================================================
||        PL/SQL调用存储过程
||    ENO    NUMBER, --输入参数，雇员编号
||    NAME   VARCHAR2, --输入参数，雇员名称
||    SALARY NUMBER, --输入参数，雇员薪水
||    DNO    NUMBER, --输入参数，雇员部门编号
||    JOB    VARCHAR2 DEFAULT 'CLERK' --输入参数，雇员工种默认'CLERK'
============================================================
*/
BEGIN
   /*按位置指定*/
   add_employee(2111,'MARY',2000,10,'MANAGER');
   /*按名字指定*/
   add_employee(dno=>10,NAME=>'MARY',salary=>2000,eno=>2112, job=>'MANAGER');
   /*混合方法指定*/
   add_employee(2113,dno=>10,NAME=>'MARY',salary=>2000,job=>'MANAGER');
END;

--带默认参数
BEGIN
   /*按位置指定*/
   add_employee(3111,'MARY',2000,10);
   /*按名字指定*/
   add_employee(dno=>10,NAME=>'MARY',salary=>2000,eno=>3112);
   /*混合方法指定*/
   add_employee(3113,dno=>10,NAME=>'MARY',salary=>2000);
END;


--带异常处理
DECLARE
   EMP_20000 EXCEPTION;
   PRAGMA EXCEPTION_INIT(EMP_20000, -20000);
   EMP_NULL_ERROR EXCEPTION; --声明异常变量
   PRAGMA EXCEPTION_INIT(EMP_NULL_ERROR, -1400); --非预定义异常，前提：deptno列非空。插入空值会报错
   EMP_NO_DEPTNO EXCEPTION; --声明异常变量
   PRAGMA EXCEPTION_INIT(EMP_NO_DEPTNO, -2291); --非预定义异常，前提：deptno列建立外键约束，插入部门编号不在部门表中会报错
BEGIN
   /*异常员工编号位数小于4位*/
    add_employee(61,'MARY',2000,10,'MANAGER');
   /*异常部门不存在*/
  -- add_employee(4111,'MARY',2000,66,'MANAGER');
   /*异常部门为空*/
  -- add_employee(4111,'MARY',2000,null,'MANAGER');
   /*异常雇员编号重复*/
   --add_employee(2111,'MARY',2000,10,'MANAGER');
EXCEPTION
   WHEN EMP_20000 THEN
      DBMS_OUTPUT.PUT_LINE(SQLERRM(-20000));
   WHEN DUP_VAL_ON_INDEX THEN
      DBMS_OUTPUT.PUT_LINE('雇员编码不能重复');
   WHEN emp_null_error THEN
      DBMS_OUTPUT.PUT_LINE('部门编码不能为空');
    WHEN emp_no_deptno THEN
       DBMS_OUTPUT.PUT_LINE('不存在该部门编号');        
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('出现了其他异常错误');
END;