Oracle stored procedures and calls

Oracle's stored procedures and calls

Oracle Stored Procedure Syntax

Oracle's stored procedure syntax is as follows:

Create procedure stored procedure name (random)
       is where you can define constants, variables, cursors, complex data types here you can define variables, constants
begin to
       execute partial end
;

(2) Stored procedure syntax with parameters:
CREATE procedure stored procedure name (variable 1 data type, variable 2 data type,..., variable n data type)
       is where you can define constants, variables, cursors, Complex data types Here you can define variables, constants
begin to
       execute partial end
;

(3) Stored procedure syntax with input and OUTPUT parameters:
CREATE procedure stored procedure name (variable 1 in (or out) data type, variable 2 in (or out) data type,..., variable n in (or out) data type)
       is where you can define constants, variables, cursors, complex data types here you can define variables, constants
begin to
       execute partial end
;

Note: When you create a stored procedure with the above syntax, you may encounter a stored procedure with the same name already in the database, so that Oracle can make an error and say the name is already in use by an existing object. There are two ways to fix this:

Method One: Change a stored procedure name

Method Two: Add the or replace keyword between the first create procedure, for example: Create or Replace procedure stored procedure name. This method is not recommended, however, because it replaces a stored procedure with the same name as the one you are currently writing.

Stored procedure case one: stored procedures with no parameters

Create replace procedure Procedure_1
is
begin
       dbms_output.put_line (' procedure_1 ... ');
End

Stored procedure case two: stored procedures with parameters

CREATE PROCEDURE procedure_2 (v_i number,v_j number)
is
       v_m number (5);
Begin
       Dbms_output.put_line (' procedure_2 ... ');
       V_m: = V_i + V_j;
       Dbms_output.put_line (v_i| | ' + ' | | v_j| | ' = ' | | V_M);
End

Stored procedure case Three: a stored procedure with input and output parameters
The parameters of the stored procedure are divided into input parameters and output parameters.
Input parameters: Input parameters are typically added in to the variable name and data type to indicate that the parameter is an input parameter
Output parameters: Output parameters typically add out between variable names and data types to indicate that the variable is an output parameter
If you do not write in and out, the default is input parameter

CREATE PROCEDURE Procedure_3 (v_i in Number,v_j in number, v_m out number)
is
begin
       Dbms_output.put_line ( ' Procedure_3 ... ');
       V_m:=v_i-v_j;
       Dbms_output.put_line (v_i| | ' | | v_j| | ' = ' | | V_M);
End

Calling a stored procedure in a pl/sql block

Here is an example of calling the above three stored procedures

DECLARE
       v_param1 number (5): =2;
       V_PARAM2 Number (5): =8;
       V_result number (5);
Begin 
       --The stored procedure procedure_1 () that invokes the case one above
       ; 
       --invokes the stored procedure
       procedure_2 (V_PARAM1,V_PARAM2) of case two above; 
       --invokes the stored procedure
       procedure_3 (v_param1,v_param2,v_result) of case three above;
       Dbms_output.put_line (V_result);
End;

/* Implementation results: */
Procedure_1 ...
procedure_2 ..... 2 + 8 = ten
procedure_3
..... 2-8 =-6
10

Java Call stored Procedures

Case one: Java calls stored procedures that do not return a value

Requirements: Write a record like database EMP table Insert a number 6666, name John, position manager

/* Stored procedures *
/CREATE PROCEDURE procedure_4 (v_empno emp.empno%type,v_ename emp.ename%type,v_job emp.job%type)
is
begin
       inserts into EMP (empno,ename,job) values (v_empno,v_ename,v_job);
End

Java Call stored procedure public
static void Main (string[] args) {
    Connection conn=null;
    CallableStatement Cs=null;
    ResultSet Rs=null;
    Java call stored procedure
    try {
        class.forname ("Oracle.jdbc.OracleDriver");
        Conn=drivermanager.getconnection ("Jdbc:oracle:thin:@127.0.01:1521:orcl", "Scott", "Tiger");
        Cs=conn.preparecall ("{Call Procedure_4 (?,?,?)}");
        Assigning value to input parameters
        (1, 6666) Cs.setint;
        Cs.setstring (2, "John");
        Cs.setstring (3, "MANAGER");
        Cs.execute ();//Execution
    } catch (Exception e) {
        e.printstacktrace ();
    } finally{
        Closeresource (CONN,CS,RS);//Close
    resource   
}
//Execute inserts a number 6666 in the EMP table of the database, with the name John, Record of position as manager

Case TWO: Java call returns a single row of stored procedures

Requirements: Write a stored procedure that looks up employee names based on the employee number and invoke the stored procedure in Java

/* stored procedures */CREATE PROCEDURE procedure_5 (V_empno in Emp.empno%type,v_ename out Emp.ename%type)
Is begin select ename into V_ename from EMP where empno=v_empno; End; 

Java Call stored procedure public
static void Main (string[] args) {
    Connection conn=null;
    CallableStatement Cs=null;
    ResultSet Rs=null;
    try {
        class.forname ("Oracle.jdbc.OracleDriver");
        Conn=drivermanager.getconnection ("Jdbc:oracle:thin:@127.0.01:1521:orcl", "Scott", "Tiger");
        Cs=conn.preparecall ("{Call Procedure_5 (?,?)}");
        Cs.setint (1, 6666);//Assign value to input parameter/
        * Specifies the data type syntax for the output parameter
        : oracle.jdbc.OracleTypes. The data type
        of the output parameter The data type of this example output parameter is VARCHAR, so it is oracle.jdbc.oracletypes.varchar*/
        cs.registeroutparameter (2, Oracle.jdbc.OracleTypes.VARCHAR);
        Cs.execute ()//executes
        //Gets the value of the output parameter, and the position corresponds to the output parameter. , the output parameter of this example corresponds to the 2nd question mark, and the output parameter data type is character type, so it is cs.getstring (2)
        String a=cs.getstring (2);
        SYSTEM.OUT.PRINTLN ("Employee Name:" +a);
    } catch (Exception e) {
        e.printstacktrace ();
    } finally{
        Closeresource (CONN,CS,RS);//close Resource
    }   
}/

* Execution result, console print: * *
Results: Employee Name: John

Case THREE: Java call returns a single row of multiple columns of stored procedures

Requirements: Write a stored procedure that locates employee names, positions, and wages based on the employee number and invokes the stored procedure in Java

/* Stored procedures *
/CREATE PROCEDURE Procedure_6 (V_empno in Emp.empno%type,v_ename out emp.ename%type,v_job out of Emp.job%type, V_sal out Emp.sal%type)
is
begin
       Select Ename,job,sal to V_ename,v_job,v_sal from EMP where empno=v_empno ;
End

Java Call stored procedure public static void main (string[] args) {Connection conn=null;
    CallableStatement Cs=null;
    ResultSet Rs=null;
        try {class.forname ("oracle.jdbc.OracleDriver");
        Conn=drivermanager.getconnection ("Jdbc:oracle:thin:@127.0.01:1521:orcl", "Scott", "Tiger");
        Cs=conn.preparecall ("{Call Procedure_6 (?,?,?,?)}");
        Cs.setint (1, 7788);
        Specifies the data type of the output parameter, note: The order should correspond to Cs.registeroutparameter (2, Oracle.jdbc.OracleTypes.VARCHAR);
        Cs.registeroutparameter (3, Oracle.jdbc.OracleTypes.VARCHAR);
        Cs.registeroutparameter (4, Oracle.jdbc.OracleTypes.DOUBLE);
        Cs.execute ()//execute//Get return value string ename=cs.getstring (2);//Get Name string job=cs.getstring (3); Get position
    Double sal=cs.getdouble (4);//Get Salary System.out.println (the name of employee number 7788 is: "+ename+" position is: "+job+" salary is: "+sal);"
    catch (Exception e) {e.printstacktrace (); }finally{Closeresource (CONN,CS,RS);//Close Resource}}/* Execution results, consolePrinting: * * The name of employee number 7788 is: SCOTT position is: ANALYST salary is: 3000.0 

Case FOUR: Java call a stored procedure that returns multiple rows of multiple columns (return list)

Requirements: Write a stored procedure that locates all employee information for the department based on the department number and invokes the stored procedure in Java

/* Define cursor
/create package my_package as
type emp_cursor is REF CURSOR;
End My_package;

/* Stored procedure
/CREATE PROCEDURE procedure_7 (V_deptno in Emp.deptno%type,emp_cursor out my_package.emp_cursor) is
begin
       Open emp_cursor for SELECT * from EMP where deptno=v_deptno;
End

Java Call stored procedure public static void main (string[] args) {Connection conn=null;
    CallableStatement Cs=null;
    ResultSet Rs=null;
        try {class.forname ("oracle.jdbc.OracleDriver");
        Conn=drivermanager.getconnection ("Jdbc:oracle:thin:@127.0.01:1521:orcl", "Scott", "Tiger");
        Cs=conn.preparecall ("{Call Procedure_7 (?,?)}"); Cs.setint (1, 20);//Assign value to input parameter Cs.registeroutparameter (2, Oracle.jdbc.OracleTypes.CURSOR);//Specify the data type of the output parameter Cs.ex
        Ecute (); Rs= (ResultSet) Cs.getobject (2); Gets the value of the output parameter while (Rs.next ()) {//order is the order of the fields in the database, such as the 5th column in the Database EMP table HireDate, the data class
        Type is date, so the 5th column value should be obtained with rs.getdate (5) System.out.println (Rs.getint (1) + "" +rs.getstring (2) + "" +rs.getdate (5));
    } catch (Exception e) {e.printstacktrace ();  }finally{Closeresource (CONN,CS,RS);//Close Resource}}//* Below is the information for all employees in Sector 20th, here for the convenience we only print the number, name and entry time running results, console printing: * * 7369 SMITH 1980-12-17 7566 JONES 1981-04-02 7788 SCOTT 1987-04-19 7876 ADAMS 1987-05-23 7902 FORD 1981-12-03 

This is the code that closes the resource method in the Java call stored procedure Code above

public static void Closeresource (Connection conn,callablestatement cs,resultset rs) {
        if (rs!=null) {
            try {
                Rs.close ();
            } catch (SQLException e) {
                e.printstacktrace ();
            }
        }
        if (cs!=null) {
            try {
                cs.close ();
            } catch (SQLException e) {
                e.printstacktrace ();
            }
        } if (conn!=null) {
            try {
                conn.close ();
            } catch (SQLException e) {
                e.printstacktrace ();
            }
        }
    }

Finally, an application, pagination of the stored procedure

Paging Stored procedures:

/* Define cursor/Create Package page_package as type page_cursor is REF CURSOR;

End Page_package;
       /* Stored procedures/CREATE PROCEDURE pro_paging (v_page_size in number,--how many v_page_count out numbers,--per page V_current_page in,--current page v_total_count out number,--record total bars emp_cursor out page_package.page_cursor--return check Cursors of the poll result set) is V_begin number (5): =v_page_size* (v_current_page-1) +1;--query start position v_end number (5): =v_page_siz e*v_current_page;--Query End Location V_sql varchar2 (1000): = ' Select Empno,ename from (select A.empno,a.ename,rownu M RN from (select Empno,ename from EMP) a where rownum<= ' | | V_end | | ') b where b.rn>= ' | |
       V_begin;
       V_ename VARCHAR2 (10);
V_empno number (4);
       Begin open Emp_cursor for V_sql;
         Loop fetch emp_cursor into v_empno,v_ename;
         Exit when Emp_cursor%notfound;  Dbms_output.put_line (v_empno| | ' '||
       V_ename);
      End Loop; V_sql:= ' select count (empno) from EMP ';
       Execute immediate v_sql into V_total_count;
       if (mod (v_total_count,v_page_size) =0) then v_page_count:=v_total_count/v_page_size;
       else V_page_count:=trunc (v_total_count/v_page_size) +1;
       End If; Dbms_output.put_line (' Co ' | |
       v_total_count| | ' Records '); Dbms_output.put_line (' Co ' | |
       v_page_count| | ' page '); Dbms_output.put_line (' current page: ' | |
       V_current_page); Dbms_output.put_line (' every page shows ' | |
v_page_size| | ' article '); End

The Java invocation is the same as the above Java invoke stored procedure example. Here for convenience, directly in the Pl/sql call the

/* Call Paging stored procedure * *
declare
      v_page_count number (5);
      V_cursor Page_package.page_cursor;
      V_total_count number (5);
Begin
      Dbms_output.put_line (' first page data ..... ');
      Pro_paging (5,--each page shows 5
      v_page_count,--Total pages
      1,--current page
      v_total_count,--record total number of
      v_cursor--cursors
      );

      Dbms_output.put_line ('--------------------------');

      Dbms_output.put_line (' second page data ..... ');
      --Display second page data
      pro_paging (5,--each page shows 5
      v_page_count,--Total pages
      2,--current page
      v_total_count,--record total number of records
      V_ cursor--cursor
      );
End;

/* Run Result:/* The
first page of data ...
6666  John  empSu2  empSave2
7369  SMITH
7499  ALLEN
Total 17 Records
Total 4 pages
current page:  1
display  5
--------------------------
second page of data per page ...
7521  WARD
7566  JONES
7654  MARTIN
7698  BLAKE
7782  CLARK
total 17 Records
Total 4 pages
Current page:  2
display 5 per page