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;

Answer 1

You can declare multiple cursors in the same pl/sql block. There is no need to declare the second cursor after you've opened the first cursor!

You would write something like:

create or replace procedure masc (p_amsprogramid varchar2)
as
  v_mid varchar2(50);
  v_sid varchar2(50);

  cursor c1 
  is
    select   distinct mid
    from     table_a
    where    aid = p_amsprogramid
    order by mid;

  cursor c2
  is
    select distinct sid
    from   table_b
    where  mid = v_mid;

begin
  open c1;
  loop
    fetch c1 into v_mid;
    exit when c1%notfound;

    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;
/

However, if you were to replace your open-cursor-loop-fetches as a cursor-for-loop, you could simplify things a bit:

create or replace procedure masc (p_amsprogramid varchar2)
as
  cursor c1 
  is
    select   distinct mid
    from     table_a
    where    aid = p_amsprogramid
    order by mid;

  cursor c2
  is
    select distinct sid
    from   table_b
    where  mid = v_mid;

begin
  for rec1 in c1
  loop
    for rec2 in c2
    loop
      dbms_output.put_line('mid : ' || rec1.mid);
      dbms_output.put_line('sid : ' || rec2.sid);
    end loop;
  end loop;
end masc;
/

Looking at that, you've got a nested cursor loop. This screams procedural thinking, rather than set-based thinking, which is pretty much a big no-no when you're working with datasets in the database (ie. it's slow. You're having to constantly switch between the SQL and PL/SQL engines, instead of simply asking the SQL engine to calculate everything before delivering it to the PL/SQL engine).

By doing the nested cursor loop, you're basically reinventing NESTED LOOP joins - something the SQL engine can do far better than you can (not to mention it might not be the most efficient join, and the SQL engine could choose a better way of doing the join!). Any time you see a nested cursor loop, you should IMMEDIATELY stop and look to see if you can combine the queries into a single select statement. (Actually, any time you see a loop you should pause and consider whether you really do need it; sometimes it's necessary, but if you're doing something like selecting a set of results and then going through each row and then doing an update, consider merging the select into the update so that you have a statement that updates all the rows at once. It'll be much faster!)

For example, your original procedure could be rewritten as:

create or replace procedure masc (p_amsprogramid varchar2)
as
  cursor c1 
  is
    select   distinct a.mid,
                      b.sid
    from     table_a a
             inner join table_b b on (a.mid = b.mid)
    where    a.aid = p_amsprogramid
    order by mid;

begin
  for rec1 in c1
  loop
    dbms_output.put_line('mid : ' || rec1.mid);
    dbms_output.put_line('sid : ' || rec1.sid);
  end loop;
end masc;
/

Much simpler to read, understand and maintain, I think you'll agree!

If you're wanting to write the results of the sql query out as a file, you'll need to use UTL_FILE, instead of DBMS_OUTPUT. Bear in mind that the directory the file is written to needs to be something that is mounted/mapped to the server the database sits on. If you write the results as character-delimited, you can then easily import that file into Excel.

You might find this to be of use.

Answer 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);
...

Answer 3

U can use reference cursor for this purpose

            create or replace PROCEDURE MASC (V_AMSPROGRAMID VARCHAR2) AS

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

            C1 sys_refcursor ;
            c2 sys_refcursor ;

            BEGIN

            OPEN C1 for SELECT distinct MID from table_a WHERE AID = V_AID
            ORDER BY MID;

            LOOP

            FETCH C1 INTO V_MID;
            EXIT WHEN C1%NOTFOUND;


            open C2 for SELECT DISTINCT SID INTO V_SID FROM table_b WHERE MID = V_MID;

            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;
            CLOSE C1;
            END LOOP;

Answer 4

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