PENDAHULUAN PL SQL
PL/SQL
(Procedural Language/Structured Query Language) merupakan sebuah
penggabungan antara bahasa pemrograman prosedural (PL) dan SQL syntax.
PL/SQL
adalah fasilitas yang disediakan Oracle sehingga pengguna dapat
memanfaatkan konsep pemrograman. Dalam PL/SQL dapat digunakan perintah
untuk memanipulasi data yang ada dalam database Oracle. PL/SQL
membentuk pemrograman terstruktur dalam memproses data.
Beberapa kelebihan PL/SQL dalam database Oracle :
- PL/SQL dapat digunakan di server sehingga client hanya dapat mengakses didalam server.
- Penggunaan PL/SQL mudah dimengerti oleh setiap pengguna.
- PL/SQL dapat didesain khusus untuk database Oracle dalam menggunakan
- program aplikasi.
Apa Itu PL/SQL Package?
Package
adalah sebuah schema object yang mengelompokkan PL/SQL types, items,
dan subprograms, yang terkait secara logikal. Packages biasanya
memiliki dua bagian, sebuah specification dan sebuah body, meskipun
kadangkala body tersebut tidak diperlukan. Specification (atau spec)
merupakan antarmuka terhadap aplikasi-aplikasi kita; ia mendeklarasikan
types, variables, constants, exceptions, cursors, dan subprograms yang
tersedia untuk digunakan. Body secara penuh mendefinisikan cursors dan
subprograms, dan juga mengimplementasikan spec.
Seperti
ditunjukkan oleh Gambar 9-1, kita dapat memikirkan sebuah spec sebagai
sebuah antarmuka operasional dan body sebagai sebuah “kotak hitam
(black box)”. Kita dapat men-debug, meningkatkan, atau menimpa sebuah
package body tanpa mengubah antarmuka (package spec) ke package
tersebut.
Contoh Sebuah PL/SQL Package
Dalam
contoh di bawah ini, kita mem-package sebuah record type, sebuah
cursor, dan dua procedure kepegawaian. Perlu diingat bahwa procedure
hire_employee menggunakan database sequence empno_seq dan function
SYSDATE untuk menambahkan sebuah employee number baru dan hire date.
CREATE OR REPLACE PACKAGE emp_actions AS -- spec
TYPE EmpRecTyp IS RECORD (emp_id INT, salary REAL);
CURSOR desc_salary RETURN EmpRecTyp;
PROCEDURE hire_employee (
ename VARCHAR2,
job VARCHAR2,
mgr NUMBER,
sal NUMBER,
comm NUMBER,
deptno NUMBER);
PROCEDURE fire_employee (emp_id NUMBER);
END emp_actions;
CREATE OR REPLACE PACKAGE BODY emp_actions AS -- body
CURSOR desc_salary RETURN EmpRecTyp IS
SELECT empno, sal FROM emp ORDER BY sal DESC;
PROCEDURE hire_employee (
ename VARCHAR2,
job VARCHAR2,
mgr NUMBER,
sal NUMBER,
comm NUMBER,
deptno NUMBER) IS
BEGIN
INSERT INTO emp VALUES (empno_seq.NEXTVAL, ename, job, mgr,
SYSDATE, sal, comm, deptno);
END hire_employee;
PROCEDURE fire_employee (emp_id NUMBER) IS
BEGIN
DELETE FROM emp WHERE empno = emp_id;
END fire_employee;
END emp_actions;
Hanya
deklarasi di dalam package yang terlihat dan dapat dikses terhadap
aplikasi. Detil implementasi di dalam package body disembunyikan dan
tidak dapat diakses. Jadi, kita dapat mengubah body (implementation)
tanpa harus meng-compile ulang program-program yang memanggilnya.
Keuntungan-keuntungan dari PL/SQL Packages
Packages
menawarkan beberapa keuntungan: modularitas, desain aplikasi yang lebih
mudah, penyembunyian informasi, tambahan fungsionalitas, dan performa
yang lebih baik.
Modularity
Packages
mengijinkan kita untuk mengenkapsulasi secara logikal types, items, dan
subprograms yang berhubungan di dalam sebuah PL/SQL module yang
memiliki nama. Setiap package mudah dipahami, dan antarmuka antara
packages adalah sederhana, jelas, dan didefinisikan dengan baik. Hal
ini membantu pembangunan aplikasi.
Easier Application Design
Ketika
mendesain sebuah aplikasi, seluruh yang kita butuhkan awalnya adalah
informasi antarmuka di dalam package specs tersebut. Kita dapat
mengkodekan dan meng-compile sebuah spec tanpa body-nya. Kemudian,
stored subprograms yang mereferensi kepada package tersebut dapat
di-compile juga. Kita tidak perlu mendefinisikan package bodies secara
penuh sampai kita siap untuk menyelesaikan aplikasi.
Information Hiding
Dengan
packages, kita dapat menentukan types, items, dan subprograms mana saja
yang bersifat public (terlihat dan dapat diakses) atau yang private
(tersembunyi dan tidak dapat diakses). Sebagai contoh, jika sebuah
package mengandung empat subprograms, yang tiga mungkin public dan yang
satu private. Package menyembunyikan implementasi dari private
subprogram sehingga hanya package tersebut (bukan aplikasi kita) yang
terpengaruh jika implementasi diubah. Hal ini memudahkan pemeliharaan
dan peningkatan. Juga, dengan menyembunyikan detil implementasi dari
pengguna, kita dapat melindungi integritas dari package tersebut.
Added Functionality
Variable-variable
dan cursor-cursor public ter-package tetap ada selama satu session.
Sehingga, mereka dapat digunakan secara bersama-sama oleh subprograms
yang berjalan dalam environment tersebut. Juga, mereka memperbolehkan
kita memelihara data melalui transaksi-transaksi tanpa harus
menyimpannya di dalam database.
Better Performance
Ketika
kita memanggil sebuah subprogram terpackage untuk pertama kali, seluruh
package di-load ke memory. Sehingga, pemanggilan-pemanggilan berikutnya
terhadap subprogram-subprogram terkait dengan di dalam package tersebut
tidak memerlukan disk I/O. Juga, packages berhenti mengaitkan
ketergantungan dan dengan demikian tidak memerlukan re-compile ulang
yang tidak perlu. Sebagai contoh, jika kita mengubah implementasi dari
sebuah function ter-package, Oracle tidak perlu meng-compile ulang
subprogram-subprogram yang memanggilnya karena mereka tidak bergantung
kepada package body.
STRUKTUR PL/SQL
Struktur
PL/SQL mirip dengan struktur bahasa pascal atau delphi yang menggunakan
struktur blok, sehingga akan mempermudah pengertian dalam pemrograman
dengan PL/SQL. Struktur Blok berisi perintah SQL dengan kondisi yang
berbeda. Perintah PL/SQL dapat menangani kesalahan saat dijalankan.
Setiap pengetikan dengan menggunakan PL/SQL dalam SQL*Plus selalu
diakhiri dengan tanda /(slash). Sintaks penggunaan PL/SQL adalah
sebagai berikut :
Declare
Begin
Exception
End
Struktur diatas dapat dijelaskan sebagai berikut :
1. Bagian Judul (Header)
Bagian
ini hanya digunakan jika PL/SQL diberikan nama,misalnya untuk prosedur
atau fungsi. Bagian ini berisi nama blok,daftar parameter, dan
pengembalian hasil (return) jika blok adalahfungsi.
2. Bagian Deklarasi (declaration)
Bagian
ini untuk membuat deklarasi mengenai semua variable dan konstanta yang
direferensikan dalam pernyataan PL/SQL. Bagian deklarasi ini dimulai
dengan perintah DECLARE. Jika tidak ada variable atau konstanta yang
ingin dideklarasikan bagian ini boleh dihilangkan, bersifat optional.
3. Bagian Eksekusi (Execution).
Bagian ini memuat pernyataan-pernyataan PL/SQL yang akan ditulis. Bagian eksekusi ini harus dimulai dengan perintah BEGIN.
4. Bagian Perkecualian (Exception)
Bagian
ini memuat cara menangani kesalahan-kesalahan (error) pada waktu
eksekusi program PL/SQL, bersifat optional. Jika program tidak memuat
cara menangani kesalahan, bagian ini boleh dihilangkan.Setiap
pernyataan PL/SQL harus diakhiri dengan tanda titik koma(;) dan semua
program PL/SQL harus diakhiri dengan perintah END.
Bentuk Umum Struktur PL/SQL
DECLARE
variabel tipe_data;
konstanta CONSTANT tipe_data := nilai;
...
BEGIN
statement_1;
statement_2;
...
EXCEPTION
WHEN nama_eksepsi THEN statement_untuk_mengatasi_error;
...
END;
Contoh Program (tanpa exception)
SQL> SET SERVEROUTPUT ON
SQL>DECLARE
v1 NUMBER(3);
BEGIN
v1 := 3;
DBMS_OUTPUT.PUT_LINE('v1= ' || v1);
END;
/
Contoh Program (dengan exception)
SQL>SET SERVEROUTPUT ON
SQL>DECLARE
X Integer;
BEGIN
X := ‘Belajar Oracle’;
DBMS_OUTPUT.PUT_LINE( TO_CHAR(X) );
EXCEPTION
WHEN VALUE_ERROR THEN
DBMS_OUTPUT.PUT_LINE (‘Kesalahan pada pengisian nilai’);
END;
/
DBMS_OUTPUT
merupakan paket yang disediakan oleh Oracle PL / SQL dan PUT_LINE
merupakan salah satu prosedur yang dikemas. Menampilkan nilai-nilai
pada SQL Plus * terminal yang harus diaktifkan dengan SERVEROUTPUT SET
ON terlebih dahulu. Untuk menjalankan kode sampel ini, login ke SQL *
Plus. PL / SQL blok diakhiri dengan tanda garis miring / atau garis
byitself.
PENDAHULUAN SQL PLUS
SQL
adalah kependekan dari Structured Query Language, digunakan untuk
berkomunikasi dengan Oracle sehingga kita dapat memerintahkan informasi
apa yang ingin kita SELECT, INSERT, UPDATE atau DELETE. Keempat kata
yang saya tulis dengan huruf besar tersebut merupakan kata inti yang
digunakan untuk memerintahkan Oracle. Apa sih SQL*Plus itu dan apa
perannya dalam database Oracle? Secara sederhana kita dapat mengatakan
bahwa SQL*Plus itu seperti assisten pribadi kita saat bekerja dengan
Oracle. Melalui SQ*Plus inilah kita memberikan perintah kepada Oracle
dan kita dapat melihat hasilnya.
Keistimewaan sql plus
- menerima tambahan masukan perintah
- menerima input SQL dari file file
- tersedia line ditor untuk memidifikasi perintah SQL
- terdapat seting untuk penanganan yang berhubungan dengan lingkungan
- hasil query d buat dalam sebuah laporn standart
- mengakses data base secara lokal dan remote
SQL *PLUS dipergunakan untuk mengakses Oracle Database Server. Untuk mengakses server dibuat sebuah service yang mengarahkan client ke server terlebih dahulu (Database Alias). Tahapan untuk membuat service tersebut sebagai berikut :
1. Pada dialog diatas diberi service name TPRAKTIKUM. Klik next
2.
Pilih protocol yang menghubungkan antara client dengan server. Pada
kesempatan ini untuk menghunbungkan client ke server mempergunakan
protocol TCP/IP. Berikutnya klik Next.
3. Pada field Host Name masukkan nama host name atau alamat IP address server ORACLE, misalnya nt5. Klik Next
4. Pada menu field Database SID, pada layar System IDentifier, masukkan nama database SID, misalnya lab4. Berikunya klik Next.
5. Untuk mencoba service name yang sudah dibuat, perlu dilakukan testing. Klik tombol Test Service, dan akan menuju layar testing.
6. Pada layar Conection Test masukkan username dan password. Silahkan masukkan Username scott dan Password tiger, klik tombol test, maka akan ditampilkan respon server. Jika sukses akan ada komentar ….successful..
jika gagal akan muncul komentar bahwa service yang dibuat gagal.
Setelah Service Name dibuat, kini masuk kedalam SQL *PLUS untuk mengakses database server.
Buka SQL *PLUS dari menu Start��Program��Oralce-OraHome81��
Aplication Development��SQL PLUS.
Akan muncul dialog SQL plus sebagai berikut :
Masukkan pada User Name system, password manager dan Host String dengan service name yang sudah dibuat tadi (TPRAKTIKUM).
Jika Login berhasil maka akan muncul Oracle SQL *PLUS sebagai berikut :
TABLESPACE
Untuk menyimpan obyek-obyek database dibutuhkan suatu ruang memori yang disebut TABLESPACE. Untuk mengakses tablespace dibutuhkan sebuah account user. Berikut ini perintah SQL untuk membuat tablespace, user dan perintah untuk manipulasi tabel.
1. Melihat tablespace
select * from user_free_space;
2. Membuat tablespace
SQL>Create tablespace nama-tablespace datafile ‘nama-datafile.dat’ size 1M
autoextend on next 500K maxsize 2M;
3. Mengubah ukuran tablespace
SQL>alter database datafile ‘nama-datafile.dat’ resize 2M;
4. Menambah ukuran tablespace dengan cara menambahkan data file baru
SQL>alter tablespace dataku add datafile ‘nama-datafile.dat’ size 2M;
5. Menghapus table space
SQL>drop tablespace nama-tablespace including contents;
Setelah Tablespace siap, maka langkah selanjutnya membuat account baru.
Account ini akan anda pergunakan selama praktikum. Account yang sudah ada ketika instalasi selesai adalah SYS, SYSTEM dan SCOTT. Untuk membuat account baru kita mempergunakan user SYS dan SYSTEM. Pergunakan tablespace yang tadi telah anda buat untuk menyimpan data anda selama praktikum.
1. Membuat user baru dan dengan tablespace yang dipakai misalnya dtpraktek.
SQL>create user nama_user identified by password-user default
tablespace dtpraktek;
Parameter nama_user adalah nama login, dan password-user adalah password user yang dipilih. Misalnya dipilih untuk nama_user user01.
2. Berikan hak akses user pada database. Misalnya hak akses grant dan resource.
SQL> grant connect to user01;
SQL> grant resource to user01;
3. Langkah berikutnya, lakukan sambungan dengan server dengan mempergunakan user_name yang sudah dibuat tadi.
SQL> connect user01
Enter password: ******
Connected.
4. Saat ini sudah tersambung dengan server dengan mempergunakan user01.
Buat tabel mahasiswa yang memiliki struktur nim varchar(5), nama
varchar(25), alamat varcahr(30).
SQL> create table mahasiswa (nim varchar(5), nama varchar(25), alamat
varchar(30));
5. Untuk melihat tabel yang ada dalam tablespace mempergunakan perintah :
SQL> select * from tab;
6. Untuk melihat struktur tabel dapat dilakukan dengan mempergunakan
perintah :
SQL>describe mahasiswa;
7. Perintah alter berguna untuk melakukan perubahan tabel diantaranya merubah tipe field, menambah field baru dan merubah nama tabel.
Merubah tipe field tabel :
SQL>alter table mahasiswa modify (nim number(5));
Menambah field tabel :
SQL>alter table mahasiswa add (usia number(2));
Mengganti nama tabel :
SQL>alter table mahasiswa rename to mhs;
8. Untuk menambahkan data kedalam tabel, pergunakan perintah insert
SQL>insert into mahasiswa values(1,’Sumanto’,’Sleman’);
9. Data yang sudah disimpan dalam tabel dapat diedit dengan memperguanakan perintah update.
SQL>update mahasiswa set nama = ‘Sumanto’ where nim=1;
10. Untuk keperluan penghapusan data, pergunakan perintah :
SQL>delete from mahasiswa where nim=1;
CONSTRAINT
Constraint (konstrain) adalah aturan yang diberikan pada suatu tabel supaya data yang dimasukkan lebih terjamin validitasnya.
1. Memberi konstrain pada saat membuat tabel :
SQL>create table mahasiswa(nim char(5) constraint pnim primary key, nama
char(20) constraint nnama not null, alamat char(30));
2. Memberi konstrain pada tabel yang sudah ada :
SQL>alter table mahasiswa add constraint pnim primary key (nim);
SQL>alter table mahasiswa modify (nama char(20) constraint nnama not null);
3. Menghapus konstrain :
SQL>alter table mahasiswa drop constraint nnama;
ORDER BY
Klausa
ORDER BY digunakan untuk mengurutkan data yang di-query. Klausa ORDER
BY diikuti dengan nama kolom atau kolom alias kemudian diikuti klausa
jenis urutan yang kita inginkan. Jenis urutan bisa ASC (ascending) atau
DESC (descending). Sebagai ilustrasi, tampilkan semua data dengan cara
diurutkan ascending berdasarkan nama mahasiswa.
SQL> select * from mahasiswa order by nama;
SQL> select * from mahasiswa order by nama desc;
WHERE
Klausa
LIKE WHERE digunakan untuk membatasi query menurut criteria tertentu.
Kriteria ini didefinisikan menggunakan bentuk perbandingan. Sebagai
ilustrasi, tampilkan data mahasiswa yang berasal dari Jogjakarta.
SQL> select * from mahasiswa where alamat = ‘Jogjakarta’;
LIKE
Klausa
LIKE digunakan untuk membatasi query menurut nilai tertentu. Nilai ini
merupakan sebagian dari nilai suatu field. Sebagai ilustrasi, akan
ditampilkan data mahasiswa yang memiliki nama yang nama akhirnya
mengandung huruf “ni”.
SQL> select * from mahasiswa where nama like ‘%ni’;
