Modul Oracle Basic

Tutor By Irmawati,MMSI

Modul Pelatihan

Oracle Medium

Oracle 10g


Daftar Isi

BAB I Pendahuluan

1.1 Pengenalan Oracle 10g

1.2 Install database oracle10g di Windows XP

1.3 Step-step membuat database Oracle 10g

1.4 Membuat Listener

1.5 Membuat (mensetting) TNS Names

BAB II Basic Administration

2.1 Memulai Koneksi ke Database

2.2 Startup dan shutdown instance

2.3 Administrasi User

BAB III SQL Basic

3.1 SQL Statement

3.2 SQL*Plus

3.3 SQL *Plus Vs SQL Statement

BAB IV SQL Function

4.1. Single Row Function

4.2 Multiple Row Function

BAB V JOIN, SUB-QUERY

5.1 Kartesian Produk


BAB I Pendahuluan

1.1. Pengenalan Oracle 10g

Oracle 10g, g adalah singkatan dari grid. Berikut ini pembahasan

mengenai Grid yang di kutip dari Wikipedia.Komputasi Grid (grid

computing) adalah penggunaan sumber daya yang melibatkan banyak

komputer yang terdistribusi dan terpisah secara geografis untuk

memecahkan persoalan komputasi dalam skala besar.

Latar belakang grid

Perkembangan kecepatan prosesor berkembang sesuai dengan Hukum

Moore, meskipun demikian bandwidth jaringan komputer berkembang

jauh lebih pesat. Semakin cepatnya jalur komunikasi ini membuka

peluang untuk menggabungkan kekuatan komputasi dari sumber- sumber


komputasi yang terpisah. Perkembangan ini memungkinkan skala

komputasi terdistribusi ditingkatkan lebih jauh lagi secara geografis,

melintasi batas-batas domain administrasi yang sudah ada.

Pesatnya perkembangan teknologi komputer di negara-negara maju,

membuat para penelitinya semakin haus akan tenaga komputasi yang

dapat menjawab tantangan dan permasalahan yang mereka hadapi.

Walaupun sudah memiliki supercomputer dengan kapasitas yang sangat

tinggi , apa yang sudah ada ini pun dirasa tetap kurang, karena mereka

berusaha memecahkan permasalahan yang lebih besar lagi. Setelah

semua komputer yg dimiliki seorang “peneliti haus tenaga komputasi”

dipergunakan habis-habisan untuk memecahkan masalahnya, setelah

berbagai cara untuk memecahkan masalah dicoba, dan dipilih yang paling

efisien, tapi tetap masalahnya belum bisa dipecahkan juga, apa yang

harus dia lakukan? Komputasi grid adalah salah satu jawaban dari

pertanyaan ini.

Definisi grid

Menurut tulisan singkat oleh Ian Foster ada check-list yang dapat

digunakan untuk mengidentifikasi bahwa suatu sistem melakukan

komputasi grid yaitu :

• Sistem tersebut melakukan koordinasi terhadap sumberdaya

komputasi yang tidak berada dibawah suatu kendali terpusat. Seandainya

sumber daya yang digunakan berada dalam satu cakupan domain

administratif, maka komputasi tersebut belum dapat dikatakan komputasi

grid.

• Sistem tersebut menggunakan standard dan protokol yang bersifat

terbuka (tidak terpaut pada suatu implementasi atau produk tertentu).

Komputasi grid disusun dari kesepakatan-kesepakatan terhadap masalah

yang fundamental, dibutuhkan untuk mewujudkan komputasi bersama

dalam skala besar. Kesepakatan dan standar yang dibutuhkan adalah


dalam bidang autentikasi, otorisasi, pencarian sumberdaya, dan akses

terhadap sumber daya.

• Sistem tersebut berusaha untuk mencapai kualitas layanan yang

canggih, (nontrivial quality of service) yang jauh diatas kualitas layanan

komponen individu dari komputasi grid tersebut.

Peluang grid

Dalam buku The Grid:Blue Print for a new computing infrastructure

dijelaskan bahwa yang dimaksud dengan komputasi grid adalah

infrastruktur perangkat keras dan perangkat lunak yang dapat

menyediakan akses yang bisa diandalkan, konsisten, tahan lama dan

tidak mahal terhadap kemampuan komputasi mutakhir yang tersedia.

“A computational grid is a hardware and software infrastructure that

provides dependable,consistent, pervasive, and inexpensive access to

high-end computational capabilities.”

Seandainya kelak di kemudian hari teknologi yang dibutuhkan untuk

mewujudkan visi paradigma komputasi grid ini sudah mapan, peluang

akan semakin terbuka bagi kerjasama lintas organisasi, lintas benua dan

lintas bangsa. Akan terbuka peluang bagi peneliti di Indonesia yang ingin

melakukan komputasi yang sangat rumit, dengan menggunakan

supercomputer tercepat di dunia, tanpa harus melakukan investasi besar-

besaran dalam bidang teknologi informasi.

Produk Grid dari Oracle

Oracle telah membangun software infrastruktur komputasi grid yang

mampu membagi dan menyeimbangkan semua beban (workload) di

antara server-server yang berbeda, dan memanage server-server yang

berbeda itu sebagai satu kesatuan sistem.

Kemampuan komputasi grid adalah setara dengan mainframe karena

semua komponennya (grid) di-cluster. Namun tidak seperti mainframe

dan server SMP (symmetric multiprocessing) yang besar, grid bersifat

terbuka (open system technologies) yang bisa mencakup system kecil-

kecil dengan processor Intel dan OS Linux sehingga biaya jadi jauh lebih

murah.


Adapun produk grid dari Oracle adalah:

• Storage Grid: Automatic Storage Management (ASM)

• Database Grid: Real Application Server (RAC)

• Application Grid: Oracle Streams

• Grid Control: Enterprise Manager Grid Control

Produk Database Server Oracle 10g dikeluarkan dalam 5 versi agar dapat

dipergunakan sesuai kebutuhan user. Serta mendukung berbagai Platform

Operating Server sehingga semua User dapat mengunakan Oracle dengan

mudah tanpa harus berganti Platform yang sudah existing. Selain support

Operating System Windows ( NT, Windows 2000 Pro / Server, Windows

XP, Windows 2003 Server, Windows 2003 Server R2, Windows Vista ),

Oracle juga Support untuk Linux, Unix, Solaris, HP-UX, dll.

Hal ini sangat memudahkan User jika suatu hari nanti harus berganti

Operating System sehingga proses Migrasi dapat dipastikan berjalan

dengan mudah dan lancar karena dilakukan dari Database Server Oracle

ke Oracle. Tentunya berbeda sekali dengan Migrasi dari suatu Database

Server tertentu ke Database Server yang berbeda. Sehingga perlu teknik

Migrasi khusus ataupun Tools tertentu dari Thirtparty agar dapat

melakukan proses Migrasi Database dengan jaminan data hasil transfer

migrasi ini benar - benar valid.

Produk Oracle 10g Database Server ada 5 macam, yaitu :

a. Oracle Database 10g Standart Edition One


i. Untuk Workgroup dan level departemen Small Office and Home

Office (SOHO)

ii. Max 2 processor, Single Server

b. Oracle Database 10g Standart Edition

i. Support High Availability dengan RAC

ii. Max 4 Processor, support Server berkapasitas tinggi

c. Oracle Database 10g Enterprise Edition

i. Secure Data Managemen, mampu memenuhi segala availability

dan scalability

ii. Unlimited Processor

d. Oracle Database 10g Personal Edition

Kemampuan sama seperti Oracle 10g Enterprise Edition, unlimited

processor, hanya dengan limitasi single user saja.

e. Oracle Database 10g Express Edition

FREE!!! 4D (Download, Develop, Deploy dan Distribute) Max 1 GB

memory, Max 4 GB Database Files.

1.2. Install database oracle10g di Windows XP

Secara umum, guide ini berlaku untuk semua instalasi Oracle. Di semua

OS, proses instalasi itu sama, hanya sedikit berbeda di pre-installation

requisite-nya.Download software Database Oracle dulu. Free, alias gratis.

Sebelumnya anda harus punya account di Oracle. Kalau belum punya,

membuatnya gampang sekali, yang penting punya email. Ikuti saja proses

“sign up”. Kalau diminta memasukkan pin OPN, dan anda tidak punya pin

OPN karena company anda bukan partner-nya Oracle, kosongkan saja.

Berikut ini panduan download Oracle Database 10g Release 2

(10.2.0.1.0):

Buka link (page) untuk download di sini

http://www.oracle.com/technology/software/products/database/index.html

Pilih versi database dan tipe OS

http://www.oracle.com/technology/software/products/database/index.html


Disini kita memilih “Oracle Database 10g Release 2 (10.2.0.1.0) for

Microsoft Windows”

Kemudian muncul pilihan berikut

- Oracle Database 10g Release 2 (10.2.0.1.0)

- Oracle Database 10g Companion CD Release 2 (10.2.0.1.0)

- Oracle Database 10g Client Release 2 (10.2.0.1.0)

- Oracle Clusterware Release 2 (10.2.0.1.0)

- Oracle Gateways 10g Release 2 (10.2.0.1.0) (10.2.0.1.0)

Pilihlah option pertama. Kalau hanya untuk sekedar belajar database,

anda cukup milih option pertama saja.

Spesifikasi yang digunakan dalam instalasi ini adalah:

- Microsoft Windows XP Professional Version 2002 Service Pack 2

- CPU: intel 2 Ghz

- Memory: 1G

- Virtual (page) memory: 1,5G

Dengan spesifikasi tersebut, PC tersebut (lebih dari cukup) memenuhi

syarat. Lebih detail tentang spesifikasi komputer yang bisa diinstall, lihat

dokumentasi (installation guide) yang ada di paket software yang telah di

download. Secara umum, berikut ini spesifikasinya:

Hardware:

- Physical memory (RAM) : 256 MB minimum, 512 MB recommended

- Virtual memory: dua kali RAM

- Disk space: kira-kira 5 G

- Video (monitor) adapter: 256 colors

- Processor : 550 MHz

Operating system (OS)

- Windows 2000 with service pack 1 or later. All editions, including

Terminal Services and Microsoft Windows 2000 MultiLanguage Edition

(MLE).

- Windows Server 2003 - all editions


- Windows XP Professional

- Windows NT is not supported.

Berikut ini langkah-langkahnya:

1. Jalankan command “setup.exe” yang ada di paket software yang telah

di download

Kemudian muncul Install wizard (GUI).

- Pilih option “Basic Installation”

- Masukkan directory “Oracle Home Location”

- Pilih “Installation Type”

- Jangan pilih “Create Starter Database”

- Klik button “Next”

2. Oracle installer akan mengecek OS kita, apakah requirement-nya

dipenuhi atau tidak.


Pastikan semua statusnya “Succeeded”. Kalau ada warning, atau

statusnya bukan Succeed, bereskan dulu OS-nya. Kemudian klik button

“Next”

3. Dalam proses instalasi, Oracle akan menjalankan program java. Bila

firewall PC anda memblock java, dan muncul alert “Windows Security

Alert”, kilk tombol “Unblock”.

4. Muncul summary komponen Oracle Database 10g yang siap kita

install.


Kemudian klik tombol “Install”

5. Installation progress ditunjukkan oleh gambar ini.


6. Setelah itu akan dilakukan konfigurasi tambahan oleh Oracle Installer.

Kita cukup perhatikan saja.

Setelah konfigurasi selesai, klik tombol “Next”.

7. Instalasi selesai.

Setelah itu klik tombol “Exit”

1.3. Step-step membuat database Oracle 10g

Setelah meng-install software database (RDBMS) Oracle, sekarang saatnya

membuat database. Baik di Windows maupun Unix (Linux, Sun Solaris, IBM


AIX, HP UX, dan lain- lain) caranya sama saja. Membuat database, bisa

dengan mamakai SQL script (via SQLPlus) ataupun GUI (wizard) yang

disediakan Oracle. Di versi 8i, tool GUI tersebut adalah dbassist; sementara

versi 9i ke atas adalah dbca.

Untuk Windows lokasi dbca ada directory %ORACLE_HOME%/bin , sementara

untuk Unix di $ORACLE_HOME/bin. Secara struktur, lokasi file-file software

Oracle baik di Windows maupun Unix adalah sama saja. Yang berbeda hanya

penulisan parameter. Di Windows, parameter diapit oleh tanda % (contoh

%ORACLE_HOME%) sedangkan di Unix parameter didahului oleh tanda $

(contoh $ORACLE_HOME)

Berikut ini adalah langkah-langkah (step-step) untuk membuat database 10g:

1. Jalankan command dbca. Akan muncul form wellcome.

Selanjutnya klik tombol Next.

2. Berikutnya keluar form pilihan option.


Pilih “Create a Database”. Selanjutnya klik tombol Next

3. Berikutnya keluar form Database template.

Ada 3 pilihan template. Kalau tidak mau pakai templete, pilih “Custom

Database”. Dalam contoh ini saya memilih template “General Purpose”.



4. Berikutnya muncul form Database Identification.

Ini nama database. Terserah mau diberi nama apa. Di sini dinamai “dataku”.


5. Berikutnya mucul form Management Option.

Pilih “Configure the Database with Enterprise Manager”. Selanjutnya klik

tombol Next.


6. Berikutnya mucul form Database Credential.

Masukkan password. Untuk mempermudah biar tidak gampang lupa, saya

memilih password yang sama untuk semua account. Selanjutnya klik tombol

Next.

7. Berikutnya mucul form Storage Option.

Pilih “File system”. Selanjutnya klik tombol Next.


8. Berikutnya mucul form Database File Location.

Saya memilih “C:\oracle\oradata”. Selanjutnya klik tombol Next.

9. Berikutnya mucul form Recovery Configuration.

Pilih Flash Recovery Area,direktori default. Selanjutnya klik tombol Next.


10. Berikutnya mucul form Database Content.

Sample schema berisi schema-schema (berserta object: table, index, view, dll)

contoh dari Oracle. Ini bermanfaat bagi yang sedang belajar. Selanjutnya klik

tombol Next.

11. Berikutnya mucul form Initialization Parameter.


Untuk memori, pilih custom, dengan shared memory management:

Automatic. Nilai SGA sesuaikan dengan memory komputer kita. Dengan

memory PC 1G, masukkan SGA: 274M. Selainnya memakai value (nilai)

default. Selanjutnya klik tombol Next.

12. Berikutnya mucul form Database storage.

Lokasi file-file adalah sesuai dengan directory yang telah kita pilih tadi. Melalui

wizard ini kita bisa mengubah ke direktori lain. Selanjutnya klik tombol Next.

13. Berikutnya mucul form Creation Option.


Pilih “Create Database” karena kita akan membuat database. Pilih juga

“Generate database creation scripts”. Bermanfaat bagi yang belajar, untuk

memahai command- command apa yang dijalankan ketika membuat

database. Selanjutnya klik tombol Finish

14. Berikutnya muncul form Confirmation.


Berisi tentang resume database yang akan kita buat. Selanjutnya klik tombol

OK.

15. Berikutnya mucul Installation Progress. Kalau create di Windows mungkin

muncul error, lihat gambarnya di sini.

Kalau menemui error ini, tambahkan parameter berikut di file

%ORACLE_HOME%/network/admin/sqlnet.ora

SQLNET.AUTHENTICATION_SERVICES = (NONE)

Bila file tersebut belum ada, buatlah. Jika file sudah ada dan nilai parameter

tersebut bukan NONE, ubah menjadi NONE. Selanjutnya klik Ignore, nanti

akan kembali ke form “Creation Option”, ulangi (lanjutkan) ke step-step

berikutnya. Bila berhasil,

maka “Creation progress” akan menunjukkan proses pembuatan database

yang sedang berjalan.


16. Akhirnya proses selesai.

Selanjutnya klik tombol Exit. Setelah selesai, bila di WIndows, ubah nilai

SQLNET.AUTHENTICATION_SERVICES dari NONE menjadi

NTSSQLNET.AUTHENTICATION_SERVICES = (NTS) Ini penting agar bisa

mudah memanage database.


1.4. Membuat Listener

Setelah install software Oracle dan membuat database Oracle, kini saatnya

kita untuk mengakses database lewat jaringan. Dari sisi server (Oracle

database) diperlukan listener, sementara dari sisi client diperlukan Local Net

Service Name (TNS Names).

Listener bisa dibuat dengan GUI (wizard) ataupun melalui command line. Di

Oracle 8i ke atas, GUI (tool) tersebut adalah netca. Seperti tool-tool database

Oracle yang lain, lokasinya ada di $ORACLE_HOME/bin.

Berikut ini langkah-langkah membuat listener pakai netca. Sebagai contoh

saya menggunakan Oracle 10g. Secara umum adalah sama untuk Oracle versi

lainnya.

1. Jalankan command netca. Akan muncul form wellcome.

Pilih “Listener Configuration”. Selanjutnya klik tombol Next.


2. Berikutnya keluar form Listener Configuration.

Pilih “Add”. Selanjutnya klik tombol Next.

3. Berikutnya keluar form Listener Name.

Masukkan nama listener. Kita bisa menamakan apa saja. Di sini saya biarkan

pakai nama default, yaitu LISTENER. Selanjutnya klik tombol Next.


4. Berikutnya keluar form Select Protocol.

Pilih TCP. Selanjutnya klik tombol Next.

5. Kalau milih TCP, berikutnya akan keluar form TCP/IP Protocol

Kemudian pilih port yang akan digunakan. Gunakan port default, 1521.


6. Berikutnya akan keluar form More Listener.


Apakah kita ingin membuat listener yang lain lagi?. Saya pilih tidak (No).


7. Akhirnya selesai.

Kalau ingin melakukan perkerjaan lainnya, klik tombol Next. Jika cukup

membuat listener ini saja, klik tombol Cancel.

Pada dasarnya secara intrinsik, Oracle melakukan 2 hal berikut ini (membuat

listener pakai command line dengan cara berikut ini):

1. Membuat file konfigurasi untuk listener di

$ORACLE_HOME/network/admin/listener.ora.

Adapun isi file tersebut adalah:

SID_LIST_LISTENER = (SID_LIST =

(SID_DESC =

(SID_NAME = PLSExtProc)

(ORACLE_HOME = C:\oracle\product\10.2.0\db_1) (PROGRAM = extproc)

)

)LISTENER = (DESCRIPTION_LIST = (DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 10.21.108.70)(PORT = 1521))

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

)

)


2. Menjalankan (menaikkan) proses listener

lsnrctl start LISTENER

Selanjutnya, masukkan database yang telah kita buat tadi ke dalam

konfigurasi listener agar database bisa dilayani listener. Sebenarnya kalau

listener dibuat (dan dinaikkan) dulu sebelum membuat database, konfigurasi

ini dilakukan secara otomatis oleh dbca (bila kita membuat database

menggunaka dbca).

Di Oracle 9i keatas, untuk melakukannya kita bisa menggunakan tool netmgr

yang ada di

$ORACLE_HOME/bin. Di Windows sepertinya netmgr tidak dibuatkan exe-nya

di

%ORACLE_HOME%/bin, tapi dibuatkan sort cut-nya di menu program. Berikut

ini step-step menggunakan netmgr:

1. Jalankan command netmgr. Kemudian muncul Wizard-nya.

Di tab, pilih “Database Services”. Selanjutnya klik tombol “Add Database”.

2. Kemudian masukkan ORACLE_HOME dan nama instance (SID) yang akan

di- manage.


3. Setelah itu simpan konfigurasi tersebut.

Selesai.

4. Agar konfigurasi bisa update, restart listener

lsnrctl stop LISTENER

lsnrctl start LISTENER


Secara intrinsik, netmgr menambahkan definisi konfigurasi di file

$ORACLE_HOME/network/admin/listener.ora (di mana kita bisa melakukannya

secara manual tanpa netmgr):

SID_LIST_LISTENER = (SID_LIST = (SID_DESC =

(SID_NAME = PLSExtProc)

(ORACLE_HOME = C:\oracle\product\10.2.0\db_1) (PROGRAM = extproc)

)

(SID_DESC =

(ORACLE_HOME = C:\oracle\product\10.2.0\db_1) (SID_NAME = dataku)

)

)

1.5. Membuat (mensetting) TNS Names

Software Oracle sudah di-install, database sudah dibuat, dan listener juga

sudah dibuat. Untuk bisa mengakses database di mesin server, suatu client

(misalnya PC) harus di-install software Oracle Client. Setelah itu, kita perlu

mensetting TNS Names di client tersebut. Tool GUI (wizard) di Oracle versi 8i

ke atas adalah netca.

Ketika install software database (RDBMS) Oracle, secara otomatis di-install

juga Oracle client. Jadi, di PC yang telah di install RDBMS Oracle itu juga

automatically sudah terinstall Oracle client. Jadi untuk belajar (lagipula karena

keterbatasan jumlah komputer) kita bisa memakai satu PC sebagai server dan

client sekaligus.


Berikut ini langkah-langkah (step-step) nya:

1. Jalankan command netca. Kemudian muncul form Welcome.

Pilih “Local Net Service Name configuration”. Selanjutnya klik tombol Next.

2. Berikutnya muncul form Net Service Name Configuration.

Pilih “Add”. Selanjutnya klik tombol Next.


3. Berikutnya masukkan “Service Name”.

Gampangnya saja, service name adalah sama dengan nama instance (SID).

Di sini nama instance adalah dataku. Selanjutnya klik tombol Next.

4. Berikutnya pilih protocol.

Protokol adalah sama dengan protokol yang dipakai listener. Saya pakai TCP.



5. Berikutnya masukkan host name (alamat) database server.

Bisa pakai IP dan bisa pakai nama komputer. Di sini memakai IP. Selanjutnya

klik tombol Next.

6. Berikutnya muncul pertanyaan, apakah akan kita lakukan test?.

Sebaiknya pilih “Yes”. Selanjutnya klik tombol Next.

7. Test koneksi memakai user system dengan password manager (default).

Karena password system adalah oracle, maka connection error.

Tutor by IRMAWATI,MMSI

Selanjutnya klik tombol “Change Login”.

8. Masukkan user system dan password-nya yang benar.

Selanjutnya klik tombol OK.

9. Kini koneksi success.



10. Berikutnya masukkan nama TNS yang kita kehendaki.

Namanya terserah kita, bebas. Di sini (by default) namanya sama dengan

nama service (SID) yang tadi kita masukkan. Selanjutnya klik tombol Next.

11. Apakah akan meng-configure TNS yang lainnya?.


Pilih “No” dan klik tombol Next.

12. Selanjutnya, berhasil.

Kalau ingin mensetting yang lainnya pakai netca, klik Next. Kalau cukup

sekian klik Cancel.


BAB II Basic Administration

2.1. Memulai Koneksi ke Database

Setelah menginstall Oracle dan membuat database, untuk langkah awal

administrasi adalah mulai melakukan koneksi ke database.

Administrasi dilakukan oleh user yang meng-install dan membuat database.

Tool native dari Oracle untuk administrasi database adalah sqlplus, lokasi ada

di $ORACLE_HOME/bin. Di Oracle versi 8 ke bawah, tool administrasi tersebut

adalah svrmgrl.

Sebelum melakukan koneksi, ada OS parameter yang perlu disetting. Di

Windows, parameter tersebut otomatis sudah dimasukkan ke dalam registry

ketika meng-install dan membuat database pakai dbca. Di Unix, setting

manual parameter berikut di user profile: ORACLE_HOME, ORACLE_SID, dan

PATH.

Misalkan kita pakai shell sh atau ksh. Edit file .profile, tambahkan parameter

berikut:

ORACLE_HOME=/data1/oracle/product/10.2.0; export ORACLE_HOME

ORACLE_SID=ts; export ORACLE_SID

PATH=$ORACLE_HOME/bin:$PATH; export PATH

Setelah mengedit file .profile, jangan lupa untuk relogin atau mengeksekusi

file tersebut agar parameter yang disetting terbaca oleh current session.

Berikut ini cara mengeksekusi file

.profile.

. ./.profile


Koneksi pakai SQLPLus di Mesin server

Sekarang, mari kita mulai koneksi ke database. Misalkan saya akan connect

pakai user system.

Sqlplus

Nanti akan diminta memasukkan username dan password. Kalau belum

dirubah, password system adalah seperti yang ditunjukkan ketika membuat

database.

Bisa juga username langsung dimasukkan ke argument-nya SQLPlus, nanti

kita cuma diminta memasukkan password saja.

sqlplus system

Bisa juga langsung memasukkan username dan password. Misalkan password

user system adalah oracle:

sqlplus system/oracle

Koneksi dengan langsung memasukkan username dan password sekaligus ini

tidak direkomendasikan, karena password akan tampak ketika di ps -ef.

Contoh:

ps -ef|grep sql

oracle 5742 25612 11:09:49 pts/1 0:00 sqlplus system/oracle

Cara lain juga, kita bisa masuk ke SQLPlus prompt tanpa login, kemudian

jalankan perintah connect atau conn di SQL prompt.


Contoh:

sqlplus /nolog

SQL> conn

Sama seperti ketika menjalankan sqlplus dari OS prompt, username dan

password bisa disebutkan langsung atau tidak; kalau tidak disebutkan nanti

akan ditanyakan.

SQL> conn system/oracle

SQL> conn system

Koneksi pakai user sys

User sys adalah merupakan super user, dikenal juga sebagai sysdba. Untuk

koneksi pakai user sys, harus ditambahkan argument as sysdba.

Contoh:

SQL> conn sys/oracle as sysdba

Bisa juga tanpa menyebutkan user sys, yaitu dengan memakai argument /.

Contoh:

SQL> conn / as sysdba

Kalau tidak sebutkan argument as sysdba, akan muncul error berikut:

SQL> conn sys/oracle

ERROR:

ORA-28009: connection as SYS should be as SYSDBA or SYSOPER Warning:

You are no longer connected to ORACLE.

Bisa juga langsung login ketika menjalankan SQLPLUS. Contoh:

sqlplus "sys/oracle as sysdba" sqlplus "/ as sysdba"


Koneksi dari client ke server

Untuk bisa melakukan koneksi client-server, pastikan kita sudah mensetting

dan menjalakan listener di server database, dan mensetting TNSNames di

client. Kalau belum punya instalasi Oracle client di mesin/komputer/PC lain,

kita bisa memanfaatkan database server sebagai client sekaligus. Ketika kita

install software database Oracle, by default juga diinstall Oracle client;

sehingga nantinya kita bisa melakukan koneksi client-server di mesin server

database kita.

Pada koneksi client-server , tambahkan argument @namatns.

Contoh:

sqlplus system@tsprimary

sqlplus system/oracle@tsprimary

sqlplus "sys@tsprimary as sysdba"

sqlplus "sys/oracle@tsprimary as sysdba"

SQL> conn system@tsprimary

SQL> conn system/oracle@tsprimary

SQL> conn sys@tsprimary as sysdba

SQL> conn sys/oracle@tsprimary as sysdba

2.2. Startup dan shutdown instance

Administrasi (aktivitas) yang bisa kita lakukan pada instance adalah startup,

shutdown, dan alter. Secara umum proses startup adalah sebagai berikut:

1. Database mati (shutdown)

Background process belum naik. Memori belum dialokasikan

mailto:@namatns

mailto:system@tsprimary

mailto:oracle@tsprimary

mailto:sys@tsprimary


mailto:system@tsprimary


mailto:sys@tsprimary



2. nomount

Backgroung process dinaikkan. Memory dialokasikan

3. mount

Instance membaca control file. Control file berisi konfigurasi database.

Instance belum membaca data file.

4. open

Instance sudah membaca data file (header). Database siap diakses

Command (perintah) startup :

startup

startup open

startup nomount

startup mount

startup force

Command “startup” saja tanpa argument, by default adalah “startup open”

Command “startup force” adalah sama saja dengan “shutdown abort”


Kemudian “startup”

Command shutdown :

shutdown normal

shutdown transactional

shutdown immediate

shutdown abort

2.3. Administrasi User

Waktu kita membuat database, secara otomatis Oracle membuat user sys

dan system. User sys bisa melakukan apa saja, oleh karenanya disebut

sebagai super user. User system digunakan untuk administrasi database

sehari-hari, misalnya membuat user, tablespace, dan lain-lain. Kalau user sys

bisa melakukan semuanya, kenapa mesti ada user system? Ya tentu saja,

pertimbangannya adalah masalah security. Mari kita mulai membahas

administrasi user di database Oracle.

Create (membuat) user

Berikut ini format perintah SQL untuk membuat user


Perintah membuat user yang paling sederhana adalah (contoh, saya akan

membuat user dengan nama IRMA dan passwordnya THEA99):

SQL> conn system

SQL> create user IRMA identified by THEA99;

Karena tidak di-specify, maka atribut-atribut yang lainnya memakai nilai

default. Untuk melihatnya lihat di view dba_users

SQL> select * from dba_users where username='IRMA';

Ini hasilnya:

USERNAME: IRMA

ACCOUNT_STATUS : OPEN

EXPIRY_DATE :

DEFAULT_TABLESPACE :

USERS

TEMPORARY_TABLESPACE

: TEMP PROFILE :

DEFAULT INITIAL_RSRC_CONSUMER_GROUP : DEFAULT_CONSUMER_GROUP


Untuk melihat default tablespace dan default temporary tablespace dari

suatu database, silahkan lihat di Administrasi Tablespace.

Berikut ini contoh membuat user dengan men-specify default tablespace,

quota pemakaian di tablespace, dan temporary tablespace (ini contoh yang

paling sering digunakan):

SQL> CREATE USER IRMA

IDENTIFIED BY THEA99

DEFAULT TABLESPACE

users QUOTA unlimited ON

users QUOTA 500K ON

data_ts TEMPOR ARY

TABLESPACE temp;

Alter (mengubah) user

Semua atribut user bisa diubah (alter) kecuali username itu sendiri. Secara

umum perintah alter sama dengan create, hanya mengganti kata create

menjadi alter. Contoh:

SQL> alter user THEA identified by THEA991;

Grant User

Setelah user dibuat, user tersebut tidak bisa melakukan koneksi sebelum

diberi grant (hak) untuk connect ke database.

SQL> conn IRMA/THEA991

ERROR:

ORA-01045: user IRMA lacks CREATE SESSION privilege; logon denied

Berikut ini perintah untuk memberi grant ke user agar bisa connect ke

database


SQL> conn system;

SQL> grant connect to IRMA;

Setelah itu, dengan user IRMA tersebut, mari kita coba membuat table


SQL> create table tab_test (no number); ERROR at line 1:

ORA-01031: insufficient privileges

ternyata user IRMA belum punya priviledges untuk membuat table. Coba

beri privilege ke user IRMA agar bisa membuat tabel

SQL> conn system;

SQL> grant create table to IRMA;

Sekarang buat table


SQL> create table tab_test (no number); ERROR at line 1:

ORA-01950: no privileges on tablespace 'USERS'

ternyata error. Walaupun default tablespace untuk user IRMA adalah

tablespace USERS, ternyata IRMA masih belum bisa membuat table

(menulis) di tablespace USERS. Beri quota ke user IRMA, bisa unlimited

ataupun di-specify besarannya. Contoh, beri quota unlimited:

SQL> conn system;

SQL> alter user IRMA quota unlimited on USERS;

Coba lagi, dan berhasil …



SQL> create table tab_test (no number); Table created.

Agar bisa membuat index, user IRMA harus diberi grant. Demikian juga untuk

bisa membuat view. Dapat anda bayangkan, berapa banyak privilege yang

mesti kita beri (grant)? Untuk mengatasi itu, Oracle membuat role. Role

berisi beberapa (banyak) privilege. Contoh, role RESOURCE berisi grant

untuk membuat table, membuat index, quota unlimited di default tablespace,

dan lain-lain.

Berikut ini perintah untuk memberi hak (grant) role RESOURCE ke user IRMA

SQL> conn system

SQL> grant RESOURCE to IRMA;

Umumnya, cukup dengan memberi grant CONNECT dan RESOURCE ke user

yang baru dibuat, user tersebut sudah bisa melakukan banyak pekerjaan.

SQL> create user IRMA identified by THEA99;

SQL> grant connect to IRMA;

SQL> grant RESOURCE to IRMA;

Menghapus User

SQL> drop user IRMA;

Kalau user tersebut mempunyai object (misalnya tabel), maka akan muncul

error berikut

ORA-01922: CASCADE must be specified to drop 'IRMA'

Kalau begitu, tambahkan parameter CASCADE untuk sekaligus menghapus

semua object yang dimiliki user tersebut

SQL> drop user THEA CASCADE;


BAB III

SQL Basic

3.1. SQL Statement

Untuk menampilkan data dari database, kita menggunakan perintah

SQL select.

Dengan menggunakan SELECT statement kita dapat melakukan hal2

berikut :

1. Selection : Kita dapat menggunakan SELECT statement untuk

memilih row/data pada table sesuai yang kita inginkan dengan

query.

2. Projection : dengan projection kita dapat menampilkan kolom

yang kita inginkan dari table dengan query.

3. Join : Dengan join kita dapat menampilkan data dari beberapa

table yang berhubungan dengan membuat link antar table

tersebut.

Syntak Select Statement dasar :

SELECT [DISTINCT] {*, column [alias], . . . } FROM table;

Keterangan :

SELECT List satu atau beberapa kolom

DISTINCT Menampilkan hanya satu data jika ada duplikasi

* Menampilkan Semua kolom

column Menampilkan kolom dengan nama column

alias Memberikan nama berbeda untuk kolom

FROM table Nama table yang di query

Menampilkan semua kolom dari tabel department

SQL> SELECT * 2 FROM dept;


DEPTNO DNAME LOC ---------- -------------- -------------

10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON

untuk menampilkan semua kolom kita bisa menggunakan asterik (*) atau

select semua kolom dari table yang di query. Untuk kasus table department

ada tiga kolom yaitu DEPTNO,DNAME dan LOC, maka ketika kolom

tersebut akan kita gunakan dalam Select statement.

SQL> SELECT deptno, dname, loc

2 FROM dept;

DEPTNO DNAME LOC ---------- -------------- -------------

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON

Arithmetic Expression

Jika pada suatu saat kita ingin melakukan modifikasi data hasil query, seperti

melakukan penambahan,pengurangan dll, kita bisa menggunakan operator

arithmetik untuk melakukannya

SQL> SELECT ename, sal, sal+200 2 FROM emp;

ENAME SAL SAL+200

---------- ---------- ---------- ALLEN 1600 1800 WARD 1250 1450 JONES 2975 3175 MARTIN 1250 1450


Urutan eksekusi operator adalah * / + -

SQL> SELECT ename, sal, 12*sal+10 2 FROM emp;

ENAME SAL 12*SAL+10 ---------- ---------- ---------- ALLEN 1921 16000

WARD 1250 15010 JONES 2975 35710

MARTIN 1250 15010

Mendefinisikan kolom ALias

Untuk memberikan nama kolom yang berbeda pada tampilan kita

menggunakan alias, contoh:

SQL> SELECT ename as "NAMA", sal "GAJI"

2 FROM emp;

NAMA GAJI

ALLEN 1600

WARD 1250 JONES 2975 MARTIN 1250

Selain menggunakan tanda petik (“”), kita juga bisa menggunakan

keyword AS. Tapi jika alias yang diberikan terdiri dari dua kata kita harus

menggunakan tanda petik. Keyword as dan tanda petik bisa digunakan

bersama-sama.

Operator Penggabungan (concatenation)

Digunakan untuk menggabungkan kolom, karakter atau string dengan

kolom yang lain. Digambarkan dengan dua garis tegak (||).


Sebagai contoh kita ingin menampilkan data karyawan x adalah

seorang y.

SQL> SELECT ename || ' Adalah Seorang ' || job as "DETAIL KARYAWAN"

2 FROM emp;

DETAIL

KARYAWAN ----------------------------------- ALLEN Adalah Seorang SALESMAN

WARD Adalah Seorang SALESMAN JONES Adalah Seorang MANAGER MARTIN Adalah Seorang SALESMAN

Duplicate Row

Secara default hasil query menampilkan semua data tanpa mengeliminasi

data yang duplikat. Untuk menangani itu kita gunakan keyword DISTINCT.

SQL> SELECT deptno

2 FROM emp;

DEPTNO ----------

10 20 10 30

30 SQL> SELECT DISTINCT deptno

2 FROM

emp;

DEPTNO ----------

10 20 30


3.2. SQL*Plus

SQL*Plus adalah suatu environment yang padanya kita dapat melakukan

eksekusi perintah SQL, Format, print hasil query untuk keperluan report,

membuat script SQL yang bisa digunakan untuk waktu berikutnya.

Loggin ke SQL *Plus

Untuk windows environment

User Name : User database Password : Password user database Host String : Database Connection String

Untuk command line

CMD> sqlplus [ username[/password[@database]]]

Untuk keamanan pada sqlplus masukkan hanya username dengan

database, untuk password diisi dari prompt password.

Menampilkan Struktur Table

Dalam SQL*Plus untuk menampilkan struktur table digunakan perintah DESC

atau DESCRIBE

mailto:@database


SQL> DESCRIBE dept;

Name Null? Type ----------------------------------------- -------- ------------ DEPTNO NOT NULL NUMBER(2)

DNAME VARCHAR2(14) LOC VARCHAR2(13)

Null? Mengindikasikan isi dari kolom, NOT NULL berarti kolom harus berisi data.

Type Informasi tipe data dari kolom.

SQL *Plus Command Edit

Command Deskripsi

A[PPEND] text Menambahkan text ke akhir dari baris

C[HANGE] /old /new Mengganti text old dengan new

C[HANGE] /text / Menghapus text dari baris

CL[EAR] BUFF[ER] Menghapus buffer

DEL Menghapus baris

I[NPUT] Menambahkan sejumlah baris

I[NPUT] text Menambahkan satu baris berisi text

L[IST] Menampilkan semua SQL di buffer

L[IST] n Menampilkan baris ke n dari SQL Buffer

L[IST] m n Menampilkan baris antara m dan n dari SQL

Buffer

n Membuat baris ke n menjadi baris sekarang

n text Replace baris ke n dengan text

SQL *Plus File Command

Command Deskripsi

SAV[E] filename[.ext]

[REP]LACE[APP[END]]

Menyimpan SQL Buffer kedalam filename

GET filename[.ext] Menuliskan isi filename ke SQL Buffer

STA[RT] filename[.ext] Menjalankan filename

ED[IT] Memanggil editor untuk edit SQL Buffer

SPO[OL] filename[.ext] Meyimpan hasi query ke filename

EXIT Keluar dari SQL *Plus


3.3. SQL *Plus Vs SQL Statement

SQL adalah bahasa yang digunakan untuk berkomunikasi dengan

database Oracle server dari sebuah tool atau aplikasi. Oracle SQL terdiri

dari beberapa bagian, ketika kita menjalankan perintah SQL, sql akan

disimpan dalam suatu memory yang disebut SQL Buffer dan akan tetap

berada dalam memory sampai ada statemen baru.

SQL *Plus adalah tool oracle yang mengenali dan menjalankan

perintah SQL ke Oracle Server.

Berikut dalah tabel perbedaan antara SQL dan SQL *Plus

SQL SQL *Plus

Bahasa untuk komunikasi Suatu Environment

ANSI Standard Khusus untuk Oracle

Manipulasi Data di database Tidak bisa memanipulasi data

Di simpan di SQL Buffer Tidak ada SQL Buffer

Keyword tidak dapat disingkat Keyword bisa disingkat

Menggunakan fungsi untuk

Format data

Menggunakan command untuk

format data


WHERE Clause

Untuk menampilkan data dari database dengan kondisi yang kita

inginkan misalnya hanya menampilkan employee yang mempunyai gaji

lebih dari 1000 kita menggunakan WHERE clause.

SELECT [DISTINCT] {* | column [alias] , . . . } FROM table [WHERE condition(s) ]

WHERE Membatasi data hasil query yang sesuai dengan

kondisi yang diberikan. condition(s) merupakan gabungan dari kolom, ekspresi, atau

operator perbandingan.

SQL> SELECT ename, job, deptno

2 FROM emp 3 WHERE job='CLERK';

ENAME JOB DEPTNO ---------- --------- ---------- SMITH CLERK 20 ADAMS CLERK 20 JAMES CLERK 30 MILLER CLERK 10

Pada contoh diatas kita ingin menampilkan semua employee yang

mempunyai pekerjaan sebagai CLERK. Pada kondisi diatas CLERK ditulis

huruf besar karena String dalam data di oracle case sensitive.


Catatan :

- Nilai Character string dan date ditempatkan diantara tanda petik

1 “ ‟ ”

- Nilai Character string adalah case sensitive dan nilai Date

format sensitive.

- Default datae format adalah DD-MON-YY

Operator Arti

= Sama Dengan

> Lebih Dari

>= Lebih Dari Sama Dengan

< Kurang Dari

<= Kurang Dari Sama Dengan

<> Tidak Sama Dengan

BETWEEN x1 AND x2 Antara x1 dan x2

IN (list) Yang sesuai dengan nilai list

LIKE Sesuai dengan pola karakter

IS NULL Nilai Null

Syntax

.. WHERE expr operator value

contoh :

… WHERE hiredate = ‟01-JAN-95‟

… WHERE sal >= 1500 … WHERE ename = „SMITH‟

query untuk menampilkan employee yang mempunyai gaji lebih kecil atau

sama dengan komisi.


SQL> SELECT ename, sal,comm 2 FROM emp 3 WHERE sal < = comm;

ENAME SAL COMM ---------- ---------- ---------- MARTIN 1250 1400

menampilkan semua employee yang mempunyai gaji antara 1000 dan

1500 dolar.

SQL> SELECT ename, sal 2 FROM emp 3 WHERE sal BETWEEN 1000 AND 1500;

ENAME SAL ---------- ---------- WARD 1250

MARTIN 1250 TURNER 1500 ADAMS 1100 MILLER 1300


Menggunakan operator LIKE

SQL> SELECT ename 2 FROM emp

3 WHERE ename LIKE '_A%';

ENAME ----------

WARD MARTIN JAMES

Symbol Deskripsi

% Merupakan gabungan beberapa karakter atau bias juga null

_ Bersesuaian dengan satu karakter

Rule of precedence

Order Operator

1 Semua operator Perbandingan

2 NOT

3 AND

4 OR

ORDER BY Clause

Dengan order by kita dapat mengurutkan data hasil query ascending atau

descending. Order By harus diletakkan di statement paling belakang.

Syntak:

SELECT expr FROM table [WHERE condition(s)] [ORDER BY {column, expr} [ASC | DESC ]]

default urutan adalah ascending yaitu dari kecil ke besar.

Untuk mengurutkan dari besar ke kecil anda harus menggunakan kata

kunci DESC.


Latihan SQL Basic :

1. Tuliskan perintah SQL untuk menampilkan struktur table

DEPATMENTS dan menampilkan semua data. 2. Buat sebuah query untuk menampilkan nama,job dan hiredate dan

nomor karyawan dari table employee, dan simpan SQL tersebut dengan nama b1q2.sql.

3. Jalankan file sql b1q2.sql yang sudah anda simpan.

4. Buat query untuk menampilkan jenis pekerjaan dari table EMP (unik).

5. Load file b1q2.sql ke SQL Buffer, ganti tampilan kolom dengan Emp #, Employee, Job dan Hire Date. Jalankan query!

6. Tampilkan semua data EMP, pisahkan untuk setiap kolom dengan koma (,), dan beri nama kolom DAFTAR KARYAWAN.

7. Tampilkan nama dan gaji karyawan yang gajinya lebih dari

2850. Simpan SQL dengan nama b1q7.sql. 8. Edit file b1q7.sql untuk menampilkan nama dan gaji keryawan dimana

gaji tidak termasuk dalam range anatara 1500 dan 2850.Simpan ulang dengan nama b1q8.sql.

9. Tampilkan nama dan deptno yanng deptno 10 dan 30 urutkan hasil

berdasarkan nama. 10.Tampilkan nama dari karyawan dimana huruf ke tiga adalah A.

11.Tampilkan nama karyawan yang mempunyai dua huruf L dan lokasi kerja di dept 30 atau managernya 7782.

12.Tampilkan nama, job, salary untuk semua karyawan dimana

pekerjaannya Clerk tau Analyst dan gaji tidak sama dengan 1000, 3000 , atau 5000.

13.Tampilkan Nama, gaji, dan komisi dari semua karyawan dimana komisi lebih besar dari gaji yang sudah dinaikkan 10%.

BAB IV

SQL FUNCTION

Tujuan : - Mengetahui type dan fungsi dalam SQL

- Menggunakan Fungsi Char, Number, dan Date dalam SELECT


statement - Menjelaskan penggunaan Fungsi Konversi.

4.1. Single Row Function

Dalam kelompok ini inputnya adalah satu row dan menghasilkan satu hasil

per-row.

Untuk single row function dapat dikategorikan sebagai berikut :

- Character

- Number

- Date

- Conversion

Single row function digunakan untuk memanipulasi data. Menerima input

satu atau lebih argumen dan mengembalikan sebuah nilai untuk setiap row

yang dihasilkan query. Inputan dapat berupa konstanta, variable, nama

kolom atau ekspresi.

Character Function

Function Purpose

LOWER (column | expression) Merubah karakter menjadi

lowercase

UPPER (column | expression) Merubah karakter menjadi

uppercase

INITCAP (column | expression) Mengubah Huruf pertama dari

setiap kata menjadi uppercase

CONCAT(column1|

expression1,column2|

expression2)

Menggabungkan expression1 dan

expression2. (ekuivalen dengan ||)


SUBSTR(column|

expression,m[n])

Mengembalikan karakter dari input

mulai dari karakter pada posisi m. n

adalah panjang karangter yang

diambil. Jika m negative proses

pengambilan dari belakang. Jika n

tidak disisi maka semua karakter

dari posisi m ke akhir akan diambil

LENGTH(column|expression) Mengembalikan jumlah karakter dari

input

INSTR(column|expression,m) Mengembalikan posisi dari m

karakter

LPAD(column|

expression,n,‟string‟)

Membuat rata kanan dengan

menambahkan string jika input

kurang dari n

TRIM(expression) Menghilangkan spasi pada string

input.


Case Conversion Function

Function Result

LOWER(„SQL Fundamental‟) sql fundamental

UPPER(„SQL Fundamental‟) SQL FUNDAMENTAL

INITCAP(„SQL Fundamental‟) Sql Fundamental

Contoh penggunaan character function

SQL> SELECT empno, ename, deptno

2 FROM emp

3 WHERE ename='blake';

no rows selected

SQL> SELECT empno, ename, deptno

2 FROM emp 3 WHERE ename=UPPER('blake');

EMPNO ENAME DEPTNO ---------- ---------- ----------

7698 BLAKE 30


Character Manipulation Function


Function Result

CONCAT(„SQL‟,‟Basic‟) SQL Basic

SUBSTR(„String‟,1,3) Str

LENGTH(„String‟) 6

INSTR(„String‟, „r‟) 3

LPAD(sal,10,„*‟) ******5000

TRIM(„X‟ FROM „XXDATAX‟) DATA

Contoh Penggunaan

SQL>SELECT ename, CONCAT(ename,job), LENGTH(ename), INSTR(ename,'A')

2 FROM emp 3 WHERE SUBSTR(job,1,5) = 'SALES';

ENAME CONCAT(ENAME,JOB) LENGTH(ENAME) INSTR(ENAME,'A') ---------- ------------------- ------------- ---------------- ALLEN ALLENSALESMAN 5 1 WARD WARDSALESMAN 4 2 MARTIN MARTINSALESMAN 6 2

TURNER TURNERSALESMAN 6 0

Number Function

Number Function menerima input numeric dan mengembalikan nilai numeric:

Function Purpose

ROUND(column|expression,n) Membulatkan kolom atau

ekspresi menjadi n dibelakan

koma. Jika n null tidak ada nilai

dibelakang koma, jika n negative

nilai decimal sebelum koma diset

nol SQL> SELECT ROUND(45.923,2), ROUND(45.923,0),

2 ROUND(45.923,-1) 3 FROM DUAL;

ROUND(45.923,2) ROUND(45.923,0) ROUND(45.923,-1) --------------- --------------- ----------------

45.92 46 50


SQL> SELECT TRUNC(45.923,2), TRUNC(45.923), 2 TRUNC(45.923,-1) 3 FROM DUAL;

TRUNC(45.923,2) TRUNC(45.923) TRUNC(45.923,-1) --------------- ------------- ----------------

45.92 45 40

SQL> SELECT ename, sal, comm, MOD(sal, comm) 2 FROM emp

3 WHERE job='SALESMAN';

ENAME SAL COMM MOD(SAL,COMM) ---------- ---------- ---------- ------------- ALLEN 1600 300 100 WARD 1250 500 250

MARTIN 1250 1400 1250 TURNER 1500 0 1500

Date Function

Oracle menyimpan data dalam numeric format yang merepresentasikan

abad, tahun, bulan, hari, jam, menit dan detik. Tampilan default adalah

DD-MON-YY, nilai valid dalam oracle adalah antara 1 januari 4712 BC dan

31 Desember 9999.

SYSDATE

Sysdate adalah fungsi yang mengembalikan tanggal hari ini. Anda dapat

Menggunakan sysdate seperti menggunakan kolom lain, sebagai contoh

anda dapat menampilkan tanggal sekarang dengan memilih sysdate dari

table dummy yang disebut DUAL.

DUAL

Dual adalah dummy table yang dimiliki oleh SYS dan dapat diakses oleh

semua user. Dual berisi satu kolom dan satu row yang berisi nilai x.


SQL>SELECT SYSDATE 2 FROM DUAL;

Aritmatika dalam Date

Karena tipe date disimpan oleh oracle berupa numeric data, maka anda

bisa melakukan Operasi aritmatika padanya seperti penjumlahan dan

pengurangan.

Operasi yang bisa dilakukan adalah sebagai berikut:

Operasi Hasil Keterangan

Date + number Date Menambahkan sejumlan hari ke

date

Date – number Date Mengurangi date dengan sejumlah

hari

Date-date Day Mengurai date dengan date

Date+number/24 Date Menambahkan jam ke date

Fungsi –fungsi dalam tipe data Date

Fungsi Keterangan

MONTHS_BETWEEN Jumlah bulan antara dua tanggal

ADD_MONTHS Menambahkan bulan ke tanggal

NEXT_DAY Hari berikutnya dari tanggal yang

disebutkan.

LAST_DAY Hari terakhir dari bulan

ROUND Round tanggal

TRUNC Truncate tanggal


Keterangan :

MONTHS_BETWEEN(date1, date2):mencari jumlah bulan antara date1 dan

date2. hasil bisa positif atau negatif.

ADD_MONTHS(date,n) : menambahkan n bulan ke date.

NEXT_DAY(date,’char’):mencari tanggal berikutnya sesuai dengan hari

yang diberikan.

LAST_DAY(date) :Mencari tanggal terakhir dari bulan dalam date.

ROUD(date[,‟fmt‟]) : mengembalikan tanggal yang diround

berdasarkan format yang diberikan. Jika format tidak diberikan maka tanggal

akan di round ke tanggal terdekat.

TRUNC(date[,‟fmt‟]) : truncate tanggal dan menghilangkan format

waktu.

Contoh :

MONTHS_BETWEEN(‟01-SEP-95‟,‟11-JAN-94‟) = 19.6774194

ADD_MONTHS(‟11-JAN-94‟,6) = 11-JUL-94 NEXT_DAY(‟01-SEP-95‟,‟FRIDAY‟) = 08-SEP-95 LAST_DAY(‟01-SEP-95‟) = 30-SEP-95

ROUND(‟25-JUL-95‟,‟MONTH‟) = 01-AUG-95 ROUND(‟25-JUL-95‟,‟YEAR‟) = 01-JAN-96

TRUNC(‟25-JUL-95‟,‟MONTH‟) = 01-JUL-95

TRUNC(‟25-JUL-95‟,‟YEAR‟) = 01-JAN-95

SQL> SELECT empno, hiredate, 2 ROUND(hiredate,'MONTH'),TRUNC(hiredate,'MONTH')

3 FROM emp;

EMPNO HIREDATE ROUND(HIR TRUNC(HIR

---------- --------- --------- --------- 7654 28-SEP-81 01-OCT-81 01-SEP-81 7782 09-JUN-81 01-JUN-81 01-JUN-81

7839 17-NOV-81 01-DEC-81 01-NOV-81 7902 03-DEC-81 01-DEC-81 01-DEC-81 7934 23-JAN-82 01-FEB-82 01-JAN-82


Conversion Function

Fungsi konversi di oracle terbagi menjadi dua yaitu: Implicit Datatype Conversion

Expicit Datatype Conversion.

Secara umum oracle server menggunakan rule ekspresi, ketika

konversi tipe data diperlukan.

Sebagai catatan CHAR ke NUMBER hanya berhasil jika karakter

adalah valid number, dan CHAR ke DATE berhasil jika format dari char

adalah DD-MON-YY.

Explicit Datatype Conversion adalah konversi tipe data menggunakan fungsi yang telah disediakan oleh oracle.

Oracle meyediakan tiga fungsi utama untuk melakukan konversi tipe data :

FUNCTION KEGUNAAN

TO_CHAR(number/date,[fmt],

[nlsparams])

Mengkonversi number atau

Date ke tipe data VARCHAR2

dengan format fmt.

Number Conversion :

nlsparams dapat berupa : - decimal character

- Group separator

- Local currency

- International currency

TO_CHAR(number/date,[fmt],

[nlsparams])

Date Conversion :

nlsparams merupakan

15


format dari tanggal, hari,

bulan dan tahun.

TO_NUMBER(char,[fmt],[nlsparams]) Mnegkonversi charater uang

berisi digit ke number.

TO_DATE(char,[fmt],[nlsparams]) Mengkonversi character

yang bersesuaian dengan

tanggal ke tipe data date.

Format Element tanggal yang valid di oracle.

Element Keterangan

SCC atau CC Abad : S adalah prefix BC

YYYY atau SYYYY Year : S prefix BC

YYY , YY, Y Tahun 3, 2 atau 1 angka terakhir

MM Bulan, dua digit

MON Nama bulan, tiga kata

MONTH Nama bulan lengkap

DDD, DD, D Hari dari tahun, bulan atau minggu

DAY Nama hari

DY Nama hari disingkat 3 huruf.

J Julian date

WW, W Minggu dari tahun atau bulan.

HH, HH12, HH24 Jam dari hari, jam (1-12), jam (0-23)

MI Menit (0-59)

SS Detik (0-59)

TH Ordinal number (contoh DDTH hasilnya 4th)

AM, PM Meridian indicator.

SQL> SELECT ename, TO_CHAR(hiredate,'fmDD Month YYYY') hiredate 2 FROM emp;

ENAME HIREDATE ---------- ----------------- ALLEN 20 February 1981

WARD 22 February 1981 JONES 2 April 1981

MARTIN 28 September 1981

TO_CHAR untuk konversi Number


Element Deskripsi

9 Merepresentasikan number

0 Menuliskan angka 0

$ Menempatkan tanda dolar

L Menggunakan local currency simbol

. Nilai desimal

, Nilai ribuan SQL> SELECT ename, TO_CHAR(sal,'$99,999') salary

2 FROM emp;

ENAME SALARY ---------- -------- ALLEN $1,600

WARD $1,250 JONES $2,975


NVL Function

NVL Function berfungsi untuk mengkonversi nilai null ke nilai yang

sebernarnya

Sebagai contoh :

- NVL(comm,0)

- NVL(hiredate,‟01-JAN-81‟)

- NVL(job,‟Job Not Yet‟)

SQL> SELECT ename, sal, comm, (sal*10)+NVL(comm,0) total 2 FROM emp;

ENAME SAL COMM TOTAL ---------- ---------- ---------- ---------- ALLEN 1600 300 16300 WARD 1250 500 13000 JONES 2975 29750 MARTIN 1250 1400 13900

DECODE FUNCTION

Fungsi decode mirip dengan kondisi if then else yang dipakai pada SQL statement.

DECODE(col/expression,search1,result1 [,search2, result2, . . . ] [,default])

contoh penggunaan : kita ingin memberikan perubahan gaji pada karyawan sesuai kondisi dibawah ini

if job = „ANALYST‟ then sal = sal * 1.1 if job = „CLERK‟ then sal = sal * 1.15

if job = „MANAGER‟ then sal = sal * 1.20 else sal = sal;


SQL> SELECT job, sal, 2 DECODE(job,'ANALYST',sal*1.1, 3 'CLERK',sal*1.15,

4 'MANAGER',sal*1.20, 5 sal) NEW_SAL 6 FROM emp;

JOB

SAL

NEW_SAL --------- ---------- ---------- MANAGER 2975 3570 SALESMAN 1250 1250 MANAGER 2850 3420 ANALYST 3000 3300 PRESIDENT

5000 5000 SALESMAN 1500 1500 CLERK 1100 1265 CLERK 950 1092.5


4.2. Multiple Row Function

Pada multiple Row function fungsi menerima input lebih dari satu dan

menghasilkan satu output.

Contoh dari multiple row function adalah : - AVG

- SUM

- COUNT

- MAX

- MIN

- STDDEV

Untuk penjelasan dari fungsi diatas akan ada pada bab selanjutnya.

Latihan SQL :

1. Buatlah query untuk menampilkan tanggal hari ini dan beri

nama kolom Tanggal Sekarang.

2. Tampilkan nomor karyawan, nama, gaji dan gaji yang sudah

ditambah 15% dan simpan dengan nama b2p2.sql.

3. Load file b2p2.sql dan edit nama kolom untuk gaji yang

ditambahkan dengan Gaji Baru.

4. Modifikasi script b2p2.sql tambahkan kolom yang isinya nilai

penambahan gaji dan beri label penambahan.

5. Tampilkan nama karyawan, tanggal masuk kerja dan tanggal gaji

pertama. Gaji pertama didapat setelah bekerja 6 bulan minggu

pertama, beri nama kolom review date. Format review date „Sunday,

the seventh of September, 1985‟.

6. Tampilkan nama karyaan dan lama mereka bekerja dalam

bulan. Beri label kolom dengan MONTHS_WORKED, urutkan

berdasarkan lama bekerja


BAB V

JOIN, SUB-QUERY

Terkadang anda pasti ingin menampilkan data dari beberapa table, sebagai

contoh anda ingin menampilkan nama, nomor departemen dan lokasi kantor.

Untuk mendapatkan itu anda harus menggabungkan table EMP

Dan DEPT untuk menampilkan data tersebut.

Syntak:

SELECT table1.column, table2.column FROM table1, table2 WHERE table1.column1 = table2.column2

Ketika data dari lebih satu table dalam database maka kondisi join

diperlukan untuk menampilkannya. Row dari satu table dapat dijoin

dengan row pada table lain berdasarkan kolom tertentu yang

berhubungan, biasanya primary key dan foreign key.

Untuk menampilkan data dari dua tabel atau lebih digunakan kondisi join

pada WHERE clause. Jika ada n table yang dijoin minimal ada n-1 kondisi

join.

5.1. Kartesian Produk

Kartesian produk terjadi ketika:

- kondisi join tidak diberikan.

- Kondisi join tidak valid.

- Semua row pada table pertama dijoin dengan semua row pada tabel

kedua.

Ketika kondisi join tidak valid atau tidak diberikan dengan benar maka

akan dihasilkan cartesian product, dimana semua kombinasi row akan

ditampilkan. Kartesian produk akan mengenerate jumlah record yang


banyak, dan hasilnya tidak terlalu perlu, kecuali memang anda ingin

menampilkan semua kombinasi yang mungkin dari dua table yang dijoin.

Tipe Join

Ada dua tipe utama dalam join yaitu :

- Equijoin

- Non-Equijoin

Selain tipe join diatas ada beberapa tipe join yang lain yaitu outer join,

Self join dan Set Operator.

Equijoin Untuk menunnjukkan data employee dan nama departemen anda harus

membandingkan nilai pada kolom DEPTNO pada table EMP dengan

kolom DEPTNO pada table DEPT. hubungan antara table EMP dan DEPT

disebut equijoins. Biasanya tipe join ini menggunakan primary key dan

foreign key untuk proses joinnya. Equijoin lebih dikenal sebgai inner join

atau simple join.

SQL> SELECT emp.empno, emp.ename, emp.deptno, 2 dept.deptno, dept.dname 3 FROM emp, dept 4 WHERE emp.deptno = dept.deptno;

EMPNO ENAME DEPTNO DEPTNO DNAME

---------- ---------- ---------- ---------- -------------- 7934 MILLER 10 10 ACCOUNTING

7782 CLARK 10 10 ACCOUNTING 7788 SCOTT 20 20 RESEARCH 7369 SMITH 20 20 RESEARCH


Pada contoh diatas perintah SELECT menampilkan empno, ename dari

table EMP, dan deptno, dname dari table DEPT. Klausa FROM

menunjukkan ada dua table yang diakses untuk menampilkan data

tersebut. Dan kondisi WHERE bagaimana kedua table harus dijoin. Pada

kondisi diatas kedua table akan dijoin berdasarkan kolom DEPTNO.

Anda harus menggunakan nama table disertai kolom untuk

menghindari ambiguitas.

Menggunakan table Alias

Menggunakan nama table untuk mengambil kolom akan

membutuhkan waktu penulisan jika nama table sangat panjang. Untuk

memudahkan oracle menyediakan table alias untuk menggantikan nama

table.

SQL> SELECT a.empno, a.ename, a.deptno, 2 b.deptno, b.dname 3 FROM emp a, 4 dept b 5 WHERE a.deptno = b.deptno;

EMPNO ENAME DEPTNO DEPTNO DNAME

---------- ---------- ---------- ---------- -------------- 7934 MILLER 10 10 ACCOUNTING

7782 CLARK 10 10 ACCOUNTING 7788 SCOTT 20 20 RESEARCH 7369 SMITH 20 20 RESEARCH


Non-Equijoin

Kita lihat table EMP dan SALGRADE, kedua table tersebut adalah non-

equijoins, karena tidak ada hubungan secara langsung antara table EMP

dengan Table SALGRADE. Hubungan antara kedua table tersebut adalah

pada nilai kolom SAL pada table EMP adalah antara kolom LOSAL dan

HISAL pada table SALGRADE. Relasi pada tipe ini adalah relasi yang

menggunakan tanda selain sama dengan (=).

SQL> SELECT a.ename, a.sal, b.grade 2 FROM emp a,

3 salgrade b 4 WHERE a.sal BETWEEN b.losal AND b.hisal;

ENAME SAL GRADE ---------- ---------- ---------- KING 5000 5

SCOTT 3000 4 CLARK 2450 4

MILLER 1300 2

Outer Join

Jika ada suatu row tidak memenuhi kondisi join maka row itu tidak akan

dimunculkan. Sebagai contoh jika kita menambah satu

departemen pada table DEPT misal OPERATIONS, maka departemen

OPERATIONS tidak akan muncul dengan query join diatas karena tidak

ada pegawai yang kerja didepartemen itu.

Departemen OPERATIONS dapat dimunculkan dengan menggunakan outer

join. Anda dapat melakukan outer join dengan operator (+).

Self Join

Kadang kita perlu melakukan join dengan table itu sendiri, sebagai contoh

untuk mencari manajer dari setiap pegawai anda membutuhkan join table


EMP dengan dirinya sendiri. Pada kasus ini kondisi join adalah kolom MGR

dan kolom EMPNO.

Group Function

Sebuah group function menerima input sekumpulan nilai dan

menghasilkan satu nilai hasil setiap group.

Beberapa contoh Group Function yang sering diapakai adalah:

Function Deskripsi

AVG([DISTINCT|ALL],n) Nilai rata-rata dari n

COUNT({*|[DISTINCT|ALL]expr}) Jumlah dari row, atau expr

MAX([DISTINCT|ALL]expr) Nilai maksimum dari expr

MIN([DISTINCT|ALL]expr) Nilai minimun dari expr

STDDEV([DISTINCT|ALL]x) Standar deviasi dari n

SUM([DISTINCT|ALL]n) Jumlah dari n

VARIANCE([DISTINCT|ALL]x) Nilai vaiance dari n


Keterangan:

- Option DISTINCT membuat fungsi hanya menggunakan nilai yang

tidak duplikat,sedangkan option ALL akan menggunakan semua

nilai.Default nilainya adalah ALL.

- Semua fungsi kecuali COUNT(*) mengabaikan nilai NULL, untuk

memperhitukan nilai NULL digunakan fungsi NVL.

- Secara implisit oracle akan mengurutkan hasil secara ascending

ketika menggunakan klausa GROUP BY.

- Tipe data dari argumen dapat berupa CHAR,VARCHAR2,

NUMBER atau DATE.

Menggunakan klausa GROUP BY

Ketika menggunakan GROUP BY pada SELECT statemen anda harus

menyakinkan bahwa semua kolom yang tidak termasuk dalam fungsi

group, harus disertakan dalam klausa GROUP BY. Sebagai contoh

SELECT deptno, AVG(sal) FROM emp

GROUP BY deptno

Pada perintah SELECT diatas, kita ingin menampilkan rata-rata gaji dari tiap departemen.

Klausa GROUP BY pada banyak Kolom

Pada suatu saat kita terkadang memerlukan menampilkan data hasil group

dari proses grouping. Sebagai contoh kita ingin menampilkan jumlah gaji

Yang harus dibayar untuk tiap pekerjaan dari tiap depertemen.

Langkah yang harus dilakukan adalah:

Table EMP pertama digroup berdasarkan departemen, kemudian hasil ini

digroup berdasarkan pekerjaan.


SELECT deptno, job, SUM(sal) FROM emp

GROUP BY deptno, job

Kesalahan-kesalahan yang sering terjadi :

Kolom yang tidak termasuk dalam fungsi group dan tidak ditempatkan pada

klausa GROUP BY.

Yang berikutnya adalah menggunakan fungsi group dalam klausa WHERE.

Membatasi hasil GROUP By dengan klausa HAVING

Kita menggunakan klausa HAVING untuk membatasi record mana yang

akan dimunculkan.

Syntak:

SELECT column, group_function FROM table [WHERE conditions ] [GROUP BY group_by_expression ] [HAVING group_condition ] [ORDER BY column ]

contoh kita ingin menampilkan gaji rata-rata dari tiap departemen dimana

maksimum gaji dari tiap departemen lebih dari 2900.

SELECT deptno, AVG(sal) FROM emp

GROUP BY deptno HAVING MAX(sal) > 2900


Sub-Queries

Jika anda ditanya siapa yang mempunyai gaji lebih besar dari Jones? Untuk

menyelesaikan permasalahan ini kita memerlukan dua query. Pertama

kita mencari berapa gaji Jones, yang kedua kita menjari gaji karyawan

yang gajinya lebih besar dari gaji Jones. Kita bisa menggabungkan dua

query diatas menjadi satu query dengan cara menempatkan query

pertama didalam query kedua.

SELECT select_list FROM table WHERE expr operator

(SELECT select list FROM table)

contoh penggunaan sub-queries

SELECT ename FROM emp WHERE sal >

(SELECT sal FROM emp WHERE empno=7566)

Tipe Sub-Queries - Single-row subqueries

- Multiple-row subqueries

- Multiple column subqueries


Single-Row subqueries

Adalah query yanng menghasilkan hanya satu baris dari inner SELECT, dan menggunakan operator perbandingan satu baris (=, > ,

<, <>,<=,=>).

SQL> SELECT ename, job 2 FROM emp

3 WHERE job = 4 ( SELECT job 5 FROM emp 6 WHERE empno = 7369) 7 AND sal > 8 (SELECT sal 9 FROM emp

10 WHERE empno = 7369);

ENAME JOB ---------- --------- ADAMS CLERK JAMES CLERK MILLER CLERK

Pada query diatas, ada tiga blok query. Inner query dijalankan

terlebih dahulu dan menghasilkan job=CLERK dan sal=1100, hasil ini

digunakan oleh outer query untuk mencari data sesua dengan yang

diinginkan.

Dalam sub-queries bisa juga menggunakan group function dan klausa

having.

Sub-queries yang salah

SQL> SELECT empno, ename 2 FROM emp

3 WHERE sal = ( SELECT MIN(sal) 4 FROM emp 5 GROUP BY deptno);

WHERE sal = ( SELECT MIN(sal)


* ERROR at line 3:

ORA-01427: single-row subquery returns more than one row

Multiple Row Sub-Queries

Adalah sub-queries yang menghasilkan data lebih dari satu baris, dan

menggunakan operator perbandingan multiple-row (IN, ANY, ALL). Sebagai

contoh kita ingin menampilkan pegawai yang mempunyai gaji terrendah

untuk masing-masing departemen.

SQL> SELECT empno, ename

2 FROM emp 3 WHERE sal IN (SELECT MIN(sal) 4 FROM emp

5 GROUP BY deptno);

EMPNO ENAME ---------- ----------

7934 MILLER 7369 SMITH 7900 JAMES


multiple-column subqueries

jika kita ingin membandingkan lebih dari satu kolom pada query kita bisa

menggunakan multiple column subqueries.

Sintaks :

SELECT column, column, … FROM table WHERE (column, column, …) IN

(SELECT column, column, … FROM table WHERE condition);


DAFTAR PUSTAKA

1. http://www.oracle-base.com

2. http://rohmad.net

3. http://www.orafaq.com

4. http://asktom.oracle.com

5. Oracle9i Database Performance Tuning Guide and Reference

6. http://download.oracle.com/docs/cd/B10501_01/server.920/a96533/sq

ltrace.htm

http://www.oracle-base.com/

http://rohmad.net/

http://www.orafaq.com/

http://asktom.oracle.com/

http://download.oracle.com/docs/cd/B10501_01/server.920/a96533/sqltrace.htm

http://download.oracle.com/docs/cd/B10501_01/server.920/a96533/sqltrace.htm

Documents

Modul Oracle Basic