Oracle数据操作脚本

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -关于 sql - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - 1.批量生成 sql语句(检测表中主键字段作于是否含有空格)
select 'select * from ' || b. table_name || ' where ' || 'substr('||b. column_name|| ',0,1)=' ' ' ' ' || 'or substr('||b. column_name || ',length('||b. column_name|| '),1)=' ' ' ''
from user_constraints a,user_cons_columns b
where a. CONSTRAINT_NAME = b. constraint_name
      AND a.CONSTRAINT_TYPE = 'P'
      AND b. constraint_name not like 'SYS%'
      AND b. table_name IN ( select TABLE_NAME from user_tables)
ORDER BY b. TABLE_NAME
- - 2.关于锁表问题的解决：
    select * from v$locked_object ; - -查看被锁定的数据，得到object_id在dba_objects中可以查看出来被锁定的表
    select * from dba_objects where object_id = '119689'; - -可以看出来哪个表被锁住
    select * from v$ session where  sid = 371; - -从v$locked_object得到sid
    alter system kill session '371,274'; - -传入sid和 serial值
- - 3.修改
    a.修改约束(先增后删)：
    b.修改表中子段类型:
        b1. alter table code_cwxxb modify hlks varchar2( 4); - -将表code_cwwxxb中hlks字段类型改为:varchar2( 4)
        b2.修改 char类型为 varchar类型(使用转换的思想) - -先改名,再添加,后赋值,最后删除
            alter table zy_cnext_hf rename column hlks to hlks_back
            alter table zy_cnext_hf a a.hlks varchar2( 4)
            update zy_cnext_hf a set a.hlks =( select trim(b.hlks_back) from zy_cnext_hf b where a.rowid =b.rowid)
            alter table zy_cnext_hf drop column hlks_back
- - 4.关于oracle中的系统表：注意(对于字段区分大小写)
    select * from user_tables - -查出该登陆用户系统中的所有表
    select * from user_tab_columns - - 查出登陆用户系统中所有表的列
    select * from dba_tables - -查询系统中所有的表
- - 5.关于系统表的比对：
    - - 1 >.缺少的表
SELECT C. *
  FROM ( SELECT A. TABLE_NAME A_TABLE,
               B. TABLE_NAME B_TABLE
          FROM ( SELECT TABLE_NAME
                  FROM Dba_Tables
                  WHERE OWNER = 'TPHIS_TH'  - -用标准库代替
                       AND TABLE_NAME NOT LIKE ( 'STU%')
                       AND TABLE_NAME NOT LIKE ( 'T_CON%')
                       AND TABLE_NAME NOT LIKE ( 'KC%')
                       AND TABLE_NAME NOT LIKE ( 'WJDM%')
                       AND TABLE_NAME NOT LIKE ( 'CWTJ%')) A
                LEFT JOIN
                ( SELECT TABLE_NAME
                   FROM Dba_Tables
                   WHERE OWNER = 'TPHIS_MC' - -用被比较的库代替
                       AND TABLE_NAME NOT LIKE ( 'STU%')
                       AND TABLE_NAME NOT LIKE ( 'T_CON%')
                       AND TABLE_NAME NOT LIKE ( 'KC%')
                       AND TABLE_NAME NOT LIKE ( 'WJDM%')
                       AND TABLE_NAME NOT LIKE ( 'CWTJ%')) B
                  ON A. TABLE_NAME = B. TABLE_NAME) C
WHERE C.B_TABLE IS NULL  
ORDER BY C.A_TABLE

    - - 2 >.缺少的视图或视图的内容不一致
SELECT C. *
  FROM ( SELECT A.VIEW_NAME   A_VIEW,
               A.TEXT_LENGTH A_TEXT_LENGTH,
               B.VIEW_NAME   B_VIEW,
               B.TEXT_LENGTH B_TEXT_LENGTH              
          FROM ( SELECT VIEW_NAME,TEXT_LENGTH
                  FROM DBA_VIEWS
                  WHERE OWNER = 'TPHIS_TH'  - -用标准库代替
               ) A
               LEFT JOIN
               ( SELECT VIEW_NAME,TEXT_LENGTH
                   FROM DBA_VIEWS
                   WHERE OWNER = 'TPHIS_MC' - -用被比较的库代替
               ) B
               ON A.VIEW_NAME = B.VIEW_NAME) C
WHERE C.B_VIEW IS NULL OR C.A_TEXT_LENGTH <> C.B_TEXT_LENGTH 
ORDER BY C.A_VIEW

    - - 3 >.缺少字段或类型不同或长度不同
SELECT C. * - - DISTINCT C.A_TABLE
   FROM ( SELECT  A. TABLE_NAME  AS A_TABLE,
                 A. COLUMN_NAME AS A_COL,
                 A.DATA_TYPE   AS A_TPYE,
                 A.DATA_LENGTH AS A_LEN,     
                 B. TABLE_NAME  AS B_TABLE,
                 B. COLUMN_NAME AS B_COL,
                 B.DATA_TYPE   AS B_TPYE,
                 B.DATA_LENGTH AS B_LEN                  
            FROM ( SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE,DATA_LENGTH
                    FROM Dba_Tab_Columns
                    WHERE OWNER = 'TPHIS_TH'  - -用标准库代替
                         AND TABLE_NAME NOT LIKE ( 'STU%')
                         AND TABLE_NAME NOT LIKE ( 'T_CON%')
                         AND TABLE_NAME NOT LIKE ( 'KC%')
                         AND TABLE_NAME NOT LIKE ( 'WJDM%')
                         AND TABLE_NAME NOT LIKE ( 'CWTJ%')) A
                  LEFT JOIN
                  ( SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE,DATA_LENGTH
                     FROM Dba_Tab_Columns
                     WHERE OWNER = 'TPHIS_MC'  - -用被比较的库代替
                         AND TABLE_NAME NOT LIKE ( 'STU%')
                         AND TABLE_NAME NOT LIKE ( 'T_CON%')
                         AND TABLE_NAME NOT LIKE ( 'KC%')
                         AND TABLE_NAME NOT LIKE ( 'WJDM%')
                         AND TABLE_NAME NOT LIKE ( 'CWTJ%')) B
                   ON A. COLUMN_NAME = B. COLUMN_NAME
                        AND A. TABLE_NAME =B. TABLE_NAME ) C
     WHERE  C.B_COL IS NULL OR ( C.A_TPYE <> C.B_TPYE OR C.A_LEN <> C.B_LEN ) 
     ORDER BY C.A_TABLE, C.A_COL
    


    - - 4 >.表中缺少存储
SELECT C.A_NAME, C.B_NAME
  FROM ( SELECT A. NAME A_NAME, B. NAME B_NAME
          FROM ( SELECT  DISTINCT NAME
                  FROM DBA_SOURCE
                 WHERE OWNER = 'TPHIS_TH'  - -用标准库代替
                   AND TYPE = 'PROCEDURE') A
                LEFT JOIN
               ( SELECT  DISTINCT NAME
                   FROM DBA_SOURCE
                   WHERE OWNER = 'TPHIS_MC'  - -用被比较的库代替
                       AND TYPE = 'PROCEDURE') B
               ON A. NAME = B. NAME) C
WHERE C.B_NAME IS NULL
ORDER BY C.A_NAME

    - - 5 >.表中缺少函数
SELECT C.A_NAME, C.B_NAME
  FROM ( SELECT A. NAME A_NAME, B. NAME B_NAME
          FROM ( SELECT  DISTINCT NAME
                  FROM DBA_SOURCE
                 WHERE OWNER = 'TPHIS_TH'  - -用标准库代替
                       AND TYPE = 'FUNCTION') A
                LEFT JOIN
               ( SELECT  DISTINCT NAME
                   FROM DBA_SOURCE
                   WHERE OWNER = 'TPHIS_MC'  - -用被比较的库代替
                       AND TYPE = 'FUNCTION') B
               ON A. NAME = B. NAME) C
WHERE C.B_NAME IS NULL
ORDER BY C.A_NAME

    - - 6 >表中存储或函数内容不一样
SELECT *
FROM ( SELECT A. TYPE A_TYPE,
             A. NAME A_NAME,
             A.TEXT A_TEXT,
             B.TEXT B_TEXT
      FROM ( SELECT TYPE, NAME, LINE, TEXT
               FROM DBA_SOURCE
               WHERE OWNER = 'TPHIS_DEV'  - -用标准库代替
                     AND (( TYPE = 'PROCEDURE') OR ( TYPE = 'FUNCTION') OR ( TYPE = 'TRIGGER') OR ( TYPE = 'PACKAGE'))) A
            LEFT JOIN
            ( SELECT TYPE, NAME, LINE, TEXT
               FROM DBA_SOURCE
               WHERE OWNER = 'TPHIS_LN'  - -用被比较的库代替
                     AND (( TYPE = 'PROCEDURE') OR ( TYPE = 'FUNCTION') OR ( TYPE = 'TRIGGER') OR ( TYPE = 'PACKAGE'))) B
            ON A. NAME = B. NAME  AND  length(replace(to_char(decode(A.TEXT, null, ' ',A.TEXT)), ' ', ''))
            = length(replace(to_char(decode(B.TEXT, null, ' ',B.TEXT)), ' ', ''))) C
WHERE C.B_TEXT IS NULL
ORDER BY C.A_NAME, C.A_TEXT;

    - - 7 >表中缺少包
SELECT C.A_NAME, C.B_NAME
  FROM ( SELECT A. NAME A_NAME, B. NAME B_NAME
          FROM ( SELECT  DISTINCT NAME
                  FROM DBA_SOURCE
                 WHERE OWNER = 'TPEMR_TH'  - -用标准库代替
                       AND TYPE = 'PACKAGE') A
                LEFT JOIN
               ( SELECT  DISTINCT NAME
                   FROM DBA_SOURCE
                   WHERE OWNER = 'TPEMR_ZH0621'  - -用被比较的库代替
                       AND TYPE = 'PACKAGE') B
               ON A. NAME = B. NAME) C
WHERE C.B_NAME IS NULL
ORDER BY C.A_NAME
   
- - 6系统函数：
    substr(bmdm, 0, 1) - -从第一位开始截取一位
    substr(bmdm, length(bmdm), 1) - -从最后一位开始截取一位
- - 7.为表中特定的列添加说明
    comment on column CODE_YPDM.CBBZ
is '0没有拆包1已拆包'

- - 8.关于表之间缺少字段或类型不同或长度不同
SELECT C. * - - DISTINCT C.A_TABLE
   FROM ( SELECT  A. TABLE_NAME  AS A_TABLE,
                 A. COLUMN_NAME AS A_COL,
                 A.DATA_TYPE   AS A_TPYE,
                 A.DATA_LENGTH AS A_LEN,     
                 B. TABLE_NAME  AS B_TABLE,
                 B. COLUMN_NAME AS B_COL,
                 B.DATA_TYPE   AS B_TPYE,
                 B.DATA_LENGTH AS B_LEN                  
            FROM ( SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE,DATA_LENGTH
                    FROM Dba_Tab_Columns
                    WHERE OWNER = 'TPEMR_DEV'  - -用标准库代替
                         AND TABLE_NAME = ''
                         ) A
                  LEFT JOIN
                  ( SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE,DATA_LENGTH
                     FROM Dba_Tab_Columns
                     WHERE OWNER = 'TPEMR_DEV'  - -用被比较的库代替
                         AND TABLE_NAME = ''
                        ) B
                   ON A. COLUMN_NAME = B. COLUMN_NAME
                        AND A. TABLE_NAME =B. TABLE_NAME ) C
    WHERE  C.B_COL IS NULL OR ( C.A_TPYE <> C.B_TPYE OR C.A_LEN <> C.B_LEN ) 
    ORDER BY C.A_TABLE, C.A_COL;
   
    - -实际应用比对tpemr与tphis同名字典表
    SELECT C. * - - DISTINCT C.A_TABLE
  FROM ( SELECT A. TABLE_NAME  AS A_TABLE,
               A. COLUMN_NAME AS A_COL,
               A.DATA_TYPE   AS A_TPYE,
               A.DATA_LENGTH AS A_LEN,
               B. TABLE_NAME  AS B_TABLE,
               B. COLUMN_NAME AS B_COL,
               B.DATA_TYPE   AS B_TPYE,
               B.DATA_LENGTH AS B_LEN
          FROM ( SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_LENGTH
                  FROM Dba_Tab_Columns
                 WHERE OWNER = 'TPHIS_DEV' - -用标准库代替
                   AND TABLE_NAME in
                       ( select table_name
                          from dba_tables
                         where owner = 'TPEMR_DEV'
                           and table_name like '%CODE%'
                           and table_name in
                               ( select table_name
                                  from dba_tables
                                 where owner = 'TPHIS_DEV'
                                   and table_name like '%CODE%'))) A
          LEFT JOIN ( SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_LENGTH
                      FROM Dba_Tab_Columns
                     WHERE OWNER = 'TPEMR_DEV' - -用被比较的库代替
                       AND TABLE_NAME in
                           ( select table_name
                              from dba_tables
                             where owner = 'TPEMR_DEV'
                               and table_name like '%CODE%'
                               and table_name in
                                   ( select table_name
                                      from dba_tables
                                     where owner = 'TPHIS_DEV'
                                       and table_name like '%CODE%'))) B ON A. COLUMN_NAME =
                                                                            B. COLUMN_NAME
                                                                        AND A. TABLE_NAME =
                                                                            B. TABLE_NAME) C
WHERE C.B_COL IS NULL
    OR ( C.A_TPYE <> C.B_TPYE OR C.A_LEN <> C.B_LEN)
ORDER BY C.A_TABLE, C.A_COL;
- - 9.关于数据库完整性 sql
- -eg：code_ypdm <规格和计量单位存在对应不一致 >
select 'code_ypdm' table_name,
       ypdm|| '(ypdm)      '||ypmc|| '(ypmc)      '||dw|| '(dw)      '||jldw|| '(jldw)      '||gg|| '(gg)      '||jlbl|| '(jlbl)      ' Error_Field,
       '在规格和剂量单位的处理上可能存在问题' Error_Explain
from ( select case when substr(gg, 1, 2) = '0.'
                    then substr(gg, 2, length(to_char(jlbl)))
                  else
                    substr(gg, 1, length(to_char(jlbl)))
             end as gg0,
             to_char(jlbl) as jlbl0, length(to_char(jlbl)),
             gg,jlbl,ypdm,dw,jldw,ypmc
      from ( select replace(gg, ' ', '') as gg,ypdm,dw,jlbl,jldw,ypmc from code_ypdm))
where gg0 <> jlbl0 and ypdm not in ( select ypdm from code_ypdm where dw = jldw and jlbl = 1)
- - 10.修改统计报表中名称字段长度
select 'alter table ' || table_name || ' modify ' || column_name || ' ' ||
       data_type || '(' || data_length || ');'
  from ( select table_name,
               column_name,
               data_type,
               ( select data_length
                from ( select column_name, max(data_length) as data_length
                      from user_tab_columns
                      where table_name not like 'BIN%'
                      and column_name in
                               ( select distinct column_name
                                from user_tab_columns
                                where table_name in ( select table_name from user_tables where table_name like 'TJ%SHOW')
                                and column_name like '%MC')
                      group by column_name
                      order by column_name) a
                where a. column_name = b. column_name) as data_length
        from user_tab_columns b
        where table_name in ( select table_name from user_tables where table_name like 'TJ%SHOW') and column_name like '%MC'
        order by table_name)
- - 11.解决操作员主键报错问题
select * from user_source where name = 'TRI_CODE_KSDM_INSERT'
select * from user_source where name = 'TRI_CODE_KSDM_UPDATE'
insert into his_dict_dept (dept_id,dept_name, input,clinic_attr,last_modify_time,outp_or_inp,sno)
                          select ksdm,ksmc,pydm, 1,xgsj,mzzy,sxh
                          from   code_ksdm
                          where  ksdm not in ( select dept_id from his_dict_dept);
- - 12.关于job的创建
    declare job1 number;
    begin
    dbms_job.submit(job1, 'PJ_CWSYQQ_SHOW2;',sysdate, 'sysdate+1');
    - -(参数 1:job号,参数 2:存储名称,参数 3:下次执行时间,参数 4:每次执行的间隔时间)
    end;
    begin
    dbms_job.remove(jobno);  - -删除
    end;
    begin
    dbms_job.next_date(job,next_date); - -修改下次执行时间
    end;
    begin
    dbms_job. interval(job, interval); - -修改间隔执行时间
    end;
- - 13.在v$ session表中显示ip信息以及触发器的创建
    - - 1).trriger实现:
        create or replace trigger on_logon_trriger
        after logon on database
        begin
        dbms_application_info.set_client_info(sys_context( 'userenv', 'ip_address'));
        end;
    - - 2).查询当前登录客户端的机器名和ip地址
        select machine,client_info from v$ session where audsid =userenv( 'sessionid');
    - - 3).授权用户实现v$ session的使用(对用户tpsoft_lp1011分配访问v$ session的权限)
        grant select on v$ session to tpsoft_lp1011
    - - 4).创建实例:
        - -函数:
        create or replace function fun_isdb_ipaddress
        return varchar2 as
            client_ipaddress varchar2( 50);
        begin
            select client_info into client_ipaddress from v$ session where audsid =userenv( 'sessionid');
        return client_ipaddress;
        end;
        - -关于添加的触发器:
        create or replace trigger tri_sis_xtcs_log_insert
        after insert on sis_xtcs
        for each row
        declare ip varchar( 50);
                mc varchar( 50);
        begin
            select machine into mc from v$ session where audsid =userenv( 'sessionid');
            select client_info into ip from v$ session where audsid =userenv( 'sessionid');
            begin
                insert into sis_xtcs_log(xtmk,csmc,xgpb,csz,mrz,bz,xgrdm,xgrmc,xglb,xgsj)
                values(: new.xtmk,: new.csmc,: new.xgpb,: new.csz,: new.mrz,: new.bz,ip,mc, 'INSERT',sysdate);
            end;
        end tri_sis_xtcs_log;
        - -关于删除的触发器:
        create or replace trigger tri_sis_xtcs_log_delete
        after delete on sis_xtcs
        for each row
        begin
            begin
            insert into sis_xtcs_log(xtmk,csmc,xgpb,csz,mrz,bz,xgrdm,xgrmc,xglb,xgsj)
                values(: old.xtmk,: old.csmc,: old.xgpb,: old.csz,: old.mrz,: old.bz, 'temp', 'temp', 'DELETE',sysdate);
            end;
        end tri_sis_xtcs_log;
- - 14.所有表空间的使用情况
    select b.file_id 文件ID号,b.tablespace_name 表空间名,b.bytes / 1024 / 1024|| 'M' 字节数,
        (b.bytes - sum(nvl(a.bytes, 0))) / 1024 / 1024|| 'M' 已使用, 100 - sum(nvl(a.bytes, 0)) /(b.bytes) * 100 占百分比,
        sum(nvl(a.bytes, 0)) / 1024 / 1024|| 'M' 剩余空间
    from dba_free_space a,dba_data_files b
    where a.file_id =b.file_id
    group by b.tablespace_name,b.file_id,b.bytes
    order by b.file_id
- - 15.查看用户默认的表空间
    select a.username,a.default_tablespace from dba_users a
- - 3.查看要扩展的表空间使用的数据文件路径与名字
    select * from dba_data_files where tablespace_name = 'TSP_TPHY'
- - 4.扩展表空间
    alter tablespace TSP_TPHY
    add datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\TPHY\TSP_TPHY1.DBF' size 500M
    autoextend on
    next 50M
    maxsize 3000M