Oracle Learning (12): Stored procedure/Storage functions

1. Knowledge points

--First stored procedure/* Print Hello worldcreate [or replace] PROCEDURE procedure name (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;/-------------------------------         ---------------/* Pay up to 100 for the specified employee and print the pre-and post-rise pay create [or replace] PROCEDURE procedure name (list)--in for input parameters, out for output parameters 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: No, because the whole business is not over yet. After calling this stored procedure, 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 (list of references) 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 number/* 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 packageDefines a self-defined type: The Empcursor type is a cursor, a stored procedure: queryempcreate OR REPLACE Package mypackage as type empcursor is ref CURSOR;--EMPCU Rsor is a self-defining type: A type that references the cursor as a 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: Precompiled SQL statement. When running, tell the number of parameters. Edit at least once * CallableStatement: The call to the stored procedure/store function is complete, there is no compilation process, call */public class Testoracle {/* * Create or Replaceprocedure Queryem                                         PInfo (Eno in number, pename out VARCHAR2, Psal out number, pjob out varchar2) */@Testpublic void TestProcedure () {//Call stored over Cheng//{call <procedure-name>[(<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. Affirming Call.registeroutparameter (2, Oracletypes.varchar); Call.registeroutparameter (3, Oracletypes.number); Call.registeroutparameter (4, Oracletypes.varchar);//Call Call.execute ();//Fetch 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 sample//{?
= 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);//Run 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? Is When the structure set is off, the cursor is switched off.
* 2. Can I run it 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);//Run 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;} /* * Run 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: Can it be enough to intervene in garbage collection through code? }}if (stmt!=null) {try {stmt.close ();} catch (SQLException e) {e.printstacktrace ();} finally{rs=null;//garbage Collection: Can you pass the codeInterfering with garbage collection? }}if (conn!=null) {try {conn.close ();} catch (SQLException e) {e.printstacktrace ();} finally{rs=null;//garbage Collection: Can it be enough to intervene in garbage collection through code? }}}}

Oracle Learning (12): Stored procedure/Storage functions