proc trades.sql

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

 *		 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 where (buyerid=p_userId) and (isSuccessful=0);
	return v_tradeInfo_cursor;
end getNotSucesTrades;
/

/* 13.trade.setPayed
************************************************************************************
 * 过程:
 *	setPayed(
 *		 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 setPayed(
 		 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 isPayed=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 setPayed;
/