oracle存储过程

,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,Oracle,存储过程,什么是存储过程？为啥要用存储过程？,存储过程：存储过程可以说是一个记录集。它是由一些,T-SQL(,Transact-SQL = SQL,程式设计语言的增强版,T-SQL,包含了,SQL,如,select * from TABLE,这既是一个,T-SQL,又是,SQL,语句，例如,BEGIN: DBMS_OUTPUT(,1111,); END,这属于,T-SQL,语句 不是,SQL,语句,),语句组成的代码块，这些,T-SQL,语句代码像一个方法一样实现一些功能（,如对单表或多表的增删改查,），然后再给这个代码块取一个名字，在用到这个功能的时候调用对应名称即可。,存储过程优点,1,）速度上,:,由于数据库执行动作时，是先编译在执行，然而存储过程属于已编译过的代码块，所以执行效率比,SQL,执行效率快；,2,）当对数据库进行复杂操作时,(,如对多个表进行,UPDATE,INSERT,QUERY,DELETE,时,），可将这些复杂操作改用存储过程封装起来之后节后数据库提供的事务处理结合一起使用。这些操作，如果用程序来完成，就变成了一条条的,SQL,语句，可能要多次连接数据库。而换成存储，只需要连接一次数据库就可以了；,3,）,存储过程可以重复使用,可减少数据库开发人员的工作量，如果内容修改后，可直接重新编译即可使用，相对于同功能的后台实现，可以减少服务器重启等。,存储过程优点,3,）,安全性高,可设定只有某此用户才具有对指定存储过程的使用权,;,4,）使用存储过程，可以实现存储过程设计和编码工作分开进行，只要将存储过程名、参数、及返回信息告诉编码人员即可；,存储过程的语法结构,结构如下,: create or replace procedure,存储过程名称,(,参数,1 in (out),类型,.),as,（,is,）,声明语句段,如声明存储过程中所要使用到的局部参数变量，以及游标的声明等等。,begin,执行语句段，具体的业务逻辑,;,exception,异常处理语句段,;,end,存储过程名称,;,存储过程的参数,参数说明：,存储过程的参数类型：,char,varchar2,number,Integer,Date,以及,oracle,的其他数据类型。,存储过程中的变量声明：,第一种：直接声明如，字段名 类型名,(,长度,),，,fieldname varchar2(32),第二种：根据某张表的表字段类型声明：,字段名 表名称,.,字段名,%type fieldname student.sno%type,参数过程的调用执行,PLSQL,中调用存储过程的语句,命令窗口模式中的调用执行：,exec,存储过程名称（参数,1,2.,）,注：调用的过程中，不论有没有参数，其,(),可有可无。,存储过程中调用另一个存储过程,存储过程名称,();,注：调用的过程中，不论有没有参数，其,(),是必不可少的。,后台方法的调用存储过程,无参存储过程调用：,call procedure_name() ;,有输入参数的调用：,call procedure_name(?,?.) ;,创建过程实例,创建名为,stu_proc,的过程,create,是创建过程的标识符,replace,表示若同名过程存在将覆盖原过程,.,该过程定义了一个变量,其类型和,student,数据表中的,sname,字段类型相同,都是字符型,将数据表中的,sno,字段为,1,的,sname,字段内容送入变量中,然后输出结果,.,表结构：,create table student(,guid varchar2(32) primary key ,sno number(6),sname varchar2(32),pno number(6),);,alter table student modify guid default sys_guid();,参数过程实例,无参数存储过程（,包含两种方式的变量声明,）：,create or replace procedure stu_proc as(is),-,变量的声明区,pname varchar2(25);,begin,select sname into pname from student where sno=1;,dbms_output.put_line(pname);,end;,或,create or replace procedure stu_proc as,pname student.sname%type;,begin,select sname into p_name from student where sno=1;,dbms_output.put_line(pname);,end;,仅有输入参数的过程,create or replace procedure stu_proc1(pno in student.sno%type,pno1 in varchar2) as,pname varchar2(25);,begin,select sname into pname from student where sno=pno;,dbms_output.put_line(pname|pno1);,end;,存储过程,仅有输出参数的存储过程,create or replace procedure stu_proc2(,pname,out,student.sname%type,) as,begin,select sname into pname from student where sno=1;,dbms_output.put_line(pname);,end;,此种存储过程不能直接用,call,来调用，这种情况的调用将在下面,oracle,函数调用中说明,参数过程实例,有输入,输出参数的存储过程：,create or replace procedure stu_proc3,(pno,in,student.sno%type,pname,out,student.sname%type) as,begin,select sname into pname from student where sno=pno;,dbms_output.put_line(pname);,end;,此种存储过程不能直接用,call,来调用，这种情况的调用将在下面,oracle,函数调用中说明,参数过程实例,Oracle,函数调用存储过程,我们已经学习了,oracle,函数，下面就针对参数的,4,种情况分别举出实例说明函数对存储过程的调用,对无参数过程的调用：,-,函数声明,create or replace function get_pname,return varchar2 is,pname varchar2(20);,begin,stu_proc;,select sname into pname from student where sno=1;,return pname;,end;,测试窗口中的调用,declare,begin,dbms_output.put_line(,在,PL/SQL,中打印的结果：,|get_pname);,end;,函数调用存储过程实例,对有输入参数过程的调用：,create or replace function get_pname1(pno in number) return varchar2 is,pname varchar2(20);,begin,stu_proc1(pno in student.sno%type),select sname into pname from student where sno=pno;,return pname;,end;,-,调用,declare,begin,dbms_output.put_line(,在,PL/SQL,中打印的结果：,|get_pname1(2);,end;,函数调用存储过程实例,对有输出参数过程的调用,:,create or replace function get_pname2(pname out varchar2) return varchar2 is,begin,stu_proc2(pname out student.sname%type);,return pname;,end;,-,调用,declare,pname student.sname%type;,begin,dbms_output.put_line(,在,PL/SQL,中打印的结果：,|get_pname2(pname);,end;,函数调用存储过程实例,对有输入,输出参数过程的调用,:,create or replace function get_pname3(pno in number,pname out varchar2) return varchar2 is,begin,stu_proc3(pno in student.sno%type,pname out student.sname%type);,return pname;,end;,-,调用,declare,pname student.sname%type;,begin,dbms_output.put_line(,在,PL/SQL,中打印的结果：,|get_pname3(2,pname);,end;,函数调用存储过程实例,JAVA,调用数据库存储过程,前面我们已经讲述了有关,oracle,数据库的存储过程的几种形式，以及,oracle,函数对存储过程的调用，下面我将根据上面存储过程的实例来举出,JAVA,对,oracle,存储过程的调用,无参数过程：,DbOper oper = DbOper.,getdbOper();,String sql=call stu_proc();,oper.,executeprocedure(sql);,JAVA,调用实例,仅有返回值的过程,public void no_paramsCall(),DbOper oper = DbOper.,getdbOper();,String sql=call stu_proc();,oper.,executeprocedure(sql);,JAVA,调用实例,既有输入参数又有输出参数的过程,DbOper oper = DbOper.,getdbOper();,String sql=call stu_proc3(?,?);,Object params = 1,222;,String str = oper.,executeprocedure_inAndOut(sql,params);,return str;,JAVA,调用实例,游标,什么是游标？,从表中检索出结果集，从中每次指向一条记录进行交互的机制,游标从概念上讲基于数据库的表返回结果集,游标有什么作用？,指定结果集中特定行的位置。基于当前的结果集位置检索一行或连续的几行。在结果集的当前位置修改行中的数据。对其他用户所做的数据更改定义不同的敏感性级别。可以以,编程,的方式访问数据库。,游标的声明,FOR,循环游标,-,（,1,）定义游标,-,（,2,）定义游标变量,-,（,3,）使用,for,循环来使用这个游标,declare,-,类型定义,cursor c_student,is,select *,from student,-,定义一个游标变量,v_cinfo c_emp%ROWTYPE,，该类型为游标,c_emp,中的一行数据类型,c_row c_student %rowtype;,begin,for c_row in c_student loop,dbms_output.put_line(c_row.pno|-|c_row.pname);,end loop;,end;,游标的声明,Fetch,游标,-,使用的时候必须要明确的打开和关闭,declare,-,类型定义,cursor c_student,is,select * from student;,-,定义一个游标变量,c_row c_student%rowtype;,begin,open c_student;,loop,-,提取一行数据到,c_row,fetch c_student into c_row;,-,判读是否提取到值，没取到值就退出,-,取到值,c_job%notfound,是,false,-,取不到值,c_job%notfound,是,true,exit when c_job%notfound;,dbms_output.put_line(c_row.pno|-|c_rowpname);,end loop;,-,关闭游标,close c_job;,end;,数据库函数和存储过程的包,创建包（,package,）,函数：,create or replace package,包名,as,函数,注意：,as,后可加多个函数,存储过程：,Create or replace package,包名,as,存储过程,注意：,as,后可加多个存储过程,函数的包调用：,call,包名,.,函数名；,存储过程的包调用：,call,包名,.,存储过程名；,包的调用,THE END,谢谢,