Oracle Magazine, Nov/Dec 2017
Database Developer PL SQL 12 16 Use EXECUTE IMMEDIATE to dynamically parse if necessary and execute the query Note my use of q to escape single quotes within my string to keep the string itself more readable Because I might be fetching more than one row I use BULK COLLECT INTO to fill up my array 18 21 Iterate through all the elements in the array and take the appropriate action ORACLE MAGAZINE NOVEMBER DECEMBER 2017 66 in this case simply display the name OPEN FOR The OPEN FOR syntax takes advantage of the cursor variable feature of PL SQL You do not in fact have to use OPEN FOR with dynamic SQL as in the following FUNCTION names_ for department_ id_ in IN INTEGER min_ salary_ in IN NUMBER RETURN SYS_ REFCURSOR IS l_ cursor SYS_ REFCURSOR BEGIN IF department_ id_ in IS NOT NULL THEN OPEN l_ cursor FOR SELECT last_ name FROM hr employees WHERE department_ id department_ id_ in ELSE OPEN l_ cursor FOR SELECT last_ name
You must have JavaScript enabled to view digital editions.