如何在PL/SQL中以字符串模式执行查询?

huangapple go评论65阅读模式
英文:

How to execute query in string mode in pl/sql?

问题

我尝试执行该过程,但在执行此行时显示缺少表达式的错误 execute immediate sqlQuery into test;。我的查询返回6列,我需要将其存储到变量中,请帮助执行此查询。

procedure getAttributeOptions(subID number, compID number,docNumber varchar2,transType varchar2 ,rowNum varchar2 ,
          Attribute1 out varchar2,Attribute2 out varchar2,Attribute3 out varchar2,Attribute4 out varchar2,
          Attribute5 out varchar2, Attribute6 out varchar2) is
sqlQuery varchar2(4000);
storageColumns varchar2(4000);
colName varchar2(1000);
tableName varchar2(1000);
primaryKey varchar2(1000);
test varchar2(4000);

begin
 colName:=' ATTRIBUTE_OPTION_1,ATTRIBUTE_OPTION_2,ATTRIBUTE_OPTION_3,ATTRIBUTE_OPTION_4,ATTRIBUTE_OPTION_5,ATTRIBUTE_OPTION_6 ';
 storageColumns:=' Attribute1,Attribute2,Attribute3,Attribute4,Attribute5,Attribute6 ';

  if transType = 'TY_ISSUE' then
    tableName:='preq_master_detail';
    primaryKey:='req_number';
  elsif transType='TY_TRANSFER' then 
     tableName:='pinv_transfer_detail'; 
     primaryKey:='transfer_id';
  elsif transType='TY_RECEIVE' then 
    tableName:='ppo_master_detail';
    primaryKey:='po_number';
  elsif transType='TY_MANUAL' then 
    tableName:='part_manual_adjustment';
    primaryKey:='adjustment_id';
    end if;
  
  dbms_output.put_line(tableName);
  
  if tableName is not null then
   begin
    sqlQuery:='select ' || colName || ' into ' || storageColumns || ' from ' || tableName ;
    sqlQuery:=sqlQuery  || ' where subscriber_id=' || subID; 
    sqlQuery:=sqlQuery  || ' and company_id=' || compID;
    sqlQuery:=sqlQuery  || ' and ' || primaryKey || '=' || '''' || docNumber || '''' || ' and row_number ='||rowNum;
    
    dbms_output.put_line(sqlQuery); 
    execute immediate sqlQuery into test;--此行存在问题
    
     dbms_output.put_line(Attribute1); 
     dbms_output.put_line(Attribute2); 
     dbms_output.put_line(Attribute3); 
     dbms_output.put_line(Attribute4); 
     dbms_output.put_line(Attribute5); 
     dbms_output.put_line(Attribute6); 
    
    exception when NO_DATA_FOUND then return ;
   end; 
  end if;
  end;
英文:

I m trying to execute the procedure but it is showing the errror of missing expression while executing this line execute immediate sqlQuery into test;
My query returns 6 columns which i need to store into the variables , please help to execute this query.

procedure getAttributeOptions(subID number, compID number,docNumber varchar2,transType varchar2 ,rowNum varchar2 ,
Attribute1 out varchar2,Attribute2 out varchar2,Attribute3 out varchar2,Attribute4 out varchar2,
Attribute5 out varchar2, Attribute6 out varchar2) is
sqlQuery varchar2(4000);
storageColumns varchar2(4000);
colName varchar2(1000);
tableName varchar2(1000);
primaryKey varchar2(1000);
test varchar2(4000);
begin
colName:=' ATTRIBUTE_OPTION_1,ATTRIBUTE_OPTION_2,ATTRIBUTE_OPTION_3,ATTRIBUTE_OPTION_4,ATTRIBUTE_OPTION_5,ATTRIBUTE_OPTION_6 ';
storageColumns:=' Attribute1,Attribute2,Attribute3,Attribute4,Attribute5,Attribute6 ';
if transType = TY_ISSUE then
tableName:='preq_master_detail';
primaryKey:='req_number';
elsif transType=TY_TRANSFER then 
tableName:='pinv_transfer_detail'; 
primaryKey:='transfer_id';
elsif transType=TY_RECEIVE then 
tableName:='ppo_master_detail';
primaryKey:='po_number';
elsif transType=TY_MANUAL then 
tableName:='part_manual_adjustment';
primaryKey:='adjustment_id';
end if;
dbms_output.put_line(tableName);
if tableName is not null then
begin
sqlQuery:='select ' || colName || 'into ' || storageColumns || 'from ' || tableName ;
sqlQuery:=sqlQuery  || ' where subscriber_id=' || subID; 
sqlQuery:=sqlQuery  || ' and company_id=' || compID;
sqlQuery:=sqlQuery  || ' and ' || primaryKey ||'='|| '''' || docNumber || '''' || ' and row_number ='||rowNum;
dbms_output.put_line(sqlQuery); 
execute immediate sqlQuery into test;--issue in this line 
dbms_output.put_line(Attribute1); 
dbms_output.put_line(Attribute2); 
dbms_output.put_line(Attribute3); 
dbms_output.put_line(Attribute4); 
dbms_output.put_line(Attribute5); 
dbms_output.put_line(Attribute6); 
exception when NO_DATA_FOUND then return ;
end; 
end if;
end;

答案1

得分: 1

你将你的INTO子句中的变量列表放在了SQL中,而不是在PL/SQL中。你需要将INTO子句放在外面。希望你可以硬编码这个子句:

sqlQuery := 'select ' || colName || ' from ' || tableName;
. . .
execute immediate sqlQuery into Attribute1, Attribute2, Attribute3, Attribute4, Attribute5, Attribute6;

在此基础上,你应该为你的WHERE子句使用绑定变量:

. . .
sqlQuery := sqlQuery || ' where subscriber_id = :subid';
sqlQuery := sqlQuery || ' and company_id = :compID';
sqlQuery := sqlQuery || ' and ' || primaryKey || ' = :docno' || ' and row_number = :rowNum';
execute immediate sqlQuery 
INTO Attribute1, Attribute2, Attribute3, Attribute4, Attribute5, Attribute6 
USING subid, compid, docNumber, rowNum;

这将让你的数据库管理员满意。

英文:

You are placing the list of variables for your INTO clause inside the SQL, rather than outside in PL/SQL. You need to use put the INTO clause outside. Hopefully you can hard-code that clause:

sqlQuery:='select ' || colName || 'from ' || tableName ;
. . .
execute immediate sqlQuery into Attribute1,Attribute2,Attribute3,Attribute4,Attribute5,Attribute6;

While you are at it, you should use bind variables for your WHERE clause:

. . .
sqlQuery:=sqlQuery  || ' where subscriber_id=:subid';
sqlQuery:=sqlQuery  || ' and company_id=:compID';
sqlQuery:=sqlQuery  || ' and ' || primaryKey ||'= :docno'|| ' and row_number =:rowNum';
execute immediate sqlQuery 
INTO Attribute1,Attribute2,Attribute3,Attribute4,Attribute5,Attribute6 
USING subid,compid,docNumber,rowNum;

That will make your DBA happy.

huangapple
  • 本文由 发表于 2023年2月16日 05:41:10
  • 转载请务必保留本文链接:https://go.coder-hub.com/75465699.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定