时间戳

首页 > 解决方案 > PL/SQL: ORA-01744: inappropriate INTO

sql - PL/SQL: ORA-01744: inappropriate INTO

问题描述

I want to store the item I get from the loop into the variable I created and print it out, but I faced this error

28/7     PL/SQL: SQL Statement ignored
30/39    PL/SQL: ORA-01744: inappropriate INTO

Here are my loop and my declare:

v_num1              number;
v_menuid            varchar(6);
v_menuname          varchar(100);
v_stock             number;
...
FOR loop_counter in 1..v_num1
   LOOP
      SELECT *
      FROM ( SELECT a.*, rownum rnum
             FROM ( SELECT menuitemid INTO v_menuid, menuitemname INTO v_menuname, quantityinstock INTO v_stock
                    FROM menuitem m, restaurant r
                    WHERE m.restaurantid = r.restaurantid AND restaurantname = IN_ResName AND quantityinstock <= 10) a
             WHERE rownum <= loop_counter ) 
      WHERE rnum >= loop_counter;
       
      DBMS_OUTPUT.PUT_LINE(chr(10));
      DBMS_OUTPUT.PUT_LINE('Menu ID: '||v_menuid);
      DBMS_OUTPUT.PUT_LINE('Menu Name: '||v_menuname);
      DBMS_OUTPUT.PUT_LINE('Quantity In Stock: '||v_stock);

   END LOOP;

I want to print out the result like :

Menu ID: M00001
Menu Name: Burger
Stock : 10

标签： sqloracleplsql

解决方案

  1. 每个投影只有一个 INTO 语句。
  2. INTO 语句属于最外层的投影。
SELECT menuitemid , menuitemname, quantityinstock
INTO v_menuid, v_menuname, v_stock
FROM ( SELECT a.*, rownum rnum
       FROM ( SELECT menuitemid, menuitemname, quantityinstock
              FROM menuitem m, restaurant r
              WHERE m.restaurantid = r.restaurantid AND restaurantname = IN_ResName AND quantityinstock <= 10) a
       WHERE rownum <= loop_counter ) 
WHERE rnum >= loop_counter;

推荐阅读