Oracle -- model usage, oraclemodel

Oracle -- model usage, oraclemodel

1. What is a model statement?

The model statement is a new feature of Oracle10g,
The MODEL clause in SQL is an important new function introduced by Oracle 10g Database for the enterprise intelligence field. This clause is of great help to accountants who like to get out of Oracle and then put it into workbooks for analysis. The purpose of the "MODEL" clause in SQL is to allow SQL statements to create multi-dimensional arrays from normal SELECT results, and then calculate rows or arrays on the SQL spreadsheet.
The MODEL clause defines a multi-digit group by ing columns in a query into three groups. The three groups are partition, dimension, and measures)
Process the SQL result set. The execution order is after Having.

Ii. Use Cases of model

Typical use cases of model.
1. Add rows in total
2. row-column transformation
3. Use the front and back rows of the current row
4. Loop execution of the RegExp_Replace Function

Iii. model syntax

MODEL [RETURN [UPDATED | ALL] ROWS][reference models][PARTITION BY (<cols>)]DIMENSION BY (<cols>)MEASURES (<cols>) [IGNORE NAV] | [KEEP NAV][RULES[UPSERT | UPDATE][AUTOMATIC ORDER | SEQUENTIAL ORDER]

Example:

Create table a asselect 'lottu' AS vname, 1 AS vals FROM dual; SELECT vname, vals from amodel -- partition by () can ignore dimension by (vals) MEASURES (vname) RULES (vname [1] = '20140901 ');

Output result:
0924 1

Explanation parameters:

Model: The keyword of the model statement. required.
Partition by: groups by ××
Dimension by n dimension: the meaning of a dimension. It can be understood as an array index and must be.
Measures: Specifies the column as an array.
Rules: description of various operations on Arrays

4. Meaning of model return updated rows
Note: The default behavior of rules is to update the rule if it exists and append the rule if it does not exist. If model return updated rows is used, the row updated or inserted by rules is displayed.
New rows are no longer used as SQL results

INSERT INTO A VALUES ('LI',2);SELECT vname,vals FROM AMODEL RETURN UPDATED ROWSDIMENSION BY(vals)MEASURES(vname)RULES (vname[2]='0924');

5. Examples:

For example, the data is as follows:

CREATE TABLE B(p_id NUMBER,p_year Varchar2(5),p_val NUMBER);INSERT INTO B VALUES (1001,'2011',25); INSERT INTO B VALUES (1001,'2012',35); INSERT INTO B VALUES (1001,'2013',65); INSERT INTO B VALUES (1001,'2014',95); INSERT INTO B VALUES (1002,'2011',25); INSERT INTO B VALUES (1002,'2012',55); INSERT INTO B VALUES (1002,'2013',75); INSERT INTO B VALUES (1002,'2014',95); 

1. Predicted 2015 of revenue is the sum of the previous two years.

SELECT * FROM B MODEL RETURN UPDATED ROWS PARTITION BY (p_id) DIMENSION BY (p_year) MEASURES (p_val) RULES (p_val['2015']=p_val['2014']+p_val['2013']);

2. forecast the operating income data for 2015. Assume that 2015 of the company's operating income in 1001 is the sum of the previous two years, and 1002 of the company's data in 2015 is twice that in the previous year. The MODEL clause is as follows:

SELECT * FROM B MODEL RETURN UPDATED ROWS DIMENSION BY (p_id,p_year) MEASURES (p_val) RULES (p_val[1001,'2015']=p_val[1001,'2013']+p_val[1001,'2014'],        P_val[1002,'2015']=2 * p_val[1002,'2014']);

3. Use between and to return data units in a specific range.

 SELECT * FROM B MODEL RETURN UPDATED ROWS PARTITION BY (p_id) DIMENSION BY (p_year) MEASURES (p_val) RULES (p_val['2015']=sum(p_val)[p_year BETWEEN '2013' AND '2014']);

4. Use the for in statement to return a specific range

SELECT * from B MODEL RETURN UPDATED ROWS PARTITION BY (p_id) DIMENSION BY (p_year) MEASURES (p_val) RULES (p_val ['000000'] = sum (p_val) [for p_year in ('20140901', '20160901')]);
-- If p_year is a numerical value, you can also use the syntax for year from 2013 to 2014 increment 1. If it is another type,
You can also use the in clause to query the strings, for example, for p_year in (select year from B)

5. Use ANY and is any to access all data units. Use ANY and is any to access all data units in the array. ANY and location tags are used together, is any and symbol tags are used together
For example, the estimated operating income data for 2017 is the sum of all years;

SELECT * from B MODEL RETURN UPDATED ROWS PARTITION BY (p_id) DIMENSION BY (p_year) MEASURES (p_val) RULES (p_val ['000000'] = SUM (p_val) [ANY]); -- or any can be changed to p_year is any.

6. Use CURRENTV () to obtain the current value of a dimension.
Example 2

SELECT * FROM B MODEL RETURN UPDATED ROWS DIMENSION BY (p_id,p_year) MEASURES (p_val) RULES (p_val[1001,'2015']=p_val[currentv(),'2013']+p_val[currentv(),'2014'], P_val[1002,'2015']=2 * p_val[currentv(),'2014']);

7. Why should I use it? What are the advantages? See the following example.
Recently I saw a problem in the group. I found p_product with p_color 'red' and more p_value than 'Blue '.
This question is not difficult; you can use decode; or case when to determine the blue p_value as negative; then use the group function sum.
If you use the model, do you think there is a sense of being tall.

select p_roduct,p_value (select * from tb  model return updated rows partition by (p_product) dimension by (p_color) measures (p_value) rules (p_value['red-bule']=p_value['red']-p_value['blue']))  from p_vlaue > 0;

Conclusion: The functions are powerful and the syntax is too complex,

Document-Chapter 9 <pro oracle SQL>.

Oracle type usage

% Type is a variable definition method.

V_StudentID students. id % type indicates that the variable v_StudentID has the same data type and length as the id field in students. Once the data type of the id field in students changes, the variable v_StudentID also changes.

Oracle update usage

Update [Table name] set [name] = 'name', [gender] = 'gender' where [account] = 'account ';
[]: It contains the field name or table name.
'': Content.