oracle教程27 存储过程与函数（存储过程与存储函数的创建）

左手的ㄟ右手 2022-05-27 06:09 231阅读 0赞

**存储过程的创建：**

**![70][]**

**例子：**

--1：创建输入参数的存储过程
    --根据员工号删除指定的员工信息
    CREATE OR REPLACE PROCEDURE proc1
    (v_empno IN empnew.empno%TYPE)
    IS
    BEGIN
      --根据员工号删除指定的员工信息
      DELETE FROM empnew WHERE empno = v_empno;
      --判断是否删除成功
      IF SQL%NOTFOUND THEN
        -- -20000~ -20999之间 自定义异常指定错误号范围
        RAISE_APPLICATION_ERROR(-20008,'指定删除的员工不存在！');
      ELSE
        DBMS_OUTPUT.put_line('删除成功！');   
      END IF;
    END;

--2;创建带有输出参数的存储过程
    --求指定部门的平均工资和总人数
    CREATE OR REPLACE PROCEDURE proc2
    (v_deptno IN NUMBER, v_avgsal OUT NUMBER, v_cnt out NUMBER)
    IS
    BEGIN
      SELECT AVG(sal),COUNT(*)
      INTO v_avgsal, v_cnt
      FROM emp
      WHERE deptno = v_deptno;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.put_line('没有此部门！');
      WHEN OTHERS THEN
        DBMS_OUTPUT.put_line(SQLERRM);
    END;

--3：创建带有输入输出参数的存储过程
    --实现两个数的交换
    CREATE OR REPLACE PROCEDURE proc3
    (v_num1 IN OUT NUMBER, v_num2 IN OUT NUMBER)
    AS
      v_temp NUMBER := 0;
    BEGIN
      v_temp:= v_num1;
      v_num1:= v_num2;
      v_num2:= v_temp;
    END;

**存储函数的创建：**

**![70 1][]**

**例子：**

--1:创建带有输入参数的存储函数
    --根据部门编号返回该部门的总工资
    CREATE OR REPLACE FUNCTION func1
    (v_deptno IN NUMBER)
    RETURN NUMBER
    IS
           v_sumsal NUMBER;
    BEGIN
           SELECT SUM(SAL) INTO v_sumsal FROM emp WHERE deptno = v_deptno;
           RETURN v_sumsal;
    EXCEPTION
           WHEN NO_DATA_FOUND THEN
                DBMS_OUTPUT.PUT_LINE('没有此部门！');
           WHEN OTHERS THEN
                DBMS_OUTPUT.PUT_LINE(SQLERRM);
    END;

--2:创建带有输出参数的存储函数
    --根据员工号输出员工的姓名和员工的工资，并且返回员工的年收入
    CREATE OR REPLACE FUNCTION func2
    (v_empno IN emp.empno%TYPE, v_name OUT emp.ename%TYPE, v_sal OUT emp.sal%TYPE)
    RETURN NUMBER
    IS
           v_salsum NUMBER;
    BEGIN
           SELECT ename,sal,(sal+nvl(comm,0))*12
           INTO v_name,v_sal,v_salsum
           FROM emp
           WHERE empno = v_empno;
           RETURN v_salsum;
    EXCEPTION
           WHEN NO_DATA_FOUND THEN
                DBMS_OUTPUT.put_line('没有此员工！');
           WHEN OTHERS THEN
                DBMS_OUTPUT.put_line(SQLERRM);
    END;

--3：创建带有输入输出参数的存储函数
    --求两个数的平方和，并输出两个数的平方
    
    CREATE OR REPLACE FUNCTION func3
    (n1 IN OUT NUMBER, n2 IN OUT NUMBER)
    RETURN NUMBER
    AS
    BEGIN
      n1 := n1*n1;
      n2 := n2*n2;
      RETURN n1+n2;
    END;

**（注意：一般存储过程和存储函数，都不需要带有commit或者rollback（除特殊情况），因为存储过程和存储函数一般是座位子程序被调用的，事务操作应由其调用方执行）**

[70]: /images/20220527/bb82254d6dc24d508c5c600dde13c16c.png
[70 1]: /images/20220527/83a2eaf2fd7c4f0db84d9907e1b489e1.png