oracle - Oracle 包和在公共变量中使用 select 语句

Question

找了好久都没有用。作为评估的一部分，它指定我们必须在包中声明一个公共变量（所以不要试图告诉我让它成为一个独立的函数......），它是另一个表中的行数（“SELECT COUNT(* ) FROM A2_GUESTS" 是提示)

我可以轻松地将公共变量设置为静态数字，但是如果我尝试添加 select 语句，则会引发错误。

如果我尝试将它分配到包体中，那么它也会引发错误，如果我将它包装在“开始”和“结束”中，它会过早终止包体。

CREATE OR REPLACE PACKAGE Beachcomber
AS
    v_Guest_Count NUMBER := 0; 
END Beachcomber;
/
CREATE OR REPLACE PACKAGE BODY Beachcomber IS
    SELECT COUNT(*) INTO v_Guest_Count FROM A2_GUESTS; -- doesn't work
    v_Guest_Count NUMBER := SELECT COUNT(*) FROM A2_GUESTS; -- doesn't work
    BEGIN
        v_Guest_Count NUMBER := SELECT COUNT(*) FROM A2_GUESTS; 
    END; -- doesn't work - ends the package prematurely
END Beachcomber;

上面的例子是我一直在尝试的方法（以及其他方法），不是同时尝试，而是单独尝试。

我们得到了测试它的代码：（不得更改此测试代码）

PROMPT 
PROMPT TESTING: Initialisation of the v_Guest_Count variable.  Expect 81.
BEGIN
   DBMS_OUTPUT.PUT_LINE('v_Guest_Count has been initialised to: '||TO_CHAR(BEACHCOMBER.v_Guest_Count));
END;

非常感谢任何帮助，我发现有人在 2015 年在这里问过这个问题，但唯一的答案是让它成为一个函数，他们调整了测试代码，所以这没什么帮助。

包中有更多的代码，包括过程和函数：

CREATE OR REPLACE PACKAGE Beachcomber
IS
    v_Guest_Count NUMBER := 0; 
    PROCEDURE ADD_GUEST 
        (p_guest_name A2_GUESTS.guest_name%TYPE,
        p_guest_address A2_GUESTS.guest_address%TYPE);
END Beachcomber;
/
    CREATE OR REPLACE PACKAGE BODY Beachcomber IS
    BEGIN 
    SELECT COUNT(*) INTO v_Guest_Count FROM A2_GUESTS;

    PROCEDURE ADD_GUEST
        (p_guest_name A2_GUESTS.guest_name%TYPE,
        p_guest_address A2_GUESTS.guest_address%TYPE)
        IS BEGIN
        INSERT INTO A2_GUESTS (Guest_ID, Guest_Name, Guest_Address) 
            VALUES (guest_id_seq.NEXTVAL, p_guest_name, p_guest_address);
        v_Guest_Count := v_Guest_Count +1;
    END ADD_GUEST;
END Beachcomber;

这将抛出：

5/5 PLS-00103: Encountered the symbol "PROCEDURE" when expecting one of the following: ( begin case declare end exception exit for goto if loop mod null pragma raise return select update while with <an identifier> <a double-quoted delimited-identifier> <a bind variable> << continue close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe purge The symbol "declare" was substituted for "PROCEDURE" to continue.

我通常可以从错误消息中解决它，但 oracle 错误消息也可以用荷兰语写给我：/

标签： oracleplsqlpackage

Answer 1

我们可以将初始化代码放在包中，方法是把它放在 body 的末尾。它采用 BEGIN 块的形式，由包体的最终 END 终止。

create or replace package BEACHCOMBER is
    v_Guest_Count pls_integer;
    function get_cnt return number;
end BEACHCOMBER;
/
create or replace package body BEACHCOMBER is
    function get_cnt return number
    is
    begin
        return v_Guest_Count;
    end get_cnt;

<< init_block >>
begin
    select count(*) 
    into v_Guest_Count
    from A2_GUESTS;
end BEACHCOMBER;
/

标签下的代码在<< init_block >>第一次调用包时运行。这包括引用公共变量。此代码不会在会话中再次运行，除非重新编译包，从而丢弃状态。

这是我的测试脚本。我已将此作为 Oracle LiveSQL 上的工作演示发布（因为 DBMS_OUTPUT），但您需要一个免费的Oracle 帐户来运行它。一探究竟

测试设置

drop table A2_GUESTS
/

create table A2_GUESTS (id number);

insert into A2_GUESTS select level from dual connect by level <=23;

create or replace package BEACHCOMBER is
    v_Guest_Count pls_integer;
    function get_cnt return number;
end BEACHCOMBER;
/
create or replace package body BEACHCOMBER is
    function get_cnt return number
    is
    begin
        return v_Guest_Count;
    end get_cnt;
begin
    select count(*) 
    into v_Guest_Count
    from A2_GUESTS;
end BEACHCOMBER;
/

这是测试；

begin
    dbms_output.put_line('count = ' || BEACHCOMBER.v_Guest_Count);
end;
/

insert into A2_GUESTS values (42)
/

select BEACHCOMBER.get_cnt 
from dual
/

alter package BEACHCOMBER compile body
/

select BEACHCOMBER.get_cnt 
from dual
/