[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 948
  • Last Modified:

How to pass variables in cursor

 function    X  is
   tempvar Integer (8);
   tempvar1 Integer (8);
   tempvar2 Integer (8);


  begin
 
   SELECT id into tempvar FROM DEPT DEP WHERE DEP.STATUS='OPEN'
   and DEP.DEP_NO='DEPT-1126';
   
   dbms_output.put_line('tempvar' || tempvar);
   
 //from this cursor one record will be fetched.do i need use cursor here?
 CURSOR C1 IS
       SELECT EMP.REF_ID_1,EMP.REF_ID_3 FROM EMPlOYEE EMP WHERE
            EMP.REF_ID_4=tempvar;

//How to pass REF_ID_1 and REF_ID_3 variables to the below two tables.Is it right way of passing variables to the below tables;should i use cursor here?
where should i define cursor.should i define cursor before begin statement?

 
 FOR R2 IN C1
            LOOP

SELECT ID INTO tempvar1 FROM DOCUMENT X WHERE X.ID=R2.REF_ID_1;  
SELECT ID INTO tempvar2 FROM TEMP_ORDER JO WHERE JO.ID=R2.REF_ID_3;  
         
end loop;
  end;


 
end    X;
0
chaitu chaitu
Asked:
chaitu chaitu
  • 5
  • 3
1 Solution
 
Jinesh KamdarCommented:
>> from this cursor one record will be fetched.do i need use cursor here?
No, u need not use a CURSOR for a SELECT returning just 1 record and hence, even the LOOP is not reqd.
FUNCTION x IS
 
tempvar    dept.id%TYPE;
tempvar1   document.id%TYPE;
tempvar2   temp_order.id%TYPE;
v_ref_id_1 emp.ref_id_1%TYPE;
v_ref_id_3 emp.ref_id_3%TYPE;
 
BEGIN  
 
SELECT  id
  INTO  tempvar
  FROM  dept
  WHERE status = 'OPEN'
    AND dep_no = 'DEPT-1126';
 
DBMS_OUTPUT.PUT_LINE('tempvar : ' || tempvar);
   
SELECT  ref_id_1  , ref_id_3
  INTO  v_ref_id_1, v_ref_id_3
  FROM  employee
  WHERE ref_id_4 = tempvar;
 
SELECT  id
  INTO  tempvar1
  FROM  document
  WHERE id = v_ref_id_1;
 
SELECT  id
  INTO  tempvar2
  FROM  temp_order
  WHERE id = v_ref_id_3;
 
EXCEPTION
 
WHEN OTHERS THEN
     ROLLBACK;
     DBMS_OUTPUT.PUT_LINE(SQLERRM);
 
END x;

Open in new window

0
 
Jinesh KamdarCommented:
Noe that ur FUNCTION must return some value. If u don't wanna return anything, use a procedure instead.
PROCEDURE x IS
 
tempvar    dept.id%TYPE;
tempvar1   document.id%TYPE;
tempvar2   temp_order.id%TYPE;
v_ref_id_1 emp.ref_id_1%TYPE;
v_ref_id_3 emp.ref_id_3%TYPE;
 
BEGIN  
 
SELECT  id
  INTO  tempvar
  FROM  dept
  WHERE status = 'OPEN'
    AND dep_no = 'DEPT-1126';
 
DBMS_OUTPUT.PUT_LINE('tempvar : ' || tempvar);
   
SELECT  ref_id_1  , ref_id_3
  INTO  v_ref_id_1, v_ref_id_3
  FROM  employee
  WHERE ref_id_4 = tempvar;
 
SELECT  id
  INTO  tempvar1
  FROM  document
  WHERE id = v_ref_id_1;
 
SELECT  id
  INTO  tempvar2
  FROM  temp_order
  WHERE id = v_ref_id_3;
 
-- Remaining code to process the SELECTed data.
 
EXCEPTION
 
WHEN OTHERS THEN
     ROLLBACK;
     DBMS_OUTPUT.PUT_LINE(SQLERRM);
 
END x;

Open in new window

0
 
chaitu chaituAuthor Commented:
PROCEDURE x IS
 
tempvar    dept.id%TYPE;
tempvar1   document.id%TYPE;
tempvar2   temp_order.id%TYPE;
v_ref_id_1 emp.ref_id_1%TYPE;
v_ref_id_3 emp.ref_id_3%TYPE;
 
BEGIN  
 
SELECT  id
  INTO  tempvar
  FROM  dept
  WHERE status = 'OPEN'
    AND dep_no = 'DEPT-1126';
 
DBMS_OUTPUT.PUT_LINE('tempvar : ' || tempvar);
   
SELECT  ref_id_1  , ref_id_3
  INTO  v_ref_id_1, v_ref_id_3
  FROM  employee
  WHERE ref_id_4 = tempvar;

 
CURSOR C1 IS
SELECT  id,temp_id
  FROM  document
  WHERE id = v_ref_id_1;


 FOR R1 IN c1
            LOOP
  DELETE
  FROM  temp_order
  WHERE id = R1.temp_id;
  END LOOP;
 

 
EXCEPTION
 
WHEN OTHERS THEN
     ROLLBACK;
     DBMS_OUTPUT.PUT_LINE(SQLERRM);
 
END x;


thanks for the explanation;i have small doubt here;i have changed little bit what you have written.

lets say  i got multiple items from the document table then i think we have to convert into cursor.then i want to loop each id and delete temp_order table;when i write like this i am getting below exception;can i initialize cusor inside begin statemnt?

Compilation errors for PACKAGE BODY    .x

Error: PLS-00103: Encountered the symbol "C1" when expecting one of the following:
       
          := . ( @ % ;


0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Jinesh KamdarCommented:
>> can i initialize cusor inside begin statemnt?

No, you can't, CURSOR declarations always go in the DECLARE section. You're right that while dealing with SELECT statements that return multiple rows in PL/SQL, we have to use CURSOR.
However, for ur case, since the operations are pretty straight-forward (since ur SELECTing from the document table just to be able to DELETE the reqd. records from the temp_order table, correct me if u think thats not the case), u can do away with them. Try below.
PROCEDURE x IS
 
tempvar    dept.id%TYPE;
tempvar1   document.id%TYPE;
tempvar2   temp_order.id%TYPE;
v_ref_id_1 emp.ref_id_1%TYPE;
v_ref_id_3 emp.ref_id_3%TYPE;
 
BEGIN  
 
SELECT  id
  INTO  tempvar
  FROM  dept
  WHERE status = 'OPEN'
    AND dep_no = 'DEPT-1126';
 
DBMS_OUTPUT.PUT_LINE('tempvar : ' || tempvar);
   
SELECT  ref_id_1  , ref_id_3
  INTO  v_ref_id_1, v_ref_id_3
  FROM  employee
  WHERE ref_id_4 = tempvar;
 
DELETE FROM temp_order
  WHERE id IN
    (SELECT  temp_id
       FROM  document
       WHERE id = v_ref_id_1);
 
SELECT  id
  INTO  tempvar2
  FROM  temp_order
  WHERE id = v_ref_id_3;
 
-- Remaining code to process the SELECTed data.
 
EXCEPTION
 
WHEN OTHERS THEN
     ROLLBACK;
     DBMS_OUTPUT.PUT_LINE(SQLERRM);
 
END x;

Open in new window

0
 
chaitu chaituAuthor Commented:
//in other scenario i must use cursor only ;for tht purpose
 I am passing variable to the cursor and cursor is returning multiple values but its not going into the loop;

PROCEDURE x IS
 
tempvar    dept.id%TYPE;
tempvar1   document.id%TYPE;
tempvar2   temp_order.id%TYPE;
v_ref_id_1 emp.ref_id_1%TYPE;
v_ref_id_3 emp.ref_id_3%TYPE;
 
cursor c1 is(tempvar integer)
SELECT  ref_id_1  , ref_id_3
  FROM  employee
  WHERE ref_id_4 = tempvar;
 
 
BEGIN  
 
SELECT  id
  INTO  tempvar
  FROM  dept
  WHERE status = 'OPEN'
    AND dep_no = 'DEPT-1126';
 
DBMS_OUTPUT.PUT_LINE('tempvar : ' || tempvar);
   
 
 //in other scenario i must use cursor only ;for tht purpose
 I am passing variable to the cursor and cursor is returning multiple values but its not going into the loop;
 its not printing '..in the loop  ...........'
 
for r1 in c1(tempvar)
 loop
 
 dbms_output.put_line('..in the loop  ...........');
DELETE FROM temp_order
  WHERE id = R1.ref_id_1;
 
 end loop;
 
SELECT  id
  INTO  tempvar2
  FROM  temp_order
  WHERE id = v_ref_id_3;
 
-- Remaining code to process the SELECTed data.
 
EXCEPTION
 
WHEN OTHERS THEN
     ROLLBACK;
     DBMS_OUTPUT.PUT_LINE(SQLERRM);
 
END x;
 
Open in New Window 

Open in new window

0
 
Jinesh KamdarCommented:
Have you SET SERVEROUTPUT ON ? I have also added a COMMIT at the end of the BEGIN block.
PROCEDURE x IS
 
tempvar    dept.id%TYPE;
tempvar1   document.id%TYPE;
tempvar2   temp_order.id%TYPE;
v_ref_id_1 emp.ref_id_1%TYPE;
v_ref_id_3 emp.ref_id_3%TYPE;
 
CURSOR c1 (tempvar INTEGER) IS
  SELECT  ref_id_1, ref_id_3
    FROM  employee
    WHERE ref_id_4 = tempvar;
 
BEGIN  
 
SELECT  id
  INTO  tempvar
  FROM  dept
  WHERE status = 'OPEN'
    AND dep_no = 'DEPT-1126';
 
DBMS_OUTPUT.PUT_LINE('tempvar : ' || tempvar);
 
FOR r1 IN c1(tempvar) LOOP
 
DBMS_OUTPUT.PUT_LINE('..in the loop  ...........');
 
DELETE FROM temp_order
  WHERE id = r1.ref_id_1;
 
END LOOP;
 
SELECT  id
  INTO  tempvar2
  FROM  temp_order
  WHERE id = v_ref_id_3;
 
-- Remaining code to process the SELECTed data.
 
COMMIT;
 
EXCEPTION
 
WHEN OTHERS THEN
     ROLLBACK;
     DBMS_OUTPUT.PUT_LINE(SQLERRM);
 
END x;

Open in new window

0
 
chaitu chaituAuthor Commented:
i made a simple mistake;its working fine now.
0
 
Jinesh KamdarCommented:
Glad to be of help :)
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now