PL/SQL变量基础语法咨询及SELECT语句PLS-00428报错排查

Hey there! Let’s break down why you’re seeing the PLS-00428 an INTO clause is expected in this Select statement error, and how to fix it.

Why This Error Happens #

Unlike plain SQL (which sends result sets directly to a client), PL/SQL is a procedural language—when you run a SELECT inside a PL/SQL block, the engine needs to know where to store the query results. Without an INTO clause, it has no way to handle the data, hence the error.

Solution 1: Single-Row Query (Use INTO to Assign to Variables) #

If your query returns exactly one row, declare variables to hold each column’s value and use INTO to map them:

DECLARE
  -- Declare variables matching the data types of your SELECT columns
  v_employee_name VARCHAR2(100);
  v_employee_salary NUMBER(8,2);
BEGIN
  -- Map query results to variables with INTO
  SELECT first_name || ' ' || last_name, salary
  INTO v_employee_name, v_employee_salary
  FROM employees
  WHERE employee_id = 101; -- This should return 1 row

  -- Use the variables (example: print output)
  DBMS_OUTPUT.PUT_LINE('Employee: ' || v_employee_name || ' | Salary: ' || v_employee_salary);
END;
/

Solution 2: Multiple-Row Query (Use BULK COLLECT INTO for Collections) #

If your query returns multiple rows, you’ll need a collection (like a table or array) to store all results. Use BULK COLLECT INTO to populate the collection:

DECLARE
  -- Define a custom collection type for employee names
  TYPE emp_name_list IS TABLE OF VARCHAR2(100);
  v_emp_names emp_name_list;
BEGIN
  -- Bulk-collect multiple rows into the collection
  SELECT first_name || ' ' || last_name
  BULK COLLECT INTO v_emp_names
  FROM employees
  WHERE department_id = 30; -- Returns multiple rows

  -- Loop through the collection to access each result
  FOR i IN v_emp_names.FIRST .. v_emp_names.LAST LOOP
    DBMS_OUTPUT.PUT_LINE('Team Member ' || i || ': ' || v_emp_names(i));
  END LOOP;
END;
/

Pro Tip: Handle Edge Cases with Exceptions #

It’s smart to add exception handling for scenarios where your query returns no rows or too many rows:

DECLARE
  v_emp_name VARCHAR2(100);
BEGIN
  SELECT first_name || ' ' || last_name
  INTO v_emp_name
  FROM employees
  WHERE employee_id = 9999; -- Non-existent ID

  DBMS_OUTPUT.PUT_LINE('Employee: ' || v_emp_name);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Error: No employee matches that ID.');
  WHEN TOO_MANY_ROWS THEN
    DBMS_OUTPUT.PUT_LINE('Error: Multiple employees match the criteria.');
END;
/

Key Takeaway #

In PL/SQL, every SELECT statement must have a way to store its results. For single rows, use INTO with scalar variables. For multiple rows, use BULK COLLECT INTO with a collection. You can’t run a "standalone" SELECT like you would in SQL*Plus or a client tool—PL/SQL needs to know what to do with the data it retrieves.

内容的提问来源于stack exchange，提问作者John