SOAL/PERMASALAHAN
1. Apa pengertian dari Strored
Procedure ?
2. Apa yang dimaksud dengan function ?
3. Berikan sintak bentuk umum dari
Function !
4. Sebutkan tipe-tipe Trigger !
5. Apa yang dimaksud dengan Trigger ?
6. Jelaskan apa yang dimaksud Trigger
Timing ?
7. Jelaskan pengertian sub query !
LANDASAN TEORI
Stored Procedures
1. Stored procedure pada dasarnya adalah sebuah program yang
disimpan di dalam database server, karena stored procedure ini dapat menerima
suatu input parameter dan output parameter serta dapat menghasilkan suatu
message succes atau error.
2. Stored procedure sangat bermanfaat untuk aplikasi
database, baik untuk meningkatkan kinerja maupun untuk pemeliharaan. Karena
setelah procedure dijalankan di server, akses berikutnya akan menjadi lebih
mudah dan cepat karena eksekusi telah tersimpan di dalam memory.
3. Stored procedure juga dapat menjalankan procedure yang
lain, dan juga dapat memberitahukan bahwa ia telah dijalankan dengan sukses
atau gagal.
4. Stored procedure dapat menerima parameter input, dengan
menggunakan variabel lokal, dan menghasilkan data dengan menggunakan output
parameter, return code, result set dari statement SELECT.
Functions
Function adalah suatu blok PL/SQL yang memiliki konsep sama
dengan procedure, hanya saja pada function terdapat pengembalian nilai (return
value).
Karena function dapat mengembalikan sebuah nilai, function dapat diakses seperti layaknya sebuah variabel biasa.
Bentuk Umum :
CREATE OR REPLACE FUNCTION nama_function (parameter_1, …)
RETURN tipe_data AS
variabel_1 tipe_data;
…
BEGIN
statemen_1;
…
RETURN nilai_yang_dikembalikan;
END;
Karena function dapat mengembalikan sebuah nilai, function dapat diakses seperti layaknya sebuah variabel biasa.
Bentuk Umum :
CREATE OR REPLACE FUNCTION nama_function (parameter_1, …)
RETURN tipe_data AS
variabel_1 tipe_data;
…
BEGIN
statemen_1;
…
RETURN nilai_yang_dikembalikan;
END;
Statemen RETURN tipe_data diatas menunjukkan bahwa function akan mengembalikan nilai dengan tipe data tertentu
Statemen RETURN nilai_yang_dikembalikan berfungsi untuk mengembalikan nilai yang telah diproses dalam function
Contoh Function Tanpa Parameter :
(1)
CREATE OR REPLACE FUNCTION tulis_teks RETURN VARCHAR2 AS
S VARCHAR2(20)
BEGIN
S := ‘HALLO SEMUA’;
RETURN S;
END;
/
(2)
SET SERVEROUTPUT ON
DECLARE
X VARCHAR2(20);
BEGIN
X := tulis_teks;
DBMS_OUTPUT.PUT_LINE(X);
END;
/
Contoh Function Dengan Parameter :
(1)
CREATE OR REPLACE FUNCTION
pangkat (bil INTEGER, n INTEGER)
RETURN INTEGER AS
HASIL INTEGER(10);
I INTEGER;
BEGIN
HASIL := 1;
FOR I IN 1..n LOOP
HASIL := HASIL * bil;
END LOOP;
RETURN HASIL;
END;
/
(2)
SET SERVEROUTPUT ON
DECLARE
H INTEGER;
BEGIN
H := pangkat(2, 3);
DBMS_OUTPUT.PUT_LINE(‘Hasil = ‘ || TO_CHAR(H));
END;
/
Contoh Function Dalam Function :
(1)
CREATE OR REPLACE FUNCTION kuadrat (X NUMBER)
RETURN NUMBER AS
HASIL NUMBER(10);
BEGIN
HASIL := X * X;
RETURN HASIL;
END;
/
(2)
CREATE OR REPLACE FUNCTION determinan
(a NUMBER, b NUMBER, c NUMBER)
RETURN NUMBER AS
D NUMBER(10);
BEGIN
D := kuadrat(b) – (4 * a * c);
RETURN D;
END;
/
Trigger pada SQL
Trigger adalah blog pada PL/SQL atau
prosedur yang berhubungan dengan tabel, view, skema atau database yang
dijalankan secara implicit pada saat terjadi sebuah event. Trigger merupakan
store prosedur yang dijalankan secara automatis saat user melakukan modifikasi
data pada tabel. Modifikasi data yang dilakukan pada tabel yaitu berupa
perintah INSERT, UPDATE dan DELETE. INSERT, UPDATE dan DELETE bisa digabung
jadi satu trigger yang dinamakan Multiple Trigger.
Tipe dari Trigger adalah :
1. Application trigger : diaktifkan
pada saat terjadi event yang berhubungan dengan sebuah aplikasi.
2. Database trigger : diaktifkan
pada saat terjadi event yang berhubunga dengan data (seperti operasi DML) atau
event yang berhubungan dengan system (misal logon atau shut down) yang terjadi
pada sebuah skeam atau database.
Trigger perlu dibuat pada saat :
1. Membuat sebuah aksi tertentu
terhadap suatu event.
2. Memusatkan operasi global.
Trigger tidak perlu dibuat, jika :
1. Fungsionalitas yang diperlukan
suatu ada pada Oracle server.
2. Duplikat atau sama dengan fungsi
trigger yang lain.
Sintak penulisan dari database
trigger, berisi komponen berikut :
1. Trigger timing :
a. Untuk tabel : BEFORE, AFTER
b. Untuk view : INSTEAD OF
2. Trigger event : INSERT, UPDATE,
atau DELETE
3. Nama tabel : yaitu nama tabel
atau view yang berhubunga dengan trigger.
4. Tipe trigger : baris atau
pernyataan (statement)
5. Klausa WHEN : untuk kondisi
pembatasan pada trigger
6. Trigger body : bagian prosedur
yang dituliskan pada trigger.
Trigger timing adalah waktu kapan
trigger diaktifkan. Ada tiga macam trigger triming, yaitu :
1. BEFORE : trigger dijalankan
sebelum DML event pada tabel.
2. AFTER : trigger dijalankan
setelah DML event pada tabel.
3. INSTEAD OF : trigger dijalankan
pada sebuah view.
Trigger event ada 3 kemungkinan :
INSERT, UPDATE, atau DELETE. Pada saat trigger event UPDATE, kita dapat
memasukkan daftar kolom untuk mengidentifikasi kolom mana yang berubah untuk
mengaktifkan sebuah trigger (contoh : UPDATE OF salary ...). Jika tidak
ditentukan, maka perubahanya akan berlaku untuk semua kolom pada semua baris.
Tipe trigger ada 2 macam yaitu :
1. Statement : trigger dijalankan sekali saja pada saat
terjadi sebuah event. Statement trigger juga dijalankan sekali, meskipun tidak
ada satupun baris yang dipengaruhi oleh event yang terjadi
2. Row : trigger dijalankan pada setiap baris yang
dipengaruhi oleh terjadinya sebuah event. Row trigger tidak dijalankan jika
event dari trigger tidak berpengaruh pada satu baris pun.
Trigger body mendefinisikan tindakan
yang perlu dikerjakan pada saat terjadinya event yang mengakibatkan sebuah
trigger menjadi aktif.
syntax TRIGGER:
CREATE
[DEFINER = { user | CURRENT_USER }]
TRIGGER trigger_name trigger_time trigger_event
ON tbl_name FOR EACH ROW trigger_stmt
[DEFINER = { user | CURRENT_USER }]
TRIGGER trigger_name trigger_time trigger_event
ON tbl_name FOR EACH ROW trigger_stmt
trigger_name adalah nama trigger yang akan
dibuat.
trigger time adalah kapan trigger di eksekusi. bisa AFTER atau BEFORE
trigger_event adalah kejadian yang mengaktifkan trigger. trigger_event bisa berupa :
trigger time adalah kapan trigger di eksekusi. bisa AFTER atau BEFORE
trigger_event adalah kejadian yang mengaktifkan trigger. trigger_event bisa berupa :
- INSERT : trigger aktif ketika ada record baru yang di-insert ke tabel.
- UPDATE : trigger aktif ketika record di modify
- DELETE : trigger aktif ketika record di hapus dari tabel
triger_stmt adalah prosedur/perintah yang dijalankan ketika trigger aktif. jika kita mempunyai beberapa perintah, gunakan BEGIN ... END
Menghapus Triggers
DROP TRIGGER [nama trigger]
contoh kasus :
secara otomatis tabel mengurangi jumlah stok ketika ada proses penjualan / barang keluar.
script trigger :
create trigger kurangStok after
insert on detail_penjualan
for each row begin
update barang set jml_stok=jml_stok-NEW.jml_keluar where id_barang=NEW.id_barang;
end;
for each row begin
update barang set jml_stok=jml_stok-NEW.jml_keluar where id_barang=NEW.id_barang;
end;
Pengertian
Sub Query
Merupakan query yang terletak dalam query yang lain.
Contoh :
Akan ditampilkan nama pegawai yang mempunyai gaji paling besar :
Merupakan query yang terletak dalam query yang lain.
Contoh :
Akan ditampilkan nama pegawai yang mempunyai gaji paling besar :
- select nama from pegawai where sal = (select max(gaji) from pegawai);
Pemberian Hak
akses merupakan salah satu cara dalam mengatur tingkat
keamanan dalam database. Database Security sendiri dikategorikan berdasarkan 2
jenis yaitu : System Security dan Data Security
System
Security melindungi dan mengatur hak akses dan penggunaan
database pada level sistem, seperti username, password, alokasi disc space yang
diberikan pada user dan pengoperasian sistem yang dapat dioperasikan oleh user.
Data
Security yaitu memastikan tidak adanya data yang corrupt dan
terkontrolnya pengaksesan terhadap data tersebut.
System Privilege
System Privilege ini digunakan oleh database
administrator untuk memberikan hak istimewa kepada user dalam menjalankan
sistem.
Tipe-tipe hak istimewa DBA atau DBA Privileges :
- CREATE USER : penerima privilege dapat membuat user database baru
- DROP USER : penerima privilege dapat menghapus user
- DROP ANY TABLE : penerima privilege dapat menghapus tabel apa saja dalam suatu schema
- BACK UP ANY TABLE : penerima privilege dapat mem-back up tabel apa saja
- SELECT ANY TABLE : penerima privilege dapat membuat query, tabel dan view dalam suatu schema
- CREATE ANY TABLE : penerima privilege dapat membuat tabel pada schema apapun
MEMBUAT USER
Sebelum memberikan privilege, hal pertama yang
harus kita lakukan yaitu membuat user baru terlebih dahulu yang diberi nama
‘manager’.
Syntax :
CREATE USER userIDENTIFIED BY password
contoh :
CREATE USER managerIDENTIFIED BY 123
Pada tahap ini manager belum dapat melakukan
apapun, karena manager hanya dibuat tanpa diberikan suatu privilege oleh
database administrator.
HAK ISTIMEWA USER
Setelah user dibuat, maka database administrator
dapat memberikanya system privilege
contoh System privilege yang dapat diberikan :
- CREATE SESSION : untuk connect ke database
- CREATE TABLE : untuk membuat tabel
- CREATE SEQUENCE : membuat urutan dalam database schema
- CREATE VIEW : membuat sebuah VIEW dalam suatu schema
- CREATE PROCEDURE : membuat STORE PROCEDURE, FUNCTION atau PACKAGE dalam user’s schema
Memberikan System Privilege Kepada User
Contoh :
GRANT CREATE SESSION, CREATE TABLE, CREATE
SEQUENCE, CREATE VIEW, CREATE PROCEDURETO manager
Pada query diatas DBA memberikan 5 hak akses
sekaligus yaitu create session, create table, create sequence, create view, dan
create procedure
Hak akses
- Perintah untuk membuat user baru. Contoh: CREATE USER ade identified by ade;
- Perintah untuk merubah password user. Contoh ALTER USER ade identified by dedek;
- Perintah untuk melock atau mengunlock suatu user. Contoh: ALTER USER ade ACCOUNT LOCK; ALTER USER ade ACCOUNT UNLOCK;
- Perintah untuk menghapus user -> DROP USER ade;
- Perintah untuk menambah hak akses pada sebuah user -> GRANT insert, update ON pegawai TO ade;
- Perintah untuk membuat suatu role (kumpulan hak akses) -> CREATE ROLE manipulasi; GRANT dba TO manipulasi;
- Perintah untuk menghapus role -> DROP ROLE manipulasi;
- Perintah untuk mencabut hak akses dari sebuah user -> REVOKE manipulasi FROM ade;
- Perintah untuk melihat hak privilege yang ada -> SELECT name FROM system_privilege_map;
- Perintah untuk melihat hak akses apa saja yang dapat diberikan kepada sebuah user -> SELECT * FROM user_sys_privs;
- Perintah untuk melihat hak akses apa saja yang telah diberikan pada sebuah user -> SELECT * FROM user_tab_privs_recd;
- Perintah untuk melihat hak akses pada role -> SELECT * FROM dba_sys_privs WHERE grantee IN ('connect', 'resource');
- Perintah untuk melihat apakah sebuah role mempunyai password -> SELECT * FROM dba_rules;
PEMBAHASAN
Fuction adalah jenis PL/SQL block yang menghasilkan
suatu nilai. Function digunakan untuk melakukan perhitungan, mengecek
eksistensi dan kevalidan data. Parameter yang berlaku pada function hanya
parameter IN.
Sintak perintah:
CREATE [FOR REPLACE] FUNCTION function_name
[(parameter1[mode1] datatype1,
RETURN datatype
IS | AS
PL/SQL block;
1. contoh
pembuatan function pada user scott dengan table emp :
SQL> CREATE OR REPLACE FUNCTION
GET_SAL (P_EMPNO NUMBER)
2
RETURN NUMBER
3
AS
4
V_SAL NUMBER(10);
5
BEGIN
6
SELECT SAL INTO V_SAL
7
FROM EMP WHERE EMPNO=P_EMPNO;
8
--Nilai yang dikeluarkan
9
RETURN V_SAL;
10 END;
11 /
Cara menjalankan function:
a. menjalankan
function sebagai parameter dari suatu procedure :
SQL>
SET SERVERPOINT ON
SQL>
EXECUTE DBMS_OUTPUT.PUT_LINE(GET_sal(7369));
b. menjalankan
function sebagai bagian dari ekspresi :
SQL>
SET AUTOPRINT ON
SQL>
VARIABLE G_HASIL NUMBER;
SQL>
EXECUTE :G_HASIL := GET_sal(7788);
c. menjalankan
function pada perintah SELECT
SQL>
SELECT EMPNO,ENAME,GET_SAL(EMPNO) FROM EMP;
PROCEDURE
PROCEDURE
merupakan subprogram yang digunakan untuk melakukan proses tertentu. Procedure
yang disimpan di database biasa disebut stored procedure.
Sintak
perintah:
CREATE
[OR REPLACE] PROCEDURE procedure_name
[(parameter1
[mode1] datatype1,
Parameter2
[mode1] datatype2,…)]
IS|AS
PL/SQL
block;
Ada
tiga mode parameter untuk stored procedure, yaitu :
1. parameter
IN
CREATE
OR REPLACE PROCEDURE ISI_BARANG
(P_KODE
number, p_nama IN varchar2,
P_image
number, p_harga number, p_satuan
varchar2,
AS
BEGIN
INSERT
INTO BARANG VALUES (P_KODE,P_NAMA, p_image, p_harga, p_satuan, p_keterangan);
COMMIT;
END;
2. parameter
OUT
CREATE
OR REPLACE PROCEDURE CARI_BARANG
(P_KODE
IN number, P_NAMA OUT VARCHAR2)
AS
V_NAMA_BRG
VARCHAR2(50);
BEGIN
SELECT
NAMA_BRG INTO V_NAMA_BRG
FROM
BARANG
WHERE
KODE_BRG=P_KODE;
P_NAMA
:= V_NAMA_BRG;
END;
/
3. parameter
IN-OUT
CREATE
OR REPLACE PROCEDURE format_phone
(phone_no
IN OUT VARCHAR2) IS
BEGIN
Phone_no
:= ‘| | SUBSTR(phone_no,1,3) | |
‘)’
| | CREATE OR REPLACE PROCEDURE format_phone
(phone_no
IN OUT VARCHAR2) IS
BEGIN
Phone_no
:= ‘| | SUBSTR(phone_no,1,3) | |
‘)’
| | SUBSTR(phone_no,4,3)| |
‘-‘
| | SUBSTR(phone_no,7) ;
END
format_phone;
/
TRIGGER
Trigger
adalah sebuah blok PL/SQL yang disimpan dalam database yang secara otomatis
akan teraktivasi ketika terjadi suatu operasi pada sebuah table, view, schema,
atau database.
TRIGGER
TIMING
Terdapat 3 waktu ubtuk sebuah trigger teraktivasi :
·
Before
Dimana
sebuah trigger akan teraktivasi sebelum terjadinya proses DML pada table.
·
After
Dimana
sebuah trigger akan teraktivasi setelah terjadinya proses DML pada table.
·
Instead
of
Trigger
yang hanya berfungsi pada view dan biasanya digunakan untuk mengUpdate data
pada view kompleks
Berikut perintah Trigger :
1. Perintah
trigger yang dijalankan sebelum untuk suatu table
CREATE
OR REPLACE TRIGGER backup
BEFORE
UPDATE ON emp
BEGIN
RAISE_APPLICATION_ERROR(-20001,’Tabel
Emp Tidak Dapat Dirubah’);
END;
2. If-Else
Trigger
CREATE
OR REPLACE TRIGGER backup2
BEFORE
UPDATE DELETE OR INSERT ON emp
BEGIN
IF
UPDATING THEN
RAISE_APPLICATION_ERROR(-20001,
‘Tabel Emp Tidak Dapat Dirubah’);
ELSEIF
DELETING THEN
RAISE_APPLICATION_ERROR(-20002,
‘Tabel Emp Tidak Dapat Dirubah’);
ELSE
RAISE_APPLICATION_ERROR(-20003,
‘Tabel Emp Tidak Dapat Dirubah’);
END
IF;
END
/
3. Trigger
after
CREATE
OR REPLACE TRIGGER Konfirmasi
AFTER
DELETE OR INSERT OR UPDATE ON Emp
BEGIN
IF
DELETING THEN
DBMS_OUTPUT.PUT_LINE
(‘Data Telah di Delete’);
ELSEIF
UPDATING THEN
DMBS_OUTPUT.PUT_LINE(‘Data
Telah di Update’);
ELSEIF INSERTING THEN
DBMS_OUTPUT.PUT_LINE(‘Data
Telah di Insert’);
END
IF;
END;
/
4. Alias
di dalam Trigger
CREATE
OR REPLACE TRIGGER Validasi
BEFORE
INSERT ONE Emp
FOR
EACH ROW
BEGIN
IF
:new_sal < 2000 THEN
RAISE_APPLICATION_ERROR
(-20000,’Gaji Minimal $2000’);
END
IF;
END;
/
5. Membuat
trigger dalam DDL Statement
CREATE
OR REPLACE TRIGGER BackupData
BEFORE
DROP ON SCHEMA
BEGIN
RAISE_APPLICATION_ERROR(-20005,’Tidak
diperbolehkan untuk menghapus table pada schema ini’);
END;
/
Database
Security dapat dikelompokan menjadi 2 hal:
·
System security
·
Data security
Priveleges
·
Priveleges adalah ha katas sesuatu
·
System priveleges adalah hak akses
terhadap database
·
Object priveleges adalah hak untuk
memanipulasi isi dari database object
·
Schema adalah kumpulan object, semisal
table, view dan sequence
Pembuatan user
CREATE USER user
IDENTIFIED BY password
Contoh :
CREATE USER scott
IDENTIFIED BY TIGER;
User created.
User System Priveleges
Sekali user dibuat,
maka DBA memberikan system privilege yang spesifik terhadap user tersebut,
yaitu :
CREATE SESSION : untuk
burhubungan dengan database
CREATE TABLE : untuk
membuat table dalam skema user
CREATE SEQUENCE : untuk
membuat sequence
CREATE VIEW : untuk
membuat view
CREATE PROCEDURE :
untuk membuat procedure
Granting system
priveleges
DBA dapat memberi atau
grant privilege system terhadap suatu user tertentu
SQL> GRANT create
session, create table,
2 create sequence,
create view
3 TO scott;
Grant succeded
Role adalah nama dari sekumpulan privilege yang saling
berelasi dan diberikan pada user.
Pembuatan dan Granting
Privelege sebuah role
Membuat role :
CREATE ROLE manager;
Grant privilege ke
role:
GRANT create table, create view
TO manager;
GRANT role ke user:
GRANT manager TO DEHAAN, KOCHHAR;
Merubah password :
ALTER USER scott
IDENTIFIED BY Lion;
Object privilege
Object privilege adalah
privilege yang sesuai untuk membentuk perlakukan terhadap object database.
Granting Object Privilege
Grant query privilege
pada table EMPLOYEES:
GRANT
select
ON
employees
TO
sue, rich;
Grant privilege untuk
mengubah kolom yang spesifik terhadap user dan role :
GRANT update (department_name, location_id)
ON departments
TO scott, manager;
Pengguanaan keyword
With Grant Option dan Public
Contoh pengguanaan WITH
GRANT OPTION :
GRANT select, insert
ON departments
TO scott
WITH GRANT OPTION;
Menghapus Object
Privilege
Untuk menghapus
privilege, digunakan perintah REVOKE. Dengan perintah REVOKE, privilege yang
diberikan ke user yang lain melalui WITH GRANT OPTION juga akan dihapus.
Missal sebagai user,
Alice menghapus privilege SELECT dan Insert yang diberikan untuk user scott
pada table DEPARTMENTS.
REVOKE select, insert
ON departments
FROM scott;
Database Link
Koneksi database link
memungkinkan local user untuk emngakses data pada remote database.
Untuk mmbuat database
link :
CREATE PUBLIC DATABASE
LINK hq.acme.com
USING ‘sales’;
Menulis AQL statement
yang menggunakan database link :
SELECT *
DROM emp@HQ.ACME.COM;
KESIMPULAN
Stored procedure pada dasarnya
adalah sebuah program yang disimpan di dalam database server, karena stored
procedure ini dapat menerima suatu input parameter dan output parameter serta
dapat menghasilkan suatu message succes atau error.
Function
adalah suatu blok PL/SQL yang memiliki konsep sama dengan procedure, hanya saja
pada function terdapat pengembalian nilai (return value).
Trigger merupakan store prosedur yang dijalankan secara automatis saat user melakukan modifikasi data pada tabel.
Trigger merupakan store prosedur yang dijalankan secara automatis saat user melakukan modifikasi data pada tabel.
Pengertian
Sub Query
Merupakan query yang terletak dalam query yang lain.
Contoh :
Akan ditampilkan nama pegawai yang mempunyai gaji paling besar :
Merupakan query yang terletak dalam query yang lain.
Contoh :
Akan ditampilkan nama pegawai yang mempunyai gaji paling besar :
select nama from pegawai where sal = (select max(gaji) from pegawai);
Trigger
timing adalah waktu kapan trigger diaktifkan. Ada tiga macam trigger triming,
yaitu :
1.
BEFORE : trigger dijalankan sebelum DML event
pada tabel.
2.
AFTER : trigger dijalankan setelah
DML event pada tabel.
3.
INSTEAD OF : trigger dijalankan pada
sebuah view.
DAFTAR PUSTAKA
http://risnotes.com/2012/01/mengatur-hak-akses/
