Oracle PLSQL Language- P13

Tham khảo tài liệu oracle plsql language- p13, 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ả
Nội dung trích xuất từ tài liệu:
Oracle PLSQL Language- P13 10.5.3 Nonsequential Use of PL/SQL Table Of course, the idea of using a minimum and maximum row assumes that the rows in the table are used sequentially. That is, you fill row one, then row two, etc. This is a perfectly reasonable way to fill a tables rows; to do this you absolutely must know the value of the row last filled. You are not, however, required to fill rows in this way. You can place a value in any row of the table you wish, regardless of the primary key value of the last row you filled. The following example illustrates filling a PL/SQL tables rows randomly rather than sequentially: DECLARE TYPE countdown_tests_tabtype IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER; countdown_test_list countdown_tests_tabtype; BEGIN countdown_test_list (1) := All systems go; countdown_test_list (43) := Internal pressure; countdown_test_list (255) := Engine inflow; END; In this situation, the minimum and maximum values do not have much significance. The ability to randomly place values in a table can come in very handy when the primary key value for the tables row is actually not sequentially derived, but is instead based on data in your application. This use of intelligent primary key values is explored in more detail in Section 10.9.2, Data-Smart Row Numbers in PL/SQL Tables later in this chapter. 10.5.4 Passing PL/SQL Tables as Parameters You can also pass a PL/SQL table as a parameter in a procedure or function; with this approach you can, in a single call, pass all the values in a table into the module. In the following package specification I define two modules that pass PL/SQL tables as parameters. The send_promos procedure sends a promotional mailing to all the companies in my table. The companies_overdue function returns a table filled with the names of companies that have overdue bills. PACKAGE company_pkg IS TYPE primary_keys_tabtype IS TABLE OF company. company_id%TYPE NOT NULL INDEX BY BINARY_INTEGER; company_keys_tab primary_keys_tabtype; emp_keys_tab primary_keys_tabtype;Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. /* Table type and table for company names */ TYPE company_names_tabtype IS TABLE OF company.name% TYPE INDEX BY BINARY_INTEGER; company_names_tab company_names_tabtype; /* Parameter is a table of company primary keys */ PROCEDURE send_promos (company_table_in IN primary_keys_tabtype); /* Function returns a table of company names */ FUNCTION companies_overdue (overdue_date_in IN DATE) RETURN company_names_tabtype; /* Returns company ID for name. */ FUNCTION id (name in IN company.name%TYPE) RETURN company.company id%TYPE END company_pkg; Now that I have a package containing both the table type and the programs referencing those types, I can call these programs. The only tricky part to remember here is that you must declare a PL/SQL table based on the type before you can use any of the programs. Here is an example of returning a PL/ SQL table as a functions return value: CREATE OR REPLACE PROCEDURE send_promos_for_overdue_companies (date_in IN DATE := SYSDATE) IS v_row PLS_INTEGER; /* Declare a PL/SQL table based on the packaged type. */ cnames company_pkg.company_names_tabtype; BEGIN cnames := company_pkg.companies_overdue (date_in); /* || In PL/SQL 2.3 I can use navigation methods to traverse. || Notice that I do not assume that rows are filled sequentially. */ v_row := cnames.FIRST; LOOP EXIT WHEN v_row IS NULL;Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. DBMS_OUTPUT.PUT_LINE (cnames(v_row)); v_row := cnames.NEXT (v_row); END LOOP; END; / Notice that I could also have avoided declaring my own PL/SQL table, cnames, by using the predefined table in the package: company_pkg.company_names_tab := company_pkg.companies_overdue (date_in); If I had taken this approach, however, any rows already defined in this global table would have been erased. Here is an example of calling a procedure, passing a PL/SQL table as an argument in the parameter list: DECLARE v_row PLS_INTEGER; /* Table of primary keys for company */ company_ids company_pkg.primary_keys_tabtype; BEGIN /* Populate the table with names of overdue companies. */ ...