intTypePromotion=1
zunia.vn Tuyển sinh 2024 dành cho Gen-Z zunia.vn zunia.vn
ADSENSE

Oracle PL/SQL Language Pocket Reference- P12

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

86
lượt xem
17
download
 
  Download Vui lòng tải xuống để xem tài liệu đầy đủ

Oracle PL/SQL Language Pocket Reference- P12: This pocket guide features quick-reference information to help you use Oracle's PL/SQL language. It includes coverage of PL/SQL features in the newest version of Oracle, Oracle8i. It is a companion to Steven Feuerstein and Bill Pribyl's bestselling Oracle PL/SQL Programming. Updated for Oracle8, that large volume (nearly 1,000 pages) fills a huge gap in the Oracle market, providing developers with a single, comprehensive guide to building applications with PL/SQL and building them the right way. ...

Chủ đề:
Lưu

Nội dung Text: Oracle PL/SQL Language Pocket Reference- P12

  1. 3. Using another exception section within the first exception section, trap this exception as the "else" in this pseudo-IF statement. Within the exception handler, try to convert the string with TO_DATE and the third mask, MM/YY. If it works, I am done. If it doesn't work, an exception is raised. 4. I have only three masks, so if I cannot convert the string after these three TO_DATE calls, the user entry is invalid and I will simply return NULL. The function convert_date that follows illustrates the full PL/SQL version of the preceding pseudocode description. I make liberal use of the WHEN OTHERS exception handler because I have no way of knowing which exception would have been raised by the conversion attempt: FUNCTION convert_date (value_in IN VARCHAR2) RETURN DATE IS return_value DATE; BEGIN IF value_int IS NULL THEN return_value := NULL; ELSE BEGIN /* IF MM/DD/YY mask works, set return value. */ return_value := TO_DATE (value_in, 'MM/DD/YY'); EXCEPTION /* OTHERWISE: */ WHEN OTHERS THEN BEGIN /* IF DD-MON-YY mask works, set return value. */ return_value := TO_DATE (value_in, 'DD-MON- YY'); EXCEPTION /* OTHERWISE: */ WHEN OTHERS THEN BEGIN /* IF MM/YY mask works, set return value. */ return_value := TO_DATE (value_in, 'MM/YY'); EXCEPTION /* OTHERWISE RETURN NULL. */ WHEN OTHERS THEN return_value := NULL; END; END; END; END IF; RETURN (return_value); END; Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  2. Previous: 8.8 Oracle PL/SQL Next: 8.10 RAISE Nothing NO_DATA_FOUND: Programming, 2nd Edition but Exceptions Multipurpose Exception 8.8 NO_DATA_FOUND: Book Index 8.10 RAISE Nothing but Multipurpose Exception Exceptions The Oracle Library Navigation Copyright (c) 2000 O'Reilly & Associates. All rights reserved. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  3. Previous: 8.9 Exception Chapter 8 Next: 9. Records in PL/SQL Handler as IF Statement Exception Handlers 8.10 RAISE Nothing but Exceptions Have you noticed that the RAISE statement acts in many ways like a GOTO statement? The GOTO statement in PL/SQL looks like this: GOTO label_name; where label_name is the name of a label. This label is placed in a program as follows: When PL/SQL encounters a GOTO statement, it immediately shifts control to the first executable statement following the label (which must still be in the execution section of the PL/SQL block). The RAISE statement works much the same way: when PL/SQL encounters a RAISE, it immediately shifts control to the exception section, and then looks for a matching exception. A very significant and fundamental difference between GOTO and RAISE, however, is that GOTO branches to another execution statement, whereas RAISE branches to the exception section. The RAISE statement, in other words, shifts the focus of the program from normal execution to "error handling mode." Both from the standpoint of code readability and also of maintenance, you should never use the RAISE statement as a substitute for a control structure, be it a GOTO or an IF statement. If you have not tried to use RAISE in this way, you might think that I am building up a straw man in order to knock it down. Would that it were so. Just in the process of writing this book, I ran across several examples of this abuse of exception handling. Check out, for example, the function description for GET_GROUP_CHAR_CELL in Oracle Corporation's Oracle Forms Reference Volume 1. It offers a function called Is_Value_In_List, which returns the row number of the value if it is found in the record group, as an example of a way to use GET_GROUP_CHAR_CELL. The central logic of Is_Value_In_List is shown in the following example. The function contains three different RAISE statements -- all of which raise the exit_function exception: 1 FUNCTION Is_Value_In_List Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  4. 2 (value VARCHAR2, rg_name VARCHAR2, rg_column VARCHAR2) 3 RETURN NUMBER 4 IS 5 Exit_Function EXCEPTION; 6 BEGIN 7 If bad-inputs THEN 8 RAISE Exit_Function; 9 END IF; 10 11 LOOP-through-record-group 12 IF match-found 13 RAISE Return_Value; 14 END IF; 15 END LOOP; 16 17 RAISE Exit_Function; 18 19 EXCEPTION 20 WHEN Return_Value THEN 21 RETURN row#; 22 23 WHEN Exit_Function THEN 24 RETURN 0; 25 END; The first RAISE on line 8 is an appropriate use of an exception because we have an invalid data structure. The function should bail out. The second RAISE on line 13 is, however, less justifiable. This RAISE is used to end the program and return the row in which the match was found. An exception is, in this case, used for successful completion. Exception Handling -- Quick Facts and Tips Here are some facts and tips to remember about exception handling: q The exception section of a PL/SQL block only handles exceptions raised in the execution section of that block. q An exception raised in the declaration section of a PL/SQL block is handled by the exception section of the enclosing block, if it exists. q An exception raised in the exception section of a PL/SQL block is handled by the exception section of the enclosing block, if it exists. q Use WHEN OTHERS when you want to trap and handle all exceptions in a PL/SQL block. q Once an exception is raised, the block's execution section is terminated and control is Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  5. transferred to the exception section. You cannot return to that execution section after the exception is raised. q After an exception is handled, the next executable statement in the enclosing block is executed. q To handle a specific exception, it must have a name. You declare exceptions to give them names. q Once you have handled an exception, normal program execution continues. You are no longer in an "exception" situation. The third RAISE on line 17 is also questionable. This RAISE is the very last statement of the function. Now, to my mind, the last line of a function should be a RETURN statement. The whole point of the function, after all, is to return a value. In this case, however, the last line is an exception, because the author has structured the code so that if I got this far, I have not found a match. So raise the exception, right? Wrong. "Row-not-found" is not an exception from the standpoint of the function. That condition should be considered one of the valid return values of a function that asks "Is value in list?" This function should be restructured so that the exception is raised only when there is a problem. From the perspective of structured exception handling in PL/SQL, this function suffered from several weaknesses: Poorly named exceptions The exception names exit_function and return_value describe actions, rather than error conditions. The name of an exception should describe the error which took place. Exceptions for valid outcomes By using these "action" names, the developers are actually being very open about how they are manipulating the exception handler. They say, "I use exceptions to implement logic branching." We should say to them, "Don't do it! Use the constructs PL/SQL provides to handle this code in a structured way." If you encounter either of these conditions in code you are writing or reviewing, take a step back. Examine the logical flow of the program and see how you can use the standard control structures (IF, LOOP, and perhaps even GOTO) to accomplish your task. The result will be much more readable and maintainable code. Previous: 8.9 Exception Oracle PL/SQL Next: 9. Records in PL/SQL Handler as IF Statement Programming, 2nd Edition 8.9 Exception Handler as IF Book Index 9. Records in PL/SQL Statement Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  6. The Oracle Library Navigation Copyright (c) 2000 O'Reilly & Associates. All rights reserved. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  7. Previous: 8.10 RAISE Chapter 9 Next: 9.2 Table-Based Nothing but Exceptions Records 9. Records in PL/SQL Contents: Record Basics Table-Based Records Cursor-Based Records Programmer-Defined Records Assigning Values to and from Records Record Types and Record Compatibility Nested Records Records in PL/SQL programs are very similar in concept and structure to the rows of a database table. A record is a composite data structure, which means that it is composed of more than one element or component, each with its own value. The record as a whole does not have value of its own; instead, each individual component or field has a value. The record gives you a way to store and access these values as a group. If you are not familiar with using records in your programs, you might initially find them complicated. When used properly, however, records will greatly simplify your life as a programmer. You will often need to transfer data from the database into PL/SQL structures and then use the procedural language to further massage, change, or display that data. When you use a cursor to read information from the database, for example, you can pass that table's record directly into a single PL/ SQL record. When you do this you preserve the relationship between all the attributes from the table. 9.1 Record Basics This section introduces the different types of records and the benefits of using them in your programs. 9.1.1 Different Types of Records PL/SQL supports three different kinds of records: table-based, cursor-based, and programmer- defined. These different types of records are used in different ways and for different purposes, but all Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  8. three share the same internal structure: every record is composed of one or more fields. However, the way these fields are defined in the record depend on the record type. Table 9.1 shows this information about each record type. Table 9.1: PL/SQL Record Types Record Type Description Fields in Record Table-based A record based on a table's column Each field corresponds to -- and has structure. the same name as -- a column in a table. Cursor-based A record based on the cursor's Each field corresponds to a column SELECT statement. or expression in the cursor SELECT statement. Programmer- A record whose structure you, the Each field is defined explicitly (its defined programmer, get to define with a name and datatype) in the TYPE declaration statement. statement for that record; a field in a programmer-defined record can even be another record. Figure 9.1 illustrates the way a cursor record adopts the structure of the SELECT statement by using the %ROWTYPE declaration attribute (explained later in this chapter). Figure 9.1: Mapping of cursor structure to PL/SQL record 9.1.2 Accessing Record-Based Data Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  9. You access the fields within a record using dot notation, just as you would identify a column from a database table, in the following format: . You would reference the first_name column from the employee table as follows: employee.first_name You would reference the emp_full_name field in the employee PL/SQL record as: employee_rec.emp_full_name The record or tuple structure of relational database tables has proven to be a very powerful way to represent data in a database, and records in your programs offer similar advantages. The next section describes briefly the reasons you might want to use records. The rest of this chapter show you how to define and use each of the different types of records, and the situations appropriate to each record type. 9.1.3 Benefits of Using Records The record data structure provides a high-level way of addressing and manipulating program-based data. This approach offers the following benefits: Data abstraction Instead of working with individual attributes of an entity or object, you think of and manipulate that entity as a "thing in itself." Aggregate operations You can perform operations which apply to all the columns of a record. Leaner, cleaner code You can write less code and make what you do write more understandable. The following sections describe each of these benefits. 9.1.3.1 Data abstraction When you abstract something, you generalize it. You distance yourself from the nitty-gritty details and concentrate on the big picture. When you create modules, you abstract the individual actions of the module into a name. The name (and program specification) represents those actions. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  10. When you create a record, you abstract all the different attributes or fields of the subject of that record. You establish a relationship between all those different attributes and you give that relationship a name by defining a record. 9.1.3.2 Aggregate operations Once you have stored information in records, you can perform operations on whole blocks of data at a time, rather than on each individual attribute. This kind of aggregate operation reinforces the abstraction of the record. Very often you are not really interested in making changes to individual components of a record, but instead to the object which represents all of those different components. Suppose that in my job I need to work with companies, but I don't really care about whether a company has two lines of address information or three. I want to work at the level of the company itself, making changes to, deleting, or analyzing the status of a company. In all these cases I am talking about a whole row in the database, not any specific column. The company record hides all that information from me, yet makes it accessible when and if I need it. This orientation brings you closer to viewing your data as a collection of objects with rules applied to those objects. 9.1.3.3 Leaner, cleaner code Using records also helps you to write clearer code and less of it. When I use records, I invariably produce programs which have fewer lines of code, are less vulnerable to change, and need fewer comments. Records also cut down on variable sprawl; instead of declaring many individual variables, I declare a single record. This lack of clutter creates aesthetically attractive code which requires fewer resources to maintain. 9.1.4 Guidelines for Using Records Use of PL/SQL records can have a dramatic impact on your programs, both in initial development and in ongoing maintenance. To ensure that I personally get the most out of record structures, I have set the following guidelines for my development: q Create corresponding cursors and records. Whenever I create a cursor in my programs, I also create a corresponding record (except in the case of cursor FOR loops). I always FETCH into a record, rather than into individual variables. In those few instances when it might involve a little extra work over simply fetching into a single variable, I marvel at the elegance of this approach and compliment myself on my commitment to principle. q Create table-based records. Whenever I need to store table-based data within my programs, I create a new (or use a predefined) table-based record to store that data. I keep my variable use to a minimum and dynamically link my program data structures to my RDBMS data structures with the %ROWTYPE attribute. q Pass records as parameters. Whenever appropriate, I pass records rather than individual variables as parameters in my procedural interfaces. This way, my procedure calls are less likely to change over time, making my code more stable. There is a downside to this technique, however: if a record is passed as an OUT or IN OUT parameter, its field values are Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  11. saved by the PL/SQL program in case of the need for a rollback. This can use up memory and consume unnecessary CPU cycles. 9.1.5 Referencing a Record and its Fields The rules you must follow for referencing a record in its entirety or a particular field in the record are the same for all types of records: table, cursor, and programmer-defined. A record's structure is similar to that of a database table. Where a table has columns, a record has fields. You reference a table's column by its name in a SQL statement, as in: SELECT company_id FROM company; Of course, the fully qualified name of a column is: . This full name is often required in a SQL statement to avoid ambiguity, as is true in the following statement: SELECT employee.company_id, COUNT(*) total_employees FROM company, employee WHERE company.company_id = employee.company_id; If I do not preface the name of company_id with the appropriate table name, the SQL compiler will not know to which table that column belongs. The same is true for a record's fields. You reference a record by its name, and you reference a record's field by its full name using dot notation, as in: . So if I create a record named company_rec, which contains a company_id field, then I would reference this field as follows: company_rec.company_id You must always use the fully qualified name of a field when referencing that field. If you don't, PL/ SQL will never be able to determine the "default" record for a field, as it does in a SQL statement. You do not, on the other hand, need to use dot notation when you reference the record as a whole; you simply provide the name of the record. In the following example, I pass a record as a parameter to a procedure: DECLARE TYPE customer_sales_rectype IS RECORD (...); Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  12. customer_rec customer_sales_rectype; BEGIN display_sales_data (customer_rec); END; I didn't make a single dotted reference to any particular field in the customer record. Instead I declared the record type, used it to create the record, used the record type again to define the type for the parameter in the procedure specification, and finally called the procedure, passing it the specific record I declared. 9.1.6 Comparing Two Records While it is possible to stay at the record level in certain situations, you can't avoid direct references to fields in many other cases. If you want to compare records, for example, you must always do so through comparison of the records' individual fields. Suppose you want to know if the old company information is the same as the new company information, both being stored in records of the same structure. The following test for equality will not compile: IF old_company_rec = new_company_rec /-- Illegal syntax! THEN ... END IF; even though the structures of the two records are absolutely identical and based on the same record type (in this case, a table record type). PL/SQL will not automatically compare each individual field in the old company record to the corresponding field in the new company record. Instead, you will have to perform that detailed check yourself, as in: IF old_company_rec.name = new_company_rec.name AND old_company_rec.incorp_date = new_company_rec. incorp_date AND old_company_rec.address1 = new_company_rec.address1 AND THEN ... the two records are identical ... END IF; Of course, you do not simply examine the value of a particular field when you work with records and their fields. Instead, you will assign values to the record and its fields, from either scalar variables or other records. You can reference a record's field on both sides of the assignment operator. In the following example I change the contents of a record, even though that record was just filled from a cursor: Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  13. DECLARE CURSOR company_cur IS ...; company_rec company_cur%ROWTYPE; BEGIN OPEN company_cur; FETCH company_cur INTO company_rec; company_rec.name := 'New Name'; END; There is, in other words, no such thing as a "read-only" PL/SQL record structure. Previous: 8.10 RAISE Oracle PL/SQL Next: 9.2 Table-Based Nothing but Exceptions Programming, 2nd Edition Records 8.10 RAISE Nothing but Book Index 9.2 Table-Based Records Exceptions The Oracle Library Navigation Copyright (c) 2000 O'Reilly & Associates. All rights reserved. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  14. Previous: 9.1 Record Basics Chapter 9 Next: 9.3 Cursor-Based Records in PL/SQL Records 9.2 Table-Based Records A table-based record, or table record, is a record whose structure (set of columns) is drawn from the structure (list of columns) of a table. Each field in the record corresponds to and has the same name as a column in the table. The fact that a table record always reflects the current structure of a table makes it useful when managing information stored in that table. Suppose we have a table defined as the following: CREATE TABLE rain_forest_history (country_code NUMBER (5), analysis_date DATE, size_in_acres NUMBER, species_lost NUMBER ); Like this table, a record created from it would also have four fields of the same names. You must use dot notation to reference a specific field in a record. If the record for the above table were named rain_forest_rec, then the fields would each be referred to as: rain_forest_rec.country_code rain_forest_rec.analysis_date rain_forest_rec.size_in_acres rain_forest_rec.species_lost 9.2.1 Declaring Records with the %ROWTYPE Attribute To create a table record, you declare it with the %ROWTYPE attribute. The %ROWTYPE attribute is very similar to the %TYPE attribute discussed in Chapter 4, Variables and Program Data, except that it is used to declare a composite structure rather than the simple, scalar variable produced with % TYPE. Sounds perfect for a record, doesn't it? The general format of the %ROWTYPE declaration for a table record is: Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  15. %ROWTYPE; where is the name of the record, and is the name of a table or view whose structure forms the basis for the record. Just as the %TYPE attribute automatically provides the column's datatype to the variable, %ROWTYPE provides the datatypes of each of the columns in a table for the record's fields. In the following example, a %TYPE declaration defines a variable for the company name, while the %ROWTYPE declaration defines a record for the entire company row. A SELECT statement then fills the comp_rec record with a row from the table. DECLARE comp_name company.name%TYPE; comp_rec company%ROWTYPE; BEGIN SELECT * FROM company INTO comp_rec WHERE company_id = 1004; Notice that I do not need to specify the names of company's columns in either the record declaration or the SELECT statement. I can keep the code very flexible with the table record. If the DBA adds a column to the table, changes the name of a column, or even removes a column, the preceding lines of code will not be affected at all. (You would, however, need to recompile your programs in order to pick up the change in data structure.) Of course, if my program makes an explicit reference to a modified column, that code would probably have to be changed. With a strong reliance on data manipulation through records, however, you can keep such references to a minimum. Previous: 9.1 Record Basics Oracle PL/SQL Next: 9.3 Cursor-Based Programming, 2nd Edition Records 9.1 Record Basics Book Index 9.3 Cursor-Based Records The Oracle Library Navigation Copyright (c) 2000 O'Reilly & Associates. All rights reserved. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  16. Previous: 9.2 Table-Based Chapter 9 Next: 9.4 Programmer- Records Records in PL/SQL Defined Records 9.3 Cursor-Based Records A cursor-based record, or cursor record, is a record whose structure is drawn from the SELECT list of a cursor. (See Chapter 6, Database Interaction and Cursors, for more information on cursors.) Each field in the record corresponds to and has the same name as the column or aliased expression in the cursor's query. This relationship is illustrated by Figure 9.1. The same %ROWTYPE attribute used to declare table records is also used to declare a record for an explicitly declared cursor, as the following example illustrates: DECLARE /* Define the cursor */ CURSOR comp_summary_cur IS SELECT C.company_id, name, city FROM company C, sales S WHERE c.company_id = s.company_id; /* Create a record based on that cursor */ comp_summary_rec comp_summary_cur%ROWTYPE; BEGIN The general format of the cursor %ROWTYPE declaration is: %ROWTYPE; where is the name of the record and is the name of the cursor upon which the record is based. This cursor must have been previously defined, in the same declaration section as the record, in an enclosing block, or in a package. 9.3.1 Choosing Columns for a Cursor Record You could declare a cursor record with the same syntax as a table record, but you don't have to match a table's structure. A SELECT statement creates a "virtual table" with columns and expressions as the Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  17. list of columns. A record based on that SELECT statement allows you to represent a row from this virtual table in exactly the same fashion as a true table record. The big difference is that I get to determine the fields in the record, as well as the names for those fields. Through the cursor you can, therefore, create special-purpose records tailored to a particular program and need. The query for a cursor can contain all or only some of the columns from one or more tables. A cursor can also contain expressions or virtual columns in its select list. In addition, you can provide aliases for the columns and expressions in the select list of a cursor. These aliases effectively rename the fields in the cursor record. In the following example I create a cursor against the rain forest history table for all records showing a greater than average loss of species in 1994. Then, for each record found, I execute the publicize_loss procedure to call attention to the problem and execute project_further_damage to come up with an analysis of future losses: DECLARE /* || Create a cursor and rename the columns to give them a more || specific meaning for this particular cursor and block of code. */ CURSOR high_losses_cur IS SELECT country_code dying_country_cd, size_in_acres shrinking_plot, species_lost above_avg_loss FROM rain_forest_history WHERE species_lost > (SELECT AVG (species_lost) FROM rain_forest_history WHERE TO_CHAR (analysis_date, 'YYYY') = '1994'); /* Define the record for this cursor */ high_losses_rec high_losses_cur%ROWTYPE; BEGIN OPEN high_losses_cur; LOOP FETCH high_losses_cur INTO high_losses_rec; EXIT WHEN high_losses_cur%NOTFOUND; /* || Now when I reference one of the record's fields, I use the || name I gave that field in the cursor, not the original column || name from the table. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  18. */ publicize_loss (high_losses_rec.dying_country_cd); project_further_damage (high_losses_rec. shrinking_plot); END LOOP; CLOSE high_losses_cur; END; 9.3.2 Setting the Record's Column Names The column aliases change the names of the fields in the record. In the above example, the customized column names are more descriptive of the matter at hand than the standard column names; the code becomes more readable as a result. A cursor's query can also include calculated values or expressions; in those cases, you must provide an alias for that calculated value if you want to access it through a record. Otherwise, there is no way for PL/SQL to create a named field for that value in the record -- and that name is your handle to the data. Suppose, for example, I have a parameterized cursor and record defined as follows: CURSOR comp_performance_cur (id_in IN NUMBER) IS SELECT name, SUM (order_amount) FROM company WHERE company_id = id_in; comp_performance_rec comp_performance_cur%ROWTYPE; I can refer to the company name with standard dot notation: IF comp_performance_rec.name = 'ACME' THEN ... But how can I refer to the sum of the order_amount values? I need to provide a name for this calculated column, as shown below: CURSOR comp_performance_cur (id_in IN NUMBER) IS SELECT name, SUM (order_amount) tot_sales FROM company WHERE company_id = id_in; comp_performance_rec comp_performance_cur%ROWTYPE; I can now refer to the sum of the order_amount values as follows: IF comp_performance_rec.tot_sales > 10000 THEN ... NOTE: Even though the same %ROWTYPE attribute is used in creating both table and cursor records and the declarations themselves look very similar, the record created Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  19. from a table has a different record type from the record created from a cursor. Records of different types are restricted in how they can interact, a topic we will explore in the next section. Previous: 9.2 Table-Based Oracle PL/SQL Next: 9.4 Programmer- Records Programming, 2nd Edition Defined Records 9.2 Table-Based Records Book Index 9.4 Programmer-Defined Records The Oracle Library Navigation Copyright (c) 2000 O'Reilly & Associates. All rights reserved. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  20. Previous: 9.3 Cursor-Based Chapter 9 Next: 9.5 Assigning Values Records Records in PL/SQL to and from Records 9.4 Programmer-Defined Records Now you know how to create a record with the same structure as a table or a cursor. These are certainly very useful constructs in a programming language designed to interface with the Oracle RDBMS. Yet do these kinds of records cover all of our needs for composite data structures? What if I want to create a record that has nothing to do with either a table or a cursor? What if I want to create a record whose structure is derived from several different tables and views? Should I really have to create a "dummy" cursor just so I can end up with a record of the desired structure? For just these kinds of situations, PL/SQL offers programmer-defined records, declared with the TYPE... RECORD statement.[1] [1] Programmer-defined records are supported -- but undocumented -- in PL/SQL Release 1.1. With the programmer-defined record, you have complete control over the number, names, and datatypes of fields in the record. To declare a programmer-defined record, you must perform two distinct steps: 1. Declare or define a record TYPE containing the structure you want in your record. 2. Use this record TYPE as the basis for declarations of your own actual records having that structure. 9.4.1 Declaring Programmer-Defined Record TYPEs You declare a record type with the record TYPE statement. The TYPE statement specifies the name of the new record structure, and the components or fields which make up that record. The general syntax of the record TYPE definition is: TYPE IS RECORD ( , Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
ADSENSE

CÓ THỂ BẠN MUỐN DOWNLOAD

 

Đồng bộ tài khoản
2=>2