oracle创建数据库到建表的相关sql

最新推荐文章于 2023-02-09 16:53:27 发布

原创最新推荐文章于 2023-02-09 16:53:27 发布 · 350 阅读

1 ·

CC 4.0 BY-SA版权

oracle 专栏收录该内容

1 篇文章

订阅专栏

本文介绍了如何在Oracle数据库中进行表空间和用户的创建，并提供了具体的SQL语句示例，接着详细阐述了创建表格的过程。

创建表空间及用户的sql:

-- 重复执行时，先删除
declare
  num integer;
begin
	select count(1) into num from dba_users where username = upper('kd_sale_dx') ;
	if num >0 then
		execute immediate 'drop user kd_sale_dx CASCADE ';
	end if;

	select count(1) into num from dba_tablespaces where tablespace_name = upper('KTS_KD_SALE_dx') ;
	if num >0 then
		execute immediate 'drop tablespace KTS_KD_SALE_dx including contents cascade constraints ';
	end if;
end;
/
-- 先创建表空间
create tablespace KTS_KD_SALE_dx
datafile 'D:\oradata\orcldx\KTS_KD_SALE_dx.DBF'
size 100M
reuse
 autoextend on
    next 500M
    maxsize unlimited
online
nologging
segment space management auto
default nocompress
/
-- 创建用户，密码是 1
-- Create the user 
create user KD_SALE_dx identified by "1" default tablespace KTS_KD_SALE_dx; 

-- 给用户赋权限
-- Grant/Revoke role privileges 
grant connect to KD_SALE_dx;
grant resource to KD_SALE_dx;
-- Grant/Revoke system privileges 
grant create any sequence to KD_SALE_dx;
grant create view to KD_SALE_dx;
grant drop any table to KD_SALE_dx;
grant select any table to KD_SALE_dx;
grant unlimited tablespace to KD_SALE_dx;
grant update any table to KD_SALE_dx;
grant select on dba_tables to KD_SALE_dx;
grant read, write on directory data_pump_dir to kd_sale_dx;
 GRANT SELECT ON dba_views TO kd_sale_dx;

创建表：

declare
num integer;
begin
 select count(*) into num from user_tables where table_name = upper('file_fundwhitelist');
  if num = 0 then
      execute immediate'
      create table file_fundwhitelist
      (
        tano            CHAR(2) default '' ''  not null,
        fundcode        CHAR(6) default '' '' not null,
        fundname        VARCHAR2(64)  default '' ''  
      )';
      
      execute immediate '
      comment on table FILE_FUNDWHITELIST
      is ''产品信息处理白名单'' ';
      execute immediate '
      comment on column file_fundwhitelist.tano
      is ''TA代码'' ';
      execute immediate '
      comment on column file_fundwhitelist.fundcode
      is ''基金代码'' ';
      execute immediate '
      comment on column file_fundwhitelist.fundname
      is ''基金名称'' ';
      
     execute immediate '
     alter table file_fundwhitelist
     add constraint PK_FILE_FUNDWHITELIST primary key (TANO, FUNDCODE)
     using index ';
  end if;
end;
/
-- 增加字段
declare
  cnt number;
begin
    select count(*) into cnt from  user_tab_cols t where t.table_name = 'ACK_TRANS' and t.column_name = 'ACHIEVEMENTPAY';
  if cnt <= 0 then    
    execute immediate 'alter table ACK_TRANS add ACHIEVEMENTPAY NUMBER(16,2) ';
    execute immediate 'comment on column ACK_TRANS.ACHIEVEMENTPAY  is ''业绩报酬'' ';
	end if;
end;
/