ホスト変数の使用について

ホスト変数は、ホスト言語とPL/SQLブロック間の通信を仲介します。ホスト変数はPL/SQLと共有できるので、PL/SQLではホスト変数の設定および参照ができます。

たとえば、ユーザーに情報の提供を求め、この情報をPL/SQLブロックに渡すためのホスト変数を使用するようにユーザーに指示できます。これにより、PL/SQLではデータベースにアクセスし、ホスト変数を使用して結果をホスト・プログラムに戻すことができます。

PL/SQLブロック内では、ホスト変数はブロック全体のグローバル変数として扱われ、PL/SQL変数を使用できる場所であればそのブロックのどこにでも使用できます。ただし、文字ホスト変数は、長さが255文字以内です。SQL文内におけるホスト変数と同様、PL/SQLブロック内のホスト変数も先頭にコロンを付ける必要があります。コロンは、ホスト変数とPL/SQL変数およびデータベース・オブジェクトとを区切ります。

例

次の例では、PL/SQLにおけるホスト変数の使用方法を示します。プログラムでは、ユーザーに従業員番号の入力を要求し、その番号に応じて従業員の役職名、雇用日および給与を表示します。

EXEC SQL BEGIN DECLARE SECTION;
 username CHARACTER(20);
 password CHARACTER(20);
 emp_number INTEGER;
 job_title CHARACTER(20);
 hire_date CHARACTER(9);
 salary REAL;
EXEC SQL END DECLARE SECTION;
EXEC SQL INCLUDE SQLCA;
display 'Username? ';
read username;
display 'Password? ';
read password;
EXEC SQL WHENEVER SQLERROR DO sql_error;
EXEC SQL CONNECT :username IDENTIFIED BY :password;
display 'Connected to Oracle';
LOOP
 display 'Employee Number (0 to end)? ';
 read emp_number;
 IF emp_number = 0 THEN
 EXEC SQL COMMIT WORK RELEASE;
 display 'Exiting program';
 exit program;
 ENDIF;
 ---------------- begin PL/SQL block -----------------
 EXEC SQL EXECUTE
 BEGIN
 SELECT job, hiredate, sal
 INTO :job_title, :hire_date, :salary
 FROM emp
 WHERE empno = :emp_number;
 END;
 END-EXEC;
 ---------------- end PL/SQL block -----------------
 display 'Number Job Title Hire Date Salary';
 display '------------------------------------';
 display emp_number, job_title, hire_date, salary;
ENDLOOP;
...
ROUTINE sql_error
BEGIN
 EXEC SQL WHENEVER SQLERROR CONTINUE;
 EXEC SQL ROLLBACK WORK RELEASE;
 display 'Processing error';
 exit program with an error;
END sql_error;

ホスト変数emp_numberがPL/SQLブロックが入力される前に設定され、ホスト変数job_title、hire_dateおよびsalaryがブロック内で設定されていることに注意してください。

複雑な例

次の例では、ユーザーに銀行口座番号、取引の種類、取引金額の入力を要求し、その後、口座の借方または貸方に記入します。口座が存在しない場合、例外が発生します。取引が完了すると、そのステータスを表示します。

EXEC SQL BEGIN DECLARE SECTION;
 username CHARACTER(20);
 password CHARACTER(20);
 acct_num INTEGER;
 trans_type CHARACTER(1);
 trans_amt REAL;
 status CHARACTER(80);
EXEC SQL END DECLARE SECTION;
EXEC SQL INCLUDE SQLCA;
display 'Username? ';
read username;
display 'Password? ';
read password;
EXEC SQL WHENEVER SQLERROR DO sql_error;
EXEC SQL CONNECT :username IDENTIFIED BY :password;
display 'Connected to Oracle';
LOOP
 display 'Account Number (0 to end)? ';
 read acct_num;
 IF acct_num = 0 THEN
 EXEC SQL COMMIT WORK RELEASE;
 display 'Exiting program';
 exit program;
 ENDIF;
 display 'Transaction Type - D)ebit or C)redit? '
 read trans_type;
 display 'Transaction Amount? '
 read trans_amt;
 --------------------- begin PL/SQL block -------------------
 EXEC SQL EXECUTE
 DECLARE
 old_bal NUMBER(9,2);
 err_msg CHAR(70);
 nonexistent EXCEPTION;
 BEGIN
 :trans_type := UPPER(:trans_type);
 IF :trans_type = 'C' THEN -- credit the account
 UPDATE accts SET bal = bal + :trans_amt
 WHERE acctid = :acct_num;
 IF SQL%ROWCOUNT = 0 THEN -- no rows affected
 RAISE nonexistent;
 ELSE
 :status := 'Credit applied';
 END IF;
 ELSIF :trans_type = 'D' THEN -- debit the account
 SELECT bal INTO old_bal FROM accts
 WHERE acctid = :acct_num;
 IF old_bal >= :trans_amt THEN -- enough funds
 UPDATE accts SET bal = bal - :trans_amt
 WHERE acctid = :acct_num;
 :status := 'Debit applied';
 ELSE
 :status := 'Insufficient funds';
 END IF;
 ELSE
 :status := 'Invalid type: ' || :trans_type;
 END IF;
 COMMIT;
 EXCEPTION
 WHEN NO_DATA_FOUND OR nonexistent THEN
 :status := 'Nonexistent account';
 WHEN OTHERS THEN
 err_msg := SUBSTR(SQLERRM, 1, 70);
 :status := 'Error: ' || err_msg;
 END;
 END-EXEC;
 ------------------- end PL/SQL block -----------------------
 display 'Status: ', status;
ENDLOOP;
ROUTINE sql_error
BEGIN
 EXEC SQL WHENEVER SQLERROR CONTINUE;
 EXEC SQL ROLLBACK WORK RELEASE; 
 display 'Processing error';
 exit program with an error;
END sql_error;

VARCHAR疑似型

プログラム要件への対応で、可変長文字列の宣言にVARCHAR擬似型が使用できると説明したことを思い出してください。VARCHARが入力ホスト変数の場合は、予想される長さをOracleに通知する必要があります。したがって、長さフィールドを文字列フィールドに格納される値の実際の長さに設定してください。

VARCHARが出力ホスト変数の場合、Oracleでは自動的に長さフィールドが設定されます。しかし、PL/SQLブロックでVARCHAR出力ホスト変数を使用するには、ブロックに入る前に、長さフィールドを初期化する必要があります。したがって、次の例に示すように、長さフィールドを宣言されたVARCHARの(最大の)長さに設定してください。

EXEC SQL BEGIN DECLARE SECTION;
 emp_number INTEGER;
 emp_name VARCHAR(10);
 salary REAL;
 ...
EXEC SQL END DECLARE SECTION;
...
set emp_name.len = 10; -- initialize length field
EXEC SQL EXECUTE
 BEGIN
 SELECT ename, sal INTO :emp_name, :salary
 FROM emp
 WHERE empno = :emp_number;
 ...
 END;
END-EXEC;