oracle的学习Ⅴ（PL/SQL程序块）

Bertha 。 2022-12-19 08:52 108阅读 0赞

# 一、什么是PL/SQL块 #

**PL/SQL**（Program Language / Structure Query Language）程序控制块是指**具用一定功能sql程序**，它可以复用，不过要**注意的是语句的缩进**以增强语句的可读性

例如：

set serveroutput on;		// 开启屏幕打印
    
    begin 				// 块开始标志
      dbms_output.put_line('hello world!');	# 利用dbms_output包里面的put_line方法进行输出
    end;				// 块结束标志

要注意的是

*  **put\_line()方法只能输出一个值**，也就是说put\_line(a, b)是不可以的，只能put\_line(a || ‘ ’ || b)
 *  语句都要**以分号结尾**
 *  **=为 比较运算符**，而 **:=** 为**赋值运算符**

# 二、块的组成部分 #

### 整体框架 ###

>  *  **declare** – 声明部分： 变量、常量、游标、数据类型等，该部分可以省略。
>  *  **begin** – 执行部分 ： 这是块的开始标志，该部分不能省略。
>  *  **exception** – 异常处理部分，该部分可以省略。
>  *  **end**; – 结束标志 ，该部分不能省略。

### 变量声明 ###

> variable\_name \[constant\] datatype\[:=value1|default value2\];

### 数据类型： ###

> number,char,varchar2,date,boolean(true false null),lob (clob,blob)

### 标识符命名 ###

>  *  通常以字母开头，最长不超过30个字符。
>  *  v\_(variable) c\_ (constant) rec\_ (record) cur\_ (cursor)

### %type ###

>  *  声明一个变量与已有的变量或已有列数据类型一致，若要与已有列数据类型一致的话，就必须对列添加归属对象（如：emp.sal而不是sal）
>  *  它是为了方便获得未知变量或列的数据类型

### %rowtype ###

>  *  声明一个变量与已有的记录或已有表结构保持一致，且声明出的这个变量是一个记录类型
>  *  它是为了方便在不知道某个表需要输出多少列的时候使用
>  *  它相当于加强的%type

例如1：

declare 
      v_bjxx varchar2(30); 		// 变量声明
      v_name varchar2(20); 
    begin 				// 块开始
      v_bjxx:='abc';
      v_name:='a';
      dbms_output.put_line('班级信息:'||v_bjxx||' '||'姓名：'||' '||v_name);		// 输出结果
    end;			// 块结束
    /				// 反斜杠号执行代码块

例如2：

// 写一个PL/SQL程序,查询7788员工的姓名和工资。
    
    declare 
      v_ename varchar2(10);
      v_sal   number(7,2);
    begin 
      select ename,sal into v_ename,v_sal from emp where empno=7788;			// 把查询的结果赋给v_ename,v_sal变量
      dbms_output.put_line('7788姓名是：'||v_ename||' '||v_sal);
    end;
    /			// 执行代码块

例如3：

// 写一个PL/SQL程序,查询7788员工的姓名和工资。
    
    declare 
      v_ename emp.ename%type;			// 获取emp.ename的数据类型，下同
      v_sal   emp.sal%type;
    begin 
      select ename,sal into v_ename,v_sal from emp where empno=7788;		// 查询的结果赋值给变量
      dbms_output.put_line('7788姓名是：'||v_ename||' '||v_sal);
    end;
    /

例如4：

// 写一个PL/SQL程序,查询7788员工信息。
    
    declare 
      rec_emp emp%rowtype;			// 获取emp.ename的数据类型，下同，注意这里的rec_emp为记录类型
    begin 
      select * into rec_emp from emp where empno=7788;	// 查询的结果赋值给变量
      dbms_output.put_line('7788姓名是：'||rec_emp.ename||' '||rec_emp.v_sal);		// 使用变量.原列名的方式获得数据
    end;
    /

# 三、哪些SQL语句可以用在PL/SQL 中使用呢 #

> select – 一般在select中使用into将查询的指导入变量中  
> DML – 数据修改语言（insert、update、delete）  
> commit – DML的提交  
> rollback – DML的滚回

注意：**DDL**（create、alter、drop）是**不可以在PL/SQL**里面改使用的

# 四、流程控制语句（选择语句） #

### if语句 ###

> if 条件1 then 处理1;  
> elsif 条件2 then 处理2;  
> …  
> else 处理N;  
> end if;

注意这里是**elsif**，也要注意块的**语句缩进**  
例子：

// 写一个PL/SQL程序，向 emp表中添加一条记录，若该记录存在，则提示记录已存在，否则添加该记录，并提示添加成功。
    
    declare 			
      v_empno emp.empno%type;		// 变量声明
      v_count int;
    begin 
      v_empno:=7700;			// 赋值
      select count(*) into v_count from emp where empno=v_empno;		// 判断emp表内是否有empno为7700的记录
      if v_count=1 then 	// 为1则已存在
         dbms_output.put_line(v_empno||' '||'已存在');
      else					// 为0则不在，并将记录插入到表内
         insert into emp(empno) values(v_empno);
         commit;			// DML需要commit
         dbms_output.put_line(v_empno||' '||'记录添加成功 ');
      end if;
    end;
    /

### case语句 ###

##### 简单case #####

> case 表达式  
> when value1 then 处理1;  
> …  
> else 处理N;  
> end case;

##### 搜索case #####

> case  
> when 条件1 then 处理1;  
> …  
> else 处理N;  
> end case;

例子：

// 查询7788员工的工资，当工资超过3000，显示高工资，2000到3000之间，一般工资，否则低工资。
    
    declare 
      v_sal emp.sal%type;
    begin 
      select sal into v_sal from emp where empno=7788;
      case				// case语句，类似于if语句
      when v_sal>3000 then 
         dbms_output.put_line('高工资');
      when v_sal between 2000 and 3000 then 
         dbms_output.put_line('一般工资');
      else
         dbms_output.put_line('低工资');
      end case;
    end;
    /

这里的**搜索case**与上面的**if**和以前的**case类似（只是结束符不同而已）**

# 五、流程控制语句（循环语句） #

### 简单循环 ###

> loop  
> 循环体  
> exit when 条件;  
> end loop;

例如：

// 输出100以内偶数和。
    
    declare
    	v_sum number default 0;			// 定义变量并赋值
    	v_i number := 2;
    begin
    	loop			// 开始循环
    		v_sum := v_sum + v_i;
    		v_i := v_i + 2;			// 增加
    		exit when v_i > 100;	// 退出条件
    	end loop;				// 循环结束标志
    	dbms_output.put_line('结果为：' || v_sum);
    end;
    /

### while循环 ###

> while 条件  
> loop  
> 循环体  
> end loop;

例如：

// 输出100以内偶数和。
    
    declare
    	v_sum number default 0;			// 定义变量并赋值
    	v_i number := 2;
    begin
    	while v_i <= 100				// 开始循环
    		loop
    			v_sum := v_sum + v_i;
    			v_i := v_i + 2;			// 增加
    		end loop;				// 循环结束标志
    	dbms_output.put_line('结果为：' || v_sum);
    end;
    /

### for 循环 ###

> for 循环控制变量 in \[reverse\] 下限…上限  
> loop  
> 循环体  
> end loop;

例如：

// 输出100以内偶数和。
    
    declare
    	v_sum number default 0;
    begin
    	for v_i in 1..100				// 循环开始
    		loop
    			if mod(v_i, 2) = 0 then		// if判断是否为偶数
    				v_sum := v_sum + v_i;
    			end if;
    		end loop;				// 循环结束
    	dbms_output.put_line('结果为：' || v_sum);
    end;
    /

要注意的是：

*  这里面**没有取余符号**，只能用\*\*mod()\*\*函数
 *  **for**里面的**循环控制变量**是**不用声明的**
 *  **for**里面的**下、上限**是**包括临界值**的

# 六、游标 #

**游标**本质就是**一个临时的内存块**，用来**存储某个查询的多条返回结果**。它可以理解为**多行的rowtype**，且它分为**隐式游标**和**显式游标**。

### 隐式游标 ###

当在**PL/SQL块中执行SQL语句中**时，**隐式游标**会在系统中**自动生成**，游标名 **默认为SQL（sql）**。  
**隐式游标属性：**

> isopen: DML执行过程中为真，结束为假 。  
> rowcount： DML语句影响的行数。  
> found： SQL 语句执行成功为真，失败为假。  
> notfound： 成功为假，失败为真。

**隐式游标用法：** sql%属性名  
例子:

// 向EMP表添加一条记录，判断该记录是否存在，不存在添加，存在则给出相应提示。
    
    declare 
       v_empno emp.empno%type;
    begin 
       v_empno:=&v_empno;
       insert into emp(empno) values(v_empno);
       if sql%found then 
          commit;
          dbms_output.put_line(v_empno||' '||'添加成功');
       else
          rollback;
          dbms_output.put_line(v_empno||' '||'已 存在 '); 
       end if;
    end;

### 显式游标 ###

**显式游标是用户自定义的**。使用显式游标**有四个步骤**：

*  ①声明游标：cursor cursor\_name is select;
 *  ②打开游标：open cursor\_name; --此时执行子查询，并读入内存缓冲区。
 *  ③提取数据 ：fetch cursor\_name into 变量名1, 变量名2……；
 *  ④关闭游标：close cursor\_name;

**显式游标属性**：

> isopen: 游标打开为真，否则为假。  
> rowcount：当前提取的行数。  
> found： 提取成功为真，失败为假  
> notfound：提取失败为真，成功为假

**显式游标用法**： 游标名%属性名

例1：

// 查询所有员工的姓名和工资。
    
    declare 			// 方法一
      cursor cur1 is select ename,sal from emp;
      v_ename emp.ename%type;
      v_sal emp.sal%type;
    begin 
      open cur1;
      loop
        fetch cur1 into v_ename,v_sal;
         exit when cur1%notfound;
        dbms_output.put_line(v_ename||' '||v_sal);
      end loop;
      close cur1;
    end;
    
    declare 				// 方法二
      cursor cur1 is select ename,sal from emp;
      v_ename emp.ename%type;
      v_sal emp.sal%type;
    begin 
      open cur1;
      fetch cur1 into v_ename,v_sal;
      while cur1%found
      loop
         dbms_output.put_line(v_ename||' '||v_sal);
         fetch cur1 into v_ename,v_sal;
      end loop;
      close cur1;
    end;

例2：

// 查询所有员工的姓名，工资，工号，工龄，奖金。
    
    declare 			// 方法一
      cursor cur1 is select ename,sal,empno,sysdate-hiredate a,hiredate,comm from emp;
      rec_cur1 cur1%rowtype;		// 这里可以是cursor类型的
    begin 
      open cur1;
      fetch cur1 into rec_cur1;
      while cur1%found
      loop
         dbms_output.put_line(rec_cur1.ename||rec_cur1.sal);
         fetch cur1 into rec_cur1;
      end loop;
      close cur1;
    end;
    
    declare 				// 方法二
      cursor cur1 is select ename,sal,empno,sysdate-hiredate a,hiredate,comm from emp;
      rec_cur1 cur1%rowtype;
    begin 
      for rec_cur1 in cur1
      loop 
             dbms_output.put_line(rec_cur1.ename||rec_cur1.sal);
      end loop;
    end;
    
    
    begin 					// 方法三，注意这里的cursor的四步都在for里面自动执行了
      for rec_cur1 in (select ename,sal,empno,sysdate-hiredate a,hiredate,comm from emp)
      loop 
             dbms_output.put_line(rec_cur1.ename||rec_cur1.sal);
      end loop;
    end;

# 七、异常处理 #

### 系统预定义异常 ###

类型有**no\_data\_found、too\_many\_rows、invalid\_cursor、zero\_divide、others**，注意这里的**others**类型要放在异常处理的**最后面**。不过这里有两个函数**sqlcode()、sqlerrm()**，它们分别为**错误编号**与**错误信息**  
例如：

// 查询特定员工的工资（使用姓名查询）。
    
    declare 
        v_empno emp.empno%type;
        v_sal emp.sal%type;
        v_ename emp.ename%type;
    begin 
        v_empno:=&v_empno;
        select ename,sal into v_ename,v_sal from emp where empno=v_empno;
        dbms_output.put_line(v_empno||'的工资是：'||v_sal);
    exception
       when no_data_found then 
           dbms_output.put_line('数据不存在');
       when too_many_rows then 
           dbms_output.put_line('数据过多');
        when others then 
           dbms_output.put_line('发生错误：'||'错误代码： '||sqlcode()||' '||'错误信息'||sqlerrm());
    end;

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

##### 方法一 #####

**用户自定义异常**分为**三**步骤： ①声明异常 （异常名 exception;）②激活异常 （raise 异常名;）③处理异常 （when 异常名 then 处理;）

// 写一个用户自定义异常，实现与异常zero_divide同样的处理。
    
    declare 
        v_a number;
        v_b number;
        zero_divide1 exception;			// 异常名定义
    begin 
        v_a:=&v_a;			// 获得输入
        v_b:=&v_b;
        if v_b=0 then 			// 激活条件
            raise zero_divide1;		// 激活异常
        else 
        dbms_output.put_line(v_a/v_b);
        end if;
    exception
        when zero_divide1 then 			// 处理异常
            dbms_output.put_line('v_b'||'不能为0');
    end;

方法二  
函数： **raise\_application\_error(-a,b)**， a代表// 写一个用户自定义异常，实现与异常zero\_divide同样的处理。，范围为**20000-20999,** b代表**错误信息**。但是该函数有时会无法捕获错误

// 写一个用户自定义异常，实现与异常zero_divide同样的处理。
    
    declare 
      v_a number;
      v_b number;
    begin 
      v_a:=&v_a;
      v_b:=&v_b;
      if v_b=0 then 
        raise_application_error(-20001,'除数不能为0');	// 函数处理异常
      else
        dbms_output.put_line(v_a/v_b);
      end if;
    end;

# 八、其他 #

**开启屏幕的打印**，该语句**不能出现的PL/SQL块**里面

set serveroutput on/off; 		// 开关屏幕输出

**从键盘输入获得内容**，注意如果要输入的内容为**字符串**或者**日期类型**的话就必须**使用引号把输入内容括起来**

v_names := '&v_name'				// v_name为变量名，这里的v_names是字符串类型的
    
    // 例如：
    select * from emp where empno = &empno;			// 不要加引号
    select * from emp where ename = ‘&ename’;		// 要加引号

本文只用于学习与记录