埋込みSQLをよく理解する方法の1つは、サンプル・プログラムの例を学習することです。
WHENEVER
文によるエラーを処理するために、次のプログラムではOracleに接続し、ユーザーに従業員番号の入力を要求して、データベースに従業員の名前、給料、歩合を問合せ、その情報を表示して終了します。
-- declare host and indicator variables EXEC SQL BEGIN DECLARE SECTION; username CHARACTER(20); password CHARACTER(20); emp_number INTEGER; emp_name CHARACTER(10); salary REAL; commission REAL; ind_comm SMALLINT; -- indicator variable EXEC SQL END DECLARE SECTION; -- copy in the SQL Communications Area EXEC SQL INCLUDE SQLCA; display 'Username? '; read username; display 'Password? '; read password; -- handle processing errors EXEC SQL WHENEVER SQLERROR DO sql_error; -- log on to Oracle EXEC SQL CONNECT :username IDENTIFIED BY :password; display 'Connected to Oracle'; display 'Employee number? '; read emp_number; -- query database for employee's name, salary, and commission -- and assign values to host variables EXEC SQL SELECT ENAME, SAL, COMM INTO :emp_name, :salary, :commission:ind_comm FROM EMP WHERE EMPNO = :emp_number; display 'Employee Salary Commission'; display '-------- ------ ----------'; -- display employee's name, salary, and commission (if not null) IF ind_comm = -1 THEN -- commission is null display emp_name, salary, 'Not applicable'; ELSE display emp_name, salary, commission; ENDIF; -- release resources and log off the database EXEC SQL COMMIT WORK RELEASE; display 'Have a good day'; exit program; ROUTINE sql_error BEGIN -- avoid an infinite loop if the rollback results in an error EXEC SQL WHENEVER SQLERROR CONTINUE; -- release resources and log off the database EXEC SQL ROLLBACK WORK RELEASE; display 'Processing error'; exit program with an error; END sql_error;