Find id then assign 1 if id found from table PL sql create procedure-CodePudding

I'm looking to create a procedure that looks for the given customer ID in the database. If the customer exists, it sets the variable found to 1. Otherwise, the found variable is set to 0. However, my call out code block does not provide a result. Did I miss something or my SELECT statement should be something else? Thank you.

CREATE OR REPLACE PROCEDURE find_customer(CUST_ID IN NUMBER, found OUT NUMBER) AS
    CUSTID NUMBER := CUST_ID;
BEGIN
    SELECT CUSTOMER_ID INTO CUSTID
    FROM CUSTOMERS
    WHERE CUSTOMER_ID = CUST_ID;
    IF CUST_ID = NULL THEN
    found := 1;
    END IF;
EXCEPTION
WHEN no_data_found THEN
found := 0;
END;
/
DECLARE
    CUSTOMER_ID NUMBER := 1;
    found NUMBER;
BEGIN
    find_customer(1,found);
    DBMS_OUTPUT.PUT_LINE (found);
END;

CodePudding user response：

I don't think there's anything other to it than the following part bellow. In your given example, it is not possible to get a null value from it as any null id would probably mean the item doesn't exist. Meaning it doesn't return a row, which triggers the NO_DATA_FOUND exception, which you catch.

This is what you wrote:

IF CUST_ID = NULL THEN
  found := 1;
END IF;

This is probably what you meant:

IF CUST_ID IS NOT NULL THEN
  found := 1;
END IF;

CodePudding user response：

I'd rewrite it so that

you distinguish parameters from local variables from column names
use table aliases
fix what happens when something is found (is not null, line #11)
while testing, use variable you declared, not a constant (1)

So:

SQL> CREATE OR REPLACE PROCEDURE find_customer (par_cust_id  IN     NUMBER,
  2                                             par_found       OUT NUMBER)
  3  AS
  4     l_custid  NUMBER;
  5  BEGIN
  6     SELECT c.customer_id
  7       INTO l_custid
  8       FROM customers c
  9      WHERE c.customer_id = par_cust_id;
 10
 11     IF l_custid IS NOT NULL
 12     THEN
 13        par_found := 1;
 14     END IF;
 15  EXCEPTION
 16     WHEN NO_DATA_FOUND
 17     THEN
 18        par_found := 0;
 19  END;
 20  /

Procedure created.

Testing:

SQL> SET SERVEROUTPUT ON
SQL> SELECT * FROM customers;

CUSTOMER_ID
-----------
        100

SQL> DECLARE
  2     l_customer_id  NUMBER := 1;
  3     l_found        NUMBER;
  4  BEGIN
  5     find_customer (l_customer_id, l_found);
  6     DBMS_OUTPUT.put_line (l_found);
  7  END;
  8  /
0

PL/SQL procedure successfully completed.

SQL> DECLARE
  2     l_customer_id  NUMBER := 100;
  3     l_found        NUMBER;
  4  BEGIN
  5     find_customer (l_customer_id, l_found);
  6     DBMS_OUTPUT.put_line (l_found);
  7  END;
  8  /
1

PL/SQL procedure successfully completed.

SQL>

CodePudding user response：

You can simplify it down to:

CREATE OR REPLACE PROCEDURE find_customer(
  p_cust_id IN  CUSTOMERS.CUSTOMER_ID%TYPE,
  p_found   OUT NUMBER
) AS
BEGIN
  SELECT 1
  INTO   p_found
  FROM   CUSTOMERS
  WHERE  CUSTOMER_ID = p_cust_id;
EXCEPTION
  WHEN no_data_found THEN
    p_found := 0;
END;
/

The line CUSTOMER_ID = p_cust_id will not match if either side is NULL so you don't need any further checks.

Then you can call it using:

DECLARE
  v_found NUMBER;
BEGIN
  find_customer(1,v_found);
  DBMS_OUTPUT.PUT_LINE (v_found);
END;
/

db<>fiddle here