Friday, 4 April 2014

PL/SQL - Collections (Part -2)

 Contd., Part-1

--Example 5–25 Printing NESTED TABLE WITH FIRST AND LAST IN FOR LOOP

DECLARE
   TYPE team_type IS TABLE OF VARCHAR2 (15);

   team   team_type;

   PROCEDURE print_team (heading VARCHAR2)
   IS
   BEGIN
      DBMS_OUTPUT.PUT_LINE (heading);

      IF team IS NULL
      THEN
         DBMS_OUTPUT.PUT_LINE ('Does not exist');
      ELSIF team.FIRST IS NULL
      THEN
         DBMS_OUTPUT.PUT_LINE ('Has no members');
      ELSE
         FOR i IN team.FIRST .. team.LAST
         LOOP
            DBMS_OUTPUT.PUT (i || '. ');

            IF team.EXISTS (i)
            THEN
               DBMS_OUTPUT.PUT_LINE (team (i));
            ELSE
               DBMS_OUTPUT.PUT_LINE ('(to be hired)');
            END IF;
         END LOOP;
      END IF;

      DBMS_OUTPUT.PUT_LINE ('---');
   END;
BEGIN
   print_team ('Team Status:');
   team := team_type ();               -- Team is funded, but nobody is on it.
   print_team ('Team Status:');
   team :=
      team_type ('Arun',
                 'Amitha',
                 'Allan',
                 'Mae');                                       -- Add members.
   print_team ('Initial Team:');
   team.DELETE (2, 3);                          -- Remove 2nd and 3rd members.
   print_team ('Current Team:');
END;
/


--Example 5–26 COUNT AND LAST VALUES FOR VARRAY

DECLARE
   TYPE NumList IS VARRAY (10) OF INTEGER;

   n   NumList
          := NumList (1,
                      3,
                      5,
                      7);

   PROCEDURE print_count_and_last
   IS
   BEGIN
      DBMS_OUTPUT.PUT ('n.COUNT = ' || n.COUNT || ', ');
      DBMS_OUTPUT.PUT_LINE ('n.LAST = ' || n.LAST);
   END print_count_and_last;
BEGIN
   print_count_and_last;
   n.EXTEND (3);
   print_count_and_last;
   n.TRIM (5);
   print_count_and_last;
END;
/



--Example 5–27 COUNT AND LAST VALUES FOR NESTED TABLE

DECLARE
   TYPE NumList IS TABLE OF INTEGER;

   n   NumList
          := NumList (1,
                      3,
                      5,
                      7);

   PROCEDURE print_count_and_last
   IS
   BEGIN
      DBMS_OUTPUT.PUT ('n.COUNT = ' || n.COUNT || ', ');
      DBMS_OUTPUT.PUT_LINE ('n.LAST = ' || n.LAST);
   END print_count_and_last;
BEGIN
   print_count_and_last;
   n.DELETE (3);                                       -- Delete third element
   print_count_and_last;
   n.EXTEND (2);                               -- Add two null elements to end
   print_count_and_last;

   FOR i IN 1 .. 8
   LOOP
      IF n.EXISTS (i)
      THEN
         IF n (i) IS NOT NULL
         THEN
            DBMS_OUTPUT.PUT_LINE ('n(' || i || ') = ' || n (i));
         ELSE
            DBMS_OUTPUT.PUT_LINE ('n(' || i || ') = NULL');
         END IF;
      ELSE
         DBMS_OUTPUT.PUT_LINE ('n(' || i || ') does not exist');
      END IF;
   END LOOP;
END;
/



--Example 5–28 LIMIT AND COUNT VALUES FOR Different Collection TYPES

DECLARE
   TYPE aa_type IS TABLE OF INTEGER
                      INDEX BY PLS_INTEGER;

   aa   aa_type;                                          -- associative array

   TYPE va_type IS VARRAY (4) OF INTEGER;

   va   va_type := va_type (2, 4);                                   -- varray

   TYPE nt_type IS TABLE OF INTEGER;

   nt   nt_type := nt_type (1, 3, 5);                          -- nested table
BEGIN
   aa (1) := 3;
   aa (2) := 6;
   aa (3) := 9;
   aa (4) := 12;
   DBMS_OUTPUT.PUT ('aa.COUNT = ');
   DBMS_OUTPUT.PUT_LINE (NVL (TO_CHAR (aa.COUNT), 'NULL'));
   DBMS_OUTPUT.PUT ('aa.LIMIT = ');
   DBMS_OUTPUT.PUT_LINE (NVL (TO_CHAR (aa.LIMIT), 'NULL'));
   DBMS_OUTPUT.PUT ('va.COUNT = ');
   DBMS_OUTPUT.PUT_LINE (NVL (TO_CHAR (va.COUNT), 'NULL'));
   DBMS_OUTPUT.PUT ('va.LIMIT = ');
   DBMS_OUTPUT.PUT_LINE (NVL (TO_CHAR (va.LIMIT), 'NULL'));
   DBMS_OUTPUT.PUT ('nt.COUNT = ');
   DBMS_OUTPUT.PUT_LINE (NVL (TO_CHAR (nt.COUNT), 'NULL'));
   DBMS_OUTPUT.PUT ('nt.LIMIT = ');
   DBMS_OUTPUT.PUT_LINE (NVL (TO_CHAR (nt.LIMIT), 'NULL'));
END;
/


--Example 5–29 PRIOR AND NEXT Methods

DECLARE
   TYPE nt_type IS TABLE OF NUMBER;

   nt   nt_type
           := nt_type (18,
                       NULL,
                       36,
                       45,
                       54,
                       63);
BEGIN
   nt.DELETE (4);
   DBMS_OUTPUT.PUT_LINE ('nt(4) was deleted.');

   FOR i IN 1 .. 7
   LOOP
      DBMS_OUTPUT.PUT ('nt.PRIOR(' || i || ') = ');
      DBMS_OUTPUT.PUT_LINE (NVL (TO_CHAR (nt.PRIOR (i)), 'NULL'));
      DBMS_OUTPUT.PUT ('nt.NEXT(' || i || ') = ');
      DBMS_OUTPUT.PUT_LINE (NVL (TO_CHAR (nt.NEXT (i)), 'NULL'));
   END LOOP;
END;
/


--Example 5–30 Printing Elements OF Sparse NESTED TABLE

DECLARE
   TYPE NumList IS TABLE OF NUMBER;

   n     NumList
            := NumList (1,
                        2,
                        NULL,
                        NULL,
                        5,
                        NULL,
                        7,
                        8,
                        9,
                        NULL);
   idx   INTEGER;
BEGIN
   DBMS_OUTPUT.PUT_LINE ('First to last:');
   idx := n.FIRST;

   WHILE idx IS NOT NULL
   LOOP
      DBMS_OUTPUT.PUT ('n(' || idx || ') = ');
      DBMS_OUTPUT.PUT_LINE (NVL (TO_CHAR (n (idx)), 'NULL'));
      idx := n.NEXT (idx);
   END LOOP;

   DBMS_OUTPUT.PUT_LINE ('--------------');
   DBMS_OUTPUT.PUT_LINE ('Last to first:');
   idx := n.LAST;

   WHILE idx IS NOT NULL
   LOOP
      DBMS_OUTPUT.PUT ('n(' || idx || ') = ');
      DBMS_OUTPUT.PUT_LINE (NVL (TO_CHAR (n (idx)), 'NULL'));
      idx := n.PRIOR (idx);
   END LOOP;
END;
/



--Example 5–31 Identically Defined PACKAGE AND LOCAL Collection TYPES

CREATE OR REPLACE PACKAGE pkg
AS
   TYPE NumList IS TABLE OF NUMBER;

   PROCEDURE print_numlist (nums NumList);
END pkg;
/

CREATE OR REPLACE PACKAGE BODY pkg
AS
   PROCEDURE print_numlist (nums NumList)
   IS
   BEGIN
      FOR i IN nums.FIRST .. nums.LAST
      LOOP
         DBMS_OUTPUT.PUT_LINE (nums (i));
      END LOOP;
   END;
END pkg;
/

DECLARE
   TYPE NumList IS TABLE OF NUMBER;    -- local type identical to package type

   n1   pkg.NumList := pkg.NumList (2, 4);                     -- package type
   n2   NumList := NumList (6, 8);                               -- local type
BEGIN
   pkg.print_numlist (n1);                                         -- succeeds
   pkg.print_numlist (n2);                                            -- fails
END;
/



----Example 5–32 Identically Defined PACKAGE AND STANDALONE Collection TYPES

CREATE OR REPLACE TYPE NumList IS TABLE OF NUMBER;
-- standalone collection type identical to package type
/

DECLARE
   n1   pkg.NumList := pkg.NumList (2, 4);                     -- package type
   n2   NumList := NumList (6, 8);                          -- standalone type
BEGIN
   pkg.print_numlist (n1);                                         -- succeeds
   pkg.print_numlist (n2);                                            -- fails
END;
/



--Example 5–33 Declaring RECORD CONSTANT

CREATE OR REPLACE PACKAGE My_Types
   AUTHID CURRENT_USER
IS
   TYPE My_Rec IS RECORD
   (
      a   NUMBER,
      b   NUMBER
   );

   FUNCTION Init_My_Rec
      RETURN My_Rec;
END My_Types;
/

CREATE OR REPLACE PACKAGE BODY My_Types
IS
   FUNCTION Init_My_Rec
      RETURN My_Rec
   IS
      Rec   My_Rec;
   BEGIN
      Rec.a := 0;
      Rec.b := 1;
      RETURN Rec;
   END Init_My_Rec;
END My_Types;
/

DECLARE
   r   CONSTANT My_Types.My_Rec := My_Types.Init_My_Rec ();
BEGIN
   DBMS_OUTPUT.PUT_LINE ('r.a = ' || r.a);
   DBMS_OUTPUT.PUT_LINE ('r.b = ' || r.b);
END;
/


--Example 5–34 RECORD TYPE Definition AND VARIABLE Declaration

DECLARE
   TYPE DeptRecTyp IS RECORD
   (
      dept_id     NUMBER (4) NOT NULL:= 10,
      dept_name   VARCHAR2 (30) NOT NULL:= 'Administration',
      mgr_id      NUMBER (6):= 200,
      loc_id      NUMBER (4):= 1700
   );

   dept_rec   DeptRecTyp;
BEGIN
   DBMS_OUTPUT.PUT_LINE ('dept_id: ' || dept_rec.dept_id);
   DBMS_OUTPUT.PUT_LINE ('dept_name: ' || dept_rec.dept_name);
   DBMS_OUTPUT.PUT_LINE ('mgr_id: ' || dept_rec.mgr_id);
   DBMS_OUTPUT.PUT_LINE ('loc_id: ' || dept_rec.loc_id);
END;
/


--Example 5–35 RECORD TYPE WITH RECORD FIELD (NESTED RECORD)

DECLARE
   TYPE name_rec IS RECORD
   (
      FIRST   employees.first_name%TYPE,
      LAST    employees.last_name%TYPE
   );

   TYPE contact IS RECORD
   (
      name    name_rec,                                       -- nested record
      phone   employees.phone_number%TYPE
   );

   friend   contact;
BEGIN
   friend.name.FIRST := 'John';
   friend.name.LAST := 'Smith';
   friend.phone := '1-650-555-1234';
   DBMS_OUTPUT.PUT_LINE (
      friend.name.FIRST || ' ' || friend.name.LAST || ', ' || friend.phone);
END;
/


--Example 5–36 RECORD TYPE WITH VARRAY FIELD

DECLARE
   TYPE full_name IS VARRAY (2) OF VARCHAR2 (20);

   TYPE contact IS RECORD
   (
      name    full_name:= full_name ('John', 'Smith'),         -- varray field
      phone   employees.phone_number%TYPE
   );

   friend   contact;
BEGIN
   friend.phone := '1-650-555-1234';
   DBMS_OUTPUT.PUT_LINE (
      friend.name (1) || ' ' || friend.name (2) || ', ' || friend.phone);
END;
/



--Example 5–37 Identically Defined PACKAGE AND LOCAL RECORD TYPES

CREATE OR REPLACE PACKAGE pkg
AS
   TYPE rec_type IS RECORD
   (                                                    -- package RECORD type
      f1   INTEGER,
      f2   VARCHAR2 (4)
   );

   PROCEDURE print_rec_type (rec rec_type);
END pkg;
/

CREATE OR REPLACE PACKAGE BODY pkg
AS
   PROCEDURE print_rec_type (rec rec_type)
   IS
   BEGIN
      DBMS_OUTPUT.PUT_LINE (rec.f1);
      DBMS_OUTPUT.PUT_LINE (rec.f2);
   END;
END pkg;
/

DECLARE
   TYPE rec_type IS RECORD
   (                                                      -- local RECORD type
      f1   INTEGER,
      f2   VARCHAR2 (4)
   );

   r1   pkg.rec_type;                                          -- package type
   r2   rec_type;                                                -- local type
BEGIN
   r1.f1 := 10;
   r1.f2 := 'abcd';
   r2.f1 := 25;
   r2.f2 := 'wxyz';
   pkg.print_rec_type (r1);                                        -- succeeds
   pkg.print_rec_type (r2);                                           -- fails
END;
/



--Example 5–38 %ROWTYPE VARIABLE Represents FULL DATABASE TABLE ROW

DECLARE
   dept_rec   departments%ROWTYPE;
BEGIN
   -- Assign values to fields:
   dept_rec.department_id := 10;
   dept_rec.department_name := 'Administration';
   dept_rec.manager_id := 200;
   dept_rec.location_id := 1700;
   -- Print fields:
   DBMS_OUTPUT.PUT_LINE ('dept_id: ' || dept_rec.department_id);
   DBMS_OUTPUT.PUT_LINE ('dept_name: ' || dept_rec.department_name);
   DBMS_OUTPUT.PUT_LINE ('mgr_id: ' || dept_rec.manager_id);
   DBMS_OUTPUT.PUT_LINE ('loc_id: ' || dept_rec.location_id);
END;
/



--Example 5–39 %ROWTYPE VARIABLE Does NOT Inherit INITIAL VALUES OR CONSTRAINTS
DROP TABLE t1;

CREATE TABLE t1
(
   c1   INTEGER DEFAULT 0 NOT NULL,
   c2   INTEGER DEFAULT 1 NOT NULL
);

DECLARE
   t1_row   t1%ROWTYPE;
BEGIN
   DBMS_OUTPUT.PUT ('t1.c1 = ');
   DBMS_OUTPUT.PUT_LINE (NVL (TO_CHAR (t1_row.c1), 'NULL'));
   DBMS_OUTPUT.PUT ('t1.c2 = ');
   PRINT (t1_row.c2);
   DBMS_OUTPUT.PUT_LINE (NVL (TO_CHAR (t1_row.c2), 'NULL'));
END;
/


--Example 5–40 %ROWTYPE VARIABLE Represents Partial DATABASE TABLE ROW

DECLARE
   CURSOR c
   IS
      SELECT first_name, last_name, phone_number FROM employees;

   friend   c%ROWTYPE;
BEGIN
   friend.first_name := 'John';
   friend.last_name := 'Smith';
   friend.phone_number := '1-650-555-1234';
   DBMS_OUTPUT.PUT_LINE (
         friend.first_name
      || ' '
      || friend.last_name
      || ', '
      || friend.phone_number);
END;
/



--Example 5–41 %ROWTYPE VARIABLE Represents JOIN ROW

DECLARE
   CURSOR c2
   IS
      SELECT employee_id,
             email,
             employees.manager_id,
             location_id
        FROM employees, departments
       WHERE employees.department_id = departments.department_id;

   join_rec   c2%ROWTYPE;                  -- includes columns from two tables
BEGIN
   NULL;
END;
/



--Example 5–42 INSERTING %ROWTYPE RECORD INTO TABLE (Wrong)
DROP TABLE plch_departure;

CREATE TABLE plch_departure (
   destination VARCHAR2 (100),
   departure_time DATE,
   delay NUMBER (10),
   expected GENERATED ALWAYS AS (departure_time + delay / 24 / 60 / 60));

DECLARE
   dep_rec   plch_departure%ROWTYPE;
BEGIN
   dep_rec.destination := 'X';
   dep_rec.departure_time := SYSDATE;
   dep_rec.delay := 1500;

   INSERT INTO plch_departure
        VALUES dep_rec;
END;
/



--Example 5–43 INSERTING %ROWTYPE RECORD INTO TABLE (RIGHT)

DECLARE
   dep_rec   plch_departure%ROWTYPE;
BEGIN
   dep_rec.destination := 'X';
   dep_rec.departure_time := SYSDATE;
   dep_rec.delay := 1500;

   INSERT INTO plch_departure (destination, departure_time, delay)
        VALUES (dep_rec.destination, dep_rec.departure_time, dep_rec.delay);
END;
/



--Example 5–44 %ROWTYPE Affected BY Making INVISIBLE COLUMN VISIBLE

CREATE TABLE t
(
   a   INT,
   b   INT,
   c   INT INVISIBLE);

INSERT INTO t (a, b, c)
     VALUES (1, 2, 3);

COMMIT;

DECLARE
   t_rec   t%ROWTYPE;                   -- t_rec has fields a and b, but not c
BEGIN
   SELECT *
     INTO t_rec
     FROM t
    WHERE ROWNUM < 2;                                -- t_rec(a)=1, t_rec(b)=2

   DBMS_OUTPUT.PUT_LINE ('c = ' || t_rec.c);
END;
/



--Make invisible column visible:
ALTER TABLE t MODIFY (c VISIBLE);

--Repeat preceding anonymous block:

DECLARE
   t_rec   t%ROWTYPE;                          -- t_rec has fields a, b, and c
BEGIN
   SELECT *
     INTO t_rec
     FROM t
    WHERE ROWNUM < 2;                               -- t_rec(a)=1, t_rec(b)=2,

   -- t_rec(c)=3
   DBMS_OUTPUT.PUT_LINE ('c = ' || t_rec.c);
END;
/



--Example 5–45 Assigning RECORD TO Another RECORD OF Same RECORD TYPE

DECLARE
   TYPE name_rec IS RECORD
   (
      FIRST   employees.first_name%TYPE DEFAULT 'John',
      LAST    employees.last_name%TYPE DEFAULT 'Doe'
   );

   name1   name_rec;
   name2   name_rec;
BEGIN
   name1.FIRST := 'Jane';
   name1.LAST := 'Smith';
   DBMS_OUTPUT.PUT_LINE ('name1: ' || name1.FIRST || ' ' || name1.LAST);
   name2 := name1;
   DBMS_OUTPUT.PUT_LINE ('name2: ' || name2.FIRST || ' ' || name2.LAST);
END;
/



--Example 5–46 Assigning %ROWTYPE RECORD TO RECORD TYPE RECORD

DECLARE
   TYPE name_rec IS RECORD
   (
      FIRST   employees.first_name%TYPE DEFAULT 'John',
      LAST    employees.last_name%TYPE DEFAULT 'Doe'
   );

   CURSOR c
   IS
      SELECT first_name, last_name FROM employees;

   target   name_rec;
   source   c%ROWTYPE;
BEGIN
   source.first_name := 'Jane';
   source.last_name := 'Smith';
   DBMS_OUTPUT.PUT_LINE (
      'source: ' || source.first_name || ' ' || source.last_name);
   target := source;
   DBMS_OUTPUT.PUT_LINE ('target: ' || target.FIRST || ' ' || target.LAST);
END;
/



--Example 5–47 Assigning NESTED RECORD TO Another RECORD OF Same RECORD TYPE

DECLARE
   TYPE name_rec IS RECORD
   (
      FIRST   employees.first_name%TYPE,
      LAST    employees.last_name%TYPE
   );

   TYPE phone_rec IS RECORD
   (
      name    name_rec,                                       -- nested record
      phone   employees.phone_number%TYPE
   );

   TYPE email_rec IS RECORD
   (
      name    name_rec,                                       -- nested record
      email   employees.email%TYPE
   );

   phone_contact   phone_rec;
   email_contact   email_rec;
BEGIN
   phone_contact.name.FIRST := 'John';
   phone_contact.name.LAST := 'Smith';
   phone_contact.phone := '1-650-555-1234';
   email_contact.name := phone_contact.name;
   email_contact.email :=
      (   email_contact.name.FIRST
       || '.'
       || email_contact.name.LAST
       || '@'
       || 'example.com');
   DBMS_OUTPUT.PUT_LINE (email_contact.email);
END;
/



--Example 5–48 SELECT INTO Assigns VALUES TO RECORD VARIABLE

DECLARE
   TYPE RecordTyp IS RECORD
   (
      LAST   employees.last_name%TYPE,
      id     employees.employee_id%TYPE
   );

   rec1   RecordTyp;
BEGIN
   SELECT last_name, employee_id
     INTO rec1
     FROM employees
    WHERE job_id = 'AD_PRES';

   DBMS_OUTPUT.PUT_LINE ('Employee #' || rec1.id || ' = ' || rec1.LAST);
END;
/


--Example 5–49 FETCH Assigns VALUES TO RECORD that FUNCTION Returns

DECLARE
   TYPE EmpRecTyp IS RECORD
   (
      emp_id   employees.employee_id%TYPE,
      salary   employees.salary%TYPE
   );

   CURSOR desc_salary
      RETURN EmpRecTyp
   IS
        SELECT employee_id, salary
          FROM employees
      ORDER BY salary DESC;

   highest_paid_emp        EmpRecTyp;
   next_highest_paid_emp   EmpRecTyp;

   FUNCTION nth_highest_salary (n INTEGER)
      RETURN EmpRecTyp
   IS
      emp_rec   EmpRecTyp;
   BEGIN
      OPEN desc_salary;

      FOR i IN 1 .. n
      LOOP
         FETCH desc_salary INTO emp_rec;
      END LOOP;

      CLOSE desc_salary;

      RETURN emp_rec;
   END nth_highest_salary;
BEGIN
   highest_paid_emp := nth_highest_salary (1);
   next_highest_paid_emp := nth_highest_salary (2);
   DBMS_OUTPUT.PUT_LINE (
         'Highest Paid: #'
      || highest_paid_emp.emp_id
      || ', $'
      || highest_paid_emp.salary);
   DBMS_OUTPUT.PUT_LINE (
         'Next Highest Paid: #'
      || next_highest_paid_emp.emp_id
      || ', $'
      || next_highest_paid_emp.salary);
END;
/


--Example 5–50 UPDATE STATEMENT Assigns VALUES TO RECORD VARIABLE

DECLARE
   TYPE EmpRec IS RECORD
   (
      last_name   employees.last_name%TYPE,
      salary      employees.salary%TYPE
   );

   emp_info     EmpRec;
   old_salary   employees.salary%TYPE;
BEGIN
   SELECT salary
     INTO old_salary
     FROM employees
    WHERE employee_id = 100;

      UPDATE employees
         SET salary = salary * 1.1
       WHERE employee_id = 100
   RETURNING last_name, salary
        INTO emp_info;

   DBMS_OUTPUT.PUT_LINE (
         'Salary of '
      || emp_info.last_name
      || ' raised from '
      || old_salary
      || ' to '
      || emp_info.salary);
END;
/



--Example 5–51 Assigning NULL TO RECORD VARIABLE

DECLARE
   TYPE age_rec IS RECORD
   (
      years    INTEGER DEFAULT 35,
      months   INTEGER DEFAULT 6
   );

   TYPE name_rec IS RECORD
   (
      FIRST   employees.first_name%TYPE DEFAULT 'John',
      LAST    employees.last_name%TYPE DEFAULT 'Doe',
      age     age_rec
   );

   name   name_rec;

   PROCEDURE print_name
   AS
   BEGIN
      DBMS_OUTPUT.PUT (NVL (name.FIRST, 'NULL') || ' ');
      DBMS_OUTPUT.PUT (NVL (name.LAST, 'NULL') || ', ');
      DBMS_OUTPUT.PUT (NVL (TO_CHAR (name.age.years), 'NULL') || ' yrs ');
      DBMS_OUTPUT.PUT_LINE (
         NVL (TO_CHAR (name.age.months), 'NULL') || ' mos');
   END;
BEGIN
   print_name;
   name := NULL;
   print_name;
END;
/


--Example 5–52 Initializing TABLE BY INSERTING RECORD OF DEFAULT VALUES
DROP TABLE schedule;

CREATE TABLE schedule
(
   week   NUMBER,
   Mon    VARCHAR2 (10),
   Tue    VARCHAR2 (10),
   Wed    VARCHAR2 (10),
   Thu    VARCHAR2 (10),
   Fri    VARCHAR2 (10),
   Sat    VARCHAR2 (10),
   Sun    VARCHAR2 (10)
);

DECLARE
   default_week   schedule%ROWTYPE;
   i              NUMBER;
BEGIN
   default_week.Mon := '0800-1700';
   default_week.Tue := '0800-1700';
   default_week.Wed := '0800-1700';
   default_week.Thu := '0800-1700';
   default_week.Fri := '0800-1700';
   default_week.Sat := 'Day Off';
   default_week.Sun := 'Day Off';

   FOR i IN 1 .. 6
   LOOP
      default_week.week := i;

      INSERT INTO schedule
           VALUES default_week;
   END LOOP;
END;
/

COLUMN week FORMAT 99
COLUMN Mon FORMAT A9
COLUMN Tue FORMAT A9
COLUMN Wed FORMAT A9
COLUMN Thu FORMAT A9
COLUMN Fri FORMAT A9
COLUMN Sat FORMAT A9
COLUMN Sun FORMAT A9

SELECT * FROM schedule;



--Example 5–53 UPDATING ROWS WITH RECORD

DECLARE
   default_week   schedule%ROWTYPE;
BEGIN
   default_week.Mon := 'Day Off';
   default_week.Tue := '0900-1800';
   default_week.Wed := '0900-1800';
   default_week.Thu := '0900-1800';
   default_week.Fri := '0900-1800';
   default_week.Sat := '0900-1800';
   default_week.Sun := 'Day Off';

   FOR i IN 1 .. 3
   LOOP
      default_week.week := i;

      UPDATE schedule
         SET ROW = default_week
       WHERE week = i;
   END LOOP;
END;

/
Posted by at
Labels: ,