Oracle PL/SQL by Example- P9

. Use of also lets you define a of records (such as array whose element type is a record, as shown in the that to reference each row of the array, you use the counter variable, just like in all because each row of this table is a record, you must also reference Use of of using an array, use a nested the preceding is declared as a nested of using a record, use a TYPE IS name_type IS TABLE OF := the portion of the script, the cursor SELECT statement has been modified so that so that only the first four records of the index-by table are displayed on the Create an array with the element type of a of using an array, use a nested of using a nested table, use a varray. . EXECUTE IMMEDIATE are called static because they do not change from consider a different type of PL/SQL in which SQL these SQL to use than the package DBMS_SQL, which Use the EXECUTE IMMEDIATE dynamic SQL are built by your program and are stored as character strings must contain valid SQL the following dynamic SQL SELECT statement returns a student’s first and last name for a given student process dynamic SQL you use EXECUTE IMMEDIATE or OPEN-FOR, IMMEDIATE is used for SELECT all To improve the of dynamic SQL you can also use BULK EXECUTE BULK FETCH, FORALL, and COLLECT INTO EXECUTE IMMEDIATE EXECUTE IMMEDIATE statement parses a dynamic statement or a PL/SQL block for is a string that contains a valid SQL statement or a PL/SQL clause is used when a dynamic SQL statement returns a single to a static SELECT INTO whose values are passed to the dynamic SQL statement or PL/SQL block. of bind arguments that store values returned by the dynamic SQL statement or PL/SQL an EXECUTE IMMEDIATE statement contains both USING and RETURNING INTO clauses, the v_zip := NUMBER := 151; -- Create table := 'CREATE TABLE '|| 'AS SELECT * FROM student WHERE zip = Select total number of records from added: Update record in := 'UPDATE SET zip = 11105 WHERE = EXECUTE IMMEDIATE sql_stmt USING RETURNING ('New zip code: script contains several examples of dynamic you create the table and populate it with records for a specified value of It is important to note that the variable v_zip is with the CREATE you select the total number of students added to the table and use the INTO option with the EXECUTE IMMEDIATE statement SELECT statement returns a single you create a simple PL/SQL block in which you select the current date and display it the PL/SQL block does not contain any bind the you update the table for a given student ID and return a new value of zip code using the RETURNING ID to the UPDATE statement at runtime, and the RETURNING INTO option allows you to pass a new value of zip code from the UPDATE statement into your IMMEDIATE := IMMEDIATE 'DROP TABLE Create table := 'CREATE TABLE IMMEDIATE sql_stmt USING v_zip; -- Select total number of records from added: '|| you drop the table created in the previous version of the re-create the table, but in this case, you use a bind argument to pass a value of zip code to the CREATE statement at CREATE TABLE statement is a data this version, you pass the table name as a bind argument to the SELECT := Create table := 'CREATE TABLE '|| 'AS SELECT * FROM student '|| 'WHERE zip ='|| v_zip; -- Select total number of records from IMMEDIATE 'SELECT COUNT(*) FROM added: '|| example causes an error because you cannot pass names of schema objects to dynamic with the SELECT IMMEDIATE 'SELECT COUNT(*) FROM mentioned earlier, a dynamic SQL string can contain any SQL statement or PL/SQL unlike static SQL a dynamic SQL statement should not be a different version of the same example in which the SELECT statement is you created the table based on the corrected version of the preceding script, IMMEDIATE := Create table := 'CREATE TABLE '|| 'AS SELECT * FROM student '|| 'WHERE zip = Select total number of records from added: '|| semicolon added to the SELECT statement is treated as an invalid character when the state- A somewhat similar error is generated when a PL/SQL block is In some cases you may need to pass a NULL value to a dynamic SQL statement as a value for a SQL and the EXECUTE IMMEDIATE Statement the SQL types. To pass a NULL value to the dynamic SQL you should modify IMMEDIATE the variable v_null has not been its Use the EXECUTE IMMEDIATE the following PL/SQL The portion of the script declares the string that contains the dynamic and three variables to hold student’s ID, first name, and last name, portion of the script contains a dynamic SQL statement with one bind argument that is used to pass the value of the student ID to the SELECT statement at contain results returned by the SELECT which is used to pass a value to the SELECT statement at are used to display the results of the SELECT 3: NUMBER := 105; B) Modify the script so that the student’s address (street, city, state, and zip code) is displayed on the v_zip ('Zip Code: you modify the dynamic SQL statement so that it can return the student’s to display the student’s address on the 3: NUMBER := 105; so that v_zip and v_state were misplaced while the SELECT statement variables v_state and v_zip are ('Zip Code: 3: NUMBER := PL/SQL: numeric or value error is generated because the variable v_state can hold up to two Modify the script created in the previous exercise so that the SELECT statement can table name used in the SELECT statement at := FROM a, zipcode b' || ' AND = ('Zip Code: portion of the script contains a new which holds the In addition, the variable has been replaced by the variable v_id because it is not known in advance which table, STUDENT or The portion of the script contains a modified dynamic SQL does not contain any specific to the STUDENT or words, the dynamic SQL statement used by the previous version FROM a, zipcode b' || ' AND = :1'; The table name (student) has been replaced by the variable in the FROM that are not based on a specific table, because you do not always know in value for 3: := 3: := 3: := 3: := Use OPEN-FOR, FETCH, and CLOSE OPEN-FOR, FETCH, and CLOSE are used for multirow queries or the case of dynamic SQL, the OPEN-FOR statement has an optional USING clause that FOR last_name FROM student '|| 'WHERE zip = :1' USING v_zip; In this code fragment, you define a weak cursor type, cursor variable based on the REF CURSOR type specified in the runtime, the variable is with the SELECT statement the first and last names of students for a given value of zip. As mentioned earlier, the FETCH statement returns a single row from the result set into a list defined in a PL/SQL block and moves the cursor to the next is as the previous example, you fetch the student’s first and last names into variables to process using the EXIT WHEN last_name FROM student '|| 'WHERE zip = :1' USING CLOSE statement the cursor variable with the multirow statement is as last_name FROM student '|| 'WHERE zip = :1' USING 5: v_zip := Use OPEN-FOR, FETCH, and CLOSE the following PL/SQL := 'SELECT zip, COUNT(*) ('Zip code: the use of spaces in the SQL generated that holds the dynamic SQL statement consists of three strings, where each string := 'SELECT zip, COUNT(*) no space is added after the STUDENT table, the resulting SELECT a string variable to hold a dynamic SQL and two to hold data returned by the the portion of the script, you generate a dynamic SQL associate it values of zip code and total number of students into the variables v_zip and Modify the script you just created so that the SELECT statement can be run the STUDENT or table. in the SELECT statement at := ('Totals from := 'SELECT zip, COUNT(*) FROM ('Zip code: this version of the script, you add a to hold the name of a table You also add a table to display a message you modify the dynamic SQL statement := 'SELECT zip, COUNT(*) FROM '|| The variable has been inserted in place of the actual table name that you a space to the variable so that the SELECT first run is based on the STUDENT value for 5: := 5: := from student table old 5: := 5: := far you have seen that values returned by the dynamic SQL are stored in of the columns returned by the SELECT when a dynamic SQL statement returns more than a few PL/SQL allows you to store values returned by the dynamic SELECT in the := ('Totals from := 'SELECT zip, COUNT(*) FROM '|| Enter value for 10: := 10: := from student table old 10: := 10: := material on native dynamic SQL and puts it to use within the context of stored The FORALL to the SQL engine, which returns results to the PL/SQL affects four or more rows, bulk SQL may improve SQL supports batch of SQL and their consists of two FORALL statement and the BULK FORALL Use the FORALL INSERT statement is sent from PL/SQL to SQL ten times. If you replace the FOR loop with the FORALL the INSERT FORALL statement has the following structure (the reserved words in brackets [SAVE FORALL statement has an defined loop counter variable with it. of a which is either a nested table or an array. . When the by the VALUES OF clause is empty, the FORALL is either a static or dynamic INSERT, UPDATE, or DELETE FORALL statement to continue even when causes an the following example that how the FORALL statement may be as well as other examples in this chapter, uses a test table created for TABLE test (row_num NUMBER, row_text IS TABLE OF NUMBER INDEX BY IS TABLE OF INDEX BY are rows in the TEST mentioned earlier, when SQL are used with FORALL they in this script, you define two types as arrays this example, you compare the execution times of the INSERT IS TABLE OF NUMBER INDEX BY IS TABLE OF INDEX BY FORALL of the FORALL '|| To calculate execution times of the FOR loop and the FORALL you employ the As mentioned the SAVE option enables the FORALL statement even when the SQL statement causes an stored in the cursor attribute called cursor attribute is a of records in which each record consists of of the FORALL statement during which an exception was and the The number of that occurred during the execution of the FORALL statement can be Next, consider an example of the FORALL statement with the SAVE IS TABLE OF NUMBER INDEX BY IS TABLE OF INDEX BY Modify 1, 5, and 7 elements of the These rows will cause exception in the FORALL FORALL caused error '||i|| ': in this case, is the INSERT statement that uses COUNT method on the such as which record number caused an exception and the error display the number of the record that caused an IS TABLE OF NUMBER INDEX BY IS TABLE OF INDEX BY are rows in the TEST FORALL statement iterates seven times, and seven rows are added to the TEST FORALL VALUES OF option specifies that the values of the loop counter in the FORALL group of indexes that the FORALL statement can loop TABLE TEST_EXC (row_num NUMBER, row_text IS TABLE OF NUMBER INDEX BY IS TABLE OF INDEX BY Modify 1, 5, and 7 elements of the These rows will cause exception in the FORALL Populate to be used in the := Insert records that caused in the newly created table is used to store records that when they are inserted into the TEST you modify the first, fifth, and seventh elements of the table to in the FORALL with index values of rows that caused the this script is executed, the TEST and TEST_EXC tables contain the following FORALL Use the FORALL this table using the FORALL for the FORALL such as SAVE INDICES OF, and VALUES the table as statement creates an empty table because the criterion specified in the WHERE the following PL/SQL IS TABLE OF INDEX BY := := := := := := Check how many records were added to records were added to This script populates the table with records selected from the enable use of the FORALL it employs seven script uses cursor FOR loop to and then uses them with the FORALL statement to populate it checks how many records were added to the table 19 records were added to table B) Modify the previous version of the script as follows: Select data from the ZIPCODE table for a Modify the selected records so that they will cause various the FORALL the FORALL statement so that it does not fail when an IS TABLE OF INDEX BY := := := := := := i in SAVE Check how many records were added to records were added to caused error '||i|| ': this script, you declare a exception and associate an Oracle error number with it loop against the ZIPCODE table, and then you modify them so that they cause in example, the first record of the zip_tab is set to a violation because the ZIP column in the table has a NOT you add the SAVE clause to the FORALL an section to the PL/SQL in the statement is added so that records that by the FORALL statement are committed when control of the execution is C) Modify the previous version of the script as follows: Do not modify records selected from table so that no are IS TABLE OF INDEX BY := := := := := := i IN INDICES OF zip_tab SAVE Check how many records were added to records were added to caused error '||i|| ': records were added to table D) Modify the second version of the script, as follows: Insert records that cause The table may be created as in the FORALL can be inserted into this IS TABLE OF INDEX BY := := := := := := i in SAVE Check how many records were added to records were added to Populate to be used in the := Insert records that caused in the this version of the script, you modify the section so that records in the FORALL statement are inserted into the table created you populate the with of records that caused the FORALL the script is executed, the table Use PL/SQL Anonymous Use the 2.1 PL/SQL Make Use of PL/SQL Language Make Use of PL/SQL Make Use of in 3.1 Making Use of DML in Use DML in a PL/SQL Make Use of a Sequence in a PL/SQL Make Use of COMMIT, ROLLBACK, and SAVEPOINT in a PL/SQL Block LAB 4.1 IF Use the IF-THEN Use the 4.2 ELSIF Use the ELSIF 4.3 Nested IF Use Nested IF 5.1 CASE Use the CASE Use the Searched CASE 7.1 The CONTINUE Use the CONTINUE Make Use of Record Use a Cursor FOR Use Row and Statement 15.1 PL/SQL Use Nested 16.1 Record Use and 16.3 of Use of 17.1 EXECUTE IMMEDIATE Use the EXECUTE IMMEDIATE 17.2 OPEN-FOR, FETCH, and CLOSE Use OPEN-FOR, FETCH, and CLOSE 18.1 The FORALL Use the FORALL Use the BULK COLLECT Invoke Functions in SQL 21.2 Cursor Make Use of Cursor Use Object Types with 24.1 Making Use of Oracle Supplied Packages to Profile PL/SQL, Access Files, and Schedule Jobs