Release 1.8
Part Number A87540-01
 Home |
 Book List |
 Contents |
 Index |
 Master Index |
 Feedback |
| Pro*PL/I Supplement to the Oracle Precompilers Guide Release 1.8 Part Number A87540-01 |
|
This chapter provides several embedded SQL programs to guide you in writing your own. These programs illustrate the key concepts and features of Pro*PL/I programming and demonstrate techniques that let you take full advantage of SQL's power and flexibility.
Each sample program in this chapter is available online. The table below shows the usual filenames of the sample programs. However, the exact filename and storage location of the online files can be system dependent. Check the Oracle installation or user's guide for your system.
/***************************************************************** This program connects to Oracle, prompts the user for an employee number, queries the database for the employee's name, salary, and commission, then displays the result. It continues until the user enters a 0 for the employee number. *****************************************************************/ QUERYEX: PROCEDURE OPTIONS(MAIN); EXEC SQL BEGIN DECLARE SECTION; DCL USERNAME CHAR(10) VARYING, PASSWORD CHAR(10) VARYING, EMP_NUMBER BIN FIXED(31), EMP_NAME CHAR(10) VARYING, SALARY DECIMAL FLOAT(6), COMMISSION DECIMAL FLOAT(6); EXEC SQL END DECLARE SECTION; DCL TOTAL BIN FIXED(31); EXEC SQL INCLUDE SQLCA; /* log in to Oracle */ USERNAME = 'SCOTT'; PASSWORD = 'TIGER'; EXEC SQL WHENEVER SQLERROR DO CALL SQLERR; EXEC SQL CONNECT :USERNAME IDENTIFIED BY :PASSWORD; PUT SKIP EDIT('Connected to Oracle as user: ',USERNAME)(A, A); TOTAL = 0; LOOP: DO WHILE (1=1); PUT SKIP(2) LIST('Enter employee number (0 to exit): '); GET LIST(EMP_NUMBER); IF (EMP_NUMBER = 0) THEN LEAVE LOOP; EXEC SQL WHENEVER NOT FOUND GOTO NOTFND; EXEC SQL SELECT ENAME, SAL, NVL(COMM,0) INTO :EMP_NAME, :SALARY, :COMMISSION FROM EMP WHERE EMPNO = :EMP_NUMBER; PUT SKIP(2) LIST('Employee Name Salary Commission'); PUT SKIP LIST('------------- ------ ----------'); PUT SKIP EDIT(EMP_NAME, SALARY, COMMISSION) (A(13), X(2), F(7,2), X, F(9,2)); TOTAL = TOTAL + 1; GOTO LOOP; NOTFND: PUT SKIP LIST('Not a valid employee number - try again.'); END; PUT SKIP(2) LIST('Total number queried was ', TOTAL, '.'); PUT SKIP(2) LIST('Have a good day.'); EXEC SQL COMMIT WORK RELEASE; /* log off Oracle */ STOP; SQLERR: PROCEDURE; EXEC SQL WHENEVER SQLERROR CONTINUE; PUT SKIP(2) LIST('Oracle error detected:'); PUT SKIP(2) LIST(SQLCA.SQLERRM); EXEC SQL ROLLBACK WORK RELEASE; STOP; END SQLERR; END QUERYEX;
/***************************************************************** This program logs on to Oracle, declares and opens a cursor, fetches the names, salaries, and commissions of all salespeople, displays the results, then closes the cursor. *****************************************************************/ CURSDEM: PROCEDURE OPTIONS(MAIN); EXEC SQL BEGIN DECLARE SECTION; DCL USERNAME CHAR(10) VARYING, PASSWORD CHAR(10) VARYING, EMP_NAME CHAR(10) VARYING, SALARY DECIMAL FLOAT(6), COMMISSION DECIMAL FLOAT(6); EXEC SQL END DECLARE SECTION; EXEC SQL INCLUDE SQLCA; /* log in to Oracle */ USERNAME = 'SCOTT'; PASSWORD = 'TIGER'; EXEC SQL WHENEVER SQLERROR DO CALL SQLERR; EXEC SQL CONNECT :USERNAME IDENTIFIED BY :PASSWORD; PUT SKIP EDIT('Connected to Oracle as user: ', USERNAME)(A, A); /* Establish the cursor. */ EXEC SQL DECLARE salespeople CURSOR FOR SELECT ENAME, SAL, COMM FROM EMP WHERE JOB LIKE 'SALES%'; EXEC SQL OPEN salespeople; PUT SKIP(2) LIST('Employee Name Salary Commission'); PUT SKIP LIST('------------- ------ ----------'); LOOP: DO WHILE (1 = 1); EXEC SQL WHENEVER NOT FOUND GOTO NOTFND; EXEC SQL FETCH salespeople INTO :EMP_NAME, :SALARY, :COMMISSION; PUT SKIP EDIT(EMP_NAME, SALARY, COMMISSION) (A(13), X(2), F(7,2), X(1), F(9,2)); GOTO LOOP; NOTFND: LEAVE LOOP; END; EXEC SQL CLOSE salespeople; PUT SKIP(2) LIST('Have a good day.'); EXEC SQL COMMIT WORK RELEASE; /* log off Oracle */ STOP; SQLERR: PROCEDURE; EXEC SQL WHENEVER SQLERROR CONTINUE; PUT SKIP(2) LIST('Oracle error detected:'); PUT SKIP(2) LIST(SQLCA.SQLERRM); EXEC SQL ROLLBACK WORK RELEASE; STOP; END SQLERR; END CURSDEM;
/************************************************************** This program logs on to Oracle, declares and opens a cursor, fetches in batches using arrays, and prints the results using the function print_rows(). **************************************************************/ ARRDEM: PROCEDURE OPTIONS(MAIN); EXEC SQL BEGIN DECLARE SECTION; DCL USERNAME CHAR(10) VARYING, PASSWORD CHAR(10) VARYING, EMP_NAME(5) CHAR(10) VARYING, EMP_NUMBER(5) BIN FIXED(31), SALARY(5) DECIMAL FLOAT(6); EXEC SQL END DECLARE SECTION; DCL NUM_RET BIN FIXED(31); EXEC SQL INCLUDE SQLCA; /* log in to Oracle */ USERNAME = 'SCOTT'; PASSWORD = 'TIGER'; EXEC SQL WHENEVER SQLERROR DO CALL SQLERR; EXEC SQL CONNECT :USERNAME IDENTIFIED BY :PASSWORD; PUT SKIP EDIT('Connected to Oracle as user: ', USERNAME)(A, A); /* Establish the cursor. */ EXEC SQL DECLARE c1 CURSOR FOR SELECT EMPNO, ENAME, SAL FROM EMP; EXEC SQL OPEN c1; NUM_RET = 0; /* initialize number of rows returned */ LOOP: DO WHILE(1 = 1); /* terminate when NOT FOUND is raised */ EXEC SQL WHENEVER NOT FOUND GOTO NOTFND; EXEC SQL FETCH c1 INTO :EMP_NUMBER, :EMP_NAME, :SALARY; CALL PRINT_ROWS(SQLCA.SQLERRD(3) - NUM_RET); NUM_RET = SQLCA.SQLERRD(3); END; NOTFND: /* Print remaining rows from last fetch, if any. */ IF ((SQLCA.SQLERRD(3) - NUM_RET) >> 0) THEN CALL PRINT_ROWS(SQLCA.SQLERRD(3) - NUM_RET); EXEC SQL CLOSE c1; PUT SKIP(2) LIST('Have a good day.'); EXEC SQL COMMIT WORK RELEASE; /* log off Oracle */ STOP; PRINT_ROWS: PROCEDURE(N); DCL (N,I) BIN FIXED (31); PUT SKIP; PUT SKIP(2) LIST('Employee Number Employee Name Salary'); PUT SKIP LIST('--------------- ------------- ------'); DO I = 1 TO N BY 1; PUT SKIP EDIT(EMP_NUMBER(I), EMP_NAME(I), SALARY(I)) (F(4), X(13), A(13), X(2), F(7,2)); END; END PRINT_ROWS; SQLERR: PROCEDURE; EXEC SQL WHENEVER SQLERROR CONTINUE; PUT SKIP LIST('Oracle error detected:'); PUT SKIP(2) LIST(SQLCA.SQLERRM); EXEC SQL ROLLBACK RELEASE; STOP; END SQLERR; END ARRDEM;
/************************************************************** This program features an in-depth example of the use of Datatype Equivalencing. After logging in, it creates a new table in the SCOTT account, IMAGE, and simulates placement of bitmap images of employees in it. Later, when an employee number is entered, his/her bitmap is selected back out of the IMAGE table, and pseudo-displayed on the terminal screen. **************************************************************/ DTYEQV: PROCEDURE OPTIONS(MAIN); EXEC SQL BEGIN DECLARE SECTION; DCL USERNAME CHAR(10) VARYING, PASSWORD CHAR(10) VARYING, EMP_NUMBER BIN FIXED(31), EMP_NAME CHAR(10) VARYING, SALARY DECIMAL FLOAT(6), COMMISSION DECIMAL FLOAT(6); DCL BUFFER CHAR(8192); EXEC SQL VAR BUFFER IS LONG RAW; DCL SELECTION BIN FIXED(31); EXEC SQL END DECLARE SECTION; DCL REPLY CHAR(10) VARYING; EXEC SQL INCLUDE SQLCA; /* log in to Oracle */ USERNAME = 'SCOTT'; PASSWORD = 'TIGER'; EXEC SQL WHENEVER SQLERROR DO CALL SQLERR; EXEC SQL CONNECT :USERNAME IDENTIFIED BY :PASSWORD; PUT SKIP EDIT('Connected to Oracle as user: ', USERNAME)(A, A); PUT SKIP(2) LIST('Program is about to drop the IMAGE table - OK [y/N]? '); GET EDIT(REPLY)(A(1)); IF ((REPLY ^= 'Y') & (REPLY ^= 'y')) THEN CALL SIGNOFF; EXEC SQL WHENEVER SQLERROR CONTINUE; EXEC SQL DROP TABLE IMAGE; IF (SQLCA.SQLCODE = 0) THEN PUT SKIP(2) LIST('Table IMAGE has been dropped - creating new table.'); ELSE IF (SQLCA.SQLCODE = -942) THEN PUT SKIP(2) LIST('Table IMAGE does not exist - creating new table.'); ELSE CALL SQLERR; EXEC SQL WHENEVER SQLERROR DO CALL SQLERR; EXEC SQL CREATE TABLE IMAGE (empno NUMBER(4) NOT NULL, bitmap LONG RAW); EXEC SQL DECLARE EMPCUR CURSOR FOR SELECT EMPNO, ENAME FROM EMP; EXEC SQL OPEN EMPCUR; PUT SKIP(2) LIST('INSERTing bitmaps into IMAGE for all employees ...'); PUT SKIP; GLOOP: DO WHILE (1 = 1); EXEC SQL WHENEVER NOT FOUND GOTO GNOTFND; EXEC SQL FETCH EMPCUR INTO :EMP_NUMBER, :EMP_NAME; PUT SKIP EDIT('Employee ', EMP_NAME)(A, A(10)); CALL GETIMG(EMP_NUMBER, BUFFER); EXEC SQL INSERT INTO IMAGE VALUES (:EMP_NUMBER, :BUFFER); PUT EDIT(' is done!')(A); GOTO GLOOP; GNOTFND: LEAVE GLOOP; END; EXEC SQL CLOSE EMPCUR; EXEC SQL COMMIT WORK; PUT SKIP(2) LIST('Done INSERTing bitmaps. Next, lets display some.'); SLOOP: DO WHILE (1 = 1); PUT SKIP(2) LIST('Enter employee number (0 to exit): '); GET LIST(SELECTION); IF (SELECTION = 0) THEN CALL SIGNOFF; EXEC SQL WHENEVER NOT FOUND GOTO SNOTFND; EXEC SQL SELECT EMP.EMPNO, ENAME, SAL, NVL(COMM,0), BITMAP INTO :EMP_NUMBER, :EMP_NAME, :SALARY, :COMMISSION, :BUFFER FROM EMP, IMAGE WHERE EMP.EMPNO = :SELECTION AND EMP.EMPNO = IMAGE.EMPNO; CALL SHWIMG(BUFFER); PUT SKIP(2) EDIT('Employee ', EMP_NAME)(A, A(10)); PUT EDIT(' has salary ', SALARY)(A, F(7,2)); PUT EDIT(' and commission ', COMMISSION)(A, F(7,2)); GOTO SLOOP; SNOTFND: PUT SKIP LIST('Not a valid employee number - try again.'); END; STOP; GETIMG: PROCEDURE(ENUM, BUF); DCL ENUM BIN FIXED(31), BUF CHAR(8192); DCL I BIN FIXED(31); DO I=1 TO 8192 BY 1; SUBSTR(BUF,I,1) = '*'; IF (MOD(I,256) = 0) THEN PUT EDIT('.')(A); END; END GETIMG; SHWIMG: PROCEDURE(BUF); DCL BUF CHAR(8192); DCL I BIN FIXED(31); PUT SKIP; DO I=1 TO 10 BY 1; PUT SKIP LIST(' *************************'); END; END SHWIMG; SIGNOFF: PROCEDURE; PUT SKIP(2) LIST('Have a good day.'); EXEC SQL COMMIT WORK RELEASE; STOP; END SIGNOFF; SQLERR: PROCEDURE; EXEC SQL WHENEVER SQLERROR CONTINUE; PUT SKIP(2) LIST('Oracle error detected:'); PUT SKIP(2) LIST(SQLCA.SQLERRM); EXEC SQL ROLLBACK WORK RELEASE; STOP; END SQLERR; END DTYEQV;
This user exit concatenates form fields. To call the user exit from a SQL*Forms trigger, use the syntax
user_exit('CONCAT field1, field2, ..., result_field');
where user_exit is a packaged procedure supplied with SQL*Forms and CONCAT is the name of the user exit. A sample form named CONCAT invokes the user exit. For more information about SQL*Forms user exits, see Chapter 10 in the Programmer's Guide to the Oracle Precompilers.
Note:The sample code listed is for a SQL*Forms user exit and is not intended to be compiled in the same manner as the other sample programs listed in this chapter.
CONCAT: PROCEDURE(CMD,CMDLEN,MSG,MSGLEN,QUERY) RETURNS(FIXED BINARY(31)); EXEC SQL BEGIN DECLARE SECTION; DCL FIELD CHARACTER(81) VARYING, VALUE CHARACTER(81) VARYING, OUTVALUE CHARACTER(241) VARYING; EXEC SQL END DECLARE SECTION; EXEC SQL INCLUDE SQLCA; EXEC SQL WHENEVER SQLERROR GOTO ER_EXIT; /* parameter declarations */ DCL CMD CHAR(80), MSG CHAR(80), CMDLEN FIXED BIN(31), MSGLEN FIXED BIN(31), QUERY FIXED BIN(31), /* local declarations */ CMDCNT FIXED BIN(31), I FIXED BIN(31), /* local copy of cmd */ LOCCMD CHAR(80), /* dynamically built error message to be returned to SQL*Forms */ ERRMSG CHAR(80), ERRLEN FIXED BIN(31); /* strip off "concat" keyword in the command string */ LOCCMD = SUBSTR(CMD, 8, CMDLEN-7); OUTVALUE = ''; I = INDEX(LOCCMD,','); DO WHILE(I >> 0); /* found field delimited by (,) */ FIELD = SUBSTR(LOCCMD, 1, I-1); /* field name minus (,) */ EXEC IAF GET :FIELD INTO :VALUE; OUTVALUE = OUTVALUE || VALUE; /* skip over (,) and following blank space */ CMDCNT = I + 2; /* take previous field off command line */ LOCCMD = SUBSTR(LOCCMD, CMDCNT, CMDLEN-I); I = INDEX(LOCCMD, ','); END; I = INDEX(LOCCMD, ' '); /* get last field concat */ FIELD = SUBSTR(LOCCMD, 1, I-1); EXEC IAF PUT :FIELD VALUES (:OUTVALUE); RETURN(SQL_IAPXIT_SUCCESS); ER_EXIT: ERRMSG = 'CONCAT: ' || SQLCA.SQLERRM; ERRLEN = 80; CALL SQLIEM(ADDR(ERRMSG), ADDR(ERRLEN)); RETURN(SQL_IAPXIT_FAILURE); END CONCAT;
Dynamic SQL Method 1 executes a SQL statement contained in a host character string that is constructed at runtime. The statement must not be a SELECT and must not contain input or output host variables. Method 1 has only one step:
EXEC SQL EXECUTE IMMEDIATE {:string_var | 'string_literal'};
This program demonstrates the use of dynamic SQL Method 1 to create a table, insert a row, commit the insert, and drop the table. It accesses Oracle through the SCOTT/TIGER account. It does not require user input or existing database tables. The program displays the SQL statements before their execution.
The program is available online in the file Sample6.
DYN1DEM: PROCEDURE OPTIONS(MAIN); /* Include the SQL Communications Area, a structure through which Oracle makes runtime status information such as error codes, warning flags, and diagnostic text available to the host program. */ EXEC SQL INCLUDE SQLCA; /* Include the Oracle Communications Area, a structure through which Oracle makes additional runtime status information available to the program. */ EXEC SQL INCLUDE ORACA; /* The ORACA=YES option must be specified to enable use of the ORACA. */ EXEC Oracle OPTION (ORACA=YES); /* Specifying the RELEASE_CURSOR=YES option instructs Pro*PL/I to release resources associated with embedded SQL statements after they are executed. This ensures that Oracle does not keep parse locks on tables after DML operations, so that subsequent DDL operations on those tables do not result in a "resource locked" Oracle run-time error. */ EXEC Oracle OPTION (RELEASE_CURSOR=YES); /* All host variables used in embedded SQL must appear in the DECLARE SECTION. */ EXEC SQL BEGIN DECLARE SECTION; DCL USERNAME CHAR(10) VARYING, PASSWORD CHAR(10) VARYING, SQLSTMT CHAR(80) VARYING; EXEC SQL END DECLARE SECTION; /* Branch to label 'SQL_ERR' if an Oracle error occurs. */ EXEC SQL WHENEVER SQLERROR GOTO SQL_ERR; /* Save text of current SQL statement in the ORACA if an error occurs. */ ORACA.ORASTXTF = 1; /* Connect to Oracle. */ USERNAME = 'SCOTT'; PASSWORD = 'TIGER'; EXEC SQL CONNECT :USERNAME IDENTIFIED BY :PASSWORD; PUT SKIP LIST('CONNECTED TO Oracle.'); /* Execute a string literal to create the table. */ PUT SKIP LIST('CREATE TABLE DYN1 (COL1 CHAR(4))'); EXEC SQL EXECUTE IMMEDIATE 'CREATE TABLE DYN1 (COL1 CHAR(4))'; /* Assign a SQL statement to the character string SQLSTMT. */ SQLSTMT = 'INSERT INTO DYN1 VALUES (''TEST'')'; PUT SKIP LIST(SQLSTMT); /* Execute sqlstmt to insert a row. This usage is "dynamic" because the SQL statement is a string variable whose contents the program may determine at runtime. */ EXEC SQL EXECUTE IMMEDIATE :SQLSTMT; /* Commit the insert. */ EXEC SQL COMMIT WORK; /* Change sqlstmt and execute it to drop the table. */ SQLSTMT = 'DROP TABLE DYN1'; PUT SKIP LIST(SQLSTMT); EXEC SQL EXECUTE IMMEDIATE :SQLSTMT; /* Commit any outstanding changes and disconnect from Oracle. */ EXEC SQL COMMIT RELEASE; PUT SKIP LIST('DISCONNECTED FROM Oracle.'); STOP; SQL_ERR: /* Oracle error handler. Print diagnostic text containing error message, current SQL statement, line number and file name of error. */ PUT SKIP(2) LIST(SQLCA.SQLERRM); PUT SKIP EDIT('IN "', ORACA.ORASTXT, '..."') (A, A(LENGTH(ORACA.ORASTXT)), A); PUT SKIP EDIT('ON LINE ', ORACA.ORASLNR, ' OF ', ORACA.ORASFNM) (A, F(3), A, A(LENGTH(ORACA.ORASFNM))); /* Disable Oracle error checking to avoid an infinite loop should another error occur within this routine. */ EXEC SQL WHENEVER SQLERROR CONTINUE; /* Roll back any outstanding changes and disconnect from Oracle. */ EXEC SQL ROLLBACK RELEASE; END DYN1DEM;
Dynamic SQL Method 2 processes a SQL statement contained in a host character string constructed at runtime. The statement must not be a SELECT but may contain input host variables. Method 2 has two steps:
EXEC SQL PREPARE statement_name FROM { :string_var | 'string_literal' }; EXEC SQL EXECUTE statement_name [USING :invar1[, :invar2...]];
This program demonstrates the use of dynamic SQL Method 2 to insert two rows into the EMP table and then delete them. It accesses Oracle through the SCOTT/TIGER account and requires the EMP table. It does not require user input. The program displays the SQL statements before their execution.
This program is available online in the file Sample7.
DYN2DEM: PROCEDURE OPTIONS(MAIN); /* Include the SQL Communications Area, a structure through which Oracle makes runtime status information such as error codes, warning flags, and diagnostic text available to the program. */ EXEC SQL INCLUDE SQLCA; /* All host variables used in embedded SQL must appear in the DECLARE SECTION. */ EXEC SQL BEGIN DECLARE SECTION; DCL USERNAME CHAR(10) VARYING, PASSWORD CHAR(10) VARYING, SQLSTMT CHAR(80) VARYING, EMPNO FIXED DECIMAL(4) INIT(1234), DEPTNO1 FIXED DECIMAL(2) INIT(97), DEPTNO2 FIXED DECIMAL(2) INIT(99); EXEC SQL END DECLARE SECTION; /* Branch to label 'sqlerror' if an Oracle error occurs. */ EXEC SQL WHENEVER SQLERROR GOTO SQL_ERR; /* Connect to Oracle. */ USERNAME = 'SCOTT'; PASSWORD = 'TIGER'; EXEC SQL CONNECT :USERNAME IDENTIFIED BY :PASSWORD; PUT SKIP LIST('CONNECTED TO Oracle.'); /* Assign a SQL statement to the character string SQLSTMT. Note that the statement contains two host variable placeholders, V1 and V2, for which actual input host variables must be supplied at the EXECUTE (see below). */ SQLSTMT = 'INSERT INTO EMP (EMPNO, DEPTNO) VALUES(:V1, :V2)'; /* Display the SQL statement and the values to be used for its input host variables. */ PUT SKIP LIST(SQLSTMT); PUT SKIP LIST(' V1 = ', EMPNO, ', V2 = ', DEPTNO1); /* The PREPARE statement associates a statement name with a string containing a SQL statement. The statement name is a SQL identifier, not a host variable, and therefore does not appear in the DECLARE SECTION. A single statement name may be PREPAREd more than once, optionally FROM a different string variable. */ EXEC SQL PREPARE S FROM :SQLSTMT; /* The EXECUTE statement performs a PREPAREd SQL statement USING the specified input host variables, which are substituted positionally for placeholders in the PREPAREd statement. For each occurrence of a placeholder in the statement there must be a variable in the USING clause, i.e. if a placeholder occurs multiple times in the statement then the corresponding variable must appear multiple times in the USING clause. The USING clause may be omitted only if the statement contains no placeholders. A single PREPAREd statement may be EXECUTEd more than once, optionally USING different input host variables. */ EXEC SQL EXECUTE S USING :EMPNO, :DEPTNO1; /* Increment empno and display new input host variables. */ EMPNO = EMPNO + 1; PUT SKIP LIST(' V1 = ', EMPNO, ', V2 = ', DEPTNO2); /* ReEXECUTE S to insert the new value of EMPNO and a different input host variable, DEPTNO2. A rePREPARE is not necessary. */ EXEC SQL EXECUTE S USING :EMPNO, :DEPTNO2; /* Assign a new value to sqlstmt. */ SQLSTMT = 'DELETE FROM EMP WHERE DEPTNO = :V1 OR DEPTNO = :V2'; /* Display the new SQL statement and the values to be used for its current input host variables. */ PUT SKIP LIST(SQLSTMT); PUT SKIP LIST(' V1 = ', DEPTNO1, ', V2 = ', DEPTNO2); /* RePREPARE S FROM the new sqlstmt. */ EXEC SQL PREPARE S FROM :SQLSTMT; /* EXECUTE the new S to delete the two rows previously inserted. */ EXEC SQL EXECUTE S USING :DEPTNO1, :DEPTNO2; /* Commit any outstanding changes and disconnect from Oracle. */ EXEC SQL COMMIT RELEASE; PUT SKIP LIST('Disconnected from Oracle.'); STOP; SQL_ERR: /* Oracle error handler. */ PUT SKIP(2) LIST(SQLCA.SQLERRM); /* Disable Oracle error checking to avoid an infinite loop should another error occur within this routine. */ EXEC SQL WHENEVER SQLERROR CONTINUE; /* Roll back any outstanding changes and disconnect from Oracle. */ EXEC SQL ROLLBACK RELEASE; END DYN2DEM;
Dynamic SQL Method 3 processes a SQL statement contained in a host character string constructed at runtime. The statement may be a SELECT, and may contain input host variables but not output host variables (the INTO clause is on the FETCH instead). This Dynamic SQL Method 3 example processes a query, and uses the following five steps:
EXEC SQL PREPARE statement_name FROM { :string_var | 'string_literal' }; EXEC SQL DECLARE cursor_name CURSOR FOR statement_name; EXEC SQL OPEN cursor_name [USING :invar1[,:invar2...]]; EXEC SQL FETCH cursor_name INTO :outvar1[,:outvar2...]; EXEC SQL CLOSE cursor_name;
This program demonstrates the use of dynamic SQL Method 3 to retrieve all the names from the EMP table. It accesses Oracle through the SCOTT/TIGER account and requires the EMP table. It does not require user input. The program displays the query and its results
The program is available online in the file Sample8.
DYN3DEM: PROCEDURE OPTIONS(MAIN); /* Include the SQL Communications Area, a structure through which Oracle makes runtime status information such as error codes, warning flags, and diagnostic text available to the program. */ EXEC SQL INCLUDE SQLCA; /* All host variables used in embedded SQL must appear in the DECLARE SECTION. */ EXEC SQL BEGIN DECLARE SECTION; DCL USERNAME CHAR(10) VARYING, PASSWORD CHAR(10) VARYING, SQLSTMT CHAR(80) VARYING, ENAME CHAR(10) VARYING, DEPTNO FIXED DECIMAL(2) INIT(10); EXEC SQL END DECLARE SECTION; /* Branch to label SQL_ERR: if an Oracle error occurs. */ EXEC SQL WHENEVER SQLERROR GOTO SQL_ERR; /* Connect to Oracle. */ USERNAME = 'SCOTT'; PASSWORD = 'TIGER'; EXEC SQL CONNECT :USERNAME IDENTIFIED BY :PASSWORD; PUT SKIP LIST('CONNECTED TO Oracle.'); /* Assign a SQL query to the character string SQLSTMT. Note that the statement contains one host variable placeholder, V1, for which an actual input host variable must be supplied at the OPEN (see below). */ SQLSTMT = 'SELECT ENAME FROM EMP WHERE DEPTNO = :V1'; /* Display the SQL statement and the value to be used for its current input host variable. */ PUT SKIP LIST(SQLSTMT); PUT SKIP LIST(' V1 = ', DEPTNO); /* The PREPARE statement associates a statement name with a string containing an SQL statement. The statement name is a SQL identifier, not a host variable, and therefore does not appear in the DECLARE SECTION. A single statement name may be PREPAREd more than once, optionally FROM a different string variable. */ EXEC SQL PREPARE S FROM :SQLSTMT; /* The DECLARE statement associates a cursor with a PREPAREd statement. The cursor name, like the statement name, does not appear in the DECLARE SECTION. A single cursor name may not be DECLAREd more than once. */ EXEC SQL DECLARE C CURSOR FOR S; /* The OPEN statement evaluates the active set of the PREPAREd query USING the specified input host variables, which are substituted positionally for placeholders in the PREPAREd query. For each occurrence of a placeholder in the statement there must be a variable in the USING clause. That is, if a placeholder occurs multiple times in the statement then the corresponding variable must appear multiple times in the USING clause. The USING clause may be omitted only if the statement contains no placeholders. OPEN places the cursor at the first row of the active set in preparation for a FETCH. A single DECLAREd cursor may be OPENed more than once, optionally USING different input host variables. */ EXEC SQL OPEN C USING :DEPTNO; /* Branch to label 'notfound' when all rows have been retrieved. */ EXEC SQL WHENEVER NOT FOUND GOTO N_FND; /* Loop until NOT FOUND condition is raised. */ DO WHILE (1 = 1); /* The FETCH statement places the SELECT list of the current row into the variables specified by the INTO clause then advances the cursor to the next row. If there are more SELECT list fields than output host variables, the extra fields will not be returned. More output host variables than SELECT list fields will result in an Oracle error. */ EXEC SQL FETCH C INTO :ENAME; PUT SKIP LIST(ENAME); END; N_FND: /* Print the cumulative number of rows processed by the current SQL statement. */ PUT SKIP LIST('QUERY RETURNED ', SQLCA.SQLERRD(3), ' ROW(S).'); /* The CLOSE statement releases resources associated with the cursor. */ EXEC SQL CLOSE C; /* Commit any outstanding changes and disconnect from Oracle. */ EXEC SQL COMMIT RELEASE; PUT SKIP LIST('DISCONNECTED FROM Oracle.'); STOP; SQL_ERR: /* Oracle error handler. Print diagnostic text containing error message. */ PUT SKIP(2) LIST(SQLCA.SQLERRM); /* Disable Oracle error checking to avoid an infinite loop should another error occur within this routine. */ EXEC SQL WHENEVER SQLERROR CONTINUE; /* Release resources associated with the cursor. */ EXEC SQL CLOSE C; /* Roll back any outstanding changes and disconnect from Oracle. */ EXEC SQL ROLLBACK RELEASE; END DYN3DEM;
Before trying the sample program, you must create a PL/SQL package named calldemo. You do that by running a script named CALLDEMO.SQL, which is supplied with Pro*C and shown below. The script can be found in the Pro*C demo library.
CREATE OR REPLACE PACKAGE calldemo AS TYPE char_array IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER; TYPE num_array IS TABLE OF FLOAT INDEX BY BINARY_INTEGER; PROCEDURE get_employees( dept_number IN number, -- department to query batch_size IN INTEGER, -- rows at a time found IN OUT INTEGER, -- rows actually returned done_fetch OUT INTEGER, -- all done flag emp_name OUT char_array, job OUT char_array, sal OUT num_array); END calldemo; / CREATE OR REPLACE PACKAGE BODY calldemo AS CURSOR get_emp (dept_number IN number) IS SELECT ename, job, sal FROM emp WHERE deptno = dept_number; -- Procedure "get_employees" fetches a batch of employee -- rows (batch size is determined by the client/caller -- of the procedure). It can be called from other -- stored procedures or client application programs. -- The procedure opens the cursor if it is not -- already open, fetches a batch of rows, and -- returns the number of rows actually retrieved. At -- end of fetch, the procedure closes the cursor. PROCEDURE get_employees( dept_number IN number, batch_size IN INTEGER, found IN OUT INTEGER, done_fetch OUT INTEGER, emp_name OUT char_array, job OUT char_array, sal OUT num_array) IS BEGIN IF NOT get_emp%ISOPEN THEN -- open the cursor if OPEN get_emp(dept_number); -- not already open END IF; -- Fetch up to "batch_size" rows into PL/SQL table, -- tallying rows found as they are retrieved. When all -- rows have been fetched, close the cursor and exit -- the loop, returning only the last set of rows found. done_fetch := 0; -- set the done flag FALSE found := 0; FOR i IN 1..batch_size LOOP FETCH get_emp INTO emp_name(i), job(i), sal(i); IF get_emp%NOTFOUND THEN -- if no row was found CLOSE get_emp; done_fetch := 1; -- indicate all done EXIT; ELSE found := found + 1; -- count row END IF; END LOOP; END; END; / /* * This program connects to Oracle, prompts the user for a * department number, uses a stored procedure to fetch Oracle * data into PL/SQL tables, returns the data in host arrays, then * displays the name, job title, and salary of each employee in * the department. * For this example to work, the package CALLDEMO must be in * the SCOTT schema, or SCOTT must have execute privileges on the * package. */ EXEC SQL BEGIN DECLARE SECTION; DCL USERNAME STATIC CHAR(10) VARYING, PASSWORD STATIC CHAR(10) VARYING, TABLE_SIZE STATIC BIN FIXED(31), DEPT_NUMBER STATIC BIN FIXED(31), DONE_FLAG STATIC BIN FIXED(31), NUM_RET STATIC BIN FIXED(31), EMP_NAME(10) STATIC CHAR(20) VARYING, JOB(10) STATIC CHAR(20) VARYING, SALARY(10) STATIC DECIMAL FLOAT(6); EXEC SQL END DECLARE SECTION; SAMP9: PROCEDURE OPTIONS(MAIN); /* connect to Oracle */ EXEC SQL INCLUDE SQLCA; USERNAME = 'SCOTT'; PASSWORD = 'TIGER'; EXEC SQL WHENEVER SQLERROR DO CALL SQLERR; EXEC SQL CONNECT :USERNAME IDENTIFIED BY :PASSWORD; PUT SKIP EDIT ('Connected to Oracle as user: ', USERNAME)(A, A); PUT SKIP(2) LIST('Enter the department number: '); GET LIST (DEPT_NUMBER); PUT SKIP; TABLE_SIZE = 2; DONE_FLAG = 0; CLOOP: DO WHILE (1 = 1); EXEC SQL EXECUTE BEGIN CALLDEMO.GET_EMPLOYEES ( :DEPT_NUMBER, :TABLE_SIZE, :NUM_RET, :DONE_FLAG, :EMP_NAME, :JOB, :SALARY); END; END-EXEC; CALL PRINT_ROWS(NUM_RET); IF (DONE_FLAG ^= 0) THEN CALL SIGNOFF; ELSE GOTO CLOOP; END; STOP; PRINT_ROWS: PROCEDURE(N); DCL N BIN FIXED(31), I BIN FIXED(31); IF N = 0 THEN DO; PUT SKIP(2) LIST('No rows retrieved.'); END; ELSE DO; PUT SKIP(2) EDIT('Got', N, ' rows.') (A, F(3)); PUT SKIP(2) LIST ('Employee name Job Salary'); PUT SKIP LIST ('-----------------------------------------------'); DO I = 1 TO N; PUT SKIP EDIT(EMP_NAME(I)) (A(20)); PUT EDIT (JOB(I)) (A(20)); PUT EDIT (SALARY(I)) (F(7,2)); END; END; END PRINT_ROWS; SIGNOFF: PROCEDURE; PUT SKIP(2) LIST('Have a good day.'); EXEC SQL COMMIT WORK RELEASE; STOP; END SIGNOFF; SQLERR: PROCEDURE; EXEC SQL WHENEVER SQLERROR CONTINUE; PUT SKIP(2) LIST('Oracle error detected:'); PUT SKIP(2) LIST(SQLCA.SQLERRM); EXEC SQL ROLLBACK WORK RELEASE; STOP; END SQLERR; END SAMP9;
|
 Copyright © 1996-2001 Oracle Corporation. All Rights Reserved. |
|