procedures.sql

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

 		 p_price			in		number,
 		 p_isSuccessful		out		number,
 		 p_errorMsg			out		varchar2
 		)
as
begin
	if (loginValidate(p_userId, p_password)) then
		update goods set price=p_price where id=p_goodId;
		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 setGoodPrice;
/

/* 14.good.setBookPrice
************************************************************************************
 * 过程:
 *	setBookPrice(
 *		 p_userId			in		varchar2,
 *		 p_password			in		varchar2,
 *		 p_bookId			in		varchar2,
 *		 p_price			in		number,
 *		 p_isSuccessful		out		number,
 *		 p_errorMsg			out		varchar2
 *		);
 * 作用:
 *	图书定价;
 * 参数:
 *	(1)p_userId:		varchar2	in	用户ID
 *	(2)p_password:		varchar2	in	用户密码
 *	(3)p_bookId:		varchar2	in	图书编号
 *	(4)p_price:			number		in	图书单价
 *	(5)p_isSuccessful:	number		out	定价是否成功标志
 *	(6)p_errorMsg:		varchar2	out	定价失败信息(定价成功时为空串)
************************************************************************************
*/

create or replace procedure setBookPrice(
 		 p_userId			in		varchar2,
 		 p_password			in		varchar2,
 		 p_bookId			in		varchar2,
 		 p_price			in		number,
 		 p_isSuccessful		out		number,
 		 p_errorMsg			out		varchar2
 		)
as
begin
	if (loginValidate(p_userId, p_password)) then
		update books set price=price where id=p_bookId;
		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 setBookPrice;
/


/* 15.good.getGoodInfo
*************************************************************************
 * 函数:
 *	SYS_REFCURSOR getGoodInfo(p_goodId varchar2);
 *
 * 作用:
 *	获取指定商品信息;
 * 参数:
 *	(1)p_goodId:		varchar2	商品ID
*************************************************************************
*/
create or replace function getGoodInfo(p_goodId varchar2)
return SYS_REFCURSOR as
  v_goodInfo_cursor SYS_REFCURSOR;
begin
  open v_goodInfo_cursor for
    select * from goods where id=p_goodId;
  return v_goodInfo_cursor;
end getGoodInfo;
/

/* 16.good.getBookInfo
*************************************************************************
 * 函数:
 *	SYS_REFCURSOR getBookInfo(p_bookId varchar2);
 *
 * 作用:
 *	获取指定图书信息;
 * 参数:
 *	(1)p_bookId:		varchar2	图书ID
*************************************************************************
*/
create or replace function getBookInfo(p_bookId varchar2)
return SYS_REFCURSOR as
  v_bookInfo_cursor SYS_REFCURSOR;
begin
  open v_bookInfo_cursor for
    select * from books where id=p_bookId;
  return v_bookInfo_cursor;
end getBookInfo;
/

/* 17.good.getAllGoodInfo
*************************************************************************
 * 函数:
 *	SYS_REFCURSOR getAllGoodInfo();
 *
 * 作用:
 *	获取所有已经发布的商品信息;
 * 参数:
 *	
*************************************************************************
*/
create or replace function getAllGoodInfo
return SYS_REFCURSOR as
  v_allGoodInfo_cursor SYS_REFCURSOR;
begin
  open v_allGoodInfo_cursor for
    select * from goods where isReleased=1;
  return v_allGoodInfo_cursor;
end getAllGoodInfo;
/

/* 18.good.getAllBookInfo
*************************************************************************
 * 函数:
 *	SYS_REFCURSOR getAllBookInfo();
 *
 * 作用:
 *	获取所有已经发布的图书信息;
 * 参数:
 *	
*************************************************************************
*/
create or replace function getAllBookInfo
return SYS_REFCURSOR as
  v_allBookInfo_cursor SYS_REFCURSOR;
begin
  open v_allBookInfo_cursor for
    select * from books where isReleased=1;
  return v_allBookInfo_cursor;
end getAllBookInfo;
/

/* 21.good.newCategory
************************************************************************************
 * 过程:
 *	newCategory(
 *		 p_userId			in		varchar2,
 *		 p_password			in		varchar2,
 *		 p_category			in		varchar2,
 *		 p_categoryLevel	in		number,
 *		 p_isSuccessful		out		number,
 *		 p_errorMsg			out		varchar2
 *		);
 * 作用:
 *	增加商品类别;
 * 参数:
 *	(1)p_userId:		varchar2	in	用户ID
 *	(2)p_password:		varchar2	in	用户密码
 *	(3)p_category:		varchar2	in	商品类别
 *	(4)p_categoryLevel:	number		in	商品类别级别
 *	(5)p_isSuccessful:	number		out	增加商品类别是否成功标志
 *	(6)p_errorMsg:		varchar2	out	增加商品类别失败信息(成功时为空串)
************************************************************************************
*/
create or replace procedure newCategory(
 		 p_userId			in		varchar2,
 		 p_password			in		varchar2,
 		 p_category			in		varchar2,
 		 p_categoryLevel	in		number,
 		 p_isSuccessful		out		number,
 		 p_errorMsg			out		varchar2 		 
 		)
as
begin
	if (loginValidate(p_userId, p_password)) then
		if (p_categoryLevel = 1) then
			insert into categories#1 values(p_category);
		elsif (p_categoryLevel = 2) then
			insert into categories#2 values(p_category);
		elsif (p_categoryLevel = 3) then
			insert into categories#3 values(p_category);
		else
			p_isSuccessful := 0;
			p_errorMsg := '商品类别等级不合法，请更改商品类别等级后再进行增加操作。';			
			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 newCategory;
/

/* 22.good.reomveCategory
************************************************************************************
 * 过程:
 *	reomveCategory(
 *		 p_userId			in		varchar2,
 *		 p_password			in		varchar2,
 *		 p_category			in		varchar2,
 *		 p_categoryLevel	in		number,
 *		 p_isSuccessful		out		number,
 *		 p_errorMsg			out		varchar2
 *		);
 * 作用:
 *	删除商品类别;
 * 参数:
 *	(1)p_userId:		varchar2	in	用户ID
 *	(2)p_password:		varchar2	in	用户密码
 *	(3)p_category:		varchar2	in	商品类别
 *	(4)p_categoryLevel:	number		in	商品类别级别
 *	(5)p_isSuccessful:	number		out	删除商品类别是否成功标志
 *	(6)p_errorMsg:		varchar2	out	删除商品类别失败信息(成功时为空串)
************************************************************************************
*/
create or replace procedure reomveCategory(
 		 p_userId			in		varchar2,
 		 p_password			in		varchar2,
 		 p_category			in		varchar2,
 		 p_categoryLevel	in		number,
 		 p_isSuccessful		out		number,
 		 p_errorMsg			out		varchar2 		 
 		)
as
begin
	if (loginValidate(p_userId, p_password)) then
		if (p_categoryLevel = 1) then
			delete from categories#1 where category=p_category;
		elsif (p_categoryLevel = 2) then
			delete from categories#2 where category=p_category;
		elsif (p_categoryLevel = 3) then
			delete from categories#3 where category=p_category;
		else
			p_isSuccessful := 0;
			p_errorMsg := '商品类别等级不合法，请更改商品类别等级后再进行删除操作。';			
			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 reomveCategory;
/

/* 23.good.getAllCategory
*************************************************************************
 * 函数:
 *	SYS_REFCURSOR getAllCategory(p_level number);
 *
 * 作用:
 *	获取指定级别的所有商品类别;
 * 参数:
 *	(1)p_level:			number		商品类别的级别
*************************************************************************
*/
create or replace function getAllCategory(p_level number)
return SYS_REFCURSOR as
  v_allCategoryInfo_cursor SYS_REFCURSOR;
begin
  if (p_level=1) then
    open v_allCategoryInfo_cursor for
      select * from categories#1;
  elsif (p_level=2) then
    open v_allCategoryInfo_cursor for
      select * from categories#2;
  elsif (p_level=3) then
    open v_allCategoryInfo_cursor for
      select * from categories#3;
  end if;
  return v_allCategoryInfo_cursor;
end getAllCategory;
/

/*
 *==================================================================================
 * 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,