在pl/sql中创建表

发布于 2024-09-30 06:32:42 字数 992 浏览 3 评论 0原文

我想合并一个创建表的 sql 脚本和一个在该表中插入数据的 pl/sql 脚本。客户希望

我的想法是在 pl sql 中创建表。但这不起作用。开始部分不允许创建。我见过的一个解决方案是在立即执行语句中执行此操作。在实践中，我尝试过这个：

    SET serveroutput ON
    spool 03_CREATE_CATEGORIEDECL.log

    BEGIN
     execute immediate 'create table CATEGORIEDECLARATION ( 
     nIdCategorieDeclaration  NUMBER(10)   not null,
     ...
     constraint PK_CATDECLA primary key (nIdCategorieDeclaration)
    )';

select c.nidcalendrier into millesime from calendrier c where c.smillesime = '2010';

-- Lignes relatives au formulaire CA3
INSERT into CATEGORIEDECLARATION (nIdCategorieDeclaration,nIdTypeFormulaire,sLibelle,sType,sAide,sTexte,sTexte2,sTypeAffichage,bAffichage,sInterval,nIdCalendrier) 
values (seq_CATEGORIEDECLARATION.nextval,'5','Autres cas  (zone de saisie libre)', 'SOMME_A_DEDUIRE','','',NULL,'CAT_AUTRE_CAS', 1, 'POSITIF',millesime);

COMMIT;
    END; 
    /
    spool off

我在 end 关键字上收到错误，这是意料之外的。所以我的问题是如何在 pl/sql 脚本中创建表？我是否必须将这两个操作保留在两个不同的脚本中？

原文

I want to merge a sql script which create a table and a pl/sql script which insert data in that table. Customer wishes

My idea was to do the table creation in pl sql. But it doesn't work. Create is not allowed in the begin part. A solution I've seen is to do this in an execute immediate statement. In practice I 've tried this :

    SET serveroutput ON
    spool 03_CREATE_CATEGORIEDECL.log

    BEGIN
     execute immediate 'create table CATEGORIEDECLARATION ( 
     nIdCategorieDeclaration  NUMBER(10)   not null,
     ...
     constraint PK_CATDECLA primary key (nIdCategorieDeclaration)
    )';

select c.nidcalendrier into millesime from calendrier c where c.smillesime = '2010';

-- Lignes relatives au formulaire CA3
INSERT into CATEGORIEDECLARATION (nIdCategorieDeclaration,nIdTypeFormulaire,sLibelle,sType,sAide,sTexte,sTexte2,sTypeAffichage,bAffichage,sInterval,nIdCalendrier) 
values (seq_CATEGORIEDECLARATION.nextval,'5','Autres cas  (zone de saisie libre)', 'SOMME_A_DEDUIRE','','',NULL,'CAT_AUTRE_CAS', 1, 'POSITIF',millesime);

COMMIT;
    END; 
    /
    spool off

I get an error on the end keyword, it wasn't expected. So my question is how to create a table in a pl/sql script? Do I have to keep those 2 actions in 2 differents scripts?

分享到QQ

分享到微博

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

发布评论

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

感受沵的脚步 2024-10-07 06:32:42

您有 2 个语法错误，均与分号有关。试试这个：

BEGIN
 execute immediate 'create table CATEGORIEDECLARATION (
 nIdCategorieDeclaration  NUMBER(10)   not null,
 ...
 constraint PK_CATDECLA primary key (nIdCategorieDeclaration)
)';
END;
/

You have 2 syntax errors, both concerning semi-colons. Try this:

BEGIN
 execute immediate 'create table CATEGORIEDECLARATION (
 nIdCategorieDeclaration  NUMBER(10)   not null,
 ...
 constraint PK_CATDECLA primary key (nIdCategorieDeclaration)
)';
END;
/

回复收藏 0 原文

我不咬妳我踢妳 2024-10-07 06:32:42

在查看这些片段之前，请记住，在撰写本文时，我无法访问 Oracle 数据库来以任何方式测试它们。接下来的一切都是凭记忆写的。

我假设您正在使用 sqlplus 来运行脚本。不能简单地将 create 语句和 PL/SQL 块放入文件中吗？


SET serveroutput ON

spool 03_CREATE_CATEGORIEDECL.log

create table CATEGORIEDECLARATION ( 
     nIdCategorieDeclaration  NUMBER(10)   not null,
     ...
constraint PK_CATDECLA primary key (nIdCategorieDeclaration)
/


BEGIN
  select c.nidcalendrier into millesime from calendrier c where c.smillesime = '2010';
  -- Lignes relatives au formulaire CA3
  INSERT into CATEGORIEDECLARATION (nIdCategorieDeclaration,nIdTypeFormulaire,sLibelle,sType,sAide,sTexte,sTexte2,sTypeAffichage,bAffichage,sInterval,nIdCalendrier) 
  values (seq_CATEGORIEDECLARATION.nextval,'5','Autres cas  (zone de saisie libre)', 'SOMME_A_DEDUIRE','','',NULL,'CAT_AUTRE_CAS', 1, 'POSITIF',millesime);

  COMMIT;
END; 
/
spool off

另一种方法是动态生成脚本并调用它

SET serveroutput ON
SET FEEDBACK OFF
SET HEADING OFF
SET LINESIZE 800
SET PAGESIZE 0
SET ECHO OFF

 SPOOL gen_cr_table_script.sql
SELECT '创建表 CATEGORIEDECLARATION ( 
     nIdCategorieDeclaration NUMBER(10) 不为空，
     ...
     约束 PK_CATDECLA 主键 (nIdCategorieDeclaration)
    )'
  来自 SYS.DUAL
/
SPOOL OFF

 @gen_cr_table_script.sql

 -- 如果需要，您可以在此处生成插入脚本
-- 假脱机 gen_ins_script.sql
 -  选择 ...
-- 线轴关闭
-- 假脱机 03_CREATE_CATEGORIEDECL.log
-- @gen_ins_script.sql
-- 线轴关闭
-- 在适当的地方添加提交

或者您可以使用没有 pl/sql 块的普通 sqlplus 方法

create table CATEGORIEDECLARATION ( 
     nIdCategorieDeclaration  NUMBER(10)   not null,
     ...
constraint PK_CATDECLA primary key (nIdCategorieDeclaration)
/

 插入类别声明（nIdCategorieDeclaration、nIdTypeFormulaire、sLibelle、sType、sAide、sTexte、sTexte2、sTypeAffichage、bAffichage、sInterval、nIdCalendrier）
     SELECT (seq_CATEGORIEDECLARATION.nextval,'5','Autres cas (zone de saisie libre)', 'SOMME_A_DEDUIRE','','',NULL,'CAT_AUTRE_CAS', 1, 'POSITIF',c.nidcalendrier);
       FROM 压延机 c
      WHERE c.smillesime = '2010'
/
犯罪
/

Before checking out the snippets please keep in mind that at the moment of this writing I didn't have access to a Oracle database to test them in any way. Everything that follows is written from memory.

I assume you are using sqlplus to run your scripts. Can't you simply put the create statement and a PL/SQL block in the file?


SET serveroutput ON

spool 03_CREATE_CATEGORIEDECL.log

create table CATEGORIEDECLARATION ( 
     nIdCategorieDeclaration  NUMBER(10)   not null,
     ...
constraint PK_CATDECLA primary key (nIdCategorieDeclaration)
/


BEGIN
  select c.nidcalendrier into millesime from calendrier c where c.smillesime = '2010';
  -- Lignes relatives au formulaire CA3
  INSERT into CATEGORIEDECLARATION (nIdCategorieDeclaration,nIdTypeFormulaire,sLibelle,sType,sAide,sTexte,sTexte2,sTypeAffichage,bAffichage,sInterval,nIdCalendrier) 
  values (seq_CATEGORIEDECLARATION.nextval,'5','Autres cas  (zone de saisie libre)', 'SOMME_A_DEDUIRE','','',NULL,'CAT_AUTRE_CAS', 1, 'POSITIF',millesime);

  COMMIT;
END; 
/
spool off

Another approach is to generate the script dynamically and call it

SET serveroutput ON
SET FEEDBACK OFF
SET HEADING OFF
SET LINESIZE 800
SET PAGESIZE 0
SET ECHO OFF

SPOOL gen_cr_table_script.sql
SELECT 'create table CATEGORIEDECLARATION ( 
     nIdCategorieDeclaration  NUMBER(10)   not null,
     ...
     constraint PK_CATDECLA primary key (nIdCategorieDeclaration)
    )'
  FROM SYS.DUAL
/
SPOOL OFF

@gen_cr_table_script.sql

-- you can generate the insert script here if needed
-- spool gen_ins_script.sql
-- select ...
-- spool off
-- spool 03_CREATE_CATEGORIEDECL.log
-- @gen_ins_script.sql
-- spool off
-- add commit where appropriate

Or you can use a plain sqlplus approach without pl/sql blocks

create table CATEGORIEDECLARATION ( 
     nIdCategorieDeclaration  NUMBER(10)   not null,
     ...
constraint PK_CATDECLA primary key (nIdCategorieDeclaration)
/

INSERT INTO  CATEGORIEDECLARATION (nIdCategorieDeclaration,nIdTypeFormulaire,sLibelle,sType,sAide,sTexte,sTexte2,sTypeAffichage,bAffichage,sInterval,nIdCalendrier)
     SELECT (seq_CATEGORIEDECLARATION.nextval,'5','Autres cas  (zone de saisie libre)', 'SOMME_A_DEDUIRE','','',NULL,'CAT_AUTRE_CAS', 1, 'POSITIF',c.nidcalendrier);
       FROM calendrier c
      WHERE c.smillesime = '2010'
/
COMMIT
/

回复收藏 0 原文

~没有更多了~