Oracle Built−in Packages- P16: Ah, for the good old days of Version of PL /SQL! Life was so simple then. No stored procedures or functions and certainly no packages. You had your set of built−in functions, like SUBSTR and TO_DATE. You had the IF statement and various kinds of loops. With these tools at hand, you built your batch−processing scripts for execution in SQL*Plus, and you coded your triggers in SQL*Forms , and you went home at night content with a good day's work done. | Appendix A What s on the Companion Disk Executing the Cursor So you ve opened and parsed the cursor. You ve bound your variables and defined your columns. Now it s time to get some work done. The function The EXECUTE function executes the SQL statement associated with the specified cursor FUNCTION c IN INTEGER RETURN INTEGER where c is the pointer to the cursor. This function returns the number of rows processed by the SQL statement if that statement is an UPDATE INSERT or DELETE. For all other SQL queries and DDL and PL SQL statements the value returned by EXECUTE is undefined and should be ignored. If the SQL statement is a query you can now call the FETCH_ROWS function to fetch rows that are retrieved by that query. If you are executing a query you can also use EXECUTE_AND_FETCH to execute the cursor and fetch one or more rows with a single program call. Fetching Rows You can fetch one or more rows of data from a dynamically constructed query with either the FETCH_ROWS or EXECUTE_AND_FETCH functions. NOTE Prior to PL SQL8 both of these functions would return either 0 no rows fetched or 1 one row fetched . With PL SQL8 and array processing these functions will return 0 no rows fetched or the actual number of rows fetched. The function The FETCH_ROWS function corresponds to the FETCH statement for regular PL SQL cursors. It fetches the next N rows from the cursor a maximum of one if not using array processing in PL SQL8 . Here s the specification for the function FUNCTION c IN INTEGER RETURN INTEGER where c is the pointer to the cursor. The function returns 0 when there are no more rows to fetch. You can therefore use FETCH_ROWS much as you would FETCH and the FOUND or NOTFOUND attributes. The following two sets of statements are equivalent Use a normal static cursor FETCH emp_cur INTO emp_rec IF emp_cur FOUND THEN . process data . END IF Use DBMS_SQL to fetch IF
