Tugas PBD Kelas P5 M3

Nama   : Ayu Shelawati

NIM     : 07.41010.0279

Dosen  : Tan Amelia

Tugas   : Contoh stored procedure, function dan trigger

 

1. Stored Procedure dari contoh database diatas.

• Stored Procedure IN

          CREATE PROCEDURE proc_thn @thn numeric(4)

          AS

          SELECT judul, pengarang

          FROM master_koleksi

          WHERE tahun = @thn

          EXECUTE proc_thn = 2008

          CREATE PROCEDURE proc_tgl_kembali @tgl datetime

          AS

          SELECT A.judul, B.nama

          FROM master_koleksi A, master_anggota B, master_pinjam C, detil_pinjam D

          WHERE A.id_koleksi = D.id_koleksi

          AND D.id_transaksi = C.id_transaksi

          AND C.id_anggota = B.id_anggota

          AND D.tgl_kembali = @tgl

          EXECUTE proc_tgl_kembali = ‘27/09/2011’

• Stored Procedure OUT

         CREATE PROCEDURE proc_maxdenda @denda INT OUTPUT

         AS

         SELECT @denda = max(denda)

         FROM detil_pinjam

         DECLARE @TheMax INT

         EXECUTE proc_maxdenda @denda = @TheMax OUTPUT

         SELECT TheMax = @TheMax

• Stored Procedure IN/OUT

        CREATE PROCEDURE proc_countpengarang

        (@pengarang varchar(50), @pengarangcount INT OUTPUT)

        AS

        SELECT @pengarangcount = count(*)

        FROM master_koleksi

        WHERE pengarang = @pengarang

        DECLARE @count INT

        EXECUTE proc_countpengarang @pengarang = ‘ABC’, @pengarangcount = @count OUTPUT

        SELECT TheCount = @count

        CREATE PROCEDURE proc_countkembali

        (@tgl_kmbali varchar(50), @tglcount INT OUTPUT)

        AS

        SELECT @tglcount = count(*)

        FROM detil_pinjam

        WHERE tgl_kembali = @tgl_kembali

       DECLARE @count INT

       EXECUTE proc_countkembali @tgl_kembali = ‘27/09/2011’, @tglcount = @count OUTPUT

       SELECT TheCount = @count

2. Function dari contoh database diatas.

• Function IN

        CREATE FUNCTION besardendajenis @jns varchar(50)

        RETURNS numeric(10)

        AS

        BEGIN

        Declare @return numeric(10)

        SELECT @return = denda

        FROM jenis_koleksi

        WHERE nama_jenis = @jns

        END

        RETURN @return

        END

        CREATE FUNCTION tahunjudul @thn varchar(50)

        RETURNS varchar(50)

        AS

        BEGIN

        DECLARE @return varchar(50)

        SELECT @return = judul

        FROM master_koleksi

        WHERE tahun= @thn

        END

        RETURN @return

        END

• Function OUT

• Function IN/OUT

3. Trigger dari contoh database diatas

• Trigger INSERT

        CREATE TRIGGER trans1

        ON master_pinjam

        FOR INSERT

        AS

        INSERT INTO detil_pinjam SET tgl_harus_kembali = getdate()+7

• Trigger UPDATE

        CREATE TRIGGER denda_trigger

        BEFORE UPDATE ON jenis_koleksi

        REFERENCING NEW ROW AS n, OLD ROW AS o

        FOR EACH ROW

        IF n.denda <> o.denda THEN

        END IF

• Trigger DELETE

        CREATE TRIGGER trans2

        ON master_pinjam

        FOR DELETE

        AS

        RAISEERROR(‘TIDAK DAPAT MENGHAPUS TRANSAKSI’)

        ROLLBACK TRANSACTION

        END