PLSQL 学习笔记.docx
- 文档编号:25258632
- 上传时间:2023-06-06
- 格式:DOCX
- 页数:45
- 大小:34.92KB
PLSQL 学习笔记.docx
《PLSQL 学习笔记.docx》由会员分享,可在线阅读,更多相关《PLSQL 学习笔记.docx(45页珍藏版)》请在冰豆网上搜索。
PLSQL学习笔记
PL/SQLStudy
说明:
本文档是平时工作的积累,正在完善和补充,这里基本采用80/20法则,大部分内容是工作中经常用到的,并且可以解决大部分的问题。
由于里面夹杂各种参考资料以及自己对这些资料的理解,其间可能存在许多不全面的,甚至错误的地方。
作者:
Garfield
发布日期:
2009-9-22
修改日期:
目录
第一章PL/SQL基础4
1基础概念4
1.1PL/SQL4
1.1.1PL/SQL的优点4
1.1.2可用于PL/SQL程序的SQL语句4
1.1.3运行PL/SQL程序4
1.2PL/SQL块4
1.2.1PL/SQL块结构4
1.2.2PL/SQL块分类5
1.2.3注释5
1.3PL/SQL变量5
1.3.1标识符5
1.3.2变量定义6
1.3.3变量赋值6
1.3.4变量作用范围及可见性7
1.4变量类型7
1.4.1简单类型7
1.4.2记录类型7
1.4.3%TYPE8
1.4.4%ROWTYPE8
1.4.5LOB9
1.5运算符9
1.5.1关系运算符9
1.5.2算术运算符9
1.5.3逻辑运算符9
2流程控制语句10
2.1条件语句10
2.1.1IF语句10
2.1.2CASE语句10
2.2循环语句11
2.2.1LOOP循环11
2.2.2WHILE循环11
2.2.3FOR循环11
2.3其他语句12
2.3.1标号和GOTO12
2.3.2NULL语句12
2.3.3EXIT13
3游标13
3.1游标概念13
3.2显式游标13
3.2.1显式游标处理步骤13
3.2.2显示游标属性15
3.2.3显示游标的FOR循环15
3.3隐式游标16
3.4游标修改和删除操作17
4异常18
4.1异常18
4.1.1异常处理概念18
4.1.2预定义异常处理18
4.1.3非预定义异常处理19
4.1.4用户自定义异常处理20
4.2异常传播20
4.2.1在执行部分引发异常21
4.2.2在声明部分引发异常21
4.3异常错误处理编程21
第二章PL/SQL编程23
1存储过程23
1.1函数23
1.1.1创建函数23
1.1.2函数的调用24
1.1.3参数默认值25
1.2存储过程25
1.2.1创建过程25
1.2.2调用存储过程26
1.3过程与函数26
2包26
2.1包的概念26
2.2创建包26
2.2.1创建包定义26
2.2.2创建包主体27
2.2.3包实例27
第一章PL/SQL基础
1基础概念
1.1PL/SQL
PL/SQL是ProcedureLanguage&StructuredQueryLanguage的缩写,是Oracle的在标准SQL的基础上加上过程化语言的处理。
目前的PL/SQL包括两部分,一部分是数据库引擎部分;另一部分是可嵌入到许多产品(如C语言,JAVA语言等)工具中的独立引擎。
可以将这两部分称为:
数据库PL/SQL和工具PL/SQL。
两者的编程非常相似,工具PL/SQL另外还增加了用于支持工具(如OracleForms)的句法。
1.1.1PL/SQL的优点
有利于客户/服务器环境应用的运行。
对于客户/服务器环境来说,真正的瓶颈是网络上。
无论网络多快,只要客户端与服务器进行大量的数据交换。
应用运行的效率自然就回受到影响。
如果使用PL/SQL进行编程,将这种具有大量数据处理的应用放在服务器端来执行。
自然就省去了数据在网上的传输时间。
适合于客户环境。
PL/SQL分为数据库PL/SQL和工具PL/SQL。
对于客户端来说,PL/SQL可以嵌套到相应的工具中,客户端程序可以执行本地包含PL/SQL部分,也可以向服务发SQL命令或激活服务器端的PL/SQL程序运行。
1.1.2可用于PL/SQL程序的SQL语句
PL/SQL是Oracle系统的核心语言,现在Oracle的许多部件都是由PL/SQL写成。
在PL/SQL中只能用SQL语句中的DML部分,如INSERT、UPDATE、DELETE、SELECTINTO、COMMIT、ROLLBACK、SAVEPOINT。
如果要在PL/SQL中使用DDL,如CREATETABLE,只能以动态的方式来使用。
如:
Excuteimmediate‘droptablexxx’;
1.1.3运行PL/SQL程序
PL/SQL程序的运行是通过Oracle中的一个引擎来进行的。
这个引擎可能在Oracle的服务器端,也可能在Oracle应用开发的客户端。
引擎执行PL/SQL中的过程性语句,然后将SQL语句发送给数据库服务器来执行,再将结果返回给执行端。
1.2PL/SQL块
1.2.1PL/SQL块结构
PL/SQL程序由三个块组成:
声明部分、执行部分、异常处理部分。
声明部分:
定义程序用到的常量、变量、类型、游标,以及局部的过程和函数。
注意:
在声明部分定义的过程和函数只在该程序中有效。
执行部分:
定义需要执行的事物,由SQL和PL/SQL语句组成。
异常部分:
处理程序中的异常和错误。
其中执行部分是必须的,并且各个部分可以互相嵌套,声明部分可以有异常处理,执行部分可以有异常处理,异常部分可以有执行部分。
PL/SQL块结构如下:
DECLARE
/*声明部分*/
BEGIN
/*执行部分*/
EXCEPTION
/*异常部分*/
END;
1.2.2PL/SQL块分类
无名块:
也叫匿名块,每次动态构造,不保存在数据库中。
子程序:
存储在数据库中的过程、函数、包,需要人为的调用。
触发器:
存储在数据库中,不由人为控制,当数据库发生操作时会触发一些事件,从而自动执行相应的程序。
1.2.3注释
可以使用两种符号来写注释,使用双‘-‘(减号)加单行注释,使用/**/来加一行或多行注释。
如:
v_salNUMBER(12,2);--工资变量。
/***********************************************/
/*文件名:
statistcs_sal.sql*/
/***********************************************/
1.3PL/SQL变量
1.3.1标识符
PL/SQL中的标识符定义与SQL中的标识符要求相同:
1、标识符长度不能超过30字符(不包括双引号);
2、第一个字符必须为字母;
3、不分大小写;
4、不能用’-‘(减号)、空格;
5、不能是SQL保留字;
6、如果希望标识符能够区分大小写或者包含特殊字符,可以使用带双引号的标识符,如”SELECT”、”DEPTID”,但不建议这样命名。
提示:
不要把变量名与表中字段名定义完全一样,如果这样可能得到不正确的结果。
如:
DECLARE
enamevarchar2(20):
=’KING’;
BEGIN
DELETEFROMempWHEREename=ename;
END;
上面的例子将会删除所有的纪录,而不是”KING”的记录
通常情况下,建议所有编程人员共同遵守一定的要求,如:
标识符
命名规则
例子
变量
v_name
v_person
常量
c_name
c_person
游标
name_cursor/name_cur
emp_cursor/emp_cur
异常
e_name
e_too_many
记录类型
name_record/name_rec
emp_record/emp_rec
过程、函数参数
p_name
p_sal
1.3.2变量定义
变量定义语法:
variable[CONSTRAINT]type[NOTNULL][:
=value]
variable表示变量名;type表示变量类型;value表示变量初始值;NOTNULL表示变量不能为空,必须赋初值;CONSTRAINT表示定义的是一个常量,必须有初值。
注意:
任何合法的标识符都可作为变量;如果一个变量定义为NOTNULL,那么变量必须有初始值,而且在程序中不能赋NULL;如果没有给变量赋初值,系统默认为NULL;在定义变量时,’:
=’可以用’DEFAULT’替代。
1.3.3变量赋值
变量赋值语法如下:
variable:
=expression;
variable是一个变量,expression是一个表达式.
1、字符及数字运算特点
空值加数字仍是空值:
NULL+<数字>=NULL
空值加(连接)字符,结果为字符:
NULL||<字符串>=<字符串>
2、BOOLEAN赋值
布尔值只有TRUE,FALSE及NULL三个值。
如:
3、数据库赋值
数据库赋值是通过SELECT语句来完成的,每次执行SELECT语句就赋值一次,一般要求被赋值的变量与SELECT中的列名要一一对应。
提示:
不能将SELECT语句中的列赋值给布尔变量。
4、CHAR转换为NUMBER
使用TO_NUMBER函数来完成字符到数字的转换,如:
v_total:
=TO_NUMBER(‘100.0’)+sal;
5、NUMBER转换为CHAR
使用TO_CHAR函数可以实现数字到字符的转换,如:
v_comm:
=TO_CHAR(‘123.45’)||’元’;
6、字符转换为日期
使用TO_DATE函数可以实现字符到日期的转换,如:
v_date:
=TO_DATE('2001.07.03','yyyy.mm.dd');
7、日期转换为字符
使用TO_CHAR函数可以实现日期到字符的转换,如:
v_to_day:
=TO_CHAR(SYSDATE,'yyyy.mm.ddhh24:
mi:
ss');
1.3.4变量作用范围及可见性
与其它高级语言类似,PL/SQL的变量作用范围特点是:
变量的作用范围是在所引用的程序单元(块、子程序、包)内,即从声明变量开始到该块的结束。
一个变量(标识)只能在所引用的块内是可见的。
当一个变量超出了作用范围,PL/SQL引擎就释放用来存放该变量的空间(因为它可能不用了)。
在子块中重新定义该变量后,它的作用仅在该块内。
1.4变量类型
1.4.1简单类型
PL/SQL中常用的简单类型有:
NUMBE、RVARCHAR2、CHAR、DATE、BOOLEAN(SQL中没有)。
例、插入一条记录并显示;
DECLARE
InfoVARCHAR2(40);
BEGIN
INSERTINTOdeptVALUES(90,‘SERVICE’,‘BEIJING’)
RETURNINGdname||’:
’||to_char(deptno)||’:
’||locINTOinfo;
DBMS_OUTPUT.PUT_LINE(info);
END;
其中:
RETURNING子句用于检索INSERT语句中所影响的数据行数,当INSERT语句使用VALUES子句插入数据时,RETURNING子句还可将列表达式、ROWID和REF值返回到输出变量中。
在使用RETURNING子句是应注意以下几点限制:
1、不能并行DML语句和远程对象一起使用;
2、不能检索LONG类型信息;
3、当通过视图向基表中插入数据时,只能与单基表视图一起使用。
1.4.2记录类型
记录类型是把逻辑相关的数据作为一个单元存储起来,必须包括至少一各简单类型或RECORD数据类型的成员。
定义记录类型语法如下:
TYPErecord_typeISRECORD(
field1type1[NOTNULL][:
=exp1],
field2type2[NOTNULL][:
=exp2],
......
fieldntypen[NOTNULL][:
=expn]);
record_type表示记录类型的名称,field表示记录类型域的名称,type表示域的类型,exp表示域的初始值,”:
=”可以用”DEFAULT”替代。
例、记录类型
DECLARE
TYPEtest_recISRECORD(
codeVARCHAR2(10),
nameVARCHAR2(30)NOTNULL:
=’abook’);
v_booktest_rec;
BEGIN
v_book.code:
=’123’;
v_book.name:
=’C++Programming’;
DBMS_OUTPUT.PUT_LINE(v_book.code||v_book.name);
END;
1.4.3%TYPE
定义一个变量,其数据类型与已经定义的某个数据变量的类型相同,或者与数据库表的某个列的数据类型相同,这时可以使用%TYPE。
使用%TYPE的优点在于:
1、所引用的数据库列的数据类型可以不必知道;
2、所引用的数据库列的数据类型可以实时改变。
例、%TYPE类型
DECLARE
TYPEt_recISRECORD(
t_noemp.empno%TYPE,
t_nameemp.ename%TYPE,
v_empt_rec;
BEGIN
SELECTempno,enameINTOv_empFROMempWHEREempno=7788;
DBMS_OUTPUT.PUT_LINE(TO_CHAR(v_emp.t_no)||v_emp.t_name);
END;
1.4.4%ROWTYPE
PL/SQL提供%ROWTYPE操作符,返回一个记录类型,其数据类型和数据库表的数据结构相一致。
使用%ROWTYPE特性的优点在于:
1、所引用的数据库中列的个数和数据类型可以不必知道;
2、所引用的数据库中列的个数和数据类型可以实时改变。
例,%ROWTYPE
DECLARE
recemp%ROWTYPE;
BEGIN
SELECT*INTOrecFROMempWHEREempno=v_empno;
DBMS_OUTPUT.PUT_LINE('姓名:
'||rec.ename||'工资:
'||rec.sal||'工作时间:
'||rec.hiredate);
END;
1.4.5LOB
Oracle提供了LOB(LargeOBject)类型,用于存储大的数据对象的类型,如BFILE,BLOB,CLOB类型。
BFILE(Movie):
存放大的二进制数据对象,这些数据文件不放在数据库里,而是放在操作系统的某个目录里,数据库的表里只存放文件的目录。
BLOB(Photo):
存储大的二进制数据类型。
变量存储大的二进制对象的位置。
CLOB(Book):
存储大的字符数据类型。
每个变量存储大字符对象的位置,该位置指到大字符数据块。
1.5运算符
1.5.1关系运算符
运算符
意义
=
等于
<>,!
=,~=,^=
不等于
<
小于
>
大于
<=
小于或等于
>=
大于或等于
1.5.2算术运算符
运算符
意义
+
加号
-
减号
*
乘号
/
除号
:
=
赋值号
=>
关系号
..
范围运算符
||
字符连接符
1.5.3逻辑运算符
运算符
意义
ISNULL
是空值
BETWEEN
介于两者之间
IN
在一列值中间
AND
逻辑与
OR
逻辑或
NOT
取返,如ISNOTNULL,NOTIN
2流程控制语句
2.1条件语句
2.1.1IF语句
IF<布尔表达式>THEN
PL/SQL和SQL语句
ENDIF;
IF<布尔表达式>THEN
PL/SQL和SQL语句
ELSE
其它语句
ENDIF;
IF<布尔表达式>THEN
PL/SQL和SQL语句
ELSIF<其它布尔表达式>THEN
其它语句
ELSIF<其它布尔表达式>THEN
其它语句
ELSE
其它语句
ENDIF;
提示:
ELSIF不能写成ELSEIF。
布尔表达式为TRUE,执行THEN后面的语句,否则(包括FALSE和NULL)执行ELSE后面的语句。
2.1.2CASE语句
搜索型CASE语句:
CASEselector
WHENexpression1THENresult1
WHENexpression2THENresult2
WHENexpressionNTHENresultN
[ELSEresultN+1]
END;
当selector的值等于expressionX的值时,执行THEN语句。
如果没有匹配的表达式,则执行ELSE语句。
条件型CASE语句:
CASE
WHENcondition1THENresult1
WHENcondition2THENresult2
WHENconditionNTHENresultN
[ELSEresultN+1]
END;
当WHEN的条件成立,执行THEN后面的语句。
如果所有条件都不成了,执行ELSE语句。
2.2循环语句
2.2.1LOOP循环
LOOP
要执行的语句;
EXITWHEN<条件语句>/*条件满足,退出循环*/
ENDLOOP;
或者
LOOP
EXITWHEN<条件语句>/*条件满足,退出循环*/
要执行的语句;
ENDLOOP;
第一个LOOP先执行语句,再判断条件;第二个LOOP先判断条件,再执行语句,可以用于不同场合。
2.2.2WHILE循环
WHILE<布尔表达式>LOOP
要执行的语句;
ENDLOOP;
布尔表达式为TRUE,执行循环,否则执行循环下面的语句。
2.2.3FOR循环
FOR循环计数器IN[REVERSE]下限..上限LOOP
要执行的语句;
ENDLOOP;
每循环一次,循环变量自动加1;使用关键字REVERSE,循环变量自动减1。
跟在INREVERSE后面的数字必须是从小到大的顺序,而且必须是整数,不能是变量或表达式。
可以使用EXIT退出循环。
例、FOR循环.
BEGIN
FORintin1..10LOOP
DBMS_OUTPUT.PUT_LINE('int的当前值为:
'||int);
ENDLOOP;
END;
例、FORREVERSE循环
BEGIN
FORintINREVERSE1..9LOOP
DBMS_OUTPUT.PUT_LINE('int的当前值为:
'||int);
ENDLOOP;
END;
2.3其他语句
2.3.1标号和GOTO
PL/SQL中GOTO语句是无条件跳转到指定的标号去的意思。
语法如下:
GOTOlabel;
......
<
例、GOTO
DECLARE
v_counterNUMBER:
=1;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE('v_counter的当前值为:
'||v_counter);
v_counter:
=v_counter+1;
IFv_counter>10THEN
GOTOl_ENDofLOOP;
ENDIF;
ENDLOOP;
<
DBMS_OUTPUT.PUT_LINE('v_counter的当前值为:
'||v_counter);
END;
2.3.2NULL语句
在PL/SQL程序中,可以用NULL语句来说明“不用做任何事情”的意思,相当于一个占位符,可以使某些语句变得有意义,提高程序的可读性。
如:
BEGIN
IFv_numISNULLTHEN
GOTOprint1;
ENDIF;
…
<
NULL;--不需要处理任何数据。
END;
2.3.3EXIT
EXIT或者EXITWHEN
EXIT用于直接退出;EXITWHEN
EXIT可以用于LOOP、WHILE、FOR等循环语句。
3游标
3.1游标概念
为了处理SQL语句,Oracle必须分配一片叫上下文(contextarea)的区域来处理所必需的信息,其中包括要处理的行的数目,一个指向语句被分析以后的表示形式的指针以及查询的活动集(activeset)。
游标是一个指向上下文的句柄(handle)或指针。
通过游标,PL/SQL可以控制上下文区和处理语句时上下文区会发生些什么事情。
对于不同的SQL语句,游标的使用情况不同:
SQL语句
游标
非查询语句
隐式的
结果是单行的查询语句
隐式的或显示的
结果是多行的查询语句
显示的
3.2显式游标
3.2.1显式游标处理步骤
1、定义游标:
就是定义一个游标名,以及与其相对应的SELECT语句。
格式:
CURSORcursor_name[(parameter[,parameter]…)]ISselect_statement;
游标参数只能为输入参数,其格式为:
parameter_name[IN]datatype[{:
=|DEFAULT}expression]
在指定数据类型时,不能使用长度约束。
如NUMBER(4)、CHAR(10)等都是错误的。
2、打开游标:
就是执行游标所对应的SELECT语句,将其查询结果放入工作区,并且指针指向工作区的首部,标识游标结果集合。
如果游标查询语句中带有FORUPDATE选项,OPEN语句还将锁定数据库表中游标结果集合对应的数据行。
格式:
OPENcursor_name[([parameter=>]value[,[parameter=>]value]…)];
在向游标传递参数时,可以使用与函数参数相同的传值方法,位置表示法或者名称表示法。
PL/SQL程序不能用OPEN语句重复打开一个游标。
如果要多次打开一个游标,必须先关闭游标,否则报错。
3、提取游标数据:
就是检索结果集合中的数据行,放入指定的输出变量中。
格式:
FETCHcursor_nameINTO{variable_list|record_variable};
对该记录进行处理;
继续处理,直到活动集合中没有记录;
4、关闭游标:
当提取和处理完游标结果集合数据后,应及时关闭游标,以释放该游标所占用的系统资源,并使该游标的工作区变成无效,不能再使用FETCH语句取其中数据。
关闭后的
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- PLSQL 学习笔记 学习 笔记