使用编译错误创建的软件包

Question

我正在使用PHP Codeigniter使用Oracle 10G Express Edition。

我正在创建这样的软件包： -

CREATE OR REPLACE PACKAGE currentMovie
    IS
        PROCEDURE 
        currentMovieList (cdate IN VARCHAR2, movieList OUT SYS_REFCURSOR) 
        IS
          BEGIN
            OPEN movieList FOR
            SELECT * FROM tbl_movie, tbl_language WHERE tbl_movie.language_id = tbl_language.language_id AND TO_DATE(cdate, 'YYYY-MM-DD hh24:mi:ss') BETWEEN movie_sdate AND movie_edate;
          END currentMovieList
    END currentMovie;
/

我正在收到警告：Package created with compilation error

但是，当我像这样分别板条时： -

CREATE OR REPLACE PROCEDURE 
        currentMovieList (cdate IN VARCHAR2, movieList OUT SYS_REFCURSOR) 
    IS
        BEGIN
            OPEN movieList FOR
            SELECT * FROM tbl_movie, tbl_language WHERE tbl_movie.language_id = tbl_language.language_id AND TO_DATE(cdate, 'YYYY-MM-DD hh24:mi:ss') BETWEEN movie_sdate AND movie_edate;
        END;
/

我没有错误。该过程可以保存正常。

我在做什么错？

Answer 1

首先，您需要在软件包中定义该过程，如下所示。我们只是在包装上定义它。身体部位变成包装。

SQL> CREATE OR REPLACE PACKAGE currentMovie AS 
   PROCEDURE currentMovieList (cdate IN VARCHAR2, movieList OUT SYS_REFCURSOR); 
END currentMovie; 
/    
Package created.

然后，您需要使用CREATE PACKAGE BODY语句来创建存储软件包的主体。

SQL> CREATE OR REPLACE PACKAGE BODY currentMovie
    IS
        PROCEDURE 
        currentMovieList (cdate IN VARCHAR2, movieList OUT SYS_REFCURSOR) 
        IS
          BEGIN
            OPEN movieList FOR
            SELECT * FROM tbl_movie, tbl_language WHERE tbl_movie.language_id = tbl_language.language_id AND TO_DATE(cdate, 'YYYY-MM-DD hh24:mi:ss') BETWEEN movie_sdate AND movie_edate;
          END currentMovieList;
    END currentMovie;
/
Package body created.