17,078
社区成员
发帖
与我相关
我的任务
分享
create or replace procedure PRC_STAT_LOGIN
(v_username in users.username%TYPE,
v_password in USERS.PASSWORD%TYPE)
IS
begin
declare
iCount1 number;
iCount2 number;
iUserlevel varchar2(20);
--声明游标 查询出每个每个用户的信息
cursor cursor_login is select * from users where username = v_username and password = v_password;
noPassword EXCEPTION;
begin
if not cursor_login%isopen then
open cursor_login;
end if;
select count(*) into iCount1 from users where username = v_username ;
if iCount1 > 0 then
begin
select count(*) into iCount2 from users where username = v_username and password = v_password;
if iCount2 > 0 then--如用户名、密码都正确
select user_level into iUserlevel from users where username = v_username and password = v_password;
if iUserlevel = 'U' then--如用户名、密码都正确,但是级别不够,管理员是A,一般用户是U,那么返回”级别不够”
DBMS_OUTPUT.put_line('Low level!') ;
else --正常登录,返回”成功登陆”
DBMS_OUTPUT.PUT_LINE('Login successfully!');
END IF;
else --如用户名存在,密码错误
RAISE noPassword;
END IF;
end;
else
DBMS_OUTPUT.put_line('The user is not existed!');--如用户名不存在,返回”用户名不存在”
end if;
exception
when NO_DATA_FOUND THEN
DBMS_OUTPUT.put_line('No data found!');
when noPassword THEN --如用户名存在,密码错误,返回”密码错误”
DBMS_OUTPUT.PUT_LINE('PASSWORD ERROR');
close cursor_login;
end;
end PRC_STAT_LOGIN;
private void btnLogin_Click(object sender, EventArgs e)
{
try
{
OracleConnection conn = new OracleConnection("Data Source=ora10;User Id=student;Password=111;");
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = "prc_stat_login";
cmd.CommandType = CommandType.StoredProcedure;
//cmd.Parameters.Add(txtUserName.Text, OracleType.Clob).Direction = ParameterDirection.Output;
//cmd.Parameters.Add(txtPassword.Text, OracleType.Clob).Direction = ParameterDirection.Output;
cmd.Parameters.Add(":username",OracleType.NVarChar, 20).Value = txtUserName.Text;
cmd.Parameters.Add(":password",OracleType.NVarChar, 20).Value = txtPassword.Text;
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
lblError.Text = "successful";
}
catch
{
lblError.Text = "fail";
}
}