Oracle 9i

1
Oracle 9i

• Database Process
• By Masmur Tarigan, ST
• Oluckysat_at_gmail.com

2
Introduction

• SQL termasuk bahasa pemrograman
• Data yang besar membutuhkan SQL agar proses
  Query-nya cepat dan tepat.
• Database Developer harus paham SQL

3
Agenda

• Managing Tables
• SQL Advance
• Sub query
• Control Program
• PL / SQL
• Variable and Data
• Control Process SQL Statement
• Stored Procedure Function

4
Overview

• Database yang handal harus dapat memproses data
  yang berskala besar
• Di dalam sebuah database terdapat istilah
  table,Key, etc..

FK
Table
PK
Trigger
Proc
View
Sec.
SQL
Etc..
5
Managing Tables

• Tablespace
• Security
• Create table with
• Primary Key
• Foreign Key
• Check
• Manipulating Data
• Create View
• Create Trigger

6
Managing TablesTablespce
Database
7
Managing TablesTablespce

• SQLgt 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

8
Managing TablesSecurity

• Syntax
• CREATE USER user_name
• IDENTIFIED EXTERNALLY By Password
• DEFAULT TABLESPACE tablespace_name
• TEMPORARY TABLESPACE tablespace_name
• ACCOUNT LOCK UNLOCK

9
Managing TablesSecurity

• Contoh
• SQLgt 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

10
Managing TablesSecurity

• 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

11
LOGIN AWAL

• User system
• Password manager

12
Managing TablesCreate Table

• Primary Key
• Foreign Key
• Check/Constraint

13
CREATE TABLE

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

14
Managing TablesCreate Table

• CREATE 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)
• )

15
Managing TablesCreate Table

• CREATE 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)
• )

16
Managing TablesCreate Table

• Masukkan data mahasiswa
• Masukkan data matakuliah
• Masukkan data nilai
• Perhatikan pengaruh penggunaan Primary
  Key,Foreign Key dan Check pada proses pemasukan
  data diatas

17
Managing TablesManipulating Data

• Update
• update mahasiswa set nama New Name where
• nim 03111009
• Lakukan manipulasi data dengan UPDATE

18
Managing TablesCreate view

• Create view vNilai as
• Select a.nim, b.nama, a.nilai From nilai a,
  mahasiswa b
• Where b.nim a.nim
• Meampilkan nim, nilai dari tabel nilai dan
  nama dari tabel mahasiswa

19
Managing TablesCreate Trigger

• CREATE OR REPLACE TRIGGER insMhs
• AFTER INSERT OR UPDATE ON Mahasiswa
• FOR EACH ROW
• BEGIN
• INSERT INTO nilai Values(NEW.NIM,0)
• END

20
SQL Advance

• Operasi Aritmatika
• Penggunaan Group By
• Penggunaan Sub Query
• Membentuk Relasi Tabel
• Inner Join, Left Join Right Join
• Aggregate Function

21
Perintah untuk melihat struktur table

• DESC namatable
• DESC mahasiswa
• DESC berguna untuk mengurutkan data

22
MANIPULATING DATA (DML)

• INSERT MEMASUKKAN DATA
• UPDATE MENGOREKSI DATA
• DELETE MENGHAPUS DATA
• SELECT MENAMPILKAN DATA

23
INSERT (Memasukkan data)

• Formatnya
• INSERT INTO nama tabel VALUES
• (datadata,data)
• Ex
• INSERT INTO mahasiswa VALUES
• (00001,Mawar,Jl. Mawar,P)
• ketentuan-nya
• Urutan kolom wajib sama
• Semua kolom harus terisi
• Yang tidak memakai tanda kutip adalah angka, int,
  dan

24
INSERT

• Formatnya
• 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)

25
INSERT 2

• Formatnya
• INSERT INTO nama tabel SET
• Contohnya
• INSERT INTO mahasiswa SET
• NamaMawar, nobp00010,
• AlamatDesa Suka Bobok No. 10

26
INSERT 3

• Formatnya
• INSERT INTO nama tabel SELECT FROM nama tabel2
• Contohnya
• INSERT INTO mahasiswa SELECT FROM mhsbaru

27
UPDATE (Memperbarui Data)

• Format
• UPDATE namatable SET
• kolom1data1,kolom2data2,kolomndatan
• WHERE kriteria
• Contohnya
• UPDATE mahasiswa SET
• JklL
• WHERE nobp00002

28
DELETE (menghapus data)

• Formatnya
• DELETE FROM namatabel WHERE kriteria
• Contohnya
• DELETE FROM mahasiswa WHERE nobp00002

29
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

30
SELECT

• SELECT kolom1,kolomn
• FROM namatabel
• WHERE kriteria
• Ex
• SELECT alamat
• FROM mahasiswa
• WHERE nobp00010

31
SQL AdvanceOperasi Aritmatika
32
SQL AdvanceOperasi Aritmatika

• Contoh
• SELECT Nim, Nilai, Nilai 10 FROM Nilai
• Gunakanlah semua operator aritmatika diatas
  didalam case study

33
SQL AdvanceGroup By

• 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

34
Sub Query

• Operator
• Paralel

35
Sub QueryOperator

1. , lt, gt, lt, gt, ltgt
2. IN, NOT IN
3. EXISTS, NOT EXISTS
4. ANY, NOT ANY, ALL

36
Sub QueryParalel

• Tahapan penampilan nilai mahasiswa dengan
  ketentuan tertentu

37
Control Program

• Pencabangan
• IF .. THEN .. ELSE .. END IF
• Pengulangan
• LOOP AND LOOP
• FOR LOOP
• WHILE
• GOTO

38
Control ProgramPencabangan

• Declare
• vNim varchar2(8) ? nilai.nimtype
• Begin
• IF THEN
• statement
• ELSE
• END IF
• End
• Perhatikan contoh untuk mengubah tabel nilai
  dengan menambah kolom lulus

39
Control ProgramPengulangan LOOP END LOOP

• Declare
• ltvariablegt
• Begin
• ltstatementgt
• LOOP
• ltstatementgt
• EXIT WHEN ltconditiongt
• END LOOP
• End

40
Control ProgramPengulangan LOOP END LOOP

• SQLgt DECLARE
• pi CONSTANT NUMBER(9,7) 3.1415926
• radius number(5)
• area number(14,2)
• begin
• radius 1
• LOOP
• area piPOWER(radius,2)
• INSERT INTO areas VALUES(radius,area)
• radius radius 1
• EXIT WHEN radiusgt10
• END LOOP
• END

41
Control ProgramPengulangan FOR LOOP

• Declare
• ltvariablegt
• Begin
• ltstatementgt
• FOR ltconditiongt LOOP
• ltstatementgt
• END LOOP
• End
• ltconditiongt ? FOR vMhs IN 1..10 LOOP

42
Control ProgramPengulangan WHILE

• Declare
• ltvariablegt
• Begin
• ltstatementgt
• WHILE ltconditiongt LOOP
• ltstatementgt
• END LOOP
• End

43
Control ProgramPengulangan GOTO

• Declare
• ltvariablegt
• Begin
• ltstatementgt
• ltltULANGgtgt
• ltstatementgt
• IF ltconditiongt THEN
• GOTO ULANG
• END IF
• End

44
Control ProgramCase 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

45
PL/SQL

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

46
PL/SQLIntroduction

• PL/SQL Procedural Language extensions to SQL.
• Bukan bahasa pemrograman yang berdiri sendiri
• Terdapat pada
• Oracle Relational Database
• Oracle Server
• Oracle Form

47
PL/SQLAccess Oracle with PL/SQL

• perhatikan Gambar

48
PL/SQLAdvantage

• 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

49
PL/SQLAdvantage

• Pemrograman Prosedural dengan struktur kontrol
• Eksekusi secara kondisional
• Eksekusi dalam iterasi
• Percabangan
• Cursor eksplisit utk mengelola data multi-row
• Menangani Error
• exception

50
PL/SQLBlock

• perhatikan gambar

51
PL/SQLConstruction

• perhatikan gambar

52
PL/SQLFunction Internal
53
PL/SQLPackage
54
PL/SQLOperator
55
PL/SQLCharacter
56
PL/SQLKonvensi
57
Variable and Data

• Variable PL/SQL
• Data Scalar
• Data Composite
• Variable Reference
• Assign value
• SQLPlus Variable in PL/SQL
• Conversion

58
Variable and DataSyntax

• ltnama_variablegt lttipe_datagt CONSTANT NOT NULL
• ltnilai_defaultgtlteksepsi_plsqlgt

Keterangan ltnama_variablegt merupakan nama
variable yang dibuat lttipe_datagt merupakan
tipe data composit atau scalar ltnilai_defaultgt
merupakan nilai default lteksepsi_plsqlgt
merupakan perintah dalam PL/SQL
59
Variable and Data Syntax

• Declare
• Var_1varchar2(10)
• Var_2Number CONSTANT NOT NULL 10

60
Variable and Data Data Skalar

1. Tipe Data numeric
2. Tipe Data Character
3. Tipe Data Boolean
4. Tipe Data Date-Time
5. Tipe Data Acuan

61
Variable and Data Data Skalar Data Acuan

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

DECLARE vNim mahasiswa.nimtype vnewNimv
vNimTYPE
62
Variable and Data Data Skalar Composite

• DECLARE
• TYPE mahasiswa_record_type IS RECORD OF
• ( vnim varchar2(8) NOT NULL,
• vnama varchar2(10) NOT NULL
• )
• mahasiswa_record mahasiswa_record_type

63
Variable and Data Data Skalar Referensi
64
Control Process SQL Statement

• Control With Cursor
• Explicit Cursor
• Declare
• Open
• Fetch
• Close

65
Control Process SQL Statement Case pd trigger

• CREATE OR REPLACE TRIGGER insMhs
• AFTER INSERT OR UPDATE ON tMahasiswa
• FOR EACH ROW
• DECLARE
• CURSOR cMtKuliah IS
• SELECT KdMtkuliah FROM tMtKuliah
• vKdMtKuliah tMtKuliah.KdMtKuliahType
• BEGIN
• OPEN cMtKuliah
• FETCH cMtKuliah INTO vKdMtKuliah
• WHILE cMtKuliahFOUND LOOP
• INSERT INTO tNilai(nim,kdmtkuliah,nilai)
  VALUES(NEW.NIM,vKdMtKuliah,0)
• FETCH cMtKuliah INTO vKdMtKuliah
• END LOOP
• CLOSE cMtKuliah
• END

66
Stored Procedure Function

• Advantage
• Structure
• Parameter