proc goods.sql

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

		update goods set isReleased=1 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 releaseBook;
/

/* 7.good.encloseGood
************************************************************************************
 * 过程:
 *	encloseGood(
 *		 p_userId			in		varchar2,
 *		 p_password			in		varchar2,
 *		 p_goodId			in		varchar2,
 *		 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_isSuccessful:	number		out	取消发布是否成功标志
 *	(5)p_errorMsg:		varchar2	out	取消发布失败信息(取消发布成功时为空串)
************************************************************************************
*/

create or replace procedure encloseGood(
 		 p_userId			in		varchar2,
 		 p_password			in		varchar2,
 		 p_goodId			in		varchar2,
 		 p_isSuccessful		out		number,
 		 p_errorMsg			out		varchar2
 		)
as
begin
	if (loginValidate(p_userId, p_password)) then
		update goods set isReleased=0 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 encloseGood;
/

/* 8.good.encloseBook
************************************************************************************
 * 过程:
 *	encloseBook(
 *		 p_userId			in		varchar2,
 *		 p_password			in		varchar2,
 *		 p_bookId			in		varchar2,
 *		 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_isSuccessful:	number		out	取消发布是否成功标志
 *	(5)p_errorMsg:		varchar2	out	取消发布失败信息(发布成功时为空串)
************************************************************************************
*/

create or replace procedure encloseBook(
 		 p_userId			in		varchar2,
 		 p_password			in		varchar2,
 		 p_bookId			in		varchar2,
 		 p_isSuccessful		out		number,
 		 p_errorMsg			out		varchar2
 		)
as
begin
	if (loginValidate(p_userId, p_password)) then
		update goods set isReleased=0 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 encloseBook;
/

/* 9.good.updateGoodInfo
************************************************************************************
 * 过程:
 *	updateGoodInfo(
 *		 p_userId			in		varchar2,
 *		 p_password			in		varchar2,
 *		 p_goodId			in		varchar2,
 *		 p_name				in		varchar2,
 *		 p_price			in		number,
 *		 p_goodNum			in		number,
 *		 p_category#1		in		varchar2,
 *		 p_category#2		in		varchar2,
 *		 p_category#3		in		varchar2,
 *		 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_name:			varchar2	in	商品名称
 *	(5)p_price:			number		in	商品单价
 *	(6)p_goodNum:		number		in	商品库存
 *	(7)p_category#1:	varchar2	in	商品的一级类别
 *	(8)p_category#2:	varchar2	in	商品的二级类别
 *	(9)p_category#3:	varchar2	in	商品的三级类别
 *	(10)p_isSuccessful:	number		out	更新是否成功标志
 *	(11)p_errorMsg:		varchar2	out	更新失败信息(更新成功时为空串)
************************************************************************************
*/

create or replace procedure updateGoodInfo(
 		 p_userId			in		varchar2,
 		 p_password			in		varchar2,
 		 p_goodId			in		varchar2,
 		 p_name				in		varchar2,
 		 p_price			in		number,
 		 p_goodNum			in		number,
 		 p_category#1		in		varchar2,
 		 p_category#2		in		varchar2,
 		 p_category#3		in		varchar2,
 		 p_isSuccessful		out		number,
 		 p_errorMsg			out		varchar2
 		)
as
begin
	if (loginValidate(p_userId, p_password)) then
		update goods set
			name=p_name, price=p_price, num=p_goodNum,category#1=p_category#1,
				category#2=p_category#2, category#3=p_category#3
			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 updateGoodInfo;
/

/* 10.good.updateBookInfo
************************************************************************************
 * 过程:
 *	updateBookInfo(
 *		 p_userId			in		varchar2,
 *		 p_password			in		varchar2,
 *		 p_bookId			in		varchar2,
 *		 p_name				in		varchar2,
 *		 p_price			in		number,
 *		 p_goodNum			in		number,
 *		 p_category#1		in		varchar2,
 *		 p_category#2		in		varchar2,
 *		 p_author			in		varchar2,
 *		 p_press			in		varchar2,
 *		 p_edition			in		number,
 *		 p_isbn				in		varchar2,
 *		 p_categoryNUM		in		varchar2,
 *		 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_name:			varchar2	in	图书名称
 *	(5)p_price:			number		in	图书单价
 *	(6)p_goodNum:		number		in	图书库存
 *	(7)p_category#1:	varchar2	in	图书的一级类别
 *	(8)p_category#2:	varchar2	in	图书的二级类别
 *	(9)p_author:		varchar2	in	图书的作者
 *	(10)p_press:		varchar2	in	图书的出版社
 *	(11)p_editon:		number		in	图书版本
 *	(12)p_isbn:			varchar2	in	图书的国际统一图书编号
 *	(13)p_categoryNUM:	varchar2	in	图书的中国图书分类号
 *	(14)p_isSuccessful:	number		out	更新是否成功标志
 *	(15)p_errorMsg:		varchar2	out	更新失败信息(更新成功时为空串)
************************************************************************************
*/

create or replace procedure updateBookInfo(
 		 p_userId			in		varchar2,
 		 p_password			in		varchar2,
 		 p_bookId			in		varchar2,
 		 p_name				in		varchar2,
 		 p_price			in		number,
 		 p_goodNum			in		number,
 		 p_category#1		in		varchar2,
 		 p_category#2		in		varchar2,
 		 p_author			in		varchar2,
 		 p_press			in		varchar2,
 		 p_edition			in		number,
 		 p_isbn				in		varchar2,
 		 p_categoryNUM		in		varchar2,
 		 p_isSuccessful		out		number,
 		 p_errorMsg			out		varchar2
 		)
as
begin
	if (loginValidate(p_userId, p_password)) then
		update books set
			name=p_name, price=p_price, num=p_goodNum, category#1=p_category#1,
				category#2=p_category#2, author=p_author, press=p_press,
				edition=p_edition, isbn=p_isbn, categoryNUM=p_categoryNUM
			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 updateBookInfo;
/

/* 11.good.updateGoodStock
************************************************************************************
 * 过程:
 *	updateGoodStock(
 *		 p_userId			in		varchar2,
 *		 p_password			in		varchar2,
 *		 p_goodId			in		varchar2,
 *		 p_goodNum			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_goodNum:		number		in	商品库存
 *	(5)p_isSuccessful:	number		out	更新是否成功标志
 *	(6)p_errorMsg:		varchar2	out	更新失败信息(更新成功时为空串)
************************************************************************************
*/

create or replace procedure updateGoodStock(
 		 p_userId			in		varchar2,
 		 p_password			in		varchar2,
 		 p_goodId			in		varchar2,
 		 p_goodNum			in		number,
 		 p_isSuccessful		out		number,
 		 p_errorMsg			out		varchar2
 		)
as
begin
	if (loginValidate(p_userId, p_password)) then
		update goods set num=p_goodNum 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 updateGoodStock;
/

/* 12.good.updateBookStock
************************************************************************************
 * 过程:
 *	updateBookStock(
 *		 p_userId			in		varchar2,
 *		 p_password			in		varchar2,
 *		 p_bookId			in		varchar2,
 *		 p_bookNum			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_bookNum:		number		in	图书库存
 *	(5)p_isSuccessful:	number		out	更新是否成功标志
 *	(6)p_errorMsg:		varchar2	out	更新失败信息(更新成功时为空串)
************************************************************************************
*/

create or replace procedure updateBookStock(
 		 p_userId			in		varchar2,
 		 p_password			in		varchar2,
 		 p_bookId			in		varchar2,
 		 p_bookNum			in		number,
 		 p_isSuccessful		out		number,
 		 p_errorMsg			out		varchar2
 		)
as
begin
	if (loginValidate(p_userId, p_password)) then
		update books set num=p_bookNum where id=p_bookId;
		if (SQL%ROWCOUNT = 1) then
			p_isSuccessful := 1;