Oracle: multiple rows in INTO variable in procedure

Question

My procedure look like this.

CREATE OR REPLACE PROCEDURE USP_TMS_OPT_DATA
AS

V_NAME           VARCHAR2(10);
V_TEL_NO         VARCHAR2(10);
V_ADDR           VARCHAR2(10);
...

SELECT NEW_NAME
     , NEW_TEL_NO
     , NEW_ADDR
     , REG_NO
  INTO V_NAME     -- get data row. but it is error.
     , V_TEL_NO
     , V_ADDR  
     , V_REG_NO
  FROM DAILY_VISITOR;
  
  MERGE  -- I use MERGE, because INSERT or UPDATE
  INTO ALL_VISITOR A
  USING dual
    ON (A.REG_NO = V_REG_NO)
  WHEN MATCHED THEN
       UPDATE
          SET A.ADDR = V_ADDR
            , A.NAME = V_NAME
            , A.TEL_NO = V_TEL_NO
  WHEN NOT MATCHED THEN
       INSERT (REG_NO, NAME, TEL_NO, ADDR)
       VALUES (V_REG_NO, V_NAME, V_TEL_NO, V_ADDR);

...  

I get data in DAILY_VISITOR and insert or update in ALL_VISITOR.

But I get an error

ORA-01422: exact fetch returns more than requested number of rows

How can I get multiple rows of data using INTO or other?

Or is it possible use MERGE like INSERT INTO SELECT?

Oracle is 11g.

Answer 1

To SELECT multiple rows into variables you can either use:

• BULK COLLECT INTO with collection variables; or
• open the statement as a cursor and use row-by-row processing with the cursor.

But don't do either of those two because you should not be using intermediate variables for this problem and should not be using PL/SQL; instead, combine the two statements and solve it entirely in SQL:

MERGE INTO ALL_VISITOR A
USING (
  SELECT NEW_NAME
       , NEW_TEL_NO
       , NEW_ADDR
       , REG_NO
  FROM DAILY_VISITOR
) v
ON (A.REG_NO = V.REG_NO)
WHEN MATCHED THEN
  UPDATE
  SET A.ADDR = V.NEW_ADDR
    , A.NAME = V.NEW_NAME
    , A.TEL_NO = V.NEW_TEL_NO
WHEN NOT MATCHED THEN
  INSERT (REG_NO, NAME, TEL_NO, ADDR)
  VALUES (V.REG_NO, V.NEW_NAME, V.NEW_TEL_NO, V.NEW_ADDR);