Oracle PL/SQL functions

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 FUNCTION Annual_incomec (uname VARCHAR2)

Annual_salazy number (7,2);

   SELECT a.sal*13 to Annual_salazy from EMP a WHERE a.ename=uname;
   RETURN Annual_salazy;
END;
/

2), call function in Sqlplus

sql> var income number;
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;

Package junit.test;

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

/**
* Demonstrates a Java program invoking Oracle's function case
*
* @author Jiqinlin
*
*/
public class Proceduretest {

public static void Main (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 ();
}
}
}

See also: http://www.cnblogs.com/linjiqin/archive/2012/02/24/2367147.html

Oracle PL/SQL functions