"Oracle Learning" 10. Stored procedures and stored functions

Objective
Learning stored procedures is premised on understanding plsql syntax and how to write.
To learn about Plsql, check out the previous summary.

Our following table operations may be based on the following two tables:
We create an employee table and a departmental table:

Employee Information table

CREATE TABLE EMP (
    EMPNO number,
    ename VARCHAR2 (Ten),
    JOB VARCHAR2 (9),
    MGR number,
    hiredate DATE,
    SAL binary_double,
    COMM binary_double,
    DEPTNO number
);

Job is the job, Mgr is the employee's boss Id,sal is wages, Comm is a commission, DEPTNO is the department.

Sql> select * from EMP;
EMPNO ename JOB MGR hiredate SAL COMM DEPTNO
--------------- -------------------- ------------------ --------------- -------------- ----- ----- ---------------
1110 three Directors 1110 December-March-14 5200 0 20
1111 Lee Four sales 1116 March-November-15 3400 500 30
1112 Harry Sales 1116 2 May-April-12 4400 800 30
1113 Zhao Two logistics 1110 30月-May-11 3450 0 40
1114 Li Leilei Accounting 1110 2 February-December-15 2500 0 50
1115 Zhang Shaoli Sales 1110 November-March-16 2400 1400 30
1116 Lin Jian Guo Head 1116 2 February-January-16 5700 0 20
1117 Mafubang Logistics 1116 2 February-July-13 2800 0 40
1118 Shen Yu Accounting 1116 June-May-10 2100 0 50

Department table

CREATE TABLE Dept (
    DEPTNO number,
    dname VARCHAR2)
;

Sql> SELECT * FROM dept t;

DEPTNO dname
--------  --------
20 Management Department
30 Sales Department
40 Logistics Department
50 Financial sector

First, introduce
stored procedures and stored functions
A subroutine stored in a database for all user programs called stored procedures, stored functions.

The difference between a stored procedure and a stored function is that the former has no return value and the latter has a return value.


Second, stored procedures
(1) Create a stored procedure
Create a stored procedure with the CREATE PROCEDURE command.
Grammar:
create [or Replace] procedure procedure name (argument list)
As
Plsql subroutine body;

Simple example of printing HelloWorld

Create or replace procedure SayHelloWorld
as
begin
  Dbms_output.put_line (' helloworld! ');
End;
/

Use Plsql-developer to perform results:

(2) calling a stored procedure
1.exec SayHelloWorld ();
Use this statement using the command console, resulting in:


2. Calling a stored procedure during a stored procedure
Such as
Begin
......
SayHelloWorld ();
End
/

(3) Examples
1. Stored Procedures with parameters
Demand: Give the specified employee a 100 salary increase and print the salary before and after the rise.

Create or Replace procedure raisesalary (Eno in number)
as
  --variable
  psal emp.sal%type
begin
  --get a rise in salary
  Select Sal into Psal from EMP where Empno=eno;
  -Rising wages
  update emp set sal=sal+100 where Empno=eno;
  --Print
  dbms_output.put_line (' Up front ' | | psal| | '  After the rise: ' | | (psal+100));
End;
/

At the time of execution, exec raisesalary (1110) can be executed directly, with a salary increase of 100 yuan for employees numbering 1110.
Don't forget to commit after you finish your execution.
You can also call Raisesalary (1110) directly in the stored procedure.

Note: Do not commit or rollback in a stored function.

Exercise: Increase the specified amount of salary for the specified employee (pass multiple parameters)

Create or Replace procedure raisesalarybycondition (Eno in Number,rsal in number)
as
  --variable
  psal emp.sal%type;
begin
  -Get up front salary
  select Sal into Psal from EMP where Empno=eno;
  -Rising wages
  update emp set sal=sal+rsal where Empno=eno;
  --Print
  dbms_output.put_line (' Up front ' | | psal| | '  After the rise: ' | | (psal+rsal));
End;
/

Second, the storage function
(1) Introduce
A function is a named stored program that takes parameters and returns a computed value. Function
is similar to the structure of a procedure, but you must have a return clause that returns the function value. Function description
To specify the function name, the type of the result value, and the parameter type, and so on.

(2) Grammar
The syntax of a resume store function:
create [or replace] function name (argument list)
return function value type
As
Plsql Sub-Program body

(3) Examples
Check the yearly income of an employee (monthly salary + bonuses and)

Create or Replace function Queryempincome (Eno in number) return number as
  --Variable
  psal emp.sal%type;
  Pcomm Emp.comm%type;
Begin
  -Get the employee's monthly salary and bonus
  select Sal,comm into the Psal,pcomm from EMP where Empno=eno;
  Return PSAL*12+NVL (pcomm,0);
End;
/

In the console compile run, query employee number 1110 annual income:


We found that the employee with number 1110 had a monthly salary of 6820 and a bonus of 0.


6820*12+0=81840, the result of our program running is correct.

Note that the way a stored function is invoked can only be invoked in a plsql statement, and a variable must be accepted to accept the returned arguments.
(If only print can be ignored)

(4) in and out of processes and functions
Generally speaking, the difference between a procedure and a function is that a function can have a return value, and the procedure has no return value.

However, both procedures and functions can specify one or more output functions through out. We can use out parameters to
Implementations in procedures and functions return multiple values.

Example: Check the name, monthly salary, and position of an employee.
Here we are using the stored procedure:

Create or Replace procedure Queryempinfo (Eno in number,
                                         pename out varchar2,
                                         psal out number,
                                         pjob out varchar 2 as
begin
  Select Ename,sal,job into Pename,psal,pjob from EMP where Empno=eno;
End;
/

Let's test this out:
Test function:

Set serveroutput on
declare
  ENO number;
  Pename varchar2 ();
  Psal number;
  Pjob varchar2 ();
Begin
  eno:=1110;
  Queryempinfo (
      Eno => Eno, Pename => pename, psal => psal, pjob =>
      );
   Dbms_output.put_line (' No. 1110 employee's name is: ' | | pename| | ' Salary for: ' | | psal| | ' Position for: ' | | Pjob);
End;
/

Test results:


The same as the result of a direct query:

To prove that our function is correct.

Since stored procedures and stored functions can achieve the same functionality, why do they need to exist together?
Because there are both stored procedures and stored functions in the old version, there may be only one of them in the new version, so
To ensure program compatibility, keep both.

When to use stored procedures/stored functions.
Principle: If there is only one return value, use the stored function;




Third, use Java to invoke a stored procedure
Open our Eclipse and create a javaproject project called "Testoracle."


We created a Lib folder under the project to put Oracle's database-driven jar packages in,
and add to BuildPath:


Then we create a jdbcutils class that gets the connection to the database:


Content:

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:xe";
    private static String user= "Jack";
    
    private static String password= "1234"; static{try {class.forname (driver);//Load driver} catch (ClassNotFoundException e) {E
        . Printstacktrace (); The public static Connection getconnection () {try {return drivermanager.getconnection (ur
        L,user,password);
        catch (SQLException e) {e.printstacktrace ();
    return null; public static void Release (Connection conn,statement St,resultset rs) {if (conn!=null) {try
            {Conn.close ();
    catch (SQLException e) {            E.printstacktrace ();
                }finally{conn=null;//Garbage Collection} if (St!=null) {try {
            St.close ();
            catch (SQLException e) {e.printstacktrace (); }finally{st=null;//Garbage Collection} if (Rs!=null) {try {R
            S.close ();
            catch (SQLException e) {e.printstacktrace ();
 }finally{rs=null;//Garbage Collection}}}

To create and write a test class:


Content

Package demo.test;
Import java.sql.CallableStatement;
Import java.sql.Connection;

Import java.sql.SQLException;

Import Oracle.jdbc.driver.OracleTypes;

Import Org.junit.Test;

Import Demo.util.JDBCUtils;
        public class Oracletest {/*create or replace procedure Queryempinfo (Eno in number, pename out VARCHAR2, Psal out number, pjob out varchar2) as begin select Ename,sal,job to pename,psal,pjob from EMP whe
    Re Empno=eno;
    End /*/@Test public void TestProcedure () {//Test stored procedure//format {call <procedure-name>[{<arg1&gt
        ;,<arg2>,...}]}
        String sql= "{call Queryempinfo (?,?,?,?)}";
        Connection Conn=null;
        CallableStatement is the interface for executing SQL stored procedures callablestatement call=null;
            try {conn=jdbcutils.getconnection ();
            Call=conn.preparecall (SQL);
            Assignment Call.setint (1, 1110); For out parameters, declare 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);
            Double sal = call.getdouble (3);
            
            String job = call.getstring (4);
            
            SYSTEM.OUT.PRINTLN (Employee information for work Number 1110: ");
            System.out.println ("Name:" +name);
            System.out.println ("Salary:" +sal);
            
        System.out.println ("Position:" +job);
        catch (SQLException e) {e.printstacktrace ();
        }finally{Jdbcutils.release (conn, call, NULL); }/*create or Replace function queryempincome (Eno in number) return number AS-
      -Variable psal emp.sal%type;
    Pcomm Emp.comm%type;
Begin-Get the employee's monthly salary and bonus select Sal,comm into the Psal,pcomm from EMP where Empno=eno;      Return PSAL*12+NVL (pcomm,0);
    End /*/@Test public void TestFunction () {//Test store function//format {? = Call <procedure-name>[{<arg1&
        Gt;,<arg2>,...}]}
        String sql= "{=call queryempincome (?)}";
        Connection Conn=null;
        CallableStatement is the interface for executing SQL stored procedures callablestatement call=null;
            try {conn=jdbcutils.getconnection ();
            Call=conn.preparecall (SQL);
            The first question mark is the return value, to declare call.registeroutparameter (1, oracletypes.number);
            
            Assignment Call.setint (2, 1110);
            
            Call Call.execute ();
 
            Remove result Double s_sal = call.getdouble (1);
            SYSTEM.OUT.PRINTLN ("Employee's annual salary: 1110)";

        System.out.println (s_sal);
        catch (SQLException e) {e.printstacktrace ();
        }finally{Jdbcutils.release (conn, call, NULL);
 }
    }
}

Note that you want to join the JUNIT4 environment here.

First Test the TestProcedure () method, test results:


Then test the TestFunction () method and test results:



Iv. issues
Although our stored procedures have out, we cannot write so many out parameters if we have a particular number of fields returned by a single piece of data.
This requires us to use the cursor in the previous plsql to solve the problem.
That is: Use the cursor in the out parameter.

(1) Confirm package structure
Query employee information According to employee's department number and ask to return all information about all employees in the department

Using the cursor as an out parameter
1. Create a package: MyPackage
2. Define a custom type in the package: Empcursor type is the cursor
A stored procedure: queryemp

CREATE OR REPLACE
PACKAGE mypackage as
 
  type empcursor is REF CURSOR;
  Procedure Queryemplist (DNO in number,emplist out empcursor);

End MyPackage;

(2) Create the package body
--Implementing the package body

CREATE OR REPLACE
PACKAGE body mypackage as

  procedure queryemplist (DNO in Number,emplist out empcursor) as
  is GIN
      
      Open emplist for SELECT * from EMP where deptno=dno;
    
  End Queryemplist;

End MyPackage;

After we compiled the above code in Oracle, we tested it in a previous Java project:

@Test public
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, 30), and/or
        Call.registeroutparameter (2, oracletypes.cursor) for all employees in department number 30;
        
        Implementation of
        Call.execute ();
        
        Remove the collection
        rs= (oraclecallablestatement) call). GetCursor (2);
        while (Rs.next ()) {
            String name=rs.getstring ("ename");
            String job=rs.getstring ("job");
            System.out.println (name+ "work is:" +job);
        }
        
        
    catch (SQLException e) {
        e.printstacktrace ();
    } finally{
        Jdbcutils.release (conn, call, RS);
    }

Test results:

Reprint Please indicate the source: http://blog.csdn.net/acmman/article/details/52512884