Use of %ROWTYPE

declare
country_record countries%rowtype;
begin
select *  into country_record from countries
where country_id='&countryid';
dbms_output.put_line('country id is   :  '||country_record.country_id
||'country name is   :  '||country_record.country_name
||'country region is   : '||country_record.region_id);
end;



declare
type dept_table_type is table of departments.department_name%type index by pls_integer;
my_dept_table dept_table_type;
loop_count number:=10;
deptno number:=0;
begin
for i in 1..loop_count loop
deptno:=deptno+10;
select department_name into my_dept_table(i) from departments where department_id=deptno;
dbms_output.put_line(my_dept_table(i));
end loop;
end;



declare
type dept_table_type is table of departments%rowtype index by pls_integer;
my_dept_table dept_table_type;
loop_count number:=10;
deptno number:=0;
begin
for i in 1..loop_count loop
deptno:=deptno+10;
select * into my_dept_table(i) from departments
where department_id=deptno;
dbms_output.put_line(my_dept_table(i).department_name);
end loop;
end;




DECLARE
    TYPE DEPT_TB_TY IS TABLE OF
DEPARTMENTS.DEPARTMENT_NAME%TYPE
INDEX BY PLS_INTEGER;
my_dept_table DEPT_TB_TY;
deptno number:=10;

begin
    select department_name
    INTO my_dept_table(10)
    from departments
    where department_id=deptno;
    dbms_output.put_line(my_dept_table(10));
end ;


DECLARE
    TYPE DEPT_TB_TY IS TABLE OF
DEPARTMENTS%rowTYPE
INDEX BY PLS_INTEGER;
my_dept_table DEPT_TB_TY;
deptno number:=0;

begin
    deptno:=deptno+10;
    select *
    INTO my_dept_table(10)
    from departments
    where department_id=deptno;
    dbms_output.put_line(my_dept_table(10).department_name);
end ;



declare
v_dept departments%rowtype;
loop_count number:=10;
num number:=0;
begin
for i in 1..loop_count loop
num:=num+10;
select * into v_dept from departments where department_id=num;
dbms_output.put_line(v_dept.department_name);
end loop;
end;



declare
v_emp employees%rowtype;
begin
select * into v_emp from employees where employee_id=180;
dbms_output.put_line(v_emp.last_name||'     '||v_emp.employee_id);
end;


declare
type emp_rec_type is record(empid number,last_name varchar2(30));
my_emp emp_rec_type;
begin
select employee_id,last_name into my_emp from employees
where employee_id=180;
dbms_output.put_line(my_emp.empid||'    '||my_emp.last_name);
end;


 declare
type emp_table_type is table of employees%rowtype index by pls_integer;
my_emp emp_table_type;
max_count number:=104;
begin
for i in 100..max_count loop
select * into my_emp(i) from employees
where employee_id=i;
dbms_output.put_line(my_emp(i).last_name);
end loop;
end;


DECLARE
   emp_id   employees.employee_id%TYPE := 100;
   emp_name employees.last_name%TYPE;
   wages    NUMBER(7,2);
BEGIN
   SELECT last_name, salary + (salary * nvl(commission_pct,0))
      INTO emp_name, wages FROM employees
      WHERE employee_id = emp_id;
   DBMS_OUTPUT.PUT_LINE('Employee ' || emp_name || ' might make ' || wages);
END;
/