♥ tugas 7 DBMS ♥

DBMS 7 [ TRIGGER ]

Nama    : Irma Santikarama

Kelas     : PIS 0904

NIM       : 30108255

create or replace trigger nama_trigger

timing   --> isinya before, after, instaead of

event1 or event2 or event3    -->insert, update, delete, logon, logoff

ON nama_objek   --> nama tabel, nama user, database

begin

...

end;

/

Contoh trigger

create or replace trigger anulogin

after

logon

on database

begin

insert into rekaman values(user, sysdate, sysdate, 'login dari database');

dbms_output.put_line('haloo '||user);

end;

/

create or replace trigger anuinsert

after

insert

on regions

for each row

begin

insert into rekaman values(user,sysdate,sysdate,

'tambah data ke tabel regions dengan id '||:new.region_id);

end;

/

♥ tugas 6 DBMS ♥

DBMS 6 [ FUNCTION ]

Nama    : Irma Santikarama

Kelas     : PIS 0904

NIM       : 30108255

CREATE OR REPLACE FUNCTION function_name

(argument1 mode1 datatype1,

argument2 mode2 datatype2,

. . .) RETURN datatype

IS

Daftar variabel lokal jika ada

BEGIN

…

RETURN …. ;

END;

/

Note:

Tidak wajib ada, sesuaikan dengan kebutuhan

Daftar argumen tidak wajib ada, sesuaikan dengan kubutuhan

Pilihan mode: IN atau OUT atau IN OUT, defaultnya adalah IN

Selain IS boleh juga pake AS

Contoh function dengan parameter

CREATE OR REPLACE FUNCTION check_sal(empno employees.employee_id%TYPE)

RETURN Varchar IS

dept_id employees.department_id%TYPE;

sal employees.salary%TYPE;

avg_sal employees.salary%TYPE;

BEGIN

SELECT salary,department_id INTO sal,dept_id

FROM employees WHERE employee_id=empno;

SELECT avg(salary) INTO avg_sal FROM employees

WHERE department_id=dept_id;

IF sal > avg_sal THEN

RETURN 'Gaji Di Atas Rata-Rata';

ELSE

RETURN 'Gaji Di Bawah Rata-Rata';

END IF;

EXCEPTION

WHEN no_data_found THEN

RETURN 'ID Karyawan tidak ditemukan';

END;

/

♥ tugas 5 DBMS ♥

DBMS 5 [ PROSEDURE ]

Nama    : Irma Santikarama

Kelas     : PIS 0904

NIM       : 30108255

CREATE OR REPLACE PROCEDURE procedure_name

(parameter1 mode1 datatype1,

parameter2 mode2 datatype2,

. . .)

IS

Daftar variabel lokal jika ada

BEGIN

…

END;

/

Note:

Tidak wajib ada, sesuaikan dengan kebutuhan

Daftar parameter tidak wajib ada, sesuaikan dengan kubutuhan

Pilihan mode: IN atau OUT atau IN OUT, defaultnya adalah IN

Selain IS boleh juga pake AS

Contoh Prosedur Tanpa Parameter

CREATE OR REPLACE PROCEDURE add_dept IS

dept_id departments.department_id%TYPE;

dept_name departments.department_name%TYPE;

BEGIN

dept_id:=280;

dept_name:='ST-Curriculum';

INSERT INTO departments (department_id,department_name)

VALUES(dept_id,dept_name);

DBMS_OUTPUT.PUT_LINE(' Berhasil menginsert'||

SQL%ROWCOUNT ||' baris ');

Exception

When dup_val_on_index then

DBMS_OUTPUT.PUT_LINE(‘Datanya udah ada‘);

END;

/

Contoh Prosedure Dengan Parameter

CREATE OR REPLACE PROCEDURE add_dept_pake_param

(dept_id IN departments.department_id%TYPE,

dept_name departments.department_name%TYPE)

IS

BEGIN

INSERT INTO departments (department_id,department_name)

VALUES(dept_id,dept_name);

DBMS_OUTPUT.PUT_LINE(' Berhasil menginsert'||

SQL%ROWCOUNT ||' baris ');

Exception

When dup_val_on_index then

DBMS_OUTPUT.PUT_LINE(‘Datanya udah ada‘);

END;

/

♥ tugas 4 DBMS ♥

Irma Santikarama
PIS0904 30108255

CURSOR IMPLISIT

select [daftar kolom]
into [daftar variabel]
from [daftar tabel]
where [syarat]
------------------------------------------------------------
select first_name, last_name, salary
into nama_depan, nama_belakang, gaji
from employees
where first_name = ‘Susan’;

ex :
declare
nama_cari varchar(15);
nama_depan varchar(15);
nama_belakang varchar(15);
gaji varchar(10);
------------------------------------------------------------------
declare
nama_cari employees.first_name%type;
nama_depan employees.first_name%type;
nama_belakang employees.last_name%type;
gaji employees.salary%type;


Contoh :
declare
nama_cari employees.first_name%type;
nama_depan employees.first_name%type;
nama_belakang employees.last_name%type;
gaji employees.salary%type;
begin
nama_cari := '&masukkan_nama';
select first_name, last_name, salary
into nama_depan, nama_belakang, gaji
from employees
where first_name = nama_cari;
dbms_output.put_line('pegawai bernama '||nama_depan||
' '||nama_belakang||' gajinya adalah USD '||gaji);
end;
/

CURSOR EKSPLISIT

DOFC à (DECLARE, OPEN, FETCH, CLOSE)
Declare
cursor [nama_cursor_bebas] is
select [daftar_kolom]
from [daftar_tabel]
where [syarat];
begin
open [nama_cursor_bebas];
loop
fetch [nama_cursor_bebas] into [daftar_variabel];
exit when [nama_cursor_bebas] %notfound;
[aksi_lain]
end loop;
close [nama_cursor_bebas];
end;
/

Ex:
declare
nama_cari employees.first_name%type;
nama_depan employees.first_name%type;
nama_belakang employees.last_name%type;
gaji employees.salary%type;
cursor anu is
select first_name, last_name, salary
from employees
where first_name = nama_cari;
begin
nama_cari := '&masukkan_nama';
open anu;
loop
fetch anu into nama_depan, nama_belakang, gaji;
exit when anu%notfound;
dbms_output.put_line('pegawai bernama '||nama_depan||' ' ||nama_belakang||' gajinya adalah USD '||gaji);
end loop;
close anu;
end;
/

♥ tugas 3 DBMS ♥

Irma Santikarama
PIS0904 30108255

SQL_3
SIMPLE LOOP

LOOP
Statement-statement;
END LOOP;


Statement For

FOR var IN [REVERSE] indeks_min .. indeks_max LOOP
Statement-statement;
END LOOP;

Ex :
Declare
x number(3);
BEGIN
X:=&berapa;
FOR i IN 1..x LOOP
if mod(i,2) = 1 then
DBMS_OUTPUT.PUT_LINE('Ini angka ' || i );
else
DBMS_OUTPUT.PUT_LINE('Ini angka ' || ‘i’ );
end if;
END LOOP;
END;
/



Statement While

WHILE kondisi LOOP
Statement-statement;
END LOOP;

Ex :
DECLARE
i NUMBER(3);
hasil NUMBER(5);
BEGIN
i:=1;
WHILE i <= 10 LOOP
hasil := i**3;
DBMS_OUTPUT.PUT_LINE(i ||' pangkat 3 = '|| hasil );
i := i + 1;
END LOOP;
END;
/



Statement Exit dan Exit When

EXIT WHEN kondisi;

IF kondisi THEN
EXIT;
END IF:

Ex :
DECLARE
total NUMBER(9);
counter NUMBER(6);
BEGIN
total := 0;
counter := 10;
LOOP
total := total + counter;
DBMS_OUTPUT.PUT_LINE(' Total: ' || total);
EXIT WHEN total > 50;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Counter: ' || counter);
END;
/

♥ tugas 2 DNMS ♥

Nama : Irma Santikarama
NIM : 30108255
Kelas : PIS0904

SQL_2

KONSEP PROSEDURAL

Konsep procedural dalam SQL merupakan sebuah teknologi yang memungkinkan kita membuat blok program layaknya dalam bahasa pemograman procedural seperti C, COBOL, dll. Dengan menggunakan SQL, aplikasi dapat mengirimkan sekumpulan stetment ke database secara langsung dalam satu waktu.
Apabila kita membuat prosedur atau fungsidalam database (stored procedure/stored function) maka aplikasi cukup melakukan pemanggilan dan menunggu hasil yang diberikan oleh database. Pemanggilan prosedur semacam ini sering disebut RPC (Remote Procedure Call).
Blok SQL secara umum terbagi menjadi tiga bagian :

DECLARE
--berisi deklarasi variable, konstanta, prosedur, ataupun fungsi
BEGIN
--berisi statement statement yang akan dieksekusi
EXCEPTION
--berisi perintah untuk mengatasi kesalahan ang mungkin muncul
END;



PROSEDUR
Prosedur adalah blok PL/SQL yang menyimpan sekumpulan perintah tanpa disertai pengambilan nilai.


CREATE [OR REPLACE] PROSEDUR name_prosedur
(parameter1 tipedata, parameter2 tipedata,……………)
IS
Variable_variable_lokal tipedata;
BEGIN
Statement_statment;
……
END;



FUNGSI
Fungsi adalah blok PL/SQL yang dapat menggembangkan nilai. Oleh karena itu perlu ditambahkan statement RETURN untuk proses pengembalian data.

CREATE [OR REPLACE] FUCTION name_fungsi
(parameter1 tipedata, parameter2 tipedata,……………)
RETURN tipe_data_fungsi IS
Variable_variable_lokal tipedata;
BEGIN
Statement_statment;
……
RETURN nilai_fungsi;
END;


TRIGGER
Tingger adalah blok PL/SQL yang disimpan dalam database dan akan diaktivasi ketika akan melakukan statement statement SQL terhadap sebuah table.


CREATE [OR REPLACE] TRINGGER nama_tringger
BEFORE|AFTER [INSERT|UPDATE|DELETE] ON nama_table
FOR EACH ROW
DECLARE
--berisi deklarasi variable
BEGIN
--berisi statement statement yang akan dieksekusi
END;

Naming Convention yang Baik

Naming convention yang tepat akan menbuat kode statement PL/SQL lebih mudah dibaca dan lebih mudah dimengerti. Keuntungan dari naming convention adalah :
• Lebih mudah dibaca
• Lebih mudah dipahami
• Memberikan informasi mengenai fungtionalitas berdasarkan identifier
• Kemudahan dalam proses debug
• Memastikan konsistensi diantara banyak kode yang dibuat oleh developer yang berbeda
•
STRUKTUR PERCABANGAN
Percabangan adalah suatu control untuk pengecekan yang dilakukan sebelum statement statement dalam sebuah blok PL/SQL dieksekusi.
STATEMAN IF
Penggunaan statement if dapat dibagi menjadi tiga bagian :
1. Satu kondisi (IF-THEN)

IF kondisi THEN
Statement statement;
END IF;

2. Dua kondisi (IF-THEN-ELSE)

IF kondisi THEN
Statement statement A;
ELSE
Statement statement B;
END IF;


3. Tiga kondisi atau lebh kondisi (IF-THEN-ELSE IF)

IF kondisi_1 THEN
Statement statement A;
ELSE IF kondisi_2 THEN
Statement statement B;
………
ELSE IF kondisi_n THEN
Statement statemen n;
[ELSE
Statement statement lain;]
END;


Contoh :

declare
nama varchar(20);
harga number(6);
diskon number(2);
jumlah number(6);
dibayar number(6);

begin
nama:='&nama';
harga:=&harga;
diskon:=&diskon;
jumlah:=(harga*diskon/100);
dibayar:=harga-jumlah;
dbms_output.put_line('selamat datang '||nama);
dbms_output.put_line('harga yang harus dibayar Rp '||dibayar);
if (diskon<20) then dbms_output.put_line('pelit amat ih');
else dbms_output.put_line('baik banget ouyy !!');
end if;

end;
/

♥ tugas 1 DBMS ♥

Resume insert, update, delete 
Tugas DBMS
Irma Santikarama (30108255) PIS0904

PEMBUATAN TABLE
Format Pembuatan Table :
CREATE TABLE Nama_Tabel (
Nama_kolom1 tipe_kolom1,
Nama_kolom2 tipe_kolom2,
Constraint1, Constraint2
);
Format Constraint Primary Key:
CONSTRAINT Nama_Constraint PRIMARY KEY (Nama_Kolom);
Format Constraint Foreign Key:
CONSTRAINT Nama_Constraint FOREIGN KEY (Nama_KolomFK) REFERENCES Nama_Tabel_Induk (Nama_KolomTI);

Contoh :
create table anggota(
no_anggota number(6),
nama_anggota varchar(50) not null,
alamat_anggota varchar (100),
constraint pk1 primary key(no_anggota)
);



ALTER TABLE
Format Menambah Kolom/Constraint Table :
ALTER TABLE Nama_Tabel ADD (
Nama_kolom1 tipe_kolom1,
Nama_kolom2 tipe_kolom2,
Constraint1, Constraint2
);
Format Memodifikasi Kolom Table :
ALTER TABLE Nama_Tabel MODIFY (
Nama_kolom1 tipe_kolom1,
Nama_kolom2 tipe_kolom2
);

Format Membuang Kolom/Constraint Table :
ALTER TABLE Nama_Tabel DROP COLUMN Nama_Kolom;
Atau
ALTER TABLE Nama_Table DROP CONSTRAINT Nama_Constraint;
Contoh :
alter table anggota add(
golongan_darah char(1),
constraint c foreign key (golongan_darah) references X(anu)
);

DROP TABLE
Format Penghancuran Table :
DROP TABLE Nama_Tabel;
Contoh :
drop table anggota;

INSERT TABLE
Format Menambah Data Jika Semua Kolom Ingin Diisi:
INSERT INTO Nama_Tabel VALUES (
Data_kolom1,
Data_kolom2,
Data_kolom3
);
Format Menambah Data Jika Hanya Kolom Tertentu Yang Diisi:
INSERT INTO Nama_Tabel (Nama_Kolom1, Nama_Kolom2)
VALUES (
Data_kolom1,
Data_kolom2
);
Contoh :
insert into anggota values(
101,
‘Ngatimin van der Woodsen’,
‘Jl. Entah Berantah nomor sekian’
);

UPDATE TABLE
Format Mengubah Data:
UPDATE Nama_Tabel
SET Nama_Kolom = data_baru
WHERE syarat_perubahan_data;
Contoh :
update anggota set no_anggota = 201
where no_anggota = 101;

DELETE TABLE
Format Menghapus Data:
DELETE FROM Nama_Tabel
WHERE syarat_perubahan_data;
Contoh :
delete from anggota
where no_anggota = 101;

SELECT TABLE
Format Query Data:
SELECT daftar_kolom
FROM daftar_tabel
WHERE syarat_dan_atau_foreign_key
Fungsi Agregat: MIN(), MAX(), AVG(), SUM(), COUNT()
SELECT daftar_kolom_non_fungsi_ag, fungsi_ag(nama_kolom1), fungsi_ag(nama_kolom2)
FROM daftar_tabel
WHERE syarat_dan_atau_foreign_key
GROUP BY daftar_kolom_non_fungsi_ag

(*) Fungsi agregat dapat digunakan sebagai pengganti daftar kolom, digunakan setelah SELECT...
(*) Jika setelah SELECT digunakan fungsi agregat bersama kolom yang lain yang bukan fungsi agregat maka HARUS menggunakan klausa GROUP BY.
Contoh :
select nama_anggota, judul_buku
from anggota, buku, peminjaman
where anggota.no_anggota = peminjaman.no_anggota
and buku.id_buku = peminjaman.id_buku
and judul_buku like 'W_______%';

Simple Anonymous PL/SQL
Panduan:
1. Bagian declare diisi dengan daftar variabel dan tipe datanya masing-masing
2. Bagian inti program selalu diawali dengan BEGIN dan diakhiri dengan END; serta /
3. PL/SQL dapat disimpan sebagai file *.sql dan di-run sebagai script dengan perintah @ fullpath, dimana fullpath tidak boleh mengandung spasi
contoh: @ D:\BelajarOracle\Pertemuan3\anu.sql;
4. Jika blok PL/SQL menmapilkan output ke layar maka setelah login kita harus mengaktifkan screen output dengan cara menjalankan perintah SET SERVEROUTPUT ON setelah login (cukup satu kali tiap login)
5. Aturan nama variabel: tidak boleh pakai spasi, diawali selain huruf atau mengandung simbol tanda baca

Format Deklarasi Variabel:
Nama_Var Tipe_data;
atau
Nama_Var Nama_Tabel.Nama_Kolom%Type;

Format Pembacaan Single Row Data:
SELECT daftar_kolom
INTO daftar_variabel
FROM daftar_tabel
WHERE syarat;

(*) Format di atas tidak bisa digunakan jika hasil query lebih dari 1 baris, kecuali jika dilengkapi dengan exception

Format Penerimaan Input Dari User:
Nama_Var := &prompt;
atau
Nama_Var := ‘&prompt’;

Note: Prompt bebas namun tidak boleh menggunakan spasi dan simbol selain alfanumerik. Jika Input bertipe selain tipe data number maka &prompt harus diapit petik tunggal (‘)

Contoh :
Declare
jari2 number(5,2);
keliling number(7,2);
luas number(7,2);

begin
jari2 := &jari2;
keliling := 2 * 3.14 * jari2;
luas := 3.14 * power(jari2,2);
dbms_output.put_line('Jari-jari lingkaran = '||jari2);
dbms_output.put_line('keliling lingkaran = '||keliling);
dbms_output.put_line('luas lingkaran = '||luas);
end;
/