pl/sql - Is there a NVL() for empty rows

With this,

set serveroutput on size 900000;
DECLARE开发者_如何学Python
  test VARCHAR(255):=0;
BEGIN
    SELECT id INTO test FROM sku WHERE id = 1515;
    dbms_output.put_line('Result:' || test);
END;

I'm having the error

"no data found"

When ID doesn't exist in database.

How can I use something like nvl() here, so I can get a default value instead of get an error?

If appropriate you could do this as an alternative to the more usual NO_DATA_FOUND exception handling:

DECLARE
  test VARCHAR(255):=0;
BEGIN
    SELECT NVL(MAX(id),'default') INTO test FROM sku WHERE id = 1515;
    dbms_output.put_line('Result:' || test);
END;

By using MAX(ID) you are sure to get one row back, which will have a NULL when there is no data; you then use NVL in the usual way.

You could catch the NO_DATA_FOUND Exception

DECLARE
  test VARCHAR(255):=0;
BEGIN
    SELECT id INTO test FROM sku WHERE id = 1515;
    dbms_output.put_line('Result:' || test);

EXCEPTION
   WHEN NO_DATA_FOUND THEN
   dbms_output.put_line ('sorry no data found :(' );
END;

More on PL/SQL error handling here: http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96624/07_errs.htm

To my knowledge, there is no such thing as nvl() for an empty row in PL/SQL, the NO_DATA_FOUND exception is probably the closest you'll get.

You can't use NVL (or NVL2 or COALESCE, which are similar) here because there's no data on which these functions can operate. The following will do what you've asked:

DECLARE 
  test VARCHAR2(255):=0; 
BEGIN 
  BEGIN
    SELECT id INTO test FROM sku WHERE id = 1515;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      test := '0';
  END;

  dbms_output.put_line('Result:' || test); 
END;

but just because something is possible doesn't mean it's a good idea. The database is trying to warn about something important (specifically, that there's no data which matches your criteria) and this code ignores that warning.

Depending on what you're doing, you could move your query to a cursor and fetch from it:

DECLARE
test VARCHAR(255):=0;

CURSOR c_test IS
    SELECT id
    FROM sku
    WHERE id = 1515
; 
BEGIN
    OPEN c_test;
    FETCH c_test INTO test;
    IF c_test%NOTFOUND THEN
        dbms_output.put_line('no data found');
    ELSE
        dbms_output.put_line('result:' || test);
    END IF;
    CLOSE c_test;
END;