oracle小知识
设置显示执行时间
set timing on;
不显示:off
最简单的块：
begin dbms_output.put_line('Hello,World'); end;
执行结果出现这种情况（执行成功，但是不显示字符串）：
这时的解决办法是：在执行块之前，先执行这条语句： set serveroutput on （可以理解为开关，把输出这个功能打开）
这时就成功了。
格式化命令：
设置每行显示的记录长度： SET LINESIZE 300
设置每页显示的记录长度：SET PAGESIZE 30
使用@执行程序
查看所有的数据表： SELECT * FROM tab;
查看当前的用户：show user
切换登陆用户： CONN 用户名/密码 【as sysdba】
对于每一张表，都有其属于的用户，不同的用户想要访问的其它用户的表，需要这张表的完整名称，及用户名.表名称
调用本机的指令，在前面加 HOST ，例如 HOST copy d:\demo.sql d:hello.sql
游标
一.For循环游标
基本的步骤是: 1.定义游标----2.定义游标变量----3.循环游标
--声明游标 CURSOR cursor_Name IS select_sattement --For循环游标 --(1)定义游标 --（2）定义游标变量 --（3）使用For循环来使用这个游标 declare cursor c_job --定义游标 is select emp.empno,emp.ename,emp.job,sal from emp where job='MANAGER'; --定义游标变量 c_row c_job%rowtype; begin for c_row in c_job loop --循环游标 dbms_output.put_line(c_row.empno||'-'||c_row.ename||'-'||c_row.job||'-'||c_row.sal); end loop; end;
二.Fetch游标
--Fetch游标 --使用的时候必须要明确的打开和关闭 declare cursor c_job is select empno,ename,job,sal from emp where job!='MANAGER'; c_row c_job%rowtype; begin open c_job; loop fetch c_job into c_row; exit when c_job%notfound; dbms_output.put_line(c_row.empno||'-'||c_row.ename||'-'||c_row.job||'-'||c_row.sal); end loop; close c_job; end;
下面是一个根据日期，更新表中每一行的是周几的游标例子
--游标练习,根据日期，更新每行是周几 DECLARE CURSOR c_date IS select DIM_TIME.DATE_ID,DIM_TIME.YEAR_MONTH FROM DIM_TIME; c_row c_date%rowtype; begin for c_row in c_date loop update DIM_TIME set Dim_Time.Week_Name=to_char(to_Date(c_row.DATE_ID,'YYYYMMDD'),'DAY') where Dim_Time.Date_Id=c_row.Date_ID; end loop; end;
下面是一个时间维度表的练习，包括创建表，利用函数 、存储过程 、游标等知识，更新表中其他的字段
--创建时间维度表，粒度精确到天 CREATE TABLE DIM_TIME( Date_ID char(8) primary key, --时间主键 例如20160703 Year_Month char(6), --年月 201607 Year number, --年 2016 Year_Quarter char(6), --年季度 2016Q3 Year_Half char(6), --年和上下半年 2016H2 Half_Number number, --上下半年 2 Half_Name char(2), --上下半年名称 H2 Quarter_Name char(2), --季度名称 Q3 Quarter_Number number, --季度 3 Month char(3), --月份 JUN Month_Number number, --7 Month_Name varchar2(10), --JUNL Month_Dedak char(4), --月和上中下旬 07D1 Week char(3), --星期 SUN Week_Number number, --1 Week_Name varchar2(10)); --SUNDAY --约束 /*限定月份在1至12月之间*/ ALTER TABLE DIM_TIME add constraint MonthCheck check(Month in('Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec')); ALTER TABLE DIM_TIME add constraint MonthNumberCheck check(Month_Number in (1,2,3,4,5,6,7,8,9,10,11,12)); alter table DIM_TIME drop constraint MonthCheck; --华丽的分割线----- /*此处练习存储过程和Merge into 的用法*/ CREATE OR REPLACE PROCEDURE SP_Update_Date ( uDate_ID char ) IS BEGIN -- UPDATE DIM_TIME SET DIM_TIME.DATE_ID=uDate_ID -- WHERE DIM_TIME.DATE_ID='20160701'; merge into Dim_Time t using (select * from DIM_TIME) v on (t.date_ID=v.date_ID) when matched then update set t.year_month=Substr(v.date_id,1,6), t.year=Substr(v.date_id,1,4), t.year_quarter=Substr(v.date_id,1,4), t.year_half=Substr(v.date_id,1,4), t.half_number=2, t.half_name='H2', t.quarter_number=3, t.quarter_name='Q3', t.month='Jul', t.month_number=7, t.month_name='Jule'; END; --根据日期获取上半年还是下半年的函数 CREATE OR REPLACE FUNCTION getYearHalf(iDate Date) RETURN number IS v_month char(2); half number; begin select to_char(iDate,'MM') into v_month from dual; half :=to_number(v_month) ; if half>6 then half:=2; else half:=1; end if; return half; end; --游标练习,根据日期，更新每行是周几（块的练习） DECLARE CURSOR c_date IS select DIM_TIME.DATE_ID,DIM_TIME.YEAR_MONTH FROM DIM_TIME; c_row c_date%rowtype; begin for c_row in c_date loop update DIM_TIME set Dim_Time.Week_Name=to_char(to_Date(c_row.DATE_ID,'YYYYMMDD'),'DAY') where Dim_Time.Date_Id=c_row.Date_ID; end loop; end; --创建空的存储过程 CREATE OR REPLACE PROCEDURE SP_Upadte AS BEGIN NULL; END; --创建带参数的存储过程 CREATE OR REPLACE PROCEDURE SP_Upadte ( Id in number, Name out nvarchar2 ) AS Sex char(2); Class varchar2(20); BEGIN NULL; END; --创建更新date的存储过程（利用游标） CREATE OR REPLACE PROCEDURE SP_Update_Date AS v_Half number; v_Quarter number; v_Month VARCHAR2(10); v_Dedak number; v_Week VARCHAR2(10); CURSOR c_date IS SELECT DATE_ID,YEAR_MONTH FROM DIM_TIME; c_row c_date%rowtype; BEGIN for c_row in c_date loop --循环游标 v_Month:=to_char(to_Date(c_row.DATE_ID,'YYYYMMDD'),'MONTH'); --月份名称 v_Week:=to_char(to_Date(c_row.DATE_ID,'YYYYMMDD'),'DAY'); --星期名称 v_Dedak:=to_number(Substr(c_row.date_id,7,2));--（日期最后几号）日 if(to_number(to_char(to_Date(c_row.DATE_ID,'YYYYMMDD'),'MM'))<7) then v_Half:=1; --上半年 else v_Half:=2; --下半年 end if; if(to_number(to_char(to_Date(c_row.DATE_ID,'YYYYMMDD'),'MM'))<4) then v_Quarter:=1; --季度 elsif(to_number(to_char(to_Date(c_row.DATE_ID,'YYYYMMDD'),'MM'))<7) then v_Quarter:=2; elsif(to_number(to_char(to_Date(c_row.DATE_ID,'YYYYMMDD'),'MM'))<10) then v_Quarter:=3; else v_Quarter:=4; end if; if v_Dedak<11 then v_Dedak:=1; elsif v_Dedak<21 then v_Dedak:=2; else v_Dedak:=3; end if; --这里也可以直接调用写好的函数 -- v_Half:=getYearHalf(to_Date(c_row.DATE_ID,'YYYYMMDD')); update DIM_TIME set year_month =Substr(c_row.date_id,1,6), --根据Date_ID得到year_Month year=Substr(c_row.date_id,1,4), --得到年份 year_quarter=Substr(c_row.date_id,1,4)||'Q'||v_Quarter, --得到年和季度 year_half=Substr(c_row.date_id,1,4)||'H'||v_Half, half_number=v_Half, half_name='H'||v_Half, quarter_name= 'Q'||v_Quarter, quarter_number= v_Quarter, month=Substr(v_Month,1,3), month_number=to_char(to_Date(c_row.DATE_ID,'YYYYMMDD'),'MM'), month_name=v_Month, MONTH_DEDAK=Substr(c_row.date_id,5,2)||'D'||v_Dedak, week=Substr(v_Week,1,3), week_number=to_number(to_char(to_Date(c_row.DATE_ID,'YYYYMMDD'),'D')), week_name=v_Week where DIM_TIME.DATE_ID=c_row.DATE_ID; end loop; END;
Oracle创建自增字段方法（序列：SEQUENCE）
1.最简单的序列，从1开始，自增1
--创建单独的序列 CREATE SEQUENCE DIM_TIME_log_id_seq increment by 1 start with 1;
2.稍微完整点的序列是如下：
--创建序列 CREATE SEQUENCE SEQ_ID MINVALUE 1 --最小值 MAXVALUE 9999 --最大值 START WITH 1 --从1开始 INCREMENT BY 1 --增1 CYCLE --到了9999后从头开始循环,此处可以为NOCYCLE NOCACHE; --没有缓存,此处可以为CACHE 10
两个重要的函数：
- SEQ_ID.CURRVAL 返回SEQ_ID的当前值
- SEQ_ID.NEXTVAL 返回SEQ_ID的下一个值（增加后的至）
Oracle返回一个时间段
比如从2013年1月1日到2014年1月1日的所有日期
SELECT DATE'2013-1-1'+(ROWNUM-1) FROM dual CONNECT BY rownum < (date'2014-1-1'-date'2013-1-1');
还有从某个日期往后多少天
SELECT to_date('2013-1-1','YYYY-MM-DD')+(rownum-1) from dual CONNECT BY rownum<=10;
SQL设置显示执行时间
SET TIMING ON ; //不显示即为off；
SET TIMING ON;
ORACLE实现分页的功能：
SELECT * FROM (SELECT A.*, ROWNUM RN FROM (SELECT * FROM TABLE_NAME) A WHERE ROWNUM<=10) WHERE RN>=5 ;
