Stored Procedure & Cursor

Stored Procedure & CursorMySQL

Stored ProcedureStored Procedure memiliki nama, parameter, dan dapat diisi banyak perintah SQLmemilik, variabel lokal, error handling, loop, dan kondisi IFCREATE PROCEDURE procedure1() BEGIN /* procedure body */END;Pemanggilan stored procedure dilakukan dengan perintah CALLCALL procedure1();

Delimiteradalah karakter atau string ang digunakan untuk memberitahu MySQL bahwa pengetikan suatu statemen SQL telah selesai. ;Untuk membedakan akhir dari stored procedure dan statemen SQL, perlu dilakukan perubahan delimiter.Misalnya dengan menggunakan //.

DELIMITER //CREATE PROCEDURE procedure1() BEGIN SQLStatement1; SQLStatement2;END//DELIMITER ;

Create ProcedureCREATE PROCEDURE p1 () SELECT * FROM t;stored procedure dengan nama p1 berisi statemen SELECT * FROM tBEGIN dan END tidak diperlukan di sini karena statemen tunggal.

Stored ProcedureNama stored procedure tidak case sensitif, jadi p1 dan P1 adalah sama.Tidak boleh menggunakan dua stored procedure dengan nama yang sama dalam MySQL.Boleh menggunakan bentuk nama_database.nama_stored_procedure, misalkan db4.sp1.Boleh menggunakan spasi, dengan catatan harus berada diantara tanda `. Panjang tidak lebih dari 64 karakter.CREATE PROCEDURE `long p1`() SELECT * FROM t;CALL `long p1`();

Procedure BodySemua DML dan DDL statemen pada level tabel boleh digunakan dalam procedure body. Yang tidak diperbolehkan antara lain: CREATE PROCEDURE, ALTER PROCEDURE, DROP PROCEDURE, CREATE FUNCTION, DROP FUNCTION, CREATE TRIGGER, DROP TRIGGER.

VariablesVariabel diletakkan di dalam procedure body (diantara BEGIN dan END).Deklarasi variable diawali dengan DECLARE.Variabel dapat diberikan nilai DEFAULT.Dalam tiap blok BEGIN-END, dapat digunakan variabel yang sama.Variable a (system variables) berbeda dengan variable @a (user defined variable)

VariablesVariable a hanya dapat digunakan di dalam STORED PROCEDUREBersifat localHarus menggunakan DECLAREVariable @a dapat digunakan dimana saja Bersifat global

Variables

VariablesDELIMITER //CREATE PROCEDURE prc_test ()BEGIN DECLARE var2 INT DEFAULT 1; SET var2 = var2 + 1;SELECT var2;END//DELIMITER ;

VariablesDELIMITER //CREATE PROCEDURE p1()BEGIN DECLARE a INT; DECLARE b INT DEFAULT 5; SET a = 10; SET a = a * b; SELECT a;END//

CALL p1();

DELIMITER //CREATE PROCEDURE prc_test ()BEGIN DECLARE var2 INT DEFAULT 1; SET var2 = var2 + 1; SET @var2= 10; SET @var2 = @var2 + 1; SELECT var2, @var2;END//DELIMITER ;

VariablesDELIMITER //CREATE PROCEDURE p1() BEGIN DECLARE x1 CHAR(5) DEFAULT 'outer'; BEGIN DECLARE x1 CHAR(5) DEFAULT 'inner'; SELECT x1; END; SELECT x1; END //

DELIMITER ;CALL p1();

Output?

Variables

ParameterStored Procedure memiliki 3 jenis parameter yaitu IN, OUT, dan INOUT.

CREATE PROCEDURE p1 (IN parameter1 INTEGER, OUT parameter2 varchar) SELECT * FROM t;

ParameterIN digunakan untuk menandai bahwa parameter digunakan sebagai input.

DELIMITER //CREATE PROCEDURE p1 (IN i1 INTEGER)BEGIN DECLARE a INT DEFAULT 5; SET a = a * i1; SELECT a;END//

DELIMITER ;CALL p1(5);

ParameterOUT digunakan untuk menandai bahwa parameter digunakan sebagai output.

DELIMITER //CREATE PROCEDURE p2 (OUT o1 INTEGER)BEGIN DECLARE b INT DEFAULT 5; SET o1 = b;END//

DELIMITER ;CALL p2(@a);SELECT @a;

ParameterINOUT digunakan untuk menandai bahwa parameter digunakan sebagai input sekaligus output.

DELIMITER //CREATE PROCEDURE p1 (INOUT io1 FLOAT)BEGIN SET io1 = io1 * pi();END//

DELIMITER ;SET @a=7;CALL p1(@a);SELECT @a;

IF THEN ELSECREATE PROCEDURE p12 (IN parameter1 INT) BEGIN DECLARE variable1 INT; SET variable1 = parameter1 + 1; IF variable1 = 0 THEN INSERT INTO t VALUES (17); END IF; IF parameter1 = 0 THEN UPDATE t SET s1 = s1 + 1; ELSE UPDATE t SET s1 = s1 + 2; END IF; END; //

CASE ELSECREATE PROCEDURE p13 (IN parameter1 INT) BEGIN DECLARE variable1 INT; SET variable1 = parameter1 + 1; CASE variable1 WHEN 0 THEN INSERT INTO t VALUES (17); WHEN 1 THEN INSERT INTO t VALUES (18); ELSE INSERT INTO t VALUES (19); END CASE; END; //

WHILE END WHILECREATE PROCEDURE p14 () BEGIN DECLARE v INT; SET v = 0; WHILE v < 5 DO INSERT INTO t VALUES (v); SET v = v + 1; END WHILE; END; //

REPEAT END REPEATCREATE PROCEDURE p15 () BEGIN DECLARE v INT; SET v = 0; REPEAT INSERT INTO t VALUES (v); SET v = v + 1; UNTIL v >= 5 END REPEAT; END; //

LOOP END LOOPCREATE PROCEDURE p16 () BEGIN DECLARE v INT; SET v = 0; loop_label: LOOP INSERT INTO t VALUES (v); SET v = v + 1; IF v >= 5 THEN LEAVE loop_label; END IF; END LOOP; END; //

ITERATECREATE PROCEDURE p20 () BEGIN DECLARE v INT; SET v = 0; loop_label: LOOP IF v = 3 THEN SET v = v + 1; ITERATE loop_label; END IF; INSERT INTO t VALUES (v); SET v = v + 1; IF v >= 5 THEN LEAVE loop_label; END IF; END LOOP; END; //

CURSORCursor adalah fitur dari stored procedure. Cursor mampu melakukan iterasi sepanjang row data dari suatu tabel. Penggunaan cursor tidak disarankan selama masih ada cara lain, karena relatif lambat.

CURSORMisalkan terdapat sebuah tabel member yang berisi id_member dan nama.Tabel member tersebut berelasi dengan tabel transaksi yang berisi id_transaksi dan jumlah tiap transaksi.

CURSORSeorang DBA ditugaskan membuat laporan tahunan dengan menghitung total transaksi tiap member.

Buat tabel baru (temporary) yang berkolom id_new, nama_new, dan jumlah_new. Tabel ini untuk menampung id, nama, dan total transaksi member.

Total pembelian dapat diperoleh dengan perintah sum().SELECT sum(jumlah) where id_member=???Masalah:Bagaimana bisa mendapat id nya danBagaimana mengambil hasil sum() nya.

Buat stored procedure dengan IN dan OUT.

DELIMITER //CREATE PROCEDURE counttotal(IN id INTEGER, OUT ct INTEGER)BEGIN SET ct:= (SELECT SUM(jumlah) FROM transaksi WHERE id_member=id);END//DELIMITER ;

CALL (1,@a);

Ketika prosedur ini dipanggil, dengan id_member sebagai input, akan menghasilkan total jumlah yang bersesuaian.Selesai?

DELIMITER //CREATE PROCEDURE cursor1 ()BEGINDECLARE id_c INT;DECLARE nama_c VARCHAR(100);DECLARE done INT DEFAULT 0;DECLARE prodrecord CURSOR FOR SELECT id_member, nama FROM member ORDER BY id_member;DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;OPEN prodrecord;WHILE NOT done DOFETCH prodrecord INTO id_c, nama_c;CALL counttotal(id_c,@a);IF @a IS NULL THENSET @a=0;END IF;IF NOT done THENINSERT INTO curtest VALUES (id_c, nama_c, @a);END IF;END WHILE;CLOSE prodrecord;END//DELIMITER ;CALL cursor1();

CursorDECLARE id_c INT;DECLARE nama_c VARCHAR(100);DECLARE done INT DEFAULT 0;DECLARE prodrecord CURSOR FOR SELECT id_member, nama FROM member ORDER BY id_member;DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

Deklarasi harus dilakukan berurutan dari deklarasi variabel, cursor, kemudian handler.

DECLARE prodrecord CURSOR FOR SELECT id_member, nama FROM member ORDER BY id_member;dibuat cursor yang berisi id_member dan nama. Tidak selalu harus seluruh kolom, boleh hanya data yang diperlukan.

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;Dibuat handler ketika pembacaan row mencapai akhir

OPEN prodrecord;Open cursor

FETCH prodrecord INTO id_c, nama_c;Masukkan nilai cursor ke dalam variabel id_c dan nama_c

CALL counttotal(id_c,@a);Panggil stored procedure untuk menghitung jumlah total dengan parameter IN id_c dan OUT @a.

IF NOT done THENINSERT INTO curtest VALUES (id_c, nama_c, @a);END IF;Masukkan data ke dalam tabel baru.

CALL cursor1()

Buat kasus berbeda yang sekiranya tidak bisa dilakukan denga query biasa atau stored procedure biasa.1 Kelompok 2 orangMinggu depan demo