PlSql甲骨文学习笔记Word格式.docx
《PlSql甲骨文学习笔记Word格式.docx》由会员分享，可在线阅读，更多相关《PlSql甲骨文学习笔记Word格式.docx（62页珍藏版）》请在冰豆网上搜索。
№5实际应用示例（查找表）16
9包19
№1创建包和规范19
№2创建包体19
№3调用包中的过程和函数20
№4获取有关包中的函数和过程的信息21
№5删除包21
10触发器21
№1触发器运行的时机21
№2设置示例触发器的准备工作22
№3创建触发器22
№4激活触发器23
№5获取有关触发器的信息23
№6禁用和启用触发器23
№7删除触发器24
№8实际应用示例（增加修改积分）24
一、创建表
创建表一
CREATETABLETABLE_NAME（
AAAINTEGER
CONSTRAINT约束名PRIMARYKEY,
BBBVARCHAR2（10）NOTNULL,
DOBDATE,
CCCVARCHAR2（14）,
）;
说明:
CONSTRAINT子句用来限制存储在表或列中的值。
对于AAA列，PRIMARYKEY关键字表明每行的AAA必须包含一个唯一的数字。
约束可以使用可选名，他必须跟在CONSTRAINT之后。
创建表二
CONSTRAINT约束名1PRIMARYKEY,
BBBINTEGER
CONSTRAINT约束名2REFERENCESTABLE_NAME1（表1中的主键DDD）
VARCHAR2（10）NOTNULL,
说明：
BBB是对TABLE_NAME1表中DDD列的引用，称之为外键，因为他引用了其他表的列。
包含这个外键的表（TABLE_NAME）称为子表，被引用的表（TABLE_NAME1）称为父表。
当增加1个AAA时，会通过BBB列中提供1个DDD，为AAA关联DDD。
这种关系称为父子关系。
一般情况下，BBB与DDD具有相同的名称。
创建表三
CONSTRAINT约束名1REFERENCESTABLE_NAME1（表1中的主键AAA’）,
CONSTRAINT约束名2REFERENCESTABLE_NAME1（表1中的主键BBB’）,
CONSTRAINT约束名3PRIMARYKEY（AAA,BBB）,
约束名3是个PRIMARYKEY。
对于该表的每条记录来说，这两列值的组合必须唯一。
二、添加、修改、删除
添加
★INSERTINTOTABLE_NAME（
AAA,BBB,CCC,DDD）VALUES（
1,’FIRED’,’06-JAN-1990’,’800-555-1215’
★INSERTINTOgajt_drv_check_34
（SFZMHM,DABH,XM,ZJCX,YZJCX,CCLZRQ,FZRQ,CCFZJG,FZJG,ZT,JBR）
VALUES（'
342421196608107510'
'
340100216391'
tom'
C1E'
EC'
to_date（'
2002-10-18'
yyyy-mm-dd'
）,
TO_DATE（'
2005-1-714:
35:
43'
yyyy-mm-ddhh24-mi-ss'
'
AA'
A'
#'
修改
UPDATETABLE_NAMESETAAA=’XXX’WHEREBBB=’YYY’;
UPDATETABLE_NAMESETAAA=’XX1’,BBB=’ZZZ’WHEREBBB=’YY1’;
UpdateGZ_GongZBA
SetA.FangZ=（SelectB.FangZFromGZ_FeiYHZBBWhereB.YueF=A.YueFAndB.ZhiGZH=A.ZhiGZH）,
A.DianF=（SelectB.DianFFromGZ_FeiYHZBBWhereB.YueF=A.YueFAndB.ZhiGZH=A.ZhiGZH）,
A.ShuiF=（SelectB.ShuiFFromGZ_FeiYHZBBWhereB.YueF=A.YueFAndB.ZhiGZH=A.ZhiGZH）,
A.WeiSF=（SelectB.WeiSFFromGZ_FeiYHZBBWhereB.YueF=A.YueFAndB.ZhiGZH=A.ZhiGZH）,
A.NuanQF=（SelectB.NuanQFFromGZ_FeiYHZBBWhereB.YueF=A.YueFAndB.ZhiGZH=A.ZhiGZH）,
A.BiLDSF=（SelectB.ShouSFFromGZ_FeiYHZBBWhereB.YueF=A.YueFAndB.ZhiGZH=A.ZhiGZH）,
A.WuYF=（SelectB.WuYFFromGZ_FeiYHZBBWhereB.YueF=A.YueFAndB.ZhiGZH=A.ZhiGZH）
WhereYueF=YF;
删除
DELETEFROMTABLE_NAMEWHEREAAA=’XX’;
三、ORACLE10g新增的数据类型
说明
BINARY_FLOAT
BINARY_DOUBLE
NUMBER
所需存储空间小
5字节
9字节
22字节
取整透明
以二进制取整，number以十进制取整。
运算封闭
要么是数字要么是特殊值
表示数字的范围更大
比NUMBER能够表示更大/更小的数据
执行运算的速度更快
通过硬件进行计算，number得先通过软件转化再进行运算。
如果涉及大量数字运算的系统，考虑是用该新数据类型。
示例
CREATTABLETABLE_NAME（
BIN_FLOATBINARY_FLOAT,
BIN_DOUBLEBINARY_DOUBLE,
INSERTINTOTABLE_NAME（
BIN_FLOAT,BIN_DOUBLE）VALUES（
39.5F,15.7D）;
特殊值
描述
BINARY_FLOAT_NAN
非BINARY_FLOAT类型的数字
BINARY_FLOAT_INFINITY
BINARY_FLOAT类型的无穷大
BINARY_DOUBLE_NAN
非BINARY_DOUBLE类型的数字
BINARY_DOUBLE_INFINITY
BINARY_DOUBLE类型的无穷大
示例：
BINARY_FLOAT_INFINITY,BINARY_DOUBLE_INFINITY）;
四、ORACLEPL/SQL简介
是一种过程语言，添加一些基于SQL的编程结构。
PL/SQL主要用来向数据库中添加过程和函数，以实现业务逻辑。
他包含了一些标准的编程结构：
☆块结构
☆变量和类型
☆条件控制/条件逻辑
☆循环
☆游标，保存着查询返回的结果。
☆过程和函数定义
☆包，可以用来将过程和函数组合到一个单元中。
这种集中化的代码可以由任何能够访问数据库的程序调用。
1块结构
PL/SQL程序可划分成称为块的结构，每个快都包含有PL/SQL和SQL语句。
典型的PL/SQL代码块结构包含如下的结构：
[DECLAREDECLARE_STATEMENTS]
BEGIN
EXECCUTEABLE_STATEMENTS
[EXCEPTIONEXCEPTION_HANDLING_STATEMENTS]
END;
声明和异常块是可选的。
☆DECLARE_STATEMENTS包含在声明块中，它负责声明该块其他部分将会使用的变量。
这些变量对于这个块来说是局部变量，也就是说不能从块的外部访问这些变量。
声明总是放在块以开始的位置上。
☆EXECCUTEABLE_STATEMENTS是块中实际可执行的语句，其中可能包含执行诸如循环，条件逻辑等任务的语句。
☆EXCEPTION_HANDLING_STATEMENTS中的语句负责处理可执行语句中可能发生的任何错误。
☆每条语句都由“；
”结尾，整个块由关键字END标志结束。
已知矩形面积和高，计算其宽。
DECLARE
WIDTHINTEGER;
HEIGHTINTEGER:
=2;
--初始化并被赋值
AREAINTEGER;
AREA:
=6;
WIDTH:
=AREA/HEIGHT;
DBMS_OUTPUT.PUT_LINE（'
WIDTH='
||WIDTH）;
--输出到屏幕
EXCEPTION
WHENZERO_DIVIDETHEN
DIVISIONBYZERO'
--异常处理
/--执行PL/SLQ语句
注意:
DBMS_OUTPUT.PUT_LINE必须在SQL/PLUS中输入SETSERVEROUTPUTON才能看到输出的数据
2变量和类型
PL/SQL数据类型与数据库列的类型很相似.格式（变量名变量类型;
）.还可以通过（%TYPE）来定义变量的类型,这个关键字告诉PL/SQL使用与表中列相同的类型.格式（要声明的变量名表名.该表中的列名%TYPE）
3条件逻辑
在PL/SQL中，IF、THEN、ELSE、ELSEIF、ENDIF等关键字用于执行条件逻辑，下面的语法说明了条件逻辑的用法。
IF条件1THEN
PL/SQL语句1
ELSEIF条件2THEN
PL/SQL语句2
ELSE
PL/SQL语句3
ENDIF;
☆条件1、条件2是布尔表达式，其值为真或假。
☆PL/SQL语句1、PL/SQL语句2、PL/SQL语句3条件逻辑的流程如下：
◆如果条件1的值为真，则执行PL/SQL语句1。
◆如果条件1的值为假而条件2的值为真，则执行PL/SQL语句2。
◆如果条件1和条件2的值都为假，则执行PL/SQL语句3。
☆可以在一个IF语句中嵌套另外一个IF语句。
IFCOUNT>
0THEN
MESSAGE:
=‘COUNTISPOSITIVE’;
IFAREA>
=‘COUNTANDAREAAREPOSITIVE’;
ENDIF
ELSEIFCOUNT=0THEN
=‘COUNTISZERO’;
=‘COUNTISNEGATIVE’;
4循环
☆简单循环（直到显示结束循环之前一直执行）
◆语法
LOOPPL/SQL语句
ENDLOOP;
◆示例
COUNTER:
=0;
LOOPCOUNTER:
=COUNTER+1;
EXITWHENCOUNTER=5;
ENDLOOP;
◆说明
要结束简单循环，可以使用EXIT或EXITWHEN语句。
EXIT立即结束循环，EXITWHEN语句在指定条件出现时结束语句。
EXITWHEN可以出现在循环代码中的任何位置。
☆WHILE循环（直到某个特定条件出现之前一直运行）
◆语法
WHILE条件LOOP
PL/SQL语句
ENDLOOP；
◆示例
COUNTER:
=0;
WHILECOUNTER<
6LOOP
略
☆FOR循环（运行预先预定的次数）
FORLOOP_VARIABLEIN[REVERSE]LOWER_BOUND..UPPER_BOUNDLOOP
PL/SQL语句
FORCOUNTIN1..5LOOP
DBMS_OUTPUT.PUT_LINE（COUNT）;
//如果本例中使用了REVERSE，循环变量COUNT会从5开始，在每次循环中递减1，最终达到1。
▽LOOP_VARIABLE:
指定循环变量，可以将已经存在的变量用作循环变量，也可让循环语句自己创建循环变量（当所指定的变量部存在时，可能需要自己来创建）。
循环变量的值在每一次循环中都增加（如果使用REVERSE关键字则减少）1。
▽REVERSE指定在每一次循环中循环变量都回递减。
循环变量先被初始化为其上限，然后在每次循环中递减1，直至达到其下限。
下限依然要在上限之前定义。
▽LOWER_BOUND指定循环的下限，在没有使用REVERSE的情况下，循环变量初始化为这个下限值。
UPPER_BOUND指定循环的上限，如果是用了REVERSE，循环变量就初始化这个上限。
5游标
当select语句从数据库中返回的记录多于一条时，就可以使用游标。
游标可以理解为“可以一次访问一个的一组记录”。
Select语句将列提取到游标中，然后根据游标取得记录。
使用游标时需要遵循5个步骤：
№1声明一些变量，用于保存select语句的返回的列值
用于保存列值得变量，必须与这些列值兼容。
应尽可能使用%TYPE。
DECLARE
V_AAATABLE1.AAA%TYPE;
V_BBBTABLE2.BBB%TYPE;
№2声明游标，并指定select语句
◆说明
游标的声明由游标名和希望执行的select语句组成，其中select语句在打开游标之前不会真正运行。
游标的声明与其它的声明一样，都放在声明段中。
声明游标的语法如下:
CURSORCURSOR_NAMEISSELECT_STATEMENT;
CURSORCV_MMMISSELECTAAA,BBBFROMTABLEWHERECCC=‘XXX’;
№3打开游标
这一步将真正运行select语句，打开游标时使用的是OPEN语句，这个语句必须放在代码段的可执行段中。
OPENCV_MMM;
№4从游标中获取记录
要从游标中取得每一条记录，可使用fetch语句。
Fetch语句将列的值读取到指定的变量中。
语法如下：
FETCHCURSOR_NAMEINTOVARIABLE[，VARIABLE1..]；
FETCHCV_MMMINTOV_AAA,V_BBB;
◆额外说明
游标可能包含很多条记录，因此要通过循环一次读取每条记录，为确定循环是否结束，可以使用布尔变量进行控制（由表名%NOTFOUND），当语句到达游标最后一条记录时，这个变量为真。
LOOP
FETCHCV_MMMINTOV_AAA,V_BBBEXITWHENCV_MMM%NOTFOUND;
DBMS_OUTPUT.PUT_LINE（‘V_AAA=’||V_AAA||’,V_BBB=’||V_BBB）;
注意：
此时输出的是读到的V_AAA、V_BBB的值。
№5关闭游标
最后一步就是关闭游标，关闭由表是为了释放系统资源。
语法如下;
CLOSECURSOR_NAME;
№6完整的示例
SETSERVEROUPTON--SQL*PLUS中显示
DECLARE--声明变量
V_BBBTABLE1.BBB%TYPE;
CURSORCV_MMMIS--定义游标
SELECTAAA,BBBFROMTABLE1ORDERBYAAA;
BEGIN--开始
--打开游标
LOOP--循环，从游标中获取记录
--循环结束
COLSECV_MMM;
--关闭游标
--结束
/--执行PL/SQL。
运行校本：
在SQL*PLUS通过命令执行脚本，命令格式：
@绝对目录\文件名.SQL
№7游标与for循环
利用for循环，可以增强游标访问记录的能力。
当访问for循环时可以不用显示的打开和关闭游标—for循环会自动执行这些操作.№6的for循环如下：
SETSERVEROUPTON--SQL*PLUS中显示
DECLARE
FOR--循环，从游标中获取记录
V_AAA_BBBINCV_MMMLOOP
DBMS_OUTPUT.PUT_LINE（‘AAA=’||V_AAA_BBB.AAA||’,BBB=’||V_AAA_BBB.BBB）;
№8游标与for循环实例
createorreplaceprocedurepro_que_ans（pro_idinnumber%type
）
as
begin
if（pro_id!
=null||pro_id.epuals（"
"
））then
deletefromNETPRLCT_ANSWER_Twhereid=pro_id;
then
deletefromNETPRLCT_QUESTION_Twhereid=（selectquestionidfromNETPRLCT_ANSWER_Twhereid=pro_id）
endif;
commit;
EXCEPTIONWHENOTHERSTHENROLLBACK;
ENDpro_que_ans;
CREATE[ORREPLACE]PROCEDUREPROCEDURE_NAME
[（PARAMETER_NAME）[IN|OUT|INOUT]TYPE[,..]]]
[IS|AS]
BEGIN
PROCEDURE_BODY;
ENDPROCEDURE_NAME;
6异常
异常用于处理PL/SQL块中出现的错误。
OTHERS异常可以处理所有异常。
因为TOHERS可处理所有异常，因此必须在EXCEPTION块中所有特定具体异常之后使用该异常。
示例如下：
DBMS_OUTPUT.PUT_LINE（1/0）;
EXCEPTION
WHENOTHERSTHEN
DBMS_OUTPUT.PUT_LINE（‘发生异常’）;
END;
/
7存储过程
可以创建包含一组SQL和PL/SQL语句的过程。
过程使得业务逻辑在数据库中集中，任何能够访问数据库的称许都可以使用过程。
№1创建过程
CREATE[ORREPLACE]PROCEDUREPROCEDURE_NAME
ENDPROCEDURE_NAME;
CREATEORREPLACEPROCEDUREUPDATE_PRODUCT_PRICE（
P_PRODUCT_IDINPRODUCTS.PRODUCT_ID%TYPE,--声明参数且值
P_FACTORINMUNBER--不可改变
）AS
V_PRODUCT_COUNTINTEGER;
--通过PRODUCT_ID计算产品数量.如果产品存在则为1,如果不存在则为0.
SELECTCOUNT（*）INTOV_PRODUCT_COUNTFROMPRODUCTSWHEREPRODUCT_ID=P_PRODUCT_ID;
--如果产品存在（V_PRODUCT_COUNT＝1）,则更新产品价格。
IFV_PRODUCT_COUNT=1THEN
UPDATEPRODUCTSSETPRICE=PRICE*P_FACTORWHEREPRODUCT_ID=P_PRODUCT_ID;
COMMIT;
WHENOTHERSTHEN
ROLLBACK;
ENDUPDATE_PRODUCT_PRICE;
▽ORREPLACE:
说明如果该过程已经存在，则用心得过程替代它。
该选项可以用来修改一个现有的过程。
▽PROCEDURE_NAME是过程名。
PARAMETER_NAME参数名，可以向一个过程传递多个参数。
▽IN|OUT|INOUT定义参数的模式。
IN是参数的默认模式，这种模式定义的参数在过程运行的时候已经具有值，在经过程序体时这个值不会改变。
OUT模式定义的参数只在过程体内部赋值。
INOUT模式定义的参数当过程运行时可能已经有值，但是在过程体内也可以修改。
▽TYPE，指定参数的类型。
PROCEDURE_BODY包含执行过程的SQL和PL/SQL语句。
№2调用过程
通过CALL语句调用过程。
CALL过程名（参数1值，参数2值，...）
升级会员 