《PL_SQL培训文档(共86张)》由会员分享，可在线阅读，更多相关《PL_SQL培训文档(共86张)（86页珍藏版）》请在金锄头文库上搜索。
1、(Procedural Language/Structured Query Language)目 录1 PL/SQL结构2 内置SQL函数3 游标4 过程和函数5 程序包6 触发器7 数据字典 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
3、 END;PL/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.56
4、781234.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可以将变量类型定义为与数据库中表的字段相同的类型，当字段的数据类型变化时则变量的类型也相应的变化。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; i
6、nsert into w_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;如
7、果n为一个确定的值则a,b的结果是一样的如果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 10
8、002d 10002c 10003c 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 7AAAG3BAABAAAIs
9、EAAH 8AAAG3BAABAAAIsEAAI 9 SELECTPL/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)S
10、ELECT BH,NFROM W_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 TABLES
11、PACE 表空间名 DATAFILE 数据文件名 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 LIN
12、K 数据库链接名CONNECT TO 用户名 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
13、 KEY(BGDMC,SBXH),CONSTRAINT 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
14、 数据类型，字段2 数据类型）；PL/SQL结构 INDEX（索引）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) val
15、ues(c,W_TEST1_S1.NEXTVAL)Insert 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_FXT
16、J TO CONNECT;PL/SQL结构 快照CREATE 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 用户/角色 CASCADE CONSTRAINTS对象权限对象类型ALTER表、序列DELETE表、视图EXECUTE过程、函数、包INDEX表INSERT表、视图REFERENCES表SELECT表、视图、序列、快照UPDATE表、视图PL/SQL结构 ROLE角色名被授予的权限CONNECTALTER SESSION，CREATE
