一些oracle中sql的基础

--从0和从1开始效果是一样的，oracle比较智能
select substr('hello',1,3) 字符串截取,
length('hello') 字符串长度,
replace('hello','o','l') 字符串替换
from dual;

--显示所有雇员姓名和姓名后的三个字符
select ename ,substr(ename,length(ename)-2) from emp;
--substr()函数可以采用倒着截取的方式，只要输入的位置是负数，就可以倒着进行
select ename ,substr(ename,-3,3) from emp;
--2.数值函数
--四舍五入:round();截断小数位trunc();取模mod()
select round(789.536) from dual;
--保留两位小数
--保留两位小数
select round(789.536,2) from dual;
--直接对整数进行四舍五入
select round(789.536,-2) from dual;
--trunc不保留小数，也不进行四舍五入
select trunc(789.536) from dual;
select trunc(789.536,2) from dual;
select trunc(789.536,-2) from dual;
--去模
select mod(10,3) from dual;
--显示雇员进入公司的星期数
select sysdate from dual;
select empno,ename,round((sysdate-hiredate)/7) from emp;
--months_between():求出给定日期范围的月数
--add_months():在指定日期上加上指定月数，求出之后的日期
--next_day():下一个的今天是那一个日期
--last_day():求出给定日期的最后一天日期
select empno,ename, months_between (sysdate,hiredate) from emp;
select add_months(sysdate,4) from dual;
select next_day(sysdate,'星期一') from dual;
select last_day(sysdate) from dual;
--4.转换函数
--to_char:转换成字符串；
--to_number:转换成数字;
--to_date:转换成日期
--利用to_char()函数进行拆分，拆分的时候必须指定拆分的通配符：
--年：y,年是四位数字，所以用yyyy表示
--月：m,月是两位数字，所以使用mm表示
--日：d,日是两位数字，所以使用dd表示
select empno,ename,to_char(hiredate,'yyyy') year,
to_char(hiredate,'mm') month,to_char(hiredate,'dd') day
from emp;
--使用to_char()进行日期显示的转换功能
select ename, to_char(hiredate,'yyyy-mm-dd') from emp;
--fm可以去掉前导0
select ename, to_char(hiredate,'fmyyyy-mm-dd') from emp;
--可以使用to_char()进行数字格式化
select ename,to_char(sal,'99,999') from emp;
--希望数字可以明确的表示区域，可以使用以下两种符号：
--$ 表示美元；L Local的缩写，一本地语言进行金额显示
select ename,to_char(sal,'$99,999') from emp;
select ename,to_char(sal,'L99,999') from emp;
--to_number
select to_number('123')+to_number('123') from dual;
--to_date
select to_date('2010-12-31','yyyy-mm-dd') from dual;
--通用函数
select empno,ename ,(sal+comm)*12 from emp;
--nvl函数，可以将一个指定的null值变成指定的内容
--如果需要进行计算的时候，对于null必须使用nvl()函数进行一个转换的操作
select empno,ename,nvl(comm,0),(sal+nvl(comm,0))*12 from emp;
--decode()函数
select decode(1,1,'内容是1',2,'内容是2',3,'内容时3') from dual;
select empno 雇员编号,ename 雇员姓名,hiredate 雇佣日期,
decode( job,'CLERK','业务员','SALESMAN','销售人员','MANAGER','经理'
,'ANALYST','分析员','PRESIDENT','总裁') 职位 from emp;
--多表查询
select * from emp,dept;
select count(*) from emp;
select count(*) from dept;

select * from emp;
select * from dept;

select * from emp e ,dept d where e.deptno=d.deptno;


select e.empno,e.ename,d.deptno,d.dname,d.loc
from emp e ,dept d where e.deptno=d.deptno;

select e.ename,e.job,m.ename,d.dname
from emp e,emp m,dept d where e.mgr=m.empno and e.deptno=d.deptno;

select * from salgrade;

select e.ename,e.sal,d.dname,decode(s.grade,1,'第五等工资',2,'第四等工资',3,'第三等工资',4,'第二等工资',5,'第一等工资')
,m.ename,m.sal,decode(ms.grade,1,'第五等工资',2,'第四等工资',3,'第三等工资',4,'第二等工资',5,'第一等工资')
from emp e,dept d,salgrade s,emp m,salgrade ms
where e.deptno=d.deptno and e.sal between s.losal and s.hisal and e.mgr=m.empno
and m.sal between ms.losal and ms.hisal;
--左右连接
select e.empno,e.ename,d.deptno,d.dname,d.loc
--左连接，默认左连接
from emp e,dept d where e.deptno=d.deptno(+);
--右连接
select e.empno,e.ename,d.deptno,d.dname,d.loc
from emp e,dept d where e.deptno(+)=d.deptno;
--组函数和及分组统计
select min(sal) from emp;
select max(sal) from emp;
select sum(sal) from emp where deptno=20;
select avg(sal) from emp;
--分组统计
select deptno ,count(deptno) from emp
group by deptno;

select deptno ,avg(sal) from emp
group by deptno;

1.select deptno,count(deptno) from emp;
2.select deptno,count(deptno),empno from emp
group by deptno;
--以上代码不能正确执行，是因为:
--1, 如果程序中使用了分组函数，则有两种可以使用的情况：
-- ：程序中存在了group by ,并指定了分组字段，这样可以将分组字段一起查询出来
-- ：如果不使用分组的话，则只能单独的使用分组函数
--2. 在使用分组函数的时候，不能出现分组函数和分组字段之外的字段。

select d.dname,count(e.empno)
from dept d,emp e
where d.deptno=e.deptno
group by d.dname;
--分组函数不允许在where语句之中出现，如果假设要指定分组的条件，
--则只能通过第二种条件的指令：having
select e.deptno,avg(e.sal)
from emp e
group by e.deptno having avg(sal)>2000;

select max(avg(sal)) from emp
group by deptno;
--分组的简单原则
-- 只要一列上存在重复的内容才有可能考虑到分组
--注意
-- 分组函数可以嵌套使用，但是在组函数嵌套使用的时候不能再出现分组字段的查询语句

--子查询
select * from emp where sal>
(select sal from emp where empno=7654);
--所有的子查询必须在"()"中编写代码
--子查询操作分为以下三类：
--1.单列子查询，返回的结果是一列的一个内容
--2.单行子查询，返回多个列，有可能是一条完整的记录
--3.多行子查询，返回多条记录
select * from emp where sal>
(select sal from emp where empno=7654) and job=(select job from emp where empno=7788);

select * from emp where sal=(select min(sal) from emp);

select d.dname,ed.c,ed.a,e.ename
from dept d,
(select deptno ,count(empno) c,avg(sal) a,min(sal) min
from emp
group by deptno) ed,emp e
where
d.deptno=ed.deptno and e.sal=ed.min;

--在子查询中，存在以下三种查询的操作符号
--in any all
--in指定一个查询范围
select * from emp where sal in (select min(sal) from emp group by deptno);
--any操作: =any与in的操作符功能一样
select * from emp where sal =any (select min(sal) from emp group by deptno);
-->any,比里面的最小值要大
select * from emp where sal >any (select min(sal) from emp group by deptno);
--<any,比里面的最大值要小
select * from emp where sal <any (select min(sal) from emp group by deptno);
--all操作
-->all,比里面的最大值要大
select * from emp where sal >all (select min(sal) from emp group by deptno);
--<all,比里面的最小值要小
select * from emp where sal <all (select min(sal) from emp group by deptno);

--多列子查询
select * from emp where (sal,nvl(comm,-1)) in (select sal,nvl(comm,-1) from emp where deptno=20);


CREATE OR REPLACE PROCEDURE "STL_HB"."P_HB_NUMBER_18"
as
type cur_ref is ref cursor; --经分提供的数据
x_mycur cur_ref;
x_area_code varchar2(20); --经分提供的CITY_ID
x_region_id varchar2(20); --所属区域（营业区概念）
x_exchange_code varchar2(20); --经分-运营商标示
x_acc_nbr_begin varchar2(20); --局向开始号段
x_acc_nbr_end varchar2(20); --局向结束号段
x_reduce number(10); --结束号段-开始号段 之差
y_area_code varchar2(20); --新系统需要用到的CITY_ID
x_jh varchar2(20); --新系统用到的号码
x_num number(8); --
x_company_id varchar2(8); --新系统 运营商标示
y_call_area_id number(18); --新系统营业区概念
y_call_group number(18); --分组
z_call_group varchar2(18);
is_null number(4);
t_sequence number(4);
is_error number(4);
-----------------------------
i number(8);
b_nine varchar2(20);
e_nine varchar2(20);
z_begin varchar2(20);
z_end varchar2(20);

err_count number(4);
ctrol_num number(4);
begin

SELECT COUNT(*) into t_sequence
FROM DBA_OBJECTS A
WHERE A.OWNER = 'STL_HB'
AND A.OBJECT_TYPE = 'SEQUENCE'
AND A.OBJECT_NAME = 'SEQ_CALL_NUMBER_MEM_18';
if(t_sequence = 1 ) then
execute immediate 'DROP SEQUENCE SEQ_CALL_NUMBER_MEM_18';
execute immediate 'CREATE SEQUENCE SEQ_CALL_NUMBER_MEM_18
START WITH 180000000
INCREMENT BY 1' ;
else
execute immediate 'CREATE SEQUENCE SEQ_CALL_NUMBER_MEM_18
START WITH 180000000
INCREMENT BY 1' ;
end if;
execute immediate 'TRUNCATE TABLE STL_HB.T_CALL_GROUP_MEM_HB_18';
DELETE FROM STL_HB.T_CALL_GROUP_MEM_LOAD_ERR_18 A
WHERE SUBSTR(TO_CHAR(A.INSERT_TIME,'YYYYMMDD'),1,8) = SUBSTR(TO_CHAR(SYSDATE,'YYYYMMDD'),1,8);
COMMIT;
select count(*) into is_error from t_call_group_mem_load_18 a where a.exchange_code not in('100','200','300','500','600')
or length(a.acc_nbr_begin) = 0 or a.acc_nbr_begin is null or a.acc_nbr_end is null or substr(a.area_code,2,3) || '0'
not in (3100,3110,3120,3130,3140,3150,3160,3170,3180,3190,3350,100) or a.region_id_2 is null;
if( is_error <> 0 ) then
insert into T_CALL_GROUP_MEM_LOAD_ERR_18
select a.*,3,'load表中有字段为空',sysdate,SUBSTR(TO_CHAR(SYSDATE,'YYYYMMDD'),5,2) from t_call_group_mem_load_18 a
where a.exchange_code not in('100','200','300','500','600')
or length(a.acc_nbr_begin) = 0 or a.acc_nbr_begin is null or a.acc_nbr_end is null or substr(a.area_code,2,3) || '0'
not in (3100,3110,3120,3130,3140,3150,3160,3170,3180,3190,3350,100) or a.region_id_2 is null;
commit;
end if;
open x_mycur for select area_code, region_id_2, acc_nbr_begin, acc_nbr_end, exchange_code from T_CALL_GROUP_MEM_LOAD_18 A WHERE
translate(trim(A.Acc_Nbr_Begin),'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz',' ') = trim(A.Acc_Nbr_Begin)
/*and a.acc_nbr_begin in('999670')*//*and rownum < 100*/;
loop

fetch x_mycur into x_area_code,x_region_id,x_acc_nbr_begin,x_acc_nbr_end,x_exchange_code ;
if ( x_area_code = 010 ) then
y_area_code := 3160 ;
else
y_area_code := substr(x_area_code,2,3)||'0';
end if;
x_reduce := to_number(x_acc_nbr_end) - to_number(x_acc_nbr_begin);
if(x_exchange_code = 100) then
x_company_id := '9001';
z_call_group := '184011001';
end if;
if(x_exchange_code = 200) then
x_company_id := '9005';
z_call_group := '181011001';
end if;
if(x_exchange_code = 300) then
x_company_id := '9004';
z_call_group := '185011001';
end if;
if(x_exchange_code = 500) then
x_company_id := '9002';
end if;
if(x_exchange_code = 600) then
x_company_id := '9003';
end if;

SELECT COUNT(*) INTO IS_NULL
FROM T_CALL_GROUP_MEM_REF_18 A WHERE A.O_AREA_CODE = x_region_id AND A.COMPANY_ID = x_company_id AND A.CITY_ID = y_area_code;
if(IS_NULL = 0) then

insert into T_CALL_GROUP_MEM_LOAD_ERR_18 values(
x_area_code,x_region_id,x_acc_nbr_begin,x_acc_nbr_end,x_exchange_code,
1,'关联表中无此信息',sysdate,SUBSTR(TO_CHAR(SYSDATE,'YYYYMMDD'),5,2)
);
end if;
if(IS_NULL > 1) then
insert into T_CALL_GROUP_MEM_LOAD_ERR_18 values(
x_area_code,x_region_id,x_acc_nbr_begin,x_acc_nbr_end,x_exchange_code,
1,'关联字段错误',sysdate,SUBSTR(TO_CHAR(SYSDATE,'YYYYMMDD'),5,2)
);
end if;
IF(IS_NULL = 1) THEN --3
SELECT nvl(A.N_AREA_CODE,0),nvl(A.CALL_GROUP_ID,0) into y_call_area_id,y_call_group
FROM T_CALL_GROUP_MEM_REF_18 A WHERE A.O_AREA_CODE = x_region_id AND A.COMPANY_ID = x_company_id AND A.CITY_ID = y_area_code;
i := 0;
if( length(trim(x_acc_nbr_end)) <> length( trim(x_acc_nbr_begin)) ) then
--dbms_output.put_line(x_acc_nbr_begin||'+++'||length( trim(x_acc_nbr_begin))||'--&--'||x_acc_nbr_end||'--'||length(trim(x_acc_nbr_end)));
insert into T_CALL_GROUP_MEM_LOAD_ERR_18 values(
x_area_code,x_region_id,x_acc_nbr_begin,x_acc_nbr_end,x_exchange_code,
1,'结束号段于开始号段长度不一致错误',sysdate,SUBSTR(TO_CHAR(SYSDATE,'YYYYMMDD'),5,2)
);

end if;
if( length(x_acc_nbr_end) = length(x_acc_nbr_begin) ) then --2
b_nine := substr(x_acc_nbr_begin,length(x_acc_nbr_begin) - i ,1);
e_nine := substr(x_acc_nbr_end,length(x_acc_nbr_end) - i ,1);
while(b_nine = '0' and e_nine = '9' ) loop
i := i + 1;
b_nine := substr(x_acc_nbr_begin,length(x_acc_nbr_begin) - i ,1);
e_nine := substr(x_acc_nbr_end,length(x_acc_nbr_end) - i ,1);
end loop;
z_begin := substr(x_acc_nbr_begin,1,length(x_acc_nbr_begin) - i );
z_end := substr(x_acc_nbr_end,1,length(x_acc_nbr_end) - i );
x_num := z_end - z_begin;
if(x_num = 0) then --1
x_jh := z_begin;
DELETE FROM T_CALL_GROUP_MEM_HB_18 A
WHERE A.CALL_NUMBER = x_jh AND A.CITY_ID = y_area_code AND
A.CALL_AREA_ID = y_call_area_id AND A.COMPANY_ID =x_company_id;
COMMIT;

insert into T_CALL_GROUP_MEM_HB_18 values
(x_jh,'Z'||z_call_group,y_area_code,'100',to_date('20090101','YYYY-MM-DD'),to_date('20500101','YYYY-MM-DD'),'2',to_date('20101001','YYYY-MM-DD'),x_company_id,'18','',SEQ_CALL_NUMBER_MEM_18.NEXTVAL,'','','1','','');
insert into T_CALL_GROUP_MEM_HB_18 values
(x_jh,'B'||z_call_group,y_area_code,'100',to_date('20090101','YYYY-MM-DD'),to_date('20500101','YYYY-MM-DD'),'2',to_date('20101001','YYYY-MM-DD'),x_company_id,'18','',SEQ_CALL_NUMBER_MEM_18.NEXTVAL,'','','1','','');
insert into T_CALL_GROUP_MEM_HB_18 values
(x_jh,'Z'||y_call_group,y_area_code,y_call_area_id,to_date('20090101','YYYY-MM-DD'),to_date('20500101','YYYY-MM-DD'),'2',to_date('20101001','YYYY-MM-DD'),x_company_id,'18','',SEQ_CALL_NUMBER_MEM_18.NEXTVAL,'','','1','','');
insert into T_CALL_GROUP_MEM_HB_18 values
(x_jh,'B'||y_call_group,y_area_code,y_call_area_id,to_date('20090101','YYYY-MM-DD'),to_date('20500101','YYYY-MM-DD'),'2',to_date('20101001','YYYY-MM-DD'),x_company_id,'18','',SEQ_CALL_NUMBER_MEM_18.NEXTVAL,'','','1','','');
COMMIT;
else
while(x_num >= 0) loop ----
x_jh := z_begin ;
z_begin := z_begin + 1;
x_num := x_num -1;
DELETE FROM T_CALL_GROUP_MEM_HB_18 A
WHERE A.CALL_NUMBER = x_jh AND A.CITY_ID = y_area_code AND
A.CALL_AREA_ID = y_call_area_id AND A.COMPANY_ID =x_company_id;
COMMIT;

insert into T_CALL_GROUP_MEM_HB_18 values
(x_jh,'Z'||z_call_group,y_area_code,'100',to_date('20090101','YYYY-MM-DD'),to_date('20500101','YYYY-MM-DD'),'2',to_date('20101001','YYYY-MM-DD'),x_company_id,'18','',SEQ_CALL_NUMBER_MEM_18.NEXTVAL,'','','1','','');
insert into T_CALL_GROUP_MEM_HB_18 values
(x_jh,'B'||z_call_group,y_area_code,'100',to_date('20090101','YYYY-MM-DD'),to_date('20500101','YYYY-MM-DD'),'2',to_date('20101001','YYYY-MM-DD'),x_company_id,'18','',SEQ_CALL_NUMBER_MEM_18.NEXTVAL,'','','1','','');
insert into T_CALL_GROUP_MEM_HB_18 values
(x_jh,'Z'||y_call_group,y_area_code,y_call_area_id,to_date('20090101','YYYY-MM-DD'),to_date('20500101','YYYY-MM-DD'),'2',to_date('20101001','YYYY-MM-DD'),x_company_id,'18','',SEQ_CALL_NUMBER_MEM_18.NEXTVAL,'','','1','','');
insert into T_CALL_GROUP_MEM_HB_18 values
(x_jh,'B'||y_call_group,y_area_code,y_call_area_id,to_date('20090101','YYYY-MM-DD'),to_date('20500101','YYYY-MM-DD'),'2',to_date('20101001','YYYY-MM-DD'),x_company_id,'18','',SEQ_CALL_NUMBER_MEM_18.NEXTVAL,'','','1','','');
COMMIT;
-- end loop;
IF(x_num < 0) then
i := 0;
exit;
end if;
end loop;
end if; --1

end if; --2
end if; --3
exit when x_mycur%notfound;
end loop;
select count(*) into err_count from T_CALL_GROUP_MEM_LOAD_ERR_18 a
where to_char(a.insert_time,'YYYYMMDD') = to_char(sysdate,'YYYYMMDD');
select count(*) into ctrol_num from t_call_group_mem_comtrol_18 a where a.t_date = to_char(sysdate,'YYYYMMDD');
if(err_count > 3 )then
if(ctrol_num = 0) then
insert into stl_hb.t_call_group_mem_comtrol_18
(t_comtrol_id, t_date, t_state)
values
(SEQ_CALL_NUMBER_COMTROL_18.Nextval, to_char(sysdate,'YYYYMMDD'), '1');
commit;
else
update stl_hb.t_call_group_mem_comtrol_18 a set a.t_state = 1 where a.t_date = to_char(sysdate,'YYYYMMDD');
end if;
end if;

if(err_count <= 3) then
if( ctrol_num = 0 ) then
insert into stl_hb.t_call_group_mem_comtrol_18
(t_comtrol_id, t_date, t_state)
values
(SEQ_CALL_NUMBER_COMTROL_18.Nextval, to_char(sysdate,'YYYYMMDD'), '0');
commit;
else
update stl_hb.t_call_group_mem_comtrol_18 a set a.t_state = 0 where a.t_date = to_char(sysdate,'YYYYMMDD');
end if;
end if;
--end if;
end p_hb_number_18;
/