主题：[推荐]Oracle数据库PL-SQL学习资料之一——基础

1、简介
PL/SQL是Procedural Language/SQL的缩写，它在SQL语言中增加了过程化语言的结构特点，如变量、控制结构、过程函数、对象类型和方法。
如例子：
先建立表：
create table stu
(
    sid integer primary key,
    name varchar(8) not null,
    sex char(1) check(sex in('M','F'))
);

插入表记录：
insert into stu values(1,'Tom','M');
insert into stu values(2,'Alice','F');
insert into stu values(3,'eric','M');

注意提交，如：commit;

更新表（可以运行两次，观察其效果）：
declare
sname varchar(10):='Jame';
begin
update stu set name='James' where name=sname;
if SQL%NOTFOUND then
  insert into stu values('9','Jame','M');
end if;
end;
/
select * from stu;

删除记录：
delete from stu where name='James';

需要提交才能在图形界面程序中看到数据的更新

不同版本的PL/SQL功能有些许差别，可以通过select * from v$version;来查看版本
但是主要的内容基本一致，如程序块，控制语句等。

2、开发运行环境
编译和运行PL/SQL程序块需要PL/SQL引擎，而PL/SQL引擎可以分为服务器端PL/SQL引擎和客户端PL/SQL引擎。
对于服务器端PL/SQL引擎比较常见，它把客户端发送的PL/SQL程序块编译执行，对于SQL语句再提交给SQL引擎编译执行，如SQL*Plus就是一个利用服务器端PL/SQL引擎工作的客户端程序。
对于客户端PL/SQL引擎，主要使用在Oracle的两个开发工具上（Forms和Reports），它们都包含一个PL/SQL引擎，能在客户端对PL/SQL程序块进行预先的编译处理，如Forms含有触发器和过程，其中的SQL语句和对存储过程的调用将被送往服务器去执行。
当然，不论哪一种都需要连接服务器进行认证处理，但这不是PL/SQL程序块的工作，而是PL/SQL命令的环境所完成的，即使对于客户端PL/SQL引擎也有自己的客户认证方式。

常见的PL/SQL开发工具有：
2、1 SQL*Plus
最简单的工具，没有本地PL/SQL引擎，是Oracle标准安装的一部分。
它不区分大小写，主要是字符模式，在Windows版本中也有GUI部分，如连接数据库和选项设置等。Oracle9i还提供了一个基于浏览器的iSQL*Plus，用于字符模式输入，但是没有任何输出。

发送的命令有些是提交给服务器处理，有些则是SQL*Plus自己处理（如set serveroutput on等）。
语句以分号（用于SQL语句或者PL/SQL语句中间）或者斜线（用于PL/SQL语句结束处）结束

语句还可以保存，命令为
save c:\tt.sql;
读取的命令为
start c:\tt.sql;
@ c:\tt.sql;
使用set echo on;还可以显示文件内容。
（SQL*Plus工具本身也有文件处理的菜单）

PL/SQL本身没有输入和输出功能，它是通过使用DBMS_OUTPUT包来实现的。
具体使用方法为：
1）打开输出功能，设置输出缓冲区大小
set serveroutput on size 1000000;
2）使用dbms_output包的put_line过程向输出缓冲区输出，当整个PL/SQL语句执行完毕后，SQL*Plus会检查缓冲区的内容，并打印出来
如：
set serveroutput on size 1000000;
begin
dbms_output.put_line('Hello from PL/SQL!');
dbms_output.put_line('Today is ' || to_char(SYSDATE,'dd-mon-yyyy'));
end;
/

2、2 Rapid SQL
2、3 DBPartner Debugger
2、4 SQL Navigator
由Quest公司出品
注意，如要看到输出，需要点选“session”——“server output”

2、5 TOAD（Tool for Oracle Application Developers）
2、6 SQL Programmer
2、7 PL/SQL Developer

3、PL/SQL基础
所有的测试，都可以使用如下的基本框架语句：
set serveroutput on size 1000000;

declare

begin
   dbms_output.put_line('Hello!');
end;
/

所有的PL/SQL语句都是由过程性语句或者SQL语句组成，其中过程性语句包括变量声明、过程调用和控制结构等，SQL语句用来访问数据库。

3、1 语句块
3、1、1 基本使用
PL/SQL的基本单元是语句块，包含匿名语句块和命名语句块。匿名语句块往往由客户端动态生成去调用服务器的存储程序，而且只能执行一次。命名语句块具有名称，又可以细分为：标号语句块、子过程、触发器

如匿名语句块
declare
sname varchar2(10):='eric';
begin
update stu set name='Eric' where name=sname;
end;
/

可以观察如下
select * from stu;

如标号语句块
<<OneExec>>
declare
sname varchar2(10):='eric';
begin
update stu set name='Eric' where name=sname;
end OneExec;
/

如子过程
create or replace procedure OneProc as
sname varchar2(10):='eric';
begin
update stu set name='Eric' where name=sname;
end OneProc;
/

可以观察如下
exec OneProc;
或者
begin
OneProc;
end;
/

如触发器（不能使用系统帐号，应该尝试其他帐号，如Scott等）
create or replace trigger OneTrigger
before update of name
on stu
for each row
begin
if :new.name='null' then
  RAISE_APPLICATION_ERROR(-21000,'Error in number!');
end if;
end OneTrigger;
/

可以观察如下
update stu set name='null' where name='eric';

3、1、2 基本的语句块结构
所有的语句块都包含三个基本部分：声明部分、执行部分和异常处理部分，只有执行部分是必须的
基本框架为：
declare
begin
exception
end

3、2 词法单元
1）不区分大小写
2）必须以字母开头（不能以下划线开头），最长30个字符
3）保留字和空格不能使用，如要使用可以使用引证标识符（用双引号括起来表示，此时区分大小写，主要用途在于数据库表的列名称可能与保留字同名）
declare
"exception" varchar2(10):='eric';
begin
update stu set name='Eric' where name="exception";
end;
/
说明：如果关键字是某些字段等存储在数据字典中的信息，必须为大写形式，因为数据字典的信息都是大写，而在双引号中的字符串是区分大小写的，如：
select "EXCEPTION" from someTable;

尽量避免使用关键字，如果存在此类和关键字相同名称的字段，可以使用视图变换

4）文字
所谓文字，即是指不能成为标识符的字符、数字或者布尔值。
字符文字用单引号括起来，因此一个单引号的字符表示为''''，字符串''表示长度为0的字符串，与NULL等同。许多字符型文字可以隐式或者显式的转换为其他类型的文字。
数字型文字可以使用科学计数法（如-17.5E-2）。
布尔型文字有TRUE，FALSE，NULL三个值。

5）注释
--：表示单行注释
/*和*/：表示多行注释

3、3 变量声明
1）任何变量如果没有初始化，则自动被赋予NULL，而不是C等语言是不确定的值。
2）声明变量时指定NOT NULL会使得变量必须初始化
declare a varchar2(10) not null :='eric';
3）常量的定义为
declare a constant varchar2(10):='eric';
4）可以使用default来替换:=
declare a varchar2(10) default 'eric';
5）一行只能声明一个变量，下面的写法是错误的
declare a,b varchar2(10);
而应该写成
declare
a varchar2(10);
b varchar2(10);

3、4 数据类型
都在standard包中定义，可以在任何语句块中使用这些内容，同时这个包还提供了可以使用的内置SQL和转换函数
具体的数据类型有如下几种：标量、复合、引用、LOB和对象类型。

3、4、1 标量类型
与数据库列使用的类型基本相同，同时具有扩展。
可以细分为七种：数字、字符、原始型、日期/区间、行标识、布尔和受托。

1）数字类型
有NUMBER，PLS_INTEGER和BINARY_INTEGER（第一种可以存储整数和小数，后两种只能存储整数）

NUMBER：
用法为NUMBER(P,S)，P是精度，表示所有的数字位个数，S是刻度，表示小数点右边的数字位个数。
刻度如为负数，则表示向整数位四舍五入的进位。
不能只指定刻度而不指定精度
还要保证精度能够容纳数据。最大的精度为38，刻度从-84到127
NUMBER的子类型（相同类型但是数据类型名称不同，可以理解为重新名称的NUMBER数据类型，主要是为了增强可阅读性，或者为了与来自其他数据库的数据类型相兼容）有dec，decimal，double precision，float，numeric，real，integer，int，smallint
NUMBER类型以十进制来存储数据，进行了相应的存储优化，不能直接计算，在使用时，PL/SQL会自动转换成二进制类型，必要时还转换回来。

BINARY_INTEGER：
4个字节，仅用来计算，不用在数据库列中，它可以存储带符号的整数，以二进制存储，循环计数变量通常使用此类型。
BINARY_INTEGER的子类型有natual，naturaln，positive，positiven，signtype

PLS_INTEGER：
和BINARY_INTEGER很相似，也是二进制类型，但是发生溢出时会报错（而BINARY_INTEGER会自动指派一个拥有最大数值的NUMBER变量而不报错）

2）字符类型
包括char，varchar2，long，nchar，nvarchar2

varchar2：变长字符串
PL-SQL中的这种类型长度大于数据库中的这种类型，所以赋值时PL-SQL中的varchar2类型应该赋给LONG类型的数据列，
设置时必须指定长度，它的长度默认以字符为单位，也可以自定义单位，如：varchar2 (1 CHAR)
子类型varchar和string等价于此类型。
之所以使用varchar2是因为varchar2是Oracle在标准的varchar上自己定义的，这样将来即使标准的varchar发生了变化，它也不用改变

char：定长字符串
PL-SQL中的这种类型长度小于数据库中的这种类型，所以赋值时PL-SQL中的varchar2类型可以赋给LONG类型的数据列，
设置时可以指定长度，它的长度默认为1，计算以字符为单位，也可以自定义单位，如：char (1 CHAR)
character是char的子类型，功能等价

long：变长字符串，与varchar2相似，但是长度更大
PL-SQL中的这种类型长度小于数据库中的这种类型，所以赋值时PL-SQL中的long类型可以赋给相同类型的数据列，

char，varchar2基于ASCII码，而nchar，nvarchar2基于UNICODE码

3）原始类型
raw：类似于char类型，但是以二进制存储，以字节为单位，而且不在不同的字符集中互相转换
long raw：长度更大

4）日期/区间类型
date：存储七个字节，分别为世纪、年、月、日、小时、分钟、秒，不存储秒的小数位信息，占用7个字节，字符类型可以自动转换为此类型

timestamp：和date相似，还能指定秒的小数精度，with time zone可以指定时区，with local time zone可以存储数据库所在时区

interval：存储两个timestamp之间的时间间隔，如interval year(2) to month（2指年的位数）表示存储年和月的个数

5）行标识类型
rowid：为二进制信息，可以看成是数据库中每一行唯一的关键字值，包含文件号、块号和行号。
在数据库中，具有和此类型相同的伪列类型，如：select rowid from stu;

urowid：存储物理行标识符（上述为逻辑值）

6）布尔类型
具有true、false和null三个值

7）受托类型
MLSLABEL：存储可变长的二进制标签

3、4、2 复合类型
由标量变量组合而成的类型，有记录、表和数组。

3、4、3 引用类型
包含ref cursor和ref，通过引用类型可以释放对象变量空间（而标量变量不能，只能通过作用域来由系统释放）

3、4、4 LOB类型
适合大块的二进制数据，也可以保存字符

3、4、5 对象类型

3、4、6 说明
1）使用%type可以在运行时直接获取数据库表列的类型
declare sn stu.name%type;

注意：%type并不会自动获取原有字段的NOT NULL约束

2）用户定义类型
也被称为“子类型”，它是基于现有的类型生成的候选名

如
declare
subtype t is NUMBER;
b t;
如
declare
subtype t is stu.name%type;
b t;
如
declare
subtype t is NUMBER(4);
b t;
如
declare
v number(4);
subtype t is v%type;
b t;
如
declare
subtype t is NUMBER;
b t(4);

3）类型转换
显式的转换利用内置的转换函数（如TO_CHAR，TO_DATE等），隐式的转换可以自动进行（如字符和数字、字符和日期）

4）变量作用域和可见性
如果存在不同作用域的变量重名，区分的方式就是使用程序块的标号来限定
set serveroutput on size 1000000;

declare
a number(1);
begin
a:=1;
declare
  a number(1);
begin
  a:=2;
  dbms_output.put_line(a);
end;
dbms_output.put_line(a);
end;
/

使用程序块的标号来限定其他域的变量，如：
set serveroutput on size 1000000;

<<OnePro>>
declare
a number(1);
begin
a:=1;
declare
  a number(1);
begin
  a:=2;
  dbms_output.put_line(a);
  dbms_output.put_line(OnePro.a);
  OnePro.a:=2;
end;
dbms_output.put_line(a);
end;
/

3、5 表达式与运算符
注意几个问题：
1）使用:=进行数据赋值，但是不能连续赋值，如 a:=b:=c:=0;
2）**表示求幂
3）||表示字符串连接
4）AND、OR、NOT为逻辑运算符，如果和NULL计算结果不一定，如TRUE AND NULL为NULL，而TRUE OR NULL为TRUE
set serveroutput on size 1000000;

declare
   b boolean;
begin
   b:=(true and null);
   if b is null then
      dbms_output.put_line('Hello!');
   end if;
end;
/

5）LIKE运算符和SQL中的使用方法很象，如%表示多个字符，_表示一个字符
6）BETWEEN表示范围，如100 BETWEEN 1 AND 200为TRUE
7）IN运算符和SQL中的集合使用方法很象
8）NULL本身可以成为一个语句，不做任何事情，只是一个占位符，如NULL;

3、6 控制语句
3、6、1 if分支语句
注意elseif写为elsif
set serveroutput on size 1000000;

declare
  v_sid number(3);
begin
  select sid into v_sid from stu where name='Tom';
  if v_sid = 1 then
    dbms_output.put_line('=1');
  elsif v_sid > 1 then
    dbms_output.put_line('>1');
  else
    dbms_output.put_line('other');
  end if;
end;
/

此语句只有一个可以被执行，即使后面还有满足的条件
set serveroutput on size 1000000;

declare
  v_sid number(3);
begin
  select sid into v_sid from stu where name='Tom';
  if v_sid = 1 then
    dbms_output.put_line('=1');
  elsif v_sid > 1 then
    dbms_output.put_line('>1');
  elsif v_sid = 1 then
    dbms_output.put_line('other');
  end if;
end;
/

如果变量为NULL，判断结果也是为false
set serveroutput on size 1000000;

declare
  v_sid number(3);
begin
  --select sid into v_sid from stu where name='Tom';
  if v_sid = 1 then
    dbms_output.put_line('=1');
  elsif v_sid > 1 then
    dbms_output.put_line('>1');
  else
    dbms_output.put_line('other');
  end if;
end;
/

观察下面的输出，看似等价，其实输出不一样，如：
set serveroutput on size 1000000;

declare
   v_number1 number;
   v_number2 number;
begin
   if v_number1>v_number2 then
      dbms_output.put_line('>');
   else
      dbms_output.put_line('<=');
   end if;
end;
/

set serveroutput on size 1000000;

declare
   v_number1 number;
   v_number2 number;
begin
   if v_number1<=v_number2 then
      dbms_output.put_line('<=');
   else
      dbms_output.put_line('>');
   end if;
end;
/

3、6、2 case分支语句
set serveroutput on size 1000000;

declare
  v_sid number(3);
begin
  select sid into v_sid from stu where name='Tom';
  case v_sid
    when 1 then
      dbms_output.put_line('=1');
    when 2 then
      dbms_output.put_line('=2');
    else
      dbms_output.put_line('other');
  end case;
end;
/

注意：
此语句也只有一个可以被执行，即使后面还有满足的条件，也不要加break
如果没有else，没有匹配成功会发出错误CASE_NOT_FOUND，即相当于加了个语句：else raise CASE_NOT_FOUND;

case语句可以使用标号
set serveroutput on size 1000000;

declare
  v_sid number(3);
begin
  select sid into v_sid from stu where name='Tom';
  <<MyCase>>
  case v_sid
    when 1 then
      dbms_output.put_line('=1');
    when 2 then
      dbms_output.put_line('=2');
    else
      dbms_output.put_line('other');
  end case MyCase;
end;
/

可以将检测式换成搜索式，具有更为灵活的条件判断，如：
set serveroutput on size 1000000;

declare
  v_sid number(3);
begin
  select sid into v_sid from stu where name='Tom';
  case  
    when v_sid<1 then
      dbms_output.put_line('<1');
    when v_sid>1 then
      dbms_output.put_line('>1');
    else
      dbms_output.put_line('other');
  end case;
end;
/

3、6、3 循环语句
利用while实现连续数字的输出，如：
set serveroutput on size 1000000;

declare
  v_counter BINARY_INTEGER:=1;
begin
  while v_counter<=10 loop
    dbms_output.put_line(v_counter);
    v_counter:=v_counter+1;
  end loop;
end;
/

while也可以使用exit和exit when退出循环
set serveroutput on size 1000000;

declare
  v_counter BINARY_INTEGER:=1;
begin
  while true loop
    dbms_output.put_line(v_counter);
    v_counter:=v_counter+1;
    exit when v_counter>10;
  end loop;
end;
/

也可以写成无条件循环形式（注意退出循环的语句），如：
set serveroutput on size 1000000;

declare
  v_counter BINARY_INTEGER:=1;
begin
  loop
    dbms_output.put_line(v_counter);
    v_counter:=v_counter+1;
   if v_counter>10 then
     exit;
   end if;
  end loop;
end;
/

上述语句可以写成专门的退出循环语句exit when，如：
set serveroutput on size 1000000;

declare
  v_counter BINARY_INTEGER:=1;
begin
  loop
    dbms_output.put_line(v_counter);
    v_counter:=v_counter+1;
    exit when v_counter>10;
  end loop;
end;
/

可以使用for语句实现（此时无需声明循环索引变量，系统隐式声明，类型为BINARY_INTEGER）
set serveroutput on size 1000000;

begin
  for v_counter in 1..10 loop
    dbms_output.put_line(v_counter);    
  end loop;
end;
/
注意：此时的v_counter的作用域属于for循环本身，不能直接赋值，如：
set serveroutput on size 1000000;

begin
  for v_counter in 1..10 loop
    v_counter:=v_counter+1;
    dbms_output.put_line(v_counter);    
  end loop;
end;
/

这种循环变量无需声明，如果声明，系统隐式声明的循环索引变量反而会隐藏外部的同名变量，如：
set serveroutput on size 1000000;

declare
  v_counter BINARY_INTEGER:=1;
begin
  for v_counter in 1..10 loop
    dbms_output.put_line(v_counter);    
  end loop;
  dbms_output.put_line(v_counter);  
end;
/

使用REVERSE会倒序循环（其他循环体内容无需改动，尤其是上下界）
set serveroutput on size 1000000;

begin
  for v_counter in reverse 1..10 loop
    dbms_output.put_line(v_counter);    
  end loop;
end;
/

循环范围可以使用表达式和变量，产生动态边界效果
set serveroutput on size 1000000;

declare
  v_min NUMBER:=1;
  v_max NUMBER:=10;
begin
  for v_counter in v_min..v_max loop
    dbms_output.put_line(v_counter);    
  end loop;
  dbms_output.put_line(v_counter);  
end;
/

利用goto语句也能循环（但是Goto尽量不要使用，而且也有局限性，如不能从外层向内层语句块跳转，从一个if子句跳向另一个子句，从异常处理语句块跳转到当前语句块）
set serveroutput on size 1000000;

declare
  v_counter BINARY_INTEGER:=1;
begin
  <<startprint>>
  dbms_output.put_line(v_counter);
  v_counter:=v_counter+1;
  if v_counter<=10 then
    goto startprint;
  end if;
end;
/

再如在上述的循环退出中也可以使用goto语句（注意null语句不能省略），如：
set serveroutput on size 1000000;

declare
  v_counter BINARY_INTEGER:=1;
begin
  loop
    dbms_output.put_line(v_counter);
    v_counter:=v_counter+1;
   if v_counter>10 then
     goto endprint;
   end if;
  end loop;
  
  <<endprint>>
  null;
end;
/

循环可以使用标号，灵活控制退出的层次
set serveroutput on size 1000000;

begin
  <<outer_for>>
  for v_counter1 in 1..10 loop
    dbms_output.put_line(v_counter1);
    dbms_output.put_line('-------------------');    
    for v_counter2 in 1..v_counter1 loop
      dbms_output.put_line(v_counter2);
      if v_counter1+v_counter2=5 then
        exit outer_for;
      end if;  
    end loop;
    dbms_output.put_line('-------------------');  
  end loop;
end;
/

3、7 记录
记录相当于C语言中的结构体
set serveroutput on size 1000000;

declare
  TYPE stutype IS RECORD
  (
    SID NUMBER(38),
    NAME VARCHAR2(8),
    SEX CHAR(1)
  );
  s1 stutype;
  s2 stutype;
begin
  s1.name:='Tom';
  s2.sid:=2;
end;
/

记录之间可以相互赋值，如：
set serveroutput on size 1000000;

declare
  TYPE stutype IS RECORD
  (
    SID NUMBER(38),
    NAME VARCHAR2(8),
    SEX CHAR(1)
  );
  s1 stutype;
  s2 stutype;
begin
  s1.name:='Tom';
  s2:=s1;
  dbms_output.put_line(s2.name);
end;
/

注意：
赋值必须是相同类型，即便是内容相同但是名称不同的记录也是不能相互赋值的，若想赋值，只能采用复制单条记录字段值的方法来进行

记录可以直接从数据库中获取数据并保存，如：
set serveroutput on size 1000000;

declare
  TYPE stutype IS RECORD
  (
    SID NUMBER(38),
    NAME VARCHAR2(8),
    SEX CHAR(1)
  );
  s stutype;
begin
  select * into s from stu where sid=1;
  dbms_output.put_line(s.sid);
  dbms_output.put_line(s.name);
  dbms_output.put_line(s.sex);
end;
/

使用%ROWTYPE运算符更为简单，自动获取数据库表的结构信息生成记录（约束不会添加进来）
set serveroutput on size 1000000;

declare
  s stu%ROWTYPE;
begin
  select * into s from stu where sid=1;
  dbms_output.put_line(s.sid);
  dbms_output.put_line(s.name);
  dbms_output.put_line(s.sex);
end;
/