1、存储过程与函数的概述
a、存储过程和存储函数的区别
- 是否使用return返回值。（即存储过程不返回值，存储函数返回值）
b、第一个存储过程与函数的程序
- 用存储过程或者函数实现输出‘Hello Everyone!’。
--创建存储过程
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、创建存储过程的语法
@H_
301_28@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;
```
- 在存储过程或者是存储函数中，一般不需要在其中添加COMMIT或ROLLBACK，谁调用由谁添加COMMIT或ROLLBACK
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;
a、调用存储过程
- 方法一：ORACLE使用EXECUTE语句来实现对存储过程的调用：（属于sqlPlus命令）
EXEC[UTE] Procedure_name(parameter1,parameter2...)
BEGIN
procedure_name(parameter1,parameter2...)
END;
b、调用存储过程的不同情况
注意：本专题第2节存储过程的创建后，Oracle数据库中保存了4个存储过程。 - 调用无参数的存储过程：直接引用过程名即可。 - 调用带有输入参数的存储过程。 - 调用带有输出参数的参数过程。 - 调用带有输入输出参数的存储过程。
-- 调用无参的存储过程
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
-- 调用无参的存储过程
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 [user.] Procedure_name
- 注意：本专题第3节存储函数的创建后，Oracle数据库中保存了4个存储函数。
--调用无参数的函数
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;
- 可以使用DROP FUNCTION命令，语法如下：
DROP FUNCTION [user.] Function_name
6、附加说明
- 参数默认值的问题：
- 过程和函数都可以在声明过程或者函数参数时，使用DEFAULT关键字为输入参数指定默认值。
- 示例代码：求部门的年收入。（存储函数）
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
