Oracle之第三篇 PL/SQL基础

傷城~ 2024-02-05 22:35 52阅读 0赞

**目录**

Oracle之 第三篇 PL/SQL基础

PL/SQL程序块

PL/SQL语言

PL/SQL的基本结构

PL/SQL块分类

一、PL/SQL语言

二、PL/SQL 常量 、变量

合法字符

数据类型

LOB 数据类型

属性类型

运算符

常量

PL/SQL常量

1 、变量的声明

2、属性类型

% type

变量赋值

%type和%rowtype区别

RECORD 复合数据类型

总结

PL/SQL语言篇--结构化程序设计

PL/SQL结构化语句

条件结构

分支结构

多分支结构

带临时变量的多分支结构

多分支结构CASE（四种—重点）

Select 语句中的case :

分支语句—case 用法3

分支语句—case 用法4

循环结构

1. LOOP-EXIT-WHEN-END循环

2. WHILE-LOOP-END循环

1、异常概述

异常的捕获与处理:

异常概述:

1 、异常的类型

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

## Oracle之 第三篇 PL/SQL基础 ##

###    PL/SQL程序块 ###

*  **理解****PL/SQL****程序块****的结构**
 *  **数据类型** **(****掌握数据类型****%type****和****%****rowtype****的定义和应用****)**
 *  **PL/SQL****语言基本的****输入和输出和****变量****的赋值方式****(select into** **赋值方式****).**

#### **PL/SQL****语言** ####

SQL 语言只是访问、操作数据库的语言，并不是一种具有流程控制的程序设计语言，而只有程序设计语言才能用于应用软件的开发。

PL /SQL 是一种高级数据库程序设计语言， 该语言专门用于在各种环境下对 ORACLE 数据库进行访问。

由于该语言集成于数据库服务器中，所以PL/SQL 代码可以对数据进行快速高效的处理。除此之外，可以在 ORACLE 数据库的某些客户端工具中，使用 PL/SQL 语言也是该语言的一个特点。

#### **PL/SQL****的基本结构** ####

---示例程序块
    ---set serveroutput on
    Declare 
        example_text   varchar2(100);
    begin
        example_text:='欢迎来到PL/SQL世界,本例子为程序块示例!';
        dbms_output.put_line(example_text);
    exception
    when others then
        dbms_output.put_line('出现异常了!');
     end;

*  PL/SQL语言以块为单位，块中可以嵌套子块。
 *  PL/SQL语言的组成、嵌套和执行
 *  1 一个基本的PL/SQL块由3部分组成：
 *  声明（DECLARE）
 *  可执行部分（BEGIN）
 *  异常处理部分(EXCEPTION）
 *  声明部分
 *  声明部分以关键字DECLARE开始，BEGIN结束。主要用于声明变量、常量、数据类型、游标、异常处理名称以及本地（局部）子程序定义等。 
 *  可执行部分
 *  以关键字BEGIN开始，EXCEPTION或END结束（该部分通过变量赋值、流程控制、数据查询、数据操纵、事务控制、游标处理等实现块的功能。
 *  异常处理部分
 *  异常处理部分以关键字EXCEPTION,该部分用于处理该块执行过程中产生的异常。 
 *  注意：

PL/SQL块中的每一条语句都必须以分号结束，SQL语句可以多行，但分号表示该语句的结束。一行中可以有多条SQL语句，他们之间以分号分隔。

• 执行部分是必需的，而声明部分和异常部分是可选的 ；可以在一个块的执行部分或异常处理部分嵌套其他的 PL/SQL 块；

• 每一个PL / SQL块由BEGIN或DECLARE开始，以 ” END;” 结束。注释由 /\*  注释文本 \* / 或‘ \- - 注释文本’ 形式表示

• PL/SQL 块 不能 在屏幕上显示 SELECT 语句的输出，数据定义语言 (Data Definition language) 不能在执行部分中使用 .

#### **PL/SQL****块分类** ####

*  PL/SQL程序块可以是一个命名的程序块也可以是一个匿名程序块，匿名程序块可以用在服务器端也可以用在客户端。
 *  1、匿名块
 *  2、命名块
 *  函数(function)
 *  存储过程(procedure)
 *  包(package)
 *  触发器(trigger)
 *  3．块的执行
 *  SQL\*PLUS中匿名的PL/SQL块的执行是在PL/SQL块后输入/来执行。
 *  命名的程序与匿名程序的执行不同，执行命名的程序块必须使用execute关键字。

### **一、****PL/SQL****语言** ###

/*示例程序块2   重要*/
      DECLARE
    	    v_xm varchar2(8):='Jame';
    		v_zym varchar2(10):='计算机';
    		v_zxf number(2):=45;    /*定义变量类型*/
    	BEGIN
                   UPDATE XS  SET zxf=v_zxf
    		           WHERE xm=v_xm;
        		IF SQL%NOTFOUND THEN
                    DBMS_OUTPUT.PUT_LINE('没有该人，需要插入该人');
    		      INSERT INTO XS(XH,XM,ZYM,ZXF)          VALUES('007',v_xm,v_zym,v_zxf);
    	    END IF;
    end;      /*注意区分字段变量和普通变量*/

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

/*示例程序块3  重要  */
    
    DECLARE
       row_id ROWID;
       info VARCHAR2(100);
    BEGIN
       UPDATE scott.dept SET deptno=90 WHERE DNAME='RESEARCH'
             RETURNING rowid, dname||':'||to_char(deptno)||':'||loc
                INTO row_id, info;
       DBMS_OUTPUT.PUT_LINE('ROWID:'||row_id);
       DBMS_OUTPUT.PUT_LINE(info);
    END;
    RETURNING 子句用于检索被修改行的信息。

*  RETURNING 子句用于检索 INSERT 语句中所影响的数据行数，当 INSERT 语句使用 VALUES 子句插入数据时，RETURNING 字句还可将列表达式、ROWID 和 REF 值返回到输出变量中。几点限制：

• 1 ．不能与  DML  语句和远程对象一起使用；

• 2 ．不能检索  LONG  类型信息；

• 3 ．当通过视图向基表中插入数据时，只能与单基表视图一起使用。

/*示例程序块3  重要*/
    
    DECLARE
        Row_id ROWID;
        info VARCHAR2(40);
    BEGIN
         delete from scott.dept where deptno=70
           RETURNING rowid, dname||':'||to_char(deptno)||':'||loc
             INTO row_id, info;
         DBMS_OUTPUT.PUT_LINE('ROWID:'||row_id);
      DBMS_OUTPUT.PUT_LINE(info);
    END;
    RETURNING 子句用于检索被修改行的信息。

/*示例程序块3  重要*/
    
    DECLARE
      Row_id ROWID;
      info VARCHAR2(40);
    BEGIN
      INSERT INTO scott.dept VALUES (12, '财务室', '海口')
        RETURNING rowid, dname||':'||to_char(deptno)||':'||loc
         INTO row_id, info;
      DBMS_OUTPUT.PUT_LINE('ROWID:'||row_id);
      DBMS_OUTPUT.PUT_LINE(info);
    END;

### **二、****PL/SQL** **常量 、变量** ###

#### **合法字符** ####

在使用PL/SQL进行程序设计时，可以使用的有效字符包括以下3类：

*  所有的大写和小写英文字母；
 *  数字0～9；
 *  符号() + － \* / < > = ! ~ ；：. ｀ @ % , ＂ \# ＾ ＆ ＿ \{ \} ? \[ \]。

PL/SQL标识符必须以字母开头，后面可以跟多个字母、数字、$、下划线和\#。最大长度为30个字符，不区分大小写。

#### 数据类型 ####

PL/SQL 支持的内置数据类型:

![075e623326b64887ab4ad3190bafd082.png][]

##### **LOB** **数据类型**  #####

*  用于存储大文本、图像、视频剪辑和声音剪辑等非结构化数据。
 *  LOB 数据类型可存储最大 4GB的数据。
 *  LOB 类型包括：
 *  BLOB 将大型二进制对象存储在数据库中
 *  CLOB 将大型字符数据存储在数据库中
 *  BFILE 将大型二进制对象存储在操作系统文件中

##### **属性类型** #####

*  用于引用数据库列的数据类型，以及表示表中一行的记录类型
 *  属性类型有两种：
 *  %TYPE - 引用变量和数据库列的数据类型
 *  %ROWTYPE - 提供表示表中一行的记录类型
 *  使用属性类型的优点：
 *  不需要知道被引用的表列的具体类型。
 *  如果被引用对象的数据类型发生改变，PL/SQL 变量的数据类型也随之改变。

##### **运算符** #####

Oracle提供了三类运算符：算术运算符、关系运算符和逻辑运算符。

1. 算术运算符

算术运算符执行算术运算。算术运算符有：

\+（加）、\-（减）、\*（乘）、/（除）

连接运算符：‖（连接）

其中﹢(加)和﹣(减)运算符也可用于对DATE(日期)数据类型的值进行运算。

PL/SQL为支持编程，还使用其他一些符号。表5.4列出了部分符号，它们是最常用的，也是使用PL/SQL的所有用户都必须了解的。

表5.4 部分其他常用符号

![61b6a2bf22684f3f84e17de033a9c795.png][]

##### **常量** #####

*  声明常量时需要使用constant关键字,并且必须在声明时就为该常量赋值,在程序其它部分不能改变该常量的值。
 *  常量名称 constant 常量类型:=值;

#####  **PL/SQL****常量** #####

DECLARE
        v1 constant  varchar2(4):='示例';
        v2 constant varchar2(10):='常量';
    BEGIN
          DBMS_OUTPUT.PUT_LINE(v2||' '||v1);
    END;
    
    PL/SQL程序块的赋值符号是   :=

变量

1. 变量的声明

数据在数据库与PL/SQL程序之间是通过变量进行传递的。变量通常是在PL/SQL块的声明部分定义的。

变量名必须是一个合法的标识符，变量命名规则如下：

(1)变量必须以字母(A～Z)开头

(2)其后跟可选的一个或多个字母、数字(0～9)或特殊字符$、\# 或\_

(3)变量长度不超过30个字符

(4)变量名中不能有空格

表5.5是否合法的变量名

![5501cc36547d496ea4210a95d58251ae.png][]

###### 1 、变量的声明 ######

在使用变量前，首先要声明变量。变量定义的基本格式为：

<变量名><数据类型>\[(宽度):=<初始值>\]；

例如：定义一个长度为10byte的变量count，其初始值为1，是varchar2类型。

count varchar2(10) :=‘1’;

###### 2、属性类型 ######

*  用于引用数据库列的数据类型，以及表示表中一行的记录类型
 *  属性类型有两种：
 *  **%TYPE - 引用变量和数据库列的数据类型**
 *  **%ROWTYPE - 提供表示表中一行的记录类型**
 *  使用属性类型的优点：
 *  不需要知道被引用的表列的具体类型。
 *  如果被引用对象的数据类型发生改变，PL/SQL 变量的数据类型也随之改变。

###### **% type** ######

*  声明一个变量，使它的类型与某个变量或数据库基本表中某个列的数据类型一致，可以使用%TYPE复合数据类型。
 *  语法：
 *  变量名 表名.列名%type;
 *  示例

**declare**

**v\_empno emp.empno%TYPE;**

使用%TYPE声明具有以下两个优点：

**① 不必知道XH列的确切的数据类型；**

**② 如果改变了XH列的数据库定义，my\_xh的数据类型在运行时会自动进行修改。**

pl/sql程序，显示输出scott.emp表中的部分数据

declare
      emp_number  constant number(4):=7876;
      emp_name  varchar2(10);
      emp_job  varchar2(9);
      emp_sal  number(7,2);
    begin
      select ename,job,sal
          into emp_name,emp_job,emp_sal
                  from scott.emp where empno=emp_number;
                   dbms_output.put_line('查询的员工号为'||emp_number);
                   dbms_output.put_line('该员工的姓名为'||emp_name);
                   dbms_output.put_line('该员工的职位为'||emp_job);
                   dbms_output.put_line('该员工的工资为'||emp_sal);
    end;

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

declare
      emp_number  constant number(4):=7900;
      emp_name  scott.emp.ename%type;
      emp_job  scott.emp.job%type;
      emp_sal  scott.emp.sal%type;
    begin
      select ename,job,sal
          into emp_name,emp_job,emp_sal
                  from scott.emp where empno=emp_number;
                   dbms_output.put_line('查询的员工号为'||emp_number);
                   dbms_output.put_line('该员工的姓名为'||emp_name);
                   dbms_output.put_line('该员工的职位为'||emp_job);
                   dbms_output.put_line('该员工的工资为'||emp_sal);
    end;

%TYPE使用举例

**declare**

**emp\_name** **scott.emp.ename%type****;**

**emp\_job** **scott.emp.job%type****;**

**emp\_sal** **scott.emp.sal%type****;**

**注意：**

**不同****schema****对象的描述**

**Scott.emp**

**System.xs**

**如果细化到列则为**

**Scott.emp.empno**

**System.xs.xh**

##### **变量赋值** #####

**SELECT INTO** **赋数用法**

**SELECT...INTO** **语句可以给多个值同时赋值且两边的数量和类型必须相等。**

**必须有****where** **子句，使得****select** **语句从数据库表中选出的记录有且仅有一条。**

**select** **\*** **from** **表名** **可能有三种情况**

*  **无值 （产生异常）**
 *  **多值 （游标）**
 *  **唯一值**

%TYPE使用举例

变量的常用赋值方式

**:=**

*  **SELECT INTO** **变量集** **(****注意必须有****where** **子句，使得选出的记录有且仅有一条****)**
 *  **FETCH INTO** **变量集**

赋值语句练习一

要求：使用PL/SQL语言，统计xs表中同学的个数并显示出来。

declare
           v_1  number;
    begin  
       select count(*) into v_1 from xs;
       dbms_output.put_line(v_1);
    exception
       when others then 
       dbms_output.put_line('出现异常了');
    end;

PL/SQL中常用的基本数据类型

![f9d14094dcee48de8193f20b6b8415d6.png][]

**常用数据类型**

*  **%****用于表示属性提示符**
 *  **复合数据类型**
 *  **--1 .****使用** **%type** **定义变量**
 *  **--2.** **将****%****rowtype****获得整个记录的数据类型。**

%ROWTYPE复合数据类型:

declare
    one_emp  scott.emp%rowtype;
      begin
         select  *
             into one_emp
            from scott.emp where empno=7900;
             dbms_output.put_line('该员工的职位为'||one_emp.job);
              dbms_output.put_line('该员工的工资为'||one_emp.sal);
    end;

**注意：如何通过点记法取%rowtype类型的值**

declare
      emp_number  constant scott.emp.empno%type:=7900;
      one_emp  scott.emp%rowtype;
      begin
        select *
            into one_emp
                from scott.emp where empno=emp_number;
        dbms_output.put_line('查询的员工号为'||emp_number);
        dbms_output.put_line('该员工的姓名为'||one_emp.ename);
        dbms_output.put_line('该员工的职位为'||one_emp.job);
         dbms_output.put_line('该员工的工资为'||one_emp.sal);
    end;

###### **%type****和****%****rowtype****区别** ######

*  **使用****%type****可以使变量获得字段的数据类型，使用****%****rowtype****可以使变量获得整个记录的数据类型。**

**--****比较两者定义的不同：**

*  **变量名 数据表****.****列名****%type**
 *  **变量名 数据表****%****rowtype****。**

%rowtype和%type类型使用举例:

DECLARE
      v_emp emp%ROWTYPE;
      v_ename emp.ename%type;
      v_sal   emp.sal%type;
    BEGIN
    SELECT * INTO v_emp FROM emp WHERE ename='SMITH';
      DBMS_OUTPUT.PUT_LINE(v_emp.empno||' '||v_emp.sal);
    
      select ename,sal INTO v_ename,v_sal FROM emp WHERE empno=7900;
      DBMS_OUTPUT.PUT_LINE(v_ename||' '||v_sal);
    END;

**%****rowtype****和****%type****类型使用举例**

**要求：使用****PL/SQL****语言，中的****%type****和****%****rowtype** **定义****xs****表中的变量，显示****007****同学的信息。**

DECLARE
      v_student_name xs.name%TYPE;
      v_student_age xs.age%TYPE;
      v_student_gender xs.gender%TYPE;
      
      v_student_row xs%ROWTYPE;
    BEGIN
      -- 使用%type定义变量
      SELECT name, age, gender INTO v_student_name, v_student_age, v_student_gender
      FROM xs
      WHERE student_id = '007';
      
      -- 使用%rowtype定义变量
      SELECT *
      INTO v_student_row
      FROM xs
      WHERE student_id = '007';
      
      -- 显示变量值
      DBMS_OUTPUT.PUT_LINE('Using %type:');
      DBMS_OUTPUT.PUT_LINE('Name: ' || v_student_name);
      DBMS_OUTPUT.PUT_LINE('Age: ' || v_student_age);
      DBMS_OUTPUT.PUT_LINE('Gender: ' || v_student_gender);
      
      DBMS_OUTPUT.PUT_LINE('Using %rowtype:');
      DBMS_OUTPUT.PUT_LINE('Name: ' || v_student_row.name);
      DBMS_OUTPUT.PUT_LINE('Age: ' || v_student_row.age);
      DBMS_OUTPUT.PUT_LINE('Gender: ' || v_student_row.gender);
    END;
    /

> 在上述示例中，定义了三个变量v\_student\_name、v\_student\_age和v\_student\_gender，它们分别与表"xs"中的"name"、"age"和"gender"列具有相同的数据类型。使用%type关键字，可以避免硬编码数据类型，使代码更加灵活和可维护。
> 
> 另外，使用%rowtype定义了一个变量v\_student\_row，它与表"xs"的行结构具有相同的字段和数据类型。通过将整行数据直接赋值给该变量，可以方便地访问表中所有列的值。
> 
> 最后，使用DBMS\_OUTPUT.PUT\_LINE函数将变量的值输出到控制台显示。运行该示例，即可显示"xs"表中"007"同学的信息。

##### **RECORD** **复合数据类型** #####

DECLARE
       --定义与 hr.employees 表中的这几个列相同的记录数据类型
       TYPE RECORD_TYPE_EMPLOYEES IS RECORD(
       f_name hr.employees.first_name%TYPE,
       h_date hr.employees.hire_date%TYPE,
       j_id hr.employees.job_id%TYPE);
      --声明一个该记录数据类型的记录变量
       v_emp_record RECORD_TYPE_EMPLOYEES;
    BEGIN
        SELECT first_name, hire_date, job_id INTO v_emp_record
         FROM hr.employees WHERE employee_id = &emp_id;
        DBMS_OUTPUT.PUT_LINE('雇员名称：'||v_emp_record.f_name
        ||' 雇佣日期：'||v_emp_record.h_date||' 岗位：'||v_emp_record.j_id);
    END;

• **一个记录类型的变量只能保存从数据库中查询出的一行记录，若查询出了多行记录，就会出  
现错误。**

**本节要点:**

*  **理解****PL/SQL****程序块****的结构**
 *  **数据类型** **(****掌握数据类型****%type****和****%****rowtype****的定义和应用****)**
 *  **PL/SQL****语言基本的****输入和输出和****变量的赋值方式。**

### **总结** ###

*  **5.1 PL/SQL****语言基本结构（****PL/SQL****块的组成）**
 *  **5.2 PL/SQL****字符集**
 *  **5.3** **变量、常量和数据类型**

### **PL/SQL****语言篇****--****结构化程序设计** ###

#### **PL/SQL****结构化语句** ####

*  IF 语句根据条件执行一系列语句，有三种形式：IF-THEN、IF-THEN-ELSE 和 IF-THEN\-ELSIF

##### **条件结构** #####

**1. IF****逻辑结构**

**(1) IF-THEN-ENDIF****结构**

**(2) IF-THEN-ELSE-ENDIF****结构**

**(3) IF-THEN-****ELSIF****结构**

**(4) CASE****结构**

1. IF逻辑结构

(3) IF-THEN-ELSIF-THEN-ELSE

语法格式：

IF 条件 1 THEN

Run\_expression1

**ELSIF**条件  2 THEN

Run\_expression2

ELSE

Run\_expression3

END IF

##### **分支结构** #####

1、要求：向学生表中添加 记录，值为’007’ ‘Jame’ ‘计算机’ 45，并说明是否成功

DECLARE
          v_xm varchar2(8):='Jame';
         v_zym varchar2(10):='计算机';
         v_zxf number(2):=45;    /*定义变量类型*/
      BEGIN
     INSERT INTO XS(XH,XM,ZYM,ZXF)        VALUES('007',v_xm,v_zym,v_zxf);
     IF SQL%FOUND THEN
           DBMS_OUTPUT.PUT_LINE('操作成功');
    ELSE
           DBMS_OUTPUT.PUT_LINE('没有插入该人');
     END IF;
    END;

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

2 、要求：针对scott.emp表，计算7788号雇员的应交税金情况，薪金>=3000,应缴税金为薪金的0.08,薪金在1500和3000之间，应缴薪金的0.06，其它应缴0.04.

declare 
       v_sal  scott.emp.sal%type;
       v_tax   scott.emp.sal%type;
    begin
       select sal into v_sal  from  scott.emp  where empno=7788;
       if  v_sal>=3000  then
          v_tax:=v_sal*0.08;
        elsif  v_sal>=1500  then  
           v_tax:=v_sal*0.06;
        else
            v_tax:=v_sal*0.04;
        end if;  
        DBMS_OUTPUT.PUT_LINE('应缴税金:'||v_tax);
      end;

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

##### **多分支结构** #####

3 、要求：涉及表为scott.emp，输入一个员工号，修改该员工的工资，如果该员工为10号部门(deptno)，则要求工资增加100；若为20号部门，要求工资增加150；若为30号部门，工资增加200；否则增加300。

• **分析：**

• **首先搭好程序块的框架（** **declare begin exception end;** **）**

• **输入一个员工号** **------** **使用临时变量**

• **找出根据那个变量值的不同来做分支结构：** **部门号** **v\_deptno**

• **Select into** **语句求此变量，部门号** **v\_deptno**

• **分支结构求工资增加量**

• **Update**  **语句修改工资**

DECLARE
         v_deptno scott.emp.deptno%type;
         v_zj NUMBER(4); 
         v_empno  scott.emp.empno%type;
    BEGIN
          v_empno:='7788';
         SELECT deptno INTO v_deptno FROM scott.emp WHERE empno=v_empno;
         IF        v_deptno=10          THEN v_zj:=100;
         ELSIF v_deptno=20          THEN v_zj:=150;
         ELSIF v_deptno=30          THEN v_zj:=200;
         ELSE    v_zj:=300;
      END IF; 
      UPDATE scott.emp SET sal=sal+v_zj  WHERE empno='7788';
    END;

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

##### **带临时变量的多分支结构** #####

declare 
       v_deptno  scott.emp.deptno%type;
       v_zl  scott.emp.sal%type;
    begin
       select deptno into v_deptno  from  scott.emp  where empno=&&a;
       if v_deptno=10 then
           v_zl:=100;
         elsif   v_deptno=20 then
            v_zl:=150;
              elsif   v_deptno=30 then
              v_zl:=200;
         else
         v_zl:=300;
         end if; 
         
         UPDATE scott.emp SET sal=sal+v_zl  WHERE empno=&a;
         end;

**多分支结构**

*  临时变量&&a 和&a

##### **多分支结构****CASE****（****四种****—****重点）** #####

四种CASE语句

1. 简单型 CASE

2. 搜索型 CASE

3. 嵌入到 SELECT 语句执行复杂任务的 CASE

4. 嵌入到 PL/SQL 程序语句（如赋值语句）的 CASE

**1、****搜索型****CASE****语句**

**语法格式：**

**CASE**  **变量名**

**WHEN** **变量值** **THEN** **处理语句** **1** **；**

**WHEN** **变量值** **THEN** **处理语句****2****；**

**ELSE**  **处理语句****n+1**  **；**

**END CASE;**

5.4 PL/SQL基本程序结构

例: 关于成绩等级制和百分制的相互转换。

---例: 关于成绩等级制和百分制的相互转换。
    ---简单case表达式
    declare 
      grade  varchar2(4):='良好';
    begin
      case grade
        when '优秀' then dbms_output.put_line('大于等于90');
        when '良好' then dbms_output.put_line('大于等于80，小于90');
        when '及格' then dbms_output.put_line('大于等于60，小于80');
        else dbms_output.put_line('不及格');
     end case;
    end;

**等值比较的****CASE****语句:**

DECLARE
       v_deptno emp.deptno%type;
       v_increment NUMBER(4);
       v_empno  emp.empno%type;
    BEGIN
      v_empno:=&x;
     SELECT deptno INTO v_deptno FROM emp WHERE empno=v_empno;
      CASE v_deptno  
        WHEN 10 THEN v_increment:=100;
        WHEN 20 THEN v_increment:=150;
        WHEN 30 THEN v_increment:=200;
        ELSE  v_increment:=300;
    END CASE;
    UPDATE emp SET sal=sal+v_increment WHERE empno=v_empno; 
    END;

*  **完善程序**
 *  **Update****语句之后追加如下语句：**
 *  **if SQL%FOUND then**
 *  **dbms\_output.put\_line****('****更改成功****');**
 *  **select** **sal** **into** **v\_sal** **from** **scott.emp** **where** **empno****='7788';**
 *  **dbms\_output.put\_line****(****v\_sal****);**
 *  **end if;**

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

**1.** **搜索型****CASE****语句**

**语法格式：**

**CASE**

**WHEN**  **关系表达式** **1** **THEN** **处理语句** **1** **；**

**WHEN** **关系表达式** **2** **THEN** **处理语句****2****；**

**ELSE**  **处理语句****n+1** **；**

**END CASE;**

**比较与简单型****case****的区别**

**5.4 PL/SQL****基本程序结构和语句**

例: 关于成绩等级制和百分制的相互转换。

---例: 关于成绩等级制和百分制的相互转换。
    ---搜索case表达式
    declare 
      score  int:=91;
    begin
      case 
        when score>=90 then dbms_output.put_line('优秀');
        when score>=80 then dbms_output.put_line('良好');
        when score>=60 then dbms_output.put_line('及格');
        else dbms_output.put_line('不及格');
     end case;
    end;

**3、****嵌入到****SELECT****语句执行复杂任务的****CASE**

**if** **语句不同****,case** **语句可以用在****select****语句中，用于在检索数据的同时对数据进行判断并返回判断结果。**

**如下图****:** **通过****case****语句显示每一位同学的获得学分情况。**

![72c98432cafc4a3681dab2e8d7141f41.png][]

嵌入到SELECT语句执行复杂任务的CASE

**预备知识（****注意列表中别名的用法）**

① **select**  **xh,xm,zxf**  **from**  **xs** **;**

② **select**  **xh,xm,zxf**  **as**  **总学分**  **from**  **xs** **;**

③ **select**  **xh,xm,zxf** **总学分**  **from**  **xs** **;**

**----****构建需求列**

① **select**  **xh,xm,zxf** **,'** **获得学分情况** **' from**  **xs** **;**

**----- ,‘****获得学分情况****’** **列需要****分情况处理（****case****）**

*  **select** **xh,xm,zxf****,** **(case** **分支语句****)** **as '****获得学分情况****’ from** **xs****;**

**Select** **语句中的****case** :

**实现****:**

*  **select** **xh,xm,zxf****,**

**(case**

**when**  **zxf** **>50 then '** **gao** **'**

**when**  **zxf** **>=40 then '** **zhong** **'**

**else** **'** **学分不够，需继续** **'**

**end) as**

**获得学分情况**  **from**  **xs** **;**

**注意：**

**1.** **整个****case** **语句没有标点符号**

**2. case** **语句的结束用****end** **而非****end case**

**3. then** **之后没有****dbms\_output.put\_line****().**

**4. as** **之后无引号****.**

思考：如何把上例的结果保存起来（以表的形式）

*      create table t01 as select xh,xm,zym,
        (case
            when zxf>50 then 'gao'
              when zxf>=40 then 'zhong'
               else '学分不够，需继续'
        end) as
        获得学分情况  from xs;

###### **Select** **语句中的****case** : ######

*  对于学生借阅图书项目，检验图书是否过期：
 *  要求具体说明：
 *  过期
 *  没过期
 *  或者有借阅图书  三种具体情况
 *  可以用带日期型的表替代借阅日期列（scott.emp）
    
        SQL> select Months_Between(sysdate,to_date('20151001','yyyymmdd')) from dual;
         
        MONTHS_BETWEEN(SYSDATE,TO_DATE
        ------------------------------
                      1.04413045101553
         
        SQL> select trunc(sysdate-to_date('20181001','yyyymmdd')) 天数 from dual;
    
    ![9d251c44398248a0ad49870f171c7da1.png][]

##### **分支语句****—case** **用法****3** #####

检验图书是否过期：

select empno,ename,job,hiredate,

(case

when trunc(sysdate\-HIREDATE)>360 then '过期'

when hiredate is null then '没借书'

else '没过期'

end)

as 是否过期 from scott.emp;

思考：如何在显示是否过期的同时，显示根据过期天数确立的罚款数。

![6da2461203124610a3663dae0aa0942d.png][]

##### **分支语句****—case** **用法****4** #####

*  **嵌入到****PL/SQL****程序语句（如赋值语句）的****CASE**

FOR循环

LOOP-EXIT-END LOOP循环

WHILE循环

###### **循环结构** ######

###### 1. LOOP-EXIT-WHEN-END循环 ######

LOOP

Run\_expression

EXIT WHEN Boolean\_expression

END LOOP;

【例5.10】求10的阶乘。

---【例5.10】求10的阶乘。
    	DECLARE 
               s NUMBER:=1;
    	    n  NUMBER:=2;
    	BEGIN
    		LOOP
    		        s:=s*n;
    		        n:=n+1;
    		        exit when n>10;
    		END LOOP;
    		dbms_output.put_line(to_char(s));
    	END;

###### 2. WHILE-LOOP-END循环 ######

语法格式：

WHILE Boolean\_expression

LOOP

Run\_expression

END LOOP;

【例5.11】用WHILE-LOOP-END循环结构求10的阶乘。

---【例5.11】用WHILE-LOOP-END循环结构求10的阶乘。
    DECLARE 
               s NUMBER:=1;
    		n NUMBER:=2;
    	BEGIN
    	    while n<=10
                     LOOP
    		        s:=s*n;
    		        n:=n+1;
    		 end LOOP;
    		dbms_output.put_line(to_char(s));
    	END;

结构化设计之循环结构

4. FOR-IN-LOOP-END循环

语法格式：

FOR count IN count\_1..count\_n  /\*定义跟踪循环的变量\*/

LOOP

/\*执行循环体\*/

END LOOP;

【例5.12】用FOR-IN-LOOP-END循环结构求10的阶乘。

---【例5.12】用FOR-IN-LOOP-END循环结构求10的阶乘。
    DECLARE 
               s NUMBER:=1;
    	   n NUMBER:=2;
    	BEGIN
    		for n in 2..10
                     Loop
    		      s:=s*n;
    		end loop;
    		dbms_output.put_line(to_char(s));
    	END;

**For** **循环中的逆序:**

DECLARE 
         s NUMBER:=1;
    	   n NUMBER:=2;
     begin
      for n in reverse 1..10
    		loop
          s:=s*n;
          end loop;
    		dbms_output.put_line(to_char(s));
    	END;

**水仙花数**

--- 水仙花数
    declare
           i int;a int;b int;c int;
    begin  
       for i in 100..999
        loop
           a:= trunc(i/100);
           b:=trunc(i/10) mod 10;
           c:=i mod 10;
          -- dbms_output.put_line(a||' '||b||' '||c);
           if (i=a*a*a+b*b*b+c*c*c) then
             dbms_output.put_line(i);
           end if;
        end loop;   
    end;

**TRUNC(****x,y****)**  
**功能****:****计算截尾到****y****位小数的****x****值****. y****缺省为****0,****结果变为一个整数值****.**

**习题:**

*  习题：针对xs\_kc,表如何通过select语句显示学生信息时，显示每个学生的成绩所属的级别？示例查询结果如下：

![563cc5b0e7b3415bad562ca73a0888f4.png][]

#### **1、****异常概述** ####

*  Oracle错误处理机制
 *  一个优秀的程序都应该能够正确处理各种出错情况，并尽可能从错误中恢复。任何 ORACLE 错误（ORA-xxxxx 形式的 Oracle 错误号）

ORACLE  提供异常情况 (EXCEPTION) 和异常处理 (EXCEPTION HANDLER) 来实现错  
误处理 。  
当异常产生时抛出相应的异常，并被异常处理器捕获，程序控制权传递给异常处理器，由异常处理器来处理运行时错误。

###### **异常的捕获与处理:** ######

*  异常处理器的基本形式为
 *  EXCEPTION
 *   WHEN 异常1 OR 异常2 THEN  语句1;
 *  WHEN 异常3 THEN 语句2;

……

WHEN OTHERS THEN  语句 n ;

END;

*  **注意：**
 *  一个异常处理器可以捕获多个异常，只需要在WHEN子句中用OR连接即可；
 *  当数据库或PL/SQL在运行时发生错误时，一个异常被PL/SQL自动抛出。
 *  一个异常只能被一个异常处理器捕获，并进行处理。

###### 异常概述: ######

###### 1 、异常的类型 ######

*  预定义的Oracle异常
 *  用户定义的异常

异常处理部分 ：

【例】下面是一个异常处理的例子：

SET SERVEROUTPUT ON;
    DECLARE
        x NUMBER;
    BEGIN
        x:= 'aa123';
    EXCEPTION
       WHEN VALUE_ERROR THEN
         DBMS_OUTPUT.PUT_LINE('数据类型错误');
    END;

**预定义的异常** **p213**

![7b2ff62d25554d208ade04367b99b2f3.png][]

与数据库有关的一段异常:查找“李明”同学的学号 :

---与数据库有关的一段异常:查找“李明”同学的学号 ★
    DECLARE
        v_result xs.xm%TYPE;
        BEGIN
        SELECT xh INTO v_result
                 FROM xs 
                 WHERE xm='李明';
        DBMS_OUTPUT.PUT_LINE('The student number is '||v_result);
        EXCEPTION
               WHEN TOO_MANY_ROWS THEN
                  DBMS_OUTPUT.PUT_LINE('There has TOO_MANY_ROWS error');
               WHEN NO_DATA_FOUND THEN
                 DBMS_OUTPUT.PUT_LINE('There has NO_DATA_FOUND error');
              WHEN OTHERS THEN
                DBMS_OUTPUT.PUT_LINE('错误情况不明');
        END;

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

查询名为SMITH的员工工资，如果该员工不存在，则输出“There is not such an employee!”；如果存在多个同名的员工，则输出'There has too\_many\_rows error!”

declare 
     v_sal scott.emp.sal%type;
    begin
        select sal into v_sal from scott.emp where ename='SMITH';
       DBMS_OUTPUT.PUT_LINE(v_sal);
      EXCEPTION
           WHEN NO_DATA_FOUND THEN
                 DBMS_OUTPUT.PUT_LINE(‘没有返回数据');
           WHEN TOO_MANY_ROWS THEN
                 DBMS_OUTPUT.PUT_LINE(‘返回多行匹配数据');
     end;

*  注意：使用others异常可以借助两个函数来说明捕捉到的异常的类型\-----SQLCODE和SQLERRM

DECLARE
          v_result number;
        BEGIN
          SELECT xm INTO v_result
            FROM xs 
            WHERE xh='010010';
          DBMS_OUTPUT.PUT_LINE('The student name is'||v_result);
          EXCEPTION
               WHEN OTHERS THEN
                   DBMS_OUTPUT.PUT_LINE('the sqlcode is'||SQLCODE);
                   DBMS_OUTPUT.PUT_LINE('the sqlERRM is'||SQLERRM);
        END;

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

**用户自定义的异常**

*  异常中涉及的步骤
 *  　　 \-　 声明异常
 *  　　 \-　 引发异常
 *  　　 \-　 处理异常
 *  用户自定义异常必须在声明部分进行声明。
 *  当异常发生时，系统不能自动触发，需要用户使用RAISE语句。
 *  在异常处理部分捕捉并处理异常。

2. 用户定义异常

语法格式：

delcare

异常处理名称 exception;

begin

…..

EXCEPTION

WHEN 异常处理名称 THEN

语句1;

WHEN THEN

语句2;

WHEN OTHERS THEN

语句3;

END;

【例】修改7844员工的工资(增加1000)，保证修改后工资不超过6000。

---修改7844员工的工资(增加1000)，保证修改后工资不超过6000。
    DECLARE
      e_1 EXCEPTION;
      v_sal scott.emp.sal%TYPE;
    BEGIN
         UPDATE scott.emp SET sal=sal+1000 WHERE empno=7844 ;
          select sal into v_sal from scott.emp  where empno=7844;
         --- 取出更新后的工资
         IF v_sal>4000 THEN 
               RAISE e_1;
         END IF;
        EXCEPTION
        WHEN e_1 THEN
                DBMS_OUTPUT.PUT_LINE('The salary is too large!');
        ROLLBACK;
    END;

异常处理练习：

练习：更新scott.emp中7788员工的工资，若没有成功，请抛出异常。

declare
        v_empno  scott.emp.empno%type;
        no_result  EXCEPTION;
    Begin
       v_empno:=&a;
        update scott.emp  set sal=sal+100 where empno=v_empno;
        if  SQL%NOTFOUND   then
             raise no_result;
        end if;
    exception
        when  no_result then
           dbms_output.put_line('数据没有更新');
        when others then
           dbms_output.put_line(sqlcode||'   '||sqlerrm);
    end;

**习题** :

显示学生借书系统，是否借书过期，如过期，请给出需要缴纳的罚款数。

select deptno,empno,sal,hiredate ,(case
      when trunc(sysdate-hiredate)>5000 then ‘过期'
      when hiredate is null then '没借书'
        else ''
       end)  as 是否借书,
      (case
      when trunc(sysdate-hiredate)>5000 then    trunc(sysdate-hiredate)*0.1
          else 0
          end) as  罚款
     from scott.emp；

异常处理练习

--- 练习：更新scott.emp中7788员工的工资，若没有成功，请抛出异常。
    
    declare
        v_empno  scott.emp.empno%type;
        no_result  EXCEPTION;
    Begin
       v_empno:=&a;
        update scott.emp  set sal=sal+100 where empno=v_empno;
        if _______then
    ___________________end if;
    exception
    _________________
    dbms_output.put_line('数据没有更新');
        when others then
           dbms_output.put_line(sqlcode||'   '||sqlerrm);
    end;

[075e623326b64887ab4ad3190bafd082.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/0aca8912f365456f9f96787c0f9d7514.png
[61b6a2bf22684f3f84e17de033a9c795.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/0f9d36053cce43a5b6eb864293ca369c.png
[5501cc36547d496ea4210a95d58251ae.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/f04555458fa2413aaac28bf40ed3b28d.png
[f9d14094dcee48de8193f20b6b8415d6.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/fa4cf0b4431045f2a488d7e91e61d15b.png
[72c98432cafc4a3681dab2e8d7141f41.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/174906c5b5c34410af569c365ff0ad2e.png
[9d251c44398248a0ad49870f171c7da1.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/c1edd2f46f404829be6aac9f8c8e5513.png
[6da2461203124610a3663dae0aa0942d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/31d1ea090f124500b169a4756800f9d9.png
[563cc5b0e7b3415bad562ca73a0888f4.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/6d2915616487488cb0a09a9883c1a8f5.png
[7b2ff62d25554d208ade04367b99b2f3.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/79865255688e4494a70b5317b07d40be.png