Oracel PL/SQL学习记录

1. PL/SQL块
PL/SQL结构化的语言，其程序结构的基本单位是“块”(Block)。PL/SQL块有三个部分：声明部分、执行部分、异常处理部分

[DECLARE]
    declaration statements
BEGIN
    executable statements
[EXCEPTION]
    exception statements
END

2. PL/SQL程序块可以是一个命名的程序块也可以是一个匿名程序块，匿名程序块可以用在服务器端也可以用在客户端。执行部分包含了所有的语句和表达式，执行部分以关键字BEGIN开始，将以关键字END结束。分号分隔每一条语句，使用赋值操作符:=或SELECT INTO给每个变量赋值，执行部分的错误将在异常处理部分解决，在执行部分中可以使用另一个PL/SQL程序块，这种程序块被称为嵌套块。

    所有的SQL数据操作语句都可以用于执行部分，PL/SQL块不能在屏幕上显示SELECT语句的输出。SELECT语句必须包括一个INTO子串或者是游标的一部分。

    执行部分使用的变量和常量必须首先在声明部分声明，执行部分必须至少包括一条可执行语句，NULL是一条合法的可执行语句，事物控制语句COMMIT和ROLLBACK可以在执行部分使用。

   数据定义语言(Data Definition language)不能在执行部分中使用，DDL语句与EXECUTE IMMEDIATE一起使用或者是DBMS_SQL调用。

变量，常量，表达式：
1. 变量
 声明变量
    Variable_name datatype [(WIDTH)] [:= DEFAULT expression]
 给变量赋值
    通常给变量赋值有两种方式：一是直接给变量赋值；二是通过SQL SELECT INTO给变量赋值。

2. 常量
常量与变量相似，但常量的值在程序内部不能改变，常量的值在定义时赋予，声明方式与变量相似，但必须包括关键字CONSTANT。

DECLARE
 ORDER_NO NUMBER(3);   
 CUST_NAME VARCHAR2(20);
 ORDER_DATE DATE;
 EMP_NO INTEGER:=25;
 PI CONSTANT NUMBER:=3.1416;
BEGIN
 NULL;
END;

流程控制

1. 条件结构：

if结构：
IF condition THEN 
   statement;
END IF;
IF condition THEN 
   statements_1;
ELSE 
   statements_2;
END IF;

case 多分支：
CASE 表达式（或变量）
     WHEN 表达式1 THEN 语句1;
     WHEN 表达式2 THEN 语句2;
     WHEN 表达式3 THEN 语句3;
     WHEN 表达式4 THEN 语句4;
     ELSE 值5;
END CASE;

2. 循环结构

1. loop循环
LOOP
   [statement1]
   [statement2]
   …………
   EXIT WHEN (condition);
END LOOP;

2. while...loop循环
 WHILE (condition) LOOP
   [statement1]
   [statement2]
   …………
END LOOP;

3. for循环
FOR counter IN [REVERSE] start_range .. end_range LOOP
  statements;
END LOOP;
counter是一个隐式声明的变量，初始值是start_range，第二个值是start_range+1(步长)，直到end_range。如果使用了REVERSE关键字，那么范围将是一个降序。

3. 异常

    1. PL/SQL的异常

        异常处理块中包含了与异常相关的错误发生以及当错误发生时要进行执行和处理的代码。

        异常部分的语法一般如下：

BEGIN
   EXCEPTION
     WHEN excep_name1 THEN
     ……
     WHEN excep_name2 THEN
     ……
     WHEN OTHERS THEN
     ……
END; 

      2. 预定义异常
        预定义异常是由运行系统产生的。例如，出现被0除时，PL/SQL就会产生一个预定义的ZERO_DIVIDE异常。
      3. 自定义异常
        异常不一定必须是Oracle返回的系统错误，用户可以在自己的应用程序中创建可触发及可处理的自定义异常，调用异常处理     需要使用Raise语句。

常用函数：    

数字函数：

字符函数：

日期函数：

数据类型转换函数：

集合函数：

简单pl块示例：

1.
declare
  a number :=20;
  b number :=-40;
  string varchar2(50);
begin
  string :=case 
           when (a>b)then 'A is greater than B'
           when (a<b)then 'A is less than B'
           else 'A is equal to B'
           end;
  dbms_output.put_line(string);
end;

2. 块嵌套
DECLARE
  v_sno VARCHAR2(6);
  v_sname VARCHAR2(20);
  v_num NUMBER(1);
BEGIN
  v_sno :='001';
  v_sname :='张三';
  BEGIN
    SELECT 1
    INTO v_num
    FROM student
    WHERE trim(sno) =v_sno;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      v_num :=0;
    WHEN OTHERS THEN
      dbms_output.put_line('Error in SELECT:'||SQLERRM);
      RETURN;
  END;
  IF (v_num = 0)THEN
    INSERT INTO student(sno,sname) VALUES (v_sno,v_sname);
    Commit;
  END IF;
  dbms_output.put_line('Successful Completion');
EXCEPTION WHEN OTHERS THEN
  dbms_output.put_line(SQLERRM);
END;


捕获用户自定义异常：
DECLARE
  exp_no001 EXCEPTION;   -- a user-defined exception
  v_cnt NUMBER;
BEGIN
  SELECT COUNT(*)
  INTO v_cnt
  FROM sc
  WHERE sno='001';
  IF (v_cnt=0)THEN
    --explicitly raising the user-defined exception
    RAISE exp_no001;
  END IF;
EXCEPTION
  --handling the raised user-defined exception
  WHEN exp_no001 THEN
    dbms_output.put_line('There are no Student NO 001');
  WHEN OTHERS THEN
    dbms_output.pu t_line('ERR:An error occurred with info :'||TO_CHAR(SQLCODE)||' '||SQLERRM);
END;