35PLSQL基础.docx
《35PLSQL基础.docx》由会员分享,可在线阅读,更多相关《35PLSQL基础.docx(20页珍藏版)》请在冰豆网上搜索。
![35PLSQL基础.docx](https://file1.bdocx.com/fileroot1/2023-1/29/e1d24468-b701-48d6-8e9c-e2af65853d8b/e1d24468-b701-48d6-8e9c-e2af65853d8b1.gif)
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