第7章Oracle10g数据库管理系统课件

实用数据库技术

Oracle10g数据库管理系统实用数据库技术Oracle10g数据库管理系统学习内容Oracle概述Oracle10g数据库安装Oracle10g数据库卸载Oracle10g常用管理工具Oracle10g表管理使用过程和触发器学习内容Oracle概述Oracle概述Oracle公司Oracle数据库Oracle概述Oracle公司Oracle10g数据库安装服务器安装需求版本企业版标准版个人版最低配置

CPU：Pentium166

内存：128MB

硬盘空间：企业版(1．76GB)：标准版(1．76GB)：个人版(1．72GB)

视频：256色Oracle10g数据库安装服务器安装需求Oracle10g数据库安装客户端安装需求安装类型管理者(administrator)运行时环境(runtime)自定义(custom)最低配置

CPU：Pentium166

内存：128MB

硬盘空间：管理者(647MB)：运行时环境(486MB)

Oracle10g数据库安装客户端安装需求Oracle10g数据库安装过程(略)Oracle10g数据库安装过程(略)Oracle数据库卸载1、停止服务2、卸载Oracle产品3、清理注册表4、清理环境变量5、清理磁盘Oracle数据库卸载1、停止服务Oracle卸载1/5开始－＞设置－＞控制面板－＞管理工具－＞服务停止所有Oracle服务Oracle卸载1/5开始－＞设置－＞控制面板－＞管理工具Oracle卸载2/5开始－＞程序－＞OracleInstallationProducts－＞UniversalInstaller卸装所有Oracle产品，但UniversalInstaller本身不能被删除Oracle卸载2/5开始－＞程序－＞OracleInsOracle卸载3/5运行regeditHKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services滚动这个列表，删除所有Oracle入口。HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Eventlog\Application，除所有Oracle入口。Oracle卸载3/5运行regeditOracle卸载4/5开始－＞设置－＞控制面板－＞系统－＞高级－＞环境变量删除环境变量CLASSPATH和PATH中有关Oracle的设定Oracle卸载4/5开始－＞设置－＞控制面板－＞系统－＞Oracle卸载5/5从桌面上、STARTUP（启动）组、程序菜单中，删除所有有关Oracle的组和图标删除\ProgramFiles\Oracle目录重新启动计算机，重起后才能完全删除Oracle所在目录,删除与Oracle有关的文件，选择Oracle所在的缺省目录C:\Oracle，删除这个入

口目录及所有子目录，并从Windows2000目录（一般为C:\WINNT）下删除以下文件ORACLE.INI、oraodbc.ini等等。Oracle卸载5/5从桌面上、STARTUP（启动）组、Oracle10g常用管理工具OEM控制台SQL*PLUS工具介绍Oracle10g常用管理工具OEM控制台Oracle10g表管理在Oracle中建表，主要有两种方式：一种是在SQL*PLUS中执行建表的SQL语句。另一种是通过OEM工具。Oracle10g表管理在Oracle中建表，主要有两种方式Oracle10g数据中的数据类型数据类型类型说明CHAR字符型，最长为2000BNCHAR基于字符集的字符型，同上VARCHAR2变长字符型，最长为4000BNVARCHAR2基于字符集的变长字符型，同上VARCHAR同VARCHAR2LONG变长字符型，最长为2GBNUMBER(s,d)数字型，总位数为s位，小数位为d位，总长最大为38位DATE日期型RAW纯二进制数据类型，最长为2000BLONGRAW变长二进制数据类型，最长为2GBBLOB二进制大对象类型，最长为4GBNLOB包含定宽的多位数字符的字符大对象类型，最长为4GBCLOB包含单个字符的字符大对象类型，最长为4GBBFILE数据库外的大型二进制文件的输入指示器类型，最大为4GBOracle10g数据中的数据类型数据类型类型说明CHARPL/SQL语言基础注释变量声明运算符PL/SQL块结构控制语句PL/SQL语言基础注释PL/SQL程序设计简介PL/SQL是一种高级数据库程序设计语言，该语言专门用于在各种环境下对ORACLE数据库进行访问。由于该语言集成于数据库服务器中，所以PL/SQL代码可以对数据进行快速高效的处理。除此之外，可以在ORACLE数据库的某些客户端工具中，使用PL/SQL语言也是该语言的一个特点。PL/SQL程序设计简介PL/SQL是一种高级数据库程序SQL与PL/SQLPL/SQL是ProcedureLanguage&StructuredQueryLanguage的缩写。ORACLE的SQL是支持ANSI(AmericannationalStandardsInstitute)和ISO92(InternationalStandardsOrganization)标准的产品。PL/SQL是对SQL语言存储过程语言的扩展。SQL与PL/SQLPL/SQL是ProcedureLa为什么使用PL/SQL有利于客户/服务器环境应用的运行适合于客户环境为什么使用PL/SQL有利于客户/服务器环境应用的运行PL/SQL块结构PL/SQL程序由三个块组成，即声明部分、执行部分、异常处理部分。DECLARE/*声明部分:在此声明PL/SQL用到的变量,类型及游标，以及局部的存储过程和函数*/BEGIN/*执行部分:过程及SQL语句,即程序的主要部分*/EXCEPTION/*执行异常部分:错误处理*/END;PL/SQL块结构PL/SQL程序由三个块组成，即声明部分、PL/SQL块可以分为三类

无名块：动态构造，只能执行一次。子程序：存储在数据库中的存储过程、函数及包等。当在数据库上建立好后可以在其它程序中调用它们。触发器：当数据库发生操作时，会触发一些事件，从而自动执行相应的程序。PL/SQL块可以分为三类无名块：动态构造，只能执行一次。标识符PL/SQL程序设计中的标识符定义与SQL的标识符定义的要求相同。要求和限制有：标识符名不能超过30字符；第一个字符必须为字母；不分大小写；不能用’-‘(减号);不能是SQL保留字。标识符PL/SQL程序设计中的标识符定义与SQL的标识符定实例下面的例子将会删除所有的纪录，而不是KING的记录。DECLAREv_enamevarchar2(20):='KING';BEGINDELETEFROMempWHEREename=v_ename;END;实例下面的例子将会删除所有的纪录，而不是KING的记录。D如何运行PL/SQL块结构？如何运行PL/SQL块结构？如何运行PL/SQL块结构？如何运行PL/SQL块结构？建议的命名方法建议的命名方法PL/SQL变量类型实例插入一条记录并显示DECLARERow_idROWID;infoVARCHAR2(40);BEGIN INSERTINTOdeptVALUES(90,'SERVICE','BEIJING') RETURNINGrowid,dname||':'||to_char(deptno)||':'||loc INTOrow_id,info; DBMS_OUTPUT.PUT_LINE('ROWID:'||row_id); DBMS_OUTPUT.PUT_LINE(info);END;PL/SQL变量类型实例插入一条记录并显示DECLAREPL/SQL变量类型实例其中，RETURNING子句用于检索INSERT语句中所影响的数据行数，当INSERT语句使用VALUES子句插入数据时，RETURNING子句还可将列表达式、ROWID和REF值返回到输出变量中。在使用RETURNING子句是应注意以下几点限制：不能并行DML语句；不能检索LONG类型信息；当通过视图向基表中插入数据时，只能与单基表视图一起使用。PL/SQL变量类型实例其中，RETURNING子句用于检PL/SQL变量类型实例修改一条记录并显示DECLARERow_idROWID;infoVARCHAR2(40);BEGIN UPDATEdeptSETdeptno=80WHEREDNAME='SERVICE' RETURNINGrowid,dname||':'||to_char(deptno)||':'||loc INTOrow_id,info; DBMS_OUTPUT.PUT_LINE('ROWID:'||row_id); DBMS_OUTPUT.PUT_LINE(info);END;PL/SQL变量类型实例修改一条记录并显示DECLAREPL/SQL变量类型实例其中，RETURNING子句用于检索被修改行信息，当UPDATE语句修改单行数据时，RETURNING子句可以检索被修改行的ROWID值，以及行中被修改列的列表达式，并可将他们存储到PL/SQL变量或复合变量中；当UPDATE语句修改多行数据时，RETURNING子句可以将被修改行的ROWID值，以及列表达式值返回到复合变量数组中。在UPDATE中使用RETURNING子句的限制与INSERT语句中对RETURNING子句的限制相同。PL/SQL变量类型实例其中，RETURNING子句用于检PL/SQL变量类型实例删除一条记录并显示DECLARERow_idROWID;infoVARCHAR2(40);BEGIN DELETEdeptWHEREDNAME='SERVICE' RETURNINGrowid,dname||':'||to_char(deptno)||':'||loc INTOrow_id,info; DBMS_OUTPUT.PUT_LINE('ROWID:'||row_id); DBMS_OUTPUT.PUT_LINE(info);END;PL/SQL变量类型实例删除一条记录并显示DECLAREPL/SQL变量类型实例RETURNING子句用于检索被删除行信息，当DELETE语句修改单行数据时，RETURNING子句可以检索被删除行的ROWID，以及被删除行中列的列表达式，并可将他们存储到PL/SQL变量或复合变量中；当UPDATE语句修改多行数据时，RETURNING子句可以将被修改行的ROWID，以及列表达式值返回到复合变量数组中。在UPDATE中使用RETURNING子句的限制与INSERT语句中对RETURNING子句的限制相同。PL/SQL变量类型实例RETURNING子句用于检索被删复合类型ORACLE在PL/SQL中除了提供象前面介绍的各种类型外，还提供一种称为复合类型的类型---记录和表。记录类型使用%TYPE使用%ROWTYPE复合类型ORACLE在PL/SQL中除了提供象前面介绍记录类型记录类型是把逻辑相关的数据作为一个单元存储起来，它必须包括至少一个标量型（数字型、字符型、布尔型、日期型）或RECORD数据类型的成员，称作PL/SQLRECORD的域(FIELD)，其作用是存放互不相同但逻辑相关的信息。记录类型记录类型是把逻辑相关的数据作为一个单元存储起来，它必定义记录类型语法如下:TYPErecord_typeISRECORD(Field1type1[NOTNULL][:=exp1],Field2type2[NOTNULL][:=exp2],......Fieldntypen[NOTNULL][:=expn]);定义记录类型语法如下:TYPErecord_typeIS实例DECLARETYPEtest_recISRECORD(CodeVARCHAR2(10),NameVARCHAR2(30)NOTNULL:='abook');V_booktest_rec;BEGINV_book.code:='123';V_:='C++Programming';DBMS_OUTPUT.PUT_LINE(v_book.code||v_);END;实例DECLARE使用%TYPE定义一个变量，其数据类型与已经定义的某个数据变量的类型相同，或者与数据库表的某个列的数据类型相同，这时可以使用%TYPE。使用%TYPE特性的优点在于：所引用的数据库列的数据类型可以不必知道；所引用的数据库列的数据类型可以实时改变。使用%TYPE定义一个变量，其数据类型与已经定义的某个数据变实例1DECLARE--用%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;实例1DECLARE实例2DECLAREv_empnoemp.empno%TYPE:=&no;Typer_recordisrecord(v_nameemp.ename%TYPE,v_salemp.sal%TYPE,v_dateemp.hiredate%TYPE);Recr_record;BEGINSELECTename,sal,hiredateINTORecFROMempWHEREempno=v_empno;DBMS_OUTPUT.PUT_LINE(Rec.v_name||'---'||Rec.v_sal||'--'||Rec.v_date);END;实例2DECLARE使用%ROWTYPEPL/SQL提供%ROWTYPE操作符,返回一个记录类型，其数据类型和数据库表的数据结构相一致。使用%ROWTYPE特性的优点在于：所引用的数据库中列的个数和数据类型可以不必知道；所引用的数据库中列的个数和数据类型可以实时改变。使用%ROWTYPEPL/SQL提供%ROWTYPE操作符实例DECLAREv_empnoemp.empno%TYPE:=&no;recemp%ROWTYPE;BEGINSELECT*INTOrecFROMempWHEREempno=v_empno;DBMS_OUTPUT.PUT_LINE('姓名:'||rec.ename||'工资:'||rec.sal||'工作时间:'||rec.hiredate);END;实例DECLARE运算符和表达式算术运算符运算符和表达式算术运算符运算符和表达式关系运算符运算符和表达式关系运算符运算符和表达式逻辑运算符运算符和表达式逻辑运算符变量赋值在PL/SQL编程中，变量赋值是一个值得注意的地方，它的语法如下：

variable是一个PL/SQL变量,expression是一个PL/SQL表达式。variable:=expression;变量赋值在PL/SQL编程中，变量赋值是一个值得注意的地方，字符及数字运算特点空值加数字仍是空值：NULL+<数字>=NULL空值加（连接）字符，结果为字符：NULL||<字符串>=<字符串>字符及数字运算特点空值加数字仍是空值：NULL+<数BOOLEAN赋值布尔值只有TRUE,FALSE及NULL三个值。如：DECLAREdoneBOOLEAN;/*thefollowingstatementsarelegal:*/BEGINdone:=FALSE;WHILENOTdoneLOOPNull;ENDLOOP;END;BOOLEAN赋值布尔值只有TRUE,FALSE及N游标赋值游标赋值是通过SELECT语句来完成的，每次执行SELECT语句就赋值一次，一般要求被赋值的变量与SELECT中的列名要一一对应。游标赋值游标赋值是通过SELECT语句来完成的，每次执行游标赋值DECLAREemp_idemp.empno%TYPE:=7788;emp_nameemp.ename%TYPE;wagesemp.sal%TYPE;BEGINSELECTename,NVL(sal,0)+NVL(comm,0)INTOemp_name,wagesFROMempWHEREempno=emp_id;DBMS_OUTPUT.PUT_LINE(emp_name||’----‘||to_char(wages));END;结果：SCOTT----3000游标赋值DECLARE可转换的类型赋值CHAR转换为NUMBER：使用TO_NUMBER函数来完成字符到数字的转换，如：v_total:=TO_NUMBER(‘100.0’)+sal;NUMBER转换为CHAR：使用TO_CHAR函数可以实现数字到字符的转换，如：v_comm:=TO_CHAR(‘123.45’)||’元’;字符转换为日期：使用TO_DATE函数可以实现字符到日期的转换，如：v_date:=TO_DATE('2001.07.03','yyyy.mm.dd');日期转换为字符：使用TO_CHAR函数可以实现日期到字符的转换，如：v_to_day:=TO_CHAR(SYSDATE,'yyyy.mm.ddhh24:mi:ss');可转换的类型赋值CHAR转换为NUMBER：变量作用范围及可见性PL/SQL的变量作用范围特点是：变量的作用范围是在你所引用的程序单元（块、子程序、包）内。即从声明变量开始到该块的结束。一个变量（标识）只能在你所引用的块内是可见的。当一个变量超出了作用范围，PL/SQL引擎就释放用来存放该变量的空间（因为它可能不用了）。在子块中重新定义该变量后，它的作用仅在该块内。变量作用范围及可见性PL/SQL的变量作用范围特点是：实例DECLAREEmesschar(80);BEGINDECLAREV1NUMBER(4);BEGINSELECTempnoINTOv1FROMempWHERELOWER(job)=’president’; DBMS_OUTPUT.PUT_LINE(V1);EXCEPTIONWhenTOO_MANY_ROWSTHENDBMS_OUTPUT.PUT_LINE(‘Morethanonepresident’);END;实例DECLARE实例续…DECLAREV1NUMBER(4);BEGINSELECTempnoINTOv1FROMempWHERELOWER(job)=’manager’;EXCEPTIONWhenTOO_MANY_ROWSTHENDBMS_OUTPUT.PUT_LINE(‘Morethanonemanager’);END;EXCEPTIONWhenothersTHENEmess:=substr(SQLERRM,1,80);DBMS_OUTPUT.PUT_LINE(emess);END;实例续…DECLARE注释在PL/SQL里，可以使用两种符号来写注释，即：使用双‘-‘(减号)加注释

V_SalNUMBER(12,2);--工资变量。使用/**/来加一行或多行注释

/***********************************************//*文件名：department_salary.sql*//***********************************************/注释在PL/SQL里，可以使用两种符号来写注释，即：示例简单数据插入例子

DECLAREv_enameVARCHAR2(20):=‘Bill’;v_salNUMBER(7,2):=1234.56;v_deptnoNUMBER(2):=10;v_empnoNUMBER(4):=8888;BEGININSERTINTOemp(empno,ename,JOB,sal,deptno,hiredate)VALUES(v_empno,v_ename,‘Manager’,v_sal,v_deptno,TO_DATE(’1954.06.09’,’yyyy.mm.dd’));COMMIT;END;示例简单数据插入例子DECLARE示例简单数据删除例子

DECLAREv_empnonumber(4):=8888;BEGINDELETEFROMempWHEREempno=v_empno;COMMIT;END;示例简单数据删除例子DECLAREPL/SQL流程控制语句PL/SQL的流程控制语句包括如下三类：控制语句：IF语句；循环语句：LOOP语句，EXIT语句；顺序语句：GOTO语句，NULL语句。PL/SQL流程控制语句PL/SQL的流程控制语句包括如下条件语句IF<布尔表达式>THENPL/SQL和SQL语句ENDIF;IF<布尔表达式>THENPL/SQL和SQL语句ELSE其它语句ENDIF;条件语句IF<布尔表达式>THENIF<布尔表达式>条件语句IF<布尔表达式>THENPL/SQL和SQL语句ELSIF<其它布尔表达式>THEN

其它语句ELSIF<其它布尔表达式>THEN

其它语句ELSE

其它语句ENDIF;条件语句IF<布尔表达式>THEN条件语句实例DECLAREv_empnoemp.empno%TYPE:=&empno;V_salaryemp.sal%TYPE;V_commentVARCHAR2(35);BEGINSELECTsalINTOv_salaryFROMempWHEREempno=v_empno;IFv_salary<1500THENV_comment:='Fairlyless';ELSIFv_salary<3000THENV_comment:='Alittlemore';ELSEV_comment:='Lotsofsalary';ENDIF;DBMS_OUTPUT.PUT_LINE(V_comment);END;条件语句实例DECLARECASE表达式CASEselector WHENexpression1THENresult1 WHENexpression2THENresult2 WHENexpressionNTHENresultN [ELSEresultN+1]END;CASE表达式CASEselectorCASE表达式实例DECLARE V_gradechar(1):=UPPER('&p_grade'); V_appraisalVARCHAR2(20);BEGIN V_appraisal:= CASEv_grade WHEN'A'THEN'Excellent' WHEN'B'THEN'VeryGood' WHEN'C'THEN'Good' ELSE'Nosuchgrade' END; DBMS_OUTPUT.PUT_LINE('Grade:'||v_grade||'Appraisal:'||v_appraisal);END;CASE表达式实例DECLARECASE表达式实例代码运行结果：Entervalueforp_grade:Aold2:V_gradechar(1):=UPPER('&p_grade');new2:V_gradechar(1):=UPPER('A');Grade:AAppraisal:ExcellentCASE表达式实例代码运行结果：循环简单循环

LOOP

要执行的语句;EXITWHEN<条件语句>/*条件满足，退出循环语句*/ENDLOOP;循环简单循环LOOPLOOP循环实例DECLAREintNUMBER(2):=0;BEGINLOOPint:=int+1;DBMS_OUTPUT.PUT_LINE('int的当前值为:'||int);EXITWHENint=10;ENDLOOP;END;LOOP循环实例DECLARE循环WHILE循环

WHILE<布尔表达式>LOOP要执行的语句;ENDLOOP;循环WHILE循环WHILE<布尔表达式>LOOPWHILE循环实例DECLARExNUMBER:=1;BEGINWHILEx<=10LOOPDBMS_OUTPUT.PUT_LINE('X的当前值为:'||x); x:=x+1;ENDLOOP;END;WHILE循环实例DECLARE循环数字式循环每循环一次，循环变量自动加1，使用关键字REVERSE，循环变量自动减1。跟在INREVERSE后面的数字必须是从小到大的顺序，而且必须是整数，不能是变量或表达式。可以使用EXIT退出循环。FOR循环计数器IN[REVERSE]下限..上限LOOP要执行的语句;ENDLOOP;循环数字式循环FOR循环计数器IN[REVERSE数字式循环实例BEGINFORintin1..10LOOPDBMS_OUTPUT.PUT_LINE('int的当前值为:'||int);ENDLOOP;END;数字式循环实例BEGIN数字式循环实例CREATETABLEtemp_table(num_colNUMBER);DECLAREV_counterNUMBER:=10;BEGININSERTINTOtemp_table(num_col)VALUES(v_counter);FORv_counterIN20..25LOOPINSERTINTOtemp_table(num_col)VALUES(v_counter);ENDLOOP;INSERTINTOtemp_table(num_col)VALUES(v_counter);FORv_counterINREVERSE20..25LOOPINSERTINTOtemp_table(num_col)VALUES(v_counter);ENDLOOP;END;DROPTABLEtemp_table;数字式循环实例CREATETABLEtemp_tablGOTO和标签<<label>>

标号是用<<

>>括起来的标识符GOTOlabel;GOTO和标签GOTOlabel;GOTO和标签实例DECLAREV_counterNUMBER:=1;BEGINLOOPDBMS_OUTPUT.PUT_LINE('V_counter的当前值为:'||V_counter);V_counter:=v_counter+1;IFv_counter>10THENGOTOl_ENDofLOOP;ENDIF;ENDLOOP;<<l_ENDofLOOP>>DBMS_OUTPUT.PUT_LINE('V_counter的当前值为:'||V_counter);END;GOTO和标签实例DECLARENULL语句DECLARE...BEGIN…

IFv_numISNULLTHEN

GOTOprint1;

ENDIF;

…

<<print1>>

NULL;--不需要处理任何数据。END;NULL语句DECLARE游标概念为了处理SQL语句获取一个表中记录，ORACLE提供一个上下文，它提供了一个指向语句的指针以及查询的活动集(activeset)。游标是一个指向上下文的句柄(handle)或指针。通过游标，PL/SQL可以控制上下文区和处理语句时上下文区会发生些什么事情。游标概念为了处理SQL语句获取一个表中记录，ORACLE游标概念对于不同的SQL语句，游标的使用情况不同：游标概念对于不同的SQL语句，游标的使用情况不同：处理显式游标显式游标处理需四个PL/SQL步骤：定义游标 格式：CURSORcursor_nameISselect_statement;打开游标格式：OPENcursor_name;提取游标数据格式：FETCHcursor_nameINTO{variable_list|record_variable};关闭游标格式：CLOSEcursor_name;处理显式游标显式游标处理需四个PL/SQL步骤：示例1

DECLARECURSORc_cursorISSELECTename,salFROMempWHERErownum<11;v_enameemp.ename%TYPE;v_salemp.sal%TYPE;BEGINOPENc_cursor;FETCHc_cursorINTOv_ename,v_sal;WHILEc_cursor%FOUNDLOOPDBMS_OUTPUT.PUT_LINE(v_ename||'---'|to_char(v_sal));FETCHc_cursorINTOv_ename,v_sal;ENDLOOP;CLOSEc_cursor;END;示例1DECLARE示例2

DECLARE DeptRecdept%ROWTYPE; Dept_namedept.dname%TYPE; Dept_locdept.loc%TYPE; CURSORc1ISSELECTdname,locFROMdeptWHEREdeptno<=30; CURSORc2(dept_noNUMBERDEFAULT10)IS SELECTdname,locFROMdeptWHEREdeptno<=dept_no; CURSORc3(dept_noNUMBERDEFAULT10)IS SELECT*FROMdeptWHEREdeptno<=dept_no;BEGIN OPENc1; LOOP FETCHc1INTOdept_name,dept_loc; EXITWHENc1%NOTFOUND; DBMS_OUTPUT.PUT_LINE(dept_name||'---'|dept_loc); ENDLOOP;示例2DECLARE示例2

续… CLOSEc1; OPENc2; LOOP FETCHc2INTOdept_name,dept_loc; EXITWHENc2%NOTFOUND; DBMS_OUTPUT.PUT_LINE(dept_name||'---'|dept_loc); ENDLOOP; CLOSEc2; OPENc3(dept_no=>20); LOOP FETCHc3INTOdeptrec; EXITWHENc3%NOTFOUND; DBMS_OUTPUT.PUT_LINE(deptrec.deptno||'---'|deptrec.dname||'---'|deptrec.loc); ENDLOOP; CLOSEc3;END;示例2续… CLOSEc1;游标属性%FOUND布尔型属性，当最近一次读记录时成功返回,则值为TRUE；

%NOTFOUND布尔型属性，与%FOUND相反；

%ISOPEN布尔型属性，当游标已打开时返回TRUE；

%ROWCOUNT数字型属性，返回已从游标中读取的记录数。游标属性%FOUND布尔型属性，当最近一次示例DECLAREv_empnoemp.empno%TYPE;v_salemp.sal%TYPE;CURSORc_cursorISSELECTempno,salFROMemp;BEGINOPENc_cursor;LOOPFETCHc_cursorINTOv_empno,v_sal;EXITWHENc_cursor%NOTFOUND;IFv_sal<=1200THENUPDATEempSETsal=sal+50WHEREempno=v_empno;DBMS_OUTPUT.PUT_LINE('编码为'||v_empno||'工资已更新!');ENDIF;DBMS_OUTPUT.PUT_LINE('记录数:'||c_cursor%ROWCOUNT);ENDLOOP;CLOSEc_cursor;END;示例DECLARE游标的FOR循环

FORindex_variableINcursor_name[value[,value]…]LOOP --游标数据处理代码

ENDLOOP;游标的FOR循环 FORindex_variableI游标中使用FOR循环示例DECLARECURSORc_salISSELECTempno,ename,salFROMemp;BEGIN--隐含打开游标

FORv_salINc_salLOOP--隐含执行一个FETCH语句

DBMS_OUTPUT.PUT_LINE(to_char(v_sal.empno)||'---'||v_sal.ename||'---'||to_char(v_sal.sal));--隐含监测c_sal%NOTFOUNDENDLOOP;--隐含关闭游标END;游标中使用FOR循环示例DECLARE有参数游标中使用FOR循环示例DECLARE CURSORc_cursor(dept_noNUMBERDEFAULT10)IS SELECTdname,locFROMdeptWHEREdeptno<=dept_no;BEGIN DBMS_OUTPUT.PUT_LINE('dept_no参数值为30：'); FORc1_recINc_cursor(30)LOOP--c_cursor(dept_n=>30) DBMS_OUTPUT.PUT_LINE(c1_rec.dname||'---'||c1_rec.loc); ENDLOOP; DBMS_OUTPUT.PUT_LINE(CHR(10)||'使用默认的dept_no参数值10：'); FORc1_recINc_cursorLOOP DBMS_OUTPUT.PUT_LINE(c1_rec.dname||'---'||c1_rec.loc); ENDLOOP;END;有参数游标中使用FOR循环示例DECLARE游标FOR循环语句中使用子查询来实现游标实例BEGIN FORc1_recIN(SELECTdname,locFROMdept)LOOP DBMS_OUTPUT.PUT_LINE(c1_rec.dname||'---'||c1_rec.loc); ENDLOOP;END;游标FOR循环语句中使用子查询来实现游标实例BEGIN处理隐式游标当查询返回结果超过一行时，就需要一个显式游标。显式游标主要是用于对查询语句的处理，尤其是在查询结果为多条记录的情况下；而对于非查询语句，如修改、删除操作，则由ORACLE系统自动地为这些操作设置游标并创建其工作区，这些由系统隐含创建的游标称为隐式游标，隐式游标的名字为SQL，这是由ORACLE系统定义的。对于隐式游标的操作，如定义、打开、取值及关闭操作，都由ORACLE系统自动地完成，无需用户进行处理。用户只能通过隐式游标的相关属性，来完成相应的操作。在隐式游标的工作区中，所存放的数据是与用户自定义的显示游标无关的、最新处理的一条SQL语句所包含的数据。处理隐式游标当查询返回结果超过一行时，就需要一个显式游标。显处理隐式游标

格式调用为：SQL%当执行一条DML语句后，DML语句的结果保存在四个游标属性中，这些属性用于控制程序流程或者了解程序的状态。当运行DML语句时，PL/SQL打开一个内建游标并处理结果，游标是维护查询结果的内存中的一个区域，游标在运行DML语句时打开，完成后关闭。处理隐式游标 格式调用为：SQL%处理隐式游标隐式游标只使用SQL%FOUND、SQL%NOTFOUND、SQL%ROWCOUNT三个属性，SQL%FOUND，SQL%NOTFOUND是布尔值，SQL%ROWCOUNT是整数值。处理隐式游标隐式游标只使用SQL%FOUND、SQL%NOT处理隐式游标 SQL%FOUND，布尔型属性，当最近一次读记录时成功返回，则值为TRUE。在执行任何DML语句前SQL%FOUND和SQL%NOTFOUND的值都是NULL，在执行DML语句后，SQL%FOUND的属性值将是：INSERT为TRUE；DELETE和UPDATE，至少有一行被DELETE或UPDATE为TRUE；SELECTINTO至少返回一行为TRUE；SQL%FOUND为TRUE时,SQL%NOTFOUND为FALSE。处理隐式游标 SQL%FOUND，布尔型属性，当最近一次读记处理隐式游标示例DECLAREV_deptnoemp.deptno%TYPE:=&p_deptno;BEGIN DELETEFROMempWHEREdeptno=v_deptno; IFSQL%NOTFOUNDTHEN DELETEFROMdeptWHEREdeptno=v_deptno; ENDIF;END;处理隐式游标示例DECLARE异常错误处理异常情况处理是用来处理正常执行过程中未预料的事件，程序块的异常处理预定义的错误和自定义错误，由于PL/SQL程序块一旦产生异常而没有指出如何处理时，程序就会自动终止整个程序运行。异常错误处理异常情况处理是用来处理正常执行过程中未预料的事件有三种类型的异常：预定义(Predefined)异常ORACLE预定义的异常情况大约有24个。对这种异常情况的处理，无需在程序中定义，由ORACLE自动将其引发。非预定义(Predefined)异常即其他标准的ORACLE错误。对这种异常情况的处理，需要用户在程序中定义，然后由ORACLE自动将其引发。用户定义(User_define)异常程序执行过程中，出现编程人员认为的非正常情况。对这种异常情况的处理，需要用户在程序中定义，然后显式地在程序中将其引发。有三种类型的异常：预定义(Predefined)异常异常处理部分结构EXCEPTIONWHENfirst_exceptionTHEN<codetohandlefirstexception>WHENsecond_exceptionTHEN<codetohandlesecondexception>WHENOTHERSTHEN<codetohandleothersexception>END;异常处理部分结构EXCEPTION预定义的异常处理预定义的异常处理实例DECLAREv_empnoemp.empno%TYPE:=&empno;v_salemp.sal%TYPE;BEGINSELECTsalINTOv_salFROMempWHEREempno=v_empno;IFv_sal<=1500THENUPDATEempSETsal=sal+100WHEREempno=v_empno;DBMS_OUTPUT.PUT_LINE('编码为'||v_empno||'员工工资已更新!');ELSEDBMS_OUTPUT.PUT_LINE('编码为'||v_empno||'员工工资已经超过规定值!');ENDIF;EXCEPTIONWHENNO_DATA_FOUNDTHENDBMS_OUTPUT.PUT_LINE('数据库中没有编码为'||v_empno||'的员工');WHENTOO_MANY_ROWSTHENDBMS_OUTPUT.PUT_LINE('程序运行错误!请使用游标');WHENOTHERSTHENDBMS_OUTPUT.PUT_LINE(SQLCODE||’---‘||SQLERRM);END;实例DECLARE非预定义的异常处理非定义的ORACLE错误进行定义，步骤如下：1. 在PL/SQL块的定义部分定义异常情况：<异常情况>EXCEPTION;2. 将其定义好的异常情况，与标准的ORACLE错误联系起来，使用EXCEPTION_INIT语句：PRAGMAEXCEPTION_INIT(<异常情况>,<错误代码>)；3. 在PL/SQL块的异常情况处理部分对异常情况做出相应的处理。非预定义的异常处理非定义的ORACLE错误进行定义，步骤如下示例INSERTINTOdeptVALUES(50,‘FINANCE’,‘CHICAGO’);DECLAREv_deptnodept.deptno%TYPE:=&deptno;deptno_remainingEXCEPTION;PRAGMAEXCEPTION_INIT(deptno_remaining,-2292);/*-2292是违反一致性约束的错误代码*/BEGINDELETEFROMdeptWHEREdeptno=v_deptno;EXCEPTIONWHENdeptno_remainingTHENDBMS_OUTPUT.PUT_LINE('违反数据完整性约束!');WHENOTHERSTHENDBMS_OUTPUT.PUT_LINE(SQLCODE||’---‘||SQLERRM);END;示例INSERTINTOdeptVALUES(50,存储函数和过程ORACLE提供可以把PL/SQL程序存储在数据库中，并可以在任何地方来运行它。这样就叫存储过程或函数。过程和函数统称为PL/SQL子程序，他们是被命名的PL/SQL块，均存储在数据库中，并通过输入、输出参数或输入/输出参数与其调用者交换信息。过程和函数的唯一区别是函数总向调用者返回数据，而过程则不返回数据。在本节中主要介绍：创建存储过程和函数；建立和管理存储过程和函数。存储函数和过程ORACLE提供可以把PL/SQL程序存储创建函数语法如下：CREATE[orREPLACE]FUNCTIONfunction_name[(argment[{in|inout}]TYPE,argment[{in|out|inout}]type]RETURNRETURN_type{IS|AS}BEGINFUNCTION_bodyEXCEPTION......ENDfunction_name;创建函数语法如下：CREATE[orREPLACE]创建函数示例CREATEORREPLACEFUNCTIONget_salary( Dept_noNUMBER, Emp_countOUTNUMBER) RETURNNUMBERIS V_sumNUMBER;BEGIN SELECTSUM(sal),count(*)INTOV_sum,emp_count FROMempWHEREdeptno=dept_no; RETURNv_sum;EXCEPTIONWHENNO_DATA_FOUNDTHENDBMS_OUTPUT.PUT_LINE('你需要的数据不存在!');WHENOTHERSTHENDBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);ENDget_salary;创建函数示例CREATEORREPLACEFUNC函数的调用格式为：

argument_value1[,argument_value2…]函数的调用格式为：函数的调用实例DECLARE V_numNUMBER; V_sumNUMBER;BEGIN V_sum:=get_salary(30,v_num); DBMS_OUTPUT.PUT_LINE('30号部门工资总和：'||v_sum||'，人数：'||v_num);END;运行结果：30号部门工资总和：9400，人数：6函数的调用实例DECLARE创建过程创建过程语法：CREATE[ORREPLACE]PROCEDUREProcedure_name[(argment[{IN|OUT|INOUT}]Type,argment[{IN|OUT|INOUT}]Type] [AUTHIDDEFINER|CURRENT_USER]{IS|AS}<类型.变量的说明>BEGIN<执行部分>EXCEPTION<可选的异常错误处理程序>END;创建过程创建过程语法：CREATE[ORREPLACE实例1CREATEtablelogtable(useridVARCHAR2(10),logdatedate);CREATEORREPLACEPROCEDURElogexecutionISBEGININSERTINTOlogtable(userid,logdate)VALUES(USER,SYSDATE);END;实例1CREATEtablelogtable(use实例2CREATEORREPLACEPROCEDUREDelEmp(v_empnoINemp.empno%TYPE)ASNo_resultEXCEPTION;BEGINDELETEFROMempWHEREempno=v_empno;IFSQL%NOTFOUNDTHENRAISEno_result;ENDIF;DBMS_OUTPUT.PUT_LINE('编码为'||v_empno||'的员工已被除名!');EXCEPTIONWHENno_resultTHENDBMS_OUTPUT.PUT_LINE('你需要的数据不存在!');WHENOTHERSTHENDBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);ENDDelEmp;实例2CREATEORREPLACEPROCEDUR实例3CREATEORREPLACEPROCEDUREInsertEmp(v_empnoinemp.empno%TYPE,v_nameinemp.ename%TYPE,v_deptnoinemp.deptno%TYPE)ASempno_remainingEXCEPTION;PRAGMAEXCEPTION_INIT(empno_remaining,-1);/*-1是违反唯一约束条件的错误代码*/BEGININSERTINTOemp(empno,ename,hiredate,deptno) VALUES(v_empno,v_name,sysdate,v_deptno);DBMS_OUTPUT.PUT_LINE('插入数据记录成功!');EXCEPTIONWHENempno_remainingTHENDBMS_OUTPUT.PUT_LINE('违反数据完整性约束!');WHENOTHERSTHENDBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);ENDInsertEmp;实例3CREATEORREPLACEPROCEDUR调用存储过程ORACLE使用EXECUTE语句来实现对存储过程的调用：EXEC[UTE]Procedure_name(parameter1,parameter2…);调用存储过程ORACLE使用EXECUTE语句来实现对存存储过程和调用实例1CREATEORREPLACEPROCEDUREQueryEmp(v_empnoINemp.empno%TYPE, v_enameOUTemp.ename%TYPE, v_salOUTemp.sal%TYPE)ASBEGIN SELECTename,salINTOv_ename,v_salFROMempWHEREempno=v_empno; DBMS_OUTPUT.PUT_LINE('编码为'||v_empno||'的员工已经查到!');EXCEPTION WHENNO_DATA_FOUNDTHENDBMS_OUTPUT.PUT_LINE('你需要的数据不存在!'); WHENOTHERSTHENDBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);ENDQueryEmp;存储过程和调用实例1CREATEORREPLACE实例2DECLAREv1emp.ename%TYPE;v2emp.sal%TYPE;BEGINQueryEmp(7788,v1,v2);DBMS_OUTPUT.PUT_LINE('姓名:'||v1);DBMS_OUTPUT.PUT_LINE('工资:'||v2);QueryEmp(7902,v1,v2);DBMS_OUTPUT.PUT_LINE('姓名:'||v1);DBMS_OUTPUT.PUT_LINE('工资:'||v2);QueryEmp(8899,v1,v2);DBMS_OUTPUT.PUT_LINE('姓名:'||v1);DBMS_OUTPUT.PUT_LINE('工资:'||v2);END;运行结果：编码为7788的员工已经查到!姓名:SCOTT工资:3000编码为7902的员工已经查到!姓名:FORD工资:3000你需要的数据不存在!姓名:工资:实例2DECLARE运行结果：实例3CREATEORREPLACEPROCEDUREproc_demo(Dept_noNUMBERDEFAULT10, Sal_sumOUTNUMBER, Emp_countOUTNUMBER)ISBEGIN SELECTSUM(sal),COUNT(*)INTOsal_sum,emp_countFROMempWHEREdeptno=dept_no;EXCEPTIONWHENNO_DATA_FOUNDTHENDBMS_OUTPUT.PUT_LINE('你需要的数据不存在!');WHENOTHERSTHENDBMS_OUTPUT.PUT_LINE(SQLCODE||’---‘||SQLERRM);ENDproc_demo;实例3CREATEORREPLACEPROCEDURE实例4DECLAREV_numNUMBER;V_sumNUMBER(8,2);BEGIN Proc_demo(30,v_sum,v_num);DBMS_OUTPUT.PUT_LINE('30号部门工资总和：'||v_sum||',人数：'||v_num); Proc_demo(sal_sum=>v_sum,emp_count=>v_num);DBMS_OUTPUT.PUT_LINE('10号部门工资总和：'||v_sum||',人数：'||v_num);END;运行结果：30号部门工资总和：9400,人数：610号部门工资总和：8750,人数：3实例4DECLARE运行结果：实例5DECLAREV_numNUMBER;V_sumNUMBER(8,2);PROCEDUREproc_demo (Dept_noNUMBERDEFAULT10, Sal_sumOUTNUMBER, Emp_countOUTNUMBER)ISBEGIN SELECTSUM(sal),COUNT(*)INTOsal_sum,emp_countFROMempWHEREdeptno=dept_no;EXCEPTION实例5DECLARE实例5续…WHENNO_DATA_FOUNDTHENDBMS_OUTPUT.PUT_LINE('你需要的数据不存在!');WHENOTHERSTHEN DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);ENDproc_demo;BEGIN Proc_demo(30,v_sum,v_num);DBMS_OUTPUT.PUT_LINE('30号部门工资总和：'||v_sum||'，人数：'||v_num); Proc_demo(sal_sum=>v_sum,emp_count=>v_num);DBMS_OUTPUT.PUT_LINE('10号部门工资总和：'||v_sum||'，人数：'||v_num);END;运行结果：30号部门工资总和：9400,人数：610号部门工资总和：8750,人数：3实例5续…WHENNO_DATA_FOUNDTHEN运删除过程和函数删除过程

:语法如下：DROPPROCEDURE[user.]Procudure_name;删除函数:语法如下：DROPFUNCTION[user.]Function_name;删除过程和函数删除过程:包的创建和应用一个包由两个分开的部分组成：包定义（PACKAGE）：包定义部分声明包内数据类型、变量、常量、游标、子程序和异常错误处理等元素，这些元素为包的公有元素。包主体（PACKAGEBODY）：包主体则是包定义部分的具体实现，它定义了包定义部分所声明的游标和子程序，在包主体中还可以声明包的私有元素。包定义和包主体分开编译，并作为两部分分开的对象存放在数据库字典中，详见数据字典user_source,all_source,dba_source.包的创建和应用一个包由两个分开的部分组成：包的定义创建包定义:CREATE[ORREPLACE]PACKAGEpackage_name[AUTHID{CURRENT_USER|DEFINER}]{IS|AS}[公有数据类型定义[公有数据类型定义]…][公有游标声明[公有游标声明]…][公有变量、常量声明[公有变量、常量声明]…][公有子程序声明[公有子程序声明]…]END[package_name];包的定义创建包定义:CREATE[ORREPLACE]包的定义创建包主体

:CREATE[ORREPLACE]PACKAGEBODYpackage_name{IS|AS}[私有数据类型定义[私有数据类型定义]…][私有变量、常量声明[私有变量、常量声明]…][私有子程序声明和定义[私有子程序声明和定义]…][公有游标定义[公有游标定义]…][公有子程序定义[公有子程序定义]…]BEGINPL/SQL语句END[package_name];包的定义创建包主体:CREATE[ORREPLACE]包的开发步骤与开发存储过程类似，包的开发需要几个步骤：将每个存储过程调式正确；用文本编辑软件将各个存储过程和函数集成在一起；按照包的定义要求将集成的文本的前面加上包定义；按照包的定义要求将集成的文本的前面加上包主体；使用SQLPLUS或开发工具进行调式。包的开发步骤与开发存储过程类似，包的开发需要几个步骤：创建包实例CREATEORREPLACEPACKAGEdemo_packIS DeptRecdept%ROWTYPE; FUNCTIONadd_dept(dept_noNUMBER,dept_nameVARCHAR2,locationVARCHAR2) RETURNNUMBER; FUNCTIONremove_dept(dept_noNUMBER) RETURNNUMBER;