Simple oracle Stored Procedure

1. oracle stored procedures and asp. NET calls stored procedures to implement a simple login function, just as learning, so the processing may not be reasonable. ① Write the stored procedure create Procedures in PLSQLDeveloper. A simple logon check Stored Procedure Code is as follows: createorreplaceprocedureuser_login (s

1. oracle stored procedures and asp. NET calls stored procedures to implement a simple login function, just as learning, so the processing may not be reasonable. ① Write the stored procedure create Procedures in PL/SQLDeveloper. A simple logon check Stored Procedure Code is as follows: createorreplaceprocedureuser_login (s

1. oracle stored procedures and asp. NET calls stored procedures to implement a simple login function, just as learning, so the processing may not be reasonable.

① Compile the stored procedure in PL/SQL Developer

Create Procedures. A simple logon check Stored Procedure Code is as follows:

Create or replace procedure user_login (sname in nvarchar2, spassword in nvarchar2, ReturnValue out number)

Is -- variable Declaration

Rname nvarchar2 (20 );

Rpassword nvarchar2 (50 );

Begin

ReturnValue: = 0; -- assign the Initial Value

Select username into Rname from userinfo where username = sname;

If Rname is not null then

ReturnValue: = 1; -- the user name exists

Select userpassword into Rpassword from userinfo where username = sname and userpassword = spassword;

If Rpassword is not null then

ReturnValue: = 2; -- login successful

Else ReturnValue: = 1; -- the user name exists and the password is incorrect.

Return;

End if;

Else ReturnValue: = 0; -- the user does not exist

End if;

Exception

When no_data_found then

DBMS_OUTPUT.put_line ('no data found ');

End;

② Call the stored procedure in the SQL window of PL/SQL Developer

Declare -- variable Declaration

ReturnValue number (10 );

Begin

User_login ('faith ', 'faith', ReturnValue );

If ReturnValue = 0 then

DBMS_OUTPUT.put_line ('the user does not exist ');

Elsif ReturnValue = 1 then

DBMS_OUTPUT.put_line ('the user exists, but the password is incorrect ');

Else

DBMS_OUTPUT.put_line ('login successful !!! ');

End if;

End;

③ Call the stored procedure in VS2012

In the OracleHelper. cs file:

Public static void iRunProc (string procName, OracleParameter [] paras)

{

OracleConnection conn = new OracleConnection (OracleHelper. strConn );

If (conn. State = ConnectionState. Closed)

{

Conn. Open ();

}

OracleCommand cmd = new OracleCommand (procName, conn );

Cmd. CommandType = CommandType. StoredProcedure;

If (paras! = Null)

{

For (int I = 0; I <paras. Length; I ++)

{

OracleParameter parameter = paras [I];

Cmd. Parameters. Add (parameter );

}

}

Int result = cmd. ExecuteNonQuery ();

Cmd. ExecuteNonQuery ();

}

In the webtestDal. cs file of the DAL Layer

Public static int loginByProc (string name, string password, string procName)

{

OracleParameter [] parameters = {

New OracleParameter ("sname", OracleType. NVarChar, 20 ),

New OracleParameter ("spassword", OracleType. NVarChar, 50 ),

New OracleParameter ("ReturnValue", OracleType. Number, 10)

};

Parameters [0]. Value = name;

Parameters [1]. Value = password;

// Parameters [2]. Value = 0;

Parameters [0]. Direction = ParameterDirection. Input;

Parameters [1]. Direction = ParameterDirection. Input;

Parameters [2]. Direction = ParameterDirection. Output;

Try

{

OracleHelper. iRunProc (procName, parameters );

Int I = int. Parse (parameters [2]. Value. ToString ());

Return I;

}

Catch (Exception e)

{

Throw e;

}

}

In the webtestBll. cs file of The BLL Layer

Public static int loginByProc (string name, string password, string procName)

{

Return webtestDal. loginByProc (name, password, procName );

}

In the logon button event of the page code

Protected void btnSubmit_Click (object sender, EventArgs e)
{
String name = this.txt Name. Text. Trim ();
String password = this.txt Password. Text. Trim ();
// Call the stored procedure to determine whether the logon is successful
If (webtestBll. loginByProc (name, password, "user_login") = 2) // The Stored Procedure output 2 indicates that the logon is successful.
{// Log on successfully. Save the cookie of the user name and password.
HttpCookie mycookie = new HttpCookie ("loginCookie ");
Mycookie. Values. Add ("cname", name );
Mycookie. Values. Add ("cpassword", password );
Mycookie. Expires = DateTime. Now. AddHours (4); // cookie Life Cycle
Response. AppendCookie (mycookie );
Response. Redirect ("userinfo. aspx ");
}
Else if (webtestBll. loginByProc (name, password, "user_login") = 1)
{
// Response. Write ("