Membuat Functions di PostgreSQL ~ NGODING

Kali ini aku menuliskan cara membuat function di PostgreSQL, pada contoh yang akan saya buat ini terdapat saya akan membuat 6 tabel, diantaranya "mahasiswa", "mata_kuliah", "nilai_semester", "nilai_sp", "nilai_upas", "nilai_upta" . langsung saja disimak .

perta kita buat tabel mahasiswa .

CREATE TABLE mahasiswa

(

nim character varying(10) NOT NULL,

nama character varying(30),

prodi character varying(5),

CONSTRAINT mahasiswa_pkey PRIMARY KEY (nim)

)

WITH (

OIDS=FALSE);

ALTER TABLE mahasiswa OWNER TO postgres;

buat tabel mata_kuliah

CREATE TABLE mata_kuliah(

kdmtk character varying(5) NOT NULL,

nm_mtk character varying(15),

sks integer,

smt integer,

CONSTRAINT mata_kuliah_pkey PRIMARY KEY (kdmtk)

)

WITH (

OIDS=FALSE

);

ALTER TABLE mata_kuliah OWNER TO postgres;

membuat tabel nilai_semester

CREATE TABLE nilai_semester(

nim character varying(10) NOT NULL,

kdmtk character varying(5),

smt integer,

tahun numeric,

great character varying(1),

CONSTRAINT nilai_semester_kdmtk_fkey FOREIGN KEY (kdmtk)

REFERENCES mata_kuliah (kdmtk) MATCH SIMPLE

ON UPDATE CASCADE ON DELETE CASCADE,

CONSTRAINT nilai_semester_nim_fkey FOREIGN KEY (nim)

REFERENCES mahasiswa (nim) MATCH SIMPLE

ON UPDATE CASCADE ON DELETE CASCADE

)

WITH (

OIDS=FALSE

);

ALTER TABLE nilai_semester OWNER TO postgres;

membuat tabel nilai_sp

CREATE TABLE nilai_sp(

nim character varying(10) NOT NULL,

kdmtk character varying(5),

smt integer,

tahun numeric,

great character varying(1),

CONSTRAINT nilai_sp_kdmtk_fkey FOREIGN KEY (kdmtk)

REFERENCES mata_kuliah (kdmtk) MATCH SIMPLE

ON UPDATE CASCADE ON DELETE CASCADE,

CONSTRAINT nilai_sp_nim_fkey FOREIGN KEY (nim)

REFERENCES mahasiswa (nim) MATCH SIMPLE

ON UPDATE CASCADE ON DELETE CASCADE

)

WITH (

OIDS=FALSE

);

ALTER TABLE nilai_sp OWNER TO postgres;

membuat tabel nilai_upas

CREATE TABLE nilai_upas(

nim character varying(10) NOT NULL,

kdmtk character varying(5),

smt integer,

tahun numeric,

great character varying(1),

CONSTRAINT nilai_upas_kdmtk_fkey FOREIGN KEY (kdmtk)

REFERENCES mata_kuliah (kdmtk) MATCH SIMPLE

ON UPDATE CASCADE ON DELETE CASCADE,

CONSTRAINT nilai_upas_nim_fkey FOREIGN KEY (nim)

REFERENCES mahasiswa (nim) MATCH SIMPLE

ON UPDATE CASCADE ON DELETE CASCADE

)

WITH (

OIDS=FALSE

);

ALTER TABLE nilai_upas OWNER TO postgres;

membuat tabel nilai_upta

CREATE TABLE nilai_upta(

nim character varying(10) NOT NULL,

kdmtk character varying(5),

smt integer,

tahun numeric,

great character varying(1),

CONSTRAINT nilai_upta_kdmtk_fkey FOREIGN KEY (kdmtk)

REFERENCES mata_kuliah (kdmtk) MATCH SIMPLE

ON UPDATE CASCADE ON DELETE CASCADE,

CONSTRAINT nilai_upta_nim_fkey FOREIGN KEY (nim)

REFERENCES mahasiswa (nim) MATCH SIMPLE

ON UPDATE CASCADE ON DELETE CASCADE

)

WITH (

OIDS=FALSE

);

ALTER TABLE nilai_upta OWNER TO postgres;

Pada Data Definition Language (DDL) yang telah kita buat tadi, sudah mempunyai relasi antar tabel. Contoh pada tabel “nilai_semester” terdapat query.

CONSTRAINT nilai_semester_kdmtk_fkey FOREIGN KEY (kdmtk)

REFERENCES mata_kuliah (kdmtk) MATCH SIMPLE

Pada query diatas mempunyai foreign key pada attribute (kdmtk) yang mereferences attribute ( kdmtk) di tabel mata_kuliah. jadi jika kita mengisi data pada tabel “nilai_semester” tetapi pada attribute(kdmtk) pada tabel “mata_kuliah” belum ada atau belum ditambahkan maka terjadi error / gagal menambahkan data..maka dengan relasi ini user tidak bisa menambahkan data sembarangan. Itu salah satu fungsi daripada relasi antar tabel .

Oke setelah kita selesai membuat tabel, sekarang kita memasukkan data ke tabel yang telah kita buat tadi.

memasukkan data ke tabel nilai_semester.

INSERT INTO nilai_semester(

nim, kdmtk, smt, tahun, great)

VALUES (2009420028, 'TI01', 3, 2008, 'B'),

(2009420028, 'TI02', 3, 2008, 'A'),

(2009420029, 'TI01', 3, 2008, 'C'),

(2009420029, 'TI02', 3, 2008, 'D'),

(2009420030, 'TI01', 3, 2008, 'C'),

(2009420030, 'TI02', 3, 2008, 'A'),

(2009420031, 'TI01', 3, 2008, 'D'),

(2009420031, 'TI02', 3, 2008, 'D');

memasukkan data ke tabel mahasiswa

INSERT INTO mahasiswa(

nim, nama,prodi)

VALUES (2009420028, 'okin luberto','TIK'),

(2009420029, 'mahasiswa1','TIK'),

(2009420030, ' mahasiswa2','TIK'),

(2009420031, ' mahasiswa3','TIK'),

(2009420032, ' mahasiswa4','TIK');

memasukkan data ke tabel mata_kuliah

INSERT INTO mata_kuliah(

kdmtk,nm_mtk,sks,smt)

VALUES ('TI01', 'ALGO 1',3,5),

('TI02', 'SQBD',3,5);

memasukkan data ke tabel nilai_sp

INSERT INTO nilai_sp(

nim, kdmtk, smt, tahun, great)

VALUES (2009420028, 'TI01', 3, 2008, 'C'),

(2009420028, 'TI02', 3, 2008, 'D'),

(2009420029, 'TI01', 3, 2008, 'C'),

(2009420029, 'TI02', 3, 2008, 'C'),

(2009420030, 'TI01', 3, 2008, 'C'),

(2009420030, 'TI02', 3, 2008, 'C'),

(2009420031, 'TI01', 3, 2008, 'C'),

(2009420031, 'TI02', 3, 2008, 'D');

memasukkan data ke tabel nilai_upas

INSERT INTO nilai_upas(

nim, kdmtk, smt, tahun, great)

VALUES (2009420028, 'TI01', 3, 2008, 'A'),

(2009420028, 'TI02', 3, 2008, 'A'),

(2009420029, 'TI01', 3, 2008, 'B'),

(2009420029, 'TI02', 3, 2008, 'B'),

(2009420030, 'TI01', 3, 2008, 'C'),

(2009420030, 'TI02', 3, 2008, 'B'),

(2009420031, 'TI01', 3, 2008, 'C'),

(2009420031, 'TI02', 3, 2008, 'C');

memasukkan data ke tabel nilai_upta

INSERT INTO nilai_upta(

nim, kdmtk, smt, tahun, great)

VALUES (2009420028, 'TI01', 3, 2008, 'A'),

(2009420028, 'TI02', 3, 2008, 'C'),

(2009420029, 'TI01', 3, 2008, 'B'),

(2009420029, 'TI02', 3, 2008, 'B'),

(2009420030, 'TI01', 3, 2008, 'C'),

(2009420030, 'TI02', 3, 2008, 'B'),

(2009420031, 'TI01', 3, 2008, 'A'),

(2009420031, 'TI02', 3, 2008, 'D');

nah setelah memasukkan data ke tabel, kita cek apakah data yang kita masukkan tadi berhasil tersimpan

SELECT * from mata_kuliah

SELECT * from mahasiswa

SELECT * from nilai_semester

SELECT * from nilai_sp

SELECT * from nilai_upas

SELECT * from nilai_upta

setelah semua okke, baru kita buat functions - nya, pada contoh ini saya akan membuat 2 functions .

1. get_khs untuk menampilkan hasil ujiannya diambil dari ( diambil nilai terbesar dari 4 nilai ujian (nilai_semester, nilai_sp, nilai_upta, nilai_upas ) di kalikan jumlah sks dari mata_kulaih yang diambil.

2. get_ipk untuk menampilkan nilai_ipk ( diambil dari (jumlah hasil semua nilai terbesar dari tiap mata_kulaih) / ( jumlah sks yang diambil )

MEMBUAT FUNCTION get_khs.

create or replace function get_khs(varchar)

returns setof record as

declare

nim_mhs alias for $1;

out record;

begin

for out in execute

'select nim,(select nama from mahasiswa where nim=ddd.nim) as nama,

kdmtk,(select nm_mtk from mata_kuliah where kdmtk=ddd.kdmtk) as nm_mtk,

smt,sks,tahun,nilai_akhir,

(case when nilai_akhir=''A'' then sks*4

when nilai_akhir=''B'' then sks*3

when nilai_akhir=''C'' then sks*2

when nilai_akhir=''D'' then sks*1

else sks*0 end)::integer as nxk from(

select nim,kdmtk,smt,(select sks from mata_kuliah where kdmtk=dd.kdmtk) as sks,tahun,nilai_akhir from (

select nim,kdmtk,smt,tahun,min(great) as nilai_akhir from (

select * from nilai_semester union all

select * from nilai_upta union all

select * from nilai_upas union all

select * from nilai_sp

) as d group by nim,kdmtk,smt,tahun having nim='''||nim_mhs||''' order by nim

) as dd

) as ddd'

loop

return next out;

end loop;

return;

end

$$ language 'plpgsql';

tau gg maksud dari query pada function get_khs diatas okelah akan saya jelaskan, agar mudah dipahami saya akan menjelaskan mulai dari query yang paling bawah, atau lapisan yang paling tengah. karena, jika saya menjelaskan mulai dari yang paling atas mungkin akan susah untuk dimengeti ( kalau menurut saya hehehe ).

nah pada query diatas telah saya beri warna yang berbeda dan berlapis2, untuk mempermudah menjelaskannya. untuk memulai membuat function semua hampir sama caranya create or replace function [nama functions](varchar) dan seterusnya seperti pada query diatas.

berikut penjelasan pada lapisan query diatas .

lapisan merah --> terdapat select * [nama tabel] karena pada ke 4 tabel nilai yang saya buat attributnya sama jadi saya bisa menampilkan semua data pada ke 4 tabel secara bersamaan menggunakan union all

lapisan kuning --> meminta menampilkan attribut nim,kdmtk,smt,tahun,min(great) as nilai_akhir, "min" mengambil nilai terbaik dari ke 4 tabel hasil ujian ."as nilai_akhir" dan diberi nama "nilai_akhir" ."having nim='''||nim_mhs||''' " akan menampilkan data meurut nim yang akan dimasukkan ke variabel "nim_mhs" ,"order by nim" hasil output diurutkan menurut nim .

lapisan hijau --> hampir sama dengan lapisan kuning, "(select sks from mata_kuliah where kdmtk=dd.kdmtk) as sks" mengambil sks dari tabel mata_kuliah dimana kdmtk=dd.kdmtk ( kdmtk hasil pemanggilan sebelumnya ), dd hanya sebagai variabel bayangan yang nantinya query harus diakhiri dengan ") as dd", dan diberi nama "sks"

lapisan biru --> terdapat case when "case when nilai_akhir=''A'' then sks*4", mengambil nilai akhir, jika nilai_akhir sama dengan 'A' maka (keluarannya) sks dikalikan 4, jika 'B' dikalikan 3, jika 'C' dikalikan 2, jika 'D' dikalikan 1, jika else lainnya dikalikan 0, dan hasil (keluarannya) tadi dimasukkan ke attribut "nxk".

MEMBUAT FUNCTION get_ipk.

create or replace function get_ipk(varchar)

returns setof record as

declare

nim_mhs alias for $1;

out record;

begin

for out in execute

'select nim,nama,(sum(nxk)/sum(sks))::double precision as ipk from (

select nim,(select nama from mahasiswa where nim=ddd.nim) as nama,

kdmtk,(select nm_mtk from mata_kuliah where kdmtk=ddd.kdmtk) as nm_mtk,

smt,sks,tahun,nilai_akhir,

(case when nilai_akhir=''A'' then smt*4

when nilai_akhir=''B'' then sks*3

when nilai_akhir=''C'' then sks*2

when nilai_akhir=''D'' then sks*1

else sks*0 end)::integer as nxk from(

select nim,kdmtk,smt,(select sks from mata_kuliah where kdmtk=dd.kdmtk) as sks,tahun,nilai_akhir from (

select nim,kdmtk,smt,tahun,min(great) as nilai_akhir from (

select * from nilai_semester union all

select * from nilai_upta union all

select * from nilai_upas union all

select * from nilai_sp

) as d group by nim,kdmtk,smt,tahun having nim='''||nim_mhs||''' order by nim

) as dd

) as ddd

) as dddd group by nim,nama'

loop

return next out;

end loop;

return;

end

$$ language 'plpgsql';

untuk menjelaskan query ini hampir sama dengan query diatas, tetapi saya hanya menambahkan sedikit penjelasan pada kapisan ungu "(sum(nxk)/sum(sks))::double precision as ipk" query ini untuk menghitung IPK ,jumlah "nxk" dibagi jumlah "sks" dan hasilnya nanti dimasukkan di variabel ipk yang ber-tipe data double .

Naaaaah , setelah buat functionnya kita penggil dengan mengetik query seperti ini:

panggil function get_khs yang nimnya= 2009420028

select * from get_khs('2009420028') as (

nim varchar,nama varchar,kdmtk varchar,nm_mtk varchar,smt int,sks int,tahun numeric,nilai_akhir text,nxk int)

panggil function get_ipk yang nimnya= 2009420028

select * from get_ipk('2009420028') as (nim varchar,nama varchar,ipk double precision)

5 comments:

AnonymousApril 2, 2014 at 10:28 AM
Gan, ane pakai postgre di Linux. Ane bingun nulis code dimana ya? Beda sekali dg Yog.
Eroz JamaicanDecember 22, 2015 at 12:20 PM
bro mau tanya nie . . . klo buat nya ke trigger gi mna bro . . mohon pencerahanya . . .??? makasi
UsamaAkJanuary 5, 2020 at 3:20 AM
Positive site, where did u come up with the information on this posting?I have read a few of the articles on your website now, and I really like your style. Thanks a million and please keep up the effective work. PostgreSQL NULL value
Shikamaru NaraJuly 9, 2020 at 2:04 AM
infonya sangat bermanfaat gan
mesin pemisah lcd

NGODING

Monday, January 16, 2012

Membuat Functions di PostgreSQL

5 comments:

Categories

Popular Posts

Blog Archive