📄 procedures.sql
字号:
/*
*==================================================================================
* 1.user包
*
* 过程列表：
* (0)loginValidate 用户登陆验证(这是个函数，仅供数据库内部使用)
* (1)loginCheck 用户登陆验证(这是个过程，数据库内部和外部都可以使用)
* (2)register 用户注册
* (3)updateInfo 更新用户信息(仅更新用户名字、性别和备注信息)
* (4)updatePassword 修改用户密码
* (5)deposit 用户充值
* (6)pay 用户支付
* (7)addGoodToCart 添加商品到购物车
* (8)removeGoodFromCart 从购物车中删除商品
* (9)getUserInfo 获取用户信息
* (10)getCartInfo 获取用户购物车信息
* (11)getGoodCounterInfo 获取指定用户的商品柜台信息
* (12)getBookCounterInfo 获取指定用户的图书柜台信息
*==================================================================================
*/
/* 0.user.loginValidate
************************************************************************************
* 函数:
* boolean loginValidate(
* p_userId varchar2,
* p_password varchar2
* );
* 作用:
* 用户登陆验证;
* 参数:
* (1)p_userId: varchar2 用户ID
* (2)p_password: varchar2 用户密码
************************************************************************************
*/
create or replace function loginValidate(
p_userId varchar2,
p_password varchar2
)
return boolean as
v_count number(1) := 0;
begin
select count(*) into v_count
from users
where (id=p_userId) and (password=p_password);
if (v_count=1) then
return true;
else
return false;
end if;
end loginValidate;
/
/* 1.user.loginCheck
************************************************************************************
* 过程:
* loginCheck(
* p_userId in varchar2,
* p_password in varchar2,
* p_isSuccessful out number,
* p_errorMsg out varchar2
* );
* 作用:
* 用户登陆验证;
* 参数:
* (1)p_userId: varchar2 in 用户ID
* (2)p_password: varchar2 in 用户密码
* (3)p_isSuccessful: number out 登陆是否成功标志
* (4)p_errorMsg: varchar2 out 登陆失败信息(登陆成功时为空串)
************************************************************************************
*/
create or replace procedure loginCheck(
p_userId in varchar2,
p_password in varchar2,
p_isSuccessful out number,
p_errorMsg out varchar2
)
as
v_password varchar2(18);
begin
select password into v_password from users where id=p_userId;
if (SQL%NOTFOUND) then
p_isSuccessful := 0;
p_errorMsg := '用户名错误，请重新输入后再进行登陆。';
return;
else
if (v_password <> p_password) then
p_isSuccessful := 0;
p_errorMsg := '密码错误，请重新输入后再进行登陆。';
return;
else
p_isSuccessful := 1;
p_errorMsg := ' ';
end if;
end if;
exception
when no_data_found then
p_isSuccessful := 0;
p_errorMsg := '用户名错误，请重新输入后再进行登陆。';
return;
when others then
p_isSuccessful := 0;
p_errorMsg := '发生未知错误，无法登陆。';
return;
end loginCheck;
/
/* 2.user.register
************************************************************************************
* 过程:
* register(
* p_userId in varchar2,
* p_password in varchar2,
* p_isSuccessful out number,
* p_errorMsg out varchar2
* );
* 作用:
* 用户注册;
* 参数:
* (1)p_userId: varchar2 in 用户ID
* (2)p_password: varchar2 in 用户密码
* (3)p_isSuccessful: number out 注册是否成功标志
* (4)p_errorMsg: varchar2 out 注册失败信息(注册成功时为空串)
************************************************************************************
*/
create or replace procedure register(
p_userId in varchar2,
p_password in varchar2,
p_isSuccessful out number,
p_errorMsg out varchar2
)
as
v_count number(1) := 0;
begin
select count(*) into v_count
from users
where (id=p_userId) and (password=p_password);
if (v_count>0) then
p_isSuccessful := 0;
p_errorMsg := '用户名已经被使用。';
else
insert into users
values(p_userId, p_password, ' ', '男', 0, ' ');
if (SQL%ROWCOUNT=1) then
p_isSuccessful := 1;
p_errorMsg := ' ';
commit;
else
p_isSuccessful := 0;
p_errorMsg := '无法向数据库中插入该用户。';
rollback;
end if;
end if;
end register;
/
/* 3.user.updateInfo
************************************************************************************
* 过程:
* updateInfo(
* p_userId in varchar2,
* p_password in varchar2,
* p_name in varchar2,
* p_gender in char,
* p_note 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_gender: char in 用户性别
* (5)p_note: varchar2 in 用户备注信息
* (6)p_isSuccessful: number out 更新是否成功标志
* (7)p_errorMsg: varchar2 out 更新失败信息(更新成功时为空串)
************************************************************************************
*/
create or replace procedure updateInfo(
p_userId in varchar2,
p_password in varchar2,
p_name in varchar2,
p_gender in char,
p_note in varchar2,
p_isSuccessful out number,
p_errorMsg out varchar2
)
as
begin
if (loginValidate(p_userId, p_password)) then
update users
set name=p_name, gender=p_gender,note=p_note
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 updateInfo;
/
/* 4.user.updatePassword
************************************************************************************
* 过程:
* updatePassword(
* p_userId in varchar2,
* p_oldpassword in varchar2,
* p_newpassword in varchar2,
* p_isSuccessful out number,
* p_errorMsg out varchar2
* );
* 作用:
* 修改用户密码;
* 参数:
* (1)p_userId: varchar2 in 用户ID
* (2)p_oldpassword: varchar2 in 用户旧密码
* (3)p_newpassword: varchar2 in 用户新密码
* (4)p_isSuccessful: number out 修改是否成功标志
* (5)p_errorMsg: varchar2 out 修改失败信息(修改成功时为空串)
************************************************************************************
*/
create or replace procedure updatePassword(
p_userId in varchar2,
p_oldpassword in varchar2,
p_newpassword in varchar2,
p_isSuccessful out number,
p_errorMsg out varchar2
)
as
begin
if (loginValidate(p_userId, p_oldpassword)) then
update users
set password=p_newpassword
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 updatePassword;
/
/* 5.user.deposit
************************************************************************************
* 过程:
* deposit(
* p_userId in varchar2,
* p_balance in number,
* p_isSuccessful out number,
* p_errorMsg out varchar2
* );
* 作用:
* 用户充值;
* 参数:
* (1)p_userId: varchar2 in 用户ID
* (2)p_balance: number in 用户充值金额
* (3)p_isSuccessful: number out 充值是否成功标志
* (4)p_errorMsg: varchar2 out 充值失败信息(充值成功时为空串)
************************************************************************************
*/
create or replace procedure deposit(
p_userId in varchar2,
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,
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -