plsql基础培训概要.ppt

plsql基础培训概要.ppt

《plsql基础培训概要.ppt》由会员分享，可在线阅读，更多相关《plsql基础培训概要.ppt（109页珍藏版）》请在冰点文库上搜索。

主讲人：

日期：

PL/SQL程序设计,（ProceduralLanguage/StructuredQueryLanguage）,目录,PL/SQL程序设计简介PL/SQL块结构和组成元素PL/SQL流程控制语句游标的使用异常错误处理存储过程和函数包的创建和应用触发器,1.PL/SQL程序设计简介,PL/SQL是ProcedureLanguage&StructuredQueryLanguage的缩写。

ORACLE的SQL是支持ANSI（AmericannationalStandardsInstitute）和ISO92（InternationalStandardsOrganization）标准的产品。

PL/SQL是对SQL语言存储过程语言的扩展。

从ORACLE6以后，ORACLE的RDBMS附带了PL/SQL。

它现在已经成为一种过程处理语言。

目前的PL/SQL包括两部分，一部分是数据库引擎部分；另一部分是可嵌入到许多产品（如C语言，JAVA语言等）工具中的独立引擎。

可以将这两部分称为：

数据库PL/SQL和工具PL/SQL。

两者的编程非常相似。

都具有编程结构、语法和逻辑机制。

工具PL/SQL另外还增加了用于支持工具（如ORACLEForms）的句法，如：

在窗体上设置按钮等。

本章主要介绍数据库PL/SQL内容。

什么是PL/SQL,PL/SQL与网络传输,Oracle数据库服务器,SQL,SQL,SQL,客户应用,Oracle数据库服务器,客户应用,SQLSQLSQL,使用SQL,使用PL/SQL,对于客户/服务器环境来说，真正的瓶颈是网络上。

无论网络多快，只要客户端与服务器进行大量的数据交换,应用运行的效率自然就会受到影响。

在执行期间，将所有的SQL语句传递给SQL语句执行器组件执行。

相对于逐条发送一组SQL语句，PL/SQL的打包传输，减少网络流量。

PL/SQL可用的SQL语句:

PL/SQL是ORACLE系统的核心语言，现在ORACLE的许多部件都是由PL/SQL写成。

在PL/SQL中可以使用的SQL语句有：

INSERT，UPDATE，DELETE，SELECTINTO，COMMIT，ROLLBACK，SAVEPOINT。

提示：

在PL/SQL中只能用SQL语句中的DML部分，不能用DDL部分，如果要在PL/SQL中使用DDL（如CREATEtable等）的话，只能以动态的方式来使用。

2.PL/SQL块结构和组成元素,PL/SQL块,PL/SQL的程序结构,PL/SQL程序都是以块（block）为基本单位。

PL/SQL块可以分为三类：

1.无名块：

动态构造，只能执行一次。

2.子程序：

存储在数据库中的存储过程、函数及包等。

当在数据库上建立好后可以在其它程序中调用它们。

3.触发器：

当数据库发生操作时，会触发一些事件，从而自动执行相应的程序。

整个PL/SQL块分三部分：

声明部分（用declare开头）、执行部分（以begin开头,以end结尾）和异常处理部分（以exception开头）。

其中执行部分是必须的，其他两个部分可选。

DECLAREnnumber（10,2）;BEGINDECLAREvvarchar2（8）;BEGINEND;END;,建议少使用“嵌套”,DECLARE变量、常量、游标、自定义异常BEGINSQL语句PL/SQL控制语句EXCEPTION错误发生时执行的动作END；,变量声明,在大多数PL/SQL块中，都需要使用变量来包含在程序逻辑中使用的值。

无论需要什么类型的变量，能够在BEGIN部分中使用它之前，都必须在块的DECLARE段中声明这些变量。

声明变量必须提供变量名称和数据类型。

变量名称遵循以下约定：

（1）以一个字母字符开头

（2）最多包含30个字符（3）可以包含大写和小写字母、数字以及_、$、#及其它特殊字符。

变量声明,变量命名在PL/SQL中有特别的讲究，建议在系统的设计阶段就要求所有编程人员共同遵守一定的要求，使得整个系统的文档在规范上达到要求。

下面是建议的命名方法：

基本数据类型变量,变量类型,/*声明部分，以declare开头*/declarev_idinteger;v_namevarchar（20）;cursorc_empisselect*fromemployeewhereemp_id=3;/*执行部分，以begin开头*/beginopenc_emp;-打开游标loopfetchc_empintov_id,v_name;-从游标取数据exitwhenc_emp%notfound;endloop;closec_emp;-关闭游标dbms_output.PUT_LINE（v_name）;/*异常处理部分，以exception开始*/exceptionwhenno_data_foundthendbms_output.PUT_LINE（没有数据）;end;,PL/SQL结构,在块的声明部分对变量进行声明。

声明一个变量的语法是：

Variable_nameCONSTANTtypeNOTNULL:

=value;,如果在变量声明中使用了CONSTANT，那么该变量必须要被初始化，并且它的取值不能被改变为与其初始值不同的数值。

常量变量被作为只读的方式进行处理。

在声明中可以用关键字DEFAULT替换:

=。

DECLAREn1number（10,2）;v1varchar2（20）DEFAULT铜陵;v2varchar2（20）:

=铜陵;v3CONSTANTvarchar2（20）:

=铜陵;v4CONSTANTvarchar2（20）DEFAULT铜陵;BEGINEND;,注意：

在声明部分，每一行只能有一个变量声明，如以下的声明是错误的：

Declarev_firstname,v_lastnamevarchar2（20）;正确的声明为：

Declarev_firstnamevarchar2（20）;v_last_namevarchar2（20）;,另外还可以在变量中加入NOTNULL如果在声明时指明notnull，那么应该给该变量赋初值，下面声明是错误的：

Declarev_tempvarnumbernotnull;正确的声明为：

Declarev_tempvarnumbernotnull:

=1;,注意：

CONSTANT关键字是在变量类型之前列出的，而NOTNULL是在数据类型之后列出的。

另外还可以在PL/SQL块中包括SQL：

declarelv_qty_numnumber（3）;beginselectcount（*）intolv_qty_numfromemp;dbms_output.put_line（lv_qty_num）;End;,PL/SQL结构,变量类型,NUMBER（P,S）,P是长度，S是精度。

长度是数值中所有数字位的个数，而精度是小数点右边的数字位的个数（如果精度是个负数，那么就由小数点开始向左边进行计算数字位的个数）。

PL/SQL结构,变量类型,使用记录类型记录类型是把逻辑相关的数据作为一个单元存储起来。

DECLARETYPEtest_recISRECORD（CodeVARCHAR2（10）,NameVARCHAR2（30）NOTNULL:

=abook）;V_booktest_rec;BEGINV_book.code:

=123;V_book.name:

=C+Programming;DBMS_OUTPUT.PUT_LINE（v_book.code|v_book.name）;END;,初始化记录,定义记录型变量,PL/SQL结构,变量类型,使用%TYPE定义一个变量，其数据类型与已经定义的某个数据变量的类型相同，或者与数据库表的某个列的数据类型相同，这时可以使用%TYPE。

TYPEt_RecordISRECORD（T_noemp.empno%TYPE,T_nameemp.ename%TYPE,T_salemp.sal%TYPE）;-声明接收数据的变量v_empt_Record;BEGINSELECTempno,ename,salINTOv_empFROMempWHEREempno=7788;DBMS_OUTPUT.PUT_LINE（TO_CHAR（v_emp.t_no）|v_emp.t_name|TO_CHAR（v_emp.t_sal）;END;,使用%TYPE特性的优点在于：

所引用的数据库列的数据类型可以不必知道；所引用的数据库列的数据类型可以实时改变。

表的某一个字段的类型,PL/SQL结构,变量类型,使用%ROWTYPE可以将变量类型定义为与具有相同类型的数据库行。

%ROWTYPE将返回一个基于表定义的类型。

DECLAREv_empnoemp.empno%TYPE:

=,使用%ROWTYPE特性的优点在于：

所引用的数据库中列的个数和数据类型可以不必知道；所引用的数据库中列的个数和数据类型可以实时改变。

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-a1bb100a1,CREATETABLEW_TEST1（BHVARCHAR2

（2）,NNUMBER（10）;,变量作用域,PL/SQL结构,SELECT,SELECT字段名1,字段名2FROM表名WHERE条件GROUPBY字段名ORDERBY字段名;,SELECTBH,SUM（N）SFROMW_TEST1WHEREN8GROUPBYBHORDERBYBH,BHN-a1a2a3a4a5a6a7a8a9C10000C10001d10002d10002c10003c10004d10004d10004,BHS-C20001a9c20007d40012,SELECTBH,NINTOv_name,v_amountFROMW_TEST1WHEREN=1,PL/SQL结构,INSERT,DECLAREnum1number;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结构,INSERT,INSERTINTO表1（字段1,字段2）SELECT字段1,字段2FROM表2WHERE条件,INSERTINTOW_TEST1（BH,N）SELECTBH,NFROMW_TEST1WHEREN8;,DECLARERECORD1W_TEST1%ROWTYPE;BEGINSELECTBH,NINTORECORD1FROMW_TEST1WHEREN=8;END;,PL/SQL结构,UPDATE,UPDATE表名SET字段=值WHERE条件,UPDATEW_TEST1SETBH=gWHEREn=5,UPDATE表名SET（字段1,字段2）=（SELECT子句）WHERE条件,UPDATEW_TEST1SET（BH,n）=（selectbh,nfromw_tes1wheren=10000）WHEREn5andn9,PL/SQL结构,DELETE,DELETEFROM表名WHERE条件;,DELETEFROMW_TEST1WHEREn=2;,PL/SQL结构,动态SQL,PL/SQL不直接支持DDL，因此需要使用动态SQL,SETSERVEROUTPUTONDECLAREv_statementVARCHAR2（500）;CURSORtrigger_curISSELECTtrigger_nameFROMuser_triggers;BEGINFORyINtrigger_curLOOP-Buildthestatementv_statement:

=ALTERTRIGGER|y.trigger_name|DISABLE;-RunthestatementEXECUTEIMMEDIATEv_statement;ENDLOOP;END;,PL/SQL结构,TABLESPACE,/创建临时表空间（临时表空间,用于临时数据的存放）createtemporarytablespaceuser_tempTempfileD:

oracleoradataOracle9iuser_temp.dbfsize50mautoextendonnext50mmaxsize20480mextentmanagementlocal;,一个表空间就是一片磁盘区域,它有一个或者多个磁盘文件组成,一个表空间可以容纳许多表、索引或者簇等每个表空间有一个初始区间（initialextent）用完这个区间后再用下一个，直到用完表空间，这时候需要对表空间进行扩展，增加数据文件或者扩大已经存在的数据文件。

/创建数据表空间（用于用户数据的存放）createtemporarytablespacetest_dataTempfileD:

oracleoradataOracle9iuser_data.dbfsize50mautoextendonnext50mmaxsize20480mextentmanagementlocal;,本地管理（LOCAL）:

用二进制的方式管理磁盘,有很高的效率,能最大限度的使用磁盘.同时能够自动跟踪记录临近空闲空间的情况，避免进行空闲区的合并操作。

PL/SQL结构,TABLE,CREATETABLEST_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）tablespaceCUST_GENERAL_Dpctfree10-用于指定BLOCK中必需保留的最小空间的比例。

pctused40-为一个百分比数值，当BLOCK中已经使用的空间降低到该数值以下时，该BLOCK才是可用的，达到或是超过这个数值的BLOCK是不可用的。

initrans1-指定可以并发操作该表的事务的数目。

maxtrans255指定最大并发操作该表的事务处理条目。

storage（initial40Knext40Kminextents1maxextentsunlimitedpctincrease0-表示每个扩展Extents的增长率）;,CREATETABLE表名（字段名1数据类型，字段名2数据类型，CONSTRAINT约束名PRIMARYKEY（字段名1,字段名2）,CONSTRAINT约束名FOREIGNKEY（字段名）REFERENCESS表名）;,PL/SQL结构,ALTER,ALTERTABLEW_TEST1MODIFY（BHVARCHAR2（4）,NNUMBER（15）;,ALTERTABLE表名MODIFY（字段1数据类型，字段2数据类型）；,PL/SQL结构,INDEX（索引）,CREATEINDEXSI_W_TEST1ONW_TEST1（BH,DH）TABLESPACETDMIS;,CREATEINDEX索引名on表名TABLESPACE表空间名;,PL/SQL结构,序列,序列（sequence）是一种oracle对象，用来产生唯一的数字。

其中sequence.CURRVAL表示序列当前值sequence.NEXTVAL使序列增值并返回新的取值,CREATESEQUENCEW_TEST1_S1INCREMENTBY1-每次加几个STARTWITH1-从1开始计数NOMAXVALUE-不设置最大值NOCYCLE-一直累加，不循环CACHE10;,Insertintow_test1（bh,n）values（c,W_TEST1_S1.NEXTVAL）Insertintow_test1（bh,n）values（c,W_TEST1_S1.NEXTVAL）Insertintow_test1（bh,n）values（d,W_TEST1_S1.CURRVAL）Insertintow_test1（bh,n）values（d,W_TEST1_S1.CURRVAL）,PL/SQL结构,同义词,CREATEPUBLICSYNONYM同义词名FOR表名或视图名,CREATEPUBLICSYNONYMCX_FXTJFORCX_FXTJ;,PL/SQL结构,快照,CREATESNAPSHOTKZ_FH_YZDZSTORAGE（INITIAL100KNEXT100KPCTINCREASE0）TABLESPACETDMISREFRESHCOMPLETE-FAST,FORCESTARTWITHSYSDATENEXTSYSDATE+1ASSELECTPID,ND,YF,MAX（FH）ZDZFROMKZ_FHGROUPBYPID,ND,YF;,EXECUTEDBMS_SNAPSHOT.REFRESH（KZ_FH_YZDZ,C）;,Oracle数据库的快照是一个表，它包含有对一个本地或远程数据库上一个或多个表或视图的查询的结果。

也就是说快照根本的原理就是将本地或远程数据库上的一个查询结果保存在一个表中。

PL/SQL结构,ROLE,CREATEROLE角色名;CREATE权限ON对象TO角色名,CREATEROLERW_TEST1;GRANTSELECTONW_TEST1TORW_TEST1;GRANTSELECTONW_TEST1TOPUBLIC;,PL/SQL结构,USER,createuser用户名identifiedby密码defaulttablespace表空间;,createuserstudyidentifiedbystudydefaulttablespacedata_test;grantconnect,resourcetostudy;,PL/SQL结构,GRANT和REVOKE,GRANT权限ON对象TO用户/角色REVOKE权限ON对象FROM用户/角色,grantalterontabletostudy;revokealterontablefromstudy;,grantconnect,resourcetostudy;,PL/SQL结构,COMMIT、ROLLBACK、SAVEPOINT,DELETEFROW_TEST1WHEREn=10000;SAVEPOINTA;DELETEFROW_TEST1WHEREn=10001;SAVEPOINTB;DELETEFROW_TEST1WHEREn=10002;SAVEPOINTC;ROLLBACKTOBCOMMIT;,3.PL/SQL流程控制语句,PL/SQL结构,控制结构,DECLAREnnumber;BEGIN.IFn50then.ELSIFn100then.ELSE.ENDIF;.END;,DECLAREnnumber（10）;vvarchar2（10）;BEGINIFn=7thenv:

=是;aELSEv:

=否;ENDIF;IFn=7THENcv:

=是;ELSEv:

=否;ENDIF;END;,如果n为一个确定的值则a,b的结果是一样的如果n为NULL则a,b的结果就是不一样的,在条件判断时要注意NULL的情况。

PL/SQL结构,控制结构,CASEselectorWHENexpression1THENresult1WHENexpression2THENresult2WHENexpressionNTHENresultNELSEresultN+1END;,DECLAREV_gradechar

（1）:

=UPPER（,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结构,循环结构,GOTOlabel;./*标号是用括起来的标识符*/,DECLAREV_counterNUMBER:

=1;BEGINLOOPDBMS_OUTPUT.PUT_LINE（V_counter的当前值为:

|V_counter）;V_counter:

=v_counter+1;IFv_counter10THENGOTOl_ENDofLOOP;ENDIF;ENDLOOP;DBMS_OUTPUT.PUT_LINE（V_counter的当前值为:

|V_counter）;END;,当程序运行到GOTOI_ENDofLOOP语句时，会自动跳转到标签处。

4.游标的使用,为了处理SQL语句，ORACLE必须分配一片叫上下文（contextarea）的区域来处理所必需的信息，其中包括要处理的行的数目，一个指向语句被分析以后的表示形式的指针以及查询的活动集（activeset）。

游标是一个指向上下文的句柄（handle）或指针。

通过游标，PL/SQL可以控制上下文区和处理语句时上下文区会发生些什么事情。

对于不同的SQL语句，游标的使用情况不同：

定义游标：

就是定义一个游标名，以及与其相对应的SELECT语句。

CURSORcursor_name（parameter,parameter）ISselect_statement;在指定数据类型时，不能使用长度约束。

如NUMBER（4）、CHAR（10）等都是错误的。

打开游标：

OPENcursor_name（parameter=value,parameter=value）;提取游标数据：

就是检索结果集合中的数据行，放入指定的输出变量中。

FETCH