procedures.sql

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

 *		 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,
 *		 p_goodCount		in		number,
 *		 p_goodType			in		char,
 *		 p_isSuccessful		out		number,
 *		 p_errorMsg			out		varchar2
 *		);
 * 作用:
 *	新增交易;
 * 参数:
 *	(1)p_listId:		varchar2	in	交易所属定单号
 *	(2)p_buyerId:		varchar2	in	买方用户ID
 *	(3)p_salerId:		varchar2	in	卖方用户ID
 *	(4)p_goodId:		varchar2	in	商品ID
 *	(5)p_goodCount:		number		in	交易的商品数量
 *	(6)p_goodType:		char		in	交易的商品类型
 *	(7)p_isSuccessful:	number		out	转帐成功标志
 *	(8)p_errorMsg:		varchar2	out	转帐失败信息(成功时为空串)
************************************************************************************
*/
create or replace procedure newTrade(
 		 p_listId			in		varchar2,
 		 p_buyerId			in		varchar2,
 		 p_salerId			in		varchar2,
 		 p_goodId			in		varchar2,
 		 p_goodCount		in		number,
 		 p_goodType			in		char,
 		 p_isSuccessful		in out	number,
 		 p_errorMsg			in out	varchar2
 		)
as
	v_tradeId varchar2(50);
begin
	/* 步骤1:生成交易号 */
	v_tradeId := concat(concat(to_char(sysdate,'yyyymmddhh24miss'),p_buyerId),p_salerId);
	
	/* 步骤2:生成交易 */
	insert into trades
		values(v_tradeId, p_listId, p_buyerId, p_salerId, p_goodId,
			p_goodCount, p_goodType, 0, 0);
	if (SQL%ROWCOUNT=1) then
		p_isSuccessful := 1;
		p_errorMsg := ' ';
		commit;
	else
		p_isSuccessful := 0;
		p_errorMsg := concat(p_errorMsg, '交易失败在步骤2生成交易。');
		rollback;
	end if;
end newTrade;
/

/* 5.trade.setTradeSuccessful
************************************************************************************
 * 过程:
 *	setTradeSuccessful(
 *		 p_tradeId			in		varchar2,
 *		 p_isSuccessful		out		number,
 *		 p_errorMsg			out		varchar2
 *		);
 * 作用:
 *	设置交易已经成功;
 * 参数:
 *	(1)p_tradeId:		varchar2	in	交易号
 *	(2)p_isSuccessful:	number		out	设置成功标志
 *	(3)p_errorMsg:		varchar2	out	设置失败信息(成功时为空串)
************************************************************************************
*/
create or replace procedure setTradeSuccessful(
 		 p_tradeId			in		varchar2,
 		 p_isSuccessful		in out	number,
 		 p_errorMsg			in out	varchar2
 		)
as
	v_itemCount number(1) := -1;
begin
	select count(*) into v_itemCount from trades where id=p_tradeId;
	if (v_itemCount <> 1) then
		p_isSuccessful := 0;
		p_errorMsg := '该交易不存在。';
		return;
	end if;
	
	update trades set isSuccessful=1 where id=p_tradeId;
	if (SQL%ROWCOUNT=1) then
		p_isSuccessful := 1;
		p_errorMsg := ' ';
		commit;
	else
		p_isSuccessful := 0;
		p_errorMsg := '无法更新交易成功状态。';
		rollback;
	end if;
end setTradeSuccessful;
/

/* 6.trade.setListSuccessful
************************************************************************************
 * 过程:
 *	setListSuccessful(
 *		 p_listId			in		varchar2,
 *		 p_isSuccessful		out		number,
 *		 p_errorMsg			out		varchar2
 *		);
 * 作用:
 *	设置定单上所有的交易已经成功;
 * 参数:
 *	(1)p_listId:		varchar2	in	定单号
 *	(2)p_isSuccessful:	number		out	设置成功标志
 *	(3)p_errorMsg:		varchar2	out	设置失败信息(成功时为空串)
************************************************************************************
*/
create or replace procedure setListSuccessful(
 		 p_listId			in		varchar2,
 		 p_isSuccessful		in out	number,
 		 p_errorMsg			in out	varchar2
 		)
as
	v_itemCount number(1) := -1;
begin
	select count(*) into v_itemCount from trades where listId=p_listId;
	if (v_itemCount <= 0) then
		p_isSuccessful := 0;
		p_errorMsg := '该定单不存在。';
		return;
	end if;
	
	update trades set isSuccessful=1 where listId=p_listId;
	if (SQL%ROWCOUNT=v_itemCount) then
		p_isSuccessful := 1;
		p_errorMsg := ' ';
		commit;
	else
		p_isSuccessful := 0;
		p_errorMsg := '无法更新定单成功状态。';
		rollback;
	end if;
end setListSuccessful;
/


/* 7.trade.getTradeInfo
*************************************************************************
 * 函数:
 *	SYS_REFCURSOR getTradeInfo(p_tradeId varchar2);
 *
 * 作用:
 *	获取指定交易信息;
 * 参数:
 *	(1)p_tradeId:		varchar2		交易编号
*************************************************************************
*/
create or replace function getTradeInfo(p_tradeId varchar2)
return SYS_REFCURSOR as
	v_tradeInfo_cursor SYS_REFCURSOR;
begin
	open v_tradeInfo_cursor for
		select * from trades where id=p_tradeId;
	return v_tradeInfo_cursor;
end getTradeInfo;
/

/* 8.trade.getTradeTime
*************************************************************************
 * 函数:
 *	date getTradeTime(p_tradeId varchar2);
 *
 * 作用:
 *	获取指定交易时间;
 * 参数:
 *	(1)p_tradeId:		varchar2		交易编号
*************************************************************************
*/
create or replace function getTradeTime(p_tradeId varchar2)
return date as
	v_tradeTime date;
begin
	v_tradeTime := to_date(substr(p_tradeId, 1, 14), 'yyyymmddhh24miss');
	return v_tradeTime;
end getTradeTime;
/

/* 9.trade.getPayedTrades
*************************************************************************
 * 函数:
 *	SYS_REFCURSOR getPayedTrades(p_userId varchar2);
 *
 * 作用:
 *	获取指定用户所有已经支付的交易;
 * 参数:
 *	(1)p_userId:		varchar2		用户ID
*************************************************************************
*/
create or replace function getPayedTrades(p_userId varchar2)
return SYS_REFCURSOR as
	v_tradeInfo_cursor SYS_REFCURSOR;
begin
	open v_tradeInfo_cursor for
		select * from trades where (buyerid=p_userId) and (isPayed=1);
	return v_tradeInfo_cursor;
end getPayedTrades;
/

/* 10.trade.getNotPayedTrades
*************************************************************************
 * 函数:
 *	SYS_REFCURSOR getNotPayedTrades(p_userId varchar2);
 *
 * 作用:
 *	获取指定用户所有未支付的交易;
 * 参数:
 *	(1)p_userId:		varchar2		用户ID
*************************************************************************
*/
create or replace function getNotPayedTrades(p_userId varchar2)
return SYS_REFCURSOR as
	v_tradeInfo_cursor SYS_REFCURSOR;
begin
	open v_tradeInfo_cursor for
		select * from trades where (buyerId=p_userId) and (isPayed=0);
	return v_tradeInfo_cursor;
end getNotPayedTrades;
/

/* 11.trade.getSucesTrades
*************************************************************************
 * 函数:
 *	SYS_REFCURSOR getSucesTrades(p_userId varchar2);
 *
 * 作用:
 *	获取指定用户所有已经成功的交易;
 * 参数:
 *	(1)p_userId:		varchar2		用户ID
*************************************************************************
*/
create or replace function getSucesTrades(p_userId varchar2)
return SYS_REFCURSOR as
	v_tradeInfo_cursor SYS_REFCURSOR;
begin
	open v_tradeInfo_cursor for
		select * from trades where (buyerid=p_userId) and (isSuccessful=1);
	return v_tradeInfo_cursor;
end getSucesTrades;
/

/* 12.trade.getNotSucesTrades
*************************************************************************
 * 函数:
 *	SYS_REFCURSOR getNotSucesTrades(p_userId varchar2);
 *
 * 作用:
 *	获取指定用户所有未成功的交易;
 * 参数:
 *	(1)p_userId:		varchar2		用户ID
*************************************************************************
*/
create or replace function getNotSucesTrades(p_userId varchar2)
return SYS_REFCURSOR as
	v_tradeInfo_cursor SYS_REFCURSOR;
begin
	open v_tradeInfo_cursor for
		select * from trades w