Oracle知识综合整理

1、SQL语言分类种类缩写全称常用操作数据定义语言DDLData deefine languuagecreate, alteer, drrop数据操纵语言DMLData Maanipullationn Langguageselect, inseert, ddeletee, upddate事务控制语言TCLTransacction Contrrol Laanguaggecommit, savaapointt, rolllbackk数据控制语言DCLData Coontroll Langguagegrant, revokkeSQL常用数据据类型分类关键字表示范围备注字符char12000字字节固定长度

2、varcharr214000字字节可自增长长度long2GB可自增长长度数值number定义方式number(P,S)P:长度 S:精度日期date日期和时间部分分，精确到整整个的秒timestaamp存储日期、时间间和时区信息息，秒值精确确到小数点后后6位RAWraw12000字字节存储二进制数据据long raaw2GB存储二进制数据据LOBclob4GB能够存储大量字字符数据blob4GB可以存储较大的的二进制对象象，如图形、视频剪辑和和声音文件bfile4GB用于将二进制数数据存储在数数据库外部的的操作系统文文件中Oracle中中的伪列Oracle 中伪列就像像一个表列，但但是它并没有

3、有存储在表中中伪列可以从表中中查询，但不不能插入、更更新和删除它它们的值常用的伪列有 ROWIDD 和ROWNUUMROWIDROWID是表表中行的存储储地址，该地地址可以唯一一地标识数据据库中的一行行，可以使用用ROWIDD伪列快速地地定位表中的的一行。ROWNUMROWNUM是是查询返回的的结果集中行行的序号，可可以使用它来来限制查询返返回的行数。命名规则Oracle中中的各种数据据对象，包括括表名称，视视图等等名称称的命名都需需要遵循Orracle的的命名规则。Oraclle的命名规规则分为标准准命名方式和和非标准命名名方式。标准命名方式以字符打头300个字符以内内只能包含AA-Z,a-

4、z,0-99,_,$和和#。不能和同一一个用户下的的其他对象重重名，不能是orracle服服务器的保留留字。非标准命名方式式你可以使用你想想使用的任何何字符，包括括中文，orracle中中的保留字，空空格等等，但但是需要将对对象名用双引引号引起来。例如： ccreatee tablle “tablee” (tesst1 vaarcharr2(10);创建表空间CREATE TABLEESPACEE tabllespaccenameeDATAFILLE fiilenamme SSIZE iintegeer K|MAUTOEXXTEND OFF|ON;注：大写字母为为关键字，小小写部分为用用户自定

5、义部部分；中中的内容为可可选择部分。例：creatte tabblespaace myy_tabllespacce dataffile c:myyspacee sizee 100KK autoeextendd on;Oracle用用户操作创建用户CREATE USER usernnameIDENTIFFIED BBY passsworddDEFAULLT TABBLESPAACE taablesppaceTEMPORRARY TTABLESSPACE tableespacee;例：creatte useer scoott iddentiffied bby tigger;密码修改ALTER UU

6、SER uusernaame IDDENTIFFIED BBY newwpasswword锁定用户ALTER UUSER uusernaame ACCCOUNTT LOCKK;撤销锁定ALTER UUSER uusernaame ACCCOUNTT UNLOOCK;删除用户DROP USSER ussernamme;权限管理Oracle权权限有两种类类型，系统权权限和对象权权限。系统权限：系统统规定用户使使用数据库的的权限。（系统权限是是对用户而言言）。实体权限：某种种权限用户对对其它用户的的表或视图的的存取权限。（是针对表表或视图而言言的）。系统权限管理系统权限分类DBA：拥有全全部特权，是

7、是系统最高权权限，只有DDBA才可以以创建数据库库结构。RESOURCCE：拥有Ressourcee权限的用户户只可以创建建实体，不可可以创建数据据库结构。CONNECTT：拥有Connnect权权限的用户只只可以登录OOraclee，不可以创创建实体，不不可以创建数数据库结构。对于普通用户：授予connnect, resoource权权限。对于DBA管理理用户：授予予conneect,resouurce, dba权限限。系统权限授权系统权限只能由由DBA用户户授出：syys, syystem(最开始只能能是这两个用用户)授权命令：GRRANT cconnecct, reesourcce, d

8、bba TOuserrname;普通用户通过授授权可以具有有与systtem相同的的用户权限，但但永远不能达达到与syss用户相同的的权限，syystem用用户的权限也也可以被回收收。例：grantt connnect, resouurce tto scoott;系统权限传递增加WITH ADMINN OPTIION选项，则则得到的权限限可以传递。例：grantt connnect, resorrce too userr01 withh admiin opttion;系统权限回收REVOKE conneect, rresourrce FRROM ussernamme;实体权限管理实体权限分类s

9、elect, updaate, iinsertt, altter, iindex, deleete, aall等实体权限授权GRANT sselectt, upddate, inserrt ONtabllenameeTOuserrname;例：grantt all on emmp to scottt;实体权限传递例：user001:graant seelect, updaate onn prodduct tto useer02 wwith ggrant optioon;实体权限回收REVOKE selecct, uppdate ONtabllenameeFROMussernamme;角色管理角

10、色是一组权限限的集合，将将角色赋给一一个用户，这这个用户就拥拥有了这个角角色中的所有有权限。系统预定义角色色预定义角色是在在数据库安装装后，系统自自动创建的一一些常用的角角色。角色所包含的权权限可以用以以下语句查询询：select * froom rolle_syss_privvs wheere roole=角色名;CONNECTT, RESSOURCEE, DBAA这些预定义角色色主要是为了了向后兼容。其主要是用用于数据库管管理。oraacle建议议用户自己设设计数据库管管理和安全的的权限规划，而而不要简单的的使用这些预预定角色。DELETE_CATALLOG_ROOLE,EXECUUTE_

11、CAATALOGG_ROLEE,SELECCT_CATTALOG_ROLE这些角色主要用用于访问数据据字典视图和和包SNMPAGEENT用于oraclle entterpriise maanagerr和Inteelligeent AggentRECOVERRY_CATTALOG_OWNERR用于创建拥有恢恢复库的用户户。关于恢复复库的信息，参参考oraccle文档Oraclle9i UUser-MManageed Bacckup aand Reecoverry GuiideHS_ADMIIN_ROLLEA DBA uusing Oraclles hheteroogeneoous seervic

12、ees feaature needss thiss rolee to aaccesss apprropriaate taables in thhe datta dicctionaary.管理角色建一个角色create role role11;授权给角色grant ccreatee any tablee,creaate prroceduure too rolee1;授予角色给用户户grant rrole1 to usser1;查看角色所包含含的权限select * froom rolle_syss_privvs;scott的权权限如图：-查看scoott用户具具有什么系统统权限select *

13、froom rolle_syss_privvs;-查看scoott用户自自己拥有什么么角色select * froom useer_rolle_priivs;-查看scoott用户自自己具有什么么的权限select * froom SESSSION_ROLESS;-查scottt用户的创创建时间、用用户状态、使使用的默认表表空间、临时时表空间等信信息select * froom useer_useers;-查看scoott用户中中，都哪些用用户把对象授授予给scoott用户select * froom useer_tabb_privvs;-查看scoott用户中中拥有的reesourcce角色

14、都具具有什么权限限select * froom rolle_syss_privvs wheere roole=RRESOURRCE;-scottt用户自己拥拥有多少表select * froom useer_tabbles;-查看scoott用户已已经使用多大大的空间，允允许使用的最最大空间是多多少select tableespacee_namee,bytees,maxx_bytees froom useer_ts_quotaas;-查看哪些表表什么权限赋赋予了其他用用户select * froom useer_tabb_privvs_madde-把自己的表表赋予给其他他用户grant sse

15、lectt on eemp too mzl;-把表的某一一列操作权限限赋予给其他他用户grant uupdatee(job) on eemp too mzl;创建带有口令以以角色(在生生效带有口令令的角色时必必须提供口令令)create role role11 idenntifieed by passwword1;修改角色：是否否需要口令alter rrole rrole1 not iidentiified;alter rrole rrole1 identtifiedd by ppasswoord1;设置当前用户要要生效的角色色set rolle rolle1;/使rolee1生效set ro

16、lle rolle,rolle2;/使rolee1,rolle2生效set rolle rolle1 iddentiffied bby passswordd1;/使使用带有口令令的rolee1生效set rolle alll;/使用用该用户的所所有角色生效效set rolle nonne;/设设置所有角色色失效set rolle alll exceept roole1;/除rolle1外的该该用户的所有有其它角色生生效select * froom SESSSION_ROLESS;/查看看当前用户的的生效的角色色修改指定用户，设设置其默认角角色alter uuser uuser1 defauul

17、t roole roole1;alter uuser uuser1 defauult roole alll exccept rrole1;删除角色drop roole roole1;Oracle用用户密码过期期解决方法原因：由于orracle111g中默认认在defaault概要要文件中设置置了“PASSWWORD_LLIFE_TTIME=1180天”所导致。解决方案：1、查看用户的的proiffle是哪个个，一般是ddefaullt：sqlSELLECT uusernaame,PRROFILEE FROMM dba_userss;2、查看指定概概要文件（如如defauult）的密密码有效期设

18、设置：sqlSELLECT * FROMM dba_profiiles ss WHERRE s.pprofille=DEEFAULTT ANDD resoource_name=PASSSWORD_LIFE_TIME;3、将密码有效效期由默认的的180天修修改成“无限制”：sqlALTTER PRROFILEE DEFAAULT LLIMIT PASSWWORD_LLIFE_TTIME UUNLIMIITED;修改之后不需要要重启动数据据库，会立即即生效。数据定义语言DDDL数据定义语言用用于改变数据据库结构，包包括创建、更更改和删除数数据库对象。用于操纵表结构构的数据定义义语言命令有有：CRE

19、ATE TABLEEALTER TTABLETRUNCATTE TABBLEDROP TAABLE利用现有表创建建新表语法： CREEATE TTABLE AS SELECCT collumn_nnames FROM ;例如：CREAATE TAABLE nnewiteemfileeAS SEELECT * FROOM iteemfilee;创建新表语法：CREAATE TAABLE ( Colnaame daatatyppe deefaultt XXX PRIIMARY KEY,Colnamee1 dattatypee deffault XXX NOT NULL | NULLLcolname

20、eN dattatypee deffault XXX);例如：creaate taable sscott.mytabble(uidd numbber(5) primmary kkey,name vvarchaar2(100);修改表结构编辑表的字段修改一个列的数数据类型(一一般限于修改改长度，修改改为一个不同同类型时有诸诸多限制)。语法：ALTEER TABBLE 表名名 MODIIFY(列名名,数据类型型);例如：alteer tabble skkate_ttest mmodifyy (autthor nnumberr(10,00) );增加一个列语法：ALTEER TABBLE 表名名 A

21、DD(列名,数据据类型);例如：ALTEER TABBLE skkate_ttest AADD(auuthor NUMBEER(38,0) noot nulll);给列改名语法：ALTEER TABBLE 表名名 DROPP COLUUMN 列名名;例如：alteer tabble skkate_ttest ddrop ccolumnn authhor;将一个表改名语法：ALTEER TABBLE 当前前表名 REENAME TO 新表表名;例如：alteer tabble skkate_ttest rrenamee to ttest_ssakte;添加约束语法：ALTEER TABBLE 表

22、名名 ADD CONSSTRAINNTconsstrainnt_namme 约束束条件(列名名) ；例如：alteer tabble scc add consttraintt sc_ffk_couurse fforeiggn keyy(cid) refeerencees couurse(ccid);/添加外键键alter ttable studeent addd connstraiint ckk_studdent_sssex ccheck(ssex in(男男,女);/添加cheeck约束删除约束语法：ALTEER TABBLE 表名名 CONSSTRAINNTconsstrainnt_nam

23、me；例如：alteer tabble sttudentt dropp consstrainnt ck_studeent_saage;截断表使用TRUNCCATE命令令可以删除表表中所有数据据，且不能回回滚。语法：TRUNNCATE TABLEE tabllenamee;删除表语法：DROPP TABLLE tabblenamme;数据操纵语言DDML数据操纵语言用用于检索、插插入和修改数数据数据操纵语言是是最常见的SSQL命令数据操纵语言命命令包括：SSELECTT、INSERRT、UPDATTE、DELETTE增insertt语法：INSEERT INNTO taable_nname (列

24、1, 列2,) VVALUESS (值1, 值2,);例如：INSEERTINTTOs_deeptVALLUES (11,FFrinacce,2);INSERTIINTOs_deptss_emp(id,last_name,firstt_namee,salaary,sttart_ddate)VALUES (11,Frinaace,Jon,15600,03-4月月-92);INSERTIINTO hhistorrySELEECTid,last_name,salarryFROMMs_empp WHHEREsttart_ddate UUPDATEE ordeer_massterSEET dell_da

25、tee =30-8月月-05WHERE oordernno SSAVEPOOINT mmark1;SQL DDELETEE FROMM ordeer_masster WWHERE orderrno = o0022;SQL SSAVEPOOINT mmark2;SQL RROLLBAACK TOO SAVEEPOINTT markk1;SQL CCOMMITT;数据控制语言DDCL数据控制语言为为用户提供权权限控制命令令 用于权限控制的的命令有：GRANT - 授予权限限REVOKE - 撤销已授授予的权限例如：SQLGRANTT SELEECT ONN venddor_maasterTTO a

26、cccountss WITHH GRANNT OPTTION;SQLREVVOKE SSELECTT, UPDDATE OON ordder_maasterFFROM MMARTINN;PL/SQL基本语法：DECLARRE 声声明部分declaraationssBEGIN 执行部分分executaable sstatemmentsEXCEPTTION 异常捕捕获部分 handlerrsEND;声明变量和常量量的语法：1.使用赋值语语句 :=2.使用 SEELECT INTO 语句例如：declaree var_nname vvarchaar(1000);-变变量的定义 var_jjob va

27、archarr(100); var_iint innt; var_cconst consttant vvarchaar(1000) :=;-);-常量的的定义 var_ddate eemp.hiiredatte%typpe;-引引用变量类型型 var_rrowtyppe empp%rowttype;提供表示示表中一行的的记录类型 var_bbooleaan booolean;-布尔类类型,值:TTRUE,FFALSE,nullbeginselect * intto varr_rowttype ffrom eemp whhere eename=upperr(var_name);end;控制结构P

28、L/SQL 支持的流程程控制结构：条件控制IF 语句CASE 语句句循环控制LOOP 循环环WHILE 循循环FOR 循环顺序控制GOTO 语句句NULL 语句句条件控制IF语句IF 语句根据据条件执行一一系列语句，有有三种形式：IF-THHEN、IFF-THENN-ELSEE 和 IFF-THENN-ELSIIF例如：declaree varr_sal emp1.sal%ttype;begin sellect ssal innto vaar_sall fromm emp11 wherre emppno=73369; if var_ssal=22000 tthen updatte empp1

29、sett sal=sal*11.05 wwhere empnoo=73699; elssif vaar_sall=10000 andd var_sal22000 tthen updatte empp1 sett sal=sal*11.1 whhere eempno=7369; elsse updatte empp1 sett sal=sal*11.2 whhere eempno=7369; endd if;end;CASE语句CASE 语句句用于根据单单个变量或表表达式与多个个值进行比较较。执行 CASEE 语句前，先先计算选择器器的值。例如：begin casse &ggrade wheen

30、 A thenn dbmss_outpput.puut_linne(优异异); wheen B thenn dbmss_outpput.puut_linne(优秀秀); wheen C thenn dbmss_outpput.puut_linne(良好好); wheen D thenn dbmss_outpput.puut_linne(一般般); wheen E thenn dbmss_outpput.puut_linne(较差差); elsse dbmms_outtput.pput_liine(无无成绩); endd casee;end;循环控制LOOP循环语法：whille loopp.

31、eend looop;exit 终止止当前循环 exit whhen 条件满满足则终止当当前循环例如：begin LLOOP dbmss_outpput.puut_linne(哈哈哈); EEND LOOOP;end;WHILE循环环例如：declaree vvar_coount nnumberr(10):=0;begin wwhile var_ccountvallue2;rreversse表示vaalue2-valuue1例如：declaree vvar_i numbeer(10):=1; vvar_j numbeer(10):=1;begin ffor vaar_i iin 1.9 loo

32、op foor varr_j inn 1.99 loopp DBMMS_OUTTPUT.pput(vaar_j|*|var_ii|=|varr_i*vaar_j| ); exiit wheen varr_j=vaar_i; ennd looop; DBBMS_OUUTPUT.put_lline( ); eend looop;end;顺序控制GOTO 语句句NULL语句goto - 无条件地转转到标签指定定的语句null -什什么也不做的的空语句例如：declaree vvar_a numbeer(2):=1;begin iif varr_a5 then gotto labbel1; eelse

33、 gotto labbel2; eend iff; DDBMS_OOUTPUTT.put_line(labeel1); nnull;end;动态SQL动态 SQL 是指在PLL/SQL程程序执行时生生成的 SQQL 语句。语法：execcute iimmediiate ddynamiic_sqll_striing INTO defiine_vaariablle_lisst USINGG bindd_arguument_list;例如：decllarevar_sqll varcchar2(200);var_emppno nuumber(4);var_empp emp%rowtyype;begin

34、 var_eempno:=&员工编编号; varr_sql := sselectt *froom empp1 wheere emmpno=:arg1 and ssal:aarg2; exeecute immeddiate var_ssql innto vaar_empp usinng varr_empnno,1000; DBMMS_OUTTPUT.pput_liine(vaar_empp.job);end;异常处理异常有两种类型型：预定义异常 - 当 PLL/SQL 程序违反 Oraclle 规则或或超越系统限限制时隐式引引发用户定义异常 - 用户可可以在 PLL/SQL 块的声明部部分定义异

35、常常，自定义的的异常通过 RAISEE 语句显式式引发预定义异常例如如：declareevar_id varchhar2(55);begin sellect eempno into var_iid froom empp1; sellect eempno into var_iid froom empp1 wheere emmpno=11111; DBMMS_OUTTPUT.pput_liine(我我被正常执行行啦!);exceptiion wheen tooo_manyy_rowss thenn DBMMS_OUTTPUT.pput_liine(返返回太多行); wheen NO_DATA_FO

36、UNDD thenn DBMMS_OUTTPUT.pput_liine(查查询未找到数数据);end;用户定义异常常例如：DECLAREE invallidCATTEGORYY EXCEEPTIONN; categgory VVARCHAAR2(100);BEGIN categgory := &CCategoory; IF caategorry NOTT IN (附件,顶盖,备件) THENN RAIISE innvaliddCATEGGORY; ELSE DBMMS_OUTTPUT.PPUT_LIINE(您您输入的类别别是| categgory); END IIF;EXCEPTIION WH

37、EN invallidCATTEGORYY THENN DBMMS_OUTTPUT.PPUT_LIINE(无无法识别该类类别);END;游标游标的类型有：1.隐式游游标 2.显显式游标 33.REF游游标(REFF 游标用于于处理运行时时才能确定的的动态 SQQL 查询的的结果)隐式游标在PL/SQLL中使用DMLL语句时自动动创建隐式游游标，隐式游标自自动声明、打打开和关闭，其其名为 SQQL。通过检查隐隐式游标的属属性可以获得得最近执行的的DML 语句句的信息。隐式游标属性：%found - SQLL 语句影响响了一行或多多行时为 TTRUE%notfouund - SQL 语语句没有影响

38、响任何行时为为 TRUEE%rowcouunt - SQL 语语句影响的行行数例如：declareevar_emppno emmp.emppno%tyype;BEGIN selecct emppno innto vaar_emppno frrom emmp wheere emmpno=77900;IF SQLL%FOUNND THEEN DBMMS_OUTTPUT.PPUT_LIINE(已已查找到|sql%rowcoount|条数据!);END IFF;EXCEPTIIONWHEN NNO_DATTA_FOUUND THHEN DBMSS_OUTPPUT.PUUT_LINNE(职员员未找到);

39、end;显示游标当查询返回结果果超过一行时时，就需要一个个显式游标。此时用户不不能使用seelect into语语句。PL/SQL管理理隐式游标，当当查询开始时时隐式游标打打开，查询结结束时隐式游游标自动关闭闭。显式游标标在PL/SSQL块的声声明部分声明明，在执行部部分或异常处处理部分打开开，取数据，关关闭。声明游标语法： CURRSOR ccursorr_namee IS sselectt_stattementt;打开游标使用游标中的值值之前应该首首先打开游标标，打开游标标初始化查询询处理。打开开游标的语法法是： OPEEN currsor_nname (curssor_naame是在声声

40、明部分定义义的游标名。)关闭游标 语法： CLLOSE ccursorr_namee从游标提取数据据 从游标标得到一行数数据使用FEETCH命令令。每一次提提取数据后，游游标都指向结结果集的下一一行。语法如如下： FEETCH ccursorr_namee INTOO variiable,variiable,.例如：declaree varr_enamme empp.enamme%typpe; currsor eemp_cuur is -声明明游标 sselectt enamme froom empp wherre jobb=uppeer(&jjob);begin opeen empp_cur

41、; -打开游游标 looop fettch emmp_curr intoo var_enamee; -提提取行 exiit wheen empp_cur%notfoound; DBMMS_OUTTPUT.pput_liine(vaar_enaame); endd loopp; cloose emmp_curr; -关闭游游标end; 带参数的游标与存储过程和函函数相似，可可以将参数传传递给游标并并在查询中使使用。这对于于处理在某种种条件下打开开游标的情况况非常有用。它的语法如如下：CURSORcursoor_namme(paarametter,pparameeter,.)ISsselectt_

42、stattementt;定义参数的语法法如下：Parametter_naameIINdaata_tyype:=|DEFFAULTvaluue 与存储过程不同同的是，游标标只能接受传传递的值，而而不能返回值值。参数只定定义数据类型型，没有大小小。另外可以给参数数设定一个缺缺省值，当没没有参数值传传递给游标时时，就使用缺缺省值。游标标中定义的参参数只是一个个占位符，在在别处引用该该参数不一定定可靠。在打开游标时给给参数赋值，语语法如下：OPENcuursor_namevaluee,vallue;参数值可以是文文字或变量。例如：DECLAREEvar_jjob emp.job%ttype;var_e

43、empno emp.eempno%type;var_eename empp.enamme%typpe;CURSOOR empp_cur(paramm_job emp.jjob%tyype) IIS SELEECT emmpno, enamee FROMM emp WHERRE jobb=paraam_jobb;BEGINvar_jjob:=uupper(&jobb);OPEN emp_ccur(vaar_jobb);LOOPFETCCH empp_cur INTO var_eempno,var_eename;EXITT WHENN emp_cur%NNOTFOUUND;DBMSS_OUTPPU

44、T.PUUT_LINNE(varr_empnno|, |vvar_enname); ENDD LOOPP;CLOSEE emp_cur;END;游标中的更新和和删除在PL/SQLL中依然可以以使用UPDDATE和DDELETEE语句更新或或删除数据行行。显式游标标只有在需要要获得多行数数据的情况下下使用。PLL/SQL提提供了仅仅使使用游标就可可以执行删除除或更新记录录的方法。语法：CURSOR IIS FOR UPDATTE OF ;UPDATE SET WHERE CCURRENNT OF DELETE FROM WHERE CCURRENNT OF 例如：declaree vvar_sa

45、al empp1.sall%typee; ccursorr emp_cur iis seleect saal froom empp1 wheere saal20000 forr updaate off sal;begin oopen eemp_cuur; lloop ffetch emp_ccur innto vaar_sall; eexit wwhen eemp_cuur%nottfoundd; uupdatee emp11 set sal=vvar_saal*1.11 wherre currrent of emmp_curr; eend looop; cclose emp_ccur;end

46、;循环游标循环游标用于简简化游标处理理代码，当用用户需要从游游标中提取所所有记录时使使用。语法：FOR IN LOOPEND LOOOP;例如：declaree currsor emp_ccur iss selecct enaame frrom emmp;begin forr emp_rec iin empp_cur looop DBMSS_OUTPPUT.PUUT_LINNE(empp_rec.enamee); endd loopp;end;REF游标REF 游标和和游标变量用用于处理运行行时动态执行行的 SQLL 查询。创建游标变量需需要两个步骤骤：1.声明 REEF 游标类类型2.声明

47、REEF 游标类类型的变量用于声明 REEF 游标类类型的语法为为：TYPE IS REF CCURSORRRETURRN ;打开游标变量的的语法如下： OPEN cursoor_namme FORR seleect_sttatemeent;声明强类型的 REF 游游标(有返回回值)：TYPE myy_cur IS REEF CURRSOR RETURRN empp%ROWTTYPE;var_curr mmy_curr; 声明弱类型的 REF 游游标：TYPE myy_cur IS REEF CURRSOR;var_curr my_cur;例如：declaree tyype myy_cur i

48、s reef currsor;声明游标类型型 vaar_empp emp%rowtyype; emmp_curr my_ccur;申明游标变变量begin oppen emmp_curr for selecct *frrom emmp;-打打开动态SQQL语句 looop feetch eemp_cuur intto varr_emp; exxit whhen emmp_curr%notffound; DBBMS_OUUTPUT.PUT_LLINE(vvar_emmp.enaame); ennd looop; cllose eemp_cuur;end;子程序命名的 PL/SQL 块块，编译并存

49、存储在数据库库中。子程序的各个部部分：声明部分可执行部分异常处理部分(可选)子程序的分类：过程 执行行某些操作函数 执行行操作并返回回值过程过程是用于完成成特定任务的的子程序。创建过程的语法法：-创建过程，可可指定运行过过程需传递的的参数CREATE OR RREPLACCE PRROCEDUURE ()IS|AS BEGIN-包括在过程程中要执行的的语句 -处理异常(可选)EXCEPTTION END;例如：create or reeplacee proccedureeget_emppno(teemp_noo emp.empnoo%typee) isvar_empp emp%rowtyype

50、;beginselect * intto varr_emp from emp wwhere empnoo=tempp_no; dbmss_outpput.puut_linne(varr_emp.enamee);exceptiion WHENN NO_DDATA_FFOUND THEN dbmss_outpput.puut_linne(无此此人信息!); end;过程参数的三种种模式：IN用于接受调用程程序的值默认的参数模式式OUT用于向调用程序序返回值 IN OUT用于接受调用程程序的值，并并向调用程序序返回更新的的值例如：create or reeplacee proccedureeswap

51、(teemp1 iin outt numbber,teemp2 iin outt numbber)istemp nuumber;begin teemp:=ttemp1; teemp1:=temp22; teemp2:=temp;end;测试declaree a numbeer(2):=1; b numbeer(2):=2;begin dbbms_ouutput.put_lline(a=|a); dbbms_ouutput.put_lline(b=|b); swwap(a,b); dbbms_ouutput.put_lline(a=|a); dbbms_ouutput.put_lline(b=|

52、b); end;函数函数是可以返回回值的命名的的 PL/SSQL 子程程序。创建函数的语法法：CREATE OR RREPLACCE FUUNCTIOON (paaram1,paramm2)RETURN IS|AS locaal decclarattionsBEGIN Execuutablee Stattementts; RETURRN ressult;EXCEPTIION Excepption handllers;END;定义函数的限制制：函数只能接受 IN 参数数，而不能接接受 IN OUT 或或 OUT 参数形参不能是 PPL/SQLL 类型函数的返回类型型也必须是数数据库类型访问函数的

53、两种种方式：使用 PL/SSQL 块使用 SQL 语句例如：create or reeplacee funcctionmy_funreturn varchhar2 iisbeginreturn 哈哈;end;测试试begin dbbms_ouutput.put_lline(mmy_funn); end;程序包程序包是对相关关过程、函数数、变量、游游标和异常等等对象的封装装。程序包由规范和和主体两部分分组成规范：声明程序序包中公共对对象。包括类类型、变量、常量、异常常、游标规范范和子程序规规范等。主体：声明程序序包私有对象象和实现在包包规范中声明明的子程序和和游标。语法：程序包规范：CREATE

54、 OR RREPLACCEPACCKAGE IS|ASSPublicc itemm decllaratiionsSubproogram speciificattionEND paackagee_namee;程序包主体：CREATE OR RREPLACCE PAACKAGEE BODYY IS|AASPrivatte iteem decclarattionsSubproogram bodieesBEGINInitiallizatiionEND paackagee_namee;例如：-定义程序序包规范create or reeplacee packkage mmy_pacckage isproc

55、eduure prrint;functioon priint_sttr(strr varcchar2) retuurn vaarcharr2;end my_packaage;-定义程序包包主体create or reeplacee packkage bbody mmy_pacckage is proceedure printt is beginn ddbms_ooutputt.put_line(哈哈); end pprint; functtion printt_str(str vvarchaar2) rreturnn varcchar2 is var_sstr vaarcharr2(1000)

56、; beginn vvar_sttr:=sttr; rreturnn strr; end pprint_str;end my_packaage;-测试begin my_paackagee.prinnt; dbms_outpuut.putt_linee(my_ppackagge.priint_sttr(o)/);end;程序包中的游标标游标的定义分为为游标规范和和游标主体两两部分。在包规范中声明明游标规范时时必须使用 RETURRN 子句指指定游标的返返回类型。RETURN子子句指定的数数据类型可以以是：用 %ROWTTYPE 属属性引用表定定义的记录类类型程序员定义的记记录类型例如：-程序包规

57、范范CREATE OR REEPLACEE PACKKAGE ccur_paack ISS CURSOOR ordd_cur(vcodee VARCCHAR2) RETTURN oorder_masteer%ROWWTYPE; PROCEEDURE ord_ppro(vccode VVARCHAAR2);END curr_packk;-程序包主体体CREATE OR REEPLACEE PACKKAGE BBODY ccur_paack ASS CURSORR ord_cur(vvcode VARCHHAR2) RETURNN ordeer_masster%RROWTYPPE IS SELEC

58、TT * FRROM orrder_mmasterr WHERRE VENNCODE=vcodee; PROCEDDURE oord_prro(vcoode VAARCHARR2) ISS or_rrec orrder_mmasterr%ROWTTYPE; BEGIN OPEN ord_ccur(vccode); LOOPFETCH oord_cuur INTTO or_rec;EXIT WHHEN orrd_curr%NOTFFOUND; DBMMS_OUTTPUT.PPUT_LIIne(返回的值为为 | or_reec.ordderno);END LOOOP;END ordd_pro;EN

59、D curr_packk;触发器触发器是当特定定事件出现时时自动执行的的存储过程。特定事件可以是是执行更新的的DML语句句和DDL语语句触发器不能被显显式调用触发器的功能：自动生成数据自定义复杂的安安全权限提供审计和日志志记录启用复杂的业务务逻辑触发器由三部分分组成：触发器语句（事事件）定义激活触发器器的 DMLL 事件和 DDL 事事件触发器限制执行触发器的条条件，该条件件必须为真才才能激活触发发器触发器操作（主主体）包含一些 SQQL 语句和和代码，它们们在发出了触触发器语句且且触发限制的的值为真时运运行语法create OR RREPLACCE trriggerr after | bef

60、oore | insteead offinsertt oor uppdate of ccolumnn_listt or ddeleteeon refereencingg oldd as old / neww AS newfor eaach roowwhen (condiition)例如create or reeplacee triggger ttrig_ssal afterr updaate off sal on emmp1 for eeach rrow when(new.ssalolld.sall)declaree sall_difff numbber;begin sall_difff:=: