本節簡單介紹了edb中的package。

**建立package**
相容Oracle語法
```
create or replace package pk_demo
as
  var_pk_demo_1 number;
  function func_demo() return number;
  procedure proc_demo();
end pk_demo;

create or replace package body pk_demo 
as
  function func_demo() return number
  AS
    BEGIN
      var_pk_demo_1 := 100;
      return var_pk_demo_1;
    END;

procedure proc_demo() 
  AS
    ret number;
    BEGIN
      select func_demo() into ret;
      var_pk_demo_1 := 200;
      dbms_output.put_line('var_pk_demo_1 := '||var_pk_demo_1||',ret = '||ret);
    END;
END pk_demo;

```

postgres=# select * from pg_namespace where oid=2200;
 nspname | nspowner | nspparent | nspobjecttype | nspforeignserver | nspsecdef | nspremoteschema | nspheadsrc | nspbodysrc |                     nspacl                      
---------+----------+-----------+---------------+------------------+-----------+-----------------+------------+------------+-------------------------------------------------
 public  |       10 |         0 |             0 |                0 | f         |                 |            |            | {enterprisedb=UC/enterprisedb,=UC/enterprisedb}
(1 row)

```

postgres=# select * from edb_variable where oid=16462;
    varname    | varpackage | vartype | vartypmod | varaccess | varisconst | varseq | varerrcode | varsrc | varexceptionname 
---------------+------------+---------+-----------+-----------+------------+--------+------------+--------+------------------
 var_pk_demo_1 |      16454 |    1700 |        -1 | +         | f          |      1 |          0 |        | 
(1 row)

```

postgres=# select proname,pronamespace from pg_proc where oid=16463;
  proname  | pronamespace 
-----------+--------------
 func_demo |        16454
(1 row)

postgres=# select proname,pronamespace from pg_proc where oid=16464;
  proname  | pronamespace 
-----------+--------------
 proc_demo |        16454
(1 row)

postgres=#

```

4.相關檢視：edb_pkgelements、edb_package
```

postgres=# select * from edb_pkgelements where packageoid = 16454;
 packageoid |    eltname    | visibilty | eltclass | eltdatatype | nargs | argtypes | argmodes | argnames | argdefvals 
------------+---------------+-----------+----------+-------------+-------+----------+----------+----------+------------
      16454 | proc_demo     | +         | P        |        2278 |     0 |          |          |          | 
      16454 | var_pk_demo_1 | +         | V        |        1700 |       |          |          |          | 
      16454 | func_demo     | +         | F        |        1700 |     0 |          |          |          | 
(3 rows)

postgres=#

postgres=# select * from pg_views where viewname='edb_pkgelements';
 schemaname |    viewname     |  viewowner   |                         definition                         
------------+-----------------+--------------+------------------------------------------------------------
 pg_catalog | edb_pkgelements | enterprisedb |  SELECT edb_variable.varpackage AS packageoid,            +
            |                 |              |     edb_variable.varname AS eltname,                      +
            |                 |              |     edb_variable.varaccess AS visibilty,                  +
            |                 |              |     'V'::character varying AS eltclass,                   +
            |                 |              |     edb_variable.vartype AS eltdatatype,                  +
            |                 |              |     NULL::smallint AS nargs,                              +
            |                 |              |     NULL::oidvector AS argtypes,                          +
            |                 |              |     NULL::"char"[] AS argmodes,                           +
            |                 |              |     NULL::text[] AS argnames,                             +
            |                 |              |     NULL::pg_node_tree AS argdefvals                      +
            |                 |              |    FROM edb_variable                                      +
            |                 |              | UNION                                                     +
            |                 |              |  SELECT pg_proc.pronamespace AS packageoid,               +
            |                 |              |     pg_proc.proname AS eltname,                           +
            |                 |              |     pg_proc.proaccess AS visibilty,                       +
            |                 |              |         DECODE(  (pg_proc.protype)::character varying     +
            |                 |              |             , ('0'::text)::character varying              +
            |                 |              |             , ('F'::text)::character varying              +
            |                 |              |             , ('1'::text)::character varying              +
            |                 |              |             , ('P'::text)::character varying              +
            |                 |              |             , NULL::character varying                     +
            |                 |              |         ) AS eltclass,                                    +
            |                 |              |     pg_proc.prorettype AS eltdatatype,                    +
            |                 |              |     pg_proc.pronargs AS nargs,                            +
            |                 |              |     pg_proc.proargtypes AS argtypes,                      +
            |                 |              |     pg_proc.proargmodes AS argmodes,                      +
            |                 |              |     pg_proc.proargnames AS argnames,                      +
            |                 |              |     pg_proc.proargdefaults AS argdefvals                  +
            |                 |              |    FROM pg_proc                                           +
            |                 |              |   WHERE (pg_proc.pronamespace IN ( SELECT pg_namespace.oid+
            |                 |              |            FROM pg_namespace                              +
            |                 |              |           WHERE (pg_namespace.nspparent <> (0)::oid)));
(1 row)

postgres=# select * from pg_views where viewname='edb_package';
 schemaname |  viewname   |  viewowner   |                                         definition                                          
------------+-------------+--------------+---------------------------------------------------------------------------------------------
 pg_catalog | edb_package | enterprisedb |  SELECT pg_namespace.oid,                                                                  +
            |             |              |     pg_namespace.nspname AS pkgname,                                                       +
            |             |              |     pg_namespace.nspparent AS pkgnamespace,                                                +
            |             |              |     pg_namespace.nspowner AS pkgowner,                                                     +
            |             |              |     edb_get_packageheaddef(pg_namespace.oid) AS pkgheadsrc,                                +
            |             |              |     edb_get_packagebodydef(pg_namespace.oid) AS pkgbodysrc,                                +
            |             |              |     'P'::character(1) AS pkgproperties,                                                    +
            |             |              |     pg_namespace.nspacl AS pkgacl,                                                         +
            |             |              |     pg_namespace.cmin,                                                                     +
            |             |              |     pg_namespace.xmin,                                                                     +
            |             |              |     pg_namespace.cmax,                                                                     +
            |             |              |     pg_namespace.xmax,                                                                     +
            |             |              |     pg_namespace.ctid                                                                      +
            |             |              |    FROM pg_namespace                                                                       +
            |             |              |   WHERE ((pg_namespace.nspparent <> (0)::oid) AND (pg_namespace.nspobjecttype = (0)::oid));
(1 row)

postgres=# select * from edb_package where pkgname='pk_demo';
  oid  | pkgname | pkgnamespace | pkgowner |                 pkgheadsrc                  |                                   pkgbodysrc                                    | pkgproperties | pkgacl | cmin | xmin | cmax | xmax |  ctid  
-------+---------+--------------+----------+---------------------------------------------+---------------------------------------------------------------------------------+---------------+--------+------+------+------+------+--------
 16454 | pk_demo |         2200 |       10 | CREATE OR REPLACE PACKAGE public.pk_demo IS+| CREATE OR REPLACE PACKAGE BODY public.pk_demo IS                               +| P             |        |    2 | 1231 |    2 |    0 | (0,29)
       |         |              |          |   var_pk_demo_1 numeric;                   +|   FUNCTION func_demo() RETURN numeric IS                                       +|               |        |      |      |      |      | 
       |         |              |          |   FUNCTION func_demo() RETURN numeric;     +|     BEGIN                                                                      +|               |        |      |      |      |      | 
       |         |              |          |   PROCEDURE proc_demo();                   +|       var_pk_demo_1 := 100;                                                    +|               |        |      |      |      |      | 
       |         |              |          | END                                         |       return var_pk_demo_1;                                                    +|               |        |      |      |      |      | 
       |         |              |          |                                             |     END;                                                                       +|               |        |      |      |      |      | 
       |         |              |          |                                             |                                                                                +|               |        |      |      |      |      | 
       |         |              |          |                                             |   PROCEDURE proc_demo() IS                                                     +|               |        |      |      |      |      | 
       |         |              |          |                                             |     ret number;                                                                +|               |        |      |      |      |      | 
       |         |              |          |                                             |     BEGIN                                                                      +|               |        |      |      |      |      | 
       |         |              |          |                                             |       select func_demo() into ret;                                             +|               |        |      |      |      |      | 
       |         |              |          |                                             |       var_pk_demo_1 := 200;                                                    +|               |        |      |      |      |      | 
       |         |              |          |                                             |       dbms_output.put_line('var_pk_demo_1 := '||var_pk_demo_1||',ret = '||ret);+|               |        |      |      |      |      | 
       |         |              |          |                                             |     END;                                                                       +|               |        |      |      |      |      | 
       |         |              |          |                                             | END                                                                             |               |        |      |      |      |      | 
(1 row)

```

PostgreSQL DBA(122) - Develop(EDB package)

相關文章