在 Oracle 包正文中使用编译指示
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2801225/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using Pragma in Oracle Package Body
提问by asalamon74
I'd like to create an Oracle Package and two functions in it: A public function ( function_public) and a private one ( function_private). The public function uses the private one in an sql statement.
我想创建一个 Oracle 包和其中的两个函数：公共函数 ( function_public) 和私有函数( function_private)。公共函数在 sql 语句中使用私有函数。
Without pragma the code does not compile (PLS-00231: function 'FUNCTION_PRIVATE' may not be used in SQL)
没有编译指示，代码不会编译 ( PLS-00231: function 'FUNCTION_PRIVATE' may not be used in SQL)
CREATE OR REPLACE PACKAGE PRAGMA_TEST AS
FUNCTION function_public(x IN VARCHAR2) RETURN VARCHAR2;
END PRAGMA_TEST;
CREATE OR REPLACE PACKAGE BODY PRAGMA_TEST AS
FUNCTION function_private(y IN VARCHAR2) RETURN VARCHAR2 IS
BEGIN
return 'z';
END;
FUNCTION function_public(x IN VARCHAR2) RETURN VARCHAR2 IS
ret VARCHAR2(100);
BEGIN
SELECT 'x' || function_private(x) INTO ret FROM dual;
return ret;
END;
END PRAGMA_TEST;
The code compiles if I add WNDS, WNPSpragma to function_private. It seems to me pragma can be used only in the package declaration, and not in package body, so I have to declare function_privatein the package as well:
如果我将WNDS, WNPS编译指示添加到function_private. 在我看来 pragma 只能在包声明中使用，不能在包体中使用，所以我也必须function_private在包中声明：
CREATE OR REPLACE PACKAGE PRAGMA_TEST AS
FUNCTION function_private(y IN VARCHAR2) RETURN VARCHAR2;
PRAGMA RESTRICT_REFERENCES( function_private, WNDS, WNPS);
FUNCTION function_public(x IN VARCHAR2) RETURN VARCHAR2;
END PRAGMA_TEST;
CREATE OR REPLACE PACKAGE BODY PRAGMA_TEST AS
FUNCTION function_private(y IN VARCHAR2) RETURN VARCHAR2 IS
BEGIN
return 'z';
END;
FUNCTION function_public(x IN VARCHAR2) RETURN VARCHAR2 IS
ret VARCHAR2(100);
BEGIN
SELECT 'x' || function_private(x) INTO ret FROM dual;
return ret;
END;
END PRAGMA_TEST;
This solution makes my function_privatepublic as well. Is there a solution to add pragma to a function which can be found only in the package body?
这个解决方案也让我function_private公开。是否有将编译指示添加到只能在包体中找到的函数的解决方案？
UPDATE:Replaced the pseudo-code with a working (simplified) example.
更新：用一个工作（简化）示例替换了伪代码。
UPDATE2: Bugfixes in the code as suggested by Rob van Wijk.
更新 2：按照 Rob van Wijk 的建议修复了代码中的错误。
回答by Dave Costa
Your problem has nothing to do with PRAGMAs. As Rob says, modern Oracle versions handle most of this automatically.
您的问题与 PRAGMA 无关。正如 Rob 所说，现代 Oracle 版本会自动处理大部分内容。
The problem is you can't call private functions from a SQL statement, even ones embedded in another subprogram within the same package.When PL/SQL executes SQL, it is handed off to the SQL engine for execution, and that essentially takes you outside the scope of the package, so it has no access to private members.
问题是您不能从 SQL 语句调用私有函数，即使是嵌入在同一包内的另一个子程序中的私有函数。当 PL/SQL 执行 SQL 时，它会交给 SQL 引擎执行，这实际上将您带出包的范围，因此它无法访问私有成员。
This compiles fine -- no pragmas, but making the "private" function public:
这编译得很好——没有编译指示，而是将“私有”函数设为公开：
CREATE OR REPLACE PACKAGE PRAGMA_TEST AS
FUNCTION function_public(x IN VARCHAR2) RETURN VARCHAR2;
FUNCTION function_private(y IN VARCHAR2) RETURN VARCHAR2;
END PRAGMA_TEST;
CREATE OR REPLACE PACKAGE BODY PRAGMA_TEST AS
FUNCTION function_private(y IN VARCHAR2) RETURN VARCHAR2 IS
BEGIN
return 'z';
END;
FUNCTION function_public(x IN VARCHAR2) RETURN VARCHAR2 IS
ret VARCHAR2(30);
BEGIN
SELECT 'x' || function_private(x) INTO ret FROM dual;
RETURN ret;
END;
END PRAGMA_TEST;
If you want to keep the function private, you need to see if you can rewrite the public function in such a way that the call to the private function is done outside the SQL statement:
如果要保持函数私有，则需要查看是否可以重写公共函数，使私有函数的调用在 SQL 语句之外完成：
CREATE OR REPLACE PACKAGE PRAGMA_TEST AS
FUNCTION function_public(x IN VARCHAR2) RETURN VARCHAR2;
END PRAGMA_TEST;
CREATE OR REPLACE PACKAGE BODY PRAGMA_TEST AS
FUNCTION function_private(y IN VARCHAR2) RETURN VARCHAR2 IS
BEGIN
return 'z';
END;
FUNCTION function_public(x IN VARCHAR2) RETURN VARCHAR2 IS
ret VARCHAR2(30);
BEGIN
ret := function_private(x);
SELECT 'x' || ret INTO ret FROM dual;
RETURN ret;
END;
END PRAGMA_TEST;
回答by APC
Your function_privateis only declared in the package body, so its scope is restricted only to other procedures in your package. Consequently it will have to conform to the purity level of those calling procedures or else the compiler will hurl a exception.
您function_private仅在包体中声明，因此其范围仅限于包中的其他过程。因此，它必须符合那些调用过程的纯度级别，否则编译器将抛出异常。
Compare this safe declaration (note, I have extended the purity of the function_public) ...
比较这个安全声明（注意，我已经扩展了 的纯度function_public）......
SQL> CREATE OR REPLACE PACKAGE PRAGMA_TEST AS
2 FUNCTION function_public(x IN VARCHAR2) RETURN VARCHAR2;
3 PRAGMA RESTRICT_REFERENCES( function_public, WNDS, WNPS, RNDS);
4 END PRAGMA_TEST;
5 /
Package created.
SQL> CREATE OR REPLACE PACKAGE BODY PRAGMA_TEST AS
2 FUNCTION function_private(y IN VARCHAR2) RETURN VARCHAR2 IS
3 BEGIN
4 return 'no harm done';
5 END;
6
7 FUNCTION function_public(x IN VARCHAR2) RETURN VARCHAR2 IS
8 BEGIN
9 return function_private(x);
10 END;
11 END PRAGMA_TEST;
12 /
Package body created.
SQL>
... with this unsafe one ...
......有了这个不安全的......
SQL> CREATE OR REPLACE PACKAGE BODY PRAGMA_TEST AS
2 FUNCTION function_private(y IN VARCHAR2) RETURN VARCHAR2 IS
3 rv varchar2(1);
4 BEGIN
5 select dummy into rv from dual;
6 return rv;
7 END;
8
9 FUNCTION function_public(x IN VARCHAR2) RETURN VARCHAR2 IS
10 BEGIN
11 return function_private(x);
12 END;
13 END PRAGMA_TEST;
14 /
Warning: Package Body created with compilation errors.
SQL> sho err
Errors for PACKAGE BODY PRAGMA_TEST:
LINE/COL ERROR
-------- -----------------------------------------------------------------
9/3 PLS-00452: Subprogram 'FUNCTION_PUBLIC' violates its associated
pragma
SQL>
The point of the RESTRICTS_REFERENCES pragma is that procedures declared in the package spec can be used by other packages, even SQL statements, owned or executed by other users (schemas), who might not have access to our package body's source. The pragma is a method by which we offer assurances to them about the impact of incorporating our code in theirs. That's why the pragma has to be declared in the spec, because that is the only part of the code exposed when we grant EXECUTE on the package to another user.
RESTRICTS_REFERENCES pragma 的要点是包规范中声明的过程可以被其他包使用，甚至是 SQL 语句，由其他用户（模式）拥有或执行，他们可能无法访问我们的包主体的源。pragma 是一种我们向他们保证将我们的代码合并到他们的代码中的影响的方法。这就是 pragma 必须在规范中声明的原因，因为这是我们将包上的 EXECUTE 授予另一个用户时暴露的唯一代码部分。
edit
编辑
Ah, having now seen your revised code example I understand what you're trying to do. It doesn't, won't, can't work. We are only allowed to use packaged functions which have been declared in the spec = public functions - in SQL. It doesn't matter whether the SQL is written in SQL*Plus or coded in another packaged procedure. The reason why is quite clear in the error stack:
啊，现在看到您修改后的代码示例后，我明白您要做什么。它不会，不会，不能工作。我们只允许使用在 SQL 中 spec = public functions - 中声明的打包函数。SQL 是用 SQL*Plus 编写的还是在另一个打包过程中编写的，这无关紧要。原因在错误堆栈中很清楚：
SQL> CREATE OR REPLACE PACKAGE PRAGMA_TEST AS
2 FUNCTION function_public(x IN VARCHAR2) RETURN VARCHAR2;
3 PRAGMA RESTRICT_REFERENCES( function_public, WNDS, WNPS);
4 END PRAGMA_TEST;
5 /
Package created.
SQL> CREATE OR REPLACE PACKAGE BODY PRAGMA_TEST AS
2 FUNCTION function_private(y IN VARCHAR2) RETURN VARCHAR2 IS
3 rv varchar2(1);
4 BEGIN
5 select dummy into rv from dual;
6 return rv;
7 END;
8
9 FUNCTION function_public(x IN VARCHAR2) RETURN VARCHAR2 IS
10 rv varchar2(1);
11 BEGIN
12 select function_private(x) into rv from dual;
13 return rv;
14 END;
15 END PRAGMA_TEST;
16 /
Warning: Package Body created with compilation errors.
SQL> sho err
Errors for PACKAGE BODY PRAGMA_TEST:
LINE/COL ERROR
-------- -----------------------------------------------------------------
12/6 PL/SQL: SQL Statement ignored
12/13 PL/SQL: ORA-00904: : invalid identifier
12/13 PLS-00231: function 'FUNCTION_PRIVATE' may not be used in SQL
SQL>
The compiler hurls ORA-00904: invalid identifierbecause the function is not declared in the spec; it has nothing to do with purity levels,
编译器抛出异常，ORA-00904: invalid identifier因为该函数未在规范中声明；它与纯度水平无关，
a note about scope
关于范围的说明
PL/SQL is not entirely consist with regards to its scoping rules: we can use private variables in our packaged SQL statement:
PL/SQL的作用域规则并不完全一致：我们可以在打包的 SQL 语句中使用私有变量：
SQL> CREATE OR REPLACE PACKAGE BODY PRAGMA_TEST AS
2
3 gv constant varchar2(8) := 'global';
4
5 FUNCTION function_private(y IN VARCHAR2) RETURN VARCHAR2 IS
6 rv varchar2(1);
7 BEGIN
8 select dummy into rv from dual;
9 return rv;
10 END;
11
12 FUNCTION function_public(x IN VARCHAR2) RETURN VARCHAR2 IS
13 rv varchar2(10);
14 BEGIN
15 select gv||'+'||dummy into rv from dual;
16 return rv;
17 END;
18 END PRAGMA_TEST;
19 /
Package body created.
SQL>
It's just functions and types which wm must declare in the spec if we want to use them in SQL statements.
如果我们想在 SQL 语句中使用它们，那么 wm 必须在规范中声明它们只是函数和类型。
回答by Rob van Wijk
You write "I'd like to add WNDS, WNPS pragma ...". Why do you like that? Since version 9 (I think) Oracle does this check for you. The only reason you might want to add the pragma yourself, is when:
您写道“我想添加 WNDS、WNPS pragma ...”。你为什么喜欢那个？由于版本 9（我认为）Oracle 会为您执行此检查。您可能想要自己添加编译指示的唯一原因是：
you know where in a SQL statement you want to use the function AND
you know which purity levels are required for this use AND
you want to find violations at compile time instead of run time
你知道你想在 SQL 语句中的哪个位置使用函数 AND
您知道此用途需要哪些纯度水平并且
你想在编译时而不是运行时发现违规
The easiest option is to just skip all the pragma declarations altogether.
最简单的选择是完全跳过所有 pragma 声明。
Having said that, you can omit the restrict_references pragma to function_private if you add the TRUST keyword to the restrict_references pragma of function_public.
话虽如此，如果您将 TRUST 关键字添加到 function_public 的 restrict_references 编译指示中，则可以省略对 function_private 的 restrict_references 编译指示。
http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96590/adg10pck.htm#21958
http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96590/adg10pck.htm#21958
Regards, Rob.
问候，罗伯。
回答by Peter Lang
Oracle does this check.
Oracle 会执行此检查。
The following code does not compile, since function_publichas the pragma RNDS, and it calls function_privatewhich reads a table.
下面的代码不能编译，因为function_public有 pragma RNDS，它调用function_private它读取一个表。
PLS-00452: Subprogram 'FUNCTION_PUBLIC' violates its associated pragma
PLS-00452：子程序“FUNCTION_PUBLIC”违反其相关的编译指示
Remove the SELECTfrom function_privateand it works.
删除SELECTfromfunction_private和它的工作原理。
CREATE OR REPLACE PACKAGE pragma_test AS
FUNCTION function_public RETURN VARCHAR2;
PRAGMA RESTRICT_REFERENCES( function_public, RNDS );
END pragma_test;
CREATE OR REPLACE PACKAGE BODY pragma_test AS
FUNCTION function_private RETURN VARCHAR2 IS
v_return dual.dummy%TYPE;
BEGIN
SELECT dummy INTO v_return FROM dual;
RETURN v_return;
END;
--
FUNCTION function_public RETURN VARCHAR2 IS
v_return dual.dummy%TYPE;
BEGIN
RETURN function_private;
END;
END pragma_test;
