oracle存储过程

1、什么是存储过程？为啥要用存储过程？什么是存储过程？为啥要用存储过程？l存储过程：存储过程可以说是一个记录集。它是由一些T-SQL(Transact-SQL = SQL Transact-SQL = SQL 程式设计语言的增强版程式设计语言的增强版,T-,T-SQLSQL包含了包含了SQL,SQL,如如select select * * from TABLE from TABLE 这既是一个这既是一个 T-SQLT-SQL又是又是SQLSQL语句，例如语句，例如BEGIN: DBMS_OUTPUT(1111); END BEGIN: DBMS_OUTPUT(1111); END 这属于这属于T-

2、SQLT-SQL语句语句 不是不是SQLSQL语句语句)语句组成的代码块，这些T-SQL语句代码像一个方法一样实现一些功能（如对单表或多表的增删改查如对单表或多表的增删改查），然后再给这个代码块取一个名字，在用到这个功能的时候调用对应名称即可。存储过程优点存储过程优点l1）速度上:由于数据库执行动作时，是先编译在执行，然而存储过程属于已编译过的代码块，所以执行效率比SQL执行效率快；l2）当对数据库进行复杂操作时(如对多个表进行如对多个表进行 UPDATE,INSERT,QUERY,DELETE UPDATE,INSERT,QUERY,DELETE 时时），可将这些复杂操作改用存储过程封装起来

3、之后节后数据库提供的事务处理结合一起使用。这些操作，如果用程序来完成，就变成了一条条的 SQL 语句，可能要多次连接数据库。而换成存储，只需要连接一次数据库就可以了；l3）存储过程可以重复使用,可减少数据库开发人员的工作量，如果内容修改后，可直接重新编译即可使用，相对于同功能的后台实现，可以减少服务器重启等。存储过程优点存储过程优点3）安全性高,可设定只有某此用户才具有对指定存储过程的使用权;4）使用存储过程，可以实现存储过程设计和编码工作分开进行，只要将存储过程名、参数、及返回信息告诉编码人员即可；存储过程的语法结构存储过程的语法结构结构如下: create or replace proce

4、dure存储过程名称(参数1 in (out) 类型.) as（is）声明语句段,如声明存储过程中所要使用到的局部参数变量，以及游标的声明等等。 begin 执行语句段，具体的业务逻辑; exception 异常处理语句段; end 存储过程名称;存储过程的参数存储过程的参数l参数说明：l存储过程的参数类型：char,varchar2,number,Integer,Date,以及oracle 的其他数据类型。l存储过程中的变量声明：l第一种：直接声明如，字段名 类型名(长度)，fieldname varchar2(32)l第二种：根据某张表的表字段类型声明： 字段名 表名称.字段名%type

5、fieldname student.sno%type参数过程的调用执行参数过程的调用执行lPLSQL中调用存储过程的语句l命令窗口模式中的调用执行： exec 存储过程名称（参数1,2.） 注：调用的过程中，不论有没有参数，其()可有可无。l存储过程中调用另一个存储过程 存储过程名称(); 注：调用的过程中，不论有没有参数，其()是必不可少的。l后台方法的调用存储过程 无参存储过程调用：call procedure_name() ; 有输入参数的调用：call procedure_name(?,?.) ;创建过程实例创建过程实例l创建名为stu_proc的过程,create是创建过程的标识符,

6、replace表示若同名过程存在将覆盖原过程.l该过程定义了一个变量,其类型和student数据表中的sname字段类型相同,都是字符型,将数据表中的sno字段为1的sname字段内容送入变量中,然后输出结果.表结构：lcreate table student(l guid varchar2(32) primary key ,l sno number(6),l sname varchar2(32),l pno number(6)l);lalter table student modify guid default sys_guid();参数过程实例参数过程实例无参数存储过程（包含两种方式的变量

7、声明）：lcreate or replace procedure stu_proc as(is)l-变量的声明区lpname varchar2(25);lbeginl select sname into pname from student where sno=1;l dbms_output.put_line(pname);lend;l或lcreate or replace procedure stu_proc aslpname student.sname%type;lbeginl select sname into p_name from student where sno=1;l dbms

8、_output.put_line(pname);lend;l仅有输入参数的过程lcreate or replace procedure stu_proc1(pno in student.sno%type,pno1 in varchar2) aslpname varchar2(25);lbeginl select sname into pname from student where sno=pno;l dbms_output.put_line(pname|pno1);l end;存储过程存储过程l仅有输出参数的存储过程lcreate or replace procedure stu_proc2

9、(pname out student.sname%type) aslbeginl select sname into pname from student where sno=1;l dbms_output.put_line(pname);l end;l此种存储过程不能直接用call来调用，这种情况的调用将在下面oracle函数调用中说明参数过程实例参数过程实例l有输入输出参数的存储过程：lcreate or replace procedure stu_proc3l(pno in student.sno%type,pname out student.sname%type) aslbeginl

10、select sname into pname from student where sno=pno;l dbms_output.put_line(pname);l end;l此种存储过程不能直接用call来调用，这种情况的调用将在下面oracle函数调用中说明参数过程实例参数过程实例Oracle函数调用存储过程函数调用存储过程l我们已经学习了oracle函数，下面就针对参数的4种情况分别举出实例说明函数对存储过程的调用l对无参数过程的调用：l -函数声明lcreate or replace function get_pname return varchar2 is l pname varch

11、ar2(20);l beginl stu_proc;l select sname into pname from student where sno=1;l return pname;l end;l测试窗口中的调用,ldeclarelbeginl dbms_output.put_line(在PL/SQL中打印的结果：|get_pname);l end;函数调用存储过程实例函数调用存储过程实例l对有输入参数过程的调用：lcreate or replace function get_pname1(pno in number) return varchar2 is l pname varchar2(

12、20);l beginl stu_proc1(pno in student.sno%type) l select sname into pname from student where sno=pno;l return pname;l end;l-调用ldeclarelbeginl dbms_output.put_line(在PL/SQL中打印的结果：|get_pname1(2);l end;函数调用存储过程实例函数调用存储过程实例l对有输出参数过程的调用:l create or replace function get_pname2(pname out varchar2) return va

13、rchar2 isl beginl stu_proc2(pname out student.sname%type);l return pname;l end;l-调用ldeclarelpname student.sname%type;lbeginl dbms_output.put_line(在PL/SQL中打印的结果：|get_pname2(pname);l end;函数调用存储过程实例函数调用存储过程实例l对有输入输出参数过程的调用:l create or replace function get_pname3(pno in number,pname out varchar2) return

14、 varchar2 isl beginl stu_proc3(pno in student.sno%type,pname out student.sname%type);l return pname;l end;l-调用ldeclarelpname student.sname%type;lbeginl dbms_output.put_line(在PL/SQL中打印的结果：|get_pname3(2,pname);l end; 函数调用存储过程实例函数调用存储过程实例JAVA调用数据库存储过程调用数据库存储过程l前面我们已经讲述了有关oracle数据库的存储过程的几种形式，以及oracle函数对

15、存储过程的调用，下面我将根据上面存储过程的实例来举出JAVA对oracle存储过程的调用l无参数过程：lDbOper oper = DbOper.getdbOper();lString sql=call stu_proc();loper.executeprocedure(sql); JAVA调用实例调用实例l仅有返回值的过程lpublic void no_paramsCall()lDbOper oper = DbOper.getdbOper();lString sql=call stu_proc();loper.executeprocedure(sql);l JAVA调用实例调用实例l既有输入

16、参数又有输出参数的过程lDbOper oper = DbOper.getdbOper();lString sql=call stu_proc3(?,?);lObject params = 1,222;lString str = oper.executeprocedure_inAndOut(sql,params);lreturn str;JAVA调用实例调用实例游标游标l什么是游标？l从表中检索出结果集，从中每次指向一条记录进行交互的机制l游标从概念上讲基于数据库的表返回结果集l游标有什么作用？l 指定结果集中特定行的位置。 基于当前的结果集位置检索一行或连续的几行。 在结果集的当前位置修改行中

17、的数据。 对其他用户所做的数据更改定义不同的敏感性级别。 可以以编程的方式访问数据库。游标的声明游标的声明lFOR循环游标l-（1）定义游标l-（2）定义游标变量l-（3）使用for循环来使用这个游标ldeclarel -类型定义l cursor c_studentl isl select *l from studentl-定义一个游标变量v_cinfo c_emp%ROWTYPE ，该类型为游标c_emp中的一行数据类型l c_row c_student %rowtype;lbeginl for c_row in c_student loopl dbms_output.put_line(c_

18、row.pno|-|c_row.pname);l end loop;lend;游标的声明游标的声明lFetch游标l-使用的时候必须要明确的打开和关闭使用的时候必须要明确的打开和关闭ldeclare l -类型定义l cursor c_studentl isl select * from student;l-定义一个游标变量l c_row c_student%rowtype;lbeginl open c_student;l loopl -提取一行数据到c_rowl fetch c_student into c_row;l -判读是否提取到值，没取到值就退出l -取到值c_job%notfound 是false l -取不到值c_job%notfound 是truel exit when c_job%notfound;l d