Oracle stored procedures, functions, and packages

To create a stored procedure:Syntax: Create [or replace] PROCEDURE procedure name (parameter list)

As

Plsql Sub-program body;

How to call stored procedures in two ways
1. Execute (EXEC)------exec function name ()
2. Begin
Function name ()
End-------begin function name () end

A function is a named stored program that can take parameters and return a computed value. Functions and procedures are similar in structure, but must have a return clause that returns the value of the function. The function description specifies the function name, the type of the result value, and the type of the parameter. to establish the syntax for a stored function:

CREATE [OR REPLACE] Function name (parameter list)

RETURN function Value type

As

Plsql Sub-program body;

In general, the difference between a procedure and a function is that a function can have a return value, whereas a procedure does not return a value.
However, procedures and functions can specify one or more output parameters through out.
We can use the out parameter to implement the return of multiple values in procedures and functions.

If there is only one return value, the stored function is used, otherwise the stored procedure is used.
Commit and rollback operations are not typically done in stored procedures and stored functions

If a stored procedure contains many return values that need to be written out in the stored procedure, the comparison is inconvenient, so you can have a pointer in the stored procedure to process it, that is, the package

1 --Stored Procedures2 Create or Replace procedureGetempinfo (EnoinchTestemp.empno%Type--input value in can be omitted3EmpName outvarchar,--output value out must be, only write return type, no write precision required4Empjob outvarchar,5Empsal out Number6 )7  as8 begin9   SelectEname,job,sal intoEmpname,empjob,empsal fromTestempwhereEmpno=Eno;Ten End; One  A --function -  - Create or Replace functionGetsals (EnoinchTestemp.empno%type) the returnTestemp.sal%Type--Defining return Types -  as -Psal testemp.sal%type; -Pcomm Testemp.comm%type; + begin -   SelectSal,comm intoPsal,pcomm fromTestempwhereEmpno=Eno; +   return(psal* A+NVL (Pcomm,0));--return value A End; at  -  - --Create a package - Create or ReplacePackage MyPackage as--MyPackage Custom Package name -Type Empcursor isRefcursor;--declaring a custom cursor type - procedureQueryemp (DtnoinchTestemp.deptno%type, inEmpinfo out Empcursor);--Stored Procedures - EndMyPackage;--End Header Definition to  + Create or Replace --Create a package body -Package Body MyPackage as the procedureQueryemp (DtnoinchTestemp.deptno%Type,empinfo out Empcursor) as *   begin $     OpenEmpinfo for Select *  fromTestempwhereDeptno=Dtno;--put the queried data into the cursorPanax Notoginseng   Endqueryemp; - EndMyPackage;

Calling a stored procedure in Java, the function

1. Guide Package

Under the Oracle installation directory Product10.2.0\db_1\jdbc\lib directory. Default in C:\oracle\product\10.2.0\db_1\jdbc\lib

2.

Java Connection to Oracle driver Private Static String  driver= "Oracle.jdbc.OracleDriver";     Private Static String url= "JDBC:ORACLE:THIN:@192.168.250.144:1521:ORCL";     Private Static String user= "Scott";     Private Static String password= "Tiger";

3.

1 call a stored procedure2String sql= "{call Getempinfo (?,?,?,?)}";//calling a stored procedure SQL statement3Connection conn=NULL;4CallableStatement callstatement=NULL;5         Try {6conn=oracleutils.getconnection ();7Callstatement=conn.preparecall (SQL);8Callstatement.setint (1, 7839);//setting input Values9Callstatement.registeroutparameter (2,oracletypes.varchar);//Setting output ValuesTenCallstatement.registeroutparameter (3, Oracletypes.varchar); OneCallstatement.registeroutparameter (4, oracletypes.number); A              -Callstatement.execute ();//Executing stored procedures -              theString name=callstatement.getstring (2);//Get output value -String job=callstatement.getstring (3); -             DoubleSal=callstatement.getdouble (4); -              +System.err.println ("name=" +name); -System.err.println ("job=" +job); +System.err.println ("sal=" +sal); A              at}Catch(Exception e) { - e.printstacktrace (); -}finally{ -Oracleutils.release (conn, callstatement,NULL); -}

1 calling Functions2String sql= "{? = Call Getsals (?)}";3Connection conn=NULL;4CallableStatement call=NULL;5         Try {6conn=oracleutils.getconnection ();7Call=conn.preparecall (SQL);8Call.registeroutparameter (1, oracletypes.number);9Call.setint (2, 7839);Ten              One Call.execute (); A              -             DoubleAllsal=call.getdouble (1); -              the System.err.println (allsal); -              -}Catch(Exception e) { - e.printstacktrace (); +}finally{ -Oracleutils.release (conn, call,NULL); +}

1 call with the cursor return is worth the packet2String sql= "{call Mypackage.queryemp (?,?)}";3Connection conn=NULL;4CallableStatement call=NULL;5ResultSet rs=NULL;6         Try {7conn=oracleutils.getconnection ();8Call=conn.preparecall (SQL);9Call.setint (1, 30);TenCall.registeroutparameter (2, oracletypes.cursor); One              A Call.execute (); -              -Rs= ((oraclecallablestatement) call). GetCursor (2);//Gets the result set of the cursor value the              while(Rs.next ())//Loop Cursor -             { -String name=rs.getstring ("ename"); -String job=rs.getstring ("Job"); +System.err.println ("Name:" +name+ "job:" +job); -                  +             } A              at}Catch(Exception e) { - e.printstacktrace (); -}

Oracle stored procedures, functions, and packages