PL/SQL functions and their differences from stored procedures

function is used to return specific data, and when a function is established, the function header must contain a return clause. The body of the function must contain the data returned by the return statement. We can use CREATE function to create functions.

1), followed by a case to simulate the use of functions

--Enter the employee's name and return the employee's annual salary
CREATE FUNCTIONAnnual_incomec (unameVARCHAR2)
RETURN  Number  is 
Annual_salazy Number(7,2);
BEGIN 
   SELECTA.sal* -  intoAnnual_salazy fromEMP AWHEREA.ename=Uname
   RETURNAnnual_salazy;
END;
/

2), call function in Sqlplus

SQL>varnumber;
SQL> call Annual_incomec ('SCOTT'into: income;
SQL>print income;

3), call the Oracle function in the Java program: Select Annual_incomec (' SCOTT ') income from dual;

 PackageJunit.test;

ImportJava.sql.Connection;
ImportJava.sql.DriverManager;
ImportJava.sql.PreparedStatement;
ImportJava.sql.ResultSet;

/**
* Demonstrates a Java program invoking Oracle's function case
*
* @authorJiqinlin
*
*/
 Public classproceduretest {

     Public Static voidMain (string[] args) {

        Try{
            //1. Load Driver
Class.forName ("Oracle.jdbc.driver.OracleDriver");
            //2. Get Connected
Connection ct = drivermanager.getconnection (
"Jdbc:oracle:thin:@127.0.0.1:1521:orcl", "Scott", "Oracle");
            //3. Create PreparedStatement
PreparedStatement PS = ct.preparestatement ("Select Annual_incomec (' SCOTT ') annual from dual");
            //4. Implementation
ResultSet Rs=ps.executequery ();
            if(Rs.next ()) {
Float annual=rs.getfloat ("annual");
SYSTEM.OUT.PRINTLN (annual);
}
            //5. Close
Rs.close ();
Ps.close ();
Ct.close ();
}Catch(Exception e) {
E.printstacktrace ();
}
}
}

The difference between a stored procedure and a function:

1. In general, the function of the stored procedure implementation is a bit more complex, and the function implementation of the function is relatively strong.

2. For stored procedures, parameters (output) can be returned, and functions can only return values or table objects.

3. The stored procedure is typically performed as a separate part, and the function can be called as part of a query statement, since the function can return a Table object. Therefore, it can be located behind the FROM keyword in a query statement.