Oracle 常用sql

1.select * from new_table union all select * from old_table; //加
    select * from new_table minus select * from old_table;   //减

 

判断选择两日期中最小的那个：

 select  case  when  sysdate<(select to_date(to_char(add_months(sysdate,1),'YYYY-MM')||'-01','YYYY-MM-DD')  from dual)  then  'small'  else  'big' end from dual

 

2.转义字符 escape
select * from bonus  t where t.ename like 'S\_%' escape '\'
表示\是转义字符，对某些关键字无法模糊查询，对_(关键字)进行转义

 

3.2、数值函数
 round 四舍五入
 select round(45.935,2) from dual;  结果45.94 第二个参数表示保留小数点后两位
 trunc 截取
 select trunc(45.935,2) from dual; 结果45.90   第二个参数表示截取到小数点后两位
3、日期函数
 add_mounths(sysdate,-5)  延迟五月
 add_mounths(sysdate,-5*12) 延迟五年
 sysdate-5 延迟五天
 to_char(sysdate,'yyyy-mm-dd'),to_date('2008-01-12','yyyy-mm-dd'),to_number() 日期格式转换

4、分页显示

一般代码中对结果集进行分页就是这么干的。即，先生成rownum后对列过滤，
  select * from (select a.*,rownum r from emp a) where r between 5 and 10

 

（1）select * from (select ename,job,sal,rownum no from emp) where no>=5 and no<=8;
 （2）相对第一种，当数据量大时，第二种效率明显高
   select * from (select ename,job,sal,rownum no from emp where rownum<=8) where no>=5 and no<=8;

 

5.for

 

Oracle里面有一个
  for c in select a,b from c loop
  //此处可以使用 c.a, c.b 等等
  end loop

这样用比游标的好处是不用再定义若干个变量供fetch into

因为我要操作的记录集还有字段比较多，要是用fetch cursor into @a, @b, @c,.....//这里可能要几百个变量了

例：

CREATE OR REPLACE
FUNCTION FO RETURN VARCHAR2 AS
n number;
BEGIN
  --ids:=0;
  for h in (select holiday_id hid from T_ATTE_HOLIDAYS) loop
   n:=h.hid;
   dbms_output.put_line('ids'|| to_char(h.hid)||to_char(n));
  end loop;
  RETURN NULL;
END FO;

 

6.MERGE INTO products p
     USING newproducts np
     ON (p.product_id = np.product_id)
     WHEN MATCHED THEN
     UPDATE
     SET p.product_name = np.product_name,
     p.category = np.category;  //Table: products, newproducts. 修改products表中的数据

7.触发器（当某个表修改时，把相关数据添加到另一个表中）

CREATE OR REPLACE
TRIGGER T3
 AFTER UPDATE ON TEST
 FOR EACH ROW
BEGIN
 insert into test2(id,name) values(:old.id,:OLD.name);
 if updating then
 dbms_output.put_line('updating');
 elsif inserting then
 dbms_output.put_line('inserting');
 elsif deleting then
 dbms_output.put_line('deleting');
 end if;
END;

 

8.id自增：

create or replace TRIGGER USER2_INS
 BEFORE INSERT ON USER2
 for each row
BEGIN
 select user2_seq.nextval into:New.id from dual;
END;

 

9.外键：

alter table US
add constraint us_user2_fk foreign key (USERID)
references USER2 (ID) on delete cascade;  //级联delete，删除了和它有关的所有行,这个表的多行被再删除

 

alter table US
add constraint us_score_fk foreign key (scoreID)
references score (ID) ON DELETE SET NULL; //级联delete,只删除这个表中的一行，但是和score(id)有关的内容全是null（不推荐使用，和上面的差不多）

 

 

alter table US
add constraint us_user2_fk foreign key (userID)
references user2 (ID); //推荐使用，默认不级联

 

alter table US
drop constraint us_user2_fk;