Oracle PL/SQL by Example- P9

Chia sẻ: Thanh Cong | Ngày: | Loại File: PDF | Số trang:50

0
88
lượt xem
21
download

Oracle PL/SQL by Example- P9

Mô tả tài liệu
  Download Vui lòng tải xuống để xem tài liệu đầy đủ

Tham khảo tài liệu 'oracle pl/sql by example- p9', công nghệ thông tin, cơ sở dữ liệu phục vụ nhu cầu học tập, nghiên cứu và làm việc hiệu quả

Chủ đề:
Lưu

Nội dung Text: Oracle PL/SQL by Example- P9

  1. LAB 16.2 Lab 16.2 Exercises 372 Consider partial output from the preceding example: Zip: 06820 Names(1): Scrittorale Names(2): Padel Names(3): Kiraly -------------------- Zip: 06830 Names(1): Dennis Names(2): Meshaj Names(3): Dalvi -------------------- Zip: 06880 Names(1): Miller Names(2): Cheevens -------------------- Zip: 06903 Names(1): Segall Names(2): Annina -------------------- Zip: 07003 Names(1): Wicelinski Names(2): Intal -------------------- Zip: 07010 Names(1): Lopez Names(2): Mulroy Names(3): Velasco Names(4): Kelly Names(5): Tucker Names(6): Mithane -------------------- ... PL/SQL procedure successfully completed. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  2. LAB 16.3 373 LAB 16.3 Collections of Records LAB OBJECTIVE After completing this lab, you will be able to . Use collections of records In the previous lab you saw an example of a nested record in which one of the record fields was defined as an associative array. PL/SQL also lets you define a collection of records (such as an associative array whose element type is a cursor-based record, as shown in the following example). FOR EXAMPLE DECLARE CURSOR name_cur IS SELECT first_name, last_name FROM student WHERE ROWNUM
  3. LAB 16.3 Lab 16.3 Exercises 374 In the declaration portion of this example, you define the name_cur cursor, which returns the first and last names of the first four students. Next, you define an associative array type whose element type is based on the cursor defined previously using the %ROWTYPE attribute. Then you define an associative array variable and the counter that is used later to reference individ- ual rows of the associative array. In the executable portion of the example, you populate the associative array and display its records on the screen. Consider the notation used in the example when referencing individual elements of the array: name_tab(v_counter).first_name and name_tab(v_counter).last_name Notice that to reference each row of the array, you use the counter variable, just like in all previ- ous examples. However, because each row of this table is a record, you must also reference indi- vidual fields of the underlying record. This example produces the following output: First Name(1): Fred Last Name(1): Crocitto First Name(2): J. Last Name(2): Landry First Name(3): Laetia Last Name(3): Enison First Name(4): Angel Last Name(4): Moskowitz PL/SQL procedure successfully completed. ▼ LAB 16.3 EXERCISES This section provides exercises and suggested answers, with discussion related to how those answers resulted. The most important thing to realize is whether your answer works. You should figure out the implications of the answers and what the effects are of any different answers you may come up with. 16.3.1 Use Collections of Records In this exercise, you learn more about collections of records. Complete the following tasks: A) Modify the script used earlier in this lab. Instead of using an associative array, use a nested table. ANSWER: The script should look similar to the following. Changes are shown in bold. -- ch16_4a.sql, version 1.0 SET SERVEROUTPUT ON DECLARE CURSOR name_cur IS SELECT first_name, last_name Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  4. Lab 16.3 Exercises LAB 16.3 375 FROM student WHERE ROWNUM
  5. LAB 16.3 Lab 16.3 Exercises 376 DBMS_OUTPUT.PUT_LINE('Last Name('||v_counter||'): '|| name_tab(v_counter).last_name); END LOOP; END; In this version of the script, name_tab is declared as a varray with four elements. As in the previ- ous version, the collection is initialized and its size is incremented before it is populated with the new record. Both scripts, ch16_4a.sql and ch16_4b.sql, produce output identical to the original example: First Name(1): Fred Last Name(1): Crocitto First Name(2): J. Last Name(2): Landry First Name(3): Laetia Last Name(3): Enison First Name(4): Angel Last Name(4): Moskowitz PL/SQL procedure successfully completed. C) Modify the script used at the beginning of this lab. Instead of using a cursor-based record, use a user-defined record. The new record should have three fields: first_name, last_name, and enrollments. The last field will contain the total number of courses in which a student is currently enrolled. ANSWER: The script should look similar to the following. Changes are shown in bold. -- ch16_4c.sql, version 3.0 SET SERVEROUTPUT ON DECLARE CURSOR name_cur IS SELECT first_name, last_name, COUNT(*) total FROM student JOIN enrollment USING (student_id) GROUP BY first_name, last_name; TYPE student_rec_type IS RECORD (first_name VARCHAR2(15), last_name VARCHAR2(30), enrollments INTEGER); TYPE name_type IS TABLE OF student_rec_type INDEX BY BINARY_INTEGER; name_tab name_type; v_counter INTEGER := 0; BEGIN FOR name_rec IN name_cur LOOP v_counter := v_counter + 1; name_tab(v_counter).first_name := name_rec.first_name; name_tab(v_counter).last_name := name_rec.last_name; Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  6. Lab 16.3 Exercises LAB 16.3 377 name_tab(v_counter).enrollments := name_rec.total; IF v_counter
  7. 378 Try it Yourself ▼ TRY IT YOURSELF In this chapter, you’ve learned about various types of records, nested records, and collections of records. Here are some projects that will help you test the depth of your understanding: 1) Create an associative array with the element type of a user-defined record. This record should contain the first name, last name, and total number of courses that a particular instructor teaches. Display the records of the associative array on the screen. 2) Modify the script you just created. Instead of using an associative array, use a nested table. 3) Modify the script you just created. Instead of using a nested table, use a varray. 4) Create a user-defined record with four fields: course_no, description, cost, and prerequisite_rec. The last field, prerequisite_rec, should be a user-defined record with three fields: prereq_no, prereq_desc, and prereq_cost. For any ten courses that have a prerequisite course, populate the user-defined record with all the corresponding data, and display its information on the screen. The projects in this section are meant to have you use all the skills you have acquired throughout this chapter. The answers to these projects can be found in Appendix D and on this book’s companion Web site. Visit the Web site periodically to share and discuss your answers. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  8. CHAPTER 17 Native Dynamic SQL CHAPTER OBJECTIVES In this chapter, you will learn about . EXECUTE IMMEDIATE statements . OPEN-FOR, FETCH, and CLOSE statements Generally, PL/SQL applications perform a specific task and manipulate a static set of tables. For example, a stored procedure might accept a student ID and return the student’s first and last names. In such a procedure, a SELECT state- ment is known in advance and is compiled as part of the procedure. Such SELECT statements are called static because they do not change from execution to execution. Now, consider a different type of PL/SQL application in which SQL statements are built on the fly, based on a set of parameters specified at runtime. For example, an application might need to build various reports based on SQL state- ments where table and column names are not known in advance, or the sorting and grouping of data are specified by a user requesting a report. Similarly, another application might need to create or drop tables or other database objects based on the action specified by a user at runtime. Because these SQL statements are generated on the fly and might change from time to time, they are called dynamic. PL/SQL has a feature called native dynamic SQL (dynamic SQL for short) that helps you build applications similar to those just described. The use of dynamic SQL makes such applications flexible, versatile, and concise because it eliminates the need for complicated programming approaches. Native dynamic SQL is more convenient to use than the Oracle-supplied package DBMS_SQL, which has similar functionality. In this chapter you will learn how to create and use dynamic SQL. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  9. LAB 17.1 380 LAB 17.1 EXECUTE IMMEDIATE Statements LAB OBJECTIVE After completing this lab, you will be able to . Use the EXECUTE IMMEDIATE statement Generally, dynamic SQL statements are built by your program and are stored as character strings based on the parameters specified at runtime. These strings must contain valid SQL statements or PL/SQL code. Consider the following dynamic SQL statement: FOR EXAMPLE 'SELECT first_name, last_name FROM student WHERE student_id = :student_id' This SELECT statement returns a student’s first and last name for a given student ID. The value of the student ID is not known in advance and is specified with the help of a bind argument, :student_id. The bind argument is a placeholder for an undeclared identifier, and its name must be prefixed by a colon. As a result, PL/SQL does not differentiate between the following statements: 'SELECT first_name, last_name FROM student WHERE student_id = :student_id' 'SELECT first_name, last_name FROM student WHERE student_id = :id' To process dynamic SQL statements, you use EXECUTE IMMEDIATE or OPEN-FOR, FETCH, and CLOSE statements. EXECUTE IMMEDIATE is used for single-row SELECT statements, all DML statements, and DDL statements. OPEN-FOR, FETCH, and CLOSE statements are used for multirow SELECTs and reference cursors. BY THE WAY To improve the performance of dynamic SQL statements you can also use BULK EXECUTE IMMEDI- ATE, BULK FETCH, FORALL, and COLLECT INTO statements. However, these statements are outside the scope of this book and therefore are not covered. You can find detailed explanations and examples of their usage in the online Oracle help. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  10. EXECUTE IMMEDIATE Statements LAB 17.1 381 THE EXECUTE IMMEDIATE STATEMENT The EXECUTE IMMEDIATE statement parses a dynamic statement or a PL/SQL block for imme- diate execution. Its structure is as follows (the reserved words and phrases in square brackets are optional): EXECUTE IMMEDIATE dynamic_SQL_string [INTO defined_variable1, defined_variable2, ...] [USING [IN | OUT | IN OUT] bind_argument1, bind_argument2, ...][{RETURNING | RETURN} field1, field2, ... INTO bind_argument1, bind_argument2, ...] dynamic_SQL_string is a string that contains a valid SQL statement or a PL/SQL block. The INTO clause contains the list of predefined variables that hold values returned by the SELECT statement. This clause is used when a dynamic SQL statement returns a single row similar to a static SELECT INTO statement. Next, the USING clause contains a list of bind argu- ments whose values are passed to the dynamic SQL statement or PL/SQL block. IN, OUT, and IN OUT are modes for bind arguments. If no mode is specified, all bind arguments listed in the USING clause are in IN mode. Finally, the RETURNING INTO or RETURN clause contains a list of bind arguments that store values returned by the dynamic SQL statement or PL/SQL block. Similar to the USING clause, the RETURNING INTO clause may also contain various argument modes; however, if no mode is specified, all bind arguments are in OUT mode. DID YOU KNOW? When an EXECUTE IMMEDIATE statement contains both USING and RETURNING INTO clauses, the USING clause may specify only IN arguments. FOR EXAMPLE DECLARE sql_stmt VARCHAR2(100); plsql_block VARCHAR2(300); v_zip VARCHAR2(5) := '11106'; v_total_students NUMBER; v_new_zip VARCHAR2(5); v_student_id NUMBER := 151; BEGIN -- Create table MY_STUDENT sql_stmt := 'CREATE TABLE my_student '|| 'AS SELECT * FROM student WHERE zip = '||v_zip; EXECUTE IMMEDIATE sql_stmt; -- Select total number of records from MY_STUDENT table -- and display results on the screen EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM my_student' INTO v_total_students; DBMS_OUTPUT.PUT_LINE ('Students added: '||v_total_students); Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  11. LAB 17.1 EXECUTE IMMEDIATE Statements 382 FOR EXAMPLE (continued) -- Select current date and display it on the screen plsql_block := 'DECLARE ' || ' v_date DATE; ' || 'BEGIN ' || ' SELECT SYSDATE INTO v_date FROM DUAL; '|| ' DBMS_OUTPUT.PUT_LINE (TO_CHAR(v_date, ''DD-MON-YYYY'')) ;'|| 'END;'; EXECUTE IMMEDIATE plsql_block; -- Update record in MY_STUDENT table sql_stmt := 'UPDATE my_student SET zip = 11105 WHERE student_id = :1 '|| 'RETURNING zip INTO :2'; EXECUTE IMMEDIATE sql_stmt USING v_student_id RETURNING INTO v_new_zip; DBMS_OUTPUT.PUT_LINE ('New zip code: '||v_new_zip); END; This script contains several examples of dynamic SQL. First, you create the table MY_STUDENT and populate it with records for a specified value of zip code. It is important to note that the variable v_zip is concatenated with the CREATE state- ment instead of being passed in as a bind argument. This point is illustrated in the next example. Second, you select the total number of students added to the MY_STUDENT table and display it on the screen. You use the INTO option with the EXECUTE IMMEDIATE statement because the SELECT statement returns a single row. Third, you create a simple PL/SQL block in which you select the current date and display it on the screen. Because the PL/SQL block does not contain any bind arguments, the EXECUTE IMMEDIATE statement is used in its simplest form. Finally, you update the MY_STUDENT table for a given student ID and return a new value of zip code using the RETURNING statement. So, the EXECUTE IMMEDIATE command contains both USING and RETURNING INTO options. The USING option allows you to pass a value of student 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 program. When run, this example produces the following output: Students added: 4 22-JUN-2003 New zip code: 11105 PL/SQL procedure successfully completed. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  12. EXECUTE IMMEDIATE Statements LAB 17.1 383 HOW TO AVOID COMMON ORA ERRORS WHEN USING EXECUTE IMMEDIATE Consider the simplified yet incorrect version of the preceding example. Changes are shown in bold. FOR EXAMPLE DECLARE sql_stmt VARCHAR2(100); v_zip VARCHAR2(5) := '11106'; v_total_students NUMBER; BEGIN -- Drop table MY_STUDENT EXECUTE IMMEDIATE 'DROP TABLE my_student'; -- Create table MY_STUDENT sql_stmt := 'CREATE TABLE my_student '|| 'AS SELECT * FROM student '|| 'WHERE zip = :zip'; EXECUTE IMMEDIATE sql_stmt USING v_zip; -- Select total number of records from MY_STUDENT table -- and display results on the screen EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM my_student' INTO v_total_students; DBMS_OUTPUT.PUT_LINE ('Students added: '|| v_total_students); END; First, you drop the MY_STUDENT table created in the previous version of the example. Next, you re-create the MY_STUDENT table, but in this case, you use a bind argument to pass a value of zip code to the CREATE statement at runtime. When run, this example produces the following error: DECLARE * ERROR at line 1: ORA-01027: bind variables not allowed for data definition operations ORA-06512: at line 12 DID YOU KNOW? A CREATE TABLE statement is a data definition statement. Therefore, it cannot accept any bind arguments. Next, consider another simplified version of the same example that also causes a syntax error. In this version, you pass the table name as a bind argument to the SELECT statement. Changes are shown in bold. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  13. LAB 17.1 EXECUTE IMMEDIATE Statements 384 FOR EXAMPLE DECLARE sql_stmt VARCHAR2(100); v_zip VARCHAR2(5) := '11106'; v_total_students NUMBER; BEGIN -- Create table MY_STUDENT sql_stmt := 'CREATE TABLE my_student '|| 'AS SELECT * FROM student '|| 'WHERE zip ='|| v_zip; EXECUTE IMMEDIATE sql_stmt; -- Select total number of records from MY_STUDENT table -- and display results on the screen EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM :my_table' INTO v_total_students USING 'my_student'; DBMS_OUTPUT.PUT_LINE ('Students added: '|| v_total_students); END; When run, this example causes the following error: DECLARE * ERROR at line 1: ORA-00903: invalid table name ORA-06512: at line 13 This example causes an error because you cannot pass names of schema objects to dynamic SQL statements as bind arguments. To provide a table name at runtime, you need to concatenate this example with the SELECT statement: EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM '||my_table INTO v_total_students; As mentioned earlier, a dynamic SQL string can contain any SQL statement or PL/SQL block. However, unlike static SQL statements, a dynamic SQL statement should not be terminated with a semicolon (;). Similarly, a dynamic PL/SQL block should not be terminated with a slash (/). Consider a different version of the same example in which the SELECT statement is terminated with a semicolon. Changes are shown in bold. WATCH OUT! If you created the MY_STUDENT table based on the corrected version of the preceding script, you need to drop it before running the following script. Otherwise, the error message generated by the example will differ from the error message shown after the example. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  14. EXECUTE IMMEDIATE Statements LAB 17.1 385 FOR EXAMPLE DECLARE sql_stmt VARCHAR2(100); v_zip VARCHAR2(5) := '11106'; v_total_students NUMBER; BEGIN -- Create table MY_STUDENT sql_stmt := 'CREATE TABLE my_student '|| 'AS SELECT * FROM student '|| 'WHERE zip = '||v_zip; EXECUTE IMMEDIATE sql_stmt; -- Select total number of records from MY_STUDENT table -- and display results on the screen EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM my_student;' INTO v_total_students; DBMS_OUTPUT.PUT_LINE ('Students added: '|| v_total_students); END; When run, this example produces the following error: DECLARE * ERROR at line 1: ORA-00911: invalid character ORA-06512: at line 13 The semicolon added to the SELECT statement is treated as an invalid character when the state- ment is created dynamically. A somewhat similar error is generated when a PL/SQL block is terminated by a slash: FOR EXAMPLE DECLARE plsql_block VARCHAR2(300); BEGIN -- Select current date and display it on the screen plsql_block := 'DECLARE ' || ' v_date DATE; ' || 'BEGIN ' || ' SELECT SYSDATE INTO v_date FROM DUAL; '|| ' DBMS_OUTPUT.PUT_LINE (TO_CHAR(v_date, ''DD-MON-YYYY''));'|| 'END;' ; EXECUTE IMMEDIATE plsql_block; END; Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  15. LAB 17.1 EXECUTE IMMEDIATE Statements 386 This example produces the following error message: DECLARE * ERROR at line 1: ORA-06550: line 1, column 133: PLS-00103: Encountered the symbol "/" The symbol "/" was ignored. ORA-06512: at line 12 PASSING NULLS In some cases you may need to pass a NULL value to a dynamic SQL statement as a value for a bind argument. For example, suppose you need to update the COURSE table so that the PREREQUISITE column is set to NULL. You can accomplish this with the following dynamic SQL and the EXECUTE IMMEDIATE statement: FOR EXAMPLE DECLARE sql_stmt VARCHAR2(100); BEGIN sql_stmt := 'UPDATE course'|| ' SET prerequisite = :some_value'; EXECUTE IMMEDIATE sql_stmt USING NULL; END; However, when run, this script causes the following error: USING NULL; * ERROR at line 7: ORA-06550: line 7, column 10: PLS-00457: expressions have to be of SQL types ORA-06550: line 6, column 4: PL/SQL: Statement ignored This error is generated because the literal NULL in the USING clause is not recognized as one of the SQL types. To pass a NULL value to the dynamic SQL statement, you should modify this example as follows (changes are shown in bold): FOR EXAMPLE DECLARE sql_stmt VARCHAR2(100); v_null VARCHAR2(1); BEGIN sql_stmt := 'UPDATE course'|| ' SET prerequisite = :some_value'; Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  16. Lab 17.1 Exercises LAB 17.1 387 EXECUTE IMMEDIATE sql_stmt USING v_null; END; To correct the script, you add an initialized variable v_null and replace the literal NULL in the USING clause with this variable. Because the variable v_null has not been initialized, its value remains NULL, and it is passed to the dynamic UPDATE statement at runtime. As a result, this version of the script completes without any errors. ▼ LAB 17.1 EXERCISES This section provides exercises and suggested answers, with discussion related to how those answers resulted. The most important thing to realize is whether your answer works. You should figure out the implications of the answers and what the effects are of any different answers you may come up with. 17.1.1 Use the EXECUTE IMMEDIATE Statement Create the following PL/SQL script: -- ch17_1a.sql, version 1.0 SET SERVEROUTPUT ON DECLARE sql_stmt VARCHAR2(200); v_student_id NUMBER := &sv_student_id; v_first_name VARCHAR2(25); v_last_name VARCHAR2(25); BEGIN sql_stmt := 'SELECT first_name, last_name'|| ' FROM student' || ' WHERE student_id = :1'; EXECUTE IMMEDIATE sql_stmt INTO v_first_name, v_last_name USING v_student_id; DBMS_OUTPUT.PUT_LINE ('First Name: '||v_first_name); DBMS_OUTPUT.PUT_LINE ('Last Name: '||v_last_name); END; Execute the script, and then complete the following exercises: A) Explain the preceding script. ANSWER: The declaration portion of the script declares the string that contains the dynamic SQL statement, and three variables to hold student’s ID, first name, and last name, respectively. The executable 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 runtime. The dynamic SQL statement is executed using the EXECUTE IMMEDIATE statement with two options, INTO and USING. The INTO clause contains two variables, v_first_name and v_last_name. These variables contain results returned by the SELECT statement. The USING clause contains the vari- able v_student_id, which is used to pass a value to the SELECT statement at runtime. Finally, two DBMS_OUTPUT.PUT_LINE statements are used to display the results of the SELECT statement on the screen. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  17. LAB 17.1 Lab 17.1 Exercises 388 When run, the script produces the following output: Enter value for sv_student_id: 105 old 3: v_student_id NUMBER := &sv_student_id; new 3: v_student_id NUMBER := 105; First Name: Angel Last Name: Moskowitz PL/SQL procedure successfully completed. B) Modify the script so that the student’s address (street, city, state, and zip code) is displayed on the screen as well. ANSWER: Your script should look similar to the following. Changes are shown in bold. -- ch17_1b.sql, version 2.0 SET SERVEROUTPUT ON DECLARE sql_stmt VARCHAR2(200); v_student_id NUMBER := &sv_student_id; v_first_name VARCHAR2(25); v_last_name VARCHAR2(25); v_street VARCHAR2(50); v_city VARCHAR2(25); v_state VARCHAR2(2); v_zip VARCHAR2(5); BEGIN sql_stmt := 'SELECT a.first_name, a.last_name, a.street_address'|| ' ,b.city, b.state, b.zip' || ' FROM student a, zipcode b' || ' WHERE a.zip = b.zip' || ' AND student_id = :1'; EXECUTE IMMEDIATE sql_stmt INTO v_first_name, v_last_name, v_street, v_city, v_state, v_zip USING v_student_id; DBMS_OUTPUT.PUT_LINE ('First Name: '||v_first_name); DBMS_OUTPUT.PUT_LINE ('Last Name: '||v_last_name); DBMS_OUTPUT.PUT_LINE ('Street: '||v_street); DBMS_OUTPUT.PUT_LINE ('City: '||v_city); DBMS_OUTPUT.PUT_LINE ('State: '||v_state); DBMS_OUTPUT.PUT_LINE ('Zip Code: '||v_zip); END; In the preceding script, you declare four new variables—v_street, v_city, v_state, and v_zip. Next, you modify the dynamic SQL statement so that it can return the student’s address. As a result, you modify the INTO clause by adding the new variables to it. Next, you add DBMS_OUTPUT.PUT_LINE statements to display the student’s address on the screen. When run, the script produces the following output: Enter value for sv_student_id: 105 old 3: v_student_id NUMBER := &sv_student_id; new 3: v_student_id NUMBER := 105; Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  18. Lab 17.1 Exercises LAB 17.1 389 First Name: Angel Last Name: Moskowitz Street: 320 John St. City: Ft. Lee State: NJ Zip Code: 07024 PL/SQL procedure successfully completed. It is important to remember that the order of variables listed in the INTO clause must follow the order of columns listed in the SELECT statement. In other words, if the INTO clause listed variables so that v_zip and v_state were misplaced while the SELECT statement remained unchanged, the scripts would generate an error: SET SERVEROUTPUT ON DECLARE sql_stmt VARCHAR2(200); v_student_id NUMBER := &sv_student_id; v_first_name VARCHAR2(25); v_last_name VARCHAR2(25); v_street VARCHAR2(50); v_city VARCHAR2(25); v_state VARCHAR2(2); v_zip VARCHAR2(5); BEGIN sql_stmt := 'SELECT a.first_name, a.last_name, a.street_address'|| ' ,b.city, b.state, b.zip' || ' FROM student a, zipcode b' || ' WHERE a.zip = b.zip' || ' AND student_id = :1'; EXECUTE IMMEDIATE sql_stmt -- variables v_state and v_zip are misplaced INTO v_first_name, v_last_name, v_street, v_city, v_zip, v_state USING v_student_id; DBMS_OUTPUT.PUT_LINE ('First Name: '||v_first_name); DBMS_OUTPUT.PUT_LINE ('Last Name: '||v_last_name); DBMS_OUTPUT.PUT_LINE ('Street: '||v_street); DBMS_OUTPUT.PUT_LINE ('City: '||v_city); DBMS_OUTPUT.PUT_LINE ('State: '||v_state); DBMS_OUTPUT.PUT_LINE ('Zip Code: '||v_zip); END; The error message is as follows: Enter value for sv_student_id: 105 old 3: v_student_id NUMBER := &sv_student_id; new 3: v_student_id NUMBER := 105; DECLARE * Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  19. LAB 17.1 Lab 17.1 Exercises 390 ERROR at line 1: ORA-06502: PL/SQL: numeric or value error ORA-06512: at line 16 This error is generated because the variable v_state can hold up to two characters. However, you are trying to store in it a value of zip code, which contains five characters. C) Modify the script created in the previous exercise (ch17_1b.sql) so that the SELECT statement can be run against either the STUDENT or INSTRUCTOR table. In other words, a user can specify the table name used in the SELECT statement at runtime. ANSWER: Your script should look similar to the following. Changes are shown in bold. -- ch17_1c.sql, version 3.0 SET SERVEROUTPUT ON DECLARE sql_stmt VARCHAR2(200); v_table_name VARCHAR2(20) := '&sv_table_name'; v_id NUMBER := &sv_id; v_first_name VARCHAR2(25); v_last_name VARCHAR2(25); v_street VARCHAR2(50); v_city VARCHAR2(25); v_state VARCHAR2(2); v_zip VARCHAR2(5); BEGIN sql_stmt := 'SELECT a.first_name, a.last_name, a.street_address'|| ' ,b.city, b.state, b.zip' || ' FROM '||v_table_name||' a, zipcode b' || ' WHERE a.zip = b.zip' || ' AND '||v_table_name||'_id = :1'; EXECUTE IMMEDIATE sql_stmt INTO v_first_name, v_last_name, v_street, v_city, v_state, v_zip USING v_id; DBMS_OUTPUT.PUT_LINE ('First Name: '||v_first_name); DBMS_OUTPUT.PUT_LINE ('Last Name: '||v_last_name); DBMS_OUTPUT.PUT_LINE ('Street: '||v_street); DBMS_OUTPUT.PUT_LINE ('City: '||v_city); DBMS_OUTPUT.PUT_LINE ('State: '||v_state); DBMS_OUTPUT.PUT_LINE ('Zip Code: '||v_zip); END; The declaration portion of the script contains a new variable, v_table_name, which holds the name of a table provided at runtime by the user. In addition, the variable v_student_id has been replaced by the variable v_id because it is not known in advance which table, STUDENT or INSTRUCTOR, will be accessed at runtime. The executable portion of the script contains a modified dynamic SQL statement. Notice that the statement does not contain any information specific to the STUDENT or INSTRUCTOR tables. In other words, the dynamic SQL statement used by the previous version (ch17_1b.sql) sql_stmt := 'SELECT a.first_name, a.last_name, a.street_address'|| ' ,b.city, b.state, b.zip' || Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  20. Lab 17.1 Exercises LAB 17.1 391 ' FROM student a, zipcode b' || ' WHERE a.zip = b.zip' || ' AND student_id = :1'; has been replaced by sql_stmt := 'SELECT a.first_name, a.last_name, a.street_address'|| ' ,b.city, b.state, b.zip' || ' FROM '||v_table_name||' a, zipcode b' || ' WHERE a.zip = b.zip' || ' AND '||v_table_name||'_id = :1'; The table name (student) has been replaced by the variable v_table_name in the FROM and the WHERE clauses. DID YOU KNOW? Note that for the last two versions of the script you have used generic table aliases—a and b instead of s and z or i and z, which are more descriptive. This technique allows you to create generic SQL statements that are not based on a specific table, because you do not always know in advance which table will be used. This version of the script produces the following output. The first run is against the STUDENT table, and the second run is against the INSTRUCTOR table: Enter value for sv_table_name: student old 3: v_table_name VARCHAR2(20) := '&sv_table_name'; new 3: v_table_name VARCHAR2(20) := 'student'; Enter value for sv_id: 105 old 4: v_id NUMBER := &sv_id; new 4: v_id NUMBER := 105; First Name: Angel Last Name: Moskowitz Street: 320 John St. City: Ft. Lee State: NJ Zip Code: 07024 PL/SQL procedure successfully completed. Enter value for sv_table_name: instructor old 3: v_table_name VARCHAR2(20) := '&sv_table_name'; new 3: v_table_name VARCHAR2(20) := 'instructor'; Enter value for sv_id: 105 old 4: v_id NUMBER := &sv_id; new 4: v_id NUMBER := 105; First Name: Anita Last Name: Morris Street: 34 Maiden Lane City: New York State: NY Zip Code: 10015 PL/SQL procedure successfully completed. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Đồng bộ tài khoản