ALL About Oracle
Perintah Dasar SQL
Perintah Select
Perintah Select digunakan untuk :
Selection : memilih baris/record
Projection : memilih kolom/field
Join : mengambil data dari beberapa tabel yang berbeda
Syntax Select
SELECT [DISTINCT] {*,column[alias],..}
FROM table;
Contoh:
SELECT loc,deptno
FROM emp
OPERATOR ARITMATIKA
Derajat Operator :
- Perkalian dan Pembagian
- Penjumlahan dan Pengurangan
- Bila ada ( ) maka ekspresi didalamnya dilaksanakan terlebih dahulu
Contoh :
SELECT ename,sal,12*sal+100 FROM emp;
NILAI NULL
Adalah nilai yang tidak ada, tidak didefinisikan, tidak diterapkan dan tidak diketahui.
Nilai null ¹ 0 { zero}, null ¹ spasi {blank}
Contoh:
SELECT ename,job,sal,comm. FROM emp;
KOLOM ALIAS
Digunakan untuk merubah tampilan judul kolom.
Menggunakan AS atau “ “ {Double Qoute}
Contoh:
SELECT ename AS name, sal “salary” FROM emp;
OPERATOR GABUNGAN
Digunakan untuk menghubungkan kolom satu dengan kolom yang lain.
Menggunakan opertor gabungan { || }
Contoh:
SELECT ename || job AS employees
FROM emp;
KOLOM ALIAS DAN GABUNGAN
Digunakan untuk merubah tampilan judul kolom.
Menggunakan AS atau “ “ {Double Qoute}
Contoh:
1. SELECT ename AS name, sal salary FROM emp;
2. SELECT ename ||”is a”|| job AS “employee details”
FROM emp;
BARIS DUPLIKAT / GANDA
Syntax Select
SELECT [DISTINCT] {*,column[alias],..}
FROM table;
Contoh:
SELECT loc,deptno
FROM emp
SQL dan SQL*PLUS
Keistimewaan SQL :
* Dapat digunakan oleh user baik yang berpengalaman atau tidak dalam pembuatan program
* Merupakan bahasa non prosedural
* Mengurangi sejumlah waktu untuk membuat dan memelihara sistem
* Sintaksnya dalam bahasa Inggris
SQL dan SQL*PLUS
SQL *PLUS adalah lingkungan untuk melakukan hal berikut ini :
- Menjalankan perintah SQL untuk mengambil, mengubah, menambah dan memindahkan data dari database.
- Bentuk, kalkulasi, simpan & mencetak hasil QUERY dalam bentuk report
- Membuat file script untuk menyimpan perintah SQL sehingga digunakan kembali dimasa datang
SQL *PLUS EDITING COMMANDS
* APPEND TEXT.
* CHANGE / OLD / NEW.
* CHANGE / TEXT /.
* CLEAR BUFFER.
* DEL.
* DEL N.
* DEL M N
* INPUT
* INPUT TEXT
* LIST
* LIST N
* LIST M N
* RUN N
* N TEXT
* 0 TEXT
v SAVE FILENAME
v GET FILENAME
v START FILENAME
v @ FILENAME
v EDIT FILENAME
v SPOOL FILENAME
EXIT
BATASAN dan PENGURUTAN DATA
Membatasi Baris Menggunakan Pilihan
Syntax :
SELECT [ DISTINCT ] { * / column [alias], … }
FROM table
[ WHERE condition(s) ]
WHERE adalah batasan baris yang memenuhi kondisi
condition adalah susunan nama - nama kolom, ekspresi, konstanta dan operator perbandingan
Character String & Date
Syntax:
WHERE expr operator value
Contoh :
… WHERE hiredate = ‘01-JAN-95’
… WHERE sal >= 1500
… WHERE ename = ‘SMITH’
Character
string dan date dalam WHERE clause harus dibatasi dengan tanda kutip
tunggal (‘ ‘). Sedangkan number tidak perlu. Oracle menyimpan tanggal
dalam bentuk numerik, yang menampilkan abad, tahun, bulan, tanggal, jam,
menit, dan detik. Tanggal default dalam bentuk DD-MON-YY.
Operator perbandingan digunakan dalam kondisi yang membandingkan satu ekspresi dengan ekspresi yang lain.
OPERATOR PEMBANDING
OPERATOR BETWEEN
Digunakan untuk membandingkan diantara 2 nilai.
Syntax :
BETWEEN … AND …
Contoh :
SELECT ename,sal FROM emp
WHERE sal BETWEEN 1000 AND 1500;
Anda
dapat menampilkan baris berdasarkan range nilai tertentu menggunakan
operator BETWEEN. Untuk menetapkan range harus disebutkan batas bawah
dan batas atasnya.
OPERATOR IN
Digunakan untuk mencari nilai yang sama dengan nilai yang didalam kurung.
Syntax :
…… IN( List )
Contoh :
SELECT empno,ename,sal,mgr FROM emp
WHERE mgr IN(7902,7566,7788);
Operator
IN dapat digunakan untuk data tipe apa saja. Jika yang didalam kurung (
) bertipe karakter atau date gunakan kutip tunggal di tiap data.
OPERATOR LIKE
Digunakan untuk membandingkan nilai yang sesuai dengan susunan karakter yang ada.
Syntax :
…. LIKE ….
Contoh :
SELECT ename FROM emp
WHERE ename LIKE “S%”;
Anda
dapat memilih baris yang sesuai dengan suatu pola karakter dengan
menggunakan operator LIKE. Dua simbol dapat digunakan untuk membentuk
string pencarian.
OPERATOR NULL
Digunakan membandingkan nilai apakah sama dengan NULL.
Syntax : … IS NULL
Contoh :
SELECT ename,mgr FROM emp
WHERE mgr IS NULL;
Operator NULL menguji nilai apakah null atau tidak.
Nilai null adalah sebuah nilai yang tidak ada, tidak didefinisikan, atau tidak diketahui.
Oleh karena itu, tidak dapat diuji dengan = karena sebuah nilai null tidak dapat disamakan dengan nilai apapun.
Urutan Operator LOGIKA :
1. Semua operator pembanding
2. NOT
3. AND
4. OR
Contoh :
SELECT ename,job FROM emp
WHERE job NOT IN(“CLERK”,”MANAGER”,”ANALYST”);
ORDER BY Clause
Syntax:
SELECT expr FROM table
[ WHERE condition(s)]
[ ORDER BY {column,expr} [ASC/DESC]];
Contoh:
SELECT ename, job, deptno, hiredate
FROM emp
ORDER BY hiredate DESC;
ORDER BY clause dapat digunakan untuk menentukan urutan tampilan dari record yang terpilih.
ASC : urutan baris / record dari kecil ke besar ( sebagai default pengurutan )
FUNGSI BARIS TUNGGAL
SQL FUNCTIONS
Dua macam SQL Functions :
Y Single row functions
-Memanipulasi data item
-Dapat memodifikasi tipe data
-Dapat berbentuk tersarang.
Y Multiple row functions
Syntax :
Function_name (column | ekpresi, [arg1, arg2, …])
Fungsi Karakter
Single-row character functions menerima data karakter sebagai masukan dan dapat menghasilkan nilai karakter dan bilangan.
Fungsi karakter dapat dibagi menjadi :
• Case conversion functions
• Character manipulation functions
Fungsi Mengubah Bentuk Huruf
- LOWER : digunakan untuk mengubah untai karakter huruf menjadi huruf kecil.
- INITCAP : digunakan untuk merubah huruf pertama dari setiap kata menjadi huruf besar dan sisanya huruf kecil.
- UPPER : digunakan untuk mengubah untai karakter huruf menjadi huruf besar.
Fungsi Manipulasi Karakter
- CONCAT : digunakan untuk menggabungkan nilai.
- SUBSTR : digunakan untuk mengambil string yang panjangnya ditentukan.
- LENGTH : digunakan untuk menunjukkan panjang dari string dengan angka
Fungsi Manipulasi Karakter
- INSTR : digunakan untuk menemukan posisi numerik dari karakter yang disebutkan.
- LPAD : digunakan untuk mengisi nilai karakter rata kiri.
- TRIM : digunakan untuk memotong bagian atas atau menarik karakter dari untai karakter.
Fungsi Number
- ROUND : untuk menerima masukkan numerik dan menghasilkan nilai numerik.
- TRUNC : untuk membulatkan kolom, ekspresi atau nilai ke n desimal.
- MOD : untuk menemukan sisa nilai1 dibagi nilai2.
Bekerja Dengan Date
- SYSDATE : fungsi tanggal yang menghasilkan tanggal dan waktu sekarang.
- DUAL : tabel dummy yang digunakan untuk melihat sysdate.
Contoh
SELECT SYSDATE
FROM dual;
Fungsi Date
• MONTHS_BETWEEN(date1, date2)
• ADD_MONTHS (date, n)
• NEXT_DAY (date, ‘char’)
• LAST_DAY (date)
• ROUND (date[,’fmt’])
• TRUNC (date[,’fmt’])
MENAMPILKAN DATA DARI BEBERAPA TABEL
JOIN
Macam - macam join :
- Equijoin
- Non – Equijoin
- Outer Join
- Self Join
Syntax :
SELECT table1.kolom, table2.kolom
FROM table1,table2
WHERE table1.kolom1 = table2.kolom2
EQUIJOIN
Adalah hubungan antara tabel yang meliputi komplemen primary dan foreign key.
Contoh :
SELECT emp.empno, emp.ename, emp.deptno,
dept.deptno,dept.loc
FROM emp,dept
WHERE emp.deptno = dept.deptno
NON - EQUIJOIN
Adalah ketidak terhubungannya antara field-field dari tabel-tabel yang telah dijoin.
Contoh :
SELECT e.ename,e.sal,s.grade
FROM emp.e,salgrade s
WHERE e.sal BETWEEN s.losal AND s.hisal;
OUTER JOIN
Digunakan untuk melihat baris yang biasanya tidak ketemu dari kondisi JOIN.
Operator yang digunakan adalah tanda plus ( + )
Syntax :
SELECT table1.column,table2.column
FROM table1,table2
WHERE table1.column(+) = table2.column;
SELECT table1.column,table2.column
FROM table1,table2
WHERE table1.column = table2.column(+);
SELF JOIN
Digunakan untuk mencari isi dari suatu field
Contoh :
SELECT worker.ename ||‘works for’||manager.ename
FROM emp.worker,emp.manager
WHERE worker.mgr = manager.empno;
Mengumpulkan Data Dengan Menggunakan Fungsi GROUP
Apakah Group Functions?
Fungsi Group beroperasi pada set baris untuk memberikan suatu hasil per group.
EMP
SALARI MAKSIMUM DITABEL EMP
|
|
|
Contoh Group Functions yang Umum Dipakai
• AVG
• COUNT
• MAX
• MIN
• SUM
Contoh Penggunaan AVG dan SUM
Functions :
AVG and SUM hanya untuk numeric data.
SQL> SELECT AVG(sal), MAX(sal),
2 MIN(sal), SUM(sal)
3 FROM emp
4 WHERE job LIKE ‘SALES%’;
|
Contoh MIN dan MAX Functions
|
MIN dan MAX untuk semua tipe data
SQL> SELECT MIN(hiredate), MAX(hiredate)
2 FROM emp;
Contoh COUNT Function
|
SQL> SELECT COUNT(*)
2 FROM emp
3 WHERE deptno = 30;
|
SQL> SELECT COUNT(kolom)
2 FROM emp
3 WHERE deptno = 30;
Contoh NVL Function pada Group Functions
NVL berfungsi memaksa Group untuk menyertakan nilai null
|
SQL> SELECT AVG(NVL(comm,0))
2 FROM emp;
|
Membuat Groups Dengan
Menggunakan GROUP BY
SELECT kolom, group_function(kolom)
FROM tabel
[WHERE condition]
[GROUP BY group_by_expression]
[ORDER BY kolom];
Membagi baris pada tabel ke groups yang lebih kecil dengan fungsi GROUP BY
Using the GROUP BY Clause
|
SQL> SELECT deptno, AVG(sal)
2 FROM emp
3 GROUP BY deptno;
Group lebih dari satu kolom
Tabel EMP
|
|
||||||
|
|||||||
|
Sintaks GROUP Untuk Beberapa Kolom
SQL> SELECT deptno, job, sum(sal)
2 FROM emp
|
KESALAHAN QUERIES
DALAM PENGGUNAAN GROUP FUNCTION
SQL> SELECT deptno, COUNT(ename)
2 FROM emp;
SELECT deptno, COUNT(ename)
*
ERROR at line 1:
ORA-00937: not a single-group group function
Kesalahan terjadi karena dalam perhitungan jumlah ename
“COUNT(ename)” tidak ditentukan berdasarkan field yang mana?.
SQL> SELECT deptno, AVG(sal)
2 FROM emp
3 WHERE AVG(sal) > 2000
4 GROUP BY deptno;
WHERE AVG(sal) > 2000
*
ERROR at line 3
ORA-00934: group function is not allowed here
Klausa WHERE tidak dapat digunakan pada Aggregate Function,
Untuk keperluan tersebut gunakanlah klausa HAVING.
PENGGUNAAN KLAUSA HAVING
|
2 FROM emp
3 WHERE job NOT LIKE ‘SALES%’
4 GROUP BY job
5 HAVING SUM(sal) > 5000
6 ORDER BY SUM(sal);
RINGKASAN
Urutan penulisan klausa dapat dilihat seperti bentuk umum dibawah ini :
SELECT field, fungsi_group(field)
FROM tabel
WHERE kondisi
GROUP BY field
HAVING kondisi_grup
ORDER BY field;
SUBQUERY
Tujuan Pembahasan
Setelah menyelesaikan materi berikut, anda diharapkan dapat :
• Mampu mendeskripsikan permasalahan apa saja yang dapat diselesaikan menggunakan subquery
• Mendefinisikan subquery
• Menggunakan single-row & multiple-row subquery
Pengertian Subquery dan Sintaksnya
Subquery adalah sebuah statement SELECT yang digunakan didalam statement SELECT lainnya
Sintaks :
SELECT select_list
FROM table
WHERE expr_operator
( SELECT select_list FROM table);
Penggunaan Subquery dalam menyelesaikan masalah
Siapakah yang memiliki gaji lebih besar dari gajinya JONES
Main Query
Siapakah yang memiliki gaji lebiih besar dari gaji JONES
|
Berapakah gaji JONES
Penggunaan Subquery
SQL > SELECT ename
FROM emp
WHERE sal >
( SELECT sal
FROM emp
WHERE empno = 7566);
|
Jenis-jenis subquery
• Single-row subquery
• Multiple-row subquery
• Multiple-column subquery
|
||||||||||||||||||
|
|
||||||||||||||||||
Contoh Single-Row Subquery
SQL > SELECT ename, job
|
WHERE job = ( SELECT job
FROM emp
WHERE empno = 7369 )
AND sal > ( SELECT sal
FROM emp
WHERE empno = 7876 ) ;
Menggunakan Group Function pada Subquery
|
FROM emp
WHERE sal = ( SELECT MIN(sal)
FROM emp ) ;
Statement HAVING dalam Subquery
• Oracle Server akan menjalankan subquery nya terlebih dahulu
• Oracle Server akan mengirimkan hasil subquery ke statement HAVING pada Main Query
SQL > SELECT deptno, MIN(sal)
FROM emp
GROUP BY deptno
HAVING MIN(sal) > (SELECT MIN(sal)
FROM emp
WHERE deptno = 20) ;
|
Menggunakan Operator ANY pada Multiple-Row Subquery
SQL > SELECT empno, ename, job
FROM emp
WHERE sal < ANY ( SELECT sal
FROM emp
WHERE job = ‘CLERK’ )
AND job < > ‘CLERK’ ) ;
|
Menggunakan Operator ALL pada Multiple-Row Subquery
SQL > SELECT empno, ename, job
FROM emp
WHERE sal > ALL ( SELECT avg(sal)
FROM emp
GROUP BY deptno) ;
|
Kesimpulan :
Subquery sangat berguna apabila sebuh query tidak diketahui nilainya
SELECT select_list
FROM table
WHERE expr_operator
( SELECT select_list
FROM table);
Menentukan Batasan Data Melalui Input Pemakai dengan SQL* PLUS
Tujuan Pembahasan
Setelah menyelesaikan materi berikut, anda diharapkan dapat :
• Menghasilkan queri yang memerlukan sebuah variabel input
• Mengubah lingkungan SQL*Plus
• Membuat dan menjalankan file script / program
• Menyimpan hasil perubahan
Variabel Pengganti
• Menggunakan variabel pengganti pada variabel substitusi untuk menyimpan suatu nilai sementara
v Single Ampersand (&)
v Double Ampersand (&&)
v Statement DEFINE & ACCEPT
• Memberikan nilai dari satu perintah SQL ke lainnya
• Mengubah Header dan Footer secara dinamik
Menggunakan (&) Variabel Pengganti
Menggunakan variabel yang didahului ampersand (&) untuk melakukan input yang dilakukan oleh user
SQL > SELECT empno, ename, sal, deptno
FROM emp
WHERE empno = employee_num ;
|
Tipe Data
Karakter dan Date pada Variabel Pengganti
Gunakan tanda petik satu (‘ ‘) untuk tanggal dan karakter
|
FROM emp
WHERE job = ‘job_title’ ;
Spesifikasi Nama Kolom, Ekspresi & Text pada saat Runtime
SQL > SELECT empno, ename, job, &column_name
FROM emp
WHERE &condition
ORDER BY &order_column ;
|
Menggunakan (&&) Variabel Pengganti
Gunakan double-ampersand (&&) jika ingin menggunakan kembali nilai variabel tanpa harus menginput ulang
SQL > SELECT empno, ename, job, &&column_name
FROM emp
ORDER BY &column_name ;
|
Mendefinisikan Variabel Pemakai
• Untuk mendefinisikan variabel dapat digunakan salah satu dari kedua statement input pada SQL*PLUS dibawah ini :
v DEFINE : membuat variabel bertipe CHAR
v ACCEPT : membaca inputan user dan menyimpannya dalam variabel
• Jika menggunakan statement DEFINE dan menginput nilai variabel terdapat spasi, maka harus digunakan tanda petik satu
Statement ACCEPT
• Mengatur sendiri kalimat yang akan ditampilkan jika ada bentuk masukan berupa input
• Mendafinisikan secara eksplisit tipe data variabel untuk NUMBER atau DATE
• Menyembunyikan masukkan pemakai untuk alasan keamanan
ACCEPT variable [datatype] [FORMAT format]
[PROMPT text] [HIDE]
Menggunakan Statement ACCEPT
ACCEPT dept PROMPT ‘Provide the Department Name :
|
FROM dept
WHERE dname = UPPER (&dept)
/
Perintah DEFINE dan UNDEFINE
• Variabel akan terdefinisi sampai Anda :
v Menggunakan perintah UNDEFINE pada sebuah variabel
v Keluar dari SQL*PLUS
• Saat Anda mengubah variabel menjadi tidak terdefinisi, Anda dapat memastikan perubahan dengan perintah DEFINE
Untuk mendefinisikan
variabel-variabel itu untuk tiap sesi, ubahlah file login.sql agar
variabel-variabel itu dibuat pada saat permulaan
Menggunakan Statement DEFINE
Membuat variabel untuk mendefinisikan nama departemen :
SQL > DEFINE deptname = sales
SQL > DEFINE deptname
SQL > DEFINE deptname = sales
Gunakan variabel seperti yang Anda inginkan :
|
Mengubah LingkunganSQL*PLUS
Menggunakan statement SET untuk mengontrol session yang sedang aktif
SET system_variable value
Pastikan apa yang telah Anda atur (set) dengan menggunakan perintah SHOW
SQL > SET ECHO ON
|
Variabel Perintah SET
• ARRAYSIZE {20 | n}
• COLSEP {_ | text}
• FEEDBACK {6 | n | OFF | ON}
• HEADING {OFF | ON}
• LINESIZE {80 | n}
• LONG {80 | n}
• PAGESIZE {24 | n}
• PAUSE {OFF | ON | text}
• TERMOUT {OFF | ON}
Menyimpan Perubahan dalam File login.sql
• File Login.sql berisi standar SET dan perintah SQL*Plus lain yang Anda butuhkan untuk setiap sesi
• Anda dapat mengubah login.sql agar berisi perintah SET tambahan
Format Statement SQL*PLUS
• COLUMN [column option]
• TTITLE [text | OFF | ON]
• BTITLE [text | OFF | ON]
• BREAK [ON report_element]
Menggunakan Statement Column
Membuat judul(heading) kolom
COLUMN ename HEADING ‘Employee|Name’ FORMAT A15
COLUMN sal JUSTIFY LEFT FORMAT $99,990.00
COLUMN mgr FORMAT 999999999 NULL ‘No manager’
Menampilkan setting untuk kolom ENAME
COLUMN ename
Menghapus setting untuk kolom ENAME
COLUMN ename CLEAR
Model Format Kolom
Elemen
|
Penjelasan
|
Contoh
|
Hasil
|
An
|
Set lebar dari n
|
N/A
|
N/A
|
9
|
Single Zero-suppression digit
|
999999
|
1234
|
0
|
Enforces leading zero
|
099999
|
01234
|
$
|
Floating tanda $
|
$9999
|
$1234
|
L
|
Mata Uang Local
|
L9999
|
L1234
|
.
|
Desimal
|
9999.99
|
1234.00
|
,
|
Ribuan
|
9,999
|
1,234
|
Menggunakan Statement BREAK
Memperkecil duplikasi dan baris sesi
Menghapus nilai yang ganda
SQL > BREAK ON ename ON job
Memecah baris per sesi sesuai nilai BREAK
SQL > BREAK ON ename SKIP 4 ON job SKIP 2
Menggunakan Statement TTITLE dan BTITLE
• Menampilkan header dan footer
TTI[TLE] [text | OFF | ON]
• Mengatur header dari report
SQL > TTITLE ‘Salary | Report’
• Mengatur footer dari report
SQL > BTITLE ‘Confidential’
Membuat Script untuk Menjalankan Report
- Membuat statement SELECT SQL
- Menyimpan statement SELECT dalam sebuah file script
- Memanggil file script kedalam editor
- Menambah perintah format sebelum perintah SELECT
- Verifikasi bahwa karakter pengeksekusi mengikuti perintah SELECT
- Menghapus perintah format setelah perintah SELECT
- Menyimpan file script
- “START nama_file” untuk menjalankan scriptnya
SQL> set pagesize 37
SQL> set linesize 60
SQL> set feedback off
SQL> ttitle ‘EMPLOYEE|REPORT’
SQL> btitle ‘CONFIDENTIAL’
SQL> break on job
SQL> column job heading ‘JOB|CATEGORY’ format A15
SQL> column ename heading ‘EMPLOYEE’ format A15
SQL> column sal heading ‘SALARY’ format $99,999.99
SQL> rem**insert selet statement
SQL> select job,ename,sal
2 from emp
3 where sal<3000
4 order by job,ename;
 |
MEMANIPULASI DATA
Tujuan
Pada akhir bab ini, anda diharapkan dapat melakukan hal berikut :
• Mendeskripsikan setiap statemen DML
• Menyisipkan baris ke dalam tabel
• Meng-update baris di tabel
• Menghapus baris dari sebuah tabel
• Mengendalikan transaksi
MENAMBAHKAN BARIS BARU KE DALAM TABEL
Dengan memberikan perintah INSERT
Sintaks :
INSERT INTO table [( coloumn [,coloumn…] ) ]
VALUES (VALUE [ ,VALUE….]);
Dengan :
Table : nama tabel
Coloumn : nama kolom pada tabel yang akan dimasukkan nilainya
Value : nilai yang diberikan untuk kolom yang bersangkutan
MENAMBAHKAN BARIS BARU
• Nilai yang dimasukkan harus sesuai dengan urutan kolom yang
ada pada tabel.
• Bersifat optional untuk menuliskan nama-nama kolom pada
klausa INSERT.
SQL > INSERT INTO dept (deptno,dname, loc)
2 VALUE ( 50,’DEVELOPMENT’, ‘DETROIT’);
1 row created.
• Berikan kutip satu pada karakter dan nilai tanggal.
BAHASA PEMANIPULASIAN DATA
Perintah DML dieksekusi ketika anda :
• Menambah baris baru ke tabel
• Memodifikasi baris yang telah ada
• Menghapus baris yang telah ada dari sebuah tabel
Transaksi adalah sekumpulan perintah DML yang membentuk sebuah unit kerja logik
MENAMBAHKAN BARIS DENGAN NILAI NULL
Metode Implisit :
Dengan menyertakan kolom yang dimaksud pada kolom yang akan dimasukkan.
|
Metode Eksplisit :
• Mencantumkan keyword Null pada klausa VALUES
• Mencantumkan string kosong ( ‘ ‘) pada klausa
VALUES untuk tiap data , karakter dan Date saja.
|
MEMASUKKAN NILAI KHUSUS
Fungsi SYSDATE mencatat tanggal dan waktu sekarang.
|
MEMASUKKAN NILAI TANGGAL SPESIFIK
Tambahkan employee baru
|
MEMASUKKAN NILAI MENGGUNAKAN VARIABEL SUBTITUSI
Membuat perintah insert yang memperbolehkan user menambahkan nilai secara interaktif menggunakan variabel subtitusi SQL * PLUS
SQL > INSERT INTO dept (deptno, dname, Loc)
2 VALUES (&department_id, ‘&department_name’,
3 ‘&location’);
Enter value for department_id : 80
Enter value for department_name : education
Enter value for location : ATLANTA
1 row created.
MEMBUAT SCRIPT DENGAN MENGATUR PROMPT
ACCEPT menyimpan nilai didalam variabel
• PROMPT menampilkan text yang ingin ditampilkan
ACCEPT department_id PROMPT ‘Please enter the department number:’
ACCEPT department_name PROMPT ‘Please enter the department name :’
ACCEPT location PROMPT ‘Please enter the location :’
INSERT INTO dept (deptno, dname, loc)
VALUES &department_id,’&department_name’,’&location’);
MENYALIN BARIS DARI TABEL LAIN
• Perintah INSERT dapat digunakan untuk menambahkan baris kedalam tabel dengan nilai yang diperoleh dari tabel yang ada.
Sintaks :
INSERT INTO table [coloumn(,coloumn)]
Subquery ;
SQL > INSERT INTO managers (id, name, salary, hiredate)
2 SELECT empno, ename, sal, hiredate from emp
3 WHERE Job = ‘ MANAGER’;
3 row created
Jangan menggunakan klausa VALUES.
• Jumlah dan tipe data kolom pada klausa INSERT harus sesuai dengan jumlah dan tipe data nilai yang ada di subquery.
PERINTAH UPDATE
Anda dapat memodifikasi baris yang ada menggunakan perintah UPDATE .
Sintaks :
UPDATE table
SET column = value [ value, column = value, ….]
[where condition];
Dengan :
Value = nilai atau hasil subquery untuk kolom yang dimaksud.
Condition = Mengidentifikasi baris tertentu yang akan di update.
MENGUPDATE BARIS DI TABEL
• Sebuah baris / beberapa baris spesifik dimodifikasi ketika anda menspesifikasikan klausa WHERE.
SQL > UPDATE emp
2 SET deptno = 20
3 WHERE empno = 7782;
1 row updated.
• Semua baris akan dimodifikasi jika anda tidak memakai WHERE
SQL > UPDATE emp
2 SET deptno = 20
14 row updated.
MENGUPDATE DENGAN MULTIPLE-COLUMN SUBQUERY
Sintaks :
UPDATE table
SET (column, column…..) =
(SELECT column, column,…..
FROM table
WHERE condition)
WHERE condition;
UPDATE emp
SET (job,deptno) =
(SELECT job,deptno FROM emp
WHERE empno = 7499)
WHERE empno = 7698;
1 row created
MEMODIFIKASI BARIS BERDASARKAN TABEL LAIN
Gunakan subquery pada perintah UPDATE untuk memodifikasi baris disuatu tabel.
SQL > UPDATE employee
2 SET deptno = (SELECT deptno From emp
3 WHERE empno = 7788)
4 WHERE job = (SELECT job FROM emp
5 WHERE empno = 7934);
2 row updated.
INTEGRITY CONSTRAINT ERROR
SQL > UPDATE emp
2 SET deptno = 55
3 WHERE DEPTNO = 10;
UPDATE emp
*
ERROR at line 1 :
ORA-02291 : integrity constraint (USR.EMP_DEPTNO_FK)
Violated – parent key not found
PERINTAH DELETE
Anda dapat menghapus baris yang telah ada dari tabel dengan menggunakan perintah DELETE.
DELETE [From] table
[WHERE condition] ;
MENGAPUS BARIS DARI TABEL
• Baris spesifik akan dihapus jika anda menspesifikasikan klausa WHERE.
SQL > DELETE FROM department
2 WHERE dname = ‘DEVELOPOMENT’ ;
1 row deleted.
• Semua baris ditabel akan dihapus jika anda tidak menggunakan klausa WHERE.
SQL > DELETE FROM department;
4 row deleted.
MENGHAPUS BARIS BERDASARKAN TABEL LAIN
Gunakan subquery pada perintah DELETE untuk menghapus dari tabel berdasarkan pada nilai tabel lain.
SQL > DELETE FROM employee
2 WHERE deptno =
3 (SELECT depno FROM dept
4 WHERE dname = ‘SALES’);
6 row deleted.
INTEGRITY CONSTRAINT ERROR
SQL > DELETE FROM emp
2 WHERE deptno = 10;
DELETE FROM dept
*
ERROR at line 1 :
ORA-02291 : integrity constraint (USR.EMP_DEPTNO_FK)
Violated – child key found
TRANSAKSI DATABASE
Terdiri dari salah satu perintah-perintah berikut ini :
• Perintah – perintah DML membuat suatu perubahan konsisten terhadap data.
• Satu perintah DDL.
• Satu perintah DCL.
TRANSAKSI DATABASE
• Dimulai saat perintah eksekusi pertama SQL di eksekusi
• Diakhiri dengan salah satu dari hal berikut ini terjadi :
- Dijalankan sebuah perintah COMMIT atau ROLLBACK
- Dijalankan sebuah perintah DDL atau DCL.
- User keluar.
- Sistem mengalami crash.
KEUNTUNGAN PERINTAH COMMIT DAN ROLLBACK
• Menjamin konsistensi data.
• Data sebelumnya dapat dirubah sebelum dibuat secara permanen.
• GROP logically related opertiions.
MENGKONTROL TRANSAKSI
PROSES TRANSAKSI SECARA IMPLISIT
AUTOMATIC COMMIT =
- Peritah DDL atauDCL dkeluarkan
- Keluar dari SQL * PLUS tanpa mengeluarkan perintah COMMIT atau ROLLBACK
AUTOMATIC ROLLBACK =
- Mengakhiri SQL * PLUS secara abnormal atau system failure
KEADAAN DATA SEBELUM DI COMMIT ATAU DI ROLLBACK
Data dapat dikembalikan ke keadaan sebelumnya.
• User yang bersangkutan dapat melihat hasil operasi pemanipulasian data dengan menggunakan perintah SELECT.
• User lain tidak dapat melihat hasil operasi pemanipulasian data.
• Baris-baris yang dipergunakan dikunci, user lain tidak dapat mengubah data pada baris yang bersangkutan.
KEADAAN DATA SETELAH DI COMMIT
• Perubahan data dibuatkan permanen kedalam database.
• Keadaan data sebelunya secara otomatis hilang.
• Semua user dapat melihat hasil transaksi.
• Lock pada baris-baris yang terkena perubahan dilepas, dan kini dapat diakases oleh user lain.
• Semua Savepoint dihapus.
MEN-COMMITKAN DATA
• Buat perubahan
SQL > UPDATE emp
2 SET deptno =10
3 WHERE empno = 7782;
1 row updated.
• Men-COMMIT perubahan
SQL > COMMIT;
commit complete.
KEADAAN DATA SETELAH DI ROLLBACK
Semua Perubahan data dapat dibatalkan menggunakan perintah ROLLBACK.
• Perubahan data dibatalkan.
• Data dikembalikan ke keadaan semula.
• Lock pada baris-baris yang terkena perubahan dilepas
ROLLBACK PERUBAHAN
• Buatlah tanda pada transaksi saat ini dengan menggunakan perintah SAVEPOINT.
• ROLLBACK pada tanda tersebut dengan menggunakan perintah ROLLBACK TO SAVEPOINT.
SQL > UPDATE…
SQL>SAVEPOINT update _done;
savepoint created.
SQL> INSERT…
SQL > ROLLBACK TO update_done;
ROLLBACK complete.
ROLLBACK LEVEL PERINTAH
• Jika perintah DML mengalami kesalahan pada saat eksekusi pengaruhnya dibatalkan oleh rollback level perintah. Oracle memberikan COMMIT secara implisit sebelum dan sesudah perintah DDL.
• Semua perubahan dapat dikembalikan.
• Meskipun perintah DDL tidak berhasil dijalankan, anda tidak dapat melakukan rollback perintah perintahsebelumnya karena server telah melakukan commit.
READ CONSISTENCY
• Read consistency menjamin user melihat seluruh data secara konsistenPerubahan yang dilakukan oleh satu user tidak akan konflik dengan perubahan yang dilakukan oleh user lain.
• Read consistency memastikan pada data yang sama :
• Readers tidak melihat data yang sedang diubah
• writer yakin bahwa perubahan database dilakukan secara konsisten.
LOCKING
• Mekanisme yang menghindari interaksi destruktif antara transaksi-transaksi yang mengakses sumber daya yang sama.
• Tidak dapat dilihat oleh user
• Tidak memerlukan aksi dari user
• Locking pada oracle dilakukan secara otomatis
MEMBUAT DAN MENGELOLA TABEL
Data Obyek
Sebuah database oracle dapat terdiri dari beberapa struktur data.
Tabel : menyimpan data.
View : subset data dari satu atau lebih tabel.
Sequence : menghasilkan nilai primary key.
Index : meningkatkan kinerja query.
Synonym : memberikan alternatif nama obyek database.
Struktur Tabel Oracle
Aturan Penamaan
• Nama tabel dan kolom harus dimulai dengan huruf dan panjangnya dapat mencapai 30 karakter.
• Nama hanya terdiri dari karakter A-Z , a-z, 0-9, _ , $ dan #
• Nama suatu tabel tidak boleh sama dengan nama obyek database lainnya yang berada disatu oracle server.
Nama tidak boleh sama dengan reserved word
Statement Create Table
Dengan perintah SQL CREATE TABLE. Perintah ini termasuk dalam DDL.
Sintaks :
CREATE [ GLOBAL TEMPORARY ] TABLE [schema] table (column datatype [DEFAULT expr] , [.....]);
Dengan :
GLOBAL TEMPORARY : menspesifikasikan bahwa tabel bersifat sementara dan definisinya dapat dipakai oleh semua session. Data yang terdapat pada tabel sementara (temporary) hanya dapat digunakan pada session yang memasukkan data tersebut ke dalam tabel.
Schema : sama dengan nama pembuat tabel.
Table : nama tabel.
DEFAULT expr : menspesifikasikan nilai default jika nilainya dihilangkan dari statement insert.
Column : nama kolom.
Datatype : tipe dan panjangnya data kolom.
Option Default
• Sebuah kolom boleh diberikan nilai default menggunakan option DEFAULT.
• Nilai default dapat berupa literal, ekspresi atau fungsi SQL.
• Nilai default tidak boleh berupa nama kolom lain ataupun pseudocolumn.
• Ekspresi default harus sesuai dengan tipe data kolom tersebut.
Pembuatan Tabel
• Membuat tabel
SQL> CREATE TABLE dept
2 (deptno NUMBER(2),
3 dname VARCHAR2 (14),
4 loc VARCHAR2 (13) ) ;
Table created.
• Menampilkan struktur tabel
SQL> DESCRIBE dept ;
Kamus Data Query
(Querying Data Dictionary)
(Querying Data Dictionary)
• Ada sekumpulan tabel dan view lain pada database oracle yang dikenal sebagai data dictionary.
• Data dictionary dibuat dan dipelihara oleh server oracle dan berisi informasi database.
• Ada 4 kategori view data dictionary, setiap kategori memiliki prefix tersendiri yang menunjukkan kegunaannya :
Kategori View Data Dictionary
Prefix
|
Keterangan
|
USER_
|
View berisi informasi mengenai obyek-obyek yang dimiliki user
|
ALL
|
View berisi informasi mengenai semua table (object table dan relational table) yang dapat diakses oleh user
|
DBA_
|
View ini merupakan view terbatas, yang hanya dapat diakses oleh DBA
|
V$_
|
View ini berisi informasi mengenai kinerja dinamis, kinerja database server dan locking
|
Tipe Data
|
Deskripsi
|
VARCHAR2(size)
|
Data karakter variable length (ukuran maksimum harus disebutkan). Ukuran minimum dan defaultnya adalah 1, maksimum 4000.
|
CHAR(size)
|
Data karakter fixed length dengan ukuran bytes (ukuran minimum dan defaultnya 1, maksimum 2000).
|
NUMBER(p,s)
|
Number
memiliki presisi p dan skala s (presisi adalah jumlah keseluruhan
digit desimal, skala adalah jumlah digit poin desimal. Presisi
berkisar dari 1 sampai 38 dan skala berkisar dari -84 sampai 127).
|
Membuat Tabel Dari
Baris-Baris Di Tabel Lain
Sintaks : CREATE TABLE table
[ ( column, column,....) ]
As subquery;
Dengan :
column :nama kolom, nilai default dan integrity constraint.
subquery :statement SELECT yang mendefinisikan sekumpulan baris yang dimasukkan ke dalam tabel yang dibuat.
Contoh :
SQL> CREATE TABLE dept30
• As
• SELECT empno, ename, sal*12 ANNSAL, hiredate
• FROM emp
• WHERE deptno = 30 ;
Table created
Statement ALTER TABLE
Statement Alter table digunakan untuk :
• Menambah sebuah kolom baru
• Memodifikasi kolom yang sudah ada
Sintaks : ALTER TABLE table
ADD (column datatype [DEFAULT expr]
[ , column datatype] …..);
ALTER TABLE table
MODIFY (column datatype [DEFAULT expr]
[ , column datatype] …..);
Dengan :
column : nama kolom baru
datatype : tipe data dan panjang kolom baru
DEFAULT expr : menspesifikasikan nilai default untuk kolom baru
Menambahkan Kolom
• Anda dapat menggunakan add clause untuk menambahkan kolom.
.
SQL>ALTER TABLE dept30
2 ADD ( job VARCHAR2(9)) ;
Table altered
• Kolom yang baru kita buat akan menjadi kolom terakhir.
EMPNO ENAME ANNSAL HIREDATE JOB
—————- ————- ————- ————— ——–
7698 BLAKE 34200 01-MAY-81
7654 MARTIN 15000 28-SEP- 81
7499 ALLEN 19200 20-FEB- 81
………
6 rows selected
Mengubah Kolom
• Anda dapat Mengubah typedata kolom,ukuran dan nilai awal.
SQL> ALTER TABLE dept30
2 MODIFY ( ename VARCHAR2(15));
Table altered.
• Perubahan pada nilai default hanya mempengaruhi sisipan yang berikutnya.
Menghapus Kolom
• Menggunakan statement ALTER TABLE dengan klausa DROP COLUMN
SQL>ALTER TABLE dept30
2 DROP COLUMN job;
Table altered.
Petunjuk :
• Kolom mungkin kosong atau berisi data
• Kolom hanya dapat dihapus satu persatu
• Tabel harus memiliki paling tidak satu kolom setelah dilakukan proses ALTER
• Setelah kolom dihapus, tidak dapat dikembalikan seperti semula
Option Set Unused
• Option
SET UNUSED memberikan tanda pada satu atau lebih kolom bahwa
kolom-kolom tersebut dapat dihapus saat tidak dibutuhkan lagi.
Sintaks :
ALTER TABLE table
SET UNUSED (column) ;
OR
ALTER TABLE table
SET UNUSED COLUMN column ;
Option Drop
Unused Columns
• Statement DROP UNUSED COLUMNS menghapus semua kolom yang telah ditandai sebagai unused.
Sintaks :
ALTER TABLE table
DROP UNUSED COLUMNS ;
SQL> ALTER TABLE dept30
• SET UNSED (ename) ;
Table altered
SQL> ALTER TABLE dept30
• DROP UNSED COLUMN ;
Table altered
Menghapus Tabel
Statement DROP TABLE menghapus tabel beserta definisinya
Sintaks :
DROP TABLE table ;
Mengubah Nama Tabel
Perintah DDL RENAME digunakan untuk mengganti nama table, view, sequence atau synonym.
Sintaks :
RENAME old_name TO new_name
Truncate Table
Untuk menghapus semua baris dari suatu tabel dan membebaskan storage yang digunakan tabel tersebut.
Sintaks :
TRUNCATE TABLE table ;
Menambahkan Komentar Tabel
• Menambahkan komentar ke sebuah tabel atau kolom menggunakan statement COMMENT.
Sintaks :
COMMENT ON TABLE table | COLUMN table.column IS ‘text’
Dengan :
table : nama tabel
column : nama kolom
text : teks komentar
• Komentar disimpan dalam data dictionary dan dapat dilihat pada salah satu dari view berikut :
- ALL_COL_COMMENTS
- USER_COL_COMMENTS
- ALL_TAB_COMMENTS
- USER_TAB_COMMENTS
Apakah Constraint itu ?
? Menjamin pelaksanaan aturan pada level tabel.
? Mencegah penghapusan tabel jika terdapat ketergantungan dari tabel lain.
? Di bawah ini adalah tipe constraint yang berlaku di oracle :
ĂĽ NOT NULL ĂĽ FOREIGN KEY
ĂĽ UNIQUE ĂĽ CHECK
ĂĽ PRIMARY KEY
Panduan Constraint
? Semua constraint disimpan dalam data dictionary.
? Jika tidak diberi nama, oracle akam membuat dengan format SYS_Cn.
? Constraint didefinisikan saat membuat tabel atau setelah tabel terbentuk.
Mendefinisikan Constraint
Sintaks :
CREATE TABLE [schema.]table
(column datatype [DEFAULT expr]
[column_constraint],
…
[table_constraint][,...]);
Level Kolom :
column [CONSTRAINT constraint_name] constraint_type,
Level Tabel :
column,…
[CONSTRAINT constraint_name] constraint_type
(column, …),
Constraint NOT NULL
@ Memastikan bahwa nilai null tidak diperbolehkan.
@ Hanya dapat dispesifikasikan pada level kolom.
@ Contoh :
SQL> CREATE TABLE emp(
2 empno NUMBER(4),
3 ename VARCHAR2(10) NOT NULL,
4 job VARCHAR2(9),
5 mgr NUMBER(4),
6 hiredate DATE,
7 sal NUMBER(7,2),
8 comm NUMBER(7,2),
9 deptno NUMBER(7,2) NOT NULL);
Constraint UNIQUE KEY
@ Mengharuskan nilai pada sebuah atau sekelompok kolom bernilai unik.
@ Contoh :
SQL> CREATE TABLE dept(
2 deptno NUMBER(2),
3 dname VARCHAR2(14),
4 loc VARCHAR2(13),
5 CONSTRAINT dept_dname_uk UNIQUE(dname));
Constraint PRIMARY KEY
@ Hanya ada satu primary key yang dibuat untuk setiap tabel.
@ Mengidentifikasikan setiap baris dalam tabel secara unik.
@Contoh :
SQL> CREATE TABLE dept(
2 deptno NUMBER(2),
3 dname VARCHAR2(14),
4 loc VARCHAR2(13),
5 CONSTRAINT dept_dname_uk UNIQUE (dname),
6 CONSTRAINT dept_deptno_pk PRIMARY KEY(deptno));
Constraint FOREIGN KEY
@ Membentuk sebuah relasi dengan primary key pada tabel yang sama atau pada tabel yang berbeda.
@ Dapat didefinisikan pada level kolom atau tabel.
@ Contoh :
SQL> CREATE TABLE emp(
2 empno NUMBER(4),
3 ename VARCHAR2(10) NOT NULL,
4 job VARCHAR2(9),
5 mgr NUMBER(4),
6 hiredate DATE,
7 sal NUMBER(7,2),
8 comm NUMBER(7,2),
9 deptno NUMBER(7,2) NOT NULL,
10 CONSTRAINT emp_deptno_fk FOREIGN KEY (deptno)
11 REFERENCES dept (deptno));
KEYWORDS FOREIGN KEY
@FOREIGN KEY digunakan untuk mendefinisikan kolom pada child table pada constraint level tabel.
@REFERENCES mengidentifikasikan tabel dan kolom di parent table.
@ ON DELETE CASCADE mengindikasikan bahwa saat baris di parent table dihapus, baris yang berhubungan denganya di child tabel juga akan terhapus.
Constraint CHECK
@Mendefinisikan kondisi yang harus dipenuhi setiap baris.
@ Beberapa kondisi yang tidak diperbolehkan :
§ Bereferensi ke pseudocolumn CURRVAL, NEXTVAL, LEVEL, & ROWNUM.
§ Memanggil fungsi SYSDATE, UID, USER, & USERNV.
§ Query yang mengambil nilai dari baris lain.
@Tidak ada batas jumlah constraint check yang dapat didefinisikan terhadap sebuah kolom.
@ Constraint CHECK dapat didefinisikan pada level kolom atau tabel.
@ Contoh :
…, deptno NUMBER(2),
CONSTRAINT emp_deptno_ck
CHECK (DEPTNO BETWEEN 10 AND 99),…
Menambahkan Constraint
@ Menggunakan statement ALTER TABLE dengan kalusa ADD.
@ Untuk menambahkan constraint NOT NULL gunakan klausa MODIFY.
@ Constraint tidak dapat diganti/dimodify strukturnya, untuk mengganti harus dihapus terlebih dahulu.
@Dapat menambah, mengahapus, menonaktifkan maupun mengaktifkan constraint.
@ Sintaks :
ALTER TABLE table
ADD [CONSTRAINT constraint] type (column);
@ Contoh :
SQL> ALTER TABLE emp
2 ADD CONSTRAINT emp_mgr_fk
3 FOREIGN KEY(mgr) REFERENCES emp(empno);
Table altered.
Menghapus Constraint
@ Sintaks :
ALTER TABLE table
DROP PRIMARY KEY / UNIQUE(column) / CONSTRAINT constraint [CASCADE]
@ Klausa CASCADE menyebabkan semua constraint yang berhubungan akan terhapus.
@ Contoh :
SQL> ALTER TABLE dept
2 DROP PRIMARY KEY CASCADE;
Table altered.
SQL> ALTER TABLE emp
2 DROP CONSTRAINT emp_mgr_fk;
Table altered.
Menonaktifkan Constraint
@ Klausa DISABLE dapat digunakan pada statement CREATE TABLE atau ALTER TABLE
@ Klausa CASCADE menonaktifkan integrity constraint yang berhubungan.
@ Contoh :
SQL> ALTER TABLE emp
2 DISABLE CONSTRAINT emp_empno_pk CASCADE;
Table altered.
Mengaktifkan Constraint
@ Indeks UNIQUE atau PRIMARY KEY akan terbuat secara otomatis, jika mengaktifkan constraint UNIQUE atau PRIMARY KEY.
@ Klausa ENABLE dapat digunakan pada statement CREATE TABLE atau ALTER TABLE
@ Contoh :
SQL> ALTER TABLE emp
2 ENABLE CONSTRAINT emp_empno_pk;
Table altered.
Melihat Constraint
@ Lakukan query terhadap tabel USER_CONSTRAINTS
SQL> SELECT constraint_name, constraint_type,
2 search_condition
3 FROM user_constraints
4 WHERE table_name = ‘EMP’;
CONSTRAINT_NAME C SEARCH_CONDITION
———————————- -- ————————-
SYS_C00674 C EMPNO IS NOT NULL
SYS_C00675 C DEPTNO IS NOT NULL
EMP_EMPNO_PK P
Melihat Kolom-kolom yang terhubung dengan Constraint
Lakukan query terhadap view USER_CONS_COLUMN
SQL> SELECT constraint_name, column_name
2 FROM user_cons_columns
3 WHERE table_name = ‘EMP’;
CONSTRAINT_NAME COLUMN_NAME
———————————— ————— ————————————
EMP_DEPTNO_FK DEPTNO
EMP_EMPNO_PK EMPNO
EMP_MGR_FK MGR
SYS_C00674 EMPNO
SYS_C00675 DEPTNO
Membuat View
Tujuan :
Setelah mempelajari bab ini, diharapkan anda dapat :
- menjelaskan apa itu view
- membuat view
- merubah view
- membuat Complex View
- menghapus view
Pengertian :
View
adalah tabel logika yang berdasarkan pada sebuah tabel atau lebih.
Tabel yang ada pada view dinamakan “tabel asal”, view disimpan seperti
perintah select pada data dictionary
Syntax :
CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view [(alias[, alias]…)]AS subquery
[WITH CHECK OPTION [CONSTRAINT constraint]]
[WITH READ ONLY];
Contoh :
SQL> CREATE VIEW empvu10
2 AS SELECT empno, ename, job
3 FROM emp
4 WHERE deptno = 10;
View created.
Merubah View
Melihat struktur view yang telah dibuat :
SQL> DESCRIBE empvu10
merubah view yang telah kita buat :
SQL> CREATE OR REPLACE VIEW empvu10
2 (employee_number, employee_name, job_title)
3 AS SELECT empno, ename, job
4 FROM emp
5 WHERE deptno = 10;
View created.
Membuat Complex View
Contoh :
SQL> CREATE VIEW dept_sum_vu
2 (name, minsal, maxsal,avgsal)
3 AS SELECT d.dname,MIN(e.sal), MAX(e.sal),
4 AVG(e.sal)
5 FROM emp e, dept d
6 WHERE e.deptno = d.deptno
7 GROUP BY d.dname;
View created.
Menghapus View
Syntax :
DROP VIEW view;
Contoh :
SQL> DROP VIEW empvu10;
View dropped.
Apakah Sequence itu ?
?Menghasilkan nilai yang unik.
?Penggunaannya seperti membuat nilai Primary Key.
?Mengubah kode aplikasi.
? Mempercepat efisiensi mengakses nilai sequence ketika disembunyikan dalam memory
Membuat Sequence
? Sintaks :
CREATE SEQUENCE sequence
[INCREMENT BY n]
[START WITH n]
[{MAXVALUE n | NOMAXVALUE}]
[{MINVALUE n | NOMINVALUE}]
[{CYCLE | NOCYCLE}]
[{CACHE n | NOCACHE}];
? Contoh :
SQL> CREATE SEQUENCE dept_deptno
2 INCREMENT BY 1
3 START WITH 91
4 MAXVALUE 100
5 NOCACHE
6 NOCYCLE;
Sequence created.
Melihat Nilai Sequence
@ Nilai-nilai sequence disimpan dalam tabel data dictionary USER_SEQUENCE.
SQL> SELECT sequence_name, min_value, max_value,
2 increment_by, last_number
3 FROM user_sequences;
@ Kolom LAST_NUMBER menampilkan nomor sequence yang digunakan selanjutnya.
NEXTVAL dan CURRVAL Peudocolumns
@ NEXTVAL mengembalikan kegunaan nilai sequence selanjutnya.
§ Mengembalikan nilai unik saat diperintah oleh user lain.
@ CURRVAL memperoleh nilai-nilai sequence yang ada saat itu.
§ NEXTVAL harus terbentuk terlebih dahulu sebelum CURRVAL berisi nilai.
Aturan Penggunaan NEXTVAL dan CURRVAL
@ Boleh digunakan dalam :
§ SELECT list pada statement SELECT yang bukan bagian dari subqueri.
§ SELECT list pada subqueri dalam statement INSERT.
§ Klausa VALUES pada statement INSERT.
§ Klausa SET pada statement UPDATE.
@ Tidak Boleh digunakan dalam :
§ SELECT list pada view.
§ Statement SELECT dengan klausa DISTINCT.
§ Statement SELECT dengan klausa GROUP BY, HAVING, atau ORDER BY.
§ Subqueri dalam statement SELECT, DELETE atau UPDATE.
§ DEFAULT ekps dalam perintah CREATE TABLE atau ALTER TABLE.
Penggunaan Nextval & Currval dalam Sequence
@ Tambahkan departemen baru dengan nama departement ‘MARKETING’ DI San Diego.
SQL> INSERT INTO dept(deptno, dname, loc)
2 VALUES (dept_deptno.NEXTVAL,
3 ‘MARKETING’, ‘SAN DIEGO’);
1 row created.
@ Tampilkan data yang ada saat ini dari sequence DEPT_DEPTNO.
SQL> SELECT dept_deptno.CURRVAL
2 FROM dual;
Memodifikasi Sequence
Contoh :
SQL> ALTER SEQUENCE dept_deptno
2 INCREMENT BY 1
3 MAXVALUE 999999
4 NOCACHE
5 NOCYCLE;
Sequence altered.
Menghapus Sequence
@ Menghapus Sequence dari data dictionary menggunaan perintah DROP SEQUENCE.
@ Sekali terhapus, sequence tidak dapat dipanggil lagi.
SQL> DROP SEQUENCE dept_deptno;
Sequence dropped.
Apakah Index itu ?
@ Sebuah objek skema.
@ Gigunakan oracle server untuk mempercepat pencarian baris dengan menggunakan pointer.
@ Dapat mengurangi I/O disk dengan menggunkan metode akses jalur cepat untuk mempercepat penemuan data.
Membuat Index
@ Membuat index untuk satu atau lebih kolom.
CREATE INDEX index
ON table (column[, column]…);
@ Contoh :
SQL> CREATE INDEX emp_ename_idx
2 ON emp(ename);
Index created.
Kapan Membuat Index ??
@ Kolom sering digunakan dalam klausa WHERE atau kondisi JOIN.
@ Kolom berisi rata-rata nilai yang besar.
@ Kolom berisi banyak nilai NULL.
@ 2 atau lebih kolom sering digunakan bersama dalam klausa WHERE atau kondisi JOIN.
@ Tabel yang besar dan sering digunakan untuk pencarian 2 – 4% baris.
Kapan Index
tidak perlu Dibuat ???
@ Tabel Kecil
@ Kolom tidak sering digunakan sebagai kondisi dalam queri.
@ Banyak queri yang digunakan untuk pencarian data yang lebih dari 2 – 4 % baris.
@Tabel sering diupdate.
Melihat Index
@ View data dictionary USER_INDEXES berisi mana index dan yang bersifat unik.
@ View USER_IND_COLUMNS berisi nama index, nama tabel, dan nama kolom.
SQL> SELECT ic.index_name, ic.column_name,
2 ic.column_position col_pos,ix.uniqueness
3 FROM user_indexes ix, user_ind_columns ic
4 WHERE ic.index_name = ix.index_name
5 AND ic.table_name = ‘EMP’;
Synonyms
@ Membuat nama lain untuk objek.
@ Memudahkan objek untuk mengakses.
@ Mempersingkat nama objek.
@ Sintaks :
CREATE [PUBLIC] SYNONYM synonym
FOR object;
Membuat dan Menghapus Synonym
@ Membuat sebuah nama singkat untuk view DEPT_SUM_VU.
SQL> CREATE SYNONYM d_sum
2 FOR dept_sum_vu;
Synonym Created.
@ Menghapus synonim.
SQL> DROP SYNONYM d_sum;
Synonym dropped
0 komentar:
Posting Komentar