ORACLE子程序及TABLE类型用法等.doc

第八章 PL/SQL子程序一、什么是子程序子程序就是能够接受参数并被其他程序所调用的命名PL/SQL块。PL/SQL子程序有两种类型，过程和函数。一般地，过程用于执行一个操作，而函数用于计算一个结果值。与 未命名或匿名PL/SQL块一样，子程序也有声明部分，执行部分和一个可选的异常处理部分。声明部分包含类型、游标、常量、变量、异常和嵌套子程序的声 明。这些内容都是本地的，在程序退出时会自动销毁。执行部分包含赋值语句、流程控制语句和Oracle的数据操作语句。异常处理部分包含异常处理程序。思 考下面用于记入借方银行账户的debit_account过程： PROCEDUREdebit_account(acct_idINTEGER,amountREAL)ISold_balanceREAL;new_balanceREAL;overdrawnEXCEPTION;BEGINSELECTbalINTOold_balanceFROMacctsWHEREacct_no=acct_id;new_balance:=old_balance-amount;IFnew_balance=min_sal)AND(salaryamt,acct_no=acct);-namednotationcredit_acct(acct_no=acct,amount=amt);-namednotationcredit_acct(acct,amount=amt);-mixednotation 1、使用位置标示法第一个过程调用使用了位置标示法。PL/SQL编译器将第一个实参acct和第一个形参acct_no关联，并把第二个实参amt和第二个形参amount关联。 2、使用名字标示法第二个过程调用使用了名字标示法。箭头(=)作为关联操作符，把左边的实参和右边的形参关联起来。 第三个过程调用也使用了名字标示法，而且我们可以随意安排参数的位置。所以，我们不需要知道形参的在参数列表中的顺序。 3、使用混合标示法第四个过程调用使用了名字标示法和位置标示法。在这种情况下，位置标示法必须在名字标示法之前，不能反过来使用，像下面这样的调用方法就是不合法的： credit_acct(acct_no=acct,amt);-illegal 九、指定子程序参数模式我 们可以使用参数的模式来定义形式参数的行为。一共有三种模式：IN、OUT和IN OUT。但是，最好避免在函数中使用OUT和IN OUT模式。函数的作用是用来接受零个或多个参数然后返回一个值。用函数返回多个值不是个好习惯。同样，函数应该避免产生负影响，那样会改变那些对子程序 来说是非本地的变量值。 1、使用IN模式IN模式能让我们把值传递给被调用子程序，在子程序中，IN模式参数的作用就像常量一样。因此，它不能被赋值。例如，下面的赋值语句就会引起编译错误： PROCEDUREdebit_account(acct_idININTEGER,amountINREAL)ISminimum_purchaseCONSTANTREALDEFAULT10.0;service_chargeCONSTANTREALDEFAULT0.50;BEGINIFamount60THENbonus:=bonus+500;ENDIF;.EXCEPTIONWHENbonus_missingTHEN.ENDcalc_bonus; 与OUT模式的形参对应的实参必须是变量；它不能是常量或表达式。例如，下面的调用就不合法： calc_bonus(7499,salary+commission);-causescompilationerror 一个OUT实参在子程序调用之前是可以有值的。但是，在子程序调用时，这个值就会丢失，除非我们使用了NOCOPY编译器提示或是子程序因未捕获异常而终止。 与变量一样，OUT模式的形参会被初始化为NULL.所以，一个OUT模式的形参的数据类型是不能有NOT NULL约束的(包括内置类型NATURALN和POSITIVEN)。否则的话，PL/SQL就会抛出VALUE_ERROR异常，见下例： DECLARESUBTYPEcounterISINTEGERNOTNULL;ROWScounter:=0;PROCEDUREcount_emps(nOUTcounter)ISBEGINSELECTCOUNT(*)INTOnFROMemp;END;BEGINcount_emps(ROWS);-raisesVALUE_ERROR 在子程序退出之前，它必须要显式地为所有的OUT模式形参赋值。否则对应的实参值就为空。如果成功地退出子程序，PL/SQL就会把值赋给实参。但是，如果有未捕获异常发生，PL/SQL就不会为实参赋值。 3、使用IN OUT模式一个IN OUT模式的参数能让我们把它的初始值传递给被调用的子程序，然后再把子程序更新后的值传递给调用者。在子程序中，一个IN OUT模式参数的作用就像一个初始化了的变量。因此，它能够被赋值，而且它的值还可以赋给其他的变量。 与IN OUT模式形参对应的实参必须是变量；它不可以是常量或表达式。如果成功地退出子程序，PL/SQL就会为实参赋值。但是，如果有未捕获异常发生，PL/SQL就不会为实参赋值。 4、子程序参数模式总结下表总结了我们应该知道关于参数模式的所有内容：INOUTIN OUT默认必须被指定必须被指定向子程序传值向调用者返回值向子程序传递初始值并向调用者返回更新后的结果值形参的作用同常量相同形参的作用同变量相同形参的作用同被初始化过的变量相同形参不能被赋值形参必须被赋值形参应该被赋值实参可以是常量、被初始化的变量、文字或表达式形参必须是变量形参必须是变量形参按引用传递形参按值传递，除非使用了NOCOPY形参按值传递，除非使用了NOCOPY十、使用NOCOPY编译提示传递大型数据结构假定子程序声明了一个IN模式参数、一个OUT模式参数和一个IN OUT模式参数。在调用子程序时，IN模式的是按引用传递的，即把指向IN模式的实参指针赋给形参。所以，两个参数引用都指向同一块内存地址,这块内存存放了实参的值。 默认情况下，OUT和IN OUT模式的参数都是按值传递的。就是把实参的值拷贝到对应的形参上。然后，如果子程序正常结束，被赋到OUT和IN OUT形参上的值就会拷贝到对应的实参上。 当 参数是大型数据结构时，如集合、记录和对象实例，把它们的内容全部拷贝给形参会降低执行速度，消耗大量内存。为了防止这样的情况发生，我们可以使用 NOCOPY提示来让编译器按引用传递OUT和IN OUT模式的参数。在下面的例子中，我们请求编译器按引用的方式来传递IN OUT参数my_staff： DECLARETYPEStaffISVARRAY(200)OFEmployee;PROCEDUREreorganize(my_staffINOUTNOCOPYStaff)IS. 记 住，NOCOPY只是一个提示，而不是指令。所以，编译器也许仍旧会把my_staff按值传递，即使我们已经发出请求了。但是，通常情况下NOCOPY 是可以成功的。下例中，我们把一个含有25000条记录的本地嵌套表中分别传递给两个没有任何功能的过程。没有使用NOCOPY的记录花费21秒，而使用 的花费不到1秒： SQLSETSERVEROUTPUTONSQLGETtest.sql1DECLARE2TYPEEmpTabTypISTABLEOFemp%ROWTYPE;3emp_tabEmpTabTyp:=EmpTabTyp(NULL);-initialize4t1NUMBER(5);5t2NUMBER(5);6t3NUMBER(5);7PROCEDUREget_time(tOUTNUMBER)IS8BEGINSELECTTO_CHAR(SYSDATE,SSSSS)INTOtFROMdual;END;9PROCEDUREdo_nothing1(tabINOUTEmpTabTyp)IS10BEGINNULL;END;11PROCEDUREdo_nothing2(tabINOUTNOCOPYEmpTabTyp)IS12BEGINNULL;END;13BEGIN14SELECT*INTOemp_tab(1)FROMempWHEREempno=7788;15emp_tab.EXTEND(24999,1);-copyelement1into2.2500016get_time(t1);17do_nothing1(emp_tab);-passINOUTparameter18get_time(t2);19do_nothing2(emp_tab);-passINOUTNOCOPYparameter20get_time(t3);21dbms_output.put_line(CallDuration(secs);22dbms_output.put_line(-);23dbms_output.put_line(JustINOUT:|TO_CHAR(t2-t1);24dbms_output.put_line(WithNOCOPY:|TO_CHAR(t3-t2);25*END;SQL/CallDuration(secs)-JustINOUT:21WithNOCOPY:0 1、权衡NOCOPY所带来的良好性能NOCOPY能为我们带来良好的性能，但它也能带来以下几个方面的影响： 1. 因为NOCOPY只是一个提示，不是指令，所以编译器可以把NOCOPY参数按值或按引用的方式传递给子程序。所以，如果子程序因发生未捕获异常而退出时，我们就不能再信赖实参中的值了。 2. 默 认地，如果子程序异常退出，赋给OUT和IN OUT参数的值就不会拷贝到对应的实参上，这看起来有点像回滚操作。但是，对于按引用传递的NOCOPY参数来说，我们对形参所作的更改会立即在对应的实 参上体现出来。所以，即使子程序是因异常发生而结束，它所做的变更内容也不会回滚。 3. 目前，RPC协议允许我们只按值传递参数。例如，如果我们把一个含有NOCOPY参数的本地过程传到远程站点，这些参数就不再按引用传递了。 还有，使用NOCOPY会增加参数别名出现的可能性。 2、NOCOPY的限制在以下几种情况中，PL/SQL编译器会忽略NOCOPY提示而直接使用按值传递参数的方法(不发生错误的情况下)： 1. 实参是索引表中的一个元素。这个限制并不适用于整个索引表。 2. 实参是受约束的(如精度或NOT NULL等)。这个约束不会扩展到元素或属性。同样，对长度受限的字符串也不适用。 3. 实参和形参都是记录，其中一个或两个使用了%ROWTYPE或%TYPE声明，且在记录中对应域的约束不同。 4. 实参和形参都是记录，实参是作为游标FOR循环的索引而被声明的(隐式声明)，记录之间对应域的约束不同。 5. 实参传递需要进行隐式地数据类型转换。 6. 子程序被外部或远程过程调用。 十一、使用子程序参数的默认值如下例所示，我们可以为IN模式的参数初始化默认值。这样，我们就可以把不同个数的参数传给子程序，其中既可以使用参数默认值又可以使用我们传入的参数值覆盖掉默认值。并且，我们还可以在不修改每个子程序调用的情况下添加新的参数。 PROCEDUREcreate_dept(new_dnameVARCHAR2DEFAULTtemp,new_locVARCHAR2DEFAULTtemp)ISBEGININSERTINTOdeptVALUES(deptno_seq.NEXTVAL,new_dname,new_loc);.END; 如果实参没有被传入，它所对应的形参就会使用定义时的默认值。下面是对过程create_dept的调用： create_dept;create_dept(MARKETING);create_dept(MARKETING,NEWYORK); 第 一个调用没有传入任何实参，所以子程序会使用两个默认的参数值；而第二个调用只为第一个参数指定了实参，这样，子程序会使用第一个参数传入的值和第二个参 数的默认值；最后一个调用接受两个实参，它们将对应的形参默认值覆盖，子程序就使用两个传入的值。通常我们使用位置标示法覆盖形参的默认值，但是，我们不 能靠省略实参来跳过它们对应的形参。例如，像下面这样把实参值NEW YORK和形参new_dname关联的做法是不对的： create_dept(NEWYORK);-incorrectcreate_dept(,NEWYORK);-notallowed 我们也不可以靠放置一个占位符来解决这个问题。例如，下面的调用就是不允许的：create_dept(,NEWYORK);-notallowed 当出现这种情况，我们就需要使用名字标示法： create_dept(new_loc=NEWYORK); 同样，我们也不能靠省略实参来为未初始化的形参赋空值。例如： DECLAREFUNCTIONgross_pay(emp_idINNUMBER,st_hoursINNUMBERDEFAULT40,ot_hoursINNUMBER)RETURNREALISBEGIN.NULL;END;BEGINIFgross_pay(emp_num)max_payTHEN.-notallowedEND; 上面代码中最后一句并不会把空值赋给ot_hours。如果确实想赋空值的话，我们需要显式地为其赋值： IFgross_pay(emp_num,ot_hour=NULL)max_payTHEN. 或者是我们像下面这样吧ot_hours初始化为NULL： ot_hoursINNUMBERDEFAULTNULL;最后，当创建存储子程序时，我们不能在DEFAULT子句中使用主变量(绑定变量)。下面SQL*Plus例子就会引起绑定变量(bind variable)错误，因为在创建时，num只是一个值可能发生变化的占位符而已： SQLVARIABLEnumNUMBERSQLCREATEFUNCTIONgross_pay(emp_idINNUMBERDEFAULT:num,. 十二、理解子程序参数别名为优化子程序调用，PL/SQL采取两种参数传递的方法，传值和传引用。NOCOPY编译器提示会增大别名出现的可能性(即两个不同的名字引用同一块内存)。在子程序调用中，如果全局变量作为子程序的实参时就可能发生这种情况。这样，结果就无法确定，因为它依赖于编译器选择的传递参数的方式。下例中，过程add_entry用两种方法引用数组lexicon：作为普通参数和作为全局变量。所以，当add_entry被调用时，标识符word_list和lexicon代表着同一个数组： DECLARETYPEdefinitionISRECORD(wordVARCHAR2(20),meaningVARCHAR2(200);TYPEDICTIONARYISVARRAY(2000)OFdefinition;lexiconDICTIONARY:=DICTIONARY();PROCEDUREadd_entry(word_listINOUTNOCOPYDICTIONARY)ISBEGINword_list(1).word:=aardvark;lexicon(1).word:=aardwolf;END;BEGINlexicon.EXTEND;add_entry(lexicon);DBMS_OUTPUT.put_line(lexicon(1).word);-printsaardvarkifparameterwaspassedbyvalue-printsaardwolfifparameterwaspassedbyreferenceEND; 过 程add_entry的运算结果取决于编译器采取哪种参数传递方式。如果编译器采用按值传递，word_list和lexicon各自独立，它们各自内容 的变化不会互相影响。但是，如果采用按引用传递的话，这个两个标识就是同一个数组的不同名称罢了。所以，当其中一个的内容发生变化时，另一个也会受到影 响，随之变化。 在子程序中，如果同一实参出现的次数多于一次，也能产生别名。下例中，n2是一个IN OUT模式的参数，所以它的值在子程序退出之前是不会被更新的。这就是为什么第一个put_line打印结果是10(n的初识值)而第三个是20。但是， 由于n3是一个NOCOPY参数，所以实参值会被立即更新为30。 DECLAREnNUMBER:=10;PROCEDUREdo_something(n1INNUMBER,n2INOUTNUMBER,n3INOUTNOCOPYNUMBER)ISBEGINn2:=20;DBMS_OUTPUT.put_line(n1);-prints10n3:=30;DBMS_OUTPUT.put_line(n1);-prints30END;BEGINdo_something(n,n,n);DBMS_OUTPUT.put_line(n);-prints20END; 因 为它们都是指针，所以游标变量也能增加别名出现的可能性。如下面的例子，赋值后，emp_cv2就是emp_cv1的别名，因为它们都指向同一块查询工作 区。所以，它们两个都能改变游标状态，这就是为什么第一次从emp_cv2就能取得第三行数据(不是第一行)，而在关闭emp_cv1之后，第二次从 emp_cv2中就取不到数据： PROCEDUREget_emp_data(emp_cv1INOUTempcurtyp,emp_cv2INOUTempcurtyp)ISemp_recemp%ROWTYPE;BEGINOPENemp_cv1FORSELECT*FROMemp;emp_cv2:=emp_cv1;FETCHemp_cv1INTOemp_rec;-fetchesfirstrowFETCHemp_cv1INTOemp_rec;-fetchessecondrowFETCHemp_cv2INTOemp_rec;-fetchesthirdrowCLOSEemp_cv1;FETCHemp_cv2INTOemp_rec;-raisesINVALID_CURSOR.END; 十三、子程序的重载PL/SQL允许我们对子程序进行重载，也就是说，我们可以使用相同名称的子程序，只要保证它们的形式参数在个数或顺序或数据类型上不同即可。假设我们要初始化下面两个索引表的前n行数据： DECLARETYPEdatetabtypISTABLEOFDATEINDEXBYBINARY_INTEGER;TYPErealtabtypISTABLEOFREALINDEXBYBINARY_INTEGER;hiredate_tabdatetabtyp;sal_tabrealtabtyp;BEGIN.END; 下面是用于初始化索引表hiredate_t