怎么理解edb中的package

本篇内容介绍了"怎么理解edb中的package"的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！

创建package
兼容Oracle语法

create or replace package pk_demoas  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;

元数据
1.存储组织结构
pg_namespace

postgres=# \d pg_namespace                Table "pg_catalog.pg_namespace"      Column      |   Type    | Collation | Nullable | Default ------------------+-----------+-----------+----------+--------- nspname          | name      |           | not null |  nspowner         | oid       |           | not null |  nspparent        | oid       |           | not null |  nspobjecttype    | oid       |           | not null |  nspforeignserver | oid       |           | not null |  nspsecdef        | boolean   |           | not null |  nspremoteschema  | text      |           |          |  nspheadsrc       | text      |           |          |  nspbodysrc       | text      |           |          |  nspacl           | aclitem[] |           |          | Indexes:    "pg_namespace_nspname_index" UNIQUE, btree (nspname, nspparent)    "pg_namespace_oid_index" UNIQUE, btree (oid)postgres=# select * from pg_namespace where nspname='pk_demo'; nspname | nspowner | nspparent | nspobjecttype | nspforeignserver | nspsecdef | nspremoteschema |     nspheadsrc      |     nspbodysrc      | nspacl ---------+----------+-----------+---------------+------------------+-----------+-----------------+---------------------+---------------------+-------- pk_demo |       10 |      2200 |             0 |                0 | t         |                 |                    +|                    +|          |          |           |               |                  |           |                 |   @VARIABLE 16462@;+|   @FUNCTION 16463@;+|          |          |           |               |                  |           |                 |   @FUNCTION 16463@;+|                    +|          |          |           |               |                  |           |                 |   @FUNCTION 16464@;+|   @FUNCTION 16464@;+|          |          |           |               |                  |           |                 |                     |                     | (1 row)postgres=# select * from pg_user where usesysid=10;   usename    | usesysid | usecreatedb | usesuper | userepl | usebypassrls |  passwd  | valuntil | useaccountstatus | uselockdate | usepasswordexpire | useconfig --------------+----------+-------------+----------+---------+--------------+----------+----------+------------------+-------------+-------------------+----------- enterprisedb |       10 | t           | t        | t       | t            | ******** |          |                0 |             |                   | (1 row)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)

2.变量
edb_variable

postgres=# \d edb_var*               Table "pg_catalog.edb_variable"      Column      |  Type   | Collation | Nullable | Default ------------------+---------+-----------+----------+--------- varname          | name    |           | not null |  varpackage       | oid     |           | not null |  vartype          | oid     |           | not null |  vartypmod        | integer |           | not null |  varaccess        | "char"  |           | not null |  varisconst       | boolean |           | not null |  varseq           | integer |           | not null |  varerrcode       | integer |           | not null |  varsrc           | text    |           |          |  varexceptionname | text    |           |          | Indexes:    "pg_variable_oid_index" UNIQUE, btree (oid)    "pg_variable_varname_pkg_index" UNIQUE, btree (varpackage, varname)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)

3.函数/过程
pg_proc

postgres=# \d pg_proc                     Table "pg_catalog.pg_proc"       Column        |     Type     | Collation | Nullable | Default ---------------------+--------------+-----------+----------+--------- proname             | name         |           | not null |  pronamespace        | oid          |           | not null |  proowner            | oid          |           | not null |  prolang             | oid          |           | not null |  procost             | real         |           | not null |  prorows             | real         |           | not null |  provariadic         | oid          |           | not null |  protransform        | regproc      |           | not null |  proisagg            | boolean      |           | not null |  proiswindow         | boolean      |           | not null |  prosecdef           | boolean      |           | not null |  proleakproof        | boolean      |           | not null |  proisstrict         | boolean      |           | not null |  proretset           | boolean      |           | not null |  proisweak           | boolean      |           | not null |  provolatile         | "char"       |           | not null |  proparallel         | "char"       |           | not null |  protype             | "char"       |           | not null |  proaccess           | "char"       |           | not null |  pronargs            | smallint     |           | not null |  pronargdefaults     | smallint     |           | not null |  prolineno           | integer      |           | not null |  prorettype          | oid          |           | not null |  proargtypes         | oidvector    |           | not null |  promemberattrs      | text         |           |          |  proallargtypes      | oid[]        |           |          |  proargmodes         | "char"[]     |           |          |  proargdeclaredmodes | "char"[]     |           |          |  proargnames         | text[]       |           |          |  proargdefaults      | pg_node_tree |           |          |  protrftypes         | oid[]        |           |          |  prosrc              | text         |           | not null |  probin              | text         |           |          |  proconfig           | text[]       |           |          |  proacl              | aclitem[]    |           |          | Indexes:    "pg_proc_oid_index" UNIQUE, btree (oid)    "pg_proc_proname_args_nsp_index" UNIQUE, btree (proname, protype, proargtypes, pronamespace)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=# \d edb_pkg*                View "pg_catalog.edb_pkgelements"   Column    |       Type        | Collation | Nullable | Default -------------+-------------------+-----------+----------+--------- packageoid  | oid               |           |          |  eltname     | name              |           |          |  visibilty   | "char"            |           |          |  eltclass    | character varying |           |          |  eltdatatype | oid               |           |          |  nargs       | smallint          |           |          |  argtypes    | oidvector         |           |          |  argmodes    | "char"[]          |           |          |  argnames    | text[]            |           |          |  argdefvals  | pg_node_tree      |           |          | 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)

"怎么理解edb中的package"的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注网站，小编将为大家输出更多高质量的实用文章！