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

Sunday, June 30, 2013

24th Example - VARRAY on PL/SQL

Leave a Comment
In the following example will be demonstrated using VARRAY data type. VARRAY is one of the collection types in Oracle Database. The principle works the same as an associative array that manages a set of data in an array that has a specified index. Here is an example of PL/SQL block that uses VARRAY:

DECLARE
     TYPE tipe_varray IS VARRAY(5) OF VARCHAR2(24);
     nama tipe_varray;
     jumlah NUMBER;
BEGIN
     nama := tipe_varray(&input_nama);-- enter 5 names, eg: 'one', 'two', 'three', 'four', 'five'

jumlah := nama.count;
     FOR i IN 1..jumlah LOOP
          DBMS_OUTPUT.PUT_LINE (i||' - '||nama(i));
     END LOOP;
END;
/

In the example above, there is a VARRAY type with name tipe_varray that can accommodate a maximum of 5 values of type VARCHAR with maximum length 24 characters. Variable nama that will hold the array values will be entered in the variable substitution &input_nama. Variable number will count the number of array index. Using FOR .. LOOP looping the array variable nama will be separated and displayed to the screen.

Read More

Friday, June 28, 2013

23rd Example - Associative Array (Index-By Table) on PL/SQL

Leave a Comment
In this example we will study the use of arrays in PL/SQL block. Array in PL/SQL block is known as collection types. One of the collection types is an associative array. Here is an example of PL /SQL block that have associative arrays:

DECLARE
     TYPE tipe_arr_nilai IS TABLE OF NUMBER 
       INDEX BY BINARY_INTEGER;
arr_nilai tipe_arr_nilai;
total NUMBER;
BEGIN
     arr_nilai(1) := 1000;
     arr_nilai(2) := 2000;
arr_nilai(3) := 3000;
total := arr_nilai(1) + arr_nilai(2) + arr_nilai(3);
DBMS_OUTPUT.PUT_LINE (total);
END;
/


In the declaration part of PL/SQL block above, found TYPE statement. TYPE is used here to form a collection types which will serve as an associative array. TYPE tipe_arr_nilai is an associative array (can be considered as a table) that have attributes with the data type NUMBER. Arr_nilai variables will have tipe_arr_nilai data type (NUMBER set). Inside the BODY of PL/SQL block found arrays arr_nilai 3 (1), arr_nilai (2) and arr_nilai (3), each of them has a different value. The third value of the array can be summed using the sum operator plus (+). Results from PL/SQL block above is the value of 6000.
Read More

Wednesday, June 26, 2013

22nd Example - Count Area and Circumference of A Circle on PL/SQL

Leave a Comment
In 18th Lessons, it has been given an example to calculate the area of ​​a circle. The formula to calculate the area of ​​a circle is pr2, where p is a constant with a value of 3:14 and r is the radius of the circle. The formula for calculating the circumference of a circle is 2pr2. The following PL/SQL block to calculate area of ​​a circle :

DECLARE
    phi CONSTANT NUMBER := 3.14;
    r NUMBER;
    hasil NUMBER;
    pilih VARCHAR2(1);
    pilihan VARCHAR2(8);
BEGIN
    r := &input_r;
    pilih := '&luas_atau_keliling';--input L atau K
    IF pilih = 'L' THEN
       hasil := phi * r * r;
       pilihan := 'Luas Lingkaran';
    ELSE
       hasil := 2 * phi * r;
       pilihan := 'Keliling Lingkaran';
    END IF;
    DBMS_OUTPUT.PUT_LINE (pilihan||' = '||hasil);
END;
/


Above PL/SQL block will ask for 2 input values​​. The first input to fill the circle radius (r) and the second input to determine the choice area or circumstance of circle that will count. If the option on the input is L then will be loaded PL/SQL block that will calculate the area of ​​a circle, if the option is K then PL/SQL block will calculate the circumference of a circle.
Read More

Friday, June 21, 2013

21st Example - Count the Average and Categorizes on PL/SQL

Leave a Comment


On an assessment will be obtained many different values​​. Those values ​​can be calculated mean values​​. The average value can be grouped into certain groups. Suppose there are found 5 values 60,70,80,90,50. The summarized values can be calculated the average score is 70. Defined 3 categories, LOW if the average value of <60, MID if the average value of 60-85 and HIGH if the average value of> 85. The following PL/SQL block to perform the calculation:

DECLARE

        n1 NUMBER;

        n2 NUMBER;

        n3 NUMBER;

        n4 NUMBER;

        n5 NUMBER;

        rata NUMBER;

        kat VARCHAR2(8);--LOW/MID/HIGH

BEGIN

        n1 := 60;

        n2 := 70;

        n3 := 80;

        n4 := 90;

        n5 := 50;

        rata := (n1+n2+n3+n4+n5)/5;

        IF rata > 85 THEN

           kat := 'HIGH';

        ELSIF rata BETWEEN 60 AND 85 THEN

           kat := 'MID';

        ELSE

           kat := 'LOW';

        END IF;

        DBMS_OUTPUT.PUT_LINE (The category is  '||kat);

END;

/

There are 5 variables n1, n2, n3, n4, n5. Average variable will summarized values and then divided by five to obtain the average value. Checking category performed using conditional statements IF .. END IF. For the case above, the output shown is "The category is MID".
Read More

Thursday, June 20, 2013

20th Example - Calculate Employee Salaries on PL/SQL

Leave a Comment
A company has many employees with various status, married and not married. A married employees would get salaries larger than the unmarried (at same level). Employees who are married will get married allowance. If you are married and have children will receive child support anyway. Here's an example of the calculation, if married will get salary + benefits of marriage, if you have children then salary + benefits + n * benefits of children otherwise will only get salary.
The following PL/SQL block to perform the calculation:

DECLARE
        nama VARCHAR2(32);
        status VARCHAR2(8);
        menikah VARCHAR2(1);
        nAnak NUMBER := 0;
        gaji NUMBER;
        tNikah NUMBER := 0.1;
        tAnak NUMBER := 0.05;
BEGIN
        nama := '&input_nama';
        status := '&input_status'; -- SINGLE/MARRIED
        nAnak := &input_nAnak;
        gaji := &input_gaji;--gaji pokok
        IF status = 'MARRIED' THEN
           IF nAnak = 0 THEN
             gaji := gaji + tNikah*gaji;
           ELSE
             gaji := gaji + tNikah*gaji + nAnak*gaji*tAnak;
           END IF;
        END IF;
        DBMS_OUTPUT.PUT_LINE ('Gaji '||nama||' = '||gaji);
END;
/


On above PL/SQL, the salary calculation that will automatically make additions if the status MARRIED. Eg the employees' basic salaries is 1000 and status MARRIED then salary earned is 1100. If the status is married with 1 child, the salary earned is 1150. If the status is SINGLE salary earned is 1000.

The best thing about blogs, they just keep going
Read More

Wednesday, June 19, 2013

19th Example - Calculating Parking Fee on PL/SQL

Leave a Comment
At a shopping area usually has a parking facility equipped with automated parking system. Automatic calculation is done by a parking program. Parking fee is determined by the time of the vehicle was in the mall parking area. Calculation is obtained from the difference from output and input times that multiplies by the hourly rate. The following PL/SQL block to perform automatic parking fee calculation:

DECLARE
 jamin TIMESTAMP;
 jamout TIMESTAMP;
 jam NUMBER;
 menit NUMBER;
 waktu VARCHAR2(32);
 tarif NUMBER;
 biaya NUMBER;
BEGIN
 tarif := 1000;
 jamin := TO_TIMESTAMP('&time_input','HH24:MI');--ex 08:15
 jamout := TO_TIMESTAMP('&time_output','HH24:MI');--ex 13:45
 jam := EXTRACT(HOUR FROM jamout)-EXTRACT(HOUR FROM jamin);
 menit := EXTRACT(MINUTE FROM jamout)-EXTRACT(MINUTE FROM jamin);
 waktu := jam||' jam '||menit||' menit ';
 IF (menit >= 1) THEN
     biaya := (jam+1)*tarif;
 ELSE
     biaya := jam * tarif;
 END IF;
 DBMS_OUTPUT.PUT_LINE(waktu);
 DBMS_OUTPUT.PUT_LINE(biaya);
END;
/


PL / SQL block above has a variable with data type TIMESTAMP. TIMESTAMP data type is able to separate the date and time being the smallest unit. Before the calculation process, the conversion of the input needs to be done at a TIMESTAMP, for example TO_TIMESTAMP ('& time_input', 'HH24: MI'). To take hours of TIMESTAMP use the EXTRACT (HOUR FROM jamout). Next do the hour and minute of reduction, if minutes> = 1 then the hours will be increased by 1. Furthermore selisihjam multiply the cost of the rate to be paid will be obtained. For example, if the input time = 8:15 and output time = 13:45 then the difference is 5 hours 30 minutes at a cost of 6000.
Read More

Monday, June 17, 2013

18th Example - Count Area of A Circle on PL/SQL

Leave a Comment
The circle is of mathematical shape that can be found in the real world such as car wheels, the pipe, cable cross-section. Circle has an area and perimeter that  can be calculated with mathematical formulas. The formula to calculate the area of ​​a circle is pr2, where p is a constant with a value of 3,14 and r is the radius of the circle. The following PL / SQL block to calculate area of ​​a circle:

DECLARE
                phi CONSTANT NUMBER := 3.14;
                r NUMBER;
                hasil NUMBER;
BEGIN
                r := &input_r;
                hasil := phi * r * r;
                DBMS_OUTPUT.PUT_LINE ('Luas Lingkaran = '||hasil);
END;
/


In the declaration of PL/SQL block above there are 3 variables. One variable is the constant phi. Constant is a variable whose value will not change when the program executed. Contents of PL/SQL block that will calculate the area of ​​a circle pr2 value stored in the variable hasil. Suppose we fill the value of the variable r 10, the output shown is "Area of ​​Circle = 314".
Read More