plsql 文章
CLASSICHUO 2010-06-29
SQL实例分析
第五章
1、PL/SQL实例分析
1）在【SQLPlusWorksheet】中直接执行如下SQL代码完成上述操作。(创建表)
―――――――――――――――――――――――――――――――
CREATETABLE"SCOTT"."TESTTABLE"("RECORDNUMBER"NUMBER(4)NOTNULL,"CURRENTDATE"DATENOTNULL)
TABLESPACE"SYSTEM"
2）以admin用户身份登录【SQLPlusWorksheet】，执行下列SQL代码完成向数据表SYSTEM.testable中输入100个记录的功能。
―――――――――――――――――――――――――――――――
setserveroutputon
declare
maxrecordsconstantint:=100;--constant是常量的
iint:=1;
begin
foriin1..maxrecordsloop
insertintoSCOTT.testtable(recordnumber,currentdate)
values(i,sysdate);
endloop;
dbms_output.put_line('成功录入数据！');
commit;
end;
2、在【SQLPlusWorksheet】中执行下列PL/SQL程序，该程序定义了名为age的数字型变量，长度为3，初始值为26。
―――――――――――――――――――――――――――――――
declare
agenumber(3):=26;
begin
commit;
end;
3、在【SQLPlusWorksheet】中执行下列PL/SQL程序，该程序定义了名为pi的数字型常量，长度为9。
―――――――――――――――――――――――――――――――
declare
piconstantnumber(9):=3.1415926;
begin
commit;
end;
4、复合数据类型变量
下面介绍常见的几种复合数据类型变量的定义。
1).使用%type定义变量
为了让PL/SQL中变量的类型和数据表中的字段的数据类型一致，Oracle9i提供了%type定义方法。这样当数据表的字段类型修改后，PL/SQL程序中相应变量的类型也自动修改。
在【SQLPlusWorksheet】中执行下列PL/SQL程序，该程序定义了名为mydate的变量，其类型和tempuser.testtable数据表中的currentdate字段类型是一致的。
―――――――――――――――――――――――――――――――
Declare
mydateSYSTEM.testtable.currentdate%type;
begin
commit;
end;
2).定义记录类型变量
很多结构化程序设计语言都提供了记录类型的数据类型，在PL/SQL中，也支持将多个基本数据类型捆绑在一起的记录数据类型。
下面的程序代码定义了名为myrecord的记录类型，该记录类型由整数型的myrecordnumber和日期型的mycurrentdate基本类型变量组成，srecord是该类型的变量，引用记录型变量的方法是“记录变量名.基本类型变量名”。
程序的执行部分从tempuser.testtable数据表中提取recordnumber字段为68的记录的内容，存放在srecord复合变量里，然后输出srecord.mycurrentdate的值，实际上就是数据表中相应记录的currentdate的值。
在【SQLPlusWorksheet】中执行下列PL/SQL程序
―――――――――――――――――――――――――――――――
setserveroutputon
declare
typemyrecordisrecord(myrecordnumberint,mycurrentdatedate);
srecordmyrecord;
begin
select*intosrecordfromSYSTEM.testtablewhererecordnumber=68;
dbms_output.put_line(srecord.mycurrentdate);
end;
3).使用%rowtype定义变量
使用%type可以使变量获得字段的数据类型，使用%rowtype可以使变量获得整个记录的数据类型。比较两者定义的不同：变量名数据表.列名%type，变量名数据表%rowtype。
在【SQLPlusWorksheet】中执行下列PL/SQL程序，该程序定义了名为mytable的复合类型变量，与testtable数据表结构相同.
―――――――――――――――――――――――――――――――
Declare
mytableSYSTEM.testtable%rowtype;
begin
select*intomytablefromSYSTEM.testtablewhererecordnumber=89;
dbms_output.put_line(mytable.currentdate);
end;
4).定义一维表类型变量
表类型变量和数据表是有区别的，定义表类型变量的语法如下：
―――――――――――――――――――――――――――――――
type表类型istableof类型indexbybinary_integer;
表变量名表类型;
―――――――――――――――――――――――――――――――
类型可以是前面的类型定义，indexbybinary_integer子句代表以符号整数为索引，这样访问表类型变量中的数据方法就是“表变量名(索引符号整数)”。
在【SQLPlusWorksheet】中执行下列PL/SQL程序，该程序定义了名为tabletype1和tabletype2的两个一维表类型，相当于一维数组。table1和table2分别是两种表类型变量。
―――――――――――――――――――――――――――――――
Declare
typetabletype1istableofvarchar2(4)indexbybinary_integer;
typetabletype2istableofSYSTEM.testtable.recordnumber%typeindexbybinary_integer;
table1tabletype1;
table2tabletype2;
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程序中的数值表达式是由数值型常数、变量、函数和算术运算符组成的，可以使用的算术运算符包括+（加法）、-（减法）、*（乘法）、/（除法）和**（乘方）等。
在【SQLPlusWorksheet】中执行下列PL/SQL程序，该程序定义了名为result的整数型变量，
计算的是10+3*4-20+5**2的值。
注意:dbms_output.put_line函数输出只能是字符串，因此利用to_char函数将数值型结果转换为字符型。
―――――――――――――――――――――――――――――――
setserveroutputon
Declare
resultinteger;
begin
result:=10+3*4-20+5**2;
dbms_output.put_line('运算结果是：'||to_char(result));
end;
―――――――――――――――――――――――――――――――
执行结果如下所示。
运算结果是：27
PL/SQL过程已成功完成。
三、流程控制
PL/SQL程序中的流程控制语句借鉴了许多高级语言的流程控制思想，但又有自己的特点。
（一）条件控制
1.if..then..endif条件控制
采用if..then..endif条件控制的语法结构如:
if条件then
语句段;
endif;
if..then..endif条件控制语法结构
在【SQLPlusWorksheet】中执行下列PL/SQL程序，该程序判断两个整数变量的大小。
―――――――――――――――――――――――――――――――
setserveroutputon
declare
number1integer:=90;
number2integer:=60;
begin
ifnumber1>=number2then
dbms_output.put_line('number1大于等于number2');
endif;
end;
―――――――――――――――――――――――――――――――
执行结果:
number1大于等于number2
PL/SQL过程已成功完成。
2.if..then..else..endif条件控制
采用if..then..else..endif条件控制的语法结构:
if条件then
语句段1;
else
语句段2;
endif;
在【SQLPlusWorksheet】中执行下列PL/SQL程序，该程序判断输出不同的结果。
―――――――――――――――――――――――――――――――
setserveroutputon
declare
number1integer:=80;
number2integer:=90;
begin
ifnumber1>=number2then
dbms_output.put_line('number1大于等于number2');
else
dbms_output.put_line('number1小于number2');
endif;
end;
―――――――――――――――――――――――――――――――
执行结果:
number1大于等于number2
PL/SQL过程已成功完成。
3.if嵌套条件控制
采用if嵌套条件控制的语法结构如:
if条件1then
if条件2then
嵌套的条件控制语句
语句段1;
else
语句段2;
endif;
else
语句段3;
endif;
在【SQLPlusWorksheet】中执行下列PL/SQL程序，该程序判断两个整数变量的大小，输出不同的结果。
―――――――――――――――――――――――――――――――
setserveroutputon
declare
number1integer:=110;
number2integer:=90;
begin
ifnumber1<=number2then
ifnumber1=number2then
dbms_output.put_line('number1等于number2');
else
dbms_output.put_line('number1小于number2');
endif;
else
dbms_output.put_line('number1大于number2');
endif;
end;
―――――――――――――――――――――――――――――――执行结果:?
(二)循环控制
循环结构是按照一定逻辑条件执行一组命令，PL/SQL中有4种基本循环结构:
1.loop..exit..endloop循环控制
采用loop..exit..endloop循环控制的语法结构如下所示：
loop
循环语句段；
if条件语句then
exit;
else
退出循环的处理语句段；
endif;
endloop;
在【SQLPlusWorksheet】中执行下列PL/SQL程序，该程序将number1变量每次加1，一直到等于number2为止，统计输出循环次数。
―――――――――――――――――――――――――――――――
setserveroutputon
declare
number1integer:=80;
number2integer:=90;
iinteger:=0;
begin
loop
number1:=number1+1;
ifnumber1=number2then
exit;
else
i:=i+1;
endif;
endloop;
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程序：？
setserveroutputon
declare
number1integer:=100;
number2integer:=108;
iinteger:=0;
begin
loop
number1:=number1+1;
ifnumber1=number2then
exit;
else
dbms_output.put_line('变量number1为:'||to_char(number1));
i:=i+1;
endif;
endloop;
dbms_output.put_line('共循环次数:'||to_char(i));
end;
―――――――――――――――――――――――――――――――
2.loop..exit..when..endloop循环控制
采用loop..exit..when..endloop循环控制的语法结构与上例结构类似。
exitwhen实际上就相当于
if条件then
exit;
endif;
在【SQLPlusWorksheet】中执行下列PL/SQL程序，该程序将number1变量每次加1，直到等于number2为止，统计输出循环次数。
―――――――――――――――――――――――――――――――
setserveroutputon
declare
number1integer:=80;
number2integer:=90;
iinteger:=0;
begin
loop
number1:=number1+1;
i:=i+1;
exitwhennumber1=number2;
endloop;
dbms_output.put_line('共循环次数:'||to_char(i));
end;
―――――――――――――――――――――――――――――――
执行结果？
3.while..loop..endloop循环控制
采用loop..exit..when..endloop循环控制的语法如下：
while条件loop
执行语句段;
endloop;
在【SQLPlusWorksheet】中执行下列PL/SQL程序，该程序将number1变量每次加1，一直到等于number2为止，统计输出循环次数。
―――――――――――――――――――――――――――――――――――――
setserveroutputon
declare
number1integer:=80;
number2integer:=90;
iinteger:=0;
begin
whilenumber1<number2loop
number1:=number1+1;
i:=i+1;
endloop;
dbms_output.put_line('共循环次数:'||to_char(i));
end;
―――――――――――――――――――――――――――――――――――――
4.for..in..loop..end循环控制
采用for..in..loop..end循环控制的语法如下：
for循环变量in[reverse]循环下界..循环上界loop
循环处理语句段;
endloop;
在【SQLPlusWorksheet】中执行下列PL/SQL程序，该程序通过循环变量I来控制number1增加次数，输出结果。
―――――――――――――――――――――――――――――――――――――
setserveroutputon
declare
number1integer:=80;
number2integer:=90;
iinteger:=0;
begin
foriin1..10loop
number1:=number1+1;
endloop;
dbms_output.put_line('number1的值:'||to_char(number1));
end;
―――――――――――――――――――――――――――――――――――――
执行结果？
用SQL进行函数查询
Oracle9i提供了很多函数可以用来辅助数据查询。接下来我们介绍常用的函数功能及使
用方法。
5.5.1【ceil】函数
【ceil】函数用法：ceil（n），取大于等于数值n的最小整数。
在【命令编辑区】输入“selectmgr,mgr/100,ceil(mgr/100)fromscott.emp;”，然后单击【执
行】按钮，出现结果?
5.5.2【floor】函数
【floor】函数用法：floor（n），取小于等于数值n的最大整数。
在【命令编辑区】输入“selectmgr,mgr/100,floor(mgr/100)fromscott.emp;”，然后单击【执
行】按钮，出现结果?
5.5.3【mod】函数
【mod】函数用法：mod（m,n），取m整除n后的余数。
在【命令编辑区】输入“selectmgr,mod(mgr,1000),mod(mgr,100),mod(mgr,10)
fromscott.emp;”，然后单击【执行】按钮，出现结果?
5.5.4【power】函数
【power】函数用法：power（m,n），取m的n次方。
在【命令编辑区】输入“selectmgr,power(mgr,2),power(mgr,3)fromscott.emp;”，然后单
击【执行】按钮，出现结果?
5.5.5【round】函数
【round】函数用法：round（m,n），四舍五入，保留n位。在【命令编辑区】输入“selectmgr,round(mgr/100,2),round(mgr/1000,2)fromscott.emp;”，
然后单击【执行】按钮，出现结果?
5.5.6【sign】函数
【sign】函数用法：sign（n）。n>0，取1；n=0，取0；n<0，取-1。
在【命令编辑区】输入“selectmgr,mgr-7800,sign(mgr-7800)fromscott.emp;”，然后单击
【执行】按钮，出现结果?
5.5.7【avg】函数
【avg】函数用法：avg（字段名），求平均值。要求字段为数值型。
在【命令编辑区】输入“selectavg(mgr)平均薪水fromscott.emp;”，然后单击【执行】
按钮，出现结果?
5.5.8【count】函数
（1）在【命令编辑区】输入“selectcount(*)记录总数fromscott.emp;”，然后单击【执
行】按钮，出现结果?
【count(*)】函数的使用
（2）在【命令编辑区】输入“selectcount(distinctjob)工作类别总数fromscott.emp;”，
然后单击【执行】按钮，出现结果?
【count(字段名)】函数的使用
【count】函数用法：count（字段名）或count（*），统计总数。
5.5.9【min】函数
在【命令编辑区】输入“selectmin(sal)最少薪水fromscott.emp;”，然后单击【执行】
按钮，出现结果?
【min】函数用法：min（字段名），计算数值型字段最小数。
5.5.10【max】函数
在【命令编辑区】输入“selectmax(sal)最高薪水fromscott.emp;”，然后单击【执行】
按钮，出现结果?
图4.39【max】函数的使用
【max】函数用法：max（字段名），计算数值型字段最大数。
5.5.11【sum】函数
在【命令编辑区】输入“selectsum(sal)薪水总和fromscott.emp;”，然后单击【执行】
按钮，出现结果?
【sum】函数用法：sum（字段名），计算数值型字段总和。
5.7游标
游标是从数据表中提取出来的数据，以临时表的形式存放在内存中，在游标中有一个数
据指针，在初始状态下指向的是首记录，利用fetch语句可以移动该指针，从而对游标中的数
据进行各种操作，然后将操作结果写回数据表中。
1定义游标
游标作为一种数据类型，首先必须进行定义，其语法如下。
cursor游标名isselect语句;
cursor是定义游标的关键词，select是建立游标的数据表查询命令。
以scott用户连接数据库，在【SQLPlusWorksheet】中执行下列PL/SQL程序，该程序定
义tempsal为与scott.emps数据表中的sal字段类型相同的变量，mycursor为从scott.emp数据
表中提取的sal大于tempsal的数据构成的游标。
―――――――――――――――――――――――――――――――――――――
setserveroutputon
declare
tempsalscott.emp.sal%type;
cursormycursoris
select*fromscott.emp
wheresal>tempsal;
begin
tempsal:=800;
openmycursor;
end;
―――――――――――――――――――――――――――――――――――――
执行结果?
2打开游标
要使用创建好的游标，接下来要打开游标，语法结构如下：
open游标名;
打开游标的过程有以下两个步骤：
（1）将符合条件的记录送入内存。
（2）将指针指向第一条记录。
3提取游标数据
要提取游标中的数据，使用fetch命令，语法形式如下。
fetch游标名into变量名1,变量名2,……;
或fetch游标名into记录型变量名;
在【SQLPlusWorksheet】中执行下列PL/SQL程序，该程序定义cursorrecord变量是游
标mycursor的记录行变量，在游标mycursor的结果中找到sal字段大于800的第一个记录，
显示deptno字段的内容。
提取游标数据
―――――――――――――――――――――――――――――――――――――
setserveroutputon
declare
tempsalscott.emp.sal%type;
cursormycursoris
select*fromscott.emp
wheresal>tempsal;
cursorrecordmycursor%rowtype;
begin
tempsal:=3000;
Openmycursor;
fetchmycursorintocursorrecord;
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语句将提示错误。
在【SQLPlusWorksheet】中执行下列PL/SQL程序，该程序利用%isopen属性判断游标
是否打开。执行结果?
―――――――――――――――――――――――――――――――――――――
setserveroutputon
declare
tempsalscott.emp.sal%type;
cursormycursoris
select*fromscott.emp
wheresal>tempsal;
cursorrecordmycursor%rowtype;
begin
tempsal:=800;
ifmycursor%isopenthen
fetchmycursorintocursorrecord;
dbms_output.put_line(to_char(cursorrecord.deptno));
else
dbms_output.put_line('游标没有打开！');
endif;
end;
―――――――――――――――――――――――――――――――――――――
游标没有打开！
2.%found属性
该属性功能是测试前一个fetch语句是否有值，有值将返回true，否则为false。
在【SQLPlusWorksheet】中执行下列PL/SQL程序。该程序利用%found属性判断游标是
否有数据。
执行结果?
―――――――――――――――――――――――――――――――――――――
setserveroutputon
declare
tempsalscott.emp.sal%type;
cursormycursoris
select*fromscott.emp
wheresal>tempsal;
cursorrecordmycursor%rowtype;
begin
tempsal:=800;
openmycursor;
fetchmycursorintocursorrecord;
ifmycursor%foundthen
dbms_output.put_line(to_char(cursorrecord.deptno));
else
dbms_output.put_line('没有数据！');
endif;
end;
―――――――――――――――――――――――――――――――――――――
30
3.%notfound属性
该属性是%found属性的反逻辑，常被用于退出循环。
在【SQLPlusWorksheet】中执行下列PL/SQL程序。该程序利用%notfound属性判断游
标是否没有数据。
执行结果?
发现数据！
―――――――――――――――――――――――――――――――――――――
setserveroutputon
declare
tempsalscott.emp.sal%type;
cursormycursoris
select*fromscott.emp
wheresal>tempsal;
cursorrecordmycursor%rowtype;
begin
tempsal:=800;
openmycursor;
fetchmycursorintocursorrecord;
ifmycursor%notfoundthen
dbms_output.put_line(to_char(cursorrecord.deptno));
else
dbms_output.put_line('发现数据！');
endif;
end;
―――――――――――――――――――――――――――――――――――――
4.%rowcount属性
该属性用于返回游标的数据行数。
在SQLPlusWorksheet的【代码编辑区】执行下列PL/SQL程序，该程序利用%rowcount
属性判断游标数据行数。
执行结果?
―――――――――――――――――――――――――――――――――――――
Setserveroutputon
declare
tempsalscott.emp.sal%type;
cursormycursoris
select*fromscott.emp
wheresal>tempsal;
cursorrecordmycursor%rowtype;
begin
tempsal:=800;
openmycursor;
fetchmycursorintocursorrecord;
dbms_output.put_line(to_char(mycursor%rowcount));
end;
―――――――――――――――――――――――――――――――――――――
1
本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/aicon/archive/2010/04/21/5511454.aspx
