oracle sql 函数

select * from emp;
CREATE TABLE emp3 as select * from emp where DEPNO = '30';
select * from emp3;
-- 两个表求并集 去除重复的
select * FROM emp;
union
select * FROM emp3;

-- union如果列和类型不一样那么要对列进行处理 用转化的方式让上下两个列的类型和数量保持一致
select employee_id emp_id,department_id,TO_CHAR(null)
from employee01
union
select TO_NUMBER(null),department_id,department_name
from dempartment;

-- 两个表求并集 包括重复的
select * FROM emp;
union all
select * FROM emp3;
-- intersect 两个表求交集
select * from emp
INTERSECT
select * from emp3;
-- MINUS 从emp表中去除emp和emp3重复的数据 也就是求差集
select * from emp
minus
select * from emp3;

 

select * from emp;
-- oracle 函数
-- 字符串 upper lower INITCAP(ch)
select UPPER('qiandaomeishigewangba') QDM,LOWER('WANGHUIWENSHIGEHAOREN') WHW,INITCAP('qiaodanmeihuaideh') HHH from dual;
-- CONCAT 连接函数
select CONCAT(ENAME || '的工资是',SAL) sal from emp;
-- SUBSTR(ch, pos, length)截取字符串
select SUBSTR(ENAME,1,4)from emp;
-- LPAD(expr1, n, expr2) 对于表达式expr1限值总共n位剩下不足的补齐expr2 RPAD(expr1, n, expr2)右对齐
select LPAD(ENAME, 10,'*') from emp;
-- TRIM('H' from expr) 从expr 中剔除两边的字符
select TRIM(UPPER('s') from ENAME) bbb from emp;
select TRIM('h' from 'hssssshhhssssh') aaa from dual;
-- REPLACE(source，a,b)将source字符串中的a全部替换成b
select replace(ENAME,UPPER('s'),'w') aaa from emp;
-- 字符串和数字日期之间的转换 TO_DATE(ch, fmt) TO_CHAR(x) TO_NUMBER(expr, fmt)
select '12'+2 from dual;
select TO_DATE('19901008','yyyy-mm-dd') from dual;
--字符串转成日期用todate函数 日期转成字符串用tochar
select * from emp where TO_CHAR(HIREDATE,'yyyy') = '1980';
select * from emp where TO_DATE('1980-12-17', 'yyyy-mm-dd') = HIREDATE;
--数字和字符串转化 TO_CHAR(x)
select TO_CHAR(12345678.9,'999,999,99') from dual;
-- 字符串和数字转行
select TO_NUMBER('$001,234,567.89', 'L000,000,999.99') from dual;
-- NVL(expr1, expr2)如果expr1的值为空用expr2的值 如果不为空则用expr1的值
select NVL('1', 0) aaa from dual;
select NVL('', 0) aaa from dual;
-- NVL2(a, b, c)当表达式a得知不为空显示b,为空显示c
select NVL2('1', '不为空', '为空') from dual;
select NVL2('', '不为空', '为空') from dual;

 

select * from emp;
select * from dept;
-- 高级子查询 查询员工的id 名称要求按照员工的部门号排序
select EMPNO,ENAME DNAME from emp a
ORDER BY (
select DNAME from DEPT b where a.DEPNO = DEPTNO
)asc
-- with 字句 结构清晰 提高查询效率
-- 原始查询
select * from emp where sal > (select SAL
from emp
where ENAME = 'ALLEN'
);
--with查询
with SAL_salary as (
select SAL from emp where ENAME = 'ALLEN'
)
select * from emp where sal>(
select sal from SAL_salary

)
--多列子查询
-- 查询员工的名字,部门id,工资,其中员工的工资,部门id与有奖金的任何一个员工的工资,部门id相同即可
select ENAME,DEPNO, SAL
from emp
where (DEPNO,SAL) in(
select DEPNO,SAL
from emp
where COMM is not null

)
-- 改造后
select ENAME,DEPNO,SAL from emp where comm is not null and DEPNO is not null;

 

 

--存储过程  查询指定部门的工资总和

create or replace procedure get_sal(dept_id number,sum_sal out number)
is

--声明一个游标
cursor v_sal is select * from emp where depno = dept_id;
begin

--给输出参数初始化
sum_sal :=0;
for c in v_sal loop
sum_sal := sum_sal + c.sal;
end loop;
dbms_output.put_line(sum_sal);
end;

--调用存储过程

declare
v_sal :=0;
begin
get_sal(30,v_sal);
end;

 

 

-- 根据部门不同修改员工的工资 其中(?,1995)的工资增加5%
-- (1995,1998)工资增加3%
-- (1998,?)工资增加1%
--将部门多付的工资输出

/*
分析:
1:输入参数是部门
2:输出参数是多付的工资
3:根据时间的不同进行更新操作
4:定义的变量都是在is后面声明
*/

create or replace procedure update_sal(dept_id number,sumsal out number)
is
--定义一个游标查询部门的工资
cursor v_sal_cursor is select * from emp where depno = dept_id;
--定义一个变量记录工资增幅
v_addsal number(4,2) :=0;
--执行方法
begin

-- 初始化输出参数
sumsal :=0;
for c in v_sal_cursor loop
if to_char(c.hiredate,'yyyy')<1995 then v_addsal := 0.05;
elsif to_char(c.hiredate,'yyyy')<1998 then v_addsal := 0.03;
else v_addsal := 0.01;
end if;
--更新工资 对应查询出来的员工
update emp
set sal = sal*(1+v_addsal)
where empno = c.empno;
--累加多付的工资
sumsal := sumsal+c.sal*v_addsal;
end loop;
-- 输出多付的工资
dbms_output.put_line('多付的工资是'||sumsal);
end;

• 根据不同条件更新操作   

  • select * from emp;

    update emp set sal = case when TO_CHAR(HIREDATE,'yyyy')<1981 then sal+50
    when TO_CHAR(HIREDATE,'yyyy')>1981 and TO_CHAR(HIREDATE,'yyyy') < 2007
    then sal+ 100
    when TO_CHAR(HIREDATE,'yyyy') >=2007 then sal + 200
    else sal　　    //else sal 非常重要 如果不指定  那么不符合条件的都会被置为null  注意
    end;