Oracle : Procedures and Functions material

Oracle 9i : Procedures and Functions

This tutorial covers Developing Procedures and Functions, Creating a Procedure, Executing a Procedure, Creating a Function, Executing a Function, Passing Parameters - IN Parameters, OUT Parameters, IN OUT Parameters, Purity of a User-Defined Function and Positional and Named Notations.

www.allwalkin.blogspot.com
Oracle 9i : Procedures and Functions

PL/SQL subprograms

A subprogram is a named block of PL/SQL. There are two types of subprograms in PL/SQL namely Procedures and Functions. Every subprogram will have a declarative part, an executable part or body, and an exception handling part, which is optional.

Declarative part contains variable declarations. Body of a subprogram contains executable statements of SQL and PL/SQL. Statements to handle exceptions are written in exception part.

When client executes a procedure are function, the processing is done in the server. This reduces network traffic. The subprograms are compiled and stored in the Oracle database as stored programs and can be invoked whenever required. As they are stored in compiled form when called they only need to be executed. Hence they save time needed for compilation.

Subprograms provide the following advantages

1. They allow you to write PL/SQL program that meet our need
2. They allow you to break the program into manageable modules.
3. They provide reusability and maintainability for the code.

Procedures

Procedure is a subprogram used to perform a specific action. A procedure contains two parts specification and the body. Procedure specification begins with CREATE and ends with procedure name or parameters list. Procedures that do not take parameters are written without a parenthesis. The body of the procedure starts after the keyword IS or AS and ends with keyword END.



In the above given syntax things enclosed in between angular brackets (“< > “) are user defined and those enclosed in square brackets (“[ ]”) are optional.

OR REPLACE is used to overwrite the procedure with the same name if there is any.

AUTHID clause is used to decide whether the procedure should execute with invoker (current-user or person who executes it) or with definer (owner or person created) rights

Example
www.allwalkin.blogspot.com
CREATE PROCEDURE MyProc (ENO NUMBER)
AUTHID DEFINER AS
BEGIN
DELETE FROM EMP
WHERE EMPNO= ENO;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(‘No employee with this number’);
END;

Let us assume that above procedure is created in SCOTT schema (SCOTT user area) and say is executed by user SEENU. It will delete rows from the table EMP owned by SCOTT, but not from the EMP owned by SEENU. It is possible to use a procedure owned by one user on tables owned by other users. It is possible by setting invoker-rights

AUTHID CURRENT_USER

PRAGMA AUTONOMOUS_TRANSACTION is used to instruct the compiler to treat the procedure as autonomous. i.e. commit or rollback the changes made by the procedure.

Parameter Modes

Parameters are used to pass the values to the procedure being called. There are 3 modes to be used with parameters based on their usage. IN, OUT, and IN OUT.

IN mode parameter used to pass the values to the called procedure. Inside the program IN parameter acts like a constant. i.e it cannot be modified.

OUT mode parameter allows you to return the value from the procedure. Inside Procedure the OUT parameter acts like an uninitialized variable. Therefore its value cannot be assigned to another variable.

IN OUT mode parameter allows you to both pass to and return values from the subprogram. Default mode of an argument is IN.

POSITIONAL vs. NOTATIONAL parameters

A procedure can be communicated by passing parameters to it. The parameters passed to a procedure may follow either positional notation or named notation.

Example
www.allwalkin.blogspot.com
If a procedure is defined as GROSS (ESAL NUMBER, ECOM NUMBER)
If we call this procedure as GROSS (ESA, ECO) then parameters used are called positional parameters. For Notational Parameters we use the following syntax
GROSS (ECOM => ECO, ESAL => ESA)



A procedure can also be executed by invoking it as an executable statement as shown below.

BEGIN
PROC1; --- PROC1 is name of the procedure.
END;
/


Next Page: Tutorial 13: Oracle 9i : Procedures and Functions - Page 2


Read Next: Tutorial 14: Oracle 9i : Database Triggers



Related Topics
Oracle VM Manager 2.1.2 Book Review
Oracle Utilities - Summary
Oracle Utilities - Dynamically Changing the External Reference
Oracle Utilities - Mapping XML files as External Tables
Oracle Utilities - Reading the listener.log from the database



Comments

todeardennnis said:
[EMAIL]psdchak@yahoo.com[/EMAIL]
March 26, 2005, 12:52 pm
ambuj_shukla said:
hi all,
please send me anser what is difference b/w truncate and delete command and exaMPLE
August 18, 2005, 6:37 am
Raghvendra Pratap Singh said:
HI ambuj
1. Delete is DML command. after which u can rollback.But Truncate is DDL command after which it do commit.
2.In delete u can give some condition so it will delete not all row. But in truncate there is no such option.
eg delete from emp where eno=134
it will delete that row which have eno 134. To delete all row u can write
delete from emp // But it is not permanent
truncate from emp;// it delete all rows and its deletion is permanent
August 16, 2006, 8:53 am
raj soni said:
truncate is a ddl and delete is a dml command
tru delete always segment,not a record so that u can't rollback
while delete deletes row and u can rollback
because after trun autocommit occur
October 11, 2006, 2:04 am
jayshree said:
can a function return more than one value at a time?
September 21, 2007, 4:55 am
TR said:
'NO'........FUNCTION CANT RETURN MORE THAN ONE VALUE AT A TIME.
BY USING "OUT PARAMETER" IN THE PROCEDURE WE CAN RETURN MORE THAN ONE VALUE AT A TIME.
October 4, 2007, 12:13 am
rajeshk said:www.allwalkin.blogspot.com
how can i fetch a value from an already existing table, and execute an operation in my procedure using select statement
December 6, 2007, 2:17 am
srikanth said:
what is the use of p-code?where we can use please give me any example for easy understan?
October 23, 2008, 4:57 am
srikanth said:
what is difference between IS/AS in procedure and function in oracle9i
October 23, 2008, 5:02 am
kulbhushan said:
difine diff b/w in out inout parmeters with example and also return type , boc students are not able to understand wht is basic diff b/w them
November 5, 2008, 12:56 am
prasan said:
please tell me how to execute pl sql block....

declare
a number;
b number;
c number;
begin
a:=&a;
b:=&b;
c:=a+b;
dbms_output.put_line('Sum of ' || a || ' and ' || b || ' is ' || c);

Here to take user input at runtime.....but its giving error...
November 26, 2008, 5:23 am
Ajay said:
how can i call a parameterised procedure in program?plz explain with example.
December 3, 2008, 8:56 am
Ashok Kumar said:
can any one help me to rectify errors in this package


create or replace package pack1 as
FUNCTION fun1(dno number) return number;
PROCEDURE PR1(eno number);
end;





www.allwalkin.blogspot.com
CREATE OR REPLACE package body pack1 as
FUNCTION fun1(dno number) return number as
lag ask_employees.department_id%type;
begin
select department_id into lag from ask_employees where department_id=dno;
return lag;
end;

PROCEDURE PR1(eno number)as
begin
insert into eem values(eno);
dbms_output.PUT_LINE('Data Inserted');
end if;
end;
end;

declare
empno ask_employees.employee_id%type:=&no;
depno ask_employees.department_id%type:=&deno;
sag ask_employees.department_id%type;
begin
sag:=pack1.fun1(depno);
if sag is null then
dbms_output.PUT_LINE('not a valid department number');
else
pack1.PR1(empno)
end if;
end;
December 16, 2008, 2:34 am
Rakesh arya said:
can i use select statment in procdure if yes plzzzzzz give me ex.
January 17, 2009, 8:22 am
Prajeesh said:
Good one.. It will be more useful if add function explanation, procedure vs function and some more examples. :)
January 19, 2009, 10:01 pm
vinay_kewalrama said:
Can you me Tell me How to alter Procedure In Oracle ?
Cause i don't want to Replace it..
January 22, 2009, 3:47 am
shantha murthy s said:
what is shared sql and dyanamic sql?
February 3, 2009, 1:28 pm
0 said:
all DDL cmds are internally commit cmds. There is no need to execute commit cmd after execution of DDL cmds.
February 10, 2009, 1:03 am
lakshmi said:www.allwalkin.blogspot.com
hi prasan u didn't end u r pl/sql block
February 10, 2009, 1:11 am
lakshmi said:
hi ajay u can call the parameterised procedure
exec procedurename(parameterlist);
February 10, 2009, 1:15 am
Srikanth said:
SQL> desc emp_test;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)

SQL> CREATE OR REPLACE PROCEDURE pro_delete_rows (
2 p_table_name IN VARCHAR2,
3 p_deleted_rows OUT NUMBER
4 )
5 IS
6 BEGIN
7 EXECUTE IMMEDIATE 'delete from' || p_table_name;
8
9 p_deleted_rows := SQL%ROWCOUNT;
10 END ;
11 /

Procedure created.

SQL> variable deleted number
SQL> execute pro_delete_rows('EMP_TEST',:deleted)
BEGIN pro_delete_rows('EMP_TEST',:deleted); END;

*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "SCOTT.PRO_DELETE_ROWS", line 7
ORA-06512: at line 1


SQL> print deleted

DELETED
----------www.allwalkin.blogspot.com