《oracle存储过程实例》由会员分享，可在线阅读，更多相关《oracle存储过程实例（35页珍藏版）》请在金锄头文库上搜索。
1、oracleoracle存储过程实例存储过程实例过程过程l过程就是高级程序设计语言中的模块的概念,将一些内部联系的命令组成一个个过程,通过参数在过程之间传递数据是模块化设计思想的重要内容.存储过程特点存储过程特点l1）存储过程是预编译过的，并且经优化后存储于SQL内存中，使用时无需再次编译，提高了工作效率；l2）存储过程的代码直接存放于数据库中，一般由客户端直接通过存储过程的名字进行调用，减少了网络流量，加快了系统执行速度，例如在进行百万以上的大批量数据查询时，使用存储过程分页要比其他方式分页快得多；l3）使用存储过程可以减少SQL注入式攻击，提高了系统的安全性，执行存储过程的用户要具有一定的
2、权限才能使用存储过程，没有数据操作权限的用户只能在其控制下间接地存取数据;存储过程特点存储过程特点4）在同时进行主、从表及多表间的数据维护及有效性验证时，使用存储过程比较方便，而且可以有效利用SQL中的事务处理的机制；5）使用存储过程，可以实现存储过程设计和编码工作分开进行，只要将存储过程名、参数、及返回信息告诉编码人员即可；6）但使用存储过程封装业务逻辑将限制应用程序的可移植性；另外，如果更改存储过程的参数或者其返回的数据及类型的话，需要修改应用程序的相关代码，比较繁琐。过程的语法结构过程的语法结构完整的过程结构如下:createorreplaceprocedure过程名as声明语句段;be
3、gin执行语句段;exception异常处理语句段;end;过程是有名称的程序块,as关键词代替了无名块的declare.创建过程实例创建过程实例l创建名为stu_proc的过程,create是创建过程的标识符,replace表示若同名过程存在将覆盖原过程.该过程定义了一个变量,其类型和student数据表中的sname字段类型相同,都是字符型,将数据表中的sno字段为1的sname字段内容送入变量中,然后输出结果.参数参数lSQL中调用存储过程语句：lcallprocedure_name();l调用时”()”是不可少的，无论是有参数还是无参数。l定义对数据库过程的调用时无参数过程:callp
4、rocedure_name仅有输入参数的过程：callprocedure_name(?,?.)这里?表示输入参数，创建存储过程时用in表示输入参数l仅有输出参数的过程:Callprocedure_name(?,?.)这里的？表示输出参数，创建存储过程时用out表示输入参数l既有输入参数又有输出参数的过程callprocedure_name(?,?.)这里的？有表示输出参数的，也有表示输入参数的l下面将会对这下面将会对这4种情况分别举出实例！种情况分别举出实例！参数过程实例参数过程实例无参数存储过程：lcreateorreplaceprocedurestu_procaslpnamevarchar
5、2(25);lbeginlselectsnameintopnamefromstudentwheresno=1;ldbms_output.put_line(pname);lend;l或者lcreateorreplaceprocedurestu_procaslpnamestudent.sname%type;lbeginlselectsnameintop_namefromstudentwheresno=1;ldbms_output.put_line(pname);lend;l仅有输入参数的过程lcreateorreplaceprocedurestu_proc1(pnoinstudent.sno%ty
6、pe)aslpnamevarchar2(25);lbeginlselectsnameintopnamefromstudentwheresno=pno;ldbms_output.put_line(pname);lend;参数过程实例参数过程实例l仅有输出参数的存储过程lcreateorreplaceprocedurestu_proc2(pnameoutstudent.sname%type)aslbeginlselectsnameintopnamefromstudentwheresno=1;ldbms_output.put_line(pname);lend;l此种存储过程不能直接用call来调用，
7、这种情况的调用将在下面oracle函数调用中说明参数过程实例参数过程实例l有输入输出参数的存储过程：lcreateorreplaceprocedurestu_proc3l(pnoinstudent.sno%type,pnameoutstudent.sname%type)aslbeginlselectsnameintopnamefromstudentwheresno=pno;ldbms_output.put_line(pname);lend;l此种存储过程不能直接用call来调用，这种情况的调用将在下面oracle函数调用中说明参数过程实例参数过程实例Oracle函数调用存储过程函数调用存储过程
8、l我们已经学习了oracle函数，下面就针对参数的4种情况分别举出实例说明函数对存储过程的调用l对无参数过程的调用：l-函数lcreateorreplacefunctionget_pnamereturnvarchar2islpnamevarchar2(20);lbeginlstu_proc;lselectsnameintopnamefromstudentwheresno=1;lreturnpname;lend;l-调用ldeclarelbeginldbms_output.put_line(在PL/SQL中打印的结果：|get_pname);lend;函数调用存储过程实例函数调用存储过程实例l对
9、有输入参数过程的调用：lcreateorreplacefunctionget_pname1(pnoinnumber)returnvarchar2islpnamevarchar2(20);lbeginlstu_proc1(pnoinstudent.sno%type)lselectsnameintopnamefromstudentwheresno=pno;lreturnpname;lend;l-调用ldeclarelbeginldbms_output.put_line(在PL/SQL中打印的结果：|get_pname1(2);lend;函数调用存储过程实例函数调用存储过程实例l对有输出参数过程的调
10、用:lcreateorreplacefunctionget_pname2(pnameoutvarchar2)returnvarchar2islbeginlstu_proc2(pnameoutstudent.sname%type);lreturnpname;lend;l-调用ldeclarelpnamestudent.sname%type;lbeginldbms_output.put_line(在PL/SQL中打印的结果：|get_pname2(pname);lend;函数调用存储过程实例函数调用存储过程实例l对有输入输出参数过程的调用:lcreateorreplacefunctionget_p
11、name3(pnoinnumber,pnameoutvarchar2)returnvarchar2islbeginlstu_proc3(pnoinstudent.sno%type,pnameoutstudent.sname%type);lreturnpname;lend;l-调用ldeclarelpnamestudent.sname%type;lbeginldbms_output.put_line(在PL/SQL中打印的结果：|get_pname3(2,pname);lend;函数调用存储过程实例函数调用存储过程实例JAVA调用数据库存储过程调用数据库存储过程l前面我们已经讲述了有关oracl
12、e数据库的存储过程的几种形式，以及oracle函数对存储过程的调用，下面我将根据上面存储过程的实例来举出JAVA对oracle存储过程的调用l仅有返回值的过程：lpublicstaticvoidmain(Stringargs)lConnectionconn=BBConnection.getConnection();lStringsql=callstu_proc2(?);ltrylCallableStatementstatement=conn.prepareCall(sql);lstatement.registerOutParameter(1,Types.VARCHAR);lstatement.
13、execute();lStringpname=statement.getString(1);lSystem.out.println(pname);lcatch(SQLExceptione)l/TODOAuto-generatedcatchblockle.printStackTrace();lllJAVA调用实例调用实例l既有输入参数又有输出参数的过程lpublicstaticvoidmain(Stringargs)lConnectionconn=BBConnection.getConnection();lStringsql=callstu_proc3(?,?);ltrylCallableSta
14、tementstatement=conn.prepareCall(sql);lstatement.setInt(1,1);lstatement.registerOutParameter(2,Types.VARCHAR);lstatement.execute();lStringpname=statement.getString(2);lSystem.out.println(pname);lcatch(SQLExceptione)l/TODOAuto-generatedcatchblockle.printStackTrace();lllJAVA调用实例调用实例l下面将举出无out参数的调用实例l这
15、种参数不适于用在查询语句上，因为查询语句需要有返回值才能被JAVA调用l返回到OUT参数中的值可能会是JDBCNULL。当出现这种情形时，将对JDBCNULL值进行转换以使getXXX方法所返回的值为null、0或false，这取决于getXXX方法类型。对于ResultSet对象，要知道0或false是否源于JDBCNULL的唯一方法，是用方法wasNull进行检测。如果getXXX方法读取的最后一个值是JDBCNULL，则该方法返回true，否则返回flaseJAVA调用实例调用实例l仅有参数的过程：lpublicstaticvoidmain(Stringargs)lConnectionc
16、onn=BBConnection.getConnection();lStringsql=callstu_proc1(?);ltrylCallableStatementstatement=conn.prepareCall(sql);lstatement.setInt(1,1);lstatement.execute();lSystem.out.println(statement.execute();lcatch(SQLExceptione)l/TODOAuto-generatedcatchblockle.printStackTrace();lfinallyltrylconn.close();lca
17、tch(SQLExceptione)l/TODOAuto-generatedcatchblockle.printStackTrace();llllJAVA调用实例调用实例l无参数过程：lpublicstaticvoidmain(Stringargs)lConnectionconn=BBConnection.getConnection();lStringsql=callstu_proc();ltrylCallableStatementstatement=conn.prepareCall(sql);lstatement.execute();lSystem.out.println(statement
18、.execute();lcatch(SQLExceptione)l/TODOAuto-generatedcatchblockle.printStackTrace();lfinallyltrylconn.close();lcatch(SQLExceptione)l/TODOAuto-generatedcatchblockle.printStackTrace();lllJAVA调用实例调用实例通过调用数据库函数调用存储过程通过调用数据库函数调用存储过程l下面将举一个通过数据库函数来调用存储过程：lpublicstaticvoidmain(Stringargs)lConnectionconn=BBC
19、onnection.getConnection();lStringsql=?=callget_pname1(?);ltrylCallableStatementstatement=conn.prepareCall(sql);lstatement.registerOutParameter(1,Types.VARCHAR);lstatement.setInt(2,1);lstatement.execute();lSystem.out.println(statement.getString(1);lcatch(SQLExceptione)l/TODOAuto-generatedcatchblockle
20、.printStackTrace();lfinallyltrylconn.close();lcatch(SQLExceptione)l/TODOAuto-generatedcatchblockle.printStackTrace();lllll？表示函数return的值，get_pname1是数据库函数名l存储过程的out和in都是以参数传进，这就是函数和存储过程的区别之一存储过程的异常处理存储过程的异常处理为了提高存储过程的健壮性，避免运行错误，当建立存储过程时应包含异常处理部分。异常(EXCEPTION)是一种PL/SQL标识符，包括预定义异常、非预定义异常和自定义异常；预定义异常是指由P
21、L/SQL提供的系统异常；非预定义异常用于处理与预定义异常无关的Oracle错误(如完整性约束等)；自定义异常用于处理与Oracle错误的其他异常情况。RAISE_APPLICATION_ERROR用于自定义错误消息，并且消息号必须在2000020999之间lOracle数据库中提供了一些异常处理的方法，下面通过一个实例来说明lcreateorreplaceprocedurestu_proc6(pnoinstudent.sno%type,pnameoutstudent.sname%type)lislbeginlselectsnameintopnamefromstudentwheresno=pn
22、o;lEXCEPTIONlwhenNO_DATA_FOUNDthenlRAISE_APPLICATION_ERROR(-20011,ERROR：不存在！);lend;存储过程的异常处理实例存储过程的异常处理实例命名的系统异常产生原因lACCESS_INTO_NULL未定义对象lCASE_NOT_FOUNDCASE中若未包含相应的WHEN，并且没有设置lCOLLECTION_IS_NULL集合元素未初始化lCURSER_ALREADY_OPEN游标已经打开lDUP_VAL_ON_INDEX唯一索引对应的列上有重复的值lINVALID_CURSOR在不合法的游标上进行操作lINVALID_NUMB
23、ER内嵌的SQL语句不能将字符转换为数字lNO_DATA_FOUND使用selectinto未返回行，或应用索引表未初始化的lTOO_MANY_ROWS执行selectinto时，结果集超过一行lZERO_DIVIDE除数为0lSUBSCRIPT_BEYOND_COUNT元素下标超过嵌套表或VARRAY的最大值lSUBSCRIPT_OUTSIDE_LIMIT使用嵌套表或VARRAY时，将下标指定为负数lVALUE_ERROR赋值时，变量长度不足以容纳实际数据lLOGIN_DENIEDPL/SQL应用程序连接到oracle数据库时，提供了不正确的用户名或密码lNOT_LOGGED_ONPL/SQ
24、L应用程序在没有连接oralce数据库的情况下访问数据lPROGRAM_ERRORPL/SQL内部问题，可能需要重装数据字典pl./SQL系统包lROWTYPE_MISMATCH主游标变量与PL/SQL游标变量的返回类型不兼容lSELF_IS_NULL使用对象类型时，在null对象上调用对象方法lSTORAGE_ERROR运行PL/SQL时，超出内存空间lSYS_INVALID_ID无效的ROWID字符串lTIMEOUT_ON_RESOURCEOracle在等待资源时超时Oracle提供的异常处理提供的异常处理l自定义异常处理：lcreateorreplaceprocedurestu_proc
25、7(pnoinstudent.sno%type,lponinstudent.sno%typel)lislv_raiseexception;lv_namestudent.sname%type;lbeginlifpno=101thenlraisev_raise;lendif;lselectsnameintov_namefromstudentwheresno=111111;lexceptionlwhenv_raisethenlRAISE_APPLICATION_ERROR(-20010,ERROR:notexisted!);lwhenno_data_foundthenlRAISE_APPLICATI
26、ON_ERROR(-20011,ERROR：不存在！);lend;存储过程的异常处理实例存储过程的异常处理实例存储过程的事务处理存储过程的事务处理l事务用于确保数据的一致性，由一组相关的DML语句组成，该组DML语句所执行的操作要么全部确认，要么全部取消。l当执行事务操作(DML)时，Oracle会在被作用的表上加锁，以防止其他用户改变表结构，同时也会在被作用的行上加行锁，以防止其他事务在相应行上执行DML操作。l当执行事务提交或事务回滚时，Oracle会确认事务变化或回滚事务、结束事务、删除保存点、释放锁。提交事务（COMMIT）确认事务变化，结束当前事务、删除保存点，释放锁，使得当前事务中
27、所有未决的数据永久改变。保存点（SAVEPOINT）在当前事务中，标记事务的保存点。回滚事务（ROLLBACK）回滚整个事务，删除该事务所定义的所有保存点，释放锁，丢弃所有未决的数据改变。回滚事务到指定的保存点（ROLLBACK TO SAVEPOINT）回滚当前事务到指定的保存点，丢弃该保存点创建后的任何改变，释放锁。存储过程的事务处理存储过程的事务处理l当执行DDL、DCL语句，或退出SQL*PLUS时，会自动提交事务；l事务期间应避免与使用者互动；l查询数据期间，尽量不要启动事务；l尽可能让事务持续地越短越好；l在事务中尽可能存取最少的数据量。存储过程的事务处理存储过程的事务处理事务处理
28、实例事务处理实例l存储过程事务处理实例：lcreateorreplaceprocedurestu_proc8lislbeginlinsertintostudentvalues(102,sky,m,22,gong);lsavepointsavepoint1;linsertintostudentvalues(102,good,w,20,wang);ldbms_output.put_line(error);lupdatestudentsetsno=103wheresname=good;lcommit;lexceptionlwhendup_val_on_indexthenlrollbacktosave
29、pointsavepoint1;lRAISE_APPLICATION_ERROR(-20010,ERROR：违反唯一索引约束！);lend;数据库函数和存储过程的包数据库函数和存储过程的包l创建包（package）l函数：lcreateorreplacepackage包名as函数l注意：as后可加多个函数l存储过程：lCreateorreplacepackage包名as存储过程l注意：as后可加多个存储过程函数的包调用：call包名.函数名；存储过程的包调用：call包名.存储过程名；包的调用包的调用THE END谢谢进入夏天，少不了一个热字当头，电扇空调陆续登场，每逢此时，总会想起进入夏天，
30、少不了一个热字当头，电扇空调陆续登场，每逢此时，总会想起那一把蒲扇。蒲扇，是记忆中的农村，夏季经常用的一件物品。记忆中的故那一把蒲扇。蒲扇，是记忆中的农村，夏季经常用的一件物品。记忆中的故乡，每逢进入夏天，集市上最常见的便是蒲扇、凉席，不论男女老少，个个手持乡，每逢进入夏天，集市上最常见的便是蒲扇、凉席，不论男女老少，个个手持一把，忽闪忽闪个不停，嘴里叨叨着一把，忽闪忽闪个不停，嘴里叨叨着“怎么这么热怎么这么热”，于是三五成群，聚在大树，于是三五成群，聚在大树下，或站着，或随即坐在石头上，手持那把扇子，边唠嗑边乘凉。孩子们却在周下，或站着，或随即坐在石头上，手持那把扇子，边唠嗑边乘凉。孩子们却
31、在周围跑跑跳跳，热得满头大汗，不时听到围跑跑跳跳，热得满头大汗，不时听到“强子，别跑了，快来我给你扇扇强子，别跑了，快来我给你扇扇”。孩。孩子们才不听这一套，跑个没完，直到累气喘吁吁，这才一跑一踮地围过了，这时子们才不听这一套，跑个没完，直到累气喘吁吁，这才一跑一踮地围过了，这时母亲总是，好似生气的样子，边扇边训，母亲总是，好似生气的样子，边扇边训，“你看热的，跑什么？你看热的，跑什么？”此时这把蒲扇，此时这把蒲扇，是那么凉快，那么的温馨幸福，有母亲的味道！蒲扇是中国传统工艺品，在是那么凉快，那么的温馨幸福，有母亲的味道！蒲扇是中国传统工艺品，在我国已有三千年多年的历史。取材于棕榈树，制作简单
32、，方便携带，且蒲扇的表我国已有三千年多年的历史。取材于棕榈树，制作简单，方便携带，且蒲扇的表面光滑，因而，古人常会在上面作画。古有棕扇、葵扇、蒲扇、蕉扇诸名，实即面光滑，因而，古人常会在上面作画。古有棕扇、葵扇、蒲扇、蕉扇诸名，实即今日的蒲扇，江浙称之为芭蕉扇。六七十年代，人们最常用的就是这种，似圆非今日的蒲扇，江浙称之为芭蕉扇。六七十年代，人们最常用的就是这种，似圆非圆，轻巧又便宜的蒲扇。蒲扇流传至今，我的记忆中，它跨越了半个世纪，圆，轻巧又便宜的蒲扇。蒲扇流传至今，我的记忆中，它跨越了半个世纪，也走过了我们的半个人生的轨迹，携带着特有的念想，一年年，一天天，流向长也走过了我们的半个人生的轨迹，携带着特有的念想，一年年，一天天，流向长长的时间隧道，袅长的时间隧道，袅结束
