Oracle（四）PL/SQL程序设计

1. PL/SQL基础

  PL/SQL是一种块结构的语言，一个PL/SQL程序包含了一个或者多个逻辑块，逻辑块中可以声明变量，变量在使用之前必须先声明。除了正常的执行程序外，PL/SQL还提供了专门的异常处理部分进行异常处理。每个逻辑块分为三个部分，语法是：

语法结构：PL/SQL块的语法
[DECLARE
     --declaration statements]  ①
BEGIN
     --executable statements    ②
[EXCEPTION
     --exception statements]    ③
END;

语法解析：
① 声明部分：声明部分包含了变量和常量的定义。这个部分由关键字DECLARE开始，如果不声明变量或者常量，可以省略这部分。
② 执行部分：执行部分是 PL/SQL块的指令部分，由关键字BEGIN开始，关键字END结尾。所有的可执行PL/SQL语句都放在这一部分，该部分执行命令并操作变量。其他的PL/SQL块可以作为子块嵌套在该部分。PL/SQL块的执行部分是必选的。注意END关键字后面用分号结尾。
③ 异常处理部分：该部分是可选的，该部分用EXCEPTION关键字把可执行部分分成两个小部分，之前的程序是正常运行的程序，一旦出现异常就跳转到异常部分执行。

PL/SQL中的特殊符号说明

符号	说明
:=	赋值运算符，Java和C#中都是等号，PL/SQL的赋值是：=
||	字符串连接操作符
--	PL/SQL中的单行注释
/* */	PL/SQL中的多行注释，多行注释不能嵌套
…	范围操作符，比如：1…5 标识从１到５
+，-，*，/	基本算术运算符，加减乘除
**	求幂操作，比如：3**2=9

，<，>=，<=，= |基本关系运算符，=表示相等关系，不是赋值
<>，!= |不等关系
AND，OR，NOT |逻辑运算符

1.1 声明与赋值

1.1.1 变量声明

  PL/SQL支持SQL中的数据类型，PL/SQL中正常支持NUMBER，VARCHAR2，DATE等Oracle SQL数据类型。声明变量必须指明变量的数据类型，也可以声明变量时对变量初始化，变量声明必须在声明部分。

声明变量的语法是：

语法格式：声明变量
变量名 数据类型[ :=初始值]

语法解析：
数据类型如果需要长度，可以用括号指明长度，比如：varchar2(20)。
注意：字符型一定要定义长度，数字型可以不用定义长度。

代码演示：声明变量
DECLARE
SNAME VARCHAR2(20) :='JERRY';  ①       
BEGIN
SNAME:=SNAME||'AND TOM';       ②           --直接赋值
DBMS_OUTPUT.PUT_LINE(SNAME);   ③
END;

代码解析：
① 声明一个变量sname，初始化值是“jerry”。字符串用单引号，如果字符串中出现单引号可以使用两个单引号（’’）来表示，即单引号同时也具有转义的作用。
② 对变量sname重新赋值，赋值运算符是“:=”。
③ dbms_output.put_line是输出语句。

对变量赋值还可以使用 SELECT…INTO 语句从数据库中查询数据对变量进行赋值。但是查询的结果只能是一行记录，不能是零行或者多行记录。

--代码演示：变量赋值
DECLARE
  SNAME VARCHAR2(20);
BEGIN
  SELECT ENAME INTO SNAME FROM EMP WHERE EMPNO = 7934;  --隐式游标赋值
  DBMS_OUTPUT.PUT_LINE(SNAME);
END;

1.1.2声明常量

  常量在声明时赋予初值，并且在运行时不允许重新赋值。使用CONSTANT关键字声明常量。

--代码演示：声明常量
DECLARE
  PI CONSTANT NUMBER := 3.14; --圆周率长值  ①
  R    NUMBER DEFAULT 3;      --圆的半径默认值3  ②
  AREA NUMBER;                --面积。
BEGIN
  AREA := PI * R * R;         --计算面积
  DBMS_OUTPUT.PUT_LINE(AREA); --输出圆的面积
END;

声明常量时使用关键字CONSTANT，常量初值可以使用赋值运算符（:=）赋值，也可以使用DEFAULT关键字赋值。

1.1.3 声明属性数据类型

%ROWTYPE：引用数据库中的一行（所有字段）作为数据类型。
%TYPE：引用数据库中的某列的数据类型或某个变量的数据类型。

--【例】找出员工编号为7934的员工名称和工资
--代码演示：%ROWTYPE
DECLARE
  MYEMP EMP%ROWTYPE;
BEGIN
  SELECT E.ENAME,E.SAL INTO MYEMP.ENAME,MYEMP.SAL FROM EMP E WHERE E.EMPNO = 7934;
  DBMS_OUTPUT.PUT_LINE(MYEMP.ENAME||'的工资是'||MYEMP.SAL);
END;

--代码演示：%TYPE
DECLARE
  SNAME EMP.ENAME%TYPE;
  SSAL  EMP.SAL%TYPE;
BEGIN
  SELECT E.ENAME, E.SAL INTO SNAME, SSAL FROM EMP E WHERE E.EMPNO = 7934;
  DBMS_OUTPUT.PUT_LINE(SNAME || '的工资是' || SSAL);
END;

1.2 条件控制语句

1. IF-THEN

该结构先判断一个条件是否为TRUE，条件成立则执行对应的语句块。
①用IF关键字开始，END IF关键字结束，注意END IF后面有一个分号。
②条件部分可以不使用括号，但是必须以关键字THEN来标识条件结束，如果条件成立，则执行THEN后到对应END IF之间的语句块内容。如果条件不成立，则不执行条件语句块的内容。
③条件可以使用关系运算符合逻辑运算符。
④在PL/SQL块中可以使用事务控制语句，该COMMIT同时也能把PL/SQL块外没有提交的数据一并提交，使用时需要注意。

2. IF-THEN-ELSE

把ELSE与IF-THEN连在一起使用，如果IF条件不成立则执行就会执行ELSE部分的语句。

3. IF-THEN-ELSIF

PL/SQL中的再次条件判断中使用关键字ELSIF

--查询JAMES的工资，如果大于1500元，则发放奖金100元，如果工作大于900元，则发奖金800元，否则发奖金400元
--代码演示：IF-THEN-ELSIF应用
DECLARE
  NEWSAL EMP.SAL % TYPE;
BEGIN
  SELECT SAL INTO NEWSAL FROM EMP WHERE ENAME = 'JAMES';
  IF NEWSAL > 1500 THEN
    UPDATE EMP SET COMM = 1000 WHERE ENAME = 'JAMES';
  ELSIF NEWSAL > 900 THEN
    UPDATE EMP SET COMM = 800 WHERE ENAME = 'JAMES';
  ELSE
    UPDATE EMP SET COMM = 400 WHERE ENAME = 'JAMES';
  END IF;
END;

4. CASE

  CASE是一种选择结构的控制语句，可以根据条件从多个执行分支中选择相应的执行动作。如果存在选择器selector，选择器selector与WHEN后面的表达式匹配，匹配成功就执行THEN后面的语句。如果所有表达式都与selector不匹配，则执行ELSE后面的语句。如果不使用CASE中的选择器，直接在WHEN后面判断条件，第一个条件为真时，执行对应THEN后面的语句序列。

1.3 循环控制

1. LOOP循环

LOOP循环是最简单的循环，也称为无限循环。

语法格式：LOOP循环
LOOP
  循环体
END LOOP；

语法说明：
1.循环体在LOOP和END LOOP之间，在每个LOOP循环体中，首先执行循环体中的语句序列，执行完后再重新开始执行。
2.在LOOP循环中可以使用EXIT或者[EXIT WHEN 条件]的形式终止循环，否则该循环就是死循环。

--执行1+2+3+…+100的值
DECLARE
  COUNTER   NUMBER(3) := 0;
  SUMRESULT NUMBER := 0;
BEGIN
  LOOP
    COUNTER   := COUNTER + 1;
    SUMRESULT := SUMRESULT + COUNTER;
    IF COUNTER >= 100 THEN      --①
      EXIT;
    END IF;
    -- EXIT WHEN COUNTER>=100;  --②
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('RESULT IS :' || TO_CHAR(SUMRESULT));
END;
/*
代码解析：
① LOOP循环中可以使用IF结构嵌套EXIT关键字退出循环;
② 该行可以代替①中的循环结构，WHEN后面的条件成立时跳出循环。
*/

2. WHILE循环

先判断条件，条件成立再执行循环体。

语法格式
WHILE 条件 LOOP
    --循环体
END LOOP;

--执行1+2+3+…+100的值
DECLARE
  COUNTER   NUMBER(3) := 0;
  SUMRESULT NUMBER := 0;
BEGIN
  WHILE COUNTER < 100 LOOP
    COUNTER   := COUNTER + 1;
    SUMRESULT := SUMRESULT + COUNTER;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('RESULT IS :' ||</