Oracle_SELECT

Meer zoals dit

Brainstorm of Personal Art Ed Philosphy leading to 6yr Outcome

door Wei Ling Loi

Conceptos SQL

door Rafael Rojas

Types of sentences

door Yorledys Herrera Regino

Equality

door Madisen Schram

row_pattern_aggregate_func

row_pattern_rec_func

row_pattern_navigation_func

row_pattern_nav_compound

row_pattern_nav_physical

row_pattern_nav_logical

row_pattern_mach_num_func

row_pattern_classifier_func

model_column

order_by_clause

row_limiting_clause

QUERY_BLOCK

table_reference join_clause (join_clause)

select_list

with_clause

FOR UPDATE

SELECT e.employee_id, e.salary, e.commission_pct

FROM employees e, departments d

WHERE job_id = 'SA_REP'

AND e.department_id = d.department_id

AND location_id = 2500

ORDER BY e.employee_id

FOR UPDATE;

SELECT e.employee_id, e.salary, e.commission_pct

FROM employees e JOIN departments d

USING (department_id)

WHERE job_id = 'SA_REP'

AND location_id = 2500

ORDER BY e.employee_id

FOR UPDATE OF e.salary;

SELECT employee_id FROM (SELECT * FROM employees)

FOR UPDATE OF employee_id;

SELECT employee_id FROM (SELECT employee_id+1 AS employee_id FROM employees)

FOR UPDATE;

Since the FOR UPDATE clause is not supported on views, anything that prevents view merging, such as the NO_MERGE hint, parameters that disallow view merging, or something in the query structure that prevents view merging, will result in an ORA-02014 error.

In the following example, the GROUP BY statement prevents view merging, which causes an error:

SELECT avgsal

FROM (SELECT AVG(salary) AS avgsal FROM employees GROUP BY job_id)

FOR UPDATE;

FROM (SELECT AVG(salary) AS avgsal FROM employees GROUP BY job_id)

*

ERROR at line 2:

ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc.

grouping_expression_list

expression_list

model_rules_clause

cell_assignment

model_column_clause

where_clause

hierarchical_query_clause

condition

single_column_loop

hint

multi_column_loop

model_iterate_clause

group_by_clause

grouping_sets_clause

rollup_cube_clause

model_clause

main_model

reference_model

return_rows_clause

cell_reference_options

FROM

WITH

subquery_factoring_clause

QUERY_NAME AS (SUBQUERY) SEARCH_CLAUSE CYCLE_CLAUSE

CYCLE_CLAUSE

SEARCH_CLAUSE

SUBQUERY

plsql_declarations

FUNCTION...;

PROCEDURE...;

row_pattern_clause

row_pattern_definition_list

row_pattern_definition

row_pattern_subset_clause

row_pattern_subset_item

row_pattern

row_pattern_term

row_pattern_factor

row_pattern_primary

row_pattern_permute

row_pattern_quantifier

row_pattern_skip_to

row_pattern_rows_per_match

row_pattern_measures

row_pattern_measure_column

row_pattern_order_by

row_pattern_partition_by

SELECT_LIST -----------------> FROM

(join_clause)

join_clause

cross_outer_apply_clause

outer_join_clause

outer_join_type

query_partition_clause

inner_cross_join_clause

table_reference

t_alias

pivot_clause unpivot_clause row_pattern_clause

table_reference PIVOT [ XML ]

( aggregate_function ( expr ) [[AS] alias ]

[, aggregate_function ( expr ) [[AS] alias ] ]...

pivot_for_clause

pivot_in_clause

)

FOR { column

| ( column [, column]... )

}

IN ( { { { expr

| ( expr [, expr]... )

} [ [ AS] alias]

}...

| subquery

| ANY [, ANY]...

}

)

table_reference UNPIVOT [ {INCLUDE | EXCLUDE} NULLS ]

( { column | ( column [, column]... ) }

pivot_for_clause

unpivot_in_clause

)

IN

( { column | ( column [, column]... ) }

[ AS { literal | ( literal [, literal]... ) } ]

[, { column | ( column [, column]... ) }

[ AS {literal | ( literal [, literal]... ) } ]

]...

)

table_reference UNPIVOT [ {INCLUDE | EXCLUDE} NULLS ]

( { column | ( column [, column]... ) }

pivot_for_clause

unpivot_in_clause

)

IN

( { column | ( column [, column]... ) }

[ AS { literal | ( literal [, literal]... ) } ]

[, { column | ( column [, column]... ) }

[ AS {literal | ( literal [, literal]... ) } ]

]...

)

MATCH_RECOGNIZE {

[ row_pattern_partition_by ]

[ row_pattern_order_by ]

[ row_pattern_measures ]

[ row_pattern_rows_per_match ]

[ row_pattern_skip_to ]

PATTERN (row_pattern)

[ row_pattern_subset_clause ]

DEFINE row_pattern_definition_list

}

unpivot_clause

pivot_clause

FLASHBACK_QUERY_CLAUSE

ONLY(query_table_expression) query_table_expression

subquery_restriction_clause

partition_extention_clause

sample_clause

DISTINCT UNIQUE ALL