CODEWITHSUNDEEP
oracle-database
Santosh Kumar Bind
Santosh Kumar Bind

Reputation: 63

How can Avoid Oracle Ref Cursor

i have a develop a application that contain large no of rows i was using ref cursor but i do not need a cursor. how can achieve that.

here is below code i was using ref cursor please help me out how can avoid it.any idea

create or replace
PROCEDURE remove_emp 
(
employee_id in NUMBER,
o_data out sys_refcursor
) 
AS
        v_account_status        help.topic%type;
        v_username             help.seq%type;
        v_lock_date             help.info%type;        
BEGIN
         open o_data for
         select topic,seq, info
         into  v_account_status,v_username,v_lock_date
         from help;-- where seq=employee_id ;
         DBMS_OUTPUT.put_line ('topic:'||v_account_status);
         DBMS_OUTPUT.put_line ('seq:'||v_username);
         DBMS_OUTPUT.put_line ('info:'||v_lock_date);
   END remove_emp;

Upvotes: 0

Views: 63

Answers (1)

MT0
MT0

Reputation: 168051

Just remove the cursor and just use SELECT ... INTO ...:

Oracle Setup:

CREATE TABLE help ( topic, seq, info ) AS
  SELECT 'a', 1, 'aa' FROM DUAL UNION ALL
  SELECT 'b', 2, 'bb' FROM DUAL UNION ALL
  SELECT 'c', 3, 'cc' FROM DUAL;

Procedure:

create PROCEDURE remove_emp 
(
  employee_id in help.seq%type
) 
AS
  v_account_status help.topic%type;
  v_username       help.seq%type;
  v_lock_date      help.info%type;        
BEGIN
  select topic,seq, info
  into  v_account_status,v_username,v_lock_date
  from help where seq=employee_id;

  DBMS_OUTPUT.put_line ('topic:'||v_account_status);
  DBMS_OUTPUT.put_line ('seq:'||v_username);
  DBMS_OUTPUT.put_line ('info:'||v_lock_date);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.put_line ('seq:Not Found!');
END remove_emp;
/

Call the procedure:

BEGIN
  remove_emp(1);
END;
/

Output:

topic:a
seq:1
info:aa

db<>fiddle here

Upvotes: 1

Related Questions