CODEWITHSUNDEEP
stored-proceduresplsqloracle-sqldeveloper
user955485
user955485

Reputation: 53

PL/SQL:, How to pass a variable into a SELECT statement and return all rows of results

I am using an oracle database. I am used to SQL server but not familiar with PL/SQL for the Oracle database. How do I Set a variable that returns all the rows that contain the value of that variable: I am lost, I tried to understand, but it is not making sense to me. This is a recent attempt I made to to this.

DECLARE date1 varchar(40);
Begin
Select '''07/31/2013_09%''' into :date1 from dual;
End;
/
print date1


Begin
Select * from TABLE1 where start_time LIKE date1;
End;
/

I should get all the rows returned from this. Thank you for your help.

Upvotes: 0

Views: 3169

Answers (3)

user272735
user272735

Reputation: 10648

It looks like you're missing the understanding of several basic building blocks:

  • You need a PL/SQL data structure where you'll save the data set queried from database table: PL/SQL Collections and Records. See especially nested tables and record variables.
  • You query the database with PL/SQL Static SQL. See especially SELECT INTO statements. In this case there is no need for dynamic SQL.
  • But of course it is bit harder to get a set of rows out of the database than only one row. Here the keywords are: SELECT INTO Statement with BULK COLLECT Clause. Note that depending on your query and table size bulk collection will potentially exhaust your server's memory (by loading millions of rows).

Here is an example that should give you a kickstart:

create table so26 (
  day date,
  event varchar(10)
);

insert all
into so26 values(trunc(sysdate - 1), 'foo1')
into so26 values(trunc(sysdate - 1), 'foo2')
into so26 values(trunc(sysdate - 1), 'foo3')
into so26 values(trunc(sysdate    ), 'bar')
into so26 values(trunc(sysdate + 1), 'zoo')
select 1 from dual;

select * from so26;

declare
  type event_list_t is table of so26%rowtype;
  v_events event_list_t := event_list_t();

  function get_events(p_day in date default sysdate) return event_list_t as
    v_events event_list_t := event_list_t();
  begin
    select *
      bulk collect into v_events
      from so26
     where day = trunc(p_day);

    return v_events;  
  end;
begin
  v_events := get_events(sysdate + 1);

  if v_events.first is null then
    dbms_output.put_line('no events on that day');
    return;
  end if;

  for i in v_events.first .. v_events.last loop
    dbms_output.put_line(i || ': event = ' || v_events(i).event);
  end loop;
end;
/

Example output when get_events(sysdate - 1):

1: event = foo1
2: event = foo2
3: event = foo3

Upvotes: 0

René Nyffenegger
René Nyffenegger

Reputation: 40499

This might help you get started:

create table table1 (
  start_time varchar2(10),
  foo        number
);

insert into table1 values ('xyz', 1);
insert into table1 values ('wxy', 2);
insert into table1 values ('abc', 3);

create type table1_obj as object (
  start_time varchar2(10),
  foo        number
);
/

create type table1_tab as table of table1_obj;
/


declare
  v table1_tab;
begin

  select table1_obj(start_time, foo) bulk collect into v
   from table1 where start_time like '%x%';
end;
/

Upvotes: 1

Harshit
Harshit

Reputation: 560

You have to create a parametrized cursor and pass that date as parameter to that cursor as below.

   CREATE or REPLACE procedure proc1(p_date date)
   as
        CURSOR C1 (date1 date)is
              SELECT * from TABLE1 where start_time LIKE date1;
  BEGIN
      FOR i in c1(p_dat)
      LOOP
        .......
      END LOOP;
      END;

Upvotes: 0

Related Questions