1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to check a value in column

Discussion in 'SQL PL/SQL' started by jknath, Feb 2, 2015.

  1. jknath

    jknath Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    select * from t1;

    id
    ==
    100
    200
    300
    400
    500

    I want a procedure to get an input and check whether the given input values is found in column ID or not. If it is found then output should be FOUND else NOT FOUND.

    Plz give me the procedure. I tried in many ways. Gotten difference answers but not the desired one.

    Plz help as I am new to plsql.
     
  2. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi,

    Change it to procedure...

    DECLARE
    v_variable NUMBER:=1234; --Here give your input value
    v_status VARCHAR2(100);
    v_count NUMBER;
    BEGIN
    SELECT COUNT(id) INTO v_count FROM t1 WHERE id=v_variable;
    IF v_count >0 THEN
    v_status:='FOUND';
    ELSE
    v_status:='NOT FOUND';
    END IF;
    dbms_output.put_line(v_status);
    END;

    Hope this will help you. Let me know, If I am wrong.
     
  3. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.

    Why procedure, but not function?

    In documentation basic examples are very well described... create procedure

    Note: If you study oracle, for a start look at documentation and basic examples.
    It will bring you a lot of benefit in the future.

    here two simple examples....

    scenario1 :
    Code (SQL):

    CREATE OR REPLACE PROCEDURE prc_test (p_id  t1.id%TYPE)
    AS
    BEGIN    
        SELECT
            decode(MAX(1),NULL,'not found','found')
        INTO
            v_status
        FROM
            t1      
        WHERE
            id = p_id;
           
        dbms_output.put_line(v_status);            
       
    END;    
    /    

    EXEC prc_test(1234);
     

    scenario2 :
    Code (SQL):

    CREATE OR REPLACE PROCEDURE prc_test
        (
            p_id  t1.id%TYPE,
            p_state OUT BOOLEAN
        )
    AS
        l_found pls_integer;
    BEGIN        
       
        p_state := TRUE; -- not found
       
        SELECT
            1
        INTO
            l_found
        FROM
            t1      
        WHERE
            id = p_id;
           
    exception
        WHEN no_data_found THEN   p_state := FALSE;
    END;    
    /    

    DECLARE
     l_state BOOLEAN;
    BEGIN
        prc_test(122234,l_state);
        dbms_output.put_line(CASE WHEN NOT l_state THEN 'not' END ||' found');
    END;
    /    
     
     
  4. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi,

    As Sergey mentioned above, you can either create a Function or Procedure - we don't know exactly your requirements as to how and where this 'component' would be called and what it is supposed to do (in addition to checking if the row with a given ID exists in table T1).

    A simple select which you can use (similar with some variance to what has been proposed above):

    Where p_id = parameter for your Function/Procedure.
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Since v_status isn't declared in your procedure it won't compile, thus it won't run. Changing the code a bit:


    Code (SQL):

    SQL> CREATE TABLE t1(
      2          id      NUMBER NOT NULL
      3  );


    TABLE created.


    SQL>
    SQL> BEGIN
      2          FOR i IN 100..1000 loop
      3                  IF MOD(i, 100) = 0 THEN
      4                          INSERT INTO t1
      5                          VALUES(i);
      6                  END IF;
      7          END loop;
      8
      9          commit;
     10  END;
     11  /


    PL/SQL PROCEDURE successfully completed.


    SQL>
    SQL> CREATE OR REPLACE PROCEDURE prc_test (p_id  t1.id%TYPE)
      2  AS
      3      v_status    varchar2(10);
      4  BEGIN
      5      SELECT
      6          decode(MAX(1),NULL,'not found','found')
      7      INTO
      8          v_status
      9      FROM
     10          t1
     11      WHERE
     12          id = p_id;
     13
     14      dbms_output.put_line(v_status);
     15
     16  END;
     17  /


    PROCEDURE created.


    SQL>
    SQL> EXEC prc_test(1234);
    NOT found


    PL/SQL PROCEDURE successfully completed.


    SQL>
     

    It helps to test code before you post it so errors won't confuse those you are trying to help.
     
  6. jknath

    jknath Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Thank u for all who have helped me.........
     
  7. Amitysoft14

    Amitysoft14 Active Member

    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    chennai
    HI Friend!

    Here i fully read your posted informations about these topic. I'm also like to share some information about this forum topic;

    This is how I'd do it. I assume you're using a newer version of Excel. If not, holler back.
    =IFERROR(IF(VLOOKUP(B2,$A:$A,1,FALSE)=B2,"x"),"")

    "Look for B2 in column A.
    If you find it, mark the cell next to it with an 'x'.
    If you get an error, I'll assume you couldn't find it, so don't do anything then."
    permalink

    [–]Fiferondebut 1 point 1 year ago
    Do you need the if statement here? Doesn't the iferror suffice?
    permalinkparent

    [–]4look4rd 1 point 1 year ago
    =IFERROR(IF(VLOOKUP(B2,$A:$A,1,FALSE)=B2,"x"),"")
    Sorry for the necro, but I just wanted to thank you for this wonderful formula!
    permalinkparent

    [–]reallifepixel 1 point 1 year ago
    I'm glad it could be of assistance. =)
    permalinkparent

    Happy to join with you all. Thank you! :)