存储过程
1、创建
create procedure 过程名（变量名 in 变量类型...变量名 out 变量类型...）is
//定义变量 注：变量类型后不需要指定大小
begin
//执行的语句
end
例：项目中所用的：
CREATE OR REPLACE PROCEDURE PROC_CBBS_FILES
------存储过程说明
--
//看看如何调用有返回值的过程
//创建 CallableStatement
18. //看看如何调用有返回值的过程
19. //创建 CallableStatement
20. /*CallableStatement cs = ct.prepareCall("{callsp_pro8(?,?,?,?)}");
22. //给第一个？赋值
23. cs.setInt(1,7788);
24. //给第二个？赋值
25. cs.registerOutParameter(2,oracle.jdbc.OracleTypes.VARCHAR);
26. //给第三个？赋值
27. cs.registerOutParameter(3,oracle.jdbc.OracleTypes.DOUBLE);
28. //给第四个？赋值
29. cs.registerOutParameter(4,oracle.jdbc.OracleTypes.VARCHAR);
31. //5.执行
32. cs.execute();
33. //取出返回值,要注意？的顺序
34. String name=cs.getString(2);
35. String job=cs.getString(4);
36. System.out.println("7788 的名字"+name+" 工作："+job);
37. } catch(Exception e){
38. e.printStackTrace();
39. } finally{
40. //6.关闭各个打开的资源
41. cs.close();
42. ct.close();
43. }
44. }
45.}
③有返回值的存储过程（列表[结果集]）
案例：编写一个过程，输入部门号，返回该部门所有雇员信息。
由于 oracle 存储过程没有返回值，它的所有返回值都是通过 out 参数来替代的，列表同样也不例外，但由于是集合，所以不能用一般的参数，必须要用pagkage了。所以要分两部分：
返回结果集的过程
1.建立一个包，在该包中，定义类型 test_cursor,是个游标。 如下：
Sql 代码
create or replace package testpackage as
TYPE test_cursor is ref cursor;
end testpackage;
2.建立存储过程。如下：
Sql 代码
1. create or replace procedure sp_pro9(spNo in number,p_cursor outtestpackage.test_cursor) is
2. begin
3. open p_cursor for select * from emp where deptno = spNo;
5. end sp_pro9;
3.如何在 java 程序中调用该过程
Java 代码
1. import java.sql.*;
2. public class Test2{
3. public static void main(String[] args){
5. try{
6. //1.加载驱动
7. Class.forName("oracle.jdbc.driver.OracleDriver");
8. //2.得到连接
9. Connection ct =DriverManager.getConnection("jdbc:oracle:thin@127.0.0.1:1521:MYORA1","scott","m123");
11. //看看如何调用有返回值的过程
12. //3.创建 CallableStatement
13. /*CallableStatement cs = ct.prepareCall("{callsp_pro9(?,?)}");
15. //4.给第？赋值
16. cs.setInt(1,10);
17. //给第二个？赋值
18. cs.registerOutParameter(2,oracle.jdbc.OracleTypes.CURSOR);
20. //5.执行
21. cs.execute();
22. //对象强转为结果集
23. ResultSet rs=(ResultSet)cs.getObject(2);
24. while(rs.next()){
25. System.out.println(rs.getInt(1)+" "+rs.getString(2));
26. }
27. } catch(Exception e){
28. e.printStackTrace();
29. } finally{
30. //6.关闭各个打开的资源
31. cs.close();
32. ct.close();
33. }
34. }
35.}
运行，成功得出部门号是 10 的所有用户
④编写分页过程
例：编写一个存储过程，要求可以输入表名、每页显示记录数、当前
页。返回总记录数，总页数，和返回的结果集。
Sql 代码
1. select t1.*, rownum rn from (select * from emp) t1 whererownum<=10;
2. --在分页时，大家可以把下面的 sql 语句当做一个模板使用
3. select * from
4. (select t1.*, rownum rn from (select * from emp) t1 whererownum<=10)
5. where rn>=6;
建立一个包，在该包中，我定义类型 test_cursor,是个游标。如下：
Sql 代码
1. create or replace package testpackage as
2. TYPE test_cursor is ref cursor;
3. end testpackage;
--开始编写分页的过程
5. create or replace procedure fenye
6. (tableName in varchar2,
7. Pagesize in number,--一页显示记录数
8. pageNow in number,
9. myrows out number,--总记录数
10. myPageCount out number,--总页数
11. p_cursor out testpackage.test_cursor--返回的记录集
12. ) is
13.--定义部分
14.--定义 sql 语句字符串
15.v_sql varchar2(1000);
16.--定义两个整数
17.v_begin number:=(pageNow-1)*Pagesize+1;
18.v_end number:=pageNow*Pagesize;
19.begin
20.--执行部分
21.v_sql:='select * from (select t1.*, rownum rn from (select * from'||tableName||') t1 where rownum<='||v_end||') where rn>='||v_begin;
22.--把游标和 sql 关联
23.open p_cursor for v_sql;
24.--计算 myrows 和 myPageCount
25.--组织一个 sql 语句
26.v_sql:='select count(*) from '||tableName;
27.--执行 sql,并把返回的值，赋给 myrows；
28.execute inmediate v_sql into myrows;
29.--计算 myPageCount
30.--if myrows%Pagesize=0 then 这样写是错的
31.if mod(myrows,Pagesize)=0 then
32. myPageCount:=myrows/Pagesize;
33.else
34.myPageCount:=myrows/Pagesize+1
35.end if;
36.--关闭游标
37.close p_cursor;
38.end;
39./
--使用 java 测试
//测试分页
Java 代码
import java.sql.*;
public class FenYe{
public static void main(String[] args){
try{
//1.加载驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
//2.得到连接
Connection ct =DriverManager.getConnection("jdbc:oracle:thin@127.0.0.1:1521:MYORA1","scott","m123");
//3.创建 CallableStatement
CallableStatement cs = ct.prepareCall("{callfenye(?,?,?,?,?,?)}");
//4.给第？赋值
cs.seString(1,"emp");
cs.setInt(2,5);
cs.setInt(3,2);
//注册总记录数
cs.registerOutParameter(4,oracle.jdbc.OracleTypes.INTEGER);
//注册总页数
cs.registerOutParameter(5,oracle.jdbc.OracleTypes.INTEGER);
//注册返回的结果集
cs.registerOutParameter(6,oracle.jdbc.OracleTypes.CURSOR);
//5.执行
cs.execute();
//取出总记录数 /这里要注意，getInt(4)中 4，是由该参数的位置决定的
int rowNum=cs.getInt(4);
int pageCount = cs.getInt(5);
ResultSet rs=(ResultSet)cs.getObject(6);
//显示一下，看看对不对
System.out.println("rowNum="+rowNum);
System.out.println("总页数="+pageCount);
while(rs.next()){
System.out.println("编号： "+rs.getInt(1)+" 名字："+rs.getString(2)+"工资："+rs.getFloat(6));
}
} catch(Exception e){
e.printStackTrace();
} finally{
//6.关闭各个打开的资源
cs.close();
ct.close();
}
}
}
运行，控制台输出：
rowNum=19
总页数：4
编号：7369 名字：SMITH 工资：2850.0
编号：7499 名字：ALLEN 工资：2450.0
编号：7521 名字：WARD 工资：1562.0
编号：7566 名字：JONES 工资：7200.0
编号：7654 名字：MARTIN 工资：1500.0
--新的需要，要求按照薪水从低到高排序，然后取出 6-10
过程的执行部分做下改动，如下：
Sql 代码
begin
--执行部分
v_sql:='select * from (select t1.*, rownum rn from (select * from'||tableName||' order by sal) t1 where rownum<='||v_end||')
where rn>='||v_begin;
重新执行一次 procedure,java 不用改变，运行，控制台输出：
rowNum=19
总页数：4
编号：7900 名字：JAMES 工资：950.0
编号：7876 名字：ADAMS 工资：1100.0
编号：7521 名字：WARD 工资：1250.0
编号：7654 名字：MARTIN 工资：1250.0
编号：7934 名字：MILLER 工资：1300.0
2、㈠在oracle客户端调用过程有两种方法：
①exec 过程名（参数值...）
②call 过程名（参数值...）
实际begin
存储过程名;
end;（或者右击存储过程名并点击测试）
㈡在JAVA中调用存储过程方法：
①加载驱动：Class.forName("oracle.jdbc.driver.OracleDriver");
②得到链接：ct=DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:数据库名","用户名","密码")
③创建CallableStatement接口对象：cs=ct.prepareCall("{call过程名（？，？）}");
④给？赋值：cs.setString(1,"haoxiaoli");//？为你插入的字段个数，与SQL语句有关
⑤执行我们的语句：cs.execute();
注：如果卡了记得在oracle中先提交commit
3、显示错误：
show error;
