proc users.sql

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

 		 p_balance			in		number,
 		 p_isSuccessful		out		number,
 		 p_errorMsg			out		varchar2
 		)
as
begin
	if (p_balance<=0) then
		p_isSuccessful := 0;
		p_errorMsg := '充值金额必须大于0元。';
	end if;
	
	update users
		set balance=balance+p_balance
		where id=p_userId;
	if (SQL%ROWCOUNT=1) then
		p_isSuccessful := 1;
		p_errorMsg := ' ';
		commit;
	else
		p_isSuccessful := 0;
		p_errorMsg := 'Oracle无法为该用户充值。';
		rollback;
	end if;
end deposit;
/

/* 6.user.pay
************************************************************************************
 * 过程:
 *	pay(
 *		 p_userId			in		varchar2,
 *		 p_password			in		varchar2,
 *		 p_cost				in		number,
 *		 p_isSuccessful		out		number,
 *		 p_errorMsg			out		varchar2
 *		);
 * 作用:
 *	用户支付;
 * 参数:
 *	(1)p_userId:		varchar2	in	用户ID
 *	(2)p_password:		varchar2	in	用户密码
 *	(3)p_cost:			number		in	用户支付金额
 *	(4)p_isSuccessful:	number		out	充值是否成功标志
 *	(5)p_errorMsg:		varchar2	out	充值失败信息(充值成功时为空串)
************************************************************************************
*/

create or replace procedure pay(
 		 p_userId			in		varchar2,
 		 p_password			in		varchar2,
 		 p_cost				in		number,
 		 p_isSuccessful		out		number,
 		 p_errorMsg			out		varchar2
 		)
as
begin
	if (loginValidate(p_userId, p_password)) then
		if (p_cost<=0) then
			p_isSuccessful := 0;
			p_errorMsg := '支付金额必须大于0元。';
		end if;
		
		update users
			set balance=balance-p_cost
			where id=p_userId;
		if (SQL%ROWCOUNT=1) then
			p_isSuccessful := 1;
			p_errorMsg := ' ';
			commit;
		else
			p_isSuccessful := 0;
			p_errorMsg := 'Oracle无法为该用户支付。';
			rollback;
		end if;
	else
		p_isSuccessful := 0;
		p_errorMsg := '请先登陆，再进行支付。';
	end if;
end pay;
/

/* 7.user.addGoodToCart
************************************************************************************
 * 过程:
 *	addGoodToCart(
 *		 p_userId			in		varchar2,
 *		 p_password			in		varchar2,
 *		 p_goodId			in		number,
 *		 p_goodCount		in		varchar2,
 *		 p_goodType			in		char,
 *		 p_isSuccessful		out		number,
 *		 p_errorMsg			out		varchar2
 *		);
 * 作用:
 *	添加商品到购物车;
 * 参数:
 *	(1)p_userId:		varchar2	in	用户ID
 *	(2)p_password:		varchar2	in	用户密码
 *	(3)p_goodId:		varchar2	in	商品编号
 *	(4)p_goodCount:		number		in	商品数量
 *	(5)p_goodType:		char		in	商品类型
 *	(6)p_isSuccessful:	number		out	添加是否成功标志
 *	(7)p_errorMsg:		varchar2	out	添加失败信息(添加成功时为空串)
************************************************************************************
*/

create or replace procedure addGoodToCart(
 		 p_userId			in		varchar2,
 		 p_password			in		varchar2,
 		 p_goodId			in		varchar2,
 		 p_goodCount		in		number,
 		 p_goodType			in		char,
 		 p_isSuccessful		out		number,
 		 p_errorMsg			out		varchar2
 		)
as
	v_count number(1) := 0;
begin
	if (loginValidate(p_userId, p_password)) then
		select count(*) into v_count from carts
			where (userId=p_userId) and (goodId=p_goodId) and (goodType=p_goodType);
		if (v_count=0) then
			insert into carts values(p_userId, p_goodId, p_goodCount, p_goodType);
		elsif(v_count=1) then
			update carts set goodCount=goodCount+p_goodCount
				where (userId=p_userId) and (goodId=p_goodId) and (goodType=p_goodType);
		else
			p_isSuccessful := 0;
			p_errorMsg := '系统数据出现严重故障，数据表carts中的数据完整性受损。';
			return;
		end if;
		
		if (SQL%ROWCOUNT=1) then
			p_isSuccessful := 1;
			p_errorMsg := ' ';
			commit;
		else
			p_isSuccessful := 0;
			p_errorMsg := 'Oracle无法为该用户添加商品。';
			rollback;
		end if;
	else
		p_isSuccessful := 0;
		p_errorMsg := '请先登陆，再进行添加商品。';
	end if;
end addGoodToCart;
/

/* 8.user.removeGoodFromCart
************************************************************************************
 * 过程:
 *	removeGoodFromCart(
 *		 p_userId			in		varchar2,
 *		 p_password			in		varchar2,
 *		 p_goodId			in		varchar2,
 *		 p_goodType			in		char,
 *		 p_isSuccessful		out		number,
 *		 p_errorMsg			out		varchar2
 *		);
 * 作用:
 *	从购物车中删除商品;
 * 参数:
 *	(1)p_userId:		varchar2	in	用户ID
 *	(2)p_password:		varchar2	in	用户密码
 *	(3)p_goodId:		varchar2	in	商品编号
 *	(4)p_goodType:		char		in	商品类型
 *	(5)p_isSuccessful:	number		out	删除是否成功标志
 *	(6)p_errorMsg:		varchar2	out	删除失败信息(删除成功时为空串)
************************************************************************************
*/

create or replace procedure removeGoodFromCart(
 		 p_userId			in		varchar2,
 		 p_password			in		varchar2,
 		 p_goodId			in		varchar2,
 		 p_goodType			in		char,
 		 p_isSuccessful		out		number,
 		 p_errorMsg			out		varchar2
 		)
as
begin
	if (loginValidate(p_userId, p_password)) then
		delete from carts
			where (userId=p_userId) and (goodId=p_goodId) and (goodType=p_goodType);
		if (SQL%ROWCOUNT=1) then
			p_isSuccessful := 1;
			p_errorMsg := ' ';
			commit;
		elsif (SQL%ROWCOUNT=0) then
			p_isSuccessful := 0;
			p_errorMsg := 'Oracle无法为该用户删除商品。';
			rollback;
		else
			p_isSuccessful := 0;
			p_errorMsg := '系统数据出现严重故障，数据表carts中的数据完整性受损。';
			return;		
		end if;
	else
		p_isSuccessful := 0;
		p_errorMsg := '请先登陆，再进行删除商品。';
	end if;
end removeGoodFromCart;
/

/* 9.user.getUserInfo
*************************************************************************
 * 函数:
 *	SYS_REFCURSOR getUserInfo(p_userId varchar2);
 *
 * 作用:
 *	获取指定用户的信息;
 * 参数:
 *	(1)p_userId:		varchar2	用户ID
*************************************************************************
*/
create or replace function getUserInfo(p_userId varchar2)
return SYS_REFCURSOR as
  v_userInfo_cursor SYS_REFCURSOR;
begin
  open v_userInfo_cursor for
    select * from users where id=p_userId;
  return v_userInfo_cursor;
end getUserInfo;
/

/* 10.user.getCartInfo
*************************************************************************
 * 函数:
 *	SYS_REFCURSOR getCartInfo(p_userId varchar2);
 *
 * 作用:
 *	获取指定用户的购物车信息;
 * 参数:
 *	(1)p_userId:		varchar2	用户ID
*************************************************************************
*/
create or replace function getCartInfo(p_userId varchar2)
return SYS_REFCURSOR as
  v_cartInfo_cursor SYS_REFCURSOR;
begin
  open v_cartInfo_cursor for
    select * from carts where userid=p_userId;
  return v_cartInfo_cursor;
end getCartInfo;
/

/* 11.user.getGoodCounterInfo
*************************************************************************
 * 函数:
 *	SYS_REFCURSOR getGoodCounterInfo(p_userId varchar2);
 *
 * 作用:
 *	获取指定用户的商品柜台信息;
 * 参数:
 *	(1)p_userId:		varchar2	用户ID
*************************************************************************
*/
create or replace function getGoodCounterInfo(p_userId varchar2)
return SYS_REFCURSOR as
  v_goodCounterInfo_cursor SYS_REFCURSOR;
begin
  open v_goodCounterInfo_cursor for
    select * from goods where salerid=p_userId;
  return v_goodCounterInfo_cursor;
end getGoodCounterInfo;
/

/* 12.user.getBookCounterInfo
*************************************************************************
 * 函数:
 *	SYS_REFCURSOR getBookCounterInfo(p_userId varchar2);
 *
 * 作用:
 *	获取指定用户的图书柜台信息;
 * 参数:
 *	(1)p_userId:		varchar2	用户ID
*************************************************************************
*/
create or replace function getBookCounterInfo(p_userId varchar2)
return SYS_REFCURSOR as
  v_bookCounterInfo_cursor SYS_REFCURSOR;
begin
  open v_bookCounterInfo_cursor for
    select * from books where salerid=p_userId;
  return v_bookCounterInfo_cursor;
end getBookCounterInfo;
/