所需介质与相关pl sql实战教程

第一 PL/SQL程序设计简SQL与PL/SQLPL/SQL可用的SQLPL/SQL只有程序设计语言才能用于应用软件的开发。PL/SQL是一种高级数据库程序设计语言，PL/SQLProcedureLanguage&StructuredQueryLanguage的缩写。ORACLESQLANSI(AmericannationalStandardsInstitute)ISO92(InternationalStPL/SQL内容。 PL/SQLPL/SQLPL/SQL。对于客户端来说，PL/SQL可以嵌PL/SQL程序运行。 PL/SQL L/SQL错误，或处理系统错误与自定义错误，以增强应用程序的健壮性。 DBA还是应用开发人员都具有重要作用。PL/SQL是ORACLE系统的语言现在ORACLE的许多部件都是由PL/SQL写成。PL/SQLSQL语句有：PL/SQLSQLDMLDDL ORACLEPL/SQLPL/SQL程序进行解释时，同时对在其所使用的PL/SQLSQL*PLUSPL/SQLPL/SQLORACLE的开发工具中使用(如：SQLDeveloperProcedureBuilder等)。PL/SQLPowerBuilder等都可以PL/SQL过程。 LEORACLEPL/SQL中的过程性语SQL语句发送给数据库服务器来执行。再将结果返回给执行端。ORACLEPL/SQLPL/SQLPL/SQLPL/SQLPL/SQL使用LOBBINDPL/SQL表BOOLEAN PL/SQLPL/SQL程序由三个块组成，即部分、执行部分、异常处理部分?1-过程及SQL语 ,即程序的主要部234567命名块（named）：是带有名称的块，这个名称就是 PL/SQLPL/SQL子块可以位于PL/SQL中的PL/SQL PL/SQLSQL标识符名过30字符SQLenameenamevarchar2(20):='EricHu';DELETEFROMscott.empWHEREename=ename;12345变量命名在PL/SQL中有特别的讲究建议在系统的设计阶段就要求所有编程人员共同遵守一 表SQL*Plus PL/SQLPL/SQL中的变量类型的合法使用列表：说明范围可选,确省小数NUMBER整数NUMBER整数NUMBERNUMBERNUMBER整数,integerSmall0-747121112311.?12Row_id3 45INSERTINTOscott.deptVALUES(90,财务室海口6RETURNINGrowid,7INTOrow_id,89UES子句插入数据时，RETURNING字句还可将列表达式、ROWIDREF值返回到输出变RETURNING子句是应注意以下几点限制：LONG2.?12Row_id3info45UPDATEdeptSETdeptno=100WHEREDNAME='财务室RETURNINGRETURNINGrowid,dname||':'||to_char(deptno)||':'||locINTOrow_id,info;6789RETURNINGUPDATE语句修改单行数据时，RETURNINGROWIDREF值，以及行中被修改列的列表达式，并可将他RETURNINGINSERT语句中对RETURNING子句的限制相同。?12Row_id3info45DELETEdeptWHEREDNAME='6RETURNINGrowid,7INTOrow_id,89TURNINGINSERTRETURNING子句的限制相同。ORACLEPL/SQL中除了提供象前面介绍的各种类型外,还提供一种称为复合类型的类记录类型类似于C语言中的结构数据类型，它把逻辑相关的、分离的、基本数据类型的变量组成一个整体起来，它必须包括至少一个标量型或RECORD数据类型的成员，称作/SQLRECORD的域(FIELD)[:=[:=default_value][NOT[:=default_value[:=default_value[NOT[NOTv1v2vnTYPErecord_nameIS123454rec_book.Namerec_book.Name rec_book.InfoPL/SQL编程DBMS_OUTPUT.PUT_LINE(rec_book.Name||''||rec_book.Info);TYPEtest_recISNameVARCHAR2(30)NOTNULL:='InfoVARCHAR2(100));rec_book123456789SELECT语句对记录变量进行赋值,只要保证记录字段与查询结果列表中的字段相5?12hr.employees3TYPERECORD_TYPE_EMPLOYEESIS4 5 6 7--一个该记录数据类型的记录变8v_emp_record9SELECTfirst_name,hire_date,job_idINTOFROMWHEREemployee_id=DBMS_OUTPUT.PUT_LINE('雇员名称 雇佣日期 PL/SQLVARRAY。TYPETYPEvarray_nameISVARRAY(size)OFelement_type[NOT1varray_name是VARRAY数据类型的名称，size是下整数，表示可容纳的成员的最大数量，每个成员的数据类型是element_type默认成员可以取空值否则需要使用NOTNULL加以限制。对于VARRAY数据类型来说，必须经过三个步骤，分别是：定义、 6?123TYPEreg_varray_typeISVARRAY(5)OF4--一个该VARRAY数据类型的变56789v_reg_varray:=('中国','','英国','','法国v_reg_varray(5)法国使用所的数据库列的数据类型可以不必知道所的数据库列的数据类型可以实时改变，容易保持一致，也不用修改----用%TYPETYPET_RecordIST_noemp.empno%TYPE,T_nameemp.ename%TYPE,T_salemp.sal%TYPE); SELECTempno,ename,salINTOv_empFROMempWHERE(TO_CHAR(v_emp.t_no)||''||v_emp.t_name||''||TO_CHAR(v_emp.t_sal));123456789v_empnov_empnoemp.empno%TYPE:=&no;Typet_recordisrecord(v_nameemp.ename%TYPE, v_dateemp.hiredate%TYPE);Rect_record;SELECTename,sal,hiredateINTORecFROMempWHEREempno=v_empno;123456789PL/SQL提供%ROWTYPE操作符,返回一个记录类型,其数据类型和数据库表的数据结所的数据库中列的个数和数据类型可以不必知道所的数据库中列的个数和数据类型可以实时改变容易保持一致也不用修改PL/SQL程序。?1v_empnoemp.empno%TYPE:=&no;recemp%ROWTYPE;SELECT*INTOrecFROMempWHEREempno=v_empno;DBMS_OUTPUT.PUT_LINE(':'||rec.ename||'间工资工作时234567ORACLE提供了LOB(LargeOBject)类型，用于大的数据对象的类型。ORACLE目BFILE,BLOB,CLOB及NCLOB类型。BFILE 大的字符数据类型每个变量大字符对象的位置该位置指到大字符数据块。大的NCHAR字符数据类型。每个变量大字符对象的位置，该位置指到大PL/SQL程序中可以使用绑定变量作为他们将要使用的其它变量为了在PL/SQL环境中绑定变量使用命令VARIABLE例如?1VARIABLEreturn_code2VARIABLEreturn_msgSQL*PlusPRINT?1PRINT2PRINTVARIABLEresultVARIABLEresultNUMBER;SELECT(sal*10)+nvl(comm,0)INTO:resultFROMempWHEREempno=7844;--PRINT1234567PL/SQL?1TYPEtable_nameISTABLEOFelement_type[NOT2INDEXBY[BINARY_INTEGER|PLS_INTEGER|关键字INDEXBY表示创建一个主键索引，以便记录表变量中的特定行返回下标为n返回下标为nTRIM(n)n个成员DELETE(n)n个成员DELETE(m,n)：删除从n到mEXTEND(n)nnullTYPETYPEdept_table_typeISTABLEdept%ROWTYPEINDEXBYv_countnumber(2):=4;FORintIN1..v_countSELECT*INTOmy_dname_table(int)FROMdeptWHEREdeptno=int*10;ENDLOOP;FORintINmy_dname_table.FIRST..my_dname_table.LASTDBMS_OUTPUT.PUT_LINE('Departmentname:'||my_dname_table(int).dname);ENDLOOP;123456789TYPEreg_table_typeISTABLEOFvarchar2(25)INDEXBYBINARY_INTEGER;1234v_reg_table(1):=v_reg_table(2):='Americas';v_reg_table(3):='Asia';v_reg_table(4):='MiddleEastandAfrica';v_reg_table(5):='NULL';(((56789 v_reg_tableTYPEemp_table_typeISTABLEOFemployees%ROWTYPEINDEXBYBINARY_INTEGER; SELECTfirst_name,hire_date,job_idFROMemployeesWHEREemployee_id=177;SELECTfirst_name,hire_date,job_idFROMemployeesWHEREemployee_id=178;||'||'||'||'123456789 =<>,!=,~=,<>+-*/ISBETWEEN取返,ISNOTNULLNOT variablevariable:=expression1variablePL/SQL变量,expressionPL/SQL表达式 空值加数字仍是空值：NULL+<数字>=空值加（连接）字符，结果为字符：NULL||<字符串>=<字符串 BOOLEANTRUE,FALSENULL?12bDone34bDone:=5WHILENOTbDone67END8 SELECTSELECT语句就赋值一次，一般要SELECT中的列名要一一对应。如： emp.empno%TYPE:=7788;emp_nameemp.ename%TYPE; SELECTename,NVL(sal,0)+NVL(comm,0)INTOemp_name,wagesFROMempWHEREempno=emp_id; 123456789 CHARTO_NUMBERv_totalv_total:=TO_NUMBER('100.0')+1NUMBERTO_CHARmmTO_CHAR('123.45')||元1TO_DATE函数可以实现v_datev_date:=1TO_CHAR1 它高级语言类似，PL/SQL的变量作用范围特点是： 变量的作用范围是在你所的程序单元（块、子程序、包）内。即从变量一个变量（标识）只能在你所的块内是可见的 当一个变量超出了作用范围，PL/SQL引擎就释放用来存放该变量的空间（因为?12Emess3456V178SELECTempnoINTOv1FROMempWHERELOWER(job)='9WhenTOO_MANY_ROWSDBMS_OUTPUT.PUT_LINE('MorethanoneV1SELECTempnoINTOv1FROMempWHEREWhenTOO_MANY_ROWSDBMS_OUTPUT.PUT_LINE('MorethanoneWhenothers ‘-‘(减号)PL/SQL–V_SalV_SalNUMBER(12,2);--1使用 ?12/*文件名： 3/*作者： 4/*时间：2011-5- 5提示：PL/SQL程序，一般系统自动将程序头部的注释去掉。只有在PROCEDURE之后的注释才被保留；另外程序中的空行也自动被去掉。/*/*作者： /*时间：2011-5- v_enameVARCHAR2(20):='Bill'; NUMBER(7,2)v_deptnoNUMBER(2):=v_empnoNUMBER(4):=8888;INSERTINTOemp(empno,ename,JOB,sal,deptno,hiredate)VALUES(v_empno,v_ename,'Manager',v_sal,v_deptno,/*/*说明123456789 /*/*作者： /*时间：2011-5- v_enameVARCHAR2(20):='Bill'; NUMBER(7,2)v_deptnoNUMBER(2):=v_empnoNUMBER(4):=8888;INSERTINTOemp(empno,ename,JOB,sal,deptno,hiredate)VALUES(v_empno,v_ename,‘Manager’,v_sal,v_deptno,TO_DATE(’1954.06.09’,’yyyy.mm.dd’));v_empnonumber(4):=8888;DELETEFROMempWHEREempno=v_empno;/*/*说明123456789CASENULLPL/SQL的流程控制语句,包括如下三类控制语句:IF循环语句:LOOP语句,EXIT顺序语句:GOTO语句,NULL IF布尔表达式THENPL/SQLSQL语句ENDIF;IF布尔表达式THENPL/SQLSQL语句ENDIF布尔表达式THENPL/SQLSQL语句ELSIF其它布尔表达式ELSIF其它布尔表达式END提示:ELSIFv_empnoemployees.employee_id%TYPE:=&empno;V_salaryemployees.salary%TYPE;mentSELECTsalaryINTOv_salaryFROMemployeesWHEREemployee_id=v_empno;IFv_salary<1500ment太少了,加点吧ELSIFv_salary<3000ment多了点,少点吧ment没有薪水ENDDBMSOUTPUT.PUTLINE(VwhennodatafoundthenwhenothersDBMS_OUTPUT.PUT_LINE(sqlcode||'---'||sqlerrm);v_first_nameVARCHAR2(20);v_salaryNUMBER(7,2);SELECTfirst_name,salaryINTOv_first_name,v_salaryFROMemployeesWHEREemployeeid=&empid;IFv_salary<10000THENIF10000<=v_salaryANDv_salary<20000THENDBMS_OUTPUT.PUT_LINE('1000020000之间');ENDIF;ENDIF;v_first_nameVARCHAR2(20);v_hire_dateDATE;v_bonusNUMBER(6,2);SELECTfirst_name,hire_dateINTOv_first_name,v_hire_dateFROMWHEREWHEREemployee_id=IFv_hire_dateTO_DATE('01-1月-90'THENv_bonus:=800;ELSIFv_hire_dateTO_DATE('01-1月-88'THENv_bonus:=1600;v_bonus:=2400;ENDIF;CASEWHEN1WHEN2WHENn[ELSE条件表达式结果]WHEN1WHEN2WHENnn[ELSE语句段]Vgradechar(1):=UPPER('&pgrade');VappraisalVARCHAR2(20);V_appraisal:=CASEWHEN'A'THEN'Excellent'WHEN'B'THEN'VeryWHENWHEN'C'THENELSE'Nosuchgrade'DBMS_OUTPUT.PUT_LINE('Grade:'||v_grade||'Appraisal:'||v_appraisal);v_first_nameemployees.first_name%TYPE;v_job_idemployees.job_id%TYPE;v_salaryemployees.salary%TYPE;v_sal_raiseNUMBER(3,2);SELECTfirst_name,job_id,salaryINTOv_first_name,v_job_id,v_salaryFROMemployeesWHEREemployee_id=&emp_id;WHENv_job_id='PU_CLERK'IFvsalary<3000THENvsalraise:=.08;ELSEvsalraise:=.07;ENDWHENv_job_id='SH_CLERK'IFv_salary<4000THENv_sal_raise:=.06;ELSEv_sal_raise:=.05;ENDWHENv_job_id='ST_CLERK'IFv_salary<3500THENv_sal_raise:=.04;ELSEv_sal_raise:=.03;ENDIF;ENDCASE; EXITWHEN条件语句条件满足，退出循环语句ENDLOOP;intNUMBER(2):=0;int:=int+EXITWHENint=10;ENDLOOP;WHILEWHILEWHILE布尔表达式ENDLOOP;xNUMBER:=1;WHILEx<=10LOOPx:=x+1;ENDLOOP;FORINREVERSEENDLOOP[循 EXIT退出循环。FORFORintin1..10DBMS_OUTPUT.PUT_LINE('int的当前值为'||int);ENDLOOP;V_counterNUMBER:=INSERTINTOtemptable(numcol)VALUES(vcounter);FORvcounterIN20..25LOOPINSERTINTOtemptable(numcol)VALUES(vcounter);ENDLOOP;INSERTINTOtemp_table(num_col)VALUES(v_counter);FORv_counterINREVERSE20..25LOOPINSERTINTOtemp_table(num_col)VALUES(v_counter);ENDLOOP;ENDv_jobidsJOBIDS_VARRAY;-- 一个具有JOBIDS_VARRAY数据类型的变量v_howmanyNUMBER;-- vjobids:=jobidsvarray('FIACCOUNT','FIMGR','STCLERK','STFORiINv_jobids.FIRST..v_jobids.LASTSELECTcount(*)INTOv_howmanyFROMemployeesWHEREjob_id='总共有'||TO_CHAR(v_howmany||个雇员');ENDLOOP;11Whileloop3.3.4标号和v_mNUMBER:=101;v_iNUMBER;v_nNUMBER:=0;WHILEv_m<110v_i:=2;IFmod(v_m,v_i)=0THENv_i:=0;ENDvi:=vi+EXITWHENv_i>v_m-1;ENDLOOP;IFv_i>0v_n:=v_n+DBMS_OUTPUT.PUT_LINE('第'||v_n||个素数是||v_m);ENDIF;v_m:=v_m+2;ENDLOOP;PL/SQLGOTOGOTOGOTO 12:V_counterNUMBER:=1;V_counter:=v_counter+1;IFIFv_counter>10THENGOTOENDIF;ENDLOOP;viNUMBER:=0;vsNUMBER:=v_i:=v_i+1;IFv_i<=1000v_s:=v_s+v_i;GOTOlabel_1;ENDIF;PL/SQL程序中，NULLnull语句来说明“不用做任IFv_numISNULLGOTOEND…NULL;不需要处理任何数据。v_emp_idv_first_namev_first_nameemployees.first_name%TYPE;v_salaryemployees.salary%TYPE;v_sal_raiseNUMBER(3,2);v_emp_id:=SELECTfirst_name,salaryINTOv_first_name,v_salaryFROMemployeesWHEREemployee_id=v_emp_id;IFv_salary<=3000THENv_sal_raise:=.10;END一 查 QQ好友QQ空间复制网址收藏夹打印邮件人人网开心网印象笔记领英飞信豆瓣一键猜你喜欢易信花瓣网腾讯+阿里巴巴朋友网麦库记事搜藏Gmail邮箱有道云笔记天涯社区贴吧轻笔记凤凰饭否豆瓣9点谷歌谷歌Buzz有道书签QQ书签MSN堆糖摇篮空间Hotmail邮箱雪球和讯139邮箱189邮箱翼友圈人民新华谷歌翻译推他猫扑推客美丽说蘑菇街赶牛网Poco网网和讯游戏江湖法律TumblrRedditInstapaperPocketWO+挖客网创业邦救救地球抽屉网递客网易集网Pdf转换友好打印W3c 验证Bit.lyDiggMail.ruDiigoFriendFeedMyspaceMixxNetLogNetvibesPhonefavs.fmxoDeliciousMisterWongStumbleuponPlurkFunpMyshareORACLEPL/SQLNO_DATA_FOUND%NOTFOUND PL/SQL 在内存中为其分配上下文区（ContextArea），即缓冲区。游标是指向该区的一个指针，QL语句的应用程序的常用编程方式。EN_CURSORS参数定义。SQLSQL 显式游标处理需四个PL/SQL步骤定义/游标：就是定义一个游标名，以及与其相对应的SELECT语句。CURSORCURSORcursor_name[(parameter[,parameter]…)][RETURNdatatype]parameter_name[IN]datatype[{:=|DEFAULT} )[RETURNdatatype]是可选的，表示游标返回数据的数据。如果选择，则应该严格打开游标：SELECT语句，将其查询结果放入工作区，并且指FORUPDATE选项，OPEN语句还将锁定数据库表中游标结果集合对应的数据行。OPENcursor_name[([parameter=>]value[,[parameter=>] 法。PL/SQLOPEN语句重复打开一个游标。FETCHcursor_nameINTO{variable_list|record_variable FETCH语句，将操作失败，并将游标属性%NOTFFETCH语句是否执行成功并返回一个数据行，以便确定是否给对应的变量赋了值。FETCH语句取其中数据。OPEN语句重新打开。CLOSE INTO子句。1.10名员工的信息。CURSORISSELECTfirst_name||last_name,SalaryFROMEMPLOYEESWHEREvenameEMPLOYEES.firstname%TYPE;vsal OPENFETCHc_cursorINTOv_ename,v_sal;WHILEc_cursor%FOUNDLOOPFETCHc_cursorINTOv_ename,v_sal;ENDCLOSECLOSEc_cursor;2. DeptnameDEPARTMENTS.DEPARTMENTNAME%TYPE;DeptlocDEPARTMENTS.LOCATIONID%TYPE;CURSORc1ISWHEREDEPARTMENT_ID<=30;WHEREDEPARTMENT_ID<=dept_no;CURSORc3(dept_noNUMBERDEFAULT10)ISSELECT*FROMDEPARTMENTSOPENc1;FETCHc1INTOdeptname,deptloc;EXITWHENc1%NOTFOUND;ENDLOOP;CLOSEOPENc2;FETCHc2INTOdept_name,dept_loc;EXITWHENc2%NOTFOUND;ENDLOOP;CLOSEOPENc3(dept_no=>20);FETCHc3INTOEXITWHENENDLOOP;CLOSEc3; FETCH 布尔型属性，与%FOUND相反； 布尔型属性，当游标已打开时返回TRUE； 3：120050v_empno CURSORccursorISSELECTEMPLOYEEID,SalaryFROMEMPLOYEES;OPENc_cursor;FETCHc_cursorINTOv_empno,v_sal;EXITWHENc_cursor%NOTFOUND;IFv_sal<=1200THENUPDATEEMPLOYEESSETSalary=Salary+50WHEREEMPLOYEE_ID=v_empno;ENDDBMS_OUTPUT.PUT_LINE('记录数:'||c_cursor%ROWCOUNT);ENDLOOP;CLOSEccursor;v_f_nameemployees.first_name%TYPE;v_j_idemployees.job_id%TYPE;CURSORc1游标,SELECTfirst_name,job_idFROMemployeesWHEREdepartment_id=20;OPENc1; FETCHc1INTOv_f_name,v_j_id; IFc1%FOUNDTHENENDIF;ENDLOOP;CLOSEc1;--关闭游标vfnameemployees.firstname%TYPE;vhdateemployees.hiredate%TYPE;CURSORc2(dept_idNUMBER,j_idVARCHAR2)--游标,SELECTfirst_name,hire_dateFROMemployeesWHERESELECTfirst_name,hire_dateFROMemployeesWHEREdepartment_id=dept_idANDjob_id=j_id;OPENc2(90'AD_VP');--打开游标,传递参数值FETCHc2INTOv_f_name,v_h_date; IFc2%FOUNDTHENENDIF;ENDLOOP;CLOSEc2;--关闭游标TYPEemp_record_typeISRECORD(f_nameemployees.first_name%TYPE,h_dateemployees.hire_date%TYPE);v_emp_recordCURSORc3(dept_idNUMBER,j_idVARCHAR2) 游标,RETURNSELECTfirst_name,hire_dateFROMemployeesWHEREdepartment_id=dept_idANDjob_id=j_id;OPENc3(j_id'AD_VPdept_id90);--打开游标,传递参数值FETCHc3INTOv_emp_record; IFc3%FOUNDTHEN||vemprecord.hENDIF;ENDLOOP;CLOSEc3;--关闭游标CURSORc4(dept_idNUMBER,j_idVARCHAR2)--游标,SELECTfirst_namef_name,hire_dateFROMemployeesWHEREdepartment_id=dept_idANDjob_id=j_id;v_emp_recordc4%ROWTYPE;OPENc4(90'AD_VP');--打开游标,传递参数值FETCHc4INTOv_emp_record; IFc4%FOUNDTHEN||vemprecord.hireENDIF;ENDLOOP;CLOSEc4;--关闭游标FORPL/SQLFOROPEN、FETCH、CLOSEFORFORindex_variableINcursor_name[(value[,value]…)]ENDindex_variable为游标FOR循环语句隐含的索引变量，该变量为记录变量，其结构与才能通过游标FOR循环语句中的索引变量来这些列数据。CURSORc_salISSELECTemployee_id,first_name||last_nameename,salaryFROMemployees;FORv_salINc_salDBMSOUTPUT.PUTLINE(tochar(vsal.employeeid)||'---'||vsal.ename||'---'||tochar(vsal.salary));ENDLOOP;例9：当所的游标带有参数时，通过游标FOR循环语句为游标传递参数CURSORc_cursor(dept_noNUMBERDEFAULT10)SELECTdepartment_name,location_idFROMdepartmentsWHEREdepartment_id<=FORc1_recINc_cursor(30)LOOP ENDDBMS_OUTPUT.PUT_LINE(CHR(10)||'使用默认的dept_noFORc1_recINc_cursor ENDLOOP;10：PL/SQLFORFORc1_recIN(SELECTdepartment_name,location_idFROMdepartments)LOOPENDLOOP;、SQLORACLEORACLE系统自动地完成，无需用户进行处理。用户只能通过隐式游标的相关属性，来完成相应的操作。在隐式游标的工作区中，所存放的数据是与用户自定义的显示游标无关的SQL语句所包含的数据。、格式调用为：值值11:EMPLOYEESDEPARTMENT12:SQL%ROWCOUNTv_rowsNUMBER;UPDATEemployeesSETsalary=WHEREdepartment_id=90ANDjob_id=v_rowsSQL%ROWCOUNT;V_deptnodepartment_id%TYPE:=&p_deptno;DELETEFROMemployeesWHEREdepartment_id=v_deptno;IFSQL%NOTFOUNDTHENDELETEFROMdepartmentsWHEREdepartment_id=v_deptno;ENDIF;.3NO_DATA_FOUND%NOTFOUNDSELECT…INTOWHERE子句未找到时触发UPDATEDELETEWHERESQL%NOTFOUND；在提取%NOTFOUND或%FOUNDNO_DATA_FOUND.4.1.4使用游标更新和删除数据FORUPDATE选项，以便在打开游标时锁定游标结果集合在表中对应为了对正在处理(查询)的行不被另外的用户改动，ORACLEFORUPDATESELECTcolumn_listFROMtable_listFORUPDATE[OFcolumn[,column]…] SELECTFORUPDATE操作一直等OPEN立即返回并给出：ORA-0054：resourcebusyandacquirewithnowait如果使用FORUPDATE游标，则可在DELETE和UPDATE语句中使13：EMPLOYEESV_deptnoemployees.department_id%TYPE:=&p_deptno;CURSORemp_cursorSELECTemployees.employeeid,FROMemployeesWHEREemployees.departmentid=vdeptnoFORUPDATENOWAIT;FORemp_recordINemp_cursorLOOPIFemp_record.salary<1500THENUPDATEemployeesSETsalary=1500WHERECURRENTOFemp_cursor;ENDIF;END v_emp_recordemployees%ROWTYPE;CURSORc1SELECT*FROMemployeesFORUPDATE;OPENc1;FETCHc1INTOvemprecord;EXITWHENIFv_emp_record.department_id=90ANDv_emp_record.job_id='AD_VP'UPDATEemployeesSETsalary=WHERECURRENTOFc1;--更新当前游标行对应的数据行ENDIF;ENDCOMMIT;--CLOSEc1;类型。在PL/SQL中，可以在块、子程序和包的区域内定义游标变量类型。TYPETYPEref_type_nameISREFCURSOR[RETURNreturn_type];REFCURSOUTYPEref_cursor_typeISREFCURSOR;步骤二：一个该数据类型的游标变量，如：cv_refREF_CURSOR_TYPE;TYPEdeptrecordISDeptnodepartments.department_id%TYPE,Dnamedepartments.departmentname%TYPE,Locdepartments.locationid%TYPETYPEdeptcurtypeISREFCURSORRETURNdepartments%ROWTYPE;TYPEdeptcurtyp1ISREFCURSORRETURNdeptrecord;TYPEcurtypeISREFCURSOR;Dept_c1deptcurtype;Dept_c2deptcurtyp1;Cvcurtype; OPEN…FOROPENOPEN{cursor_variable_name|:host_cursor_variable_name}FORselect_statement;其中：cursor_variable_name为游标变量，host_cursor_variable_namePL/SQL主机环境（如OCI:ORACLECallInterface，Pro*c程序等）中的游标变量。ALREAD_OPEN异常错误。新打开游标变量时，前一个查询的内存处理区将被释放。FETCHFETCHFETCH{cursor_variable_name|:host_cursor_variable_name}INTO{variable[,variable]…|record_variable};CLOSECLOSE{cursor_variable_name| URSOR异常错误。TYPEemp_job_recISEmployee_idemployees.employee_id%TYPE,Employeenameemployees.firstname%TYPE,Jobtitleemployees.jobid%TYPETYPEemp_job_refcur_typeISREFCURSORRETURNemp_job_rec;Emp_refcuremp_job_refcur_type;Emp_jobemp_job_rec;OPENemp_refcurSELECTemployees.employee_id,employees.first_name||employees.last_name,FROMORDERBYFETCHemprefcurINTOempjob;WHILEemprefcur%FOUNDLOOPDBMSOUTPUT.PUTLINE(empjob.employeeid||':'||empjob.employeename||'isa'||emFETCHemp_refcurINTOemp_job;ENDLOOP;PROMPT'WhattablewouldyouliketoACCEPTtabPROMPT'(D)epartment,orTyperefcurtISREFCURSOR;Refcurrefcur_t;TYPEsample_rec_typeISRECORD(Idnumber,sampleselectionvarchar2(1):=UPPER(SUBSTR('&tab',1,1));IFselection='D'THENOPENrefcurFORSELECTdepartments.department_id,departments.department_nameFROMdepartments;DBMSOUTPUT.PUTLINE('Departmentdata');ELSIFselection='E'THENOPENrefcurFORSELECTemployees.employee_id,employees.first_name||'isa'||employees.job_idFROMemplDBMS_OUTPUT.PUT_LINE('Pleaseenter''D''or''E''');END FETCHrefcurINTOsample;WHILErefcur%FOUNDLOOPDBMSOUTPUT.PUTLINE(sample.id||':'||sample.description);FETCHrefcurINTOsample;ENDCLOSErefcur;TYPEemp_cursor_typeISREF c1 v_emp_recordemployees%ROWTYPE;vregrecordregions%ROWTYPE;OPENc1FORSELECT*FROMemployeesWHEREdepartment_id=20;FETCHc1INTOv_emp_record;EXITWHENENDOPENc1FORSELECT*FROMregionsWHEREregion_idIN（1，2）;FETCHc1INTOv_reg_record;EXITWHENc1%NOTFOUND;ENDLOOP;CLOSEc1;TYPEemp_record_typeISRECORD(f_nameemployees.first_name%TYPE,hdateemployees.hiredate%TYPE,j employees.job v_emp_recordTYPEemp_cursor_typeISREFCURSORRETURNEMP_RECORD_TYPE; c1EMP_CURSOR_TYPE;OPENc1FORSELECTfirst_name,hire_date,job_idFROMemployeesWHEREdepartment_id=20;FETCHc1INTOv_emp_record;EXITWHEN||'||'岗位：'||v_emp_record.j_id);ENDLOOP;CLOSEc1; 在部 PL/SQLSQLCODE,SQLERRML/SQL程序执行之前。ORACLE提供异常情况(EXCEPTION)和异常处理(EXCEPTIONHANDLER)5.1异常情况处理(EXCEPTION)是用来处理正常执行过程中未预料的,程序块的异常处理预定义的错误和自定义错误,由于PL/SQL程序块一旦产生异常而没有如何处理时,(Predefined)义，由ORACLE自动将其。(Predefined)后由ORACLE自动将其。用户定义(User_define)程序执行过程中，出现编程人员认为的情况。对这种异常情况的处理，需要用户在程序中定义，然后显式地在程序中将其。PL/SQL程序体的后半部,结构为 WHENfirst_exceptionTHENWHENfirst_exceptionTHEN<codetohandlefirstexception>WHENsecond_exceptionTHEN<codetohandlesecondexception>WHENOTHERSTHEN<codetohandleothersexception>异常处理可以按任意次序排列,OTHERS必须放在最后5.1.1预定义的异常处 ORACLE异常错误信无效的用户名/SELECTINTOSELECTINTO内存不够的内部错PL/SQLnullExists以外的集合collection)nullpl/sqlvarray对嵌套或varray索引得超出范围以对嵌套或varray索引得大于集合中元素个数v_empnoemployees.employee_id%TYPE:=&empno;v_salemployees.salary%TYPE;.2SELECTsalaryINTOv_salFROMemployeesWHEREemployee_id=v_empno;IFv_sal<=1500THENUPDATEemployeesSETsalarysalary100WHEREemployee_id=v_empno;ENDIF;WHENTOO_MANY_ROWSTHENWHENOTHERSTHENORACLEPL/SQL<异常情况> PL/SQL块的异常情况处理部分对异常情况做出相应的处理。2：删除指定部门的记录信息，以确保该部门没有员工。INSERTINSERTINTOdepartmentsVALUES(50,'FINANCE','CHICAGO');v_deptnodepartments.department_id%TYPE:=&deptno;deptno_remainingEXCEPTION;/*-2292是 DELETEFROMdepartmentsWHEREdepartment_id=v_deptno;WHENdeptno_remainingTHEN WHENOTHERSTHEN是通过显式使用RAISE语句来触发。当一个异常错误时，控制就转向到EXCEPTION块异常错误部分，执行错误处理代码。PL/SQL<异常情况> RAISE<异常情况PL/SQL块的异常情况处理部分对异常情况做出相应的处理。3：100；v_empnoemployees.employee_id%TYPE:=&empno;no_resultEXCEPTION;UPDATEemployeesSETsalary=salary+100WHEREemployeeid=vempno;IFSQL%NOTFOUNDTHENRAISEno_result;ENDIF;WHENno_resultWHENOTHERSTHEN调用DBMS_STANDARD(ORACLE提供的包)包所定义的ORACLERAISE_APPLICATION_ERROR error_number–20,000–20,999error_message是相应的提示信息(<2048字节keep_errors为可选如果keep_errors=TRUE,则新错误将被添加到已经的错误列keep_errors=FALSE(缺省),则新错误将替换当前的错误列表。4：get_salary,该函数检索指定部门的工资总和，其中定义了-20991和-20992号错误，分别处理参数为空和部门代码两种错误：ErrcodeNUMBER,ErrtextCHAR(40));CREATEORRE CEFUNCTIONget_salary(p_deptnoNUMBER)RETURNNUMBERv_salNUMBER;IFp_deptnoISNULLTHENELSIFp_deptno<0SELECTSUM(employees.salary)INTOv_salFROMemployeesWHEREemployees.department_id=p_deptno;RETURNv_sal;ENDIF;V_salaryNUMBER(7,2);V_sqlcodeNUMBER;VsqlerrNulldeptnoEXCEPTION;InvaliddeptnoEXCEPTION;PRAGMAEXCEPTION_INIT(invalid_deptno,-20992);V_salary'V_salary:=get_salary(-10);WHENinvalid_deptnoTHENV_sqlcode:=SQLCODE;V_sqlerr:=SQLERRM;INSERTINTOerrlog(errcode,errtext)VALUES(v_sqlcode,v_sqlerr);ENDVsalary:=getV_salary:=get_salary(NULL);ENDinner2;Vsalary:=getWHENWHENnull_deptnoTHENV_sqlcode:=SQLCODE;V_sqlerr:=SQLERRM;INSERTINTOerrlog(errcode,errtext)VALUES(v_sqlcode,v_sqlerr);WHENOTHERSTHENEND例5：定义触发器，使用RAISE_APPLICATION_ERROR没有员工的新员式记录插入CREATEORRE CECREATEORRE CETRIGGERtr_insert_empBEFOREINSERTONemployeesFOREACHROWIF:new.first_nameISNULLOR:new.last_nameisnullTHENRAISE_APPLICATION_ERROR(-20000,'Employeemusthaveaname.');ENDIF;由于异常错误可以在部分和执行部分以及异常错误部分出现因而在不同部分.1在执行部 当一个异常错误在执行部分时，有下列情况1)。.2 PL/SQL程序：nameWHENOTHERSAbcnumber(3)=’abc’;EXCEPTIONWHENOTHERSTHENWHENOTHERSTHENnameWHENOTHERSWHENOTHERSPL/SQLSQLCODE,SQLERRMORACLE512SQLERRMSUBSTRWHENOTHERS 返回遇到的Oracle错误号, SQLERRM=’no_data_found SQLERRM=’normal,successfual6.ORACLEerr_msgVARCHAR2(100);/*ORACLE*/FORerr_numIN-100..0LOOPerr_msgerr_msg:=ENDLOOP;7.ORACLEINSERTINTOemployees(employeeid,firstname,lastname,hiredate,departmentid)VALUES(2222,'Eric','Hu',SYSDATE,20);INSERTINTOemployees(employee_id,first_name,last_name,hire_date,department_id)VALUES(2222,'胡','勇',SYSDATE,20);WHENOTHERSTHEN8.ORACLEempno_remainingPRAGMAEXCEPTIONINIT(empnoremaining,-/*-1是 INSERTINTOemployees(employee_id,first_name,last_name,hire_date,department_id)VALUES(3333,'Eric','Hu',SYSDATE,20);INSERTINTOemployees(employee_id,first_name,last_name,hire_date,department_id)VALUES(3333,'胡','勇',SYSDATE,20);WHENempnoremainingTHEN WHENOTHERSTHEN调用过PRAGMA开发过程步，被编译后在数据库中，以备执行。因此，其它PL/SQL块可以按名称来使用他们。? CE]FUNCTION(arg1[{IN|OUT|INOUT}]type1[DEFAULTvalue1],[arg2[{IN|OUT|INOUT}]type2[DEFAULT[argn[{IN|OUT|INOUT}]typen[DEFAULTvaluen]])[AUTHIDDEFINER|CURRENT_USER]RETURNreturn_typeIS|ASENDfunction_name;<spanstyle="font-family:verdana,Arial,Helvetica,sans-serif;font-size:14px;line-height:1.5;background-color:#ffffff;"> 123456789IN,OUT,INOUTIN模式。IN模式的形参只能模式的形参会忽略调用时的实参值（或说该形参的初始值总是NULL，但在函数内部可以被读或写，函数返回时形参的值会赋予给实参。INOUT具有前两种模式的特性，即调用时，实参的值总是传递给形参，结束时，形参的值传递给实参。调用时，对于INOUTINOUT模式的实参必须是变量。ORREPALCE关键字，否则容易删除有用的函数。 获取某部门的工资总和?123FUNCTION4Dept_no5Emp_countOUT6RETURN78V_sum9SELECTSUM(SALARY),count(*)INTOV_sum,FROMEMPLOYEESWHERERETURNWHENOTHERSENDEND1V_numV_numNUMBER;V_sumNUMBER;V_sum:=get_salary(10,1234567argumentargument=>parameter1其中：argument为形式参数，它必须与函数定义时所的形式参数名称相同parameter为实际参数。?12V_numV_sumV_sumNUMBER;V_sum:=get_salary(emp_count=>v_num,dept_no=>34567?1CREATEORRECEFUNCTION23Age4VARCHAR2)5RETURN67V_var89RETURNVar:=demo_fun('user1',30,=>'男Var:=demo_fun('user2',age=>40,=>'男Var:=demo_fun('user3',=>'女',age=>时，输出参数和输入/输出参数均采用传值法。在函数调用时，ORACLE将实际参数数据拷贝到输入/输出参数，而当函数正常运行退出时，又将输出形式参数和输入/输出形在CREATEORRE CEFUNCTION语句中函数参数时可以使用DEFAULT关键?1CREATEORRECEFUNCTION2Name3Age45RETURN67V_var89 RETURN使用实际参数值。在创建函数时，只能为输入参数设置默认值，而不能为输入/输出参?1234Var:=demo_fun('user1',56Var:=demo_fun('user2',age=>78Var 女age96.3.1在ORACLESERVER上建立过程,可以被多个应用程序调用,可以向过CREATE[ORRECREATE[ORRECE]PROCEDUREprocedure_name([arg1[IN|OUT|INOUT]]type1[DEFAULTvalue1],[arg2[IN|OUT|INOUT]]type2[DEFAULTvalue1]],[argn[IN|OUT|INOUT]]typen[DEFAULTvaluen])[AUTHIDDEFINER|CURRENT_USER]{IS|ASENDprocedure_name;123456789?123CREATEORRECEPROCEDURE456INSERTINTOlogtable(userid,logdate)VALUES(USER,7CREATEORRE CREATEORRE PROCEDURE(v_empnoINemployees.employee_id%TYPE)DELETEFROMemployeesWHEREemployee_id=v_empno;IFSQL%NOTFOUNDTHENRAISE123456789ENDWHENno_resultWHENOTHERSEND?1PROCEDURE v_firstnameinemployees.first_name%TYPE,v_lastnamein inPRAGMAEXCEPTION_INIT(empno_remaining,-/*-1是唯一约束条件的错误代码*/ WHENempno_remainingWHENOTHERSTHENEND23456789CREATECREATEOR PROCEDURE(v_dept_idINv_dept_nameINdepartments.department_name%TYPE,v_mgr_idINdepartments.manager_id%TYPE,v_loc_idINdepartments.location_id%TYPE)12345678ept_null_error9ept_no_loc_idPRAGMAEXCEPTION_INIT(ept_no_loc_id,-INSERTINTO(department_id,department_name,manager_id,(v_dept_id,v_dept_name,v_mgr_id,WHENDUP_VAL_ON_INDEX'WHENept_null_error'WHENept_no_loc_id'ENDept_20000PRAGMAEXCEPTION_INIT(ept_20000,-ept_20001PRAGMAEXCEPTION_INIT(ept_20001,-ept_20002PRAGMAEXCEPTION_INIT(ept_20002,-insert_dept(300,300',100,insert_dept(310,NULL,100,insert_dept(310,310',100,WHENept_20000WHENept_20001WHENept_20002WHENOTHERSept_20000PRAGMAEXCEPTION_INIT(ept_20000,-ept_20001PRAGMAEXCEPTION_INIT(ept_20001,-ept_20002PRAGMAEXCEPTION_INIT(ept_20002,-insert_dept(v_dept_name310'v_dept_idv_mgr_id=>100,v_loc_id=>insert_dept(320,320',v_mgr_id100,v_loc_idWHENept_20000WHENept_20001WHENept_20002WHENOTHERS.2调 过过程建立完成后，只要通过，用户就可以在SQLPLUS、ORACLE开发传递和组合传递，传递方法与函数的一样。ORACLEEXECUTE语句来实现对存EXEC[UTE]procedure_name(parameter1, EXECUTE CREATECREATEOR PROCEDURE(v_empnoINemployees.employee_id%TYPE,v_enameOUTemployees.first_name%TYPE, OUTemployees.salary%TYPE)SELECTlast_name||last_name,salaryINTOv_ename,123456789FROMWHEREemployee_id=WHENOTHERSEND--v1v2QueryEmp(100,v1,QueryEmp(103,v1,QueryEmp(104,v1,?12PROCEDURE3(45sal_sumOUT6emp_countOUT7)89SELECTSUM(salary),COUNT(*)INTOsal_sum,FROMemployeesWHEREdepartment_id=WHENOTHERSENDV_numNUMBER;V_sumNUMBER(8,2);Proc_demo(30,v_sum,Proc_demo(sal_sum=>v_sum,emp_count=>v_num);在PL/SQL程序中还可以在块内建立本地函数和过程，这些函数和过程不在PL/SQLPL/SORRECE关键字。例?V_numV_sumNUMBER(8,PROCEDURE(Sal_sumOUTEmp_countOUT)SELECTSUM(salary),COUNT(*)INTOsal_sum,FROMemployeesWHEREWHENOTHERSEND--Proc_demo(30,v_sum,Proc_demo(sal_sum=>v_sum,emp_count=>.3AUTHIDORACLE，这个过程使用谁的权限运行．默任14：AUTOID?1Connect2DROPTABLE3CREATEtablelogtable(useridVARCHAR2(10),logdate45CREATEORRECEPROCEDURE6AUTHID789INSERTINTOlogtable(userid,logdate)VALUES(USER,GRANTEXECUTEONlogexecutionTOCONNECT/ASGRANTCONNECTTOtestuser1IDENTIFIEDBYCONNECTEXECUTECONNECTSELECT*FROM15：AUTOID?1CONNECT23CREATEOR CEPROCEDURE4AUTHID567INSERTINTOlogtable(userid,logdate)VALUES(USER,89GRANTEXECUTEONlogexecutionTOCONNECTEXECUTE.4PRAGMAORACLE8i可以支持事务处理中的事务处理的概念．这事务处理可以完成它?1DROPTABLE234Username5Dassate_time6Mege789CREATETABLEtemp_table(NnumberCREATEORRECEPROCEDURElog_message(p_messagePRAGMAINSERTINTOlogtableVALUES(user,sysdate,p_messageENDLog_message(‘AbouttoinsertintoINSERTINTOtemp_tableVALUESLog_message(‘RollbacktoinsertintoSELECT*FROMSELECT*FROM?1CREATEORRECEPROCEDURElog_message(p_message234INSERTINTOlogtableVALUES(user,sysdate,p_message56END789Log_message('AbouttoinsertintoINSERTINTOtemp_tableVALUESLog_message('RollbacktoinsertintoSELECT*FROMSELECT*FROM.5开 使用文字编辑处理软件编辑过程源码要用类似WORD文字处理软件进行编辑SQL>START我们不能保证所写的过程达到一次就正确。所以这里的调式是每个程序员必须SQLPLUS下来调式主要用的方法是：SHOWERROR使用user_errors数据字典来查看各过程的错误位置用系统的一部分的过程也必须进行才能达到要求。在SQL*PLUS下可以用GRANT命令来进行过程的运行。GRANT?1GRANTsystem_privilege|2TOuser|role|PUBLIC[WITHADMIN34GRANTobject_privilege|ALLON5TOuser|role|PUBLIC[WITHGRANT67--89CREATEORRECEPUBLICSYNONYMdbms_jobFORGRANTEXECUTEONdbms_jobTOPUBLI