database - 简单的 Firebird 查询

Question

我正在尝试在 Firebird 中执行一个 while 循环，以使用 FlameRobin 工具执行来自 SP 的所有值。但是，这是行不通的。有什么建议吗？

declare i int = 0;
while ( i <= 2 ) do BEGIN
   SELECT p.SOD_AUTO_KEY, p.CURRENCY_CODE, p.SO_CATEGORY_CODE, p.SO_NUMBER, p.INVC_NUMBER,  p.ENTRY_DATE, p.SHIP_DATE, p.NEXT_SHIP_DATE, p.CONDITION_CODE, p.QTY_ORDERED,       p.QTY_PENDING_INVOICE, p.QTY_INVOICED, p.UNIT_PRICE, p.EXCHANGE_RATE, p.UNIT_COST,     p.ITEM_NUMBER, p.CONSIGNMENT_CODE, p.NOTES, p.STOCK_LINE, p.STM_AUTO_KEY, p.SERIAL_NUMBER,     p.REMARKS, p.PN, p.PNM_AUTO_KEY, p.GR_CODE, p.CUSTOMER_PRICE, p.OPEN_FLAG, p.ROUTE_CODE,     p.ROUTE_DESC, p.COMPANY_CODE, p.SITE_CODE, p.COMPANY_NAME, p.COMPANY_REF_NUMBER, p.CUST_REF,     p.HOT_PART
   FROM SPB_SALESHISTORY(i) p
   i = i + 1;
end

我得到的错误信息:

Preparing query: declare i int = 0
Error: *** IBPP::SQLException ***
Context: Statement::Prepare( declare i int = 0 )
Message: isc_dsql_prepare failed

SQL Message : -104
can't format message 13:896 -- message file C:\Windows\firebird.msg not found

Engine Code    : 335544569
Engine Message :
Dynamic SQL Error
SQL error code = -104
Token unknown - line 1, column 9
i


Total execution time: 0.004s

这是我尝试过的，但它只显示“脚本执行完成”并且不返回任何结果:

    set term !! 
EXECUTE BLOCK returns(p) AS
declare i integer = 0
BEGIN
while ( i <= 1000 ) do BEGIN
   SELECT p.SOD_AUTO_KEY, p.CURRENCY_CODE, p.SO_CATEGORY_CODE, p.SO_NUMBER, p.INVC_NUMBER,  p.ENTRY_DATE, p.SHIP_DATE, p.NEXT_SHIP_DATE, p.CONDITION_CODE, p.QTY_ORDERED,p.QTY_PENDING_INVOICE, p.QTY_INVOICED, p.UNIT_PRICE, p.EXCHANGE_RATE, p.UNIT_COST,     p.ITEM_NUMBER, p.CONSIGNMENT_CODE, p.NOTES, p.STOCK_LINE, p.STM_AUTO_KEY, p.SERIAL_NUMBER,     p.REMARKS, p.PN, p.PNM_AUTO_KEY, p.GR_CODE, p.CUSTOMER_PRICE, p.OPEN_FLAG, p.ROUTE_CODE,     p.ROUTE_DESC, p.COMPANY_CODE, p.SITE_CODE, p.COMPANY_NAME, p.COMPANY_REF_NUMBER, p.CUST_REF,     p.HOT_PART
   FROM SPB_SALESHISTORY(i) p
   i = i + 1
end
END !!

马克，

我尝试了您的建议，但出现以下错误:

 set term!!;
EXECUTE BLOCK RETURNS (
    SOD_AUTO_KEY Integer,
    CURRENCY_CODE Char(3),
    SO_CATEGORY_CODE Char(10),
    SO_NUMBER Char(12),
    INVC_NUMBER Char(12),
    ENTRY_DATE Timestamp,
    SHIP_DATE Timestamp,
    NEXT_SHIP_DATE Timestamp,
    CONDITION_CODE Varchar(10),
    QTY_ORDERED Double precision,
    QTY_PENDING_INVOICE Double precision,
    QTY_INVOICED Double precision,
    UNIT_PRICE Double precision,
    EXCHANGE_RATE Double precision,
    UNIT_COST Double precision,
    ITEM_NUMBER Integer,
    CONSIGNMENT_CODE Char(10),
    NOTES Blob sub_type 1,
    STOCK_LINE Integer,
    STM_AUTO_KEY Integer,
    SERIAL_NUMBER Varchar(40),
    REMARKS Varchar(50),
    PN Varchar(40),
    PNM_AUTO_KEY Integer,
    GR_CODE Varchar(10),
    CUSTOMER_PRICE Double precision,
    OPEN_FLAG Char(1),
    ROUTE_CODE Char(1),
    ROUTE_DESC Varchar(20),
    COMPANY_CODE Varchar(10),
    SITE_CODE Varchar(10),
    COMPANY_NAME Varchar(50),
    COMPANY_REF_NUMBER Varchar(30),
    CUST_REF Varchar(15),
    HOT_PART Char(1) 
    )
     AS
declare i integer;
BEGIN
i=0;
while ( i <= 2 ) do 
BEGIN
   for SELECT SOD_AUTO_KEY,CURRENCY_CODE,SO_CATEGORY_CODE, SO_NUMBER,INVC_NUMBER,ENTRY_DATE, SHIP_DATE, NEXT_SHIP_DATE, CONDITION_CODE, QTY_ORDERED,QTY_PENDING_INVOICE, QTY_INVOICED, UNIT_PRICE, EXCHANGE_RATE, UNIT_COST,ITEM_NUMBER, CONSIGNMENT_CODE, NOTES, STOCK_LINE, STM_AUTO_KEY, SERIAL_NUMBER,REMARKS, PN, PNM_AUTO_KEY, GR_CODE, CUSTOMER_PRICE, OPEN_FLAG, ROUTE_CODE,ROUTE_DESC, COMPANY_CODE, SITE_CODE, COMPANY_NAME, COMPANY_REF_NUMBER, CUST_REF, HOT_PART
   FROM SPB_SALESHISTORY (i)
   into :SOD_AUTO_KEY, :CURRENCY_CODE, :SO_CATEGORY_CODE, :SO_NUMBER, :INVC_NUMBER,
   :ENTRY_DATE, :SHIP_DATE, :NEXT_SHIP_DATE, :CONDITION_CODE, :QTY_ORDERED,:QTY_PENDING_INVOICE,
   :QTY_INVOICED, :UNIT_PRICE, :EXCHANGE_RATE, :UNIT_COST,     :ITEM_NUMBER, :CONSIGNMENT_CODE, :NOTES, :STOCK_LINE,
   :STM_AUTO_KEY, :SERIAL_NUMBER,     :REMARKS, :PN, :PNM_AUTO_KEY, :GR_CODE, :CUSTOMER_PRICE, :OPEN_FLAG, :ROUTE_CODE,:ROUTE_DESC,
   :COMPANY_CODE, :SITE_CODE, :COMPANY_NAME, :COMPANY_REF_NUMBER, :CUST_REF,:HOT_PART
   DO
    suspend;
   i = i + 1;
    end
END!!
SET TERM;!!

错误:

Message: isc_dsql_prepare failed

SQL Message : -206
can't format message 13:794 -- message file C:\Windows\firebird.msg not found

Engine Code    : 335544569
Engine Message :
Dynamic SQL Error
SQL error code = -206
Column unknown
I
At line 46, column 27


Total execution time: 0.005s

Answer 1

根据您对 Ain 回答的评论，您似乎还想返回从 EXECUTE BLOCK 中选择的值.您的 RETURNS (p) 无效，无法使用。您需要显式声明要返回的所有列，并且需要SUSPEND每一行。

此外，您还忘记了几个语句终止符 (;)，并且您不能将变量及其值一起声明。生成的执行 block 将类似于:

set term !!;
EXECUTE BLOCK returns (
    SOD_AUTO_KEY INTEGER,
    /* ... */
    HOT_PART VARCHAR(255)
) AS
    declare i integer;
BEGIN
    i = 0;
    while ( i <= 1000 ) do 
    BEGIN
       FOR SELECT SOD_AUTO_KEY, /* ... */ HOT_PART
           FROM SPB_SALESHISTORY(i) 
           INTO :SOD_AUTO, /* ... */ :HOT_PART
       DO
           SUSPEND;
       i = i + 1;
    end
END!!
SET TERM ;!!

为简洁起见，我省略了一些列并猜测了它们的类型。