stored procedures, storage functions (Oracle)

stored procedures and stored functionsThat is stored in the database for all user programs to call. Sub-ProgramCalled stored procedures, stored functions. What are the differences between stored procedures and stored functions? Store function: You can return a function value through a return statement. Stored procedures: No other than that we can think of them as exactly the same. Stored Procedures 1. Create a stored procedureUse the CREATE PROCEDURE command to resume the stored procedure. Syntax:create [or Replace] procedure procedure name (parameter list) as Plsql subroutine body; Print Hello Word

-- Print Hello world Create or Replace procedure SayHelloWorld  as   -- Description Section begin    dbms_output.put_line ('helloworld'); End ; /

After compiling:

2. Call the stored procedure method:1. Exec process Name 2, begin process name; process name; end; /Test Call stored Procedure

--connecting to a databaseC:\WINDOWS\system32>Sqlplus Scott/Tiger@192.168.56.101:1521/Orclsql>--Call mode oneSql> SetServeroutput onSQL> execSayhelloworld;hello WORLDPL/The SQL process has completed successfully. SQL> --Call mode two:Sql> begin  2SayHelloWorld (); 3SayHelloWorld (); 4  End; 5  /Hello Worldhello worldpl/The SQL process has completed successfully.

Stored procedure with parameters:

--pay up to 100 for a given employee and print a pre-and post-rise salaryCreate or Replace procedureRaisesalary (Enoinch  Number)--In as input parameter as   --Description SectionPsal emp.sal%type;begin  --get a pre-rise salary  SelectSal intoPsal fromEmpwhereEmpno=Eno; UpdateEmpSetSal=Sal+ - whereEmpno=Eno; --do you want a commit?   --to ensure that in the same transaction, commit is called by who commits theDbms_output.put_line ('before the rise:'||Psal||'after the rise:'||(psal+ -));End;/

Test:

Storage FunctionsA 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. storage function Syntax:Create[or Replace] functiion function name (argument list) return function value type as Plsql subroutine body; query Employee annual income

--Query The annual income of an employeeCreate or Replace functionQueryempincome (Enoinch  Number)return  Number as  --Monthly salary and bonusPsal emp.sal%type; Pcomm Emp.comm%type;begin  SelectSal,comm intoPsal,pcomm fromEmpwhereEmpno=Eno; --Return Annual income  returnPsal* A+NVL (Pcomm,0);End;/

Test:

in and out of procedures and functionsIn 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, and we can use out parameters to implement the return of multiple values in procedures and functions. When do I use a stored procedure/storage function? Principles(not required): if there is only one return value, the stored function is used, otherwise the stored procedure is used. Stored Procedures

Create or Replace procedureQueryempinfo (Enoinch  Number, PName outvarchar2, Psal out Number, Pjob outvarchar2) as begin  SelectEname,sal,empjob intoPname,psal,pjob fromEmpwhereEmpno=Eno;End;

Test

Calling a stored procedure using a Java program

/** Stored procedure * Create or Replace procedure Queryempinfo (Eno in number, * PE Name out VARCHAR2, * psal out number, * PJ OB out VARCHAR2)*/@Test Public voidTestProcedure () {//{call <procedure-name>[(<ARG1>,<ARG2>,. ...)]}String sql = "{call Queryempinfo (?,?,?,?)}"; CallableStatement Call=NULL; Connection Connection=jdbcutils.getconnection (); Try{ Call=connection.preparecall (SQL); //for the in parameter, assign the valueCall.setint (1, 7839); //for Out parameters, declareCall.registeroutparameter (2, Oracletypes.varchar); Call.registeroutparameter (3, Oracletypes.number); Call.registeroutparameter (4, Oracletypes.varchar); //ExecutionCall.execute (); //Remove ResultsString name = call.getstring (2); DoubleSal = call.getdouble (3); String Job= Call.getstring (4); SYSTEM.OUT.PRINTLN (Name+ "\ T" + sal + "\ T" +job); } Catch(SQLException e) {e.printstacktrace (); }finally{jdbcutils.release (connection, call,NULL); }    }

Calling a stored function using a Java program

/** Save function * Create or Replace function Queryempincome (Eno in number) return number*/@Test Public voidtestfunction () {//{? = call <procedure-name>[(<ARG1>,<ARG2>,. ...)]}String sql = "{? =call queryempincome (?)}"; Connection Conn=NULL; CallableStatement Call=NULL; Try{conn=jdbcutils.getconnection (); Pager=conn.preparecall (SQL); //for Out parameters, assign a valueCall.registeroutparameter (1, Oracletypes.number); //for the in parameter, assign the valueCall.setint (2, 7839); //ExecutionCall.execute (); //Remove Data           DoubleIncome = call.getdouble (1);       System.out.println (income); } Catch(Exception e) {e.printstacktrace (); } finally{jdbcutils.release (conn, call,NULL); }    }

using cursors in Out parametersQuestion: Query All information about all employees in a department 1, declare package structure

CREATE OR REPLACE as is cursor;       -- Create stored procedure, output parameter is custom type  procedure inch  Number , emplist out empcursor); END MyPackage;

2. Create the package body (Implementation)

CREATE OR REPLACEPackage BODY MyPackage as  procedureQueryemplist (DNOinch  Number, emplist out Empcursor) as  BEGIN    --Implement    OpenEmplist for Select *  fromEmpwhereDeptno=DNO; ENDqueryemplist;ENDMyPackage;

Calling a stored procedure with a package using Java

 Public voidtestcursor () {//{call <procedure-name>[(<ARG1>,<ARG2>,. ...)]}String SQL= "{call Mypackage.queryemplist (?,?)}"; Connection Conn=NULL; CallableStatement Call=NULL; ResultSet RS=NULL; Try{conn=jdbcutils.getconnection (); Pager=conn.preparecall (SQL); //for an in parameter, assign a value?Call.setint (1, 20); //for Out parameters, assign a valueCall.registeroutparameter (2, Oracletypes.cursor); //ExecutionCall.execute (); //Remove Resultsrs = ((oraclecallablestatement) call). GetCursor (2);  while(Rs.next ()) {String name= Rs.getstring ("ename"); DoubleSal = rs.getdouble ("Sal"); SYSTEM.OUT.PRINTLN (Name+ "\ T" +Sal); }       } Catch(Exception e) {e.printstacktrace (); } finally{jdbcutils.release (conn, call, RS); }    }

This case cursor is not closed because the cursor is close when the resultset is closed

stored procedures, storage functions (Oracle)