Table Procedure Example
Table procedures are useful for programmatically performing selections from various tables.
For example:
INGRES TERMINAL MONITOR Copyright 2009 Ingres Corporation
Ingres Microsoft Windows Version II 9.3.0 (int.w32/155) login
Thu Sep 24 23:12:56 2009
continue
* go
* * * * * * * * *
/*
** Table Procedure Test Case
*/
/* Drop Procedure
*/
drop procedure tblproc;
commit;
Executing . . .
continue
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
/*
** Create Table Procedure
*/
create procedure tblproc (id integer not null)
result row( char(32) not null, char(32) not null)
as
declare id2 integer not null;
col1 char(64) not null;
col2 char(32) not null;
begin
if (id = 0) then
for select first 5 cap_capability, cap_value into :col1, :col2
from "$ingres".iidbcapabilities do return row (:col1, :col2);
endfor;
elseif (id = 1) then
for select first 5 table_owner, table_name into :col1, :col2
from "$ingres".iitables do
return row (:col1, :col2);
endfor;
elseif (id = 2) then
for select first 5 trim(table_owner)+'.'+trim(table_name), column_name into :col1, :col2
from "$ingres".iicolumns do
return row (:col1, :col2);
endfor;
elseif (id = 3) then
for select first 5 cap_capability, cap_value into :col1, :col2
from "$ingres".iidbcapabilities do
return row (:col1, :col2);
endfor;
for select first 5 table_owner, table_name into :col1, :col2
from "$ingres".iitables do
return row (:col1, :col2);
endfor;
for select first 5 trim(table_owner)+'.'+trim(table_name), column_name into :col1, :col2
from "$ingres".iicolumns do
return row (:col1, :col2);
endfor;
else
for select cap_capability, cap_value into :col1, :col2
from "$ingres".iidbcapabilities do
return row (:col1, :col2);
endfor;
endif;
end
Executing . . .
* * ;
commit;
Executing . . .
continue
* * * * * * *
/*
** Get help on created procedure
*/
help procedure tblproc;
Executing . . .
Procedure: tblproc
Owner: ingres
Procedure Type: native
Object type: user object
Created: 24-sep-2009 23:12:56
Procedure Definition:
/* 1 */ create procedure tblproc (id integer not null) result row(
char(32) not null, char(32) not null) as declare id2 integer not null;
/* 2 */ col1 char(64) not null;
/* 3 */ col2 char(32) not null;
/* 4 */ begin if (id = 0) then for select first 5 cap_capability,
cap_value into :col1, :col2 from "$ingres".iidbcapabilities do return
row (:col1, :col2);
/* 5 */ endfor;
/* 6 */ elseif (id = 1) then for select first 5 table_owner,
table_name into :col1, :col2 from "$ingres".iitables do return row
(:col1, :col2);
/* 7 */ endfor;
/* 8 */ elseif (id = 2) then for select first 5 trim(table_owner)+
'.'+trim(table_name), column_name into :col1, :col2 from
"$ingres".iicolumns do return row (:col1, :col2);
/* 9 */ endfor;
/* 10 */ elseif (id = 3) then for select first 5 cap_capability,
cap_value into :col1, :col2 from "$ingres".iidbcapabilities do return
row (:col1, :col2);
/* 11 */ endfor;
/* 12 */ for select first 5 table_owner, table_name into :col1,
:col2 from "$ingres".iitables do return row (:col1, :col2);
/* 13 */ endfor;
/* 14 */ for select first 5 trim(table_owner)+'.'+trim(table_name),
column_name into :col1, :col2 from "$ingres".iicolumns do return row
(:col1, :col2);
/* 15 */ endfor;
/* 16 */ else for select cap_capability, cap_value into :col1, :col2
from "$ingres".iidbcapabilities do return row (:col1, :col2);
/* 17 */ endfor;
/* 18 */ endif;
/* 19 */ end
continue
* * * * * * *
/*
** test cases
*/
select * from tblproc();
Executing . . .
+--------------------------------+--------------------------------+
|result_column0 |result_column1 |
+--------------------------------+--------------------------------+
|QUEL_LEVEL |II9.3.0 |
|SQL_LEVEL |II9.3.0 |
|DISTRIBUTED |N |
|MIXEDCASE_NAMES |N |
|INGRES |Y |
+--------------------------------+--------------------------------+
(5 rows)
continue
* * select * from tblproc(id=1);
Executing . . .
+--------------------------------+--------------------------------+
|result_column0 |result_column1 |
+--------------------------------+--------------------------------+
|$ingres |iiprotect |
|$ingres |iidbcapabilities |
|$ingres |iiotables |
|$ingres |ii_fields |
|$ingres |iirule |
+--------------------------------+--------------------------------+
(5 rows)
continue
* * select * from tblproc(id=2);
Executing . . .
+--------------------------------+--------------------------------+
|result_column0 |result_column1 |
+--------------------------------+--------------------------------+
|$ingres.iirelation |relfree |
|$ingres.iirelation |relnparts |
|$ingres.iirelation |relmax |
|$ingres.iirelation |relcreate |
|$ingres.iirelation |relsave |
+--------------------------------+--------------------------------+
(5 rows)
continue
* * select * from tblproc(id=3);
Executing . . .
+--------------------------------+--------------------------------+
|result_column0 |result_column1 |
+--------------------------------+--------------------------------+
|QUEL_LEVEL |II9.3.0 |
|SQL_LEVEL |II9.3.0 |
|DISTRIBUTED |N |
|MIXEDCASE_NAMES |N |
|INGRES |Y |
|$ingres |iiprotect |
|$ingres |iidbcapabilities |
|$ingres |iiotables |
|$ingres |ii_fields |
|$ingres |iirule |
|$ingres.iirelation |relfree |
|$ingres.iirelation |relnparts |
|$ingres.iirelation |relmax |
|$ingres.iirelation |relcreate |
|$ingres.iirelation |relsave |
+--------------------------------+--------------------------------+
(15 rows)
continue
* * select * from tblproc(id=99);
Executing . . .
+--------------------------------+--------------------------------+
|result_column0 |result_column1 |
+--------------------------------+--------------------------------+
|QUEL_LEVEL |II9.3.0 |
|SQL_LEVEL |II9.3.0 |
|DISTRIBUTED |N |
|MIXEDCASE_NAMES |N |
|INGRES |Y |
|INGRES/SQL_LEVEL |00930 |
|COMMON/SQL_LEVEL |00930 |
|INGRES/QUEL_LEVEL |00930 |
|SAVEPOINTS |Y |
|DBMS_TYPE |INGRES |
|PHYSICAL_SOURCE |T |
|MAX_COLUMNS |1024 |
|INGRES_RULES |Y |
|INGRES_UDT |Y |
|INGRES_AUTH_GROUP |Y |
|INGRES_AUTH_ROLE |Y |
|INGRES_LOGICAL_KEY |Y |
|UNIQUE_KEY_REQ |N |
|ESCAPE |Y |
|OWNER_NAME |QUOTED |
|STANDARD_CATALOG_LEVEL |00930 |
|OPEN/SQL_LEVEL |00904 |
|DB_NAME_CASE |LOWER |
|DB_DELIMITED_CASE |LOWER |
|DB_REAL_USER_CASE |LOWER |
|NATIONAL_CHARACTER_SET |N |
|SQL_MAX_BYTE_LITERAL_LEN |32000 |
|SQL_MAX_CHAR_LITERAL_LEN |32000 |
|SQL_MAX_BYTE_COLUMN_LEN |32000 |
|SQL_MAX_VBYT_COLUMN_LEN |32000 |
|SQL_MAX_CHAR_COLUMN_LEN |32000 |
|SQL_MAX_VCHR_COLUMN_LEN |32000 |
|SQL_MAX_NCHR_COLUMN_LEN |16000 |
|SQL_MAX_NVCHR_COLUMN_LEN |16000 |
|SQL_MAX_SCHEMA_NAME_LEN |32 |
|SQL_MAX_TABLE_NAME_LEN |32 |
|SQL_MAX_COLUMN_NAME_LEN |32 |
|SQL_MAX_USER_NAME_LEN |32 |
|SQL_MAX_ROW_LEN |262144 |
|SQL_MAX_STATEMENTS |0 |
|SQL_MAX_DECIMAL_PRECISION |39 |
+--------------------------------+--------------------------------+
(41 rows)
continue
* *
Ingres Version II 9.3.0 (int.w32/155) logout
Thu Sep 24 23:12:56 2009