Menulis Pernyataan executable
Identifiers
• Dapat berisi hingga 30 karakter
• Harus diawali dengan karakter abjad
• Dapat berisi angka, tanda-tanda dolar, garis bawah, dan tanda nomor
• Tidak dapat mengandung karakter seperti tanda hubung, garis miring, dan spasi
• Sebaiknya tidak memiliki nama yang sama sebagai nama kolom tabel database
• Sebaiknya tidak reserved words
Sintaks dan Pedoman Block PL / SQL
• Literal
- Karakter dan literal tanggal harus diapit dalam tanda kutip tunggal.
v_name: = 'Henderson';
- Bilangan bisa berupa nilai sederhana atau notasi ilmiah.
• Sebuah garis miring (/) menjalankan blok PL / SQL dalam file script atau di beberapa alat seperti iSQL * PLUS.
Mengomentari Kode
• komentar single-line Prefix dengan dua strip (--).
• Tempatkan banyak baris komentar antara simbol-simbol
/ * Dan * /.
Example:
DECLARE
...
v_sal NUMBER (9,2);
BEGIN
/* Compute the annual salary based on the
monthly salary input from the user */
v_sal := :g_monthly_sal * 12;
END; -- This is the end of the block
Fungsi SQL dalam PL / SQL
• Tersedia dalam pernyataan prosedural:
– Single-row number
– Single-row character
– Data type conversion
– Date
– Timestamp
– GREATEST and LEAST
– Miscellaneous functions
• Tidak tersedia dalam pernyataan prosedural:
– DECODE
– Group functions
Fungsi SQL dalam PL / SQL: Contoh
• Membangun mailing list untuk sebuah perusahaan.
v_mailing_address := v_name||CHR(10)||
v_address||CHR(10)||v_state||
CHR(10)||v_zip;
• Mengkonversi nama karyawan untuk huruf kecil.
v_ename := LOWER(v_ename);
Konversi Tipe Data
• Konversi data ke tipe data yang sebanding.
• tipe data campuran dapat mengakibatkan kesalahan dan mempengaruhi kinerja.
• fungsi Konversi:
– TO_CHAR
– TO_DATE
– TO_NUMBER
DECLARE
v_date DATE := TO_DATE('12-JAN-2001', 'DD-MON-YYYY');
BEGIN
. . .
Pernyataan ini menghasilkan kesalahan kompilasi jika variabel v_date dinyatakan sebagai tipe data DATE.
v_date := 'January 13, 2001';
Untuk memperbaiki kesalahan, gunakan fungsi konversi TO_DATE
v_date := TO_DATE ('January 13, 2001',
'Month DD, YYYY');
Variabel Blok Nested dan Ruang Lingkup
• PL / SQL blok dapat diulang dimanapun suatu
pernyataan dieksekusi diperbolehkan.
• Sebuah blok bersarang menjadi sebuah pernyataan..
• • Sebuah bagian exception dapat berisi blok bersarang.
• Ruang lingkup dari sebuah identifier adalah bahwa wilayah yang
program unit (block, subprogram, atau package)
yang mana Anda bisa reference identifier.
Contoh:
...
x BINARY_INTEGER;
BEGIN
...
DECLARE
y NUMBER;
BEGIN
y:= x;
END;
...
END;
Lingkup Identifier
Suatu identifier terlihat di daerah di mana Anda dapat
referensi identifier tanpa harus memenuhi syarat:
• Sebuah Blok dapat terlihat sampai ke enclosing block
• Sebuah blok tidak dapat melihat ke bawah enclosed blocks
Kualifikasi suatu Identifier
• kualifikasi dapat menjadi label dari enclosing block
• Memenuhi syarat sebuah identifier dengan menggunakan blok label prefix.
<<outer>>
DECLARE
birthdate DATE;
BEGIN
DECLARE
birthdate DATE;
BEGIN
...
outer.birthdate :=
TO_DATE('03-AUG-1976',
'DD-MON-YYYY');
END;
....
END;
Menentukan Ruang Lingkup Variabel
Class Exercise
<<outer>>
DECLARE
v_sal NUMBER(7,2) := 60000;
v_comm NUMBER(7,2) := v_sal * 0.20;
v_message VARCHAR2(255) := ' eligible for commission';
BEGIN
DECLARE
v_sal NUMBER(7,2) := 50000;
v_comm NUMBER(7,2) := 0;
v_total_comp NUMBER(7,2) := v_sal + v_comm;
BEGIN
v_message := 'CLERK not'||v_message;
outer.v_comm := v_sal * 0.30;
END;
v_message := 'SALESMAN'||v_message;
END;
Operator di PL / SQL
• Logis
• Aritmatika
• Concatenation
• Parentheses untuk mengontrol urutan operasi
• Eksponensial operator (**)
Contoh:
• Increment counter untuk loop.
v_count := v_count + 1;
• Mengatur nilai Boolean flag.
v_equal := (v_n1 = v_n2);
• Validasi apakah nomor pegawai berisi nilai.
v_valid := (v_empno IS NOT NULL);
Pedoman pemrograman
Membuat pemeliharaan kode lebih mudah dengan:
• Mendokumentasikan kode dengan komentar
• Mengembangkan konvensi kasus untuk kode
• Mengembangkan konvensi penamaan untuk pengidentifikasi dan objects lainnya
• Meningkatkan readability oleh Indentasi
Kode Indentasi
Untuk kejelasan, indentasi setiap tingkat kode.
Contoh:
BEGIN
IF x=0 THEN
y:=1;
END IF;
END;
DECLARE
v_deptno NUMBER(4);
v_location_id NUMBER(4);
BEGIN
SELECT department_id,
location_id
INTO v_deptno,
v_location_id
FROM departments
WHERE department_name
= 'Sales';
...
END;
/
Berinteraksi dengan server oracle
Laporan SQL dalam PL / SQL
• Ekstrak baris data dari database dengan menggunakan perintah SELECT.
• Lakukan perubahan pada baris dalam database dengan menggunakan perintah DML .
• Kontrol transaksi dengan ROLLBACK, COMMIT, atau perintah SAVEPOINT .
• Menentukan hasil DML dengan implisit cursor attributes.
Laporan SELECT dalam PL / SQL
Ambil data dari database dengan pernyataan SELECT .
Syntax:
SELECT select_list
INTO {variable_name[, variable_name]...
| record_name}
FROM table
[WHERE condition];
• Klausa INTO diperlukan.
• Queries harus mempunyai satu nilai balik dan hanya satu baris.
Contoh:
DECLARE
v_deptno NUMBER(4);
v_location_id NUMBER(4);
BEGIN
SELECT department_id, location_id
INTO v_deptno, v_location_id
FROM departments
WHERE department_name = 'Sales';
...
END;
/
Mengambil Data dalam PL / SQL
Ambil tanggal mempekerjakan dan gaji untuk ditentukan
karyawan.
Contoh:
DECLARE
v_hire_date employees.hire_date%TYPE;
v_salary employees.salary%TYPE;
BEGIN
SELECT hire_date, salary
INTO v_hire_date, v_salary
FROM employees
WHERE employee_id = 100;
...
END;
/
Mengambil Data dalam PL / SQL
Return jumlah gaji untuk semua pegawai di
departemen tertentu.
Contoh:
SET SERVEROUTPUT ON
DECLARE
v_sum_sal NUMBER(10,2);
v_deptno NUMBER NOT NULL := 60;
BEGIN
SELECT SUM(salary) -- group function
INTO v_sum_sal
FROM employees
WHERE department_id = v_deptno;
DBMS_OUTPUT.PUT_LINE ('The sum salary is ' ||
TO_CHAR(v_sum_sal));
END;
/
Konvensi Penamaan
DECLARE
hire_date employees.hire_date%TYPE;
sysdate hire_date%TYPE;
employee_id employees.employee_id%TYPE := 176;
BEGIN
SELECT hire_date, sysdate
INTO hire_date, sysdate
FROM employees
WHERE employee_id = employee_id;
END;
/
Memanipulasi Data Menggunakan PL / SQL
Membuat perubahan pada tabel database dengan menggunakan perintah DML:
• INSERT
• UPDATE
• DELETE
• MERGE
Memasukkan data
Tambahkan informasi karyawan baru ke KARYAWAN
meja.
Contoh:
BEGIN
INSERT INTO employees
(employee_id, first_name, last_name, email,
hire_date, job_id, salary)
VALUES
(employees_seq.NEXTVAL, 'Ruth', 'Cores', 'RCORES',
sysdate, 'AD_ASST', 4000);
END;
/
Memperbarui data
Meningkatkan gaji semua karyawan yang stock clerks
Contoh:
DECLARE
v_sal_increase employees.salary%TYPE := 800;
BEGIN
UPDATE employees
SET salary = salary + v_sal_increase
WHERE job_id = 'ST_CLERK';
END;
/
Menghapus data
Hapus baris yang milik departemen 10 dari tabel KARYAWAN .
Contoh:
DECLARE
v_deptno employees.department_id%TYPE := 10;
BEGIN
DELETE FROM employees
WHERE department_id = v_deptno;
END;
/
Penggabungan Baris
Memasukkan atau memperbarui baris dalam tabel COPY_EMP untuk mencocokkan di tabel KARYAWAN..
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,
. . .,e.department_id);
END;
Konvensi Penamaan
• Menggunakan konvensi penamaan untuk menghindari ambiguitas dalam
Klausa WHERE.
Kolom database dan identifier harus memiliki
nama yang berbeda.
• Sintaks error dapat muncul karena PL / SQL cek
database pertama untuk kolom dalam tabel.
• Nama-nama variabel lokal dan formal parameter didahulukan dari nama-nama
tabel database.
• Nama-nama kolom tabel database mendahului nama variabel lokal.
SQL kursor
• kursor adalah area kerja pribadi SQL.
• Ada dua jenis kursor:
- Implisit kursor
- Eksplisit kursor
• Server Oracle menggunakan cursor implisit untuk mengurai
dan mengeksekusi pernyataan SQL Anda.
• cursor eksplisit secara eksplisit dinyatakan oleh
programmer.
SQL kursor Atribut
Menggunakan atribut kursor SQL, Anda dapat menguji
hasil dari pernyataan SQL Anda.
SQL% ROWCOUNT Jumlah baris yang terpengaruh oleh pernyataan SQL yang paling terakhir (sebuah
nilai integer)
SQL% FOUND adalah atribut Boolean yang mengevaluasi untuk TRUE jika SQL yang paling terakhir
pernyataan mempengaruhi satu atau lebih baris
NOTFOUND% SQL atribut Boolean yang mengevaluasi untuk TRUE jika SQL yang paling terakhir
pernyataan tidak mempengaruhi baris
SQL% ISOPEN Selalu mengevaluasi ke FALSE karena PL / SQL menutup kursor implisit
segera setelah mereka dieksekusi
Hapus baris yang memiliki karyawan ID tertentu dari tabel KARYAWAN. Mencetak jumlah baris dihapus.
Contoh:
VARIABLE rows_deleted VARCHAR2(30)
DECLARE
v_employee_id employees.employee_id%TYPE := 176;
BEGIN
DELETE FROM employees
WHERE employee_id = v_employee_id;
:rows_deleted := (SQL%ROWCOUNT ||
' row deleted.');
END;
/
PRINT rows_deleted
Kontrol Transaksi Laporan
• Memulai transaksi dengan perintah DML pertama
mengikuti COMMIT atau ROLLBACK.
• Gunakan COMMIT dan ROLLBACK pernyataan SQL untuk
menghentikan transaksi eksplisit.
Tidak ada komentar:
Posting Komentar