oracle 在 pl/sql 中创建表

table creation in pl/sql

提问by jayjaypg22

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

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

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 :

我的想法是在 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

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?

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

回答by Tony Andrews

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

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

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

回答by mulander

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.

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

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?

我假设您正在使用 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

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

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

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
/