简单的火鸟查询

Question

我试图在火鸟中做一个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

Error Message I get:

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:Windowsfirebird.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:Windowsfirebird.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每一行。

此外，您还忘记了几个语句终止符(;)，并且您不能同时声明变量和它的值。生成的执行块类似于:

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 ;!!

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

Answer 2

不，你不能在Flamerobin的查询窗口中直接执行这样的脚本。我认为最简单的方法是将脚本封装到存储过程中，然后在处理完结果后将其丢弃。要创建临时SP，右键单击Flamerobin数据库树中的Procedures节点并选择Create new -这将为您创建SP窗口，您可以在其中插入代码。

Answer 3

您需要将存储过程像代码一样包装在EXECUTE BLOCK语句中。

Answer 4

您的sql脚本可能已损坏