IF-ELSE Statement

IF-ELSE Statement

Sama seperti bahasa pemrograman yang lain, PL/SQLjuga menyediakan Statement IF-ELSE

Syntax :

IF condition THEN
statements;
[ELSIF condition THEN
statements;]
[ELSE
statements;]
END IF;

Contoh :

SET SERVEROUTPUT ON
SET VERIFY OFF

DECLARE
ANGKA NUMBER :=&n;
BEGIN
IF Angka > 10 THEN
DBMS_OUTPUT.PUT_LINE('ANGKA' || ANGKA || 'LEBIH BESAR DARI 10');
ELSIF ANGKA < 10 THEN
DBMS_OUTPUT.PUT_LINE('ANGKA' || ANGKA || 'LEBIH KECIL DARI 10');
ELSE DBMS_OUTPUT.PUT_LINE('ANGKA' || ANGKA || 'SAMA DENGAN 10');
END IF;
END;

Blok PL/SQL diatas terdapat simbul ampersand (&)  yang digunakan untuk meminta inputan user. Apabila user memasukkan angka lebih besar dari 10 maka Oracle Server akan mencetak statement DBMS_OUTPUT.PUT_LINE yang pertama. Jika Angka yang diinput lebih keci dari 10 Maka Oracle Server akan mencetak statement DBMS_OUTPUT.PUT_LINE yang kedua. Dan jika tidak ada kondisi yang memenuhi semua kondisi diatas, maka oracle server akan mencetak statement DBMS_OUTPUT.PUT_LINE yang ketiga.

CASE Statement

Selain menggunakan statement IF, Oracle juga menyediakan alternatif lain untuk melakukan pemilihan statement, yaitu dengan menggunakan CASE  Statement.  Case statement hampir sama logikanya dengan menggunakan SWITCH-CASE pada bahasa C.

Syntax :

CASE selector
WHEN expression1 THEN result1
WHEN expression2 THEN result2
…
WHEN expressionN THEN resultN
[ELSE resultN+1]
END;

Contoh :

SET SERVEROUTPUT ON
SET VERIFY OFF

DECLARE
input VARCHAR2(1):= upper('&input');
kota VARCHAR2(50);
BEGIN

kota:=

CASE input
WHEN 'A' THEN 'Jakarta'
WHEN 'B' THEN 'Padang'
WHEN 'C' THEN 'Bandung'
ELSE 'Indonesia'
END;

DBMS_OUTPUT.PUT_LINE ('Input: '|| inpur|| 'Kota' || kota);
END;

http://risnotes.com/2012/02/if-else-statement/

Soal Cursor

Setelah lama ga update blog, malah ketemu soal tentang cursor yang menarik

1. Buatlah statement pl/sql dengan menggunakan cursor untuk menampilkan employee_id, department_id, salary, dan rata-rata salary per departemen maksimal 10 baris. Dan Tampilkan juga berapa rows yg ditampilkan.

SET SERVEROUTPUT ON DECLARE emp employees.employee_id%type; dep employees.department_id%type; sal employees.salary%type; avsal employees.salary%type; CURSOR emp_cursor IS SELECT employee_id, salary, department_id,(selectavg(salary) FROM employees WHERE department_id= b.department_id) FROM employees b; BEGIN OPEN emp_cursor; LOOP FETCH emp_cursor into emp, sal, dep, avsal; dbms_output.put_line('emp id: ' || emp || ' ' || 'department id : ' || dep || ' ' || 'salary : ' || sal || ' ' || 'average salary : ' || avsal); EXIT WHEN emp_cursor%ROWCOUNT = 10; END LOOP; dbms_output.put_line('jumlah baris= : ' || emp_cursor%ROWCOUNT); CLOSE emp_cursor; end; / Hasilnya : emp id: 198 department id : 50 salary : 2600 average salary : 3475,56 emp id: 199 department id : 50 salary : 2600 average salary : 3475,56 emp id: 200 department id : 10 salary : 4400 average salary : 4400 emp id: 201 department id : 20 salary : 13000 average salary : 10450 emp id: 202 department id : 20 salary : 7900 average salary : 10450 emp id: 203 department id : 40 salary : 6500 average salary : 6500 emp id: 204 department id : 70 salary : 10000 average salary : 10000 emp id: 205 department id : 110 salary : 12000 average salary : 10150 emp id: 206 department id : 110 salary : 8300 average salary : 10150 emp id: 100 department id : 90 salary : 1000 average salary : 11666,67 jumlah baris = : 10 PL/SQL procedure successfully completed.

2. Tampilkanlah first name, last name, dan salary dari tabel employees dengan menggunakan cursor yang memiliki sebuah parameter. Kemudian Tampilkan data karyawan tersebut dimana karyawan tersebut memiliki salary yang nilainya lebih kecil dari salary dari parameter cursor

DECLARE CURSOR cur_emps(salary_param NUMBER) IS SELECT first_name, last_name, salary FROM employees WHERE salary < salary_param; f_name employees.first_name%type; l_name employees.last_name%type; gaji employees.salary%type; BEGIN OPEN cur_emps(2500); LOOP FETCH cur_emps INTO  f_name, l_name, gaji; EXIT WHEN cur_emps%NOTFOUND; DBMS_OUTPUT.PUT_LINE(f_name || ' ' || l_name || ' memiliki gaji: ' || gaji); END LOOP; CLOSE cur_emps; END; / Hasil jika nilai parameter=2500 Steven King memiliki gaji: 1000  James Landry memiliki gaji: 2400  Steven Markle memiliki gaji: 2200  TJ Olson memiliki gaji: 2100  Ki Gee memiliki gaji: 2400  Hazel Philtanker memiliki gaji: 2200  Peter Tucker memiliki gaji: 1000  Tayler Fox memiliki gaji: 2100  PL/SQL procedure successfully completed.

3. Buatlah sebuah cursor yang digunakan untuk mengUPDATE salary karyawan menjadi 2 kali gaji awal dimana id karyawan tersebut adalah 109. Kemudian tampilkan pesan ‘Data Telah diUpdate’ ketika data telah berhasil diUPDATEdan tampilkan pesan ‘Karyawan tidak ditemukan’ ketika employee_id nya tidak terdapat dalam database.

DECLARE CURSOR cur_upd_salary(comp_emp_id NUMBER) IS SELECT salary FROM employees WHERE employee_id = comp_emp_id      FOR UPDATE OF SALARY; id employees.employee_id%type; BEGIN OPEN cur_upd_salary(109); FETCH cur_upd_salary INTO id; IF SQL%NOTFOUND THEN DBMS_OUTPUT.PUT_LINE ('Karyawan tidak ditemukan!'); ELSE UPDATE employees SET salary = salary * 2 WHERE CURRENT OF cur_upd_salary; DBMS_OUTPUT.PUT_LINE('UPDATE SUCCESS!'); END IF; CLOSE cur_upd_salary; END; /

4. Buatlah sebuah table yang sama dengan table employees dengan nama employees2. Kemudian buatlah sebuah cursor yang berguna untuk menghapus data karyawan yang memiliki employee_id = 120. Dan pastikan data yang telah dihapus tidak dapat dikembalikan

CREATE TABLE employees2 AS SELECT * FROM employees; DECLARE CURSOR cur_upd_emp(emp_id varchar2) IS SELECT employee_id FROM employees2 WHERE employee_id = emp_id FOR UPDATE; id employees.employee_id%TYPE; BEGIN   OPEN cur_upd_emp(120); FETCH cur_upd_emp into id;   IF cur_upd_emp%FOUND then DELETE FROM employees2   WHERE CURRENT OF cur_upd_emp;   COMMIT;   ELSE    DBMS_OUTPUT.PUT_LINE('data not found');   END IF;   CLOSE cur_upd_emp; END; /

5. Buatlah sebuah Anonymous block untuk case berikut ini Gunakan CURSOR dan WHERE CURRENT OF cursor sebagai solusi
Case :
Lakukan update terhadap data lokasi beberapa nama department berikut :
- Department “Recruiting” dan “Administration” akan diupdate lokasinya sama dengan  lokasi department  “Human Resources”
- Department  “Retail Sales” dan  “Government Sales” akan diupdate lokasinya sama dengan department “Sales”
Notes :

• Buatlah agar perubahan tersebut tidak dapat dirollback
• Buatlah exception jika terjadi table departments sedang dilock Tampilkan pesan ‘ Row of Departments table is locked’ (error number is –54, dapat menggunakan PRAGMA EXCEPTION_INIT)
• Jika terjadi error lainnya tampilkan RAISE_APPLICATION_ERROR nomor -20001, pesannya ‘Unknown Error‘

DECLARE CURSOR dept_cursor  IS SELECT * FROM departments WHERE department_namein('Recruiting','Administration','Retail Sales','Government Sales') for update; Loc1 departments.location_id%type; Loc2 departments.location_id%type; Loc departments.location_id%type; row_lock EXCEPTION; PRAGMA EXCEPTION_INIT(row_lock, -54); BEGIN SELECT select location_id INTO loc1 from departmentswhere department_name ='Human Resources'; SELECT location_id INTO loc2 from departments wheredepartment_name='Sales'; for dept_record in dept_cursor loop if ( dept_record.department_name ='Recruiting' or dept_record.department_name ='Administration' )  then loc:=loc1;   else loc:=loc2; END IF; UPDATE departments set location_id =loc WHERE current of dept_cursor; COMMIT; end loop; EXCEPTION WHEN row_lock THEN DBMS_OUTPUT.PUT_LINE('Row of Departments table is locked'); WHEN OTHERS THEN RAISE_APPLICATION_ERROR (-20001,'Unknown Error'); end;

Handling Exceptions

Sebuah Informasi yang cukup bermanfaat bagi yang ingin mempelajari PL/SQL, karena ini termasuk dasar dari PL/SQL.

Handling Exceptions merupakan salah satu kelebihan dari PL/SQL yang digunakan untuk menangani error yang terjadi pada saat run time.

Sebagai contoh, pada saat kita menuliskan sebuah query, dan data tersebut tidak ditemukan di dalam database, maka oracleakan membangkitkan eksepsi NO_DATA_FOUND  dengan kode ORA-01403 yang berarti data tidak ditemukan. Berikut ini kita akan menuliskan perbedaan dengan menggunakan blok eksepsi dengan yang tidak menggunakan blok eksepsi.

Contoh Yang tidak Menggunakan blok eksepsi :

SET SERVEROUTPUT ON
DECLARE
nama employees.last_name%type;
BEGIN
SELECT last_name INTO nama FROM employees
WHERE employee_id = 309;
DBMS_OUTPUT.PUT_LINE ('nama');
END;

Hasilnya:

DECLARE
*

ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 4

Contoh Yang Menggunakan Blok Eksepsi :

SET SERVEROUTPUT ON
DECLARE
nama employees.last_name%type;
BEGIN
SELECT last_name INTO nama FROM employees
WHERE employee_id = 309;
DBMS_OUTPUT.PUT_LINE ('nama');
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE ('TIdak Ditemukan Datanya dalam Database');
END;

Hasilnya :

TIdak Ditemukan Datanya dalam Database
PL/SQL procedure successfully completed.

Berikut ini merupakan nama beserta Code exception di PL/SQL

Berikut ini merupakan keterangan dari nama exception di PL/SQL

Pada variabel dengan tipe data skalar, kita tidak dapat menampung banyak nilai dalam variabel tersebut, untuk menangani error yang terjadi ketika kita memasukkan banyak nilai ke dalam satu variabel skalar, maka kita menggunakan EXCEPTION TOO_MANY_ROWS

Contoh :

DECLARE
empno NUMBER;
BEGIN
SELECT employee_id INTO empno FROM employees ;
DBMS_OUTPUT.PUT_LINE(empno);
EXCEPTION
WHEN TOO_MANY_ROWS THEN

DBMS_OUTPUT.PUT_LINE('THERE ARE TOO MANY ROWS');
END;

Hasilnya :

THERE ARE TOO MANY ROWS
PL/SQL procedure successfully completed.

Kita tidak perlu menghafal semua nama dari setiap exception handling diatas, kita cukup hanya menghafal beberapa saja dan sisanya kita dapat menggunakan eksepsi OTHERS.

DECLARE
empno NUMBER;
BEGIN
SELECT employee_id INTO empno FROM employees ;
DBMS_OUTPUT.PUT_LINE(empno);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Data Tidak Ditemukan');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Terjadi Error yang Lain');
END;

Hasil :

Terjadi Error yang Lain
PL/SQL procedure successfully completed.

PRAGMA EXCEPTION INIT

PRAGMA EXCEPTION INIT digunakan agar  kita dapat mendefinisikan nama EXCEPTION kita sendiri, tetapi berdasakan dengan ERROR CODE yang ada.

contoh :

SET SERVEROUTPUT ON

DECLARE
kebanyakan EXCEPTION;
PRAGMA EXCEPTION_INIT(kebanyakan, -01422);
emp employees.employee_id%type;

BEGIN
SELECT employee_id INTO emp FROM Employees;
DBMS_OUTPUT.PUT_LINE(EMP);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Hasil tidak ditemukan');

WHEN kebanyakan THEN
DBMS_OUTPUT.PUT_LINE(‘hasilnya kebanyakan’);

END;

Penjelasan :

Pada mulanya error code -01422 merupakan code exception dari TOO_MANY_ROWS, tetapi dengan menggunakan PRAGMA_EXCEPTION_INIT, kita merubah TOO_MANY_ROW menjadi KEBANYAKAN. Yang sebelumnya, KEBANYAKAN telah dideklarasikan terlebih dahulu dengan menjadi sebuah EXCEPTION.

SQLCODE dan SQLERRM

SQLCODE dan SQLERRM biasa digunakan pada EXCEPTION OTHERS untuk mengetahui error apa yang terjadi. SQLCODE untuk menampilkan kode eksepsi, sedangkan SQLERRM digunakan untuk menampilkan keterangan eksepsi.

contoh :

SET SERVEROUTPUT ON
DECLARE
emp employees%rowtype;

BEGIN
SELECT * into emp FROM EMPLOYEES WHERE employee_id > 100;

EXCEPTION
WHEN no_data_found THEN
DBMS_OUTPUT.PUT_LINE('No Data Found');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR');
DBMS_OUTPUT.PUT_LINE('error code ='||SQLCODE);
DBMS_OUTPUT.PUT_LINE('error message ='||SQLERRM);

END;

Hasilnya :

ERROR 
error code =-1422 
error message =ORA-01422: exact fetch returns more than requested number of rows 
PL/SQL procedure successfully completed.

RAISE APPLICATION ERROR

Pada PRAGMA EXCEPTION INIT kita hanya dapat mendefinisikan sendiri nama dari eksepsi tersebut dan masih harus sesuai dengan ERROR CODEnya. Tetapi dengan menggunakan RAISE APPLICATION ERROR, kita dapat menentukan sendiri nama beserta code dari eksepsi yang kita inginkan.

contoh :

SET SERVEROUTPUT ON

DECLARE
name VARCHAR2(100);
sal NUMBER;
BEGIN 
SELECT last_name, salary INTO name, sal FROM employeesWHERE employee_id = 120;

IF sal > 5000 THEN
RAISE_APPLICATION_ERROR (-20000,'Gaji Kebanyakan');
END IF;

DBMS_OUTPUT.PUT_LINE(name||' '||sal);
END;

Hasilnya :
DECLARE *
ERROR at line 1: 
ORA-20000: Gaji Kebanyakan 
ORA-06512: at line 8