Download - Oracle 9i
![Page 2: Oracle 9i](https://reader035.vdokumen.com/reader035/viewer/2022081516/5681451f550346895db1e299/html5/thumbnails/2.jpg)
Introduction Introduction
SQL termasuk bahasa pemrogramanData yang besar membutuhkan SQL agar
proses Query-nya cepat dan tepat.Database Developer harus paham SQL
![Page 3: Oracle 9i](https://reader035.vdokumen.com/reader035/viewer/2022081516/5681451f550346895db1e299/html5/thumbnails/3.jpg)
AgendaAgenda
Managing Tables SQL AdvanceSub queryControl Program PL / SQLVariable and DataControl Process SQL StatementStored Procedure & Function
![Page 4: Oracle 9i](https://reader035.vdokumen.com/reader035/viewer/2022081516/5681451f550346895db1e299/html5/thumbnails/4.jpg)
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
![Page 5: Oracle 9i](https://reader035.vdokumen.com/reader035/viewer/2022081516/5681451f550346895db1e299/html5/thumbnails/5.jpg)
Managing TablesManaging Tables
Tablespace Security Create table with :
– Primary Key– Foreign Key– Check
Manipulating Data Create View Create Trigger
![Page 6: Oracle 9i](https://reader035.vdokumen.com/reader035/viewer/2022081516/5681451f550346895db1e299/html5/thumbnails/6.jpg)
Managing TablesManaging TablesTablespceTablespce
Database
Tablespace
Segment
Extent
DB-Block
![Page 7: Oracle 9i](https://reader035.vdokumen.com/reader035/viewer/2022081516/5681451f550346895db1e299/html5/thumbnails/7.jpg)
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
![Page 8: Oracle 9i](https://reader035.vdokumen.com/reader035/viewer/2022081516/5681451f550346895db1e299/html5/thumbnails/8.jpg)
Managing TablesManaging TablesSecuritySecurity
Syntax :CREATE USER user_nameIDENTIFIED EXTERNALLY | {By Password}[DEFAULT TABLESPACE tablespace_name][TEMPORARY TABLESPACE tablespace_name][ACCOUNT {LOCK | UNLOCK}]
![Page 9: Oracle 9i](https://reader035.vdokumen.com/reader035/viewer/2022081516/5681451f550346895db1e299/html5/thumbnails/9.jpg)
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
![Page 10: Oracle 9i](https://reader035.vdokumen.com/reader035/viewer/2022081516/5681451f550346895db1e299/html5/thumbnails/10.jpg)
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
![Page 11: Oracle 9i](https://reader035.vdokumen.com/reader035/viewer/2022081516/5681451f550346895db1e299/html5/thumbnails/11.jpg)
LOGIN AWALLOGIN AWAL
User : systemPassword : manager
![Page 12: Oracle 9i](https://reader035.vdokumen.com/reader035/viewer/2022081516/5681451f550346895db1e299/html5/thumbnails/12.jpg)
Managing TablesManaging TablesCreate TableCreate Table
Primary Key Foreign Key Check/Constraint
![Page 13: Oracle 9i](https://reader035.vdokumen.com/reader035/viewer/2022081516/5681451f550346895db1e299/html5/thumbnails/13.jpg)
CREATE TABLECREATE TABLE
CREATE TABLE nama_tabel(field1 type(length) not null,Field2 type(length), fieldn type(length),CONSTRAINT namakey PRIMARY
KEY(field1,field2))
![Page 14: Oracle 9i](https://reader035.vdokumen.com/reader035/viewer/2022081516/5681451f550346895db1e299/html5/thumbnails/14.jpg)
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))
![Page 15: Oracle 9i](https://reader035.vdokumen.com/reader035/viewer/2022081516/5681451f550346895db1e299/html5/thumbnails/15.jpg)
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));
![Page 16: Oracle 9i](https://reader035.vdokumen.com/reader035/viewer/2022081516/5681451f550346895db1e299/html5/thumbnails/16.jpg)
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
![Page 17: Oracle 9i](https://reader035.vdokumen.com/reader035/viewer/2022081516/5681451f550346895db1e299/html5/thumbnails/17.jpg)
Managing TablesManaging TablesManipulating DataManipulating Data Update
update mahasiswa set nama = ‘New Name’ wherenim = ‘03111009’;
* Lakukan manipulasi data dengan UPDATE
![Page 18: Oracle 9i](https://reader035.vdokumen.com/reader035/viewer/2022081516/5681451f550346895db1e299/html5/thumbnails/18.jpg)
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
![Page 19: Oracle 9i](https://reader035.vdokumen.com/reader035/viewer/2022081516/5681451f550346895db1e299/html5/thumbnails/19.jpg)
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;
![Page 20: Oracle 9i](https://reader035.vdokumen.com/reader035/viewer/2022081516/5681451f550346895db1e299/html5/thumbnails/20.jpg)
SQL AdvanceSQL Advance
Operasi AritmatikaPenggunaan Group ByPenggunaan Sub QueryMembentuk Relasi Tabel Inner Join, Left Join & Right JoinAggregate Function
![Page 21: Oracle 9i](https://reader035.vdokumen.com/reader035/viewer/2022081516/5681451f550346895db1e299/html5/thumbnails/21.jpg)
Perintah untuk melihat struktur Perintah untuk melihat struktur tabletable
DESC namatable
DESC mahasiswa
DESC berguna untuk mengurutkan data
![Page 22: Oracle 9i](https://reader035.vdokumen.com/reader035/viewer/2022081516/5681451f550346895db1e299/html5/thumbnails/22.jpg)
MANIPULATING DATA (DML)MANIPULATING DATA (DML)
INSERT = MEMASUKKAN DATAUPDATE = MENGOREKSI DATADELETE = MENGHAPUS DATASELECT = MENAMPILKAN DATA
![Page 23: Oracle 9i](https://reader035.vdokumen.com/reader035/viewer/2022081516/5681451f550346895db1e299/html5/thumbnails/23.jpg)
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
![Page 24: Oracle 9i](https://reader035.vdokumen.com/reader035/viewer/2022081516/5681451f550346895db1e299/html5/thumbnails/24.jpg)
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’)
![Page 25: Oracle 9i](https://reader035.vdokumen.com/reader035/viewer/2022081516/5681451f550346895db1e299/html5/thumbnails/25.jpg)
INSERT 2INSERT 2
Formatnya :INSERT INTO nama tabel SET
Contohnya :INSERT INTO mahasiswa SETNama=‘Mawar’, nobp=‘00010’,Alamat=‘Desa Suka Bobok No. 10’
![Page 26: Oracle 9i](https://reader035.vdokumen.com/reader035/viewer/2022081516/5681451f550346895db1e299/html5/thumbnails/26.jpg)
INSERT 3INSERT 3
Formatnya :INSERT INTO nama tabel SELECT * FROM nama tabel2
Contohnya :INSERT INTO mahasiswa SELECT * FROM mhsbaru
![Page 27: Oracle 9i](https://reader035.vdokumen.com/reader035/viewer/2022081516/5681451f550346895db1e299/html5/thumbnails/27.jpg)
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’;
![Page 28: Oracle 9i](https://reader035.vdokumen.com/reader035/viewer/2022081516/5681451f550346895db1e299/html5/thumbnails/28.jpg)
DELETE (menghapus data)DELETE (menghapus data)
Formatnya :DELETE FROM namatabel WHERE kriteria;
Contohnya :DELETE FROM mahasiswa WHERE nobp=‘00002’;
![Page 29: Oracle 9i](https://reader035.vdokumen.com/reader035/viewer/2022081516/5681451f550346895db1e299/html5/thumbnails/29.jpg)
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
![Page 30: Oracle 9i](https://reader035.vdokumen.com/reader035/viewer/2022081516/5681451f550346895db1e299/html5/thumbnails/30.jpg)
SELECTSELECTSELECT kolom1,kolomnFROM namatabelWHERE kriteria;
Ex : SELECT alamatFROM mahasiswaWHERE nobp=‘00010’;
![Page 31: Oracle 9i](https://reader035.vdokumen.com/reader035/viewer/2022081516/5681451f550346895db1e299/html5/thumbnails/31.jpg)
SQL AdvanceSQL AdvanceOperasi AritmatikaOperasi Aritmatika
![Page 32: Oracle 9i](https://reader035.vdokumen.com/reader035/viewer/2022081516/5681451f550346895db1e299/html5/thumbnails/32.jpg)
SQL AdvanceSQL AdvanceOperasi AritmatikaOperasi Aritmatika
Contoh :
SELECT Nim, Nilai, Nilai + 10 FROM Nilai
* Gunakanlah semua operator aritmatika diatas didalam case study
![Page 33: Oracle 9i](https://reader035.vdokumen.com/reader035/viewer/2022081516/5681451f550346895db1e299/html5/thumbnails/33.jpg)
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
![Page 34: Oracle 9i](https://reader035.vdokumen.com/reader035/viewer/2022081516/5681451f550346895db1e299/html5/thumbnails/34.jpg)
Sub QuerySub Query
OperatorParalel
![Page 35: Oracle 9i](https://reader035.vdokumen.com/reader035/viewer/2022081516/5681451f550346895db1e299/html5/thumbnails/35.jpg)
Sub QuerySub QueryOperatorOperator
1. =, <, >, <=, >=, <>2. IN, NOT IN3. EXISTS, NOT EXISTS4. ANY, NOT ANY, ALL
![Page 36: Oracle 9i](https://reader035.vdokumen.com/reader035/viewer/2022081516/5681451f550346895db1e299/html5/thumbnails/36.jpg)
Sub QuerySub QueryParalelParalel
Tahapan penampilan nilai mahasiswa dengan ketentuan tertentu
![Page 37: Oracle 9i](https://reader035.vdokumen.com/reader035/viewer/2022081516/5681451f550346895db1e299/html5/thumbnails/37.jpg)
Control ProgramControl Program
Pencabangan– IF .. THEN .. ELSE .. END IF
Pengulangan :– LOOP … AND LOOP– FOR LOOP– WHILE– GOTO
![Page 38: Oracle 9i](https://reader035.vdokumen.com/reader035/viewer/2022081516/5681451f550346895db1e299/html5/thumbnails/38.jpg)
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
![Page 39: Oracle 9i](https://reader035.vdokumen.com/reader035/viewer/2022081516/5681451f550346895db1e299/html5/thumbnails/39.jpg)
Control ProgramControl ProgramPengulangan – LOOP … END LOOPPengulangan – LOOP … END LOOP
Declare<variable>
Begin <statement> LOOP <statement> EXIT WHEN <condition>
END LOOP;End;
![Page 40: Oracle 9i](https://reader035.vdokumen.com/reader035/viewer/2022081516/5681451f550346895db1e299/html5/thumbnails/40.jpg)
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;
![Page 41: Oracle 9i](https://reader035.vdokumen.com/reader035/viewer/2022081516/5681451f550346895db1e299/html5/thumbnails/41.jpg)
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
![Page 42: Oracle 9i](https://reader035.vdokumen.com/reader035/viewer/2022081516/5681451f550346895db1e299/html5/thumbnails/42.jpg)
Control ProgramControl ProgramPengulangan – WHILEPengulangan – WHILE
Declare<variable>
Begin <statement> WHILE <condition> LOOP <statement> END LOOP;End;
![Page 43: Oracle 9i](https://reader035.vdokumen.com/reader035/viewer/2022081516/5681451f550346895db1e299/html5/thumbnails/43.jpg)
Control ProgramControl ProgramPengulangan – GOTOPengulangan – GOTO
Declare<variable>
Begin <statement> <<ULANG>> <statement> IF <condition> THEN GOTO ULANG; END IF;End;
![Page 44: Oracle 9i](https://reader035.vdokumen.com/reader035/viewer/2022081516/5681451f550346895db1e299/html5/thumbnails/44.jpg)
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
![Page 45: Oracle 9i](https://reader035.vdokumen.com/reader035/viewer/2022081516/5681451f550346895db1e299/html5/thumbnails/45.jpg)
PL/SQLPL/SQL
Introduction Access Oracle with PL/SQL Advantage Environment Block Construction Function and Package Internal Operator Character Convention
![Page 46: Oracle 9i](https://reader035.vdokumen.com/reader035/viewer/2022081516/5681451f550346895db1e299/html5/thumbnails/46.jpg)
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
![Page 47: Oracle 9i](https://reader035.vdokumen.com/reader035/viewer/2022081516/5681451f550346895db1e299/html5/thumbnails/47.jpg)
PL/SQLPL/SQLAccess Oracle with PL/SQLAccess Oracle with PL/SQL
… perhatikan Gambar
![Page 48: Oracle 9i](https://reader035.vdokumen.com/reader035/viewer/2022081516/5681451f550346895db1e299/html5/thumbnails/48.jpg)
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
![Page 49: Oracle 9i](https://reader035.vdokumen.com/reader035/viewer/2022081516/5681451f550346895db1e299/html5/thumbnails/49.jpg)
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
![Page 50: Oracle 9i](https://reader035.vdokumen.com/reader035/viewer/2022081516/5681451f550346895db1e299/html5/thumbnails/50.jpg)
PL/SQLPL/SQLBlockBlock
… perhatikan gambar
![Page 51: Oracle 9i](https://reader035.vdokumen.com/reader035/viewer/2022081516/5681451f550346895db1e299/html5/thumbnails/51.jpg)
PL/SQLPL/SQLConstructionConstruction
… perhatikan gambar
![Page 52: Oracle 9i](https://reader035.vdokumen.com/reader035/viewer/2022081516/5681451f550346895db1e299/html5/thumbnails/52.jpg)
PL/SQLPL/SQLFunction InternalFunction Internal
![Page 53: Oracle 9i](https://reader035.vdokumen.com/reader035/viewer/2022081516/5681451f550346895db1e299/html5/thumbnails/53.jpg)
PL/SQLPL/SQLPackagePackage
![Page 54: Oracle 9i](https://reader035.vdokumen.com/reader035/viewer/2022081516/5681451f550346895db1e299/html5/thumbnails/54.jpg)
PL/SQLPL/SQLOperatorOperator
![Page 55: Oracle 9i](https://reader035.vdokumen.com/reader035/viewer/2022081516/5681451f550346895db1e299/html5/thumbnails/55.jpg)
PL/SQLPL/SQLCharacterCharacter
![Page 56: Oracle 9i](https://reader035.vdokumen.com/reader035/viewer/2022081516/5681451f550346895db1e299/html5/thumbnails/56.jpg)
PL/SQLPL/SQLKonvensiKonvensi
![Page 57: Oracle 9i](https://reader035.vdokumen.com/reader035/viewer/2022081516/5681451f550346895db1e299/html5/thumbnails/57.jpg)
Variable and DataVariable and Data
Variable PL/SQLData ScalarData CompositeVariable ReferenceAssign value SQL*Plus Variable in PL/SQLConversion
![Page 58: Oracle 9i](https://reader035.vdokumen.com/reader035/viewer/2022081516/5681451f550346895db1e299/html5/thumbnails/58.jpg)
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
![Page 59: Oracle 9i](https://reader035.vdokumen.com/reader035/viewer/2022081516/5681451f550346895db1e299/html5/thumbnails/59.jpg)
Variable and DataVariable and Data SyntaxSyntax
DeclareVar_1varchar2(10);Var_2Number CONSTANT NOT NULL :=10;
![Page 60: Oracle 9i](https://reader035.vdokumen.com/reader035/viewer/2022081516/5681451f550346895db1e299/html5/thumbnails/60.jpg)
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
![Page 61: Oracle 9i](https://reader035.vdokumen.com/reader035/viewer/2022081516/5681451f550346895db1e299/html5/thumbnails/61.jpg)
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;
![Page 62: Oracle 9i](https://reader035.vdokumen.com/reader035/viewer/2022081516/5681451f550346895db1e299/html5/thumbnails/62.jpg)
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;
![Page 63: Oracle 9i](https://reader035.vdokumen.com/reader035/viewer/2022081516/5681451f550346895db1e299/html5/thumbnails/63.jpg)
Variable and DataVariable and Data Data Skalar – ReferensiData Skalar – Referensi
![Page 64: Oracle 9i](https://reader035.vdokumen.com/reader035/viewer/2022081516/5681451f550346895db1e299/html5/thumbnails/64.jpg)
Control Process SQL Control Process SQL StatementStatementControl With CursorExplicit CursorDeclareOpenFetchClose
![Page 65: Oracle 9i](https://reader035.vdokumen.com/reader035/viewer/2022081516/5681451f550346895db1e299/html5/thumbnails/65.jpg)
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;
![Page 66: Oracle 9i](https://reader035.vdokumen.com/reader035/viewer/2022081516/5681451f550346895db1e299/html5/thumbnails/66.jpg)
Stored Procedure & FunctionStored Procedure & Function
AdvantageStructureParameter