PL/SQL Records
DECLARE
TYPE t_EMP IS RECORD
( V_EMP_id employees.employee_id%type,
v_first_name employees.first_name%type,
v_last_name employees.last_name%type
);
v_emp t_EMP;
BEGIN
select employee_id ,first_name ,last_name
into v_emp.V_EMP_id, v_emp.v_first_name,v_emp.v_last_name
from
employees
where employee_id=100;
dbms_output.put_line(v_emp.V_EMP_id||' '||v_emp.v_first_name||' '||v_emp.v_last_name);
END;
------------------------------------------------------------------------------------------------------
DECLARE
TYPE t_EMP IS RECORD
( V_EMP_id employees.employee_id%type,
v_first_name employees.first_name%type,
v_last_name employees.last_name%type
);
v_emp t_EMP;
BEGIN
select employee_id ,first_name,last_name
into v_emp
from employees
where employee_id=100;
dbms_output.put_line(v_emp.V_EMP_id||' '||v_emp.v_first_name||' '||v_emp.v_last_name);
END;
-------------------------------------------------------------------------------------------------------
DECLARE
TYPE t_EMP IS RECORD
( V_EMP_id employees.employee_id%type,
v_first_name employees.first_name%type,
v_last_name employees.last_name%type
);
v_emp t_EMP;
BEGIN
for i in 100..103
loop
select employee_id ,first_name ,last_name
into v_emp
from
employees
where employee_id=i;
dbms_output.put_line(v_emp.V_EMP_id||' '||v_emp.v_first_name||' '||v_emp.v_last_name);
end loop;
END;
PL/SQL Records: %ROWTYPE method
select * from DEPARTMENTS where department_id=10;
create table copy_DEPARTMENTS as select * from DEPARTMENTS where 1=2;
select * from copy_DEPARTMENTS;
------------------------------
declare
type t_dept is record
( v_dept_id departments.department_id%type,
v_dept_name departments.department_name%type,
v_dept_manager departments.manager_id%type,
v_dept_loc departments.location_id%type
);
v_dept t_dept;
begin
select department_id,department_name,manager_id,location_id
into v_dept
from DEPARTMENTS where department_id=10;
insert into copy_DEPARTMENTS values v_dept;
/*
insert into copy_DEPARTMENTS values (v_dept.v_dept_id,v_dept.v_dept_name,.....
*/
end;
select * from copy_DEPARTMENTS;
---------------------
--using the %rowtype
declare
v_dept DEPARTMENTS%rowtype;
begin
select department_id,department_name,manager_id,location_id
into v_dept
from DEPARTMENTS where department_id=10;
insert into copy_DEPARTMENTS values v_dept;
/*
insert into copy_DEPARTMENTS values (v_dept.department_id,v_dept.department_name,.....
*/
end;
select * from copy_DEPARTMENTS;
----------------------------------------------------
--using the %rowtype in update
declare
v_dept DEPARTMENTS%rowtype;
begin
v_dept.department_id:=10;
v_dept.department_name:='test';
update copy_DEPARTMENTS
set row=v_dept;
end;
select * from copy_DEPARTMENTS;
------------------------------------------------------------------
Nested PL/SQL Records
drop table emp_tel;
create table emp_tel
(emp_id number primary key,
full_name varchar2(100),
mob1 varchar2(20),
mob2 varchar2(20),
landline varchar2(20)
)
insert into emp_tel values
(1,'khaled alkhudari','+971505914999','+97125914777','+971508105499');
select * from emp_tel
declare
type t_tel is record
( v_mob1 emp_tel.mob1%type,
v_mob2 emp_tel.mob2%type,
v_landline emp_tel.landline%type
);
type t_emp_tel is record
(v_emp_id emp_tel.emp_id%type,
v_full_name emp_tel.full_name%type,
v_tel t_tel
);
v_rec t_emp_tel;
begin
select emp_id, full_name,
mob1,mob2,landline
into v_rec.v_emp_id,v_rec.v_full_name,
v_rec.v_tel.v_mob1, v_rec.v_tel.v_mob2, v_rec.v_tel.v_landline
from emp_tel
where emp_id=1;
DBMS_OUTPUT.PUT_LINE( 'emp_id:'||v_rec.v_emp_id);
DBMS_OUTPUT.PUT_LINE('landline:'||v_rec.v_tel.v_landline);
end;
INDEX BY tables (Associative arrays)
declare
type tab_no is table of varchar2(100)
index by pls_integer;
v_tab_no tab_no;
begin
v_tab_no(1):='khaled';
v_tab_no(6):='ahmed';
v_tab_no(4):='jad';
dbms_output.put_line(v_tab_no(1));
dbms_output.put_line(v_tab_no(6));
dbms_output.put_line(v_tab_no(4));
end;
-------------------------
declare
type tab_no is table of number
index by varchar2(100);
v_tab_no tab_no;
begin
v_tab_no('khaled'):=1;
v_tab_no('ahmed'):=6;
v_tab_no('jad'):=4;
dbms_output.put_line(v_tab_no('khaled'));
dbms_output.put_line(v_tab_no('ahmed'));
dbms_output.put_line(v_tab_no('jad'));
end;
--------------------------
declare
type tab_emp is table of varchar2(100)
index by pls_integer;
v_tab_emp tab_emp;
v_name varchar2(100);
begin
for i in 100..110
loop
select first_name||' '||last_name into v_name
from employees
where employee_id=i;
v_tab_emp(i):=v_name;
end loop;
for i in 100..110
loop
dbms_output.put_line( v_tab_emp(i));
end loop;
end;
INDEX BY tables Methods
declare
type tab_no is table of varchar2(100)
index by pls_integer;
v_tab_no tab_no;
begin
v_tab_no(1):='khaled';
v_tab_no(6):='ahmed';
v_tab_no(4):='jad';
dbms_output.put_line(v_tab_no(1));
dbms_output.put_line(v_tab_no(6));
dbms_output.put_line(v_tab_no(4));
end;
-------------------------
declare
type tab_no is table of number
index by varchar2(100);
v_tab_no tab_no;
begin
v_tab_no('khaled'):=1;
v_tab_no('ahmed'):=6;
v_tab_no('jad'):=4;
dbms_output.put_line(v_tab_no('khaled'));
dbms_output.put_line(v_tab_no('ahmed'));
dbms_output.put_line(v_tab_no('jad'));
end;
--------------------------
declare
type tab_emp is table of varchar2(100)
index by pls_integer;
v_tab_emp tab_emp;
v_name varchar2(100);
begin
for i in 100..110
loop
select first_name||' '||last_name into v_name
from employees
where employee_id=i;
v_tab_emp(i):=v_name;
end loop;
for i in 100..110
loop
dbms_output.put_line( v_tab_emp(i));
end loop;
end;
INDEX BY tables of Records
declare
type tab_no is table of varchar2(100)
index by pls_integer;
v_tab_no tab_no;
v_total number;
begin
v_tab_no(1):='khaled';
v_tab_no(2):='ahmed';
v_tab_no(3):='jad';
v_tab_no(6):='nader';
v_tab_no(5):='joan';
for i in 1..10
loop
if v_tab_no.exists(i) then
dbms_output.put_line('the element '||i||' is exist in the array and='||v_tab_no(i));
else
dbms_output.put_line('the element '||i||' is not exist in the array');
end if;
end loop;
v_total:=v_tab_no.count;
dbms_output.put_line('the total elements in the array='||v_total);
dbms_output.put_line('the first element INDEX in the array='||v_tab_no.first);
dbms_output.put_line('the NEXT element INDEX AFTER INDEX 3='||v_tab_no.NEXT(3));
end;
nested table
declare
type tab_no is table of employees%rowtype
index by pls_integer;
v_tab_no tab_no;
v_total number;
begin
v_tab_no(1).employee_id:=1;
v_tab_no(1).first_name:='ahmed';
v_tab_no(1).last_name:='jad';
v_tab_no(2).employee_id:=2;
v_tab_no(2).first_name:='khaled';
v_tab_no(2).last_name:='yaser';
dbms_output.put_line(v_tab_no(1).employee_id||' '||v_tab_no(1).first_name||' '||v_tab_no(1).last_name);
dbms_output.put_line(v_tab_no(2).employee_id||' '||v_tab_no(2).first_name||' '||v_tab_no(2).last_name);
end;
----------------------------
declare
type tab_no is table of employees%rowtype
index by pls_integer;
v_tab_no tab_no;
begin
for i in 100..104
loop
select * into v_tab_no(i)
from
employees
where employee_id=i;
dbms_output.put_line(v_tab_no(i).employee_id||' '||
v_tab_no(i).first_name||' '||v_tab_no(i).last_name
);
end loop;
end;
-------------------------------------
declare
type tab_no is table of employees%rowtype
index by pls_integer;
v_tab_no tab_no;
begin
for i in 100..104
loop
select * into v_tab_no(i)
from
employees
where employee_id=i;
end loop;
for i in v_tab_no.first..v_tab_no.last
loop
dbms_output.put_line(v_tab_no(i).employee_id);
dbms_output.put_line(v_tab_no(i).first_name);
dbms_output.put_line(v_tab_no(i).last_name);
dbms_output.put_line(v_tab_no(i).salary);
end loop;
end;
-----------------------------------------------------------------------
declare
type t_locations is table of varchar2(100);
loc t_locations;
begin
loc:=t_locations('USA','UK','JORDAN');
dbms_output.put_line(loc(1) );
dbms_output.put_line(loc(2) );
dbms_output.put_line(loc(3) );
end;
-----------------
declare
type t_locations is table of varchar2(100);
loc t_locations;
begin
loc:=t_locations('USA','UK','JORDAN');
for i in loc.first..loc.last
loop
dbms_output.put_line(loc(i) );
end loop;
end;
-----------------
declare
type t_locations is table of varchar2(100);
loc t_locations;
begin
loc:=t_locations('USA','UK','JORDAN');
loc.extend;
loc(4):='Lebanon';
dbms_output.put_line(loc(1) );
dbms_output.put_line(loc(2) );
dbms_output.put_line(loc(3) );
dbms_output.put_line(loc(4) );
end;
---------------------
declare
type t_locations is table of varchar2(100);
loc t_locations;
begin
loc:=t_locations('jordan','uae','Syria');
loc.delete(2);
loc(2):='Iraq';
dbms_output.put_line(loc(1) );
dbms_output.put_line(loc(2) );
dbms_output.put_line(loc(3) );
end;
------------------------------------------------------------------------
--how to use is in SQL
drop table x_customer;
drop type t_tel;
create or replace type t_tel as table of number;
create table x_customer
( cust_id number,
cust_name varchar2(100),
tel t_tel
)
nested table tel store as t_tel_tbl;
insert into x_customer (cust_id,cust_name,tel)
values (1,'khaled',t_tel(050121,0501245,0589877));
select * from x_customer;
Varray
declare
type t_locations is varray(3) of varchar2(100);
loc t_locations;
begin
loc:=t_locations('jordan','uae','Syria');
dbms_output.put_line(loc(1) );
dbms_output.put_line(loc(2) );
dbms_output.put_line(loc(3) );
end;
-----------------------------------------------
--you can not extend the varray, it will give error
declare
type t_locations is varray(3) of varchar2(100);
loc t_locations;
begin
loc:=t_locations('jordan','uae','Syria');
loc.extend;
loc(4):='aa';
dbms_output.put_line(loc(1) );
dbms_output.put_line(loc(2) );
dbms_output.put_line(loc(3) );
end;
-------------
declare
type t_locations is varray(3) of varchar2(100);
loc t_locations;
begin
loc:=t_locations('jordan','uae','Syria');
loc.trim(1); -- this delete one element from last
dbms_output.put_line(loc(1) );
dbms_output.put_line(loc(2) );
---dbms_output.put_line(loc(3) );
end;
-----------------------------------------------
--how to use is in SQL
drop table x_customer;
drop type t_tel;
create or replace type t_tel as varray(10) of number;
create table x_customer
( cust_id number,
cust_name varchar2(100),
tel t_tel
)
--no need for this nested table tel store as t_tel_tbl;
insert into x_customer (cust_id,cust_name,tel)
values (1,'khaled',t_tel(050121,0501245,0589877));
select * from x_customer;
No comments:
Post a Comment