Oracle数据库脚本整理

Oracle数据库常用SQL语句及操作详解

最新推荐文章于 2025-09-09 08:37:13 发布

转载最新推荐文章于 2025-09-09 08:37:13 发布 · 154 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：https://my.oschina.net/hoodlake/blog/136171

文章标签：

#数据库 #python

本文详细介绍了Oracle数据库的基本操作，包括创建表、序列、视图，修改表结构，操作表中数据，以及数据备份与导入导出方法。涵盖了从创建表到数据管理的全过程。

2019独角兽企业重金招聘Python工程师标准>>>

公司开发的项目，使用的数据库全部都是ORACLE,针对平时的一些使用情况，总结了一些常用的SQL语句，这里贴出来，一是分享；二是自己做个备忘

--ORACLE关键字,表名，字段最好使用大写
------------------------
--修改表结构           #
------------------------
--创建表
DECLARE
  tb_exists NUMBER; 
BEGIN
  SELECT COUNT(*) INTO tb_exists FROM tabs WHERE table_name='TB_USER';
   IF tb_exists = 0 THEN
     EXECUTE IMMEDIATE 'create table TB_USER(  
       USER_ID                   NUMBER(30),
       USER_CODE   VARCHAR2(100),
       USER_NAME   VARCHAR2(100),
		   constraint PK_TB_USER primary key (USER_ID)
		);';
   END IF;
END;
/
--创建序列
DECLARE
  VC_STR   VARCHAR2(2000);
  VN_COUNT NUMBER;
BEGIN
  SELECT COUNT(*) INTO VN_COUNT FROM USER_SEQUENCES WHERE SEQUENCE_NAME = 'CMS_REPAY_PLAN_SEQ';
  --如果没有则新增，如果有就不处理
  IF VN_COUNT = 0 THEN
    VC_STR := 'create sequence CMS_REPAY_PLAN_SEQ 
                      minvalue 1 maxvalue 99999999999999999999
                      start with 1 increment by 1 nocache';
    EXECUTE IMMEDIATE VC_STR;
  END IF;
END;
/
--创建视图
--主要是为了查询方便，但是要注意一个问题，千万不能覆盖了别人的视图，导致别人查询时少字段报错
--如果和别人视图冲突了，最好改名；如果要在视图上加字段，那必须在字段信息最全的视图上做修改
create or replace view v_query_fc_acc_all as
  select c.corp_code,c.corp_name,b.blnctr_acc,b.acc_name,
            b.acc_type_code,d.acc_type_name,b.shortcut,b.interest_rate,
            b.interest_start_date,b.acc_start_date,b.acc_open_time,
            e.cur_code,e.cur_name,g.subject_code,g.subject_name,
            b.acc_attribute,b.acc_property
      case b.acc_attribute
           when '01' then '综合户(可收可支)'
           when '02' then '收入户'
           when '03' then '支出户'
      end  acc_attribute_name,
      case b.acc_property
           when 10 then '一般'
           when 11 then '临时'
           when 12 then '基本'
           when 13 then '专户'
           when 14 then '其他'
      end as acc_property_name,
             f.net_code,f.net_name,b.agreed_deposit_sign as regular_sign ,
             b.acc_state,b.acc_state accStatea,
             b.interest_sign,b.overdraft_sign,
             b.overdraft_money,b.if_compare,b.deadline_date
  from fc_acc b ,bt_corp c , fc_acc_type d ,bt_currency e,bt_net f ,fc_subject g
  where b.corp_code=c.corp_code
  and f.net_code=b.net_code
  and b.cur_code=e.cur_code
  and g.subject_code=b.subject_code
  and d.acc_type_code=b.acc_type_code;
--操作列(主要是避免操作不存在的列)
--修改列(给用户表tb_user的user_code增加唯一性限制)
DECLARE
  VN_COUNT NUMBER;
  VC_STR   VARCHAR2(1000);
BEGIN
  SELECT COUNT(*) INTO VN_COUNT FROM USER_TAB_COLUMNS
  WHERE TABLE_NAME = 'TB_USER' AND COLUMN_NAME = 'USER_CODE';
  IF VN_COUNT = 1 THEN
    --列必须存在，才能修改其属性，否则会报错
    VC_STR := 'alter table TB_USER add constraint USER_CODE_UNIQUE unique (USER_CODE)';
    EXECUTE IMMEDIATE VC_STR;
  END IF;
END;
/
--删除主键限制
--判断表TB_USER是否已经建有名为PK_TB_USER的主键，有则删除
DECLARE
  VN_COUNT    NUMBER;
BEGIN
  SELECT COUNT(*) INTO VN_COUNT FROM user_constraints 
         WHERE constraint_type = 'P' and constraint_name = 'PK_TB_USER' and table_name = 'TB_USER';
  IF VN_COUNT <> 0 THEN
   EXECUTE IMMEDIATE 'alter table TB_USER drop constraint PK_TB_USER';
  END IF;
  COMMIT; 
END;
/
--判断表TB_USER是否已经建有名为PK_TB_USER的主键，没有则新增
DECLARE
  VN_COUNT    NUMBER;
BEGIN
  SELECT COUNT(*)INTO VN_COUNT FROM user_constraints 
         WHERE constraint_type = 'P' and constraint_name = 'PK_TB_USER' and table_name = 'TB_USER';
  IF VN_COUNT < 1 THEN
   EXECUTE IMMEDIATE 'alter table TB_USER add constraint PK_TB_USER primary key(USER_ID)';
  END IF;
  COMMIT; 
END;
/
--删除索引
--判断表TB_USER是否已经建有名为IDX_TB_USER_USER_CODE的索引，有则删除
DECLARE
  VN_COUNT NUMBER;
BEGIN
  SELECT COUNT(*)INTO VN_COUNT FROM USER_INDEXES
   WHERE INDEX_NAME = 'IDX_TB_USER_USER_CODE' AND TABLE_NAME = 'TB_USER';
  IF VN_COUNT >= 1 THEN
    EXECUTE IMMEDIATE 'drop index IDX_TB_USER_USER_CODE';
  END IF;
  COMMIT;
END;
/
--注释(注释一般紧随在创建表的语句后面，对已有的字段进行注释；或者对已经存在的表增加字段后
--所以不用判断表或者字段是否存在)
COMMENT ON TABLE  TB_USER IS '系统用户表';
COMMENT ON COLUMN  TB_USER.USER_CODE IS '系统用户编码';


------------------------
--操作表中数据         #
------------------------
--新加数据（为了避免插入重复的数据）
DECLARE
  V_COUNT NUMBER;
BEGIN
  SELECT COUNT(*) INTO V_COUNT FROM TB_USER WHERE USER_CODE = 'admin';
  IF V_COUNT = 0 THEN
  --不存在才插入
    INSERT INTO TB_USER
      (USER_ID, USER_CODE, USER_NAME)
    VALUES
      (SELECT MAX(USERID) + 1 FROM TB_USER, 'admin', 'administrator');
  END IF;
END;
/
--删除满足条件的数据(一般不会删除数据，此操作要谨慎)
--一定别忘了commit,否则后果很严重
DELETE FROM BT_USER where USER_ID='1123';
DELETE FROM BT_USER where USER_ID='1124';
COMMIT;
/

--数据备份
--导出
将数据全部导出
(导出自己的)
exp username/pwd@192.168.0.148/dbname full=y file=D:\export.dmp log=D:\hkbfull.log
(导出其他多个用户的数据库)
exp system/manager@TEST file=d:\data.dmp owner=(system,sys)
将表的数据导出到Excel： 
 spool E:\data.xlsx;
      select * from fc_subject;
 spool off; 
将表table1、table2导出
Exp btv10_cp2/btv10_cp2@192.168.0.66/bttest file=E:\data.dmp tables=(fc_subject,fc_acc)
导出表中指定的字段：
  Exp btv10_cp2/btv10_cp2@192.168.0.66/bttest file=E:\data.dmp tables=(fc_acc) query=\"where acc_name like '%银行%'\"
  
--导入
加上ignore=y ，对于已经存在的表格，将自动忽略不导入
全部导入：imp btv10_cp2/btv10_cp2@192.168.0.66/bttest  file=d:\data.dmp 
      start C:\Users\zhangfh\Desktop\sql\201306031526_ORCL.sql;
@C:\Users\zhangfh\Desktop\sql\201306031526_ORCL.sql;

导入指定的表格:
imp btv10_cp2/btv10_cp2@192.168.0.66/bttest FILE=d:\data.dmp  tables=(fc_subject)

从一个用户导到另一个用户:
imp system/manager@bttest fromuser=btv10_cp2 touser=test file=D:\data.dmp log=D:\imp.log;

转载于:https://my.oschina.net/hoodlake/blog/136171