1、存储过程与函数的概述
a、存储过程和存储函数的区别
- 是否使用return返回值。（即存储过程不返回值，存储函数返回值）
b、第一个存储过程与函数的程序
--创建存储过程
CREATE OR REPLACE PROCEDURE first_proc
IS
BEGIN
DBMS_OUTPUT.put_line('我的过程');
DBMS_OUTPUT.put_line('Hello Everyone!');
END;
--创建函数
CREATE OR REPLACE FUNCTION first_func
RETURN VARCHAR
IS
BEGIN
DBMS_OUTPUT.put_line('我的函数');
RETURN 'hello everyone';
END;
```
- 可以看到，当执行完代码之后，左侧的all objects中已经的Functions和Procedures中已经分别保存了存储过程FIRST_FUNC和存储函数FREST_PROC。
- 调用存储过程
BEGIN first_proc; END;
- 调用存储函数
BEGIN DBMS_OUTPUT.put_line(first_func); END;
# 2、存储过程的创建 ## a、创建存储过程的语法
CREATE [OR REPLACE] PROCEDURE procedure_name [(argument1 [{ IN | OUT | IN OUT}] type,argument2 [ { IN | OUT | IN OUT}] type,...)] （IN 为输入参数、OUT输出参数、IN | OUT为输入输出参数） { IS | AS }（即使没有声明部分，IS | AS 也不能够省略，IS或者AS选择哪一个都可以） 声明部分，类型.变量的说明 BEGIN 执行部分 EXCEPTION 可选的异常错误处理部分 END;
## b、代码示例：创建输入参数的存储过程
CREATE OR REPLACE PROCEDURE proc1 (v_empno IN emp01.empno%TYPE) IS BEGIN --根据员工号删除指定的员工信息 DECLARE FROM emp01 WHERE empno = v_empno; --判断是否删除成功 IF sql%NOTFOUND THEN -- -20000 ~ -20999之间 RAISE_APPLICATION_ERROR(-20008,'指定删除的员工不存在'); ELSE DBMS_OUTPUT。put_line('删除成功'); END; ```
c、创建带有输出参数的存储过程
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;
d、创建带有输入输出参数的存储过程
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;
3、存储函数的创建
a、创建存储函数的语法
CREATE [OR REPLACE] FUNCTION function_name
[ argument1[ { IN | OUT | IN OUT }] type,argument2[{IN | OUT | IN OUT }] type,...)]
RETURN return_type
{ IS | AS}
声明部分，类型.变量的说明
BEGIN
执行部分，函数体
EXCEPTION
可选的异常错误处理部分
END;
b、创建带有输入参数的存储函数
- 根据部门编号返回该部门的总工资：
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;
c、创建带有输出参数的存储函数
- 根据员工号输出员工的姓名和员工的工资，并且返回员工的年收入：
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;
d、创建带有输入输出参数的存储函数
- 求两个数的平方和，并输出两个数的平方
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;
4、存储过程的调用和删除
a、调用存储过程
EXEC[UTE] Procedure_name(parameter1,parameter2...)
BEGIN procedure_name(parameter1,parameter2...) END;
b、调用存储过程的不同情况
注意：本专题第2节存储过程的创建后，Oracle数据库中保存了4个存储过程。 - 调用无参数的存储过程：直接引用过程名即可。 - 调用带有输入参数的存储过程。 - 调用带有输出参数的参数过程。 - 调用带有输入输出参数的存储过程。
- sqlPlus环境：
-- 调用无参的存储过程 sql> set serveroutput on sql> exec first_proc 我的过程 Hello Everyone! PL/sql procedure successfully completed -- 调用带有输入参数的存储过程 sql> exec proc1(1234); begin proc1(1234); end; ORA-20008: 指定删除的员工不存在 ORA-06512: 在 "SCOTT.PROC1",line 9 ORA-06512: 在 line 1
- PL/sql环境：
-- 调用无参的存储过程 BEGIN first_proc; END; -- 调用带有输入参数的存储过程 BEGIN proc1(1234); END; -- 调用带有输出参数的存储过程 DECLARE v_avgsal NUMBER; v_count NUMBER; BEGIN PROC2(10,v_avgsal,v_count); DBMS_OUTPUT.put_line('平均工资:' || v_avgsal); DBMS_OUTPUT.put_line('总人数：' || v_count); END; --调用带有输入输出参数的存储过程 DECLARE v_n1 NUMBER := 5; v_n2 NUMBER := 10; BEGIN PROC3(v_n1,v_n2); DBMS_OUTPUT.put_line('N1:' || v_n1); DBMS_OUTPUT.put_line('N2:' || v_n2); END;
c、删除存储过程的语法格式
- 使用DROP PROCEDURE命令，语法如下：
DROP PROCEDURE [user.] Procedure_name
5、存储函数的调用和删除
a、调用存储函数
- PL.sql环境：
--调用无参数的函数 BEGIN dbms_output.put_line(first_func); END; -- 调用带有输入参数的函数 BEGIN dbms_output.put_line('部门的工资总额：' || func1(&no)); END; -- 调用带有输出参数的函数 DECLARE v_name emp.ename%TYPE; v_sal emp.sal%TYPE; v_salsum NUMBER; BEGIN v_salsum := func2(&no,v_name,v_sal); DBMS_OUTPUT.put_line('姓名：' || v_name); DBMS_OUTPUT.put_line('工资：' || v_sal); DBMS_OUTPUT.put_line('年收入：' || v_salsum); END; -- 调用带有输入输出参数的函数 DECLARE v_n1 NUMBER := 5; v_n2 NUMBER := 6; v_sum number; BEGIN v_sum := func3(v_n1,v_n2); DBMS_OUTPUT.put_line('n1的平方：' || v_n1); DBMS_OUTPUT.put_line('n2的平方：' || v_n2); DBMS_OUTPUT.put_line('n1和n2的平方和：' || v_sum); END;
b、删除函数过程
- 可以使用DROP FUNCTION命令，语法如下：
DROP FUNCTION [user.] Function_name
6、附加说明
CREATE OR REPLACE FUNCTION func2
(v_empno IN emp.empno%TYPE,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;
--调用 DECLARE v_totalsal NUMBER; BEGIN v_totalsal := func5; DBMS_OUTPUT.put_line(v_totalsal); END;原文链接：https://www.f2er.com/oracle/206717.html
