溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
本篇內容介紹了“怎么理解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進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
