FUNCTION
A function is nothing but a stored PL/SQL program which perform some operation which takes arguments and return a value back to the calling program.
Difference between Procedure and Function
Procedure may not return a value to the calling program. Always function must return a value to the calling program.
Syntax
CREATE OR REPLACE FUNCTION function_name
(argument1 in/out data type, argument2 in/out data type....)
RETURN data type
AS
PL/SQL block
End function_name
While writing a function we should have a return statement with in the PL/SQL block.
You cannot execute a function as same as stored procedure. You should
call a function from a PL/SQL block or from a sql statement or from
another stored procedure or stored function, the reason being the value
is returned by the function and that value should be in a variable.
Write a function to get the customer name from customer table by passing the customer number.
CREATE OR REPLACE FUNCTION get_custname ( var_custno CUSTOMER.CUST_NO%TYPE)
return char AS
var_custnmhold CUSTOMER.CUST_NAME%TYPE;
SELECT cust_name into var_custnmhold
FROM customer
WHERE cust_no = var_custno;
Return var_custnmhold;
EXCEPTION
WHEN no_data_found then
Return ' ';
END get_custname;
Write a Function to update the customer name by passing the customer number and the new name. If you find the row and updated then return1 else -1.
CREATE OR REPLACE FUNCTION func_upt_custname ( var_custno
customer.cust_no%TYPE, var_custname customer.cust_name%TYPE ) return number IS
BEGIN
Update customer set cust_name = var_custname
Where cust_no = var_custno;
IF SQL%FOUND then
Return 1
ELSIF SQL%NOTFOUND then
Return -1
END IF
EXCEPTION
WHEN Others THEN
Return -1
END func_upt_custname;