Thursday, May 13, 2010

Oracle PL/SQL Notes - 2

General Syntax to create a procedure is:
CREATE [OR REPLACE] PROCEDURE proc_name [list of parameters]
Declaration section
Execution section
Exception section

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
cursor emp_cur is
select empno, ename from emp where sal>1500;
emp_rec emp_cur%rowtype;
open emp_cur;
fetch emp_cur into emp_rec;
exit when emp_cur%notfound;
dbms_output.put_line(emp_rec.empno|| ' :: ' ||emp_rec.ename);
end loop;
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.
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;
Return return_variable;
exception section
Return return_variable;
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,
This line displays the value returned by the function.

create or replace function myfunction
return varchar2 is
divide_by_zero_error exception;
raise divide_by_zero_error;
return 'hello world';
when divide_by_zero_error then
return 'zero error';

SQL> select myfunction from emp;

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.

No comments:

Post a Comment

Amazon Best Sellors

Weblogic - Server Creation - Issues

Datasource creation using python script.  Python Script was failing after creating Datasource. From UI Admin Console, if I try to activat...