![]() The anchor member must appear before the recursive member, and it cannot reference query_name. A recursive subquery_factoring_clause must contain two query blocks: the first is the anchor member and the second is the recursive member. If a subquery_factoring_clause refers to its own query_name in the subquery that defines it, then the subquery_factoring_clause is said to be recursive. For recursive subquery factoring, the query name is even visible to the subquery that defines the query name itself. The query name is visible to the main query and to all subsequent subqueries. You can specify this clause in any top-level SELECT statement and in most types of subqueries. The search_clause and cycle_clause are valid only for recursive subquery factoring but are not required. The column aliases following the query_name and the set operators separating multiple subqueries in the AS clause are valid and required for recursive subquery factoring. Refer to " Database Object Naming Rules" for information on database object names. The query_name is subject to the same naming conventions and restrictions as database schema objects. Oracle Database optimizes the query by treating the query_name as either an inline view or as a temporary table. ![]() You can then reference the subquery block multiple places in the query by specifying query_name. The subquery_factoring_clause lets you assign a name ( query_name) to a subquery block. " Using a PL/SQL Function in the WITH Clause: Examples" Oracle Database PL/SQL Language Reference for syntax and restrictions for function_declaration and procedure_declaration. The WITH_PLSQL hint only enables you to specify the WITH plsql_declarations clause within the statement. If the top-level statement is a DELETE, MERGE, INSERT, or UPDATE statement, then it must have the WITH_PLSQL hint. If the top-level statement is a SELECT statement, then it must have either a WITH plsql_declarations clause or the WITH_PLSQL hint. ![]() If the query in which you specify this clause is not a top-level SELECT statement, then the following rules apply to the top-level SQL statement that contains the query: For the purposes of name resolution, these function names have precedence over schema-level stored functions. You can then reference the PL/SQL functions in the query in which you specify this clause, as well as its subqueries, if any. The plsql_declarations clause lets you declare and define PL/SQL functions and procedures. Subquery blocks (using the subquery_factoring_clause) PL/SQL procedures and functions (using the plsql_declarations clause) Use the with_clause to define the following: In addition, either you must have FLASHBACK object privilege on the objects in the select list, or you must have FLASHBACK ANY TABLE system privilege. To issue an Oracle Flashback Query using the flashback_query_clause, you must have the READ or SELECT privilege on the objects in the select list. To specify the FOR UPDATE clause, the preceding prerequisites apply with the following exception: The READ and READ ANY TABLE privileges, where mentioned, do not allow you to specify the FOR UPDATE clause. The READ ANY TABLE or SELECT ANY TABLE system privilege also allows you to select data from any table or any materialized view or the base table of any view. Whoever owns the schema containing the view must have the READ or SELECT privilege on the base tables. The view must be in your own schema or you must have the READ or SELECT privilege on the view, and For you to select data from a table or materialized view, the table or materialized view must be in your own schema or you must have the READ or SELECT privilege on the table or materialized view.įor you to select rows from the base tables of a view:
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |