英文:
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论