Collections dan Records PL/SQL

5.8. Menggunakan Collections PL/SQL dengan Perintah-perintah SQL

Collections mengijinkan kita memanipulasi tipe-tipe data kompleks di dalam PL/SQL. Program kita dapat menghitung subscript-subscript untuk memproses elemen-elemen tertentu di dalam memory, dan menggunakan SQL untuk menyimpan hasil-hasilnya di dalam database tables.

Contoh: Menciptakan Tipe SQL Berkaitan dengan Nested Table PL/SQL

Di dalam SQL*Plus, kita dapat menciptakan tipe-tipe SQL yang definisinya terkait dengan nested tables dan varrays PL/SQL:

CREATE TYPE CourseList AS TABLE OF VARCHAR2(64);

Kita dapat menggunakan tipe-tipe SQL ini sebagai kolom-kolom di dalam database tables:

CREATE TABLE department (
name VARCHAR2(20),
director VARCHAR2(20),
office VARCHAR2(20),
courses CourseList)
NESTED TABLE courses STORE AS courses_tab;

Setiap item di dalam kolom COURSES merupakan nested table yang akan menyimpan kursus yang ditawarkan oleh departemen tertentu. Klausa NESTED TABLE diperlukan ketika database table memiliki kolom nested table. Klausa tersebut mengidentifikasi nested table dan memberikan nama table-table yang di-generate oleh sistem, dimana Oracle menyimpan data nested table.

Contoh: Menambahkan Nested Table ke Database Table

Sekarang, kita dapat mempopulasikan database table. Constructor table menyediakan nilai-nilai yang seluruhnya akan menuju ke kolom tunggal COURSES:

BEGIN
  INSERT INTO department VALUES('English','Lynn Saunders', 'Breakstone Hall 205',CourseList('Expository   Writing','Film and Literature','Modern Science Fiction','Discursive Writing','Modern English Grammar','Introduction to Shakespeare','Modern Drama','The Short Story','The American Novel'));
END;

Contoh: Menampilkan Nested Table PL/SQL dari Database Table

Kita dapat menampilkan seluruh kursus yang ditawarkan oleh departemen English ke dalam nested table PL/SQL:

DECLARE
  english_courses   CourseList;
BEGIN
  SELECT courses
  INTO english_courses
  FROM department
  WHERE name = 'English';
END;

Di dalam PL/SQL, kita dapat memanipulasi nested table dengan melakukan looping melalui elemen-elemennya, menggunakan method-method seperti TRIM atau EXTEND, dan mengubah beberapa atau seluruh data dari elemen-elemen tersebut. Setelah itu, kita dapat menyimpan table yang telah di-update tersebut ke dalam database lagi.

Contoh: Mengupdate Nested Table di dalam Database Table

Kita dapat merevisi daftar kursus yang ditawarkan oleh departemen English:

DECLARE
  new_courses CourseList := CourseList('Expository Writing', 'Film and Literature', 'Discursive Writing', 'Modern English Grammar', 'Realism and Naturalism', 'Introduction to Shakespeare', 'Modern Drama', 'The Short Story', 'The American Novel', '20th-Century Poetry', 'Advanced Workshop in Poetry');
BEGIN
  UPDATE department
  SET courses = new_courses
  WHERE name = 'English';
END;

5.8.1. Beberapa Contoh Varray

Dalam SQL*Plus, asumsikan kita mendefinisikan object type Project, seperti berikut ini:

CREATE TYPE Project AS OBJECT (
project_no NUMBER(2), title VARCHAR2(35),
cost NUMBER(7,2));

Berikutnya, kita mendefinisikan tipe VARRAY ProjectList, yang menyimpan objek-objek Project:

CREATE TYPE ProjectList AS VARRAY(50) OF Project;

Akhirnya, kita menciptakan relational table department, yang memiliki kolom dengan tipe ProjectList, seperti contoh berikut:

CREATE TABLE department
(dept_id NUMBER(2),name VARCHAR2(15),budget NUMBER(11,2),projects ProjectList);

Setiap item di dalam kolom projects adalah varray yang akan menyimpan projects yang dijadwalkan untuk departemen tersebut.

Sekarang, kita telah siap untuk mempopulasi relational table department. Di dalam contoh berikut ini, perhatikan bagaimana constructor varray ProjectList() menyediakan nilai-nilai untuk kolom projects:

BEGIN
  INSERT INTO department
  VALUES(30,'Accounting', 1205700,ProjectList(Project(1,'Design New Expense Report', 3250),Project(2,'Outsource Payroll', 12350),Project(3,'Evaluate Merger Proposal', 2750),Project(4,'Audit Accounts Payable', 1425)));
  INSERT INTO department
  VALUES(50,'Maintenance', 925300,ProjectList(Project(1,'Repair Leak in Roof', 2850),Project(2,'Install New Door Locks', 1700),Project(3,'Wash FrontWindows', 975),Project(4,'Repair Faulty Wiring', 1350),Project(5,'Winterize Cooling System', 1125)));
  INSERT INTO department
  VALUES(60,'Security', 750400,ProjectList(Project(1,'Issue New Employee Badges', 13500),Project(2,'Find Missing IC Chips', 2750),Project(3,'Upgrade Alarm System', 3350),Project(4,'Inspect Emergency Exits', 1900)));
END;

Di dalam contoh berikut ini, kita meng-update daftar project yang diberikan untuk Security Department:

DECLARE
  new_projects   ProjectList := ProjectList(Project(1,'Issue New Employee Badges', 13500),Project(2,'Develop New Patrol Plan', 1250),Project(3,'Inspect Emergency Exits', 1900),Project(4,'Upgrade Alarm System', 3350),Project(5,'Analyze Local Crime Stats', 825));
BEGIN
  UPDATE department
  SET projects = new_projects
  WHERE dept_id = 60;
END;

Di dalam contoh selanjutnya, kita menampilkan seluruh project untuk Accounting Department ke dalam varray lokal:

DECLARE
  my_projects   ProjectList;
BEGIN
  SELECT projects
  INTO my_projects
  FROM department
  WHERE dept_id = 30;
END;

Di dalam contoh terakhir, kita menghapus Accounting Department beserta daftar project-nya dari table department:

BEGIN
  DELETE FROM department WHERE dept_id = 30;
END;

5.8.2. Memanipulasi Elemen Collection Secara Individual Menggunakan SQL

Secara default, operasi-operasi SQL menyimpan dan menampilkan seluruh collection dibandingkan elemen-elemen individual. Untuk memanipulasi elemen-elemen dari collection dengan menggunakan SQL, kita menggunakan operator TABLE. Operator TABLE menggunakan subquery untuk menggali varray atau nested table, sehingga perintah INSERT, UPDATE, atau DELETE diaplikasikan terhadap nested table dibandingkan top-level table.

Contoh: Menambahkan Elemen Kedalam Nested Table dengan SQL

Dalam contoh berikut ini, kita menambahkan baris data ke nested table History Department yang tersimpan di dalam kolom COURSES:

BEGIN
-- Operator TABLE membuat perintah diterapkan terhadap nested table
-- dari baris data 'History' dari table DEPARTMENT
INSERT INTO TABLE(SELECT courses FROM department WHERE name = 'History')
VALUES('Modern China');
END;

Contoh: Mengubah Elemen-elemen Dalam Nested Table dengan SQL

Dalam contoh berikutnya, kita menyingkat nama-nama untuk beberapa kursus yang ditawarkan oleh Phsycology Department:

BEGIN
  UPDATE TABLE(SELECT courses FROM department WHERE name = 'Psychology')
  SET credits = credits + adjustment
  WHERE course_no IN (2200, 3540);
END;

Contoh: Menampilkan Elemen Tunggal dari Nested Table dengan SQL

Dalam contoh berikut ini, kita menampilkan judul dari kursus tertentu yang ditawarkan oleh History Department:

DECLARE
  my_title   VARCHAR2(64);
BEGIN
  -- Kita mengetahui bahwa terdapat kursus sejarah dengan judul 'Etruscan'.
  -- Query ini menampilkan judul lengkap dari nested table dari kursus
  -- untuk History department.
  SELECT title INTO my_title
  FROM TABLE(SELECT courses FROM department WHERE name = 'History')
  WHERE name LIKE '%Etruscan%';
END;

Contoh: Menghapus Elemen-elemen dari Nested Table dengan SQL

Dalam contoh selanjutnya, kita menghapus 5 kredit kursus yang ditawarkan oleh English Department:

BEGIN
  DELETE TABLE(SELECT courses FROM department WHERE name = 'English')
  WHERE credits = 5;
END;

Contoh: Menampilkan Elemen-elemen dari Varray dengan SQL

Dalam contoh di bawah ini, kita menampilkan judul dan biaya dari proyek keempat Maintenance Department dari varray kolom projects:

DECLARE
  my_cost NUMBER(7,2);
  my_title VARCHAR2(35);
BEGIN
  SELECT cost, title INTO my_cost, my_title
  FROM TABLE(SELECT projects FROM department WHERE dept_id = 50)
  WHERE project_no = 4;
  ...
END;

Contoh: Melakukan Operasi-operasi INSERT, UPDATE, dan DELETE pada Varray dengan SQL

Saat ini, kita tidak dapat mereferensi elemen-elemen individual dari varray di dalam perintah INSERT, UPDATE, atau DELETE. Kita harus menampilkan seluruh varray, dan menggunakan perintah-perintah prosedural PL/SQL untuk menambahkan, menghapus, atau mengubah elemen-elemennya, dan kemudian menyimpan kembali varray yang telah kita ubah tersebut ke dalam database table.

CREATE PROCEDURE add_project (dept_no IN NUMBER, new_project IN Project, position IN NUMBER) AS
  my_projects   ProjectList;
BEGIN
  SELECT projects INTO my_projects FROM department
  WHERE dept_no = dept_id FOR UPDATE OF projects;
  my_projects.EXTEND;   -- menciptakan ruang untuk project baru
  /* Memindah elemen-elemen varray ke posisi lebih depan. */
  FOR i IN REVERSE position..my_projects.LAST - 1 LOOP
    my_projects(i + 1) := my_projects(i);
  END LOOP;
  my_projects(position):= new_project; -- menambahkan project baru
  UPDATE department
  SET projects = my_projects
  WHERE dept_no = dept_id;
END add_project;

Stored procedure di bawah ini mengubah project yang telah diberikan:

CREATE PROCEDURE update_project (dept_no IN NUMBER, proj_no IN NUMBER, new_title IN VARCHAR2 DEFAULT NULL,
new_cost IN NUMBER DEFAULT NULL) AS
  my_projects   ProjectList;
BEGIN
  SELECT projects INTO my_projects FROM department
  WHERE dept_no = dept_id FOR UPDATE OF projects;
  /* Mencari project, mengubahnya, lalu segera keluar dari loop. */
  FOR i IN my_projects.FIRST..my_projects.LAST LOOP
    IF my_projects(i).project_no = proj_no THEN
      IF new_title IS NOT NULL THEN
        my_projects(i).title:= new_title;
      END IF;
      IF new_cost IS NOT NULL THEN
        my_projects(i).cost:= new_cost;
      END IF;
      EXIT;
    END IF;
  END LOOP;
  UPDATE department
  SET projects = my_projects
  WHERE dept_no = dept_id;
END update_project;

Contoh: Melakukan Operasi-operasi INSERT, UPDATE, dan DELETE pada Nested Table PL/SQL

Untuk melakukan operasi-operasi DML pada nested table PL/SQL, kita menggunakan operator-operator TABLE dan CAST. Dengan cara ini, kita dapat melakukan kumpulan operasi-operasi pada nested table menggunakan notasi SQL, tanpa benar-benar menyimpan nested table di dalam database.

Operand-operand CAST merupakan collection variable PL/SQL dan collection type SQL (diciptakan dengan perintah CREATE TYPE). CAST mengkonversi colleciton PL/SQL ke type SQL.

Contoh berikut menghitung jumlah perbedaan antara daftar kursus yang telah direvisi dan yang asli (perhatikan bahwa jumlah kredit untuk kursus 3720 berubah dari 4 ke 3).

DECLARE
  revised CourseList := CourseList(Course(1002,'Expository Writing', 3),Course(2020,
                        'Film and Literature', 4),Course(2810,'Discursive Writing', 4),
                        Course(3010,'Modern English Grammar ', 3),Course(3550,
                        'Realism and Naturalism', 4),Course(3720,'Introduction to Shakespeare', 3),
                        Course(3760,'Modern Drama', 4),Course(3822,'The Short Story', 4),
                        Course(3870,'The American Novel', 5),Course(4210,'20th-Century Poetry', 4),
                        Course(4725,'Advanced Workshop in Poetry', 5));

  num_changed INTEGER;
BEGIN
  SELECT COUNT(*) INTO num_changed
  FROM TABLE(CAST(revised AS CourseList)) new,TABLE(SELECT courses FROM department WHERE
  name = 'English') AS old
  WHERE new.course_no = old.course_no AND (new.title!= old.title OR new.credits != old.credits);
  dbms_output.put_line(num_changed);
END;