Transcript Oracle 9i

Oracle 9i
Database Process
By Masmur Tarigan, ST
[email protected]
Introduction
SQL termasuk bahasa pemrograman
 Data yang besar membutuhkan SQL agar
proses Query-nya cepat dan tepat.
 Database Developer harus paham SQL

Agenda
Managing Tables
 SQL Advance
 Sub query
 Control Program
 PL / SQL
 Variable and Data
 Control Process SQL Statement
 Stored Procedure & Function

Overview
Database yang handal harus dapat
memproses data yang berskala besar
 Di dalam sebuah database terdapat istilah
table,Key, etc..

FK
Table
PK
Trig
Vie
ger
w
SQL Etc
..
Proc
Sec.
Managing Tables



Tablespace
Security
Create table with :
– Primary Key
– Foreign Key
– Check



Manipulating Data
Create View
Create Trigger
Managing Tables
Tablespce
Database
Tablespace
Segment
Extent
DB-Block
Managing Tables
Tablespce
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 Tables
Security
Syntax :
CREATE USER user_name
IDENTIFIED EXTERNALLY | {By Password}
[DEFAULT TABLESPACE tablespace_name]
[TEMPORARY TABLESPACE tablespace_name]
[ACCOUNT {LOCK | UNLOCK}]
Managing Tables
Security
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 Tables
Security
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 AWAL
User : system
 Password : manager

Managing Tables
Create Table



Primary Key
Foreign Key
Check/Constraint
CREATE TABLE
CREATE TABLE nama_tabel
(field1 type(length) not null,
Field2 type(length), fieldn type(length),
CONSTRAINT namakey PRIMARY
KEY(field1,field2)
)
Managing Tables
Create 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)
)
Managing Tables
Create 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)
);
Managing Tables
Create 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 Tables
Manipulating Data

Update
update mahasiswa set nama = ‘New Name’ where
nim = ‘03111009’;
*
Lakukan manipulasi data dengan UPDATE
Managing Tables
Create 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
Managing Tables
Create 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 Advance
Operasi Aritmatika
 Penggunaan Group By
 Penggunaan Sub Query
 Membentuk Relasi Tabel
 Inner Join, Left Join & Right Join
 Aggregate Function

Perintah untuk melihat struktur
table
DESC namatable
DESC mahasiswa
DESC berguna untuk mengurutkan data
MANIPULATING DATA (DML)
INSERT = MEMASUKKAN DATA
 UPDATE = MENGOREKSI DATA
 DELETE = MENGHAPUS DATA
 SELECT = MENAMPILKAN 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 sama
2. Semua kolom harus terisi
3. Yang tidak memakai tanda kutip adalah angka, int,
dan
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’)
INSERT 2
Formatnya :
INSERT INTO nama tabel SET
Contohnya :
INSERT INTO mahasiswa SET
Nama=‘Mawar’, nobp=‘00010’,
Alamat=‘Desa Suka Bobok No. 10’
INSERT 3
Formatnya :
INSERT INTO nama tabel SELECT * FROM nama
tabel2
Contohnya :
INSERT INTO mahasiswa SELECT * FROM mhsbaru
UPDATE (Memperbarui Data)
Format :
UPDATE namatable SET
kolom1=data1,kolom2=data2,kolomn=datan
WHERE kriteria
Contohnya ;
UPDATE mahasiswa SET
Jkl=‘L’
WHERE nobp=‘00002’;
DELETE (menghapus data)
Formatnya :
DELETE FROM namatabel WHERE
kriteria;
Contohnya :
DELETE FROM mahasiswa WHERE
nobp=‘00002’;
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
SELECT
SELECT kolom1,kolomn
FROM namatabel
WHERE kriteria;
Ex :
SELECT alamat
FROM mahasiswa
WHERE nobp=‘00010’;
SQL Advance
Operasi Aritmatika
SQL Advance
Operasi Aritmatika
Contoh :
SELECT Nim, Nilai, Nilai + 10 FROM
Nilai
* Gunakanlah semua operator aritmatika
diatas didalam case study
SQL Advance
Group 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 Query
Operator
 Paralel

Sub Query
Operator
1.
2.
3.
4.
=, <, >, <=, >=, <>
IN, NOT IN
EXISTS, NOT EXISTS
ANY, NOT ANY, ALL
Sub Query
Paralel
Tahapan penampilan nilai mahasiswa
dengan ketentuan tertentu
Control Program

Pencabangan
– IF .. THEN .. ELSE .. END IF

Pengulangan :
–
–
–
–
LOOP … AND LOOP
FOR LOOP
WHILE
GOTO
Control Program
Pencabangan
Declare
vNim
varchar2(8);  nilai.nim%type
Begin
IF … THEN
statement
ELSE
…
END IF;
End;
* Perhatikan contoh untuk mengubah tabel nilai
dengan menambah kolom lulus
Control Program
Pengulangan – LOOP … END LOOP
Declare
<variable>
Begin
<statement>
LOOP
<statement>
EXIT WHEN <condition>
END LOOP;
End;
Control Program
Pengulangan – LOOP … END LOOP
SQL> DECLARE
pi 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 Program
Pengulangan – FOR LOOP
Declare
<variable>
Begin
<statement>
FOR <condition> LOOP
<statement>
END LOOP;
End;
* <condition>  FOR vMhs IN 1..10 LOOP
Control Program
Pengulangan – WHILE
Declare
<variable>
Begin
<statement>
WHILE <condition> LOOP
<statement>
END LOOP;
End;
Control Program
Pengulangan – GOTO
Declare
<variable>
Begin
<statement>
<<ULANG>>
<statement>
IF <condition> THEN
GOTO ULANG;
END IF;
End;
Control Program
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/SQL










Introduction
Access Oracle with PL/SQL
Advantage
Environment
Block
Construction
Function and Package Internal
Operator
Character
Convention
PL/SQL
Introduction
PL/SQL : “Procedural Language
extensions to SQL.
 Bukan bahasa pemrograman yang berdiri
sendiri
 Terdapat pada :

– Oracle Relational Database
– Oracle Server
– Oracle Form
PL/SQL
Access Oracle with PL/SQL
… perhatikan Gambar
PL/SQL
Advantage

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/SQL
Advantage

Pemrograman Prosedural dengan struktur
kontrol
–
–
–
–

Eksekusi secara kondisional
Eksekusi dalam iterasi
Percabangan
Cursor eksplisit utk mengelola data multi-row
Menangani Error
– exception
PL/SQL
Block
… perhatikan gambar
PL/SQL
Construction
… perhatikan gambar
PL/SQL
Function Internal
PL/SQL
Package
PL/SQL
Operator
PL/SQL
Character
PL/SQL
Konvensi
Variable and Data
Variable PL/SQL
 Data Scalar
 Data Composite
 Variable Reference
 Assign value
 SQL*Plus Variable in PL/SQL
 Conversion

Variable and Data
Syntax
<nama_variable> <tipe_data> [CONSTANT] [NOT
NULL]
[:= {<nilai_default>|<eksepsi_plsql>}];
Keterangan :
<nama_variable>
<tipe_data>
<nilai_default>
<eksepsi_plsql>
: merupakan nama variable yang dibuat
: merupakan tipe data composit atau scalar
: merupakan nilai default
: merupakan perintah dalam PL/SQL
Variable and Data
Syntax
Declare
Var_1varchar2(10);
Var_2Number CONSTANT NOT NULL :=10;
Variable and Data
Data Skalar
1.
2.
3.
4.
5.
Tipe Data numeric
Tipe Data Character
Tipe Data Boolean
Tipe Data Date-Time
Tipe Data Acuan
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.nim%type;
vnewNimv vNim%TYPE;
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;
Variable and Data
Data Skalar – Referensi
Control Process SQL
Statement
Control With Cursor
 Explicit Cursor
 Declare
 Open
 Fetch
 Close

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.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 & Function
Advantage
 Structure
 Parameter
