ホスト変数は、ホスト言語とPL/SQLブロック間の通信を仲介します。ホスト変数はPL/SQLと共有できるので、PL/SQLではホスト変数の設定および参照ができます。
たとえば、ユーザーに情報の提供を求め、この情報をPL/SQLブロックに渡すためのホスト変数を使用するようにユーザーに指示できます。これにより、PL/SQLを使用してデータベースにアクセスし、ホスト変数を介してその結果をホスト・プログラムに戻せるようになります。
PL/SQLブロック内ではホスト変数はブロック全体のグローバル変数として扱われ、PL/SQL変数を使用できる位置であればどこにでも使用できます。SQL文内におけるホスト変数と同様、PL/SQLブロック内のホスト変数も先頭にコロンを付ける必要があります。コロンは、ホスト変数とPL/SQL変数およびデータベース・オブジェクトとを区切ります。
注意:
PL/SQLブロックでVARCHAR、CHARZまたはSTRING型を出力ホスト変数として使用するには、ブロックを入力する前に長さを初期化する必要があります。次の例のように、長さはVARCHAR、CHARZまたはSTRINGの宣言された(最大の)長さに設定してください。
次の例では、PL/SQLにおけるホスト変数の使用方法を示します。プログラムでは、ユーザーに従業員番号の入力を求めるプロンプトが表示され、その番号に応じて、従業員の役職名、雇用日、給与が表示されます。
char username[100], password[20]; char job_title[20], hire_date[9], temp[32]; int emp_number; float salary; #include <sqlca.h> printf("Username? \n"); gets(username); printf("Password? \n"); gets(password); EXEC SQL WHENEVER SQLERROR GOTO sql_error; EXEC SQL CONNECT :username IDENTIFIED BY :password; printf("Connected to Oracle\n"); for (;;) { printf("Employee Number (0 to end)? "); gets(temp); emp_number = atoi(temp); if (emp_number == 0) { EXEC SQL COMMIT WORK RELEASE; printf("Exiting program\n"); break; } /*-------------- 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 -----------------*/ printf("Number Job Title Hire Date Salary\n"); printf("------------------------------------\n"); printf("%6d %8.8s %9.9s %6.2f\n", emp_number, job_title, hire_date, salary); } ... exit(0); sql_error: EXEC SQL WHENEVER SQLERROR CONTINUE; EXEC SQL ROLLBACK WORK RELEASE; printf("Processing error\n"); exit(1);
ホスト変数emp_numberがPL/SQLブロックが入力される前に設定され、ホスト変数job_title、hire_dateおよびsalaryがブロック内で設定されていることに注意してください。
次の例では、ユーザーに銀行口座番号、取引の種類、取引金額の入力を求めるプロンプトが表示されてから、口座に記帳されます。口座が存在しない場合、例外が発生します。取引が完了すると、そのステータスが表示されます。
#include <stdio.h> #include <sqlca.h> char username[20]; char password[20]; char status[80]; char temp[32]; int acct_num; double trans_amt; void sql_error(); main() { char trans_type; strcpy(password, "TIGER"); strcpy(username, "SCOTT"); EXEC SQL WHENEVER SQLERROR DO sql_error(); EXEC SQL CONNECT :username IDENTIFIED BY :password; printf("Connected to Oracle\n"); for (;;) { printf("Account Number (0 to end)? "); gets(temp); acct_num = atoi(temp); if(acct_num == 0) { EXEC SQL COMMIT WORK RELEASE; printf("Exiting program\n"); break; } printf("Transaction Type - D)ebit or C)redit? "); gets(temp); trans_type = temp[0]; printf("Transaction Amount? "); gets(temp); trans_amt = atof(temp); /*----------------- 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 ----------------------- */ printf("\nStatus: %s\n", status); } exit(0); } void sql_error() { EXEC SQL WHENEVER SQLERROR CONTINUE; EXEC SQL ROLLBACK WORK RELEASE; printf("Processing error\n"); exit(1); }
VARCHARデータ型は、可変長文字列の宣言に使用できます。VARCHARが入力ホスト変数の場合は、どのくらいの長さを前提にすればよいかをOracleに通知する必要があります。したがって、文字列コンポーネントに格納される値の実際の長さに、長さコンポーネントを設定してください。
VARCHARが出力ホスト変数の場合は、Oracleで自動的に長さコンポーネントが設定されます。ただし、PL/SQLブロックでVARCHAR(CHARZおよびSTRING)出力ホスト変数を使用するには、ブロックを入力する前に長さコンポーネントを初期化する必要があります。したがって、次の例に示すとおり、長さコンポーネントはVARCHARの宣言された(最大の)長さに設定してください。
int emp_number; varchar emp_name[10]; float salary; ... emp_name.len = 10; /* initialize length component */ EXEC SQL EXECUTE BEGIN SELECT ename, sal INTO :emp_name, :salary FROM emp WHERE empno = :emp_number; ... END; END-EXEC; ...
PL/SQLブロックでは、Cポインタまたは配列構文を使用しないでください。PL/SQLコンパイラはCホスト変数の式を認識できないため、解析できません。たとえば、次の例は無効です。
EXEC SQL EXECUTE BEGIN :x[5].name := 'SCOTT'; ... END; END-EXEC;
構文エラーを回避するには、プレースホルダ(一時変数)を使用して、構造体の移入を行う構造体フィールドのアドレスを保持します。次の例は有効です。
name = x[5].name ; EXEC SQL EXECUTE BEGIN :name := ...; ... END; END-EXEC;