Collections

for v_arrays
===============

declare
v_nest type_nest1:=type_nest1(1,2,3,4);
v_nest1 type_nest3:=type_nest3(1,1,1,1);
v_table pack_name.t_name:=pack_name.t_name(1,2,3,4);
begin
if v_nest.exists(4) then
--dbms_output.put_line('the count is'||' '||v_nest.limit);
--dbms_output.put_line('the count is'||' '||v_nest1.limit);
--dbms_output.put_line('the count is'||' '||v_nest1.next(2));
--dbms_output.put_line('the count is'||' '||v_nest1.prior(4));
--dbms_output.put_line('the count is'||' '||v_nest.limit);
v_nest.extend(6);
dbms_output.put_line('the v_nest is'||' '||v_nest.count);
dbms_output.put_line('the v_table is'||' '||v_table.count);
v_table.extend(6);
dbms_output.put_line('v_table'||' '||v_table.count);
v_table.extend(3,2);
dbms_output.put_line('the value is'||' '||v_table(10));
dbms_output.put_line('v_table'||' '||v_table.count);
for i in v_table.first..v_table.last loop
dbms_output.put_line(v_table(i));
end loop;
--v_nest1.delete(2);
--dbms_output.put_line('the count is'||' '||v_nest.count);
end if;
end;


pl sql table
=============

declare
type type_pl is table of varchar2(30) index by binary_integer;
v type_pl;
begin
select empno bulk collect into v from emp;
for i in v.first..v.last loop
dbms_output.put_line(v(i));
end loop;
end;

associate arry
==============

declare
type type_pl is table of varchar2(30); --index by binary_integer;
v type_pl;
begin
select empno bulk collect into v from emp;
for i in v.first..v.last loop
dbms_output.put_line(v(i));
end loop;
end;

varray
=======

create type type_var is varray(10) of varchar2(20)

create table tbl_varay(empno number,ename varchar2(10),phnum type_var)

insert into tbl_varay values(101,'lax',type_var('balu','balu1','balu2'))

select * from tbl_varay

nested table
=============

create type type_nest is table of varchar2(20)

create table tbl_nest(empno number,ename varchar2(20),phnum type_nest) nested table phnum store as pnum

insert into tbl_nest values(101,'lax',type_nest(101,101,102,103))

nested table
=============
create type type_nest is table of varchar2(20) ;
create table tbl_nest(empno number,ename varchar2(20),phnum type_nest) nested table phnum store as pnum ;
insert into tbl_nest values(101,'lax',type_nest(101,101,102,103)) ;
--select * from table(select phnum from tbl_nest) ;
 correct  ----> select * from table(select phnum from tbl_nest) ;
select * from tbl_nest ;
===================================================================================================
SQL> select * from table(select phnum from tbl_nest where empno='101');
COLUMN_VALUE
--------------------
101
101
102
103
o/p:
  1* select  * from tbl_nest where empno='101'
SQL> /
     EMPNO ENAME
---------- --------------------
PHNUM
------------------------------------------------------
       101 lax
TYPE_NEST('101', '101', '102', '103')
SQL> select * from pnum;
select * from pnum
              *
ERROR at line 1:
ORA-22812: cannot reference nested table column's storage table
==================================================================================================================

procedure with out perameter
============================
1.
===
create or replace procedure proc_sele(eno in number,psal out number,pdeptno out number)
as
begin
select sal,deptno into pdeptno,psal from emp where empno=eno;
display(psal||' '||pdeptno);
end;

exec proc_sele(7788,:n,:n)


2.
===
create or replace procedure pro_cpos_1(eno number,psal out number,errcode out varchar2,errmsg out varchar2)
is
n number;
begin
select sal into psal from emp where empno=eno;
exception
when no_data_found then
errcode:='err-001';
errmsg:='tha data not found'||sqlcode||' '||substr(sqlerrm,1,10);
display(errcode||' '||errmsg);
return;
end;

How to execute a PL/SQL Function?
----------------------------------

A function can be executed in the following ways.

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

Variable_name:=  Function_name;

If Variable_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 Function_name  FROM dual;

3) In a PL/SQL Statements like,

dbms_output.put_line(Function_name);

This line displays the value returned by the function.




USE OF PRAGMA IN PROCEDURES
===========================
create or replace procedure proc_pragma(p_empno number)
as
--pragma autonomous_transaction;
begin
delete from emp where empno=p_empno;
commit;
end;

begin
insert into emp(empno,deptno) values(314,20);
update emp set sal=sal+100 where empno=7788;
proc_pragma(313);
end;