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;