How to declare a cursor after BEGIN?

Question

I want to know if a cursor can be declared after BEGIN.

And how can I export the result of the plsql to an Excel sheet, because I have to run this procedure as a job.

CREATE OR REPLACE PROCEDURE masc(v_amsprogramid VARCHAR2) AS

  v_mid VARCHAR2(50);
  v_sid VARCHAR2(50);

  CURSOR c1 IS
    SELECT DISTINCT mid
    FROM table_a WHERE aid = v_aid
    ORDER BY mid;

  BEGIN

    OPEN c1;

    LOOP
      FETCH c1 INTO v_mid;
      EXIT WHEN c1%NOTFOUND;

      DECLARE
        CURSOR c2 IS
          SELECT DISTINCT sid INTO v_sid
          FROM table_b WHERE mid = v_mid;

      BEGIN
        OPEN c2;
        LOOP

          FETCH c1 INTO v_mid;
          EXIT WHEN c1%NOTFOUND;

          dbms_output.PUT_LINE('MID : ' || v_mid);
          dbms_output.PUT_LINE('Sid : ' || v_sid);

        END LOOP;

        CLOSE c2;
      END LOOP;

      CLOSE c1;
  END masc;

Solution 1

I want to know if i can declare a cursor after begin

Not exactly. But you could use a cursor for loop instead of declaring an explicit cursor.

For example,

FOR i IN (SELECT distinct MID from table_a WHERE AID = V_AID ORDER BY MID)
LOOP
   <do something>
END LOOP;

But anyway, this would be slower as row-by-row is slow-by-slow. I don't see a need of procedure at all. If you really need to do it in PL/SQL then consider BULK COLLECT.

And how can i export the result of the plsql to an excel sheet because i ahev to run this procedure as a job.

I don't see a need of PL/SQL in that case. You could simply use SPOOL in SQL*Plus.

For example,

sqlplus user/pass@service_name
<required formatting options>

SPOOL /location/myfile.csv
SELECT distinct MID from table_a WHERE AID = V_AID ORDER BY MID;
SPOOL OFF

Solution 2

Maybe you are looking for this:

create or replace PROCEDURE MASC (V_AMSPROGRAMID VARCHAR2) AS

V_MID VARCHAR2(50);
V_SID VARCHAR2(50);

CURSOR C1 IS
SELECT distinct MID from table_a WHERE AID = V_AID
ORDER BY MID;

CURSOR C2 IS
SELECT DISTINCT SID INTO V_SID FROM table_b WHERE MID = V_MID
ORDER BY MID;

BEGIN    
...

or

create or replace PROCEDURE MASC (V_AMSPROGRAMID VARCHAR2) AS

V_MID VARCHAR2(50);
V_SID VARCHAR2(50);

CURSOR C1 IS
SELECT distinct MID from table_a WHERE AID = V_AID
ORDER BY MID;

CURSOR C2(v in NUMBER) IS
SELECT DISTINCT SID INTO V_SID FROM table_b WHERE MID = v
ORDER BY MID;

BEGIN

OPEN C1;
...
OPEN C2(V_MID);
...