Cara Bikin Cursor di Visual Foxpro : CREATE CURSOR Temp2 (No int(10), ItemCode c(20), NamaItem c(80), Satuan c(10), QtyBeli n(10,2),; Harga n(16,2), Persen n(10,2), Jumlah n(16,2), Expired D(8), QtyShp n(10,2),; stnbeli c(10), keterangan c(100), qtyord n(10,2), QtyBb n(10,2), QtyBo n(10,2)) Taraaa.. udah jadi sekarang tinggal kita pakai aja tabel itu layaknya tabel lokal. gunakan saja : SELE Temp2 atau USE Temp2 Kali ini saya akan memberi contoh membuat sebuah Trigger Sederhana menggunakan PostgreSQL Pertama kita akan buat tabel terlebih dahulu, kita akan buat tabel stokbarang, dan pembelian. Membuat tabel stokbarang CREATE TABLE stokbarang( kode_barang character varying(20) NOT NULL, nama_barang character varying(30), stok_ketersediaan integer, CONSTRAINT stokbarang_pkey PRIMARY KEY (kode_barang) )WITH ( OIDS=FALSE ); ALTER TABLE stokbarang OWNER TO postgres; Membuat table pembelian CREATE TABLE pembelian( kode_barang character varying(20), jumlah_beli integer, CONSTRAINT pembelian_kode_barang_fkey FOREIGN KEY (kode_barang) REFERENCES stokbarang (kode_barang) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION )WITH (
This document is posted to help you gain knowledge. Please leave a comment to let me know what you think about it! Share it to your friends and learn new things together.
Taraaa.. udah jadisekarang tinggal kita pakai aja tabel itu layaknya tabel lokal.gunakan saja :SELE Temp2atauUSE Temp2
Kali ini saya akan memberi contoh membuat sebuah Trigger Sederhana menggunakan PostgreSQLPertama kita akan buat tabel terlebih dahulu, kita akan buat tabel stokbarang, dan pembelian.
Membuat tabel stokbarang
CREATE TABLE stokbarang(kode_barang character varying(20) NOT NULL,nama_barang character varying(30),stok_ketersediaan integer,CONSTRAINT stokbarang_pkey PRIMARY KEY (kode_barang))WITH (OIDS=FALSE);ALTER TABLE stokbarang OWNER TO postgres;
Membuat table pembelian
CREATE TABLE pembelian(kode_barang character varying(20),jumlah_beli integer,CONSTRAINT pembelian_kode_barang_fkey FOREIGN KEY (kode_barang)REFERENCES stokbarang (kode_barang) MATCH SIMPLEON UPDATE NO ACTION ON DELETE NO ACTION)WITH (OIDS=FALSE);ALTER TABLE pembelian OWNER TO postgres;
pada 2 tabel diatas sudah di relasikan, kemudian kita buat functionnya.
CREATE OR REPLACE FUNCTION update_stok()RETURNS trigger AS$BODY$beginupdate stokbarang set stok_ketersediaan=stok_ketersediaan-new.jumlah_beli where kode_barang=new.kode_barang;return new;end;$BODY$LANGUAGE 'plpgsql' VOLATILECOST 100;ALTER FUNCTION update_stok() OWNER TO postgres;
akan saya jelaskan sedikit, isi function diatas .update stokbarang set stok_ketersediaan=stok_ketersediaan-new.jumlah_beli where kode_barang=new.kode_barang;return new;
mengupdate tabel stokbarang pada kolom stok_ketersediaan=( stok_ketersediaan dikurangi jumlah_beli ) nilai jumlah beli diambil saat mengimputkan jumlah _beli pada table pembelian, karena trigger ini dipanggil setelah mengisi data pada tabel pembelian.
Kemudian kita masukkan data stokbarang.
insert into stokbarang values('10023','sikat gigi',50)insert into stokbarang values('10024','sabun mandi',50)
Membuat trigger dengan nama “update_stok”
CREATE TRIGGER update_stokAFTER INSERT ON pembelianFOR EACH ROWEXECUTE PROCEDURE update_stok();
maksud dari query diatas, membuat trigger namanya update_stok dan akan dijalankan setelah proses pemasukan/insert di tabel pembelian, isi dari trigger ini adalah memanggil function update_stok(); yang sudah kita buat tadi.
Untuk lebih jelasnya lihat contoh proses berikut ini.
Terlihat stok barang yang telah kita masukkan tadi, dengan kode_barang, nama_barang, dan stok_barang. kedua barang mempunyai stok yang masih utuh ( 50 ).
Selanjutnya masukkan data ke tabel pembelian, kita misalkan ada pembelian membeli barang yang ( kode_barangnya 10023 dan jumlah barang yang dibeli 5 ) , setelah proses insert berhasil maka trigger akan otomatis berjalan ( memanggil function yang telah dibuat tadi ) , cara kerja function ( mengurangi stok ) dapat dilihat pada penjelasan diatas.
Table "public.log" Column | Type | Modifiers---------+-----------------------+----------- process | character varying(10) | date | date
a. Trigger insert data di table productPertama buka text editor geditKemudian ketikan sintak sql di dalamnya, sintaknya adalah sebagai berikut :
create or replace function ins_product() returns trigger as $$begin insert into log values('insert',now());return new;end $$language plpgsql;create trigger ins_data after insert on product for each row execute procedure ins_product();
Kemdian simpan dengan nama ins_product.sqlKemudian jalankan fungsi triggernya dengan langkah masuk ke dalam postgresql dan ketikan perintah :
c. Trigger delete data di table productLangkah seperti sebelumnyaKemudian ketik :
create or replace function del_product() returns trigger as $$begininsert into log values('delete',now());return new;end $$language plpgsql;create trigger del_data after delete on product for each row execute procedure del_product();
Kemudian simpan dengan nama del_product.sqlKemudian jalankan fungsi triggernya dengan langkah masuk ke dalam postgresql dan ketikan perintah :
rb1=> select product_id,concat(stock)from price_product where
product_id='pr1';
product_id | concat
------------+--------
pr1 | 40
pr1 | 60
pr1 | 60
(3 rows)
h. group concat array
rb1=> select product_id,array_to_string (array(select price from
price_product where product_id='pr1'order by date),',')as groupconcat
from price_product where product_id='pr1'order by date),',')as
groupconcat from price_product where product_id='pr1' group by
product_id;
product_id | groupconcat
------------+----------------------
pr1 | 450000,460000,475000
(1 row)
C. fungsi datea. current date
rb1=> select
current_date;
date
------------
2013-10-09
(1 row)
b. current Time
rb1=> select current_time;
timetz
--------------------
15:56:45.025454+07
(1 row)
c. current times tamp
rb1=> select current_timestamp;
now
-------------------------------
2013-10-09 15:58:04.497006+07
(1 row)
d. now
rb1=> select now();
now
-------------------------------
2013-10-09 15:58:14.453479+07
(1 row)
Latihan .
1. tampilkan semua data product(product_id,product_name,price)
rb1=> select price_product.product_id,product.product_name,price_product.price from product left join price_product on product.product_id=price_product.product_id; product_id | product_name | price ------------+-------------------------+-------- pr1 | monitor acer 14 inch | 450000 pr1 | monitor acer 14 inch | 460000 pr1 | monitor acer 14 inch | 475000 pr2 | monitor acer 20 inch | 655000 pr2 | monitor acer 20 inch | 555000 pr2 | monitor acer 20 inch | 575000 pr3 | monitor samsung 21 inch | 335000 pr3 | monitor samsung 21 inch | 350000 pr3 | monitor samsung 21 inch | 360000 pr4 | modem sierra 101 | 450000 pr4 | modem sierra 101 | 420000 pr4 | modem sierra 101 | 120000 pr5 | modem sierra 102 | 120000 pr5 | modem sierra 102 | 150000 pr5 | modem sierra 102 | 170000 pr7 | modem huawei AC123 | 170000 pr7 | modem huawei AC123 | 180000 pr8 | printer canon mp999 | 180000 pr8 | printer canon mp999 | 190000 pr9 | printer canon mp278 | 110000 pr9 | printer canon mp278 | 120000 | modem sierra 103 | (22 rows)
2. tampilkan harga product dengan merek modem sierrarb1=> select price_product.product_id,product.product_name,price_product.price from product left join price_product on product.product_id=price_product.product_idwhere product_name='modem sierra 101'; product_id | product_name | price ------------+------------------+-------- pr4 | modem sierra 101 | 450000 pr4 | modem sierra 101 | 420000 pr4 | modem sierra 101 | 120000(3 rows)
rb1=> select price_product.product_id,product.product_name,price_product.price from product left join price_product on product.product_id=price_product.product_idwhere product_name='modem sierra 102; product_id | product_name | price ------------+------------------+-------- pr5 | modem sierra 102 | 120000 pr5 | modem sierra 102 | 150000 pr5 | modem sierra 102 | 170000(3 rows)
3.tampilkan nama product ,stok, harga dan tanggal >150000rb1=> select product.product_name,price_product.stock,price_product.price,price_product.date from product left join price_product on product.product_id=price_product.price_product.product_id where price>150000; product_name | stock | price | date -------------------------+-------+--------+------------ monitor acer 14 inch | 40 | 450000 | 2012-04-04 monitor acer 14 inch | 60 | 460000 | 2012-05-14 monitor acer 14 inch | 60 | 475000 | 2012-05-20
4. tampilkan semua nama barang yang mempunyai harga <400000rb1=> select product.product_name,price_product.price from product left join price_product on product.product_id=price_product.product_id where price<400000; product_name | price -------------------------+-------- monitor samsung 21 inch | 335000 monitor samsung 21 inch | 350000 monitor samsung 21 inch | 360000 modem sierra 101 | 120000 modem sierra 102 | 120000 modem sierra 102 | 150000 modem sierra 102 | 170000 modem huawei AC123 | 170000 modem huawei AC123 | 180000 printer canon mp999 | 180000 printer canon mp999 | 190000 printer canon mp278 | 110000 printer canon mp278 | 120000
(13 rows)
Latihan..1.rb1=> select product_id,date,stock from price_product where product_id='pr1'; product_id | date | stock------------+------------+------- pr1 | 2012-04-04 | 40 pr1 | 2012-05-14 | 60 pr1 | 2012-05-20 | 60(3 rows)
2.rb1=> select product.product_name,price_product.date,price_product.stock from product left join price_product on product.product_id=price_product.product_id whereproduct_name='monitor acer 14 inch'; product_name | date | stock----------------------+------------+------- monitor acer 14 inch | 2012-04-04 | 40 monitor acer 14 inch | 2012-05-14 | 60 monitor acer 14 inch | 2012-05-20 | 60(3 rows)
3.rb1=> select product_name,array_to_string (array(select price from price_product where product_id='pr1'order by date),',')as stok from product where product_name='monitor acer 14 inch' group by product_id; product_name | stok ----------------------+---------------------- monitor acer 14 inch | 450000,460000,475000(1 row)
4.rb1=> select product_name,array_to_string(array(select date from price_product where product_id='pr8' order by date),',')as tanggal,array_to_string (array(select price from price_product where product_id='pr8'order by date),',')as stok from product where product_name='printer canon mp999' group by product_id; product_name | tanggal | stok ---------------------+-----------------------+--------------- printer canon mp999 | 2012-07-17,2012-08-17 | 180000,190000
(1 row)
postgres=# \c rb1;You are now connected to database "rb1" as user "postgres".rb1=# create schema data;CREATE SCHEMArb1=# alter schema data owner to dani;ALTER SCHEMArb1=# \qadministrator@administrator-desktop:~$ psql rb1 danipsql.bin (9.2.4)Type "help" for help.
rb1=> show search_path ; search_path
---------------- "$user",public(1 row)rb1=> set search_path to data;SETrb1=> show search_path ; search_path ------------- data(1 row)rb1=> create table data.product (product_id char(3),product_name varchar(50));CREATE TABLErb1=> \d public.product; Table "public.product" Column | Type | Modifiers
--------------+-----------------------+----------- product_id | character(3) | not null product_name | character varying(30) | not nullIndexes: "product_pkey" PRIMARY KEY, btree (product_id)Referenced by: TABLE "public.price_product" CONSTRAINT "price_product_product_id_fkey" FOREIGN KEY (product_id) REFERENCES public.product(product_id)Triggers: del_data AFTER DELETE ON public.product FOR EACH ROW EXECUTE PROCEDURE public.del_product() ins_data AFTER INSERT ON public.product FOR EACH ROW EXECUTE PROCEDURE public.ins_product() up_data AFTER UPDATE ON public.product FOR EACH ROW EXECUTE PROCEDURE public.up_product()
latihanadministrator@administrator-desktop:~$ psqlpsql.bin (9.2.4)Type "help" for help.
postgres=# \c rb1You are now connected to database "rb1" as user "postgres".rb1=# create schema mydata;CREATE SCHEMArb1=# alter schema mydata owner to dani;ALTER SCHEMArb1=# show search_path ; search_path ----------------
"$user",public(1 row)
rb1=# set search_path to mydata;SETrb1=# show search_path ; search_path ------------- mydata(1 row)
rb1=# \d public.price_product; Table "public.price_product" Column | Type | Modifiers ------------+--------------+----------- product_id | character(3) | price | integer | stock | integer | date | date | Foreign-key constraints: "price_product_product_id_fkey" FOREIGN KEY (product_id) REFERENCES public.product(product_id)Triggers: delete_data AFTER INSERT ON public.price_product FOR EACH ROW EXECUTE PROCEDURE public.delete_price() insert_data AFTER INSERT ON public.price_product FOR EACH ROW EXECUTE PROCEDURE public.insert_price() update_data AFTER INSERT ON public.price_product FOR EACH ROW EXECUTE PROCEDURE public.update_price()
rb1=# \d mydata.price_product ; Table "mydata.price_product" Column | Type | Modifiers ------------+--------------+----------- product_id | character(3) | price | integer | stock | integer | date | date |
\d: extra argument ";" ignored
rb1=# insert into mydata.price_product values ('001',100000,50,'2012-09-21');INSERT 0 1
rb1=# insert into mydata.price_product values ('002',200000,90,'2012-09-24');INSERT 0 1rb1=# insert into mydata.price_product values ('003',400000,40,'2012-12-24');INSERT 0 1rb1=# insert into mydata.price_product values ('004',300000,70,'2012-02-14');INSERT 0 1rb1=# insert into mydata.price_product values ('005',900000,10,'2012-05-24');INSERT 0 1
1. Membuat database dengan Query analyzerBukalah query analyzer pada SQL Server 2000 pada menu Tools pilih SQL Query Analyzer. Kemudian ketikan perintah berikut:CREATE DATABASE DBTOKO
2. Membuat Tabel BARANGPerintah untuk membuat table barang yaitu:CREATE TABLE BARANG(KODE_BRG NVARCHAR (15),NAMA_BRG NVARCHAR (50), JMLH_BRG NUMERIC (9),SATUAN NVARCHAR (20) PRIMARY KEY (KODE_BRG))
3. Memasukkan Data dengan store procedure dan menjalankannya di sql query analyzerUntuk membuat store procedure memasukkan data yaitu klik kanan pada Store Procedure kemudian klik New Store Procedure. Ketikkan perintahCREATE PROCEDURE ENTRY @KODE NVARCHAR (15), @NAMA NVARCHAR (50),@JMLH NUMERIC (9), @SAT NVARCHAR (20) ASINSERT INTO BARANG (KODE_BRG, NAMA_BRG, JMLH_BRG, SATUAN) VALUES (@KODE, @NAMA, @JMLH, @SAT)GOUntuk menjalankan store procedure masukkan perintah berikut ke new SQL Analyzer
EXEC ENTRY 'AN001','CITRA LOTION','80','BOTOL'.Dimana ENTRY adalah nama Store Procedure.
4. Menampilkan DataBuat store procedure TAMPIL dengan perintah seperti di bawah ini:CREATE PROCEDURE TAMPIL @KODE NVARCHAR (20) ASSELECT*FROM BARANG WHERE KODE_BRG=@KODEGOJalankan perintah tersebut di SQL Query Analyzer dengan mengetikkan perintah:EXEC TAMPIL 'AN001'Dimana TAMPIL adalah nama Store Procedure
MENERAPKAN STORE PROCEDURE DI MICROSOFT VISUAL BASIC 6.01. Sebelum kita membuat form, Klik Project -> Reference -> Microsoft ActiveX Data
Objects 2.0 library. Buatlah tampilan form seperti dibawah ini:
Keterangan:- TextBox
Text1 untuk kode barangText2 untuk nama barangText3 untuk jumlah barangText4 untuk satuan
Command2 untuk menyimpan dataCommand3 untuk keluarCommand4 untuk ulang
2. Setelah membuat form seperti di atas, kita masukkan koding yang ada di bawah ini:‘Untuk mengkosongkan textboxPrivate Sub Command4_Click()Text1.Text = ""Text2.Text = ""Text3.Text = ""Text4.Text = ""
End Sub
Private Sub Form_Load()KONEKSI.Open "Provider=MSDASQL.1;Persist Security Info=False;Data Source=ODBARANG;Initial Catalog=DBTOKO"
End Sub
‘Untuk menampilkan dataPrivate Sub Command1_Click() Dim PARAMETER1 As New ADODB.PARAMETER Dim COMAND1 As New ADODB.Command Dim RS1 As New ADODB.Recordset With COMAND1 .ActiveConnection = KONEKSI .CommandType = adCmdStoredProc .CommandText = "TAMPIL" Set PARAMETER1 = .CreateParameter("KODE_BRG", adVarChar, adParamInput, 20, Text1.Text).Parameters.Append PARAMETER1Set RS1 = .Execute End With
‘Untuk menginputkan/menyimpan dataPrivate Sub Command2_Click() Dim PARAMETER As New ADODB.PARAMETER Dim COMAND As New ADODB.Command Dim RS As New ADODB.Recordset With COMAND .ActiveConnection = KONEKSI .CommandType = adCmdStoredProc .CommandText = "ENTRY" Set PARAMETER = .CreateParameter("KODE_BRG", adVarChar, adParamInput, 20, Text1.Text).Parameters.Append PARAMETERKONEKSI.Execute "ENTRY '" & Text1.Text & "','" & Text2.Text & "','" & Text3.Text & "','" & Text4.Text & "'"X = MsgBox("DATA TELAH TERSIMPAN", vbInformation, "INFORMASI") End With
End Sub
Private Sub Command3_Click()EndEnd Sub
3. Setelah selesai, kita coba untuk menjalankannya dengan memasukkan / menyimpan data, misalkan seperti gambar di bawah ini:
4. Kita dapat melihat apakah data tersebut sudah tersimpan atau belum dengan membuka database DBTOKO dengan table BARANG diSQL Server.
5. Menjalankan untuk menampilkan data, misalkan data yang dicari AN100.Setelah diketikkan di Kode Barang, kemudian klik tombol “MENAMPILKAN DATA”. Maka akan terlihat seperti di bawah ini:
Fungsi yang terdapat pada POSTGRESQLini adalah beberapa sintak yang terdapat pada database manajemen sistem POSTGRESQL yang saya
dapat pada praktikum ketika saya menempuh mata kuliah pemrograman SQLA. Rangkuman Materia. Join
rb1=> select product.product_name,price_product.price from product left join price_product on product.product_id=price_product.product_id; product_name | price
b. rightrb1=> select product_name ,right(product_name,4) as hasilpotongkanan from product; product_name | hasilpotongkanan-------------------------+------------------ monitor acer 14 inch | inch monitor acer 20 inch | inch modem sierra 101 | 101 modem sierra 102 | 102 modem sierra 103 | 103 modem huawei AC123 | C123 printer canon mp278 | p278 monitor samsung 21 inch | inch printer canon mp999 | p999(9 rows)
c. lowerrb1=> select product_name ,lower(product_name) as hasillower from product; product_name | hasillower -------------------------+------------------------- monitor acer 14 inch | monitor acer 14 inch monitor acer 20 inch | monitor acer 20 inch modem sierra 101 | modem sierra 101 modem sierra 102 | modem sierra 102 modem sierra 103 | modem sierra 103 modem huawei AC123 | modem huawei ac123 printer canon mp278 | printer canon mp278 monitor samsung 21 inch | monitor samsung 21 inch printer canon mp999 | printer canon mp999(9 rows)
d. upperrb1=> select product_name ,upper(product_name) as hasilupper from product; product_name | hasilupper -------------------------+------------------------- monitor acer 14 inch | MONITOR ACER 14 INCH monitor acer 20 inch | MONITOR ACER 20 INCH modem sierra 101 | MODEM SIERRA 101 modem sierra 102 | MODEM SIERRA 102 modem sierra 103 | MODEM SIERRA 103 modem huawei AC123 | MODEM HUAWEI AC123 printer canon mp278 | PRINTER CANON MP278 monitor samsung 21 inch | MONITOR SAMSUNG 21 INCH printer canon mp999 | PRINTER CANON MP999(9 rows)
e. substringrb1=> select product_name ,substring(product_name,10) as hasilsubstring from product; product_name | hasilsubstring-------------------------+---------------- monitor acer 14 inch | cer 14 inch monitor acer 20 inch | cer 20 inch modem sierra 101 | rra 101 modem sierra 102 | rra 102 modem sierra 103 | rra 103 modem huawei AC123 | wei AC123 printer canon mp278 | anon mp278 monitor samsung 21 inch | amsung 21 inch printer canon mp999 | anon mp999(9 rows)
rb1=> select product_name ,substring(product_name,2,5) as hasilsubstring from product; product_name | hasilsubstring-------------------------+----------------
monitor acer 14 inch | onito monitor acer 20 inch | onito modem sierra 101 | odem modem sierra 102 | odem modem sierra 103 | odem modem huawei AC123 | odem printer canon mp278 | rinte monitor samsung 21 inch | onito printer canon mp999 | rinte(9 rows)
f. asciirb1=> select product_name ,substring(product_name,0,2) as hasilsubstring,ascii(substring(product_name,0,2)) as hasilascii from product; product_name | hasilsubstring | hasilascii-------------------------+----------------+------------ monitor acer 14 inch | m | 109 monitor acer 20 inch | m | 109 modem sierra 101 | m | 109 modem sierra 102 | m | 109 modem sierra 103 | m | 109 modem huawei AC123 | m | 109 printer canon mp278 | p | 112 monitor samsung 21 inch | m | 109 printer canon mp999 | p | 112(9 rows)
g. concatrb1=> select product_id,concat(stock)from price_product where product_id='pr1'; product_id | concat------------+-------- pr1 | 40
pr1 | 60 pr1 | 60(3 rows)
h. group concat arrayrb1=> select product_id,array_to_string (array(select price from price_product where product_id='pr1'order by date),',')as groupconcat from price_product where product_id='pr1'order by date),',')as groupconcat from price_product where product_id='pr1' group by product_id; product_id | groupconcat ------------+---------------------- pr1 | 450000,460000,475000(1 row)
C. fungsi datea. current date
rb1=> select current_date; date ------------ 2013-10-09(1 row)
b. current Timerb1=> select current_time; timetz -------------------- 15:56:45.025454+07(1 row)
c. current times tamprb1=> select current_timestamp; now ------------------------------- 2013-10-09 15:58:04.497006+07(1 row)
d. nowrb1=> select now(); now ------------------------------- 2013-10-09 15:58:14.453479+07(1 row)
Latihan .
1. tampilkan semua data product(product_id,product_name,price)
rb1=> select price_product.product_id,product.product_name,price_product.price from product left join price_product on product.product_id=price_product.product_id; product_id | product_name | price ------------+-------------------------+-------- pr1 | monitor acer 14 inch | 450000
2. tampilkan harga product dengan merek modem sierrarb1=> select price_product.product_id,product.product_name,price_product.price from product left join price_product on product.product_id=price_product.product_idwhere product_name='modem sierra 101'; product_id | product_name | price ------------+------------------+-------- pr4 | modem sierra 101 | 450000 pr4 | modem sierra 101 | 420000 pr4 | modem sierra 101 | 120000(3 rows)
rb1=> select price_product.product_id,product.product_name,price_product.price from product left join price_product on product.product_id=price_product.product_idwhere product_name='modem sierra 102;
product_id | product_name | price ------------+------------------+-------- pr5 | modem sierra 102 | 120000 pr5 | modem sierra 102 | 150000 pr5 | modem sierra 102 | 170000(3 rows)
3.tampilkan nama product ,stok, harga dan tanggal >150000rb1=> select product.product_name,price_product.stock,price_product.price,price_product.date from product left join price_product on product.product_id=price_product.price_product.product_id where price>150000; product_name | stock | price | date -------------------------+-------+--------+------------ monitor acer 14 inch | 40 | 450000 | 2012-04-04 monitor acer 14 inch | 60 | 460000 | 2012-05-14 monitor acer 14 inch | 60 | 475000 | 2012-05-20 monitor acer 20 inch | 20 | 655000 | 2012-04-05 monitor acer 20 inch | 80 | 555000 | 2012-05-05 monitor acer 20 inch | 30 | 575000 | 2012-05-24 monitor samsung 21 inch | 110 | 335000 | 2012-04-24 monitor samsung 21 inch | 110 | 350000 | 2012-05-03 monitor samsung 21 inch | 110 | 360000 | 2012-06-04 modem sierra 101 | 10 | 450000 | 2012-04-01 modem sierra 101 | 40 | 420000 | 2012-06-01 modem sierra 102 | 10 | 170000 | 2012-07-14 modem huawei AC123 | 10 | 170000 | 2012-07-14 modem huawei AC123 | 5 | 180000 | 2012-08-24 printer canon mp999 | 15 | 180000 | 2012-07-17 printer canon mp999 | 55 | 190000 | 2012-08-17(16 rows)
4. tampilkan semua nama barang yang mempunyai harga <400000rb1=> select product.product_name,price_product.price from product left join price_product on product.product_id=price_product.product_id where price<400000; product_name | price
-------------------------+-------- monitor samsung 21 inch | 335000 monitor samsung 21 inch | 350000 monitor samsung 21 inch | 360000 modem sierra 101 | 120000 modem sierra 102 | 120000 modem sierra 102 | 150000 modem sierra 102 | 170000 modem huawei AC123 | 170000 modem huawei AC123 | 180000 printer canon mp999 | 180000 printer canon mp999 | 190000 printer canon mp278 | 110000 printer canon mp278 | 120000(13 rows)
Latihan..1.rb1=> select product_id,date,stock from price_product where product_id='pr1'; product_id | date | stock------------+------------+------- pr1 | 2012-04-04 | 40 pr1 | 2012-05-14 | 60 pr1 | 2012-05-20 | 60(3 rows)
2.rb1=> select product.product_name,price_product.date,price_product.stock from product left join price_product on product.product_id=price_product.product_id whereproduct_name='monitor acer 14 inch'; product_name | date | stock----------------------+------------+------- monitor acer 14 inch | 2012-04-04 | 40 monitor acer 14 inch | 2012-05-14 | 60 monitor acer 14 inch | 2012-05-20 | 60(3 rows)
3.rb1=> select product_name,array_to_string (array(select price from price_product where product_id='pr1'order by date),',')as stok from product where product_name='monitor acer 14 inch' group by product_id; product_name | stok ----------------------+---------------------- monitor acer 14 inch | 450000,460000,475000(1 row)
4.rb1=> select product_name,array_to_string(array(select date from price_product where product_id='pr8' order by date),',')as tanggal,array_to_string (array(select price from price_product where product_id='pr8'order by date),',')as stok from product where product_name='printer canon mp999' group by product_id; product_name | tanggal | stok ---------------------+-----------------------+--------------- printer canon mp999 | 2012-07-17,2012-08-17 | 180000,190000(1 row)
procedure dan trigger mysql
PROCEDURE DAN TRIGGER MYSQL
BERKENALAN DENGAN STORE PROCEDURE DAN TRIGGER
Stored Procedures which allow us to automate or program our way out of many tasks on directly on the server rather than having to write external scripts to do complex manipulation of data – MYSQL References
Prosedur merupakan program yang ditulis dan dieksekusi langsung dari Engine database. Beberapa situasi dimana stored procedure sangat bermanfaat :
1. Pada system yang didalamnya terdapat bermacam-macam platform bahasa pemrograman, akan tetapi proses yang terjadi pada database harus tetap memiliki standard yang sama.
2. Sistem dikerjakan pada lingkungan yang berbeda-beda dengan prioritas keamanan yang berbeda sehingga diperlukan pembatasan terhadap hak-hak penggunaan terhadap tabel-tabel tertentu.
3. Dengan adanya multi koneksi , maka dengan store procedure akan memberikan performa yang lebih baik karena proses interkoneksi dari luar server menjadi lebih sedikit.
4. Programmer gak perlu pusing melihat bertumpuk-tumpuk tabel karena hanya memakai prosedur yang sudah ada.
Sedangkan trigger merupakan store procedure yang digunakan untuk memicu perubahan pada suatu tabel sesuai dengan event yang terjadi (insert, delete, update). Trigger dapat digunakan untuk memicu proses terhadap tabel lain maupun tabel itu sendiri.
Manfaat dari trigger :
1. Pada pemanfaatan program aplikasi multi platform, proses bisnis dari sistem dapat dibuat langsung dilakukan didatabase sehingga perbedaan interpretasi di program aplikasi menjadi lebih diminimalisisr
2. Proses lebih cepat
3. Programmer tinggal ongkang-ongkang kaki karena yang pusing DBAnya hehehehe..
Contoh tabel dan sampel data untuk coba-coba trigger dan procedure
CREATE TABLE jurusan (id CHAR(3),nama VARCHAR(50) NOT NULL,Constraint pk_jur primary key(id));
Contoh Trigger yang digunakan untuk memicu perubahan pada tabel kuliah, pada tabel kuliah proses insert diasumsikan hanya dilakukan pada kolom nilai_angka saja sedangkan nilai huruf dan bobot akan terisi secara otomatis (dg mekanisme trigger) dilakukan jika nilai_angka terisi dengan kriteria tertentu.
Trigger disini dilakukan ketika terjadi 2 event yaitu insert (Before Insert) pada nilai_angka atau update (before update) pada nilai_angka.
Kalo di oracle bisa dibuat satu saja tapi kok di mysql aku coba gak bisa , terpaksa bikin 2 trigger.
DELIMITER &&CREATE TRIGGER updatebobot before update on kuliahfor each rowbeginif new.nilai_angka < 20 thenset new.bobot = 0;set new.nilai_huruf=’E’;else if new.nilai_angka >= 20 and new.nilai_angka <=40 thenset new.bobot = 1;set new.nilai_huruf=’D’;else if new.nilai_angka >= 41 and new.nilai_angka <=60 thenset new.bobot = 2;
set new.nilai_huruf=’C’;else if new.nilai_angka >= 61 and new.nilai_angka <=80 thenset new.bobot = 3;set new.nilai_huruf=’B’;else if new.nilai_angka >= 81 and new.nilai_angka <=100 thenset new.bobot = 4;set new.nilai_huruf=’A’;end if;end if;end if;end if;end if;end &&DELIMITER ;
DELIMITER &&CREATE TRIGGER insertbobot before insert on kuliahfor each rowbeginif new.nilai_angka < 20 thenset new.bobot = 0;set new.nilai_huruf=’E’;else if new.nilai_angka >= 20 and new.nilai_angka <=40 thenset new.bobot = 1;set new.nilai_huruf=’D’;else if new.nilai_angka >= 41 and new.nilai_angka <=60 thenset new.bobot = 2;set new.nilai_huruf=’C’;else if new.nilai_angka >= 61 and new.nilai_angka <=80 thenset new.bobot = 3;set new.nilai_huruf=’B’;else if new.nilai_angka >= 81 and new.nilai_angka <=100 thenset new.bobot = 4;set new.nilai_huruf=’A’;end if;end if;end if;end if;end if;end &&DELIMITER ;
Untuk mencoba trigger :
UPDATE KULIAH set nilai_angka =60 where nim =’0110001′ and mka =’IK02001′ and th_akademik=’20102′;INSERT INTO kuliah (nilai_angka, Th_akademik, nim, mka) VALUES (66, ’20102′, ’010004′, ‘TI01004′);
B. PROCEDURE
Contoh procedure untuk melakukan cetak KRS, cetak KHS dan melakukan Proses KRS (input KRS) dan juga Input Nilai.
a) Procedure cetak KRS
DELIMITER &&CREATE PROCEDURE CetakKRS(nim char(7), ta char(7))BEGINSELECT distinct concat(m.nim ,”, m.nama ,’ | TA : ‘, k.th_akademik) as “Data Mahasiswa” from mahasiswa minner join kuliah k on m.nim = k.nimwhere k.nim =nim and k.Th_akademik =ta;SELECT concat(k.mka ,’ | ‘ , mk.nama, ‘ ‘) as “Mata Kuliah”, d.nama as “Dosen”, mk.sks as “SKS” from kuliah kinner join matakuliah mk on k.mka = mk.kode_mkainner join mahasiswa mhs on k.nim = mhs.niminner join dosen d on d.nip = mk.dosenwhere k.nim = nim and k.Th_akademik = ta;SELECT Concat(‘Jumlah SKS = ‘, sum(mk.sks)) as ” Keterangan” from kuliah kinner join mahasiswa m on k.nim = m.niminner join matakuliah mk on k.mka = mk.kode_mkawhere k.nim = nim and k.Th_akademik = tagroup by m.nim ;END &&DELIMITER ;
b) Procedure cetak KHS
DELIMITER &&CREATE PROCEDURE CetakKHS(nim char(7), ta char(7))BEGINSELECT distinct concat(m.nim ,’ ‘, m.nama ,’ | TA : ‘, k.th_akademik) as “Data Mahasiswa” from mahasiswa minner join kuliah k on m.nim = k.nimwhere k.nim =nim and k.Th_akademik =ta;
SELECT k.mka, mk.nama, mk.sks, k.nilai_huruf as Nilai, k.bobot, (k.bobot * mk.sks) as “bobot (bobot x sks)” from kuliah kinner join matakuliah mk on k.mka = mk.kode_mkainner join mahasiswa mhs on k.nim = mhs.nimwhere k.nim = nim and k.Th_akademik = ta;SELECT Concat(‘Jumlah SKS = ‘, sum(mk.sks)) as ” Keterangan “, (sum(k.bobot * mk.sks)) as “Total Bobot”, (sum(k.bobot * mk.sks)/ sum(mk.sks)) as “IPSementara” from kuliah kinner join mahasiswa m on k.nim = m.niminner join matakuliah mk on k.mka = mk.kode_mkawhere k.nim = nim and k.Th_akademik = ta
group by m.nim ;END &&DELIMITER ;
c) Proses insert dan update tabel kuliah pada trigger diatas dapat juga dijadikan procedure
DELIMITER &&CREATE PROCEDURE InputKRS(nr char(7), mk char(10), ta char(5))BEGINInsert into kuliah(nim , mka, th_akademik)values (nr, mk, ta);END &&DELIMITER ;
DELIMITER &&CREATE PROCEDURE InputNilai(nr char(7), mk char(10), ta char(5), nl tinyint)BEGINUpdate kuliah set nilai_angka = nlwhere kuliah.nim =nr and kuliah.mka = mk and kuliah.th_akademik = ta;END &&DELIMITER ;