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。

调用存储过程
```
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_name
```
5、存储函数的调用和删除
注意：本专题第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_name
```
6、附加说明
参数默认值的问题：
过程和函数都可以在声明过程或者函数参数时，使用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之存储过程与函数
URL网址：http://www.tsicrk.com/article/jcsehc.html

建站动态

Oracle专题14之存储过程与函数

1、存储过程与函数的概述

a、存储过程和存储函数的区别

b、第一个存储过程与函数的程序

2、存储过程的创建

a、创建存储过程的语法

b、代码示例：创建输入参数的存储过程

c、创建带有输出参数的存储过程

d、创建带有输入输出参数的存储过程

3、存储函数的创建

a、创建存储函数的语法

b、创建带有输入参数的存储函数

c、创建带有输出参数的存储函数

d、创建带有输入输出参数的存储函数

4、存储过程的调用和删除

a、调用存储过程

b、调用存储过程的不同情况

调用带有输入输出参数的存储过程。

c、删除存储过程的语法格式

5、存储函数的调用和删除

a、调用存储函数

存储过程和存储函数的调用都是一样，可分为：
1、调用无参数的存储函数；
2、调用带有输入参数的存储函数；
3、调用带有输出参数的存储函数；
4、调用带有输入输出参数的存储函数。

b、删除函数过程

6、附加说明

其他资讯

网站建设专属方案

网站定制化设计

7X24小时服务

N对管家服务

让你的专属顾问为你服务

建站动态

Oracle专题14之存储过程与函数

1、存储过程与函数的概述

a、存储过程和存储函数的区别

b、第一个存储过程与函数的程序

2、存储过程的创建

a、创建存储过程的语法

b、代码示例：创建输入参数的存储过程

c、创建带有输出参数的存储过程

d、创建带有输入输出参数的存储过程

3、存储函数的创建

a、创建存储函数的语法

b、创建带有输入参数的存储函数

c、创建带有输出参数的存储函数

d、创建带有输入输出参数的存储函数

4、存储过程的调用和删除

a、调用存储过程

b、调用存储过程的不同情况

调用带有输入输出参数的存储过程。

c、删除存储过程的语法格式

5、存储函数的调用和删除

a、调用存储函数

存储过程和存储函数的调用都是一样，可分为：1、调用无参数的存储函数；2、调用带有输入参数的存储函数；3、调用带有输出参数的存储函数；4、调用带有输入输出参数的存储函数。

b、删除函数过程

6、附加说明

其他资讯

网站建设专属方案

网站定制化设计

7X24小时服务

N对管家服务

让你的专属顾问为你服务

存储过程和存储函数的调用都是一样，可分为：
1、调用无参数的存储函数；
2、调用带有输入参数的存储函数；
3、调用带有输出参数的存储函数；
4、调用带有输入输出参数的存储函数。