Shulou(Shulou.com)06/02 Report--

In this issue, the editor will bring you about how to create stored procedures in Oracle. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

1. JAVA calls Oracle stored procedure

The stored procedure that JAVA calls Oracle is most commonly used between JAVA and Oracle. The following is a brief description of how JAVA calls Oracle stored procedures.

Ⅰ, without output parameters

The procedure name is pro1, the number of parameters is 1, and the data type is shaping data.

Import java.sql. *; public class ProcedureNoArgs {public static void main (String args []) throws Exception {/ / load Oracle driver DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver ()); / / get Oracle database connection Connection conn = DriverManager.getConnection ("jdbc:oracle:thin:@MyDbComputerNameOrIP:1521:ORCL", sUsr, sPwd ") / / create the object of the stored procedure CallableStatement c = conn.divpareCall ("{call pro1}"); / / set the parameter of the Oracle stored procedure to 188 c.setInt (1,188); / / execute the Oracle stored procedure c.execute (); conn.close ();}}

Ⅱ, case with output parameters

The procedure name is pro2, the number of parameters is 2, the data type is shaping data, and the return value is shaping type.

Import java.sql.*;public class ProcedureWithArgs {public static void main (String args []) throws Exception {/ / load Oracle driver DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver ()); / / get Oracle database connection Connection conn = DriverManager.getConnection ("jdbc:oracle:thin:@MyDbComputerNameOrIP:1521:ORCL", sUsr, sPwd ") / / create the object of the Oracle stored procedure, call the stored procedure CallableStatement c=conn.divpareCall ("{call pro2}"); / / set the value of the first parameter to 188c.setInt (1188) for the parameter of the Oracle stored procedure; / / register the second parameter of the stored procedure, c.registerOutParameter (2java.sql.Types.integer) / / execute the Oracle stored procedure c.execute (); / / get the output parameter values of the stored procedure and print out System.out.println (c.getInt (2); conn.close ();}}

The Oracle stored procedure consists of three parts: procedure declaration, execution procedure, and stored procedure exception.

Oracle stored procedures can have no-parameter stored procedures and stored procedures with parameters.

1. No-parameter procedure syntax

Create or replace procedure NoParPro

As...

Begin

……

Exception / / stored procedure exception

……

End

2. an example of a stored procedure with parameters

Create or replace procedure queryempname (sfindno emp.empno%type) as

SName emp.ename%type

Sjob emp.job%type

Begin

....

Exception

....

End

Third, stored procedures with parameters include assignment methods

Create or replace procedure runbyparmeters (isal in emp.sal%type

Sname out varchar,sjob in out varchar)

As icount number

Begin

Select count (*) into icount from emp where sal > isal and job=sjob

If icount=1 then

....

Else

....

End if

Exception

When too_many_rows then

DBMS_OUTPUT.PUT_LINE ('return value more than 1 row')

When others then

DBMS_OUTPUT.PUT_LINE ('error during RUNBYPARMETERS!')

End

Fourth, the call to stored procedure in Oracle

Procedure call mode 1

Declare

Realsal emp.sal%type

Realname varchar (40)

Realjob varchar (40)

Begin / / stored procedure call starts

Realsal:=1100

Realname:=''

Realjob:='CLERK'

Runbyparmeters (realsal,realname,realjob);-must be in order

DBMS_OUTPUT.PUT_LINE (REALNAME | |''| | REALJOB)

END; / / procedure call ends

Procedure call mode 2

Declare

Realsal emp.sal%type

Realname varchar (40)

Realjob varchar (40)

Begin / / procedure call starts

Realsal:=1100

Realname:=''

Realjob:='CLERK'

Runbyparmeters (sname= > realname,isal= > realsal,sjob= > realjob);-- variable order of corresponding variables for specified values

DBMS_OUTPUT.PUT_LINE (REALNAME | |''| | REALJOB)

END; / / procedure call ends

This is how to create stored procedures in the Oracle shared by the editor. If you happen to have similar doubts, please refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.