Oracle Learning (12): Stored procedure/Storage functions

1. Knowledge points

--First stored procedure/* Print Hello worldcreate [or replace] PROCEDURE procedure name (parameter list) as Plsql subroutine body; Call stored procedure: 1. exec SayHelloWorld (); 2.      Begin SayHelloWorld ();   SayHelloWorld ();   End /*/create or replace procedure Sayhelloworld<span style= "White-space:pre" ></span>-- SayHelloWorld for Procedure name as--declare--variable description begin Dbms_output.put_line (' Hello World '); end;/-------------------------------         ---------------/* Raises 100 of the salary for the specified employee and prints the pre-and post-rise pay create [or replace] PROCEDURE procedure name (parameter list)--in as input parameter, out for output parameter as Plsql subroutine body;  Sql> begin 2 Raisesalary (7839);  3 Raisesalary (7566);  4 commit;  5 end;        6/Up Before: 7986 Rise: 8086 before: 5024.53 up: The 5124.53pl/sql process has been completed successfully. */create or Replace procedure raisesalary (Eno in number) <span style= "White-space:pre" ></span>--    Stored procedure with input parameters as--variable psal emp.sal%type;begin--Get up front pay select Sal into Psal from EMP where Empno=eno;  --Raise the wage update EMP set sal=sal+100 where Empno=eno; --Question: Do you want to commit?? A: Not required, because the entire transaction is not finished, and so on after calling this stored procedure, then commit--print DBMS_output.put_line (' Pre-rise: ' | |  psal| | ' After the rise: ' | |  (psal+100)); end;/-------------------------------------------------------------------/* Query an employee's annual revenue create [OR REPLACE] FUNCTION Function name (argument list) return function value type Asplsql subroutine body; */create or replace function queryempincome (Eno in number) return Number<span style=  "White-space:pre" ></span>as--variable psal emp.sal%type;  Pcomm Emp.comm%type;begin Select Sal,comm into Psal,pcomm from EMP where Empno=eno; Return PSAL*12+NVL (pcomm,0); end;/-----------------------------------------------------------------------out parameter/* Query the name of an employee monthly salary position */create or replace procedure Queryempinfo (Eno in number, pename ou T varchar2, psal out number, pjob out VARCHAR2) Asbegin Select Ename,sal,empjob to Pename,psal,pjob from EMP where empno=eno;end;/------------------------- ---------------------------------------------to query all information about all employees in a department--1. Create a package: MyPackage--2. In the packageA custom type is defined in: Empcursor type is a cursor, a stored procedure: queryempcreate OR REPLACE Package mypackage as type empcursor is ref cursor;--empcur The Sor is a custom type: the type of the cursor is referenced as the type of Empcursor procedure queryemplist (DNO in number,emplist out empcursor); END mypackage;==============================================create OR replacepackage BODY MYPACKAGE as procedure Queryemplist (DNO in number,emplist off empcursor) as BEGIN open emplist for SELECT * from EMP where Deptno=dno      ; END queryemplist; END MyPackage;------------------------------------------------------------------

Test stored procedure/storage function in 2.JAVA program

Package Demo.util;import Java.sql.callablestatement;import Java.sql.connection;import java.sql.ResultSet;import Oracle.jdbc.oraclecallablestatement;import Oracle.jdbc.oracletypes;import org.junit.test;/* * Performance: * Statement < PreparedStatement < CallableStatement * PreparedStatement: Pre-compile SQL statements, tell parameters at execution time, edit at least once * callablestatement: Complete the stored procedure/                                         The call to the stored function, without the compile process, calls directly */public class Testoracle {/* * Create or Replaceprocedure queryempinfo (Eno in number,                                         Pename out VARCHAR2, psal out number, Pjob out VARCHAR2) */@Testpublic void TestProcedure () {//Call stored procedure//{call <procedure-name>[(&lt ; arg1>,<arg2>, ...)]} String sql = "{call Queryempinfo (?,?,?,?)}"; Connection Conn=null; CallableStatement call = Null;try{conn = Jdbcutils.getconnection (); call = Conn.preparecall (SQL);//Assignment Call.setint (1, 7839);//For Out parameters, declare call.registeroutparameter (2, Oracletypes.varchar); Call.regisTeroutparameter (3, Oracletypes.number); Call.registeroutparameter (4, Oracletypes.varchar);//Call Call.execute ();// Take out the result string name = Call.getstring (2);d ouble sal = call.getdouble (3); String job = call.getstring (4); SYSTEM.OUT.PRINTLN (name); System.out.println (SAL); SYSTEM.OUT.PRINTLN (Job);} catch (Exception e) {e.printstacktrace ();} Finally{jdbcutils.release (conn, call, NULL);}}  /* * Create or Replacefunction queryempincome (Eno in number) return number */@Testpublic void TestFunction () {//Storage function Example//{?= Call <procedure-name>[(<arg1>,<arg2>, ....)]} String sql = "{? =call queryempincome (?)}"; Connection conn = null; CallableStatement call = Null;try{conn = Jdbcutils.getconnection (), call = Conn.preparecall (SQL); Call.registeroutparameter (1, Oracletypes.number); Call.setint (2, 7839);//execute Call.execute ();//Take out yearly income double income = Call.getdouble (1); System.out.println (income);} catch (Exception e) {e.printstacktrace ();} Finally{jdbcutils.release (conn, call, NULL);}} /* * Question: * 1. Is the cursor closed? : Yes, when the structure set is off, the cursor is switched off.* 2. Is it possible to execute on MySQL? : Cannot */@Testpublic void Testcursor () {String sql = "{call Mypackage.queryemplist (?,?)}"; Connection conn = null; CallableStatement call = null; ResultSet RS =null;try {conn = jdbcutils.getconnection (); call = Conn.preparecall (sql); Call.setint (1, 10); Call.registeroutparameter (2, oracletypes.cursor);//execute Call.execute ();//Remove Set rs = ((oraclecallablestatement) call). GetCursor (2); while (Rs.next ()) {String name = rs.getstring ("ename"); String job = rs.getstring ("job"); System.out.println (name+ "The position is" +job);}} catch (Exception e) {e.printstacktrace ();} Finally{jdbcutils.release (conn, call, RS);}}}

3. JDBC class used

Package Demo.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 driver= " Oracle.jdbc.OracleDriver ";p rivate static string Url=" Jdbc:oracle:thin: @localhost: 1521:ORCL ";p rivate static string User= "Scott";p rivate static String password= "Tiger"; static{try {class.forname (driver);} catch ( ClassNotFoundException e) {throw new Exceptionininitializererror (e);}} public static Connection getconnection () {try {return drivermanager.getconnection (URL, user, password);} catch ( SQLException e) {e.printstacktrace ();} return null;} /* * Execute Java program * java-xms100m-xmx200m HelloWorld */public static void release (Connection conn,statement stmt,resultset RS {if (rs!=null) {try {rs.close ();} catch (SQLException e) {e.printstacktrace ();} finally{rs=null;//garbage Collection: Is it possible to intervene in garbage collection through code? }}if (stmt!=null) {try {stmt.close ();} catch (SQLException e) {e.printstacktrace ();} finally{rs=null;//Garbage Collection: Whether you canCode Intervention garbage Collection? }}if (conn!=null) {try {conn.close ();} catch (SQLException e) {e.printstacktrace ();} finally{rs=null;//garbage Collection: Is it possible to intervene in garbage collection through code? }}}}