PL/SQL语言基础

块（Block）是PL/SQL程序中最基本的结构。
PL/SQL的块由变量声明、程序代码和异常处理代码3部分组成。
DECLARE    --标记声明部分
……          --此处用来定义常量、变量、类型和游标等
BEGIN      --标记程序体部分开始
……           --此处用来编写各种PL/SQL语句、函数和存储过程
EXCEPTION --标记异常处理部分开始
……           --此处用来编写异常处理代码
END;        --标记程序体部分结束

 示例程序的代码如下：
SET ServerOutput ON; <-----设置环境变量ServerOutput为打开状态, 打开oracle自带的输出方法dbms_output
DECLARE　　　　　　   <-----声明段开始
  /* 声明变量 */
  var_UserName  VARCHAR2(100);
BEGIN　　　　　　　　 <-----主程序体开始
  SELECT UserName
    INTO var_UserName
    FROM Users
    WHERE UserId = 1;
  dbms_output.put_line(var_UserName); --输出变量(该函数可以输出变量的值)
END;　　　　　　　　　<-----主程序体结束

DECLARE对变量进行声明：
DECLARE
     <变量名1>  <数据类型1>;
     <变量名2>  <数据类型2>;
     ……
     <变量名n>  <数据类型n>;

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

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

声明常量。声明常量的基本格式如下：
<常量名> constant <数据类型> := <值>;
    关键字constant表示声明的是常量。要声明一个程序的版本信息常量conVersion：
　　conVersion constant VARCHAR2(20) := '1.0.0.1';

 声明变量。声明变量的基本格式如下：
　　<变量名> <数据类型> [(宽度) := <初始值>];

　　DECLARE  Database VARCHAR2(50) := ' Oracle 10g ';

 

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

 分支语句CASE
　　CASE <变量>
  　　 WHEN <表达式1> THEN 值1
  　　 WHEN <表达式2> THEN 值2
  　　 ……
 　　  WHEN <表达式n> THEN 值n
  　　 ELSE 值n + 1
　　END CASE;

 FI:

　　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;

　　DECLARE
　　   Num INTEGER := -11;
　　BEGIN
 　　 CASE
 　　   when  Num < 0 THEN
 　　     dbms_output.put_line('负数');
 　　   when Num >0 THEN
 　　     dbms_output.put_line('正数');
 　　 ELSE
 　　    dbms_output.put_line('0');
 　　 END CASE;
　  END;

 

循环语句LOOP…EXIT…END
　　LOOP
 　　 <程序块1>
  　　IF <条件表达式> THEN
  　　  EXIT
  　　END IF
  　　<程序块2> 
　　END LOOP;

FI:

　　DECLARE
  　　v_Num INTEGER := 1; 
  　　v_Sum INTEGER := 0;
　　BEGIN
　　  LOOP
  　　  v_Sum := v_Sum + v_Num;
   　　 dbms_output.put_line(v_Num);
   　　 IF v_Num = 3 THEN
   　　   EXIT;
   　　 END IF;
   　　 dbms_output.put_line(' + ');
   　　 v_Num := v_Num + 1;
  　　END LOOP;
  　　dbms_output.put_line(' = ');
  　　dbms_output.put_line(v_Sum);
　　END;

 

循环语句LOOP…EXIT WHEN…END
　　LOOP
  　　<程序块1>
  　　EXIT WHEN <条件表达式> 
  　　<程序块2>
　　END LOOP;

FI:

　　DECLARE
 　　 v_Num INTEGER := 1; 
  　　v_Sum INTEGER := 0;
　　BEGIN
 　　 LOOP
  　　  v_Sum := v_Sum + v_Num;
  　　  dbms_output.put_line(v_Num);
   　　 EXIT WHEN v_Num = 3;
   　　 dbms_output.put_line(' + ');
    　　v_Num := v_Num + 1;
  　　END LOOP;
 　　 dbms_output.put_line(' = ');
  　　dbms_output.put_line(v_Sum);
　　END;

循环语句FOR…IN…LOOP…END LOOP
　　FOR <循环变量> IN <初始值> .．<终止值>
　　LOOP
 　　 <程序块>
　　END LOOP;

FI:

　　DECLARE
 　　 v_Num INTEGER; 
  　　v_Sum INTEGER := 0;
　　BEGIN
  　　FOR v_Num IN 1..3
  　　LOOP
  　　  v_Sum := v_Sum + v_Num;
   　　 dbms_output.put_line(v_Num);
   　　 IF v_Num < 3 THEN
   　　   dbms_output.put_line(' + ');
   　　 END IF;
  　　END LOOP;
  　　dbms_output.put_line(' = ');
  　　dbms_output.put_line(v_Sum);
　　END;

 

WHEN语句来定义异常处理：
EXCEPTION
  WHEN <异常情况名> THEN
    <异常处理代码>
  WHEN <异常情况名> THEN
    <异常处理代码>
  ……
  WHEN OTHERS THEN
    <异常处理代码>

FI:

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

　　DECLARE
 　　   x NUMBER;
  　　  y number:=0;
　　BEGIN
  　　  x:= 123/y;
 　　EXCEPTION
   　　 WHEN ZERO_DIVIDE THEN
    　　 DBMS_OUTPUT.PUT_LINE('除数为0');
　　END;

DECLARE
    var_UserName VARCHAR(40);
BEGIN
    SELECT UserName INTO var_UserName
    FROM Users WHERE UserType = 1;
EXCEPTION
   WHEN NO_DATA_FOUND THEN
     DBMS_OUTPUT.PUT_LINE('没有数据');
   WHEN TOO_MANY_ROWS THEN
     DBMS_OUTPUT.PUT_LINE('返回多行匹配的数据');
   WHEN OTHERS THEN
     DBMS_OUTPUT.PUT_LINE('错误情况不明');
END;

 

常用函数 

ABS函数返回给定数字表达式的绝对值。

BEGIN
  dbms_output.put_line(ABS(-4));
END;

CEIL函数返回大于或等于所给数字表达式的最小整数。

BEGIN
  dbms_output.put_line(CEIL(116.24));
  dbms_output.put_line(CEIL(-112.75));
  dbms_output.put_line(CEIL(0));
END;

 

FLOOR函数返回小于或等于所给数字表达式的最大整数。

BEGIN 
dbms_output.put_line(FLOOR(116.24));
  dbms_output.put_line(FLOOR(-112.75));
    dbms_output.put_line(FLOOR(0));
END;

 

POWER函数返回给定表达式乘指定次方的值。

BEGIN
  dbms_output.put_line(POWER(15, 4));
END;

 

ROUND函数返回数字表达式并四舍五入为指定的长度或精度。

BEGIN  dbms_output.put_line(ROUND(123.456, 2));
  dbms_output.put_line(ROUND(123.456, 1));
    dbms_output.put_line(ROUND(123.456, 0));
      dbms_output.put_line(ROUND(123.456, -1));
  dbms_output.put_line(ROUND(123.456, -2));
    dbms_output.put_line(ROUND(123.456, -3));
END;

 

ASCII函数返回字符表达式最左端字符的 ASCII 代码值。

BEGIN
  dbms_output.put_line(ASCII('A'));
END;

 

LENGTH函数返回给定字符串表达式的字符（而不是字节）个数，其中不包含尾随空格。

SELECT UserName, LENGTH(UserName) FROM Users WHERE UserType =1

 

UPPER函数返回将小写字符数据转换为大写的字符表达式。

BEGIN
  dbms_output.put_line(UPPER('abc'));
END;

 

SYSDATE。返回当前日期和时间。TO_CHAR。转换日期为字符串。

BEGIN
  dbms_output.put_line(SYSDATE);
END;

BEGIN
  dbms_output.put_line(TO_CHAR(SYSDATE));
END; 

LAST_DAY。返回包含日期d的月份的最后一天的日期。这个函数可以被用来确定当前月中还剩下多少天。

MONTHS_BETWEEN。返回两个日期之间月的数目。

BEGIN
  dbms_output.put_line(LAST_DAY(SYSDATE));
END

DECLARE
  date1 VARCHAR2(20) := '2008-06-05';
  date2 VARCHAR2(20) := '2008-10-05';
BEGIN
  dbms_output.put_line(MONTHS_BETWEEN(TO_DATE(date2,'yyyy-mm-dd'), TO_DATE(date1, 'yyyy-mm-dd')));
END;

 

COUNT函数返回组中项目的数量。
  【例】统计表Users中用户记录的数量：
SELECT COUNT(UserName) FROM UserMan.Users;
MAX。MAX函数返回表达式的最大值。
  【例】统计表Users中最大的用户编号：
SELECT MAX(UserId) FROM UserMan.Users;
MIN。MIN函数返回表达式的最小值。
  【例】统计表Users中最小的用户编号：
SELECT MIN(UserId) FROM UserMan.Users;