Sunday, May 13, 2012

SQL

Publiser angga Debby Frayudha


a.      SQL
Setelah kita membahas tentang prosedur-prosedur dalam PL/SQL, kali ini akan kita lanjutkan tentang SQL, SQL sangat diperlukan dalam PL/SQL karena query yang digunakan menggunakan query SQL.
Kali ini kita akan mempelajari bagaimana menampilkan data dengan menggunakan perintah-perintah SQL. perintah yang digunakan untuk menampilkan data adalah SELECT.
-          Sintaks Dasar
Perintah SELECT pada Oracle SQL minimal harus terdiri dari SELECT dan FROM.
SELECT [DISTINCT] {*, column [alias], …}FROM table;
   ü  SELECT                     : Menampilkan paling tidak satu kolom
ü  DISTINCT                  : Menghilangkan nilai duplikat
ü  *                                  : Menampilkan semua kolom
ü  Column                       : Menampilkan kolom tertentu
 ü  Alias                            : Memberikan judul lain kolom
 ü  FROM table                : Menunjukkan nama tabel asal
-          Penulisan Perintah SQL
·         Perintah-perintah SQL dapat ditulis dalam satu baris atau lebih, namun untuk kemudahan sebaiknya setiap klausa ditulis dalam baris yang berbeda
·         Perintah-perintah SQL tidak case-sensitive
·         Gunakan perintah ED untuk mengedit perintah-perintah SQL
-          Menampilkan Seluruh Kolom dan Baris
Menampilkan seluruh kolom dari suatu tabel dapat dilakukan dengan dua cara, yaitu:
  • Menyebutkan seluruh nama kolom
SELECT id, name, region_id FROM department;
  • Menggunakan tanda bintang (*)
SELECT * FROM department;
-          Menampilkan Kolom Tertentu
Menampilkan kolom tertentu dapat dilakukan dengan cara menyebutkan nama kolom yang ingin ditampilkan datanya. Contoh berikut ini menunjukkan bagaimana cara menampilkan kolom tertentu dari tabel Department.
SELECT name FROM department;
-          Operasi Aritmatika
Perhitungan dapat dilakukan pada perintah SQL dengan menggunakan ekspresi aritmatika, yang dapat mengandung nama-nama kolom, nilai-nilai numerik yang teta, dan operator-operator aritmatika.
+          : Penjumlahan
-
          : Penguranan
*
         : Perkalian
/
          : Pembagian
  • Operator perkalian dan pembagian memiliki prioritas di atas penjumlahan dan pengurangan
  • Operator dengan prioritas yang sama di evaluasi dari kiri ke kanan
  • Tanda kurung dapat digunakan untuk memaksa prioritas evaluasi dan memperjelas perintah
Contoh:
  • Menampilkan nama belakang dan menghitung gaji tahunan karyawan
SELECT last_name, salary*12 FROM employee;
  • Menampilkan nama belakang, gaji bulanan, dan gaji tahunan karyawan. Gaji tahunan merupakan gaji bulanan di kali dengan 12, ditambah dengan bonus 1.000.000
SELECT last_name, salary, salary*12+1000000 FROM salary;
  • Menampilkan nama belakang, gaji bulanan, dan gaji tahunan karyawan. Gaji tahunan merupakan gaji bulanan ditambah dengan bonus 1.000.000, di kali dengan 12
SELECT last_name, salary, 12*(salary+1000000) FROM employee;
-          Operator Penggabungan
Kita dapat menggabungkan satu kolom dengan lainnya, ekspresi aritmatika, atau nilai tetap untuk menciptakan ekspresi karakter menggunakan operator penggabungan (||). Kolom-kolom pada setiap sisi dari operator di kombinasikan untuk menghasilkan satu output kolom tunggal.
Contoh:
Menampilkan nama lengkap karyawan dengan judul Karyawan.
SELECT first_name||last_name AS “Karyawan” FROM employee;
Menampilkan nama lengkap dan jabatan karyawan dengan judul Karyawan. Dalam contoh ini antara nama depan dan nama belakang di pisahkan spasi, dan antara nama lengkap dan jabatan di pisahkan koma.
SELECT first_name||’ ‘||last_name||’, ‘||title AS “Karyawan” FROM employee;
-          Manajemen Nilai Null
Null adalah nilai yang tidak tersedia, tidak diberikan, tidak diketahui, atau tidak dapat digunakan. Null tidak sama dengan nol atau spasi. Nol adalah angka, dan spasi adalah karakter. Kolom dapat mengandung nilai null, kecuali kolom yang telah didefinisikan sebagai NOT NULL atau PRIMARY KEY ketika tabel diciptakan.
-          Nilai Null dalam Ekspresi Aritmatika
Jika nilai kolom dalam sebuah ekspresi adalah null, maka hasilnya juga null. Sebagai contoh, jika kita berusaha melakukan pembagian terhadap nol, maka akan terjadi kesalahan. Namun jika kita membagi terhadap null, maka hasilnya adalah null.
Contoh
Menampilkan nama belakang, gaji, jabatan, dan hasil perhitungan komisi.
SELECT last_name, title, salary, salary*commission_pct/100 KOMISI FROM employee;
-          Fungsi NVL
Fungsi NVL digunakan untuk mengkonversi nilai null menjadi nilai yang kita kehendaki. Sintaks fungsi NVL adalah sebagai berikut:
NVL (expr1, expr2)
expr1: nilai sumber atau ekspresi yang mungkin bernilai null
expr2: nilai target hasil konversi null
Kita dapat menggunakan fungsi NVL untuk mengkonversi tipe data apapun, namun hasilnya akan selalu sama dengan tipe data dari expr1.
Tipe data dan contoh konversi:
  • NUMBER
    • NVL (kolom_angka,9)
  • DATE
    • NVL (kolom_tanggal,’01-JAN-99′)
  • CHAR dan VARCHAR2
    • NVL (kolom_karakter,’Tidak tersedia’)
Contoh:
Berikut ini adalah modifikasi dari query sebelumnya, namun nilai null yang mungkin dihasilkan oleh prosentase komisi dalam hasil perhitungan komisi dikonversi menjadi nol.
SELECT last_name, title, salary, salary*NVL(commission_pct,0)/100 KOMISI FROM employee;
-          Mencegah Tampilnya Nilai Duplikat
Tampilan default dari query adalah seluruh baris termasuk baris-baris duplikat (dobel).
Contoh:
Menampilkan seluruh nama departemen dari tabel DEPARTMENT.
SELECT name FROM department;
-          Penggunaan Keyword DISTINCT
Baris-baris duplikat hasil query dapat dikurangi dengan menggunakan keyword DISTINCT dan SELECT.
Contoh:
Menampilkan nama departemen secara unik (tidak ada nilai duplikat).
SELECT DISTINCT name FROM department;
Menampilkan kombinasi-kombinasi berbeda dari jabatan dan kode departemen.
SELECT DISTINCT title, dept_id FROM employee;
-          Menampilkan Struktur Tabel
Dalam SQL*Plus kita dapat menampilkan struktur dari suatu tabel dengan menggunakan perintah DESCRIBE. Perintah ini menghasilkan nama kolom, tipe data, dan kolom mana yang harus berisi data (NOT NULL).
DESC[RIBE] tablename
tablename: nama dari tabel, view atau synonym yang boleh diakses user
Contoh:
Menampilkan struktur tabel DEPARTMENT.
DESCRIBE department;
atau
DESC department;
Name: nama kolom
Null?: menunjukkan apakah suatu kolom harus berisi data
Type: tipe data dari kolom
-          Tipe Data
  • NUMBER(p,s)
Digit maksimum p dan s angka dibelakang koma
  • VARCHAR2(s)
Tipe data karakter dengan panjang bervariasi (maksimum 2000)
  • CHAR(s)
Tipe data karakter dengan panjang tetap
  • DATE
Tipe data tanggal antara 1 Januari 4217 BC dan 4712 AD
-          Mengurutkan Data
Kita dapat menampilkan data secara urut berdasarkan satu atau beberapa kolom dengan menambahkan klausa ORDER BY pada perintah SELECT yang telah kita pelajari sebelumnya.
SELECT [DISTINCT] {*, column [alias], . . .} FROM table [ORDER BY {column, expr} [ASC|DESC]];
  • ORDER BY   : menentukan dasar urutan data yang akan ditampilkan
  • ASC                : menampilkan data secara urut menaik (ascending). Ini merupakan default pengurutan data
  • DESC              : menampilkan data secara urut menurun (descending)
-          Default Pengurutan Data
Apabila kita tidak menuliskan klausa ASC atau DESC dalam klausa ORDER BY, maka proses pengurutan dilakukan secara ascending (default). Berikut ini hal-hal yang perlu diketahui mengenai pengurutan data.
  • Nilai-nilai numerik ditampilkan dengan nilai terkecil terlebih dahulu, contoh 1-999
  • Nilai-nilai tanggal ditampilkan dengan nilai paling awal terlebih dahulu, contoh 01-JAN-92 sebelum 01-JAN-95
  • Nilai-nilai karakter ditampilkan secara alphabet, contoh A pertama dan Z terakhir
  • Nilai-nilai null ditampilkan terakhir dalam urutan ascending dan pertama dalam urutan descending
Contoh:
Menampilkan data karyawan urut berdasarkan nama belakang.
SELECT last_name, dept_id, hire_date FROM employee ORDER BY last_name;
Contoh diatas dapat dimodifikasi sehingga urut berdasarkan nama belakang secara menurun (descending) dengan menambahkan klausa DESC.
SELECT last_name, dept_id, hire_date FROM employee ORDER BY last_name DESC;
-          Mengurutkan Data Menggunakan Alias Kolom
Kita dapat menggunakan alias kolom dalam klausa ORDER BY. Fitur ini tersedia sejak Oracle 7 Release 7.0.16.
Contoh
Menampilkan data karyawan urut berdasarkan nama belakang.
SELECT last_name EMPLOYEE, hire_date FROM employee ORDER BY EMPLOYEE DESC;
-          Mengurutkan Data Berdasarkan Posisi Kolom
Metode lain untuk mengurutkan data adalah berdasarkan posisi kolom. Hal ini khususnya berguna jika mengurutkan berdasarkan ekspresi yang panjang. Daripada harus mengetikkan ekspresi yang panjang, kita dapat menentukan posisi kolom dalam SELECT.
Contoh:
Menampilkan data karyawan urut berdasarkan gaji tahunan.
SELECT last_name, salary*12 FROM employee ORDER BY 2;
-          Mengurutkan Data Berdasarkan Beberapa Kolom
Kita dapat mengurutkan hasil query berdasarkan beberapa kolom. Batasannya adalah jumlah kolom dalam tabel.
Contoh
Menampilkan data karyawan urut secara menaik (ascending) berdasarkan kode departemen, kemudian urut secara menurun (descending) berdasarkan gaji.
SELECT last_name, salary FROM employee ORDER BY dept_id, gaji DESC;

publisher Angga Debby Frayudha

Share:

EMANIPULASI DATA DENGAN PL/SQL

Publiser angga Debby Frayudha

Manipulasi data menggunakan PL/SQL
Untuk membuat perubahan data pada table, digunakan perintah DML sebagai berikut :
·         INSERT
·         UPDATE
·         DELETE
·         MERGE

Menyisipkan Data
Berikut contoh-contoh program untuk menyisipkan data pegawai baru ke dalam table employees

Pada saat menyisipkan baris data ke dalam table yang dicantumkan pada blok PL/SQL, kita bisa :
·         Menggunakan fungsi sql, semisal user dan sysdate
·         Men-generate primary key dengan menggunakan sequences
·         Menurunkan nilai dalam blok pl/sql
·         Menambahkan kolom yang memiliki nilai default
Mengubah data
Berikut contoh program untuk menambah gaji semua pegawai yang pekerjaannya adalah stock clerks
Pada klausa SET, bagian sebelah kiri selalu kolom/field pada table, sedangkan pada bagian kanan dapat merupakan ekspresi yang terdiri dari kolom table, atau variable local PL/SQL klausa WHERE digunakan untuk menetukan baris yang harus diubah datanya, jika tidak ada baris yang dimodifikasi, tidak ada error yang ditampilkan
penandaan pada program PL/SQL selalu menggunakan := sedangkan penandaan pada kolom sql selalu menggunakan =
Menghapus data
Contoh berukut menghapus baris data pada departemen 10.
Jika klausa WHERE tidak digunakan pada UPDATE, maka semua baris data pada table akan dihapus
Menggabung (Merge) baris
Menyisipkan atau mengubah baris data yang ada dalam table copy_emp untuk dicocokkan dengan data yang ada di table employees. Jika data yang ada di EMPLOYEES sudah ada di copy_emp, maka data di copy_emp tinggal disesuaikan dengan sisi data yang ada di employees. Jika data yang ada di employees belum ada di copy_emp, maka data tersebut disisipkan sebagai data baru ke dalam table copy_emp.

DECLARE
v_empno employees.employee_id%type :=100;
BEGIN
MERGE INTO copy__emp c
USING employees e
ON (e.employee_id = v_empno)
WHEN MATCHED THEN
UPDATE SET
c.first_name = e.first_name,
c.last_name = e.last_name,
c.email = e.email,
. . . . . ..
WHEN NOT MATCHED THEN
INSERT VALUES (e.employee_id, e.first_name, e.last_name, . . . . . . .. . . . . . . . );
END;

 

Aturan Penamaan :
·         Berfunsi untuk menghindari ambiguitas pada klausa where
·         Kolom table dan nama variable harus memiliki nama yang unik tidak boleh sama
·        

DECLARE
v_last_name VARCHAR2(25);

 
Untuk menghindari ambiguitas, missal variable local diawali dengan v_, semisal :



tidak mungkin terjadi ambiguitas pada klausa INTO, ini dikarenakan yang terlibat pada klausa INTO pasti variable PL/SQL, dan bukan nama field dari table.

publisher Angga Debby Frayudha
Share:

STRUKTUR KONTROL SQL



a.      Struktur Control
1.      IF . . . . .THEN
IF . . . . .THEN merupakan struktur control program yang tersederhana, yang digunakan untuk mengontrol agar dilakukan tindakan tertentu bila dipenuhi kondisi tertentu pula.
-          Struktur sintaks untuk 1 kondisi
IF kondisi THEN
            Pernyataan;
END IF;
-          Struktur sintaks untuk 1 kondisi
IF kondisi1 THEN
            Pernyataan1;
ELSE
            Pernyataan2;
END IF;
-          Struktur sintaks untuk 1 kondisi
IF kondisi1 THEN
            Pernyataan1;
ELSE IF kondisi2 Then
            Pernyataan2;
ELSE
            Pernyataan3;
END IF;

2.      FOR-LOOP
FOR-LOOP termasuk struktur control perulangan, maksudnya ialah dengan LOOP-END LOOP, tindakan akan diulang sampai tercapai kondisi tertentu.
Struktur sintaks LOOP
FOR variable_loop IN nilai_awal…..nilai_akhir
LOOP
      Pernyataan1;
      Pernyataan2;
      ……….
END LOOP

3.      WHILE…LOOP
Struktur WHILE…LOOP hampir sama dengan loop.
Struktur sintaksnya sebagai berikut
WHILE kondisi_x
LOOP
      Pernyataan1;
      Pernyataan2;
      ……….
END LOOP
b.      Contoh beberapa Perintah SQL dalam PL/SQL
·         Mengambil isi data dari table menggunakan perintah SELECT
·         Membuat perubahan pada isi table dengan perintah DML
·         Control transaksi menggunakan perintah COMMIT, ROLLBACK, atau SAVEPOINT
·         Menetukan hasil perintah DML dengan atribut cursor secara implicit

v  Mengambil isi data dari table menggunakan perintah SELECT
Menampilkan data dengan menggunakan PL/SQL
Gunakan perintah SELECT untuk mengambil  data dari table
§  select_list  berisi sedikitnya satu kolom pada table, atau tanda bintang (*) untuk semua baris, juga bisa berisi ekspresi, fungsi baris atau fungsi group.
§  variable_name adalah variable scalar yang menerima nilai
§  record_name nama RECORD PL/SQL yang menerima nilai
§  table_nama table yang datanya diambil
§  condition terdiri dari nama kolom, ekspresi, konstanta, operator pembandingan, termasuk variable PL/SQL dan konstanta
petunjuk menampilkan data menggunakan PL/SQL
·         akhiri setiap perintah SQL dengan tanda titik koma (;)
·         klausa INTO diperlukan pada saat mencantumkan perintah sql pada program perintah select yang ditulis dalam PL/SQL
·         klausa where bersifat optional dan dapat digunakan untuk menetukan variable input, konstanta, literal atau ekspresi PL/SQL
·         Pastikan untuk mencantumkan jumlah variable yang sama antara yang ditulis setelah SELECT dan pada bagian yang ditulis setelah INTO. Pastika type data yang digunakan oleh variable tersebut compatible dengan type data dari kolom/field yang diambil
Perintah SELECT pada PL/SQL
·         Membutuhkan klausa INTO
·         Queri harus mengembalikan satu nilai dan hanya satu nilai saja untuk tiap variable
Query hanya mengembalikan satu baris
PL/SQL akan menangani eror yang disebabkan oleh suatu kesalahan. Kesalahan ini bisa ditrap dalam bagian exception pada blok dengan menggunakan NO_DATA_FOUND jika data tidak ditemukan dan TOO_MANY_ROWS jika menerima lebih dari satu nilai pada SQL. Berikut diberikan cotoh program untuk menampilkan tanggal mulai bekerja pegawai (hire date) dan gaji pegawai
Contoh
Meskipun setelah clausa SELECT kita bisa menampilkan ekspresi, tapi variable Boolean tidak dapat ditulis di sini
Berikut program untuk menjumlah gaji semua pegawai yang berada di departmen 60
Kita tidak diperbolehkan mendefinisikan variable dengan menggunakan %TYPE untuk menerima hasil dari fungsi SUM atau COUNT. Sebagai contoh. variable v_sum_sal di atas tidak boleh dideklarasikan sebagai employes.salary%TYPE
Share: