ujuan
PL / SQL adalah teknologi yang memungkinkan SQL Oracle untuk bertindak seperti bahasa prosedural. Menjelang akhir hari ini, Anda harus
- Memiliki pemahaman dasar PL / SQL
- Memahami fitur yang membedakan PL / SQL SQL dari standar
- Memiliki pemahaman tentang elemen dasar dari sebuah program / PL SQL
- Mampu menulis sederhana PL / SQL program
- Memahami bagaimana kesalahan ditangani dalam PL / SQL program
- Menyadari bagaimana PL / SQL digunakan di dunia nyata
Pengenalan
Salah satu cara untuk memperkenalkan PL / SQL untuk memulai dengan menjelaskan standar Structured Query Language, atau SQL. SQL adalah bahasa yang memungkinkan para pengguna database relasional untuk berkomunikasi dengan database secara langsung. Anda dapat menggunakan perintah SQL untuk query database dan mengubah tabel dalam database. Ketika Anda menulis pernyataan SQL, Anda mengatakan database apa yang ingin Anda lakukan, bukan bagaimana melakukannya. The query optimizer memutuskan dengan cara yang paling efisien untuk mengeksekusi pernyataan Anda. Jika Anda mengirim serangkaian pernyataan SQL ke server di SQL standar, server mengeksekusi mereka satu demi satu dalam urutan kronologis.
PL / SQL adalah bahasa prosedural’s Oracle, melainkan terdiri dari standar bahasa SQL dan berbagai macam perintah yang memungkinkan Anda untuk mengontrol eksekusi pernyataan SQL sesuai dengan kondisi yang berbeda. PL / SQL juga dapat menangani error runtime. Pilihan seperti loop dan JIKA ... MAKA memberikan laporan PL / SQL kekuatan-bahasa pemrograman generasi ketiga. PL / SQL memungkinkan Anda untuk menulis interaktif, ramah-program pengguna yang dapat melewati nilai-nilai ke dalam variabel. Anda juga dapat menggunakan beberapa paket yang telah ditetapkan, salah satu yang dapat menampilkan pesan kepada pengguna.
18 hari meliputi fitur-fitur utama dari PL / SQL:
- Pemrogram dapat mendeklarasikan variabel yang akan digunakan selama proses pernyataan.
- Pemrogram dapat menggunakan rutin penanganan kesalahan program mencegah dari batal tiba-tiba.
- Pemrogram dapat menulis program interaktif yang menerima masukan dari pengguna.
- Membagi fungsi pemrogram dapat menjadi blok-blok logis kode. Teknik pemrograman modular mendukung fleksibilitas dalam pengembangan aplikasi.
- pernyataan SQL dapat diproses secara simultan untuk performa lebih baik secara keseluruhan.
Jenis data dalam PL / SQL
Sebagian besar data jenis ini jelas mirip, tetapi pelaksanaan masing-masing memiliki penyimpanan yang unik dan persyaratan pemrosesan internal. Saat menulis PL / SQL blok, Anda akan menyatakan variable, yang harus tipe data yang valid. The subbagian berikut dengan singkat menjelaskan jenis data yang tersedia dalam PL / SQL.
Di PL / SQL Oracle menyediakan subtipe dari tipe data. Sebagai contoh, tipe data NUMBER memiliki subtipe disebut INTEGER. Anda dapat menggunakan subtipe di / Program PL SQL Anda untuk membuat tipe data yang kompatibel dengan tipe data dalam program-program lainnya, seperti program COBOL, terutama jika Anda embedding PL / kode SQL pada program lainnya. Subtipe adalah nama alternatif hanya untuk tipe data Oracle dan karena itu harus mengikuti aturan tipe data yang terkait.
CATATAN: Seperti dalam kebanyakan implementasi dari SQL, sensitivitas kasus bukan merupakan faktor dalam sintaks dari pernyataan. PL / SQL memungkinkan baik huruf besar atau huruf kecil dengan perintah tersebut.
Jenis Data String Karakter
Karakter tipe data string dalam PL / SQL, seperti yang mungkin Anda harapkan, yaitu data jenis umum didefinisikan sebagai memiliki nilai alpha-numeric. Contoh karakter string adalah nama-nama, kode, deskripsi, dan nomor seri yang menyertakan karakter.
toko CHAR tetap-panjang karakter string. Panjang maksimum adalah 32.767 byte CHAR, meskipun sulit membayangkan seperangkat nilai-panjang tetap di meja yang begitu lama.
Sintaks:
CHAR (max_length)
Subtipe: KARAKTER
VARCHAR2 toko variabel-panjang karakter string. Anda biasanya pengguna VARCHAR2 bukan CHAR untuk menyimpan-panjang data variabel, seperti nama seorang individu. Panjang maksimumVARCHAR2 juga 32.767 byte.
Sintaks:
VARCHAR2 (max_length)
Subtipe:, STRING VARCHAR
PANJANG juga menyimpan panjang karakter string-variabel, memiliki panjang maksimum 32760 byte. PANJANG biasanya digunakan untuk menyimpan teks panjang seperti komentar, meskipunVARCHAR2 dapat digunakan juga.
Jenis Data numerik
NOMOR toko semua jenis nomor dalam database Oracle.
Sintaks:
NOMOR (max_length)
Anda dapat menentukan presisi data s 'NOMOR dengan sintaks berikut:
NOMOR (presisi, skala)
Subtipe:, DECIMAL, DOUBLE PRECISION Desember, INTEGER, INT, numerik, REAL, SMALLINT, Float
PLS_INTEGER mendefinisikan kolom yang mungkin berisi bilangan bulat dengan tanda, seperti nomor negatif.
Jenis Data Biner
Jenis data biner yang menyimpan data dalam format biner, seperti grafik atau foto. Ini tipe data termasuk RAW dan LONGRAW.
The DATE Jenis Data
DATE adalah tipe data Oracle berlaku di mana untuk menyimpan tanggal. Ketika anda mendefinisikan suatu kolom sebagai, DATE Anda tidak menetapkan panjang, karena panjang lapangan DATEtersirat. Format tanggal Oracle adalah, misalnya, 01-Oktober-97.
Boolean
Boolean menyimpan nilai-nilai berikut: TRUE, FALSE, dan NULL. Seperti DATE, Boolean tidak memerlukan parameter ketika mendefinisikan sebagai kolom atau tipe data yang variabel.
ROWID
ROWID adalah pseudocolumn yang ada di setiap tabel dalam database Oracle.. Yang ROWID disimpan dalam format biner dan mengidentifikasi setiap baris dalam tabel menggunakan Indeks ROWID s sebagai pointer ke data.
Struktur SQL / Blok PL
PL / SQL adalah bahasa terstruktur-blok, yang berarti bahwa PL / SQL program dibagi dan ditulis dalam blok logika kode. Dalam SQL / PL blok kode, proses seperti manipulasi data atau query dapat terjadi. Bagian-bagian berikut SQL / blok PL dibahas dalam bagian ini:
- Bagian MENYATAKAN berisi definisi dari variabel, dan objek lain seperti konstanta dan kursor. Bagian ini adalah bagian opsional SQL / blok PL.
- Bagian PROSEDUR berisi perintah bersyarat dan pernyataan SQL dan adalah tempat blok dikendalikan. bagian ini adalah wajib hanya sebagian SQL / blok PL.
- Bagian PENGECUALIAN mengatakan PL / blok SQL bagaimana menangani kesalahan tertentu dan-yang didefinisikan pengguna pengecualian. Bagian ini adalah bagian opsional SQL / blok PL.
CATATAN: blok adalah unit logis dari PL / SQL code, mengandung setidaknya bagian yang opsional PROSEDUR dan MENYATAKAN dan bagian PENGECUALIAN.
Berikut adalah struktur dasar dari PL / SQL block:
Sintaks:
BEGIN - opsional, menunjukkan awal MENYATAKAN blok - opsional, definisi variabel BEGIN - wajib, menunjukkan awal bagian PENGECUALIAN prosedur - opsional, menunjukkan awal bagian AKHIR pengecualian - wajib, menunjukkan bagian akhir dari prosedur AKHIR - opsional , menunjukkan akhir blok
Perhatikan bahwa hanya bagian-bagian wajib SQL / PL adalah blok BEGIN END kedua dan pertama, yang membentuk bagian PROSEDUR. Tentu saja, Anda akan memiliki laporan di antaranya. Jika Anda menggunakan BEGIN pertama, maka Anda harus menggunakan AKHIR kedua, dan sebaliknya.
Komentar
Apa yang akan program tanpa komentar? bahasa pemrograman menyediakan perintah yang memungkinkan Anda untuk menempatkan komentar dalam kode Anda, dan PL / SQL tidak terkecuali.Komentar setelah setiap baris dalam struktur blok sampel sebelumnya menjelaskan setiap perintah. Komentar diterima di PL / SQL adalah sebagai berikut:
Sintaks:
- Ini adalah komentar satu baris.
/ * Ini adalah banyak baris komentar .* /
CATATAN: PL / SQL secara langsung mendukung Bahasa Manipulasi Data (DML) perintah dan query database. Namun, tidak mendukung Bahasa Kamus Data (DDL) perintah. Anda biasanya dapat menggunakan PL / SQL untuk memanipulasi data dalam struktur database, tetapi tidak untuk memanipulasi struktur-struktur.
MENYATAKAN Bagian
Bagian MENYATAKAN dari sebuah blok PL / SQL kode terdiri dari variabel, konstanta, definisi kursor, dan khusus tipe data. Sebagai SQL / programmer PL, Anda dapat mendeklarasikan semua jenis variabel dalam blok kode Anda. Namun, Anda harus menetapkan tipe data, yang harus sesuai dengan kaidah Oracle dari tipe data tertentu, untuk setiap variabel yang Anda tetapkan. Variabel juga harus sesuai dengan Oracle objek penamaan standar.
Variabel Tugas
Variabel adalah nilai-nilai yang dapat berubah dalam blok / PL SQL. PL / Variabel SQL harus diberi tipe data yang valid pada deklarasi dan dapat diinisialisasi jika perlu. Contoh berikut mendefinisikan suatu set variabel di bagian blok MENYATAKAN:
MENYATAKAN pemilik char (10); tablename char (30); byte nomor (10); hari ini tanggal;
ANALISIS:
Bagian MENYATAKAN blok tidak dapat dijalankan dengan sendirinya. Bagian MENYATAKAN dimulai dengan pernyataan MENYATAKAN. Kemudian variabel individu didefinisikan pada baris yang terpisah.Perhatikan bahwa setiap deklarasi variabel diakhiri dengan titik koma.
Variabel dapat juga diinisialisasi pada bagian MENYATAKAN. Sebagai contoh:
MENYATAKAN char pelanggan (30); fiscal_year nomor (2): = '97 ';
Anda dapat menggunakan simbol: = untuk menginisialisasi, atau menetapkan nilai awal untuk variabel di bagian MENYATAKAN. Anda harus menginisialisasi variabel yang didefinisikan sebagai NOT NULL.
MENYATAKAN char pelanggan (30); fiscal_year nomor (2): TIDAK NULL = '97 ';
ANALISIS:
Klausa NULL TIDAK dalam definisi fiscal_year menyerupai definisi kolom dalam pernyataan CREATE TABLE.
Konstan Tugas
Konstanta didefinisikan dengan cara yang sama bahwa variabel tersebut, tetapi nilai-nilai konstan statis; mereka tidak berubah. Dalam contoh sebelumnya, fiscal_year mungkin konstan.
CATATAN: Anda harus mengakhiri setiap deklarasi variabel dengan titik koma.
Definisi kursor
kursor adalah jenis lain dari variabel dalam PL / SQL. Biasanya ketika Anda memikirkan sebuah variabel, nilai tunggal datang ke pikiran. kursor adalah variabel yang menunjuk ke sebuah baris data dari hasil query. Dalam baris hasil set-ganda, Anda perlu cara untuk menelusuri setiap record untuk menganalisis data. kursor adalah hanya itu. Ketika PL / SQL blok melihat hasil query di blok tersebut, menggunakan kursor menunjuk masing-masing kembali baris. Berikut adalah contoh dari kursor yang ditetapkan dalam PL / SQL block:
INPUT:
MENYATAKAN employee_cursor kursor adalah pilih * dari karyawan;
kursor adalah serupa dengan melihat. Dengan menggunakan loop di bagian PROSEDUR, Anda dapat gulir kursor. Teknik ini ditutupi segera.
The JENIS% Atribut
JENIS% adalah atribut variabel yang mengembalikan nilai kolom tertentu meja. Alih-alih keras-kode tipe data di / SQL Anda blok PL, Anda dapat menggunakan JENIS% untuk menjaga konsistensi data jenis dalam blok kode Anda.
INPUT:
MENYATAKAN employee_cursor kursor adalah emp_id pilih, emp_name dari karyawan; id_num employees.emp_id% TYPE; nama% employees.emp_name JENIS;
ANALISIS:
Id_num variabel tersebut dinyatakan memiliki tipe data yang sama seperti emp_id pada tabel KARYAWAN.% JENIS menyatakan nama variabel memiliki tipe data sama dengan emp_name kolom dalam tabel KARYAWAN.
The ROWTYPE% Atribut
Variabel tidak terbatas pada nilai-nilai tunggal. Jika Anda mendeklarasikan sebuah variabel yang berhubungan dengan kursor ditetapkan, Anda dapat menggunakan% ROWTYPE atribut untuk menyatakan tipe data dari variabel yang harus sama seperti setiap kolom dalam satu baris seluruh data dari kursor. Dalam leksikon Oracle’s atribut ROWTYPE% menciptakan variabel catatan.
INPUT:
employee_cursor kursor MENYATAKAN adalah emp_id pilih, emp_name dari karyawan;% employee_cursor employee_record ROWTYPE;
ANALISIS:
Contoh ini menyatakan sebuah variabel yang disebut employee_record. Atribut ROWTYPE% mendefinisikan variabel ini memiliki tipe data yang sama sebagai seluruh baris data dalamemployee_cursor tersebut. Variabel dideklarasikan menggunakan atribut ROWTYPE% disebut juga variabel agregat.
The ROWCOUNT% Atribut
PL / SQL% mempertahankan atribut ROWCOUNT hitungan baris bahwa laporan SQL di blok tertentu telah diakses di kursor.
INPUT:
MENYATAKAN kursor adalah employee_cursor pilih emp_id, emp_name dari karyawan; : = Employee_cursor records_processed% ROWCOUNT;
ANALISIS:
Dalam contoh ini variabel records_processed menunjukkan jumlah baris yang saat ini PL / SQL blok diakses di employee_cursor tersebut.
PERINGATAN: Hati-hati penamaan konflik dengan nama tabel ketika mendeklarasikan variabel. Misalnya, jika Anda mendeklarasikan variabel yang memiliki nama sama dengan meja yang Anda coba akses dengan PL / SQL code, variabel lokal akan didahulukan dari nama tabel.
Bagian PROSEDUR
Bagian PROSEDUR adalah wajib hanya sebagian SQL / blok PL. Ini bagian dari blok panggilan variabel dan menggunakan cursors untuk memanipulasi data dalam database,. PROSEDUR Bagian adalah utama bagian blok bersyarat mengandung pernyataan dan perintah SQL.
BEGIN … END
Dalam satu blok, pernyataan BEGIN menunjukkan awal prosedur. Demikian pula, pernyataan END menandai akhir prosedur. Contoh berikut menunjukkan struktur dasar bagian PROSEDUR:
Sintaks:
BEGIN terbuka kursor; condition1; statement1; condition2; statement2; condition3; statement3; . . . dekat kursor; AKHIR
Perintah Kontrol kursor
Sekarang bahwa Anda telah belajar bagaimana untuk menentukan kursor dalam SQL / blok PL, Anda perlu tahu bagaimana untuk mengakses kursor didefinisikan. Bagian ini menjelaskan perintah-perintah kontrol kursor dasar: MENYATAKAN, OPEN, FETCH dan CLOSE.
MENYATAKAN
Sebelumnya hari ini Anda belajar bagaimana menentukan kursor di bagian MENYATAKAN blok. Pernyataan MENYATAKAN termasuk dalam daftar perintah kontrol kursor.
BUKA
Sekarang bahwa Anda telah menetapkan kursor Anda, bagaimana Anda menggunakannya? Anda tidak dapat menggunakan buku ini kecuali Anda membukanya. Demikian pula, Anda tidak dapat menggunakan kursor sampai Anda membukanya dengan perintah BUKA. Sebagai contoh:
Sintaks:
BEGIN employee_cursor terbuka; statement1; statement2; . . . AKHIR
FETCH
FETCH Mempopulai variabel dengan nilai-nilai dari kursor. Berikut adalah dua contoh menggunakan FETCH: Satu Mempopulai sebuah variabel agregat, dan variabel individu Mempopulai lainnya.
INPUT:
MENYATAKAN employee_cursor kursor adalah emp_id pilih, emp_name dari karyawan; employee_record% employee_cursor ROWTYPE; BEGIN employee_cursor terbuka; putaran mengambil employee_cursor ke employee_record; loop end; dekat employee_cursor; AKHIR
ANALISIS:
Contoh sebelumnya menjemput baris saat ini kursor ke employee_record variabel agregat. Menggunakan loop untuk gulir kursor. Tentu saja, blok tersebut tidak benar-benar mencapai apa pun.
MENYATAKAN employee_cursor kursor adalah pilih emp_id, emp_name dari karyawan; id_num employees.emp_id% TYPE; nama% employees.emp_name JENIS; BEGIN employee_cursor terbuka; putaran mengambil employee_cursor ke id_num, nama; loop end; dekat employee_cursor; AKHIR
ANALISIS:
Contoh ini menjemput baris kursor sekarang ke dalam id_num variabel dan nama, yang didefinisikan pada bagian MENYATAKAN.
CLOSE
Bila Anda telah selesai menggunakan kursor di blok, Anda harus menutup kursor, seperti biasa menutup buku ketika Anda selesai membacanya. Perintah yang Anda gunakan CLOSE.
Sintaks:
BEGIN employee_cursor terbuka; statement1; statement2; . . . dekat employee_cursor; AKHIR
ANALISIS:
Setelah kursor ditutup, set hasil query itu tidak ada lagi. Anda harus membuka kembali kursor untuk mengakses terkait set data.
Laporan Bersyarat
Sekarang kita sampai ke hal yang baik – laporan bersyarat yang memberi Anda kontrol atas bagaimana Anda SQL laporan diproses. Laporan bersyarat dalam PL / SQL mirip dengan dalam bahasa generasi ketiga yang paling.
JIKA … MAKA
The JIKA ... MAKA pernyataan mungkin adalah pernyataan bersyarat paling akrab kebanyakan programmer. The JIKA ... MAKA pernyataan menentukan kinerja tindakan tertentu jika kondisi tertentu terpenuhi. Struktur dari IF ... THEN pernyataan adalah sebagai berikut:
Sintaks:
Condition1 JIKA KEMUDIAN statement1; END IF;
Jika Anda memeriksa dua kondisi, Anda dapat menulis laporan Anda sebagai berikut:
Sintaks:
Condition1 JIKA KEMUDIAN statement1; ELSE statement2; END IF;
Jika Anda memeriksa lebih dari dua kondisi, Anda dapat menulis pernyataan Anda sebagai berikut:
Sintaks:
Condition1 JIKA KEMUDIAN statement1; ELSIF condition2 KEMUDIAN statement2; ELSE statement3; END IF;
ANALISIS:
Contoh terakhir menyatakan: Jika condition1 terpenuhi, kemudian melakukan statement1, jika condition2 terpenuhi, kemudian melakukan statement2; dinyatakan, melakukan statement3. JIKA ... MAKA laporan mungkin juga diulang dalam laporan lain dan / atau looping.
Loop
Loop dalam SQL / blok PL memungkinkan laporan di blok tersebut untuk diproses secara terus menerus selama kondisi tertentu ada. Ada tiga jenis loop.
LOOP adalah infinite loop, yang paling sering digunakan untuk gulir kursor. Untuk mengakhiri jenis loop Anda harus menentukan kapan harus keluar. Sebagai contoh, di bergulir kursor Anda akan keluar loop setelah baris terakhir di kursor telah diproses:
INPUT:
BEGIN employee_cursor terbuka; LOOP FETCH employee_cursor ke employee_record; EXIT KETIKA% employee_cursor NOTFOUND; statement1; . . . END LOOP; dekat employee_cursor; END,
NOTFOUND% adalah atribut kursor yang mengidentifikasi bila tidak ada banyak data yang ditemukan di kursor. Pintu keluar contoh sebelumnya loop bila tidak ada banyak data yang ditemukan. Jika Anda menghilangkan pernyataan dari loop, maka loop akan terus selamanya.
The WHILE-LOOP mengeksekusi perintah sementara kondisi tertentu adalah TRUE. Bila kondisi ini tidak lagi benar, loop kembali ke laporan berikutnya.
INPUT:
MENYATAKAN payment_cursor kursor adalah pilih cust_id, pembayaran, total_due dari payment_table; cust_id% payment_table.cust_id JENIS; pembayaran% payment_table.payment JENIS; total_due% payment_table.total_due JENIS; BEGIN payment_cursor terbuka; <Pembayaran total_due LOOP WHILE FETCH payment_cursor ke cust_id, pembayaran, total_due; EXIT KETIKA% payment_cursor NOTFOUND; masukkan ke underpay_table nilai (cust_id, 'masih berhutang'); END LOOP; dekat payment_cursor; END;
ANALISIS:
Contoh sebelumnya menggunakan WHILE-LOOP untuk menggulir kursor dan untuk menjalankan perintah dalam loop selama kondisi pembayaran total_due <terpenuhi.
Anda dapat menggunakan FOR-LOOP di blok sebelumnya untuk secara implisit mengambil baris kursor sekarang ke dalam variabel ditetapkan.
INPUT:
MENYATAKAN payment_cursor kursor adalah pilih cust_id, pembayaran, total_due dari payment_table; cust_id% payment_table.cust_id JENIS; pembayaran% payment_table.payment JENIS; total_due% payment_table.total_due JENIS; MULAI payment_cursor terbuka; Pay_rec UNTUK DI payment_cursor LOOP JIKA pay_rec.total_due <pay_rec.payment KEMUDIAN masukkan ke underpay_table nilai (pay_rec.cust_id, 'masih berhutang'); END IF; END LOOP; dekat payment_cursor; END;
ANALISIS:
Contoh ini menggunakan FOR-LOOP untuk menggulir kursor. Para FOR-LOOP adalah melakukan suatu FETCH implisit, yang dihilangkan saat ini. Juga, perhatikan bahwa atribut NOTFOUND% telah dihilangkan. Atribut ini tersirat dengan FOR-LOOP, karena itu, ini dan contoh sebelumnya menghasilkan hasil dasar yang sama.
Bagian PENGECUALIAN
Bagian PENGECUALIAN adalah bagian opsional dari setiap SQL / blok PL. Jika bagian ini dihilangkan dan kesalahan ditemui, blok akan dihentikan. Beberapa kesalahan yang mungkin tidak ditemui membenarkan penghentian segera blok, sehingga bagian PENGECUALIAN dapat digunakan untuk menangani error tertentu atau pengecualian didefinisikan pengguna secara teratur. Pengecualian dapat didefinisikan pengguna, meskipun banyak pengecualian yang telah ditetapkan oleh Oracle.
Budidaya Pengecualian
Pengecualian dibesarkan di blok dengan menggunakan perintah RAISE. Pengecualian dapat ditingkatkan secara eksplisit oleh pemrogram, sedangkan kesalahan database internal secara otomatis, atau secara implisit, dibangkitkan oleh database server.
Sintaks:
MULAI MENYATAKAN exception_name PENGECUALIAN; BEGIN IF kondisi THEN RAISE exception_name; END IF; PENGECUALIAN KETIKA KEMUDIAN exception_name pernyataan; AKHIR; AKHIR;
ANALISIS:
Blok ini menunjukkan dasar-dasar secara eksplisit meningkatkan pengecualian. Pertama exception_name ini dideklarasikan dengan menggunakan pernyataan PENGECUALIAN. Pada bagianPROSEDUR, kecuali ditinggikan menggunakan RAISE jika kondisi tertentu terpenuhi. The RAISE kemudian referensi bagian PENGECUALIAN blok, di mana tindakan yang tepat diambil.
Penanganan Pengecualian
Contoh sebelumnya ditangani pengecualian di bagian PENGECUALIAN blok. Kesalahan dapat dengan mudah ditangani di PL / SQL, dan dengan menggunakan pengecualian, PL / SQL block dapat terus berjalan dengan kesalahan atau mengakhiri anggun.
Sintaks:
PENGECUALIAN KETIKA KEMUDIAN exception1 statement1; KETIKA KEMUDIAN exception2 statement2; KETIKA ORANG LAIN KEMUDIAN statement3;
ANALISIS:
Contoh ini menunjukkan bagaimana bagian PENGECUALIAN akan terlihat jika Anda memiliki lebih dari satu pengecualian. Contoh ini mengharapkan dua pengecualian (exception1 danexception2) ketika menjalankan blok ini. KETIKA ORANG LAIN memberitahu statement3 untuk mengeksekusi jika ada pengecualian lain terjadi pada saat blok sedang diproses. KETIKA ORANG LAIN memberi Anda kontrol atas setiap kesalahan yang mungkin terjadi di blok tersebut.
Mengeksekusi SQL / Blok PL
PL / SQL biasanya dibuat menggunakan editor host dan dieksekusi seperti file skrip SQL normal. PL / SQL menggunakan titik koma untuk mengakhiri setiap pernyataan di blok – dari tugas variabel untuk perintah manipulasi data. Garis miring (/) terutama terkait dengan file script SQL, tapi PL / SQL juga menggunakan garis miring untuk mengakhiri blok dalam sebuah file script. Cara termudah untuk memulai PL / SQL blok dengan mengeluarkan perintah START, disingkat STA atau @.
Anda PL / SQL script file akan terlihat seperti ini:
Sintaks:
/ * File ini disebut * proc1.sql / BEGIN MENYATAKAN ... BEGIN ... laporan; ... PENGECUALIAN ... END; END; /
Anda jalankan Anda PL / SQL script file sebagai berikut:
SQL> mulai proc1 atau SQL> sta proc1 atau SQL> @ proc1
CATATAN: PL / SQL file script dapat dijalankan dengan menggunakan perintah START atau karakter @. PL / SQL file naskah juga dapat disebut dalam lain / PL SQL file, shell script, atau program lainnya.
Menampilkan Output untuk Pengguna yang
Terutama ketika menangani pengecualian, Anda mungkin ingin untuk menampilkan output untuk menjaga pengguna informasi tentang apa yang terjadi. Anda dapat menampilkan output untuk menyampaikan informasi, dan Anda dapat menampilkan pesan kesalahan kustom Anda sendiri, yang mungkin akan lebih masuk akal untuk pengguna dari jumlah kesalahan. Mungkin Anda ingin pengguna untuk menghubungi administrator database jika kesalahan terjadi selama pemrosesan, bukan untuk melihat pesan yang tepat.
PL / SQL tidak menyediakan suatu metode langsung untuk menampilkan output sebagai bagian dari sintaks, tetapi hal itu memungkinkan Anda untuk menelepon sebuah paket yang melayani fungsi ini dari dalam blok. Paket ini disebut DBMS_OUTPUT.
PENGECUALIAN KETIKA KEMUDIAN zero_divide DBMS_OUTPUT.put_line ('ERROR: pembagi IS ZERO.. ANDA SEE DBA');
ANALISIS:
Oracle standar ZERO_DIVIDE adalah pengecualian. Sebagian besar kesalahan umum yang terjadi selama proses program ini akan ditetapkan sebagai pengecualian dan dibesarkan secara implisit (yang berarti bahwa Anda tidak perlu meningkatkan kesalahan pada bagian PROSEDUR blok).
Jika pengecualian ini ditemui selama pemrosesan blok, pengguna akan melihat:
INPUT:
SQL> @ block1 ERROR: pembagi IS ZERO. LIHAT DBA ANDA. PL / SQL prosedur berhasil diselesaikan.
Bukankah itu pesan tampak ramah dari:
INPUT / OUTPUT:
SQL> @ block1 mulai * ERROR pada baris 1: ORA-01476: pembagi sama dengan nol ORA-06512: di baris 20
Kontrol transaksional dalam PL / SQL
Pada 11 Hari, “Pengendalian Transaksi,” dibahas kita perintah kontrol transaksi COMMIT, ROLLBACK, dan SAVEPOINT. Perintah ini memungkinkan pemrogram untuk mengontrol kapan transaksi sebenarnya ditulis ke database, seberapa sering, dan ketika mereka harus dibatalkan.
Sintaks:
BEGIN MENYATAKAN ... BEGIN laporan ... IF kondisi THEN COMMIT; ELSE ROLLBACK; END IF; ... PENGECUALIAN ... END; END;
Hal yang baik tentang PL / SQL adalah bahwa Anda dapat mengotomatisasi penggunaan kontrol transaksional perintah daripada terus-menerus memantau transaksi besar, yang bisa jadi sangat membosankan.
Puting Semuanya Bersama
Sejauh ini, Anda telah diperkenalkan kepada PL / SQL, telah menjadi akrab dengan tipe data yang didukung, dan akrab dengan fitur-fitur utama dari SQL / blok PL. Kau tahu bagaimana cara mendeklarasikan variabel lokal, konstanta, dan kursor. Anda juga telah melihat bagaimana menanamkan SQL pada bagian PROSEDUR, memanipulasi kursor, dan menaikkan pengecualian. Ketika kursor telah dibangkitkan, Anda harus memiliki pemahaman dasar tentang bagaimana untuk menangani hal itu di bagian PENGECUALIAN blok. Sekarang Anda siap untuk bekerja dengan beberapa contoh praktis dan membuat blok dari BEGIN ke END. Pada akhir bagian ini, Anda benar-benar harus memahami bagaimana bagian-bagian dari suatu SQL / blok PL berinteraksi satu sama lain.
Contoh Tabel dan Data
Kami akan menggunakan dua meja untuk membuat PL / SQL blok. PAYMENT_TABLE mengidentifikasi pelanggan, berapa banyak ia telah membayar, dan jumlah total yang jatuh tempo.PAY_STATUS_TABLE belum berisi data. Data akan dimasukkan ke dalam PAY_STATUS_TABLE sesuai dengan kondisi tertentu dalam PAYMENT_TABLE.
INPUT:
SQL> pilih * 2 dari payment_table;
OUTPUT:
PEMBAYARAN PELANGGAN TOTAL_DUE -------- -------- --------- ABC 90,50 150,99 AAA 79,00 79,00 BBB 950,00 0,00 1000 CCC 27,50 27,50 DDD 350,00 500,95 EEE 67.89 67.89 FFF 555.55 455.55 GGG 122.36 122.36 HHH 26.75 0.00 9 rows selected.
INPUT:
SQL> describe pay_status_table
OUTPUT:
Name Null? Jenis ------------------------------ --------- ---- CUST_ID NOT NULL CHAR(3) STATUS NOT NULL VARCHAR2(15) AMT_OWED NUMBER(8,2) AMT_CREDIT NUMBER(8,2)
ANALISIS:
DESCRIBE is an Oracle SQL command that displays the structure of a table without having to query the data dictionary. DESCRIBE and other Oracle SQL*Plus commands are covered on Day 20, “SQL*Plus.”
A Simple PL/SQL Block
This is how the PL/SQL script ( block1.sql ) file looks:
INPUT:
set serveroutput pada BEGIN MENYATAKAN AmtZero PENGECUALIAN;% JENIS payment_table.cust_id cCustId; fPayment% payment_table.payment JENIS;% JENIS payment_table.total_due fTotalDue; payment_cursor kursor adalah cust_id pilih, pembayaran, total_due dari payment_table, nomor fOverPaid (8,2); fUnderPaid nomor (8,2); BEGIN payment_cursor terbuka; loop fetch payment_cursor ke cCustId, fPayment, fTotalDue; keluar ketika payment_cursor NOTFOUND%, jika (fTotalDue = 0) kemudian meningkatkan AmtZero; berakhir jika, jika (> fTotalDue fPayment) kemudian fOverPaid: = fPayment - fTotalDue; masukkan ke pay_status_table (cust_id, status, amt_credit) nilai (cCustId, 'Lebih', fOverPaid); elsif (fPayment <fTotalDue) kemudian fUnderPaid: = - fTotalDue fPayment Dibayar; masukkan ke pay_status_table (cust_id, status, amt_owed) nilai (cCustId, 'masih berhutang', fUnderPaid); menyisipkan lain ke dalam nilai-nilai pay_status_table (cCustId, 'Disetor Penuh', null, null); akhir jika; loop akhir; payment_cursor dekat; PENGECUALIAN ketika AmtZero kemudian DBMS_OUTPUT.put_line ('ERROR : jumlahnya nol.. Anda Lihat pengawas '); ketika LAIN kemudian DBMS_OUTPUT.put_line (' ERROR: Kesalahan yang tidak diketahui. Lihat DBA '); AKHIR; AKHIR; /
ANALISIS:
Bagian MENYATAKAN enam mendefinisikan variabel lokal, serta kursor yang disebut payment_cursor. Bagian PROSEDUR dimulai dengan pernyataan BEGIN kedua di mana langkah pertama adalah untuk membuka kursor dan mulai loop. Perintah FETCH melewati nilai saat ini di kursor ke dalam variabel-variabel yang didefinisikan dalam bagian MENYATAKAN. Selama loop menemukan catatan di kursor, pernyataan itu membandingkan jumlah yang dibayar oleh pelanggan dengan jumlah yang jatuh tempo. Kelebihan dan kekurangan bayar dihitung sesuai dengan jumlah yang dibayar, dan kami menggunakan yang dihitung jumlah memasukkan nilai-nilai ke dalam PAY_STATUS_TABLE. loop itu berakhir, dan kursor menutup. Bagian PENGECUALIAN menangani kesalahan yang mungkin terjadi selama pemrosesan.
Sekarang jalankan PL / SQL script file dan melihat apa yang terjadi.
INPUT:
SQL> @ block1
OUTPUT:
Input dipotong ke 1 karakter ERROR: Zero jumlahnya. Lihat atasan Anda. PL / SQL prosedur berhasil diselesaikan.
Sekarang Anda tahu bahwa jumlah yang tidak benar muncul di kolom total karena, Anda dapat memperbaiki jumlah dan menjalankan script lagi.
INPUT / OUTPUT:
SQL> update payment_table 2 set total_due = 26,75 3 mana cust_id = 'HHH'; 1 baris diperbarui. SQL> commit; Komit lengkap. SQL truncate table> pay_status_table; Meja dipotong.
CATATAN: Contoh ini memotong PAY_STATUS_TABLE untuk membersihkan meja isi; jangka berikutnya pernyataan itu akan terisi kembali tabel. Anda mungkin ingin menambahkan pernyataan TABLE truncate ke / SQL Anda blok PL.
INPUT / OUTPUT:
SQL> @ block1 Input dipotong ke 1 karakter PL / SQL prosedur berhasil diselesaikan.
Sekarang Anda dapat memilih dari PAY_STATUS_TABLE dan melihat status pembayaran setiap pelanggan.
INPUT / OUTPUT:
SQL> pilih * 2 dari pay_status_table 3 order menurut status;
STATUS PELANGGAN AMT_OWED AMT_CREDIT -------- -------------- --------- ----------- FFF Lebih Dibayar 100,00 AAA Disetor Penuh CCC Disetor Penuh Eee Disetor Penuh GGG Disetor Penuh HHH Disetor Penuh ABC masih berhutang 60,49 DDD masih berhutang 150,95 BBB masih berhutang 50,00 9 baris yang dipilih.
ANALISIS:
Sebuah baris dimasukkan ke dalam PAY_STATUS_TABLE untuk setiap baris data yang terdapat dalam PAYMENT_TABLE. Jika pelanggan membayar lebih dari jumlah yang jatuh tempo, maka perbedaan itu masukan ke dalam kolom amt_credit. Jika pelanggan membayar kurang dari jumlah utang, maka sebuah entri dibuat di kolom amt_owed. Jika pelanggan dibayar penuh, maka tidak ada jumlah dolar yang dimasukkan ke dalam salah satu dari dua kolom.
Program lain
Contoh ini menggunakan sebuah tabel yang disebut PAY_TABLE:
INPUT:
SQL> desc pay_table
OUTPUT:
Name Null? Type ------------------------------ -------- ---- NAME NOT NULL VARCHAR2(20) PAY_TYPE NOT NULL VARCHAR2(8) PAY_RATE NOT NULL NUMBER(8,2) EFF_DATE NOT NULL DATE PREV_PAY NUMBER(8,2)
First take a look at the data:
INPUT:
SQL> pilih * 2 from pay_table 3 order by pay_type, pay_rate desc;
OUTPUT:
NAME PAY_TYPE PAY_RATE EFF_DATE PREV_PAY
-------------------- -------- --------- --------- ---------
SANDRA SAMUELS HOURLY 12.50 01-JAN-97
ROBERT BOBAY HOURLY 11.50 15-MAY-96
KEITH JONES HOURLY 10.00 31-OCT-96
SUSAN WILLIAMS HOURLY 9.75 01-MAY-97
CHRISSY ZOES SALARY 50000.00 01-JAN-97
CLODE EVANS SALARY 42150.00 01-MAR-97
JOHN SMITH SALARY 35000.00 15-JUN-96
KEVIN TROLLBERG SALARY 27500.00 15-JUN-96
8 rows selected.
Situation: Sales are up. Any individual who has not had a pay increase for six months (180 days) will receive a raise effective today. All eligible hourly employees will receive a 4 percent increase, and eligible salary employees will receive a 5 percent increase.
Today is:
INPUT/OUTPUT:
SQL> select sysdate 2 dari dual; SYSDATE -------- 20-MAY-97
Before examining the next PL/SQL block, we will perform a manual select from the PAY_TABLE that flags individuals who should receive a raise.
INPUT:
SQL> select name, pay_type, pay_rate, eff_date,
2 'YES' due
3 from pay_table
4 where eff_date < sysdate - 180
5 UNION ALL
6 select name, pay_type, pay_rate, eff_date,
7 'No' due
8 from pay_table
9 where eff_date >= sysdate - 180
10 order by 2, 3 desc;
OUTPUT:
NAME PAY_TYPE PAY_RATE EFF_DATE DUE
-------------------- --------- -------- --------- ---
SANDRA SAMUELS HOURLY 12.50 01-JAN-97 No
ROBERT BOBAY HOURLY 11.50 15-MAY-96 YES
KEITH JONES HOURLY 10.00 31-OCT-96 YES
SUSAN WILLIAMS HOURLY 9.75 01-MAY-97 No
CHRISSY ZOES SALARY 50000.00 01-JAN-97 No
CLODE EVANS SALARY 42150.00 01-MAR-97 No
JOHN SMITH SALARY 35000.00 15-JUN-96 YES
KEVIN TROLLBERG SALARY 27500.00 15-JUN-96 YES
8 rows selected.
The DUE column identifies individuals who should be eligible for a raise. Here’s the PL/SQL script:
INPUT:
set serveroutput on
BEGIN
DECLARE
UnknownPayType exception;
cursor pay_cursor is
select name, pay_type, pay_rate, eff_date,
sysdate, rowid
from pay_table;
IndRec pay_cursor%ROWTYPE;
cOldDate date;
fNewPay number(8,2);
BEGIN
open pay_cursor;
loop
fetch pay_cursor into IndRec;
exit when pay_cursor%NOTFOUND;
cOldDate := sysdate - 180;
if (IndRec.pay_type = 'SALARY') then
fNewPay := IndRec.pay_rate * 1.05;
elsif (IndRec.pay_type = 'HOURLY') then
fNewPay := IndRec.pay_rate * 1.04;
else
raise UnknownPayType;
end if;
if (IndRec.eff_date < cOldDate) then
update pay_table
set pay_rate = fNewPay,
prev_pay = IndRec.pay_rate,
eff_date = IndRec.sysdate
where rowid = IndRec.rowid;
commit;
end if;
end loop;
close pay_cursor;
EXCEPTION
when UnknownPayType then
dbms_output.put_line('=======================');
dbms_output.put_line('ERROR: Aborting program.');
dbms_output.put_line('Unknown Pay Type for Name');
when others then
dbms_output.put_line('ERROR During Processing. See the DBA.');
END;
END;
/
Are you sure that you want to give four employees a pay raise? (The final SELECT statement has four Yes values in the DUE column.) Why not…let’s give all four employees a raise. You can apply the appropriate pay increases by executing the PL/SQL script file, named block2.sql :
INPUT/OUTPUT:
SQL> @block2
Input truncated to 1 characters
PL/SQL procedure successfully completed.
You can do a quick select to verify that the changes have been made to the pay_rate of the appropriate individuals:
INPUT:
SQL> pilih * 2 from pay_table 3 order by pay_type, pay_rate desc;
OUTPUT:
NAME PAY_TYPE PAY_RATE EFF_DATE PREV_PAY
-------------------- --------- -------- -------- -----------
SANDRA SAMUELS HOURLY 12.50 01-JAN-97
ROBERT BOBAY HOURLY 11.96 20-MAY-97 11.5
KEITH JONES HOURLY 10.40 20-MAY-97 10
SUSAN WILLIAMS HOURLY 9.75 01-MAY-97
CHRISSY ZOES SALARY 50000.00 01-JAN-97
CLODE EVANS SALARY 42150.00 01-MAR-97
JOHN SMITH SALARY 36750.00 20-MAY-97 35000
KEVIN TROLLBERG SALARY 28875.00 20-MAY-97 27500
8 rows selected.
ANALISIS:
Empat karyawan menerima kenaikan gaji. Jika Anda bandingkan output ini dengan output dari perintah SELECT asli, Anda dapat melihat perubahan. Tingkat membayar saat ini telah diupdate untuk mencerminkan kenaikan gaji, tingkat membayar asli dimasukkan ke dalam kolom membayar sebelumnya, dan tanggal efektif telah diupdate untuk itu tanggal hari ini. Tidak ada aksi diambil pada orang-orang yang tidak memenuhi syarat untuk kenaikan gaji.
Tunggu – Anda tidak mendapatkan kesempatan untuk melihat cara kerja kecuali ditetapkan. Anda dapat menguji bagian PENGECUALIAN dengan menyisipkan sebuah PAY_TYPE valid ke PAY_TABLE.
INPUT:
SQL> insert ke nilai-nilai pay_table 2 ('Jennings', 'JURNAL' JEFF, 71,50, '01-JAN-97 ', NULL);
OUTPUT:
1 baris dibuat.
Saat kebenaran:
INPUT / OUTPUT:
SQL> @ block2 Input dipotong ke 1 karakter ======================= ERROR: Membatalkan program. Jenis Unknown Bayar untuk: Jennings JEFF PL / SQL prosedur berhasil diselesaikan.
ANALISIS:
Pesan kesalahan kepada Anda bahwa JEFF Jennings memiliki Bayar Jenis dengan nilai selain GAJI atau PER JAM. Artinya, perkecualian ditangani dengan pesan kesalahan.
Disimpan Prosedur, Paket, dan Pemicu
Using PL/SQL, you can create stored objects to eliminate having to constantly enter monotonous code. Procedures are simply blocks of code that perform some sort of specific function. Related procedures can be combined and stored together in an object called a package. A trigger is a database object that is used with other transactions. You might have a trigger on a table called ORDERSthat will insert data into a HISTORY table each time the ORDERS table receives data. The basic syntax of these objects follows.
Sample Procedure
SYNTAX:
PROCEDURE procedure_name IS variable1 datatype; ... BEGIN statement1; ... EXCEPTION when ... END procedure_name;
Sample Package
SYNTAX:
CREATE PACKAGE package_name AS PROCEDURE procedure1 (global_variable1 datatype, ...); PROCEDURE procedure2 (global_variable1 datatype, ...); END package_name; CREATE PACKAGE BODY package_name AS PROCEDURE procedure1 (global_variable1 datatype, ...) IS BEGIN statement1; ... END procedure1; PROCEDURE procedure2 (global_variable1 datatype, ...) IS BEGIN statement1; ... END procedure2; END package_name;
Sample Trigger
SYNTAX:
CREATE TRIGGER trigger_name AFTER UPDATE OF column ON table_name FOR EACH ROW BEGIN statement1; ... END;
The following example uses a trigger to insert a row of data into a transaction table when updating PAY_TABLE . The TRANSACTION table looks like this:
INPUT:
SQL> describe trans_table
OUTPUT:
Nama Null? Jenis ------------------------------ -------- ---- ACTION VARCHAR2(10) NAME VARCHAR2(20) PREV_PAY NUMBER(8,2) CURR_PAY NUMBER(8,2) EFF_DATE DATE
Here’s a sample row of data:
INPUT/OUTPUT:
SQL> pilih * 2 from pay_table 3 where name = 'JEFF JENNINGS'; NAME PAY_TYPE PAY_RATE EFF_DATE PREV_PAY -------------------- -------- --------- -------- ---------- JEFF JENNINGS WEEKLY 71.50 01-JAN-97
Now, create a trigger:
SQL> CREATE TRIGGER pay_trigger
2 AFTER update on PAY_TABLE
3 FOR EACH ROW
4 BEGIN
5 insert into trans_table values
6 ('PAY CHANGE', :new.name, :old.pay_rate,
7 :new.pay_rate, :new.eff_date);
8 END;
9 /
Trigger created.
The last step is to perform an update on PAY_TABLE , which should cause the trigger to be executed.
INPUT/OUTPUT:
SQL> update pay_table 2 set pay_rate = 15.50, 3 eff_date = sysdate 4 where name = 'JEFF JENNINGS'; 1 row updated. SQL> pilih * 2 from pay_table 3 where name = 'JEFF JENNINGS'; NAME PAY_TYPE PAY_RATE EFF_DATE PREV_PAY -------------------- -------- --------- -------- ---------- JEFF JENNINGS WEEKLY 15.50 20-MAY-97 SQL> pilih * 2 from trans_table; ACTION NAME PREV_PAY CURR_PAY EFF_DATE ---------- -------------------- ---------- ---------- --------- PAY CHANGE JEFF JENNINGS 71.5 15.5 20-MAY-97
ANALISIS:
PREV_PAY is null in PAY_TABLE but PREV_PAY appears in TRANS_TABLE . This approach isn’t as confusing as it sounds. PAY_TABLE does not need an entry for PREV_PAY because the PAY_RATE of71.50 per hour was obviously an erroneous amount. Rather, we inserted the value for PREV_PAY in TRANS_TABLE because the update was a transaction, and the purpose of TRANS_PAY is to keep a record of all transactions against PAY_TABLE .
CATATAN: Jika Anda sudah familiar dengan teknologi jaringan, Anda mungkin melihat persamaan antara PL / SQL dan Java disimpan prosedur dicatat. Namun, beberapa perbedaan seharusnya. PL / SQL merupakan pengembangan dari SQL standar, melaksanakan perintah-perintah bahasa prosedural. Jawa, yang jauh lebih maju dari PL / SQL, memungkinkan pemrogram untuk menulis program yang lebih kompleks daripada yang dimungkinkan dengan PL / SQL. PL / SQL didasarkan pada fungsi-intensif database SQL; Jawa lebih cocok untuk program CPU-intensif. Kebanyakan bahasa prosedural, seperti PL / SQL, yang dikembangkan secara khusus untuk platform yang sesuai. Sebagai bahasa prosedural perkembangan teknologi, tingkat lebih tinggi standardisasi akan diterapkan di seluruh platform.
Ringkasan
PL / SQL memperluas fungsionalitas dari SQL standar. Komponen dasar PL / SQL jenis yang sama melakukan fungsi sebagai bahasa generasi ketiga. Penggunaan variabel lokal kode dinamis mendukung, yaitu nilai-nilai dalam blok dapat berubah dari waktu ke waktu sesuai dengan input pengguna, kondisi tertentu, atau isi kursor /. PL SQL menggunakan program bahasa prosedural laporan kontrol standar. JIKA. KEMUDIAN .. laporan dan loop memungkinkan Anda untuk mencari kondisi-kondisi tertentu, Anda juga dapat menggunakan loop untuk menelusuri isi dari kursor didefinisikan.
Kesalahan yang terjadi selama proses dari setiap program yang menjadi perhatian utama. PL / SQL memungkinkan Anda untuk menggunakan pengecualian untuk mengontrol perilaku dari sebuah program yang menemukan kesalahan sintaks salah satu atau kesalahan logis. Banyak pengecualian yang telah ditetapkan, seperti kesalahan-dengan-nol membagi. Kesalahan dapat ditingkatkan setiap saat selama proses sesuai dengan persyaratan yang ditetapkan dan dapat ditangani dengan cara apapun PL / SQL keinginan pemrogram.
18 hari juga memperkenalkan beberapa penggunaan praktis dari PL / SQL. Database benda seperti pemicu, prosedur tersimpan, dan paket dapat mengotomatisasi fungsi pekerjaan yang banyak.itu contoh Hari ini menerapkan beberapa konsep yang tercakup pada hari sebelumnya.
Sumber ; http://www.webbasedprogramming.com
Otodidak Teknologi Informasi & Komunikasi 