oracle 9i

Oracle 9iOracle 9iDatabase Process

By Masmur Tarigan, STOluckysat@gmail.com

Introduction Introduction

SQL termasuk bahasa pemrogramanData yang besar membutuhkan SQL agar

proses Query-nya cepat dan tepat.Database Developer harus paham SQL

AgendaAgenda

Managing Tables SQL AdvanceSub queryControl Program PL / SQLVariable and DataControl Process SQL StatementStored Procedure & Function

Overview Overview

Database yang handal harus dapat memproses data yang berskala besar

Di dalam sebuah database terdapat istilah table,Key, etc..

Proc

Etc..

Sec.

Trigger

ViewSQL

PKFKTable

Managing TablesManaging Tables

Tablespace Security Create table with :

– Primary Key– Foreign Key– Check

Manipulating Data Create View Create Trigger

Managing TablesManaging TablesTablespceTablespce

Database

Tablespace

Segment

Extent

DB-Block

Managing TablesManaging TablesTablespceTablespce

SQL> create tablespace latihan 2 datafile 'D:\oracle\data\latihan.data' 3 size 5M autoextend on next 5M maxsize 100M 4 /create tablespace latihan

* Buat tablespace berdasarkan kasus yang anda kerjakan. Contoh : Koperasi, Perkuliahan, dst

Managing TablesManaging TablesSecuritySecurity

Syntax :CREATE USER user_nameIDENTIFIED EXTERNALLY | {By Password}[DEFAULT TABLESPACE tablespace_name][TEMPORARY TABLESPACE tablespace_name][ACCOUNT {LOCK | UNLOCK}]

Managing TablesManaging TablesSecuritySecurity

Contoh :SQL> create user mazmur 2 identified by tarigan 3 default tablespace latihan 4 /identified by tarigan

* Buat user masing-masing dengan menggunakan tablespace yang telah anda buat sebelumya

Managing TablesManaging TablesSecuritySecurity Grant Access Revoke Access

Example:1. Access Global

- GRANT connect, resource TO mazmur

- REVOKE connect, resource FROM mazmur

2. Access Object- GRANT select on mahasiswa TO mazmur

- REVOKE select on mahasiswa FROM mazmur

LOGIN AWALLOGIN AWAL

User : systemPassword : manager

Managing TablesManaging TablesCreate TableCreate Table

Primary Key Foreign Key Check/Constraint

CREATE TABLECREATE TABLE

CREATE TABLE nama_tabel(field1 type(length) not null,Field2 type(length), fieldn type(length),CONSTRAINT namakey PRIMARY

KEY(field1,field2))

Managing TablesManaging TablesCreate TableCreate TableCREATE TABLE MAHASISWA( NIM VARCHAR2(8),

NAMA VARCHAR2(25),TGLMASUK DATE NOT NULL,TGLKELUAR DATE NOT NULL,ALAMAT VARCHAR2(50),TMPLAHIR VARCHAR2(15),TGLLAHIR DATE NOT NULL,JKELAMIN VARCHAR2(1) CHECK (JKELAMIN IN('L','P')),STATUS VARCHAR2(1) CHECK (STATUS IN('B','K','J','D')),AGAMA VARCHAR2(1) CHECK (AGAMA IN('I','P','K','H','B')),FAKULTAS VARCHAR2(1) CHECK (FAKULTAS IN('T','M','A')),JURUSAN VARCHAR2(20),DARAH VARCHAR2(2) CHECK (DARAH IN('A','B','0','AB')),GAMBAR VARCHAR2(50),CONSTRAINT PK_MAHASISWA PRIMARY KEY(NIM))

Managing TablesManaging TablesCreate TableCreate TableCREATE TABLE MTKULIAH( KDMTKULIAH VARCHAR2(5), NMMTKULIAH VARCHAR2(15), SKS NUMBER(1,0), CONSTRAINT PK_MTKULIAH PRIMARY KEY(KDMTKULIAH)

)

CREATE TABLE NILAI(NIM VARCHAR2(8),KDMTKULIAH VARCHAR2(5),NILAI NUMBER(3,1),CONSTRAINT FK_NILAI_MAHASISWA FOREIGN KEY(NIM) REFERENCES

MAHASISWA ON DELETE CASCADE,CONSTRAINT FK_NILAI_MTKULIAH FOREIGN KEY(KDMTKULIAH)

REFERENCES MTKULIAH ON DELETE CASCADE,CONSTRAINT PK_NILAI PRIMARY KEY(NIM,KDMTKULIAH));

Managing TablesManaging TablesCreate TableCreate Table Masukkan data mahasiswa Masukkan data matakuliah Masukkan data nilai

* Perhatikan pengaruh penggunaan Primary Key,Foreign Key dan Check pada proses pemasukan data diatas

Managing TablesManaging TablesManipulating DataManipulating Data Update

update mahasiswa set nama = ‘New Name’ wherenim = ‘03111009’;

* Lakukan manipulasi data dengan UPDATE

Managing TablesManaging TablesCreate viewCreate view

Create view vNilai asSelect a.nim, b.nama, a.nilai From nilai a, mahasiswa bWhere b.nim = a.nim

* Meampilkan nim, nilai dari tabel nilai dan nama dari tabel mahasiswa

Managing TablesManaging TablesCreate TriggerCreate Trigger

CREATE OR REPLACE TRIGGER insMhs AFTER INSERT OR UPDATE ON Mahasiswa FOR EACH ROW

BEGIN INSERT INTO nilai Values(:NEW.NIM,0);END;

SQL AdvanceSQL Advance

Operasi AritmatikaPenggunaan Group ByPenggunaan Sub QueryMembentuk Relasi Tabel Inner Join, Left Join & Right JoinAggregate Function

Perintah untuk melihat struktur Perintah untuk melihat struktur tabletable

DESC namatable

DESC mahasiswa

DESC berguna untuk mengurutkan data

MANIPULATING DATA (DML)MANIPULATING DATA (DML)

INSERT = MEMASUKKAN DATAUPDATE = MENGOREKSI DATADELETE = MENGHAPUS DATASELECT = MENAMPILKAN DATA

INSERT (Memasukkan data)INSERT (Memasukkan data)Formatnya :INSERT INTO nama tabel VALUES (‘data’’data’,’data’)

Ex : INSERT INTO mahasiswa VALUES (‘00001’,’Mawar’,’Jl. Mawar’,’P’)

ketentuan-nya :1. Urutan kolom wajib sama2. Semua kolom harus terisi3. Yang tidak memakai tanda kutip adalah angka, int, dan

INSERTINSERTFormatnya :INSERT INTO nama tabel(namakolom1,namakolom2,namakolom ke-n)VALUES (data1,data2,data ke n)

Contohnya :INSERT INTO mahasiswa(nama,nobp,alamat)VALUES (‘Frans’,’00002’,’Jl. Melati’)

INSERT 2INSERT 2

Formatnya :INSERT INTO nama tabel SET

Contohnya :INSERT INTO mahasiswa SETNama=‘Mawar’, nobp=‘00010’,Alamat=‘Desa Suka Bobok No. 10’

INSERT 3INSERT 3

Formatnya :INSERT INTO nama tabel SELECT * FROM nama tabel2

Contohnya :INSERT INTO mahasiswa SELECT * FROM mhsbaru

UPDATE (Memperbarui Data)UPDATE (Memperbarui Data)Format :UPDATE namatable SET kolom1=data1,kolom2=data2,kolomn=datanWHERE kriteria

Contohnya ;UPDATE mahasiswa SET Jkl=‘L’WHERE nobp=‘00002’;

DELETE (menghapus data)DELETE (menghapus data)

Formatnya :DELETE FROM namatabel WHERE kriteria;

Contohnya :DELETE FROM mahasiswa WHERE nobp=‘00002’;

SELECT (Menampilkan Data)SELECT (Menampilkan Data)Formatnya :SELECT kolom1, kolom2, kolomn FROM NamaTabel;

Contohnya :SELECT nobp, nama FROM mahasiswa;

Kolom kalau diwakili dengan tanda *, maka akan muncul semua nya

Ex : SELECT * FROM mahasiswa

SELECTSELECTSELECT kolom1,kolomnFROM namatabelWHERE kriteria;

Ex : SELECT alamatFROM mahasiswaWHERE nobp=‘00010’;

SQL AdvanceSQL AdvanceOperasi AritmatikaOperasi Aritmatika

SQL AdvanceSQL AdvanceOperasi AritmatikaOperasi Aritmatika

Contoh :

SELECT Nim, Nilai, Nilai + 10 FROM Nilai

* Gunakanlah semua operator aritmatika diatas didalam case study

SQL AdvanceSQL AdvanceGroup ByGroup By

1. Pengelompokan baris berdasarkan group yag sama

Berkaitan dengan aggregate function : AVG, SUM, COUNT, MAX, MIN

 Contoh :SELECT Nim, SUM(Nilai) FROM Nilai GROUP BY Nim

SELECT Nim, MAX(Nilai) FROM Nilai GROUP BY Nim * Gunakanlah semua operator aritmatika diatas

didalam case study

Sub QuerySub Query

OperatorParalel

Sub QuerySub QueryOperatorOperator

1. =, <, >, <=, >=, <>2. IN, NOT IN3. EXISTS, NOT EXISTS4. ANY, NOT ANY, ALL

Sub QuerySub QueryParalelParalel

Tahapan penampilan nilai mahasiswa dengan ketentuan tertentu

Control ProgramControl Program

Pencabangan– IF .. THEN .. ELSE .. END IF

Pengulangan :– LOOP … AND LOOP– FOR LOOP– WHILE– GOTO

Control ProgramControl ProgramPencabanganPencabangan

Declare vNim varchar2(8); nilai.nim%type

BeginIF … THEN

statement ELSE … END IF;End;* Perhatikan contoh untuk mengubah tabel nilai dengan

menambah kolom lulus

Control ProgramControl ProgramPengulangan – LOOP … END LOOPPengulangan – LOOP … END LOOP

Declare<variable>

Begin <statement> LOOP <statement> EXIT WHEN <condition>

END LOOP;End;

Control ProgramControl ProgramPengulangan – LOOP … END LOOPPengulangan – LOOP … END LOOP

SQL> DECLAREpi CONSTANT NUMBER(9,7) := 3.1415926;radius number(5);area number(14,2);begin

radius := 1;LOOP

area := pi*POWER(radius,2);INSERT INTO areas VALUES(radius,area);radius := radius + 1;

EXIT WHEN radius>10;END LOOP;

END;

Control ProgramControl ProgramPengulangan – FOR LOOPPengulangan – FOR LOOP

Declare<variable>

Begin <statement> FOR <condition> LOOP <statement> END LOOP;End;

* <condition> FOR vMhs IN 1..10 LOOP

Control ProgramControl ProgramPengulangan – WHILEPengulangan – WHILE

Declare<variable>

Begin <statement> WHILE <condition> LOOP <statement> END LOOP;End;

Control ProgramControl ProgramPengulangan – GOTOPengulangan – GOTO

Declare<variable>

Begin <statement> <<ULANG>> <statement> IF <condition> THEN GOTO ULANG; END IF;End;

Control ProgramControl ProgramCase Study Case Study

1. buatlah tabel : Kurs create table Kurs( dollar number(4), rupiah number(10) ) Kasus:

Buatlah program untuk mengisi tabel KURS ini dengan menggunakan pengulangan, nilai dollar

dari $1 s/d $250, tentukan juga nilai Rupiahnya dengan nilai kurs $1 = Rp 9800

PL/SQLPL/SQL

Introduction Access Oracle with PL/SQL Advantage Environment Block Construction Function and Package Internal Operator Character Convention

PL/SQLPL/SQLIntroductionIntroduction

PL/SQL : “Procedural Language extensions to SQL.

Bukan bahasa pemrograman yang berdiri sendiri

Terdapat pada :– Oracle Relational Database– Oracle Server– Oracle Form

PL/SQLPL/SQLAccess Oracle with PL/SQLAccess Oracle with PL/SQL

… perhatikan Gambar

PL/SQLPL/SQLAdvantageAdvantage

Pemrograman Modular– Mengelompokkan instruksi2x yang berkaitan dalam

blok-blok– Sub blok dapat dimasukkan dalam super blok– Menguraikan persoalan yang kompleks dalam satu

modul yang logis Deklarasi Variabel

– Variabel, constant, cursors dan exception– Tipe data sederhana sampai composite(bentukan)– Variabel dinamis

PL/SQLPL/SQLAdvantageAdvantage

Pemrograman Prosedural dengan struktur kontrol– Eksekusi secara kondisional– Eksekusi dalam iterasi– Percabangan– Cursor eksplisit utk mengelola data multi-row

Menangani Error– exception

PL/SQLPL/SQLBlockBlock

… perhatikan gambar

PL/SQLPL/SQLConstructionConstruction

… perhatikan gambar

PL/SQLPL/SQLFunction InternalFunction Internal

PL/SQLPL/SQLPackagePackage

PL/SQLPL/SQLOperatorOperator

PL/SQLPL/SQLCharacterCharacter

PL/SQLPL/SQLKonvensiKonvensi

Variable and DataVariable and Data

Variable PL/SQLData ScalarData CompositeVariable ReferenceAssign value SQL*Plus Variable in PL/SQLConversion

Variable and DataVariable and DataSyntaxSyntax

<nama_variable> <tipe_data> [CONSTANT] [NOT NULL]

[:= {<nilai_default>|<eksepsi_plsql>}];

Keterangan :<nama_variable> : merupakan nama variable yang dibuat<tipe_data> : merupakan tipe data composit atau scalar<nilai_default> : merupakan nilai default<eksepsi_plsql> : merupakan perintah dalam PL/SQL

Variable and DataVariable and Data SyntaxSyntax

DeclareVar_1varchar2(10);Var_2Number CONSTANT NOT NULL :=10;

Variable and DataVariable and Data Data SkalarData Skalar

1. Tipe Data numeric2. Tipe Data Character3. Tipe Data Boolean4. Tipe Data Date-Time5. Tipe Data Acuan

Variable and DataVariable and Data Data Skalar – Data AcuanData Skalar – Data Acuan

- menggunakan atribut %TYPE-  pengacuan data diambil dari:                       1. tipe data variable lain 2. tipe data kolom dalam suatu table

DECLAREvNim mahasiswa.nim%type;

vnewNimv vNim%TYPE;

Variable and DataVariable and Data Data Skalar – CompositeData Skalar – Composite

DECLARETYPE mahasiswa_record_type IS RECORD OF( vnim varchar2(8) NOT NULL, vnama varchar2(10) NOT NULL);

mahasiswa_record mahasiswa_record_type;

Variable and DataVariable and Data Data Skalar – ReferensiData Skalar – Referensi

Control Process SQL Control Process SQL StatementStatementControl With CursorExplicit CursorDeclareOpenFetchClose

Control Process SQL Control Process SQL Statement – Case pd triggerStatement – Case pd triggerCREATE OR REPLACE TRIGGER insMhs AFTER INSERT OR UPDATE ON tMahasiswa FOR EACH ROWDECLARECURSOR cMtKuliah IS

SELECT KdMtkuliah FROM tMtKuliah;

vKdMtKuliah tMtKuliah.KdMtKuliah%Type;BEGIN OPEN cMtKuliah; FETCH cMtKuliah INTO vKdMtKuliah; WHILE cMtKuliah%FOUND LOOP

INSERT INTO tNilai(nim,kdmtkuliah,nilai) VALUES(:NEW.NIM,vKdMtKuliah,0); FETCH cMtKuliah INTO vKdMtKuliah; END LOOP; CLOSE cMtKuliah;END;

Stored Procedure & FunctionStored Procedure & Function

AdvantageStructureParameter