oracle 第五讲PLSQL.docx
《oracle 第五讲PLSQL.docx》由会员分享，可在线阅读，更多相关《oracle 第五讲PLSQL.docx（17页珍藏版）》请在冰豆网上搜索。
oracle第五讲PLSQL
PL/SQL
❑PL/SQL是过程语言（ProceduralLanguage）与结构化查询语言（SQL）结合而成的编程语言
❑PL/SQL是对SQL的扩展
❑支持多种数据类型，如大对象和集合类型，可使用条件和循环等控制结构
❑可用于创建存储过程、触发器和程序包，给SQL语句的执行添加程序逻辑
PL/SQL程序块的总体结构
declare（可选）
定义语句段
begin
执行语句段（还可以包含子程序块）
exception（可选）
异常处理语句段
end;
注意：
sqlplus中编辑代码块可以使用ed命令切换到afiedt.buf中编辑
◆常量变量
常量名constant类型标识符:
=值;
declare
nameconstantvarchar2（20）:
='东方不败';
PIconstantnumber（5,2）:
=3.146;
begin
dbms_output.put_line（name||''||PI）;--dbms_output内置程序包用于控制输出
end;--注意；结尾
setserveroutputon设置输出
/*
这是多行注释
变量赋值方式
（1）:
=
（2）select..into语句返回结果要求有且只有一条记录
*/
declare
mynamevarchar2（20）;
mydatedate;
mynumnumber（8）:
=1000;
begin
myname:
='东方不败';
selecthiredateintomydatefromempwhereempno=7788;
dbms_output.put_line（myname||'---'||mydate）;
end;
◆pl/sql常用数据类型
●数字类型
number
binary_integer（带符号整数-2^31-1---2^31-1）
PLS_integer（带符号整数-2^31---2^31,运算速度快）
●字符类型
char
varchar2
raw
long/longraw
●日期类型
Date
Timestamp
●布尔类型
boolean（truefalsenull）
●lob类型（4G）
❑BLOB将大型二进制对象存储在数据库中
❑CLOB将大型字符数据存储在数据库中
❑NCLOB存储大型UNICODE字符数据
❑BFILE将大型二进制对象存储在操作系统文件中
--BFILE类型演示
createtabletest_bfile
（
file_namevarchar2（100）,
book_filebfile
）;
--创建目录
createorreplacedirectorymydir
as'F:
\MyDocuments\ORACLE\oracle_accp\PX_课件\files';
insertintotest_bfilevalues（'文本文件',
bfilename（'MYDIR','book.txt'））;--目录名大写
commit;
--blob类型演示
createtabletest_blob
（
file_namevarchar2（20）,
myimageblob
）;
--插入数据
declare
v_bfilebfile;
v_blobblob;
begin
--blob插入记录，先使blob为空empty_blob（）表示设置blob类型为空
insertintotest_blobvalues（'fish',empty_blob（））
returnmyimageintov_blob;
v_bfile:
=bfilename（'MYDIR','fish.gif'）;--读取文件到bfile中（指定目录名和文件名）
dbms_lob.open（v_bfile,dbms_lob.file_readonly）;--以只读方式打开bfile类型变量
dbms_lob.loadfromfile（v_blob,v_bfile,dbms_lob.getlength（v_bfile））;
--加载bfile指向的文件字节到blob变量中
dbms_lob.close（v_bfile）;--关闭bfile
commit;
end;
--CLOB演示
createtabletest_clob（textidnumber（10）,myclobclob）;
--插入数据
insertintotest_clobvalues（100,'秋天地上的小草渐渐枯黄，落叶树的叶子也开始变红或变黄，并随风飘落下来。
桂花飘香，菊花争艳，
一串红、鸡冠花木芙蓉等一些花儿也开放了蒲公英结出了白绒毛似的“小伞兵”，
随风飘散，大地呈现出美丽的秋色。
'）;
commit;
--读取clob数据
selectmyclobfromtest_clobwheretextId=100;
--通过dbms_lob程序包读取clob数据
declare
v_clobclob;
v_txtvarchar2（3000）;
amountinteger;--要读取的字符数
offsetinteger;--起始位置
begin
selectmyclobintov_clobfromtest_clobwhereTEXTID=100;
amount:
=1000;
offset:
=1;
dbms_lob.read（v_clob,amount,offset,v_txt）;
--读取clob变量到字符变量中要读取的字符数及起始位置必须是变量
dbms_output.put_line（v_txt）;
end;
●属性类型
%type
引用其他变量的类型或数据表中的字段的数据类型来声明变量的类型
声明:
变量名表.字段名%type;
declare
mydatescott.emp.hiredate%type;
begin
selecthiredateintomydatefromempwhereempno=7788;
dbms_output.put_line（mydate）;
end;
%rowtype
表示表中一行记录的类型
声明:
变量名表%rowtype;
declare
mytablescott.emp%rowtype;
begin
select*intomytablefromscott.empwhereempno=7788;
dbms_output.put_line（mytable.ename||''||mytable.sal）;
end;
%type与%rowtype区别：
使用%type定义变量：
变量名数据表.列名%type
使用%rowtype定义变量：
变量名数据表%rowtype
引用%rowtype定义的变量时，使用：
变量名.列名
◆运算符及表达式
字符表达式
字符运算符||
关系表达式
<小于
>大于
=等于（不是赋值运算符:
=）
like
in
<=小于等于
>=大于等于
!
=不等于<>
betweenand
逻辑表达式优先次序为：
NOT、AND、OR
NOT：
逻辑非
OR：
逻辑或
AND：
逻辑与
◆流程控制
/*
条件控制
1….if...then...[else...]endif;
*/
declare
numinteger;
inputnonumber（10）;
begin
inputno:
='&请输入';
selectsalintonumfromscott.empwhereempno=inputno;
if（num>2000）then
dbms_output.put_line（'薪水高于2000'）;
else
dbms_output.put_line（'薪水低于2000'）;
endif;
end;
2…..if...then...elsif..then..else...endif;
declare
numinteger;
inputnonumber（10）;
begin
inputno:
='&请输入';
selectsalintonumfromscott.empwhereempno=inputno;
if（num<2000）then
dbms_output.put_line（'薪水低于2000'）;
elsif（num>=2000andnum<=3000）then
dbms_output.put_line（'薪水在3000-2000之间'）;
else
dbms_output.put_line（'薪水高于3000'）;
endif;
end;
3…….if嵌套条件控制
语法结构：
if条件1then
if条件2then
语句段1;
else
语句段2;
endif;
else
语句段3;
endif;
case语句写法有两种
--写法1
case字段名when常量then赋值表达式;
when..then...
else...
endcase;
--目标给各部门员工加薪
declare
v_deptnonumber:
=10;
v_salnumber;
begin
casev_deptno
when10thenv_sal:
=1;
when20thenv_sal:
=2;
else
v_sal:
=3;
endcase;
updatescott.empsetsal=sal+v_sal;
commit;
end;
--写法2
case
when条件表达式then赋值表达式;
when..then...
else...
endcase;
--目标使用case语句统计员工薪水等级
selectename,sal,case
whensal<2000then'低等'
whensal>=2000andsal<3000then'中等'
whensal>=3000andsal<4000then'上等'
else'高等'
end薪水等级
fromemp;
declare
v_dvarchar2（4）;
v_tnumber:
=18;
begin
case
whenv_t>=7andv_t<11thenv_d:
='上午';
whenv_t>=11andv_t<13thenv_d:
='中午';
whenv_t>=13andv_t<17thenv_d:
='下午';
else
v_d:
='晚上';
endcase;
dbms_output.put_line（v_d）;
end;
◆循环控制
◆1．loop…exit…endloop循环控制：
declare
v_iint:
=1;
begin
loop
v_i:
=v_i+1;
if（v_i=20）then
exit;--退出循环另外：
continue跳出当次循环return无条件退出代码块
endif;
dbms_output.put_line（v_i）;
endloop;
end;
2.loop...exitwhen...endloop循环控制
declare
v_iint:
=1;
begin
loop
v_i:
=v_i+1;
exitwhenv_i=20;
dbms_output.put_line（v_i）;
endloop;
end;
3．while...loop...endloop循环控制
--九九乘法表
declare
v_inumber:
=1;
v_jnumber;
begin
while（v_i<10）loop
v_j:
=1;
loop
dbms_output.put（v_j||'*'||v_i||'='||v_j*v_i||''）;
v_j:
=v_j+1;
exitwhenv_j>v_i;
endloop;
dbms_output.put_line（''）;
v_i:
=v_i+1;
endloop;
end;
/*4.foriinn..mloop...endloop;
for循环变量in[reverse]循环下界..循环上界loop
循环处理语句段;
endloop;
*/
declare
v_sumnumber:
=1;
begin
foriin1..5loop
v_sum:
=v_sum*i;
endloop;
dbms_output.put_line（'阶乘结果:
'||v_sum）;
end;
--reverse
begin
foriinreverse1..10loop
dbms_output.put_line（i）;
endloop;
end;
◆顺序控制
GOTO语句无条件跳转的标签指定的语句,标签:
<<标签名>>--pl/sql块中唯一
不能跳转到if、case、loop语句
NULL语句什么也不做，只是转到下一个语句，
对于有些语法至少需要一个可执行语句，但又不需要执行任何操作的情况下使用;
declare
v_intnumber
（2）;
begin
v_int:
='&input';
if（v_int=1）then
gotoTOUPDATE;
else
gotoQUIT;
endif;
<>
dbms_output.put_line（'GOTO跳转到这'）;
<>
NULL;
end;
◆动态SQL
❑指在PL/SQL程序执行时生成的SQL语句
❑DDL语句命令和会话控制语句不能在PL/SQL中直接使用，但是可以通过动态SQL来执行DDL
executeimmediate实现DDL
declare
v_ddlvarchar2（200）;
begin
v_ddl:
='createtablemytableasselectename,salfromemp';
executeimmediatev_ddl;
dbms_output.put_line（'mytable表已建'）;
end;
executeimmediate返回单行记录
executeimmediate查询语句into行数据类型using对应占位符变量
declare
v_idemp.empno%type;
v_empemp%rowtype;
v_sqlvarchar2（100）;
begin
v_id:
=7788;
v_sql:
='select*fromempwhereempno=:
id';--id为占位符
executeimmediatev_sqlintov_empusingv_id;
dbms_output.put_line（v_emp.ename||''||v_emp.sal）;
end;
◆异常处理
异常处理就是针对错误进行处理的程序段
分为系统预定义异常处理和自定义异常处理两部分
系统预定义异常处理
异常名称描述
ACCESS_INTO_NULL未初始化对象
CURSOR_ALREADY_OPEN试图打开已经打开的游标
ZERO_DIVIDE除数为零
NO_DATA_FOUND无返回记录
INVALID_NUMBER字符串转换为数字失败
VALUE_ERROR转换、截断、大小约束错误
TOO_MANY_ROWS执行selectinto语句返回多行记录
OTHERS其他异常
例1:
declare
v_nameemp.ename%type;
begin
selectenameintov_namefromempwhereempno=&emp_no;
dbms_output.put_line（'员工姓名:
'||v_name）;
exception
whenno_data_foundthen
dbms_output.put_line（'无记录返回'）;
end;
例2:
--测试数据
insertintodeptvalues（50,'技术部','我的公司'）;
insertintoemp（empno,ename,job,sal,deptno）
values（8888,'东方不败','保镖',9000,50）;
commit;
declare
v_namevarchar2（10）;
begin
selectenameintov_namefromscott.empwheredeptno=&v_deptno;
raiseINVALID_NUMBER;--raise显式引发INVALID_NUMBER异常
dbms_output.put_line（'正常执行'）;
exception
whenTOO_MANY_ROWSthen
dbms_output.put_line（'此处不能返回多行记录'）;
whenothersthen
dbms_output.put_line（'扑获其他异常'）;
end;
自定义异常处理
1．定义异常处理
异常名exception;
2．引发异常处理
raise异常名;
3．处理异常
exception
when异常名1then
异常处理语句段1;
when异常名2then
异常处理语句段2;
declare
sal_exceptionexception;--声明异常
v_empscott.emp%rowtype;
begin
select*intov_empfromscott.empwhereempno=&v_empno;
if（v_emp.sal<1000）then
raisesal_exception;--显式引发异常
endif;
dbms_output.put_line（v_emp.ename||'薪水:
$'||v_emp.sal）;
exception
whensal_exceptionthen--异常处理
raise_application_error（-20001,'薪水太少哦......'）;
--使用系统过程引发异常错误编号为-20000到-20999之间的负整数
end;
升级会员 