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