plsql基础培训概要PPT课件下载推荐.ppt

1、PL/SQL是ORACLE系统的核心语言，现在ORACLE的许多部件都是由PL/SQL写成。在PL/SQL中可以使用的SQL语句有：INSERT，UPDATE，DELETE，SELECT INTO，COMMIT，ROLLBACK，SAVEPOINT。提示：在 PL/SQL中只能用 SQL语句中的 DML 部分，不能用 DDL 部分，如果要在PL/SQL中使用DDL（如CREATE table 等）的话，只能以动态的方式来使用。,2.PL/SQL块结构和组成元素,PL/SQL块,PL/SQL的程序结构,PL/SQL程序都是以块（block）为基本单位。PL/SQL块可以分为三类：1.无名块：动态

2、构造，只能执行一次。2.子程序：存储在数据库中的存储过程、函数及包等。当在数据库上建立好后可以在其它程序中调用它们。3.触发器：当数据库发生操作时，会触发一些事件，从而自动执行相应的程序。,整个PL/SQL块分三部分：声明部分（用declare开头）、执行部分（以begin开头,以end结尾）和异常处理部分（以exception开头）。其中执行部分是必须的，其他两个部分可选。,DECLARE n number（10,2）;BEGIN DECLARE v varchar2（8）;BEGIN END;END;,建议少使用“嵌套”,DECLARE 变量、常量、游标、自定义异常 BEGIN SQL语句

3、 PL/SQL控制语句 EXCEPTION 错误发生时执行的动作 END；,变量声明,在大多数PL/SQL块中，都需要使用变量来包含在程序逻辑中使用的值。无论需要什么类型的变量，能够在BEGIN部分中使用它之前，都必须在块的DECLARE段中声明这些变量。声明变量必须提供变量名称和数据类型。变量名称遵循以下约定：（1）以一个字母字符开头（2）最多包含30个字符（3）可以包含大写和小写字母、数字以及_、$、#及其它特殊字符。,变量声明,变量命名在PL/SQL中有特别的讲究，建议在系统的设计阶段就要求所有编程人员共同遵守一定的要求，使得整个系统的文档在规范上达到要求。下面是建议的命名方法：,基本数

4、据类型变量,变量类型,/*声明部分，以declare开头*/declare v_id integer;v_name varchar（20）;cursor c_emp is select*from employee where emp_id=3;/*执行部分，以begin开头*/begin open c_emp;-打开游标loopfetch c_emp into v_id,v_name;-从游标取数据exit when c_emp%notfound;end loop;close c_emp;-关闭游标dbms_output.PUT_LINE（v_name）;/*异常处理部分，以exception

5、开始*/exceptionwhen no_data_found then dbms_output.PUT_LINE（没有数据）;end;,PL/SQL结构,在块的声明部分对变量进行声明。声明一个变量的语法是：,Variable_name CONSTANT type NOT NULL:=value;,如果在变量声明中使用了CONSTANT，那么该变量必须要被初始化，并且它的取值不能被改变为与其初始值不同的数值。常量变量被作为只读的方式进行处理。在声明中可以用关键字DEFAULT替换:=。,DECLARE n1 number（10,2）;v1 varchar2（20）DEFAULT 铜陵;v2 v

6、archar2（20）:=铜陵;v3 CONSTANT varchar2（20）:v4 CONSTANT varchar2（20）DEFAULT 铜陵;,注意：在声明部分，每一行只能有一个变量声明，如以下的声明是错误的：Declare v_firstname,v_lastname varchar2（20）;正确的声明为：Declare v_firstname varchar2（20）;v_last_name varchar2（20）;,另外还可以在变量中加入NOT NULL 如果在声明时指明not null，那么应该给该变量赋初值，下面声明是错误的：Declare v_tempvar numb

7、er not null;Declare v_tempvar number not null:=1;CONSTANT关键字是在变量类型之前列出的，而NOT NULL是在数据类型之后列出的。,另外还可以在PL/SQL块中包括SQL：declare lv_qty_num number（3）;begin select count（*）into lv_qty_num from emp;dbms_output.put_line（lv_qty_num）;End;,PL/SQL结构,变量类型,NUMBER（P,S）,P是长度，S是精度。长度是数值中所有数字位的个数，而精度是小数点右边的数字位的个数（如果精度是

8、个负数，那么就由小数点开始向左边进行计算数字位的个数）。,PL/SQL结构,变量类型,使用记录类型记录类型是把逻辑相关的数据作为一个单元存储起来。,DECLARE TYPE test_rec IS RECORD（Code VARCHAR2（10）,Name VARCHAR2（30）NOT NULL:=a book）;V_book test_rec;BEGIN V_book.code:=123;V_book.name:=C+Programming;DBMS_OUTPUT.PUT_LINE（v_book.code|v_book.name）;,初始化记录,定义记录型变量,PL/SQL结构,变量类型,

9、使用%TYPE定义一个变量，其数据类型与已经定义的某个数据变量的类型相同，或者与数据库表的某个列的数据类型相同，这时可以使用%TYPE。,TYPE t_Record IS RECORD（T_no emp.empno%TYPE,T_name emp.ename%TYPE,T_sal emp.sal%TYPE）;-声明接收数据的变量 v_emp t_Record;BEGIN SELECT empno,ename,sal INTO v_emp FROM emp WHERE empno=7788;DBMS_OUTPUT.PUT_LINE（TO_CHAR（v_emp.t_no）|v_emp.t_name

10、|TO_CHAR（v_emp.t_sal）;,使用%TYPE特性的优点在于：所引用的数据库列的数据类型可以不必知道；所引用的数据库列的数据类型可以实时改变。,表的某一个字段的类型,PL/SQL结构,变量类型,使用%ROWTYPE可以将变量类型定义为与具有相同类型的数据库行。%ROWTYPE将返回一个基于表定义的类型。,DECLARE v_empno emp.empno%TYPE:=,使用%ROWTYPE特性的优点在于：所引用的数据库中列的个数和数据类型可以不必知道；所引用的数据库中列的个数和数据类型可以实时改变。,PL/SQL结构,DECLARE n number（10）;BEGIN n:in

11、sert 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_test1（bh,n）values（b,n）;,BH N-a 1 b b 100 a 1,CREATE TABLE W_TEST1（BH VARCHAR2（2）,N NUMBER（10）;,变量作用域,PL/SQL结构,SELECT,SELECT 字段名1,字段名2FROM 表名WHERE 条件GROUP BY 字段名ORDER BY 字段名;,SELECT

12、 BH,SUM（N）S FROM W_TEST1WHERE N8GROUP BY BHORDER BY BH,BH N-a 1a 2a 3a 4a 5a 6a 7a 8a 9C 10000C 10001d 10002d 10002c 10003c 10004d 10004d 10004,BH S-C 20001a 9c 20007d 40012,SELECT BH,N INTO v_name,v_amount FROM W_TEST1WHERE N=1,PL/SQL结构,INSERT,DECLARE num1 number;BEGIN num1:=8;INSERT INTO W_TEST1（B

13、H,N）VALUES（e,10）;INSERT INTO W_TEST1（BH,N）VALUES（e,num1）;,INSERT INTO 表名（字段1,字段2）VALUES（值1,值2）;,PL/SQL结构,INSERT,INSERT INTO 表1（字段1,字段2）SELECT 字段1,字段2FROM 表2WHERE 条件,INSERT INTO W_TEST1（BH,N）SELECT BH,NFROM W_TEST1WHERE N8;,DECLARE RECORD1 W_TEST1%ROWTYPE;BEGINSELECT BH,N INTO RECORD1FROM W_TEST1WHER

14、E N=8;,PL/SQL结构,UPDATE,UPDATE 表名SET 字段=值WHERE 条件,UPDATE W_TEST1SET BH=gWHERE n=5,UPDATE 表名SET（字段1,字段2）=（SELECT 子句）WHERE 条件,UPDATE W_TEST1SET（BH,n）=（select bh,n from w_tes1 where n=10000）WHERE n5 and n9,PL/SQL结构,DELETE,DELETE FROM 表名 WHERE 条件;,DELETE FROM W_TEST1 WHERE n=2;,PL/SQL结构,动态SQL,PL/SQL不直接支持

15、DDL，因此需要使用动态SQL,SET SERVEROUTPUT ONDECLAREv_statement VARCHAR2（500）;CURSOR trigger_curISSELECT trigger_nameFROMuser_triggers;BEGINFOR y IN trigger_curLOOP-Build the statementv_statement:=ALTER TRIGGER|y.trigger_name|DISABLE;-Run the statementEXECUTE IMMEDIATE v_statement;END LOOP;,PL/SQL结构,TABLESPAC

16、E,/创建临时表空间（临时表空间,用于临时数据的存放）create temporary tablespace user_tempTempfile D:oracleoradataOracle9iuser_temp.dbfsize 50mautoextend onnext 50m maxsize 20480mextent management local;,一个表空间就是一片磁盘区域,它有一个或者多个磁盘文件组成,一个表空间可以容纳许多表、索引或者簇等 每个表空间有一个初始区间（initial extent）用完这个区间后再用下一个，直到用完表空间，这时候需要对表空间进行扩展，增加数据文件或者扩大

17、已经存在的数据文件。,/创建数据表空间（用于用户数据的存放）create temporary tablespace test_dataTempfile D:oracleoradataOracle9iuser_data.dbfsize 50mautoextend onnext 50m maxsize 20480mextent management local;,本地管理（LOCAL）:用二进制的方式管理磁盘,有很高的效率,能最大限度的使用磁盘.同时能够自动跟踪记录临近空闲空间的情况，避免进行空闲区的合并操作。,PL/SQL结构,TABLE,CREATE TABLE ST_BJ_JXBZ2（BGD

18、MC VARCHAR2（20）,-报告单名称SBLX VARCHAR2（20）,-设备类型SBXH VARCHAR2（40）,-设备型号JXXZ VARCHAR2（10）,-检修性质（年检/安装/大修）JSGSSM VARCHAR2（60）,-检修工时说明CONSTRAINT PK_ST_BJ_JXBZ2 PRIMARY KEY（BGDMC,SBXH）,CONSTRAINT FK_ST_BJ_JXBZ2 FOREIGN KEY（BGDMC）REFERENCES ST_BJ_JXBZ）tablespace CUST_GENERAL_D pctfree 10-用于指定BLOCK中必需保留的最小空间

19、的比例。pctused 40-为一个百分比数值，当BLOCK中已经使用的空间降低到该数值以下 时，该BLOCK才是可用的，达到或是超过这个数值的BLOCK是不可 用的。initrans 1-指定可以并发操作该表的事务的数目。maxtrans 255指定最大并发操作该表的事务处理条目。storage（initial 40K next 40K minextents 1 maxextents unlimited pctincrease 0-表示每个扩展Extents的增长率）;,CREATE TABLE 表名（字段名1 数据类型，字段名2 数据类型，CONSTRAINT 约束名 PRIMARY KE

20、Y（字段名1,字段名2）,CONSTRAINT约束名 FOREIGN KEY（字段名）REFERENCESS 表名）;,PL/SQL结构,ALTER,ALTER TABLE W_TEST1 MODIFY（BH VARCHAR2（4）,N NUMBER（15）;,ALTER TABLE 表名 MODIFY（字段1 数据类型，字段2 数据类型）；,PL/SQL结构,INDEX（索引）,CREATE INDEX SI_W_TEST1 ON W_TEST1（BH,DH）TABLESPACE TDMIS;,CREATE INDEX 索引名 on 表名TABLESPACE 表空间名;,PL/SQL结构,序

21、列,序列（sequence）是一种oracle对象，用来产生唯一的数字。其中sequence.CURRVAL表示序列当前值 sequence.NEXTVAL使序列增值并返回新的取值,CREATE SEQUENCE W_TEST1_S1 INCREMENT BY 1-每次加几个 START WITH 1-从1开始计数 NOMAXVALUE-不设置最大值 NOCYCLE-一直累加，不循环 CACHE 10;,Insert into w_test1（bh,n）values（c,W_TEST1_S1.NEXTVAL）Insert into w_test1（bh,n）values（c,W_TEST1_S

22、1.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;,PL/SQL结构,快照,CREATE SNAPSHOT KZ_FH_YZDZSTORAGE（INITIAL 100K NEXT 100K PCTINCREASE 0）TABLES

23、PACE TDMISREFRESH COMPLETE-FAST,FORCE START 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）;,Oracle数据库的快照是一个表，它包含有对一个本地或远程数据库上一个或多个表或视图的查询的结果。也就是说快照根本的原理就是将本地或远程数据库上的一个查询结果保存在一个表中。,PL/SQL结构,ROLE,CREATE ROLE 角色名;CREATE

24、权限 ON 对象 TO 角色名,CREATE ROLE RW_TEST1;GRANT SELECT ON W_TEST1 TO RW_TEST1;GRANT SELECT ON W_TEST1 TO PUBLIC;,PL/SQL结构,USER,create user 用户名 identified by 密码 default tablespace 表空间;,create user study identified by study default tablespace data_test;grant connect,resource to study;,PL/SQL结构,GRANT 和 REVO

25、KE,GRANT 权限 ON 对象 TO 用户/角色REVOKE 权限 ON 对象 FROM 用户/角色,grant alter on table to study;revoke alter on table from study;,grant connect,resource to study;,PL/SQL结构,COMMIT、ROLLBACK、SAVEPOINT,DELETE FRO W_TEST1WHERE n=10000;SAVEPOINT A;DELETE FRO W_TEST1WHERE n=10001;SAVEPOINT B;DELETE FRO W_TEST1WHERE n=1

26、0002;SAVEPOINT C;ROLLBACK TO BCOMMIT;,3.PL/SQL流程控制语句,PL/SQL结构,控制结构,DECLARE n number;BEGIN.IF n50 then.ELSIF n100 then.ELSE.END IF;.END;,DECLARE n number（10）;v varchar2（10）;BEGIN IF n=7 then v:=是;a ELSE v:=否;END IF;IF n=7 THENc v:ELSE v:,如果n为一个确定的值则a,b的结果是一样的如果n为NULL则a,b的结果就是不一样的,在条件判断时要注意NULL的情况。,PL

27、/SQL结构,控制结构,CASE selectorWHEN expression1 THEN result1WHEN expression2 THEN result2WHEN expressionN THEN resultN ELSE resultN+1END;,DECLAREV_grade char（1）:=UPPER（,PL/SQL结构,循环结构,DECLARE n number（10）;LOOP insert into w_test1（bh,n）values（a,n）;=n+1;EXIT WHEN n10;WHILE n10 LOOP insert into w_test1（bh,n）v

28、alues（a,n）;BEGIN FOR n IN 1.9 LOOP insert into w_test1（bh,n）values（a,n）;,FOR循环,WHILE循环,简单循环,FOR n IN REVERSE 1.9 LOOP（表示从大到小循环）,PL/SQL结构,循环结构,GOTO label;./*标号是用括起来的标识符*/,DECLARE V_counter NUMBER:BEGIN LOOP DBMS_OUTPUT.PUT_LINE（V_counter的当前值为:|V_counter）;V_counter:=v_counter+1;IF v_counter 10 THEN GO

29、TO l_ENDofLOOP;DBMS_OUTPUT.PUT_LINE（V_counter的当前值为:,当程序运行到GOTO I_ENDofLOOP语句时，会自动跳转到标签处。,4.游标的使用,为了处理 SQL 语句，ORACLE 必须分配一片叫上下文（context area）的区域来处理所必需的信息，其中包括要处理的行的数目，一个指向语句被分析以后的表示形式的指针以及查询的活动集（active set）。游标是一个指向上下文的句柄（handle）或指针。通过游标，PL/SQL可以控制上下文区和处理语句时上下文区会发生些什么事情。对于不同的SQL语句，游标的使用情况不同：,定义游标：就是定义一个游标名，以及与其相对应的SELECT语句。CURSOR cursor_name（parameter,parameter）IS select_statement;在指定数据类型时，不能使用长度约束。如NUMBER（4）、CHAR（10）等都是错误的。打开游标：OPEN cursor_name（parameter=value,parameter=value）;提取游标数据：就是检索结果集合中的数据行，放入指定的输出变量中。FETCH