Saturday, July 13, 2013

28th Example - Implicit Cursor on PL/SQL

Leave a Comment
Implicit cursor is a cursor that was made on the contents of PL/SQL block and can only do 1 unit record processing. Unlike explicit cursors, implicit cursor does not contain CURSOR statement on its block.
Here is an example of using implicit cursor (SELECT INTO) in the PL / SQL block:

DECLARE
v_nim TMahasiswa.NIM%TYPE;
v_nama TMahasiswa.NAMA%TYPE;
BEGIN
SELECT nim, nama INTO v_nim, v_nama FROM tmahasiswa WHERE nim='MI1001';
DBMS_OUTPUT.PUT_LINE (v_nim);
DBMS_OUTPUT.PUT_LINE (v_nama);
END;
/


Block PL/SQL above has an implicit cursor form of SELECT ... INTO statement. Implicit cursor placed is under the BEGIN statement. Conducted by the implicit cursor is retrieving data nim and nama of the table tmahasiswa to be stored in the variable v_nim and v_nama. WHERE syntax on the implicit cursor used to restrict only 1 record that will be processed by an implicit cursor. The results are then displayed to the screen with dbms_output.put_line statement.
Read More

Tuesday, July 9, 2013

27th Example - Explicit Cursor on PL/SQL

Leave a Comment
Cursor is part of a block of PL/SQL that references to one or several tables in the database. In the PL/SQL there are two types of cursors that can be made which are implicit cursor and explicit cursor. Explicit cursor is a cursor that was made in the declaration block of PL/SQL while the implicit cursor is made ​​on the part of the content (body) PL/SQL block. Excess of the explicit cursor is able to record more than one operation while the implicit cursor can only perform one operation at a time record. PL/SQL block use explicit cursor join with looping block.
Here is an example of PL/SQL block with explicit cursor:

DECLARE
CURSOR C_Mahasiswa IS
SELECT nim, nama FROM TMahasiswa;
Var1 TMahasiswa.NIM%TYPE;
Var2 TMahasiswa.NAMA%TYPE;
BEGIN
FOR i IN C_Mahasiswa LOOP
Var1 := i.NIM;
Var2 := i.NAMA;
DBMS_OUTPUT.PUT_LINE(Var1||‘ ‘||Var2);
END LOOP;
END;
/


Above anonymous block of PL/SQL has a cursor with the name c_mahasiswa. C_mahasiswa cursor will accommodate nim and nama attributes of the table tmahasiswa. By using the FOR..LOOP looping, PL/SQL block able to display all the data nim and nama that is on the table tmahasiswa (dbms_output.put_line (var1 | | '' | | var2)).
Read More

Sunday, July 7, 2013

26th Example - Cursor Based Record on PL/SQL

Leave a Comment
In the previous module has been described on table-based record. At this time will be explained about the cursor-based record. In contrast to table-based record, cursor-based record has characteristics not directly interact with the table, but interact through the mediation CURSOR. Cursor is a declaration on the block PL/SQL to create a variable that contains a query from one or more existing tables in the database. Here is an example of using cursor-based record (use tmahasiwa table that's been made before):

DECLARE
   CURSOR c_mhs IS SELECT nim, nama FROM mahasiswa;
   mhs_rec c_mhs%ROWTYPE;
BEGIN
   OPEN c_mhs;
   LOOP
     FETCH c_mhs INTO mhs_rec;
     EXIT WHEN c_mhs%NOTFOUND;
     DBMS_OUTPUT.PUT_LINE(mhs_rec.nim||' '||mhs_rec.nama);
   END LOOP;
   CLOSE c_mhs;
END;
/


PL/SQL block above has a cursor with the name c_mhs. Cursor c_mhs contains a query that took nim and nama attributes of the table tmahasiswa. Mhs_rec variable has a data type c_mhs% ROWTYPE, which means it is able to accommodate variable nim and nama of the cursor c_mhs. On the contents of block PL/SQL cursor variable c_mhs loaded in mhs_rec inside a loop block. Cursor will continue to be read until the end and the result is displayed to the screen by calling mhs_rec.nim and mhs_rec.nama.
Read More

Wednesday, July 3, 2013

25th Example - Tabel Based Record on PL/SQL

Leave a Comment
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.
Read More