pengenalan pl/sql -...

31
Pengenalan PL/SQL Pertemuan 3

Upload: hadan

Post on 02-Mar-2019

244 views

Category:

Documents


0 download

TRANSCRIPT

Page 1: Pengenalan PL/SQL - lulu.staff.gunadarma.ac.idlulu.staff.gunadarma.ac.id/Downloads/files/37377/Pengenalan+PLSQL.pdf · Shared Oracle Server library. Keuntungan PL/SQL Application

Pengenalan PL/SQL

Pertemuan 3

Page 2: Pengenalan PL/SQL - lulu.staff.gunadarma.ac.idlulu.staff.gunadarma.ac.id/Downloads/files/37377/Pengenalan+PLSQL.pdf · Shared Oracle Server library. Keuntungan PL/SQL Application

Apakah PL/SQL

• PL/SQL adalah perluasan SQL dengan fitur

bahasa pemrograman .

• SQL Data manipulation and query statements

disertakan dalam unit kode prosedural.disertakan dalam unit kode prosedural.

Page 3: Pengenalan PL/SQL - lulu.staff.gunadarma.ac.idlulu.staff.gunadarma.ac.id/Downloads/files/37377/Pengenalan+PLSQL.pdf · Shared Oracle Server library. Keuntungan PL/SQL Application

Keuntungan PL/SQL

• Integrasi

ApplicationApplicationApplicationApplication

Oracle ServerOracle ServerSharedSharedlibrarylibrary

Page 4: Pengenalan PL/SQL - lulu.staff.gunadarma.ac.idlulu.staff.gunadarma.ac.id/Downloads/files/37377/Pengenalan+PLSQL.pdf · Shared Oracle Server library. Keuntungan PL/SQL Application

Keuntungan PL/SQL

Application Other DBMSs

SQLSQL

SQLSQL

SQLSQL

SQLSQL

• Meningkatkan Unjuk Kerja

ApplicationOracle with

PL/SQL

SQLSQL

SQLSQLIF...THENIF...THEN

SQLSQLELSEELSE

SQLSQLEND IF;END IF;SQLSQL

Page 5: Pengenalan PL/SQL - lulu.staff.gunadarma.ac.idlulu.staff.gunadarma.ac.id/Downloads/files/37377/Pengenalan+PLSQL.pdf · Shared Oracle Server library. Keuntungan PL/SQL Application

Struktur Blok PL/SQL

•• DECLARE – Optional

Variables, cursors, user-defined exceptions

• BEGIN – Harus ada

– Statemen SQL

– Statemen PL/SQL

• EXCEPTION – Opsional• EXCEPTION – Opsional

Aksi yang ditampilkan ketika terjadi error

• END; – Harus ada DECLAREDECLARE

BEGINBEGIN

EXCEPTIONEXCEPTION

END;END;

Page 6: Pengenalan PL/SQL - lulu.staff.gunadarma.ac.idlulu.staff.gunadarma.ac.id/Downloads/files/37377/Pengenalan+PLSQL.pdf · Shared Oracle Server library. Keuntungan PL/SQL Application

Struktur Blok PL/SQL

DECLAREDECLARE

v_variable VARCHAR2(5);

BEGIN

SELECT column_name

INTO v_variable

FROM table_name;FROM table_name;

EXCEPTION

WHEN exception_name THEN

...

END;

DECLAREDECLARE

BEGINBEGIN

EXCEPTIONEXCEPTION

END;END;

Page 7: Pengenalan PL/SQL - lulu.staff.gunadarma.ac.idlulu.staff.gunadarma.ac.id/Downloads/files/37377/Pengenalan+PLSQL.pdf · Shared Oracle Server library. Keuntungan PL/SQL Application

Tipe Block • Anonymous Prosedur Fungsi

[DECLARE][DECLARE]

BEGINBEGIN

----statementsstatements

PROCEDURE namePROCEDURE name

ISIS

BEGINBEGIN

----statementsstatements

FUNCTION nameFUNCTION name

RETURN datatypeRETURN datatype

ISIS

BEGINBEGIN

----statementsstatements

RETURN value;RETURN value;

[EXCEPTION][EXCEPTION]

END;END;

[EXCEPTION][EXCEPTION]

END;END;

RETURN value;RETURN value;

[EXCEPTION][EXCEPTION]

END;END;

Page 8: Pengenalan PL/SQL - lulu.staff.gunadarma.ac.idlulu.staff.gunadarma.ac.id/Downloads/files/37377/Pengenalan+PLSQL.pdf · Shared Oracle Server library. Keuntungan PL/SQL Application

Program Constructs

AnonymousAnonymous

blockblock

ApplicationApplication

triggertrigger

Stored Stored

procedure/procedure/

functionfunction

ApplicationApplication

procedure/procedure/

DECLAREDECLARE

BEGINBEGIN

triggertrigger

DatabaseDatabase

triggertrigger

procedure/procedure/

functionfunction

PackagedPackaged

procedure/procedure/

functionfunction

EXCEPTIONEXCEPTION

END;END;

Page 9: Pengenalan PL/SQL - lulu.staff.gunadarma.ac.idlulu.staff.gunadarma.ac.id/Downloads/files/37377/Pengenalan+PLSQL.pdf · Shared Oracle Server library. Keuntungan PL/SQL Application

Kegunaan Variables

• Variables digunakan untuk:

– Menyimpan data sementara

– Memanipulasi nilai yang disimpan

– Dapat digunakan kemballi

– Mudah dalam pemeliharaan

Page 10: Pengenalan PL/SQL - lulu.staff.gunadarma.ac.idlulu.staff.gunadarma.ac.id/Downloads/files/37377/Pengenalan+PLSQL.pdf · Shared Oracle Server library. Keuntungan PL/SQL Application

Penanganan Variable dalam PL/SQL

– Deklarasi dan inisialisasi variabel pada

declaration section.

– Pemberian nilai baru untuk variable pada

executable section.executable section.

– Melewatkan nilai kedalam blok PL/SQL

melalui parameter.

– Menampilkan hasil melalui variable output

Page 11: Pengenalan PL/SQL - lulu.staff.gunadarma.ac.idlulu.staff.gunadarma.ac.id/Downloads/files/37377/Pengenalan+PLSQL.pdf · Shared Oracle Server library. Keuntungan PL/SQL Application

TipeVariables

– Variabel PL/SQL s:

• Scalar

• Composite

• Reference• Reference

• LOB (large objects)

– Variabel Non-PL/SQL : Bind and host

variables

Page 12: Pengenalan PL/SQL - lulu.staff.gunadarma.ac.idlulu.staff.gunadarma.ac.id/Downloads/files/37377/Pengenalan+PLSQL.pdf · Shared Oracle Server library. Keuntungan PL/SQL Application

TRUETRUE

Tipe Variable25-OCT-9925-OCT-99

“Four score and seven years agoour fathers brought forth uponthis continent, a new nation,conceived in LIBERTY, and dedicatedto the proposition that all menare created equal.”

AtlantaAtlanta

are created equal.”

256120.08256120.08

Page 13: Pengenalan PL/SQL - lulu.staff.gunadarma.ac.idlulu.staff.gunadarma.ac.id/Downloads/files/37377/Pengenalan+PLSQL.pdf · Shared Oracle Server library. Keuntungan PL/SQL Application

Deklarasi Variabel PL/SQL

SyntaxSyntaxSyntaxSyntax

identifier [CONSTANT] datatype [NOT NULL]

[:= | DEFAULT expr];

ContohContohContohContoh

Declare

v_hiredate DATE;

v_deptno NUMBER(2) NOT NULL := 10;

v_location VARCHAR2(13) := 'Atlanta';

c_comm CONSTANT NUMBER := 1400;

Page 14: Pengenalan PL/SQL - lulu.staff.gunadarma.ac.idlulu.staff.gunadarma.ac.id/Downloads/files/37377/Pengenalan+PLSQL.pdf · Shared Oracle Server library. Keuntungan PL/SQL Application

Deklarasi Variabel PL/SQL

• Guideline

– Mengikuti aturan penamaan.

– Inisialisasi variable dengan NOT NULL dan

CONSTANT.CONSTANT.

– Inisialisasi identifier menggunakan assignment

operator (:=) atau reserved word DEFAULT.

– Deklarasi paling banyak satu identifier per baris.

Page 15: Pengenalan PL/SQL - lulu.staff.gunadarma.ac.idlulu.staff.gunadarma.ac.id/Downloads/files/37377/Pengenalan+PLSQL.pdf · Shared Oracle Server library. Keuntungan PL/SQL Application

Aturan Penamaan

– Dua variabel dapat memiliki nama yang sama, disediakan pada blok yang berbeda.

– Nama variabel (identifier) tidak seharusnya sama dengan nama kolom yang digunakan pada blok PL/SQL.

DECLARE

empno NUMBER(4);

BEGIN

SELECT empno

INTO empno

FROM emp

WHERE ename = 'SMITH';

END;

Page 16: Pengenalan PL/SQL - lulu.staff.gunadarma.ac.idlulu.staff.gunadarma.ac.id/Downloads/files/37377/Pengenalan+PLSQL.pdf · Shared Oracle Server library. Keuntungan PL/SQL Application

Pemberian nilai Variable

SyntaxSyntax

ContohContoh

SyntaxSyntax

ContohContoh

• identifier := expr;

v_ename := 'Maduro';

v_hiredate := '31-DEC-98';

ContohContoh

Pemberian nilai predefined Pemberian nilai predefined hiredate hiredate funtuk karyawan baru. funtuk karyawan baru.

ContohContoh

Pemberian nilai predefined Pemberian nilai predefined hiredate hiredate funtuk karyawan baru. funtuk karyawan baru.

Set the employee name to Maduro. Set the employee name to Maduro. Set the employee name to Maduro. Set the employee name to Maduro.

Page 17: Pengenalan PL/SQL - lulu.staff.gunadarma.ac.idlulu.staff.gunadarma.ac.id/Downloads/files/37377/Pengenalan+PLSQL.pdf · Shared Oracle Server library. Keuntungan PL/SQL Application

Inisialisasi dan Keyword Variable

• Menggunakan:

– Assignment operator (:=)

– DEFAULT keyword

– NOT NULL constraint– NOT NULL constraint

Page 18: Pengenalan PL/SQL - lulu.staff.gunadarma.ac.idlulu.staff.gunadarma.ac.id/Downloads/files/37377/Pengenalan+PLSQL.pdf · Shared Oracle Server library. Keuntungan PL/SQL Application

Tipe data Scalar

• Hold a single value

• Have no internal components

• Hold a single value

• Have no internal components

25-OCT-9925-OCT-99“Four score and seven years

TRUETRUE25-OCT-9925-OCT-99

AtlantaAtlanta

“Four score and seven yearsago our fathers brought forth upon this continent, anew nation, conceived in LIBERTY, and dedicated to the proposition that all menare created equal.”

TRUETRUE

256120.08256120.08

Page 19: Pengenalan PL/SQL - lulu.staff.gunadarma.ac.idlulu.staff.gunadarma.ac.id/Downloads/files/37377/Pengenalan+PLSQL.pdf · Shared Oracle Server library. Keuntungan PL/SQL Application

Tipe data Scalar Dasar

– VARCHAR2 (maximum_length)

– NUMBER [(precision, scale)]

– DATE

– CHAR [(maximum_length)]

LONG– LONG

– LONG RAW

– BOOLEAN

– BINARY_INTEGER

– PLS_INTEGER

Page 20: Pengenalan PL/SQL - lulu.staff.gunadarma.ac.idlulu.staff.gunadarma.ac.id/Downloads/files/37377/Pengenalan+PLSQL.pdf · Shared Oracle Server library. Keuntungan PL/SQL Application

Base Scalar Datatypes

– VARCHAR2 (maximum_length)

– NUMBER [(precision, scale)]

– DATE

– CHAR [(maximum_length)]

LONG– LONG

– LONG RAW

– BOOLEAN

– BINARY_INTEGER

– PLS_INTEGER

Page 21: Pengenalan PL/SQL - lulu.staff.gunadarma.ac.idlulu.staff.gunadarma.ac.id/Downloads/files/37377/Pengenalan+PLSQL.pdf · Shared Oracle Server library. Keuntungan PL/SQL Application

Deklarasi Variabel Scalar

v_job VARCHAR2(9);

v_count BINARY_INTEGER := 0;

v_total_sal NUMBER(9,2) := 0;

• Examples

v_orderdate DATE := SYSDATE + 7;

c_tax_rate CONSTANT NUMBER(3,2) := 8.25;

v_valid BOOLEAN NOT NULL := TRUE;

Page 22: Pengenalan PL/SQL - lulu.staff.gunadarma.ac.idlulu.staff.gunadarma.ac.id/Downloads/files/37377/Pengenalan+PLSQL.pdf · Shared Oracle Server library. Keuntungan PL/SQL Application

Attribute %TYPE

– Deklarasi sebuah variabel sesuai dengan:

• Definisi kolom sebuah database

• Variabel yang telah dideklarasikan sebelumnya

– Awali %TYPE dengan:

• The database table and column

• The previously declared variable name

Page 23: Pengenalan PL/SQL - lulu.staff.gunadarma.ac.idlulu.staff.gunadarma.ac.id/Downloads/files/37377/Pengenalan+PLSQL.pdf · Shared Oracle Server library. Keuntungan PL/SQL Application

Declaring Variables

dengan Attribute %TYPE

• Contoh

...

v_ename emp.ename%TYPE;v_ename emp.ename%TYPE;

v_balance NUMBER(7,2);

v_min_balance v_balance%TYPE := 10;

...

Page 24: Pengenalan PL/SQL - lulu.staff.gunadarma.ac.idlulu.staff.gunadarma.ac.id/Downloads/files/37377/Pengenalan+PLSQL.pdf · Shared Oracle Server library. Keuntungan PL/SQL Application

Deklarasi Boolean Variable

– Hanya nilai TRUE, FALSE, and NULL yang

dapat diberikan untuk variable Boolean.

– Variable dihubungkan dengan logical

operators AND, OR, dan NOT.

– Variable selalu menghasilkan nilai TRUE, – Variable selalu menghasilkan nilai TRUE,

FALSE, atau NULL.

– Ekspresi Arithmetic, character, and date

dapat digunakan untuk menghasilkan nilai

Boolean.

Page 25: Pengenalan PL/SQL - lulu.staff.gunadarma.ac.idlulu.staff.gunadarma.ac.id/Downloads/files/37377/Pengenalan+PLSQL.pdf · Shared Oracle Server library. Keuntungan PL/SQL Application

PL/SQL table structure PL/SQL table structure

Structur PL/SQL Record

TRUE 23-DEC-98 ATLANTA

1 5000

2 2345

3 12

4 3456

1 SMITH

2 JONES

3 NANCY

4 TIM

BINARY_INTEGER

VARCHAR2

BINARY_INTEGER

NUMBER

Page 26: Pengenalan PL/SQL - lulu.staff.gunadarma.ac.idlulu.staff.gunadarma.ac.id/Downloads/files/37377/Pengenalan+PLSQL.pdf · Shared Oracle Server library. Keuntungan PL/SQL Application

Tipe Variable LOB

BookBook

(CLOB)(CLOB)

PhotoPhoto

(BLOB)(BLOB)

MovieMovie

(BFILE)(BFILE)

NCLOBNCLOB

Page 27: Pengenalan PL/SQL - lulu.staff.gunadarma.ac.idlulu.staff.gunadarma.ac.id/Downloads/files/37377/Pengenalan+PLSQL.pdf · Shared Oracle Server library. Keuntungan PL/SQL Application

Bind Variables

O/SO/S

Server

O/SO/S

Bind variableBind variable

Page 28: Pengenalan PL/SQL - lulu.staff.gunadarma.ac.idlulu.staff.gunadarma.ac.id/Downloads/files/37377/Pengenalan+PLSQL.pdf · Shared Oracle Server library. Keuntungan PL/SQL Application

Mereferensikan Variabel Non-PL/SQL

• Menyimpan gaji tahunan kedalam

SQL*Plus host variable.:g_monthly_sal := v_sal / 12;

– Mereferensikan variabel non-PL/SQL sebagai

host variables.

– Awali referensi dengan tanda colon (:).

:g_monthly_sal := v_sal / 12;

Page 29: Pengenalan PL/SQL - lulu.staff.gunadarma.ac.idlulu.staff.gunadarma.ac.id/Downloads/files/37377/Pengenalan+PLSQL.pdf · Shared Oracle Server library. Keuntungan PL/SQL Application

DBMS_OUTPUT.PUT_LINE

– Prosedur Oracle-supplied packaged

– Sebagai alternatif menampilkan data dari

blok PL/SQL

– Harus di aktifkan dalam SQL*Plus dengan – Harus di aktifkan dalam SQL*Plus dengan

perintah SET SERVEROUTPUT ON

Page 30: Pengenalan PL/SQL - lulu.staff.gunadarma.ac.idlulu.staff.gunadarma.ac.id/Downloads/files/37377/Pengenalan+PLSQL.pdf · Shared Oracle Server library. Keuntungan PL/SQL Application

Kesimpulan

– PL/SQL blocks are composed of the

following sections:

• Declarative (optional)

• Executable (required)

DECLAREDECLARE

BEGINBEGIN• Executable (required)

• Exception handling (optional)

– A PL/SQL block can be an

anonymous block, procedure, or

function.

EXCEPTIONEXCEPTION

END;END;

Page 31: Pengenalan PL/SQL - lulu.staff.gunadarma.ac.idlulu.staff.gunadarma.ac.id/Downloads/files/37377/Pengenalan+PLSQL.pdf · Shared Oracle Server library. Keuntungan PL/SQL Application

Summary

– PL/SQL identifiers:

• Are defined in the declarative section

• Can be of scalar, composite, reference, or LOB

datatypedatatype

• Can be based on the structure of another

variable or database object

• Can be initialized