PLSQL For Syntax

Submitted by code_admin on Fri, 07/20/2018 - 13:19

Loop through array

  1. set serveroutput on;
  2. declare
  3.     TYPE t_queues IS TABLE OF VARCHAR2(255) INDEX BY PLS_INTEGER;
  4.   l_queues t_queues;
  5.   l_c number;
  6. begin
  7.   dbms_output.put_line('Start');
  8.  
  9.     l_queues(1) := 'IC_SOA_PSAO_STUDENT_INone';
  10.     l_queues(2) := 'IC_SOA_PSAO_STUDENT_INtwo';
  11.     l_queues(3) := 'IC_SOA_PSAO_STUDENT_INthree';
  12.     l_queues(4) := 'IC_SOA_PSAO_STUDENT_INfour';
  13.  
  14.     FOR l_c IN 1..l_queues.COUNT LOOP
  15.                 NULL;
  16.             dbms_output.put_line('Test:' || to_char(l_c) || ':' || l_queues(l_c));
  17.      END LOOP;  
  18.  
  19.   dbms_output.put_line('End');
  20. end;  

Loop through Array indexed by varchar2

  1. set serveroutput on;
  2. declare
  3.     TYPE t_namespaces IS TABLE OF varchar2(4096) INDEX BY VARCHAR2(255);
  4.     g_knownNamespaces t_namespaces;
  5.  
  6.     l_key varchar2(255);
  7. begin
  8.   dbms_output.put_line('Start');
  9.  
  10.   g_knownNamespaces('ddf') := 'dsad';
  11.   g_knownNamespaces('swr') := 'rethbg';
  12.  
  13.  
  14.   l_key := g_knownNamespaces.first;
  15.   loop
  16.       exit when l_key is null;
  17.       dbms_output.put_line('xmlns:' || l_key || '="' || g_knownNamespaces(l_key) || '" ');
  18.       l_key := g_knownNamespaces.next(l_key);
  19.   end loop;
  20.  
  21.   dbms_output.put_line('End');
  22. end;

Cursor Loop

  1. SET VERIFY OFF;
  2.  
  3. set serveroutput on;
  4.  
  5. --insert into statement
  6. declare
  7.   cursor cols_c is
  8.     select table_name, owner, column_name, data_type
  9.     from all_tab_columns
  10.     where table_name = 'RA_INT_LINES_ALL_CP'
  11.     ;
  12.   l_rec cols_c%rowtype;
  13. begin
  14.   dbms_output.put_line('Start');  
  15.  
  16.   for l_rec in cols_c loop
  17.     dbms_output.put_line(l_rec.column_name);
  18.   end loop;
  19.  
  20.   dbms_output.put_line('End');  
  21.  
  22. end;

Cursor loop with bulk collect

  1. procedure StopSOA(p_stop in boolean)
  2. is
  3.   cursor c_queues is
  4.     select
  5.     uq.name,
  6.     uq.dequeue_enabled
  7.     from user_queues uq
  8.     where uq.queue_table='IC_SOA_JMS_QTAB'
  9.     and name not like 'AQ$%';
  10.   TYPE t_queues IS TABLE OF c_queues%ROWTYPE INDEX BY PLS_INTEGER;  
  11.   l_queues t_queues;
  12.  
  13.   l_cur number;
  14. begin
  15.   open c_queues;
  16.   fetch c_queues bulk collect into l_queues;
  17.   close c_queues;
  18.  
  19.  
  20.   l_cur := l_queues.FIRST;
  21.   while (l_cur is not null) LOOP
  22.         dbms_output.put_line(l_queues(l_cur).name);
  23.         l_cur := l_queues.NEXT(l_cur);
  24.   END LOOP; --Loop through messages
  25.  
  26. end;

Tags

RJM Article Type
Quick Reference