oracle存储过程.ppt

1、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）速度上:由于数据库执行动作时，是

2、先编译在执行，然而存储过程属于已编译过的代码块，所以执行效率比SQL执行效率快； 2）当对数据库进行复杂操作时(如对多个表进行 UPDATE,INSERT,QUERY,DELETE 时），可将这些复杂操作改用存储过程封装起来之后节后数据库提供的事务处理结合一起使用。这些操作，如果用程序来完成，就变成了一条条的 SQL 语句，可能要多次连接数据库。而换成存储，只需要连接一次数据库就可以了； 3）存储过程可以重复使用,可减少数据库开发人员的工作量，如果内容修改后，可直接重新编译即可使用，相对于同功能的后台实现，可以减少服务器重启等。,存储过程优点,3）安全性高,可设定只有某此用户才具有对指定存储过

3、程的使用权; 4）使用存储过程，可以实现存储过程设计和编码工作分开进行，只要将存储过程名、参数、及返回信息告诉编码人员即可；,存储过程的语法结构,结构如下: create or replace procedure存储过程名称(参数1 in (out) 类型.) as（is） 声明语句段,如声明存储过程中所要使用到的局部参数变量，以及游标的声明等等。 begin 执行语句段，具体的业务逻辑; exception 异常处理语句段; end 存储过程名称;,存储过程的参数,参数说明： 存储过程的参数类型：char,varchar2,number,Integer,Date,以及oracle 的其他数据

4、类型。 存储过程中的变量声明： 第一种：直接声明如，字段名 类型名(长度)，fieldname varchar2(32) 第二种：根据某张表的表字段类型声明： 字段名 表名称.字段名%type fieldname student.sno%type,参数过程的调用执行,PLSQL中调用存储过程的语句 命令窗口模式中的调用执行： exec 存储过程名称（参数1,2.） 注：调用的过程中，不论有没有参数，其()可有可无。 存储过程中调用另一个存储过程 存储过程名称(); 注：调用的过程中，不论有没有参数，其()是必不可少的。 后台方法的调用存储过程 无参存储过程调用：call procedure_n

5、ame() ; 有输入参数的调用：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) );

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.

7、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

8、|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 i

9、n 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

10、 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 varc

11、har2 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) retu

12、rn 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 i

13、s 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对orac

14、le存储过程的调用,无参数过程： 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(

15、); String sql=call stu_proc3(?,?); Object params = 1,222; String str = oper.executeprocedure_inAndOut(sql,params); return str;,JAVA调用实例,游标,什么是游标？ 从表中检索出结果集，从中每次指向一条记录进行交互的机制 游标从概念上讲基于数据库的表返回结果集 游标有什么作用？ 指定结果集中特定行的位置。基于当前的结果集位置检索一行或连续的几行。在结果集的当前位置修改行中的数据。对其他用户所做的数据更改定义不同的敏感性级别。可以以编程的方式访问数据库。,游标的声明,FO

16、R循环游标 -（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