本篇内容介绍了“怎么理解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”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注亿速云网站，小编将为大家输出更多高质量的实用文章！
亿速云「云服务器」，即开即用、新一代英特尔至强铂金CPU、三副本存储NVMe SSD云盘，价格低至29元/月。点击查看>>
免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：is@yisu.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。
原文链接：http://blog.itpub.net/6906/viewspace-2662858/
