SELECT (embedded) Examples
The following examples illustrate the non-cursor SELECT statement:
1. Retrieve the name and salary of an employee. Drop locks by committing the following transaction.
EXEC SQL SELECT ename, sal
into :namevar, :salvar
from employee
where eno = :numvar;
exec sql commit;
2. Select all columns in a row into a host language variable structure. (The emprec structure has members that correspond in name and type to columns of the employee table.)
EXEC SQL SELECT *
into :emprec
from employee
where eno = 23;
3. Select a constant into a variable.
EXEC SQL SELECT 'Name: ', ename
into :title, :ename
from employee
where eno >= 148 and age = :age;
4. Select the row in the employee table whose number and name correspond to the variables, numvar and namevar. The columns are selected into a host structure called emprec. Because this statement is issued many times (in a subprogram, perhaps), it is formulated as a repeat query.
EXEC SQL REPEATED SELECT *
INTO :emprec
FROM employee
WHERE eno = :numvar AND ename = :namevar;
5. Example of a select loop: insert new employees, and select all employees and generate a report. If an error occurs during the process, end the retrieval and back out the changes. No database statements are allowed inside the select loop (BEGIN-END block).
error = 0;
EXEC SQL INSERT INTO employee
SELECT * FROM newhires;
EXEC SQL SELECT eno, ename, eage, esal, dname
INTO :eno, :ename, :eage, :esal, :dname
FROM employee e, dept d
WHERE e.edept = d.deptno
GROUP BY ename, dname
EXEC SQL BEGIN;
generate report of information;
if error condition then
error = 1;
EXEC SQL ENDSELECT;
end if;
EXEC SQL END;
/*
** Control transferred here by completing the
** retrieval or because the endselect statement
** was issued.
*/
if error = 1
print 'Error encountered after row',
sqlca.sqlerrd(3);
EXEC SQL ROLLBACK;
else
print 'Successful addition and reporting';
EXEC SQL COMMIT;
end if;
6. The following SELECT statement uses a string variable to substitute for the complete search condition. The variable search_condition is constructed from an interactive forms application in query mode, and during the select loop the employees who satisfy the qualification are displayed.
run forms in query mode;
construct search_condition of employees;
EXEC SQL SELECT *
INTO :emprec
FROM employee
WHERE :search_condition;
EXEC SQL BEGIN;
load emprec into a table field;
EXEC SQL END;
display table field for browsing;
7. This example illustrates session switching inside a select loop. The main program processes sales orders and calls the new_customer subroutine for every new customer.
The main program:
...
EXEC SQL INCLUDE SQLCA;
EXEC SQL BEGIN DECLARE SECTION;
/* Include output of dclgen for declaration of record order_rec */
EXEC SQL INCLUDE 'decls';
EXEC SQL END DECLARE SECTION;
EXEC SQL CONNECT customers session 1;
EXEC SQL CONNECT sales session 2;
...
EXEC SQL SELECT * INTO :order_rec FROM orders;
EXEC SQL BEGIN;
if (order_rec.new_customer = 1) then
call new_customer(order_rec);
endif
process order;
EXEC SQL END;
...
EXEC SQL DISCONNECT;
The subroutine, new_customer, which is from the select loop, contains the session switch:
subroutine new_customer(record order_rec)
begin;
EXEC SQL SET_SQL(session = 1);
EXEC SQL INSERT INTO accounts
VALUES (:order_rec);
process any errors;
EXEC SQL SET_SQL(session = 2);
/* Reset status information before resuming select loop */
sqlca.sqlcode = 0;
sqlca.sqlwarn.sqlwarn0 = ' ';
end subroutine;