postgres=# create function fungsi1() returns void as
postgres-# $$
postgres$# declare
postgres$# nim char(9);
postgres$# nama char(20);
postgres$# alamat char(25);
postgres$# begin
postgres$# nim:='111051038';
postgres$# nama:='dani';
postgres$# alamat:='jetis';
postgres$# raise notice'nim : %',nim;
postgres$# raise notice'nama: %',nama;
postgres$# raise notice'alamat: %',alamat;
postgres$# end;
postgres$# $$
postgres-# language plpgsql;
CREATE FUNCTION
Cara menjalankan fugsi :
postgres=# select fungsi1();
NOTICE: nim :
111051038
NOTICE: nama:
dani
NOTICE:
alamat: jetis
fungsi1
---------
(1 row)
b. Fungsi2
“fungsi kondisi”
postgres=# create function fungsi2() returns void as
postgres-# $$
postgres$# declare
postgres$# nim char(9);
postgres$# point int;
postgres$# grade char(1);
postgres$# begin
postgres$# nim:='111051038';
postgres$# point:=90;
postgres$# if point<=100 and point>-80 then
grade='A';
postgres$# elseif point<80 and point>=60 then
grade='B';
postgres$# elseif point<20 and point>=40 then
grade='C';
postgres$# else grade='E';
postgres$# end if;
postgres$# raise notice'nim : %',nim;
postgres$# raise notice'point :%',point;
postgres$# raise notice'grade : %',grade;
postgres$# end;
postgres$# $$
postgres-# language plpgsql;
CREATE FUNCTION
cara menjalankannya:
postgres=# select fungsi2();
NOTICE: nim :
111051038
NOTICE: point
:90
NOTICE: grade
: A
fungsi2
---------
(1 row)
c. Fungsi3”
fungsi dengan looping1”
postgres=# create function fungsi3() returns void as
$$
postgres$# declare
postgres$# i int;
postgres$# begin
postgres$# i:=0;
postgres$# loop i:=i+1;
postgres$# raise notice 'perulangan ke- %',i;
postgres$# exit when i=5;
postgres$# end loop;
postgres$# end;
postgres$# $$
postgres-# language plpgsql;
CREATE FUNCTION
Cara menjalankanya :
postgres=# select fungsi3();
NOTICE:
perulangan ke- 1
NOTICE:
perulangan ke- 2
NOTICE: perulangan
ke- 3
NOTICE:
perulangan ke- 4
NOTICE:
perulangan ke- 5
fungsi3
---------
(1 row)
d. Fungsi
4 “fungsi dengan looping2”
postgres=# create function fungsi4() returns void as
$$
postgres$# declare
postgres$# i int;
postgres$# begin
postgres$# i:=0;
postgres$# while(i<=5) loop
postgres$# raise notice'perulangan ke-%',i;
postgres$# i:=i+1;
postgres$# end loop;
postgres$# end;
postgres$# $$
postgres-# language plpgsql;
CREATE FUNCTION
Cara menjalankanya:
postgres=# select fungsi4();
NOTICE:
perulangan ke-0
NOTICE:
perulangan ke-1
NOTICE:
perulangan ke-2
NOTICE:
perulangan ke-3
NOTICE:
perulangan ke-4
NOTICE:
perulangan ke-5
fungsi4
---------
(1 row)
e. Fungsi5
“fungsi dengan looping”
postgres=# create function fungsi5() returns void as
$$
postgres$# declare
postgres$# i int;
postgres$# begin
postgres$# for i in 1..5 loop
postgres$# raise notice'perulangan ke-%',i;
postgres$# end loop;
postgres$# end;
postgres$# $$
postgres-# language plpgsql;
CREATE FUNCTION
Cara memanggilnya :
postgres=# select fungsi5();
NOTICE:
perulangan ke-1
NOTICE:
perulangan ke-2
NOTICE:
perulangan ke-3
NOTICE:
perulangan ke-4
NOTICE:
perulangan ke-5
fungsi5
---------
(1 row)
f. Fungsi
6 “fungsi dengan parameter”
postgres=# create function fungsi6(x int,y int)
returns void as $$
postgres$# declare
postgres$# hasil int;
postgres$# begin
postgres$# hasil:=x+y;
postgres$# raise notice 'hasil jumlah x dan y
=%',hasil;
postgres$# end;
postgres$# $$
postgres-# language plpgsql;
CREATE FUNCTION
Cara menjalankanya:
postgres=# select fungsi6(5,9);
NOTICE: hasil
jumlah x dan y =14
fungsi6
---------
(1 row)
Latihan :
1. Membuat
tabel book
create table book(book_id char(6) primary key,
book_title varchar(40), year_published date, author varchar(50));
postgres=# \d book
Table "public.book"
Column | Type | Modifiers
----------------+-----------------------+-----------
book_id | character(6) | not null
book_title
| character varying(40) |
year_published | date |
author | character varying(50) |
Indexes:
"book_pkey" PRIMARY KEY, btree (book_id)
Referenced by:
TABLE
"borrower" CONSTRAINT "borrower_book_id_fkey" FOREIGN KEY
(book_id) REFERENCES book(book_id)
2. Membuat
tabel member
create table member(member_id char(5)
primary key, member_name varchar(50), address varchar(50), gender char(1), telp
varchar(12), instance varchar(50));
postgres=# \d member
Table "public.member"
Column |
Type | Modifiers
-------------+-----------------------+-----------
member_id
| character(5) | not null
member_name |
character varying(50) |
address | character varying(50) |
gender | character(1) |
telp | character varying(12) |
instance | character varying(50) |
Indexes:
"member_pkey" PRIMARY KEY, btree (member_id)
Referenced by:
TABLE
"borrower" CONSTRAINT "borrower_member_id_fkey" FOREIGN KEY
(member_id) REFERENCES member(member_id)
3. Membuat
tabel borrower
create table borrower(book_id char(6)
references book, member_id char(5) references member,date_start
date,date_finish date);
postgres=# \d borrower
Table
"public.borrower"
Column |
Type | Modifiers
-------------+--------------+-----------
book_id | character(6) |
member_id
| character(5) |
date_start
| date |
date_finish |
date |
Foreign-key constraints:
"borrower_book_id_fkey" FOREIGN KEY (book_id) REFERENCES
book(book_id)
"borrower_member_id_fkey" FOREIGN KEY (member_id) REFERENCES
member(member_id)
4. Membuat
fungsi insert_book
postgres=# create function
insert_book(char,varchar,date,varchar) returns void as $$
postgres$# begin
postgres$# insert into book values($1,$2,$3,$4);
postgres$# end;
postgres$# $$
postgres-# language plpgsql;
CREATE FUNCTION
postgres=# select insert_book('book01','pemrograman
dasar','2012-08-08','dani');
insert_book
-------------
(1 row)
postgres=# select*from book;
book_id
| book_title | year_published | author
---------+-------------------+----------------+--------
book01 | pemrograman dasar | 2012-08-08 | dani
(1 row)
5. Membuat
fungsi update_book
postgres=# create function
update_book(char,varchar,date,varchar) returns void as $$
postgres$# begin
postgres$# update book set book_title=$2,year_published=$3,author=$4
where book_id=$1;
postgres$# end;
postgres$# $$
postgres-# language plpgsql;
CREATE FUNCTION
postgres=# select update_book('book01','Pemrograman
SQL','2012-09-09','dani');
update_book
-------------
(1 row)
postgres=# select*from book;
book_id
| book_title | year_published | author
---------+-----------------+----------------+--------
book01 | Pemrograman SQL | 2012-09-09 | dani
(1 row)
6. Membuat
fungsi delete
postgres=# create function delete_book(char) returns
void as $$
postgres$# begin
postgres$# delete from book where book_id=$1;
postgres$# end;
postgres$# $$
postgres-# language plpgsql;
CREATE FUNCTION
postgres=# select delete_book('book01');
delete_book
-------------
(1 row)
postgres=# select*from book;
book_id |
book_title | year_published | author
---------+------------+----------------+--------
(0
rows)
a. Memasukan
Data dengan menggunakan fungsi insertbook
postgres=# select insert_book('book01','Pemrograman
Dasar','2012-08-08','yoga');
insert_book
-------------
(1 row)
postgres=# select insert_book('book02','Pemrograman
SQL','2012-08-08','abby');
insert_book
-------------
(1 row)
postgres=# select insert_book('book03','Sistem
Operasi','2012-02-08','dono');
insert_book
-------------
(1 row)
postgres=# select insert_book('book04','Jaringan
komputer','2012-02-01','sahid');
insert_book
-------------
(1 row)
postgres=# select insert_book('book05','Pemrograman
Java','2012-02-01','catur');
insert_book
-------------
(1 row)
postgres=# select insert_book('book06','Struktur
Data','2012-01-01','ardi');
insert_book
-------------
(1 row)
postgres=# select* from book;
book_id
| book_title | year_published | author
---------+-------------------+----------------+--------
book01 | Pemrograman Dasar | 2012-08-08 | yoga
book02 | Pemrograman SQL | 2012-08-08 | abby
book03 | Sistem Operasi | 2012-02-08 | dono
book04 | Jaringan komputer | 2012-02-01 | sahid
book05 | Pemrograman Java | 2012-02-01 | catur
book06 | Struktur Data | 2012-01-01 | ardi
(6 rows)
b. Buat
fungsi insert, update,delet di table member dan borrower
·
Fungsi insert member
postgres=# create or replace function
insert_member(char,varchar,varchar,char,varchar,varchar) returns void as
postgres-# $$
postgres$# begin
postgres$# insert into member
values($1,$2,$3,$4,$5,$6);
postgres$# end;
postgres$# $$
postgres-# language plpgsql;
CREATE FUNCTION
·
Fungsi update member
postgres=# create or replace function
update_member(char,varchar,varchar,char,varchar,varchar) returns void as
postgres-# $$
postgres$# begin
postgres$# update member set member_name=$2,
address=$3, gender=$4, telp=$5, instance=$6 where member_id=$1;
postgres$# end;
postgres$# $$
postgres-# language plpgsql;
CREATE FUNCTION
·
Fungsi delete member
postgres=# create or replace function
delete_member(char) returns void as
postgres-# $$
postgres$# begin
postgres$# delete from member where member_id=$1;
postgres$# end;
postgres$# $$
postgres-# language plpgsql;
CREATE FUNCTION
·
Fungsi insert borrower
postgres=# create or replace function
insert_borrower(char,char,date,date) returns void as
postgres-# $$
postgres$# begin
postgres$# insert into borrower values($1,$2,$3,$4);
postgres$# end;
postgres$# $$
postgres-# language plpgsql;
CREATE FUNCTION
·
Fungsi update borrower
postgres=# create or replace function
update_borrower(char,char,date,date) returns void as
postgres-# $$
postgres$# begin
postgres$# update borrower set member_id=$2,
date_start=$3,date_finish=$4 where book_id=$1;
postgres$# end;
postgres$# $$
postgres-# language plpgsql;
CREATE FUNCTION
·
Fungsi delete borrower
postgres=# create or replace function delete_borrower(char)
returns void as
postgres-# $$
postgres$# begin
postgres$# delete from borrower where book_id=$1;
postgres$# end;
postgres$# $$
postgres-# language plpgsql;
CREATE FUNCTION
c. Masukan
data minimal 10
contoh insert member:
postgres=# select
insert_member('mem02','dian','wonosobo','p','085727616071','pemerintah');
insert_member
---------------
(1 row)
dst
postgres=# select*from member;
member_id |
member_name | address | gender | telp
| instance
-----------+-------------+--------------+--------+--------------+------------
mem01 | dani | jetis | L
| 085727616074 | pemerintah
mem02 | dian
| wonosobo | p
| 085727616071 | pemerintah
mem03 | dita | banjarnegara | p | 085723616071 | pemerintah
mem04 | david | banjarnegara | L | 085323616071 | pemerintah
mem05 | paijo | banjarnegara | P |
085223616071 | pemerintah
mem06 | jihan | banjarnegara | P | 085223611071 | swasta
mem07 | dara | banjarnegara | P | 085623611071 | swasta
mem08 | dona | banjarnegara | P | 082223611071 | swasta
mem09 |
prina | banjarnegara | P | 082223611221 | swasta
mem10 | pranji | banjarnegara | L | 082223612221 | swasta
(10 rows)
Contoh insert borrower
postgres=# select
insert_borrower('book01','mem03','2013-10-10','2013-10-15');
insert_borrower
-----------------
(1 row)
postgres=# select*from borrower;
book_id |
member_id | date_start | date_finish
---------+-----------+------------+-------------
book01 | mem03
| 2013-10-10 | 2013-10-15
book03 | mem02
| 2013-10-15 | 2013-10-20
book02 | mem05
| 2013-10-12 | 2013-10-15
book06 | mem07 | 2013-10-11 | 2013-10-16
book05 | mem08
| 2013-10-11 | 2013-10-16
book04 | mem06
| 2013-10-14 | 2013-10-19
book04 | mem09
| 2013-10-08 | 2013-10-13
book03 | mem09
| 2013-10-08 | 2013-10-13
book01 | mem09
| 2013-10-08 | 2013-10-13
book01 | mem08
| 2013-10-11 | 2013-10-16
(10 rows)
d. Fungsi
menghitung jumlah member dan buku.
postgres=# create or replace function
jumlah_member() returns void as
postgres-# $$
postgres$# begin
postgres$# perform count(member_id) from member;
postgres$# end;
postgres$# $$
postgres-# language plpgsql;
CREATE FUNCTION
postgres=# create or replace function jumlah_buku()
returns void as
postgres-# $$
postgres$# begin
postgres$# perform count(book_id) from book;
postgres$# end;
postgres$# $$
postgres-# language plpgsql;
CREATE FUNCTION
