Prosedur eksternal dan SQL dapat dibuat dengan parameter opsional. Parameter prosedur opsional ditentukan untuk memiliki nilai default
Misalkan Anda memiliki prosedur yang didefinisikan dengan parameter sebagai berikut. Apakah ini merupakan bagian dari prosedur SQL atau pernyataan prosedur eksternal tidak masalah;
CREATE PROCEDURE UPDATE_EMPLOYEE_INFO
[IN EMPLOYEE_NUMBER CHAR[10],
IN EMP_DEPT CHAR[3],
IN PHONE_NUMBER CHAR[4]]
. . .
Prosedur ini telah digunakan sejak lama dan dipanggil dari banyak tempat. Sekarang, seseorang telah menyarankan bahwa prosedur ini akan berguna untuk memperbarui beberapa kolom lain dalam tabel yang sama, PEKERJAAN dan EDLEVEL. Menemukan dan mengubah semua panggilan ke prosedur ini adalah pekerjaan besar, tetapi jika Anda menambahkan parameter baru sehingga memiliki nilai default, itu sangat mudah
Definisi parameter dalam pernyataan CREATE PROCEDURE berikut akan mengizinkan semua kolom kecuali nomor karyawan untuk diteruskan secara opsional
CREATE OR REPLACE PROCEDURE UPDATE_EMPLOYEE_INFO
[IN EMPLOYEE_NUMBER CHAR[10],
IN EMP_DEPT CHAR[3] DEFAULT NULL,
IN PHONE_NUMBER CHAR[4] DEFAULT NULL,
IN JOB CHAR[8] DEFAULT NULL,
IN EDLEVEL SMALLINT DEFAULT NULL]
. . .
_Kode untuk prosedur ini, baik rutin SQL atau program eksternal, perlu dimodifikasi untuk menangani parameter baru dan memproses dua parameter yang ada dengan benar saat nilai NULL dilewatkan. Karena parameter default bersifat opsional, setiap panggilan yang ada ke prosedur ini tidak perlu diubah; . Setiap penelepon yang membutuhkan parameter baru dapat memasukkannya ke dalam pernyataan SQL CALL
Meskipun contoh ini menggunakan NULL untuk semua nilai default, hampir semua ekspresi dapat digunakan. Ini bisa berupa konstanta sederhana atau kueri kompleks. Itu tidak dapat mereferensikan salah satu parameter lainnya
Ada beberapa cara agar default digunakan untuk pernyataan CALL
- Abaikan parameter di bagian akhir yang tidak perlu Anda gunakan.
Default akan digunakan untuk parameter JOB dan EDLEVELCALL UPDATE_EMPLOYEE_INFO['123456', 'D11', '4424']
- Gunakan kata kunci DEFAULT untuk setiap parameter yang dihilangkan.
Semua parameter diwakili dalam pernyataan ini. Nilai default akan digunakan untuk parameter EMP_DEPT, JOB, dan EDLEVELCALL UPDATE_EMPLOYEE_INFO['123456', DEFAULT, '4424', DEFAULT, DEFAULT]
- Sebutkan secara eksplisit beberapa argumen dengan nama parameter yang sesuai dan abaikan parameter yang tidak digunakan.
_Dengan menggunakan nama parameter, tiga parameter lainnya tidak perlu direpresentasikan dalam pernyataan CALL ini. Nilai default akan digunakan untuk parameter EMP_DEPT, PHONE_NUMBER, dan JOBCALL UPDATE_EMPLOYEE_INFO['123456', EDLEVEL => 18]
Argumen bernama bisa dalam urutan apapun dalam pernyataan CALL. Argumen tanpa nama harus cocok dengan urutan definisi parameter untuk prosedur dan harus ditentukan sebelum argumen bernama. Setelah argumen bernama digunakan dalam pernyataan, semua argumen yang mengikutinya juga harus diberi nama. Parameter apa pun yang tidak memiliki argumen dalam pernyataan CALL harus memiliki default yang ditentukan
Dalam contoh berikut, prosedur membuat tabel yang berisi semua karyawan di departemen tertentu. Skema tempat ia dibuat selalu dikodekan keras untuk lingkungan tempat ia digunakan. Namun, untuk pengujian, akan lebih mudah membuat tabel dalam skema pengujian
CREATE OR REPLACE PROCEDURE CREATE_DEPT_TABLE2
[IN P_DEPT CHAR[3],
IN SCHEMA_NAME VARCHAR[128] DEFAULT 'CORPDATA']
BEGIN
DECLARE DYNAMIC_STMT VARCHAR[1000];
DECLARE MESSAGE CHAR[20];
DECLARE TABLE_NAME VARCHAR[200];
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
SET MESSAGE = 'ok';
SET TABLE_NAME = '"' CONCAT SCHEMA_NAME CONCAT
'".DEPT_' CONCAT P_DEPT CONCAT '_T';
SET DYNAMIC_STMT = 'DROP TABLE ' CONCAT TABLE_NAME;
EXECUTE IMMEDIATE DYNAMIC_STMT;
SET DYNAMIC_STMT = 'CREATE TABLE ' CONCAT TABLE_NAME CONCAT
'[ EMPNO CHAR[6] NOT NULL,
FIRSTNME VARCHAR[12] NOT NULL,
MIDINIT CHAR[1] NOT NULL,
LASTNAME CHAR[15] NOT NULL,
SALARY DECIMAL[9,2]]';
EXECUTE IMMEDIATE DYNAMIC_STMT;
SET DYNAMIC_STMT = 'INSERT INTO ' CONCAT TABLE_NAME CONCAT
'SELECT EMPNO, FIRSTNME, MIDINIT, LASTNAME, SALARY
FROM CORPDATA.EMPLOYEE
WHERE WORKDEPT = ?';
PREPARE INSERT_INTO_DEPARTMENT_STMT FROM DYNAMIC_STMT;
EXECUTE INSERT_INTO_DEPARTMENT_STMT USING P_DEPT;
END;
Parameter kedua ditentukan untuk meneruskan nama skema. Ini memiliki default 'CORPDATA'. Ini adalah nilai yang telah digunakan oleh prosedur di masa laluSaat dijalankan di lingkungan produksi, pernyataan CALL mungkin.
CALL CREATE_DEPT_TABLE2['D21']
Karena parameter SCHEMA_NAME tidak ditentukan, nilai parameter default digunakan. Tabel DEPT_D21 dibuat di CORPDATASaat dijalankan di lingkungan pengujian, pernyataan CALL mungkin.
CALL CREATE_DEPT_TABLE2['D21', 'TESTSCHEMA']
Ini membuat tabelDEPT_D21 dalam skema yang ditentukan, TESTSCHEMA