Oracle 9 i Database Process By Masmur Tarigan

Oracle 9 i Database Process By Masmur Tarigan, ST Oluckysat@gmail. com

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

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

Overview Database yang handal harus dapat memproses data yang berskala besar l Di dalam sebuah database terdapat istilah table, Key, etc. . l Table PK FK Vie Trig Proc ger w Sec. SQL Etc. .

Managing Tables l l l Tablespace Security Create table with : – Primary Key – Foreign Key – Check l l l Manipulating Data Create View Create Trigger

Managing Tablespce Database Tablespace Segment Extent DB-Block

Managing Tablespce SQL> create tablespace latihan 2 datafile 'D: oracledatalatihan. data' 3 size 5 M autoextend on next 5 M maxsize 100 M 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 l l 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 l Password : manager l

Managing Tables Create Table l l l Primary Key Foreign Key Check/Constraint

CREATE TABLE nama_tabel (field 1 type(length) not null, Field 2 type(length), fieldn type(length), CONSTRAINT namakey PRIMARY KEY(field 1, field 2) )

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

Managing Tables Create Table CREATE TABLE MTKULIAH( KDMTKULIAH VARCHAR 2(5), NMMTKULIAH VARCHAR 2(15), SKS NUMBER(1, 0), CONSTRAINT PK_MTKULIAH PRIMARY KEY(KDMTKULIAH) ) CREATE TABLE NILAI( NIM VARCHAR 2(8), KDMTKULIAH VARCHAR 2(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 l l l * 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 l Update update mahasiswa set nama = ‘New Name’ where nim = ‘ 03111009’; * Lakukan manipulasi data dengan UPDATE

Managing Tables Create view v. Nilai 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 ins. Mhs AFTER INSERT OR UPDATE ON Mahasiswa FOR EACH ROW BEGIN INSERT INTO nilai Values(: NEW. NIM, 0); END;

SQL Advance Operasi Aritmatika l Penggunaan Group By l Penggunaan Sub Query l Membentuk Relasi Tabel l Inner Join, Left Join & Right Join l Aggregate Function l

Perintah untuk melihat struktur table DESC namatable DESC mahasiswa DESC berguna untuk mengurutkan data

MANIPULATING DATA (DML) INSERT = MEMASUKKAN DATA l UPDATE = MENGOREKSI DATA l DELETE = MENGHAPUS DATA l SELECT = MENAMPILKAN DATA l

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 (namakolom 1, namakolom 2, namakolom ke-n) VALUES (data 1, data 2, 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 tabel 2 Contohnya : INSERT INTO mahasiswa SELECT * FROM mhsbaru

UPDATE (Memperbarui Data) Format : UPDATE namatable SET kolom 1=data 1, kolom 2=data 2, 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 kolom 1, kolom 2, kolomn FROM Nama. Tabel; Contohnya : SELECT nobp, nama FROM mahasiswa; Kolom kalau diwakili dengan tanda *, maka akan muncul semua nya Ex : SELECT * FROM mahasiswa

SELECT kolom 1, 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 + 10 FROM Nilai * Gunakanlah semua operator aritmatika diatas didalam case study

SQL Advance Group By 1. Pengelompokan baris berdasarkan group yag sama l 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 l Paralel l

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 l Pencabangan – IF. . THEN. . ELSE. . END IF l Pengulangan : – LOOP … AND LOOP – FOR LOOP – WHILE – GOTO

Control Program Pencabangan Declare v. Nim varchar 2(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 v. Mhs 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 l l l l l 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. l Bukan bahasa pemrograman yang berdiri sendiri l Terdapat pada : l – Oracle Relational Database – Oracle Server – Oracle Form

PL/SQL Access Oracle with PL/SQL … perhatikan Gambar

PL/SQL Advantage l Pemrograman Modular – Mengelompokkan instruksi 2 x yang berkaitan dalam blok-blok – Sub blok dapat dimasukkan dalam super blok – Menguraikan persoalan yang kompleks dalam satu modul yang logis l Deklarasi Variabel – Variabel, constant, cursors dan exception – Tipe data sederhana sampai composite(bentukan) – Variabel dinamis

PL/SQL Advantage l Pemrograman Prosedural dengan struktur kontrol – Eksekusi secara kondisional – Eksekusi dalam iterasi – Percabangan – Cursor eksplisit utk mengelola data multi-row l 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 l Data Scalar l Data Composite l Variable Reference l Assign value l SQL*Plus Variable in PL/SQL l Conversion l

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_1 varchar 2(10); Var_2 Number CONSTANT NOT NULL : =10;

Variable and 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 Skalar – Data Acuan - menggunakan atribut %TYPE - pengacuan data diambil dari: 1. tipe data variable lain 2. tipe data kolom dalam suatu table DECLARE v. Nim mahasiswa. nim%type; vnew. Nimv v. Nim%TYPE;

Variable and Data Skalar – Composite DECLARE TYPE mahasiswa_record_type IS RECORD OF ( vnim varchar 2(8) NOT NULL, vnama varchar 2(10) NOT NULL ); mahasiswa_record_type;

Variable and Data Skalar – Referensi

Control Process SQL Statement Control With Cursor l Explicit Cursor l Declare l Open l Fetch l Close l

Control Process SQL Statement – Case pd trigger CREATE OR REPLACE TRIGGER ins. Mhs AFTER INSERT OR UPDATE ON t. Mahasiswa FOR EACH ROW DECLARE CURSOR c. Mt. Kuliah IS SELECT Kd. Mtkuliah FROM t. Mt. Kuliah; v. Kd. Mt. Kuliah t. Mt. Kuliah. Kd. Mt. Kuliah%Type; BEGIN OPEN c. Mt. Kuliah; FETCH c. Mt. Kuliah INTO v. Kd. Mt. Kuliah; WHILE c. Mt. Kuliah%FOUND LOOP INSERT INTO t. Nilai(nim, kdmtkuliah, nilai) VALUES(: NEW. NIM, v. Kd. Mt. Kuliah, 0); FETCH c. Mt. Kuliah INTO v. Kd. Mt. Kuliah; END LOOP; CLOSE c. Mt. Kuliah; END;

Stored Procedure & Function Advantage l Structure l Parameter l