Oracle Procdure、Function

Windows XP

oracle 10.2.0

pl/sql

 

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

import java.sql.*; import java.sql.ResultSet; public class TestProcFunc { public static void main(String[] args) { String driver = "oracle.jdbc.driver.OracleDriver"; String strUrl = "jdbc:oracle:thin:@192.168.1.31:1521:CS"; Statement stmt = null; ResultSet rs = null; Connection conn = null; try { Class.forName(driver); conn = DriverManager.getConnection(strUrl, "username", "password"); CallableStatement cs = null; // 注：以下测试user id must in[1-5] // test 1 无返回值 -- 新增购买商品 -- // cs = conn.prepareCall("{call PROC_ADD_CONSUMERINFO(?,?,?,?)}"); // cs.setInt(1, 2);//2为user id // cs.setString(2, "TV");//商品名称 // cs.setInt(3, 2000);//商品价格 // cs.setInt(4, 2);//数量 // cs.execute(); // test 2 无输入参数，有返回值，输出参数为cursor -- 查询user消费信息 // cs = conn.prepareCall("{call PROC_GET_CONSUMERINFO(?)}"); // cs.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR); // cs.execute(); // rs = (ResultSet) cs.getObject(1);//1输出参数位置 // while (rs.next()) { // System.out.println("商品名称 : " + rs.getString(3)); // } // test 3 有输入参数，输出参数类型非列表 -- 根据goods id 查询 // cs = conn.prepareCall("{call PROC_GET_CONSUMERINFO2(?,?)}"); // int goodsId = 2; // cs.setInt(1, goodsId); // cs.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR); // cs.execute(); // System.out.println("商品名称 : "+cs.getString(2));//2为out参数的位置 // test 4 -- 有输入参数，输出参数类型为cursor -- 根据user id查询消费信息 // cs = conn.prepareCall("{call PROC_GET_CONSUMERINFO3(?,?)}"); // int userId = 2; // cs.setInt(1, userId); // cs.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR); // cs.execute(); // // rs = (ResultSet) cs.getObject(2);// 2为out参数的位置 // while (rs.next()) { // System.out.println("商品名称 : " + rs.getString(3)); // } // test 5 有输入参数，多个输出参数 -- 根据user id查询消费信息，并返回user name // cs = conn.prepareCall("{call PROC_GET_CONSUMERINFO4(?,?,?)}"); // int userId = 2; // cs.setInt(1, userId); // cs.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR); // cs.registerOutParameter(3, oracle.jdbc.OracleTypes.CURSOR); // cs.execute(); // // System.out.println("--- 第一个输出参数 ---"); // System.out.println("user name : "+cs.getString(2));//2为第一个out参数的位置 // // System.out.println("--- 第二个输出参数 ---"); // rs = (ResultSet) cs.getObject(3);// 3为第二个out参数的位置 // while (rs.next()) { // System.out.println("商品名称 : " + rs.getString(3)); // } // test 6 procedure call function // cs = conn.prepareCall("{call PROC_GET_CONSUMERINFO5(?,?)}"); // int userId = 2; // cs.setInt(1, userId); // cs.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR); // cs.execute(); // // rs = (ResultSet) cs.getObject(2);// 2为out参数的位置 // while (rs.next()) { // System.out.println("商品名称 : " + rs.getString(3)); // } // test 6 -- 直接调用 Function cs = conn.prepareCall("{?=call FUNC_GETUSERINFO(?)}"); cs.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR); cs.setInt(2, 2); cs.execute(); rs = (ResultSet) cs.getObject(1);// 1为out参数位置 while (rs.next()) { System.out.println("商品名称 : " + rs.getString(3)); } } catch (SQLException ex2) { ex2.printStackTrace(); } catch (Exception ex2) { ex2.printStackTrace(); } finally { try { if (rs != null) { rs.close(); if (stmt != null) { stmt.close(); } if (conn != null) { conn.close(); } } } catch (SQLException ex1) { ex1.printStackTrace(); } } } } /** 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.函数可以用在表达式中，过程不可以 */

 

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) 函数可以用在表达式中，过程不可以