proc goods.sql

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

/*
 *==================================================================================
 * 2.good包
 *
 *  过程列表： 
 *	(1)newGood				新增商品
 *	(2)newBook				新增图书
 *	(3)removeGood			删除商品
 *	(4)removeBook			删除图书
 *  (5)releaseGood			发布商品
 *	(6)releaseBook			发布图书
 *	(7)encloseGood			取消发布商品
 *	(8)encloseBook			取消发布图书
 *	(9)updateGoodInfo		更新商品信息
 *	(10)updateBookInfo		更新图书信息
 *	(11)updateGoodStock		更新商品库存
 *	(12)updateBookStock		更新图书库存
 *	(13)setGoodPrice		给商品定价
 *	(14)setBookPrice		给图书定价
 *	(15)getGoodInfo			获取商品信息
 *	(16)getBookInfo			获取图书信息
 *	(17)getAllGoodInfo		获取所有已经发布的商品信息
 *	(18)getAllBookInfo		获取所有已经发布的图书信息
 *	(19)getSomeGoodInfo		获取满足特定条件的商品信息
 *	(20)getSomeBookInfo		获取满足特定条件的图书信息
 *	(21)newCategory			新增商品类别
 *	(22)removeCategory		删除商品类别
 *	(23)getAllCategory		获取指定级别的所有商品类别
 *==================================================================================
*/

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

create or replace procedure newGood(
 		 p_userId			in		varchar2,
 		 p_password			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
	v_goodId varchar2(33);
begin
	if (loginValidate(p_userId, p_password)) then
		v_goodId := concat(to_char(sysdate,'yyyymmddhh24miss'),p_userId);
		insert into goods
			values(v_goodId, p_name, p_price, p_goodNum, p_userId,
					p_category#1, p_category#2, p_category#3, 0);
		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 newGood;
/

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

create or replace procedure newBook(
 		 p_userId			in		varchar2,
 		 p_password			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
	v_bookId varchar2(33);
begin
	if (loginValidate(p_userId, p_password)) then
		v_bookId := concat(to_char(sysdate,'yyyymmddhh24miss'),p_userId);
		insert into books
			values(v_bookId, p_name, p_price, p_goodNum, p_userId,
					p_category#1, p_category#2, p_author, p_press,
					p_edition, p_isbn, p_categoryNUM, 0);
		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 newBook;
/
/* 3.good.removeGood
************************************************************************************
 * 过程:
 *	removeGood(
 *		 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 removeGood(
 		 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
		delete from goods 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 removeGood;
/

/* 4.good.removeBook
************************************************************************************
 * 过程:
 *	removeBook(
 *		 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 removeBook(
 		 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
		delete from books 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 removeBook;
/

/* 5.good.releaseGood
************************************************************************************
 * 过程:
 *	releaseGood(
 *		 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 releaseGood(
 		 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=1 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 releaseGood;
/

/* 6.good.releaseBook
************************************************************************************
 * 过程:
 *	releaseBook(
 *		 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 releaseBook(
 		 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