Oracle数据库学习：PL/SQL(详解)

一时失言乱红尘 2022-11-21 11:19 263阅读 0赞

# Oracle数据库学习：PL/SQL #

# 什么是PL/SQL #

> PL/SQL 是过程语言(Procedural Language)与结构化查询语言(SQL)结合而成的**扩展语言**;  
> **使用PL/SQL 可以编写具有很多高级功能的程序**  
> 虽然通过多个SQL语句也可以实现相同的功能，但对于应用程序开发过程中, 需要多次与数据库打交道,  
> PL/SQL可以针对于某一种功能特定的实现…(类似Java的方法) 一定程度上避免了频繁于数据库的请求 (减低服务器压力)  
> **优点：**
> 
> 1.  PL/SQL具有编程语言的特点, 它把一组SQL语句放到一个模块中执行，使其更具模块化程序的特点；
> 2.  PL/SQL可以采用过程语言的程序结构，可以在其中写 逻辑判断 循环…
> 3.  与其它编程语言类似，PL/SQL对于程序中的异常也有其响应的处理方式;
> 4.  PL/SQL块，具有更好的可移植性; 可以移植到另一个Oracle数据库中…
> 5.  减少了程序网络的交互(数据了访问), 提高程序性能;

## PL/SQL体系结构： ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1NTQyMzgw_size_16_color_FFFFFF_t_70_pic_center]

> **PL/SQL块：**  
> 由PL/SQL引擎来编译 和 执行PL/SQL块; 该引擎存在于 Oracle服务器中；  
> **PL/SQL引擎：** 执行所有过程语句，将SQL语句发送给Oracle的SQL语句执行器

## PL/SQL简介： ##

> PL/SQL 是一种块结构的语言：  
> 它把一组SQL语句放到一个模块中执行，使其更具模块化程序的特点；  
> **匿名块** 是一个未在数据库中命名的PL/SQL块, 在运行时传递到PL/SQL引擎中执行…  
> PL/SQL中可以使用： select insert update delete…事务语句以及sql函数；**增删改别忘了 COMMIT 提交事务~**  
> PL/SQL中不允许直接使用：create deop alter 但可以通过动态SQL来执行它们…  
> **一个PL/SQL语句由三个部分组成：`声明部分` `执行部分` `异常处理部分`**

## **PL/SLQ结构块：** ##

--PL/SQL块基本结构：
    DECLARE
           -- DECLARE 可选,声明部分: 
           --在此声明PL/SQL用到的变量,常量,类型,游标 以及局部的存储过程和函数;
    BEGIN
           -- BEGIN~end 必须项,执行部分:
           -- 在此编写执行的过程 及 SQL语句,即程序的主要部分;
           EXCEPTION
                    -- EXCEPTION 可选,指定出现错误时需要执行的操作
    END;
           -- end: 表示Pl/sql块的结束,别忘了分号结尾;
           
    
    -- 上面是PL/SQL块的基本结构, BEGIN~END; 是必须的 声明部分和异常处理部分并不是必须的..
    -- 是PL/SQL中的单行注释 /**/ 多行注释;

**运算符和表达式：**  
**关系运算符：**

<table> 
 <thead> 
  <tr> 
   <th>运算符</th> 
   <th>意义</th> 
   <th>运算符</th> 
   <th>意义</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>&lt;</td> 
   <td>小于</td> 
   <td>&gt;</td> 
   <td>大于</td> 
  </tr> 
  <tr> 
   <td>&lt;=</td> 
   <td>小于或等于</td> 
   <td>&gt;=</td> 
   <td>大于或等于</td> 
  </tr> 
  <tr> 
   <td>=</td> 
   <td>等于</td> 
   <td>&lt;&gt;, !=, ~=, ^=</td> 
   <td>不等于</td> 
  </tr> 
 </tbody> 
</table>

**一般预算符：**

<table> 
 <thead> 
  <tr> 
   <th>运算符</th> 
   <th>运算符</th> 
   <th>运算符</th> 
   <th>运算符</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>+加号</td> 
   <td>-减</td> 
   <td>* 乘</td> 
   <td>/除</td> 
  </tr> 
  <tr> 
   <td>:=赋值号</td> 
   <td>=&gt;关系号</td> 
   <td>. . 范围运算符</td> 
   <td>︳︳字符连接符</td> 
  </tr> 
 </tbody> 
</table>

**逻辑预算符：**

<table> 
 <thead> 
  <tr> 
   <th>运算符</th> 
   <th>意义</th> 
   <th>运算符</th> 
   <th>意义</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>IS NULL</td> 
   <td>空值</td> 
   <td>BETWEEN AND</td> 
   <td>介于两者之间</td> 
  </tr> 
  <tr> 
   <td>iN</td> 
   <td>在一列值之间</td> 
   <td>AND</td> 
   <td>逻辑与</td> 
  </tr> 
  <tr> 
   <td>OR</td> 
   <td>逻辑或</td> 
   <td>NOT</td> 
   <td>取反, 如 IS NOT NULL 或 NOT IN</td> 
  </tr> 
 </tbody> 
</table>

## DECLARE变量的声明： ##

**语法：**

/** 使用DECLARE 关键字,用于定义变量或者常量: PL/SQL块的可执行部分引用变量和常量前,必须先在DECLARE 对其进行声明; */   
    
    DECLARE 
            variable_name [CONSTANT] type[(size)] [NOT NULL] [:=value];  
    BEGIN
         EXCEPTION
    END;
    -- variable_name:变量名称
    -- [CONSTANT] :可选表示是否为常量
    -- type :表示变量的数据类型
    -- [(size)] :可选,数据类型的长度
    -- [NOT NULL] :可选,该变量是否可以为空
    -- [:=value] :可选,使用 := 给对于对象赋值 初始值;

**PL/SQL变量名规范**

> **变量命名规则**  
> 变量名首字母必须是英文字母  
> 其后可以是字母、数字或者特殊字符$、\#和下划线  
> 变量名长度不超过30个字符  
> 变量名中不能有空格 不能是SQL保留字 不能用 - (减号)  
> **唯一比较好的就是不区分大小写了吧…**  
> ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1NTQyMzgw_size_16_color_FFFFFF_t_70_pic_center 1]

## 实例Demo ##

-- scott用户下emp举例
    select * from emp;
    
    --PL/SQL 根据输入id,获得当前用户名 JOB
    -- 方式一:PL/SQL块 注意分号~
    declare
           --声明变量 id ename job, 每一个变量之间 ; 分号分隔...
           --id 通过 :=值 进行赋默认值,并通过 &xxx由Oracle工具输入参数;
           v_id number :=&myid;  --还可以把: &myid手动输入换位固定的 :=7934; 固定的id号;
           v_ename varchar2(50);
           v_JOB varchar2(50);
    begin
          select ename , job 
                 into v_ename,v_JOB 
                 from emp where empno = v_id;
          dbms_output.put_line('用户名'||v_ename||'职位: '||v_JOB);
    end;
    -- 还可以通过: select into 给变量进行赋值 
    -- select 列1,列2 into 变量1,变量2 form 表 形式进行下标自动映射赋值..因此列类型和变量类型 顺序都要尽量对应
    -- dbms_output.put_line(); Oracle还可以通过该语句进行数据打印输出...
    
    
    -- 方式二: 解决了不清楚变量与列的类型
    declare
           v_id    emp.empno%type :=&id; 
           v_ename emp.ename%type;
           v_JOB   emp.job%type;
           v_emp   emp%Rowtype;
    begin
          select ename , job 
                 into v_ename,v_JOB
                 from emp where empno = v_id;
          dbms_output.put_line('用户名'||v_ename||'职位: '||v_JOB);
          
          select * into v_emp from emp where empno = v_id;
          dbms_output.put_line('用户名'||v_emp.ename||'职位: '||v_emp.job);
    end;
    -- 表.列%type: 可以在不清楚列类型时候给设置类型;
    -- 表%ROWTYPE: 可以在不知道表中列个数 和 数据类型时候,可以通过 表%ROWTYPE 获取一个表类型变量...

> **注意：**  
> SELECT 语句在PL/SQL中的写法稍微有改变,  
> SELECT INTO　查询结果只能返回一条记录, 并赋值到变量中保存；如果返回多条数据或没有都会报错;  
> PL/SQL　中对于多条记录的查询, 后面会学习 **游标** 进行存储多个结果集;

## PL/SQL控制语句： ##

> PL/SQL可以通过控制结构, 来控制命令执行的流程。  
> 标准的SQL 没有流程控制的概念, 而PL/SQL提供了丰富的流程控制语句;  
> **控制结构共有三种类型：条件控制 循环控制 顺序控制**

### 条件控制 ###

条件控制用于控制条件执行一系列的语句：**`条件控制包括 IF语句 和 CASE语句`**  
**IF-THEN语句**

IF 布尔表达式 THEN
        	PL/SQL和SQL执行语句
    	END IF;

**IF-THEN-ELSE语句**

IF 布尔表达式 THEN
        	PL/SQL和SQL执行语句1
    	ELSE
        	PL/SQL和SQL执行语句2
    	END IF;

**IF-THEN-ELSIF语句**

IF 布尔表达式1 THEN
       		PL/SQL和SQL执行语句1
    	ELSIF 布尔表达式2 THEN					--注意：是ELSIF而不是ELSEIF
        	PL/SQL和SQL执行语句2
    	ELSE
        	PL/SQL和SQL执行语句3
    	END IF;

**CASE：类似于Java的Switch**在Oracle 9i 后引入，依据表达式，选择相应的when子句执行

CASE 条件表达式
        	WHEN 值1 THEN 			
        		PL/SQL和SQL执行语句1;
       	    WHEN 值2 THEN 
       	    	PL/SQL和SQL执行语句2;
       		.....
        	WHEN 值n THEN 
        		PL/SQL和SQL执行语句n;
        	[ELSE else_statements;]				--可选：相当于default 都不符合则执行..
    	END CASE;

![在这里插入图片描述][20201101224637587.png_pic_center]

### 循环控制 ###

> 循环控制用户重复执行一系列语句，**`循环控制包括: LOOP WHERE FOR`**  
> **EXIT 语句用于退出循环**  
> **EXIT WHERE <条件语句>** 条件成立则退出循环…

**LOOP死循环**

LOOP
       	PL/SQL和SQL执行语句;
    END LOOP;
    --实现计数器功能，当计数器为10或者大于10时退出
    DECLARE
    	v_count integer := 1;							        --声明变量
    BEGIN
    	LOOP 											       	--死循环开始
           	v_count:=v_count+1;							    	--变每次+1
           	IF v_count>=10 THEN							    	--判断变量 大于等于10 
               	EXIT;									        --退出循环 
           	END IF;										        --IF 结束
       	END LOOP;										        --死循环结束
    END;												        --PL/SQL块结束

**WHILE-LOOP循环:** 类似于while循环

WHILE 布尔表达式  LOOP
      	PL/SQL和SQL执行语句;
    END LOOP;

**FOR-LOOP循环:** 类似与for循环

FOR 变量名 in 最小值..最大值 loop			
    	循环执行的语句；
    END LOOP;
    
    -- ..两点表示在其中范围~eg: 1..20 循环从1开始20结束;
    begin
    	for i in 1..20 loop
    		insert 表 value(i,值1,值2,值3);
    		COMMIT;								--提交事务~
    	end loop;	
    	COMMIT;									--或放在最后统一提交事务~
    	-- 后面在加一个异常处理，如果出现异常 ROLLBACK; 回滚数据; 
    end;

### 顺序控制 ###

控制语句用户按顺序执行语句 **NULL** 个人觉得用的不多…

--NULL:
         -- NULL语句可以是一个可执行的语句,相当于一个占位符,不会执行任何的操作 的空语句;
         -- 它可以使某些语句变的有意义,提高程序的可读性,保证其它语句结构的完整性 和 正确性;
    DECLARE
    		v_count Number := 5;							        --声明变量
    BEGIN
         IF v_count >=10 then
            null;                                 --IF中需要有执行,不然报错; 为了使结构成立有意义使用 NULL
         Else
             dbms_output.put_line('值太小的'||v_count);
         END IF;		
    END;

### 动态SQL ###

> **什么是动态SQL**  
> 编译期间SQL 语句是不确定的，并且在运行时允许发生变化  
> **动态SQL应用场合**  
> 要执行一个DDL 语句时 需要增加程序的灵活性时 使用包DBMS\_SQL动态执行SQL语句时;  
> **动态SQL简直是PL/SQL的一大亮点而且可以搭配：条件控制…使用而产生不同的SQL语句用于执行不同的操作**

--动态sql 
    --查询用户总共有多少表
    select count(1) from dba_tables 
    select count(1) from dba_tables where table_name = upper('WSM');
    --查询所有表
    select * from dba_tables 
    
    --查询用户存在WSM表吗？ 如果有删除/没有则创建; 注意切换一个可以创建的用户哦！System用户！
    declare
           v_count number;       --用于判断是否存在表; 
           v_sql varchar2(500);  --执行的sql变量,varchar2类型长度500 毕竟存储的是sql长一点好~
    begin
           select count(1)       --获取count
                 into v_count
           from dba_tables where table_name = upper('WSM');       --upper() 方法将字符串中的小写字母转为大写字母
           
           if v_count>0 then                 --判断是否存在
              v_sql := 'drop table WSM';     --拼接SQL 
           else                              --else 拼接SQL 
              v_sql := 'create table WSM('||
                      'id number,'||
                      'name varchar2(20)'||
                      ')'; 
           end if;
           --执行动态sql
           execute immediate v_sql;
    end;
    -- 前面说PL/SQL中不可以使用 DDL语句;
    -- 而 execute immediate SQL; 可以解决该问题...

## EXECUTE IMMEDIATE语句 ##

> **EXECUTE IMMEDIATE dynamic\_sql**  
> dynamic\_sql: 表示一个SQL语句或者一个PL/SQL语句块的字符串表达式, 可以通过**EXECUTE IMMEDIATE语句执行…**

**常用语法**

--基本语法：
    BEGIN
         execute immediate '执行的SQL';
    END;
    
    -- 如果在存储过程 或 PL/SQL块执行还可以;
    -- 给变量进行赋值 INTO
    -- 对于需要参数执行的SQL(增删改查条件数据..),通过 Using 传参(根据顺序进行参数匹配注意类型也对应哦..);
    execute immediate 'select 列1,列2 from 表 where id=:id' into 变量1,变量2 USING 条件id;
    
    declare   
       v_name  varchar2(20);   
    begin   
       execute immediate 'select ename from emp where empno=:a' into v_name USING 7369; --USING指定 7369编号; 
         
       dbms_output.put_line(v_name);   
    end; 
    
    /* 注意事项： EXECUTE IMMEDIATE将不会提交一个DML事务执行，应该显式提交(对于增删改需要 commit; ) 当执行SQL语句时，不要用分号，当执行PL/SQL块时，在其尾部用分号. 对于处理动态语句,EXECUTE IMMEDIATE 比以前可能用到的更容易并且更高效. */

**对于execute 是真的非常好用, 尤其在存储过程~ 但是对于拼接一些特殊字符要非常注意！！**  
比如引号 ‘ 这真是恶心死我了搜了很多有的是 用 ’‘’ 三个引号间隔拼接为一个 ‘ 的对于新手非常难受;

> 可以使用**ASCLL码**来代替引号或是其它特殊字符~  
> chr(39) 就表示一个单引号~ 39 ASCLL码表示一个 单引号;  
> 这是我在完成存储过程中的一个条件查询拼接时间条件范围查询：  
> `v_where:= v_where||' AND EXP_DATE >= TO_DATE('||chr(39)||v_qdate||chr(39)||','||chr(39)||'yyyy-fmmm-fmdd'||chr(39)||')';`  
> 当前时写 简直恶心死了, **先将字符转换为时间进行判断, 过程还要当心引号！！**

--------------------

## 异常处理 EXCEPTION ##

> **什么是异常：**  
> 在运行过程中出现的错误叫做异常,  
> 发生异常后, 语句将停止执行，PL/SQL引擎立即将`控制权转到` **PL/SQL的异常处理部分**  
> **注意：**  
> 这里说的是 执行过程中的异常, **并不包含编译的异常…**  
> **异常处理`exception`** 用来处理正常执行过程中未预料的事件…  
> **两种比较经典的异常：** `预定义异常` 和 `用户自定义异常`

## 预定义异常： ##

Oracle预定义异常情况大约有 24个,  
对于这种异常情况的处理无须在程序中定义, 可由Oracle自动引发…  
当 PL/SQL 程序违反 Oracle 规则或超越系统限制时隐式引发；  
**常见预定义异常：**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1NTQyMzgw_size_16_color_FFFFFF_t_70_pic_center 2]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1NTQyMzgw_size_16_color_FFFFFF_t_70_pic_center 3]

> **OTHERS**  
> 处理程序异常不会漏过任何异常, 它相当于Java的 Exception 无论什么异常都可以**捕获**  
> 所以一般声明在 最后, 进行捕获遗落的异常处理…  
> **图上代码实例：**  
> 异常处理之后加一个 ROLLBACK; 回滚一下效果更好…  
> 实例意思是：循环新增三个商品类别(当前数据有点随便…) 如果过程中有异常进行捕获处理…则插入成功！

## 用户自定义异常： ##

用户可以在 PL/SQL 块的声明部分定义异常，  
自定义的异常通过 **RAISE语句** 显式引发;  
**实例Demo**

-- 切换Scott用户
    select * from emp
    
    -- 根据输入id 查询对应用户工资
    declare
           v_empno number :=&myid;
           v_ename varchar2(50);
           v_sal number;
           
           e_exp exception;                               -- exception异常类型;
    begin
         -- 查询数据赋值;
          select ename , sal 
                 into v_ename,v_sal 
                 from emp where empno = v_empno;
          -- 打印输出
          dbms_output.put_line(v_ename||'点击量:'||v_sal);
          
          if v_sal<2000 then                              -- 如果工资小于两千 
             raise e_exp;                                 -- 抛出异常..
          end if;
    exception
             when no_data_found then
                  dbms_output.put_line('没有找到数据');
                  rollback;                            
             when e_exp then                              -- 处理手动异常...
                  dbms_output.put_line('工资小于两千');
                  rollback;
             when others then                             -- 处理任何异常..
                  dbms_output.put_line('程序异常');
                  rollback;
    end;

**`扩`**

> 除了以上的异常情况处理…  
> **RAISE\_APPLICATION\_ERROR**  
> 还可以重新定义异常错误消息，为应用程序提供一种与Oracle交互方法d  
> **语法：**  
> `RAISE_APPLICATION_ERROR(err_number,err_message);`  
> err\_number: 表示为用户异常标识指定的编号, 注意范围必须是 -20999~20000之间负整数’  
> err\_message: 用户为异常指定的文本信息…长度可达 2049字节…

# 游标 #

**游标基本原理**

> Oracle 在执行一个包含 SELECT INSERT DELETE UPDATE …的语句时候  
> 会在内存中创建出一个缓存区，将执行结果放在这个缓冲区中, 而游标是指向该区的一个指针…

**游标分类：**

> Oracle中提供了两种游标类型, **静态游标(本次讲解)** **动态游标**  
> 静态游标： 是在编译器就知道明确的SELECT 语句的游标… **又细分为 显示游标/隐式游标**

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1NTQyMzgw_size_16_color_FFFFFF_t_70_pic_center 4]

**声明游标**

CURSOR cursor_name [( parameter[,parameter]…)]
    [RETURN return_type] IS selectsql;
    
    /* CURSOR： 用于声明一个游标,一般配合PL/SQL 声明使用... cursor_name： 自定义游标名; parameter： 可选参数,用于指定参数类型、模式等 return_type： 可选,指定游标的返回类型 selectsql: 需要处理的select语句，不能含INTO子句 */

**打开游标**

open cursor_name;
    -- cursor_name: 指定的游标名,来打开游标...

**提取游标**

FETCH cursor_name INTO variable_list;
    -- cursor_name: 指定的游标名,来打开游标...
    -- variable_list： 用于接收,游标中数据的变量名...注意类型匹配哦,使用时和普通的 SELECT 列1,列2 INTO 变量1,变量2 ... 类似;

**关闭游标**

close 游标名;
    -- 使用CLOSE语句关闭一个游标
    -- 关闭游标后，所有资源都将被释放，且不能再次被打开

## **实例Demo** ##

declare         
    --声明部分 
          --创建变量
          Ename varchar2(20);
          --创建一个静态游标, IS指定SELECT
          CURSOR AA IS SELECT Ename FROM EMP;
    begin
    --执行部分
         OPEN AA;   --打开游标,一般都是第一行声明...
         --调用游标;
         FETCH AA INTO Ename;        --第一次调用
         dbms_output.put_line(Ename);
         FETCH AA INTO Ename;        --第二次调用
         dbms_output.put_line(Ename);
         
         Close AA;  --关闭游标,一般都是最后一行...
    end;
    
    -- 两次分别输出了,两个用户名...

读取多行记录时，可以采用循环的方式 : **LOOP循环** **WHILE循环** **FOR循环**  
**显示游标的属性：**

<table> 
 <thead> 
  <tr> 
   <th>属性名称</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>%found</td> 
   <td>返回true和false，表示是否有数据</td> 
  </tr> 
  <tr> 
   <td>%isopen</td> 
   <td>返回true和false，表示游标是否打开</td> 
  </tr> 
  <tr> 
   <td>%notfound</td> 
   <td>返回true和false，表示是否没有数据，<code>一般调用前建议判断是否有值在使用..</code></td> 
  </tr> 
  <tr> 
   <td>%rowcount</td> 
   <td>表示受影响的数据一共有多少行</td> 
  </tr> 
 </tbody> 
</table>

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1NTQyMzgw_size_16_color_FFFFFF_t_70_pic_center 5]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1NTQyMzgw_size_16_color_FFFFFF_t_70_pic_center 6]

WHERE循环条件可以直接设置： **`游标%notfound`** 进行循环…

## 使用显示游标 删除或更新 ##

个人看了一段时间才明白, 可能介绍不全… 而且觉得很鸡肋！！

> 使用游标时候，如果处理过程中需要删除 或 更新行**可以借鉴下面做法…**;  
> 在定义游标时候必须使用：**SELECT…FOR UPDATE语句**  
> 在执行 DELETE 和 UPDATE时使用：**WHERE CURRENT OF 子句指定游标当前行**`详情如下！`

**声明**

CURSOR 游标名 IS SELECT 查询语句 FOR UPDATE [of columns];
    -- FOR UPDATE： 删除/跟新关键字
    -- [of columns: 查询语句单表时候,可省略...对于多表的查询,需要根据 of 列; 
    -- 锁定针对某一个表的操作,该游标 删/改只能对于该表...
    
    --使用时：根据WHERE后面条件...
    UPDATE 表 SET 列
    WHERE CURRENT OF 游标名;
    
    DELETE FROM 表
    WHERE CURRENT OF 游标名;

**实例Demo**

select * from emp
    --下面以给工资低于2000的雇员增加100元工资为例，说明使用显示游标更新数据的方法。示例如下：
    declare
    cursor emp_cursor is 
    select ename,sal from emp for update;
    v_ename emp.ename%type;--定义变量
    v_sal emp.sal%type;
    begin
    open emp_cursor;--打开游标
    loop
    fetch emp_cursor into v_ename,v_sal;--提取游标数据赋值给变量
    exit when emp_cursor%notfound;
    if v_sal <2000 then --当变量v_sal 小于2000时执行更新操作
    update emp set sal=sal+100 where current of emp_cursor;
    end if ;
    end loop;
    close emp_cursor;--关闭游标
    end;

# 存储过程 #

> 将SQL或者PL/SQL代码块集中用于完成特定功能的集合  
> **过程的结构**  
> 声明部分：包括类型、变量、游标  
> 执行部分：完成功能而编写的SQL语句或则是PL/SQL代码块  
> 异常处理部分  
> **从根本上来讲就是命名的PL/SQL块, 它可以比赋予参数并存储在数据库中** **可以由一个应用程序或其它PL/SQL调用使用**

## 创建存储过程 ##

CREATE [OR REPLACE] PROCEDURE <procedure_name> [(<parameter list>)]
    IS|AS 
       [local variable declaration]          --可选,局部声明;
    BEGIN
       <executable statements>               --可执行语句
    [EXCEPTION
       <exception handlers>]                 --可选,异常处理执行...
    END [procedure_name] ;
    
    --<procedure_name>: 存储过程名称;
    --[(<parameter list>)] 可选,存储过程的参数列表
    --[OR REPLACE] 可选,如果不包含 OR REPLACE 则表示仅仅新建一个存储过程,存在则报错;
    -- 如果包含 OR REPLACE 如果已存在则覆盖..

**实战**

----------------无参存储过程 myproc1 ---------------------
    --判断员工EMP表,每个员工在对应部门的平均薪资...评价
    create or replace procedure myproc1
    as
      -- 定义变量
      v_name emp.ename%type;    
      v_sal  emp.sal%type;
      v_dept emp.empno%type;
      v_dept_avgsal number;
      v_sql varchar2(200);      --动态SQL变量
      -- 定义游标
      cursor cur_result
             is select ename,sal,deptno from emp order by deptno;
    begin
      -- 打开游标
      open cur_result;
      -- 死循环结束
      loop
          -- 调用游标..
          fetch cur_result into v_name,v_sal,v_dept;
          -- 判断条件终止循环..
          if cur_result%notfound then
             exit;
          end if;
          -- 定义SQL
          v_sql:='select avg(sal) from emp where deptno =:a'; 
          -- 执行SQL获取 当前部门平均薪资
          execute immediate v_sql into v_dept_avgsal using v_dept ;
          -- 判断平均薪资
          if v_sal>v_dept_avgsal then
             dbms_output.put_line(v_name||':很优秀');
          elsif v_sal=v_dept_avgsal then
             dbms_output.put_line(v_name||':良好');
          else
             dbms_output.put_line(v_name||':需要努力了');
          end if ;
      
      end loop;             --循环结束 
      close cur_result;     --关闭游标 
      exception             --异常处理
         when others then
              dbms_output.put_line('程序出异常了！');
              
    end myproc1;            --存储过程结束; 
    
    
    ----------------有参存储过程 add_EMP -------------------
    -- 添加员工记录
    CREATE OR REPLACE PROCEDURE add_EMP(
           --输入参数
           eno     NUMBER,                  --员工编号
           name    VARCHAR2,                --名称
           salary  varchar2,                --薪水
           job     varchar2 DEFAULT 'CLERK',--员工部分,默认CLERK 
           dno     number
    ) IS
    BEGIN
         INSERT INTO EMP(empno,ename,sal,job,deptno) VALUES(eno,name,salary,job,dno);
    END;

存储过程创建完, 数据库定义执行完后并不会立刻执行，而是在数据库中存在…  
和 序列 索引 一样存储在某个位置, 方便后面使用时直接调用即可…(**数据库/应用程序…执行时可以调用`降低数据库和程序之间交流..降低资源`**)

## 过程的调用与删除 ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1NTQyMzgw_size_16_color_FFFFFF_t_70_pic_center 7]  
**调用存储过程**

> EXEC\[UTE\] 存储过程名( 参数列表 );

**有参存储过程 参数传递的三种方式？**

--按位置传递参数
    EXEC add_EMP(3,'WSM',1000,'CLERK',10);
    --按名称传递参数
    EXEC add_EMP(eno=>3,name=>'WSM',salary=>1000,job=>'CLERK',dno=>10);
    --混合方式传递参数：前面使用顺序传参,后面使用名称传参
    EXEC add_EMP(3,'WSM',salary=>1000,job=>'CLERK',dno=>10); 
    --但,前面顺序之后使用了名称之后就不可以在使用顺序了,因为顺序有可能已经乱了
    
    
    --注意使用前用户要具有操作 存储过程的授权;
    --存储过程创建之后只有,创建者和管理员才具有调用操作,如果普通用户需要还要赋权！！
    --授权语句：
    GRANT EXECUTE ON 存储过程名 TO 用户名;
    --撤销权限
    REVOKE EXECUTE ON 存储过程名 FROM 用户名;

## 存储过程的参数模式： ##

> **IN**  
> 用于接受调用程序的值 默认的参数模式  
> **OUT**  
> 用于向调用程序返回值  
> **IN OUT**  
> 用于接受调用程序的值，并向调用程序返回更新的值  
> **IN表示向存储过程传递参数，OUT表示从存储过程返回参数。而IN OUT 表示传递参数和返回参数；**

------------------创建入参和出参的过程--------------------
    -- 根据部门查询出对应 人员姓名和职位;
    create or replace procedure myproc2                       -- 声明 有参存储过程( 参数 );
    (      
         in_dept     in   number,                             -- in 入参,进入过程执行的参数,需要提供; 这里指对应部门;
         out_result  out  sys_refcursor                       -- out 出参,执行过程之后返回的结果参数,称为出参; 这里是一个游标,返回一个结果集; 
    )
    as
         v_sql varchar2(500);                                 -- 声明变量sql;
    begin
         v_sql:='select ename , job from emp where deptno = :a'; 
         open out_result for v_sql using in_dept;             -- 指向sql 给对应占位符;
    end myproc2;
    
    
    -- 执行 myproc2 存储过程~
    declare
           -- 声明变量,用于接收 或 存储过程需要的参数;
           v_deptno number:=20;                       -- 参数1
           v_result sys_refcursor;                    -- 出参 动态游标:结果
           v_name   varchar2(50);
           v_job    varchar2(50);
    begin
           scott.myproc2(v_deptno,v_result);                   -- 调用存储过程(给出对应的参数 部门no 出餐游标)~ 
           
           loop
               fetch v_result into v_name, v_job;
               if v_result%notfound then
                  exit;
               end if;
               dbms_output.put_line(v_name ||':'|| v_job);
           end loop;
           close v_result;
    end;

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1NTQyMzgw_size_16_color_FFFFFF_t_70_pic_center]: /images/20221120/3502e6918dcb4ed5893f7e290ea6c95a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1NTQyMzgw_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20221120/a4d4790eb57949edb781d96e5cb17f63.png
[20201101224637587.png_pic_center]: /images/20221120/ad2d7d60dc944684af6a5f53566591cb.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1NTQyMzgw_size_16_color_FFFFFF_t_70_pic_center 2]: /images/20221120/95c168b0c6a44562bb499bea896d1762.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1NTQyMzgw_size_16_color_FFFFFF_t_70_pic_center 3]: /images/20221120/8f503f75e5e64dbdac070767e3cb374e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1NTQyMzgw_size_16_color_FFFFFF_t_70_pic_center 4]: /images/20221120/a5a79ef8b1fa4021bac8cdf5edf32540.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1NTQyMzgw_size_16_color_FFFFFF_t_70_pic_center 5]: /images/20221120/c72dda3d7d3c4576a30d690c2b153dc7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1NTQyMzgw_size_16_color_FFFFFF_t_70_pic_center 6]: /images/20221120/902cca0e84aa4adf87c7626b257ebe3a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1NTQyMzgw_size_16_color_FFFFFF_t_70_pic_center 7]: /images/20221120/d74406d88caa47a0bc87e3e0913dd9a9.png