PL_SQL培训ppt课件

1、PL/SQL程序设计(Procedural Language/Structured Query Language)目 录PL/SQL构造内置SQL函数游标过程和函数程序包触发器数据字典 PL/SQL构造PL/SQL构造PL/SQL块 一切的PL/SQL程序中的根本单位都是块(block)。PL/SQL程序都是由块组成的， 这些块可以顺序出现(一个接一个)也可以相互嵌套(一个在另一个内部)。DECLARE n number(10,2);BEGIN DECLARE v varchar2(8); BEGIN END; END;建议少运用“嵌套PL/SQL构造变量声明在块的声明部分对变量进展声明。声明

2、一个变量的语法是：Variable_name type CONSTANTNOT NULL:=value; 假设在变量声明中运用了CONSTANT，那么该变量必需求被初始化，并且它的取值不能被改动为与其初始值不同的数值。常量变量被作为只读的方式进展处置。在声明中可以用关键字DEFAULT交换:=。DECLARE n1 number(10,2); v1 varchar2(20) DEFAULT 铜陵; v2 varchar2(20):=铜陵; v3 CONSTANT varchar2(20):=铜陵; v4 CONSTANT varchar2(20) DEFAULT 铜陵;BEGIN END;PL

3、/SQL构造变量类型Date日期类型Boolean布尔类型Rowid伪列类型Char(n)类型，固定长度字符类型，n最大值32767Varchar2(n)类型，可变长度字符类型，n最大值32767Long和long raw类型，最大长度为32760BLOB类型，4GB大小Number类型，最大精度38，刻度范围从-84到127PL/SQL构造变量类型NUMBER(P,S)P是精度，S是刻度范围。 精度是数值中一切数字位的个数， 而刻度范围是小数点右边的数字位的个数假设刻度范围是个负数，那么就由小数点开场向左边进展计算数字位的个数。定义赋值存储值NUMBER;1234.56781234.5678

4、NUMBER(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可以将变量类型定义为与数据库中表的字段一样的类型，当字段的数据类型变化时那么变量的类型也相应的变化。DECLARE v1 W_TEST1.BH%TYPE;BEGIN END;表名字段名PL/SQL构造变量类型运用%ROWTYPE可以将变量类型定义为与具有一样类型的数据

5、库行。%ROWTYPE将前往一个基于表定义的类型。DECLARE record1 W_TEST1%ROWTYPE;BEGIN END;Record1定义了一个记录，该记录中的字段有与W_TEST1表中的列相对应。BH VARCHAR2(2)N NUMBER(10)PL/SQL构造DECLARE n number(10);BEGIN n:=1; insert into w_test1(bh,n) values(a,n); DECLARE n number(10); BEGIN insert into w_test1(bh,n) values(b,n); n:=100; insert into w

6、_test1(bh,n) values(b,n); END; insert into w_test1(bh,n) values(a,n);END; BH N- - a 1 b b 100 a 1CREATE TABLE W_TEST1(BH VARCHAR2(2),N NUMBER(10);变量作用域PL/SQL构造控制构造DECLARE n number;BEGIN . IF n50 then . ELSIF n=7 then v:=是;a ELSE v:=否; END IF; IF n=7 THENc v:=是; ELSE v:=否; END IF; END;假设n为一个确定的值那么a,b

7、的结果是一样的假设n为NULL那么a,b的结果就是不一样的,在条件判别时要留意NULL的情况。PL/SQL构造循环构造DECLARE n number(10);BEGIN n:=1; LOOP insert into w_test1(bh,n) values(a,n); n:=n+1; EXIT WHEN n10; END LOOP;END;DECLARE n number(10);BEGIN n:=1; WHILE n8GROUP BY BHORDER BY BHBH N- -a 1a 2a 3a 4a 5a 6a 7a 8a 9C 10000C 10001d 10002d 10002c 1

8、0003c 10004d 10004d 10004BH S- -C 20001a 9c 20007d 40012PL/SQL构造ROWID和ROWNUM(行标识符和行序号)SELECT ROWID,ROWNUMFROM W_TEST1;ROWID ROWNUM- -AAAG3BAABAAAIsEAAA 1AAAG3BAABAAAIsEAAB 2AAAG3BAABAAAIsEAAC 3AAAG3BAABAAAIsEAAD 4AAAG3BAABAAAIsEAAE 5AAAG3BAABAAAIsEAAF 6AAAG3BAABAAAIsEAAG 7AAAG3BAABAAAIsEAAH 8AAAG3BA

9、ABAAAIsEAAI 9SELECTPL/SQL构造INSERTDECLARE num1 number;BEGIN num1:=8; INSERT INTO W_TEST1(BH,N) VALUES(e,10); INSERT INTO W_TEST1(BH,N) VALUES(e,num1);END;INSERT INTO 表名(字段1,字段2) VALUES(值1,值2);PL/SQL构造INSERTINSERT INTO 表1( 字段1,字段2)SELECT 字段1,字段2FROM 表2WHERE 条件INSERT INTO W_TEST1(BH,N)SELECT BH,NFROM W

10、_TEST1WHERE N5 and n4 AND ROWNUM8留意:ROWNUM的变化PL/SQL构造CREATE 和 DROPDATAFILETABLESPACEDATABASE LINKUSERTABLEVIEWSEQUENCESYNONYMSNPSHOTPL/SQL构造TABLESPACEcreate tablespace TDMIS datafile .DATABASETDMIS size 500M online;create public rollback segment TDMIS tablespace TDMIS;CREATE TABLESPACE 表空间名 DATAFILE

11、 数据文件名 SIZE 大小 ONLINECREATE PUBLIC ROLLBACK SEGMENT 回滚段名 TABLESPACE 表空间名PL/SQL构造USERCREATE USER zhcxzxt IDENTIFIED BY zhcxzxt DEFAULT TABLESPACE tdmisGRANT RESOURCE,CONNECT TO zhcxzxt;CREATE USER 用户名 IDENTIFIED BY 密码 DEFAULT TABLESPACE 表空间名PL/SQL构造DATABASE LINKCREATE DATABASE LINK 数据库链接名CONNECT TO 用

12、户名 IDENTIFIED BY 密码USING 数据库衔接字符串SELECT 字段1,字段2FROM 表数据库链接名WHERE 条件PL/SQL构造TABLECREATE TABLE ST_BJ_JXBZ2(BGDMC VARCHAR2(20), - 报告单称号SBLX VARCHAR2(20), - 设备类型SBXH VARCHAR2(40), - 设备型号JXXZ VARCHAR2(10), - 检修性质(年检/安装/大修)JSGSSM VARCHAR2(60), - 检修工时阐明CONSTRAINT PK_ST_BJ_JXBZ2 PRIMARY KEY(BGDMC,SBXH),CONS

13、TRAINT FK_ST_BJ_JXBZ2 FOREIGN KEY(BGDMC) REFERENCES ST_BJ_JXBZ);CREATE TABLE 表名字段名1 数据类型，字段名2 数据类型，CONSTRAINT 约束名 PRIMARY KEY(字段名1,字段名2),CONSTRAINT约束名 FOREIGN KEY(字段名) REFERENCESS 表名);PL/SQL构造ALTERALTER TABLE W_TEST1 MODIFY(BH VARCHAR2(4),N NUMBER(15);ALTER TABLE 表名 MODIFY字段1 数据类型，字段2 数据类型；PL/SQL构造I

14、NDEX索引CREATE INDEX SI_W_TEST1 ON W_TEST1(BH) TABLESPACE TDMIS;CREATE INDEX 索引名 on 表名TABLESPACE 表空间名;PL/SQL构造序列序列(sequence)是一种oracle对象，用来产生独一的数字。其中sequence.CURRVAL表示序列当前值 sequence.NEXTVAL使序列增值并前往新的取值CREATE SEQUENCE W_TEST1_S1 START WITH 10000;Insert into w_test1(bh,n) values(c,W_TEST1_S1.NEXTVAL)Inse

15、rt into w_test1(bh,n) values(c,W_TEST1_S1.NEXTVAL)Insert into w_test1(bh,n) values(d,W_TEST1_S1.CURRVAL)Insert into w_test1(bh,n) values(d,W_TEST1_S1.CURRVAL)PL/SQL构造同义词CREATE PUBLIC SYNONYM 同义词名 FOR 表名或视图名CREATE PUBLIC SYNONYM CX_FXTJ FOR CX_FXTJ;GRANT SELECT ON CX_FXTJ TO CONNECT;PL/SQL构造快照CREATE

16、SNAPSHOT KZ_FH_YZDZSTORAGE (INITIAL 100K NEXT 100K PCTINCREASE 0)TABLESPACE TDMISREFRESH COMPLETESTART WITH SYSDATE NEXT SYSDATE+1ASSELECT PID,ND,YF,MAX(FH) ZDZFROM KZ_FHGROUP BY PID,ND,YF;EXECUTE DBMS_SNAPSHOT.REFRESH(KZ_FH_YZDZ,C);PL/SQL构造GRANT 和 REVOKEGRANT 权限 ON 对象 TO 用户/角色REVOKE 权限 ON 对象 FROM 用

17、户/角色 CASCADE CONSTRAINTS对象权限对象类型ALTER表、序列DELETE表、视图EXECUTE过程、函数、包INDEX表INSERT表、视图REFERENCES表SELECT表、视图、序列、快照UPDATE表、视图PL/SQL构造ROLE角色名被授予的权限CONNECTALTER SESSION，CREATE CLUSTER，CREATE DATABASE LINK，CREATE SEQUENCE，CREATE TABLE，CREATE VIEW，CREATE SYNONYMRESOURCECREATE CLUSTER，CREATE PROCEDURE，CREATE SE

18、QUENCE，CREATE TABLE，CREATE PROCEDUREDBA所有系统权限CREATE ROLE 角色名;CREATE 权限 ON 对象 TO 角色名CREATE ROLE RW_TEST1;GRANT SELECT ON W_TEST1 TO RW_TEST1;GRANT SELECT ON W_TEST1 TO PUBLIC;PL/SQL构造COMMIT、ROLLBACK、SAVEPOINTDELETE FRO W_TEST1WHERE n=10000;SAVEPOINT A;DELETE FRO W_TEST1WHERE n=10001;SAVEPOINT B;DELET

19、E FRO W_TEST1WHERE n=10002;SAVEPOINT C;ROLLBACK TO BCOMIT;内置SQL函数内置SQL函数CHR(x)与ASCII前往在数据库字符集中与x拥有等价数值的字符。CHR和ASCII是互为反函数。CHR前往给定字符码的字符，而ASCII前往给定字符的字符码。SELECT CHR(37) A,CHR(100) B,CHR(101) C,ASCII(A) D,ASCII(B) EFROM DUAL;A B C D E- - - - -% d e 65 66内置SQL函数CONCAT(string1,string2)前往string1与string2拼

20、接后的字符串。该函数与|运算符一样。Select concat(安徽省,铜陵市) VFROM DUALV-安徽省铜陵市内置SQL函数LOWER(string)以小写方式前往string。不是字母的字符不会受影响。假设string是CHAR类型那么前往的结果也是CHAR类型，假设string是VARCHAR2类型那么前往的结果也上VARCHAR2类型。Select lower(AAAbbbbDDcc) string1, upper(AAAbbbbDDcc) string2From dualSTRING1 STRING2- -aaabbbbddcc AAABBBBDDCCUPPER(string)

21、与LOWER(string)相反内置SQL函数LPAD(string1,x,string2)RPAD(string1,x,string2)SELECT LPAD(8,2,0) STRING1,RPAD(8,2,0) STRING2FROM DUAL;STRING1 STRING2- -08 80SELECT LPAD(ABCD,20,XYZ) STRING1,RPAD(ABCD,20,XYZ) STRING2FROM DUAL;STRING1 STRING2- -XYZXYZXYZXYZXYZXABCD ABCDXYZXYZXYZXYZXYZX内置SQL函数LTRIM(string1,stri

22、ng2)RTRIM(string1,string2)前往删除从左边算起出如今string2中的字符的string1。当遇到不在string2中的第一个字符，结果就被前往了。String2被缺省设置为单个空格。前往删除从右边算起出如今string2中的字符的string1。select ltrim(abcdabcdefghxyzxyz,abc) v1,rtrim(abcdabcdefghxyzxyz,zy) v2 from dual;select ltrim(abcdabcdefghxyzxyz,abcd) v1,rtrim(abcdabcdefghxyzxyz,zyx) v2 from dua

23、l;V1 V2- -dabcdefghxyzxyz abcdabcdefghxyzxV1 V2- -efghxyzxyz abcdabcdefgh内置SQL函数REPLACE(string,search_string,replace_string)前往用replace_string交换一切出现的search_string以后的string。字符串搜索的字符串交换的字符串select replace(abcdabcdabcd,ab,xy) v from dual;select replace(abcdabcdabcd,ab) v from dual;V-xycdxycdxycdV-cdcdcd内

24、置SQL函数TRANSLATE(string,from_string,to_string)是REPLACE所提供的功能的一个超集。to_string不能为空。select TRANSLATE(abcdefghijk,abcdefghij,123456) Vfrom dual;V-123456k内置SQL函数SUBSTR(string,n1,n2)前往从字母位置n1开场有n2个字符长的string的一部分。select substr(abcdefghijk,4,4) v from dual;select substr(abcdefghijk,-4,3) v from dual; select s

25、ubstr(abcdefghijk,4) v from dual; V-defgV-hijV-defghijk内置SQL函数INSTR(string1, string2,a,b) 前往在string1中包含string2的位置。String1从左边开场扫描，起始位置是a.假设a是个负数，那么string1是右边开场进展扫描。 第b次出现的位置将被前往。a和b的缺省值都是1。 假设string2在a,b的规定没有找到那么前往0select instr(abcdefabcdefabcdef,ef,4,2) n from dual N- 11内置SQL函数LENGTH(string)以字节为单位前往

26、string的长度。假设string是NULL，那么前往NULL。留意：char类型与varchar2类型的不同。内置SQL函数ABS(x)前往x的绝对值。内置SQL函数ACOS(x)前往x的反余弦值。X应该是从-1到1之间的数，并且输出值从0到，以弧度为单位。内置SQL函数ASIN(x)前往x的反正弦值。X应该是从-1到1之间的数，并且输出值从-/2 到/2，以弧度为单位。内置SQL函数ATAN(x)前往x的反正正切值。输出值从-/2 到/2，以弧度为单位。内置SQL函数ATAN2(x,y)前往x和y的反正正切值。输出值从- 到，以弧度为单位。ATAN2(x,y)和ATAN(x/y)是完全一

27、样的。内置SQL函数CEIL(x)前往大于或等于x的最小整数值。内置SQL函数COS(x)前往x的余弦，x是以弧度表达的角度。SELECT COS(0),COS(90*3.1415926/180) FROM DUAL;内置SQL函数COSH(x)前往x的双曲余弦，x是以弧度表达的角度。SELECT COSH(0),COSH(90*3.1415926/180) FROM DUAL;内置SQL函数EXP(x)前往e的x次幂，e=2.71828183。Select exp(1),exp(2.7) from dual;内置SQL函数FLOOR(x)前往小于或等于x的最大整数值。Select floor

28、(-26.5),floor(26.5) from dual;内置SQL函数LN(x)前往x的自然对数。X必需大于0内置SQL函数LOG(x,y)前往以x为底的y的对数。X必需是不是1和0的正数，y为恣意的正数。Select log(2,32),log(5,25) from dual;内置SQL函数MOD(x,y)前往x除以y的余数。假设y是0，那么前往x。当x是负数的时候，MOD函数和经典的取模函数是不同的。经典的取模函数可以定义为：X-y*FLOOR(x/y)Select mod(23,5),mod(23,0) from dual;内置SQL函数POWER(x,y)前往x的y次幂。底x和指数

29、y没有必要都是正数，但是假设x是负数的话，那么y必需是以个整数。Select power(4,3),power(1.1,2.6),power(25,-2),power(-2,3) from dual;内置SQL函数ROUND(x,y)前往舍入到小数点右边y位的x值。y缺省设置为0，这会将x舍入为最近的整数。假设y是负数，那么就舍入到小数点左边相应的位上。Y必需是整数。Select round(1.56),round(1.56,1),round(12.34,-2) from dual;内置SQL函数SIGN(x)假设x0,前往1。Select sign(-47.3),sign(0),sign(4

30、7.3) from dual;内置SQL函数SIN(x)前往x的正弦。X是以弧度表达的角度。Select sin(0),sin(60*3.1415926/180) from dual;内置SQL函数SINH(x)前往x的双曲正弦值。Select sinh(0),sinh(60*3.1415926/180) from dual;内置SQL函数SQRT(x)前往x的平方根。X不能为负。Select sqrt(64),sqrt(97.654) from dual;内置SQL函数TAN(x)前往x的正切值，X是以弧度为单位表达的角度。Select tan(0),tan(-60*3.14159/180)

31、 from dual;内置SQL函数TANH(x)前往x的双曲正切值。Select tanh(0),tanh(-60*3.14159/180) from dual;内置SQL函数TRUNC(x,y)前往x截尾到y位小数的x值。y缺省设置为0，会将x截尾为一个整数。假设y是负数，就截尾到小数点左边相应的位上。Select trunc(-123.456),trunc(-123.456,1),trunc(123.456,-1) from dual;内置SQL函数ADD_MONTHS(d,x)前往日期d加上x月以后的结果。X可以是恣意整数。Select add_months(02-FEB-91,1),

32、 add_months(19-JAN-87,1), add_months(30-JAN-87,13),FROM DUAL;内置SQL函数LAST_DAY(d)前往包含日期d的月份的最后一天的日期。这个函数可以被用来确定当前月中还剩下多少天。Select last_day(12-APR-88) CURRENT, LAST_DAY(12-APR-88)-TO_DATE(12-APR-88) FROM DUAL;内置SQL函数MONTHS_BETWEEN(date1,date2)前往在date1和date2之间月的数目。假设date1和date2这两个日期中“日分量信息是一样的，或者这两个日期分别是

33、所在月的最后一天，那么前往的结果是个整数。否那么，前往的结果能够会包含一个小数部分以31天计算。Select MONTHS_BETWEEN(12-APR-88,12-MAR-97)FROM DUAL;内置SQL函数NEXT_DAY(d,string)前往在d之后满足由string给出的条件的第一天。Select next_day(12-apr-88,thursday)From dual;内置SQL函数SYSDATE前往当前的日期的时间，类型是DATE本地数据库的日期和时间。内置SQL函数日期算术运算返回数值类型结果D1-d2NUMBER两个日期之间相差的天数D1+d2非法D1+nDATE在d1上加上n天并作为date类型返回D1-nDATE在d1上减去n天并作为date类型返回内置SQL函数TO_CHAR(d