Oracle--PL/SQL学习笔记

PL/SQL基本语法

体系结构

块头标

1. 包含程序单元的类型、程序单元的名称和用于调用该程序单元的任意参数。
2. 块头标的定义如下：

PROGRAM_TYPE name
([parameter IN/OUT/IN OUT para_type_specs,] …)
[RETURN datatype]

• PROGRAM_TYPE
  FUNCTION
  PROCEDURE
  PACKAGE
• Parameter
  IN
  OUT
  IN OUT
• para_type_specs，可以指明某参数是NOT NULL
• RETURN用于指明该函数的返回值的类型

比如：希望某个PL/SQL函数返回一个包含客户所居地理位置的数字。函数需要接受州名缩写、城市名称与州名缩写或邮政编码。

Function getLocation(state in varchar2) return number
Function getLocation(city in varchar2, state in varchar2) 
return number
Function getLocation(zip in number) return number

声明节
1. 定义

name [CONSTANT] datatype [(constraint)] [ := value];

• Constraint：是对变量的一种约束，例如NOT NULL；如在使用变量的过程中，违反了变量的约束，PL/SQL将返回错误。
• 在声明变量的最后，可以为该变量赋值或定义默认值。

2.变量通常在PL/SQL程序单元的声明节中声明，但是借助以下语法可以在PL/SQL程序单元的任意位置声明变量或常量

DECLARE
name [CONSTANT] datatype [(constraint)] [ := value];
……

执行节

• 执行节完成PL/SQL程序单元中所有的行为。以关键字BEGIN开头，END结束（END后必须有一个分号结束）。
• 任何PL/SQL必须包含执行节
• 执行节必须包含至少一个可执行语句，尽管该语句可以是关键字NULL。此时NULL表示为空语句。
• PL/SQL块是可以嵌套的，因此必须考虑变量的作用域问题。
  注意
  任何特殊标识符的作用于都限于声明它的块以及包含在该块的任何子块内。要求在作用域内保持一致

<<main>>
DECALRE
  myname varchar2;
  BEGIN
    DECLARE
      myname VARCHAR2;
    myname := main.myname;
  END;

异常节

1. 异常节是最后一节，是可选的。
2. 异常节用于处理在执行该块的逻辑期间所发生的任何错误。

EXCEPTION
  WHEN exception_name
  THEN
       error handling statements; ……
       [WHEN OTHERS
          default exception handling;]

异常相关函数

1. 异常处理过程中，需要明确知道发生异常的原因。
2. 在WHEN OTHERS语句中，将众多的错误原因放在一起进行处理，需要明确知道错误的类型和原因。
3. Oracle提供了两个系统变量用以处理此问题：
   –SQLCODE，返回错误的错误代码；
   –SQLERRM，返回错误的出错信息；

exception 
  when others
  then
    dbms_output.put_line (‘Error’ || SQLERRM || ‘occurred.’);//“||”数据库中字符串连接符

运算符

• 算术运算符
  

• 逻辑运算符
  

• 常用函数
  

应用例子：

DECLARE
   todays_date             DATE;
   current_day                  VARCHAR2(9);
   current_day_length      BINARY_INTEGER;
BEGIN
   todays_date := SYSDATE;
   -- 得到今天的日期赋给变量
   current_day := TO_CHAR(todays_date, 'DAY');
   -- 返回今天星期几
   current_day := RTRIM(current_day);
   --  去掉右空格
   current_day := INITCAP(current_day);
   -- 日期长度
   current_day_length := LENGTH(current_day);
   DBMS_OUTPUT.PUT_LINE('Today''s date is ' || current_day || ', ' || 
TO_CHAR(todays_date));
   DBMS_OUTPUT.PUT_LINE('The length of the word ' || current_day 
   || ' is ' || TO_CHAR(current_day_length) || ' characters.');
END;

实现逻辑

1. 通过使用条件性逻辑、分支和循环逻辑，介绍在PL/SQL中实现逻辑的基本方法。
   条件逻辑 –IF
   — IF … THEN … ELSE
   — CASE
   分支
   循环
   — LOOP
   — FOR … LOOP
   — WHILE … LOOP

//1
if  condition
  then
     logic1;
end if;
//2
if  condition1
  then
     logic1;
  elseif condition2
     logic2;
else
  logic3;
end if;
//3
if  condition
  then
     logic1;
  else
     logic2;
end if;

1. 例子：nEmpId为参数

declare 
  mysal real(6,2);
  mycomm real(6,2);
begin
  select sal, comm into mysal, mycomm from emp
    where empid = :nEmpId for update of comm;
  if mysal+mycomm <= 100000 then
    update emp set comm=100000 -sal where empid=:nEmpId;
  else
    update emp set sal=9999.99 where empid=:nEmpId;
  end if;
  commit;
END;

条件逻辑 – CASE

• 如果IF分支语句中的分支过多，容易造成IF语句结构变得复杂，此时可以使用CASE语句来代替。
• CASE语句中，包含多个WHEN条件。如果WHEN的条件满足，则执行其后的语句。如果所有的WHEN条件都没有满足，则代码会产生一个异常：CASE_NOT_FOUND，并将控制转移给代码块的异常节。

case myoffice
  when ‘Boston’
    then mycomm := mycomm * 2;
  when ‘Atlanta’
    then mycomm := mycomm + 100;
  else
    begin
        mycomm := mycomm;
    end;
end case;

分支

• GOTO：可以将程序定向至某个标号（LABEL）所指定的程序位置。其中，标号的声明需要用双尖括号包围起来，例如<< Label1>>，goto lavel1。一般情况下不要用GOTO分支
• RETURN：中止当前程序的执行，并将控制返回给调用程序。

if grade = ‘A’ then
  goto label1;
end if;
……
<<lable1>>
if flag = ‘N’ then
    insert …
else    
    update …
end if;

循环

• LOOP
• FOR
• WHILE

LOOP循环

LOOP
    LOGIC
    EXIT;
END LOOP;
LOOP
    LOGIC
    EXIT WHEN condition;//带有循环终止条件
END LOOP;

注意：1. 循环中可以包含多个EXIT。但只要执行EXIT，在循环中后续代码都忽略。
2.如没有EXIT，循环将无限制地执行下去
FOR 循环

• FOR … LOOP结构允许指定LOOP循环的次数

for counter [reverse] low … high//指定循环次数
loop
    logic;
end loop;

• WHILE … LOOP

WHILE condition
LOOP
    logic
END LOOP;

• 与FOR循环的差异
  FOR循环有明确的循环次数，WHILE循环没有
  FOR循环至少执行一次，WHILE可能一次也没有。与LOOP的第二种形式类似。

例子：
编写PL/SQL，该程序向某个班添加一个学生。过程中检查该班注册人数是否达到其极限。
如果该班有空位置，则将该班学生人数加1；
如果该班已经满员，该过程报一个错误，让调用程序知道存在问题。

首先，需要创建块的头标部分。
必须首先以PL/SQL编译器指令开头。需要CREATE或者REPLACE关键字。REPLACE关键字的意思是用新代码替换现有的具有相同名称的程序单元。

create or replace procedure addstudent
    ( roomin in integer ) is

后续的步骤有：

• 添加变量的声明
• 添加事务逻辑
• 添加异常处理

CREATE OR REPLACE PROCEDURE addstudent (roomin IN INTEGER)
IS
//变脸声明
   roomname       VARCHAR2 (20);
   studentcount   PLS_INTEGER;
   capacity       PLS_INTEGER;
   noroom         EXCEPTION;
//执行节
BEGIN
   SELECT scount, cap, rname
     INTO studentcount, capacity, roomname
     FROM scott.rooms
    WHERE room_id = roomin;

   IF studentcount > capacity - 1
   THEN
      RAISE noroom;
   ELSE
   UPDATE scott.rooms
         SET scount = studentcount + 1
       WHERE room_id = roomin;
      COMMIT;
    DBMS_OUTPUT.put_line (   'Student count now '  || studentcount  || ' in '                           || roomname  );
   END IF;
EXCEPTION
   WHEN noroom   THEN
      DBMS_OUTPUT.put_line ('There is no room in ' || roomname);
   WHEN OTHERS   THEN
      DBMS_OUTPUT.put_line ('Error ' || SQLERRM || ' occurred.');
END;