如何制作Firebird SP，从一些动态表中获取行？

发布于 2024-08-20 07:27:44 字数 582 浏览 4 评论 0原文

我的表有一些用户不能知道的内部前缀。
所以我做了一个像这样的SP来检索行。

CREATE PROCEDURE p_Test  
 ( inStation varchar(10) )  
RETURNS  
 ( ServiceName varchar(20) )  
AS    
DECLARE VARIABLE StationTable varchar(20);  
BEGIN   
  StationTable = 'TSTATION_' || Upper(Trim(inStation));  

  SELECT StationTable.ServiceName  
  FROM StationTable  
  INTO :ServiceName;  
  suspend;  

END^

我收到这样的失败消息

SQL 错误代码 = -204
表未知
车站表

我的 fbclient.dll 版本是 2.1.2.18118。
我的测试环境是Flamerobin 0.9.2.1851。

有什么想法可以让这项工作成功吗？

原文

My tables have some inner pre-fix that users must not know.
So I made a SP like this to retrieve lines.

CREATE PROCEDURE p_Test  
 ( inStation varchar(10) )  
RETURNS  
 ( ServiceName varchar(20) )  
AS    
DECLARE VARIABLE StationTable varchar(20);  
BEGIN   
  StationTable = 'TSTATION_' || Upper(Trim(inStation));  

  SELECT StationTable.ServiceName  
  FROM StationTable  
  INTO :ServiceName;  
  suspend;  

END^

And I got a fail message like this

SQL error code = -204
Table unknown
STATIONTABLE

My fbclient.dll version is 2.1.2.18118.
My test enviroment is Flamerobin 0.9.2.1851.

Any idea to make this work?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

晒暮凉 2024-08-27 07:27:44

您可以尝试使用执行块：

execute block (inStation varchar(10) = ?)
returns (ServiceName = varchar(20))
as
DECLARE variable SQL Varchar(300);
DECLARE VARIABLE StationTable varchar(20);
begin
    StationTable = 'TSTATION_' || Upper(Trim(inStation)); 
    SQL := 'SELECT ' || StationTable ||'.ServiceName FROM ' || StationTable || ' INTO :ServiceName;';
    execute STATEMENT sql;
    SUSPEND;
end

检查此处

you can try to use execute block :

execute block (inStation varchar(10) = ?)
returns (ServiceName = varchar(20))
as
DECLARE variable SQL Varchar(300);
DECLARE VARIABLE StationTable varchar(20);
begin
    StationTable = 'TSTATION_' || Upper(Trim(inStation)); 
    SQL := 'SELECT ' || StationTable ||'.ServiceName FROM ' || StationTable || ' INTO :ServiceName;';
    execute STATEMENT sql;
    SUSPEND;
end

check here

回复收藏 0 原文

极致的悲 2024-08-27 07:27:44

感谢您的提示，@Hugues Van Landeghem。我使用两个关键字EXECUTE STATMENT使其工作。这是我的SP：

CREATE PROCEDURE p_Test ( inStation varchar(10) )
RETURNS ( ServiceName varchar(20) )
AS
    DECLARE VARIABLE StationTable varchar(20);  
BEGIN   
    StationTable = 'TSTATION_' || Upper(Trim(inStation));  
    EXECUTE STATEMENT 'SELECT ServiceName FROM ' || StationTable INTO :ServiceName;
    SUSPEND;  
END^

Thanks for your tips, @Hugues Van Landeghem. I've made it work using the two keywords EXECUTE STATEMENT. Here is my SP:

CREATE PROCEDURE p_Test ( inStation varchar(10) )
RETURNS ( ServiceName varchar(20) )
AS
    DECLARE VARIABLE StationTable varchar(20);  
BEGIN   
    StationTable = 'TSTATION_' || Upper(Trim(inStation));  
    EXECUTE STATEMENT 'SELECT ServiceName FROM ' || StationTable INTO :ServiceName;
    SUSPEND;  
END^

回复收藏 0 原文

~没有更多了~