oracle-database-11g-plsql-编程实战笔记

oracle-database-11g-plsql-编程实战笔记Chap1DML语句是select、insert、update、delete和mergeDDL语句是create、alter、drop、rename、truncate、commentDCL语句是grant、revokeTCL语句是commit、rollback和savepointsql16个基本命令——参考 书 关于书的成语关于读书的排比句社区图书漂流公约怎么写关于读书的小报汉书pdf 《OCA认证考试指南（IZ0-051）》清华大学出版社《oracledatabasesqllanguagereference11g》有非遵循格式字符串依赖于格式掩码chap22.1.3关于语句中有多个单引号时处理：1、select'It''sabird,noplancan''tbe'aspharsefromdual;此处两个单引号即为一个单引号2、只能用q再加’(语句)’selectq'(It'sabird,noplancan'tbe)'aspharsefromdual;均输出PHARSE----------------------------It'sabird,noplancan'tbe2.1.4定义变量与申明变量的区别：定义变量即为变量分配名称并指定数据类型；申明变量首先需要定义变量，然后为其赋值。(赋值也称为初始化)替代变量前面要加&前缀且若替代变量为字符型时要加两个单引号如’&a’declarelv_whomvarchar2(20);/*lv-whom为申明变量，a为替代变量，a没有变量类型*/beginlv_whom:='&a';end;或者declarelv_whomvarchar2(20);beginlv_whom:=&a;end;但是要在输入框中字符加两个单引号替代变量用define申明，且定义时不可以指定类型，默认为char型=1\*GB3①Definex=emp;Select*from&x;/*调用要用&，此处不加单引号，解析后即为emp表*/=2\*GB3②definex=adasd;select'&x'zfromdual;/*此处解析后变为select'adasd'zfromdual，此处必须要有单引号，使adasd作为直接变量，否则出错*/所以替代变量是否加单引号要根据解析的值来确定注意：避免在声明块中将任何实参赋给局部变量（constant变量除外），且替代变量与绑定变量都不应该在声明块中赋值定义为constant的变量，必须在申明块中申明，这意味着常量必须先定义，再给他赋一个不变的值绑定变量（bind）用var或variable申明使用冒号（:）作为前缀varanumberbegin:a:=22;end;上面也可以输出结果，下面也可以输出结果，但二者不能同时放一块printa;/*此处a前面不能加冒号，print可用来输出所有类型的变量*/另外也可通过exec直接给绑定变量赋值：SQL>varxnumber/*必须要先定义再赋值，而替代变量定义时不需指定类型，直接赋值即可*/SQL>varynumberSQL>exec:x:=1;:y:=2;/*exec等价于begin…end*/PL/SQLproceduresuccessfullycompletedx---------1y---------2也可以先定义好绑定变量，再将其赋给其他变量：varanumberbegin:a:=22;end;现将其运行再declarelv_anumber;beginlv_a:=:a;dbms_output.put_line('hello，'||lv_a||'.');end;结果：hello,22.将上面的代码放入到d:\q.sql中用@d:\q.sql;调用即可，但是里面的代码要正确2.1.5命名块1、过程块createorreplaceprocedureabc(aavarchar2)isbegindbms_output.put_line('sdhl'||aa);end;execabc('dkjdhhdj');可能出现错误一：【ORA-00955:名称已由现有对象使用】这个错误说的不仅仅是有可能你有存储过程使用了【abc】，还有可能是你有表名叫【abc】或者其他oracle对象叫【abc】。说明：其实由于你已经使用了【createorrepalaceprocedure】即使有存储过程叫【abc】也会被覆盖掉，你用下面的命令查询一下，有什么对象叫【abc】，删掉它或者换一个存储过程名字。【SELECTOBJECT_NAME,OBJECT_TYPEFROMUSER_OBJECTSWHEREOBJECT_NAME='STUDENT2';】错误二：过程中的参数不能指定具体长度2、函数块错误一:函数里面要有return，不是dbms_output.put_line（但实验之后二者均可以成功创建函数，但是调用时只有return的能没有错误的执行）createorreplacefunctionabc(aavarchar2)returnvarchar2isbegindbms_output.put_line('sdhl'||aa);end;改为createorreplacefunctionabc(aavarchar2)returnvarchar2isbeginreturn'sdhl'||aa;end;但函数不能像过程一样来执行，因为execute命令不允许管理函数返回值。必须要用call或begindbms...end命令将返回值放入一个绑定变量（要先定义）中，在输出。（必须要先执行函数，在执行下面语句，不能一起运行）variableresultvarchar2(20);callabc('akdhak')into:result;--call后面只能用绑定变量？或者：SQL>begin2dbms_output.put_line(abc('du'));3end;4/sdhldu此处若用print则绑定变量不能加冒号只能为printresult;但可以查询select:resultfromdual;（exec是sqlplus的命令，只能在sqlplus中使用。call是sql命令，任何工具都可以使用）2.1.6嵌套块命名块还可以嵌套在其他命名块或匿名块中，但嵌套命名块并不是已经发布的，这意味着在调用一个命名块时，被调用的命名块可能还没有定义declareprocedureaisbegindbms_output.put_line(b||'hello!');/*此处b为被调用函数，但之前没有被解析，所以运行会出错*/enda;functionbreturnvarchar2isbeginreturn'hello!';endb;begina;end;注：所有匿名块都会在程序都会在实际执行前进行 分析 定性数据统计分析pdf销售业绩分析模板建筑结构震害分析销售进度分析表京东商城竞争战略分析 ，分析是一个编译过程。分析过程将会识别标识符（保留字）、预定义标识符、引用标识符、用户定义变量、子例程或UDT。命名块也是标识符，PL/SQL按照自顶向下的次序，将标识符读取到内存中。上例中函数b因为在过程a的下面还没被解析，所以会出错。使用“前向引用”可以修正这个问 题 快递公司问题件快递公司问题件货款处理关于圆的周长面积重点题型关于解方程组的题及答案关于南海问题 ，函数或过程的前向引用只需要函数或过程的署名，不需要同时包括署名和实现，这些署名在PL/SQL中叫做“占位程序（stub）”。占位程序允许编译过程在实现命名块之前接受其标识符名称。更正：declareprocedurea;functionbreturnvarchar2;procedureaisbegindbms_output.put_line(b||'hello!');enda;functionbreturnvarchar2isbeginreturn'hello!';endb;begina;end;2.2变量：类型、赋值和运算符2.2.1文本数据类型用伪列来隐式地定义数据类型。伪列如%typeChar(20)为一个定长的类型，不管其中变量的长度有没有达到20，最后显示长度均为20字符Varchar2（20）则要根据实际情况来确定其长度Clob（characterlargeobject字符大对象）同varchar22.2.2日期和时间戳类型1、日期有两种字符串字面值赋值支持到date类型的隐式转换Lv_datedate:=’22-mon-75’；或者Lv_datedate:=’22-mon-1975’；除了上面两种，任何其他字符串字面值都要求使用to_date内置SQL函数来覆盖格式掩码如lv_date_1date:=to_date(‘19750430’,’YYYYMMDD’);2、间隔间隔子类型允许将天的间隔表示秒，将年的间隔表示月份数Intervaldaytosecond数据类型的默认值在两个date相减时能起作用，只要在执行减法之前将其转化为timestamp，因为to_timestamp函数保留了date的精度，而该精度低于timestamp将天的间隔转换为秒的数据类型为intervalday/day(4或其他数)toseconddeclarelv_intervalintervaldaytosecond;lv_end_daydate:=sysdate;---sysdate与date对应lv_start_daydate:='18-4月-2012';beginlv_interval:=to_timestamp(lv_end_day)-to_timestamp(lv_start_day);dbms_output.put_line(lv_interval);end;结果-2014:56:54.000000PL/SQLproceduresuccessfullycompletedDate数据类型默认支持2位数字表示的天，timestamp的精度要求使用9位数字表示的天或者declarelv_intervalintervalday(9)tosecond;lv_end_daytimestamp:=systimestamp;---systimestamp与timestamp对应lv_start_daytimestamp:='18-4月-2012';beginlv_interval:=lv_end_day-lv_start_day;dbms_output.put_line(lv_interval);end;结果-00000294302:52:41.860000PL/SQLproceduresuccessfullycompleted将年的间隔转化为月份数的数据类型为intervalyeartomonth从一个日期中提取年用to_char(extract(yearfromlv_end_day))完整代码：declarelv_adate:='20-4月-2009';avarchar2(12);begina:=to_char(extract(yearfromlv_a));dbms_output.put_line(a);end;结果为：20093、时间戳（timestamp）Timestamp数据类型精度要比date数据类型精度高2．2.3数值类型可以将number数据类型隐性转化为intger类型，可能会丢失一些小数点Binary_float32位浮点数Binary_double是一个64位浮点数2.2.4复合变量类型SQLUDT用于保存一个数据结构2.3控制结构1、If语句，ifelsifelse2、case语句分为简单的case语句case…when…then…else…endcase(break隐式存在)与搜索型的case语句casewhen…then…else…endcase简单case语句可使用char、nchar、varchar2数据类型，而搜索case语句可以使用任意布尔表达式，搜索case语句不局限于等值匹配2.3.3、循环结构Loop循环退出要借助exit或exitwhen语句Forloop循环：分为范围循环和游标循环插入：关于游标（见E:\sas\sql\各类知识要点\游标）范围循环即foriin1..4loop游标循环1、隐式foriin(select语句)loop2、显式foriincur_v(游标)loop其中第2种要比第一种要有更好的可读性（以后用这种）wherecurrentof字句while循环while(...)loop内可含continue/goto语句Simple循环语句即利用隐式游标属性来进行循环判断如ifSQL%FOUNDthen...else...endif;2．4批量操作（见E:\sas\sql\各类知识要点\游标）批量处理是进行成批处理和大批量处理的默认选择本章小结：1、始终在执行块中进行赋值或初始化。除非局部变量被视为常量2、在实现任何局部块命名之前，始终未其定义前向引用规范，从而确保这些局部命名块能够互相调用3、应尽可能使用表集合。避免用varray，因为前者不受固定大小的限制。Varray通常需要更改大小限制，这将花费时间和金钱4、显式游标应尽可能使用形参5、应尽可能使用SQL数据类型作为函数的返回值。SQL数据类型不需要编写管道化表函数对其进行封装就可以在SQL中使用，并且数据类型总是可检测的，因为它是目录中的一个命名集合或结构本章的问题1、在程序块中至少包括一个null语句，否则会编译错误2、PL/SQL中必须定义一个record结构，因为该结构是仅适用于PL/SQL环境的变量3、for循环必须知道其范围ide上界，而游标集返回的行数设置了游标for循环的上边界4、可以使用bulkcollectinto语句讲一个完整游标或游标的某些行选择到一个记录结构集合中。可以使用limit关键字来限制行集5、在循环内部可以使用forall语句，但该语句没有结束块语句，所以需要endforall或endloop或endforallloop语句来结束forall语句。Chap3事物作用域3.1数据库ACID测试Atomicity原子性、consistency一致性、isolation隔离性、durability持久性3.2多版本并发控制MVCC（multiversionedconcurrencycontrol）使用数据库快照为用户提供数据库的永久内存副本3.33.3.1保存点、提交和事务回滚若有2个保存点，且回滚到了第一个保存点，则第二个保存点就不存在了3.3.2提交注意：数据字典作强制隐式的提交Commit命令中的注释将写入到dba_pc2_pending字典视图中必须具有forcetransaction或forceanytransaction系统权限才能强制提交Nowait选项不会验证对重做和归档日志文件的写入Work选项将会在所有commit语句中有效例1、标准commitUPDATEhr.employeesSETsalary=salary*1.03WHEREdepartment_idin(20,30,40);Commit或将commit改为COMMITWORKWRITEIMMEDIATEWAIT;例2、含有nowait和batch选项的commit使用nowait选项将不会验证对重做和归档日志的写入避免重写oracle的提交等待过程。这样做可能会使自己的事物陷入危险例3、强制commit语句并写入注释INSERTINTOhr.job_historyVALUES(100,'01-Jan-2000',trunc(sysdate),'AD_PRES',90);COMMITCOMMENT'In-doubttransactionforcedbyprocessxyzondate123';Commitforce‘2.33.192’;发出commitforce需要一个具有dba权限的账户。单引号（’）内的信息代表可疑的事物id。3.4DML锁定和隔离控制为了防止不可重复读取或影子读取设置事物级别为读取一致（在跟踪分布式事物时，设置事物语句命名将会非常有益）settransactionname可更好地监控长时间的事务。SETTRANSACTIONREADONLYNAME'DistributedtoNYC';--添加到该设置事物语句的’distributedtoNYC’注释将会保存在dba_pc2_pending字典视图中（http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10759/statements_10005.htm）SELECTproduct_id,warehouse_id,quantity_on_handFROMoe.inventories@nyc_001WHEREproduct_id=3246;COMMIT;锁定用户具有锁定自己所拥有的全部表的权限，如果想要锁定另一个模式中的表，则必须具有lockanytable系统权限B．1在游标内使用forupdateDECLARECURSORc_employeesISSELECT*FROMhr.employeesFORUPDATEWAIT10;--该行锁定c_employees游标中所引用的行。其中wait10表示，若游标所引用的行已经存在锁定，则等待10秒，若前一次锁定持续时间大于10秒，则此次更新将会被取消，也可以用nowait关键字，表示存在锁定的情况下立即返回事物BEGINFORr_employeesINc_employeesLOOPUPDATEhr.employeesSETsalary=salary*1.025WHERECURRENTOFC_employees;--该语句引用游标中的最新行，最后锁定会一直持续，直至发出commit或rollback命令ENDLOOP;END;B．2发起locktable语句LOCKTABLEhr.employeesINEXCLUSIVEMODENOWAIT;UPDATEhr.employeesSETsalary=salary*1.025WHEREdepartment_id=10;COMMIT;与forupdate语句不同，locktable命令将会锁定表中的每一行。此外，短语inexclusivemode将会锁定除select语句之外的全部操作有效的锁定模式有：=1\*GB3①rowexclusive模式，限制最少的锁定级别，允许行共享，并防止用户锁定整个表或锁定到rowshare模式=2\*GB3②rowshare模式，除了没有独占共享的限制之外，该模式等同于rowexclusive=3\*GB3③share模式，允许select但是不允许其他更新=4\*GB3④sharerowexclusive模式，等同于share模式，但是同时禁止用户锁定到share模式=5\*GB3⑤exclusive模式限制最多的级别，阻止除select之外的所有DML操作改善事物性能利用批量DML操作的功能3.5调用者和定义者权限以程序所有者的身份来保留程序执行的全部权限：在命名程序头使用authiddefiner关键字例1发出定义者权限CREATEORREPLACEFUNCTIONhr.quarterly_sales(pi_employee_idinnumber,pi_quarterindate)AUTHIDDEFINERAS...declarativecodegoeshere...BEGIN...dosomethinghereandreturn...EXCEPTION...handletheexceptionhereandreturn...END;例2.发出调用者权限以程序调用者的身份来调用程序，用关键字authidcurrent_userCREATEORREPLACEFUNCTIONhr.give_raise(pi_employee_idinnumber)AUTHIDCURRENT_USERAS...declarativecodegoeshere...BEGIN...dosomethinghereandreturn...EXCEPTION...handletheexceptionhereandreturn...END;本章小结1、确保自己有一个良好的备份。仅仅激活像rman这样的备份程序是不够的2、使用简洁而有意义的名称来命名保存点3、强制可疑事务要求深入了解oracle系统更改号（systemchangenumber,SCN）和数据字典。只有当事务无法再次尝试时才使用commitforce命令4、使用settransactionname参数对分布式事务命名测验小结原子性意味着一个事务全部写入到永久存储中或任何部分都没有写入到永久存储中一致性意味着一个并发多用户系统中为所有事务分配相同的服务cpu时间量和内存隔离性意味着一个事务的任何部分在事务完成并提交之前都不可见持久性意味着事务在完成之后写入到冗余磁盘阵列Undo_retention参数（还不是很熟悉），设置语句在内存中运行的时间长度Chap4错误管理数据库强化的两种 方法 快递客服问题件处理详细方法山木方法pdf计算方法pdf华与华方法下载八字理论方法下载 是使用DBMS_ASSERT包和绑定变量。DBMS_ASSERT包用于验证输入参数的形式是否正确，以及模式和对象名称是否实际存在。绑定变量防止匿名PL/SQL块的嵌套4.1错误类型4.1.1编译时错误（即语法上有错误）很多程序员基于编写较大的代码块，而不愿意停下来执行临时编译。一次性调试很多错误可能会花费大量时间查看不重要的信息，尤其当错误栈中打印出许多不必要的信息时。一种更高效的编程方法是按逻辑分组来编写每个程序块，在函数单元完成后执行编译。例：循环式编码方法可以避免不必要的调试4.1.2语义错误语义错误只在程序内部发生的逻辑错误。如加减乘除的错误Oracle提供的错误条件Oracle提供了超过2300页的预定义错误条件，程序员可以参考他们来编写或直接调用Oraclesqlcode和sqlerrmSQLERRM是系统内置变量保存了当前错误的详细信息。Oracledatabaseserrormessages11gguide中的每个错误都有唯一的代号和消息。与错误条件相关联的数字值称为sqlcode。可以使用pragmaexception_init关键字将变量与sqlcode代号关联。当希望在发生oracle错误的情况下执行特定任务时，该功能特别有用。例：用pragmaexception_init捕获系统错误CREATETABLEhr.emergency_contact(employee_idNUMBER,full_nameVARCHAR2(50),phone_homeVARCHAR2(15),phone_cellVARCHAR2(15),phone_pagerVARCHAR2(15));ALTERTABLEemergency_contactADD(CONSTRAINTec_employee_id_unkUNIQUE(employee_id,full_name));BEGINFORiIN1..2LOOP--此处因为循环两次，导致插入的数据一样，所以会导致违反unique约束--INSERTINTOemergency_contactVALUES(1,'JaneDoe','+1.123.456.7890',NULL,'+1.123.567.8901');ENDLOOP;END;输出ORA-00001:违反唯一约束条件(SCOTT.EC_EMPLOYEE_ID_UNK)ORA-06512:在line4用pragmaexception_init来捕获（为什么要捕获错误，因为发现错误时oracle则不执行其他代码了，为了对其执行额外的任务，则就需要进行捕获错误，此处当程序遇到ORA-00001错误时，运行执行dbms，而不会立即中断程序）declareunique_constraintexception;pragmaexception_init(unique_constraint,-00001);BEGINFORiIN1..2LOOPINSERTINTOemergency_contactVALUES(1,'JaneDoe','+1.123.456.7890',NULL,'+1.123.567.8901');ENDLOOP;exceptionwhenunique_constraintthendbms_output.put_line('o,youthrowtheunique_constrainterror');END;输出o,youthrowtheunique_constrainterrorPL/SQLproceduresuccessfullycompleted异常作用域Pl/sql块的连续性通过在匿名子块中封装可能的错误代码来维护。封装技术有：标准子块封装、循环子块封装、保存点重定向、goto跳转封装子块DECLAREln_parentNUMBER;ln_child_level1NUMBER;ln_child_level2NUMBER;ln_random_0_1NUMBER;BEGINBEGINDBMS_OUTPUT.PUT_LINE('MadeitpastParent.');ln_random_0_1:=ROUND(DBMS_RANDOM.VALUE(0,1));ln_parent:=1/ln_random_0_1;BEGINDBMS_OUTPUT.PUT_LINE('MadeitpastChildLevel1.');ln_random_0_1:=ROUND(DBMS_RANDOM.VALUE(0,1));ln_child_level1:=1/ln_random_0_1;BEGINDBMS_OUTPUT.PUT_LINE('MadeitpastChildLevel2.');ln_random_0_1:=ROUND(DBMS_RANDOM.VALUE(0,1));ln_child_level2:=1/ln_random_0_1;END;END;END;EXCEPTIONWHENOTHERSTHENDBMS_OUTPUT.PUT_LINE(SQLERRM);END;在循环内封装DECLAREln_employee_idNUMBER;ln_order_totalNUMBER;CURSORc_employeeISSELECT*FROMhr.employees;BEGINFORr_employeeINc_employeeLOOPln_employee_id:=r_employee.employee_id;DECLAREno_salesman_foundexception;BEGINSELECTSUM(order_total)INTOln_order_totalFROMoe.ordersWHEREsales_rep_id=ln_employee_id;IFln_order_totalISNOTNULLTHENDBMS_OUTPUT.PUT_LINE(ln_order_total);ELSERAISEno_salesman_found;ENDIF;EXCEPTIONWHENno_salesman_foundTHENDBMS_OUTPUT.PUT_LINE('CaughtNO_SALESMAN_FOUND');END;ENDLOOP;EXCEPTIONWHENOTHERSTHENDBMS_OUTPUT.PUT_LINE(SQLERRM);END;保存点异常重试定义自定义错误条件为了定义自己的错误条件，首先必须创建一个异常变量。最后在代码的异常块内必须说明这个错误的处理方法例：DECLAREln_order_totalnumber;ln_promotion_idnumber:=1;ln_order_countnumber;no_promo_foundexception;BEGINSELECTCOUNT(*)INTOln_order_countFROMoe.ordersWHEREpromotion_id=ln_promotion_id;IFln_order_count>0THENSELECTSUM(order_total)INTOln_order_totalFROMoe.ordersWHEREpromotion_id=ln_promotion_id;ELSEraiseno_promo_found;ENDIF;EXCEPTIONWHENno_promo_foundTHENDBMS_OUTPUT.PUT_LINE('NoSalesfoundforPromotion:'||ln_promotion_id);END;开发人员总是使用这个逻辑，问题在于oracle已经使用no_data_found条件处理这种情况。下面对其精简：DECLAREln_order_totalnumber;ln_promotion_idnumber:=1;BEGINSELECTorder_totalINTOln_order_totalFROMoe.ordersWHEREpromotion_id=ln_promotion_id;IFln_order_count>0THENSELECTSUM(order_total)INTOln_order_totalFROMoe.ordersWHEREpromotion_id=ln_promotion_id;EXCEPTIONWHENNO_DATA_FOUNDTHENDBMS_OUTPUT.PUT_LINE('NoSalesfoundforPromotion:'||ln_promotion_id);END;注：掌握并适当利用内置错误，以简化自己的代码Raise_application_error过程是DBMS_STANDARD内置包的组成成分，该过程允许生成ORA-相关信息，而不需要首先声明异常变量或使用exception_init指令（可以简化代码）例使用Raise_application_error捕获错误条件DECLARECURSORc_rentalISSELECTc.member_id,c.first_name||''||c.last_namefull_name,t.transaction_amountFROMvideo_store.transactiont,video_store.rentalr,video_store.contactcWHEREr.rental_id=t.rental_idANDr.customer_id=c.contact_id;BEGINFORr_rentalINc_rentalLOOPIFr_rental.transaction_amount>75THENRAISE_APPLICATION_ERROR(-20001,'Notransactionmaybemorethan$75',TRUE);ENDIF;ENDLOOP;END;注：上述程序并未创建异常变量。此外，将用户定义20001SQLCODE与错误消息关联。其中的true参数通知过程在错误栈中包含该错误。第三个参数的默认值是false，通知RAISE_APPLICATION_ERROR过程清除错误栈，并只打印错误消息。4.2PL/SQL工具用户编写的许多PL/SQL应用程序将需要花费一定的时间来执行，一种最常见但却存在问题的技术是在提交时使用时间戳。更新的时间戳列标记哪些行已经被程序修改。然而问题在于，提交本身基于需呀耗费不少时间，将其放在应用程序的中间会引发如下问题：减慢应用程序导致oracle服务器上的内存和cpu占用率突然增高生成大量的重做日志信息我们需要的是一种记录程序活动而不需要额外消耗太多系统资源的方法。这种输出也称为程序工具（利用内置程序结合实际需要写的代码）本章小结：1、如果PL/SQL程序在创建时没有进行适当的错误处理和工具化，则开发人员需要花很多时间来维护并反复检查程序结果。通过工具化代码并提供适当的错误管理结果，将会节省大量的维护和排错时间。2、运行错误消息回传到其主调用应用程序，而不要使用whenothers短语3、使用保存点回滚部分完成的事务，可以编码产生孤立数据，从而维护数据完整性。4、避免使用goto语句5、掌握并适当采用内置错误，以简化自己的代码6、尽可能工具化自己的pl/sql程序测验小结：DBMS_ASSET包允许验证输入参数来过滤传入的web参数PLS-错误是PL-SQl错误ORA-错误与常规数据库错误和SQL有关RAISE_APPLICATION_ERROR允许引发一个自定义异常，但是不等价于RAISE语句。RAISE语句允许调用申明块中预定义的EXCEPTION变量pragmaexception_init允许将一个默认错误号映射到用户定义变量，然后可以使用RAISE语句引发该错误。SQLCODE对于PL/SQL块中引入的错误返回错误代码号使用在-20000和-20999范围之间的错误号才能引发RAISE_APPLICATION_ERROR调用Chap5函数5.1函数构架5.1.1传值函数传值函数在调用时接受值，在完成时返回单个值。形参只有in模式5.1.2传址函数在调用传址函数时，至少发送一个或多个局部变量引用作为实参。形参有三种可能模式（in、inout、out）传址函数并不像传值函数那样将形参全部消耗，虽然in模式参数被消耗，但inout模式变量通常会在更改状态后返回5.1.3函数模型选择当希望消耗输入并产生某种结果时，应该将函数实现为传值函数当需要验证客户端或web交互程序的完成情况或返回结果时，应该使用传址模式。5.1.5调用参数说明createorreplacefunctionthree(anumber:=0,bnumber:=0,cnumber:=1)returnnumberisbeginreturn(a-b)/c;end;=1\*GB3①位置参数说明实际调用参数依次映射到形参，但是必须为列表中的每个形参提供实参或调用值，当希望跳过某个形参时，可以赋值为nullbegindbms_output.put_line(three(3,4,5));end;结果为-.2PL/SQLproceduresuccessfullycompleted=2\*GB3②命名参数说明实际调用参数不需要映射到形参序列begindbms_output.put_line(three(c=>4,b=>3,a=>5));end;=3\*GB3③混合参数说明位置参数必须位于命名参数之前。且在第一个位置参数之后，只能省略可选参数。begindbms_output.put_line(three(8,c=>4));end;第一个参数是形参a，所以结果是8减去0并除以4结果：2PL/SQLproceduresuccessfullycompleted5.2函数开发限制：PL/SQL函数不能包含“数据操作语言”（DML）语言，或调用另一个包含DML语句的pl/sql单元，否则将会引发ORA-14551异常。该错误表示不能再查询内部执行DML操作。一个问题：虽然可以再SQL语句或PL/SQL块内部调用不带参数列表并且不适用括号的函数，但是在CALL语句中调用相同程序时必须使用空括号。例：利用之前的three函数variableanumber;callthree()into:a;--没有括号则会出错结果：Methodcalleda---------0select:afromdual;结果：:A----------0a---------0当然，也可以通过使用SQl来调用该函数，以节约时间（加不加括号均可以）SQL>selectthree()fromdual;THREE()—---良好的编程需要括号----------0SQL>selectthreefromdual;THREE----------05.2.1确定性字句确定性函数在returnnumber后面加上关键字deterministic(见各类知识要点)能够确保对于任何输入都可以相同的工作方式工作(插入：设置输出列格式Columnaformat99,999,90Select:basafromdual;)（Withcas(select1asa1,0asa2,1asa3fromdual)Selectthree(a1,a2,a3)asafromc;--c为虚拟表或试图）结果为A----------1完整例子：CREATEORREPLACEFUNCTIONpv(future_valueNUMBER,periodsNUMBER,interestNUMBER)RETURNNUMBERDETERMINISTICISBEGINRETURNfuture_value/((1+interest/100)**periods);ENDpv;/调用：VARIABLEresultNUMBERCALLpv(10000,5,6)INTO:result;COLUMNmoney_todayFORMAT99,999.90SELECT:resultASmoney_todayFROMdual;/WITHdata_setAS(SELECT235000ASprincipal,30ASyears,5.875ASinterestFROMdual)SELECTpv(principal,years,interest)ASmoney_todayFROMdata_set;5.2.2parallel_enable子句Createorreplacefunctionmerge(last_namevarchar2,First_namevarchar2,Middile_namevarchar2)Returnvarchar2parallel_enableisBeginReturnlast_name||’,’||first_name||’,’||middle_name;End;parallel_enable字句允许在查询优化期间将函数标识为并行安全。虽然SQl引擎可以完成这些决策，但是将函数标识为安全可以节省几毫秒的时间可以用如下方法调用此程序Selectmerge(c.last_name,c.first_name,c.middle_name)ascustermerfromcontactc;5.2.3管线表字句管线表（pipelinedtable）字句的功能非常强大，管线表函数允许将SQL或PL/SQL记录数据类型的集合转换为SQL兼容的聚合表。然后可以使用TABLE函数通过SQL语句访问这些聚合表第11章中。SQl对象不能作为引用游标赋值的目标，该限制不仅适用于结构，也适用于结构集合。只有两种情况必须使用PL/SQL数据类型和管线表函数。一是 设计 领导形象设计圆作业设计ao工艺污水处理厂设计附属工程施工组织设计清扫机器人结构设计 要求返回引用游标的内容到聚合表中。另一种是维护现有的代码库，其中大量引用了管线表函数。创建传统管线表函数的步骤：在包规范中创建一个记录在包规范中创建一个基于记录的嵌套表创建结构之后，有两种方式定义管线表函数：可以再包主体内部创建管线表函数，或将其创建为模式级函数（即与包一个级别，不需在包体内部，也就引用引用时不需要加包名称）完整例子：先创建包规范CREATEORREPLACEPACKAGEpipelining_libraryIS--Createarecordstructure.TYPEcommon_lookup_recordISRECORD(common_lookup_idNUMBER,common_lookup_typeVARCHAR2(30),common_lookup_meaningVARCHAR2(255));--CreateaPL/SQLcollectiontype.TYPEcommon_lookup_tableISTABLEOFcommon_lookup_record;ENDpipelining_library;再创建管线表函数--Createapipelinedfunctionforarowofdata.CREATEORREPLACEFUNCTIONget_common_lookup_record_table(pv_table_nameVARCHAR2,pv_column_nameVARCHAR2)RETURNpipelining_library.common_lookup_tablePIPELINEDIS--Declarealocalvariables.lv_counterINTEGER:=1;lv_tablePIPELINING_LIBRARY.COMMON_LOOKUP_TABLE:=pipelining_library.common_lookup_table();-----定义lv_table（嵌套表）为空集合--Defineadynamiccursorthattakestwoformalparameters.CURSORc(table_name_inVARCHAR2,table_column_name_inVARCHAR2)ISSELECTcommon_lookup_id,common_lookup_type,common_lookup_meaningFROMcommon_lookupWHEREcommon_lookup_table=UPPER(table_name_in)ANDcommon_lookup_column=UPPER(table_column_name_in);-----之前都是函数的申明块BEGINFORiINc(pv_table_name,pv_column_name)LOOPlv_table.EXTEND;--对前面的lv_table进行赋值时，要先在内存中为其分配空间，此行是对其进行扩展，lv_table(lv_counter):=i;--此处赋值PIPEROW(lv_table(lv_counter));---？难道是输出lv_counter:=lv_counter+1;ENDLOOP;END;现在对该函数进行测试，COLUMNcommon_lookup_idFORMAT9999HEADING"ID"COLUMNcommon_lookup_typeFORMATA16HEADING"LookupType"COLUMNcommon_lookup_meaningFORMATA30HEADING"LookupMeaning"--Querythevaluesfromthetable.SELECT*FROMTABLE(get_common_lookup_record_table('ITEM','ITEM_TYPE'));--变为聚合表再调用该函数--ShowinghowtoleverageapipelinedfunctionreturninaPL/SQLcontext.DECLARECURSORcv_sampleISSELECT*FROMTABLE(get_common_lookup_record_table('ITEM','ITEM_TYPE'));BEGINFORiINcv_sampleLOOPdbms_output.put('['||i.common_lookup_id||']');dbms_output.put('['||i.common_lookup_type||']');dbms_output.put_line('['||i.common_lookup_meaning||']');ENDLOOP;END;虽然使用管线表函数能实现上述功能，但不是最有办法。集合比管线表函数更优。以下情况必须使用管线表函数返回引用游标的内容到某个记录结构或记录结构集合（嵌套表）中处理不能重新编写的现有代码建议在新编写的代码中使用SQL数据类型，当希望返回一个记录集合时，使用管线表函数。记录集合（嵌套表）可以视为目录表或试图，但更常见的情况是将其视为对象的子集或超集。下面的采用了一种非目录记录结构：如何实现一个在SQL中可调用的管线表函数的等价物。（最佳实践：作为管线表函数的替代方法，可以将记录结构的集合，定义为SQL数据类型，并且使用SQL数据类型作为返回数据类型）第一步、创建一个SQL记录类型CreateorreplaceTYPEcommon_lookup_objectISobject(common_lookup_idNUMBER,common_lookup_typeVARCHAR2(30),common_lookup_meaningVARCHAR2(255));/接下来创建一个SQL对象集合类型（嵌套表），这实际上是一种记录类型CreateorreplaceTYPEcommon_lookup_object_tableIstableofcommon_lookup_object;/5.2.4result_cache结果缓存结果缓存是oracle11g数据库的一项新功能。该功能允许定义一个函数，并在缓存中保存结果集以便于重复使用。与简单的将包固定在内存中相比，这种方法在提高执行速度方面是一项重大的改进。这种将直接查询结果缓存，而不是使用第二个函数、第三个函数等等来查询源表或视图。它减少了计算时间，并改善了数据库吞吐性能。但结果缓存也存在一些局限性，该函数只能返回标量或标量变量集合结果缓存最适合于集合操作首先定义一个可变长字符串集合Createorreplacetypelookupistableofvarchar2(325);/现在可以定义一个缓存函数SQL>CREATEORREPLACEFUNCTIONget_common_lookup2(table_nameVARCHAR2,column_nameVARCHAR2)RETURNLOOKUP3RESULT_CACHERELIES_ON(common_lookup)IS4--Alocalvariableoftheuser-definedscalarcollectiontype.5lookupsLOOKUP;67--Acursortoconcatenatethecolumnsintoonestringwithadelimiter.8CURSORc(table_name_inVARCHAR2,table_column_name_inVARCHAR2)IS9SELECTcommon_lookup_id||'|'10||common_lookup_type||'|'11||common_lookup_meaning12FROMcommon_lookup13WHEREcommon_lookup_table=UPPER(table_name_in)14ANDcommon_lookup_column=UPPER(table_column_name_in);15BEGIN16OPENc(table_name,column_name);17LOOP18FETCHcBULKCOLLECTINTOlookups;19EXITWHENc%NOTFOUND;20ENDLOOP;21RETURNlookups;--输出结果，与第2、5行对应22ENDget_common_lookup;23/第3行的RELIES_ON字句可以确保底层数据的任何改变将会清除缓存。第18行以批量读取取代了管线表函数的逐行读取方法（所以不需要个扩展了）注：缓存函数输出与管线表函数输出语句不一样5.2.5系统引用游标统引用游标是仅支持PL/SQL的结构。强类型引用游标绑定到某个表或视图、而弱类型引用游标则可以接受查询所得到的任何记录结构。SYS_REFCURSOR是默认的弱类型系统引用游标可以使用如下方法定义一个绑定到item表的强类型引用游标Typeaisrefcursorreturnitem%rowtype弱类型Typeaisrefcursor弱类型引用游标没什么意义，当需要弱类型引用游标时，应该尽可能使用SYS_REFCURSOR，因为该游标是预定义的，且普遍适用。例：如何编写个函数，使其返回一个弱类型的默认系统引用游标SQL>Createorreplacefunctionget_full_titles2ReturnSYS_REFCURSORis3lv_titleSYS_REF