PL/SQL存储过程返回布尔值判断执行结果，包代码报错求助

Alright, let's break down all the issues in your PL/SQL package code that's preventing it from running, then fix them step by step:

1. Procedure Name Mismatch #

Your package specification declares procedures named exem1 and exem2, but the package body uses exam1 and exam2 (note the 'a' vs 'e' in the first syllable). PL/SQL is case-insensitive but spelling-sensitive—this mismatch will cause compilation errors because the body doesn't align with the spec. We'll standardize on exam1 and exam2 for consistency.

2. Parameter Mode & Type Inconsistencies #

• The spec defines exam1 (originally exem1) with an OUT BOOLEAN parameter, but your body defines it as IN VARCHAR2 DEFAULT 'NULL'. These need to match. Since you want to return a success/failure flag, we'll keep p_success OUT BOOLEAN as the output parameter, and add a separate p_input IN VARCHAR2 DEFAULT NULL if you need to pass a value to the procedure.
• Also, DEFAULT 'NULL' sets the default to the string literal 'NULL', not the SQL NULL value. Use DEFAULT NULL instead.

3. Procedure vs. Function Misuse #

You tried to use exam2 in an IF condition (if exam2(p_value); then), but procedures don't return values—only functions do. To check if exam2 executes successfully, we'll keep exam2 as a procedure with an OUT BOOLEAN parameter to pass back its success status (this keeps procedures focused on executable logic, using outputs for status tracking).

4. Syntax & Control Flow Errors #

• You placed the BEGIN keyword inside the IF block, which is wrong—PL/SQL procedures follow the structure PROCEDURE ... IS BEGIN [logic] END;.
• Missing semicolons: else p_value:=false needs a semicolon after FALSE, and END exam1 needs a semicolon to close the procedure.
• The IF statement had an extra semicolon after the procedure call, which is invalid syntax.

Fixed Complete Code #

Here's the corrected package specification and body that compiles and works as intended:

CREATE OR REPLACE PACKAGE p1 AS
  PROCEDURE exam1 (p_input IN VARCHAR2 DEFAULT NULL, p_success OUT BOOLEAN);
  PROCEDURE exam2 (p_input IN VARCHAR2 DEFAULT NULL, p_success OUT BOOLEAN);
END p1;
/

CREATE OR REPLACE PACKAGE BODY p1 AS
  PROCEDURE exam1 (p_input IN VARCHAR2 DEFAULT NULL, p_success OUT BOOLEAN) IS
  BEGIN
    -- Call exam2 and get its success status
    exam2(p_input, p_success);
    
    IF p_success THEN
      DBMS_OUTPUT.put_line('successful!!!!');
    ELSE
      DBMS_OUTPUT.put_line('Error!!!!');
    END IF;
  EXCEPTION
    -- Catch any unexpected errors and mark as failure
    WHEN OTHERS THEN
      p_success := FALSE;
      DBMS_OUTPUT.put_line('Unexpected error: ' || SQLERRM);
  END exam1;

  PROCEDURE exam2 (p_input IN VARCHAR2 DEFAULT NULL, p_success OUT BOOLEAN) IS
  BEGIN
    -- Add your actual business logic here
    -- Example: Validate input
    IF p_input IS NOT NULL AND LENGTH(p_input) > 0 THEN
      p_success := TRUE;
      -- Insert your core logic here
    ELSE
      p_success := FALSE;
      -- Insert error handling logic here
    END IF;
  EXCEPTION
    WHEN OTHERS THEN
      p_success := FALSE;
      DBMS_OUTPUT.put_line('Exam2 error: ' || SQLERRM);
  END exam2;
END p1;
/

How to Test It #

Use an anonymous block to call the procedure and verify results:

SET SERVEROUTPUT ON;
DECLARE
  v_status BOOLEAN;
BEGIN
  -- Test with valid input
  p1.exam1('test input', v_status);
  
  -- Test with NULL input
  p1.exam1(NULL, v_status);
END;
/

This will print success/error messages to the console and set the v_status variable to TRUE or FALSE based on execution results.

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