Example 1: Creating a stored module and stored procedure
The following example shows how to create a stored module and
stored procedure using interactive SQL:
SQL> CREATE MODULE testmod LANGUAGE SQL
cont> PROCEDURE testproc;
cont> COMMIT;
cont> END MODULE;
SQL> SHOW MODULE testmod
Module name is: TESTMOD
Source:
TESTMOD LANGUAGE SQL
Owner is:
Module ID is: 1
Example 2: Creating a stored module with SQL module language
The following code fragment shows how to create a stored module
as part of a procedure in a nonstored module:
PROCEDURE create_them
SQLCODE;
CREATE MODULE my LANGUAGE SQL AUTHORIZATION smith
PROCEDURE p1 ( :x CHAR(5) );
BEGIN
INSERT INTO s (snum) VALUES (:x);
END;
PROCEDURE p2 ( :y SMALLINT );
BEGIN
SELECT STATUS INTO :y FROM s LIMIT TO 1 ROW;
END;
PROCEDURE p3 (:x INT, :y SMALLINT );
BEGIN
INSERT INTO s (snum) VALUES (:x);
SELECT STATUS INTO :y FROM s WHERE snum = :x;
END;
PROCEDURE p4 (:x CHAR(5), :y CHAR(20) );
BEGIN
INSERT INTO s (snum,sname) VALUES (:x, :y);
SELECT sname INTO :y FROM s WHERE snum = :x;
END;
END MODULE;
Example 3: Creating a stored module containing a stored routines
SQL> CREATE MODULE utility_functions
cont> LANGUAGE SQL
cont> --
cont> -- Define a stored procedure.
cont> --
cont> PROCEDURE trace_date (:dt DATE);
cont> BEGIN
cont> TRACE :dt;
cont> END;
cont> --
cont> FUNCTION mdy (IN :dt DATE) RETURNS CHAR(10)
cont> COMMENT 'Returns the date in month/day/year format';
cont> BEGIN
cont> IF :dt IS NULL THEN
cont> RETURN '**/**/****';
cont> ELSE
cont> CALL trace_date (:dt);
cont> RETURN CAST(EXTRACT(MONTH FROM :dt) AS VARCHAR(2)) || '/' ||
cont> CAST(EXTRACT(DAY FROM :dt) AS VARCHAR(2)) || '/' ||
cont> CAST(EXTRACT(YEAR FROM :dt) AS VARCHAR(4));
cont> END IF;
cont> END;
cont> END MODULE;
Example 4: Using a stored function in a SELECT statement
SQL> SELECT mdy(job_end), job_end
cont> FROM job_history WHERE employee_id = '00164';
JOB_END
**/**/**** NULL
9/20/1981 20-Sep-1981
2 rows selected
Example 5: Using declared local temporary tables in stored
procedures
SQL> -- The following table must exist in order to execute the following
SQL> -- queries.
SQL> --
SQL> CREATE TABLE payroll
cont> (employee_id CHAR(5),
cont> hours_worked INTEGER,
cont> hourly_sal REAL,
cont> week_date CHAR(10));
SQL> COMMIT;
SQL> --
SQL> -- Create the module containing a declared local temporary table.
SQL> --
SQL> CREATE MODULE paycheck_decl_mod
cont> LANGUAGE SQL
cont> DECLARE LOCAL TEMPORARY TABLE module.paycheck_decl_tab
cont> (employee_id ID_DOM,
cont> last_name CHAR(14) ,
cont> hours_worked INTEGER,
cont> hourly_sal INTEGER(2),
cont> weekly_pay INTEGER(2))
cont> ON COMMIT PRESERVE ROWS
cont> --
cont> -- Create the procedure to insert rows.
cont> --
cont> PROCEDURE paycheck_ins_decl;
cont> BEGIN
cont> INSERT INTO module.paycheck_decl_tab
cont> (employee_id, last_name, hours_worked, hourly_sal, weekly_pay)
cont> SELECT p.employee_id, e.last_name,
cont> p.hours_worked, p.hourly_sal,
cont> p.hours_worked * p.hourly_sal
cont> FROM employees e, payroll p
cont> WHERE e.employee_id = p.employee_id
cont> AND p.week_date = '1995-08-01';
cont> END;
cont> --
cont> -- Create the procedure to count the low hours.
cont> --
cont> PROCEDURE low_hours_decl (:cnt INTEGER);
cont> BEGIN
cont> SELECT COUNT(*) INTO :cnt FROM module.paycheck_decl_tab
cont> WHERE hours_worked < 40;
cont> END;
cont> END MODULE;
SQL> --
SQL> -- Call the procedure to insert the rows.
SQL> --
SQL> CALL paycheck_ins_decl();
SQL> --
SQL> -- Declare a variable and call the procedure to count records with
SQL> -- low hours.
SQL> --
SQL> DECLARE :low_hr_cnt integer;
SQL> CALL low_hours_decl(:low_hr_cnt);
LOW_HR_CNT
2
SQL> --
SQL> -- Because the table is a declared local temporary table, you cannot
SQL> -- access it from outside the stored module that contains it.
SQL> --
SQL> SELECT * FROM module.paycheck_decl_tab;
%SQL-F-RELNOTDCL, Table PAYCHECK_DECL_TAB has not been declared in module or
environment
Example 6: Creating a stored procedure containing a simple
statement
SQL> CREATE MODULE a
cont> LANGUAGE SQL
cont> PROCEDURE new_salary_proc
cont> (:id CHAR (5),
cont> :new_salary INTEGER (2));
cont> UPDATE salary_history
cont> SET salary_end = CURRENT_TIMESTAMP
cont> WHERE employee_id = :id;
cont> END MODULE;
Example 7: Declaring a Global Variable to Exchange Information
Between Two Routines
SQL> CREATE MODULE sample
cont> LANGUAGE SQL
cont> DECLARE :iter_count INTEGER
cont> PROCEDURE set_iter (IN :val INTEGER)
cont> COMMENT IS 'Validate the iteration count and assign'
cont> / 'to a global variable.';
cont> BEGIN
cont> IF (:val IS NULL) OR (:val < 1) THEN
cont> SIGNAL 'XXXXX'; --illegal value
cont> ELSE
cont> SET :iter_count =:val;
cont> TRACE 'Iteration count set to ', :val;
cont> END IF;
cont> END;
cont> FUNCTION GET_ITER ()
cont> RETURNS INTEGER
cont> COMMENT IS 'Trace the value used and then return the'
cont> / 'value from the global variable.';
cont> BEGIN
cont> TRACE 'Using iteration count ', :iter_count;
cont> RETURN :iter_count;
cont> END;
cont> END MODULE;
Example 8: Using a cursor implemented by external routines
This example uses multiple external routines to manage a table
cursor in the external routine database environment. This
management includes the OPEN, FETCH and CLOSE of a single cursor.
Several domains are defined so that parameter data types can be
consistently defined in the database that contain the application
and also the database upon which the cursor is open.
create domain SQLSTATE_T char(5);
create domain STATUS_CODE char(1);
create domain STATUS_NAME char(8);
create domain STATUS_TYPE char(14);
The external function interface is contained within a single
CREATE MODULE statement. This module also contains the
application in a single stored SQL procedure.
create module EX
language SQL
-- These procedure define the interface to the external
-- routines that implement the transaction and cursor operations
--
procedure EX_START_READ_TXN
(inout :ss sqlstate_t);
external location 'TEST$SCRATCH:EX.EXE'
language general
general parameter style
comment is 'start a READ ONLY transaction';
procedure EX_COMMIT
(inout :ss sqlstate_t);
external location 'TEST$SCRATCH:EX.EXE'
language general
general parameter style;
procedure EX_OPEN_CURSOR
(inout :ss sqlstate_t);
external location 'TEST$SCRATCH:EX.EXE'
language general
general parameter style
comment is 'find all rows in WORK_STATUS order by STATUS_CODE';
procedure EX_CLOSE_CURSOR
(inout :ss sqlstate_t);
external location 'TEST$SCRATCH:EX.EXE'
language general
general parameter style;
procedure EX_FETCH_CURSOR
(inout :ss sqlstate_t,
out :s_code STATUS_CODE, out :s_code_ind integer,
out :s_name STATUS_NAME, out :s_name_ind integer,
out :s_type STATUS_TYPE, out :s_type_ind integer);
external location 'TEST$SCRATCH:EX.EXE'
language general
general parameter style;
-- This SQL procedures implements a simple application
--
procedure WORK_STATUS
comment is 'Use an external cursor to fetch all rows in the'
/ 'WORK_STATUS table';
begin
declare :s_code STATUS_CODE;
declare :s_name STATUS_NAME;
declare :s_type STATUS_TYPE;
declare :s_code_ind, :s_name_ind, :s_type_ind integer;
declare :ss sqlstate_t;
-- start a read-only transaction on the PERSONNEL database
call EX_START_READ_TXN (:ss);
if :ss ^= '00000' then
SIGNAL :ss;
end if;
-- open the cursor on the work-status table
call EX_OPEN_CURSOR (:ss);
if :ss ^= '00000' then
SIGNAL :ss;
end if;
-- now loop and fetch all the rows
FETCH_LOOP:
loop
call EX_FETCH_CURSOR (:ss,
:s_code, :s_code_ind,
:s_name, :s_name_ind,
:s_type, :s_type_ind);
case :ss
when '02000' then
-- no more rows to fetch
leave FETCH_LOOP;
when '00000' then
begin
-- we have successfully fetched a row, so display it
trace 'Status Code: ', case when :s_code_ind < 0
then 'NULL'
else :s_code
end;
trace 'Status Name: ', case when :s_name_ind < 0
then 'NULL'
else :s_name
end;
trace 'Status Type: ', case when :s_type_ind < 0
then 'NULL'
else :s_type
end;
trace '***';
end;
else
-- signal will implicitly leave the stored procedure
SIGNAL :ss;
end case;
end loop;
-- close the cursor
call EX_CLOSE_CURSOR (:ss);
if :ss ^= '00000' then
SIGNAL :ss;
end if;
-- commit the transaction
call EX_COMMIT (:ss);
if :ss ^= '00000' then
SIGNAL :ss;
end if;
end;
end module;
The external procedures for this this example are written using
the SQL module language. However, any language with embedded SQL,
such as C, could have been used.
module EX
language GENERAL
parameter colons
-- EX: Sample application
-- Process the WORK_STATUS table using a table cursor
--
declare alias filename 'PERSONNEL'
declare c cursor for
select status_code, status_name, status_type
from WORK_STATUS
order by status_code
procedure EX_START_READ_TXN
(sqlstate);
begin
-- abort any stray transactions
rollback;
-- start a READ ONLY transaction
set transaction read only;
end;
procedure EX_COMMIT
(sqlstate);
commit work;
procedure EX_ROLLBACK
(sqlstate);
rollback work;
procedure EX_OPEN_CURSOR
(sqlstate);
open c;
procedure EX_CLOSE_CURSOR
(sqlstate);
close c;
procedure EX_FETCH_CURSOR
(sqlstate,
:s_code STATUS_CODE,
:s_code_ind integer,
:s_name STATUS_NAME,
:s_name_ind integer,
:s_type STATUS_TYPE,
:s_type_ind integer);
fetch c
into :s_code indicator :s_code_ind,
:s_name indicator :s_name_ind,
:s_type indicator :s_type_ind;
procedure EX_DISCONNECT
(sqlstate);
disconnect default;
When run the application calls the external procedures to open
the cursor and fetch the rows and display them using the TRACE
statement.
SQL> set flags 'trace';
SQL>
SQL> call WORK_STATUS ();
~Xt: Status Code: 0
~Xt: Status Name: INACTIVE
~Xt: Status Type: RECORD EXPIRED
~Xt: ***
~Xt: Status Code: 1
~Xt: Status Name: ACTIVE
~Xt: Status Type: FULL TIME
~Xt: ***
~Xt: Status Code: 2
~Xt: Status Name: ACTIVE
~Xt: Status Type: PART TIME
~Xt: ***
SQL>
Oracle recommends that the cursors be closed, and the external
routines database environment be disconnected before the calling
session is disconnected. This can be achieved by using NOTIFY
routines.
For example, the external procedure that starts the transaction
could be modified as shown below to declare a NOTIFY routine (EX_
RUNDOWN) that when called would close the cursors, rollback the
transaction and disconnect from the database.
procedure EX_START_READ_TXN
(inout :ss sqlstate_t);
external location 'TEST$SCRATCH:EX.EXE'
language general
general parameter style
notify EX_RUNDOWN on BIND
comment is 'start a READ ONLY transaction';
The BIND notification ensures that EX_RUNDOWN will be called
during the DISCONNECT of the caller and allow the transaction to
be rolled back and the session disconnected. ROLLBACK or COMMIT
will implicitly close any open cursors, unless the cursor were
defined as WITH HOLD. In this case it is important to also close
that cursor. Code similar to the following (in C) could implement
this rundown routine.
#include <string.h>
#include <stdio.h>
#define RDB$K_RTX_NOTIFY_ACTV_END 2
#define SQLSTATE_LEN 5
void sql_signal ();
void EX_CLOSE_CURSOR (char sqlstate [SQLSTATE_LEN]);
void EX_DISCONNECT (char sqlstate [SQLSTATE_LEN]);
void EX_ROLLBACK (char sqlstate [SQLSTATE_LEN]);
extern void EX_RUNDOWN
(int *func_code,
int *u1, /* U1, U2, U3 are currently unused */
int *u2, /* and are reserved for future use */
int *u3)
{
char sqlstate [SQLSTATE_LEN];
if (*func_code == RDB$K_RTX_NOTIFY_ACTV_END)
{
/* we are running down this external routine, so
* close the cursor
*/
EX_CLOSE_CURSOR (sqlstate);
if (memcmp ("00000", sqlstate, SQLSTATE_LEN) != 0
&& memcmp ("24000", sqlstate, SQLSTATE_LEN) != 0)
/* we expect success or maybe 24000 (bad cursor state)
*/
sql_signal ();
/* rollback the transaction
*/
EX_ROLLBACK (sqlstate);
if (memcmp ("00000", sqlstate, SQLSTATE_LEN) != 0
&& memcmp ("25000", sqlstate, SQLSTATE_LEN) != 0)
/* we expect success or maybe 25000 (bad transaction state)
*/
sql_signal ();
/* disconnect from the database
*/
EX_DISCONNECT (sqlstate);
if (memcmp ("00000", sqlstate, SQLSTATE_LEN) != 0)
/* we expect success or maybe 25000 (bad transaction state)
*/
sql_signal ();
}
}
The application can be compiled and built using this fragment of
DCL code:
$ create ex.opt
symbol_vector = (EX_START_READ_TXN = procedure)
symbol_vector = (EX_COMMIT = procedure)
symbol_vector = (EX_ROLLBACK = procedure)
symbol_vector = (EX_OPEN_CURSOR = procedure)
symbol_vector = (EX_CLOSE_CURSOR = procedure)
symbol_vector = (EX_FETCH_CURSOR = procedure)
symbol_vector = (EX_DISCONNECT = procedure)
symbol_vector = (EX_RUNDOWN = procedure)
psect_attr = RDB$MESSAGE_VECTOR,noshr
psect_attr = RDB$DBHANDLE,noshr
psect_attr = RDB$TRANSACTION_HANDLE,noshr
sql$user/library
$
$ cc EX_RUNDOWN
$ sql$mod EX
$ link/share EX,EX_RUNDOWN,EX/option