25th Example - Tabel Based Record on PL/SQL

After various examples of PL/SQL block is given, let's try an example PL/SQL block through interaction at the tables in the database. A table consists of attributes and rows.

Record is one of composite data type that is used to hold the value of the table processed by the PL/SQL block. Table based record is kind of record that has the characteristics of interacting directly with the tables to be managed. Table based on the use of records used in conjunction with the operator %ROWTYPE. Suppose a table in the know with the name TMAHASISWA.

CREATE TABLE TMahasiswa (

nim NUMBER NOT NULL,

nama VARCHAR(64),

notelp VARCHAR(16),

CONSTRAINT pk_mhs PRIMARY KEY (nim)

);

With table of contents as follows:

NIM	NAMA	NOTELP
MI010059	Andri Subagja	0812334455
MI010060	Bambang Sudrajat	0813334456
MI010061	Bambang Hendra	0814334457
MI010062	Cecep Suprianto	0815334458
MI010063	Indri Suratmi	0816334459
TI010030	Bambang Sudrajat	0817334460
TI010031	Beni sukmana	0818334461
TI010032	Fani Yuniar	0856334462
TI010033	Ilham Sawargi	0857334463
TI010034	Yuni Asniar	0888334464
KA010050	Anti Sulistawati	0889334465
KA010051	Angga Suhendar	0811334466
KA010052	Bambang Sudrajat	0812334467
KA010053	Roni Pambudi	0813334468
KA010054	Nurma Melati	0815334469

Here is an example of using table-based records in PL/SQL block:

DECLARE

   mhs_tabel_rec tmahasiswa%ROWTYPE;

BEGIN

   SELECT * INTO mhs_tabel_rec FROM tmahasiswa WHERE nim=’MI010058’;

   DBMS_OUTPUT.PUT_LINE (mhs_tabel_rec.nim);

   DBMS_OUTPUT.PUT_LINE (mhs_tabel_rec.nama);

END;

/

PL/SQL block above has a variable named mhs_tabel_rec that has a data type tmahasiswa% ROWTYPE. Note here that the data type is used instead of a scalar-type record. mhs_tabel_rec tmahasiswa%ROWTYPE means are all the attributes of the tables were taken and loaded on tmahasiswa mhs_tabel_rec variables. At the contents of which contained an implicit cursor variable mhs_tabel_rec that will fill the entire contents of a table with the results displayed on the tmahasiswa mhs_tabel_rec.nim and mhs_tabel_rec.nama.