1.
SET verify off
DECLARE
v_countryid VARCHAR2(20):= 'CA';
v_country_record countries%ROWTYPE;
BEGIN
SELECT *
INTO v_country_record
FROM countries
WHERE country_id=UPPER(v_countryid);

DBMS_OUTPUT.put_line('country id: '|| v_country_record.country_id||' region: '|| v_country_record.region_id);
END;


2.DECLARE
TYPE dept_table_type IS TABLE OF
departments.department_name%TYPE
INDEX BY PLS_INTEGER;
my_dept_table dept_table_type;
f_loop_count NUMBER(2):=10;
v_deptno NUMBER(4):=0;
BEGIN
FOR i IN 1..f_loop_count
LOOP
v_deptno:=v_deptno+10;
SELECT department_name
INTO my_dept_table(i)
FROM departments
WHERE department_id = v_deptno;
END LOOP;
FOR i IN 1..f_loop_count
LOOP
DBMS_OUTPUT.put_line(my_dept_table(i));
END LOOP;
END;

3.
DECLARE
TYPE dept_table_type IS TABLE OF departments%ROWTYPE
INDEX BY PLS_INTEGER;
my_dept_table dept_table_type;
f_loop_count NUMBER(2):=10;
v_deptno NUMBER(4):=0;
BEGIN
FOR i IN 1..f_loop_count
LOOP
v_deptno := v_deptno + 10;
SELECT *
INTO my_dept_table(i)
FROM departments
WHERE department_id = v_deptno;
END LOOP;
FOR i IN 1..f_loop_count
LOOP
DBMS_OUTPUT.put_line('Dept no: '||
my_dept_table(i).department_id ||' Manager id: '||
my_dept_table(i).manager_id
||' Location id: '|| my_dept_table(i).location_id);
END LOOP;
END;

4.DECLARE
TYPE table_ename IS TABLE OF employees.last_name%TYPE
INDEX BY BINARY_INTEGER;
TYPE table_dept IS TABLE OF employees.department_id%TYPE
INDEX BY BINARY_INTEGER;
tename table_ename;
tdept table_dept;
i BINARY_INTEGER := 0;
CURSOR namedept IS SELECT last_name,department_id FROM
employees WHERE employee_id < 115;
track NUMBER :=15;
BEGIN
FOR emprec IN namedept
LOOP
i := i+1;
tename(i) := emprec.last_name;
tdept(i):=emprec.department_id;
END LOOP;
FOR i IN 1..track
LOOP
DBMS_OUTPUT.put_line('employee name: '||tename(i)||
' department id: '||tdept(i));
END LOOP;
END;

5.
CREATE TYPE typ_item1 AS OBJECT
(prodid NUMBER(5), price NUMBER(7,2));
/
CREATE TYPE typ_item_nst1
AS TABLE OF typ_item;
/
CREATE TABLE porder1(
orderid NUMBER(5),
supplier NUMBER(5),
requester NUMBER(4),
ordered DATE,
items typ_item_nst)
nested TABLE items store AS item_stor_tab1;

6.
truncate TABLE pOrder;
BEGIN
INSERT INTO pOrder
(ordid, supplier, requester, ordered, items)
VALUES (1000, 12345, 9876, SYSDATE,
typ_item_nst(typ_item(99,129.00)));
END;
BEGIN
INSERT INTO pOrder
(ordid, supplier, requester, ordered, items)
VALUES (1001, 12346, 9877, sysdate-1, NULL);
UPDATE pOrder
SET items=typ_item_nst(typ_item(100,130.00))
WHERE ordid=1000;
END;
SELECT * FROM pOrder;
BEGIN
UPDATE pOrder SET
items=typ_item_nst(typ_item(100,130.00),typ_item(110, 140.00))
WHERE ordid=1000;
END;

SELECT * FROM pOrder;