oracle 9i
DESCRIPTION
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 - PowerPoint PPT PresentationTRANSCRIPT
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