Oracle Learning (12): stored procedure/storage function

1. Knowledge Point

-- The First stored PROCEDURE/* prints Hello Worldcreate [or replace] PROCEDURE process name (parameter list) as plsql subroutine body; Calls stored PROCEDURE: 1. exec sayHelloWorld (); 2. begin sayHelloWorld (); end;/*/create or replace procedure sayHelloWorld <span style = "white-space: pre "> </span> -- sayHelloWorld is the process name as -- declare -- variable description begin dbms_output.put_line ('Hello World'); end; /------------------------------------------/* increase the salary of the specified employee by 100, and print the pre-and post-raise salary create [Or replace] PROCEDURE process name (parameter list) -- in is the input parameter, out is the output parameter as plsql subroutine; SQL> begin 2 raiseSalary (7839); 3 raiseSalary (7566 ); 4 commit; 5 end; 6/before: 7986 after: 8086 before: 5024.53 after: 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 -- select sal into psal from emp where empno = eno; -- update emp set sal = sal + 100 where empno = eno; -- problem: do you want to commit ?? A: No, because the entire transaction is not over yet. Wait until the stored procedure is called and commit -- print dbms_output.put_line ('before upstreaming: '| psal |' After upstreaming: '| (psal + 100); end;/-----------------------------------------------------------------/* query the annual income of an employee. CREATE [or replace] FUNCTION name (parameter list) RETURN function value type ASPLSQL subroutine; */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; /timed OUT parameter/* query the name and monthly salary of an employee */create or replace procedure queryEmpInfo (eno in number, pename out varchar2, psal out number, pjob out varchar2) asbegin select ename, sal, empjob into pename, psal, pjob from emp where empno = eno; end;/---------------------------------------------------------------------- query all information of all employees in a department -- 1. create a package: MYPACKAGE -- 2. define a custom type in this PACKAGE: the empcursor type is cursor, a stored procedure: queryempCREATE or replace package mypackage as type empcursor is ref cursor; -- empcursor is a custom type: reference the cursor type as the empcursor type procedure queryEmpList (dno in number, empList out empcursor); end mypackage; ========================================================== ====== create or replacepackage body mypackage as procedure queryEmpList (dno in number, empList out empcursor) as begin open empList for select * from emp where deptno = dno; END queryEmpList; end mypackage; BEGIN ;------------------------------------------------------------------

2. Test stored procedures/stored functions in JAVA programs

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-compiled SQL Statement. During execution, the parameter is told to be edited at least once * CallableStatement: call the stored procedure/stored function directly */public class TestOracle {/** create or r Eplaceprocedure queryEmpInfo (eno in number, pename out varchar2, psal out number, pjob out varchar2) * // @ Testpublic void testProcedure () {// call the Stored procedure // {call <procedure-name> [(<arg1>, <arg2>,...)]} string SQL = "{call queryEmpInfo (?,?,?,?)} "; Connection conn = null; CallableStatement call = null; try {conn = JDBCUtils. getConnection (); call = conn. prepareCall (SQL); // assign a value to call. setInt (1, 7839); // For the out parameter, declare call. registerOutParameter (2, OracleTypes. VARCHAR); call. registerOutParameter (3, OracleTypes. NUMBER); call. registerOutParameter (4, OracleTypes. VARCHAR); // call call.exe cute (); // retrieve the result String name = call. getString (2); double sal = call. getDouble (3); String j Ob = 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.exe cute (); // retrieve the annual income double income = call. getDouble (1); System. out. println (income);} catch (Exception e) {e. printStackTrace ();} finally {JDBCUtils. release (conn, call, null) ;}/ ** problem: * 1. is the cursor locked?: Yes. After the structure set is switched off, the cursor is switched off. * 2. Can I execute 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); // execute call.exe cute (); // retrieve the set rs = (OracleCallableStatement) call ). getCursor (2); while (rs. next () {String name = rs. getString ("ename"); String job = rs. getString ("job"); System. out. the position of println (name + "is" + job);} catch (Exception e) {e. printStackTrace ();} finally {JDBCUtils. release (conn, call, rs );}}}

3. JDBC classes 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 "; private static String url =" jdbc: oracle: thin: @ localhost: 1521: orcl "; private static String user =" scott "; private 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: Can I intervene in garbage collection through code ?}} If (stmt! = Null) {try {stmt. close ();} catch (SQLException e) {e. printStackTrace () ;}finally {rs = null; // garbage collection: Can I intervene in garbage collection through code ?}} If (conn! = Null) {try {conn. close ();} catch (SQLException e) {e. printStackTrace () ;}finally {rs = null; // garbage collection: Can I intervene in garbage collection through code ?}}}}