翻转课堂：PL/SQL编程

PL/SQL基础知识

什么是PL/SQL

PL/SQL是结合了Oracle过程语言和结构化查询语言的一种扩展语言，使用PL/SQL可以编写具有很多高级功能的程序，虽然通过多个SQL语句也能实现同样的功能，但是相比而言，PL/SQL具有更为明显的优点。
(1)PL/SQL具有编程语言的特点,它能把一组sql语句放到一 个模块中，使其更具模块化程序的特点。
(2) PL/SQL可以采用过程性语言控制程序的结构,也就是说。我们可以在PL/SQL中增加逻辑结构，如判断、循环等程序结构。
(3)问其他的编程语言一 样，PL/SQL可以对程序中的错误进行自动处理， 使程序能够在遇到错误时不会中断,即它的异常处理机制。
(4) PL/sqL程序块具有更好的可移植性， 可以移植到另一个Oracle数据库中。
(5) PL/SQL程序减少了网络的交互，有助于提高程序性能。

1. PL/SQL 体系结构
   PL/SQL引擎用来编译和执行PL/SQL 块或子程序，该引擎驻留在Oracle服务器中。PL/SQL引章仅执行过程语句，而将sqL语句发送给Oracle 服务器上的SQL语句执行器，由SQL语句执行器执
   行这些SQL语句。PL/SQL体系结构如图所示。
   

2. PL/SQL块简介
   PL/SQL是种块结构的语言， 它将一组语句放在一个块中。 PL/SQL 块将逻辑上相关的声明和语句组合在一起。 匿名块是一个未在数据库中命名的PL/SQL块，在运行时被传递到PL/SQL 引擎以便执行。在PL/SQL块中可以使用SELECT、 INSERT、UPDATE、 DELETE 等DML语句、事务控制语句及SQL函数等。PL/SQL块中不允许直接使用CREATE、DROP 或ALTER等DDL语句但可以通过动态SQL来执行它们。

一个PL/SQL块由3个部分组成：声明部分、执行部分、异常处理部分。
PL/SQL块的结构：

DECLARE  
--声明部分：在此声明PL/SQL的变量、类型及游标，以及局部的存储过程和函数。
BEGIN  
--执行部分：过程及SQL语句，是程序的主要部分，不可省略
[EXCEPTION]  
--执行异常处理部分：错误处理，可选
END;

3. 运算符和表达式
   PL/SQL语言支持的操作符包含关系运算符、一般运算符和逻辑运算符有等，与SQL语言类似。
   关系运算符：
   

在这里插入代码片

• 常量与变量的声明
  在PL/SQL块的可执行部分引用变量和常量前，必须先对其进行声明。变量和常量在PL/SQL块的声明部分声明，在PL/SQL块的可执行部分使用。
  声明变量语法如下：
  语法：

DECLARE variable_name date_type[(size)][:=init_value];

在语法中:

• variable_name:变量名
• date_type:数据类型
• size：指定变量范围
• init_value:变量的初始值
  声明变量语法如下：
  语法：

DECLARE variable_name CONSTANT date_type :=value;

PL/SQL程序设计中的标识符定义与SQL的标识符定义的要求相同，要求和限制如下：

• 标识符名不能超过30个字符
• 第一个字符必须为字母
• 不分大小写
• 不能用减号（-）
• 不能是SQL保留字
  变量命名方法：
  
  示例1：

declare
    v_ename VARCHAR2(20);
    v_rate number(7,2);
    c_rate_incr constant number(7,2):=1.10;
begin
  --方法一：通过SELECT INTO 给变量赋值
  SELECT ename,sal* c_rate_incr INTO v_ename,v_rate
  FROM employee
  WHERE empno='7788';
  --方法二：通过赋值操作符":="给变量赋值
  v_ename:='SCOTT';
END;

编码规则：
1、利用缩进排列展现逻辑结构
2、利用大小写增强可读性
3、格式化单独语句
4、格式化SQL语句

5. 注释
   使用双"-"（减号）加注释
   使用"/* */"来加一行或多行注释。

PL/SQL数据类型

1.标量数据类型
标量数据类型包含单个值，没有内部组件。变量数据类型包括数字、字符、布尔值和日期时间值4类数据类型。

2.LOB数据类型
Oracle提供了LOB（Large Object）类型，用于存储大的数据对象的类型。Oracle目前主要支持BFILE、BLOB、CLOB及NCLOB。

3.属性类型
属性用于引用变量或数据库列的数据类型，以及表示表中一行的记录类型。PL/SQL支持一下两种属性类型：

1. %TYPE
   定义一个 变量，其数据类型与已经定义的某个数据变量(尤其是表的某一列)的数据类型相一致，这时可以使用%TYPE。
   使用%TYPE特性的优点在于:
   (1)所引用的数据库列的数据类型可以不必知道。
   (2)所引用的数据库列的数据类型可以实时改变，容易保持一致， 不用修改PL/SQL程序。
   2)%ROWTYPE
   返回一个记录类型，其数据类型和数据库表的数据结构相- -致，这时可以使用%ROWTYPE。
   使用%ROWTYPE特性的优点在于:
   (1)所引用的数据库中列的个数和数据类型可以不必知道。
   (2)所引用的数据库中列的个数和数据类型可以实时改变，容易保持一致，不用修改PL/SQL程序。
   示例2

DECLARE 
  v_empno employee.empno%TYPE :=7788;
  v_rec employee%ROWTYPE;
begin
  SELECT * INTO v_rec FROM employee where empno=v_empno;
  DBMS_OUTPUT.put_line
         ('姓名:'||v_rec.ename||'工资:'||v_rec.sal||'工作时间:'||v_rec.hiredate);
END;

PL/SQL控制语句

PL/SQL程序可通过控制结构来控制命令执行的流程。标准的SQL没有流程控制的概念，PL/SQL提供了丰富的流程控制语句。
控制结构共有三种类型具体包括：条件控制、循环控制、顺序控制。
1、条件控制
用于根据条件执行一系列语句。条件控制包括IF语句和CASE语句。
IF语句语法：

IF语法：
                IF<布尔表达式> THEN
                    PL/SQL和SQL语句
                END IF;
                
 IF-ELSE语法：
                IF<布尔表达式> THEN
                    PL/SQL和SQL语句
                ELSE
                    --其他语句
                END IF;
                
--多重IF语法：
                IF<布尔表达式> THEN
                    PL/SQL和SQL语句
                ELSIF<其他布尔表达式> THEN
                    其他语句
                ELSIF<其他布尔表达式> THEN
                    其他语句
                ELSE
                    其他语句
                END IF;

CASE语句语法如下：

  一：
                 CASE 条件表达式
                    WHEN 条件表达式结果1 THEN
                        语句段1
                    WHEN 条件表达式结果2 THEN
                        语句段2
                    WHEN 条件表达式结果n THEN
                        语句段n
                    [ELSE 语句段]
                 END CASE;
                 
                二：
                 CASE 
                    WHEN 条件表达式1 THEN
                        语句段1
                    WHEN 条件表达式2 THEN
                        语句段2
                    WHEN 条件表达式n THEN
                        语句段n
                    [ELSE 语句段]
                 END CASE;

2.循环语句
用于重复执行一系列语句。循环控制包括LOOP和EXIT语句，使用EXIT语句可以立即退出循环，使用EXIT WHEN语句可以根据条件结束循环。
循环共有三种类型，包括LOOP循环、WHILE循环和FOR循环.
LOOP循环：

LOOP
	要执行的语句;
        EXIT WHEN<条件语句>--满足条件，退出循环
        END LOOP;

WHILE循环：

  WHILE<布尔表达式> 
                LOOP
                    要执行的语句;
                END LOOP;

FOR循环：

 FOR  循环计数器  IN   [REVERSE]  下限  ...  上限   LOOP
                    要执行的语句;
           END LOOP;

• 顺序控制
  顺序控制用于按顺序执行语句，顺序控制包括NULL语句和GOTO语句。GOTO语句不推荐使用，下面介绍NULL语句
• NULL语句：这是一个可执行语句，相当于一个占位符或不执行任何操作的空语句，可以使某些语句变得有意义，提高程序可读性，保证其他语句结果的完整性和正确性。

异常处理

什么是异常？

在程序运行时出现的错误叫作异常。异常情况处理用来处理正常执行过程中未预料的事件。

预定义异常

Oracle预定义的异常情况大约有24个。对于这种异常情况的处理，无需在程序中定义，由Oracle自动将其引发。参照下表：
语法：

   BEGIN    
                    sequence_of_statements;
                EXCEPRION
                    WHEN <exception_name> THEN    
                        sequence_of_statements;
                    WHEN OTHERS THEN 
                        sequence_of_statements;
                END;

处理用户自定义异常

程序在执行过程中，出现编程人员认为的非正常情况。对于这种异常情况的处理，需要用户在程序中定义，然后显式地在程序中将其引发。用户定义的异常错误通过显式使用RAISE语句来触发。当引发一个异常错误时，控制就转向到EXCEPTION块异常错误部分，执行错误处理代码。
对于这类异常情况的处理，步骤如下。
(1)在PL/SQL块的定义部分定义异常情况:
<异常情况> EXCEPTION ;
(2)抛出异常情况:
RAISE <异常情况>;
(3)在PL/SQL块的异常情况处理部分对异常情况做出相应的处理。
示例4

DECLARE 
  v_comm employee.comm%TYPE;
  v_comm_is_null EXCEPTION;--定义异常类型变量
begin
  SELECT comm  INTO v_comm FROM employee where empno=7788;
  if v_comm id null then
    RAISE e_comm_is_null;
  end if;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      Dbms_Output.put_line('雇主不存在！错误为:'||SQLCODE||SQLERRM);
    WHERE e_comm_is_null then
      Dbms_Output.put_line('该雇主无补助');
    WHERE others then
      Dbms_Output.put_line('出现其他异常');
END;

raise_application_error(error_number,error_message)的语法：
error_number表示用户为异常指定的编号。该编号必须是介于20000~20999之间的负整数。
error_message表示用户为异常指定的消息文本。消息长度可长达2048字节。错误消息是与error_number表示关联的文本。
示例5

DECLARE
      ...
BEGIN
      ...
      IF v_comm IS NULL THEN
        RAISE_APPLICATION_ERROR(-20001,'该雇主无补助');
      END IF;
END;

游标

游标基本原理

在Oracle中，在执行一个查询，新增，修改，删除语句PL/SQL块时，Oracle在内存中分配一个缓冲区将执行结果放在缓冲区，游标则是指向该区的一个指针。