CODEWITHSUNDEEP
oracle-databaseoracle10goracle11goracle9i
user1804985
user1804985

Reputation: 297

Select Statement in oracle procedure

I can't able to create a select statement in oracle procedure. Please help me to create this. Now I create the insert,update.delete statement in a procedure but i can't create a select statement. Please help me to create the select statement using cursor.

c_dbuser OUT SYS_REFCURSOR

ELSIF (TYPE_ =1) THEN

  OPEN c_dbuser FOR
  SELECT * FROM tbl_discount_master ;
  CLOSE c_dbuser;

END IF;

call procedure_name(xx,xx,xx,1);

how can i get the selected value using call procedure statement.

Upvotes: 0

Views: 2216

Answers (2)

kmkaplan
kmkaplan

Reputation: 18960

In addition to the other suggestion, you have this solution when you are getting exactly one row.

DECLARE
    myvar1 mytable.mycolumn1%TYPE;
    myvar2 mytable.mycolumn2%TYPE;
BEGIN
    SELECT mycolumn1, mycolumn2
      INTO myvar1, myvar2
      FROM mytable
     WHERE …;
END;

This will throw an exception if there is no selected row (NO_DATA_FOUND) or if there is more than one row (TOO_MANY_ROWS).

Upvotes: 1

David Aldridge
David Aldridge

Reputation: 52386

The difference between select and the insert/update/delete is that you need to select into some structure, either one or more variables or a rowtype variable.

Avoid explicit cursors whenever possible in favour of the faster, less verbose and less error prone implicit cursor. eg.

for cur_my_query in
  select column1,
         column2,
         ...
  from   ...
  where  ...
loop
  refer here to cur_my_query or my_query.column1 etc
end loop

Upvotes: 0

Related Questions