Oracle小案例

標籤：

--建立使用者CREATE USER jim IDENTIFIED BY changeit;--給使用者賦登陸串連許可權GRANT CONNECT TO jim;--給使用者賦資源許可權GRANT RESOURCE TO jim;--回收登陸許可權REVOKE CONNECT FROM jim;--回收資源操作許可權REVOKE RESOURCE FROM jim;--鎖定使用者ALTER USER jim ACCOUNT LOCK;--給使用者解鎖ALTER USER jim ACCOUNT UNLOCK;--給使用者修改密碼ALTER USER jim IDENTIFIED BY changeit;--建立時間維度表CREATE TABLE DIM_DATE(    Date_ID char(8) primary key , --20160704    Date_Name date,  --2016/7/4    Year number,    --2016    Year_Half char(6), --2016H2    Year_Month char(6),  --201607    Year_Quarter char(6),-- 2016Q3    Half_Number number,--2    Half_Name char(2), --H2    Quarter_Number number,--3    Quarter_Name char(2),--Q3    Month_Number number,--7    Month_Name nvarchar2(20),--JUNL    Week_Number number, --2    Week_Name nvarchar2(20),--Monday    Creat_Date date,   --建立時間    Update_Date date) --更新時間    ;        --測試選出一段時間    select to_date(‘2014-01-01‘,‘YYYY-MM-DD‘)+(rownum-1) from dual     connect by rownum<=(to_date(‘2015-01-01‘,‘YYYY-MM-DD‘)-to_date(‘2014-01-01‘,‘YYYY-MM-DD‘));        with     temp_date as (        select to_date(‘2014-01-01‘,‘YYYY-MM-DD‘)+(rownum-1) from dual          connect by rownum<=(to_date(‘2015-01-01‘,‘YYYY-MM-DD‘)-to_date(‘2014-01-01‘,‘YYYY-MM-DD‘))    )    select * from temp_date;    --建立預存程序，匯入時間    CREATE OR REPLACE PROCEDURE SP_ADD_DATE    AS    v_Half number;    v_Quarter number;    v_Month DIM_Date.Month_Name%type;    v_Month_Number number;    CURSOR c_date IS    SELECT Date_ID,DIM_Date.Date_Name FROM DIM_DATE;    c_row c_date%rowtype;    BEGIN      MERGE INTO DIM_DATE T1      USING(            select to_date(‘2014-01-01‘,‘YYYY-MM-DD‘)+(rownum-1) date_name from dual          connect by rownum<=(to_date(‘2018-01-01‘,‘YYYY-MM-DD‘)-to_date(‘2014-01-01‘,‘YYYY-MM-DD‘))       ) temp_Date      on (t1.date_name=temp_Date.date_name)      when matched then        update set t1.date_id=to_char(temp_Date.date_name,‘YYYYMMDD‘)      when not matched then        insert (Date_ID) values(to_char(temp_Date.date_name,‘YYYYMMDD‘));        --迴圈遊標        FOR c_row in c_date loop          v_Month_Number:=to_number(to_char(c_row.Date_Name,‘MM‘));          if v_Month_Number>6 then           v_Half:=2;           else             v_Half:=1;             end if;          update DIM_DATE set          DIM_DATE.DATE_NAME=to_date(c_row.Date_ID,‘YYYY-MM-DD‘),          DIM_DATE.HALF_NAME=‘H‘||v_Half   --後面在這裡補充          where  DIM_DATE.DATE_ID=c_row.Date_ID;          end loop;      END;    

 

Oracle小案例