New answers tagged plsql
No - there is no a short-cut syntax to initialize an associative array. Unlike varrays and nested tables associative arrays do not have collection constructors. Instead you should write your own initializer function. See an example from Declaring Associative Array Constants.
If you need just to check existence I'd go with SELECT 1 FROM DUAL WHERE EXISTS (SELECT NULL FROM [your query]) ... For "top-n" queries there are quite a few alternatives - https://oracle-base.com/articles/misc/top-n-queries . Note, some of them may have side effects ; for instance, where rownum < :value can change optimizer mode to FIRST_ROWS.
select count(*) from ... where (...) and rownum<=3 return 3 if there are 3 or more rows and the number of rows if there are 2 or less rows. I think this statement tells Oracle exactly what you want. Maybe the use of a cursor is possble, too .If you use a cursor and want fetch the first three rows and the close the cursor, then you should give oracle a ...
One ad hoc improvement: poor: for root in all_fitting_roots LOOP FITTING := FITTING + 1; END LOOP; better: SELECT COUNT(DISTINCT ROOT_ID) into FITTING FROM ( SELECT UNIQUE_ID, ROOT_ID, FIRST_NAME, LAST_NAME, POSTAL_CODE, FSA FROM (SELECT p.*, CONNECT_BY_ROOT UNIQUE_ID AS ROOT_ID FROM UNIQUE_PHYSICIAN p START WITH PARENT_PHYS_ID IS ...
Top 50 recent answers are included