oracle存储过程(超级有用)教学课件

Oracle存储过程基础和实例2020/12/111存储过程特点1）存储过程是预编译过的，并且经优化后存储于SQL内存中，使用时无需再次编译，提高了工作效率；2）存储过程的代码直接存放于数据库中，一般由客户端直接通过存储过程的名字进行调用，减少了网络流量，加快了系统执行速度，例如在进行百万以上的大批量数据查询时，使用存储过程分页要比其他方式分页快得多；3）使用存储过程可以减少SQL注入式攻击，提高了系统的安全性，执行存储过程的用户要具有一定的权限才能使用存储过程，没有数据操作权限的用户只能在其控制下间接地存取数据;2020/12/112存储过程特点4）在同时进行主、从表及多表间的数据维护及有效性验证时，使用存储过程比较方便，而且可以有效利用SQL中的事务处理的机制；5）使用存储过程，可以实现存储过程设计和编码工作分开进行，只要将存储过程名、参数、及返回信息告诉编码人员即可；6）但使用存储过程封装业务逻辑将限制应用程序的可移植性；另外，如果更改存储过程的参数或者其返回的数据及类型的话，需要修改应用程序的相关代码，比较繁琐。2020/12/113过程的语法结构完整的过程结构如下:

createorreplaceprocedure过程名as

声明语句段;

begin

执行语句段;

exception

异常处理语句段;

end;过程是有名称的程序块,as关键词代替了无名块的declare.2020/12/114创建过程实例创建名为stu_proc的过程,create是创建过程的标识符,replace表示若同名过程存在将覆盖原过程.该过程定义了一个变量,其类型和student数据表中的sname字段类型相同,都是字符型,将数据表中的sno字段为1的sname字段内容送入变量中,然后输出结果.2020/12/115参数SQL中调用存储过程语句：call

procedure_name();调用时”()”是不可少的，无论是有参数还是无参数。定义对数据库过程的调用时

无参数过程:{

call

procedure_name}

仅有输入参数的过程：{call

procedure_name(?,?...)}

这里?表示输入参数，创建存储过程时用in表示输入参数

仅有输出参数的过程:{

Call

procedure_name(?,?...)}

这里的？表示输出参数，创建存储过程时用out表示输入参数

既有输入参数又有输出参数的过程 {call

procedure_name(?,?...)}

这里的？有表示输出参数的，也有表示输入参数的

下面将会对这4种情况分别举出实例！！！2020/12/116参数过程实例无参数存储过程：createorreplaceprocedurestu_procaspnamevarchar2(25);beginselectsnameintopnamefromstudentwheresno=1;dbms_output.put_line(pname);end;或者createorreplaceprocedurestu_procaspnamestudent.sname%type;beginselectsnameintop_namefromstudentwheresno=1;dbms_output.put_line(pname);end;2020/12/117仅有输入参数的过程createorreplaceprocedurestu_proc1(pnoinstudent.sno%type)aspnamevarchar2(25);beginselectsnameintopnamefromstudentwheresno=pno;dbms_output.put_line(pname);end;参数过程实例2020/12/118仅有输出参数的存储过程createorreplaceprocedurestu_proc2(pnameoutstudent.sname%type)asbeginselectsnameintopnamefromstudentwheresno=1;dbms_output.put_line(pname);end;此种存储过程不能直接用call来调用，这种情况的调用将在下面oracle函数调用中说明参数过程实例2020/12/119有输入\输出参数的存储过程：createorreplaceprocedurestu_proc3(pnoinstudent.sno%type,pnameoutstudent.sname%type)asbeginselectsnameintopnamefromstudentwheresno=pno;dbms_output.put_line(pname);end;此种存储过程不能直接用call来调用，这种情况的调用将在下面oracle函数调用中说明参数过程实例2020/12/1110Oracle函数调用存储过程我们已经学习了oracle函数，下面就针对参数的4种情况分别举出实例说明函数对存储过程的调用2020/12/1111对无参数过程的调用：

--函数createorreplacefunctionget_pnamereturnvarchar2ispnamevarchar2(20);beginstu_proc;selectsnameintopnamefromstudentwheresno=1;returnpname;end;--调用declarebegindbms_output.put_line('在PL/SQL中打印的结果：'||get_pname);end;函数调用存储过程实例2020/12/1112对有输入参数过程的调用：createorreplacefunctionget_pname1(pnoinnumber)returnvarchar2ispnamevarchar2(20);beginstu_proc1(pnoinstudent.sno%type)selectsnameintopnamefromstudentwheresno=pno;returnpname;end;--调用declarebegindbms_output.put_line('在PL/SQL中打印的结果：'||get_pname1(2));end;函数调用存储过程实例2020/12/1113对有输出参数过程的调用:createorreplacefunctionget_pname2(pnameoutvarchar2)returnvarchar2isbeginstu_proc2(pnameoutstudent.sname%type);returnpname;end;--调用declarepnamestudent.sname%type;begindbms_output.put_line('在PL/SQL中打印的结果：'||get_pname2(pname));end;函数调用存储过程实例2020/12/1114对有输入\输出参数过程的调用:createorreplacefunctionget_pname3(pnoinnumber,pnameoutvarchar2)returnvarchar2isbeginstu_proc3(pnoinstudent.sno%type,pnameoutstudent.sname%type);returnpname;end;--调用declarepnamestudent.sname%type;begindbms_output.put_line('在PL/SQL中打印的结果：'||get_pname3(2,pname));end;

函数调用存储过程实例2020/12/1115JAVA调用数据库存储过程前面我们已经讲述了有关oracle数据库的存储过程的几种形式，以及oracle函数对存储过程的调用，下面我将根据上面存储过程的实例来举出JAVA对oracle存储过程的调用2020/12/1116仅有返回值的过程：public

static

voidmain(String[]args){Connectionconn=BBConnection.getConnection();Stringsql="{callstu_proc2(?)}";try{CallableStatementstatement=conn.prepareCall(sql);statement.registerOutParameter(1,Types.VARCHAR);statement.execute();Stringpname=statement.getString(1);System.out.println(pname);}catch(SQLExceptione){//TODOAuto-generatedcatchblocke.printStackTrace();}}}JAVA调用实例2020/12/1117既有输入参数又有输出参数的过程public

static

voidmain(String[]args){Connectionconn=BBConnection.getConnection();Stringsql="{callstu_proc3(?,?)}";try{CallableStatementstatement=conn.prepareCall(sql);statement.setInt(1,1);statement.registerOutParameter(2,Types.VARCHAR);statement.execute();Stringpname=statement.getString(2);System.out.println(pname);}catch(SQLExceptione){//TODOAuto-generatedcatchblocke.printStackTrace();}}}JAVA调用实例2020/12/1118下面将举出无out参数的调用实例这种参数不适于用在查询语句上，因为查询语句需要有返回值才能被JAVA调用返回到OUT参数中的值可能会是JDBCNULL。当出现这种情形时，将对JDBCNULL值进行转换以使getXXX方法所返回的值为null、0或false，这取决于getXXX方法类型。对于ResultSet对象，要知道0或false是否源于JDBCNULL的唯一方法，是用方法wasNull进行检测。如果getXXX方法读取的最后一个值是JDBCNULL，则该方法返回true，否则返回flase

JAVA调用实例2020/12/1119仅有参数的过程：public

static

voidmain(String[]args){Connectionconn=BBConnection.getConnection();Stringsql="{callstu_proc1(?)}";try{CallableStatementstatement=conn.prepareCall(sql);statement.setInt(1,1);statement.execute();System.out.println(statement.execute());}catch(SQLExceptione){//TODOAuto-generatedcatchblocke.printStackTrace();}finally{try{conn.close();}catch(SQLExceptione){//TODOAuto-generatedcatchblocke.printStackTrace();}}}}JAVA调用实例2020/12/1120无参数过程：public

static

voidmain(String[]args){Connectionconn=BBConnection.getConnection();Stringsql="{callstu_proc()}";try{CallableStatementstatement=conn.prepareCall(sql);statement.execute();System.out.println(statement.execute());}catch(SQLExceptione){//TODOAuto-generatedcatchblocke.printStackTrace();}finally{try{conn.close();}catch(SQLExceptione){//TODOAuto-generatedcatchblocke.printStackTrace();} }}JAVA调用实例2020/12/1121通过调用数据库函数调用存储过程下面将举一个通过数据库函数来调用存储过程：public

static

voidmain(String[]args){Connectionconn=BBConnection.getConnection();Stringsql="{?=callget_pname1(?)}";try{CallableStatementstatement=conn.prepareCall(sql);statement.registerOutParameter(1,Types.VARCHAR);statement.setInt(2,1);statement.execute();System.out.println(statement.getString(1));}catch(SQLExceptione){//TODOAuto-generatedcatchblocke.printStackTrace();}finally{try{conn.close();}catch(SQLExceptione){//TODOAuto-generatedcatchblocke.printStackTrace();}}}}？表示函数return的值，get_pname1是数据库函数名存储过程的out和in都是以参数传进，这就是函数和存储过程的区别之一2020/12/1122存储过程的异常处理为了提高存储过程的健壮性，避免运行错误，当建立存储过程时应包含异常处理部分。异常(EXCEPTION)是一种PL/SQL标识符，包括预定义异常、非预定义异常和自定义异常；预定义异常是指由PL/SQL提供的系统异常；非预定义异常用于处理与预定义异常无关的Oracle错误(如完整性约束等)；自定义异常用于处理与Oracle错误的其他异常情况。RAISE_APPLICATION_ERROR用于自定义错误消息，并且消息号必须在－20000～－20999之间2020/12/1123Oracle数据库中提供了一些异常处理的方法，下面通过一个实例来说明createorreplaceprocedurestu_proc6(pnoinstudent.sno%type,pnameoutstudent.sname%type)isbeginselectsnameintopnamefromstudentwheresno=pno;EXCEPTIONwhenNO_DATA_FOUNDthenRAISE_APPLICATION_ERROR(-20011,'ERROR：不存在！');end;存储过程的异常处理实例2020/12/1124

命名的系统异常

产生原因ACCESS_INTO_NULL 未定义对象CASE_NOT_FOUND CASE中若未包含相应的WHEN，并且没有设置COLLECTION_IS_NULL 集合元素未初始化CURSER_ALREADY_OPEN

游标已经打开DUP_VAL_ON_INDEX 唯一索引对应的列上有重复的值INVALID_CURSOR 在不合法的游标上进行操作INVALID_NUMBER 内嵌的SQL语句不能将字符转换为数字NO_DATA_FOUND 使用selectinto未返回行，或应用索引表未初始化的

TOO_MANY_ROWS 执行selectinto时，结果集超过一行ZERO_DIVIDE 除数为0SUBSCRIPT_BEYOND_COUNT 元素下标超过嵌套表或VARRAY的最大值SUBSCRIPT_OUTSIDE_LIMIT 使用嵌套表或VARRAY时，将下标指定为负数VALUE_ERROR 赋值时，变量长度不足以容纳实际数据LOGIN_DENIED

PL/SQL应用程序连接到oracle数据库时，提供了不正 确的用户名或密码NOT_LOGGED_ON PL/SQL应用程序在没有连接oralce数据库的情况下访 问数据PROGRAM_ERROR PL/SQL内部问题，可能需要重装数据字典＆pl./SQL系 统包ROWTYPE_MISMATCH 主游标变量与PL/SQL游标变量的返回类型不兼容SELF_IS_NULL 使用对象类型时，在null对象上调用对象方法STORAGE_ERROR 运行PL/SQL时，超出内存空间SYS_INVALID_ID 无效的ROWID字符串TIMEOUT_ON_RESOURCE Oracle在等待资源时超时Oracle提供的异常处理2020/12/1125自定义异常处理：createorreplaceprocedurestu_proc7(pnoinstudent.sno%type,poninstudent.sno%type)is

v_raiseexception;v_namestudent.sname%type;begin

ifpno=101thenraisev_raise;endif;selectsnameintov_namefromstudentwheresno=111111;exception

whenv_raisethenRAISE_APPLICATION_ERROR(-20010,'ERROR:notexisted!');whenno_data_foundthenRAISE_APPLICATION_ERROR(-20011,'ERROR：不存在！');end;存储过程的异常处理实例2020/12/1126存储过程的事务处理事务用于确保数据的一致性，由一组相关的DML语句组成，该组DML语句所执行的操作要么全部确认，要么全部取消。当执行事务操作(DML)时，Oracle会在被作用的表上加锁，以防止其他用户改变表结构，同时也会在被作用的行上加行锁，以防止其他事务在相应行上执行DML操作。当执行事务提交或事务回滚时，Oracle会确认事务变化或回滚事务、结束事务、删除保存点、释放锁。2020/12/1127提交事务（COMMIT）确认事务变化，结束当前事务、删除保存点，释放锁，使得当前事务中所有未决的数据永久改变。保存点（SAVEPOINT）在当前事务中，标记事务的保存点。回滚事务（ROLLBACK）回滚整个事务，删除该事务所定义的所有保存点，释放锁，丢弃所有未决的数据改变。回滚事务到指定的保存点（ROLLBACKTOSAVEPOINT）回滚当前事务到指定的保存点，丢弃该保存点创建后的任何改变，释放锁。存储过程的事务处理2020/12/1128当执行DDL、DCL语句，或退出SQL*PLUS时，会自动提交事务；事务期间应避免与使用者互动；查询数据期间，尽量不要启动事务；尽可能让事务持续地越短越好；在事务中尽可能存取最少的数据量。存储过程的事务处理2020/12/1129事务处理实例存储过程事务处理实例：createorreplaceprocedurestu_proc8isbegininsertintostudentvalues(102,'sky','m',22,'gong');savepointsavepoint1;insertintostudentvalues(102,'good','w',20,'wang');dbms_output.put_line('error');updatestudentsetsno=103wheresname='good';commit;exceptionwhendup_val_on_indexthenrollbacktosavepointsavepoint1;RAISE_APPLICATION_ERROR(-20010,'ERROR：违反唯一索引约束！');end;2020/12/1130数据库函数和存储过程的包创建包（package）函数：createorreplacepackage包名as函数注意：as后可加多个函数存储过程：Createorreplacepackage包名as存储过程注意：as后可加多个存储过程2020/12/1131函数的包调用：call包名.函数名；存储过程的包调用：call包名.存储过程名；包的调用2020/12/1132无参数存储过程--无参数存储过程createprocedurestu_proc1asp_namevarchar2(32);beginselectsnameintop_namefromst