在PSQL中使用参数 [英] Using parameter in PSQL

问题描述

我在psql中使用参数时遇到问题. 如何在where子句中正确使用参数. 编译我在下面列出的程序时没有错误. 但是当我传递"bla bla bla"字符串时没有任何结果.

I have a problem with using parameter in psql. How to properly use the parameter in where clause. There are no errors compiling the procedure which I listed below. But no results when I pass 'bla bla bla' string.

CREATE PROCEDURE SELECTCATALOGUE (
 TXT  VARCHAR(30))
RETURNS (
 CODE VARCHAR(9),
 NAME VARCHAR(50))
AS 
BEGIN     
for execute statement
'select code,name
            from catalogue
            where name='||:TXT
into :CODE,:NAME
do 
   suspend;
END

但是，当我通过替换参数来执行此过程时，例如:

But, when I execute this procedure by replacing the parameter for example:

CREATE PROCEDURE SELECTCATALOGUE (
 TXT  VARCHAR(30))
RETURNS (
 CODE VARCHAR(9),
 NAME VARCHAR(50))
AS 
BEGIN     
for execute statement
'select code,name
            from catalogue
            where name=''bla bla bla'''
into :CODE,:NAME
do 
   suspend;
END

我得到了正确的结果.我是否缺少某些东西，如何使其起作用?

I'm getting results properly. Am I missing something, how to make it work?

好的，这是另外一件事.此过程:

Ok, here is one more thing. This procedure:

CREATE PROCEDURE SELECTCATALOGUE
RETURNS (
 CODE VARCHAR(9),
 NAME VARCHAR(50))
AS 
declare variable stmt varchar(1000);
declare variable paramTXT varchar(100);
declare variable paramTXT1 varchar(100);
declare variable T varchar(1000);
BEGIN     
paramTXT='PLO'||'%';
paramTXT1='REM'||'%';
T='paramTXT,paramTXT1';
stmt='select code,name
            from catalogue
            where name like ? or name like ? order by name';
for execute statement (
  stmt
)
            (paramTXT,paramTXT1)
into :CODE,:NAME
do 
   suspend;
END

请给我适当的结果.有没有一种方法可以使用"T"字符串代替((paramTXT，paramTXT1)""，这样可以给我结果?

Gives me back proper results. Is there a way to use "T" string instead "(paramTXT,paramTXT1)" and that can give me results?

推荐答案

在您的第一个版本中，您将两个字符串连接起来，得到以下字符串:

In your first version, you concatenate two strings which result in following string:

select code,name from catalogue where name=bla bla bla

您看到字符串bla bla bla周围没有引号，因此引擎应将其视为列名，通常这会导致出现诸如找不到列bla bla bla列"之类的错误.如果碰巧您发送的值与某些列名匹配，那么除非有两行具有相同值的行，否则您将获得空结果集.

You see that there is no quotes around the string bla bla bla, so the engine should treat it as a column name and usually this should result in a error like "column bla bla bla not found" or some such. If it happens that you send in an value which matches some column name then you would get empty resultset unless there is row(s) where the two columns have the same value.

要解决此问题，请使用参数化语句:

To fix it, use parameterized statement:

for execute statement (
   'select code,name
            from catalogue
            where name = :parName')
   (parName := TXT)
   into :CODE,:NAME
do 

请参见

See the documentation for the full syntax of the execute statement.

这篇关于在PSQL中使用参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！