Oracle stored procedures and custom functions detailed _oracle

Overview

Procedures and functions in pl/sql, often called subroutines, are a special type of pl/sql blocks that can be stored in a database in a compiled form and invoked for subsequent blocks.

Same point: A program that completes a specific function

Different point: Whether to return the value with the returns statement.

As an example:

Create or Replace procedure printstudents (P_staffname in Xgj_test.username%type) as

 cursor C_testdata is
 select T.sal, T.comm from xgj_test t where t.username = P_staffname;

Begin for

 v_info in C_testdata loop
 dbms_output. Put_Line (V_info.sal | | ' ' || V_INFO.COMM);
 End Loop;

End Printstudents;

Once you create a change program and store it in a database, you can call the procedure in the following ways

Begin
 Printstudents (' Computer science ');
 Printstudents (' Match ');
End;
/

Or

exec printstudents (' Computer science ');
exec printstudents (' Match ');

In the command window:

In the SQL window of the Pl/sql tool:

Creation and invocation of stored procedures

Basic syntax

create [or Replace] procedure procedure_name
[(argument [{in | Out | In Out}] type,
...
argument [{in | Out | In Out}] type) ' {is | as}
procedure_body

Non-parameter stored procedures

/**
 -without-parameter stored-
 print Hello World

 Call stored procedure
 : 1. Exec sayhelloworld ();
 2 begin 
 SayHelloWorld ();
 End;
 /

*/
Create or Replace procedure SayHelloWorld as
-description part
begin
 Dbms_output.put_line ( ' Hello World ');
End SayHelloWorld;

Call Procedure:

Sql> set serveroutput on;
Sql> exec SayHelloWorld ();

Hello World

pl/sql procedure successfully completed

sql> begin
 2 SayHelloWorld ();
 3 SayHelloWorld ();
 4 End;
 5/

Hello World
Hello World

pl/sql procedure successfully completed

Stored Procedures with parameters

/**
creates a stored procedure with parameters

to increase the payroll for the specified employee and print the salary before and after the increase

/create
or Replace procedure addsalary (Staffname in Xgj _test.username%type)
As--Define a variable to save the adjustment before the salary
oldsalary Xgj_test.sal%type;

Begin
 -check salary before employee rise
 Select T.sal into oldsalary from Xgj_test t where T.username=staffname; 

 --Adjust Salary
 update Xgj_test t set t.sal = sal+1000 where t.username=staffname;

 --Output
 dbms_output.put_line (' Adjusted Salary: ' | | oldsalary | | ', adjusted Salary: ' | | (oldsalary + 1000));

End Addsalary;

As you can see, there is no commit operation after the UPDATE statement.

Generally speaking, in order to ensure the consistency of the transaction, by the caller to submit more appropriate, of course, it is necessary to distinguish between the specific business requirements ~

Begin 
Addsalary (' Xiao ');
Addsalary (' gong ');
commit;
End;
/

Stored functions

Basic syntax

create [or replace] function function_name
[(argument [{in |] Out | In Out}] type,
...
argument [{in | Out | In Out}] type)] return 
{is | as}
function_body

Where the return clause must exist, a function that ends if it does not perform an error is different from the one that has been saved.

Stored functions

The data prepared are as follows:

/**
Check employee's annual salary (monthly salary *12 + bonus)
*

/Create or Replace function Querysalaryincome (staffname in varchar2)

 return Number
 AS--Define variables save employee's salary and bonus
 psalary Xgj_test.sal%type;
 Pcomm Xgj_test.comm%type;

Begin
 --check employee payroll and bonuses
 Select T.sal, t.comm into
 psalary, pcomm from
 xgj_test t
 where t.username = Staffname;
 --Return the salary directly
 psalary * + pcomm;
End Querysalaryincome;

There is a problem, when the bonus is empty, the calculated annual income unexpectedly is empty.

Because if there is a null value in an expression, the result of the expression is a null value.

So we need to process the null value and use the NVL function.

The last modified function is

Create or Replace function Querysalaryincome (staffname in varchar2) return number
 AS--Define variable save employee's wages and bonuses
 Psalary Xgj_test.sal%type;
 Pcomm Xgj_test.comm%type;

Begin
 --check employee payroll and bonuses
 Select T.sal, t.comm into
 psalary, pcomm from
 xgj_test t
 where t.username = Staffname;
 --Return the salary directly
 Psalary * + NVL (pcomm,0);
End Querysalaryincome;

Out parameters

In general, the difference between stored procedures and stored functions is that a stored function can have a return value and the stored procedure has no return value.

• Both stored procedures and stored functions can have out parameters
• Both stored procedures and stored functions can have more than one out parameter
• Stored procedures can implement return values through out parameters
  

So how do we choose between stored procedures and stored functions?

Principle:

If there is only one return value, the stored procedure is used, otherwise (that is, there is no return value or there are multiple return values).

/**
According to employee's name, inquire all information of employee
/Create or Replace procedure Querystaffinfo (Staffname in Xgj_test.username%type,
           psal out number,
           Pcomm out Xgj_test.comm%type,
           pjob out Xgj_test.job%type) 

is

begin
 -- Check the employee's salary, bonus and position
 select T.sal,t.comm,t.job into Psal,pcomm,pjob from Xgj_test t where T.username=staffname;
End Querystaffinfo;

Throw two thinking questions first:

• Query all information about an employee –> out parameters too much what to do?
• Query information for all employees in a department –> out to return the collection?

I'll talk about how to solve it later. Can't write out one by one.

Accessing stored procedures and stored functions in the application

Overview

We use Java programs to connect to Oracle databases.

Using Jar:ojdbc14.jar

About the difference between several jars that Oracle has officially provided

• Classes12.jar (1,600,090 bytes)-For use with JDK 1.2 and JDK 1.3
• Classes12_g.jar (2,044,594 bytes)-Same as Classes12.jar, except that classes were compiled with "Javac-g" and contain s ome tracing information.
• Classes12dms.jar (1,607,745 bytes)-Same as Classes12.jar, except that it contains additional code ' to support Oracle Dyna Mic monitoring Service.
• Classes12dms_g.jar (2,052,968 bytes)-Same as Classes12dms.jar except that classes were compiled with "JAVAC-G" and cont Ain some tracing information.
• Ojdbc14.jar (1,545,954 bytes)-Classes for use with JDK 1.4 and 1.5
• Ojdbc14_g.jar (1,938,906 bytes)-Same as Ojdbc14.jar, except that classes were compiled with "javac-g" and contain some Tracing information.
• Ojdbc14dms.jar (1,553,561 bytes)-Same as Ojdbc14.jar, except that it contains additional code ' to support Oracle Dynamic Monitoring Service.
• Ojdbc14dms_g.jar (1,947,136 bytes)-Same as Ojdbc14dms.jar, except that classes were compiled with "javac-g" and contain Some tracing information.
  

The project catalogue is as follows:

Simple write down a tool class to get a database connection

Import java.sql.Connection;
Import Java.sql.DriverManager;
Import Java.sql.ResultSet;
Import java.sql.SQLException;

Import java.sql.Statement; public class Dbutils {//Set database driver, database connection address port name, username, password private static final String Driver = "Oracle.jdbc.driver.OracleDri
 Ver ";
 Private static final String URL = "Jdbc:oracle:thin: @ip: xxxx";
 Private static final String username = "XXXX";

 Private static final String password = "xxxx";
  /** * Register Database driver/static {try {Class.forName (driver);
  catch (ClassNotFoundException e) {throw new Exceptionininitializererror (E.getmessage ()); }/** * Get database connection/public static Connection getconnection () {try {Connection Connection = drivermanager.g
   Etconnection (URL, username, password);
  Succeeded, returned connection return connection;
  catch (SQLException e) {e.printstacktrace ();
 }//Get failed, returns null return null; /** * FREE connection/public static void Cleanup (Connection conn, Statement St, ResultSet rs) {if (RS != null) {try {rs.close ();
   catch (SQLException e) {e.printstacktrace ();
   finally {rs = null;
   } if (St!= null) {try {st.close ();
   catch (SQLException e) {e.printstacktrace ();
   finally {st = null;
   } if (conn!= null) {try {conn.close ();
   catch (SQLException e) {e.printstacktrace ();
   finally {conn = null;

 }
  }

 }
}

Accessing stored procedures in the application

According to the official API, we can see:

Import java.sql.CallableStatement;
Import java.sql.Connection;

Import java.sql.SQLException;

Import Org.junit.Test;

Import Com.turing.oracle.dbutil.DBUtils;


Import Oracle.jdbc.OracleTypes; public class TestProcedure {@Test public void callprocedure () {//{call <procedure-name>[(<arg1>,<arg

  2>...)]}
  Connection conn = null;

  CallableStatement callablestatement = null;
              /** * According to the employee's name, all information of the employee is queried create or replace procedure Querystaffinfo (Staffname in Xgj_test.username%type, 
    Psal out number, pcomm out Xgj_test.comm%type, pjob out Xgj_test.job%type) is begin
   --Check the employee's salary, bonus and position select T.sal,t.comm,t.job into psal,pcomm,pjob from Xgj_test t where T.username=staffname;
   End Querystaffinfo;

  *//We can see that there are 4 parameters 1 entry parameter 3 out parameter String sql = "{call Querystaffinfo (?,?,?,?)}";
   try {//Get Connection conn = Dbutils.getconnection (); Get to CallableStatement callablestatement = Conn.preparec by connectionAll (SQL);
   For in parameters, a value of callablestatement.setstring (1, "Xiao") needs to be assigned; For out parameters, you need to declare Callablestatement.registeroutparameter (2, Oracletypes.number);
   Second one?
   Callablestatement.registeroutparameter (3, Oracletypes.number);//Third?

   Callablestatement.registeroutparameter (4, Oracletypes.varchar);//Fourth?

   Execute call Callablestatement.execute ();
   Take out the result int salary = Callablestatement.getint (2);
   int comm = Callablestatement.getint (3);

   String job = callablestatement.getstring (3);

  System.out.println (Salary + "\ T" + comm + "\ T" + job);
  catch (SQLException e) {e.printstacktrace ();
  }finally {dbutils.cleanup (conn, callablestatement, NULL);

 }


 }
}

Accessing a stored function in an application

According to the official API, we can see:

Import java.sql.CallableStatement;

Import java.sql.Connection;

Import Org.junit.Test;

Import Com.turing.oracle.dbutil.DBUtils;

Import Oracle.jdbc.OracleTypes; public class Testfuction {@Test public void callfuction () {//{?= call <procedure-name>[(<arg1>,<arg2&
  gt;,..)]}
  Connection conn = null;
  CallableStatement call = null;
     /** * Create or Replace function Querysalaryincome (staffname in VARCHAR2) return number AS--Define variable save employee's wages and bonuses
     Psalary Xgj_test.sal%type;

    Pcomm Xgj_test.comm%type; Begin--Check employee payroll and bonuses Select T.sal, T.comm into Psalary, pcomm from xgj_test t where t.username = St
     Affname;
    --Return the salary directly psalary * + NVL (pcomm,0);
   End Querysalaryincome;

  */String sql = "{=call querysalaryincome (?)}";
   try {//Get Connection conn = Dbutils.getconnection ();

   Get CallableStatement call = Conn.preparecall (SQL) via conn; Out parameter, you need to declare call.registeroutparameter (1, ORACLETYPES.NUmber);

   In parameter, need to assign value call.setstring (2, "Gong");
   Implementation of Call.execute (); Remove the return value first?
   The value double income = call.getdouble (1);
  SYSTEM.OUT.PRINTLN ("Annual income of the employee:" + income);
  catch (Exception e) {e.printstacktrace ();
  }finally {DBUTILS.CLEANUP (conn, call, NULL);

 }
 }

}

Accessing the cursor in out parameters

Using the cursor in out parameters

The two thoughts we threw before:

• Query all information about an employee –> out parameters too much what to do?
• Query information for all employees in a department –> out to return the collection?

We can do that by returning to cursor.

To use the cursor in an out parameter:

• Statement Package Structure
• Baotou
• Inclusion
  

Baotou:

Create or Replace package MyPackage is

 --author:administrator-
 -created:2016-6-4 18:10:42-
 -Purpose: 
   --Use the type keyword is REF CURSOR description is the type type
 staffcursor is ref CURSOR;

 Procedure Querystaffjob (Pjob   in Xgj_test.job%type,
       jobstafflist out staffcursor);

End MyPackage;

After the header is created, the package body is created, and the package body needs to implement all the methods declared in the header.

Inclusion

Create or Replace package body MyPackage

 was procedure querystaffjob (Pjob   in Xgj_test.job%type,
       Jobstafflist out Staffcursor)

 as
 begin
  Open jobstafflist to select * from Xgj_test t where T.job=pjob;
 End Querystaffjob;

End MyPackage;

In fact, the Plsql tool creates the header, and when compiled, the frame of the package is automatically generated.

Accessing stored procedures under packages in an application

Accessing stored procedures under packages in an application

Accessing a stored procedure under a package in an application requires a package name

Import java.sql.CallableStatement;
Import java.sql.Connection;

Import Java.sql.ResultSet;

Import Org.junit.Test;

Import Com.turing.oracle.dbutil.DBUtils;
Import Oracle.jdbc.OracleTypes;

Import oracle.jdbc.driver.OracleCallableStatement;
     public class Testcursor {@Test public void Testcursor () {/** * * Create or Replace package MyPackage is

     Type staffcursor is REF CURSOR;

    Procedure Querystaffjob (Pjob in Xgj_test.job%type, jobstafflist out staffcursor);
   End MyPackage;

  */String sql = "{call Mypackage.querystaffjob (?,?)}";
  Connection conn = null;
  CallableStatement call = null;

  ResultSet rs = null;
   try {//Get database Connection conn = Dbutils.getconnection ();

   Create Callablestatemet call = Conn.preparecall (SQL) via conn;
   The in parameter needs to be assigned a value of call.setstring (1, "Staff");

   Out parameters need to declare Call.registeroutparameter (2, oracletypes.cursor);

   Execute call Call.execute (); Gets the return value rs = ((oraclecallablestatement) call). GetCursor (2);
   while (Rs.next ()) {//Fetch value String username = rs.getstring ("username");
    Double sal = rs.getdouble ("Sal");

    Double comm = rs.getdouble ("comm");
   System.out.println ("Username:" + username + "\ t sal:" + sal + "T comm:" + comm);
  } catch (Exception e) {e.printstacktrace ();
  }finally {DBUTILS.CLEANUP (conn, call, RS);

 }
 }

}

Original link: http://blog.csdn.net/yangshangwei/article/details/51581952

The above is the entire content of this article, I hope to help you learn, but also hope that we support the cloud habitat community.