Oracle笔记（九）：存储过程【多实例】

桃扇骨 2022-03-25 14:26 770阅读 0赞

**目录**

一、概述

二、创建

三、调用方式

实例：嵌套存储过程(Nested Procedure)

实例：独立存储过程(Standalone Procedure)

包存储过程 (Package Procedure)

四、存储过程操作表

实例：Update表

实例：带参数搜索指定数据

## 一、概述 ##

简单来说，存储过程就是多条SQL语句的集合。可视为批文件，但其作用不仅限于批处理。在数据转换/ 查询报表时会经常使用存储过程，它的作用是SQL语句不可替代的。

## 二、创建 ##

**公式：**

CREATE [OR REPLACE] PROCEDURE [Schema.] procedure_name
        [parameter_name [IN] datatype [{:=DEFAULT} expression ]]
        {IS | AS}
      BODY;

**一个超简单的存储过程：**

CREATE PROCEDURE HELLO
      AS
      BEGIN
      dbms_output.put_line('Hello this is a Simple Procedure');
    END;
      
    BEGIN
      HELLO;
    END;
    /

## 三、调用方式 ##

*  像下面一个例子中，调用在BEGIN...AND...之间
 *  execute procedure\_name;【Command命令行】
 *  exec procedure\_name;     【简写版】

### **实例：嵌套存储过程(Nested Procedure)** ###

SET SERVEROUTPUT ON;
    DECLARE  
      ming   varchar2(20) := 'Leonardo';  --赋初值
      xing   varchar2(20) := 'Scb';  
          
      -- 定义嵌套存储过程  
      PROCEDURE print(  
        first_name    varchar2, -- 注意，此处参数不能指定长度滴 
        last_name     varchar2  
      )  
      IS
        -- 在此处申明变量
        full_name varchar2(40);
      BEGIN
        full_name :=  last_name || '_' || first_name;
        dbms_output.putline('My name is ' || full_name);  
      END print;  
      
    BEGIN  
      -- 调用存储过程方式一  
      print(ming, xing);  
        
      -- 调用.......方式二  
      print(first_name => ming, last_name => xing);  
    END;
    /

**输出结果为：**

**My name is Scb\_Leonardo  
My name is Scb\_Leonardo  
PL/SQL procedure successfully completed**

### 实例：独立存储过程(Standalone Procedure) ###

存储过程还可以定义在模式(schema)中，我们把它叫做独立存储过程。

/**
       定义独立存储过程
      */
    CREATE OR REPLACE PROCEDURE concatenate_name (
      first_name         varchar2,              -- 默认是输入参数
      last_name  IN      varchar2 := 'unknown', -- 输入参数同时定义了默认值
      full_name  IN OUT  varchar2,              -- 输入输出参数
      error_code OUT     number                 --输出参数
    )
    IS
    BEGIN
      -- 检查输入参数
      IF first_name IS NULL OR last_name IS NULL THEN 
        full_name := '未知'; -- 给输出参数赋值
        error_code := -1;
        return;              -- 返回
      END IF;
     
      full_name := last_name || '1' || first_name;
    END;
    / 
    
    DECLARE
      ming    varchar2(20) := 'Scb';
      xing    varchar2(20) := 'Leo';
      xing_ming  varchar2(40);
      error_code number(1);
     
    BEGIN
      -- 调用存储过程
      concatenate_name(ming, xing, xing_ming, error_code);
      IF error_code < 0 THEN 
        return;
      END IF;
      
      DBMS_OUTPUT.PUT_LINE(xing_ming);
    END;
    /

**输出结果为：**

**Leo1Scb  
PL/SQL procedure successfully completed**

### 包存储过程 (Package Procedure) ###

存储过程还可以定义在包(package)中，我们把它叫做包存储过程。

/**
       包存储过程
      */
    
    -- 声明包
    CREATE OR REPLACE PACKAGE util AS
      PROCEDURE print(first_name varchar2, last_name varchar2);
    END util;
    /
    -- 定义包
    CREATE PACKAGE BODY util AS
      --定义包存储过程
      PROCEDURE print(  
        first_name    varchar2,  
        last_name     varchar2  
      )  
      IS  
      BEGIN  
        DBMS_OUTPUT.PUT_LINE('My name is ' || last_name || '2' || first_name);  
      END print;
    END util;
    / 
    -- 测试包存储过程
    DECLARE  
      ming    varchar2(20) := 'Leonardo';
      xing    varchar2(20) := 'Scb';
     
    BEGIN  
      -- 调用包存储过程  
      util.print(ming, xing); 
    END;
    /

**输出结果为：**

**My name is Scb2Leonardo**

**PL/SQL procedure successfully completed**

本文部分代码来源于[https://blog.csdn.net/shangboerds/article/details/41702759][https_blog.csdn.net_shangboerds_article_details_41702759]   Oracle精粹

## 四、存储过程操作表 ##

### 实例：Update表 ###

假设将数据表fruits中最低价格的两种水果设置为“打折水果”。

CREATE PROCEDURE fruits_prc
        AS 
     BEGIN
       UPDATE fruits SET f_name='打折水果'
        WHERE f_id IN
        (
         SELECT f_id FROM
          (SELECT * FROM  fruits ORDER BY f_price ASC)
          WHERE ROWNUM<3
        );
     COMMIT;
     END;
    /
    
    BEGIN 
      fruits_prc;
    END;
    /

**查看结果： **

SELECT * FROM fruits ORDER BY f_name DESC;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FkaWRhczc0ODkxNDk2_size_16_color_FFFFFF_t_70][]

### 实例：带参数搜索指定数据 ###

在fruits表中搜索符合条件的数据，  
   执行存储过程时，需指定对应的参数。

CREATE PROCEDURE FRUITS_PRCC(parm_sid IN NUMBER(6))
    AS 
     cur_id fruits.f_id%type;                           --存放水果的编码
     cur_prtifo fruits%ROWTYPE;                         --存放表fruits的行记录
    
    BEGIN
              SELECT fruits.f_id INTO cur_id
                FROM fruits;
               WHERE s_id = parm_sid;                   --根据水果类型编码获取水果的编码
     IF SQL%FOUND THEN
               DBMS_OUTPUT.PUT_LINE(parm_sid||':');
               END IF;
                FOR my_prdinfo_rec IN
                  (
                   SELECT * FROM fruits WHERE CATEGORY=cur_id)
                 LOOP
                  DBMS_OUTPUT.PUT_LINE('水果名称'|| my_prdinfo_rec.f_name
                                        ||'水果价格'|| my_prdinfo_rec.f_price
                                        ||'水果数量'|| my_prdinfo_rec.QUANTITY);
                  END LOOP;
                EXCEPTION
                WHEN NO_DATA_FOUND THEN
                   DBMS_OUTPUT.PUT_LINE('没有数据');
                WHEN TOO_MANY_ROWS THEN
                   DBMS_OUTPUT.PUT_LINE('数据过多');
    END;

**SQL> EXEC FRUITS\_PRCC（106）;  
执行结果如下：**

106：  
水果名称 mango 水果价格 15.6 水果数量1

## 五、查看存储过程编译中产生的ERRORS ##

**编写存储过程遇到问题时，可查看详细报错信息**

**假设我上一个案例是报错的，【fruits\_prcc真的有问题】**

**SQL> SHOW ERRORS PROCEDURE fruits\_prcc;**  
**Errors for PROCEDURE LEO01.FRUITS\_PRCC:  
LINE/COL ERROR**  
\-------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  
1/41     PLS-00103: 出现符号 "("在需要下列之一时：   := . ) , @ % default     character  符号 ":=" 被替换为 "(" 后继续。    
9/4      PLS-00103: 出现符号 "WHERE"在需要下列之一时：   ( begin case declare end     exception exit for goto if loop mod null pragma raise return     select update while with <an identifier>     <a double-quoted delimited-identifier> <a bind variable> <<     continue close current delete fetch lock insert open rollback     savepoint set sql execute commit forall merge pipe purge

*  第一处，IN后面不应该加括号（）；
 *  第二处，把Where之前的分号去掉。

[https_blog.csdn.net_shangboerds_article_details_41702759]: https://blog.csdn.net/shangboerds/article/details/41702759
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FkaWRhczc0ODkxNDk2_size_16_color_FFFFFF_t_70]: /images/20220325/dc12bfe5b4144d8ba98e53d6f4a5b868.png