何海洋

路上风景正好，天上太阳正晴。

博客已停止维护。

• 博客园
• 首页
• 联系
• 管理
• 标签

随笔-117 文章-0 评论-56

PL/SQL之存储过程和函数

阅读目录

• 1、创建存储过程
• 2、创建函数
• 3、删除过程与函数
• 4、库存子程序和局部子程序

回到目录

1、创建存储过程

1.1语法：
CREATE[OR REPLACE] PROCEDURE [schema.] procedure_name[(argument[{IN|OUT|IN OUT}] datatype[,...])] {IS|AS} pl/sql_body;
procedure_name为存储过程的名称，
argument是参数名，
datatype是对应参数的数据类型，
pl/sql_body是该存储过程真正进行的处理操作的PL/SQL块，
OR REPLACE是可选项，如果存在一个同名的存储过程，则先删除后创建，
关键字IS|AS是等价的，用来引出过程体。
1)存储过程的参数
创建存储过程
CREATE OR REPLACE PROCEDURE UpdateAuths(
    p_AuthsCode auths.author_code%TYPE,
    p_AuthsSalary auths.salary%TYPE)
AS
BEGIN
    UPDATE auths
        SET salary=p_AuthsSalary
        WHERE author_code=p_AuthsCode;
    COMMIT;
END UpdateAuths;
调用存储过程
DECLARE
    v_authorcode auths.author_code%TYPE:='A0001';
    v_salary auths.salary%TYPE:=350;
BEGIN
    UpdateAuths(v_authorcode,v_salary);
END;
形参和实参
p_AuthsSalary,p_AuthsCode为形式参数
v_authorcode,v_salary为实际参数
关键字代表参数的三种不同模式
IN：当调用存储过程的时候，该模式的形参接收对应实参的值，并且该形参是只读的，即不能被修改。如果在创建存储过程时没有指定参数的模式，则默认为IN。
OUT：在存储过程中，该形参被认为是只能写，即只能为其赋值。在存储过程中不能读它的值。返回时，将该形参值传给相应的实参。
IN OUT：该模式是前两种模式的合并。
2)存储过程的处理部分
    一个PL/SQL块，该块包含定义部分、可执行部分以及异常处理部分，其中可执行部分是必须有的。
CREATE OR REPLACE 过程名
(
    --参数定义部分
)    
IS
    --局部变量定义部分
BEGIN
    --可执行部分
EXCEPTION
    --异常处理部分
END 过程名;
在END后加上存储过程名是可选的，只是为了增加程序的可读性。如果加上，则必须要和前面的存储过程名相同。
1.2存储过程的参数
1)参数的数据类型
    在定义一个存储过程参数时，不能指定CHAR类型和VARCHAR2类型形参的长度，也不能指定NUMBER形参的精度和标度。这些约束有实参来传递。
CREATE OR REPLACE PROCEDURE proc_auths(
    --参数定义了类型长度，这将产生编译错误。
    p_code IN OUT VARCHAR2(6),
    P_salary OUT NUMBER(8,2)) AS
BEGIN
    ...
END proc_auths;
应修改为：
CREATE OR REPLACE PROCEDURE proc_auths(
    p_code IN OUT VARCHAR2,
    P_salary OUT NUMBER) AS
BEGIN
    SELECT salary INTO p_salary
        FROM auths
        WHERE author_code=p_code;
END proc_auths;
调用存储过程：
DECLARE
    v_code VARCHAR2(6);
    v_salary NUMBER(8,2);
BEGIN
    v_code:='A0001';
    proc_auths(v_code,v_salary);
END;
如果使用%TYPE为参数定义类型，那么该参数将具有定义在形参上而不是通过实参传递的数据长度。
CREATE OR REPLACE PROCEDURE query_salary(
    p_code IN OUT auths.author_code%TYPE,
    P_salary OUT auths.salary%TYPE) AS
BEGIN
    ...
END query_salary;
如上面的存储过程，由于auths表中的author_code字段长度为6，因此p_code的长度也为6。
2)参数的传值方式
位置表示法
    实参通过位置与形参进行联系。
名称表示法
    实参是与形参的名称进行联系。
定义一个存储过程
CREATE OR REPLACE PROCEDURE insert_auths(
    p_code auths.author_code%TYPE,
    p_name auths.name%TYPE) AS
BEGIN
    INSERT INTO auths(author_code,name) VALUES(p_code,p_name);
END insert_auths;
DECLARE
    v_code VARCHAR2(6);
    v_name VARCHAR2(12);
BEGIN
    v_code:='A0001';
    v_name:='张三';
    --使用位置表示法调用
    insert_auths(v_code,v_name);
    --使用命名表示法调用
    insert_auths(p_name-->v_name,p_code-->v_code);
END;
两种表示法可以混合使用。但是，当调用存储过程中出现了第一个命名表示法的参数时，后面的参数也必须使用命名表示法传值。
3)参数的缺省值
类似于变量的声明，一个过程或函数的形参可以有缺省值。如果参数有缺省值，那么在调用时就可以不用给它传值，只使用缺省值。如果给它传值，则实参的值代替缺省的值。
参数缺省值声明如下：
parameter [mode] datatype {:=|DEFAULT} initial_value
parameter是形参名称
mode是参数模式
datatype是参数的类型
initial_value用来为形参指定缺省值
使用关键字DEFAULT或":="来指定一个缺省值。
CREATE OR REPLACE PROCEDURE insert_auths(
    p_code auths.author_code%TYPE :='A0001',
    p_name auths.name%TYPE DEFAULT '张三'
) AS
BEGIN
    ...
END insert_auths;

回到目录

2、创建函数

函数和存储过程非常类型，都有三种模式的参数。他们都可以被存储在数据库中，并且在快中调用。
存储过程只能作为一个PL/SQL语句调用，而函数作为表达式的一部分调用。并且它们的定义部分、可执行部分和异常处理部分都是不同的。
2.1 创建函数的语法
CREATE [OR REPLACE] FUNCTION schema.function 
    [(argument[{IN|OUT|IN OUT}]datatype[,...])]
RETURN return_datatype {IS|AS}
PL/SQL_body;
function是函数名
argument是参数名
datatype是参数的类型
return_datatype是函数返回值的类型
PL/SQL_body是函数的处理部分
参数列表时可选的。在没有参数的情况下,函数的定义与调用都没有圆括号。但返回值类型是必需的，因为函数是作为表达式的一部分调用，必须返回一个值。
CREATE OR REPLACE FUNCTION SalaryStat(
    p_Sex auths.sex%TYPE)
RETURN BOOLEAN IS
    v_MaxAuthors NUMBER;
    v_ReturnValue BOOLEAN;
BEGIN
    SELECT COUNT(author_code)
        INTO v_MaxAuthors
        FROM auths;
    IF v_MaxAuthors> 10 THEN
        v_ReturnValue:=TRUE;
    ELSE
        v_ReturnValue:=FALSE;
    END IF;
    RETURN v_ReturnValue;
END SalaryStat;
DECLARE CURSOR c_Auths IS
    SELECT distinet sex
        FROM auths;
BEGIN
    FOR v_AuthsRecord IN c_Auths LOOP
        IF SalaryStat(v_AuthsRecord.sex) THEN
            UPDATE auths
                SET salary = salary-50
                WHERE sex=v_AuthsRecord.sex;
        END IF;
    END LOOP;
END;
2.2函数的返回值
    在函数内，是通过RETURN语句来返回值的。
    RETURN expression;
expression是准备返回的值。如果expression的类型和函数头中指定的类型不符，expression类型会自动转换。RETURN语句被执行后，控制权立刻返回给调用环境。
在函数体重可以有多条RETURN语句，但还只能有一条被执行。在函数结束的时候，如果没有执行RETURN语句会产生错误。
CREATE OR REPLACE FUNCTION SalaryStat(
    p_Sex auths.sex%TYPE)
RETURN VARCHAR2 IS
    v_MaxAuthors NUMBER;
BEGIN
    SELECT COUNT(author_code)
        INTO v_MaxAuthors
        FROM auths;
    IF v_MaxAuthors> 10 THEN
        RETURN v_ReturnValue;
    ELSE
        RETURN v_ReturnValue;
    END IF;
END SalaryStat;
RETURN也可以用在存储过程中。在这种情况下，它没有参数。当执行了不带参数的RETURN语句后，立刻将控制权返回到调用环境，并将OUT和IN OUT模式的形参的当前值传给实参，然后继续执行调用存储过程后的语句。
函数和存储过程的相同点：
都可以通过OUT模式的参数返回一个活多个值。
代码都有定义部分、可执行部分和异常部分。
都可以使用缺省值。
都可以用位置表示法和命名表示法调用。
一般情况下，如果只有一个返回值，使用函数；如果有多个返回值则使用存储过程。

回到目录

3、删除过程与函数

删除过程
DROP PROCEDURE procedure_name;
删除函数
DROP FUNCTION function_name;

回到目录

4、库存子程序和局部子程序

前面所讲的子程序都是存储在数据库中的子程序，即库存子程序。这些子程序都是有CREATE命令创建的，并可在其它的PL/SQL快中调用。他们在创建时要要进行编译，并将编译后的代码存储在数据库中。当子程序被调用时，编译后的代码从数据库中读出并执行。
    一个子程序也可以在块的定义部分创建，这样的子程序被叫作局部子程序。
DECLARE 
    CURSOR c_AllAuthors IS 
        SELECT name,sex 
        FROM auths;
    v_FormattedName VARCHAR2(60);
    /*函数将返回带性别的作家名，性别用括号括起放在名字的后面*/
    FUNCTION FormatName(p_Name IN VARCHAR2,p_Sex IN NUMBER) RETURN VARCHAR2 IS 
        v_Sex VARCHAR2(16);
    BEGIN 
        IF p_Sex=1 THEN
            v_Sex:='男';
        ELSE
            v_Sex:='女';
        END IF;
        RETURN p_Name ||'('||v_Sex||')';
    END FormatName;
--块的执行部分开始
BEGIN
    FOR v_AuthsRecord IN c_AllAuthors LOOP
        v_FormattedName:=FormatName(v_AuthsRecord.name,v_AuthsRecord.sex);
        DBMS_OUTPUT.PUT_LINE(v_FormattedName);--显示结果
    END LOOP;
END;
上面的示例，在无名块的定义部分穿件了FormatName函数。这个函数只在创建它的块中可用，它的作用域从创建它开始到块结束，其他的块不能调用它。
局部子程序只能在定义部分的最后被创建，如将FormatName函数移到c_AllAuthors的前面，会出现编译错误。

作者：何海洋

出处：http://hehaiyang.cnblogs.com/

本博客内容主要以学习、研究和分享为主，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文链接，否则保留追究法律责任的权利。

分类: 数据库

标签: PL/SQL, Oracle, SQL, 函数, 存储过程

好文要顶 关注我 收藏该文

何海洋
关注 - 462
粉丝 - 78

+加关注

« 上一篇：Oracle左连接、右连接、全外连接以及（+）号用法
» 下一篇：PL/SQL之包

posted @ 2015-08-21 11:53 何海洋 阅读(6207) 评论(1) 编辑 收藏

评论列表

#1楼 2017-06-11 11:21 幸玖奈

创建函数那个过程我运行出来为什么提示名称已由现有对象使用呢？

支持(0)反对(0)

刷新评论刷新页面返回顶部

https://www.cnblogs.com/hehaiyang/p/4747461.html

PL/SQL过程和函数以及参数

2018年03月05日 15:04:26 LLY19960418 阅读数：367

PL/SQL过程和函数

PL/SQL中过程和函数设计的目的就是为了将代码模块化，有效的管理代码。

我写这篇文章的目的是为了在我阅读《Oracle.PL.SQL程序设计》一书是做些笔记，并分享给其他同学。

过程

我们可以把过程理解为一个或者多个动作的集合，我们可以像是调用PL/SQL中可执行语句一样来调用过程。下面来介绍一下过程的格式。

procedure [scheam.]name[(parameter)]  --scheam 可选 默认为当前用户名，如果当前用户具有权限，也可以为其他模式创建过程。
  [AUTHID DEFINER|CURRENT_USER] --AUTHID 可选 设置了当前过程的运行权限，定义者|当前用户
IS
  [declaration statement]
BEGIN
  executable statement
[EXCEPTION
  EXCEPTION HANDLERS
]
END [NAME];

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12

下面我写了一个批量设置员工工资的过程

CREATE OR REPLACE PROCEDURE EDIT_SALARY(in_year in NUMBER,per in BINARY_DOUBLE)
IS
CURSOR  ALL_EMP IS SELECT hiredate,sal,empno FROM EMP where GET_YEAR(EMP.HIREDATE) < in_year;
BEGIN
        for cur_emp in ALL_EMP
          LOOP
            UPDATE EMP set sal =  cur_emp.sal *(1+ per) where empno = cur_emp.empno;
          end LOOP;
  end EDIT_SALARY;

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10

首先我为这个存储过程设置了两个参数，in_year 的含义为入职年份早于此年份则可以涨工资，per 的含义为涨工资的比率是多少。
之后我声明了一个游标，取出了emp表附和可以涨工资年份的员工的信息。GET_YEAR(EMP.HIREDATE) 是一个函数，我将在下面介绍函数时讲解。
最后在执行单元中遍历游标，设置每个员工的新工资。

我们调用一个过程时，当这个过程没有参数可以这样写： “procedure_name；”不需要加上（）。
在存储过程的结尾 end [name]; name 虽然是可选的，但是可以提升代码的可读性。

函数

在调用函数时更像是一种类似调用表达式的语句，他只能作为可执行语句的一部分，函数必须有RETURN的设置，提供了返回参数的功能。当然，我们也可以设置更多的RETUEN以满足我们函数的需求。
下面介绍一下函数的结构

FUNCTION [SCHEAM.]NAME[(PARAMETERS)]
      RETURN return_datatype     --设置了返回值类型。
  [AUTHID DEFINER | CURRENT_USER]
  [DETERMINISTIC]        --保留函数返回值，查询优化器可以决定直接使用结果还是重新执行
  [PARALLEL_ENABLE..]   --当函数在select语句中，可以并发执行
  [PIPELINED] --结果通过pipe row 多次返回
  [RESULT_CACHE ...] --将输入值和返回值都保存在缓存中
IS
  [declaration statement]
BEGIN
  executable statement
[EXCEPTION
  EXCEPTION HANDLERS
]
END [NAME];

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16

在上一个过程中，我们用到了一个函数，在这里我们来一起看一下

create or replace function get_year(in_date IN DATE)
RETURN CHAR
IS
       return_year char(4,char);
BEGIN
   return_year:=substr(to_char(to_date(in_date),'yyyy-MM-dd'),0,4);
   RETURN return_year;
END get_year;

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8

首先我们创建一个函数，并且有一个输入参数，类型为日期类型，返回值是一个char类型。在执行单元首先我们设置了日期的格式，并将它设置为字符串，然后使用substr截取年份部分并返回。

参数

不论过程还是函数，都需要参数作为由外部传递数据的载体，参数的定义和声明变量十分相似，但是不同的是参数具有传递性，而且参数是不受声明限制的。

DECLEARE
    my_variable  VARCHAR2(10);

• 1

• 2

  function my_function(paramater IN VARCHAR) RETURN VARCHAR

• 1

这里可以看出，参数的定义不可以对其进行约束（将varchar后对长度的约束去掉）。

在PL/SQL中参数一共有三种模式
- IN 只可以被读取
- OUT 只可以被写入
- IN OUT 即可以被读取也可以被写入。

IN 参数模式

IN参数模式是只读的，可以把它理解为一个常量，它只是将数据传递给过程内部，并不负责将数据传递出去，并且IN模式是参数的缺省模式，当一个参数没有指定参数模式时，我们默认它就是IN模式的。

OUT 参数模式

OUT参数模式和IN模式相反，你可以通过这个参数模式将数据传递给调用者。在程序内部，out参数更像是一个没有被初始化的变量，我们只能写入一些数据，但是不能读出一些什么。在使用OUT参数模式要注意，我们不能把out参数赋值给其他甚至是他自己；我们不能为out参数提供默认值，只能在程序内部设置它的值；out参数的实参必须是一个变量，不能是常量，表达式等。我们为OUT参数赋值时，其实都是在对一个PL/SQL程序内裤创建的副本进行操作，当程序返回到调用者部分时，才会将副本中的值返回给真正的OUT参数。

IN OUT 参数模式

IN OUT 即可以被读取也可以被写入,同样的，他也不能被设置默认值，而且它的实参也必须是一个变量，除此之外就没什么要求了。

形参和实参的关联

关联方式有两种，其一时位置关联，其二是命令关联。

function get_year(in_date IN DATE , in_varchar varchar2)
-----------------------------------------------------------------
get_yaer(date，‘asdasd’);

• 1
• 2
• 3

上面的方式就是依靠参数的位置来关联，date指向第一个参数in_date，像是这样的。

function get_year(in_date IN DATE , in_varchar varchar2)
-----------------------------------------------------------------
get_yaer(in_date =>> date ，in_varchar =>> ‘asdasd’);

• 1
• 2
• 3

这种方式就是命令表示法，依靠 =>>指明关联关系。

这两种关联方法可以混用，在一次调用中可以用两种不同的关联方法指明参数，但是值得注意的是，一旦使用了命令关联，则后面的参数就不能使用位置关联表示了。

NOCOPY

之前我们提到过，OUT,INOUT参数模式，我们在程序中为这类参数赋值时，实际上是在对PL/SQL程序生成的参数的副本进行操作，但当我们操作一些集合等体积较大的数据时会影响我们的效率，所以可以使用NOCOPY来该县这种情况。

IN参数的缺省值

上面提到的OUT,INOUT参数模式 不能由缺省值，所以这里显示一下IN参数的缺省值

function get_year(in_varchar varchar2 ：= ‘asdad’)

https://blog.csdn.net/LLY19960418/article/details/79445995

PL/SQL 默认参数问题

2018年05月07日 16:30:57 weixin_39730950 阅读数：50

PL/SQL支持默认参数的比如我们创建一个存储过程,拥有一个默认为True的参数：

1. CREATE OR REPLACE PROCEDURE Procedure_Name (

2. Var IN BOOLEAN DEFAULT TRUE)

   但是在执行时遇到了问题如果：

   exec Procedure_Name()— Var 的值是TRUE

   exec Procedure_Name(null)— Var 的值也是TRUE？ 答案是否定的 Var的值就是null 所以在执行时就要注意了

SQL SERVER存储过程，参数默认值设置

https://blog.csdn.net/weixin_39730950/article/details/80227481

PLSQL程序设计语言中procedure(存储过程)的3种参数模式分析

2018年06月01日 14:04:42 Mr_guoyu 阅读数：220 标签： PLSQLORACLEPROCEDURE 更多

个人分类： PLSQLOracle

1、IN模式

IN模式是参数的默认模式，这种模式就是在程序运行的时候已经具有值，在程序体中值不会改变。

错误例子：

1  create or replace procedure in_proc (
 2         p_a in number , -- 形参， 这里的值是调用处传递的实参
 3         p_b in number 
 4         )as
 5 begin 
 6     p_a := 10 ;     --实参被传递进来以后，在这个程序体中值就不可能被改变了
 7     p_b := 20 ;
 8     DBMS_OUTPUT.PUT_LINE(p_a) ;
 9     DBMS_OUTPUT.PUT_LINE(p_b) ;
10 end ;

错误日志:

1 LINE/COL ERROR
2 -------- -------------------------------------------
3 6/5      PL/SQL: Statement ignored
4 6/5      PLS-00363: 表达式 'P_A' 不能用作赋值目标
5 7/5      PL/SQL: Statement ignored
6 7/5      PLS-00363: 表达式 'P_B' 不能用作赋值目标

注意：红色区域是错误的代码，值传递到程序体中值就不会改变了。

2、OUT模式

out模式定义的参数只能在过程体内部赋值，表示该参数可以将某个值传递到存储过程的调用处。

错误例子：

1 create or replace procedure out_proc (
2         p_a out number ,    --使用OUT模式 
3         p_b out number 
4         ) as 
5 begin
6         DBMS_OUTPUT.PUT_LINE('p_a : ' || p_a) ; --输出参数值
7         DBMS_OUTPUT.PUT_LINE('p_b : ' || p_b) ;
8 end ;

目前out_proc过程体内并没有对参数进行赋值，编写一个PLSQL块，进行验证该过程。

1 declare
2     v_a number ;    --定义变量
3     v_b number ;
4 begin
5     v_a := 10 ; --为变量赋值
6     v_b := 20 ;
7     out_proc(v_a , v_b) ;   --调用out_proc过程
8 end ;

可以发现此时根本没有把参数传递的值打印出来，这样就可以验证使用OUT模式不可以传值的问题。

程序修改：

1 create or replace procedure out_proc (
 2         p_a out number ,    --使用OUT模式 
 3         p_b out number 
 4         ) as 
 5 begin
 6         DBMS_OUTPUT.PUT_LINE('p_a : ' || p_a) ; -- OUT模式修饰的参数是不会接收从外部过程调用处传递进来的值
 7         DBMS_OUTPUT.PUT_LINE('p_b : ' || p_b) ;
 8         p_a := 100 ;    -- 在过程体内为参数赋值
 9         p_b := 200 ;
10 end ;

编写PLSQL块验证：

1 declare
 2 v_a number ; --定义变量
 3 v_b number ;
 4 begin
 5 v_a := 10 ;
 6 v_b := 20 ;
 7 out_proc(v_a , v_b) ; --调用out_proc过程
 8 DBMS_OUTPUT.PUT_LINE(v_a) ;
 9 DBMS_OUTPUT.PUT_LINE(v_b) ;
10 end ;

这次值被成功输出了。这就验证了前面提出的问题（out模式定义的参数只能在过程体内部赋值，表示该参数可以将某个值传递到存储过程的调用处）。

简单来说就是OUT不可以接收从该过程的调用处传递进来的值，只能在过程体内部对参数进行赋值，而后才能把过程体内部的值传递到该过程的被调用处。

3、IN OUT模式

IN OUT 通俗来说就表示既可以向过程体外传递参数也可以从过程体内传出数值 。

例子：

1 create or replace procedure inout_proc (
 2         p_a     in out      number ,    -- 定义形参
 3         p_b     in out      number 
 4         )as 
 5 begin
 6     DBMS_OUTPUT.PUT_LINE('传递到过程体内的 p_a : ' || p_a ) ;  --打印输出过程调用出传递进来的实参
 7     DBMS_OUTPUT.PUT_LINE('传递到过程体内的 p_b : ' || p_b ) ;
 8     p_a := 100 ;    -- 在过程体内为参数赋值
 9     p_b := 200 ;
10 end ;

编写PLSQL块验该过程

1 declare
 2     v_a number ;    -- 定义变量
 3     v_b number ;
 4 begin
 5     v_a := 10 ; --为变量赋值
 6     v_b := 20 ;
 7     inout_proc(v_a , v_b) ; --调用inout_proc 传递实参进去
 8     DBMS_OUTPUT.PUT_LINE('传递到过程体内的 v_a : ' || v_a ) ;   -- 输出在过程体内被修改的值
 9     DBMS_OUTPUT.PUT_LINE('传递到过程体内的 v_b : ' || v_b ) ;
10 end ;

执行结果：

1 传递到过程体内的 v_a : 10
2 传递到过程体内的 v_b : 20
3 传递到过程体内的 v_a : 100
4 传递到过程体内的 v_b : 200

IN OUT简单来说就是过程调用处传递的实参，在过程体内会被接收到。并且在过程体内为形参赋的值也会被传递到过程调用处。

https://blog.csdn.net/yu_ge_ge/article/details/80536818