Skip to main content

Differences ( Function, Procedure, Package )

1. A function is expected to return values and can be assigned to a variable
Example : substr() is a string function which is expected to return a value
Can be assigned to a variable:
var1 := substr('Susil Kumar',1,5);



Procedure cannot be assigned to a variable.


2. A function can be called inside a procedure. Reverse is not possible unless you use 'Execute Immediate' to generate dynamic code which is executed during runtime.

3. Global variables can only be declared in Pacakage

4. If you want to do overloading of functions or procedures, you should seal them in a package. If not, you cannot overload functions or procedures

5. Though functions allow insert statements in it, it throws error when you use the function in select statement. Procedure goes good with Insert statements.

Example:

SQL> create or replace function func_with_insert
2 return number
3 as
4 begin
5 insert into emp values('Name-xxxxx',4544);
6 return sql%rowcount;
7 commit;
8 end;
9 /

Function created.

SQL> declare
2 no_of_rows number;
3 begin
4 no_of_rows := func_with_insert();
5 dbms_output.put_line(no_of_rows||' rows inserted in EMP table');
6 end;
7 /
1 rows inserted in EMP table

PL/SQL procedure successfully completed.

SQL> select func_with_insert() from dual;
select func_with_insert() from dual
*
ERROR at line 1:
ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at "SUSIL.FUNC_WITH_INSERT", line 5

Comments

Post a Comment

Popular posts from this blog

Only native data types are supported in PLSQL programming

create or replace package check1 is procedure p1(a1 integer); procedure p1(a1 number); end check1; / create or replace package body check1 is procedure p1(a1 integer) as begin dbms_output.put_line('Using Integer'); end p1; procedure p1(a1 number) as begin dbms_output.put_line('Using Number'); end p1; end; / --The above code gets compiled successfully. --But when you try to execute it .. begin check1.p1(8779); end; --See what happens.............. This concludes ONLY different native datatypes can be used in PLSQL OO implementation. Happy learning!!!!!
2 comments
Read more

Editor's Column - Traditional DBMS

Editor's column There were days when Rooms-sized processors used for processing data. IT boomed up at the start of 21st Century and so many innumerable inventions made Computer world and few among them are still up in the markets for thier better implemented programming Alogrithms which couples with computer hardware and software ( OS ). Textpads or notepads were the repository of data and programmers had to be Experts to do data manipulation. In other words, Programmers had to use thier own logic to do insert , delete, update in data source. For instance, Consider the following: file1.txt: 1 I am line number 1 2 I am line number 2 3 I am line number 3 4 I am line number 4 5 I am line number 5 6 I am line number 6 7 I am line number 7 8 I am line number 8 9 I am line number 9 10 I am line number 10 To programmatically delete 8 th record , developer had to use the following logic with OLD file handling methods in 3rd generation language. 1. Open file1.txt, an...
Post a Comment
Read more

PLSQL programming ( Learn by examples - Part 3 )

/** In this section , lets learn the following. How to use a for loop to process multiple records... **/ set serveroutput on size 100000 begin --Select objects of table type that are valid under the schema Susil for var in (Select * from all_objects where object_name not like '%$%' and object_name in ('EMPLOYEE','EMP') and status='VALID' and owner='SUSIL') loop if var.object_type='TABLE' then /* Dbms_metadata package provides us a function get_ddl to get the data definition on object. For your understanding , i have just chosen two tables in this example If you remove Object_name condition in the above SQL , it will print DDL definitions for all tables under the specified schema **/ dbms_output.put_line('====================================================================='); dbms_output.put_line('Table name =>'||var.objec...
Post a Comment
Read more