proc goods.sql

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

			p_errorMsg := ' ';
			commit;
		else
			p_isSuccessful := 0;
			p_errorMsg := 'Oracle无法为该用户更新图书库存信息。';
			rollback;
		end if;
	else
		p_isSuccessful := 0;
		p_errorMsg := '请先登陆，再进行库存更新。';
	end if;
end updateBookStock;
/

/* 13.good.setGoodPrice
************************************************************************************
 * 过程:
 *	setGoodPrice(
 *		 p_userId			in		varchar2,
 *		 p_password			in		varchar2,
 *		 p_goodId			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_goodId:		varchar2	in	商品编号
 *	(4)p_price:			number		in	商品单价
 *	(5)p_isSuccessful:	number		out	定价是否成功标志
 *	(6)p_errorMsg:		varchar2	out	定价失败信息(定价成功时为空串)
************************************************************************************
*/

create or replace procedure setGoodPrice(
 		 p_userId			in		varchar2,
 		 p_password			in		varchar2,
 		 p_goodId			in		varchar2,
 		 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;
/