Contoh Stored Procedure SQL

Contoh beberapa procedure sql:

CONTOH 1 MENAMPILKAN DATA DARI NILAI RATA-RATA BERDASARKAN ID.

delimiter //

create procedure rata (in idd int(2))
begin
declare nilai int;
declare msg char(20);
select avg(respond + attendance)/2 into nilai from performance where id=idd group by id;
if nilai>70 then set msg=”good”;
else set msg=”bad”;
end if;
select msg as hasil;
end//

CONTOH 2 MENAMPILKAN DATA SESUAI DENGAN NAMA YANG DI INPUTKAN.

delimiter //
create procedure nama (nama varchar(20))
begin
select * from pegawai where name=nama;
end//

CONTOH 3 MEMBUAT PROCEDURE TRIGGER UNTUK INSERT DI TABLE LAIN KETIKA ADA PRINTAH UPDATE.

create trigger performance before update on performance for each row
-> begin
-> insert into log_perf
-> set ID = OLD.ID, kd_jabatan=old.kd_jabatan, respond=old.respond, solving=old.solving, helpfull=old.helpfull, attendance=old.attendance;
-> end//

kalo belum buat table lognya, buat dulu.
create table log_perf (log_id int(10), id int(2), kd_jabatan varchar(2), respond int(3), solving int(3), helpfull int(3), attendance int(3));//

alter table log_perf change log_id log_Id int not null auto_increment primary key;//