Oracle（六）PL/SQL语言

PL/SQL语言的结构

块（Block）是PL/SQL程序中最基本的结构.

PL/SQL的块由变量声明、程序代码和异常处理代码3部分组成。

DECLARE    --标记声明部分 
……              --此处用来定义常量、变量、类型和游标等
BEGIN          --标记程序体部分开始 
……              --此处用来编写各种PL/SQL语句、函数和存  
                       储过程
EXCEPTION --标记异常处理部分开始
……              --此处用来编写异常处理代码
END;            --标记程序体部分结束

示例：

SET ServerOutput ON;//设置环境变量serveroutput为打开状态，从而使PL/SQL程序能够在SQL*Plus中输出结果。
DECLARE
  var_UserName  VARCHAR2(100);
BEGIN
  SELECT UserName 
    INTO var_UserName
    FROM Users
    WHERE UserId = 1;
  dbms_output.put_line(var_UserName); --输出变量
END;
/

PL/SQL组件

声明部分

DECLARE
     <变量名1>  <数据类型1>;
     <变量名2>  <数据类型2>;
     ……
     <变量名n>  <数据类型n>;

变量名定义规则：

• 标识符必须以字符开头。
• 标识符中可以包含数字（0～9）、下划线（_）、“$”和“#”。
• 标识符最大长度为30。
• 标识符不区分大小写，TypeName和typename是完全相同的。
• 不能使用PL/SQL保留字为标识符名，例如不能声明变量名为DECLARE。

常用的几种基本数据类型 ：

• BLOB：二进制大对象，可以用来保存图像和文档等二进制数据。
• BOOLEAN：布尔数据类型，支持TRUE/FALSE值。
• CHAR：固定长度字符串。
• CLOB：字符大对象，可用来保存多达4GB的字符数据。
• DATE：存储全部日期的固定长度字符串。
• LONG：可变长度字符串。
• NUMBER：可变长度数值。
• RAW：二进制数据的可变长度字符串。
• VARCHAR2：可变长度字符串。

声明常量

常量名 constant 数据类型 := 值;
Conversion constant VARCHAR2(20) := '1.0.01';

声明变量

变量名 数据类型 [(宽度) := 初始值];
Database VARCHAR2(50) := ' Oracle 11g ';

执行部分

赋值语句

变量名  := 值;

条件语句IF

IF 条件表达式 THEN
  执行语句 …… 执行语句n
[ELSIF 条件表达式 THEN
  执行语句 …… 执行语句n
 ……
 ELSE
  执行语句]
END IF;

分支语句CASE

CASE 变量
  WHEN 表达式1 THEN 值1 (or 语句)
  WHEN 表达式2 THEN 值2 (or 语句)
  ……
  WHEN 表达式n THEN 值n (or 语句)
  [ELSE 值m (or 语句)]
END;

示例：

SET ServerOutput ON;
DECLARE
  varDAY INTEGER := 3; 
  Result VARCHAR2(20);
BEGIN
  Result := CASE varDAY
    WHEN 1 THEN '星期一'
    WHEN 2 THEN '星期二'
    WHEN 3 THEN '星期三'
    WHEN 4 THEN '星期四'
    WHEN 5 THEN '星期五'
    WHEN 6 THEN '星期六'
    WHEN 7 THEN '星期七'
    ELSE '数据越界'
  END;
  dbms_output.put_line(Result);
END;

循环语句LOOP…EXIT…END

LOOP
  程序块1
  IF 条件表达式 THEN
    EXIT
  END IF
  程序块2  
END LOOP;

循环语句LOOP…EXIT WHEN…END

LOOP
  程序块1
  EXIT WHEN 条件表达式 
  程序块2
END LOOP;

循环语句WHILE…LOOP…END LOOP

WHILE 条件表达式
LOOP
  程序块
END LOOP;

循环语句FOR…IN…LOOP…END LOOP

循环变量自动加1

FOR 循环变量 IN 初始值 .．终止值
LOOP
  程序块
END LOOP;

异常处理

EXCEPTION
  WHEN 异常情况名 THEN
    异常处理代码
  WHEN 异常情况名 THEN
    异常处理代码
  ……
  WHEN OTHERS THEN
    异常处理代码

PL/SQL标准异常名 ：


示例：

SET SERVEROUTPUT ON;
DECLARE
    x NUMBER;
BEGIN
    x:= 'a123';--向NUMBER型变量赋值字符串，导致异常
EXCEPTION
   WHEN VALUE_ERROR THEN
     DBMS_OUTPUT.PUT_LINE('数据类型错误');
END;

常用函数

数值型函数

• ABS：给定数字表达式的绝对值
• CEIL：返回大于或等于所给数字表达式的最小整数
• FLOOR：返回小于或等于所给数字表达式的最大整数
• POWER：返回给定表达式指定次方的值
• ROUND（num,精度值）返回数字表达式并四舍五入为指定的长度或精度（ROUND(123.456, 2)）

字符型函数

• ASCII：返回字符表达式最左端字符的 ASCII 代码值 ASCII(‘ABC’)=65（A为65）
• LENGTH：返回给定字符串表达式的字符（而不是字节）个数，其中不包含尾随空格
• UPPER：返回将小写字符数据转换为大写的字符表达式

日期型函数

• SYSDATE：返回当前日期和时间
• TO_CHAR：转换日期为字符串
• LAST_DAY ：返回指定日期所在月份的最后一天的日期
• MONTHS_BETWEEN ：返回两个日期之间月的数目

统计函数

• COUNT函数返回组中项目的数量
• MAX函数返回表达式的最大值
• MIN函数返回表达式的最小值。