Oracle PLSQL Language- P19

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

Thêm vào BST

Báo xấu

77
lượt xem 7
download

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

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ả

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: 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 O'Reilly & 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 I've 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. Notice that I placed both pragmas together at the bottom of the package Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
specification -- the pragma does not need to immediately follow the function specification. I also went to the trouble of specifying the WNPS and RNPS arguments for both of the functions. Oracle Corporation recommends that you assert the highest possible purity levels so that the compiler will never reject the function unnecessarily. NOTE: If a function you want to call in SQL calls a procedure in a package, you must also provide a RESTRICT_REFERENCES pragma for that procedure. You can't call the procedure directly in SQL, but if it is going to be executed indirectly from within SQL, it still must follow the rules. 17.5.1.1 Pragma violation errors If your function violates its pragma, you will receive the PLS-00452 error. Suppose, for example, that the body of the company_financials package looks like this: CREATE OR REPLACE PACKAGE BODY company_financials IS FUNCTION company_type (type_code_in IN VARCHAR2) RETURN VARCHAR2 IS v_sal NUMBER; BEGIN SELECT sal INTO v_sal FROM emp WHERE empno = 1; RETURN 'bigone'; END; FUNCTION company_name (company_id_in IN company. company_id%TYPE) RETURN VARCHAR2 IS BEGIN UPDATE emp SET sal = 0; RETURN 'bigone'; END; END company_financials; / When I attempt to compile this package body I will get the following error: 3/4 PLS-00452: Subprogram 'COMPANY_TYPE' violates its associated pragma because the company_type function reads from the database and I have asserted the RNDS purity level. If I remove that silly SELECT statement, I will then receive this error: Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
11/4 PLS-00452: Subprogram 'COMPANY_NAME' violates its associated pragma because the company_name function updates the database and I have asserted the WNDS level. You will sometimes look at your function and say: "Hey, I absolutely do not violate my purity level. There is no UPDATE, DELETE, or UPDATE around." Maybe not. But there is a good chance that you are calling a built-in package or in some other way breaking the rules. 17.5.2 Asserting Purity Level with Package Initialization Section If your package contains an initialization section (executable statements after a BEGIN statement in the package body), you must also assert the purity level of that section. The initialization section is executed automatically the first time any package object is referenced. So if a packaged function is used in a SQL statement, it will trigger execution of that code. If the initialization section modifies package variables or database information, the compiler needs to know about that through the pragma. You can assert the purity level of the initialization section either explicitly or implicitly. To make an explicit assertion, use the following variation of the pragma RESTRICT_REFERENCES: PRAGMA RESTRICT_REFERENCES (package_name, WNDS, [, WNPS] [, RNDS] [, RNPS]) Instead of specifying the name of the function, you include the name of the package itself, followed by all the applicable state arguments. In the following argument I assert only WNDS and WNPS because the initialization section reads data from the configuration table and also reads the value of a global variable from another package (session_pkg.user_id). PACKAGE configure IS PRAGMA RESTRICT_REFERENCES (configure, WNDS, WNPS); user_name VARCHAR2(100); END configure; PACKAGE BODY configure IS BEGIN SELECT lname || ', ' || fname INTO user_name FROM user_table WHERE user_id = session_pkg.user_id; END configure; Why can I assert the WNPS even though I do write to the user_name package variable? The answer is that it's a variable from this same package, so the action is not considered a side effect. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
You can also implicitly assert the purity level of the package's initialization section by allowing the compiler to infer that level from the purity level(s) of all the pragmas for individual functions in the package. In the following version of the company package, the two pragmas for the functions allow the Oracle Server to infer a combined purity level of RNDS and WNPS for the initialization section. This means that the initialization section cannot read from the database and cannot write to a package variable. PACKAGE company IS FUNCTION get_company (company_id_in IN VARCHAR2) RETURN company%ROWTYPE; FUNCTION deactivate_company (company_id_in IN company. company_id%TYPE) RETURN VARCHAR2; PRAGMA RESTRICT_REFERENCES (get_company, RNDS, WNPS); PRAGMA RESTRICT_REFERENCES (deactivate_name, WNPS); END company; Generally, you are probably better off providing an explicit purity level assertion for the initialization section. This makes it easier for those responsible for maintaining the package to understand both your intentions and your understanding of the package. Previous: 17.4 Restrictions Oracle PL/SQL Next: 17.6 Column/ on PL/SQL Functions in Programming, 2nd Edition Function Name Precedence SQL 17.4 Restrictions on PL/SQL Book Index 17.6 Column/Function Name Functions in SQL Precedence 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.
Previous: 17.5 Calling Chapter 17 Next: 17.7 Realities: Packaged Functions in SQL Calling PL/SQL Functions in Calling PL/SQL Functions SQL in SQL 17.6 Column/Function Name Precedence If your function has the same name as a table column in your SELECT statement and it has no parameters, then the column takes precedence over the function. The employee table has a column named "salary." Suppose you create a function named salary as well: CREATE TABLE employee (employee_id NUMBER, ... , salary NUMBER, ...); FUNCTION salary RETURN NUMBER; Then a SELECT statement referencing salary always refers to the column and not the function: SELECT salary INTO calculated_salary FROM employee; If you want to override the column precedence, you must qualify the name of the function with the name of the schema that owns the function, as follows: SELECT scott.salary INTO calculated_salary FROM employee; This now executes the function instead of retrieving the column value. Previous: 17.5 Calling Oracle PL/SQL Next: 17.7 Realities: Packaged Functions in SQL Programming, 2nd Edition Calling PL/SQL Functions in SQL 17.5 Calling Packaged Book Index 17.7 Realities: Calling PL/ Functions in SQL SQL Functions in SQL Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
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.
Previous: 17.6 Column/ Chapter 17 Next: 17.8 Examples of Function Name Precedence Calling PL/SQL Functions in Embedded PL/SQL SQL 17.7 Realities: Calling PL/SQL Functions in SQL The ability to call PL/SQL functions in SQL has been around since Release 2.1, but in many ways (at least until Oracle8) it can still be considered "bleeding edge" technology. Why? q You must manually apply RESTRICT_REFERENCES pragmas to all of your code -- and you have to figure out where all those pragmas need to go. This process is described in a subsection below. q Functions execute outside of the read consistency model of the Oracle database (!). This issue is also explored below in a subsection below. q The overhead of calling a function from SQL remains high. The exact price you pay to call a function from within SQL (compared to, say, executing in-line SQL code) can be hard to pin down. It varies from computer to computer and even by instance or by the function being called; I have heard reports that range from an extra half-second to an astonishing additional 50 seconds (in that case, I suggested that they do some more analysis and debugging). Whatever the specific amount of time, the delay can be noticeable and you need to factor it into your design and test plans. q Tuning mechanisms such as EXPLAIN PLAN do not take into account the SQL that may be called inside functions called in your SQL statement. PL/SQL functions are ignored by the EXPLAIN PLAN facility. This makes it very difficult to come up with a comprehensive understanding of performance bottlenecks and tuning needs. q So much Oracle technology, especially that found in built-in packages, is declared "off limits" to functions in SQL. Just consider DBMS_OUTPUT. You can't use it in functions called in SQL. But ideally you will want to use those same functions in SQL and PL/SQL. Another issue may be that your standard debugging technique is to insert calls to a trace program at the beginning of each of your functions and procedures. Chances are that the trace program relies on DBMS_OUTPUT (or UTL_FILE or DBMS_PIPE or take your pick, they're all -- at least until Oracle8 -- off limits). Sorry! You will not be able to use that function in SQL. So you have to pull out some of the code from the function. As a result, you can end up with two versions of your code: one for PL/SQL and one for SQL. That is one nasty scenario for software developers. Let's examine two of these issues in more detail. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
17.7.1 Manual Application of Pragmas You must manually apply RESTRICT_REFERENCES pragmas to all of your code -- and you have to figure out where all those pragmas need to go. This process is often similar to a Sherlock Holmes plot. You compile a package and get a pragma violation error. This can happen because your program breaks a rule (like trying to change data) or because it calls other programs which break a rule. You notice in this case that your function calls five or six other functions or procedures, so you must apply pragmas to each of these. By doing so, you assert purity levels where none had been asserted before, raising more errors and in some cases significant architectural issues. For example, suppose that you suddenly have to apply a pragma to a procedure in package X and that package has an initialization section; you must then also pragma-tize the initialization section. A common practice in this section is to set up a PL/SQL table for in-memory manipulation of data. If you use any PL/SQL table methods to do this initialization, your pragma will fail. This can be a very frustrating exercise, at times leading to abandoning the effort to enable your function for execution in SQL. In my experience, you will want to identify in advance (as much as possible) those areas of your application which you will want to call in SQL. You will then strive to keep this code very "clean" and focused, with limited entanglements with other packages, and with an absolutely minimal use of built-in packaged functionality. Neither an easy nor a particularly desirable task. 17.7.2 Read Consistency Model Complications Yes, it is hard to believe, but quite true: unless you take special precautions, it is quite possible that your SQL query will violate the read consistency model of the Oracle RDBMS, which has been sacrosanct territory for years at Oracle. To understand this issue, consider the following query and the function it calls: SELECT name, total_sales (account_id) FROM account WHERE status = 'ACTIVE'; FUNCTION total_sales (id_in IN account.account_id%TYPE) RETURN NUMBER IS CURSOR tot_cur IS SELECT SUM (sales) total FROM orders WHERE account_id = id_in AND year = TO_NUMBER (TO_CHAR (SYSDATE, 'YYYY')); tot_rec tot_cur%ROWTYPE; BEGIN OPEN tot_cur; Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
FETCH tot_cur INTO tot_rec; RETURN tot_rec.total; END; The account table has five million active rows in it (a very successful enterprise!). The orders table has 20 million rows. I start the query at 11 a.m.; it takes about an hour to complete. At 10:45 a.m., somebody with the proper authority comes along, deletes all rows from the orders table and performs a commit. According to the read consistency model of Oracle, the session running the query should see all those deleted rows until the query completes. But the next time the total_sales function executes from within the query, it finds no order rows and returns NULL -- and will do so until the query completes. So if you are executing queries inside functions which are called inside SQL, you need to be acutely aware of read-consistency issues. If these functions are called in long-running queries or transactions, you will probably need to issue the following command to enforce read-consistency between SQL statements in the current transaction: SET TRANSACTION READ ONLY You will find more information about this command in Chapter 6, Database Interaction and Cursors. Working with functions in SQL is more difficult and more complicated than you might first imagine. Big surprise. You can say that about almost every aspect of Oracle technology, especially the newer additions to the stable. I hope that over time Oracle will make our lives easier (there are definitely some improvements in Oracle 8.0). Ultimately we need a utility that allows a developer to "point" to a function and request, "make that function usable in SQL." And that utility will then apply all the pragmas or at least generate a report of the steps necessary to get the job done. We can dream, can't we? Previous: 17.6 Column/ Oracle PL/SQL Next: 17.8 Examples of Function Name Precedence Programming, 2nd Edition Embedded PL/SQL 17.6 Column/Function Name Book Index 17.8 Examples of Embedded Precedence PL/SQL 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.
Previous: 17.7 Realities: Chapter 17 Next: V. New PL/SQL8 Calling PL/SQL Functions Calling PL/SQL Functions in Features in SQL SQL 17.8 Examples of Embedded PL/SQL The more you think about stored functions in SQL, the more you come up with ways to put them to use in every single one of your applications. To prod your creativity and get you started, here are a number of examples of the ways stored functions in SQL can change the way you build Oracle-based systems. 17.8.1 Encapsulating Calculations In just about any and every application, you will need to perform the same calculations over and over again. Whether it is a computation of net present value, mortgage balance, the distance between two points on a Cartesian plane, or a statistical variance, with native SQL you have to recode those computations in each of the SQL statements in which they are needed. You can pay a big price for this kind of redundancy. The code that implements your business rules is repeated throughout the application. Even if the business rule doesn't change, the way you should implement the rule is almost sure to require modification. Worse than that, the business rule itself might evolve, which could necessitate fairly significant alterations. To solve this problem, you can hide or encapsulate all of your formulas and calculations into stored functions. These functions can then be called from within both SQL statements and also PL/SQL programs. One fine example of the value of encapsulated calculations arose when an insurance company needed to perform date-based analyses on its accounts. The last day of the month is, of course, a very important date for most financial institutions. To manipulate dates, the company's IS department planned to make use of the built-in LAST_DAY function to obtain the last day of the month, and ADD_MONTHS to move from one month to the next. It soon uncovered a very interesting nuance to the way ADD_MONTHS worked: if you pass a day to ADD_MONTHS which is the last day in the month, SQL always returns the last day in the resulting month, regardless of the number of actual days in each of the months. In other words: Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
ADD_MONTHS ('28-FEB-1994', 2) ==> 30-APR-1993 This approach might make sense for some applications and queries. The requirement at the insurance company, however, was that when you move a month, you must always land on the same day of the month (or the last day, if the original month's day was past the last day of the target month). Without stored functions, the SQL required to perform this calculation is as follows: SELECT DECODE (payment_date, LAST_DAY (payment_date), LEAST (ADD_MONTHS (payment_date, 1), TO_DATE (TO_CHAR (ADD_MONTHS (payment_date, 1), 'MMYYYY') || TO_CHAR (payment, 'DD'), 'MMYYYYDD')), ADD_MONTHS (payment_date, 1)) FROM premium_payments; which may be read as, "If the last payment date falls on the last day of the month, then return as the next payment date the earliest of either the result of adding one month to payment date (using ADD_MONTHS) or the same day in the new month as the day in the month of the last payment date. If the last payment was not made on the last day of the month, simply use ADD_MONTHS to get the next payment date." Not only is that difficult to understand, but it required three different calls to the ADD_MONTHS built-in. And remember that this complex SQL would have to be repeated in every SELECT list where ADD_MONTHS was used to increment or decrement dates. You can well imagine how happy the programmers in this company became when they installed Oracle Server Version 7.1 and were able to use the following function inside their SQL statements (for a full explanation of the function's logic, see Chapter 12, Date Functions): FUNCTION new_add_months (date_in IN DATE, months_shift IN NUMBER) RETURN DATE IS return_value DATE; BEGIN return_value := ADD_MONTHS (date_in, months_shift); IF date_in = LAST_DAY (date_in) THEN return_value := LEAST (return_value, TO_DATE (TO_CHAR (return_value, 'MMYYYY') Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
|| TO_CHAR (date_in, 'DD') , 'MMYYYYDD')); END IF; RETURN return_value; END new_add_months; With the stored function, the SELECT statement to obtain the next payment date is simply: SELECT new_add_months (payment_date,1) FROM premium_payments; The more you look through your SQL statements, the more opportunities you find for stored functions that can hide calculations and therefore improve the long-term stability of your code. While it is unlikely that you will have the time and resources to go back and rewrite wholesale the SQL underpinnings of your application, you can at least build the functions and roll them into any new product development. 17.8.2 Combining Scalar and Aggregate Values This simple question is hard to answer in SQL: "Show me the name and salary of the employee who has the highest salary in each department, as well as the total salary for that person's department." Broken into two separate queries, this question poses no problem. Here is the first part: SELECT department_id, last_name, salary FROM employee E1 WHERE salary = (SELECT MAX (salary) FROM employee E2 WHERE E.department_id = E2. department_id) GROUP BY department_id; and here is the second part: SELECT department_id, SUM (salary) FROM employee ORDER BY department_id; However, I cannot very easily combine them since that would require listing and obtaining both scalar (single row) and aggregate (across multiple rows) values from the same table. The following SELECT list contains the information I want to display. How could I construct my FROM, WHERE, and GROUP BY clauses to show both an individual's salary and the departmental total? SELECT department_id, last_name, salary, SUM (salary) Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
FROM ...? WHERE ...? GROUP BY ...? The most straightforward solution prior to Release 2.1 of PL/SQL was to create a view that "presummarized" the salary for each department: CREATE VIEW dept_salary AS SELECT department_id, SUM (salary) total_salary FROM employee GROUP BY department_id; Now, with this view, I can get at my answer with a single SQL statement as follows: SELECT E.department_id, last_name, salary, total_salary FROM employee E, dept_salary DS WHERE E.department_id = DS.department_id AND salary = (SELECT MAX (salary) FROM employee E2 WHERE E.department_id = E2. department_id); This doesn't seem like such a bad solution, except that you have to create a customized view each time you want to perform this kind of calculation. In addition, this SQL is far less than straightforward for many programmers. A better solution is to make use of a stored function in SQL. Instead of creating a view, create a function that performs exactly the same calculation, but this time only for the specified department: FUNCTION total_salary (dept_id_in IN department. department_id%TYPE) RETURN NUMBER IS CURSOR grp_cur IS SELECT SUM (salary) FROM employee WHERE department_id = dept_id_in; return_value NUMBER; BEGIN OPEN grp_cur; FETCH grp_cur INTO return_value; CLOSE grp_cur; RETURN return_value; Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
END; In this version I outer-join the department and employee tables. This way if a department does not exist (bad department ID number), I return NULL. If a department has no employees, I return 0; otherwise, I return the total salary in that department. Now my query does not need to join to a view containing a GROUP BY. Instead, it calls the total_salary function, passing the employee's department ID number as a parameter: SELECT E.department_id, last_name, salary, total_salary (E.department_id) FROM employee E WHERE salary = (SELECT MAX (salary) FROM employee E2 WHERE E.department_id = E2. department_id); The resulting SQL statement is not only easier to read; it also executes faster, especially for larger tables. I could simplify the SQL statement further by creating a function that returns the maximum salary in a particular department. The above SELECT would then become simply: SELECT department_id, last_name, salary, total_salary (department_id) FROM employee E WHERE salary = max_sal_in_dept (department_id); 17.8.3 Replacing Correlated Subqueries You can also use stored functions in a SQL statement to replace correlated subqueries. A correlated subquery is a SELECT statement inside the WHERE clause of a SQL statement (SELECT, INSERT, or DELETE) which is correlated (or makes reference) to one or more columns in the enclosing SQL statement. In the preceding section I used a correlated subquery to determine the employee who receives the highest salary in each department: SELECT E.department_id, last_name, salary, total_salary (E.department_id) FROM employee E WHERE salary = (SELECT MAX (salary) FROM employee E2 WHERE E.department_id = E2. department_id); Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
The last three lines in the query contain a SELECT statement matching the department ID number for the "inner" employee (E2) to the department ID number for the "outer" employee table (E1). The inner query is executed once for every row retrieved in the outer query. The correlated subquery is a very powerful feature in SQL, since it offers the equivalent of a procedural language's nested loop capability, as in: LOOP LOOP END LOOP; END LOOP; Two drawbacks with a correlated subquery are: q The logic can become fairly complicated q The resulting SQL statement can be difficult to understand and follow You can use a stored function in place of a correlated subquery to address these drawbacks; in the above example, I would want a function that calculates the highest salary in a given department: FUNCTION max_salary (dept_id_in IN department. department_id%TYPE) RETURN NUMBER IS CURSOR grp_cur IS SELECT MAX (salary) FROM employee WHERE department_id = dept_id_in; return_value NUMBER; BEGIN OPEN grp_cur; FETCH grp_cur INTO return_value; CLOSE grp_cur; RETURN return_value; END; I can now use both total_salary and max_salary in my SELECT statement that says, "Show me the name and salary of the employee who has the highest salary in each department, as well as the total salary for that person's department." SELECT E.department_id, last_name, salary, total_salary (department_id) FROM employee WHERE salary = max_salary (department_id); Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Compare that simple, self-documenting piece of SQL to the version requiring a view and correlated subquery: CREATE VIEW dept_salary AS SELECT department_id, SUM (salary) total_salary FROM employee GROUP BY department_id; SELECT E.department_id, last_name, salary, total_salary FROM employee E, dept_salary DS WHERE E.department_id = DS.department_id AND salary = (SELECT MAX (salary) FROM employee E2 WHERE E.department_id = E2. department_id); and I am sure you will agree that stored functions in SQL can make your life much easier. You may have noticed that the total_salary function from the previous section and the max_salary from this section look very similar. The only difference between the two is that the cursor in total_salary uses the SUM group function and the cursor in max_salary uses the MAX group function. If you are as fanatical about consolidating your code into the smallest possible number of distinct "moving parts," you might consider a single function that returns a different group-level statistic for a department based on a second parameter, as follows: FUNCTION salary_stat (dept_id_in IN department.department_id%TYPE, stat_type_in IN VARCHAR2) RETURN NUMBER IS v_stat_type VARCHAR2(20) := UPPER (stat_type_in); CURSOR grp_cur IS SELECT SUM (salary) sumsal, MAX (salary) maxsal, MIN (salary) minsal, AVG (salary) avgsal, COUNT (DISTINCT salary) countsal, FROM employee WHERE department_id = dept_id_in; grp_rec grp_cur%ROWTYPE; retval NUMBER; Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
BEGIN OPEN grp_cur; FETCH grp_cur INTO grp_rec; CLOSE grp_cur; IF v_stat_type = 'SUM' THEN retval := grp_rec.sumsal; ELSIF v_stat_type = 'MAX' THEN retval := grp_rec.maxsal; ELSIF v_stat_type = 'MIN' THEN retval := grp_rec.minsal; ELSIF v_stat_type = 'COUNT' THEN retval := grp_rec.countsal; ELSIF v_stat_type = 'AVG' THEN retval := grp_rec.avgsal; END IF; RETURN retval; END; The overhead of adding these additional expressions in the SELECT list -- and the processing of the IF statement -- is negligible. With this new, generic utility, my request for salary analysis shown above now becomes: SELECT E.department_id, last_name, salary, salary_stat (department_id, 'sum') FROM employee WHERE salary = salary_stat (department_id, 'max'); If I ever have to change the SQL required to obtain departmental-level statistics for salary, I have to upgrade only this single function. 17.8.4 Replacing DECODEs with IF Statements The DECODE function offers IF-like capabilities in the nonprocedural SQL environment provided by the Oracle Server. You can use the DECODE syntax to create matrix reports with a fixed number Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
of columns and also perform complex IF-THEN-ELSE logic within a query. The downside to DECODE is that it can be difficult to write and very difficult to maintain. Consider the following example of DECODE to determine whether a date is within the prescribed range and, if it is, add to the count of rows that fulfill this requirement: SELECT FC.year_number, SUM (DECODE (GREATEST (ship_date, FC.q1_sdate), ship_date, DECODE (LEAST (ship_date, FC. q1_edate), ship_date, 1, 0), 0)) Q1_results, SUM (DECODE (GREATEST (ship_date, FC.q2_sdate), ship_date, DECODE (LEAST (ship_date, FC. q2_edate), ship_date, 1, 0), 0)) Q2_results, SUM (DECODE (GREATEST (ship_date, FC.q3_sdate), ship_date, DECODE (LEAST (ship_date, FC. q3_edate), ship_date, 1, 0), 0)) Q3_results, SUM (DECODE (GREATEST (ship_date, FC.q4_sdate), ship_date, DECODE (LEAST (ship_date, FC. q4_edate), ship_date, 1, 0), 0)) Q4_results FROM orders O, fiscal_calendar FC GROUP BY year_number; The result set for this query might look like this: YEAR NUMBER Q1 RESULTS Q2 RESULTS Q3 RESULTS Q4 RESULTS ------------ ---------- ---------- ---------- ---------- 1993 12000 14005 22000 40000 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.