PL/SQL -- Oracle存储过程

红太狼 2022-05-17 01:08 206阅读 0赞

#### 一、PL/SQL是什么? ####

**1. PL/SQL概念**  
　　PL/SQL(Procedural Language/SQL)是一种过程化语言，它允许SQL的数据操纵语言和查询语句包含在块结构(block\_structured)和代码过程语言中，使其成为一个功能强大的事务处理语言。  
　　在甲骨文数据库管理方面，PL/SQL是对结构化查询语言（SQL）的过程语言扩展。PL/SQL的目的是联合数据库语言和过程语言。PL/SQL的基本单位叫做一个区段，由三个部分组成：一个声明部分，一个可运行部分和排除－构建部分。PL/SQL区段只被编译一次并且以可运行的形式储存，以降低响应时间。

> PL/SQL过程化包括有：类型定义，判断，循环，游标，异常或例外处理…  
> PL/SQL 只有 Oracle 数据库有，MySQL 目前不支持 PL/SQL 的。  
> PL/SQL是Oracle的结构化的语言，MySql或其他数据库有自己的结构化语言。

**2. PL/SQL和SQL的区别**

答：SQL是结构化查询语言（**Structured Query Language**），是用来访问关系型数据库一种通用语言，属于第四代语言（4GL），其执行特点是非过程化，即不用指明执行的具体方法和途径，而是简单地调用相应语句来直接取得结果即可。显然，这种不关注任何实现细节的语言对于开发者来说有着极大的便利。然而，有些复杂的业务流程要求相应的程序来描述，这种情况下4GL就有些无能为力了。

**为什么要用PL/SQL?**

答：SQL是第四代命令式语言，无法显示处理过程化的业务，PL/SQL的出现正是为了解决这一问题。PL/SQL是一种过程化语言，属于第三代语言，它与C、 C++、Java等语言一样关注于处理细节，可以用来实现比较复杂的业务逻辑。  
PL/SQL–Oracle对SQL标准的扩充，增加了面向过程的功能，所以可以用来编写存储过程、存储函数、触发器等等。SQL和PL/SQL不是替代关系，是弥补关系

**3. PL/SQL与SQL执行有什么不同?**  
　　SQL是单条执行的，PL/SQL是整体执行的，不能单条执行，整个PL/SQL结束用/，其中每条语句结束用 ‘；’

#### 二、Oracle存储过程 ####

**1. 存储过程概念**

存储过程（Stored Procedure）是一组为了完成特定功能的SQL 语句集，经编译后存储在数据库中。用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。在ORACLE 中，若干个有联系的过程可以组合在一起构成程序包。  
简单说，你在你的机器上写了个存储过程，这个存储过程像那些表里的数据一样被放在遥远的数据库服务器当中，但是它又是可执行的代码，其他能连到数据库服务器的用户，可以调用你写的存储过程。它的作用是隐藏细节，你写的存储过程代码可能很复杂，但是其他人调用它却很简单，不用具体知道它是如何做的，且一次能完成多个指令。

**2. PL/SQL和Oracle存储过程的区别**

存储过程：简单来说就是有名字的PL/SQL块。  
　　Oracle的存储过程是使用PL/SQL编程的，PL/SQL还用于编写存储函数、触发器等，存储过程是平台相关的，也就是说不同数据库的存储过程语法是有区别的，Oracle、SQLServer、MySql的存储过程语法大体一样，少许改动。

**3. Oracle存储过程和Oracle函数的区别**

*  调用的区别：函数可以在查询语句中直接调用，而存储过程必须单独调用；
 *  返回值的区别：Oracle函数和存储过程最大的区别就在于，函数必须带上一个return返回值，后面跟的是返回值的类型，并一般只返回一个值，而存储过程可以不带任何返回值。从一般应用上来看，如果不需要返回值或者需要多个返回值就使用存储过程，如果只用一个返回值，就使用函数，从一般程序员的习惯上来看，这样更符合思维；
 *  函数一般情况下是用来计算并返回一个计算结果，而存储过程一般是用来完成特定的数据操作（比如修改、插入数据库表或执行某些DDL语句等等。

**4.存储过程（语法结构）：**

> 存储过程的三种类型参数：  
> 输入参数（默认） in  
> 输出参数 out  
> 输入输出参数 in out  
> 　in 是参数的默认模式，这种模式就是在程序运行的时候已经具有值，在程序体中值不会改变；  
> 　out模式定义的参数只能在过程体内部赋值，表示该参数可以将某个值传递回调用他的过程；  
> 　in out 表示高参数可以向该过程中传递值，也可以将某个值传出去 。

create or replace 存储过程名(参数列表)
      as
       --定义变量
      begin
       --pl/sql
      end;

*  [Oracle存储过程----变量的介绍及使用(PL/SQL)][Oracle_----_PL_SQL]

写存储过程中 type cur is ref cursor是什么意思？  
`create or replace package '...' as type cur is ref cursor`

> 意思是“创建一个类型变量cur，它引用游标”，除了cur外，其余全是关键字。  
> type cur：定义类型变量  
> is ref cursor：相当于数据类型，不过是引用游标的数据类型  
> 这种变量通常用于存储过程和函数返回结果集时使用，因为PL/SQL不允许存储过程或函数直接返回结果集，但可以返回类型变量，于是引用游标的类型变量作为输出参数或返回值就应运而生了

`open cursor for select * from table_name;`  
open cursor打开一个游标，游标的内容为查询的结果集  
目的就是遍历table\_name这个表的每条数据。

**5.Oracle函数定义**

语法结构：         
    create or replace function 函数名(参数列表) return 类型
      is
      begin
      end;
      
    案例：         
    create or replace function f1(n1 dec,n2 dec) return dec
       is
        r dec(19,2);
       begin
        r:=n1/n2;
        return r;
       exception
        when zero_divide then
         dbms_output.put_line('除数不能为0');
         return 0;
       end;

*  [Oracle存储过程 - 存储过程执行简单的增删改查Sql][Oracle_ - _Sql]

**6. 'as’和’is’的区别**

*  在存储过程(procedure)和函数(function)中没有区别；
 *  在视图(view)中，只能用as不能用is；
 *  在游标(cursor)中，只能用is不能用as。

#### 三、Remark ####

[Oracle及PLSQL的存储过程详解][Oracle_PLSQL]

**1.基本语法结构**

CREATE OR REPLACE PROCEDURE 存储过程名字
    (
        参数1 IN NUMBER,
        参数2 IN NUMBER
    ) IS
    变量1 INTEGER :=0;
    变量2 DATE;
    BEGIN
        --执行体
    END 存储过程名字;

**详细示例：**

create or replace procedure 存储过程名
        （param1 in type，param2 out type）
    as
    变量1 类型（值范围）;
    变量2 类型（值范围）;
    begin
    	 --使用select ... into ...要保证数据库表中有该条数据，不然会报错
        select count(*) into 变量1 from 表A where列名=param1        
         if (判断条件) then
           select 列名 into 变量2 from 表A where列名=param1；
           dbms_output.put_line(‘打印信息’);
        else (判断条件) then
           dbms_output.put_line(‘打印信息’);
        else
           raise 异常名（NO_DATA_FOUND）;
        end if;
     eption
        When others then
           Rollback;
    end;

**详细示例**

--创建一个名为p_contract_purchase的存储过程
    create or replace procedure p_purchase(
      --以下写存储过程的外部参数（传入的参数）
      --格式为：参数名 in 参数类型
      V_ID       in varchar2,
      V_Money   in number,                                                  
      V_Name         in varchar2, 
      --设置一个返回值
      V_return         out number             --返回结果 0:成功；1：失败；4：查不到供应商； 
                                                       --5：添加关联失败；6：新增采购合同失败
    )
     
    --以下写内部参数
    --格式为：参数名称 参数类型
    as
    v_id           integer; 
    v_count         varchar2(100);
    v_sqlerrm         varchar2(4000);    --错误详情
     
    --存储过程开始
    begin
      --为某些变量赋初值 
      --格式为 变量名 := 值
      V_return := 1;   
      v_id:= '';
      v_count := '';
     
      --写具体的操作语句（sql）
      --if语句(以下是异常处理语句)
      if(V_Name is not null) then
        begin
          select t.id,t.count,t.bank ,t.name 
            into v_id,v_count,v_bank,v_name
            from t_supplier t where t.name=trim(V_Name) and t.subcompanyid=trim(V_ID);
        --抛异常
        exception
          when others then
            V_return := 4 ;  --找不到该供应商
            v_name := V_Name;
     
             -- 将异常原因写入存储过程日志表
            v_sqlerrm := sqlerrm;
            insert into t_log_dberr
             (errtime, errmodel, errdesc)
            values
              (sysdate,
                'PROCEDURES',
                 'p_contract_purchase_import:ret=' || V_return ||','||
            v_sqlerrm);
            commit;
        end ;
      end if;
     
     ······
     
        end ; 
        commit;
        V_return :=0 ;
       return;
       
    --存储过程结束
    end p_purchase;
    --补充：可以将错误内容打印出来，调试的时候能够看错误类型

**2.函数定义(语法结构)**

create or replace function 函数名(参数列表) return 类型
      is
      begin
      end;
            案例：         
    
    create or replace function f1(n1 dec,n2 dec) return dec
       is
        r dec(19,2);
       begin
        r:=n1/n2;
        return r;
       exception
        when zero_divide then
         dbms_output.put_line('除数不能为0');
         return 0;
       end;

**3.New SQL执行存储过程**  
例：

EXEC USER.CUNCHU_NAME
    	( :V_PRODUCT,
    	  :V_SECTION , 
    	  :V_LINE,  
    	  :P_CURSOR);

**4.Oracle异常处理**

已命名的异常:
    
       命名的系统异常	                             产生原因
    ACCESS_INTO_NULL	      			       未定义对象
    CASE_NOT_FOUND	CASE      		        中若未包含相应的 WHEN ，并且没有设置ELSE 时
    COLLECTION_IS_NULL	      			    集合元素未初始化
    CURSER_ALREADY_OPEN	                    游标已经打开
    DUP_VAL_ON_INDEX	                       唯一索引对应的列上有重复的值
    INVALID_CURSOR	                       在不合法的游标上进行操作
    INVALID_NUMBER	                       内嵌的 SQL 语句不能将字符转换为数字
    NO_DATA_FOUND	                       使用 select into 未返回行，或应用索引表未初始化的
    TOO_MANY_ROWS	                       执行 select into 时，结果集超过一行
    ZERO_DIVIDE	                           除数为 0
    SUBSCRIPT_BEYOND_COUNT	               元素下标超过嵌套表或 VARRAY 的最大值
    SUBSCRIPT_OUTSIDE_LIMIT	               使用嵌套表或 VARRAY 时，将下标指定为负数
    VALUE_ERROR	                           赋值时，变量长度不足以容纳实际数据
    LOGIN_DENIED	PL/SQL                  应用程序连接到 oracle 数据库时，提供了不正确的用户名或密码
    NOT_LOGGED_ON	PL/SQL                  应用程序在没有连接 oralce 数据库的情况下访问数据
    PROGRAM_ERROR	PL/SQL                  内部问题，可能需要重装数据字典＆ pl./SQL系统包
    ROWTYPE_MISMATCH	                    宿主游标变量与 PL/SQL 游标变量的返回类型不兼容
    SELF_IS_NULL	                        使用对象类型时，在 null 对象上调用对象方法
    STORAGE_ERROR	                        运行 PL/SQL 时，超出内存空间
    SYS_INVALID_ID	                        无效的 ROWID 字符串
    TIMEOUT_ON_RESOURCE	Oracle              在等待资源时超时

关于异常处理的例子：

create or replace procedure table_insert(val in varchar2) is
    error exception;
    begin
      if val =  'ok' 
    	  		then insert into sm_user(cuserid,user_name) values(sys_guid(),val);
    	  elseif 
    	  		val = 'nk' then  insert into sm_user(cuserid,user_name) values(sys_guid(),val);
    	  raise error;
    	  else 
    	 		 dbms_output.put_line('val' || val); 
      end if;
      commit;
      exception
      		when error then rollback;
      		dbms_output.put_line('ERRO'); 
    end;

[Oracle_----_PL_SQL]: https://blog.csdn.net/wohaqiyi/article/details/81697710
[Oracle_ - _Sql]: https://blog.csdn.net/wohaqiyi/article/details/81529997
[Oracle_PLSQL]: https://www.2cto.com/database/201803/729289.html