CODEWITHSUNDEEP
sqloracle-databaseplsql
Arianit
Arianit

Reputation: 553

Select a value from a specific column and compare it with other value

I want to get only one value (varchar2 data type) from a specific record column of a table and store it as a variable to compare. Depending on the value and condition, I am going to call one of two cursors.

This all will be inside a function. So what's the easiest and shortest way to archive that? And, by the way, can this comparison be done directly without storing the value into a variable? (I am kinda new in oracle world!)

Upvotes: 1

Views: 1730

Answers (3)

APC
APC

Reputation: 146349

"I want to get only one value (varchar2 data type) from a specific record column of a table"

You need to select this value into a variable, even if you are going to use it in the next statement.

"depending of the value and condition, I am gonna call one of two cursors. "

Something like this:

create or replace function get_customers 
     return sys_refcursor
is
    rv  sys_refcursor;
    report_type your_table.some_col%type;
begin
    select some_col into report_type
    from your_table
    where other_col = 'WHATEVER';

    if report_type = 'CORPORATE' then
        open rv for 
            select * from customers 
            where type = 'CORP';
    else
        open rv for 
            select * from customers;
    end if;

    return rv;
end;

Upvotes: 1

Zynon Putney II
Zynon Putney II

Reputation: 695

Since the data is being stored in a table, the comparison can be done directly by joining the table with the data to the cursor and adding to the where clause the specifics. 

declare
  cursor cur_1 as
    select value1
    from table1 a
    join table2 b on a.fk = b.pk
    where b.value = 'desired_value';

  cursor cur_2 as
    select value2
    from table3 c
    join table4 d on c.fk = d.pk
    where d.value = 'desired_value2';

 begin
  for rec in cur_1 loop
    null;
  end loop;

  for rec2 in cur_2 loop
    null;
  end loop;
 end;

You could also use dynamic SQL and EXECUTE IMMEDIATE to build a statement on the fly. Without more specific information about what you're trying to do it's hard to point you in a specific direction.

Upvotes: 1

Lukasz Szozda
Lukasz Szozda

Reputation: 176124

You could use SELECT INTO:

DECLARE
    variable_name VARCHAR2(200);
BEGIN

    SELECT col_name
    INTO variable_name
    FROM your_tab
    WHERE specific_column = value;

-- rest

EXCEPTION
    WHEN NO_DATA_FOUND THEN ...
    WHEN TOO_MANY_ROWS THEN ...

END;

Of course you have to declare your cursor and wrap it with function (here I've used annonymous block).

Upvotes: 1

Related Questions