PLSQL基础一.docx
《PLSQL基础一.docx》由会员分享，可在线阅读，更多相关《PLSQL基础一.docx（36页珍藏版）》请在冰点文库上搜索。
PLSQL基础一
PL/SQL块语法格式:
DECLARE定义以DECLARE开始（可选）
/*
*定义部分-->定义常量.变量.复杂数据类型.游标.例解
*/
BEGIN执行部分以BEGIN开始（必选）
/*
*执行部分-->PL/SQL语句和SQL语句
*/
EXCEPTION例外处理以EXCEPTION开始（可选）
/*
*例外处理部分-->处理运行错误
*/
END;/*块结束标记*/
注意:
DECLARE,BEGIN,EXCEPTION后面是没有分号的（;）而END后面必需要带有分号（;）/*…*/（多行注释符号）
例:
SQL>setserveroutputon<==执行过程的时候，显式输出结果on功能打开/off功能关闭
SQL>begin
2dbms_output.put_line（'Hello,everyone!
'）;
3end;
4/
Hello,everyone!
dbms_output是oracle所提供的系统包,put_line是该包所包含的过程,用于输出字符串信息.当使用dbms_output包输出数据或消息时,必需要将SQL*Plus的环境变量serveroutput设置on
例2:
带定义部分
SQL>declare
2v_enamevarchar2（5）;
3begin
4selectenameintov_enamefromemp
5whereempno=&no;
6dbms_output.put_line（'雇员名:
'||v_ename）;
7end;
8/
输入no的值:
7369
原值5:
whereempno=&no;
新值5:
whereempno=7369;
雇员名:
mfs
例3:
带例外部分
SQL>declare
2v_enamevarchar2（5）;
3begin
4selectenameintov_enamefromemp
5whereempno=&no;
6dbms_output.put_line（'雇员名:
'||v_ename）;
7exception
8whenno_data_foundthen<==NO_DATA_FOUND是PL/SQL的预定义例外.
9dbms_output.put_line（'请输入正确的雇员号'）;
10end;
11/
输入no的值:
999
原值5:
whereempno=&no;
新值5:
whereempno=999;
请输入正确的雇员号
根据实现的应用模块功能,分为,匿名块,命名块,子程序和触发器四种
1匿名块
没有名称的PL/SQL块
例:
SQL>declare
2v_avgsalnumber（6,2）;
3begin
4selectavg（sal）intov_avgsalfromemp
5wheredeptno=&no;
6dbms_output.put_line（'平均工资;'||v_avgsal）;
7end;
8/
输入no的值:
20
原值5:
wheredeptno=&no;
新值5:
wheredeptno=20;
平均工资;2300.78
PL/SQL过程已成功完成。
执行以上PL/SQL块,会根据输入的部门号显示部门平均工资,但因为该PL/SQL块直接以DECLARE开始,没有给出任何名称,所以该PL/SQL块属于匿名块
2命名块
指具有特定名称标识的PL/SQL块,命名块和匿名块非常类似,只不过在PL/SQL块前使用<<>>加以标记.当使用嵌套块时,为了区分多级嵌套层次关系,可以使用命名块加以区分
例:
SQL><>
2declare
3v_deptnonumber
（2）;
4v_dnamevarchar2（10）;
5begin
6<>
7begin
8selectdeptnointov_deptnofromemp
9wherelower（ename）=lower（'&name'）;
10end;--<>
11selectdnameintov_dnamefromdept
12wheredeptno=v_deptno;
13dbms_output.put_line（'部门名:
'||v_dname）;
14end;--<>
15/
输入name的值:
scott
原值9:
wherelower（ename）=lower（'&name'）;
新值9:
wherelower（ename）=lower（'scott'）;
部门名:
RESEARCH
PL/SQL过程已成功完成。
注解:
<>主块（外层块）<>子块（内层块）的标记
3子程序
子程序包括过程,函数和包
3.1过程
--过程用于执行特定操作,当建立过程时,即可以指定输入参数（IN）,也可以指定输出参数（OUT）.通过在过程中使用输入参数,可以讲应用环境的数据传递到执行部分;通过使用输出参数,可以将执行部分的数据传递到应用环境.
在SQL*Plus中可以使用CREATEPROCEDURE命令建立过程
例:
SQL>createprocedureupdate_sal（
2namevarchar2,newsalnumber）
3is
4begin
5updateempsetsal=newsal
6wherelower（ename）=lower（name）;
7end;
SQL>/
过程已创建。
调用过程
SQL>execupdate_sal（'scott',2000）
PL/SQL过程已成功完成。
或
SQL>callupdate_sal（'scott',3000）;
调用完成。
3.2函数
--用于返回特定数据.当建立函数时,在函数头部必须包含RETURN子句,而在函数体内必须要包含RETURN语句返回数据.
在SQL*Plus中可以使用CREATEFUNCTION命令简历函数
SQL>createfunctionannual_income（namevarchar2）
2returnnumberis
3annual_salarynumber（7,2）;
4begin
5selectsal*12+nvl（comm,0）intoannual_salary
6fromempwherelower（ename）=lower（name）;
7returnannual_salary;
8end;
9/
函数已创建。
调用函数
--调用函数时,可以使用多种方法.这里使用SQL*Plus绑定变量存放输出结果
SQL>varincomenumber
SQL>callannual_income（'scott'）into:
income;
调用完成。
SQL>printincome
INCOME
----------
36000
3.3包
--用于逻辑组合相关的过程和函数,它由包规范和包体两部分组成.包规范用于定义公用的常量,变量,过程和函数.
在SQL*Plus中建立包规范可以使用CREATEPACKAGE命令
例:
SQL>createpackageemp_pkgis
2procedureupdate_sal（
3namevarchar2,newsalnumber）;
4functionannual_income（namevarchar2）
5returnnumber;
6end;
7/
程序包已创建。
包规范只包含了过程和函数的说明,而没有过程和函数的实现代码.
包体用于实现包规范中的过程和函数,在SQL*Plus中使用CREATEPACKAGEBODY命令
例:
SQL>createpackagebodyemp_pkgis
2procedureupdate_sal（namevarchar2,newsalnumber）
3is
4begin
5updateempsetsal=newsal
6wherelower（ename）=lower（name）;
7end;
8functionannual_income（namevarchar2）returnnumber
9is
10annual_salarynumber（7,2）;
11begin
12selectsal*2+nvl（comm,0）intoannual_salary
13fromempwherelower（ename）=lower（name）;
14returnannual_salary;
15end;
16end;
17/
程序包体已创建。
调用包
--当调用包的过程和函数时,在过程和函数名之前必需要带有包名作为前缀（包名.子程序名）,而如果要访问其他方案的包,还必需要加上方案名称作为前缀（方案名,包名.子程序名）
SQL>callemp_pkg.update_sal（'mfs',1919）;
SQL>callemp_pkg.annual_income（'scott'）into:
income;
SQL>printincome
INCOME
----------
6000
4触发器
--是指隐含执行的存储过程.当定义触发器时,必需要指定触发时间以及触发操作,常用的触发器时间包括INSERT,UPDATE和DELETE语句,而触发操作实际是一个PL/SQL块
在SQL*Plus中使用CREATETRIGGER命令来完成的
例:
SQL>createtriggerupdate_cascade
2afterupdateofdeptnoondept
3foreachrow
4begin
5updateempsetdeptno=:
new.deptno
6wheredeptno=:
old.deptno;
7end;
8/
触发器update_cascade用于实现级联更新,如果不建立触发器,那么当更新dept表的deptno列数据时就会显示错误"ORA-02292:
违反完整约束条件（SCOTLFK_DEPTNO）-已找到子记录日志";而在建立了触发器之后,当更新deptno列时,就会级联更新emp表的deptno列的相关数据.
验证:
select*fromdept;
updatedeptsetdeptno=60wherelower（dname）='research'
select*fromemp;
常量（constant）
--在一个变化过程中，此量的数值始终是不变的，我们称它为常量。
它们可以是不随时间变化的某些量和信息，也可以是表示某一数值的字符或字符串，常被用来标识、测量和比较。
变量（variable）
--是指没有固定的值，可以改变的数。
变量以非数字的符号来表达，一般用拉丁字母。
变量是常数的相反。
变量的用处在于能一般化描述指令的方式。
如果只能使用真实的值，指令只能应用于某些情况下。
变量能够作为某特定种类的值中任何一个的保留器。
计算机中的变量
--是一段有名字的连续存储空间。
在源代码中通过定义变量来申请并命名这样的存储空间，并通过变量的名字来使用这段存储空间。
--变量是程序中数据的临时存放场所。
在代码中可以只使用一个变量，也可以使用多个变量，变量在内存中的存储.变量中可以存放单词、数值、日期以及属性。
由于变量让你能够把程序中准备使用的每一段数据都赋给一个简短、易于记忆的名字，因此它们十分有用。
5:
定义并使用变量
编写PL/SQL程序时,若临时存储数值,必需要定义变量和常量,若在应用环境和子程序之间传递数据,必需要为子程序指定参数.
在PL/SQL程序中定义变量,常量和参数时,则必需要为它们指定PL/SQL数据类型.
在编写PL/SQL程序时,可以使用标量（scalar）类型,复合（composite）类型,参照（reference）类型和LOB（largeobject--大对象）类型等四种类型
PL/SQL程序时可以引用的各种数据类型查看图图在<<精通oracle10g.pl/sql编程.pdf>>35页
5.1:
标量变量
--指只能存放单个数值的变量.最常用的变量就是标量变量.当定义标量变量时,必需要指定标量数据类型.标量数据类型包括数字,字符,日期和布尔类型,每种类型又包含有相应的子类型,如number类型包含有integer（整数）,positive（正数）等子类型
布尔类型-布尔类型对象可以被赋予值true或者false，所对应的关系就是真与假
1-常用标量类型
varchar2（n）
用于定义可变长度的字符串,其中n用于指定字符串的最大长度,最大值为32767字节.在PL/SQL块中其值长度不应该超过4000字节.
char（n）
用于定义固定长度的字符串,其中n用于指定字符串的最大长度,最大值为32767字节,默认值为1,在PL/SQL块中其值长度不应该超过2000字节.
number（p,s）
用于定义固定长度的整数和浮点数,其中p表示精度,指定数字的总位数;s表示标度,用于指定小数点后的数字位数.如:
定义变量number（6,2）整数位数最大是4位,小数部分最大是2位
date
用于定义日期和时间数据,且数据长度为固定长度（7字节）.但注意,当给date变量赋值时,数据必需要与日期格式和日期语言匹配.
timestamp
是oracle9i新增加的数据类型,用于定义日期和时间数据.当显示timestamp变量数据时,不仅会显示日期,而且还会显示时间和上下午标记.
long和longraw
long用于定义变长字符串,类似与varchar2,但其字符串的对打长度为32760字节;longraw用于定义变长的二进制数据,最大长度为32760字节
boolean
用于定义布尔变量,其变量值为TRUE,FALSE或NULL.该数据类型是PL/SQL数据类型,表列不能采用该数据类型
binary_integer
该数据类型用于定义整数,其数值范围在-2147473647和2147473647之间.该数据类型是PL/SQL数据类型,表列不能采用该数据类型
binary_float和binary_double
是10g新增加的数据类型,分别用于定义单精度的浮点数和双精度的浮点数.这两种数据类型主要用于高速的科学计算,当为binary_float赋值时,应带有后缀f（如:
1.5f）,当为binary_double赋值时,应带有后缀d（如:
2.00095d）.
5.2定义标量变量
如果要引用标量变量,必需首先在定义部分定义标量变量,然后才能在执行部分或例外处理部分中使用这些标量变量
在PL/SQL块中定义变量和常量的语法如下:
identifier[constant]datatype[notnull][:
=|defaultexpr]
identifier:
用于指定变量或常量名称
constant:
指定常量.当定义常量时,必需指定它的初始值,并且其数值不能改变
datatype:
指定变量或常量的数据类型
notnull:
强制初始化变量（不能为NULL）.当指定NOTNULL选项时,必需要为变量提供数值
:
=:
用于为变量和常量指定初始值在PL/SQL块中为变量赋值不同于其它编程语言,必需要在等号前面加冒号（:
=）
default:
用于为变量和常量指定初始值
expr:
指定初始值的PL/SQL表达式,可以是文本值,其他变量,函数等
定义标量变量实例:
v_enamevarchar2（10）;-->定义变量v_ename为可变长的字符串类型长度最长为10
v_salnumber（6,2）;
v_balancebinary_float;
c_tax_rateconstantnumber（3,2）:
=5.5;-->定义的常量
v_hiredatedate;
v_validbooleannotnulldefaultfalse;为变量v_valid提供了初始值,
需注意,如果在定义变量时没有指定初始值,那么变量初始值为null
使用标量变量
例:
实现以输入雇员号显示雇员姓名,工资,个人所得税.
SQL>declare
2v_enamevarchar2（5）;v_salnumber（6,2）;
3c_tax_rateconstantnumber（3,2）:
=0.03;v_tax_salnumber（6,2）;
4begin
5selectename,salintov_ename,v_salfromempwhereempno=&eno;
6v_tax_sal:
=v_sal*c_tax_rate;
7dbms_output.put_line（'雇员名:
'||v_ename）;
8dbms_output.put_line（'雇员工资:
'||v_sal）;
9dbms_output.put_line（'所得税:
'||v_tax_sal）;
10end;
11/
输入eno的值:
7788
原值5:
selectename,salintov_ename,v_salfromempwhereempno=&eno;
新值5:
selectename,salintov_ename,v_salfromempwhereempno=7788;
雇员名:
SCOTT
雇员工资:
3000
所得税:
90
PL/SQL过程已成功完成。
SQL>selectenamefromempwhereempno=7654;
ENAME
----------
MARTIN
SQL>declare
2v_enamevarchar2（5）;v_salnumber（6,2）;
3c_tax_rateconstantnumber（3,2）:
=0.03;
4v_tax_salnumber（6,2）;
5begin
6selectename,salintov_ename,v_salfromemp
7whereempno=&eno;
8v_tax_sal:
=v_sal*c_tax_rate;
9dbms_output.put_line（'雇员名:
'||v_ename）;
10dbms_output.put_line（'雇员工资:
'||v_sal）;
11dbms_output.put_line（'所得税:
'||v_tax_sal）;
12end;
13/
输入eno的值:
7654
原值7:
whereempno=&eno;
新值7:
whereempno=7654;
declare
*
第1行出现错误:
ORA-06502:
PL/SQL:
数字或值错误:
字符串缓冲区太小
ORA-06512:
在line6
以上报错因为输入的编号对应的雇员名超过了变量定义的v_enamevarchar2（5）长度,则会显示错误信息（ORA-06502:
PL/SQL:
数字或值错误:
字符串缓冲区太小）,原因是v_ename变量的最大长度为5字节,而emp表中ename列的最大长度为10个字节,所以只需要将v_ename变量的最大长度设置为10字节.
SQL>declare
2v_enamevarchar2（10）;<==做变更
3v_salnumber（6,2）;
4c_tax_rateconstantnumber（3,2）:
=0.03;
5v_tax_salnumber（6,2）;
6begin
7selectename,salintov_ename,v_salfromemp
8whereempno=&eno;
9v_tax_sal:
=v_sal*c_tax_rate;
10dbms_output.put_line（'雇员名:
'||v_ename）;
11dbms_output.put_line（'雇员工资:
'||v_sal）;
12dbms_output.put_line（'所得税:
'||v_tax_sal）;
13end;
14/
输入eno的值:
7654
原值8:
whereempno=&eno;
新值8:
whereempno=7654;
雇员名:
MARTIN
雇员工资:
1663.75
所得税:
49.91
PL/SQL过程已成功完成。
为了降低PL/SQL程序的维护工作量,可以使用%TYPE属性定义变量.使用%TYPE属性
定当以PL/SQL变量存放列值时,必需确保变量使用合适的数据类型和长度,否则在运行过程中可能会出现PL/SQL运行错误.为了避免这种不必要的错误,可以使用%TYPE属性定义变量.当使用%TYPE属性定义变量时,它会按照数据库列或其他变量来确定新变量的类型和长度
例:
SQL>declare
2v_enameemp.ename%type;<--使用%type属性
3v_salnumber（6,2）;
4c_tax_rateconstantnumber（3,2）:
=0.03;
5v_tax_salnumber（6,2）;
6begin
7selectename,salintov_ename,v_salfromemp
8whereempno=&eno;
9v_tax_sal:
=v_sal*c_tax_rate;
10dbms_output.put_line（'雇员名:
'||v_ename）;
11dbms_output.put_line（'雇员工资:
'||v_sal）;
12dbms_output.put_line（'所得税:
'||v_tax_sal）;
13end;
14/
输入eno的值:
7654
原值8:
whereempno=&eno;
新值8:
whereempno=7654;
雇员名:
MARTIN
雇员工资:
1663.75
所得税:
49.91
PL/SQL过程已成功完成。
数组
是在程序设计中，为了处理方便，把具有相同类型的若干变量按有序的形式组织起来的一种形式。
这些按序排列
升级会员 