Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2006
    Posts
    58

    Question Unanswered: How to decide whether there are records in the table?

    Hi, everyone, Thank you very much!

    In the T-SQL of Microsoft SQL Server 2005, we can use the following statement to decide whether there are some records or not and deal with other logics:

    Ex: IF EXISTS(SELECT * FROM table_1)
    BEGIN
    DELETE FROM table_1;
    END
    Here, if there are some records in the TABLE "table_1" , I want to DELETE the records.

    When I use PL/SQL to deal with the same function, how can I do? That is to say, if there are some records in the TABLE "table_1" , I want to DELETE the records. How can I do in PL/SQL in Oracle 10g?

    I am anxious to get your reply. Thank you very much!

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    DELETE FROM table_1;


    What is wrong with just doing the above; regardless of number of existing rows?

    FWIW - I more than a decade I've never had an application needing to do as you deem necessary to do.

    Exactly what is the business case for this action?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by anacedent
    FWIW - I more than a decade I've never had an application needing to do as you deem necessary to do.
    Totally agree. I too would be interested in the reasoning behind that
    Probably some strange MS SQL oddity...

  4. #4
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    There is no such direct way to do this in PL/SQL, you would get this error :
    Code:
    PLS-00204: function or pseudo-column 'EXISTS' may be used inside a SQL statement only
    But you can do something like this :
    Code:
    DECLARE
    
        iNb NUMBER(1);
    
    BEGIN
    
        SELECT CASE WHEN EXISTS(SELECT NULL FROM table_1) THEN 1 ELSE 0 END
        INTO iNb
        FROM DUAL;
    
        IF (iNb = 1) THEN
            dbms_output.put_line('exists !');
        ELSE
            dbms_output.put_line('not exists !');
        END IF;
    
    END;
    BTW if you ALWAYS perform the DELETE if there is any row within the table, then I agree with anacedent and shammat : this test is useless. If you have more complex processing however, then it can be very useful to use EXISTS as it tells you extremely quickly if there is any data within the table.

    HTH & Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  5. #5
    Join Date
    Aug 2006
    Posts
    58
    Thank you, all of above friends.
    I am sorry for I could not clearly express my meanings. In Microsoft SQL Server 2005, In the procedure, When I want to create a table, I will do as the following steps:
    (1) If there is the given table in the given database, I will delete the records from the given table.
    (2) If there is not the given table in the given database, I will create the given the table.

    So I often use the following SQL Statements:

    IF EXISTS( SELECT name FROM sysobjects
    WHERE name = 'T1' AND type = 'U')
    BEGIN
    DELETE FROM T1'
    END
    ELSE
    BEGIN
    -- Creation Table...
    END

    How can I do as the above example in Oracle 10g?

    Thank you very much!!!

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Substitute user_tables for sysobjects, and you should be on your way.

  7. #7
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Code:
    DECLARE
    
        iNb NUMBER(1);
    
    BEGIN
    
        -- Beware : Oracle stores object names in upper case
        -- User_Tables will work if the table is within your schema, else use All_Tables and "AND Owner = ..."
        SELECT CASE WHEN EXISTS(SELECT NULL FROM User_Tables WHERE Table_Name = 'TABLE_1') THEN 1 ELSE 0 END
        INTO iNb
        FROM DUAL;
    
        IF (iNb = 1) THEN
            -- DELETE FROM TABLE_1;
        ELSE
            -- CREATE TABLE TABLE_1...
        END IF;
    
    END;
    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Another way:
    Code:
    declare
       table_does_not_exist exception;
       pragma exception_init(table_does_not_exist,-942);
    begin
       execute immediate 'truncate table mytable';
    exception
       when table_does_not_exist then
          execute immediate 'create table mytable (id integer)';
    end;
    /

  9. #9
    Join Date
    Aug 2006
    Posts
    58
    Thanks! According to your advice, I have succeeded.

  10. #10
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Tony's the best....
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •