Interaksi PL/SQL dengan Oracle
6.5.6. Contoh Cursor Variable: Master Table dan Details Table
Mari kita perhatikan stored procedure di bawah ini, yang mencari database dari perpustakaan utama untuk buku, majalah, dan tape. Tabel master menyimpan judul dan kode kategori (dimana 1=buku, 2=majalah, 3=tape) untuk setiap item. Tiga tabel detail menyimpan informasi spesifik dari setiap kategori. Ketika tipanggil, procedure tesrebut mencari tabel master berdasarkan judul, menggunakan kode kategori terkait untuk membuka perintah OPEN-FOR, lalu membuka cursor variable untuk query dari tabel detail yang tepat.
CREATE PACKAGE cv_types AS TYPE LibCurTyp IS REF CURSOR; ... END cv_types;
CREATE PROCEDURE find_item ( title VARCHAR2(100), lib_cv IN OUT cv_types.LibCurTyp)
AS
code BINARY_INTEGER;
BEGIN
SELECT item_code
FROM titles INTO code
WHERE item_title = title;
IF code = 1 THEN
OPEN lib_cv FOR SELECT * FROM books
WHERE book_title = title;
ELSIF code = 2 THEN
OPEN lib_cv FOR SELECT * FROM periodicals
WHERE periodical_title = title;
ELSIF code = 3 THEN
OPEN lib_cv FOR SELECT * FROM tapes
WHERE tape_title = title;
END IF;
END find_item;
6.5.8. Contoh Cursor Variable: Pro*C Program
Program Pro*C berikut ini menanyakan kepada user untuk memilih database table, membuka cursor variable untuk query dari table tersebut, lalu melakukan fetch baris-baris data yang dihasilkan oleh query tersebut:
#include <stdio.h>
#include <sqlca.h>
void sql_error();
main()
{
char temp[32];
EXEC SQL BEGIN DECLARE SECTION;
char * uid = "scott/tiger";
SQL_CURSOR generic_cv; /* cursor variable */
int table_num; /* selector */
struct /* EMP record */
DEPT record */
{
int dept_num;
char dept_name[15];
char location[14];
} dept_rec;
struct /* BONUS record */
{
char emp_name[11];
char job_title[10];
float salary;
} bonus_rec;
EXEC SQL END DECLARE SECTION;
/* Handle Oracle errors. */
EXEC SQL WHENEVER SQLERROR DO sql_error();
/* Connect to Oracle. */
EXEC SQL CONNECT :uid;
/* Initialize cursor variable. */
EXEC SQL ALLOCATE :generic_cv;
/* Exit loop when done fetching. */
EXEC SQL WHENEVER NOT FOUND DO break;
for (;;)
{
printf("\n1 = EMP, 2 = DEPT, 3 = BONUS");
printf("\nEnter table number (0 to quit): ");
gets(temp);
table_num = atoi(temp);
if (table_num <= 0) break;
/* Open cursor variable. */
EXEC SQL EXECUTE
BEGIN
IF :table_num = 1 THEN
OPEN :generic_cv FOR SELECT * FROM emp;
ELSIF :table_num = 2 THEN
OPEN :generic_cv FOR SELECT * FROM dept;
ELSIF :table_num = 3 THEN
OPEN :generic_cv FOR SELECT * FROM bonus;
END IF;
END;
END-EXEC;
for (;;)
{
switch (table_num)
{
case 1: /* Fetch row into EMP record. */
EXEC SQL FETCH :generic_cv INTO :emp_rec;
break;
case 2: /* Fetch row into DEPT record. */
EXEC SQL FETCH :generic_cv INTO :dept_rec;
break;
case 3: /* Fetch row into BONUS record. */
EXEC SQL FETCH :generic_cv INTO :bonus_rec;
break;
}
/* Process data record here. */
}
/* Close cursor variable. */
EXEC SQL CLOSE :generic_cv;
}
exit(0);
}
void sql_error()
{
/* Handle SQL error here. */
}
6.5.9. Contoh Cursor Variable: Memanipulasi Host Variables di dalam SQL*Plus
Host variable adalah variable untuk mendeklarasikan di dalam host environment, lalu melewatkannya ke satu atau lebih program-program PL/SQL, yang mana kita dapat menggunakannya seperti variable lainnya. Dalam lingkungan SQL*Plus, untuk mendeklarasikan host variable, kita menggunakan perintah VARIABLE. Sebagai contoh, kita mendeklarasikan variable dengan tipe NUMBER seperti di bawah ini:
VARIABLE return_code NUMBER
Baik SQL*Plus maupun PL/SQL dapat mereferensi host variable, dan SQL*Plus dapat menampilkan nilainya. Namun, untuk mereferensi host variable di dalam PL/SQL, kita harus mengawali namanya dengan tanda titik dua (:), seperti ditunjukkan oleh contoh berikut ini:
DECLARE
...
BEGIN
:return_code := 0;
IF credit_check_ok(acct_no) THEN
:return_code := 1;
END IF;
...
END;
Untuk menampilkan nilai dari host variable di dalam SQL*Plus, kita menggunakan perintah PRINT, seperti berikut ini:
SQL> PRINT return_code RETURN_CODE ----------- 1
Tipe data SQL*Plus yaitu REFCURSOR mengijinkan kita untuk mendeklarasikan host variables, yang mana kita dapat menggunakannya untuk mengembalikan hasil-hasil query dari stored subprograms. Dalam script di bawah ini, kita mendeklarasikan host variable dari REFCURSOR. Kita menggunakan perintah SET AUTOPRINT dari SQL*Plus untuk menampilkan hasil-hasil query secara otomatis.
CREATE PACKAGE emp_data AS TYPE EmpRecTyp IS RECORD ( emp_id NUMBER(4), emp_name VARCHAR2(10), job_title VARCHAR2(9), dept_name VARCHAR2(14), dept_loc VARCHAR2(13)); TYPE EmpCurTyp IS REF CURSOR RETURN EmpRecTyp; PROCEDURE get_staff (dept_no IN NUMBER, emp_cv IN OUT EmpCurTyp); END; /
CREATE PACKAGE BODY emp_data AS
PROCEDURE get_staff (dept_no IN NUMBER, emp_cv IN OUT EmpCurTyp) IS
BEGIN
OPEN emp_cv FOR
SELECT empno, ename, job, dname, loc FROM emp, dept
WHERE emp.deptno = dept_no AND emp.deptno = dept.deptno
ORDER BY empno;
END;
END;
/
COLUMN EMPNO HEADING Number COLUMN ENAME HEADING Name COLUMN JOB HEADING JobTitle COLUMN DNAME HEADING Department COLUMN LOC HEADING Location
SET AUTOPRINT ON
VARIABLE cv REFCURSOR EXECUTE emp_data.get_staff(20, :cv)
 |
Buku ini membahas berbagai macam tip dan trik yang sangat berguna bagi Anda pembaca pengguna database Oracle. Pemula, mahasiswa, programmer, maupun database administrator yang mengharapkan solusi cepat dalam menangani permasalahan-permasalahan dalam penggunaan database Oracle dapat membaca buku ini. Anda tidak harus membaca buku ini secara urut karena buku ini di susun berdasarkan topik permasalahan, dan Anda dapat membaca topik-topik yang sesuai dengan permasalahan yang Anda hadapi. Dapat di beli di toko buku Gramedia, Gunung Agung, Uranus, Karisma dsb. Harga Rp. 24.800,- (157 halaman) |
[…] Interaksi PL/SQL dengan Oracle - 6,172 viewsFungsi Baris Tunggal - 4,245 viewsData Modelling dan Database Design - 3,954 viewsMengubah Table dan Constraint - 3,213 viewsTipe-tipe Data PL/SQL - 3,145 viewsPL/SQL Subprograms - 2,742 viewsMengontrol Akses User - 2,717 viewsCollections dan Records PL/SQL - 2,654 viewsMenampilkan Data - 2,572 viewsPendahuluan - 2,555 views […]