Hastinapura

Oracle Database Resources

Subscribe via Email

Menentukan Variable Saat Runtime

Hingga sejauh ini, report yang kita hasilkan melalui berbagai macam jenis query yang telah kita pelajari sebelumnya, sama sekali tidak interaktif. Pengguna menjalankan report tanpa ada pertanyaan mengenai data yang akan ditampilkan. Data yang akan dilaporkan ditentukan telah ditentukan sejak awal pada klausa WHERE yang tetap pada script SQL*Plus. Namun, SQL*Plus memungkinkan kita untuk menciptakan report yang mampu menanyakan kepada user agar memberikan nilai yang mereka inginkan untuk membatasi data-data yang akan ditampilkan.

Report Interaktif

Untuk menciptakan report yang interaktif, kita dapat melekatkan substitution variable di dalam command file atau perintah SQL tunggal. Variable dapat dianggap sebagai tempat menyimpan nilai sementara.

7.1. Substitution Variables

Dalam SQL*Plus, kita dapat menggunakan single ampersand substitution variable (&) untuk menyimpan nilai secara sementara.

Kita dapat menentukan variables dalam SQL*Plus dengan menggunakan perintah-perintah ACCEPT atau DEFINE. ACCEPT membaca baris yang diinputkan user dan menyimpannya didalam variable. DEFINE menciptakan dan menempatkan nilai ke dalam variable.

Contoh Jangkauan Data Terbatas

• Report untuk kwartal saat ini atau jarak tanggal tertentu
• Report pada data yang relevan terhadap user yang memintanya
• Menampilkan personel didalam departemen tertentu

Efek-efek Interaktif Lain

Efek-efek interaktif tidak dibatasi kepada interaksi user secara langsung didalam klausa WHERE. Prinsip yang sama dapat digunakan untuk mendapatkan hasil yang lain, sebagai contoh:

• Secara dinamis mengubah header dan footer
• Memperoleh parameter input dari file dibandingkan dari orang
• Melewatkan nilai dari satu statement SQL kepada statement lainnya

SQL*Plus tidak mendukung cek validasi terhadap input user. Pastikan bahwa permintaan nilai kepada user yang anda tulis sederhana dan tidak rancu.

7.2. Single Ampersand Substitution Variables

Ketika menjalankan report, user seringkali ingin membatasi data yang dihasilkan secara dinamis. SQL*Plus menyediakan fleksibilitas dengan menggunakan user variables. Gunakan single ampersand (&) untuk mengidentifikasi setiap variable didalam SQL statement. Kita tidak perlu mendefinisikan nilai dari setiap variable.

Contoh:

Menciptakan statement untuk meminta user mengisikan nilai kode department saat runtime. Report akan berisi kode, nama dan gaji karyawan.

SELECT id, last_name, salary
FROM employee
WHERE dept_id = &department_number;

Dengan single ampersand, user akan ditanya setiap kali perintah dieksekusi.

SET VERIFY Command

Untuk mengkonfirmasi perubahan pada SQL statement, gunakan perintah SET VERIFY. Mengeset SET VERIFY menjadi ON akan memaksa SQL*Plus untuk menampilkan teks dari perintah sebelumnya dan setelah ia mengganti substitution variable dengan sebuah nilai.

7.2.1. Menentukan Nilai Karakter dan Tanggal dengan Substitution Variables

Kita harus ingat bahwa didalam klausa WHERE, nilai bertipe character dan date harus ditutup dengan tanda petik tunggal. Aturan yang sama juga berlaku untuk substitution variables.

Untuk menghindari mengetik tanda petik tunggal pada saat runtime, tutup variable dengan tanda petik tunggal pada SQL statement itu sendiri.

Contoh:

Tuliskan query untuk menampilkan kode, nama, dan gaji karyawan berdasarkan jabatan yang diinputkan oleh user.

SELECT id, last_name, salary
FROM employee
WHERE title = ‘&job_title’;

Enter value for job_title: Stock Clerk

7.2.2. Menentukan Nama Kolom, Ekspresi, dan Teks saat Runtime

Kita tidak hanya dapat menggunakan substitution variables didalam klausa WHERE pada SQL statement, namun variable-variable ini juga dapat digunakan untuk menggantikan nama kolom, ekspresi dan teks.

Contoh:

Menampilkan kode, dan kolom-kolom lainnya, dan kondisi tertentu dari sebuah penjualan. Cobalah beberapa variasi dari nama kolom dan kondisi untuk mempelajari hasilnya.

SELECT id, &column_name
FROM orders
WHERE &condition;

Enter value for column_name: total
Enter value for condition: payment_type=’CASH’

SELECT id, &column_name
FROM orders
WHERE &condition;

Enter value for column_name: date_ordered
Enter value for condition: total > 300000

Jika kita tidak menginputkan nilai untuk substitution variable, maka kita akan menerima pesan kesalahan ketika kita mengeksekusi perintah diatas.

7.3. Mendefinisikan User Variables

Kita dapat mendefinisikan user variables sebelum mengeksekusi perintah SELECT. SQL*Plus menyediakan dua perintah untuk mendefinisikan dan mengeset user variables. DEFINE dan ACCEPT.

Sintaks

ACCEPT variable [datatype] [FORMAT][PROMPT text][HIDE]

Catatan

Jangan mengawali SQL*Plus substitution parameter dengan ampersand (&) ketika mereferensikan substitution parameter didalam perintah ACCEPT.

Aturan

• Perintah-perintah ACCEPT dan DEFINE keduanya akan menciptakan variable jika belum ada; perintah-perintah ini secara otomatis mendefinisikan ulang variable jika telah ada.
• Ketika menggunakan perintah DEFINE, gunakan tanda petik tunggal (‘ ‘) untuk menutup string yang berisi space yang dilekatkan
• Gunakan perintah ACCEPT untuk:

• Memberikan prompt yang dapat diatur ketika menerima input dari user. Jika tidak, maka kita akan melihat prompt default “Enter the value for variable.”
• Secara eksplisit definisikan variable bertipe data NUMBER dan DATE
• Sembunyikan input user untuk alasan keamanan

Contoh:

Menampilkan kode dan nama daerah untuk nama departemen tertentu. Ciptakan file skrip dengan nama 17prompt.sql, dan gunakan perintah ACCEPT untuk menanyakan user dengan pesan yang kita atur sendiri.

SET ECHO OFF
ACCEPT p_dname PROMPT ‘Provide the department name: ‘
SELECT d.name, r.id, r.name “REGION NAME”
FROM department, region
WHERE d.region_id = r.id
AND UPPER(d.name) LIKE UPPER (‘%p_dname%’)
/
SET ECHO ON

START 17prompt
Provide the department name: sales

SET ECHO Command

Variable ECHO mengontrol apakah perintah-perintah START dan @ akan menampilkan setiap perintah di dalam command file setiap perintah dieksekusi. Mengeset variable ECHO menjadi ON menampilkan perintah, dan mengesetnya menjadi OFF menghilangkannya.

Variable dedefinisikan hingga kita:

• Mengetikkan perintah UNDEFINE terhadap variable
• Keluar dari SQL*Plus

Ketika kita melakukan undefine terhadap variable, kita dapat memverifikasi perubahannya dengan perintah DEFINE. Ketika kita keluar dari SQL*Plus, variable yang telah didefinisikan selama session tersebut akan hilang. Untuk mendefinisikan variable-variable tersebut untuk setiap session, kita dapat mengubah file login.sql sehingga variable-variable tersebut akan diciptakan saat startup.

Contoh:

Menciptakan variable yang menyimpan nama departemen. Menampilkan nama departemen sesuai nilai yang telah diberikan terhadap variable.

DEFINE dname = sales
DEFINE dname
SELECT name
FROM department
WHERE lower(name) = ‘&dname’;
UNDEFINE dname
DEFINE dname

7.3.1. Melewatkan Nilai Ke Dalam File Script

Sebuah parameter merupakan nilai yang dapat kita lewatkan ke dalam report melalui command line. Untuk menciptakan dan menjalankan report yang memiliki parameter, kita dapat mengikuti langkah-langkah berikut ini:

1. Kita ciptakan file script yang berisi SQL statement
2. Pada SELECT statement, kita gunakan notasi &number pada setiap variable yang diacu
3. Pada saat menjalankan script, sebutkan nama file. Gunakan spasi untuk memisahkan nilai

Contoh:

Ciptakan file script dengan nama 17param.sql, untuk menghasilkan report berdasarkan jabatan karyawan. Dibandingkan harus menanyakan jabatan melalui prompt, kita dapat menginputkan jabatan dalam command line ketika kita memanggil file script tersebut.

SET ECHO OFF
SELECT id, last_name, salary
FROM employee
WHERE title = ‘&’
/
SET ECHO ON
START 17param President

Aturan

• Kita dapat menggunakan perintah DEFINE untuk mengasosiasikan nama-nama yang berarti dengan parameter
• Awalan dapat digunakan untuk membedakan nama kolom (tanpa awalan), variable sederhana (sebagai contoh, v_test), dan variable parameter (sebagai contoh, p_name)
• Posisi setiap nilai parameter didalam command line adalah menentukan. Nilai pertama terhubung dengan &1, nilai kedua dengan &2, dan seterusnya
• Report dapat menerima maksimum sembilan parameter yang diberi nama mulai dari &1 sampai dengan &9
• SQL*Plus menahan parameter-parameter report dan nilai-nilainya hingga kita meredefenisikannya ulang, membatalkan definisinya, atau mengakhiri SQL*Plus session, dalam arti kita disconnect atau keluar dari SQL*Plus

Oracle Magazine contains technology strategy articles, sample code, tips, Oracle and partner news, how to articles for developers and DBAs, and more. Oracle (NASDAQ: ORCL) is the world’s largest enterprise software company.

For more information about Oracle, please visit their Web site at http://www.oracle.com. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.

Get Free Oracle Magazine

7.4. Latihan

1. Tampilkan kode, nama belakang dan gaji bulanan pegawai yang bekerja pada departemen tertentu, dengan catatan kode departemen tempat pegawai tersebut bekerja diinputkan secara interaktif melalui keyboard.

SELECT id, last_name, salary
FROM employee
WHERE dept_id = &department_number;

2. Tampilkan kode dan kolom lainnya dari data order, dengan catatan query tersebut dapat ditampilkan dengan kolom lain dan kondisi sesuai keinginan kita secara interaktif melalui keyboard. Setelah itu coba Anda tampilkan kolom kode dan total order khusus untuk order dengan tipe pembayaran CASH dengan menggunakan query yang baru saja Anda buat tersebut. Coba sekali lagi tampilkan kolom kode dan tanggal order khusus untuk order dengan total lebih besar dari 30000.

SELECT id, &column_name
FROM orders
WHERE &condition;

3. Ketikkan script berikut melalui edit buffer.

SET ECHO OFF
ACCEPT p_dname PROMPT ‘Provide the department name : ‘
SELECT d.name, r.id, r.name “REGION NAME”
FROM department d, region r
WHERE d.region_id = r.id
AND UPPER(d.name) LIKE UPPER(’%&p_dname%’)
/
SET ECHO ON

Setelah selesai keluar dari edit buffer, kemudian simpan script tersebut dengan nama MYSCRIPT.SQL melalui SQL prompt dengan perintah :

SAVE FIRSTSCRIPT;

Kemudian jalankan script yang telah Anda buat tersebut dengan perintah :

START FIRSTSCRIPT;    atau   @ FIRSTSCRIPT;

4. Ketikkan dan amatilah urutan perintah-perintah berikut.

DEFINE dname = sales
DEFINE dname

SELECT name
FROM department
WHERE lower(name) = ‘&dname’;

UNDEFINE dname
DEFINE dname

5. Ketikkan script berikut melalui edit buffer.

SET ECHO OFF
SELECT id, last_name, salary
FROM employee
WHERE title = ‘&1′
/
SET ECHO ON

Setelah selesai keluar dari edit buffer, kemudian simpan script tersebut dengan nama MYSCRIPT.SQL melalui SQL prompt dengan perintah :

SAVE SECONDSCRIPT;

Kemudian jalankan script yang telah Anda buat tersebut dengan perintah :

START SECONDSCRIPT President

6. Buatlah sebuah script dengan nama THIRDSCRIPT.SQL untuk menampilkan kode pengguna, gabungan nama depan dan nama belakang, dan tanggal pegawai mulai bekerja, untuk pegawai yang mulai bekerja pada range tanggal tertentu dengan catatan range tanggal tersebut diinputkan secara interaktif dari keyboard dengan pertanyaan:

Enter the low date range (DD/MM/YY)     : 09/01/91
Enter the hight date range (DD/MM/YY)    : 09/01/92

SET ECHO OFF
ACCEPT lowdate  PROMPT ‘Enter the low date range (MM/DD/YY)  : ‘
ACCEPT highdate PROMPT ‘Enter the high date range (MM/DD/YY) : ‘
SELECT userid, concat(first_name, last_name) EMPLOYEE, start_date
FROM employee
WHERE start_date BETWEEN TO_DATE(’&lowdate’,'MM/DD/YY’)
AND  TO_DATE(’&highdate’,'MM/DD/YY’)
/ SET ECHO ON

7. Buatlah sebuah script dengan nama FOURTHSCRIPT.SQL untuk menampilkan kode dan nama pelanggan dengan kondisi sesuai nama pelanggan yang diinputkan secara interaktif melalui keyboard, dengan catatan bahwa kondisi tersebut dapat menerima berbagai jenis huruf (case-insensitive), atau kata tertentu yang terdapat pada nama pelanggan tersebut.

SET ECHO OFF
ACCEPT custname PROMPT ‘Please enter the customer’’s name : ‘
SELECT id, name “CUSTOMER NAME”
FROM customer
WHERE LOWER(name) LIKE LOWER(’%&custname’)
/
SET ECHO ON

8. Buatlah sebuah script dengan nama FIFTHSCRIPT.SQL untuk menampilkan gabungan nama depan dan nama belakang sales representative, nama pelanggan dan total order untuk setiap pelanggan tersebut khusus untuk pelanggan yang tinggal pada daerah tertentu.

SET ECHO OFF
COLUMN total FORMAT $999,999,999
COLUMN employee FORMAT A30
COLUMN customer FORMAT A30
ACCEPT regionid PROMPT ‘Please enter a region number : ‘
SELECT CONCAT(e.first_name, e.last_name) EMPLOYEE, c.name CUSTOMER, o.total SALES
FROM employee e, department d, customer c, orders o
WHERE e.id = c.sales_rep_id AND o.customer_id = c.id
AND e.dept_id = d.id AND d.region_id = ®ionid
/
SET ECHO ON

Silahkan melanjutkan membaca pembahasan Data Modelling dan Database Design

Catatan:
Untuk mempraktekkan artikel-artikel mengenai Oracle SQL & PL/SQL Anda dapat mendownload script table disini, lalu jalankan sesuai live demo (browser Anda harus mendukung Flash Player).

Buku ini membahas berbagai macam tip dan trik yang sangat berguna bagi Anda pembaca pengguna database Oracle. Pemula, mahasiswa, programmer, maupun database administrator yang mengharapkan solusi cepat dalam menangani permasalahan-permasalahan dalam penggunaan database Oracle dapat membaca buku ini. Anda tidak harus membaca buku ini secara urut karena buku ini di susun berdasarkan topik permasalahan, dan Anda dapat membaca topik-topik yang sesuai dengan permasalahan yang Anda hadapi. Dapat di beli di toko buku Gramedia, Gunung Agung, Uranus, Karisma dsb. Harga Rp. 24.800,- (157 halaman)

• Fungsi Grup
• Mengontrol Akses User
• Menciptakan Index
• Menciptakan View
• Menciptakan Sequence

Copyright © 2012 Hastinapura. Hosting by MWN
Akbar | Beranda | Berbagi Cerita | Cantik Indonesia | Hangat | Hastinapura | Indra | Kelasik | Langgam | Nunung | Obsesi | Propolis | Rintis | Santap | Serasi | Spektakuler | Soerabaia | Xpresi

Switch to our mobile site