java调用存储过程.docx
- 文档编号:26219653
- 上传时间:2023-06-17
- 格式:DOCX
- 页数:19
- 大小:20.85KB
java调用存储过程.docx
《java调用存储过程.docx》由会员分享,可在线阅读,更多相关《java调用存储过程.docx(19页珍藏版)》请在冰豆网上搜索。
java调用存储过程
数据采用Oracle数据库scott/tiger示范账户下的emp(员工)表和dept(部门)表:
[sql] viewplain copy
1.create table DEPT
2.
3.(
4.
5. DEPTNO NUMBER
(2) not null primary key,
6.
7. DNAME VARCHAR2(14),
8.
9. LOC VARCHAR2(13)
10.
11.)
[sql] viewplain copy
1.create table EMP
2.
3.(
4.
5. EMPNO NUMBER(4) not null primary key,
6.
7. ENAME VARCHAR2(10),
8.
9. JOB VARCHAR2(9),
10.
11. MGR NUMBER(4),
12.
13. HIREDATE DATE,
14.
15. SAL NUMBER(7,2),
16.
17. COMM NUMBER(7,2),
18.
19. DEPTNO NUMBER
(2),
20.
21. constraint FK_DEPTNO foreign key (DEPTNO)references DEPT (DEPTNO)
22.
23.)
oracle存储过程代码:
[sql] viewplain copy
1.Create or replace PROCEDURE searchEmpByDept(
2.
3. indeptno IN NUMBER,
4.
5. empcur OUT sys_refcursor,
6.
7. errorMsg OUT varchar)
8.
9.IS
10.
11.BEGIN
12.
13. errorMsg:
='';
14.
15. OPEN empcur FOR
16.
17. SELECT *
18.
19. FROM emp
20.
21. WHERE deptno = indeptno
22.
23. ORDER BY empno;
24.
25. EXCEPTION
26.
27. WHEN OTHERS THEN
28.
29. errorMsg:
= sqlerrm;-- sqlcode是异常编号,sqlerrm是异常的详细信息
30.
31.END searchEmpByDept;
使用 pl/sql 过程语句测试上面的存储过程:
[sql] viewplain copy
1.declare
2.
3.errorMsg varchar(1000);
4.
5.empcur sys_refcursor;
6.
7.emp scott.emp%rowtype;
8.
9.begin
10.
11. searchEmpByDept(10,empcur, errorMsg);
12.
13. if errorMsg is not null then
14.
15. dbms_output.put_line(errorMsg);
16.
17. end if;
18. loop
19.
20. fetch empcur into emp;
21.
22. EXIT WHEN empcur%notfound ;
23.
24. dbms_output.put_line(emp.ename);
25.
26. end loop;
27.
28.
29.
30. close empcur;
31.
32.end;
Java调用代码:
[java] viewplain copy
1.public class DBHelper {
2.
3. private Connection conn = null;
4.
5. public Connection getConnection() throws ClassNotFoundException, SQLException {
6.
7. Class.forName("oracle.jdbc.driver.OracleDriver");
8.
9. conn = DriverManager.getConnection(
10.
11. "jdbc:
oracle:
thin:
@192.168.1.6:
1521:
ntcsoft",
12.
13. "scott",
14.
15. "tiger");
16.
17. return conn;
18.
19. }
20.
21.}
22.
23.public class CallOracleProcedure {
24.
25. public static void main(String args[]) {
26.
27. ResultSet rs = null;
28.
29. CallableStatement st = null;
30.
31. Connection con = null;
32.
33. try {
34.
35. con = new DBHelper().getConnection();
36.
37. String sql = "call searchEmpByDept(?
?
?
)";
38.
39. st = con.prepareCall(sql);
40.
41. st.setInt(1, 20);//设置入参部门编号20
42.
43. //注册返回类型参数。
CURSOR类型在java.sql.Tyes中没有定义,在驱动程序包中找到了
44.
45. st.registerOutParameter(2, oracle.jdbc.driver.OracleTypes.CURSOR);
46.
47. st.registerOutParameter(3, Types.VARCHAR);
48.
49. boolean result = st.execute();
50.
51. //获取返回参数
52.
53. rs = (ResultSet) st.getObject
(2);
54.
55. String msg = st.getString(3);
56.
57. if(msg !
= null)
58.
59. System.out.println(msg);//异常信息部为null则打印
60.
61. System.out.println("empno" + ""t" + "ename" + ""t" + "sal" + ""t"+ "deptno");
62.
63. //输出查询结果
64.
65. StringBuilder output = new StringBuilder();
66.
67. while (rs.next()) {
68.
69. output.append(rs.getInt("empno"))
70.
71. .append(""t")
72.
73. .append(rs.getString("ename"))
74.
75. .append(""t")
76.
77. .append(rs.getDouble("sal"))
78.
79. .append(""t")
80.
81. .append(rs.getInt("deptno"));
82.
83. System.out.println(output.toString());
84.
85. output.delete(0, output.length());
86.
87. }
88.
89. output = null;
90.
91. }catch (Exception e) {
92.
93. e.printStackTrace();
94.
95. }finally{
96.
97. try {
98.
99. if(rs!
=null) rs.close();
100.
101. if(st!
=null) st.close();
102.
103. if(con!
=null) con.close();
104.
105. } catch (SQLException e) {
106.
107. e.printStackTrace();
108.
109. }
110.
111. }
112.
113. }
114.
115.}
java下实现调用oracle的存储过程和函数
在Oracle下创建一个test的账户,然后
1.创建表:
STOCK_PRICES
1--创建表格
2CREATETABLESTOCK_PRICES(
3RICVARCHAR(6)PRIMARYKEY,
4PRICENUMBER(7,2),
5UPDATEDDATE);
2.插入测试数据:
1--插入数据
2INSERTINTOstock_pricesvalues('1111',1.0,SYSDATE);
3INSERTINTOstock_pricesvalues('1112',2.0,SYSDATE);
4INSERTINTOstock_pricesvalues('1113',3.0,SYSDATE);
5INSERTINTOstock_pricesvalues('1114',4.0,SYSDATE);
3.建立一个返回游标:
PKG_PUB_UTILS
1--建立一个返回游标
2CREATEORREPLACEPACKAGEPKG_PUB_UTILSIS
3--动态游标
4TYPEREFCURSORISREFCURSOR;
5ENDPKG_PUB_UTILS;
4.创建和存储过程:
P_GET_PRICE
1--创建存储过程
2CREATEORREPLACEPROCEDUREP_GET_PRICE
3(
4AN_O_RET_CODEOUTNUMBER,
5AC_O_RET_MSGOUTVARCHAR2,
6CUR_RETOUTPKG_PUB_UTILS.REFCURSOR,
7AN_I_PRICEINNUMBER
8)
9IS
10BEGIN
11AN_O_RET_CODE:
=0;
12AC_O_RET_MSG:
='操作成功';
13
14OPENCUR_RETFOR
15SELECT*FROMSTOCK_PRICESWHEREPRICE 16EXCEPTION 17WHENOTHERSTHEN 18AN_O_RET_CODE: =-1; 19AC_O_RET_MSG: ='错误代码: '||SQLCODE||CHR(13)||'错误信息: '||SQLERRM; 20ENDP_GET_PRICE; 5.创建函数: 1--创建函数: F_GET_PRICE 2CREATEORREPLACEFUNCTIONF_GET_PRICE(v_priceINNUMBER) 3RETURNPKG_PUB_UTILS.REFCURSOR 4AS 5stock_cursorPKG_PUB_UTILS.REFCURSOR; 6BEGIN 7OPENstock_cursorFOR 8SELECT*FROMstock_pricesWHEREprice 9RETURNstock_cursor; 10END; 6.JAVA调用存储过程返回结果集 JDBCoracle10G_INVOKEPROCEDURE.Java 1importjava.sql.*; 2importoracle.jdbc.OracleCallableStatement; 3importoracle.jdbc.OracleTypes; 4 5/*本例是通过调用oracle的存储过程来返回结果集: 6*oracle9i、10G的jdbc由1个jar包组成: classes12.zip 7*/ 8publicclassJDBCoracle10G_INVOKEPROCEDURE{ 9Connectionconn=null; 10Statementstatement=null; 11ResultSetrs=null; 12CallableStatementstmt=null; 13 14Stringdriver; 15Stringurl; 16Stringuser; 17Stringpwd; 18Stringsql; 19Stringin_price; 20 21publicJDBCoracle10G_INVOKEPROCEDURE() 22{ 23driver="oracle.jdbc.driver.OracleDriver"; 24url="jdbc: oracle: thin: @localhost: 1521: ORCL"; 25//oracle用户 26user="test"; 27//oracle密码 28pwd="test"; 29init(); 30//mysid: 必须为要连接机器的sid名称,否则会包以下错: 31//java.sql.SQLException: Io异常: Connection 32//refused(DESCRIPTION=(TMP=)(VSNNUM=169870080)(ERR=12505)(ERROR_STACK=(ERROR=(CODE=12505)(EMFI=4)))) 33//参考连接方式: 34//Class.forName("oracle.jdbc.driver.OracleDriver"); 35//cn=DriverManager.getConnection( 36//"jdbc: oracle: thin: @MyDbComputerNameOrIP: 1521: ORCL",sUsr,sPwd); 37 38} 39 40publicvoidinit(){ 41System.out.println("oraclejdbctest"); 42try{ 43Class.forName(driver); 44System.out.println("driverisok"); 45conn=DriverManager.getConnection(url,user,pwd); 46System.out.println("conectionisok"); 47statement=conn.createStatement(); 48//conn.setAutoCommit(false); 49//输入参数 50in_price="3.0"; 51//调用函数 52stmt=conn.prepareCall("callP_GET_PRICE(? ? ? ? )"); 53stmt.registerOutParameter(1,java.sql.Types.FLOAT); 54stmt.registerOutParameter(2,java.sql.Types.CHAR); 55stmt.registerOutParameter(3,oracle.jdbc.OracleTypes.CURSOR); 56stmt.setString(4,in_price); 57stmt.executeUpdate(); 58intretCode=stmt.getInt (1); 59StringretMsg=stmt.getString (2); 60if(retCode==-1){//如果出错时,返回错误信息 61System.out.println("报错! "); 62}else{ 63//取的结果集的方式一: 64rs=((OracleCallableStatement)stmt).getCursor(3); 65//取的结果集的方式二: 66//rs=(ResultSet)stmt.getObject(3); 67Stringric; 68Stringprice; 69Stringupdated; 70//对结果进行输出 71while(rs.next()){ 72ric=rs.getString (1); 73price=rs.getString (2); 74updated=rs.getString(3); 75System.out.println("ric: "+ric+";--price: "+price 76+";--"+updated+";"); 77} 78} 79 80}catch(Exceptione){ 81e.printStackTrace(); 82}finally{ 83System.out.println("close"); 84} 85} 86 87publicstaticvoidmain(Stringargs[])//自己替换[] 88{ 89newJDBCoracle10G_INVOKEPROCEDURE(); 90} 91} 7.开发JAVA调用函数返回结果集 JDBCoracle10G_INVOKEFUNCTION.java 1importjava.sql.*; 2importoracle.jdbc.OracleCallableStatement; 3importoracle.jdbc.OracleTypes; 4 5/* 6/*本例是通过调用oracle的函数来返回结果集: 7*oracle9i、10G的jdbc由1个jar包组成: classes12.zip 8*/ 9publicclassJDBCoracle10G_INVOKEFUNCTION{ 10Connectionconn=null; 11Statementstatement=null; 12ResultSetrs=null; 13CallableStatementstmt=null; 14 15Stringdriver; 16Stringurl; 17Stringuser; 18Stringpwd; 19Stringsql; 20Stringin_price; 21 22publicJDBCoracle10G_INVOKEFUNCTION() 23{ 24driver="oracle.jdbc.driver.OracleDriver"; 25url="jdbc: oracle: thin: @localhost: 1521: ORCL"; 26//oracle用户 27user="test"; 28//oracle密码 29pwd="test"; 30init(); 31//mysid: 必须为要连接机器的sid名称,否则会包以下错: 32//java.sql.SQLException: Io异常: Connection 33//refused(DESCRIPTION=(TMP=)(VSNNUM=169870080)(ERR=12505)(ERROR_STACK=(ERROR=(CODE=12505)(EMFI=4)))) 34//参考连接方式: 35//Class.forName(
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- java 调用 存储 过程