Oracle PL/SQL Notes - 2

=============

Procedure

=============

General Syntax to create a procedure is:

CREATE [OR REPLACE] PROCEDURE proc_name [list of parameters]

IS

Declaration section

BEGIN

Execution section

EXCEPTION

Exception section

END;

IS - marks the beginning of the body of the procedure and is similar to DECLARE in anonymous PL/SQL Blocks. The code between IS and BEGIN forms the Declaration section.

create or replace procedure emp_proc

is

cursor emp_cur is

select empno, ename from emp where sal>1500;

emp_rec emp_cur%rowtype;

begin

open emp_cur;

loop

fetch emp_cur into emp_rec;

exit when emp_cur%notfound;

dbms_output.put_line(emp_rec.empno|| ' :: ' ||emp_rec.ename);

end loop;

end;

There are two ways to execute a procedure.

1) From the SQL prompt.

EXECUTE [or EXEC] procedure_name;

2) Within another procedure – simply use the procedure name.

procedure_name;

=================

PL/SQL Functions

=================

A function is a named PL/SQL Block which is similar to a procedure. The major difference between a procedure and a function is, a function must always return a value, but a procedure may or may not return a value.

The General Syntax to create a function is:

CREATE [OR REPLACE] FUNCTION function_name [parameters]

RETURN return_datatype;

IS

Declaration_section

BEGIN

Execution_section

Return return_variable;

EXCEPTION

exception section

Return return_variable;

END;

1) Return Type: The header section defines the return type of the function. The return datatype can be any of the oracle datatype like varchar, number etc.

2) The execution and exception section both should return a value which is of the datatype defined in the header section.

A function can be executed in the following ways.

1) Since a function returns a value we can assign it to a variable.

employee_name := employer_details_func;

If ‘employee_name’ is of datatype varchar we can store the name of the employee by assigning the return type of the function to it.

2) As a part of a SELECT statement

SELECT employer_details_func FROM dual;

3) In a PL/SQL Statements like,

dbms_output.put_line(employer_details_func);

This line displays the value returned by the function.

create or replace function myfunction

return varchar2 is

divide_by_zero_error exception;

begin

raise divide_by_zero_error;

return 'hello world';

exception

when divide_by_zero_error then

return 'zero error';

end;

SQL> select myfunction from emp;

MYFUNCTION

-------------------------------------

zero error

zero error

zero error

======================================

Parameters in Procedure and Functions

======================================

IN - CREATE [OR REPLACE] PROCEDURE proc_name ( param1 IN datatype)

This type of parameter is a read only parameter. We can assign the value of IN type parameter to a variable or use it in a query, but we cannot change its value inside the procedure.

OUT - CREATE [OR REPLACE] PROCEDURE proc2 (param_name OUT datatype)

This is a write-only parameter i.e, we cannot pass values to OUT paramters while executing the stored procedure, but we can assign values to OUT parameter inside the stored procedure and the calling program can recieve this output value.

IN OUT - CREATE [OR REPLACE] PROCEDURE proc3 (param_name IN OUT datatype)

By using IN OUT parameter we can pass values into a parameter and return a value to the calling program using the same parameter. But this is possible only if the value passed to the procedure and output value have a same datatype. This parameter is used if the value of the parameter will be changed in the procedure.

If a parameter is not explicitly defined a parameter type, then by default it is an IN type parameter.