oracle小知识

阅读量：4584 次

发布时间：2019-06-09

本文共 7341 字，大约阅读时间需要 24 分钟。

设置显示执行时间

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;

View Code

下面是一个时间维度表的练习，包括创建表，利用函数、存储过程、游标等知识，更新表中其他的字段

--创建时间维度表，粒度精确到天 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 ASBEGIN 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_IDMINVALUE 1      --最小值MAXVALUE 9999   --最大值START WITH 1     --从1开始INCREMENT BY 1   --增1CYCLE   --到了9999后从头开始循环,此处可以为NOCYCLENOCACHE;  --没有缓存,此处可以为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 ;

转载于:https://www.cnblogs.com/Jims2016/p/5622511.html

你可能感兴趣的文章