我有以下存储过程:
ALTER PROCEDURE SP_STOCK_ANALYSIS
(
MAIN_GROUP CHAR(6)
)
RETURNS
(
STOCK_CODE CHAR(21),
STOCK_GROUP CHAR(6),
DESCRIPTION CHAR(31),
EXPENSE NUMERIC(15, 4)
)
AS
BEGIN
FOR
SELECT
L.STOCK_CODE, INV.DESCRIPTION, INV.STOCK_GROUP, L.BALANCE
FROM
LEDGER L LEFT JOIN INVENTORY INV ON L.STOCK_CODE = INV.STOCK_CODE
WHERE
INV.STOCK_GROUP in (:MAIN_GROUP)
AND L.LEDGER_ACCOUNT in ('71212', '71211' ,'83791')
INTO
STOCK_CODE, STOCK_GROUP, DESCRIPTION, EXPENSE
DO
在select语句中，我有以下三个帐户:
- 71212
- 71211
- 83791
理想情况下，我希望更改存储过程，以便能够将帐号作为参数的一部分输入。挑战在于，账户的数量可能会发生变化。是否可以使用字符串作为参数?我该怎么做呢?
Firebird不支持存储过程的参数数量可变。但是，您可以定义默认参数值。因此，您可以指定不带默认值的第一个参数，然后指定带默认值的多个参数，然后使用1个或多个参数调用存储过程。
create procedure SP_STOCK_ANALYSIS (
group_1 CHAR(6), group_2 CHAR(6) DEFAULT NULL, group_3 CHAR(6) DEFAULT NULL /* ... etc ...*/)
RETURNS (
STOCK_CODE CHAR(21),
STOCK_GROUP CHAR(6),
DESCRIPTION CHAR(31),
EXPENSE NUMERIC(15, 4))
as
begin
for select L.STOCK_CODE /* ... etc ... */
from LEDGER L LEFT JOIN INVENTORY INV ON L.STOCK_CODE = INV.STOCK_CODE
where INV.STOCK_GROUP in (group_1, group_2, group_3 /* ... etc ... */)
/* ... etc ... */
into STOCK_CODE /* ... etc ... */
do
begin
/* ... etc ... */
end
end
或者，您也可以传递一个逗号分隔的字符串，然后使用helper存储过程将该字符串分割成多行。
然后你会像
这样做create procedure SP_STOCK_ANALYSIS(group_list VARCHAR(8191)
RETURNS (
STOCK_CODE CHAR(21),
STOCK_GROUP CHAR(6),
DESCRIPTION CHAR(31),
EXPENSE NUMERIC(15, 4))
as
begin
for select L.STOCK_CODE /* ... etc ... */
from LEDGER L LEFT JOIN INVENTORY INV ON L.STOCK_CODE = INV.STOCK_CODE
where INV.STOCK_GROUP in (select group_value from split_groups(:group_list))
/* ... etc ... */
into STOCK_CODE /* ... etc ... */
do
begin
/* ... etc ... */
end
end
与split_groups类似
create procedure split_group(group_list varchar(8191))
returns (group_value varchar(1000))
as
declare previouspos smallint = 1;
declare nextpos smallint;
begin
-- produce no rows for null input
if (group_list is null) then exit;
-- find next , in group_list
nextpos = position(',', group_list);
while (nextpos <> 0) do
begin
-- get item
group_value = substring(group_list from previouspos for nextpos - previouspos);
if (char_length(group_value) > 0) then
-- output item as a row
suspend;
-- first character after the found ,
previouspos = nextpos + 1;
-- find next , in group_list
nextpos = position(',', group_list, previouspos);
end
-- output item after last found ,
group_value = substring(group_list from previouspos);
if (char_length(group_value) > 0) then
suspend;
end
您还可以选择使用反向LIKE而不是IN。毕竟，随着物品数量的增加，IN在Interbase/Firebird上的速度会变慢。LIKE总是对整个表进行自然扫描。
创建自己的分析。您甚至可以根据参数字符串长度切换到一种或另一种策略。您对FirebirdVarChar的32KB长度限制感到困惑，也许这对您的应用程序很重要。
所以，一般的方向看我的回答https://stackoverflow.com/a/43997801/976391
应用"Route #2"对你来说，应该是这样的……
ALTER PROCEDURE SP_STOCK_ANALYSIS
(
MAIN_GROUP varCHAR(32760) character set ascii
)
RETURNS
(
STOCK_CODE varCHAR(21),
STOCK_GROUP varCHAR(6),
DESCRIPTION varCHAR(31),
EXPENSE NUMERIC(15, 4)
)
AS
BEGIN
FOR
SELECT
L.STOCK_CODE, INV.DESCRIPTION, INV.STOCK_GROUP, L.BALANCE
FROM
LEDGER L LEFT JOIN INVENTORY INV ON L.STOCK_CODE = INV.STOCK_CODE
WHERE
(:MAIN_GROUP CONTAINING '~' || INV.STOCK_GROUP || '~')
-- INV.STOCK_GROUP in (:MAIN_GROUP)
AND (L.LEDGER_ACCOUNT in ('71212', '71211' ,'83791'))
INTO
STOCK_CODE, STOCK_GROUP, DESCRIPTION, EXPENSE
DO
.....
然后像这样调用:
SELECT * FROM SP_STOCK_ANALYSIS ('~1~4~8~11~')
您可以使用LIST聚合函数将返回ID的查询转换为字符串，如
SELECT '~' || LIST (ID, '~') || '~' FROM source-table WHERE ........
但我认为工程明智，最好使用事务本地GTT(全局临时表)而不是双转换，然后在SP中进行自然join。
insert into SP-helping-GTT
SELECT ID FROM source-table WHERE ........;
…然后执行无参数SP，然后只执行COMMIT来清理GTT
缺点当然是隐式严格耦合和命名空间污染。
但是，由于您同时对两个表进行多重过滤-L和INV-并且您可能希望将两个列表转换为参数，那么joining两个表(gtt)对于关系数据库引擎来说将是自然的，并且两个嵌套的自然扫描将获得糟糕的O(n^2)缩放。
此外，您真的会考虑是否需要外来的CHAR数据类型而不是VARCHAR。它确实是一个小麻烦，但人们不知何故一次又一次地被它绊倒。
- Firebird查询使用char/varchar
- 修剪字符字段
- Firebird中的表名有很多额外的空间
- 从右侧开始修剪空白
- 获取SQL中字符串的长度
…这些只是少数。