Functions and procedures in SQL
Functions and procedures in SQL:
Functions:
It is similar to a procedure except that it returns a value.
Syntax:
CREATE OR REPLACE FUNCTION function_name
(
RETURN retutn_datatype
IS|AS
BEGIN
….
END
)
Example:
CREATE OR REPLACE FUNCTION total_custs
RETURN number
IS
total number(2):=0;
BEGIN
SELECT count(*) INTO total
FROM customers;
RETURN total;
END;
How to call a function?
DECLARE
c number(2);
BEGIN
c:= total_custs();
dbms_output.put_line(‘Total is’ || c);
END
Procedures:
Procedures are named blocks that can be reused. The difference between the function and procedure, is function will return a value and the procedure will perform an action. The ‘declare’ statement is used to declare variables of any SQL data type. A compound statement ‘begin … end’, contains multiple SQL statements between them. The procedure is executed by declaring with an ‘executed’ statement.
The syntax for creating a procedure:
CREATE OR REPLACE PROCEDURE procedure_name
IS|AS
// declare variables
BEGIN
…..
END;
Parameter modes in procedure:
IN:
It is a default mode. We cannot assign any values because it is a read-only parameter.
OUT:
It returns a value t the calling program. We can change its value.
IN OUT:
The initial value is passed to a subprogram and the updated value is returned to the caller.
The keywords in and out indicate parameters have values assigned to them and parameters return results respectively.
Example
DECLARE
a number;
b number;
c number;
PROCEDURE findmul (u IN number, w IN number, v OUT number)
IS
BEGIN
z:=x*y;
END;
BEGIN
a:=50;
b:=10;
findmul(a,b,c)
dbms_output.put_line('Multiplication is'||c);
END;
EXECUTED findmul;
Output:
If we have the same name for one or more procedures, the SQL permits it. The name and the arguments are used for identifying procedures.
SQL also permits the functions with the same function name. so long as the different functions with the same name either have different numbers of arguments or for functions with the same number of arguments, but at least one argument is different.
Also, read Consider the following directed graph G as shown in Figure 2.