pl基础.docx
- 文档编号:24591576
- 上传时间:2023-05-29
- 格式:DOCX
- 页数:16
- 大小:20.43KB
pl基础.docx
《pl基础.docx》由会员分享,可在线阅读,更多相关《pl基础.docx(16页珍藏版)》请在冰豆网上搜索。
pl基础
一、基础与概念
1、PL/SQL不区分大小写,除非是由引号引起来的字符串。
1、
2、PL/SQL标识符的命名规则
标识符的最大长度是30个字符,包括字母,数字,$,_,#
不可包含保留字
要以字来打头
不能和同一块中的表中的字段名一样
3、命名规范
sql*plussubstitutionvariablep_name
variablev_name
constantc_name
sql*plusglobalvariableg_name
exceptione_name
4、注销方式
--注释内容
/*……注释内容………….*/
5、只要表达式里有NULL,则整个表达式的值为NULL
BOOLEAN和NULL
这里要注意的是:
NULLANDTRUE==NULL
NULLANDFALSE==FALSE
NULLORTRUE==TRUE
NULLORFALSE==NULL
6、PL/SQLEnvironment
PL/SQLBlcok-----mon-sql-----proceduralstatementexecutor
-----sql------------sqlstatementexecutor
7、分类
procedures:
执行一个动作,做为一个pl/sql来执行,可以返回一个值
function:
计算一个值,用于嵌入到表达式中,并必须返回一个值
package:
把函数和过程逻辑的关联起来
8、pl/sqlblockstructure
header:
Containsthesubprogramname,type,andarguments.Onlyusedforsubprograms.
Declarative:
Containsthelocalidentifiersfortheblock.
Executable:
ContainstheSQLstatementsandPL/SQLcontrolstatements.
Exception:
Performsactionswhenerrorsoccur.
9、DCL和DDL都不被pl/sql支持。
DML和commit可以被支持.
10、一个嵌套块成为封闭块里的一个可执行语句,一个块可嵌套在任何允许放置可执行语句的地方,包括执行部分和异常处理部分。
11、PL/SQL表达式不能包含组函数,但一个PL/SQL块里的SQL语句可以。
12、当一个语句中有混合的数据类型时,PL/SQL可以动态转变。
如:
想把一个NUMBER值存进一个VARCHAR2的变量里,PL/SQL会动态地把NUMBER值转变为VARCHAR2类型的字符值。
13、PL/SQL表达式可以包含SQL函数。
14、嵌套块里的语句不能包含一个异常段。
15、PL/SQL中的SELECT语句必须使用INTO子句。
二、数据类型:
1)BaseScalarDatatypes
2)•
ØVARCHAR2:
在PL/SQL中可以存储2000,在oracle8中是4000字节
ØNUMBER[(precision,scale)]:
当声明时。
默认值是null,
指数用1个字节
尾数中的每两个有效数字用1个字节
负数用1个字节
ØDATE:
存储日期型
ØCHAR[(maximum_length)]:
如没有指定,数据类型为CHAR的列默认长度为1。
这个长度的范围是1到2000。
ØLONG:
用于长度不定,最大值为2G的字符型数据。
ØRow用来存二进制,在PL/SQL中最大长度是32767,在oracle8中是255字节
ØLONGRAW:
可以容纳rawbinarydata
ØBOOLEAN:
只能为trueorfalseornull
ØBINARY_INTEGER
ØROWID数据类型:
ROWID是数据库中每行的唯一标识符,ROWID在磁盘上需要10个字节的存储空间,并使用18个字符来显示
ØPLS_INTEGER
2)CompositeDatatypes
ØPL/SQLTABLES:
PrimaryKey:
KEY的类型是BINARY_INTEGER
VALUE:
真正的值
声明:
TYPEtype_nameISTABLEOF
{column_type|variable%TYPE|table.column%TYPE}[NOTNULL]
[INDEXBYBINARY_INTEGER];
identifiertype_name;
eg1:
TYPEt_nametableISTABLEOFVARCHAR
(2)
INDEXBYBINARY_INTEGER;
引用:
t_nametable
(1):
=’jact’
eg2:
DECLARE
TYPEdept_table_typeISTABLEOFdept%ROWTYPE
INDEXBYBINARY_INTEGER;
dept_tabledept_table_type;
引用:
dept_table
(1).row1:
=’jact’;
ØPL/SQLRECORDS
定义:
TYPEtype_nameISRECORD
(field_declaration[,field_declaration]…);
identifiertype_name;
TYPErecord_nameISRECORD(
namevarchar(20),
idnumber(9)
)
引用:
studentrecord_name
student.name
student.id
3)3)
4)Lob:
用来存二进制,最大长度是4GB
CLOB:
RECIPE
BLOB:
PHOTO(可以容纳4G的二进制)
BFILE:
MOVIE把二进制存在一个扩展的文件中。
容量是4G
NCLOB
三、PL/SQLBlockStructure
DECLARE---Optional
-Variables,Constants,cursors,user-definedexceptions
BEGIN---Mandatory
-SQLstatements
-PL/SQLcontrolstatements
EXCEPTION-Optional
-Actionstoperformwhenerrorsoccur
END:
---Mandatory
Blocktype
Anonymous:
[DECLARE]
BEGIN
……..
[EXCEPTION]
END;
Procedure:
PROCEDUREnameIS
[DECLARE]
BEGIN
……..
[EXCEPTION]
END;
Function:
FUNTIONname
RETURNdatatypeIS
[DECLARE]
BEGIN
……..
RETURNvalue;
[EXCEPTION]
END;
四、变量
1、变量的赋值:
identifier[CONSTANT]datatype[NOTNULL][:
=|DEFAULTexpr];
2、
变量名number(9,2)NOTNULL:
=0;//在声明一个PL/SQL参数时,可以使用赋值运算符:
=为该参数赋初值。
如果没有为参数赋初值,参数会被设置为null。
如果参数被定义NOTNULL约束,那么就必须赋初值。
3、变量的类型:
变量名number(9,2);
变量名saray%type;//这样value的类型就和saray一样,也可以用列名取代saray
变量名parts%rowtype
//parts是表名,这是个记录类型的变量。
其内部结构和parts一样
是根据表或视图的列来定义各变量
用表名做为前缀
结构和表的结构一样
4、在PL/SQL调用sqlplus的变量,在变量前面加”:
”
:
g_monthly_sal:
=v_sal/12;
五、控制程序流
1、loop
程序段
exit[when表达式]
endloop;
可以为循环设定标签
EG:
BEGIN
<
LOOP
v_counter:
=v_counter+1;
EXITWHENv_counter>10;
<
LOOP
...
EXITOuter_loopWHENtotal_done='YES';
--Leavebothloops
EXITWHENinner_done='YES';
--Leaveinnerlooponly
...
ENDLOOPInner_loop;
...
ENDLOOPOuter_loop;
END;
2、while表达式loop
程序段
endloop;
3、forIin1..20loop
程序段
endloop;
4、if[not]…..then
程序段
elsif….then
程序段
else
程序段
endif;
5、message1
<
a:
=a+1
注意:
不可以同外层跳到里层
不可以从一个IF子句跳到另一个IF子句
不可以从异常处理块跳到当前块
6、selectinto为变量赋值
selectnameintov_namewhere…
六、游标
游标的本质是SQL语句的一个工作区域,用于处理多行记录集的查询。
分类:
Implicitcursors:
由DML和PL/SQL的SELECT隐式的定义,不可以使用open,fetch和close去控制这个sql游标。
每一次只处理一行。
但是可以使用游标的属性
Explicitcursors:
由程序员定义,显式游标用于返回多于一行的查询
以下讨论的都是显式游标
执行的四个步骤
:
1、
1、声明:
定义游标的名字和结构,select中可以使用orderby
2、打开游标:
执行查询同时绑定所有涉及到的变量
执行的内容:
为select分配内存并分析select语句
绑定输入的变量
配置指针在活动集的第一行
注意:
如果查询不返回结果,不会引发PL/SQL的异常,你可以在执行fetch后测试返回的结果
如果游标内的声明包括update。
一样会执行行锁定
3、Fetch:
把当前行的值赋给变量,每个fetch都会把游标指针向下移动一行。
如果到了最后一行就会自动退出forloop
4、关闭:
释放活动的集,可以再次使用open
游标的几个属性:
SQL%ROWCOUNT受最近执行的SQL语句影响的行的数目。
(一个整数值)
SQL%FOUNDBoolean属性,如果最近的SQL语句影响了一行或多行,其值为
TRUE。
SQL%NOTFOUNDBoolean属性,如果最近的SQL语句没有影响任何行,其值为
TRUE。
SQL%ISOPEN总是为FALSE,原因是PL/SQL总是它们结束执行后立即关闭内隐游标。
例子
EG1:
常规用法
CURSORc1IS
SELECTempno,ename
FROMemp;
emp_recordc1%ROWTYPE;
BEGIN
OPENc1;
...
FETCHc1INTOemp_record;
EG2:
使用for循环实现游标
DECLARE
CURSORc1IS
SELECTempno,ename
FROMemp;
emp_recordc1%ROWTYPE;
BEGIN
FORemp_recordINc1LOOP
--implicitopenandimplicitfetchoccur
IFemp_record.empno=7839THEN
...
ENDLOOP;--implicitcloseoccurs
END;
游标FOR循环立时不需要FETCH语句的。
游标打开,在循环中每次重复提取一行,所有的行都处理后,游标会自动关闭。
EG3:
不定义游标的方式
BEGIN
FORemp_recordIN(SELECTempno,ename
FROMemp)LOOP
--implicitopenandimplicitfetchoccur
IFemp_record.empno=7839THEN
...
ENDLOOP;--implicitcloseoccurs
END;
EG4
:
带变量的游标------你必须指定指定参数的数据类型,但不用指定大小
DECLARE
CURSORc1
(v_deptnoNUMBER,v_jobVARCHAR2)IS
SELECTempno,ename
FROMemp
WHEREdeptno=v_deptno
ANDjob=v_job;
BEGIN
OPENc1(10,'CLERK');
...
EG5:
FORUPDATE----实际上是把select出来的记录都加锁。
其他事务不能对这些记录加锁或者update这些。
SELECT...FROM...
FORUPDATE[OFcolumn_reference][NOWAIT]
DECLARE
CURSORc1IS
SELECTempno,ename
FROMemp
FORUPDATENOWAIT;
NOWAIT:
返回一个oracle的错误信息如果此行给其他的会话锁住了。
EG6:
WHERECURRENTOF:
对于游标来说,处理记录的时候,是一条一条记录来处理的,使用WHERECURRENTOF,实际上就是指明是游标正在处理的当前行记录。
注意:
必须使用FORUPDATE去锁住行
使用WHERECURRENTOF去指向当前的行
DECLARE
CURSORc1IS
SELECT...
FORUPDATENOWAIT;
BEGIN
...
FORemp_recordINc1LOOP
UPDATE...
WHERECURRENTOFc1;
...
ENDLOOP;
COMMIT;
END;
七、处理异常
PredefinedException
BEGINSELECT...COMMIT;
EXCEPTION
WHENNO_DATA_FOUNDTHEN
statement1;
statement2;
WHENTOO_MANY_ROWSTHEN
statement1;
WHENOTHERSTHEN
statement1;
statement2;
statement3;
END;
…….
Non-PredefinedException
DECLARE
e_products_invalidEXCEPTION;
PRAGMAEXCEPTION_INIT(
e_products_invalid,-2292);
v_messageVARCHAR2(50);
BEGIN
...
EXCEPTION
WHENe_products_invalidTHEN
:
g_message:
='Productcode
specifiedisnotvalid.';
...
END;
User-DefinedException
DECLARE
e_amount_remainingEXCEPTION;
...
BEGIN
...
RAISEe_amount_remaining;
...
EXCEPTION
WHENe_amount_remainingTHEN
:
g_message:
='Thereisstillanamount
instock.';
...
END;
RAISE_APPLICATION_ERROR
DECLARE
…….
InvalidpartEXCEPTION;
BEGIN
…….
IFSQL%NOTFOUNDTHEN
RAISEinvalidpart;
ENDIF;
EXCEPTION
WHENinvalidpartTHEN
Raise_application_error(-20003,’InvalidPartid#’||partnum);
WHENOTHERSTHEN
Raise_application_error(-20000,errNum||errMsg);
END
✧使用EXCEPTION关键字在一个PL/SQL块的声明部分声明用户自定义异常
✧使用PL/SQL命令RAISE检测用户自定义异常
✧PL/SQL可使用Raise_application_error过程返回一个用户自定义错误数和消息给调用环境。
所有的用户自定义错误消息必须在-20000到-20999之间
✧PL/SQL程序可以使用WHENOTHERS异常处理来处理没有特定处理的所有异常
✧PL/SQL程序可以使用特殊的SQLCODE和SQLERRM函数返回最新的oracle错误号码和消息
DECLARE
v_error_codeNUMBER;
v_error_messageVARCHAR2(255);
BEGIN
...
EXCEPTION
...
WHENOTHERSTHEN
ROLLBACK;
v_error_code:
=SQLCODE;
v_error_message:
=SQLERRM;
INSERTINTOerrorsVALUES(v_error_code,
v_error_message);
END;
数据库联接
定义:
CREATEDATABASELINKlink_name
CONNECTTOusernameIDENTIFIEDBYpassword
USINGsqlnet_string;
使用:
表名@link_name
替代名
CREATESYNONYMsynonym_nameFORreference;
Eg:
CREATESYNONYMbackupFORbackup@esal
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- pl 基础