Oracle之第三篇 PL/SQL基础-Toy模板网

这篇具有很好参考价值的文章主要介绍了Oracle之第三篇 PL/SQL基础。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

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 支持的内置数据类型:

Oracle之第三篇 PL/SQL基础,数据库学习,平时学习,课外新知识,oracle

LOB 数据类型

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

属性类型

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

运算符

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

1. 算术运算符

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

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

连接运算符：‖（连接）

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

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

表5.4 部分其他常用符号

Oracle之第三篇 PL/SQL基础,数据库学习,平时学习,课外新知识,oracle

常量

声明常量时需要使用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是否合法的变量名

Oracle之第三篇 PL/SQL基础,数据库学习,平时学习,课外新知识,oracle

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使用举例

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;

变量的常用赋值方式

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中常用的基本数据类型

Oracle之第三篇 PL/SQL基础,数据库学习,平时学习,课外新知识,oracle

常用数据类型

%用于表示属性提示符
复合数据类型
--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语句显示每一位同学的获得学分情况。

Oracle之第三篇 PL/SQL基础,数据库学习,平时学习,课外新知识,oracle

嵌入到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;

Oracle之第三篇 PL/SQL基础,数据库学习,平时学习,课外新知识,oracle

分支语句—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;

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

Oracle之第三篇 PL/SQL基础,数据库学习,平时学习,课外新知识,oracle

分支语句—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语句显示学生信息时，显示每个学生的成绩所属的级别？示例查询结果如下：

Oracle之第三篇 PL/SQL基础,数据库学习,平时学习,课外新知识,oracle

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

Oracle之第三篇 PL/SQL基础,数据库学习,平时学习,课外新知识,oracle

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


---与数据库有关的一段异常:查找“李明”同学的学号 ★
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；

异常处理练习文章来源地址https://www.toymoban.com/news/detail-781873.html

--- 练习：更新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;

到了这里，关于Oracle之第三篇 PL/SQL基础的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！

Oracle之 第三篇 PL/SQL基础

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 、异常的类型

相关文章

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏

支付宝扫一扫领取红包，优惠每天领

二维码1

二维码2

Oracle之第三篇 PL/SQL基础

Oracle之第三篇 PL/SQL基础

二、PL/SQL 常量、变量