Analytic Functions are designed to address such problems as "Calculate a running total", "Find percentages within a group", "Top-N queries", "Compute a moving average" and many more.
Analytic functions compute an aggregate value based on a group of rows. They differ from aggregate functions in that they return multiple rows for each group. The group of rows is called a window and is defined by the analytic clause. For each row, a "sliding" window of rows is defined. The window determines the range of rows used to perform the calculations for the "current row". Window sizes can be based on either a physical number of rows or a logical interval such as time.
Analytic functions are the last set of operations performed in a query except for the final ORDER BY clause. All joins and all WHERE, GROUP BY, and HAVING clauses are completed before the analytic functions are processed. Therefore, analytic functions can appear only in the select list or ORDER BY clause.
The Syntax
The Syntax of analytic functions is rather straightforward in appearance
Analytic-Function (<Argument>, <Argument>,...)
OVER (
<Query-Partition-Clause>
<Order-By-Clause>
<Windowing-Clause>
)
• Analytic-Function
Specify the name of an analytic function, Oracle actually provides many analytic functions such as AVG, CORR, COVAR_POP, COVAR_SAMP, COUNT, CUME_DIST, DENSE_RANK, FIRST, FIRST_VALUE, LAG, LAST, LAST_VALUE, LEAD, MAX, MIN, NTILE, PERCENT_RANK, PERCENTILE_CONT, PERCENTILE_DISC, RANK, RATIO_TO_REPORT, STDDEV, STDDEV_POP, STDDEV_SAMP, SUM, VAR_POP, VAR_SAMP, VARIANCE.
• Arguments
Analytic functions take 0 to 3 arguments.
• Query-Partition-Clause
The PARTITION BY clause logically breaks a single result set into N groups, according to the criteria set by the partition expressions. The words "partition" and "group" are used synonymously here. The analytic functions are applied to each group independently; they are reset for each group.
• Order-By-Clause
The ORDER BY clause specifies how the data is sorted within each group (partition). This will definitely affect the outcome of any analytic function.
• Windowing-Clause
The windowing clause gives us a way to define a sliding or anchored window of data, on which the analytic function will operate, within a group. This clause can be used to have the analytic function compute its value based on any arbitrary sliding or anchored window within a group.
Commit processing is the way Form Builder attempts to make the data in the database identical to the data in the form. Form Builder's normal cycle of operation is:
1 Read records from the database.
2 Allow the end user to make tentative insertions, updates, and deletions. The tentative changes appear only in the form. The database remains unchanged.
3 Post changes to the database. Form Builder does all of its remaining processing and sends the data to the database. After posting the data, Form Builder can only roll back the changes (via the [Clear Form] function key or CLEAR_FORM built-in) or commit them.
4 Form Builder commits the posted changes. They become permanent changes to the database.
The term commit processing refers to steps 3 and 4 of the above cycle. Normally these steps occur together. In response to the [Commit] key or invocation of the COMMIT_FORM built-in, Form Builder firsts posts, then commits the data to the database.
Posting can occur separately before committing. End users cannot issue posting commands, but triggers can invoke the POST built-in.
Note: Form Builder does not support a commit that does not include the normal Form Builder commit processing. For example, Form Builder does not support a commit issued from an Oracle Precompiled user exit or from a stored procedure.
Processing inserts, updates, and deletes
Posting consists of sending tentative changes from the form to the database. These are records that have been marked for insertion, update, or deletion since the last post. During posting, Form Builder processes inserts, updates, and deletes for all blocks in a form. Form Builder has a standard way to process these changes. Triggers provide a flexible mechanism for altering the standard behavior.
Insert: An insert is the pending insertion of a row in the database. Each insert has an associated SQL statement, which Form Builder executes when it posts the insert. The statement has the following form:
INSERT INTO table [(column, column, . . .)] VALUES (value, value, . . .);
Table The name of the base table for the current block.
Column A column corresponding to a base table item. If an item is a derived column, it does not appear in the column clause.
Value The value to insert into the corresponding column.
Update: An update is the pending update of an existing row in the database. Each update has an associated SQL statement, which Form Builder executes when it posts the update. The statement has the following form:
UPDATE table SET (column=value, column=value, . . .) WHERE ROWID=rowid_value;
Table The name of the base table for the current block.
Column A column corresponding to a base table item. If an item is a derived column, it does not appear in the column clause.
Value The value to update the corresponding column.
Rowid_value The ROWID value for the row Form Builder is updating.
Note:
The WHERE clause specifies only a ROWID value. This identifies the unique row that the database should update. Form Builder uses the ROWID construct only when the block's Key Mode property has the value Unique_Key (the default).
If an end user does not have update privileges for a column, and the block's Enforce Column Security property has the value Yes, Form Builder does not include the column in the UPDATE statement. Different end users can have different UPDATE statements, depending on their privileges, but the statement remains unchanged during an end user's Form Builder session.
Delete: A delete is the pending deletion of a row in the database. Each delete has an associated SQL statement, which Form Builder executes when it posts the delete. The statement has the following form:
DELETE FROM table WHERE ROWID=rowid_value;
Table The name of the base table for the current block.
Rowid_value The ROWID value for the row Form Builder is deleting.
Note: The WHERE clause specifies only a ROWID value. This identifies the unique row that the database should delete. Form Builder does not use the ROWID value for non-Oracle data sources.
Caution: If a commit fails, the ROWID value may not have a null value. Use record status rather than the ROWID value to test for success or failure of the commit.
When commit processing occurs
Form Builder performs commit processing when
A trigger or the [Commit] key invokes the COMMIT_FORM built-in.
Database items in the current block have changed since the last commit, and a trigger invokes the CLEAR_BLOCK built-in (with the DO_COMMIT argument).
Database items in the form have changed since the last commit, and a trigger invokes the CLEAR_FORM built-in (with the DO_COMMIT argument).
An end user answers Yes to the alert that asks
Do you want to commit the changes you have made?
The alert appears as a result of any of the following:
Database items in the current block have changed since the last commit, and any of the following events occurs:
CLEAR_BLOCK (with the ASK_COMMIT argument or with no argument)
COUNT_QUERY
ENTER_QUERY
EXECUTE_QUERY
NEW_FORM
Database items in any block of the form have changed since the last commit, and any of the following events occurs:
CLEAR_FORM (with the ASK_COMMIT argument or with no argument)
Leave the Form
Note: When a PL/SQL block issues a database commit from within Form Builder (via the SQL COMMIT statement), Form Builder commit processing occurs as if the COMMIT_FORM built-in had been invoked.
Changing data during commit processing
Commit processing performs validation and can fire triggers. As a result, a commit event can change database items. For some triggers Form Builder attempts to commit those changes to the database during the current commit event.
Caution: Form Builder can commit un-validated database changes made by the PRE-DELETE, PRE-INSERT, and PRE-UPDATE triggers.
For example, assume that a PRE-INSERT trigger selects a value into a base table item.
If Form Builder has not already processed the affected record's updates, it commits the changes during the current commit event without validating them.
If Form Builder has already processed the affected record's updates, it does not commit the changes, but it marks the records as having come from the database. Subsequently attempting to update the record before it is committed causes an error.
Caution: Form Builder can commit un-validated database changes made by the POST-COMMIT, POST-DELETE, POST-INSERT, and POST-UPDATE triggers.
For example, assume that a POST-INSERT trigger selects a value into a base table item.
If the affected record is in a block that the commit has processed or is processing, Form Builder does not commit the changes during the current commit event. If the commit succeeds, Form Builder marks all items and records as Valid. This results in an error during subsequent processing.
If the affected record is in a block that the commit has not yet processed, Form Builder validates the changes and commits them during the current commit event.
Replacing standard commit processing
The ON-INSERT, ON-UPDATE, and ON-DELETE triggers replace standard commit processing.
