Oracle专题14之存储过程与函数
1、存储过程与函数的概述
- 存储过程和存储函数：是存储在数据库中的被命名的PLSQL块，供所有用户程序调用，完成特定功能的子程序。
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。
- 可以看到，当执行完代码之后，左侧的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; - 在存储过程或者是存储函数中，一般不需要在其中添加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;
4、存储过程的调用和删除
a、调用存储过程
- 方法一：ORACLE使用EXECUTE语句来实现对存储过程的调用：（属于SQLPlus命令）
EXEC[UTE] Procedure_name(parameter1, parameter2...) - 方法二：在PL/SQL代码中直接调用，如：
BEGIN procedure_name(parameter1, parameter2...) END;b、调用存储过程的不同情况
注意：本专题第2节存储过程的创建后，Oracle数据库中保存了4个存储过程。
10余年的四方台网站建设经验，针对设计、前端、开发、售后、文案、推广等六对一服务，响应快，48小时及时工作处理。成都全网营销的优势是能够根据用户设备显示端的尺寸不同，自动调整四方台建站的显示方式，使网站能够适用不同显示终端，在浏览器中调整网站的宽度，无论在任何一种浏览器上浏览网站，都能展现优雅布局与设计，从而大程度地提升浏览体验。成都创新互联公司从事“四方台网站设计”,“四方台网站推广”以来，每个客户项目都认真落实执行。
- 调用无参数的存储过程：直接引用过程名即可。
- 调用带有输入参数的存储过程。
- 调用带有输出参数的参数过程。
-
调用带有输入输出参数的存储过程。
- 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_name5、存储函数的调用和删除
- 注意：本专题第3节存储函数的创建后，Oracle数据库中保存了4个存储函数。
a、调用存储函数
-
存储过程和存储函数的调用都是一样，可分为：
1、调用无参数的存储函数；
2、调用带有输入参数的存储函数；
3、调用带有输出参数的存储函数；
4、调用带有输入输出参数的存储函数。 - 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_name6、附加说明
- 参数默认值的问题：
- 过程和函数都可以在声明过程或者函数参数时，使用DEFAULT关键字为输入参数指定默认值。
- 示例代码：求部门的年收入。（存储函数）
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; - 调用此存储函数：
--调用 DECLARE v_totalsal NUMBER; BEGIN v_totalsal := func5; DBMS_OUTPUT.put_line(v_totalsal); END;
新闻名称：Oracle专题14之存储过程与函数
网站地址：http://myzitong.com/article/jcsehc.html