PL SQL DENGAN SQL PLUS

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’;