Hastinapura

Oracle Database Resources

Collections dan Records PL/SQL

5.16. Memanipulasi Records

Tipe data RECORD mengijinkan kita untuk mengumpulkan informasi mengenai attributes dari sesuatu. Informasi ini mudah dimanipulasi karena kita dapat mengacu kepada kumpulan tersebut sebagai keseluruhan. Di dalam contoh berikut ini, kita mengumpulkan data-data berbagai pos akuntansi dari database tables assets dan liabilities, lalu menggunakan analisa rasio untuk membandingkan performa dari dua cabang perusahaan.

DECLARE
  TYPE FiguresRec IS RECORD (cash REAL, notes REAL, ...);
  sub1_figs FiguresRec;
  sub2_figs FiguresRec;
  FUNCTION acid_test (figs FiguresRec) RETURN REAL IS ...
BEGIN
  SELECT cash, notes, ...
  INTO sub1_figs
  FROM assets, liabilities
  WHERE assets.sub = 1 AND liabilities.sub = 1;
  SELECT cash, notes, ...
  INTO sub2_figs
  FROM assets, liabilities
  WHERE assets.sub = 2 AND liabilities.sub = 2;
  IF acid_test(sub1_figs) > acid_test(sub2_figs) THEN ...
   ...
END;

Perhatikan betapa mudahnya ia melewatkan pos-pos yang telah berhasil diambil ke function acid_test, yang mana menghitung rasio keuangan.

Dalam SQL*Plus, andaikata kita mendefinisikan object type Passenger, seperti berikut ini:

SQL> CREATE TYPE Passenger AS OBJECT(
2 flight_no NUMBER(3),
3 name VARCHAR2(20),
4 seat CHAR(5));

Berikutnya, kita mendefinisikan tipe VARRAY PassengerList, yang menyimpan objects Passenger:

SQL> CREATE TYPE PassengerList AS VARRAY(300) OF Passenger;

Akhirnya, kita menciptakan relational table flights, yang memiliki kolom dengan tipe PassengerList, seperti berikut ini:

SQL> CREATE TABLE flights (
2 flight_no NUMBER(3),
3 gate CHAR(5),
4 departure CHAR(15),
5 arrival CHAR(15),
6 passengers PassengerList);

Setiap item di dalam kolom passengers  merupakan varray yang akan menyimpan daftar penumpang untuk penerbangan yang telah diberikan. Sekarang, kita dapat mempopulasikan database table flights, seperti berikut ini:

BEGIN
  INSERT INTO flights
  VALUES(109,'80', 'DFW 6:35PM', 'HOU 7:40PM',PassengerList(Passenger(109,'Paula Trusdale', '13C'),
  Passenger(109,'Louis Jemenez', '22F'),Passenger(109,'Joseph Braun', '11B'), ...));
  INSERT INTO flights
  VALUES(114,'12B', 'SFO 9:45AM', 'LAX 12:10PM',PassengerList(Passenger(114,'Earl Benton', '23A'),
  Passenger(114,'Alma Breckenridge', '10E'),Passenger(114,'Mary Rizutto', '11C'), ...));
  INSERT INTO flights
  VALUES(27,'34', 'JFK 7:05AM', 'MIA 9:55AM',PassengerList(Passenger(27,'Raymond Kiley', '34D'),
  Passenger(27,'Beth Steinberg', '3A'),Passenger(27,'Jean Lafevre', '19C'), ...));
END;

Di dalam contoh di bawah ini, kita mengambil baris-baris data dari database table flights dan memasukkannya ke dalam record flight_info. Dengan cara itu, kita dapat memperlakukan seluruh informasi tentang penerbangan, termasuk daftar penumpang, sebagai unit logikal.

DECLARE
  TYPE FlightRec IS RECORD (flight_no NUMBER(3), gate CHAR(5), departure CHAR(15),
       arrival CHAR(15), passengers PassengerList);
  flight_info FlightRec;
  CURSOR c1 IS
  SELECT *
  FROM flights;
  seat_not_available EXCEPTION;
BEGIN
  OPEN c1;
  LOOP
    FETCH c1 INTO flight_info;
    EXIT WHEN c1%NOTFOUND;
    FOR i IN 1..flight_info.passengers.LAST LOOP
      IF flight_info.passengers(i).seat = 'NA' THEN
        dbms_output.put_line(flight_info.passengers(i).name);
        RAISE seat_not_available;
      END IF;
      ...
    END LOOP;
  END LOOP;
  CLOSE c1;
  EXCEPTION WHEN seat_not_available THEN
    ...
END;

5.16.1. Menambahkan PL/SQL Records ke dalam Database

Perluasan PL/SQL dari perintah INSERT mengijinkan kita untuk menambahkan data ke dalam baris-baris data pada database dengan menggunakan variable tunggal bertipe RECORD atau %ROWTYPE dibandingkan daftar fields. Hal ini membuat kode program kita lebih mudah dibaca dan lebih mudah dipelihara.

Jumlah fields di dalam record harus sama dengan jumlah kolom yang disebutkan di dalam klausa INTO, dan fields dan kolom-kolom terkait harus memiliki tipe data yang kompatibel. Untuk meyakinkan kita bahwa sebuah record kompatibel dengan table, mungkin lebih baik bagi kita untuk mendeklarasikan variable dengan tipe table_name%ROWTYPE.

5.16.1.1. Menambahkan PL/SQL Record Menggunakan %ROWTYPE: Contoh

Contoh ini mendeklarasikan variable record menggunakan pengkualifikasi %ROWTYPE. Kita dapat menambahkan variable ini tanpa menentukan daftar kolom. Deklarasi %ROWTYPE memastikan bahwa attribute-attribute record benar-benar memiliki nama dan tipe data yang sama dengan kolom-kolom pada table.

DECLARE
  dept_info dept%ROWTYPE;
BEGIN
  -- deptno, dname, and loc adalah kolom pada table.
  -- Record mengambil nama-nama ini dari %ROWTYPE.
  dept_info.deptno:= 70;
  dept_info.dname:= 'PERSONNEL';
  dept_info.loc:= 'DALLAS';
  -- Menggunakan %ROWTYPE berarti kita dapat mengabaikan daftar kolom
  -- (deptno, dname, loc) dari perintah INSERT.
  INSERT INTO dept VALUES dept_info;
END;

5.16.2. Mengubah Database dengan Nilai-nilai PL/SQL Record

Perluasan PL/SQL dari perintah UPDATE mengijinkan kita untuk mengubah baris-baris data pada database dengan menggunakan variable tunggal bertipe RECORD atau %ROWTYPE dibandingkan dengan daftar fields.

Jumlah fields di dalam record harus sama dengan jumlah kolom yang disebutkan dalam klausa SET, dan field-field dan kolom-kolom yang berhubungan harus memiliki tipe data yang kompatibel.

Mengubah Baris Data Menggunakan: Contoh

Kita dapat menggunakan kata kunci ROW untuk merepresentasikan keseluruhan baris data:

DECLARE
  dept_info dept%ROWTYPE;
BEGIN
  dept_info.deptno:= 30;
  dept_info.dname:= 'MARKETING';
  dept_info.loc:= 'ATLANTA';
  -- Baris data akan memiliki nilai untuk kolom
  -- yang diisi, dan null untuk kolom-kolom lainnya.
  UPDATE dept
  SET ROW = dept_info
  WHERE deptno = 30;
END;

Kata kunci ROW diperbolehkan hanya pada sisi kiri dari klausa SET.

SET ROW Tidak Diperbolehkan Bersama Subquery: Contoh

Kita tidak dapat menggunakan ROW bersama dengan subquery. Sebagai contoh, perintah UPDATE berikut ini tidak diperbolehkan:

UPDATE emp SET ROW = (SELECT * FROM mgrs); -- tidak diperbolehkan

Mengubah Baris Data Menggunakan Record yang Mengandung Object: Contoh

Records mengandung tipe-tipe objects diperbolehkan:

CREATE TYPE Worker AS OBJECT (name VARCHAR2(25), dept VARCHAR2(15));
/
CREATE TABLE teams (team_no NUMBER, team_member Worker);
DECLARE
  team_rec
  teams%ROWTYPE;
BEGIN
  team_rec.team_no:= 5;
  team_rec.team_member:= Worker('Paul Ocker', 'Accounting');
  UPDATE teams SET ROW = team_rec;
END;
/

Mengubah Baris Data Menggunakan Record yang Mengandung Collection: Contoh

Record dapat pula mengandung collections:

CREATE TYPE Worker AS OBJECT (name VARCHAR2(25), dept VARCHAR2(15));
/
CREATE TYPE Roster AS TABLE OF Worker;
/
CREATE TABLE teams (team_no NUMBER, members Roster)
  NESTED TABLE members STORE AS teams_store;
INSERT INTO teams
VALUES (1, Roster(
Worker('PaulOcker', 'Accounting'),
Worker('Gail Chan', 'Sales')
Worker('Marie Bello', 'Operations')
Worker('Alan Conwright', 'Research')));
DECLARE
  team_rec teams%ROWTYPE;
BEGIN
  team_rec.team_no:= 3;
  team_rec.members:= Roster(
  Worker('William Bliss', 'Sales'),
  Worker('Ana Lopez', 'Sales')
  Worker('Bridget Towner', 'Operations')
  Worker('Ajay Singh', 'Accounting'));
  UPDATE teams
  SET ROW = team_rec;
END;
/

Menggunakan Klausa RETURNING Bersama Record: Contoh

Perintah-perintah INSERT, UPDATE, dan DELETE dapat terdiri dari klausa RETURNING, yang menghasilkan nilai-nilai kolom dari baris-baris data yang dihasilkan ke dalam variable record PL/SQL. Hal ini menyingkirkan kebutuhan untuk melakukan SELECT terhadap baris data setelah penambahan atau penghapusan, atau sebelum penghapusan. Kita dapat menggunakan klausa ini hanya ketika beroperasi pada satu baris data.

Di dalam contoh berikut ini, kita mengubah gaji karyawan dan, pada saat bersamaan, menampilkan nama, jabatan, dan gaji baru karyawan ke dalam variable record:

DECLARE
  TYPE EmpRec IS RECORD (emp_name VARCHAR2(10), job_title VARCHAR2(9), salary NUMBER(7,2));
  emp_info EmpRec;
  emp_id NUMBER(4);
BEGIN
  emp_id:= 7782;
  UPDATE emp
  SET sal = sal * 1.1
  WHERE empno = emp_id
  RETURNING ename, job, sal INTO emp_info;
END;

5.16.3. Batasan-batasan pada Penambahan/Pengubahan Record

Saat ini, batasan-batasan berikut ini diterapkan pada penambahan/pengubahan record:

• Variable-variable record diperbolehkan hanya dalam tempat-tempat berikut ini:
  • Pada sisi kanan dari klausa SET di dalam perintah UPDATE
  • Di dalam klausa VALUES dari perintah INSERT
  • Di dalam sub-klausa INTO dari klausa RETURNING

Variable-variable record tidak diperbolehkan dalam daftar SELECT, klausa WHERE, klausa GROUP BY, atau klausa ORDER BY.

• Kata kunci ROW diperbolehkan hanya pada sisi kiri klausa SET. Juga, kita tidak dapat menggunakan ROW bersama subquery.
• Dalam perintah UPDATE, hanya klausa SET yang diperbolehkan jika ROW digunakan.
• Jika klausa VALUES dari perintah INSERT mengandung variable record, tidak ada variable atau nilai lain yang diperbolehkan di dalam klausa tersebut.
• Jika sub-klausa INTO dari klausa RETURNING mengandung variable record, tidak ada variable atau nilai yang diperbolehkan di dalam sub-klausa tersebut.
• Hal-hal berikut ini tidak di-support:
  • Tipe-tipe nested record
  • Functions yang menghasilkan record
  • Penambahan/pengubahan record menggunakan perintah EXECUTE IMMEDIATE

5.16.4. Menampilkan Data ke dalam Collections dari Records

Operasi-operasi binding PL/SQL terbagai menjadi tiga kategori:

• define Mengacu kepada nilai-nilai database yang ditampilkan dengan perintah SELECT atau FETCH ke dalam PL/SQL variables atau host variables.
• in-bind Mengacu kepada nilai-nilai database yang ditambahkan dengan perintah INSERT atau dimodifikasi dengan perintah UPDATE.
• out-bind Mengacu kepada nilai-nilai database yang dihasilkan dengan klausa RETURNING dari perintah INSERT, UPDATE, atau DELETE ke dalam PL/SQL variables atau host variables.

PL/SQL mendukung bulk binding terhadap collections dari records di dalam perintah-perintah DML. Secara khusus, pendefinisian atau out-bind variable dapat berupa collection dari records, dan nilai-nilai in-bind dapat disimpan di dalam collection atau records. Sintaksnya adalah sebagai berikut:

SELECT select_items BULK COLLECT INTO record_variable_name
FROM rest_of_select_stmt

FETCH { cursor_name | cursor_variable_name | :host_cursor_variable_name}
BULK COLLECT INTO record_variable_name [LIMIT numeric_expression];

FORALL index IN lower_bound..upper_bound
  INSERT INTO { table_reference|THE_subquery} [{column_name[, column_name]...}]
  VALUES (record_variable_name(index)) rest_of_insert_stmt

FORALL index IN lower_bound..upper_bound
  UPDATE {table_reference | THE_subquery} [alias]
  SET (column_name[, column_name]...) = record_variable_name(index)
  rest_of_update_stmt

RETURNING row_expression[, row_expression]...
  BULK COLLECT INTO record_variable_name;

Di dalam setiap perintah dan klausa di atas, variable record menyimpan collection dari records. Jumlah fields di dalam record harus sama dengan jumlah item di dalam daftar SELECT, jumlah kolom di dalam klausa INSERT INTO, jumlah kolom di dalam klausa UPDATE…SET, atau jumlah baris ekspresi-ekspresi di dalam klausa RETURNING, secara berturut-turut. Field-field dan kolom-kolom yang berhubungan harus memiliki tipe-tipe data yang kompatibel. Berikut ini beberapa contohnya:

CREATE TABLE tab1 (col1 NUMBER, col2 VARCHAR2(20));
/

CREATE TABLE tab2 (col1 NUMBER, col2 VARCHAR2(20));
/

DECLARE
  TYPE RecTabTyp IS TABLE OF tab1%ROWTYPE
    INDEX BY BINARY_INTEGER;
  TYPE NumTabTyp IS TABLE OF NUMBER
    INDEX BY BINARY_INTEGER;
  TYPE CharTabTyp IS TABLE OF VARCHAR2(20)
    INDEX BY BINARY_INTEGER;
  CURSOR c1 IS
  SELECT col1, col2
  FROM tab2;
  rec_tab RecTabTyp;
  num_tab NumTabTyp := NumTabTyp(2,5,8,9);
  char_tab CharTabTyp := CharTabTyp('Tim', 'Jon', 'Beth', 'Jenny');
BEGIN
  FORALL i IN 1..4
    INSERT INTO tab1
    VALUES(num_tab(i), char_tab(i));
  SELECT col1, col2
  BULK COLLECT INTO rec_tab
  FROM tab1
  WHERE col1 < 9;
  FORALL i IN rec_tab.FIRST..rec_tab.LAST
    INSERT INTO tab2
    VALUES rec_tab(i);
  FOR i IN rec_tab.FIRST..rec_tab.LAST LOOP
    rec_tab(i).col1 := rec_tab(i).col1 + 100;
  END LOOP;
  FORALL i IN rec_tab.FIRST..rec_tab.LAST
    UPDATE tab1 SET (col1, col2) = rec_tab(i) WHERE col1 < 8;
  OPEN c1;
  FETCH c1 BULK COLLECT INTO rec_tab;
  CLOSE c1;
END;

Silahkan melanjutkan membaca pembahasan Interaksi PL/SQL dengan Oracle

 

 

Buku ini membahas berbagai macam tip dan trik yang sangat berguna bagi Anda pembaca pengguna database Oracle. Pemula, mahasiswa, programmer, maupun database administrator yang mengharapkan solusi cepat dalam menangani permasalahan-permasalahan dalam penggunaan database Oracle dapat membaca buku ini. Anda tidak harus membaca buku ini secara urut karena buku ini di susun berdasarkan topik permasalahan, dan Anda dapat membaca topik-topik yang sesuai dengan permasalahan yang Anda hadapi. Dapat di beli di toko buku Gramedia, Gunung Agung, Uranus, Karisma dsb. Harga Rp. 24.800,- (157 halaman)

 

• Native Dynamic SQL
• PL/SQL Object Types
• PL/SQL Packages
• PL/SQL Subprograms
• Menangani PL/SQL Errors

 

Copyright © 2012 Hastinapura. Beranda | Berbagi Cerita | Hastinapura | Kelasik | Langgam | Nunung | Obsesi | Propolis | Serasi | Spektakuler

Switch to our mobile site