proc trades.sql

基于J2EE技术的 电子购物商城系统

/*
 *==================================================================================
 * 3.trade包
 *
 *  过程列表：
 *  (1)transferGood			商品转交
 *	(2)transferBook			图书转交
 *	(3)remit				转帐
 *	(4)newTrade				新增交易
 *	(5)setTradeSuccessful	设定交易成功
 *	(6)setListSuccessful	设定一张定单上的所有交易成功
 *	(7)getTradeInfo			获取交易信息
 *	(8)getTradeTime			商品交易时间
 *	(9)getPayedTrades		获取用户已经支付的交易
 *	(10)getNotPayedTrades	获取用户未支付的交易
 *	(11)getSucesTrades		获取用户成功的交易
 *	(12)getNotSucesTrades	获取用户未成功的交易
*       (13)trade.setPayed	设置支付成功
 *==================================================================================
*/

/* 1.trade.transferGood
************************************************************************************
 * 过程:
 *	transferGood(
 *		 p_buyerId			in		varchar2,
 *		 p_salerId			in		varchar2,
 *		 p_goodId			in		varchar2,
 *		 p_goodCount		in		number,
 *		 p_isSuccessful		out		number,
 *		 p_errorMsg			out		varchar2
 *		);
 * 作用:
 *	商品转交;
 * 参数:
 *	(1)p_buyerId:		varchar2	in	买方用户ID
 *	(2)p_salerId:		varchar2	in	卖方用户ID
 *	(3)p_goodId:		varchar2	in	商品ID
 *	(4)p_goodCount:		number		in	商品数量
 *	(5)p_isSuccessful:	number		out	商品转交是否成功标志
 *	(6)p_errorMsg:		varchar2	out	商品转交失败信息(成功时为空串)
************************************************************************************
*/
create or replace procedure transferGood(
 		 p_buyerId			in		varchar2,
 		 p_salerId			in		varchar2,
 		 p_goodId			in		varchar2,
 		 p_goodCount		in		number,
 		 p_isSuccessful		out		number,
 		 p_errorMsg			out		varchar2
 		)
as
	v_itemCount number(1) := 0;
	v_name varchar2(33);
	v_price number(6, 2);
	v_goodNum number(4) := 0;
	v_category#1 varchar2(18);
	v_category#2 varchar2(18);
	v_category#3 varchar2(18);
	v_isReleased number(1) := 0;
	v_newGoodId varchar2(33);
begin
	select name, price, num, category#1, category#2, category#3, isReleased 
		into v_name, v_price, v_goodNum, v_category#1, v_category#2, v_category#3, v_isReleased
		from goods where id=p_goodId;
	if (SQL%NOTFOUND) then
		p_isSuccessful := 0;
		p_errorMsg := '买方请求购买的商品已经不存在了。';
		return;
	elsif (v_isReleased=0) then
		p_isSuccessful := 0;
		p_errorMsg := '买方请求购买的商品已经停止出售了。';
		return;
	elsif (v_goodNum<=0 or v_goodNum<p_goodCount) then
		p_isSuccessful := 0;
		p_errorMsg := '当前商品库存已经不足，请等待库存补充。';
		return;
	end if;
	
	v_newGoodId := concat(to_char(sysdate,'yyyymmddhh24miss'),p_buyerId);
	insert into goods
		values(v_newGoodId, v_name, v_price, p_goodCount, p_buyerId,
				v_category#1, v_category#2, v_category#3, 0);
	if (SQL%ROWCOUNT <> 1) then
		p_isSuccessful := 0;
		p_errorMsg := 'Oracle无法完成商品转交操作步骤1为买方增加商品。';
		rollback;
		return;
	end if;
	
	if (v_goodNum=p_goodCount) then
		delete from goods where id=p_goodId;
	else
		update goods set num=num-p_goodCount where id=p_goodId;
	end if;
	
	if (SQL%ROWCOUNT <> 1) then
		p_isSuccessful := 0;
		p_errorMsg := 'Oracle无法完成商品转交操作步骤2为卖方删除商品。';
		rollback;
		return;
	else
		p_isSuccessful := 1;
		p_errorMsg := ' ';
		commit;
	end if;
	
exception
	when no_data_found then
		p_isSuccessful := 0;
		p_errorMsg := '找不到你要购买的商品。';
		return;
	when others then
		p_isSuccessful := 0;
		p_errorMsg := '未知错误。';
		rollback;
		return;
end transferGood;
/

/* 2.trade.transferBook
************************************************************************************
 * 过程:
 *	transferBook(
 *		 p_buyerId			in		varchar2,
 *		 p_salerId			in		varchar2,
 *		 p_bookId			in		varchar2,
 *		 p_bookCount		in		number,
 *		 p_isSuccessful		out		number,
 *		 p_errorMsg			out		varchar2
 *		);
 * 作用:
 *	图书转交;
 * 参数:
 *	(1)p_buyerId:		varchar2	in	买方用户ID
 *	(2)p_salerId:		varchar2	in	卖方用户ID
 *	(3)p_bookId:		varchar2	in	图书ID
 *	(4)p_bookCount:		number		in	图书数量
 *	(5)p_isSuccessful:	number		out	图书转交是否成功标志
 *	(6)p_errorMsg:		varchar2	out	图书转交失败信息(成功时为空串)
************************************************************************************
*/
create or replace procedure transferBook(
 		 p_buyerId			in		varchar2,
 		 p_salerId			in		varchar2,
 		 p_bookId			in		varchar2,
 		 p_bookCount		in		number,
 		 p_isSuccessful		out		number,
 		 p_errorMsg			out		varchar2
 		)
as
	v_itemCount number(1) := 0;
	v_name varchar2(33);
	v_price number(6, 2);
	v_bookNum number(4) := 0;
	v_category#1 varchar2(18);
	v_category#2 varchar2(18);
	v_author varchar2(33);
	v_press varchar2(33);
	v_edition number(1);
	v_isbn varchar2(33);
	v_categoryNUM varchar2(33);
	v_isReleased number(1) := 0;
	v_newBookId varchar2(33);
begin
	select name, price, num, category#1, category#2, author,
			press, edition, isbn, categoryNUM, isReleased 
		into v_name, v_price, v_bookNum, v_category#1, v_category#2,
			v_author, v_press, v_edition, v_isbn, v_categoryNUM, v_isReleased
		from books where id=p_bookId;
	if (SQL%NOTFOUND) then
		p_isSuccessful := 0;
		p_errorMsg := '买方请求购买的图书已经不存在了。';
		return;
	elsif (v_isReleased=0) then
		p_isSuccessful := 0;
		p_errorMsg := '买方请求购买的图书已经停止出售了。';
		return;
	elsif (v_bookNum<=0 or v_bookNum<p_bookCount) then
		p_isSuccessful := 0;
		p_errorMsg := '当前图书库存已经不足，请等待库存补充。';
		return;
	end if;
	
	v_newBookId := concat(to_char(sysdate,'yyyymmddhh24miss'),p_buyerId);
	insert into books
		values(v_newBookId, v_name, v_price, p_bookCount, p_buyerId,
				v_category#1, v_category#2, v_author, v_press, v_edition,
				v_isbn, v_categoryNUM, 0);
	if (SQL%ROWCOUNT <> 1) then
		p_isSuccessful := 0;
		p_errorMsg := 'Oracle无法完成图书转交操作步骤1为买方增加图书。';
		rollback;
		return;
	end if;
	
	if (v_bookNum=p_bookCount) then
		delete from books where id=p_bookId;
	else
		update books set num=num-p_bookCount where id=p_bookId;
	end if;
	
	if (SQL%ROWCOUNT <> 1) then
		p_isSuccessful := 0;
		p_errorMsg := 'Oracle无法完成图书转交操作步骤2为卖方删除图书。';
		rollback;
		return;
	else
		p_isSuccessful := 1;
		p_errorMsg := ' ';
		commit;
	end if;
	
exception
	when no_data_found then
		p_isSuccessful := 0;
		p_errorMsg := '找不到你要购买的商品。';
		return;
	when others then
		p_isSuccessful := 0;
		p_errorMsg := '未知错误。';
		rollback;
		return;
end transferBook;
/

/* 3.trade.remit
************************************************************************************
 * 过程:
 *	remit(
 *		 p_fromUserId		in		varchar2,
 *		 p_toUserId			in		varchar2,
 *		 p_balance			in		number,
 *		 p_isSuccessful		out		number,
 *		 p_errorMsg			out		varchar2
 *		);
 * 作用:
 *	转帐;
 * 参数:
 *	(1)p_fromUserId:	varchar2	in	转帐源用户ID
 *	(2)p_toUserId:		varchar2	in	转帐目的用户ID
 *	(3)p_balance:		number		in	转帐金额
 *	(4)p_isSuccessful:	number		out	转帐成功标志
 *	(5)p_errorMsg:		varchar2	out	转帐失败信息(成功时为空串)
************************************************************************************
*/
create or replace procedure remit(
 		 p_fromUserId		in		varchar2,
 		 p_toUserId			in		varchar2,
 		 p_balance			in		number,
 		 p_isSuccessful		in out	number,
 		 p_errorMsg			in out	varchar2
 		)
as
	v_fromUserPassword varchar(18);
	v_fromUserBalance number(8, 2) := 0;
begin
	select password, balance into v_fromUserPassword, v_fromUserBalance
		from users where id=p_fromUserId;
	if (SQL%NOTFOUND) then
		p_isSuccessful := 0;
		p_errorMsg := '源用户不存在。';
	elsif (v_fromUserBalance<=0 or v_fromUserBalance<p_balance) then
		p_isSuccessful := 0;
		p_errorMsg := '源用户金额不足。';
	end if;
	
	pay(p_fromUserId, v_fromUserPassword, p_balance, p_isSuccessful, p_errorMsg);
	if (p_isSuccessful=1) then
		deposit(p_toUserId, p_balance, p_isSuccessful, p_errorMsg);
		if (p_isSuccessful=1) then
			p_errorMsg := ' ';
		else
			p_isSuccessful := 0;
			p_errorMsg := '转帐失败，失败在步骤2无法向目的帐户中注入金额。';
		end if;
	else
		p_isSuccessful := 0;
		p_errorMsg := '转帐失败，失败在步骤1无法从源帐户中扣除金额。';
	end if;
exception
	when no_data_found then
		p_isSuccessful := 0;
		p_errorMsg := '转帐失败，无法找到用户信息。';
		rollback;
		return;
	when value_error then
		p_isSuccessful := 0;
		p_errorMsg := '转帐失败，金额数字问题。';
		rollback;
		return;
	when others then
		p_isSuccessful := 0;
		p_errorMsg := '转帐失败，未知错误。';
		rollback;
		return;
end remit;
/

/* 4.trade.newTrade
************************************************************************************
 * 过程:
 *	newTrade(
 *		 p_listId			in		varchar2,
 *		 p_buyerId			in		varchar2,
 *		 p_salerId			in		varchar2,
 *		 p_goodId			in		varchar2,