Oracle query Stored Procedure Call example

Stored Procedure:

Create or replace procedure sp_test
(
Indthm in varchar,
Outfzr out varchar,
Outsjhm out varchar
)
Is

Begin
/* Obtain the first record of the returned result */
Select fzr, sjhm into outfzr, outsjhm from police where DTHM = indthm and rownum = 1;
End;

Procedure Call method:

I. SQL * Plus:

SQL> var A varchar2 (20 );
SQL> var B varchar2 (20 );
SQL> exec sp_test ('w2400001876 ',: A,: B );

PL/SQL procedure successfully completed
A
---------
Zhang San
B
---------
013688888888

 

Ii. PL/SQL:

 

SQL window
Declare fzr varchar2 (20 );
Sjhm varchar2 (20 );
Begin
Sp_test ('w243851876 ', fzr, sjhm );
Dbms_output.put_line (fzr );
Dbms_output.put_line (sjhm );
End;

Command window
SQL> var A varchar2 (20 );
SQL> var B varchar2 (20 );
SQL> exec sp_test ('w2400001876 ',: A,: B );

PL/SQL procedure successfully completed
A
---------
Zhang San
B
---------
013688888888

Iii. vb ado:

 

Gdbname = trim (txtdbname. Text)
Gdbuser = trim (txtuserid. Text)
Gdbpass = trim (txtpassid. Text)
Set adoconn = new ADODB. Connection
Adoconn. Open "provider = oraoledb. Oracle; Password =" + gdbpass + "; user id =" + gdbuser + "; Data Source =" + gdbname

If adoconn. State <> adstateopen then
Msgbox "database connection failed. Please reset the parameter! ", Vbexclamation," system prompt"
Exit sub
End if

Set adocommand = new ADODB. Command
Set adocommand. activeconnection = adoconn
Adocommand. commandtext = "sp_test"
Adocommand. commandtype = adw.storedproc

Adocommand. Parameters. append adocommand. createparameter ("indthm", advarchar, adparaminput, 20, trim (txtdthm. Text ))
Adocommand. Parameters. append adocommand. createparameter ("outfzr", advarchar, adparamoutput, 20)
Adocommand. Parameters. append adocommand. createparameter ("outsjhm", advarchar, adparamoutput, 20)

Adocommand. Execute
Txtfzr. Text = CSTR (adocommand ("outfzr "))
Txtsjhm. Text = CSTR (adocommand ("outsjhm "))