Detailed explanation of Oracle stored procedures and custom functions

Basic syntax for Oracle stored procedures

1. Basic structure
CREATE OR REPLACE PROCEDURE Stored procedure name
(
Parameter 1 in number,
Parameter 2 in number
) is
Variable 1 INTEGER: = 0;
Variable 2 DATE;
BEGIN

End Stored Procedure name

2.SELECT into STATEMENT
By saving the results of a select query into a variable, you can store multiple columns in multiple variables at the same time, you must have a
Record, otherwise throw an exception (if no record is thrown no_data_found)
Example:
BEGIN
SELECT col1,col2 into variable 1, variable 2 from typestruct where xxx;
EXCEPTION
When No_data_found THEN
xxxx
End;
...

3.IF judgment
IF v_test=1 THEN
BEGIN
Do something
End;
End IF;

4.while Loop
While V_test=1 loop
BEGIN
Xxxx
End;
End LOOP;

5. Variable Assignment value
V_test: = 123;

6. Use cursor with for
...
Is
CURSOR cur is SELECT * from xxx;
BEGIN
For Cur_result in Cur loop
BEGIN
V_sum: =cur_result. Column name 1+cur_result. Column Name 2
End;
End LOOP;
End;

7. Cursor with parameters
CURSOR C_user (c_id number) is SELECT NAME from USER WHERE typeid=c_id;
OPEN c_user (variable value);
LOOP
FETCH C_user into V_name;
EXIT FETCH C_user%notfound;
Do something
End LOOP;
Close C_user;

8. Use Pl/sql Developer Debug
Create a test WINDOW after connecting to the database
Enter the code for the calling SP in the window, F9 start debug,ctrl+n step

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

/**
No parameters Saved
Print Hello World

To invoke a stored procedure:
1. exec SayHelloWorld ();
2 begin
SayHelloWorld ();
End
/

*/
Create or replace procedure SayHelloWorld
As
--Description section
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

/**
To create a stored procedure with parameters

Increase the salary 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 salary before the adjustment
Oldsalary Xgj_test.sal%type;

Begin
--Check the salary before the employee rises
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 wages and bonuses
Psalary Xgj_test.sal%type;
Pcomm Xgj_test.comm%type;

Begin
--check employees ' salaries and bonuses
Select T.sal, T.comm
Into Psalary, Pcomm
From Xgj_test t
where t.username = Staffname;
--Direct return to annual salary
return 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 variables save employee's wages and bonuses
Psalary Xgj_test.sal%type;
Pcomm Xgj_test.comm%type;

Begin
--check employees ' salaries and bonuses
Select T.sal, T.comm
Into Psalary, Pcomm
From Xgj_test t
where t.username = Staffname;
--Direct return to annual salary
return psalary * + NVL (pcomm,0);
End Querysalaryincome;
Out parameters

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).

/**
Check all the employee's information according to the employee's name
*/
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.OracleDriver";
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.getconnection (URL, Username, password);
           //success, returning connection
             return connection;
       } catch (SQLException e) {
             E.printstacktrace ();
       }
       //Get failed, return 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>,<ARG2>, ...)]}

Connection conn = null;
CallableStatement callablestatement = null;

/**
*
Check all the employee's information according to the employee's name
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 we've saved 4 arguments, 1 parameters, 3 out of the argument,
String sql = "{call Querystaffinfo (?,?,?,?)}";

try {
Get connection
conn = Dbutils.getconnection ();
Access to CallableStatement via connection
CallableStatement = Conn.preparecall (sql);

For in parameters, you need to assign a value
Callablestatement.setstring (1, "Xiao");
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?

Executes the call
Callablestatement.execute ();

Remove results
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

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>[(&LT;ARG1&GT;,&LT;ARG2&GT;, ...)]}
Connection conn = null;
CallableStatement call = null;
/**
* Create or Replace function Querysalaryincome (staffname in VARCHAR2)
Return number as
--Define variables save employee's wages and bonuses
Psalary Xgj_test.sal%type;
Pcomm Xgj_test.comm%type;

Begin
--check employees ' salaries and bonuses
Select T.sal, T.comm
Into Psalary, Pcomm
From Xgj_test t
where t.username = Staffname;
--Direct return to annual salary
return psalary * + NVL (pcomm,0);
End Querysalaryincome;
*/

String sql = "{=call querysalaryincome (?)}";

try {
Get connection
conn = Dbutils.getconnection ();
Get CallableStatement through Conn
Call = Conn.preparecall (SQL);

           /Out parameters, need to declare
             call.registeroutparameter (1, oracletypes.number);
           //In parameter, required assignment
             call.setstring (2, "Gong");

Perform
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:

--Using the type keyword is REF CURSOR description is cursor 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 is

Procedure Querystaffjob (Pjob in Xgj_test.job%type,
Jobstafflist out Staffcursor)

As
Begin
Open jobstafflist for SELECT * 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 {
Getting a database connection
conn = Dbutils.getconnection ();
Create CALLABLESTATEMET through Conn
Call = Conn.preparecall (SQL);

In parameter requires assignment
Call.setstring (1, "Staff");
Out parameter requires declaration
Call.registeroutparameter (2, oracletypes.cursor);

Executes the call
Call.execute ();

           //Get 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);
}
}

}