以文本方式查看主题

-  课外天地 李树青  (http://www.njcie.com/bbs/index.asp)
--  数据库系统原理课件  (http://www.njcie.com/bbs/list.asp?boardid=19)
----  [推荐]Oracle数据库PL-SQL学习资料之一——基础  (http://www.njcie.com/bbs/dispbbs.asp?boardid=19&id=602)

--  作者:admin
--  发布时间:2008/11/21 19:09:58
--  [推荐]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;
/

[此贴子已经被作者于2010-12-11 20:00:23编辑过]