oracle - 语句未在 Oracle 中使用 Execute Immediate 执行

Question

以下语句在正常执行中执行，但使用时EXECUTE IMMEDIATE没有结果返回。

select distinct 
    FEEDBACKQUESTIONS.FeedbackQuestionId as id,
    FEEDBACKQUESTIONS.FeedbackQuestionId  
from FEEDBACKQUESTIONS 
left join LANGUAGES on FEEDBACKQUESTIONS.LanguageId = LANGUAGES.LanguageId  
where 
    1=1 
    and FEEDBACKQUESTIONS.CrdBy = 1 
order by 
    FEEDBACKQUESTIONS.FeedbackQuestionId desc 
    OFFSET 10 * (1 - 1) ROWS FETCH NEXT 10 ROWS ONLY

标签： oracle

Answer 1

来自Oracle 文档

如果 dynamic_sql_statement 是 SELECT 语句，并且您同时省略了 into_clause 和 bulk_collect_into_clause，则 execute_immediate_statement 永远不会执行。

SELECT INTO如果您想查看EXECUTE IMMEDIATE选择值的结果，您可以使用 use子句并将值分配给某个变量。如果您的select语句只返回一行，那么您可以使用以下过程。

DECLARE P_CRDBY NUMBER := 1; 
V_ID VARCHAR2(20);
V_QID VARCHAR2(20);
BEGIN 
EXECUTE IMMEDIATE 'SELECT DISTINCT FEEDBACKQUESTIONS.FEEDBACKQUESTIONID AS ID,
                   FEEDBACKQUESTIONS.FEEDBACKQUESTIONID FROM FEEDBACKQUESTIONS 
                   LEFT JOIN LANGUAGES ON FEEDBACKQUESTIONS.LANGUAGEID = LANGUAGES.LANGUAGEID 
                   WHERE 1=1 AND FEEDBACKQUESTIONS.CRDBY = 1 
                   ORDER BY FEEDBACKQUESTIONS.FEEDBACKQUESTIONID DESC 
                   OFFSET 10 * (1 - 1) ROWS FETCH NEXT 10 ROWS ONLY' 
                   INTO V_ID,V_QID ; 
                   --USING P_CRDBY; DBMS_OUTPUT.PUT_LINE(P_CRDBY); 
 DBMS_OUTPUT.PUT_LINE('ID=' || V_ID ||', ID2=' || V_QID);
 END;

对于多行，您可以使用FOR LOOP CURSOR循环遍历结果集。

DECLARE P_CRDBY NUMBER := 1; 
V_ID VARCHAR2(20);
V_QID VARCHAR2(20);
BEGIN 
FOR MYROW IN (SELECT DISTINCT FEEDBACKQUESTIONS.FEEDBACKQUESTIONID AS ID,
                   FEEDBACKQUESTIONS.FEEDBACKQUESTIONID AS ID2 FROM FEEDBACKQUESTIONS 
                   LEFT JOIN LANGUAGES ON FEEDBACKQUESTIONS.LANGUAGEID = LANGUAGES.LANGUAGEID 
                   WHERE 1=1 AND FEEDBACKQUESTIONS.CRDBY = 1 
                   ORDER BY FEEDBACKQUESTIONS.FEEDBACKQUESTIONID DESC 
                   OFFSET 10 * (1 - 1) ROWS FETCH NEXT 10 ROWS ONLY) 
 --USING P_CRDBY; DBMS_OUTPUT.PUT_LINE(P_CRDBY); 
LOOP                
    DBMS_OUTPUT.PUT_LINE('ID=' || MYROW.ID||', ID2=' || MYROW.ID2);
END LOOP;
END;