Proper way of checking if row exists in table in PL/SQL block

sql

prompt-engineering

best-practices

debugging

byAlex Kataev·Dec 26, 2024

To achieve peak efficiency when checking for a row's existence, combine SELECT INTO with a single variable and an IF check:

DECLARE
  v_count PLS_INTEGER;
BEGIN
  SELECT COUNT(1) INTO v_count FROM your_table WHERE your_condition;
  IF v_count > 0 THEN
    -- Hey, we got a match! 🥳
  ELSE
    -- Pay no mind, just a tumbleweed 🌵
  END IF;
END;

This strategy gracefully sidesteps any NO_DATA_FOUND exceptions, keeping your PL/SQL block as smooth as a buttered slide.

Kick those performance concerns to the curb

Less is more: wreak havoc with ROWNUM

Consider crafting your SELECT statement with WHERE ROWNUM = 1. Here's why: you only need to establish whether your row is hanging out in the table at all, not how many times it decided to replicate itself. The Oracle engine will thank you by wrapping up the search early.

How about no exceptions

Reserve exception handlers for, well, exceptions. Misusing them as flow control risks turning your PL/SQL code into spaghetti code faster than you can say "carbonara". Trust me, it's not a sight for sore eyes.

Don't forget to close the door

Be a good database citizen and close those cursors as explicitly as you would close the door when you leave. Unused database resources floating around? Not on my watch.

Keep it all in the main frame

For efficient usage of database capabilities, embed those existence checks right in your main queries.

Row existence strategies: pick your weapon of choice

The Fast and Furious: CURSOR

Taking an explicit cursor for a spin? Deck it out with %ROWTYPE to give fetching a boost:

DECLARE
  CURSOR c_your_table IS SELECT * FROM your_table WHERE your_condition; 
  r_your_table c_your_table%ROWTYPE;
BEGIN
  OPEN c_your_table;
  FETCH c_your_table INTO r_your_table;

  IF c_your_table%FOUND THEN
    -- "FAST" Furious 💨
  ELSE
    -- Can't touch this 🖐️
  END IF;

  CLOSE c_your_table;
END;

The Upside Down: OUTER JOIN

Why not shake things up a bit and play with a left outer join matched with a NULL check?

DECLARE
  v_id        your_table.id%TYPE;
  v_other_id  other_table.id%TYPE := your_value;
BEGIN
  SELECT a.id INTO v_id
  FROM your_table a
  LEFT JOIN other_table b ON a.join_col = b.join_col AND b.id = v_other_id
  WHERE b.id IS NULL AND your_condition;

  IF v_id IS NOT NULL THEN
    -- Stranger Things have happened...
  ELSE
    -- Who you gonna call? Ghostbusters!
  END IF;
END;

The Clean Slate: table package function

Subjecting your codebase to spring cleaning? Consider a table package function for boolean checks on row existence.

Dodging pitfalls like a pro

Singleton SELECTs: not your friend

Singleton SELECTs, like that cowboy who can't shoot straight in an old Western, may end up being slower and more error-prone than their companions. Stick with other methods that make better use of PL/SQL block features.

Close your cursors to wipe out any lurking database resource leaks. Consider it your superhero responsibility.

'NO DATA FOUND'? Find a better way

NO_DATA_FOUND can feel like a trusty sidekick, but don't let it fool you. Best practice recommends using it as an actual exception handler, not a flow control mechanism.