/
Procedures.htm
94 lines (76 loc) · 4.28 KB
/
Procedures.htm
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
<!--
* The contents of this file are subject to the Initial
* Developer's Public License Version 1.0 (the "License");
* you may not use this file except in compliance with the
* License. You may obtain a copy of the License at
* http://www.ibphoenix.com/main.nfs?a=ibphoenix&page=ibp_idpl
*
* Software distributed under the License is distributed on
* an "AS IS" basis, WITHOUT WARRANTY OF ANY KIND, either
* express or implied. See the License for the specific
* language governing rights and limitations under the License.
*
*
* The Original Code was created by Vladimir Tsvigun for IBPhoenix.
*
* Copyright (c) 2004 Vladimir Tsvigun
* All Rights Reserved.
*
* This translation and editing was done by Paul Beach of IBPhoenix
*
-->
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2 Final//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" Content="text/html; charset=Windows-1252">
<TITLE>Procedures</TITLE>
</HEAD>
<BODY style="font-family:Tahoma, Verdana, sans-serif" BGCOLOR="#FFFFFF" TEXT="#000000">
<P><A NAME="hid_procedures"></A><h1>Stored Procedures</h1></P>
<P>Firebird supports two mechanisms to call stored procedures.</P>
<P><B>execute procedure MyProc(?,?) </B></P>
<P>In this example the stored procedure expects to receive data based on the parameters that are being passed. If the parameters are invalid, nothing will be returned.</P>
<P><B>select * from MyProc(?,?) </B></P>
<P>In this example the stored procedure expects to generate a result set.</P>
<P>Programs such as Microsoft Excel etc when calling a stored procedure use
the following </P>
<P> <B>{[? =] Call MyProc (?,?)}</B>.<BR></P>
<P>The Firebird ODBC driver determines what call to use to execute the stored procedure depending on how the stored procedure was constructed. The key to this is the usage of the word SUSPEND in the stored procedure definition.</P>
<P> If the BLR code for the stored procedure contains if (countSUSPEND == 1)
as would be the case using this stored procedure defintion:</P>
<P>create procedure TEST<BR>
as<BR>
begin<BR>
end
</P>
<P>Then the ODBC driver will use execute procedure TEST.</P>
<P>If the BLR code for the stored procedure contains if (countSUSPEND > 1)
as would be the case in this stored procedure definition:</P>
<P>create procedure "ALL_LANGS" <BR>
returns ("CODE" varchar(5), <BR>
"GRADE" varchar(5), <BR>
"COUNTRY" varchar(15), <BR>
"LANG" varchar(15)) <BR>
as <BR>
BEGIN <BR>
"LANG" = null; <BR>
FOR SELECT job_code, job_grade, job_country FROM job <BR>
INTO :code, :grade, :country <BR>
DO <BR>
BEGIN <BR>
FOR SELECT languages FROM show_langs(:code, :grade, :country) <BR>
INTO :lang <BR>
DO <BR>
<B>SUSPEND;</B> <BR>
/* Put nice separators between rows */ <BR>
code = '====='; <BR>
grade = '====='; <BR>
country = '==============='; <BR>
lang = '=============='; <BR>
<B>SUSPEND; </B> <BR>
END <BR>
END</P>
<P>Then the ODBC Driver will use <B>select * from "ALL_LANGS"</B></P>
<P>For more details of how to do this and for other advanced topics please look at the examples.</P>
</BODY>
</HTML>