plsql 文章

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; --[color=red]constant 是常量的[/color]
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


本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/aicon/archive/2010/04/21/5511454.aspx