Oracle学习之存储过程

Oracle存储过程讲解过程就是高级程序设计语言中的模块的概念,将一些内部联系的命令组成一个个过程,通过参数在过程之间传递数据是模块化设计思想的重要内容.过程1）存储过程是预编译过的，并且经优化后存储于SQL内存中，使用时无需再次编译，提高了工作效率；2）存储过程的代码直接存放于数据库中，一般由客户端直接通过存储过程的名字进行调用，减少了网络流量，加快了系统执行速度，例如在进行百万以上的大批量数据查询时，使用存储过程分页要比其他方式分页快得多；3）使用存储过程可以减少SQL注入式攻击，提高了系统的安全性，执行存储过程的用户要具有一定的权限才能使用存储过程，没有数据操作权限的用户只能在其控制下间接地存取数据;存储过程特点4）在同时进行主、从表及多表间的数据维护及有效性验证时，使用存储过程比较方便，而且可以有效利用SQL中的事务处理的机制；5）使用存储过程，可以实现存储过程设计和编码工作分开进行，只要将存储过程名、参数、及返回信息告诉编码人员即可；6）但使用存储过程封装业务逻辑将限制应用程序的可移植性；另外，如果更改存储过程的参数或者其返回的数据及类型的话，需要修改应用程序的相关代码，比较繁琐。存储过程特点完整的过程结构如下:createorreplaceprocedure过程名as声明语句段;begin执行语句段;exception异常处理语句段;end;过程是有名称的程序块,as关键词代替了无名块的declare.过程的语法结构创建名为stu_proc的过程,create是创建过程的标识符,replace表示若同名过程存在将覆盖原过程.该过程定义了一个变量,其类型和student数据表中的sname字段类型相同,都是字符型,将数据表中的sno字段为1的sname字段内容送入变量中,然后输出结果.创建过程实例SQL中调用存储过程语句：callprocedure_name();调用时”()”是不可少的，无论是有参数还是无参数。定义对数据库过程的调用时无参数过程:{callprocedure_name}仅有输入参数的过程：{callprocedure_name(?,?...)}这里?表示输入参数，创建存储过程时用in表示输入参数仅有输出参数的过程:{Callprocedure_name(?,?...)}这里的？表示输出参数，创建存储过程时用out表示输入参数既有输入参数又有输出参数的过程{callprocedure_name(?,?...)}这里的？有表示输出参数的，也有表示输入参数的下面将会对这4种情况分别举出实例！！！参数无参数存储过程：createorreplaceprocedurestu_procaspnamevarchar2(25);beginselectsnameintopnamefromstudentwheresno=1;dbms_output.put_line(pname);end;或者createorreplaceprocedurestu_procaspnamestudent.sname%type;beginselectsnameintop_namefromstudentwheresno=1;dbms_output.put_line(pname);end;参数过程实例仅有输入参数的过程createorreplaceprocedurestu_proc1(pnoinstudent.sno%type)aspnamevarchar2(25);beginselectsnameintopnamefromstudentwheresno=pno;dbms_output.put_line(pname);end;参数过程实例仅有输出参数的存储过程createorreplaceprocedurestu_proc2(pnameoutstudent.sname%type)asbeginselectsnameintopnamefromstudentwheresno=1;dbms_output.put_line(pname);end;此种存储过程不能直接用call来调用，这种情况的调用将在下面oracle函数调用中说明参数过程实例有输入\输出参数的存储过程：createorreplaceprocedurestu_proc3(pnoinstudent.sno%type,pnameoutstudent.sname%type)asbeginselectsnameintopnamefromstudentwheresno=pno;dbms_output.put_line(pname);end;此种存储过程不能直接用call来调用，这种情况的调用将在下面oracle函数调用中说明参数过程实例我们已经学习了oracle函数，下面就针对参数的4种情况分别举出实例说明函数对存储过程的调用Oracle函数调用存储过程对无参数过程的调用：--函数createorreplacefunctionget_pnamereturnvarchar2ispnamevarchar2(20);beginstu_proc;selectsnameintopnamefromstudentwheresno=1;returnpname;end;--调用declarebegindbms_output.put_line('在PL/SQL中打印的结果：'||get_pname);end;函数调用存储过程实例对有输入参数过程的调用：createorreplacefunctionget_pname1(pnoinnumber)returnvarchar2ispnamevarchar2(20);beginstu_proc1(pnoinstudent.sno%type)selectsnameintopnamefromstudentwheresno=pno;returnpname;end;--调用declarebegindbms_output.put_line('在PL/SQL中打印的结果：'||get_pname1(2));end;函数调用存储过程实例对有输出参数过程的调用:createorreplacefunctionget_pname2(pnameoutvarchar2)returnvarchar2isbeginstu_proc2(pnameoutstudent.sname%type);returnpname;end;--调用declarepnamestudent.sname%type;begindbms_output.put_line('在PL/SQL中打印的结果：'||get_pname2(pname));end;函数调用存储过程实例对有输入\输出参数过程的调用:createorreplacefunctionget_pname3(pnoinnumber,pnameoutvarchar2)returnvarchar2isbeginstu_proc3(pnoinstudent.sno%type,pnameoutstudent.sname%type);returnpname;end;--调用declarepnamestudent.sname%type;begindbms_output.put_line('在PL/SQL中打印的结果：'||get_pname3(2,pname));end;函数调用存储过程实例前面我们已经讲述了有关oracle数据库的存储过程的几种形式，以及oracle函数对存储过程的调用，下面我将根据上面存储过程的实例来举出JAVA对oracle存储过程的调用JAVA调用数据库存储过程仅有返回值的过程：publicstaticvoidmain(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-generatedcatchblocke.printStackTrace();}}}JAVA调用实例既有输入参数又有输出参数的过程publicstaticvoidmain(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-generatedcatchblocke.printStackTrace();}}}JAVA调用实例下面将举出无out参数的调用实例这种参数不适于用在查询语句上，因为查询语句需要有返回值才能被JAVA调用返回到OUT参数中的值可能会是JDBCNULL。当出现这种情形时，将对JDBCNULL值进行转换以使getXXX方法所返回的值为null、0或false，这取决于getXXX方法类型。对于ResultSet对象，要知道0或false是否源于JDBCNULL的唯一方法，是用方法wasNull进行检测。如果getXXX方法读取的最后一个值是JDBCNULL，则该方法返回true，否则返回flaseJAVA调用实例仅有参数的过程：publicstaticvoidmain(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-generatedcatchblocke.printStackTrace();}finally{try{conn.close();}catch(SQLExceptione){//TODOAuto-generatedcatchblocke.printStackTrace();}}}}JAVA调用实例无参数过程：publicstaticvoidmain(String[]args){Connectionconn=BBConnection.getConnection();Stringsql={callstu_proc()};try{CallableStatementstatement=conn.prepareCall(sql);statement.exe