PL/SQL实例分析

PL/SQL实例分析

第五章
1、PL/SQL实例分析
1）在【SQLPlus Worksheet】中直接执行如下SQL代码完成上述操作。(创建表)
―――――――――――――――――――――――――――――――
  
CREATE TABLE "SCOTT"."TESTTABLE" ("RECORDNUMBER" NUMBER(4) NOT NULL, "CURRENTDATE" DATE NOT NULL) 
TABLESPACE "SYSTEM"


2）以admin用户身份登录【SQLPlus Worksheet】，执行下列SQL代码完成向数据表SYSTEM.testable中输入100个记录的功能。
―――――――――――――――――――――――――――――――
set serveroutput on
declare
  maxrecords constant int:=100;
  i int:=1;
begin
  for i in 1..maxrecords loop
    insert into SCOTT.testtable(recordnumber,currentdate)
    values(i,sysdate);
  end loop;
  dbms_output.put_line('成功录入数据！');
  commit;
end;




2、在【SQLPlus Worksheet】中执行下列PL/SQL程序，该程序定义了名为age的数字型变量，长度为3，初始值为26。
―――――――――――――――――――――――――――――――
declare
  age number(3):=26;
begin
  commit;
end;

3、在【SQLPlus Worksheet】中执行下列PL/SQL程序，该程序定义了名为pi的数字型常量，长度为9。
―――――――――――――――――――――――――――――――
declare
  pi constant number(9):=3.1415926;
begin
  commit;
end;


4、复合数据类型变量
下面介绍常见的几种复合数据类型变量的定义。
1). 使用%type定义变量
为了让PL/SQL中变量的类型和数据表中的字段的数据类型一致，Oracle 9i提供了%type定义方法。这样当数据表的字段类型修改后，PL/SQL程序中相应变量的类型也自动修改。

在【SQLPlus Worksheet】中执行下列PL/SQL程序，该程序定义了名为mydate的变量，其类型和tempuser.testtable数据表中的currentdate字段类型是一致的。
―――――――――――――――――――――――――――――――
Declare
  mydate SYSTEM.testtable.currentdate%type;
begin
  commit;
end;

2). 定义记录类型变量
很多结构化程序设计语言都提供了记录类型的数据类型，在PL/SQL中，也支持将多个基本数据类型捆绑在一起的记录数据类型。
下面的程序代码定义了名为myrecord的记录类型，该记录类型由整数型的myrecordnumber和日期型的mycurrentdate基本类型变量组成，srecord是该类型的变量，引用记录型变量的方法是“记录变量名.基本类型变量名”。
程序的执行部分从tempuser.testtable数据表中提取recordnumber字段为68的记录的内容，存放在srecord复合变量里，然后输出srecord.mycurrentdate的值，实际上就是数据表中相应记录的currentdate的值。
在【SQLPlus Worksheet】中执行下列PL/SQL程序
―――――――――――――――――――――――――――――――
set serveroutput on
declare
  type myrecord is record(myrecordnumber int,mycurrentdate date);
  srecord myrecord;
begin
  select * into srecord from SYSTEM.testtable where recordnumber=68;
  dbms_output.put_line(srecord.mycurrentdate);
end;


3). 使用%rowtype定义变量
使用%type可以使变量获得字段的数据类型，使用%rowtype可以使变量获得整个记录的数据类型。比较两者定义的不同：变量名 数据表.列名%type，变量名 数据表%rowtype。
在【SQLPlus Worksheet】中执行下列PL/SQL程序，该程序定义了名为mytable的复合类型变量，与testtable数据表结构相同.
―――――――――――――――――――――――――――――――
Declare
  mytable SYSTEM.testtable%rowtype;
begin
  select * into mytable from SYSTEM.testtable where recordnumber=89;
  dbms_output.put_line(mytable.currentdate);
end;



4). 定义一维表类型变量
表类型变量和数据表是有区别的，定义表类型变量的语法如下：
―――――――――――――――――――――――――――――――
type 表类型 is table of 类型 index by binary_integer;
表变量名 表类型;
―――――――――――――――――――――――――――――――
类型可以是前面的类型定义，index by binary_integer子句代表以符号整数为索引，这样访问表类型变量中的数据方法就是“表变量名(索引符号整数)”。
在【SQLPlus Worksheet】中执行下列PL/SQL程序，该程序定义了名为tabletype1和tabletype2的两个一维表类型，相当于一维数组。table1和table2分别是两种表类型变量。
―――――――――――――――――――――――――――――――
Declare
  type tabletype1 is table of varchar2(4) index by binary_integer;
  type tabletype2 is table of SYSTEM.testtable.recordnumber%type  index by binary_integer;
  table1 tabletype1;
  table2 tabletype2;
begin
  table1(1):='大学';
  table1(2):='大专';
  table2(1):=88;
  table2(2):=55;
  dbms_output.put_line(table1(1)||table2(1));
  dbms_output.put_line(table1(2)||table2(2));
end;
执行结果如下所示。
―――――――――――――――――――――――――――――――
大学88
大专55

PL/SQL 过程已成功完成。




二、 表达式
在PL/SQL中常见表达式的运算规则:
1. 数值表达式
PL/SQL程序中的数值表达式是由数值型常数、变量、函数和算术运算符组成的，可以使用的算术运算符包括+（加法）、-（减法）、*（乘法）、/（除法）和**（乘方）等。
在【SQLPlus Worksheet】中执行下列PL/SQL程序，该程序定义了名为result的整数型变量，
计算的是10+3*4-20+5**2的值。
注意:dbms_output.put_line函数输出只能是字符串，因此利用to_char函数将数值型结果转换为字符型。
―――――――――――――――――――――――――――――――
set serveroutput on
  Declare
  result integer;
begin
  result:=10+3*4-20+5**2;
  dbms_output.put_line('运算结果是：'||to_char(result));
end;
―――――――――――――――――――――――――――――――
执行结果如下所示。
运算结果是：27

PL/SQL 过程已成功完成。





三、 流程控制
PL/SQL程序中的流程控制语句借鉴了许多高级语言的流程控制思想，但又有自己的特点。
（一）条件控制
1. if..then..end if条件控制
采用if..then..end if条件控制的语法结构如:

if 条件 then
语句段;
end if;

 if..then..end if条件控制语法结构
在【SQLPlus Worksheet】中执行下列PL/SQL程序，该程序判断两个整数变量的大小。

―――――――――――――――――――――――――――――――
set serveroutput on
declare
  number1 integer:=90;
  number2 integer:=60;
begin
  if number1>=number2 then
     dbms_output.put_line('number1大于等于number2');
  end if;
end;
―――――――――――――――――――――――――――――――
执行结果:
number1大于等于number2

PL/SQL 过程已成功完成。




2. if..then..else..end if条件控制
采用if..then..else..end if条件控制的语法结构:

if 条件 then
语句段1;
else
语句段2;
end if;
 
在【SQLPlus Worksheet】中执行下列PL/SQL程序，该程序判断输出不同的结果。
―――――――――――――――――――――――――――――――
set serveroutput on
declare
  number1 integer:=80;
  number2 integer:=90;
begin
  if number1>=number2 then
    dbms_output.put_line('number1大于等于number2');
  else
    dbms_output.put_line('number1小于number2');
  end if;
end;

―――――――――――――――――――――――――――――――
执行结果:
number1大于等于number2

PL/SQL 过程已成功完成。


3. if嵌套条件控制
采用if嵌套条件控制的语法结构如:

if 条件1 then
  if 条件2 then
    嵌套的条件控制语句
    语句段1;
  else
    语句段2;
  end if;
else
  语句段3;
end if;

在【SQLPlus Worksheet】中执行下列PL/SQL程序，该程序判断两个整数变量的大小，输出不同的结果。
―――――――――――――――――――――――――――――――
set serveroutput on
declare
  number1 integer:=110;
  number2 integer:=90;
begin
  if number1<=number2 then
    if number1=number2 then
      dbms_output.put_line('number1等于number2');
    else
      dbms_output.put_line('number1小于number2');
    end if;
  else
    dbms_output.put_line('number1大于number2');
  end if;
end;
―――――――――――――――――――――――――――――――执行结果:?

(二) 循环控制
循环结构是按照一定逻辑条件执行一组命令，PL/SQL中有4种基本循环结构:

1. loop..exit..end loop循环控制
采用loop..exit..end loop循环控制的语法结构如下所示：

loop
循环语句段；
if 条件语句 then
  exit;
else
  退出循环的处理语句段；
end if;
end loop;
 
在【SQLPlus Worksheet】中执行下列PL/SQL程序，该程序将number1变量每次加1，一直到等于number2为止，统计输出循环次数。
―――――――――――――――――――――――――――――――
set serveroutput on
declare
   number1 integer:=80;
   number2 integer:=90;
   i integer:=0;
begin
  loop
    number1:=number1+1;
    if number1=number2 then
      exit;
    else
       i:=i+1;
    end if;
  end loop;
  dbms_output.put_line('共循环次数:'||to_char(i));
end;
―――――――――――――――――――――――――――――――
执行结果：？



习题：已知执行结果如下：
―――――――――――――――――――――――――――――――
变量number1为:101
变量number1为:102
变量number1为:103
变量number1为:104
变量number1为:105
变量number1为:106
变量number1为:107
共循环次数:7

PL/SQL 过程已成功完成。
―――――――――――――――――――――――――――――――如何修改PL/SQL程序：？

set serveroutput on
declare
    number1 integer:=100;
    number2 integer:=108;
    i integer:=0;
begin
  loop
    number1:=number1+1;
    if number1=number2 then
      exit;
    else
      dbms_output.put_line('变量number1为:'||to_char(number1));
       i:=i+1;
    end if;
  end loop;
  dbms_output.put_line('共循环次数:'||to_char(i));
end;
―――――――――――――――――――――――――――――――


2. loop..exit..when..end loop循环控制
采用loop..exit..when..end loop循环控制的语法结构与上例结构类似。
exit when实际上就相当于
if 条件 then
exit;
end if;
在【SQLPlus Worksheet】中执行下列PL/SQL程序，该程序将number1变量每次加1，直到等于number2为止，统计输出循环次数。
―――――――――――――――――――――――――――――――
set serveroutput on
declare
   number1 integer:=80;
   number2 integer:=90;
   i integer:=0;
begin
  loop
    number1:=number1+1;
    i:=i+1;
   exit when number1=number2;
   end loop;
   dbms_output.put_line('共循环次数:'||to_char(i));
end;
―――――――――――――――――――――――――――――――
执行结果？


3. while..loop..end loop循环控制
采用loop..exit..when..end loop循环控制的语法如下：

while 条件 loop
执行语句段;
end loop;

在【SQLPlus Worksheet】中执行下列PL/SQL程序，该程序将number1变量每次加1，一直到等于number2为止，统计输出循环次数。
―――――――――――――――――――――――――――――――――――――
set serveroutput on
declare
  number1 integer:=80;
  number2 integer:=90;
  i integer:=0;
begin
  while number1<number2 loop
    number1:=number1+1;
    i:=i+1;
  end loop;
  dbms_output.put_line('共循环次数:'||to_char(i));
end;
―――――――――――――――――――――――――――――――――――――



4. for..in..loop..end循环控制
采用for..in..loop..end循环控制的语法如下：

for 循环变量 in [reverse] 循环下界..循环上界 loop
循环处理语句段;
end loop;

在【SQLPlus Worksheet】中执行下列PL/SQL程序，该程序通过循环变量I来控制number1增加次数，输出结果。
―――――――――――――――――――――――――――――――――――――
set serveroutput on
declare
  number1 integer:=80;
  number2 integer:=90;
  i integer:=0;
begin
  for i in 1..10 loop
    number1:=number1+1;
    end loop;
    dbms_output.put_line('number1的值:'||to_char(number1));
  end;
―――――――――――――――――――――――――――――――――――――
执行结果？


用SQL进行函数查询 

Oracle 9i提供了很多函数可以用来辅助数据查询。接下来我们介绍常用的函数功能及使
用方法。 

5.5.1 【ceil】函数 

【ceil】函数用法：ceil（n），取大于等于数值n的最小整数。 
在【命令编辑区】输入“select mgr, mgr/100,ceil(mgr/100) from scott.emp;”，然后单击【执
行】按钮，出现结果?




5.5.2 【floor】函数 

【floor】函数用法：floor（n），取小于等于数值n的最大整数。
 
在【命令编辑区】输入“select mgr, mgr/100,floor(mgr/100) from scott.emp;”，然后单击【执
行】按钮，出现结果?


5.5.3 【mod】函数 

【mod】函数用法：mod（m,n），取m整除n后的余数。 
在【命令编辑区】输入“select mgr, mod(mgr,1000), mod(mgr,100), mod(mgr,10) 
from scott.emp;”，然后单击【执行】按钮，出现结果? 

 


5.5.4 【power】函数 
【power】函数用法：power（m,n），取m的n次方。 

在【命令编辑区】输入“select mgr, power(mgr,2),power(mgr,3) from scott.emp;”，然后单
击【执行】按钮，出现结果? 


5.5.5 【round】函数 

【round】函数用法：round（m,n），四舍五入，保留n位。 在【命令编辑区】输入“select mgr, round(mgr/100,2),round(mgr/1000,2) from scott.emp;”，
然后单击【执行】按钮，出现结果? 





5.5.6 【sign】函数 
【sign】函数用法：sign（n）。n>0，取1；n=0，取0；n<0，取-1。 
在【命令编辑区】输入“select mgr, mgr-7800,sign(mgr-7800) from scott.emp;”，然后单击
【执行】按钮，出现结果? 



5.5.7 【avg】函数 

【avg】函数用法：avg（字段名），求平均值。要求字段为数值型。 
在【命令编辑区】输入“select avg(mgr) 平均薪水 from scott.emp;”，然后单击【执行】
按钮，出现结果? 
 
5.5.8 【count】函数 

（1）在【命令编辑区】输入“select count(*) 记录总数 from scott.emp;”，然后单击【执
行】按钮，出现结果? 

【count(*)】函数的使用 

（2）在【命令编辑区】输入“select count(distinct job ) 工作类别总数 from scott.emp;”，
然后单击【执行】按钮，出现结果? 

 【count(字段名) 】函数的使用 

【count】函数用法：count（字段名）或count（*），统计总数。 

5.5.9 【min】函数 

在【命令编辑区】输入“select min(sal) 最少薪水 from scott.emp;”，然后单击【执行】
按钮，出现结果? 

【min】函数用法：min（字段名），计算数值型字段最小数。 

5.5.10 【max】函数 

在【命令编辑区】输入“select max(sal) 最高薪水 from scott.emp;”，然后单击【执行】
按钮，出现结果? 
图4.39 【max】函数的使用 

【max】函数用法：max（字段名），计算数值型字段最大数。 

5.5.11 【sum】函数 

在【命令编辑区】输入“select sum(sal) 薪水总和 from scott.emp;”，然后单击【执行】
按钮，出现结果? 

【sum】函数用法：sum（字段名），计算数值型字段总和。 













 5.7游标 

游标是从数据表中提取出来的数据，以临时表的形式存放在内存中，在游标中有一个数
据指针，在初始状态下指向的是首记录，利用fetch语句可以移动该指针，从而对游标中的数
据进行各种操作，然后将操作结果写回数据表中。 

1 定义游标 

游标作为一种数据类型，首先必须进行定义，其语法如下。 
cursor 游标名 is select 语句; 
cursor是定义游标的关键词，select是建立游标的数据表查询命令。 

以scott用户连接数据库，在【SQLPlus Worksheet】中执行下列PL/SQL程序，该程序定
义tempsal为与scott.emps数据表中的sal字段类型相同的变量，mycursor为从scott.emp数据
表中提取的sal大于tempsal的数据构成的游标。 

――――――――――――――――――――――――――――――――――――― 

set serveroutput on 
declare 
 tempsal scott.emp.sal%type; 
 cursor mycursor is 
 select * from scott.emp 
 where sal>tempsal; 
begin 
 tempsal:=800; 
 open mycursor; 
end; 

――――――――――――――――――――――――――――――――――――― 
执行结果? 


2 打开游标 

要使用创建好的游标，接下来要打开游标，语法结构如下： 

open 游标名; 

打开游标的过程有以下两个步骤： 

（1）将符合条件的记录送入内存。 

（2）将指针指向第一条记录。 

3 提取游标数据 

要提取游标中的数据，使用fetch命令，语法形式如下。 

fetch 游标名 into 变量名1, 变量名2,……; 

或 fetch 游标名 into 记录型变量名; 

在【SQLPlus Worksheet】中执行下列PL/SQL程序，该程序定义cursorrecord变量是游
标mycursor的记录行变量，在游标mycursor的结果中找到sal字段大于800的第一个记录，
显示deptno字段的内容。 



提取游标数据 
――――――――――――――――――――――――――――――――――――― 

set serveroutput on 
declare 
 tempsal scott.emp.sal%type; 
 cursor mycursor is 
 select * from scott.emp 
 where sal>tempsal; 
 cursorrecord mycursor%rowtype; 
begin 
 tempsal:=3000; 
 Open mycursor; 
 fetch mycursor into cursorrecord; 
 dbms_output.put_line(to_char('NAME:'||cursorrecord.ENAME||',deptno:'||cursorrecord.deptno)); 
end; 

――――――――――――――――――――――――――――――――――――― 
执行结果?
NAME:KING,deptno:10



4 关闭游标 
使用完游标后，要关闭游标，使用close命令，语法形式如下： 
close 游标名; 

5 游标的属性 
游标提供的一些属性可以帮助编写PL/SQL程序，游标属性的使用方法为：游标名[属性]，
例如mycursor%isopen，主要的游标属性如下。 
1. %isopen属性 
该属性功能是测试游标是否打开，如果没有打开游标就使用fetch语句将提示错误。 

在【SQLPlus Worksheet】中执行下列PL/SQL程序，该程序利用%isopen属性判断游标
是否打开。执行结果?

――――――――――――――――――――――――――――――――――――― 

set serveroutput on 
declare 
 tempsal scott.emp.sal%type; 
 cursor mycursor is 
    select * from scott.emp 
    where sal>tempsal; 
 cursorrecord mycursor%rowtype; 
begin 
 tempsal:=800; 
 if mycursor%isopen then 
     fetch mycursor into cursorrecord; 
     dbms_output.put_line(to_char(cursorrecord.deptno)); 
 else 
     dbms_output.put_line('游标没有打开！'); 
 end if; 
end; 

――――――――――――――――――――――――――――――――――――― 
游标没有打开！
 

2. %found属性 

该属性功能是测试前一个fetch语句是否有值，有值将返回true，否则为false。 

在【SQLPlus Worksheet】中执行下列PL/SQL程序。该程序利用%found属性判断游标是
否有数据。 

执行结果?

――――――――――――――――――――――――――――――――――――― 

set serveroutput on 
declare 
 tempsal scott.emp.sal%type; 
 cursor mycursor is 
    select * from scott.emp 
    where sal>tempsal; 
 cursorrecord mycursor%rowtype; 
begin 
 tempsal:=800; 
 open mycursor; 
 fetch mycursor into cursorrecord; 
 if mycursor%found then 
     dbms_output.put_line(to_char(cursorrecord.deptno)); 
 else 
     dbms_output.put_line('没有数据！'); 
 end if; 
end; 

――――――――――――――――――――――――――――――――――――― 
30


3. %notfound属性 

该属性是%found属性的反逻辑，常被用于退出循环。 

在【SQLPlus Worksheet】中执行下列PL/SQL程序。该程序利用%notfound属性判断游
标是否没有数据。 

执行结果?
发现数据！
――――――――――――――――――――――――――――――――――――― 

set serveroutput on 
declare 
 tempsal scott.emp.sal%type; 
 cursor mycursor is 
   select * from scott.emp 
   where sal>tempsal; 
 cursorrecord mycursor%rowtype; 
begin 
 tempsal:=800; 
 open mycursor; 
 fetch mycursor into cursorrecord; 
 if mycursor%notfound then 
     dbms_output.put_line(to_char(cursorrecord.deptno)); 
 else 
     dbms_output.put_line('发现数据！'); 
 end if; 
end; 

――――――――――――――――――――――――――――――――――――― 




4. %rowcount属性 

该属性用于返回游标的数据行数。 

在SQLPlus Worksheet的【代码编辑区】执行下列PL/SQL程序，该程序利用%rowcount
属性判断游标数据行数。 

执行结果? 

――――――――――――――――――――――――――――――――――――― 

Set serveroutput on 
declare 
 tempsal scott.emp.sal%type; 
 cursor mycursor is 
 select * from scott.emp 
 where sal>tempsal; 
 cursorrecord mycursor%rowtype; 
begin 
 tempsal:=800; 
 open mycursor; 
 fetch mycursor into cursorrecord; 
 dbms_output.put_line(to_char(mycursor%rowcount)); 
end; 

――――――――――――――――――――――――――――――――――――― 
 1

from：http://yjs.ntu.edu.cn/uploads/ORACLE5.txt