1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
|
-- Copyright (c) 1994 by Oracle Corporation
-- SIMPLE :
-- 1) Logon to the ORACLE database
-- 2) Prompt user for a name that exists
-- in the EMP table
-- 3) Do a simple SQL join that finds the
-- employee's department and location
with text_io, integer_text_io, sql_standard;
-- Note: the precompiler "with's" the other required ORACLE
-- packages.
procedure SIMPLE is
use text_io, integer_text_io;
-- declare host and program variables
ORACLE_ID : constant string := "SCOTT/TIGER";
ENAME : string(1..20);
ENAME_LEN : integer;
DEPT_NAME : string(1..14);
LOCATION : string(1..13);
EXEC SQL BEGIN DECLARE SECTION;
SQLCODE : sql_standard.sqlcode_type; -- for ANSI mode
SQLSTATE : sql_standard.sqlstate_type; -- ANSI mode
EXEC SQL END DECLARE SECTION;
SQL_ERROR : exception;
SQL_WARNING : exception;
-- Check to see if the last database
-- operation returned any rows.
-- (See Chapter 5 on error handling.)
function EMPLOYEE_EXISTS return boolean is
begin
return (not(ORACLE.ERROR.IF_NOT_FOUND));
end EMPLOYEE_EXISTS;
begin
-- Direct the precompiler to insert "if" logic that
-- checks the ORACLE return code and raises an exception
-- if needed.
EXEC SQL WHENEVER SQLERROR raise SQL_ERROR;
-- Check for warnings, such as data truncation, also.
EXEC SQL WHENEVER SQLWARNING raise SQL_WARNING;
-- Connect to ORACLE
EXEC SQL CONNECT :ORACLE_ID;
NEW_LINE;
PUT_LINE("Connected to ORACLE as " & ORACLE_ID);
NEW_LINE;
PUT_LINE("*** ORACLE DEMO #1 ***");
NEW_LINE;
loop
PUT("Enter employee last name (CR to exit): ");
GET_LINE(ENAME, ENAME_LEN);
exit when ENAME_LEN = 0;
-- SELECT statements that return one row can use a
-- simple SELECT statement. Otherwise, a cursor must be
-- declared for the SELECT, and a FETCH statement is used.
EXEC SQL SELECT INITCAP(loc), INITCAP(dname)
INTO :LOCATION, :DEPT_NAME
FROM emp, dept
WHERE dept.deptno = emp.deptno
AND EMP.ENAME =
upper(:ENAME(1..ENAME_LEN));
if EMPLOYEE_EXISTS then
NEW_LINE;
PUT("Employee ");
PUT(ENAME(1..ENAME_LEN));
PUT(" works for department " & DEPT_NAME);
PUT(" in " & LOCATION);
else
PUT_LINE("Sorry, no such employee (try ALLEN or JONES)");
NEW_LINE;
-- The following lines illustrate the use of SQLSTATE and SQLCODE
PUT("The SQLSTATE error code is: ");
PUT_LINE(string(sqlstate));
PUT("The SQLCODE error code is: ");
PUT(integer(sqlcode),1);
end if;
NEW_LINE; NEW_LINE;
end loop;
NEW_LINE;
PUT_LINE("Good bye.");
-- Disconnect from the database.
EXEC SQL COMMIT RELEASE;
exception
-- Turn off error checking, since we do not want
-- to raise an exception when logging out under
-- any circumstance.
EXEC SQL WHENEVER SQLERROR CONTINUE;
EXEC SQL WHENEVER SQLWARNING CONTINUE;
when SQL_ERROR =>
PUT_LINE(" ** ORACLE ERROR OCCURRED **");
NEW_LINE;
PUT_LINE(ORACLE.ERROR.MESSAGE);
EXEC SQL ROLLBACK RELEASE;
when SQL_WARNING =>
PUT_LINE(" ** ORACLE WARNING OCCURRED **");
NEW_LINE;
EXEC SQL ROLLBACK RELEASE;
end SIMPLE;
|
