35PLSQL基础.docx
《35PLSQL基础.docx》由会员分享，可在线阅读，更多相关《35PLSQL基础.docx（20页珍藏版）》请在冰豆网上搜索。
35PLSQL基础
●PL/SQL块简介
⏹PL/SQL块结构
PL/SQL由三部分组成：
定义，执行，异常处理
Declare
/*
*定义部分—定义常量，变量，复杂数据类型，游标
*/
Begin
/*
*执行部分—PL/SQL语句与SQL语句
*/
Exception
/*
*异常处理部分—处理运行错误
*/
End;/*块结束标记*/
示例一：
只包含执行部分的PL/SQL块
SQL>setserveroutputon
SQL>begin
2dbms_output.put_line（'hello,cosima'）;
3end;
4/
hello,cosima
实例二：
包含定义部分和执行部分的PL/SQL块
SQL>declare
2v_namevarchar2（10）;
3begin
4selectenameintov_namefromscott.emp
5whereempno=&no;
6dbms_output.put_line（'雇员名:
'||v_name）;
7end;
8/
实例三：
包含定义部分和执行部分和异常处理部分的PL/SQL块
SQL>declare
2v_namevarchar2（10）;
3begin
4selectenameintov_namefromscott.emp
5whereempno=&no;
6dbms_output.put_line（'雇员名:
'||v_name）;
7execption
8whenno_data_foundthen
9dbms_output.put_line（'请输入正确的雇员名'）;
10end;
11/
⏹PL/SQL块分类
◆匿名块
指的是没有名称的PL/SQL块。
SQL>declare
2v_avgsalnumber（6,2）;
3begin
4selectavg（sal）intov_avgsalfromscott.emp
5wheredeptno=&no;
6dbms_output.put_line（'adfasdfasdf:
'||v_avgsal）;
7end;
8/
◆命名块
指的是具有特定名称标识的pl/sql块，命名快与匿名块类似，但在PL/SQL块前使用<<>>加以标记。
为了区分多级嵌套层次关系，可以使用命名块加以区分。
使用替代变量输入雇员名，并输出雇员所在的部门名
SQL>declare
2v_deptnonumber
（2）;
3v_dnamevarchar2（10）;
4begin
5<>
6begin
7selectdeptnointov_deptnofromscott.emp
8wherelower（ename）=lower（'scott'）;
9end;------inner
10selectdnameintov_dnamefromscott.dept
11wheredeptno=v_deptno;
12dbms_output.put_line（'fdf:
'||v_dname）;
13end;
14/------outer
◆子程序
●过程—用于执行特定操作
下面建立用于更新雇员工资的过程，并且用该过程修改scott工资
SQL>createorreplaceprocedureupdate_sal（namevarchar2,newsalnumber）
2is
3begin
4updatescott.empsetsal=newsalwherelower（ename）=lower（name）;
5end;
6/
Procedurecreated
SQL>execupdate_sal（'scott',2000）;
PL/SQLproceduresuccessfullycompleted
●函数—用于返回特定数据
建立函数时，必须包含return子句，并且函数体必须包含return语句。
下面：
建立用于取得雇员全年收入的函数，并引用该函数取得scott全年收入为例。
SQL>createorreplacefunctionannual_income（namevarchar2）returnnumberis
2annual_salarynumber（7,2）;
3begin
4selectsal*12+nvl（comm,0）intoannual_salary
5fromscott.empwherelower（ename）=lower（name）;
6returnannual_salary;
7end;
8/
Functioncreated
SQL>selectannual_income（'scott'）年收入fromdual;
年收入
----------
24000
●包---用于逻辑组合相关的过程和函数。
它由包规范和包体两部分组成。
包规范：
用于定义公用的常量，变量，过程和函数。
而包体则用于实现包规范中的过程和函数。
以下是以建立包含过程的update_sal和函数annual_income的包emp_pkg为例。
SQL>createorreplacepackageemp_pkgis
2procedureupdate_sal（namevarchar2,newsalnumber）;
3functionannual_income（namevarchar2）returnnumber;
4endemp_pkg;
5/
Packagecreated
SQL>
SQL>createorreplacepackagebodyemp_pkgis
2procedureupdate_sal（namevarchar2,newsalnumber）
3is
4begin
5updateempsetsal=newsalwherelower（ename）=lower（name）;
6end;
7functionannual_income（namevarchar2）returnnumber
8is
9annual_salarynumber（7,2）;
10begin
11selectsal*12+nvl（comm,0）intoannual_salary
12fromempwherelower（ename）=lower（name）;
13returnannual_salary;
14end;
15end;
16/
SQL>execemp_pkg.update_sal（'scott',1500）
PL/SQLproceduresuccessfullycompleted
SQL>selectemp_pkg.annual_income（'scott'）fromdual;
EMP_PKG.ANNUAL_INCOME（'SCOTT'）
------------------------------
18000
◆触发器
触发器是指被隐含执行的PL/SQL块。
当触发了与触发器相关的事件后，oracle会隐含执行触发器的PL/SQL块
createorreplacetriggerupdate_cadcade
afterupdateondeptforeachrow
declare
--localvariableshere
begin
updateempsetdeptno=:
new.deptnowheredeptno=:
old.deptno;
endupdate_cadcade;----->当触动DEPT表时，EMP表也自动改动
●定义并使用变量
⏹标量变量
Scalar变量：
指能存放单个数值的变量。
定义标量变量时，需要制定标量数据类型。
标量数据类型包括数字，字符，日期和布尔等类型。
每种类型又包含子类型
◆PL/SQL数字类型和字类型
Pls_integer:
定义整数。
是PL/SQL类型，表列不能支持该数据类型。
Binary_integer:
定义整数。
是PL/SQL类型，表列不能支持该数据类型。
Binary_float:
定义单精度浮点数。
当定义该变量赋值是，应该带有后缀f（1.5f）
Binary_double:
定义双精度浮点数。
应带带有后缀d,（3.00004d）
Number（p,s）:
定义固定长度的整数和浮点数。
P:
总位数。
S用于指定小数位数
Natural:
定义自然数。
（pls_integer子类型）
Naturaln:
定义具有notnull约束的自然数
Positive:
定义正数
Positiven:
定义具有notnull约束的正数
Signtype:
定义-1,0,1
Simple_integer:
定义具有notnull约束的整数
Desc,decimal,numberic:
定义最大精度为38位的数字（number子类型）
Doubleprecision:
定义最大精度为38位的双精度浮点数
Float:
定义最大精度为38位的单精度浮点数
Int,integer,smallint:
定义最大精度为38位的数字
Real:
定义最大精度为18位的实数。
◆PL/SQL字符类型和子类型
Varchar2（n[char|byte]）:
定义变长字符串。
N最大长度，char：
字符byte：
字节（默认）
Char（n[char|byte]）:
定义定长字符串。
N表示长度，char：
字符byte：
字节（默认）
Raw（n）:
定义变长二进制串
Nchar（n）:
定义定长多字节字符串
Nvarchar2（n）:
定义变长多字节字符串
Long:
定义变长字符串
Longraw:
定义变长二进制串
Rowid:
存储物理rowid
Urowid:
存储物理rowid和逻辑rowid;
◆PL/SQL布尔类型
Boolean定布尔变量：
值为true/false/null。
PL/SQL类型，表列不支持该数据类型
◆PL/SQL日期时间类型
Date:
日期时间，数据长度固定为7字节。
Timestamp[（precision）]:
date的扩展，其数值不仅包括了年，月，日，小时，分钟，秒而且包含了时区，上下午标记以及秒的小数部分,precision:
指定小数部分
Timestamp[（precision）]withtimezone:
timestamp的扩展，包括了时区位置
Timestamp[（precision）]withlocaltimezone:
包括了时区位置
Interval[（precision）]yeartomonth：
定义年月间隔。
Precision:
用于指定年成员的位数
Intervalday[（precision1）]tosecond[（precision2）]:
定义日小时分钟秒的间隔。
P1用于指定日成员的数字位数。
P2：
用于指定秒成员的数字位数
◆定义标量变量
当在pl/sql块中引用标量变量时，必须首先在定义部分定义标量变量，后才能在执行部分或异常处理部分中使用这些标量变量。
定义标量变量的语法如下：
Identifier[constrant]datatype[notnull][:
=|defaultexpr
变量名定义常量数据类型强制初始化变量初始值（可以是文本，其他变量，函数等）
V_enamevarchar2（10）;c_tax_rateconstrantnumber（5,2）--->定义常量
V_blancebinary_float;v_validBooleannotnulldefaultflashe;--->初始化默认值
◆使用标量变量
下面以使用替代变量输入雇员名，并输出雇员名，工资，个人所得税为例
SQL>declare
2v_enamevarchar2（5）;
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/
◆使用%type属性
SQL>declare
2v_enameemp.ename%type;
3v_salemp.sal%type;
4c_tax_rateconstantnumber（3,2）:
=0.03;
5v_tax_salv_sal%type;
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/
⏹复合变量
用于存放多个数值的PL/SQL变量。
包括以下四种
◆PL/SQL记录
Pl/sql记录类似3gl语言的结构，每条pl/sql记录包含多个成员。
当使用pl/sql记录时，首先需要定义记录类型和记录变量，然后才能引用记录变量。
引用记录成员时，需要加记录变量作为前缀。
下面以定义记录类型emp_record_type（成员：
ename,salary,title）和记录变量emp_record里面是逗号
declare
typeemp_record_typeisrecord（
v_nameemp.ename%type,
v_salemp.sal%type,
titleemp.job%type）;
emp_recordemp_record_type;
begin
selectename,sal,jobintoemp_record
fromempwhereempno=&eno;
v_tax_sal:
=v_sal*c_tax_rate;
dbms_output.put_line（'雇员名'||emp_record.v_ename）;
dbms_output.put_line（'雇员工资'||emp_record.v_sal）;
dbms_output.put_line（'岗位'||emp_record.title）;
end;
◆PL/SQL表
类似于3GL语言的数组。
当使用pl/sql表时，首先定义Pl/sql表类型，和pl/sql表变量，然后才能引用pl/sql表变量
下面的例子：
定义pl/sql表类型ename_table_type和pl/sql表变量ename_table,使用替代变量输入雇员名，并输出雇员名（元素ename_table（-1）存放雇员名）为例
SQL>setserveroutputon;
SQL>
SQL>declare
2typeename_table_typeistableofemp.ename%type
3indexbybinary_integer;
4ename_tableename_table_type;
5begin
6selectenameintoename_table（-1）fromemp
7whereempno=&eno;
8dbms_output.put_line（'雇员名'||ename_table（-1））;
9end;
10/
雇员名ALLEN
PL/SQLproceduresuccessfullycompleted
◆嵌套表
类似于pl/sql表，但嵌套表可以作为表列的数据类型，而pl/sql表不能作为表列的数据类型。
当表列使用嵌套表类型时，需要使用createtype语句建立嵌套表类型，并且嵌套表列数据需要存储在单独的存储表中。
下面以建立对象类型emp_type,嵌套表类型emp_array和表department为例
1、创建类型ITEM_TYPE：
此类型中，对于每个ITEM_TYPE类型都包含有一个记录，记载了其名称、类型、层次和数量信息。
CREATEORREPLACETYPE"ITEM_TYPE"AsObject
（
Item_SeqNumber,
Item_NameVarchar2（100）,
Item_TypeVarchar2（100）,
Item_LevelVarchar2（100）,
Item_AmtNumber
）
2、创建ITEM_LIST_TYPE：
此类型将用作一个嵌套表的基础类型。
CREATEORREPLACETYPE"ITEM_LIST_TYPE"AsTableOfitem_type
3、创建表T_MAC_INFO：
设备资费信息表
--Createtable
createtableT_MAC_INFO
（
CHARGE_NOVARCHAR2（100）,
MAC_TYPEVARCHAR2（100）,
SUBTOTALNUMBER,
ITEMSITEM_LIST_TYPE,
MAC_SEQNUMBER,
PRODUCT_SEQNUMBER
）
nestedtableITEMSstoreasITEMS_TAB
4、向嵌套表中插入记录
变量声明：
Item_List1Item_List_Type;
Item1Item_Type;
ForIin1…10toloop
Item_List1.Extend;
Item_List1（Item_List1.Count）:
=Item_Type（Item_List1.Count,Item1.Item_Name,Item1.Item_Type,Item1.Item_Level,Item1.Item_Amt）;
Endloop;
InsertIntot_Mac_Info
（--Cust_Id,
Product_Seq,
Mac_Seq,
Charge_No,
Mac_Type,
Subtotal,
Items）
Values
（--Mac_Info1.Cust_Id,
v_Seq_Product,--Mac_Info1.Product_Seq,
Mac_Seq,
V_Charge_No,
V_Mac_Type,
V_Subtotal,
Item_List1）;
commit;
其中ITEM_LIST1作为一嵌套表，被插入保存在T_MAC_INFO这个表里面。
5、查询嵌套表
Select*Fromt_Mac_Infod,Table（d.Items）Emp
◆VARARY（变长数组）
它可以作为表列和对象类型属性的数据类型。
首先需要建立varray类型，但varray列数据不需要专门的存储表。
createtypearticle_typeasobject（
titlevarchar2（30）,pubdatedate）;
/
Typecreated
SQL>createtypearticle_arrayisvarray（20）ofarticle_type;
2/
Typecreated
SQL>createtableauthor（
idnumber（6）,namevarchar2（10）,articlearticle_array）;
Tablecreated
⏹参照变量（类似与3GL的指针）
通过使用参照变量，一方面可以提高pl/sql编程的灵活性，另一方面可以使得pl/sql块共享相同对象，从而降低空间占用
◆REFCURSOR
用于定义游标变量。
通过使用游标变量，可以在打开游标时指定不同select语句，从而实现动态游标操作。
下面以使用替代变量动态输入名，表明和where子句条件，并分别显示部门表和雇员表的查询结果。
来说明refcursor实现动态游标
SQL>declare
2typec1isrefcursor;
3dyn_cursorc1;
4col1varchar2（20）;
5col2varchar2（20）;
6begin
7opendyn_cursorforselect&col1,&col2from&tabwhere&con;
8fetchdyn_cursorintocol1,col2;
9dbms_output.put_line（'col1:
'||col1）;
10dbms_output.put_line（'col2:
'||col2）;
11closedyn_cursor;
12end;
13/
/---->执行一个程序
/---->执行另一个程序
◆REFobj_type
用于定义特定对象类型的指针类型，并且ref实际是指向对象实例的指针，开发pl/sql对象类型应用时，为了共享对象，节省对象空间占用，可以使用refobject_type。
SQL>createorreplacetypehouse_typeasobject（
2streetvarchar2（50）,cityvarchar2（20）,
3statevarchar2
升级会员 