Oracle PLSQL Language- P19

Tham khảo tài liệu oracle plsql language- p19, 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- P19 ORA-06571: Function TOTAL_COMP does not guarantee not to update database As discussed in Section 17.7, Realities: Calling PL/SQL Functions in SQL, it can be very difficult at times (and sometimes impossible) to avoid this error. In other situations, however, there is an easy resolution (certainly do check the above list of restrictions). Previous: 17.3 Oracle PL/SQL Next: 17.5 Calling Requirements for Stored Programming, 2nd Edition Packaged Functions in SQL Functions in SQL 17.3 Requirements for Stored Book Index 17.5 Calling Packaged Functions in SQL Functions in SQL The Oracle Library Navigation Copyright (c) 2000 OReilly & Associates. All rights reserved.Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Previous: 17.4 Restrictions Chapter 17 Next: 17.6 Column/ on PL/SQL Functions in Calling PL/SQL Functions in Function Name Precedence SQL SQL 17.5 Calling Packaged Functions in SQL As I describe in Chapter 16, Packages, the specification and body of a package are distinct; a specification can (and must) exist before its body has been defined. This feature of packages makes life complicated when it comes to calling functions in SQL. When a SELECT statement calls a packaged function, the only information available to it is the package specification. Yet it is the contents of the package body which determine whether that function is valid for execution in SQL. The consequence of this structure is that you will have to add code to your package specification in order to enable a packaged function for calling in SQL. To use the official lingo, you must explicitly assert the purity level (the extent to which a function is free of side effects) of a stored function in a package specification. The Oracle Server can then determine when the package body is compiled whether the function violates that purity level. If so, an error will be raised and you then face the sometimes daunting task of figuring out where and how the violation occurs. You assert a purity level for a function with the RESTRICT_REFERENCES pragma, explored in the next section. 17.5.1 The RESTRICT_REFERENCES Pragma As Ive mentioned, a pragma is a special directive to the PL/SQL compiler. If you have ever created a programmer-defined, named exception, you have already encountered your first pragma. In the case of the RESTRICT_REFERENCES pragma, you are telling the compiler the purity level you believe your function meets or exceeds. You need a separate pragma statement for each packaged function you wish to use in a SQL statement, and it must come after the function declaration in the package specification (you do not specify the pragma in the package body). To assert a purity level with the pragma, use the following syntax:Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. PRAGMA RESTRICT_REFERENCES (function_name, WNDS [, WNPS] [, RNDS] [, RNPS]) where function_name is the name of the function whose purity level you wish to assert, and the four different codes have the following meanings: WNDS Writes No Database State. Asserts that the function does not modify any database tables. WNPS Writes No Package State. Asserts that the function does not modify any package variables. RNDS Reads No Database State. Asserts that the function does not read any database tables. RNPS Reads No Package State. Asserts that the function does not read any package variables. Notice that only the WNDS level is mandatory in the pragma. That is consistent with the restriction that stored functions in SQL may not execute an UPDATE, INSERT, or DELETE statement. All other states are optional. You can list them in any order, but you must include the WNDS argument. No one argument implies another argument. I can write to the database without reading from it. I can read a package variable without writing to a package variable. Here is an example of two different purity level assertions for functions in the company_financials package: PACKAGE company_financials IS FUNCTION company_type (type_code_in IN VARCHAR2) RETURN VARCHAR2; FUNCTION company_name (company_id_in IN company. company_id%TYPE) RETURN VARCHAR2; PRAGMA RESTRICT_REFERENCES (company_type, WNDS, RNDS, WNPS, RNPS); PRAGMA RESTRICT_REFERENCES (company_name, WNDS, WNPS, RNPS); END company_financials; In this package, the company_name function reads from the database to obtain the name for the specified company. No ...