Pemrograman Basis Data

Tri Afirianto, S.T., M.T.tri.afirianto@ub.ac.idUniversitas Brawijaya

Pengantar dan Kontrak Kuliah

Peraturan Kuliah• Berdasarkan Buku Pedoman PTIIK

Materi Kuliah• Stored Procedure Introduction• Basic SQL Procedure Structure• Overview SQL• Using Flow of Control Statements• Cursor and Result Sets• Condition Handling• Dynamic SQL• Nested Stored Procedure• User Defined Functions (UDFs)• Trigger

Perkuliahan• Penilaian:• Quis : 20%• UTS : 15%• UAS/Project : 20%• Ujian Koding : 20%• Praktikum : 25%

Aturan Kelas• Kuliah hadir tepat waktu (batas keterlambatan 15

menit)• Kehadiran minimal 80%• Jujur dan bersungguh-sungguh dalam mengerjakan

setiap tugas• Menghindari plagiasi dalam setiap tugas• Pelanggaran plagiasi akan memperoleh sanksi:

pekerjaan tidak diakui (nilai 0)

Etika Mengirim Pesan

Filosofi Pemrograman

“Everybody should learn how to program a computer …because it teaches you how to think.”

- Steve Jobs -

Filosofi Pemrograman

“Good programmer write code for machine…..”

Filosofi Pemrograman

“Great Programmer write code for other programmer…..”

DB2 Stored Procedure Introduction

Unit Objectives• After completing this unit, you should be able to:• Describe a stored procedure• Determine when a stored procedure should be used• Describe execution flow using stored procedures• List DB2 stored procedure design and general tips

What is a Stored Procedure?A Stored Procedure:• Is an executable program under control of the DB2

server• Is invoked via the SQL CALL statement• May contain business logic• May be invoked locally or remotely• May receive or pass parameters• May produce result sets

When to Use a Stored Procedure

Stored Procedure may be justified when:• The application’s performance expectations are not

being met• There are a large number of clients to which

application code is distributed• The client application generates relatively heavy

database activity, but involves little user interaction• The client application code changes frequently• The access to the client application code needs to

be controlled• The client application executes many SQL

statements

Considerations when Using Stored Procedures

Stored Procedures are incorporated by:• Determining client portion of application• Thin client?• Selecting stored procedure server• Developing stored procedure• Determining calling parameters• Selecting programming language SQL PL / PL SQL,

IBM DB2 Data Studio Developer / CLP, Command Editor, Text Editor• Defining SP to server• Coding and testing

Application Flow

Client/Server: the Big Picture

Server Procedure: Design/General Tips• Consider the cost of invoking a stored procedure

versus the cost of network transmission for a distributed application• Stored procedures run in background – do not use

the standard I/O streams• No commands to terminate current process• Overloading stored procedures restricted to

parameter subsets• SQL limited by stored procedure CREATE

parameters

Unit Summary• Describe a stored procedure• Determine when a stored procedure should be

used• Describe execution flow using stored procedures• List DB2 stored procedure design and general tips