PL_SQL培训文档

(ProceduralLanguage/StructuredQueryLanguage)目录1PL/SQL结构2内置SQL函数3游标4过程和函数5程序包6触发器7数据字典PL/SQL结构PL/SQL结构PL/SQL块所有的PL/SQL程序中的基本单位都是块(block)。PL/SQL程序都是由块组成的，这些块可以顺序出现(一个接一个)也可以相互嵌套(一个在另一个内部)。DECLAREnnumber(10,2);BEGIN…………DECLAREvvarchar2(8);BEGIN………END;…………END;建议少使用“嵌套”PL/SQL结构变量声明在块的声明部分对变量进行声明。声明一个变量的语法是：Variable_nametype[CONSTANT][NOTNULL][:=value];如果在变量声明中使用了CONSTANT，那么该变量必须要被初始化，并且它的取值不能被改变为与其初始值不同的数值。常量变量被作为只读的方式进行处理。在声明中可以用关键字DEFAULT替换:=。DECLAREn1number(10,2);v1varchar2(20)DEFAULT‘铜陵’;v2varchar2(20):=‘铜陵’;v3CONSTANTvarchar2(20):=‘铜陵’;v4CONSTANTvarchar2(20)DEFAULT‘铜陵’;BEGIN………END;PL/SQL结构变量类型Date日期类型Boolean布尔类型Rowid伪列类型Char(n)类型，固定长度字符类型，n最大值32767Varchar2(n)类型，可变长度字符类型，n最大值32767Long和longraw类型，最大长度为32760BLOB类型，4GB大小Number类型，最大精度38，刻度范围从-84到127PL/SQL结构变量类型NUMBER(P,S)P是精度，S是刻度范围。精度是数值中所有数字位的个数，而刻度范围是小数点右边的数字位的个数（如果刻度范围是个负数，那么就由小数点开始向左边进行计算数字位的个数）。定义赋值存储值NUMBER;1234.56781234.5678NUMBER(3);123123NUMBER(3);1234错NUMBER(4,3);123.4567错NUMBER(4,3);1.2345671.235NUMBER(7,2);12345.6712345.67NUMBER(4,-3);12341000NUMBER(4,-1);12341230PL/SQL结构变量类型使用%TYPE可以将变量类型定义为与数据库中表的字段相同的类型，当字段的数据类型变化时则变量的类型也相应的变化。DECLAREv1W_TEST1.BH%TYPE;BEGIN…END;表名字段名PL/SQL结构变量类型使用%ROWTYPE可以将变量类型定义为与具有相同类型的数据库行。%ROWTYPE将返回一个基于表定义的类型。DECLARErecord1W_TEST1%ROWTYPE;BEGIN…END;Record1定义了一个记录，该记录中的字段有与W_TEST1表中的列相对应。BHVARCHAR2(2)NNUMBER(10)PL/SQL结构DECLAREnnumber(10);BEGINn:=1;insertintow_test1(bh,n)values('a',n);DECLAREnnumber(10);BEGINinsertintow_test1(bh,n)values('b',n);n:=100;insertintow_test1(bh,n)values('b',n);END;insertintow_test1(bh,n)values('a',n);END;BHN-------------a1bb100a1CREATETABLEW_TEST1(BHVARCHAR2(2),NNUMBER(10));变量作用域PL/SQL结构控制结构DECLAREnnumber;BEGIN…………….IFn50then…….ELSIFn100then…….ELSE…….ENDIF;…………..END;DECLAREnnumber(10);vvarchar2(10);BEGINIFn=7thenv:=‘是’;aELSEv:=‘否’;ENDIF;IFn7thenv:=‘否’;bELSEv:=‘是’;ENDIF;IFnISNULLTHENv:=‘空值’;ELSIFn=7THENcv:=‘是’;ELSEv:=‘否’;ENDIF;END;如果n为一个确定的值则a,b的结果是一样的如果n为NULL则a,b的结果就是不一样的,在条件判断时要注意NULL的情况。PL/SQL结构循环结构DECLAREnnumber(10);BEGINn:=1;LOOPinsertintow_test1(bh,n)values('a',n);n:=n+1;EXITWHENn10;ENDLOOP;END;DECLAREnnumber(10);BEGINn:=1;WHILEn10LOOPinsertintow_test1(bh,n)values('a',n);n:=n+1;ENDLOOP;END;DECLAREnnumber(10);BEGINFORnIN1..9LOOPinsertintow_test1(bh,n)values('a',n);ENDLOOP;END;FOR循环WHILE循环简单循环FORnINREVERSE1..9LOOP(表示从大到小循环)PL/SQL结构SQL语句类别示例数据操纵语言SELECT、INSERT、UPDATE、DELETE数据定义语言DROP、CREATE、ALTER、GRANT、REVOKE事务控制COMMIT、ROLLBACK、SAVEPOINT会话控制ALTERSESSION、SETROLE系统控制ALTERSYSTEM嵌入式SQLCONNECT、DECLARECURSOR、ALLOCATEPL/SQL结构SELECTSELECT字段名1,字段名2FROM表名WHERE条件GROUPBY字段名ORDERBY字段名;SELECTBH,SUM(N)SFROMW_TEST1WHEREN8GROUPBYBHORDERBYBHBHN-------------a1a2a3a4a5a6a7a8a9C10000C10001d10002d10002c10003c10004d10004d10004BHS------------C20001a9c20007d40012PL/SQL结构ROWID和ROWNUM(行标识符和行序号)SELECTROWID,ROWNUMFROMW_TEST1;ROWIDROWNUM----------------------------AAAG3BAABAAAIsEAAA1AAAG3BAABAAAIsEAAB2AAAG3BAABAAAIsEAAC3AAAG3BAABAAAIsEAAD4AAAG3BAABAAAIsEAAE5AAAG3BAABAAAIsEAAF6AAAG3BAABAAAIsEAAG7AAAG3BAABAAAIsEAAH8AAAG3BAABAAAIsEAAI9SELECTPL/SQL结构INSERTDECLAREnum1number;BEGINnum1:=8;INSERTINTOW_TEST1(BH,N)VALUES(‘e’,10);INSERTINTOW_TEST1(BH,N)VALUES(‘e’,num1);END;INSERTINTO表名(字段1,字段2)VALUES(值1,值2);PL/SQL结构INSERTINSERTINTO表1(字段1,字段2)SELECT字段1,字段2FROM表2WHERE条件INSERTINTOW_TEST1(BH,N)SELECTBH,NFROMW_TEST1WHEREN8;DECLARERECORD1W_TEST1%ROWTYPE;BEGINSELECTBH,NINTORECORD1FROMW_TEST1WHEREN=8;END;PL/SQL结构UPDATEUPDATE表名SET字段=值WHERE条件UPDATEW_TEST1SETBH=‘g’WHEREn=5UPDATE表名SET(字段1,字段2)=(SELECT子句)WHERE条件UPDATEW_TEST1SET(BH,n)=(selectbh,nfromw_tes1wheren=10000)WHEREn5andn9PL/SQL结构DELETEDELETEFROM表名WHERE条件;DELETEFROMW_TEST1WHEREn=2;DELETEFROMW_TEST1WHEREROWNUM4ANDROWNUM8注意:ROWNUM的变化PL/SQL结构CREATE和DROPDATAFILETABLESPACEDATABASELINKUSERTABLEVIEWSEQUENCESYNONYMSNPSHOTPL/SQL结构TABLESPACEcreatetablespaceTDMISdatafile'..\DATABASE\TDMIS'size500Monline;createpublicrollbacksegmentTDMIStablespaceTDMIS;CREATETABLESPACE表空间名DATAFILE数据文件名SIZE大小ONLINECREATEPUBLICROLLBACKSEGMENT回滚段名TABLESPACE表空间名PL/SQL结构USERCREATEUSERzhcxzxtIDENTIFIEDBYzhcxzxtDEFAULTTABLESPACEtdmisGRANTRESOURCE,CONNECTTOzhcxzxt;CREATEUSER用户名IDENTIFIEDBY密码DEFAULTTABLESPACE表空间名PL/SQL结构DATABASELINKCREATEDATABASELINK数据库链接名CONNECTTO用户名IDENTIFIEDBY密码USING数据库连接字符串SELECT字段1,字段2FROM表@数据库链接名WHERE条件PL/SQL结构TABLECREATETABLEST_BJ_JXBZ2(BGDMCVARCHAR2(20),--报告单名称SBLXVARCHAR2(20),--设备类型SBXHVARCHAR2(40),--设备型号JXXZVARCHAR2(10),--检修性质(年检/安装/大修)JSGSSMVARCHAR2(60),--检修工时说明CONSTRAINTPK_ST_BJ_JXBZ2PRIMARYKEY(BGDMC,SBXH),CONSTRAINTFK_ST_BJ_JXBZ2FOREIGNKEY(BGDMC)REFERENCESST_BJ_JXBZ);CREATETABLE表名（字段名1数据类型，字段名2数据类型，CONSTRAINT约束名PRIMARYKEY(字段名1,字段名2),CONSTRAINT约束名FOREIGNKEY(字段名)REFERENCESS表名);PL/SQL结构ALTERALTERTABLEW_TEST1MODIFY(BHVARCHAR2(4),NNUMBER(15));ALTERTABLE表名MODIFY（字段1数据类型，字段2数据类型）；PL/SQL结构INDEX（索引）CREATEINDEXSI_W_TEST1ONW_TEST1(BH)TABLESPACETDMIS;CREATEINDEX索引名on表名TABLESPACE表空间名;PL/SQL结构序列序列(sequence)是一种oracle对象，用来产生唯一的数字。其中sequence.CURRVAL表示序列当前值sequence.NEXTVAL使序列增值并返回新的取值CREATESEQUENCEW_TEST1_S1STARTWITH10000;Insertintow_test1(bh,n)values(‘c’,W_TEST1_S1.NEXTVAL)Insertint