数据仓库：oracle应用笔记

客官°小女子只卖身不卖艺 2024-03-25 19:38 44阅读 0赞

#### 数据仓库：oracle应用笔记 ####

*  存储过程
 *  merge into
 *  dual 表
 *  dblink
 *  DDL语句查询
 *  造数
 *  一些函数
 *   *  NVL
     *  TRUNC
     *  随机数
 *  造数

## 存储过程 ##

语法

CREATE OR REPLACE PROCEDURE 名称(
           参数 in/out 类型,
           datadate in varchar2,
           name out varchar2) 
    AS
    --as后接定义或赋值语句
    data := datadate;
    dt varchar2(8);
    da_t date;
    
    BEGIN
    --begin后接函数主体
      merge into xxx 
      using(select xx from xx left join on xx where xx)
      on()
      when matched then update set
      	xx = xx
      when not matched then insert(
      	x.xx,x.xxx)values(
     	 xxx,xxx
      );
      commit;
      
      *****记录日志*****
    dbms_output.put_line('SQLCODE='||SQLCODE||'%SQLERRM'||SQLERRM||'%');
    
    ****异常处理****
      EXCEPTION
        WHEN OTHERS THEN 
        dbms_output.put_line('SQLCODE='||SQLCODE||'%SQLERRM'||SQLERRM||'%');
    rollback:
    raise:
    END

## merge into ##

merge into table_name  a1   --目标表
    using (table|view|select ) a2    --数据源表 可表、视图、子查询
    on ()   --关联条件
    when matched then              --关联成立
      --更新
      update  table_name set  xxx=xxx  where xx
      --删除  
      delete from table_name where xxx=xxx  where xx
      --更新删除同存时，删除的条件要在更新条件内
    when not matched then      --关联不成立
      --插入
      insert (xxx) values (xxx)  where xx
    when not matched by source then      --当源表不存在，目标表存在
      --删除
      delete;

## dual 表 ##

--查看当前连接用户
    select user from dual;
    
    --查看当前日期、时间
    select sysdate from dual;
    select to_char(sysdate,''yyyy-mm-dd hh24:mi:ss'') from dual;
    
    --当作计算器用
    select 1+2 from dual;
    
    --测试函数
    select nvl(1,1,0) from dual;

## dblink ##

创建

CREATE DATABASE LINK 数据库链接名
    CONNECT TO 用户名
    IDENTIFIED BY 密码
    USING '’数据库连接字符串';

CREATE PUBLIC DATABASE LINK LINK_SDATA
    CONNECT TO query
    IDENTIFIED BY 123456
    USING '(DESCRIPTION =
      (ADDRESS_LIST =
         (ADDRESS = (PROTOCOL = TCP)(HOST = 10.188.245.201)(PORT = 1521))
      )
      (CONNECT_DATA =
          (SERVICE_NAME = hsmis)
      ))';

调用

--后加@连接名
    select * from xx.xxx@LINK_SDATA

## DDL语句查询 ##

--查询dblink
    select DBMS_METADATA.GET_DDL('DB_LINK','LINK_SDATA ') FROM DUAL;
    --查询建表语句
    select DBMS_METADATA.GET_DDL('TABLE','TableName') FROM DUAL;

## 造数 ##

TRUNCATE TABLE LS_CRM.T_PC_CUST_BAK;
    INSERT INTO LS_CRM.T_PC_CUST_BAK SELECT * FROM LS_CRM.T_PC_CUST WHERE CUST_ID = '2020202020';
    UPDATE LS_CRM.T_PC_CUST SET XXX = 'XXX', XXX = 'XXX' WHERE CUST_ID = '';

## 一些函数 ##

sys\_guid()  
系统随机生成主键函数

### NVL ###

nvl(colmn,a)  
如果字段colmn不为空则返回列值，为空则返回a

nvl2(colmn,a,b)  
如果字段colmn不为空则返回a，为空则返回b

drop table XXX purge

### TRUNC ###

TRUNC函数可以用来截取日期或数字。它的用法取决于传入的参数类型。

当传入的参数为日期时，TRUNC函数可以按指定的格式截取输入的日期。语法格式为：TRUNC(date \[,fmt\])，其中date为一个日期值，fmt为日期格式。该日期将按指定的日期格式截取；忽略它则由最近的日期截取1。

SELECT TRUNC(SYSDATE) FROM DUAL; --返回当前时间
    SELECT TRUNC(SYSDATE,'YY') FROM DUAL; --返回当年第一天
    SELECT TRUNC(SYSDATE,'MM') FROM DUAL; --返回当月的第一天
    SELECT TRUNC(SYSDATE,'D') FROM DUAL; --返回当前星期的第一天，即星期天

当传入的参数为数字时，TRUNC函数可以截取数字。语法格式为：TRUNC(number \[,decimals\])，其中number为待做截取处理的数值，decimals指明需保留小数点后面的位数，可选项，忽略它则截去所有的小数部分。注意：截取时并不对数据进行四舍五入1。

SELECT TRUNC(123.567,2) FROM DUAL; --123.56,将小数点右边指定位数后面的截去;
    SELECT TRUNC(123.567,-2) FROM DUAL; --100,第二个参数可以为负数，表示将小数点左边指定位数后面的部分截去，即均以0记;
    SELECT TRUNC(123.567) FROM DUAL; --123,默认截去小数点后面的部分;

### 随机数 ###

DBMS_RANDOM.VALUE*1OOOO
    --10000以内随机数
    trunc(DBMS_RANDOM.VALUE*1OOOO,2)
    --10000以内随机数,保留两位小数

## 造数 ##

将一个表中20230223日的数据全部复制插入到20230224日的数据

INSERT INTO table_name (
    column1
    , column2
    , ...
    , date_column)
    SELECT 
    column1
    , column2
    , ...
    , TO_DATE('20230224', 'YYYYMMDD')
    FROM table_name
    WHERE date_column = TO_DATE('20230223', 'YYYYMMDD');

将一个表中20230223日分区的数据全部插入到20230223日分区下

INSERT INTO table_name PARTITION (partition_name)
    SELECT * FROM table_name
    WHERE date = TO_DATE('20230223', 'YYYYMMDD');