全面解析OracleProcedure基本语法

1、全面解析oraciep基本语法ure关键字or存储过程1基.本结构op存储过程名字参数1INNUMBER,参数2INNUMBER变量1INTEGER:=0;变量2DATE;存储过程名字SELECTINTO将ele查询的结果存入到变量中，可以同时将多个列存储多个变量中，必须有一条记录，否则抛出异常如果没有记录抛出OO例子：col变量I变量oOerucerePTIOO判断dosomething循环eHILEV_TEST=1LOOPENDLOOP;变.量赋值用or使用curorOcurORcur_resulcurLOOPcUf名recUrre歹列名OOP带参数的curorOOP变量值LOOPFETC

2、HC_USERINTOV_NAME;EXITFETCHC_USER%NOTFOUND;dooeENDLOOP;O用IIdeeloerdeu连接数据库后建立一个eO在窗口输入调用R的代码开始deu单步调试转载：oracle存储过程关键字ora存储过程存储过程创建语法：TOC o 1-5 h zcreaeorrelacen存储过程名（ara,araeoute变量类型（值范围）变量类型（值范围）eeleccou变量表o列名aram判断条件eele列名变量o表列名ara;ouPuul打印信息l判断条件eouPuul打印信息lea异常名（O）dxceptioneoereRollback;nd;注意事项：

3、1存储过程参数不带取值范围，表示传入，OU表示输出变量带取值范围，后面接分号在判断语句前最好先用count(*)函数判断是否存在该条操作记录用o。给变量赋值在代码中抛异常用raise+异常名以命名的异常命名的系统异常产生原因ACCESS_INTO_NULL未定义对象CASE_NOT_FOUNDCASE中若未包含相应的WHEN,并且没有设置ELSE时COLLECTION_IS_NULL集合元素未初始化CURSER_ALREADY_OPEN游标已经打开DUP_VAL_ON_INDEX唯一索引对应的列上有重复的值INVALID_CURSOR在不合法的游标上进行操作INVALID_

4、NUMBER内嵌的SQL语句不能将字符转换为数字NO_DATA_FOUND使用selectinto未返回行,或应用索引表未初始化的TOO_MANY_ROWS执行selectinto时，结果集超过一行ZERO_DIVIDE除数为0SUBSCRIPT_BEYOND_COUNT元素下标超过嵌套表或VARRAY的最大值SUBSCRIPT_OUTSIDE_LIMIT使用嵌套表或VARRAY时，将下标指定为负数VALUE_ERROR赋值时，变量长度不足以容纳实际数据LOGIN_DENIEDPL/SQL应用程序连接到oracle数据库时,提供了不正确的用户名或密码NOT_LOGGED_ONPL/SQL应用程

5、序在没有连接oralce数据库的情况下访问数据PROGRAM_ERRORPL/SQL内部问题,可能需要重装数据字典pl./SQL系统包ROWTYPE_MISMATCH宿主游标变量与PL/SQL游标变量的返回类型不兼容SELF_IS_NULL使用对象类型时，在null对象上调用对象方法STORAGE_ERROR运行PL/SQL时,超出内存空间SYS_INVALID_ID无效的ROWID字符串TIMEOUT_ON_RESOURCEOracle在等待资源时超时语法及示例：1、存储过程创建存储过程的语法：CREATEORREPLACEPROCEDUREprocedure_name(parameter_

6、list)IS|ASlocal_declarationsBEGINexecutable_statementsEXCEPTIONexception_handlersENDprocedure_name;其中：procedure_name是过程的名称。parameter_list是参数列表。local_declarations是局部声明。executable_statements是可执行语句。exception_handlers是异常处理程序。示例1：演示创建过程(参数列表中为IN参数赋予一个默认值，不能为OUT、INOUT参数赋予默认值)createorreplaceprocedurefind_e

7、mp(emp_noinnumber:=7900)asempnamevarchar2(20);beginselectenameintoempnamefromempwhereempno=emp_no;dbms_output.put_line雇员姓名是|empname);exceptionwhenno_data_foundthendbms_output.put_li调用过程：EXECUTEprocudure_name(parameters_list);也可以在过程里面调用，直接写上procudure_name而不必写EXECUTE。示例2:演示创建带OUT参数的过程createorreplacepr

8、oceduretest(value1varchar2,value2outnumber)isidentitynumber;beginselectsalintoidentityfromempwhereempno=value1;ifidentity2000thenvalue2:=1000;elsevalue2:=500;endif;end;调用带OUT参数的过程：declarevalue2number;begintest(7900,value2);dbms_output.put_line(value2);end;示例3：演示创建带INOUT参数的过程createorreplaceprocedures

9、wap(p1inoutnumber,p2inoutnumber)isv_tempnumber;beginv_temp:=p1;p1:=p2;p2:=v_temp;end;调用带INOUT参数的过程：declarenum1number:=100;num2number:=200;beginswap(num1,num2);dbms_output.put_line(num1=|num1);dbms_output.put_line(num2=|num2);end;示例4：将过程的执行权限授予其他用户GRANTEXECUTEONfind_empTOscott;GRANTEXECUTEONswapTOPUB

10、LIC;将find_emp过程的执行权限授予给用户scott,将执行swap过程的权限授予所有数据库用户。删除过程语法：DROPPROCEDUREprocudure_name;2、函数定义函数的语法如下：CREATEORREPLACEFUNCTIONfunction_name(parameter_list)RETURNdatatypeIS|ASlocal_declarationsBEGINexecutable_statementsEXCEPTIONexception_handlersENDfunction_name;其中：function_name是函数的名称。parameter_list是参

11、数列表。local_declarations是局部声明。executable_statements是可执行语句。exception_handlers是异常处理程序。使用函数时注意：形式参数必须只使用数据库类型，不得使用3L/SQL类型。函数的返回类型也必须是数据库类型。函数不能单独执行，只能通过SQL语句或PL/SQL程序块来调用。示例5：演示如何创建函数createorreplacefunctionfun_helloreturnvarchar2isbeginreturn朋友,您好;end;调用函数：selectfun_hellofromdual;函数的授权：同过和的授权一样具体请看示例4。删

12、除函数：DROPFUNCTIONfunction_name过程和函数的差异过程函数作为PL/SQL语句执行作为表达式的一部分调用在规范中不包含RETURN子句必须在规范中包含RETURN子句不返回任何值必须返回单个值可以包含RETURN语句，但是与函数不同，它不能用于返回值必须包含至少一条RETURN语句3、程序包创建包规范的语法：CREATEORREPLACEPACKAGEpackage_nameIS|ASPublictypeanditemdeclarationsSubprogramspecificationsENDpackage_name;其中：package_name是包的名称。Publ

13、ictypeanditemdeclarations是声明类型、常量、变量、异常和游标等。Subprogramspecifications声明PL/SQL子程序。示例6：演示创建程序包规范createorreplacepackagepack_opisprocedurepro_print_ename(idnumber);procedurepro_print_sal(idnumber);functionfun_re_date(idnumber)returndate;end;创建包主体的语法：CREATEORREPLACEPACKAGEBODYpackage_nameIS|ASPublictypean

14、ditemdeclarationsSubprogrambodiesBEGINInitialization_statementsENDpackage_name;其中：package_name是包的名称。Publictypeanditemdeclarations是声明类型、常量、变量、异常和游标等。Subprogrambodies是定义公共和私有PL/SQL子程序。示例7：演示创建程序包主体createorreplacepackagebodypack_opisprocedurepro_print_ename(idnumber)isnameemp.ename%type;beginselectenam

15、eintonamefromempwhereempno=id;dbms_output.put_line职员姓名：|name);endpro_print_ename;procedurepro_print_sal(示例8：调用程序包中创建的过程和函数execpack__print_ename(7900);execpack__print_sal(7900);selectpack_op.fun_re_date(7900)fromdual;示例9：演示程序包中的游标创建包规范createorreplacepackagepack_empiscursorcur_empreturnemp

16、%rowtype;procedurepro_cur;endpack_emp;创建包主体createorreplacepackagebodypack_empiscursorcur_empreturnemp%rowtypeisselect*fromemp;procedurepro_curisrec_empemp%rowtype;beginopencur_emp;loopfetchcur_empintorec_emp;exitwhencur_emp%notfound;ifrec_emp.sal1000t调用程序包中的过程以调用程序包中的游标execpack__cur;示例10:存储过程返回游标的子程序包(此程序包返回r_cur游标)CREATEORREPLACEpackageSCOTT.pk_wtistypemytypeisrefcursor;procedurep_wt(mycsoutmytype);end;CREATEORREPLACEpackageBODYSCOTT.pk_wtisprocedurep_wt(mycsoutmytype)isr_curmytype;beginopenr_curforselect*fromemp;mycs:=r_cur;