stored procedures and stored functions for Oracle Learning Notes

Description of stored procedure and stored function: Store function has return value! The stored procedure does not return a value!

A subroutine stored in a database that is called by all user programs is called a stored procedure, stored function.
When to use stored procedures/storage functions
Principle: If there is only one return value, use a stored function, otherwise, use a stored procedure.

1. Create a stored procedure

Create a stored procedure with the CREATE PROCEDURE command. The syntax is as follows:

create [or replace] PROCEDURE procedure name [(parameter list)]

As

Variable declaration

Plsql Sub-program body;

1) Getting Started with stored procedures:

Create or Replaceprocedure sayhelloasbegin  dbms_output.put_line (' Hello world! '); End

cmd command line execution program:

2) Raise wages for employees

Create or Replaceprocedure addsal (Eno in number) Asbegin  update emp2 set sal=sal*1.1 where Empno=eno;  Dbms_output.put_line (' pay up '); end;

3) stored procedure with output results

Create or Replaceprocedure queryempsal (Eno in Number,pname out varchar2,psal out number) asbegin  select ename, Sal int o pname,psal from EMP2 where empno=eno;end;

Test the above code:

We can tell by saying "disconnect from database Scott" that the database has shut down properly-so the database has helped us commit the transaction automatically!

2. Create a storage function

function ( 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.

The syntax is as follows:

Create[or REPLACE] Function name (argument list)

RETURN function Value type

As

Variable declaration

Plsql Sub-program body;

1) Getting Started with storage functions:

Create or Replacefunction querysal (Eno in number) return number as Vsal emp2.sal%type;begin  Select Sal to Vsal from E MP2 where Empno=eno;  return vsal;end;

Test the above code:

2) storage function with output parameters

Create or Replacefunction queryfuncempsal (Eno in Number,pname out varchar2,psal out number) return Numberasbegin  Sele CT ename, sal into Pname,psal from EMP2 where Empno=eno;  return 1;end;

================================================================================================

Calling stored procedures and stored functions through JDBC

1) Build the environment

Importing Oracle's JAR package: E:\app\Administrator\product\11.2.0\dbhome_1\jdbc\lib

2) Write Tool class Jdbcutils

Package Cn.itcast.util;import Java.sql.connection;import Java.sql.drivermanager;import java.sql.ResultSet;import Java.sql.sqlexception;import Java.sql.statement;public class Jdbcutils {private static String jdbcurl= "Jdbc:oracle: Thin: @localhost: 1521:ORCL ";p rivate static string driverclass=" Oracle.jdbc.OracleDriver ";p rivate static string Username= "Scott";p rivate static String password= "169500";/** * Get database connection * */public static Connection getconnection () {try { Class.forName (Driverclass);//Register Drive return drivermanager.getconnection (jdbcurl, username, password);} catch (Exception e) {throw new RuntimeException ();}} /** * Close Resource * Public */public static void Closeresource (Connection conn,statement state,resultset rs) {if (conn!=null) {try {C Onn.close ();} catch (SQLException e) {e.printstacktrace ();} Finally{conn=null;}} if (state!=null) {try {state.close ();} catch (SQLException e) {e.printstacktrace ();} Finally{state=null;}} if (rs!=null) {try {rs.close ();} catch (SQLException e) {e.printstacktrace ();} Finally{rS=null;}}}} 

2) test the stored procedure:

Package Cn.itcast.test;import Java.sql.callablestatement;import Java.sql.connection;import Oracle.jdbc.internal.oracletypes;import Org.junit.test;import Cn.itcast.util.jdbcutils;public class TestProcedure { Connection Con=null; CallableStatement Call=null; @Testpublic void Test () throws Exception{string sql= "{call Addsal (?)}"; Con=jdbcutils.getconnection (); Call=con.preparecall (SQL); Call.setobject (1, 7369);//Set parameter Call.execute ();//execute// Show result Jdbcutils.closeresource (con, call, null);} @Testpublic void Test1 () throws Exception{string sql= "{call Queryempsal (?,?,?)}"; Con=jdbcutils.getconnection (); Call=con.preparecall (sql);//Set Input parameters Call.setobject (1, 7369);// Set Output Parameters Call.registeroutparameter (2, Oracletypes.varchar); Call.registeroutparameter (3, Oracletypes.number); Call.execute ();//execute//display result String name = (string) call.getobject (2);D ouble value = call.getdouble (3); System.out.println (name+ ":" +value); Jdbcutils.closeresource (con, call, null);}}

Unit Test test1 ()

3) test the storage function--about the SQL notation to the JDK

Package Cn.itcast.test;import Java.sql.callablestatement;import Java.sql.connection;import java.sql.SQLException; Import Org.junit.test;import Oracle.jdbc.internal.oracletypes;import Cn.itcast.util.jdbcutils;public class testfunction {@Testpublic void Test () throws Exception{connection Conn=null; CallableStatement Call=null; String sql= "{? =call querysal (?)}"; Conn=jdbcutils.getconnection (); Call=conn.preparecall (SQL); Call.setobject (2,7934); Call.registeroutparameter (1, Oracletypes.number);//Execution result Call.execute ();//Gets the return result of the Object object = Call.getobject (1); System.out.println (object);}}

Note: The syntax for SQL goes to the JDK:

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

stored procedures and stored functions for Oracle Learning Notes