本篇内容介绍了“怎么理解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;元数据
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”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注亿速云网站，小编将为大家输出更多高质量的实用文章！
免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：is@yisu.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。
