还剩19页未读，继续阅读
本资源只提供10页预览，全部文档请下载后查看！喜欢就下载吧，查找使用更方便
文本内容:
游标和异常处理游标的概念游标是的一个内存工作区，由系统或用户以变量的形式定义SQL游标的作用就是用于临时存储从数据库中提取的数据块在某些情况下，需要把数据从存放在磁盘的表中调到计算机内存中进行处理，最后将处理结果显示出来或最终写回数据库这样数据处理的速度才会提高，否则频繁的磁盘数据交换会降低效率游标有两种类型显式游标和隐式游标在前述程序中用到的…查询语句，一次只能从数据库中提取一行数据，对于这种SELECT…INTO形式的查询和操作，系统都会使用一个隐式游标但是如果要提取多行数据，就要由程序DML员定义一个显式游标，并通过与游标有关的语句进行处理显式游标对应一个返回结果为多行多列的语句SELECT游标一旦打开，数据就从数据库中传送到游标变量中，然后应用程序再从游标变量中分解出需要的数据，并进行处理隐式游标如前所述，操作和单行语句会使用隐式游标，它们是DML SELECT*插入操作INSERT*更新操作UPDATEo*删除操作DELETEo*单行查询操作SELECT...INTO...o当系统使用一个隐式游标时，可以通过隐式游标的属性来了解操作的状态和结果，进而控制程序的流程隐式游标可以使用名字来访问，但要注意，通过游标名总是只能访问前一SQL SQL个操作或单行操作的游标属性所以通常在刚刚执行完操作之后，立即使用DML SELECTSQL游标名来访问属性游标的属性有四种，如下表所示隐式游标的属性返回值类型意义SQL%ROWCOUNT整型代表DML语句成功执行的数据行数值为TRUE代表插入、删除、更新或单行查询操作成功SQL%FOUND布尔型SQL%NOTFOUND布尔型与SQL%FOUND属性返回值相反布尔型DML执行过程中为真，结束后为假SQL%ISOPEN范例使用隐式游标的属性，判断对雇员工资的修改是否成功SET SERVEROUTPUTONBEGINUPDATE empSET sal=sal+100WHERE empno=1234;错误名EXCEPTION;定义后使用来将一个定义的错误同一个特别的错误代PRAGMA EXCEPTIONJNITOracle码相关联，就可以同系统预定义的错误一样使用了语法如下」错误名，-错误代码；范例定义新的系统错误类型PRAGMA EXCEPTIONNITSET SERVEROUTPUTONDECLAREV_ENAME VARCHAR210;NULL_INSERT_ERROR EXCEPTION;PRAGMA EXCEPTIONJNITNULLJNSERT_ERROR,-1400;BEGININSERT INTOEMPEMPNO VALUESNULL;EXCEPTIONWHEN NULL_INSERT_ERROR THEN无法插入值！匕DBMSJDUTPUT.PUTJJNE NULLWHEN OTHERS THENDBMS_OUTPUT.PUT_LINEC发生其他系统错误！,;END;执行结果为:无法插入值！NULL过程已成功完成PL/SQL说明是自定义异常，同系统错误相关联NULL_INSERT_ERROR1400自定义异常程序设计者可以利用引发异常的机制来进行程序设计，自己定义异常类型可以在声明部分定义新的异常类型，定义的语法是错误名EXCEPTION;用户定义的错误不能由系统来触发，必须由程序显式地触发，触发的语法是错误名；RAISE也可以用来引发模拟系统错误，比如，将引发模拟的除零错误RAISE RAISEZERO_DMDE使用函数也可以引发异常该函数要传递两个参数，第一RAISE_APPLICATION_ERROR个是用户自定义的错误编号，第二个参数是用户自定义的错误信息使用该函数引发的异常的编号应该在和之间选择2000020999自定义异常处理错误的方式同前范例插入新雇员，限定插入雇员的编号在之间7000〜8000SET SERVEROUTPUTONDECLAREnew_no NUMBERIO;new_excpl EXCEPTION;new_excp2EXCEPTION;BEGINnew_no:=6789;（）INSERT INTOemp empno,ename（小关/）；VALUES new_no,IF new_no7000THENRAISE new_excpl;END IF;IFnew_no8000THENRAISE new_excp2;END IF;COMMIT;EXCEPTIONWHEN new_excpl THENROLLBACK;（,雇员编号小于的下限！,）；DBMSJDUTPUT.PUTJJNE7000WHEN new_excp2THENROLLBACK;（,雇员编号超过的上限!,）;DBMSJDUTPUT.PUTJJNE8000END;__执行结果为:雇员编号小于7000的下限！过程已成功完成PL/SQL说明在此例中，自定义了两个异常和分别代表编号小于和编号new_excpl new_excp2,7000大于的错误在程序中通过判断编号大小，产生对应的异常，并在异常处理部分回退插8000入操作，然后显示相应的错误信息范例使用函数引发系统异常RAISE APPLICATIONERRORSET SERVEROUTPUTONDECLARE（）New_noNUMBER10;BEGINNew_no:=6789;（）INSERT INTOemp empno,ename（）VALUES new_no,JAMES;IFnew_no7000THENROLLBACK;RAISE_APPLICATION_ERROR（-20001，编号小于7000的下限！,）;END IF;IF new_no8000THENROLLBACK;（，编号大于的下限！）RAISE_APPLICATION_ERROR-200028000;END IF;END;执行结果为:DECLARE位于第行:ERROR1ORA-20001:编号小于7000的下限！在ORA-06512:line9说明在本训练中，使用弓|发自定义异常，并以系统错误RAISE_APPLICATION_ERROR的方式进行显示错误编号为和2000120002o注意同上一个训练比较，此种方法不需要事先定义异常，可直接引发可以参考下面的程序片断将出错信息记录到表中，其中，为记录错误信息的表，errors为发生异常的错误编号，为发生异常的错误信息SQLCODE SQLERRMDECLAREverror codeNUMBER;v_error_message VARCHAR2255;BEGIN•••EXCEPTION•••WHEN OTHERSTHENv_error_code:=SQLCODE;v_error_message:=SQLERRM;INSERT INTOerrorsVALUESv_error_code,v_error_message;END;练习修改雇员的工资，通过引发异常控制修改范围在之间600〜6000存储过程、函数和包认识存储过程和函数存储过程和函数也是一种块，是存入数据库的块但存储过程和函数不同于已PL/SQL PL/SQL经介绍过的程序，我们通常把程序称为无名块，而存储过程和函数是以命名的PL/SQL PL/SQL方式存储于数据库中的和程序相比，存储过程有很多优点，具体归纳如下•存储过PL/SQL程和函数以命名的数据库对象形式存储于数据库当中存储在数据库中的优点是很明显的，因为代码不保存在本地，用户可以在任何客户机上登录到数据库，并调用或修改代码•存储过程和函数可由数据库提供安全保证，要想使用存储过程和函数，需要有存储过程和函数的所有者的授权，只有被授权的用户或创建者本身才能执行存储过程或调用函数•存储过程和函数的信息是写入数据字典的，所以存储过程可以看作是一个公用模块，用户编写的程序或其他存储过程都可以调用它但存储过程和函数不能调用程PL/SQL PL/SQL序一个重复使用的功能，可以设计成为存储过程，比如显示一张工资统计表，可以设计成为存储过程；一个经常调用的计算，可以设计成为存储函数；根据雇员编号返回雇员的姓名，可以设计成存储函数•像其他高级语言的过程和函数一样，可以传递参数给存储过程或函数，参数的传递也有多种方式存储过程可以有返回值，也可以没有返回值，存储过程的返回值必须通过参数带回；函数有一定的数据类型，像其他的标准函数一样，我们可以通过对函数名的调用返回函数值•存储过程和函数需要进行编译，以排除语法错误，只有编译通过才能调用创建和删除存储过程创建存储过程，需要有或的系统权限该CREATE PROCEDURECREATE ANYPROCEDURE权限可由系统管理员授予创建一个存储过程的基本语句如下［］存储过程名［（参数［］数据类型…）］CREATE OR REPLACE PROCEDUREIN|OUT|IN OUT［说明部分］可执行部分［{AS|IS}BEGIN EXCEPTION错误处理部分］［过程名］;END其中可选关键字表示如果存储过程已经存在，则用新的存储过程覆盖，通常OR REPLACE用于存储过程的重建参数部分用于定义多个参数（如果没有参数，就可以省略）参数有三种形式、IN OUT和如果没有指明参数的形式，则默认为IN OUTIN关键字也可以写成后跟过程的说明部分，可以在此定义过程的局部变量AS IS,编写存储过程可以使用任何文本编辑器或直接在环境下进行，编写好的存储SQL*Plus过程必须要在环境下进行编译，生成编译代码，原代码和编译代码在编译过程中都会SQL*Plus被存入数据库编译成功的存储过程就可以在环境下进行调用了Oracle一个存储过程在不需要时可以删除删除存储过程的人是过程的创建者或者拥有DROP ANY系统权限的人删除存储过程的语法如下PROCEDURE存储过程名；DROP PROCEDURE如果要重新编译一个存储过程，则只能是过程的创建者或者拥有ALTER ANY系统权限的人语法如下PROCEDUREALTER PROCEDURE存储过程名COMPILE；执行（或调用）存储过程的人是过程的创建者或是拥有系统权限的人EXECUTE ANYPROCEDURE或是被拥有者授予权限的人执行的方法如下EXECUTE方法1模式名.存储过程名［（参数…）］；EXECUTE方法2BEGIN模式名.存储过程名［（参数…）］;END;传递的参数必须与定义的参数类型、个数和顺序一致（如果参数定义了默认值，则调用时可以省略参数）参数可以是变量、常量或表达式，用法参见下一节如果是调用本账户下的存储过程，则模式名可以省略要调用其他账户编写的存储过程，则模式名必须要添加以下是一个生成和调用简单存储过程的训练注意要事先授予创建存储过程的权限范例创建一个显示雇员总人数的存储过程步骤1登录SCOTT账户步骤2在SQL*Plus输入区中，输入以下存储过程CREATE ORREPLACE PROCEDUREEMP_COUNTASV_TOTALNUMBER10;BEGINSELECT COUNT*INTO V_TOTAL FROMEMP;,雇员总人数为DBMS_OUTPUT.PUT_LINE||V_TOTAL;END;步骤3/执行是进行编译如果存在错误，就会显示警告创建的过程带有编译错误如果存在错误，对脚本进行修改，直到没有错误产生如果要想查看编译的错误SHOW ERRORSPROCEDURE emp_count;如果编译结果正确，将显示过程已创建步骤4调用存储过程，在输入区中输入以下语句并执行EXECUTE EMP_COUNT;显示结果为:雇员总人数为14过程已成功完成PL/SQL说明在该训练中，变量是存储过程定义的局部变量，用于接收查询到的雇员总人数V_TOTAL注意在中输入存储过程，按〃执行〃按钮是进行编译，不是执行存储过程SQL*Plus如果在存储过程中引用了其他用户的对象，比如表，则必须有其他用户授予的对象访问权限一个存储过程一旦编译成功，就可以由其他用户或程序来引用但存储过程或函数的所有者必须授予其他用户执行该过程的权限存储过程没有参数，在调用时，直接写过程名即可注意用调用存储过程只有两种情况EXECUTE一种是存储过程不带参数一种是存储过程不带输出参数，带输入参数不是变量带入是直接带入范例在程序中调用存储过程PL/SQL步骤1登录SCOTT账户步骤2授权STUDENT账户使用该存储过程，即在SQL*Plus输入区中，输入以下的命令GRANT EXECUTEON EMP_COUNTTO STUDENT授权成功步骤3登录STUDENT账户，在SQL*Plus输入区中输入以下程序SET SERVEROUTPUTONBEGINSCOTT.EMP_COUNT;END;步骤4执行以上程序，结果为:雇员总人数为14过程已成功完成PL/SQL说明在本例中，存储过程是由账户创建的，账户获得账户的授权后,才SCOTT STUDENSCOTT能调用该存储过程注意在程序中调用存储过程，使用了第二种语法范例编写显示雇员信息的存储过程并引用存储过程EMP LIST,EMP COUNT步骤1在SQL*Plus输入区中输入并编译以下存储过程CREATE ORREPLACE PROCEDUREEMP_LIST ASCURSOR emp_cursor ISSELECT empno,ename FROMemp;BEGINFOR Emp_record INemp_cursor LOOPDBMS_OUTPUT.PUT_LINEEmp_record.empno11Emp_record.ename;END LOOP;EMP_COUNT;END;执行结果:过程已创建步骤2调用存储过程，在输入区中输入以下语句并执行EXECUTE EMP_LIST说明以上的存储过程中定义并使用了游标，用来循环显示所有雇员的信息然后调EMPJJST用已经成功编译的存储过程用来附加显示雇员总人数通过命令来执行EMP_COUNT,EXECUTE存储过程EMP_LIST练习编写显示部门信息的存储过程要求统计出部门个数DEPT LIST,参数传递参数的作用是向存储过程传递数据，或从存储过程获得返回结果正确的使用参数可以大大增加存储过程的灵活性和通用性参数的类型有三种，如表8・1所示参数类型说明定义一个输入参数变量，用于传递参数给存储过程INOUT定义一个输出参数变量，用于从存储过程获取数据定义一个输入、输出参数变量，兼有以上两者的功能IN OUT参数的定义形式和作用如下:参数名数据类型值；IN DEFAULT定义一个输入参数变量，用于传递参数给存储过程在调用存储过程时，主程序的实际参数可以是常量、有值变量或表达式等关键字为可选项，用来设定参数的默认值DEFAULT如果在调用存储过程时不指明参数，则参数变量取默认值在存储过程中，输入变量接收主程序传递的值，但不能对其进行赋值参数名数据类型;OUT定义一个输出参数变量，用于从存储过程获取数据，即变量从存储过程中返回值给主程序在调用存储过程时，主程序的实际参数只能是一个变量，而不能是常量或表达式在存储过程中，参数变量只能被赋值而不能将其用于赋值，在存储过程中必须给输出变量至少赋值一次参数名数据类型值；IN OUTDEFAULT定义一个输入、输出参数变量，兼有以上两者的功能在调用存储过程时，主程序的实际参数只能是一个变量，而不能是常量或表达式关键字为可选项，用来设定参数DEFAULT的默认值在存储过程中，变量接收主程序传递的值，同时可以参加赋值运算，也可以对其进行赋值在存储过程中必须给变量至少赋值一次注意如果省略、或则默认模式是IN OUTIN OUT,IN参数的定义只能给出类型，无需定义长度范例编写给雇员增加工资的存储过程通过类型的参数传递要增加工资的CHANGE SALARY,IN雇员编号和增加的工资额步骤1登录SCOTT账户步骤2在SQL*Plus输入区中输入以下存储过程并执行CREATE ORREPLACE PROCEDURECHANGE_SALARYP_EMPNO INNUMBER DEFAULT7788,P_RAISE NUMBERDEFAULT10ASV_ENAME VARCHAR210;V_SALNUMBER5;BEGINSELECT ENAME,SAL INTO V_ENAME,V_SAL FROMEMP WHEREEMPNO=P_EMPNO;UPDATE EMPSET SAL=SAL+P_RAISE WHEREEMPNO=P_EMPNO;雇员的工资被改为DBMS_OUTPUT.PUT_LINE||V_ENAME|||TO_CHARV_SAL+P_RAISE;COMMIT;EXCEPTIONWHEN OTHERSTHEN发生错误，修改失败！’；DBMSJDUTPUT.PUTJJNECROLLBACK;END;执行结果为过程已创建步骤3调用存储过程，在输入区中输入以下语句并执行:EXECUTE CHANGE_SALARY7788,80显示结果为:雇员的工资被改为SCOTT3080说明从执行结果可以看到，雇员的工资已由原来的改为SCOTT30003080参数的值由调用者传递，传递的参数的个数、类型和顺序应该和定义的一致如果顺序不一致，可以采用以下调用方法如上例，执行语句可以改为EXECUTE CHANGE_SALARYP_RAISE=80,P_EMPNO=7788;可以看出传递参数的顺序发生了变化，并且明确指出了参数名和要传递的值，=运算符左侧是参数名，右侧是参数表达式，这种赋值方法的意义较清楚练习创建插入雇员的存储过程并将雇员编号等作为参数INSERT EMP,在设计存储过程的时候，也可以为参数设定默认值，这样调用者就可以不传递或少传递参数了练习调用存储过程不传递参数，使用默认参数值CHANGE SALARY,在输入区中输入以下命令并执行SQL*PlusEXECUTE CHANGE_SALARY显示结果为:雇员的工资被改为SCOTT3090说明在存储过程的调用中没有传递参数，而是采用了默认值和即默认雇员号为778810,7788,增加的工资为10范例使用类型的参数返回存储过程的结果OUT步骤1登录SCOTT账户步骤2在SQL*Plus输入区中输入并编译以下存储过程CREATE ORREPLACE PROCEDUREEMP_COUNTP_TOTAL OUTNUMBERASBEGINSELECT COUNT*INTO P_TOTAL FROMEMP;END;执行结果为:过程已创建步骤3输入以下程序并执行DECLAREV_EMPCOUNT NUMBER;BEGINEMP_COUNTV_EMPCOUNT;,雇员总人数为DBMSJDUTPUT.PUTJJNE||V_EMPCOUNT;END;显示结果为:雇员总人数为14过程已成功完成PL/SQL说明在存储过程中定义了类型的参数在主程序调用该存储过程时，传递了参OUT P_TOTAL,数在存储过程中的…语句中对进行赋值，赋值结果由V_EMPCOUNT SELECT…INTO P_TOTAL变量带回给主程序并显示V_EMPCOUNT以上程序要覆盖同名的存储过程，如果不使用选项，就会出现以EMP COUNTORREPLACE下错误:位于第行ERROR1名称已由现有对象使用ORA-00955:练习创建存储过程，使用类型参数获得雇员经理名OUT创建和删除存储函数创建函数，需要有或的系统权限该CREATE PROCEDURECREATE ANYPROCEDURE权限可由系统管理员授予创建存储函数的语法和创建存储过程的类似，即［］函数名［（参数［］数据类型）］CREATE ORREPLACE FUNCTIONIN…数据类型RETURN{AS|IS}［说明部分］BEGIN可执行部分（表达式）RETURN［EXCEPTION错误处理部分］［函数名］;END其中，参数是可选的，但只能是类型（关键字可以省略）IN IN在定义部分的数据类型，用来表示函数的数据类型，也就是返回值的类型，RETURN此部分不可省略在可执行部分的（表达式），用来生成函数的返回值，其表达式的类型应该和定义部分RETURN说明的函数返回值的数据类型一致在函数的执行部分可以有多个语句，但只有一个RETURN语句会被执行，一旦执行了语句，则函数结束并返回调用环境RETURN RETURN一个存储函数在不需要时可以删除，但删除的人应是函数的创建者或者是拥有系统权限的人其语法如下DROP ANYPROCEDURE函数名；DROP FUNCTION重新编译一个存储函数时，编译的人应是函数的创建者或者拥有系ALTER ANYPROCEDURE统权限的人重新编译一个存储函数的语法如下:ALTER PROCEDURE函数名COMPILE；函数的调用者应是函数的创建者或拥有系统权限的人，或EXECUTE ANYPROCEDURE是被函数的拥有者授予了函数执行权限的账户函数的引用和存储过程不同，函数要出现在程序体中，可以参加表达式的运算或单独出现在表达式中，其形式如下变量名:二函数名（…）范例创建一个通过雇员编号返回雇员名称的函数GET EMPNAME步骤1登录SCOTT账户步骤2在SQL*Plus输入区中输入以下存储函数并编译（）CREATE ORREPLACE FUNCTION GET_EMP_NAME P_EMPNO NUMBERDEFAULT7788RETURN VARCHAR2AS（）V_ENAME VARCHAR210;BEGINSELECT ENAMEINTOV_ENAME FROMEMP WHEREEMPNO=P_EMPNO;RETURNV_ENAME;EXCEPTIONWHEN NO_DATA_FOUNDTHEN没有该编号雇员!,;DBMS_OUTPUT.PUT_LINERETURN NULL;WHEN TOO_MANY_ROWS THEN,有重复雇员编号!;DBMSJDUTPUT.PUTJJNERETURN NULL;WHEN OTHERSTHEN发生其他错误!*;DBMSJDUTPUT.PUTJJNE0RETURN NULL;END;步骤3调用该存储函数，输入并执行以下程序BEGIN雇员的名称是||DBMS_OUTPUT.PUT_LINEC7369GET_EMP_NAME7369;,雇员的名称是||DBMS_OUTPUT.PUT_LINE7839GET_EMP_NAME7839;END;显示结果为:雇员的名称是7369SMITH雇员的名称是7839KING过程已成功完成PL/SQL说明函数的调用直接出现在程序的语句中，作为字符串表达式的一DBMS_OUTPUT.PUT_LINE部分如果输入了错误的雇员编号，就会在函数的错误处理部分输出错误信息试修改雇员编号，重新运行调用部分练习创建一个通过部门编号返回部门名称的存储函数GET DEPTNAME练习将函数的执行权限授予账户，然后登录账户调用STUDENT STUDENT存储过程和函数的查看可以通过对数据字典的访问来查询存储过程或函数的有关信息、，如果要查询当前用户的存储过程或函数的源代码，可以通过对数据字典视图的查询得到USER_SOURCE USER的结构如下SOURCEDESCRIBE USER_SOURCE结果为:名称是否为空？类型NAME VARCHAR230TYPE VARCHAR212LINE NUMBERTEXTVARCHAR24000说明里面按行存放着过程或函数的脚本，是过程或函数名，代表类型NAME TYPEK是行号，为脚本PROCEDURE BFUNCTION,LINE TEXTIFSQL%FOUNDTHEN成功修改雇员工资!DBMS—OUTPUT.PUTJJNECCOMMIT;ELSE修改雇员工资失败!;DBMS—OUTPUT.PUTJJNECEND IF;END;说明本例中，通过属性判断修改是否成功，并给出相应信息SQL%FOUND显式游标游标的定义和操作游标的使用分成以下个步骤4声明游标
1.在部分按以下格式声明游标DECLEAR游标名［（参数数据类型［,参数数据类型…］）］CURSOR12语句；IS SELECT参数是可选部分，所定义的参数可以出现在以语句的子句中如果SELE WHERE定义了参数，则必须在打开游标时传递相应的实际参数语句是对表或视图的查询语句，甚至也可以是联合查询可以带条件、SELECT WHEREORDER或等子句，但不能使用子句在语句中可以使用在定义游标之BY GROUPBY INTOSELECT前定义的变量打开游标
2.在可执行部分，按以下格式打开游标游标名［（实际参数［实际参数OPEN1,2…打开游标时，语句的查询结果就被传送到了游标工作区SELECT提取数据
3.在可执行部分，按以下格式将游标工作区中的数据取到变量中提取操作必须在打开游标之后进行游标名变量名［变量名］FETCH INTO1,
2...;或游标名记录变量；FETCH INTO游标打开后有一个指针指向数据区，语句一次返回指针所指的一行数据，FETCH要返回多行需重复执行，可以使用循环语句来实现控制循环可以通过判断游标的属性来进行下面对这两种格式进行说明第一种格式中的变量名是用来从游标中接收数据的变量，需要事先定义变量的个数和类型应与语句中的字段变量的个数和类型一致SELECT第二种格式一次将一行数据取到记录变量中，需要使用％ROWTYPE事先定义记录变量，这种形式使用起来比较方便，不必分别定义和使用多个变量范例查询过程的脚本EMP COUNT在中输入并执行如下查询:SQL*Plusselect TEXTfrom usersource WHERENAME=EMP COUNT,;WMB，结果为:TEXTPROCEDURE EMP_COUNTP_TOTAL OUTNUMBERASBEGINSELECT COUNT*INTO P_TOTAL FROMEMP;END;范例查询过程的参数GET EMPNAME在中输入并执行如下查询SQL*PlusDESCRIBE GET_EMP_NAME结果为:FUNCTIONGET_EMP_NAME RETURNSVARCHAR2参数名称类型输入/输出默认值（）P_EMPNO NUMBER4IN DEFAULT范例在发生编译错误时，显示错误SHOW ERRORS说明查询一个存储过程或函数是否是有效状态（即编译成功），可以使用数据字典的歹人USER_OBJECTS STATUS范例查询存储过程是否可用EMPJJSTSELECT STATUSFROM USERJDBJECTSWHERE OBJECT_NAME=,EMP_LIST,;结果为:STATUSVALID说明表示该存储过程有效（即通过编译），表示存储过程无效或需要重VALID INVALID新编译当调用一个无效的存储过程或函数时，首先试图对其进行编译，如果编译成功Oracle则将状态置成并执行，否则给出错误信息VALID当一个存储过程编译成功，状态变为会不会在某些情况下变成结论是完全可能VALID,INVALIDo的比如一个存储过程中包含对表的查询，如果表被修改或删除，存储过程就会变成无效所以要注意存储过程和函数对其他对象的依赖关系INVALIDo如果要检查存储过程或函数的依赖性，可以通过查询数据字典USER DENPENDENCIES来确定，该表结构如下DESCRIBE USER_DEPENDENCIES;名称是否为空？类型NAME NOTNULL VARCHAR230TYPE VARCHAR212REFERENCED_OWNER VARCHAR230REFERENCED_NAME VARCHAR264REFERENCED_TYPE VARCHAR212REFERENCED_LINK_NAME VARCHAR2128SCHEMAID NUMBERDEPENDENCY_TYPE VARCHAR24说明为实体名，为实体类型，为涉及到的实体拥有者账户，NAME TYPEREFERENCED_OWNER为涉及到的实体名，为涉及到的实体类型REFERENCED_NAME REFERENCED_TYPE范例查询存储过程的依赖性EMP LISTSELECTREFERENCED_NAME REFERENCED_TYPE FROMUSER_DEPENDENCIES WHERE/NAME=,EMP_LIST,;说明可以看出存储过程依赖一些系统包、表和存储过程如果删EMPJJST EMP EMPJZOUNT除了表或存储过程，将变成无效EMPEMP_COUNT EMP_LIST还有一种情况需要我们注意如果一个用户被授予执行属于用户的一个存储过程的权限，A B在用户的存储过程中，访问到用户的表，用户被授予访问用户的表的权限，但用户B CB C A没有被授予访问用户表的权限，那么用户调用用户的存储过程是失败的还是成功的呢？CAB答案是成功的如果读者有兴趣，不妨进行一下实际测试定义记录变量的方法如下变量名表名|游标名％ROWTYPE；其中的表必须存在，游标名也必须先定义关闭游标
4.游标名；CLOSE显式游标打开后，必须显式地关闭游标一旦关闭，游标占用的资源就被释放,游标变成无效，必须重新打开才能使用范例以下是使用显式游标的一个简单练习用游标提取表中雇员的名称和职务emp7788ybl.sqlSET SERVEROUTPUTONDECLAREv_ename VARCHAR210;vjob VARCHAR210;CURSOR emp_cursor ISSELECT enamejob FROMemp WHEREempno=7788;BEGINOPEN emp_cursor;FETCH emp_cursor INTOv_ename,vjob;DBMS_OUTPUT.PUT_LINEv_ename||；l Ivjob;CLOSE emp_cursor;END;说明该程序通过定义游标提取并显示雇员的名称和职务作emp_cursor,7788为对以上例子的改进，在以下训练中采用了记录变量范例用游标提取表中雇员的姓名、职务和工资emp7788yb
2.sqlSET SERVEROUTPUTONDECLARECURSOR emp_cursor ISSELECT enamejob,sal FROMemp WHEREempno=7788;emp_record emp_cursor%ROWTYPE;BEGINOPEN emp_cursor;FETCH emp_cursor INTOemp_record;DBMS_OUTPUT.PUT_LINEemp_record.ename117||emp_record.job117||to_charemp_record.sal;CLOSE emp_cursor;END;说明实例中使用记录变量来接收数据，记录变量由游标变量定义，需要出现在游标定义之后注意可通过以下形式获得记录变量的内容记录变量名.字段名范巧显示工资最高的前名雇员的名称和工资3yb
3.sqlSET SERVEROUTPUTONDECLAREv_ename VARCHAR210;v_sal NUMBER5;CURSOR emp_cursor ISSELECTename,sal FROMemp ORDER BY salDESC;BEGINOPEN emp_cursor;FOR IIN
1..3LOOPFETCH emp_cursor INTOv_ename,v_sal;DBMS_OUTPUT.PUT_LINEv_ename|r；l|v_sal;END LOOP;CLOSE emp_cursor;END;说明该程序在游标定义中使用了子句进行排序，并使用循环语句来提取多行数据ORDERBY游标循环范例使用特殊的循环形式显示全部雇员的编号和名称FOR yb
4.sqlSET SERVEROUTPUTONDECLARECURSOR emp_cursor ISSELECTempno,ename FROMemp;BEGINFOR emp_record INemp_cursor LOOPDBMS_OUTPUT.PUT_LINEEmp_record.empno||Emp_record.ename;END LOOP;END;说明可以看到该循环形式非常简单，隐含了记录变量的定义、游标的打开、提取和关闭过程为隐含定义的记录变量，循环的执行次数与游标取得的数据的行数相一致范例Emp_record另一种形式的游标循环yb
5.sqlSET SERVEROUTPUTONBEGINFOR reIN SELECTename FROMEMP LOOPDBMS_OUTPUT.PUT_LINEre.ename;END LOOP;END;说明该种形式更为简单，省略了游标的定义，游标的查询语句在循环中直接出现SELECT显式游标属性虽然可以使用前面的形式获得游标数据，但是在游标定义以后使用它的一些属性来进行结构控制是一种更为灵活的方法显式游标的属性如下表所示游标的属性返回值类型意义%ROWCOUNT整型获得FETCH语句返回的数据行数布尔型最近的FETCH语句返回一行数据则为真，否则为假%FOUND%NOTFOUND布尔型与％FOUND属性返回值相反布尔型游标已经打开时值为真，否则为假%ISOPEN可按照以下形式取得游标的属性游标名％属性要判断游标是否处于打开状态，可以使用属性如果emp_cursor emp_cursor%ISOPENo游标已经打开，则返回值为〃真〃，否则为〃假〃具体可参照以下的训练范例使用游标的属性练习（yb
6.sql）SET SERVEROUTPUTONDECLARE（）v_ename VARCHAR210;CURSOR emp_cursor ISSELECTename FROMemp;BEGINOPEN emp_cursor;IF emp_cursor%ISOPEN THENLOOPFETCH emp_cursor INTOv_ename;EXIT WHENemp_cursor%NOTFOUND;（（））DBMS_OUTPUT.PUT_LINE to_char emp_cursor%ROWCOUNT|I-l|v_ename;END LOOP;ELSE用户信息游标没有打开！，）；DBMS_OUTPUT.PUTJJNECEND IF;CLOSE emp_cursor;END;说明本例使用判断游标是否打开；使用获得到emp_cursor%ISOPEN emp_cursor%ROWCOUNT目前为止语句返回的数据行数并输出；使用循环来获取数据、在循环体中使用语FETCH FETCH句；使用判断语句是否成功执行，当语句失败时说明数emp_cursor%NOTFOUND FETCHFETCH据已经取完，退出循环练习:去掉语句，重新执行以上程序OPEN emp cursor;游标参数的传递（了解）范例带参数的游标（V例.sql）SET SERVEROUTPUTONDECLAREv_empno NUMBER5;v_ename VARCHAR210;CURSOR emp_cursorp_deptno NUMBER,pjob VARCHAR2ISSELECTempno,ename FROMempWHERE deptno=p_deptno ANDjob=pjob;BEGINOPEN emp_cursor10,CLERK;LOOPFETCH emp_cursor INTOv_empno,v_ename;EXIT WHENemp_cursor%NOTFOUND;DBMS_OUTPUT.PUT_LINEv_empno|11v_ename;END LOOP;END;说明游标定义了两个参数代表部门编号，」代表职务语句emp_cursor p_deptno pob OPEN传递了两个参数值给游标,即部门为、职务为所以游标查询的emp_cursor10,CLERK10CLERK,内容是部门的职务为的雇员循环部分用于显示查询的内容10CLERK练习:修改语句的参数部门号为、职务为并重新执行Open20ANALYST,也可以通过变量向游标传递参数，但变量需要先于游标定义，并在游标打开之前赋值对以上例子重新改动如下范例通过变量传递参数给游标yb
8.sqlSET SERVEROUTPUTONDECLAREv_empno NUMBER5;v_ename VARCHAR210;v_deptno NUMBER5;vjob VARCHAR210;CURSOR emp_cursor ISSELECTempno,ename FROMempWHERE deptno=v_deptno ANDjob=vjob;BEGINv_deptno:=10;vJob:=CLERK;OPEN emp_cursor;LOOPFETCH emp_cursor INTOv_empno,v_ename;EXIT WHENemp_cursor%NOTFOUND;DBMS_OUTPUT.PUT_LINEv_empno|171|v_ename;END LOOP;END;说明该程序与前一程序实现相同的功能利用游标删除和修改数据的时候要注意表名游标名；UPDATE SET...WHERE CURRENT OF表名游标名；DELETE WHERECURRENTOF范例定义游标通过使用游标，根据职务调整雇员的工资empcuryb
9.sqDSET SERVEROUTPUTONDECLAREvjob emp.job%TYPE;CURSOR emp_curISSELECT jobFROMempFOR UPDATE;BEGINOPEN emp_cur;LOOPFETCHemp_cur INTOvjob;EXIT WHENemp_cur%NOTFOUND;CASEWHEN vJob=CLERK THENupdateemp set sal=sal+50where currentof emp_cur;」」WHEN v ob=SALESMAN ORvob=ANALYST THENupdateemp setsal=sal+40where currentof emp_cur;ELSEupdate empsetsal=sal+10where currentof emp_cur;END CASE;END LOOP;COMMIT;END;范例用游标循环，实现打印某一职务输入一职务的雇员的雇员编号和雇员姓名For可参考yblO.sql yb
4.sqlDECLAREvjob emp.job%TYPE;CURSORemp_cursor ISSELECTempno,ename FROMemp WHEREjob=vjob;BEGINvjob:=*vjob;FOR emp_record INemp_cursor LOOPDBMS_OUTPUT.PUT_LINEEmp_record.empno||Emp_record.ename;END LOOP;END;异常处理错误处理错误处理部分位于程序的可执行部分之后，是由语句引导的多个分支构成的WHEN错误处理的语法如下EXCEPTION错误错误WHEN1[OR2]THEN语句序列1；错误错误WHEN3[0R4]THEN语句序列2；WHEN OTHERS语句序列n；END;错误是在标准包中由系统预定义的标准错误，或是由用户在程序的说明部分自定义的错误，参见下一节系统预定义的错误类型语句序列就是不同分支的错误处理部分凡是出现在后面的错误都是可以捕捉到的错误，其他未被捕捉到的错误，将在WHEN WHEN部分进行统一处理，必须是部分的最后一个错误处理分支如要在OTHERS OTHERSEXCEPTION该分支中进一步判断错误种类，可以通过使用预定义函数和来获得系统SQLCODE SQLERRM错误号和错误信息如果在程序的子块中发生了错误，但子块没有错误处理部分，则错误会传递到主程序中下面是由于查询编号错误而引起系统预定义异常的例子范例查询编号为的雇员名字1234SET SERVEROUTPUTONDECLAREv_name VARCHAR210;BEGINSELECT enameINTOv_nameFROM empWHEREempno=1234;该雇员名字为DBMSJDUTPUT.PUTJJNEC|v_name;EXCEPTIONWHEN NO_DATA_FOUNDTHEN编号错误，没有找到相应雇员！,；DBMS_OUTPUT.PUT_LINEWHEN OTHERSTHEN发生其他错误!,;DBMSJDUTPUT.PUTJJNEEND;说明在以上查询中，因为编号为的雇员不存在，所以将发生类型为〃1234NO DATAFOUND〃的异常〃NO_DATA_FOUND〃是系统预定义的错误类型，EXCEPTION部分下的WHEN语句将捕捉到该异常，并执行相应代码部分在本例中，输出用户自定义的错误信息〃编号错误，没有找到相应雇员已如果发生其他类型的错误，将执行条件下的代码部分，显示〃OTHERS发生其他错误!〃范例由程序代码显示系统错误SET SERVEROUTPUTONDECLAREv_temp NUMBER5:=1;BEGINv_te mp:=v_te mp/t;EXCEPTIONWHEN OTHERSTHEN发生系统错误!,;DBMS_OUTPUT.PUTJJNE错误代码:||DBMS_OUTPUT.PUT_LINE SQLCODE;,错误信息:|DBMS_OUTPUT.PUT_LINE||SQLERRM;END;说明程序运行中发生除零错误，由捕捉到，执行用户自己的输出语句显示错WHENOTHERS误信息，然后正常结束在错误处理部分使用了预定义函数和来进一步SQLCODE SQLERRM获得错误的代码和种类信息预定义错误了解的系统错误很多，但只有一部分常见错误在标准包中予以定义定义的错误可以在Oracle部分通过标准的错误名来进行判断，并进行异常处理常见的系统预定义异常如下EXCEPTION表所示错误名称错误代码错误含义CURSOR_ALREADY_OPEN ORA_06511试图打开已经打开的游标INV\LID_CURSOR ORA.OIOOI试图使用没有打开的游标DUP_V\L_ONJNDEX ORA.OOOOl保存重复值到，毋-索引约束的列中ZERO_DIVIDE ORA_01476发生除数为零的除法错误INV\LID_NUMBER ORA_01722试图对无效字符进行数值转换ROWTYPE.M ISMATCHORA_06504主变量和游标的类型不兼容V\LUE_ERROR ORA_06502转换、截断或算术运算发生错误TOO_MANY_ROWS ORA_01422SELECT...INTO…语句返回多于一行的数据NO_DATA_FOUND ORA_01403SELECT…INTO...语句没有数据返回TIM EOUT_ON_RESOURCE ORA_00051等待资源时发生超时错误TRANSACTION_BACKED_OUT ORA_00060由于死锁，提交失败STORAGE_ERROR ORA_06500发生内存错误PROGRAM_ERROR ORA_06501发生PL/SQL内部错误NOT_LOGGED_ON ORA_01012试图操作未连接的数据库LOGIN_DENIED ORA_01017在连接时提供了无效用户名或口令比如，如果程序向表的主键列插入重复值，则将发生」错误DUP_VAL_ON NDEX如果一个系统错误没有在标准包中定义，则需要在说明部分定义，语法如下:。
个人认证
优秀文档
获得点赞 0
