Tujuan

Selama 12 hari terakhir, Anda telah memeriksa setiap topik utama yang digunakan untuk menulis pertanyaan yang kuat untuk mengambil data dari database. Anda juga secara singkat menjelajahi aspek desain database dan database keamanan. Hari ini tujuannya adalah untuk mencakup topik SQL lanjutan, yang meliputi:

  • Sementara tabel
  • Cursors
  • Prosedur tersimpan
  • Pemicu
  • Embedded SQL

CATATAN: Today’s contoh menggunakan Oracle7’s PL / SQL dan / Microsoft’s Transact-SQL Sybase SQL Server implementasi. Kami berusaha untuk memberikan contoh dengan menggunakan kedua rasa dari SQL sedapat mungkin. Anda tidak perlu memiliki salinan baik Oracle7 atau database SQL Server produk. persyaratan Jangan ragu untuk database Anda memilih produk berbasis Anda. (Jika Anda membaca ini untuk mendapatkan pengetahuan yang cukup untuk memulai sebuah proyek untuk pekerjaan Anda, kemungkinan Anda tidak akan punya pilihan.)

CATATAN: Meskipun Anda dapat menerapkan sebagian besar contoh-contoh dalam buku ini untuk sistem manajemen database yang populer, pernyataan ini tidak berlaku untuk semua materi yang dibahas hari ini. Banyak vendor masih tidak mendukung tabel sementara, prosedur tersimpan, dan pemicu. Periksa dokumentasi Anda untuk menentukan fitur tersebut disertakan dengan sistem database favorit Anda.

Tabel Sementara

Topik lanjutan pertama kita bahas adalah penggunaan tabel sementara, yang sebenarnya adalah tabel yang ada sementara dalam database dan secara otomatis berkurang bila pengguna kayu keluar atau koneksi database mereka berakhir. Transact-SQL menciptakan tabel ini sementara dalam database tempdb. Database ini dibuat ketika Anda menginstal SQL Server. Dua jenis sintaks yang digunakan untuk membuat tabel sementara.

Sintaks:

Sintaks 1:
membuat tabel # table_name (
field1 datatype,
.
.
.
fieldn datatype
)

Sintaks 1 menciptakan tabel dalam database tempdb. Tabel ini dibuat dengan nama yang unik yang terdiri dari kombinasi nama tabel yang digunakan dalam perintah CREATE TABLE dan waktu stempel tanggal. Sebuah meja sementara tersedia hanya untuk penciptanya. Lima puluh pengguna secara bersamaan bisa menjalankan perintah berikut:

1> membuat tabel album # (
2> artis char (30),
3> album_name char (50),
4 media_type int>)
5> pergi

Tanda pound (#) sebelum nama tabel adalah identifier bahwa SQL Server menggunakan untuk bendera meja sementara. Masing-masing dari 50 pengguna pada dasarnya akan menerima sebuah meja pribadi untuk dia sendiri menggunakan atau itu. Setiap user bisa update, insert, dan menghapus catatan dari meja ini tanpa perlu khawatir pengguna lain mengabaikan data meja. Tabel ini bisa turun seperti biasa dengan mengeluarkan perintah berikut:

1 tabel drop> # album
2> pergi

Tabel ini juga dapat turun secara otomatis bila pengguna yang menciptakannya kayu keluar dari SQL Server. Jika Anda membuat pernyataan menggunakan beberapa jenis koneksi SQL dinamis (seperti di SQL Server DB-Library), meja akan dihapus ketika koneksi SQL dinamis ditutup.

Sintaks 2 menunjukkan cara lain untuk membuat tabel sementara pada SQL Server. sintaks ini menghasilkan hasil yang berbeda dibandingkan dengan sintaks yang digunakan dalam sintaks 1, jadi berhati-hati untuk memperhatikan perbedaan sintaksis.

Sintaks:

Sintaks 2:
membuat tabel tempdb .. tablename (
field1 datatype,
.
.
.
fieldn datatype)

Membuat tabel sementara dengan menggunakan format sintaks 2 masih hasil dalam sebuah tabel yang dibuat dalam database tempdb. meja Nama ini memiliki format yang sama dengan nama tabel yang dibuat menggunakan 1 sintaks. Perbedaannya adalah bahwa tabel ini tidak berkurang bila pengguna koneksi ke database berakhir. Sebaliknya, pengguna harus benar-benar mengeluarkan perintah TABLE DROP untuk menghapus tabel dari database ini tempdb.

TIP: Cara lain untuk menyingkirkan meja yang dibuat menggunakan tempdb membuat tabel tablename sintaks .. adalah untuk mematikan dan restart SQL Server. Metode ini akan menghapus semua tabel dari database sementara tempdb.
Contoh 13,1 dan 13,2 mengilustrasikan fakta bahwa tabel sementara memang sementara, dengan menggunakan dua bentuk sintaks yang berbeda. Setelah dua contoh, 13,3 menggambarkan Contoh penggunaan umum dari tabel sementara: untuk menyimpan data sementara kembali dari query. Data ini kemudian dapat digunakan dengan permintaan lainnya.

Anda perlu membuat database untuk menggunakan contoh-contoh ini. Database MUSIK dibuat dengan tabel berikut:

ARTIS
MEDIA
Rekaman
Gunakan pernyataan SQL berikut untuk membuat tabel ini:

INPUT:

1> ARTIS membuat tabel (
2> nama char (30),
3> pangkalan char (40),
4 style> char (20),
5 int artist_id>)
6> pergi
1> membuat tabel MEDIA (
2 media_type int>,
3> deskripsi char (30),
4 harga float>)
5> pergi
1> membuat rekaman tabel (
2 int artist_id>,
3 media_type int>,
4> judul char (50),
5 tahun int>)
6> pergi

CATATAN: Tabel 13,1, 13,2, dan 13,3 menampilkan data sampel beberapa untuk tabel ini.
Tabel 13.1. The ARTIS tabel.

Nama Pangkalan Gaya Artist_ID
Suaka Soul Minneapolis Batuan 1
Maurice Ravel Perancis Klasik 2
Dave Matthews Band Charlottesville Batuan 3
Vince Gill Nashville Negara 4
Oingo Boingo Los Angeles Pop 5
Crowded House Selandia Baru Pop 6
Mary Chapin Carpenter- Nashville Negara 7
Edward MacDowell Amerika Serikat Klasik 8
Tabel 13.2. The MEDIA tabel.

Media_Type Deskripsi Harga
1 Catatan 4,99
2 Tape 9,99
3 CD 13,99
4 CD-ROM 29,99
5 DAT 19,99
Tabel 13.3. Tabel rekaman.

Artist_Id Media_Type Judul Tahun
1 2 Hang Waktu 1988
1 3 Made To Be Broken 1986
2 3 Blus pendek wanita 1990
3 5 Berdasarkan Tabel dan Dreaming 1994
4 3 Ketika Cinta Menemukan Anda 1994
5 2 Boingo 1987
5 1 Dead Man’s Party 1984
6 2 Woodface 1990
6 3 Bersama Sendirian 1993
7 5 Come On, Come On 1992
7 3 Batu di Jalan 1994
8 5 Kedua Piano Concerto 1985
Contoh 13,1

Anda dapat membuat tabel sementara dalam database tempdb. Setelah memasukkan catatan dummy ke dalam tabel ini, log out. Setelah masuk kembali ke SQL Server, cobalah untuk memilih catatan dummy dari tabel sementara. Catatan hasil:

INPUT:

1> menciptakan tabel album # (
2> artis char (30),
3> album_name char (50),
4 media_type int>)
5> pergi
1> # album memasukkan nilai-nilai (“The” Penggantian, “Senang Untuk Temui Saya”, 1)
2> pergi

Sekarang log out dari koneksi SQL Server menggunakan EXIT (atau QUIT) perintah. Setelah login kembali dan beralih ke database Anda terakhir digunakan, coba perintah berikut:

INPUT:

1 * pilih> dari album #
2> pergi

ANALISIS:

Tabel ini tidak ada dalam database saat ini.

Contoh 13,2

Sekarang ciptakan tabel dengan 2 sintaks:

INPUT:

1> membuat tabel tempdb album .. (
2> artis char (30),
3> album_name char (50),
4 media_type int>)
5> pergi
1> # album memasukkan nilai-nilai (“The” Penggantian, “Senang Untuk Temui Saya”, 1)
2> pergi

Setelah keluar log dan masuk kembali, beralih ke database yang Anda gunakan saat membuat tabel tempdb .. album () telah diterbitkan, kemudian mengeluarkan perintah berikut:

INPUT:

1 * pilih> dari album # 2> pergi
Kali ini, Anda mendapatkan hasil sebagai berikut:

OUTPUT:

artis album_name media_type
_______________________________________________________________________________________

The Penggantian Senang Untuk Temui Saya 1

Contoh 13,3

Contoh ini menunjukkan penggunaan umum dari tabel sementara: untuk menyimpan hasil query yang kompleks untuk digunakan dalam permintaan kemudian.

INPUT:

1> membuat tabel # temp_info (
2> nama char (30),
3> pangkalan char (40),
4 style> char (20),
5 int artist_id>)
6 menyisipkan> # temp_info
7 * pilih> dari ARTIS mana pangkalan = “Nashville”
8> Pilih Rekaman .* dari rekaman, ARTIS
9> mana RECORDINGS.artist_id = # temp_info.artist_id
10> pergi

Batch sebelumnya perintah memilih keluar informasi merekam untuk semua seniman yang rumahnya dasar Nashville.

Perintah berikut adalah cara lain untuk menulis set pernyataan SQL digunakan dalam 13,3 Contoh:

1> ARTIS pilih .* dari ARTIS, rekaman di mana ARTISTS.homebase = “Nashville”
2> pergi

Cursors

Sebuah database kursor mirip dengan kursor pada layar pengolah kata. Saat Anda menekan tombol Panah Bawah, kursor gulungan bawah melalui teks satu baris pada satu waktu. Menekan tombol Panah gulungan kursor satu baris pada satu waktu. Menekan tombol lain seperti Page Up dan Page Down menghasilkan lompatan beberapa baris ke arah baik. Database cursors beroperasi dengan cara yang sama.

Database cursors memungkinkan Anda untuk memilih sekelompok data, gulir melalui kelompok catatan (sering disebut recordset a), dan memeriksa setiap baris data individu sebagai titik kursor ke sana. Anda dapat menggunakan kombinasi variabel lokal dan kursor untuk memeriksa setiap record secara individual dan melakukan operasi eksternal apapun yang dibutuhkan sebelum pindah ke catatan berikutnya.

Satu penggunaan umum lainnya adalah untuk menyimpan kursor hasil query untuk digunakan nanti. Hasil yang mengatur kursor A dibuat dari hasil set query SELECT. Jika aplikasi Anda atau prosedur memerlukan penggunaan berulang dari satu set catatan, adalah lebih cepat untuk menciptakan kursor sekali dan menggunakannya kembali beberapa kali daripada berulang-ulang query database. (Dan Anda memiliki keuntungan tambahan untuk dapat menelusuri permintaan yang hasilnya ditetapkan dengan kursor.)

Ikuti langkah-langkah untuk membuat, menggunakan, dan menutup kursor database:

  1. Buat kursor.
  2. Buka kursor untuk digunakan dalam prosedur atau aplikasi.
  3. Fetch rekaman data satu baris pada suatu waktu sampai Anda telah mencapai akhir kursor yang mencatat.
  4. Tutup kursor ketika Anda selesai dengan hal itu.
  5. Kursor. Yang Deallocate untuk benar-benar membuangnya.

Menciptakan sebuah kursor

Untuk membuat kursor menggunakan Transact-SQL, masalah sintaks berikut:

Sintaks:

menyatakan kursor cursor_name
untuk select_statement
[Untuk (baca saja | update [dari] column_name_list)]

The Oracle7 SQL sintaks yang digunakan untuk membuat kursor akan terlihat seperti ini:

Sintaks:

MENYATAKAN cursor_name kursor
UNTUK (perintah SELECT | | statement_name block_name)

Dengan mengeksekusi pernyataan cursor_name MENYATAKAN kursor, Anda telah menetapkan menetapkan hasil kursor yang akan digunakan untuk semua operasi kursor Anda. kursor A memiliki dua bagian penting: hasil kursor set dan posisi kursor.

Pernyataan berikut membuat kursor berdasarkan tabel ARTIS:

INPUT:

1> membuat kursor Artists_Cursor
2> untuk * pilih dari ARTIS
3> pergi

ANALISIS:

Sekarang Anda memiliki objek kursor sederhana bernama Artists_Cursor yang berisi semua catatan dalam tabel ARTIS. Tapi pertama-tama Anda harus membuka kursor.

Membuka sebuah kursor

Perintah sederhana untuk membuka kursor untuk digunakan adalah

Sintaks:

terbuka cursor_name

Pelaksana pernyataan berikut membuka Artists_Cursor untuk digunakan:

1> terbuka Artists_Cursor
2> pergi

Sekarang Anda bisa menggunakan kursor untuk menelusuri hasil set.

Scrolling sebuah kursor

Untuk menggulir melalui’s hasil mengatur kursor, Transact-SQL menyediakan perintah FETCH berikut.

Sintaks:

mengambil [cursor_name ke] fetch_target_list

Oracle SQL menyediakan sintaks berikut:

FETCH cursor_name (INTO: host_variable
[[INDIKATOR]:] indicator_variable
[,: Host_variable
[[INDIKATOR]: indicator_variable]] …
| MENGGUNAKAN deskripsi deskripsi)

Setiap kali perintah FETCH dijalankan, uang muka kursor pointer melalui hasil menetapkan satu baris pada satu waktu. Jika diinginkan, data dari setiap baris dapat diambil ke variabel fetch_target_list.

CATATAN: Transact-SQL memungkinkan programmer untuk maju lebih dari satu baris pada satu waktu dengan menggunakan perintah berikut: set nomor baris kursor cursor_name. Perintah ini tidak dapat digunakan dengan klausa INTO, namun. Hal ini berguna hanya untuk meloncat maju dikenal beberapa baris, bukan berulang kali melaksanakan pernyataan FETCH.
Pernyataan berikut mengambil data dari hasil set Artists_Cursor dan mengembalikan data ke variabel program:

INPUT:

1> menyatakan @ nama char (30)
2> menyatakan @ pangkalan char (40)
3> menyatakan gaya char @ (20)
4> menyatakan @ artist_id int
5> mengambil Artists_Cursor ke @ nama, @ pangkalan, @ gaya, @ artist_id
6 cetak> @ nama
7> @ pangkalan cetak
8 cetak> @ gaya
9> cetak char (@ artist_id)
10> pergi

Anda dapat menggunakan loop WHILE (lihat Hari 12, “Database Security”) untuk loop melalui mengatur seluruh hasil. Tetapi bagaimana Anda tahu kapan Anda telah mencapai akhir dari catatan?

Pengujian sebuah kursor’s Status

Transact-SQL memungkinkan Anda untuk memeriksa status kursor setiap saat melalui pemeliharaan dari dua variabel global: @ @ @ @ sqlstatus dan rowcount.

The @ @ sqlstatus variabel kembali status informasi tentang pernyataan FETCH dieksekusi terakhir. (The dokumentasi SQL-Transact menyatakan bahwa tidak ada perintah selain pernyataan FETCH dapat memodifikasi variabel sqlstatus @ @ variabel. Ini) berisi salah satu dari tiga nilai. Tabel berikut muncul di manual referensi SQL-Transact:

Arti
0 Menjalankan laporan FETCH.
1 Pernyataan FETCH menghasilkan kesalahan.
2 Tidak ada data yang lebih dalam hasil set.
The @ @ variabel rowcount berisi jumlah baris yang kembali dari kursor hasil disiapkan sesuai dengan mengambil sebelumnya. Anda dapat menggunakan nomor ini untuk menentukan jumlah record di kursor yang hasilnya ditetapkan.

Kode berikut memperpanjang laporan dieksekusi selama diskusi tentang pernyataan FETCH. Anda sekarang menggunakan loop WHILE dengan variabel sqlstatus @ @ untuk menggulir kursor:

INPUT:

1> menyatakan @ nama char (30)
2> menyatakan @ pangkalan char (40)
3> menyatakan @ gaya char (20)
4> menyatakan @ artist_id int
5> mengambil Artists_Cursor ke @ nama, @ pangkalan, @ gaya, @ artist_id
6> sementara (sqlstatus @ @ = 0)
7> mulai
8 mencetak> @ nama
9> @ pangkalan cetak
10 mencetak> @ gaya
11> cetak char (@ artist_id)
12> mengambil Artists_Cursor ke @ nama, @ pangkalan, @ gaya, @ artist_id
13> akhir
14> pergi

ANALISIS:

Sekarang Anda memiliki kursor sepenuhnya berfungsi! Langkah kiri hanya untuk menutup kursor. Penutupan kursor adalah masalah yang sangat sederhana. Pernyataan itu untuk menutup kursor adalah sebagai berikut:

Sintaks: dekat cursor_name

Kursor ini masih ada, namun itu harus dibuka kembali. Penutupan kursor pada dasarnya menutup set keluar hasilnya, tidak seluruh keberadaan. Ketika Anda benar-benar selesai dengan kursor, perintah DEALLOCATE membebaskan memori yang terkait dengan kursor dan membebaskan nama kursor untuk digunakan kembali. Sintaks pernyataan DEALLOCATE adalah sebagai berikut:

Sintaks:

deallocate cursor_name kursor

Contoh 13,4 mengilustrasikan proses lengkap dari kursor menciptakan, menggunakan, dan kemudian menutupnya, menggunakan Transact-SQL.

Contoh 13,4

INPUT:

1> menyatakan @ nama char (30)
2> menyatakan @ pangkalan char (40)
3> menyatakan @ gaya char (20)
4> menyatakan @ artist_id int
5> membuat kursor Artists_Cursor
6> untuk * pilih dari ARTIS
7> terbuka Artists_Cursor
8> mengambil Artists_Cursor ke @ nama, @ pangkalan, @ gaya, @ artist_id
9> sementara (sqlstatus @ @ = 0)
10> mulai
11 mencetak> @ nama
12> @ pangkalan cetak
13 mencetak> @ gaya
14> cetak char (@ artist_id)
15> mengambil Artists_Cursor ke @ nama, @ pangkalan, @ gaya, @ artist_id
16> akhir
17> dekat Artists_Cursor
18> deallocate kursor Artists_Cursor
19> pergi

CATATAN: Berikut ini adalah data sampel saja.
OUTPUT:

Soul Asylum Minneapolis Rock 1
Perancis Maurice Ravel Klasik 2
Dave Matthews Band 3 Charlottesville Rock
Nashville Vince Gill 4 Negara
Los Angeles Boingo Oingo Pop 5
Crowded House Selandia Baru Pop 6
Mary Chapin Carpenter Nashville Negara-7
Edward USA MacDowell Klasik 8

Lingkup cursors

Tidak seperti tabel, indeks, dan objek lain seperti pemicu dan prosedur yang tersimpan, kursor tidak ada sebagai objek database setelah mereka diciptakan. Sebaliknya, cursors memiliki ruang lingkup terbatas digunakan.

PERINGATAN: Ingat, bagaimanapun, bahwa memori tetap dialokasikan untuk kursor, meskipun namanya mungkin tidak ada lagi. Sebelum pergi ke luar kursor’s lingkup, kursor harus selalu tertutup dan deallocated.
Sebuah kursor dapat dibuat dalam tiga wilayah:

Dalam sesi – sesi dimulai ketika pengguna log on. Jika pengguna login ke SQL Server dan kemudian membuat kursor, lalu cursor_name akan ada sampai user logout. Pengguna tidak dapat menggunakan kembali cursor_name selama sesi berjalan. Prosedur Tersimpan – Sebuah kursor dibuat di dalam sebuah prosedur yang disimpan adalah baik hanya selama pelaksanaan prosedur yang tersimpan. Begitu keluar dari prosedur yang tersimpan, cursor_name tidak berlaku lagi. Trigger – Sebuah kursor dibuat di dalam memicu memiliki pembatasan yang sama sebagai salah satu dibuat di dalam sebuah prosedur yang tersimpan.

Menciptakan dan Menggunakan Stored Prosedur

Konsep disimpan adalah prosedur yang penting bagi programmer profesional untuk menguasai database. Stored prosedur adalah fungsi yang berisi pengelompokan berpotensi besar pernyataan SQL. Fungsi-fungsi ini dipanggil dan dijalankan hanya sebagai C, FORTRAN, atau Visual Basic fungsi akan dipanggil. Sebuah prosedur yang tersimpan harus encapsulate seperangkat logis dari perintah yang sering dieksekusi (seperti satu set query kompleks, pembaruan, atau sisipan). prosedur Tersimpan memungkinkan programmer untuk sekadar panggilan prosedur disimpan sebagai sebuah fungsi bukan berulang kali menjalankan pernyataan di dalam prosedur yang tersimpan. Namun, prosedur yang tersimpan memiliki keunggulan tambahan.

Sybase, Inc, mempelopori prosedur yang tersimpan dengan produk SQL Server di akhir 1980-an. Prosedur-prosedur ini dibuat dan kemudian disimpan sebagai bagian dari database, seperti tabel dan indeks disimpan dalam database. Transact SQL izin baik parameter input dan output untuk panggilan prosedur disimpan. Mekanisme ini memungkinkan Anda untuk membuat prosedur yang disimpan dengan cara generik sehingga variabel dapat dikirimkan kepada mereka.

Salah satu keuntungan terbesar dengan prosedur yang tersimpan terletak pada desain eksekusi mereka. Ketika menjalankan sebuah batch besar pernyataan SQL ke server database melalui jaringan, aplikasi Anda selalu berkomunikasi dengan server, yang dapat membuat berat beban yang sangat pada jaringan yang sangat cepat. Pada beberapa pengguna menjadi terlibat dalam komunikasi ini, kinerja jaringan dan database server menjadi semakin lambat. Penggunaan prosedur disimpan memungkinkan programmer untuk mengurangi beban komunikasi ini.

Setelah dilakukan prosedur yang tersimpan, laporan dijalankan secara berurutan pada SQL server database. Beberapa pesan atau data dikembalikan ke pengguna komputer hanya ketika prosedur ini selesai. Pendekatan ini meningkatkan kinerja dan menawarkan keuntungan lain juga. Stored prosedur sebenarnya dikompilasi oleh mesin database pertama kali digunakan. Peta dikompilasi disimpan pada server dengan prosedur. Dengan demikian, Anda tidak perlu laporan SQL mengoptimalkan setiap kali Anda mengeksekusi mereka, yang juga meningkatkan kinerja.

Gunakan sintaks berikut untuk membuat sebuah prosedur disimpan menggunakan Transact-SQL:

Sintaks:

membuat prosedur procedure_name
[[(]Parameter_name
datatype [(panjang) | ([presisi,] skala)
default] = [] output [
[, @ Parameter_name
datatype [(panjang) | ([presisi,] skala)
default] = []]…[)]] output [
[Dengan mengkompilasi ulang]
sebagai SQL_statements

Jalankan perintah ini mengeksekusi prosedur:

Sintaks:

melaksanakan [@ return_status =]
procedure_name
[[@ Parameter_name =] nilai |
[@ Parameter_name =] @ variabel [output ]…]]
[Dengan mengkompilasi ulang]

Contoh 13,5

Contoh ini membuat prosedur sederhana menggunakan isi dari Contoh 13.4.

INPUT:

1> menciptakan prosedur Print_Artists_Name
2> sebagai
3> menyatakan @ nama char (30)
4> menyatakan @ pangkalan char (40)
5> menyatakan @ gaya char (20)
6> menyatakan @ int artist_id
7> membuat kursor
8>Artists_Cursor> untuk memilih * dari ARTIS
9> terbuka Artists_Cursor
10> mengambil Artists_Cursor ke @ nama, @ pangkalan, @ gaya, @ artist_id 11> sementara (sqlstatus @ @ = 0)
12> mulai mencetak
13> @ nama
 14> mengambil Artists_Cursor ke @ nama, @ pangkalan, gaya @, @ artist_id
 15> akhir
16> dekat Artists_Cursor
17> deallocate kursor Artists_Cursor
18> pergi

Anda sekarang dapat menjalankan prosedur Print_Artists_Name menggunakan pernyataan Jalankan:

INPUT:

1> mengeksekusi Print_Artists_Name
2> pergi

OUTPUT:

Soul Asylum
Maurice Ravel
Dave Matthews Band
Vince Gill
Oingo Boingo
Crowded House
Mary Chapin Carpenter-
Edward MacDowell

Contoh 13,5 merupakan prosedur yang disimpan kecil, namun, suatu prosedur yang tersimpan dapat berisi banyak pernyataan, yang berarti Anda tidak perlu mengeksekusi setiap pernyataan secara individual.

Parameter Menggunakan Stored Prosedur

Contoh 13,5 merupakan langkah awal yang penting karena menunjukkan penggunaan PROSEDUR pernyataan CREATE sederhana. Namun, dengan melihat sintaks yang diberikan di sini, Anda dapat melihat bahwa ada lebih ke laporan PROSEDUR CREATE dari yang ditunjukkan pada Contoh 13.5. prosedur tersimpan juga menerima parameter sebagai masukan untuk laporan SQL mereka. Selain itu, data dapat kembali dari suatu prosedur yang tersimpan melalui penggunaan parameter output.

Masukan nama parameter harus dimulai dengan simbol @, dan parameter-parameter ini harus menjadi Transact-SQL yang valid tipe data. Nama parameter output juga harus dimulai dengan simbol @. Selain itu, kata kunci OUTPUT harus mengikuti nama parameter output. (Anda juga harus memberi kata kunci ini OUTPUT ketika menjalankan prosedur yang tersimpan.)

Contoh 13,6 menunjukkan penggunaan parameter masukan untuk suatu prosedur yang tersimpan.

Contoh 13,6

Prosedur tersimpan berikut memilih nama semua seniman yang tipe media CD:

1> menciptakan prosedur Match_Names_To_Media @ deskripsi char (30)
2> sebagai
3 pilih ARTISTS.name> dari ARTIS, MEDIA, rekaman
4> mana MEDIA.description = @ deskripsi dan
5> MEDIA.media_type = RECORDINGS.media_type dan
6 RECORDINGS.artist_id> = ARTISTS.artist_id
7> pergi
1> melaksanakan Match_Names_To_Media “CD”
2> pergi

Pelaksana pernyataan ini akan kembali berikut kumpulan catatan:

OUTPUT:

NAMA
Soul Asylum
Maurice Ravel
Vince Gill
Crowded House
Mary Chapin Carpenter-

Contoh 13,7

Contoh ini mendemonstrasikan penggunaan parameter output. Fungsi ini mengambil artis pangkalan sebagai input dan mengembalikan artis namanya sebagai output:

INPUT:

1> menciptakan prosedur Match_Homebase_To_Name @ pangkalan char (40), @ nama char (30) output
2> sebagai
3 pilih @ nama = nama dari ARTIS mana pangkalan = @ pangkalan
4> pergi
1> menyatakan @ return_name char (30)
2> melaksanakan Match_Homebase_To_Name “Los Angeles”, return_name @ @ output = nama
3 mencetak> @ nama
4> pergi

OUTPUT:

Oingo Boingo

Menghapus sebuah Prosedur Tersimpan

Sekarang, Anda mungkin dapat membuat dugaan terpelajar tentang bagaimana untuk menyingkirkan suatu prosedur yang tersimpan. Jika Anda menebak perintah DROP, Anda benar-benar benar. Pernyataan berikut menghapus sebuah prosedur yang disimpan dari database:

Sintaks:

procedure_name drop prosedur

Perintah DROP sering digunakan: Sebelum suatu prosedur yang disimpan dapat diciptakan kembali, prosedur lama dengan namanya harus turun. Dari pengalaman pribadi, ada beberapa kasus di mana prosedur dibuat dan kemudian tidak pernah diubah. Sering kali, pada kenyataannya, kesalahan terjadi di suatu tempat dalam laporan yang menyusun prosedur. Kami menyarankan Anda membuat prosedur Anda disimpan menggunakan skrip SQL file yang berisi semua laporan Anda. Anda dapat menjalankan script file ini melalui server database Anda untuk menjalankan laporan yang Anda inginkan dan membangun kembali prosedur Anda. Teknik ini memungkinkan Anda untuk menggunakan editor teks biasa seperti vi atau Windows Notepad untuk membuat dan menyimpan script SQL Anda. Ketika menjalankan skrip ini, namun Anda harus ingat untuk selalu drop prosedur, meja, dan seterusnya dari database sebelum membuat yang baru. Jika Anda lupa perintah DROP, akan menghasilkan kesalahan.

Sintaks berikut ini sering digunakan dalam file script SQL Server sebelum membuat objek database:

Sintaks:

if exists (select * from sysobjects where name = “procedure_name”)
begin
drop procedure procedure_name
akhir
pergi
create procedure procedure_name
sebagai
.
.
.

Perintah-perintah ini memeriksa meja SYSOBJECTS (dimana database informasi objek disimpan di SQL Server) untuk melihat apakah ada obyek. Jika tidak, itu dijatuhkan sebelum yang baru dibuat. Membuat file naskah dan mengikuti langkah-langkah sebelumnya menghemat sejumlah besar waktu (dan banyak potensi kesalahan) dalam jangka panjang.

Nesting Prosedur Tersimpan

panggilan prosedur tersimpan juga dapat diulang untuk pemrograman modularitas meningkat.Sebuah prosedur yang tersimpan dapat panggilan lain prosedur yang tersimpan, yang kemudian dapat panggilan lain prosedur yang tersimpan, dan sebagainya. Nesting prosedur disimpan adalah ide yang bagus untuk beberapa alasan:

Nesting prosedur yang tersimpan mengurangi query Anda yang paling rumit untuk tingkat fungsional. (Alih-alih melaksanakan 12 queries dalam berturut-turut, Anda mungkin bisa mengurangi ini 12 pertanyaan untuk tiga disimpan panggilan prosedur, tergantung pada situasi.)
Nesting prosedur yang tersimpan meningkatkan kinerja. The query optimizer mengoptimalkan lebih kecil, kelompok yang lebih singkat dari pertanyaan lebih efektif dari satu grup besar laporan.
Ketika Prosedur disimpan bersarang, setiap variabel atau obyek database yang dibuat dalam satu disimpan Prosedur terlihat ke semua prosedur yang tersimpan itu panggilan. Setiap variabel lokal atau objek sementara (seperti tabel sementara) akan dihapus pada akhir prosedur tersimpan yang menciptakan elemen-elemen ini.

Ketika mempersiapkan besar file script SQL, Anda bisa berjalan ke meja atau database objek masalah referensi. Anda harus membuat prosedur tersimpan nested sebelum Anda dapat memanggil mereka. Namun, prosedur memanggil mungkin membuat tabel sementara atau kursor yang kemudian digunakan dalam prosedur disimpan disebut. Prosedur ini disebut disimpan tidak menyadari tabel ini sementara atau kursor, yang dibuat kemudian pada file script. Cara termudah mengatasi masalah ini adalah untuk menciptakan objek sementara sebelum semua prosedur yang tersimpan dibuat, kemudian drop item sementara (dalam file script) sebelum mereka diciptakan lagi dalam prosedur yang tersimpan. Apakah Anda bingung belum? Contoh 13,8 akan membantu Anda memahami proses ini.

Contoh 13,8

INPUT:

1> create procedure Example13_8b
2> as
3> select * from #temp_table
4> go
1> create procedure Example13_8a
2> as
3> create #temp_table (
4> data char(20),
5> numbers int)
6> execute Example13_8b
7> drop table #temp_table
8> go

ANALISIS:

s Anda dapat melihat, prosedur Example13_8b menggunakan # temp_table. Namun, # temp_table tidak diciptakan sampai nanti (dalam prosedur Example13_8a). Hasil ini merupakan kesalahan prosedur penciptaan. Bahkan, karena Example13_8b tidak diciptakan (karena meja hilang # temp_table), prosedur Example13_8a tidak diciptakan baik (karena Example13_8b tidak diciptakan).

Kode berikut perbaikan masalah ini dengan menciptakan # temp_table sebelum prosedur pertama dibuat. # Temp_table kemudian jatuh sebelum penciptaan prosedur kedua:

INPUT:

1> create #temp_table (
2> data char(20),
3> numbers int)
4> go
1> create procedure Example13_8b
2> as
3> select * from #temp_table
4> go
1> drop table #temp_table
2> pergi
1> create procedure Example13_8a
2> as
3> create #temp_table (
4> data char(20),
5> numbers int)
6> execute Example13_8b
7> drop table #temp_table
8> go

Designing and Using Triggers

Memicu pada dasarnya adalah tipe khusus prosedur tersimpan yang dapat dijalankan sebagai tanggapan terhadap salah satu dari tiga kondisi:

An UPDATE
An INSERT
A DELETE
The Transact-SQL syntax to create a trigger looks like this:

Sintaks:

create trigger trigger_name
on table_name
for {insert, update, delete}
as SQL_Statements

The Oracle7 SQL syntax used to create a trigger follows.

Sintaks:

CREATE [OR REPLACE] TRIGGER [schema.]trigger_name
{BEFORE | AFTER}
{DELETE | INSERT | UPDATE [OF column[, column]…]}
[OR {DELETE | INSERT | UPDATE [OF column [, column] …]}]…
ON [schema.]table
[[REFERENCING { OLD [AS] old [NEW [AS] new]
| NEW [AS] new [OLD [AS] old]}]
FOR EACH ROW
[WHEN (condition)] ]
pl/sql statements…

Pemicu yang paling berguna untuk menegakkan integritas referensial, sebagaimana disebutkan pada Hari 9, “Menciptakan dan Mempertahankan Tabel,” ketika Anda belajar bagaimana untuk membuat tabel. integritas Referential memaksa aturan digunakan untuk memastikan bahwa data tetap berlaku di beberapa meja. Misalkan pengguna memasukkan perintah berikut:

INPUT:

1> insert RECORDINGS values (12, “The Cross of Changes”, 3, 1994)
2> pergi

ANALISIS:

Menyisipkan pernyataan ini berlaku sempurna SQL rekor baru dalam tabel rekaman. Namun, pemeriksaan cepat dari tabel ARTIS menunjukkan bahwa tidak ada Artist_ID = 12. Seorang pengguna dengan hak akses INSERT pada tabel rekaman benar-benar dapat menghancurkan integritas referensial Anda.

Catatan: Meskipun sistem database yang dapat menegakkan integritas referensial melalui penggunaan kendala dalam pernyataan CREATE TABLE, memicu menyediakan fleksibilitas yang lebih banyak. Kendala kembali pesan kesalahan sistem untuk pengguna, dan (karena Anda mungkin tahu sekarang) pesan error ini tidak selalu membantu. Di sisi lain, memicu dapat mencetak pesan kesalahan, telepon prosedur yang tersimpan lain, atau mencoba untuk memperbaiki masalah jika perlu.
Pemicu dan Transaksi

Tindakan dilakukan dalam memicu secara implisit dilaksanakan sebagai bagian dari transaksi.Berikut urutan peristiwa yang luas:

  1. A BEGIN TRANSACTION statement is implicitly issued (for tables with triggers).
  2. The insert, update, or delete operation occurs.
  3. The trigger is called and its statements are executed.
  4. The trigger either rolls back the transaction or the transaction is implicitly committed.

Example 13.9

This example illustrates the solution to the RECORDINGS table update problem mentioned earlier.

INPUT:

1> create trigger check_artists
2> on RECORDINGS
3> for insert, update as
4> if not exists (select * from ARTISTS, RECORDINGS
5> where ARTISTS.artist_id = RECORDINGS.artist_id)
6> begin
7> print “Illegal Artist_ID!”
8> rollback transaction
9> akhir
10> go

ANALISIS:

Masalah yang sama bisa ada untuk menghapus dari tabel rekaman. Anggaplah bahwa ketika Anda menghapus data hanya seorang artis dari meja rekaman, Anda juga ingin menghapus artis dari meja ARTIS. Jika catatan sudah dihapus ketika memicu dipecat, bagaimana Anda tahu mana Artist_ID harus dihapus? Ada dua metode untuk memecahkan masalah ini:

Hapus semua seniman dari meja ARTIS yang tidak lagi memiliki rekaman dalam tabel rekaman. (Lihat Contoh 13.10a.) Periksa tabel logis dihapus. Transact-SQL mempertahankan dua tabel: DIHAPUS dan dimasukkan. Tabel ini, yang mempertahankan perubahan terbaru paling ke meja yang sebenarnya, memiliki struktur yang sama dengan tabel yang memicu dibuat meja. Oleh karena itu, Anda dapat mengambil ID dari artis DIHAPUS meja dan kemudian hapus ID dari ARTIS tersebut. (Lihat 13.10b Contoh.)

Contoh 13.10a

INPUT:

1> menciptakan delete_artists memicu
2> pada rekaman
3> untuk menghapus sebagai
4> mulai
5> menghapus dari ARTIS mana artist_id tidak
6> (pilih artist_id dari rekaman)
7> akhir
8> pergi

Contoh 13.10b

1> menciptakan delete_artists memicu
2> pada rekaman
3> untuk menghapus sebagai
4> mulai
5> menghapus ARTIS dari ARTIS, dihapus
6> di mana ARTIST.artist_id = deleted.artist_id
7> akhir
8> pergi

Menggunakan pembatasan Pemicu

Anda harus mengamati batasan berikut ketika Anda menggunakan pemicu:

Pemicu tidak dapat dibuat pada tabel temporer.
Pemicu harus diciptakan pada tabel dalam database saat ini.
Pemicu tidak dapat dibuat pada tampilan.
Ketika meja terjatuh, semua memicu terkait dengan tabel yang otomatis menurun dengan itu.
Pemicu bersarang

Pemicu juga dapat bersarang. Katakanlah bahwa Anda telah membuat sebuah pemicu api menghapus, misalnya. Jika ini memicu sendiri kemudian menghapus catatan, database server dapat diatur untuk memicu api lain. Pendekatan ini, tentu saja, hasil dalam satu lingkaran, berakhir hanya ketika semua catatan dalam tabel itu dihapus (atau beberapa kondisi internal memicu dipenuhi). Nesting perilaku tidak default, namun. Lingkungan harus diatur untuk memungkinkan jenis fungsionalitas. Konsultasikan’s database server dokumentasi Anda untuk informasi lebih lanjut tentang topik ini.

Menggunakan Perintah SELECT dengan UPDATE dan DELETE

Berikut adalah beberapa pernyataan SQL yang kompleks menggunakan UPDATE dan DELETE:

INPUT:

SQL> UPPDATE EMPLOYEE_TBL
SET LAST_NAME = ‘SMITH’
Ada WHERE (SELECT EMPLOYEE_ID
DARI PAYROLL_TBL
WHERE EMPLOYEE_ID = 2);

OUTPUT:

1 baris diperbarui.

ANALISIS:

Tabel KARYAWAN memiliki nama karyawan yang salah. Kami memperbarui tabel EMPLOYEE hanya jika tabel penggajian memiliki ID yang benar.

INPUT / OUTPUT:

SQL> UPDATE EMPLOYEE_TABLE
SET HOURLY_PAY = ‘HOURLY_PAY * 1,1
WHERE EMPLOYEE_ID = (SELECT EMPLOYEE_ID
DARI PAYROLL_TBL
WHERE EMPLOYEE_ID = ‘222222222 ‘);

1 baris diperbarui.

ANALISIS:

Kami meningkatkan tarif per jam karyawan sebesar 10 persen.

INPUT / OUTPUT:

SQL> HAPUS DARI EMPLOYEE_TBL
WHERE EMPLOYEE_ID = (SELECT EMPLOYEE_ID
DARI PAYROLL_TBL
WHERE EMPLOYEE_ID = ‘222222222 ‘;
1 baris dihapus.

ANALISIS:

Di sini kita dihapus karyawan dengan ID dari 222.222.222.

Pengujian SELECT Laporan Sebelum Pelaksanaan

Jika Anda membuat laporan (menggunakan PLUS * SQL untuk contoh) dan laporan agak besar, Anda mungkin ingin memeriksa spasi, kolom, dan judul sebelum menjalankan program dan membuang banyak waktu. Sebuah cara sederhana untuk memeriksa adalah dengan menambahkan tempat rownum pilih *
dari employee_tbl
mana rownum <5;

ANALISIS:

Anda mendapatkan empat baris pertama dalam tabel dari mana Anda dapat memeriksa ejaan dan spasi untuk melihat apakah Anda cocok. Jika tidak, laporan Anda dapat kembali ratusan atau ribuan baris sebelum Anda menemukan kesalahan ejaan atau spasi yang tidak tepat.

TIP: Sebagian besar dari pekerjaan Anda – mungkin 50 persen – adalah untuk mencari tahu apa yang pelanggan Anda benar-benar keinginan dan kebutuhan. Keterampilan komunikasi yang baik dan pengetahuan tentang bisnis tertentu yang bekerja untuk Anda akan melengkapi program keterampilan Anda. Misalnya, Anda adalah programmer di sebuah dealer mobil.. Mobil bekas manajer ingin mengetahui berapa banyak kendaraan dia mendatang inventaris Anda berpikir (untuk diri sendiri): Go menghitungnya. Yah, dia meminta berapa banyak kendaraan dia, tetapi Anda tahu bahwa untuk persediaan manajer benar-benar ingin tahu bagaimana mobil (, truk), model, banyak model tahun jenis, dan sebagainya. Jika Anda memberikan apa yang ia minta dan membuang waktu Anda, atau jika Anda memberikan apa yang dia butuhkan?
Embedded SQL

Buku ini menggunakan istilah embedded SQL untuk merujuk pada topik yang lebih besar untuk menulis kode program yang sebenarnya menggunakan SQL – yaitu, menulis prosedur tersimpan tertanam dalam database yang dapat dipanggil oleh sebuah program aplikasi untuk melakukan tugas tertentu. Beberapa sistem database datang dengan tool kit lengkap yang memungkinkan Anda untuk membangun objek layar menu sederhana dan menggunakan kombinasi bahasa pemrograman hak milik dan SQL. Kode SQL tertanam dalam kode ini.

Di lain pihak, SQL tertanam umumnya mengacu pada apa yang secara teknis dikenal sebagai Statis SQL.

Statis dan dinamis SQL

SQL statis berarti embedding pernyataan SQL secara langsung dalam kode pemrograman. Kode ini tidak dapat diubah pada saat runtime. Pada kenyataannya, sebagian besar implementasi Statis SQL memerlukan penggunaan precompiler yang perbaikan pernyataan SQL pada saat runtime. Baik Oracle dan Informix telah mengembangkan paket SQL Statis untuk sistem database mereka. Produk ini mengandung precompilers untuk digunakan dengan beberapa bahasa, termasuk yang berikut:

Pascal
Ada
COBOL
FORTRAN
Beberapa keuntungan dari Statis SQL

Peningkatan kecepatan runtime
Kompilasi-time error memeriksa
Kelemahan Statis SQL adalah bahwa

Ini adalah fleksibel.  Hal ini membutuhkan kode lebih (karena query tidak dapat dirumuskan pada saat runtime). Kode SQL statis tidak portabel dengan sistem database lain (faktor yang Anda harus selalu mempertimbangkan). Jika Anda mencetak salinan kode ini, laporan SQL muncul di sebelah kode bahasa C (atau apa pun bahasa yang Anda gunakan). Program variabel terikat untuk field pada database menggunakan perintah precompiler. Lihat Contoh 13,11 contoh sederhana untuk kode SQL statis.

Dynamic SQL, di sisi lain, memungkinkan para programmer untuk membangun pernyataan SQL pada saat runtime dan meneruskan pernyataan ini ke mesin database. Mesin kemudian kembali data ke dalam variabel program, yang juga terikat pada runtime. Topik ini dibahas secara menyeluruh pada hari 12.

Contoh 13,11

Contoh ini menggambarkan penggunaan Static SQL pada fungsi C. Harap dicatat bahwa sintaks yang digunakan di sini tidak sesuai dengan standar ANSI. Statis sintaks SQL ini tidak benar-benar sesuai dengan produk komersial, meskipun sintaks yang digunakan adalah mirip dengan produk komersial yang paling.

INPUT:

Bool Print_Employee_Info (void)
(
Umur int = 0;
Char Nama [41] = “\ 0”;
Char Alamat [81] = “\ 0”;
/ * Sekarang Bind Setiap Bidang Kami Akan Pilih Untuk sebuah * Variabel Program /
# BIND SQL (UMUR, Umur)
# BIND SQL (NAMA, Nama);
# BIND SQL (ALAMAT, Address);
/ * Laporan di atas “bind” field dari database ke variabel dari program ini.
Setelah kami query database, kita akan gulir ke catatan kembali
dan kemudian mencetaknya ke layar * /

# UMUR SELECT SQL, NAMA, ALAMAT DARI KARYAWAN;

# SQL FIRST_RECORD
if (Umur == NULL)
(
return FALSE;
)
sementara (Umur! = NULL)
(
printf (“UMUR =% d \ n, Umur);
printf (“NAME =% s \ n, Nama);
printf (“ALAMAT =% s \ n”, Alamat);
# SQL NEXT_RECORD
)
mengembalikan TRUE;

)

ANALISIS:

Setelah Anda ketik kode Anda dan simpan file tersebut, kode tersebut biasanya berlangsung melalui beberapa jenis precompiler. precompiler ini mengubah baris yang dimulai dengan direktif precompiler SQL # untuk kode C aktual, yang kemudian dikompilasi dengan sisa program anda untuk menyelesaikan tugas di tangan.

If you have never seen or written a C program, don’t worry about the syntax used in Example 13.11. (As was stated earlier, the Static SQL syntax is only pseudocode. Consult the Static SQL documentation for your product’s actual syntax.)

Programming with SQL

Sejauh ini, kita telah membahas dua digunakan untuk pemrograman dengan SQL. Yang pertama, yang merupakan fokus dari 12 hari pertama buku ini, digunakan untuk menulis SQL query dan memodifikasi data. Yang kedua adalah kemampuan untuk pernyataan SQL menanamkan dalam ketiga kode-atau bahasa generasi keempat. Jelas, penggunaan pertama untuk SQL sangat penting jika Anda ingin memahami bahasa dan pemrograman database secara umum. Kita telah membahas kelemahan tertanam atau statis menggunakan SQL sebagai lawan Dynamic SQL. 18 hari, “PL / SQL: An Introduction,” dan Hari 19 “Transact-SQL: An Introduction,” mencakup dua ekstensi ke SQL yang dapat Anda gunakan bukan embedded SQL untuk melakukan yang sama jenis fungsi dibahas dalam bagian ini.

Ringkasan

Popularitas lingkungan pemrograman seperti Visual Basic, Delphi, dan database programer PowerBuilder memberikan banyak tools yang bagus untuk mengeksekusi query dan pemutakhiran data dengan database. Namun, karena Anda menjadi semakin terlibat dengan database, Anda akan menemukan keuntungan menggunakan alat dan topik yang dibahas hari ini. Sayangnya, konsep seperti cursors, pemicu, dan inovasi prosedur disimpan adalah database terbaru dan memiliki tingkat rendah standardisasi seluruh produk. Namun, teori dasar penggunaan di balik semua fitur adalah sama dalam semua sistem manajemen database.

Temporary tabel adalah tabel yang ada selama sesi pengguna. Tabel ini biasanya ada dalam database khusus (bernama tempdb bawah SQL Server) dan sering diidentifikasi dengan stempel tanggal-waktu yang unik serta nama. Temporary tabel dapat menyimpan hasil set dari permintaan untuk penggunaan kemudian oleh pertanyaan lain. Kinerja bisa mengikis Namun, jika banyak pengguna yang membuat dan menggunakan tabel sementara sekaligus, karena jumlah besar aktivitas yang terjadi dalam database tempdb.

Cursors dapat menyimpan hasil set untuk menelusuri hasil ini menetapkan satu catatan pada waktu (atau beberapa catatan waktu jika diinginkan). Pernyataan FETCH digunakan dengan kursor untuk mengambil individu yang merekam data dan juga untuk gulir kursor ke catatan berikutnya. Berbagai variabel sistem dapat dimonitor untuk menentukan apakah catatan akhir telah tercapai.

prosedur Tersimpan adalah objek database yang dapat menggabungkan beberapa statemen SQL ke satu fungsi. prosedur tersimpan dapat menerima dan kembali nilai-nilai parameter serta memanggil prosedur tersimpan lainnya. Prosedur-prosedur ini dijalankan pada server database dan disimpan dalam bentuk disusun dalam database. Menggunakan prosedur yang tersimpan, daripada melaksanakan permintaan mandiri, meningkatkan kinerja.

Pemicu adalah prosedur khusus yang disimpan yang dijalankan ketika mengalami sebuah tabel INSERT, DELETE, atau UPDATE operasi. Pemicu sering menegakkan integritas referensial dan juga dapat memanggil prosedur tersimpan lainnya.

Embedded SQL adalah penggunaan SQL pada kode program sebenarnya. Embedded SQL terdiri dari kedua Statik dan Dinamis SQL pernyataan. Statis SQL tidak dapat diubah saat runtime; Dynamic SQL dapat berubah.

Sumber ;  http://www.webbasedprogramming.com