Shulou(Shulou.com)06/02 Report--

If the function often produces a definite record for the above situation, you can use the

Use DETERMINISTIC in the function declaration so that ORACLE automatically caches these recordsets in memory, if you are not sure

The result will be uncertain.

1. Concept and meaning

1, grammar

CREATE OR REPLACE FUNCTION SCHEMA.FUNTION_NAME

(ARGUMENT IN NOC0PY DATATYPE)

RETURN DATETYPE

DETERMINISTIC

IS

BEGIN

END

2, specify that DETERMINISTIC is used for procedures that call the same parameter and the same value multiple times and return the same result

3, if you are in function-based index-related expressions or queries related to materialized views of REFRESH FAST and ENABLE QUERY REWRITE

The DETERMINISTIC keyword must be specified

4, if you later change the definition of the above expression, you must manually rebuild the materialized view or the function-based index

5. If a function uses package variables, or the access to the database can affect the result set of the function, do not use DETERMINISTIC

6. Semantic rules for using DETERMINISTIC statements, which here refer to defining or declaring rather than using

1, can be in the top-level subroutine, in the package

2, can be in the package specification, that is, the package-level subroutine, but not in the package body; refers to the subroutine declaration of the package

3, cannot be in a private subroutine (subroutine: inside another subroutine or inside a package), that is, a package or a subroutine in another subroutine

4, a subroutine with DETERMINISTIC can call another subroutine.

Regardless of whether the called subroutine declares DETERMINISTIC or not

2, example

1Magnum create OR REPLACE FUNCTION text_length (a CLOB)

RETURN NUMBER DETERMINISTIC IS

BEGIN

RETURN DBMS_LOB.GETLENGTH (a)

END; 2, an example written by myself

1SQL > desc t_deterministic

Is the name empty? Types

-

A CHAR (2)

SQL > select * from t_deterministic

A

--

twelve

Ab

2, create or replace function func_deterministic (a_len t_deterministic.a%type 2 return number deterministic is

3 v_len number

4 begin

5 select length (a) into v_len from t_deterministic where a=a_len

6 return v_len

7 * end

SQL > /

The function has been created.

3Jing SQL > select func_deterministic ('ab') from tweaking, purposeful, witty-query is NULL

FUNC_DETERMINISTIC ('AB')

-

SQL > select func_deterministic ('12') from tasking results are available.

FUNC_DETERMINISTIC ('12')

-

twelve

3, the difference between adding it and not adding it

4, which statements can have DETERMINISTIC, in this case, FUNCTION,PACKAGE,PACKAGE BODY,PROCEDURE,VIEW

1Perfect create procedure process cannot have DETERMINISTIC

2Grammar reference for Oracle create OR REPLACE PACKAGE. Database PL/SQL User's Guide and Reference

Test package specification

It has been tested to create a function declaration with DETERMINISTIC in the package specification

CREATE OR REPLACE PACKAGE PKG_DETERMINISTIC

AS

FUNCTION FUNC_TEST

RETURN NUMBER DETERMINISTIC

END

To continue the test, the package can also create a function declaration with DETERMINISTIC.

CREATE OR REPLACE PACKAGE BODY PKG_DETERMINISTIC

IS

Function FUNC_TEST

Return number deterministic is

V_len number

Begin

Select length (a) into v_len from t_deterministic

Return v_len

End

END PKG_DETERMINISTIC

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.