Oracle_SELECT

Meer zoals dit

sentences

door leonardo contreras

Linear Programming

door Reina P. Castro

verbs

door Marina Kostina

Playing Teaching e Guitar

door Steve Dsyon

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