Hi 1.What is difference between procedure and function?1. procedure may or may not return values whereas function must return value2. we can call the function in the sql statements whereas we can't call the procedureExcept these two, is there any difference between these two.2. What is pl/sql table and what is use of it. In which case, we can use it3. What is ref cursor, what is the use of it.4.how the function will return multiple values. Pls give me with examples

Manikandan

• Dec 7th, 2006

 

2) pl/sql table is a collection type also called index by table or associative arrays.

it stores a key,value pair, value is found by using the index (which is number or varchar).

and can be used only with Pl/sql and cannot be used as a column in table.

Initialised automatically.

3) ref cursor- accepts parameter and returns a record type or rowtype.

It is used to dynamically construct cursor based upon various criteria.

4)function will return multiple values

by using as many out parameters and only one return value.

function <funName>(p1 out <dtype>,p2 out <dtype>,..) return <datatype>

vaibhav Tyagi

• Sep 26th, 2007

 

The main difference is in Function you can perform only DDL
but in Stored
procedure you can perform DDL, DML both

ravgopal

 Profile Answers by ravgopal 

• Oct 10th, 2007

 

We can do either DDL or DML inside a function.

1) Let us try keeping a DML inside a function

  1  create or replace function test (a number) return number as
  2  begin
  3  insert into holiday values(sysdate,sysdate);
  4  return a;
  5* end;

SQL> /

Function created.

method1:

SQL> select test(56) from dual;
select test(56) from dual
       *
ERROR at line 1:
ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at "SCOTT.TEST", line 3


Method2:

SQL> select * from holiday;

H_DT      H_DESC
--------- ------------------
01-JAN-07 New Year
08-OCT-07 Columbus day

SQL> declare
  2  abc number:=0;
  3  begin
  4  abc := test1(56);
  5  dbms_output.put_line(abc);
  6  end;
  7  /
56

PL/SQL procedure successfully completed.

SQL> select * from holiday;

H_DT      H_DESC
--------- ------------------
01-JAN-07 New Year
08-OCT-07 Columbus day
10-OCT-07 10-OCT-07

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

2) Let us alter the function to have a DDL instead of DML

  1  create or replace function test (a number) return number as
  2  begin
  3  execute immediate 'drop table xyz';
  4  return a;
  5* end;

SQL> /
Function created.

Method1:

SQL> select test(56) from dual;
select test(56) from dual
       *
ERROR at line 1:
ORA-14552: cannot perform a DDL, commit or rollback inside a query or DML
ORA-06512: at "SCOTT.TEST", line 3


Method2:

SQL> create table xyz(a number);

Table created.

SQL> declare
  2  abc number;
  3  begin
  4  abc:=test(56);
  5  dbms_output.put_line(abc);
  6  end;
  7  /

56

PL/SQL procedure successfully completed.

SQL> desc xyz
ERROR:
ORA-04043: object xyz does not exist