如何使用“立即执行”将包公共变量访问到SELECT语句中？

Question

CREATE OR REPLACE PACKAGE sample_pkg AS
g_var VARCHAR2(5);
END sample_pkg;

/

CREATE OR REPLACE PACKAGE BODY sample_pkg AS 
BEGIN
g_var := 'hello';
dbms_output.put_line(g_var);
END;

/

SELECT sample_pkg.g_var FROM dual;

error ORA-06553: PLS-221：“G_VAR”不是一个过程或未定义的

Answer 1

显然，您不能调用这样的包的内部变量，可能会转换为这个变量。

CREATE OR REPLACE PACKAGE sample_pkg AS
  g_var VARCHAR2(500) := 'hello';
  FUNCTION f_var( i_var VARCHAR2 DEFAULT g_var ) RETURN VARCHAR2;  
END sample_pkg;
/
CREATE OR REPLACE PACKAGE BODY sample_pkg AS      
  FUNCTION f_var( i_var VARCHAR2 DEFAULT g_var ) RETURN VARCHAR2 IS
  BEGIN  
    RETURN i_var;
  END;  
END;
/

像这样打电话：

SELECT sample_pkg.f_var FROM dual;

F_VAR
-----
hello

或者这个：

SET SERVEROUTPUT ON  
BEGIN
  DBMS_OUTPUT.PUT_LINE(sample_pkg.f_var);
END; 
/
hello

编辑：确实，不需要；您也可以按需要使用EXECUTE IMMEDIATE调用语句，如

DECLARE
   result VARCHAR2(89);
   i_val  VARCHAR2(89):='Hello World!';
BEGIN
   EXECUTE IMMEDIATE 'SELECT sample_pkg_.f_var(:prm1) FROM dual' INTO result USING i_val; 
  DBMS_OUTPUT.PUT_LINE(result);
END;
/    

Answer 2

包体具有函数和过程，而不是独立的代码--不能在select语句中调用过程，这需要在pl/sql块中执行。在包规范或子程序中声明包变量时，可以初始化它。在一个例子中把这一切结合在一起：

koen>create or replace PACKAGE sample_pkg AS
  2    g_var VARCHAR2(100) := 'initial value'; 
  3    PROCEDURE init;
  4    FUNCTION ret_g_var RETURN VARCHAR2;
  5  END sample_pkg;
  6* /

Package SAMPLE_PKG compiled

koen>create or replace PACKAGE BODY sample_pkg AS 
  2   
  3    PROCEDURE init
  4    IS
  5    BEGIN
  6      g_var := 'Hello !';
  7    END;
  8    FUNCTION ret_g_var return VARCHAR2
  9    IS
 10    BEGIN
 11      RETURN g_var;
 12    END;
 13  
 14  END;
 15* /

Package Body SAMPLE_PKG compiled

koen>set serveroutput on size 999999
koen>select sample_pkg.ret_g_var from dual;

       RET_G_VAR 
________________ 
initial value    

koen>begin
  2    dbms_output.put_line(sample_pkg.g_var);
  3    sample_pkg.init;
  4    dbms_output.put_line(sample_pkg.g_var);
  5  end;
  6* /
initial value
Hello !


PL/SQL procedure successfully completed.

koen>select sample_pkg.ret_g_var from dual;

   RET_G_VAR 
____________ 
Hello !      

koen>