Oracle Procdure、Function

1、创建表

 

   create table T_USER
(
   user_id number,
   user_name varchar(50),
   gender varchar(5),
   email varchar(50),
   constraint T_USER_PK PRIMARY KEY (user_id)
);

   create table T_STORAGE
(
   goods_id number,
   user_id number,
   goods_name varchar(50),
   goods_price float,
   goods_amount number,
   goods_total_price float
   constraint T_STORAGE_PK PRIMARY KEY (goods_id),
   constraint T_STORAGE_FK FOREIGN KEY (user_id) REFERENCES T_USER (user_id)
)

 

2、创建sequence

 

create sequence T_STORAGE_SEQ
minvalue 1
maxvalue 9999999999999999999999999999
start with 1
increment by 1
cache 20;

 

3、初始化数

 

insert into T_USER (USER_ID,USER_NAME,GENDER,EMAIL) values (1,'robin','male','robin@163.com');
insert into T_USER (USER_ID,USER_NAME,GENDER,EMAIL) values (2,'robin2','male','robin2@163.com');
insert into T_USER (USER_ID,USER_NAME,GENDER,EMAIL) values (3,'robin3','male','robin3@163.com');
insert into T_USER (USER_ID,USER_NAME,GENDER,EMAIL) values (4,'robin4','male','robin4@163.com');
insert into T_USER (USER_ID,USER_NAME,GENDER,EMAIL) values (5,'robin5','male','robin5@163.com');

insert into T_STORAGE (GOODS_ID,USER_ID,GOODS_NAME,GOODS_PRICE,GOODS_AMOUNT,GOODS_TOTAL_PRICE) values (T_STORAGE_SEQ.Nextval,1,'MP3',150,1,150);
insert into T_STORAGE (GOODS_ID,USER_ID,GOODS_NAME,GOODS_PRICE,GOODS_AMOUNT,GOODS_TOTAL_PRICE) values (T_STORAGE_SEQ.Nextval,1,'MP4',250,1,250);
insert into T_STORAGE (GOODS_ID,USER_ID,GOODS_NAME,GOODS_PRICE,GOODS_AMOUNT,GOODS_TOTAL_PRICE) values (T_STORAGE_SEQ.Nextval,2,'MP5',550,1,550);
insert into T_STORAGE (GOODS_ID,USER_ID,GOODS_NAME,GOODS_PRICE,GOODS_AMOUNT,GOODS_TOTAL_PRICE) values (T_STORAGE_SEQ.Nextval,3,'MP6',850,1,850);
insert into T_STORAGE (GOODS_ID,USER_ID,GOODS_NAME,GOODS_PRICE,GOODS_AMOUNT,GOODS_TOTAL_PRICE) values (T_STORAGE_SEQ.Nextval,4,'MP7',950,1,950);
insert into T_STORAGE (GOODS_ID,USER_ID,GOODS_NAME,GOODS_PRICE,GOODS_AMOUNT,GOODS_TOTAL_PRICE) values (T_STORAGE_SEQ.Nextval,5,'MP8',1150,2,2300);

 

4、创建package

 

CREATE OR REPLACE PACKAGE STORAGEPACKAGE  AS
 TYPE STORAGE_CURSOR IS REF CURSOR;
end STORAGEPACKAGE;

 

5、创建函数

 

---根据商品价格和数量计算总价
CREATE OR REPLACE FUNCTION func_getTotal(goods_price t_storage.goods_price%TYPE,goods_amount t_storage.goods_amount%TYPE)
RETURN FLOAT
IS
total_price t_storage.goods_total_price%TYPE;
BEGIN
     total_price :=  goods_price * goods_amount;
   RETURN total_price;
EXCEPTION
     WHEN OTHERS THEN
        dbms_output.put_line(SQLERRM);
END ;

---根据user id查询消费信息
CREATE OR REPLACE FUNCTION func_getUserInfo(userid in number) RETURN STORAGEPACKAGE.STORAGE_CURSOR IS
  STORAGE_CUR STORAGEPACKAGE.STORAGE_CURSOR;
 begin
    open STORAGE_CUR for SELECT * FROM T_STORAGE s WHERE s.user_id  = userid;
    RETURN(STORAGE_CUR);
    close STORAGE_CUR;
  EXCEPTION
     WHEN OTHERS THEN
       dbms_output.put_line(SQLERRM);
END func_getUserInfo;

 

6、创建过程

---新增消费信息
CREATE OR REPLACE PROCEDURE proc_add_consumerinfo(user_id t_user.user_id%TYPE,goods_name t_storage.goods_name%TYPE,
goods_price t_storage.goods_price%TYPE,goods_amount t_storage.goods_amount%TYPE)
IS
total_price t_storage.goods_total_price%TYPE;
BEGIN
  SELECT func_getTotal(goods_amount,goods_price) INTO total_price FROM dual;
  INSERT INTO t_storage (GOODS_ID,USER_ID,GOODS_NAME,GOODS_PRICE,GOODS_AMOUNT,GOODS_TOTAL_PRICE) 
  VALUES(T_STORAGE_SEQ.Nextval,user_id,goods_name,goods_price,goods_amount,total_price);
  COMMIT;
EXCEPTION
   WHEN OTHERS THEN
       ROLLBACK;
    dbms_output.put_line(SQLERRM);

END;

---查询全部消费信息
CREATE OR REPLACE PROCEDURE proc_get_consumerinfo(storage_cur out STORAGEPACKAGE.STORAGE_CURSOR)
IS
BEGIN
  open storage_cur for select * from t_storage;
EXCEPTION
   WHEN OTHERS THEN
       ROLLBACK;
    dbms_output.put_line(SQLERRM);
END;

---根据消费id查询消费记录，并返回消费商品名称
CREATE OR REPLACE PROCEDURE proc_get_consumerinfo2(goodsid in number,goodsname out varchar2)
IS
BEGIN
  select goods_name into goodsname from t_storage t where t.goods_id = goodsid;
EXCEPTION
   WHEN OTHERS THEN
       ROLLBACK;
    dbms_output.put_line(SQLERRM);
END;

---根据user id,查询消费信息
CREATE OR REPLACE PROCEDURE proc_get_consumerinfo3(userid in number,storage_cur out STORAGEPACKAGE.STORAGE_CURSOR)
IS
BEGIN
  open storage_cur for select * from t_storage t where t.user_id = userid;
EXCEPTION
   WHEN OTHERS THEN
       ROLLBACK;
    dbms_output.put_line(SQLERRM);
END;

--根据user id查询消费信息，返回多个结果（用户姓名，消费记录）
CREATE OR REPLACE PROCEDURE proc_get_consumerinfo4(userid in number, username out varchar2, storage_cur out STORAGEPACKAGE.STORAGE_CURSOR)
IS
BEGIN
  open storage_cur for select * from t_storage t where t.user_id = userid;
  select u.user_name into username from t_user u where u.user_id = userid;
EXCEPTION
   WHEN OTHERS THEN
       ROLLBACK;
    dbms_output.put_line(SQLERRM);
END;

---根据user id，调用function,返回消费信息
CREATE OR REPLACE PROCEDURE proc_get_consumerinfo5(userid in number, storage_cur out STORAGEPACKAGE.STORAGE_CURSOR)
IS
BEGIN
  storage_cur := FUNC_GETUSERINFO2(userid);
EXCEPTION
   WHEN OTHERS THEN
       ROLLBACK;
    dbms_output.put_line(SQLERRM);
END;

 

7、测试类

 

TestProcFunc.java

 

[java]  view plain copy

1. import java.sql.*;  
2. import java.sql.ResultSet;  
3. public class TestProcFunc {  
4.     public static void main(String[] args) {  
5.         String driver = "oracle.jdbc.driver.OracleDriver";  
6.         String strUrl = "jdbc:oracle:thin:@192.168.1.31:1521:CS";  
7.         Statement stmt = null;  
8.         ResultSet rs = null;  
9.         Connection conn = null;  
10.         try {  
11.             Class.forName(driver);  
12.             conn = DriverManager.getConnection(strUrl, "username",  
13.                     "password");  
14.             CallableStatement cs = null;  
15.             // 注：以下测试user id must in[1-5]  
16.             // test 1 无返回值 -- 新增购买商品 --  
17. //             cs = conn.prepareCall("{call PROC_ADD_CONSUMERINFO(?,?,?,?)}");  
18. //             cs.setInt(1, 2);//2为user id  
19. //             cs.setString(2, "TV");//商品名称  
20. //             cs.setInt(3, 2000);//商品价格  
21. //             cs.setInt(4, 2);//数量  
22. //             cs.execute();  
23.             // test 2 无输入参数，有返回值，输出参数为cursor -- 查询user消费信息  
24. //             cs = conn.prepareCall("{call PROC_GET_CONSUMERINFO(?)}");  
25. //             cs.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);  
26. //             cs.execute();  
27. //             rs = (ResultSet) cs.getObject(1);//1输出参数位置  
28. //             while (rs.next()) {  
29. //             System.out.println("商品名称 : " + rs.getString(3));  
30. //             }  
31.             // test 3 有输入参数，输出参数类型非列表 -- 根据goods id 查询  
32. //             cs = conn.prepareCall("{call PROC_GET_CONSUMERINFO2(?,?)}");  
33. //             int goodsId = 2;  
34. //             cs.setInt(1, goodsId);  
35. //             cs.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR);  
36. //             cs.execute();  
37. //             System.out.println("商品名称 : "+cs.getString(2));//2为out参数的位置  
38.             // test 4 -- 有输入参数，输出参数类型为cursor -- 根据user id查询消费信息  
39. //             cs = conn.prepareCall("{call PROC_GET_CONSUMERINFO3(?,?)}");  
40. //             int userId = 2;  
41. //             cs.setInt(1, userId);  
42. //             cs.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR);  
43. //             cs.execute();  
44. //              
45. //             rs = (ResultSet) cs.getObject(2);// 2为out参数的位置  
46. //             while (rs.next()) {  
47. //             System.out.println("商品名称 : " + rs.getString(3));  
48. //             }  
49.             // test 5 有输入参数，多个输出参数 -- 根据user id查询消费信息，并返回user name  
50. //             cs = conn.prepareCall("{call PROC_GET_CONSUMERINFO4(?,?,?)}");  
51. //             int userId = 2;  
52. //             cs.setInt(1, userId);  
53. //             cs.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR);  
54. //             cs.registerOutParameter(3, oracle.jdbc.OracleTypes.CURSOR);  
55. //             cs.execute();  
56. //                                      
57. //             System.out.println("--- 第一个输出参数 ---");  
58. //             System.out.println("user name : "+cs.getString(2));//2为第一个out参数的位置  
59. //                                      
60. //             System.out.println("--- 第二个输出参数 ---");  
61. //             rs = (ResultSet) cs.getObject(3);// 3为第二个out参数的位置  
62. //             while (rs.next()) {  
63. //             System.out.println("商品名称 : " + rs.getString(3));  
64. //             }  
65.             // test 6 procedure call function  
66. //             cs = conn.prepareCall("{call PROC_GET_CONSUMERINFO5(?,?)}");  
67. //             int userId = 2;  
68. //             cs.setInt(1, userId);  
69. //             cs.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR);  
70. //             cs.execute();  
71. //                          
72. //             rs = (ResultSet) cs.getObject(2);// 2为out参数的位置  
73. //             while (rs.next()) {  
74. //             System.out.println("商品名称 : " + rs.getString(3));  
75. //             }  
76.             // test 6 -- 直接调用 Function  
77.              cs = conn.prepareCall("{?=call FUNC_GETUSERINFO(?)}");  
78.              cs.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);  
79.              cs.setInt(2, 2);  
80.              cs.execute();  
81.              rs = (ResultSet) cs.getObject(1);// 1为out参数位置  
82.              while (rs.next()) {  
83.              System.out.println("商品名称 : " + rs.getString(3));  
84.              }  
85.         } catch (SQLException ex2) {  
86.             ex2.printStackTrace();  
87.         } catch (Exception ex2) {  
88.             ex2.printStackTrace();  
89.         } finally {  
90.             try {  
91.                 if (rs != null) {  
92.                     rs.close();  
93.                     if (stmt != null) {  
94.                         stmt.close();  
95.                     }  
96.                     if (conn != null) {  
97.                         conn.close();  
98.                     }  
99.                 }  
100.             } catch (SQLException ex1) {  
101.                 ex1.printStackTrace();  
102.             }  
103.         }  
104.     }  
105. }  
106.   

 

8、遇到的问题

 

1) java.sql.SQLException: 无效的列索引
  
  rs = (ResultSet) cs.getObject(1);//输出参数位置错误
  
2) java.sql.SQLException: Cursor is closed.

   隐式cursor,会自动打开关闭。
  open storage_cur for select * from t_storage t where t.user_id = userid;
  select u.user_name into username from t_user u where u.user_id = userid;
  
  如果先执行select的话，就会产生这个异常。

3) 过程或者函数中的参数不可以和数据库表字段相同

4) 函数可以用在表达式中，过程不可以