Oracle 的 sp_generate_inserts

发布于 2024-09-28 18:32:38 字数 244 浏览 4 评论 0原文

大多数 SQL 开发人员都知道并使用 Narayana Vyas Kondreddi 的 sp_generate_inserts，来自 http://vyaskn .tripod.com/code/generate_inserts.txt

Oracle 有类似的东西吗？

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

维持三分热 2024-10-05 18:32:39

如果您想要一个包为您完成这一切（而 SQL Developer、TOAD 等都可以临时完成）；但我见过的最接近 sp_generate_inserts 的是：

“PL/SQL Interface Generator”
http://sourceforge.net/projects/plsqlintgen/

运行下载中的两个脚本后必须这样做：

CREATE TABLE XYZ(AA VARCHAR2(50) , BB NUMBER(10,2) );

declare 

TPT XTAB_PKG.TAB_PARAMETERS_TYP ;
S   xtab_pkg.SQLDATA_TYP ;
E   XTAB_PKG.xerror_typ ;
BEGIN
        TPT.OWNER := 'ownerName';
        TPT.TABLE_NAME := 'xyz';
        TPT.PACKAGE_NAME := 'xyz_pkg';
        TPT.FILE_PATH := NULL ;
        TPT.FILE_NAME := NULL;
        xtab_pkg.Generate(tpt,s,e);

         for i in s.first..s.last
                 loop
                      DBMS_OUTPUT.PUT_LINE(s(I));
                 end loop;      

END ;

这将为输入表的 CRUD 发出 Create SPEC/BODY （这不会创建包，它只是将包脚本存储在 s 变量中：例如，

即使

/* PL/SQL Interface for Table xyz*/
CREATE SEQUENCE xyz_seq INCREMENT BY 1 START WITH 1 MINVALUE 1 MAXVALUE 999999999999999999999999999 NOCYCLE NOORDER NOCACHE 
/
create or replace package xyz_pkg is 
SUCCESS     constant    varchar2(20) := 'SUCCESS'; ERROR       constant    varchar2(20) := 'ERROR'; WARNING     constant    varchar2(20) := 'WARNING'; 
type xerror_typ is record (status      varchar2(50),message     varchar2(2000));
 type xyz_typ is record 
(
);
type xyz_ref is r.......
Procedure Add (r in out xyz_typ, p_commit in boolean, e   out    xerror_typ);
Procedure Add (t in out xyz_tab, p_commit in boolean, e   out    xerror_typ);
Procedure Del (p_ in xyz.%type, p_commit in boolean, e   out    xerror_typ);
Procedure Del (t in  xyz_tab, p_commit in boolean, e   out    xerror_typ);
Procedure Upd (r in out xyz_typ, p_commit in boolean, e   out    xerror_typ);
Procedure Upd (t in out xyz_tab, p_commit in boolean, e   out    xerror_typ);

这不完全适合您的需要，您应该能够按照自己的方式提取逻辑（只是注意，我根本不隶属于这个项目）。

if you wanted an package to do it all for you (while SQL Developer, TOAD, etc can all do it ad-hoc); but the closest thing to the sp_generate_inserts that I have seen is:

"PL/SQL Interface Generator"
http://sourceforge.net/projects/plsqlintgen/

after running the two scripts in the download all you have to do it:

CREATE TABLE XYZ(AA VARCHAR2(50) , BB NUMBER(10,2) );

declare 

TPT XTAB_PKG.TAB_PARAMETERS_TYP ;
S   xtab_pkg.SQLDATA_TYP ;
E   XTAB_PKG.xerror_typ ;
BEGIN
        TPT.OWNER := 'ownerName';
        TPT.TABLE_NAME := 'xyz';
        TPT.PACKAGE_NAME := 'xyz_pkg';
        TPT.FILE_PATH := NULL ;
        TPT.FILE_NAME := NULL;
        xtab_pkg.Generate(tpt,s,e);

         for i in s.first..s.last
                 loop
                      DBMS_OUTPUT.PUT_LINE(s(I));
                 end loop;      

END ;

and this will emit the Create SPEC/BODY for the CRUD for the inputted table (this does not create the package, it just stores the package script in the s variable as such:

e.g.

/* PL/SQL Interface for Table xyz*/
CREATE SEQUENCE xyz_seq INCREMENT BY 1 START WITH 1 MINVALUE 1 MAXVALUE 999999999999999999999999999 NOCYCLE NOORDER NOCACHE 
/
create or replace package xyz_pkg is 
SUCCESS     constant    varchar2(20) := 'SUCCESS'; ERROR       constant    varchar2(20) := 'ERROR'; WARNING     constant    varchar2(20) := 'WARNING'; 
type xerror_typ is record (status      varchar2(50),message     varchar2(2000));
 type xyz_typ is record 
(
);
type xyz_ref is r.......
Procedure Add (r in out xyz_typ, p_commit in boolean, e   out    xerror_typ);
Procedure Add (t in out xyz_tab, p_commit in boolean, e   out    xerror_typ);
Procedure Del (p_ in xyz.%type, p_commit in boolean, e   out    xerror_typ);
Procedure Del (t in  xyz_tab, p_commit in boolean, e   out    xerror_typ);
Procedure Upd (r in out xyz_typ, p_commit in boolean, e   out    xerror_typ);
Procedure Upd (t in out xyz_tab, p_commit in boolean, e   out    xerror_typ);

Even if this doesn't completely fit your need, you ought to be able to extract the logic to your own means. (just a note, I am not affiliated with this project at all)

回复收藏 0 原文