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

Sunday, June 16, 2013

Celsius to Fahrenheit Conversion on Oracle PL/SQL

Leave a Comment
This article will discuss about Celsius to Fahrenheit Conversion on Oracle PL/SQL. Temperature has a different measurement units such as Celsius, Fahrenheit and Kelvin. If we know the value of one measurement unit then we will be able to determine the value in other units. We call this as a unit conversions. This can be done by doing the conversion in any programming language, one is Oracle PL/SQL. First wee need to know is we must know the formula to convert the units.

The following example will show PL/SQL block to convert the temperature from Celsius to Fahrenheit. The formula used to convert from Celsius to Fahrenheit is 9/5 x Celsius + 32. But before we execute any of PL/SQL syntax, firstly after we do a login process on SQL*Plus me must type the SET SERVEROUTPUT ON commnad (just only one time every session).

DECLARE
        celcius NUMBER;
        fahrenheit NUMBER;
BEGIN
        celcius := &input_celcius;
        fahrenheit := 9/5 * celcius + 32;
        DBMS_OUTPUT.PUT_LINE (celcius ||' Celcius = '||fahrenheit|| ' Fahrenheit');
     END;
     /


Above PL/SQL block will require an input value from keyboard to fill the Celsius value. Celsius value will be converted using the formula 9/5 x Celsius + 32. Suppose that the input given on Celsius is 30, then the output is 86 Fahrenheit.


That's all the explanation about Celsius to Fahrenheit Conversion on Oracle PL/SQL. You should be able to convert other type of conversion as long as you know the formula.

Thank You - Bobsis
Read More

Saturday, June 15, 2013

16th Example - Factorial Value on PL/SQL

Leave a Comment


Factorial is one of the techniques in the mathematical sciences. A factorial value of a number is the product of the number (n) with the number minus one (n-1). Suppose the value to be searched of factorial is 5, the calculation will be done is 5x4x3x2x1 which will result in the value 120. The following PL / SQL block will do factorial calculation:

DECLARE

        n NUMBER;

        hasil NUMBER:=1;

BEGIN

        n := &input_n;

        FOR i IN 1..n LOOP

                        hasil := hasil * i;

        END LOOP;

        DBMS_OUTPUT.PUT_LINE('5! = '||hasil);

END;

/

Suppose input_n variable value is 5. FOR loop iteration will do 5 times iteration. The outcome variable in the loop will make the process of multiplication value of i (number of looping) multiplied by the value of the last 5 times. After the loop 5 times then the hasil variabel value will be 120.
Read More

Friday, June 14, 2013

15th Example - Count the Discount Price on PL/SQL

Leave a Comment
At a store or a supermarket, the payment process is at the cashier. Cashier operator will do scanning of the item code by a barcode reader. The process of calculating the value of the groceries are automatically performed by the application at the cashier. Some items might get some discount if purchased. Here is an example of a block PL / SQL to perform automatic calculations that give a discount on an item of goods that are bought:

DECLARE
        harga NUMBER;
        diskon NUMBER;
        bayar NUMBER;
BEGIN
        harga := &input_harga;--obtained from barcode reader
        diskon := harga * 0.1; --10%
        bayar := harga - diskon;
        DBMS_OUTPUT.PUT_LINE ('Harga Barang = '||harga);
        DBMS_OUTPUT.PUT_LINE ('Diskon 10% = '||diskon);
        DBMS_OUTPUT.PUT_LINE ('Total Bayar = '||bayar);
END;
/


There are 3 variables: harda with NUMBER data type, diskon with NUMBER data type and bayar with NUMBER data types. In the contents, the harga variable will be filled by substitution variable named input_harga. Diskon variable will contain a value that is 10% discount of the price. Harga Variable will contain the item price after discount. Those variables then displayed by using the command dbms_output.put_line ();. Suppose input_harga 1000 then the bayar will contain the value 900.
Read More

Monday, June 10, 2013

14th Example - Calculate Your Age on PL/SQL

Leave a Comment

We have learned the basics of PL/SQL block from the start structured, selection, looping and exception. Let us now try to make a block of PL/SQL to solve real problems. The following will be shown examples of PL/SQL to calculate a person's age. Person's age can be determined from the difference between the current date with the date of birth.

DECLARE
        tlahir DATE;
        umur NUMBER;
BEGIN
        --ENter your birth data with DD-MM-YYYY (ex: 17-02-1990)
        tlahir := TO_DATE('&indate', 'DD-MM-YYYY');
        umur := FLOOR((SYSDATE - tlahir)/365);
        DBMS_OUTPUT.PUT_LINE ('Your age is '||umur||' years old');
END;
/

Above PL/SQL block have 2 variables, tlahir with DATE data type and age with NUMBER data type. Tlahir variable will receive input from a keyboard entry date of birth in the format DD-MM-YYYY. Umur variable will make the process of calculating the difference between SYSDATE (current date) with tlahir (date of birth) were divided by 365 (days in a year). To generate integers by rounding down is used FLOOR function. If the input is "17-02-1990" then the output is "Your age is 23 years old".
Read More