Teach Yourself PL/SQL in 21 Days- P11: Welcome to Sams Teach Yourself PL/SQL in 21 Days, Second Edition. The fact that you purchased this book indicates that you have an interest in learning the PL/SQL language, which is Oracle’s relational database procedural programming language. It allows you to develop powerful and complex programs to access and manipulate data in the Oracle8i database. We have attempted to include as many examples of PL/SQL code as possible to illustrate PL/SQL features | Generating Dynamic SQL 477 Syntax Of those three steps only the first step represents a difference from what you would do to process a static SELECT statement by using PL SQL. The Syntax for the Statement OPEN cursor FOR string USING bind bind . In this syntax the parameters are as follows cursor is the cursor that you want to open. This is actually a pointer to a cursor and must be a REF CURSOR variable. string is a variable or literal that contains the SQL statement you want to execute. bind is a bind variable. You use these to pass parameters to the dynamic SQL statement. The parameter markers are numbered and must be 1 2 and so on. The first bind variable becomes the value 1 the second bind variable becomes the value 2 and so forth. The cursor that you declare and use with the statements must be a REF CURSOR. You can declare a REF CURSOR like this TYPE ref_cursor_type IS REF CURSOR your_cursor ref_cursor_type Listing shows being used to open a cursor on a dynamic SQL statement. The rows returned by that statement are then fetched into a PL SQL record and from there they are displayed by using DBMS_OUTPUT. Input 16 Listing Executing a SELECT by Using Native Dynamic SQL 1 DECLARE 2 TYPE your_cursor_type IS REF CURSOR 3 your_cursor your_cursor_type 4 5 -- Declare a record type for the output 6 TYPE dyn_record IS RECORD 7 yourrow TYPE 8 yourdesc TYPE 9 10 11 -- Note could also use 12 -- dyn_rec yourtable ROWTYPE 13 -- 14 dyn_rec dyn_record 15 continues 478 Day 16 Listing continued 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 dynamic_select_stmt VARCHAR2 100 BEGIN -- Generate the dynamic SELECT statement. dynamic_select_stmt SELECT yourrow yourdesc FROM yourtable 1 dynamic_select_stmt dynamic_select_stmt j ORDER BY yourrow DESC1 -- Open a cursor on the dynamic statement. OPEN your_cursor FOR dynamic_select_stmt -- Loop through and display all the
