第一章 数据库及bi相关介绍关系型数据库：  中大型数据库：      oracle, sql server, db2, hive, greenplum （处理单表千万级以上数据）  小型数据库：        mysql(处理百万级)bi系统结构  业务数据库1   --|              |-- dm1 -- rpt1  业务数据库2   --|——>ods——>dw——>|-- dm2 -- rpt2  ...           --|              |-- dm3 -- rpt3  1.业务系统（财务管理,oa,业务销售)   oltp  2.数据仓库                          olap    2.1 ods（贴源层-操作性的数据存储）    2.2 dw （数据仓库）    2.3 dm （数据集市）    2.4 rpt（报表）岗位：  1. etl工程师（数据采集、清洗）     工具：kettle/datastage/informatic  2. 数据仓库工程师（数据建模）  3. etl工程师（编写存储过程）  4.报表开发工程师    工具：finereport/cognos/tableau/powerbi/...安排：  1.jdk安装和配置环境变量  2.kettle连接数据库  3.excel填写个人基本信息  4.通过etl导入excel个人信息到数据库  5.帆软报表展现班级统计信息第二章 sql数据操作和查询    -- 1.语句命令组成：       数据 定义 语言（DDL），包括 create（创建）命令、alter（修改）命令、drop（删除）、truncate 命令等。       数据 操纵 语言（DML），包括 insert（插入）命令、update（更新）命令、delete（删除）命令等。       数据 查询 语言（DQL），包括基本查询语句、order by 子句、group by 子句等。       数据 控制 语言（DCL），grant（授权）命令、revoke（撤销）命令。       事务 控制 语言（TCL），包括 commit（提交）命令、rollback（回滚）命令。     ddl:（动作+对象类型+类型名称)     -- 创建用户(管理员权限)     create user test1 identified by test1 account unlock;       create table t_test (       id number(18),       name varchar2(20),       sex char(4),       create_dt date default sysdate       );       数据类型       数字：    number(8)/number(10,2)/number    -- 整数位占8位/整数是8位、小数2位/默认是38位整数  （不超过位数）       日期:     date       字符串：  char/char(10)/varchar2(10)       -- 默认1个字符长度/字符长度不超过10位,少于10位默认后面补空格/字符长度不超过10位,少于10位不会补空格       insert into t_test(id) values (4569821990080821123);  -- 报错超过18位       insert into t_test(id,name) values (2,'abc');         -- name长度3       insert into t_test(id,sex) values (3,'1');            -- sex长度为4,不满4位，后面补空格l       insert into t_test(id,create_dt) values (4,sysdate);  -- 系统日期       insert into t_test(id,create_dt) values (4,date'2019-08-26');  -- 日期       insert into t_test(id,create_dt) values (4,to_date('2019-08-26','yyyy-mm-dd'));  -- 日期     commit;-- 插入多条数据https://blog.csdn.net/gnail_oug/article/details/80005957     练习：建表并插入数据。  -- 2.查询结构     select *|列名|表达式 from 表名 where 条件 group by 列名 having 条件order by 列名 [asc/desc]  -- 3.查询顺序     select     字段（多个字段用逗号隔开）             -- 5     from       表（或者视图或者数据集）               -- 1     where      字段的限定（多个限定用or/and链接）     -- 2     group by   字段（多个字段用逗号隔开）             -- 3     having     分组后的筛选(前面必须有group by）      -- 4     order by   字段（多个字段用逗号隔开）             -- 6     -- 最简单查询     select *       from t_stu_0826;     -- 条件筛选     select *         from t_stu_0826        where sno=12     -- 多条件筛选     select *         from t_stu_0826        where sno=11      and sex='女';     练习：查询2012年6月后毕业的男生的信息     -- 虚拟表       select 10/2 from dual;       select 1, '1' from dual;     -- 按性别统计2012年6月后毕业的人数     select sex,            count(1) -- count(*)       from t_stu_0826      where graduated_mon>=201206      group by sex;     -- 2012年6月后毕业,毕业年份和性别统计人数,只显示人数大于3的信息,并按人数降序排列     select graduated_mon            sex,            count(1) as cnt -- count(*)       from t_stu_0826      where graduated_mon>=201206      group by graduated_mon,sex       having count(1)>3      order by cnt desc;     注意：     select *,sex       from t_stu_0826;  -- 报错,表名或者表别名.*才行       select t.*,              t.sex         from t_stu_0826 t; -- 正确  -- 4.聚合函数  聚合函数：聚合函数同时可以对多行数据进行操作，并返回一个结果。  聚合函数与 group by 使用（字段需要分组，聚合函数必须要跟 group by 联用）  avg      平均值  sum      求和  min、max 最小值、最大值  count  数据统计  举例1: 整体维度  select sum(sal) 总工资,         avg(sal) 平均工资,         count(1) 总人数,         min(sal) 最低工资,         max(sal) 最高工资    from emp;  举例2：每个部门的平均工资  select e.deptno,           avg(e.sal) as avg_sal      from emp e     group by e.deptno;  -- 前面select中，除了聚合函数外的字段都要在group by中  举例3：按部门和工作分组，求平均工资  select e.deptno,       e.job,       avg(e.sal) as avg_sal     from emp e    group by e.deptno,           e.job;  举例4：查询emp表中员工人数大于3的所在部门的部门编号，及对应的人数    select e.deptno,         count(1) as cnt    from emp e     group by e.deptno   having count(1)>3;    -- count对列和对*的区别  select count(*),   --14       count(1),   --14       count(comm) --4    from emp;    -- rollup    -- rollup ：https://www.cnblogs.com/anzhi/p/7515747.html  select deptno,count(1)    from emp    group by deptno  union all  select null as deptno,count(1)    from emp;  select deptno,count(1)  from emp  group by rollup(deptno);  练习：1. 统计每个部门的人数        2. 查询emp表中各部门工资大于2000的员工人数，并且按人数从高到低排列        3. 查询emp表中排除10号部门， 员工人数大于2的所在部门的部门编号，及对应的人数          4.工资在1500到2800之间，除了10号部门的各部门员工平均工资和总人数  -- 5.操作符  算术运算，关系运算，和逻辑运算     (1)算术运算: +、-、*、/      select e.*, (nvl(e.sal,0)+nvl(e.comm,0))*12 as 年薪        from emp e;     (2)关系运算: =、 != 、<>、>、>=、<、<=        select * from emp where empno=7369;     (3)逻辑运算: not>and>or          select (case when 1=1 or 1=3 and 1=2                       then 1                       else 2                  end) as flag            from dual;  -- 1  -- 6.null操作（'',null）  在查询条件中null值用is null作条件，非null值用 is not null做条件  空值的一些特性：  1、空值跟任何值进行算术运算，得到的结果都为空值  2、空值跟任何值进行关系运算，得到的结果都为不成立  3、空值不参与任何聚合运算  4、排序的时候，空值永远是最大的  -- 1  select e.*, comm+100, sal + comm from emp e;  -- 2  select * from emp where comm<>0;  -- 3  select deptno,sum(comm) from emp group by deptno;  -- 4  select * from emp order by comm asc;  -- 7.数据类型的计算  数值型: +-*/ 和关系运算  -- power 幂指数运算  power(2,7) 指的是2的7次方，结果为 128  -- mod   取余运算    mod(128,7) 指的是128/7 的余数，结果为2 ; 函数mod(m,n)，其中m和n都是数字, 返回m/n的余数，如果 n 为 0，则返回 m  select 10/3, power(2,7), mod(128,7) from dual;  字符型：关系运算  日期型：+-和关系运算    select date'2019-07-02' + 3 from dual;  -- 日期只能加数值（不能日期加日期）    select date'2019-07-02' - 3 from dual;  -- 日期减去天数    select date'2019-07-02' - date'2019-07-01' from dual;-- 日期减去日期（相隔的天数）  -- 7.去重（distinct, group by, rowid）  select distinct job,deptno  -- distinct 只能出现在最前，不能一个字段去重一个字段不去重      from emp;  select job,deptno      from emp     group by job,deptno;    -- 错误1  select job,           distinct deptno      from emp;    -- 错误2  select distinct job,           distinct deptno      from emp;  -- 8.in 操作  查询出工作职责是salesman或者president或者analyst的员工。    select ename,job from emp where job = 'salesman' or job = 'president' or job = 'analyst';  select ename,job from emp where job in ('salesman','president','analyst');    select ename,job from emp where job = any('salesman','president','analyst');  -- 9.between…and…（包含边界从小到大顺序）    查询列值包含在指定区间内的行,包含边界。  查询工资在1500到2000之间的员工信息。  select *    from emp   where sal >=1500 and sal <=2000;  select *    from emp   where sal between 1500 and 2000;  -- 查询不到值  select *    from emp   where sal between 2000 and 1500;    -- 10.like模糊查询（对字符串）  字符匹配操作可以使用通配符'%'和'_':    %：代表零个或多个字符。    _：代表任意一个字符。  显示员工名称第二个字母为a最后一个字母为s的员工的姓名、工资。  select ename,sal from emp where ename like '_a%s';  -- 11.集合运算(a={1,2,3,3} , b={2,3,4})  交集：intersect              -- 两者共有部分  并集（去重）：  union  并集（不去重）：union all  补集：minus                  -- 前者有后者无  --  “minus”直接翻译为中文是“减”的意思，在Oracle中也是用来做减法操作的，         只不过它不是传统意义上对数字的减法，而是对查询结果集的减法。         A minus B就意味着将结果集A去除结果集B中所包含的所有记录后的结果，         即在A中存在，而在B中不存在的记录。其算法跟Java中的Collection的removeAll()类似，         即A minus B将只去除A跟B的交集部分，对于B中存在而A中不存在的记录不会做任何操作，也不会抛出异常  当使用集合操作的时候，要注意：  1.查询所返回的列数以及列的类型必须匹配，列名可以不同。  2.只有union all不会去重。其他三个都需要排序后去重，性能比较差  例题：  1.求员工表和部门表中的共有的部门编号（去重）   -- 交集  select deptno                select deptno    from emp                     from emp  intersect                    intersect  select deptno                select deptno    from dept;                   from dept;  -- 3行  2.求员工表或者部门表中所包含的部门编号（不去重）  3.求员工表或者部门表中所包含的部门编号（去重）  4.求部门表中不在员工表中的部门编号  -- 并集（去重）  select deptno    from emp  union  select deptno    from dept;  -- 4行  -- 并集（不去重）  select deptno    from emp  union all  select deptno    from dept;  -- 18行  -- 补集  select deptno    from emp  minus  select deptno    from dept;  -- 空  select deptno    from dept  minus  select deptno    from emp;  -- 1行    -- 12.连接查询（重点）  包括内联接(inner join 1种)和外联接(outer join 3种)  （1）内连接(inner join)：inner可省略     内连接写法1（标准写法）     select       from 表名1       join 表名2         on 表名1.字段1=表名2.字段2     -- 关系运算符都行（不只是等于）      and 表名1.字段1=表名2.字段2;    内连接写法2（oracle写法）    select      from 表名1,表名2     where 表名1.字段=表名2.字段       and 表名1.字段1=表名2.字段2;    1.查看7369员工的员工名称和部门名称    select e.ename, d.dname  -- */e.*/d.*      from emp e      join dept d        on e.deptno=d.deptno -- 顺序无关 d.deptno=e.deptno     where e.empno=7369;    -- oracle    select d.dname      from emp e,dept d       where e.deptno=d.deptno and e.empno=7369;        练习1:查询部门名称为'sales'的所有员工信息        select e.*          from dept d          join emp e            on d.deptno=e.deptno         where d.dname='sales';         select e.*           from dept d, emp e          where d.deptno=e.deptno            and d.dname='sales';  （2）左外连接(left outer join): outer可省略       left join 跟表的前后顺序有关       左外连接写法1（标准写法）     select       from 表名1                     -- 主表       left join 表名2                -- 从表         on 表名1.字段1=表名2.字段2      and 表名1.字段1=表名2.字段2;     左外连接写法2（oracle写法）     select       from 表名1,表名2 -- 带(+) 是从表      where 表名1.字段=表名2.字段(+);    1.查询各部门名称及其对应的人数    select d.dname,         count(e.empno) as cnt      from dept d     -- 主表      left join emp e      on d.deptno=e.deptno     group by d.dname;    select *      from dept d     -- 主表信息全部展示（关联不上的也要展示）      left join emp e -- 从表只显示关联得上的信息      on d.deptno=e.deptno;    select *      from dept d, emp e     where d.deptno=e.deptno(+);    1.查询各部门名称，及对应的经理名称     select d.dname,        e.ename       from dept d       left join emp e       on d.deptno=e.deptno      and e.job='manager';     等价     select d.dname,        t.ename       from dept d       left join (select e.* from emp e where e.job='manager') t       on d.deptno=t.deptno;     等价     select d.dname,        e.ename,        e.job       from dept d       left join emp e       on d.deptno=e.deptno      where e.job='manager' or e.job is null;     等价      select *        from dept d, emp e       where d.deptno=e.deptno(+)         and e.job(+)='manager';     -- 区别如下写法     select d.dname,        e.ename       from dept d       left join emp e       on d.deptno=e.deptno      where e.job='manager';    -- oracle 写法      select d.dname, e.ename        from dept d, emp e       where d.deptno=e.deptno(+) and (e.job='manager' or e.job is null);  （2）右外连接(right outer join): outer可省略  （4）全外连接(full outer join): outer可省略    -- full join    select e.*,d.*      from emp_bak e      full join dept_bak d      on e.deptno=d.deptno;        -- 错的    select e.*,d.*      from emp e, dept d     where e.deptno(+)=d.deptno(+);第三章 子查询和常用函数    -- 1.子查询        子查询在select、update、delete 语句内部可以出现select语句。        1.单行子查询：不向外部返回结果，或者只返回一行结果。        2.多行子查询：向外部返回零行、一行或者多行结果。    -- 单行    select * from emp where empno=7369;    select * from emp where deptno=10;    -- 查询工资和7369相同的员工信息        select * from emp where sal = (select sal from emp where empno=7369);     -- 单行结果"="，并且字段要对应    -- 查询工资和10号部门员工工资相同的其它部门的员工信息        select * from emp where sal in (select sal from emp where deptno = 10) and deptno <> 10;   -- 单行结果"in"，并且字段要对应        select * from emp where sal = any(select sal from emp where deptno = 10) and deptno <> 10; -- 单行结果"in"，并且字段要对应    -- 查询emp表中每个部门的最低工资的员工信息    select e.*      from emp e, (select deptno, min(sal) as min_sal from emp group by deptno) a                           -- 子查询作为临时表关联     where e.deptno = a.deptno       and e.sal = a.min_sal;        select * from emp where (deptno,sal) in (select deptno, min(sal) as min_sal from emp group by deptno);  -- 可以多列对应    -- 查询员工名称及其直接上级的名称        select e1.ename 员工名称,(select e.ename from emp e where e.empno=e1.mgr) as 上级名称 -- 子查询作为查询内容      from emp e1;        -- 更新所有员工工资和7369工资相同    update emp set sal = (select sal from emp where empno=7369);    -- 删除和7369相同工资的员工数据    delete from emp where sal = (select sal from emp where empno=7369);    练习：查询出销售部（sales）下面的员工姓名，工作，工资。（子查询）    select e.ename,e.job,e.sal      from emp e     where e.deptno = (select deptno from dept where dname='sales');  -- 2.表结构操作  创建表方式：  （1）语法结构：直接创建表    create table 表名    (列名 数据类型 [,     列名 数据类型]...    );  （2）语法结构：根据结果集创建表    create table 表名 as select语句;    create table 表名2  as select * from 表名1 where 1=2; -- 拷贝表结构（不带索引约束等）    create table 表名2  as select * from 表名1;           -- 拷贝表结构和数据（不带索引约束等）  -- 增加列  alter table 表名 add 列名 数据类型;  alter table t_course add cteacher varchar2(20);  -- 删除列  alter table 表名 drop column 列名;  alter table t_course drop column cdate;  -- 修改列名  alter table 表名 rename column 旧列名 to 新列名;  alter table t_course rename column cteacher to ctr;  -- 修改表名  alter table 表名 rename to 新表名;  alter table t_course rename to t_cr;  -- 修改数据类型  （1）表中没有数据    alter table t_cr modify cno   varchar2(10); -- number(10)   到 varchar2(10)    alter table t_cr modify cname varchar2(10); -- varchar2(20) 到 varchar2(10)    alter table t_cr modify cname number(10);   -- varchar2(10) 到 number(10)  （2）表中有数据  字段不能更改为其它数据类型;  字段只能在原来的数据类型上增加位数;  -- 删除表  drop table 表名;  drop table t_cr;    -- 3.数据操作语言（dml）  -- 1  create table t_course (  cno number(10),  cname varchar2(20),  cdate date  );  -- 2    create table t_course_copy as select * from t_course;  -- 插入  语法结构：数据插入  insert into 表名(列名1,列名2……) values (值1,值2……)  insert into t_course(cno,cname,cdate) values (1,'语文',sysdate);  insert into t_course values (2,'数学',date'2019-08-19');  insert into t_course(cno,cname) values (3,'英语');  -- 更新  update 表名 set 列名1=值,列名2=值…… where 条件  update t_course set cname='政治', cdate=sysdate where cno=3;  update t_course set cno=4, cdate=sysdate where cname='政治';  -- 删除数据  delete from 表名 where 条件  truncate table 表名（慎用）  -- 无条件删除表数据  delete from t_course where cno=4;  truncate table t_course;    -- 4.oracle单行函数  常用的单行函数有：  字符函数：对字符串操作。  数字函数：对数字进行计算，返回一个数字。  转换函数：可以将一种数据类型转换为另外一种数据类型。  日期函数：对日期和时间进行处理。  （1）字符函数  ascii(x)  返回字符x的ascii码。 -- chr(数字) 反推字符  concat(x,y) 连接字符串x和y。（|| 也可以连接字符串）  length(x) 返回x的长度。    -- 返回的是字符长度  lengthb(x) 返回x的字节长度 -- 返回的是字节长度 -- 其中字节的长度，根据数仓的字符集决定，在字符集是UTF-8的情况下，每一个中文的字节长度是3，每一个英文的字节长度是1   -- select  length('陈') as cnt4,lengthb('陈') as cnt5 from dual   -- 结果为：1，3  lower(x)  x转换为小写。  upper(x)  x转换为大写。  replace(x,old,new)  在x中查找old，并替换为new。  ltrim(x[,trim_str]) 把x的左边截去 trim_str 字符串，缺省截去空格。  rtrim(x[,trim_str]) 把x的右边截去 trim_str 字符串，缺省截去空格。  trim([trim_str from] x) 把x的两边截去 trim_str 字符串，缺省截去空格。  instr(x, str [,start] [,n]) 在x中查找str，可以指定从start开始，第n次出现。    select instr('0722bi大数据bi520', 'bi') from dual;       -- 5   从'0722bi大数据'的第1个字符开始查找'bi'第1次出现的位置     select instr('0722bi大数据bi520', 'bi',6) from dual;     -- 10  从'0722bi大数据'的第6个字符开始查找'bi'第1次出现的位置     select instr('0722bi大数据bi520', 'bi',5, 2) from dual;  -- 10  从'0722bi大数据'的第5个字符开始查找'bi'第2次出现的位置     select instr('0722bi大数据bi520', 'bi',6, 2) from dual;  -- 0   从'0722bi大数据'的第6个字符开始查找'bi'第2次出现的位置   substr(x,start[,length])  返回x的字串，从start处开始，截取length个字符，缺省length，默认到结尾。  wm_concat(列)  字符聚合函数列转行（默认里面值用,隔开）  translate 语法： translate(string,from_str,to_str)，在 string 查找 from_str，按照字符来一一对应，替换 to_str， to_str 不能为空        示例：select translate('我是中国人，我爱中国','中国','China') from dual -- 得到：我是Ch人，我爱Ch        注意看上面的替换，把'我是中国人，我爱中国'的'中'替换称了'C'，'国'替换称了'h'，替换的是位置上的一一对应。        同样，可以看到后面的 to_str 后面还有三个字符'ina'，但是因为是多出来，前面没有对应关系，所以没有可对应的替换值，不会引发异常；        但如果 from_str 中的字符比 to_str 的字符多呢，就会将 from_str 多出来的字符移除，        如下面的例子，返回的是 123456，'BC'没有对应值可以兑换，所以就移除了            SELECT translate('我是爱中国ABC',                             '我是爱中国ABC',                             '123456') AS b              FROM dual;              -- 123456        -- https://www.cnblogs.com/huli-DBA/archive/2012/02/13/2348824.html  rpad 函数从右边对字符串使用指定的字符进行填充        rpad(string,padded_length,[pad_string])        string 表示：被填充的字符串        padded_length 表示：字符的长度，是返回的字符串的数量，如果这个数量比原字符串的长度要短，rpad 函数将会把字符串截取成从左到右的n个字符;        pad_string 是个可选参数，这个字符串是要粘贴到string的右边，如果这个参数未写，rpad 函数将会在string的右边粘贴空格。        例如：         rpad('tech', 7); 将返回'tech '         rpad('tech', 2); 将返回'te'        rpad('tech', 8, '0'); 将返回'tech0000'        rpad('tech on the net', 15, 'z'); 将返回 'tech on the net'        rpad('tech on the net', 16, 'z'); 将返回 'tech on the netz'  lpad 函数从左边对字符串使用指定的字符进行填充  利用 numtodsinterval()方法，该方法只转换成时分秒，如果溢出，则重新开始。  -- https://blog.csdn.net/yzy85/article/details/80566651  -- 示例  select SUBSTR(numtodsinterval(123.13,'hour'),12,8) from dual;  -- 将 123.13 个小时换算成 天时分秒 +000000005 03:07:48.000000000，注意，填入的参数的单位是：小时  -- 时间 - 时间，得到的值的单位是：天数  比如：select  sysdate - (sysdate - 1) as cnt from dual ; -- 1 天  select to_date(86399,'SSSSS') from dual;--2018/6/1 23:59:59，当天23时59分59秒;--wm_concat,示例用法select a      ,cast(wm_concat(b) as  varchar(100)) as b   from (         select 'a' as a, 1 as b           from dual         union all         select 'a' as a, 2 as b           from dual         ) t  group by a;  -- 示例  select ascii('a') from dual;   -- 65  --  select '0722' || 'bi' || '大数据' from dual;  select concat(concat('0722','bi'),'大数据') from dual;  select concat(concat('0722','bi'), (select '大数据' from dual)) from dual;  --  select lower('ab2'), upper('ab2'), replace('a2b3a','a','520') from dual;  select length('a ub好'), lengthb('中国平安'), lengthb('abcd') from dual;  --  select ltrim(' a bldf '), ltrim('  a bldf '),       rtrim(' a bldf '), rtrim('  a bldf '),       trim(' a bldf '), trim('  a bldf ')    from dual;  -- 默认截去空格  select ltrim(' a badf', 'a'), ltrim('a badfa', 'a'),       rtrim('ab badf a', 'a'), rtrim('a badfa ', 'a')    from dual;  -- 截去'a'（可以截去单个或多个字符）  select trim('a' from 'a bada ') from dual;  -- 只能截去单个字符  select trim('ba' from 'a bada ') from dual; -- 报错  --  select instr('0722bi大数据bi520', 'bi') from dual;       -- 从'0722bi大数据'的第1个字符开始查找'bi'第1次出现的位置(5)  select instr('0722bi大数据bi520', 'bi',6) from dual;     -- 从'0722bi大数据'的第6个字符开始查找'bi'第1次出现的位置(10)  select instr('0722bi大数据bi520', 'bi',5, 2) from dual;  -- 从'0722bi大数据'的第5个字符开始查找'bi'第2次出现的位置(10)  select instr('0722bi大数据bi520', 'bi',6, 2) from dual;  -- 从'0722bi大数据'的第6个字符开始查找'bi'第2次出现的位置(0)  --  select substr('ra badf', 2) from dual;    -- 从第2个字符截取到最后一个字符  select substr('ra badf', 2, 3) from dual; -- 从第2个字符截取3个字符    练习：从字符串中'1#qfq#3df#520#d234#dlaj#' 查找第3个#号和第4个#号之间的字符串  select substr('1#qfq#3df#520#d234#dlaj#', 11, 3) from dual;    instr('1#qfq#3df#520#d234#dlaj#', '#', 1, 3)+1 起始位置 11  instr('1#qfq#3df#520#d234#dlaj#', '#', 1, 4) - instr('1#qfq#3df#520#d234#dlaj#', '#', 1, 3) - 1  select substr('1#qfq#3df#520#d234#dlaj#',                instr('1#qfq#3df#520#d234#dlaj#', '#', 1, 3)+1,          instr('1#qfq#3df#520#d234#dlaj#', '#', 1, 4) - instr('1#qfq#3df#520#d234#dlaj#', '#', 1, 3) - 1)    from dual;  --  wm_concat(列)  将a表转换为b表  a:  sno    sname  100    a  100    b  200    c  200    d  300    e  b:  sno    sname  100    a_b  200    c_d  300    e  /*  create table a(  sno number,  sname varchar2(10)  );  insert into a (sno, sname) values (100 ,   'a');  insert into a (sno, sname) values (100 ,   'b');  insert into a (sno, sname) values (200 ,   'c');  insert into a (sno, sname) values (200 ,   'd');  insert into a (sno, sname) values (300 ,   'e');  commit;  */  select * from a;  -- concat非聚合函数  select sno,       concat(sname,'_')    from a   group by sno;  -- 报错  -- wm_concat聚合函数  select sno,       replace(wm_concat(sname),',','_')    from a   group by sno;   （2）数字函数    abs(x)         x绝对值               select abs(-2) from dual;      -- 2    mod(x,y)       x除以y的余数          select mod(7,3) from dual;     -- 1                                         select mod(8.12,2) as cn from dual -- 0.12 小数    power(x,y)     x的y次幂              select power(2,3) from dual;   -- 2的3次幂，输出结果为：8    round(x[,y])   x在第y位四舍五入(默认四舍五入到整数) select round(2.989),round(2.989,2),round(5.989,-1) from dual; -- 3, 2.99, 10    trunc(x[,y])   x在第y位截断(默认截断到整数)         select trunc(2.989),trunc(2.989,2),trunc(5.989,-1) from dual; -- 2, 2.98, 0    ceil(x)        向上取整              select ceil(2.13), ceil(2.00),ceil(-2.13) from dual;    -- 3, 2, -2  -- 这两个函数，想到坐标轴的Y轴，就很好记忆了，向上取整    floor(x)       向下取整              select floor(2.89),floor(3.00),floor(-2.89) from dual;  -- 2, 3,-3   -- 这两个函数，想到坐标轴的Y轴，就很好记忆了，向下取整    months_between 计算月数，需要用到 months_between 函数,计算年数，是通过计算出月数，然后再除以12，months_between(A,B)，时间A减去时间B，返回的值是以月为单位的    max() keep   （3）日期函数    add_months(d,n)，在某一个日期d上，加上指定的月数n，返回计算后的新日期。d表示日期，n表示要加的月数（n可以为负值）    select add_months(sysdate, 1),           add_months(date'2019-08-30', -6),           add_months(date'2020-08-30', -6)      from dual;  last_day(d)，返回指定日期当月的最后一天              select last_day(date'2020-02-26') from dual;--2020/2/29  --  trunc(d[,fmt])和 round(d[,fmt])对日期处理：  trunc(d[,fmt])：  fmt: 'dd'       截取到当月的当天  fmt: 'ddd'      截取到当月的当天（默认格式）  fmt: 'd'        截取到当周的第一天  fmt: 'month'    截取到当月的第一天  fmt: 'q'        截取到当季度的第一天  fmt: 'year'     截取到当年的第一天  select sysdate,         trunc(sysdate),         trunc(sysdate,'dd'),         trunc(sysdate,'ddd'),         trunc(sysdate,'d'),    -- 周日为本周第一天         trunc(sysdate,'month'),-- 可以写成'mm'         trunc(sysdate,'q'),         trunc(sysdate,'year')  -- 可以写成'y'    from dual;  练习：2019年7月22日的情况    1.当年的第一天和最后一天    select trunc(sysdate, 'y') from dual;    select last_day(add_months(trunc(sysdate, 'y'), 11)) from dual;    2.本月的第一天和最后一天      select trunc(date '2019-7-22', 'mm') from dual;      select last_day(trunc(date'2019-7-22')) from dual;    3.本季的第一天和最后一天      select trunc(date '2019-7-22', 'q') from dual;    select last_day(add_months(trunc(date '2019-7-22', 'q'), 2)) from dual;    4.当天为当年的第几天      select date '2019-7-22' - trunc(date '2019-7-22', 'y') + 1 from dual;    5.本周的第一天和最后一天      select trunc(date '2019-7-22', 'd') from dual;    select trunc(date '2019-7-22', 'd') + 6 from dual;    6.当年一共有多少天      select add_months(trunc(date '2019-7-22', 'y'), 12) -         trunc(date '2019-7-22', 'y')      from dual;  -- 5.转义  create table t_123(  sname varchar2(20)  );  insert into t_123 values('a_343');  insert into t_123 values('b%343');  insert into t_123 values('c3%4_3');  insert into t_123 values('i''am');  -- insert into t_123 values('d&34_3');  -- select ascii('&') from dual;  -- chr(38) 为&  insert into t_123 values('d'||chr(38)||'34_3');    /*  select * from t_123 where sname like '%\_%' escape '\';  select * from t_123 where sname like '%\%%' escape '\';  select * from t_123 where sname like '%\%%\_%' escape '\';  -- select * from t_123 where sname like '%\'%' escape '\';  select * from t_123 where sname like '%''%';  -- select * from t_123 where sname like '%\&3%' escape '\';  select * from t_123 where sname like '%'||chr(38)||'3%';  */  -- 6.转换函数  (1)to_char(d|n[,fmt])     select 12, to_char(12),to_char(date'2019-07-08','yyyymmdd') from dual;    -- fmt 格式        select to_char(date'2019-08-30','yyyy'),   -- '2019'               to_char(date'2019-08-30','yyyymm'), -- '201908'               to_char(date'2019-08-30','ww'),     -- '35'  第35周 -- 与星期几无关，1月1号至1月7号，都是第一周，8号是第二周，如此类推               to_char(date'2019-08-30','iw'),     -- '35'  第35周 -- 与星期几紧紧相关，1月的第一个星期一为第一周的第一天，直到星期天，下个星期一开始为第二周               to_char(date'2019-08-30','q'),      -- '3'   第3季度               to_char(date'2019-08-30','mm'),     -- '08'               to_char(date'2019-08-30','dd')      -- '30'          from dual;      练习：计算员工表中每年入职的人数。    select to_char(hiredate, 'yyyy') as 入职年份,         count(*) as 入职人数      from emp     group by to_char(hiredate, 'yyyy')     order by to_char(hiredate, 'yyyy');  (2)to_number(x[,fmt])     x 里面必须全部为数字,如果有空格必须在最前或最后    select to_number('1 2'),   -- 不行           to_number(' 12'),   -- 可以 12           to_number('12 '),   -- 可以 12           to_number(' 12 '),  -- 可以 12           to_number('12a')    -- 不行      from dual;    -- 日期不能直接转数字    select to_number(date'2019-08-22') from dual;  -- 错误    select to_number(to_date('20190822','yyyymmdd')) from dual;  -- 错误    select to_number(to_char(date'2019-08-22','yyyymmdd')) from dual; -- 20190822  (3)to_date(x [,fmt])     select to_date('2019-07-08','yyyy-mm-dd'),            to_date(20190708,'yyyy-mm-dd')       from dual;     注意：字符或数值中的年月日必须是日期范围内的值     select to_date('2019-13-08','yyyy-mm-dd'),  -- 月份不对            to_date(20190732,'yyyy-mm-dd')       -- 天数不对       from dual;     -- 转换格式前后对应     select to_date('2019-07-08','yyyymmdd'), -- 错误            to_date(20190708,'yyyy-mm-dd')    -- 可以       from dual;    -- 7.空值转换函数  nvl （列，默认值） 如果列值为null，则使用默认值表示  nvl2（列，返回值1，返回值2） 如果列值为null，返回结果2 ；如果列值不为null，返回结果1；  select nvl(null,0),     -- 空转0       nvl(null,100),   -- 空转100       nvl('', 99),     -- 空字符转99       nvl(10, 100),    -- 非空不会转       nvl('ad',77)     -- 非空不会转    from dual;  练习：查看员工信息和其年薪  select e.*, nvl(sal,0)+nvl(comm,0) from emp e;  select nvl2(null, 0, 1),       -- 空转1       nvl2('', 99, 2),        -- 空字符转2       nvl2(10, 3, 100),       -- 非空转3       nvl2('ad', 'ad', 7)     -- 非空转'ad'    from dual;  练习：对emp表中的奖金为空的转换为100，不为空的转换为88  select comm, nvl2(comm,88,100) from emp;  -- 8.条件转换函数（从前往后判断，满足前面条件，执行对应操作后退出）  decode（列|值，判断值1，返回值1，判断值2，返回值2，...，默认值）  case when 条件1 then 返回值1 [when 条件2 then 返回值2 ...] else 默认值 end  区别：decode    只能对单个字段进行等值判断，        case when 可以对多个字段进行任意条件判断。  -- decode对值  select decode(12, 1, 2, 12, 10),    -- 10         decode(12, 1, 2, 13, 10),    -- 空         decode(12, 1, 2, 13, 10, 7), -- 7         decode(12, 1, 2, 12, 10, 12, 88, 7) -- 10    from dual;  -- decode对列（对emp表中的奖金为空的转换为100，不为空的转换为88）  select comm,       decode(comm, null, 100, 88)    from emp;  练习：将部门名称进行如下更改并展示部门信息  accounting   会计部  research     研究部  sales        销售部  operations   操作部  select d.deptno,       decode(dname,          'accounting','会计部',          'research','研究部',          'sales','销售部',          'operations','操作部') as 部门,       d.loc    from dept d;  -- case when(对emp表中的奖金为空的转换为100，不为空的转换为88)  select comm,         (case when comm is null then 100             else 88         end) as comm_1    from emp;  select dept.*,       (case       when dname = 'accounting' then '会计部'       when dname = 'research' then '研究部'       when dname = 'sales' then '销售部'       else '操作部'       end) as 部门    from dept;  -- 统计员工表中各部门的员工人数，10号部门不将经理计算在内    select deptno,         count(case when deptno=10 and job='manager' then null            else empno          end) as cnt      from emp     group by deptno;  练习：1.对员工表的job类型进行转换,clerk -> 职员, salesman -> 销售员, president -> 总裁, manager -> 经理, analyst ->分析师;      2.对员工表的薪资打标, 小于1500为'低薪',大于等于1500并且小于等于2500为'中薪', 其它为'高薪';      3.更新数据：job为'clerk' 转为'职员',将其工资增加10%,                   'salesman' 转为'销售员',将其工资增加5%,                   'manager' 转为'经理',将其工资增加2000,                   'analyst' 转为'分析师',将其工资增加6%,                   'president' 转为'老板',其工资不变;      4.计算每个部门发放的总工资，总裁不考虑在内      select deptno,         sum(case when job='president' then null              else nvl(sal,0)+nvl(comm,0)            end) as sum_amount      from emp       group by deptno;  -- 9.exists 函数    exists(查询结果集)：查询结果集有记录则成立，否则不成立    not exists(查询结果集)：与exists相反    列出有员工的部门信息    select distinct d.dname      from dept d      join emp e        on d.deptno=e.deptno;    select d.*      from dept d     where deptno in (select deptno from emp);  -- exists  select d.dname    from dept d   where exists (select 1 from emp e where e.deptno=d.deptno);    select d.dname      from dept d     where exists (select null from dual);  -- https://www.cnblogs.com/xuanhai/p/5810918.html  结论：EXISTS与IN的使用效率的问题，通常情况下采用exists要比in效率高，因为IN不走索引，但要看实际情况具体使用：        IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。  -- 10.伪列    rowid  插入数据的时候生成,记录的是该行的物理地址(用作去重)    rownum 查询数据的时候生成,返回的是序号(用作分页) -- 一定要有别名  select e.*, e.rowid, rownum  -- rownum是变化的值，不能指定到某个表      from emp e;    select e.rowid,d.rowid,rownum, e.*,d.*      from emp e      join dept d        on e.deptno=d.deptno;    -- rownum 查询只能是小于或小于等于某个值（不能直接等于或者大于或者不是从1开始的某个区间段）    select e.*,rowid, rownum      from emp e     where rownum<=10;    抽取排在第7到第10之间的数据  -- 1.补集  select e.*, rownum      from emp e     where rownum <= 10    minus    select e.*, rownum      from emp e     where rownum <= 6;  -- 2.子查询    select t.*      from (            select e.*, rownum as rn   -- 一定要有别名              from emp e             where rownum <= 10) t     where t.rn between 7 and 10;    -- rowid    删除重复数据，相同数据只保留一条    delete from 表名 别名     where rowid not in (select min(rowid) from 表名 别名 group by 列名);    从员工表找出所有部门编号（不重复）  select distinct deptno    from emp;  select deptno    from emp   group by deptno;    select deptno    from emp   where rowid in (select min(rowid) from emp group by deptno);    -- 每个部门的员工数据只保留一条  delete from emp   where rowid not in (select min(rowid) from emp group by deptno);    -- 11.分析函数（重点）    它可以在数据中进行分组然后计算基于组的某种统计值，并且每一组的每一行都可以返回一个统计值    -- 分组聚合    select deptno,           count(empno)      from emp     group by deptno;    按照每个部门的工资从低到高排序，计算每个部门的累计工资？    按照每个部门的工资从低到高排序，给出各部门工资的序号？    语法格式：分析函数语法（<> 内的内容可以选择性省略)    function_name(<参数>,…) over (<partition by 表达式,…> <order by 表达式 <asc desc> )    (1)、函数为聚合函数（avg,sum,count)情况下：        1.function_name(<参数>,…) over (partition by 表达式,… )         -- 分组求值        2.function_name(<参数>,…) over (order by 表达式 <asc desc> )    -- 整体数据未做分组，先排序，在求累计求值        3.function_name(<参数>,…) over (partition by 表达式,… order by 表达式 <asc desc> ) -- 先分组，按组内排序，对组内求累计求值  -- partition by   1.查看员工表中员工信息以及对应部门的总人数  select e.*,         count(empno) over (partition by deptno) as cnt    from emp e;  -- order by       2.对员工表的工资从低到高排序，求累计应发放薪资  select e.*,         sum(nvl(sal,0)+nvl(comm,0)) over (order by sal)    from emp e;  区别    select e.*,           sum(nvl(sal,0)+nvl(comm,0)) over (order by sal,empno)      from emp e;  -- partition by + order by   3.按照每个部门的工资从低到高排序，计算每个部门的累计工资    select e.*,           sum(sal) over (partition by deptno order by sal)      from emp e;    select e.*,           sum(sal) over (partition by deptno order by sal,empno)      from emp e;    练习：  每日生产量      t_a    data_date       dail_yout    2019-07-10      120    2019-07-11      100    2019-07-12      150    求每日累计产量  t_b    data_date       dail_yout   sum_dail_yout    2019-07-10      120         120    2019-07-11      100         220    2019-07-12      150         370    /*    create table t_a (    data_date date,    dail_yout  number);    insert into t_a(data_date,dail_yout) values (date'2019-07-10', 120);    insert into t_a(data_date,dail_yout) values (date'2019-07-11', 100);    insert into t_a(data_date,dail_yout) values (date'2019-07-12', 150);    commit;    */Oracle分析函数-first_value()和last_value()    (2)、函数为排序函数（row_number(),rank(),dense_rank())情况下：        ①row_number：        row_number 函数返回一个唯一的值，当碰到相同数据时，排名按照记录集中记录的顺序依次递增。        ②dense_rank：        dense_rank 函数返回一个唯一的值，当碰到相同数据时，此时所有相同数据的排名都是一样的。        同时会在最后一条相同记录和下一条不同记录的排名之间不空出排名。        ③rank：        rank 函数返回一个唯一的值，当碰到相同的数据时，此时所有相同数据的排名是一样的，        同时会在最后一条相同记录和下一条不同记录的排名之间空出排名。        2.function_name() over (order by 表达式 <asc desc> )                       -- 整体数据未做分组，按order by 的字段排名        3.function_name() over (partition by 表达式,… order by 表达式 <asc desc> ) -- 先分组，按组内排序，对组内进行排名  -- order by   2.将emp表按工资从低到高排序，并给出排名（排名不并列）  select e.*,         row_number() over (order by sal)    from emp e;    -- partition by + order by   3.按照员工部门分组, 给出工资从低到高的排名（排名不并列）  select e.*,         row_number() over (partition by deptno order by sal)    from emp e;   （3）、函数为位移函数（lead(列,参数), lag(列,参数)    select e.*,           lag(e.sal,1) over(order by e.sal) as 工资下移一位,           lead(e.sal,1) over(order by e.sal) as 工资上移一位      from emp e;    员工表按入职先后排序, 求出每两个员工的入职时间差    select e.*,           lag(hiredate,1) over (order by hiredate) as 下移,           lead(hiredate,1) over (order by hiredate) as 上移,           lead(hiredate,1) over (order by hiredate) - hiredate as 入职时间差      from emp e;  -- 找出员工表每个部门的一条员工信息（分析函数）    select *      from (          select e.*,                 row_number() over(partition by deptno order by empno) as rn            from emp e          ) t    where t.rn = 1;    -- 12.行列转换    select deptno,           count(empno)      from emp     group by deptno;    员工表人数统计：    10号部门  20号部门  30号部门           3         5         6  with t as (          select deptno,           count(empno) as cnt        from emp         group by deptno)  select distinct (select cnt from t where deptno = 10) as "10号部门",          (select cnt from t where deptno = 20) as "20号部门",          (select cnt from t where deptno = 30) as "30号部门"    from t;  with t as (          select deptno,           count(empno) as cnt        from emp         group by deptno)  select max(case when t.deptno=10 then t.cnt else 0 end) "10号部门",       max(case when t.deptno=20 then t.cnt else 0 end) "20号部门",       max(case when t.deptno=30 then t.cnt else 0 end) "30号部门"   from t;  with t as (          select deptno,           count(empno) as cnt        from emp         group by deptno)  select max(decode(t.deptno, 10, t.cnt, 0)) "10号部门",       max(decode(t.deptno, 20, t.cnt, 0)) "20号部门",       max(decode(t.deptno, 30, t.cnt, 0)) "30号部门"   from t;   （1）列转行    有一张表s，记录了某公司每个季度的销售额，如下    y      q      amt      产品  ...    2015   1      100    2015   2      110    2015   3      130    2015   4      100    2016   1      200    2016   2      150    2016   3      100    2016   4      300    y      q1   q2   q3   q4    2015   100  110  130  100    2016   200  150  100  300    /*    --    create table t_y_q_amt (    y number,    q number,    amt number    );    insert into t_y_q_amt values(2015,   1 ,     100);    insert into t_y_q_amt values(2015,   2 ,     110);    insert into t_y_q_amt values(2015,   3 ,     130);    insert into t_y_q_amt values(2015,   4 ,     100);    insert into t_y_q_amt values(2016,   1 ,     200);    insert into t_y_q_amt values(2016,   2 ,     150);    insert into t_y_q_amt values(2016,   3 ,     100);    insert into t_y_q_amt values(2016,   4 ,     300);    --    create table t_y_q_amt_1 (    y number,    q1 number,    q2 number,    q3 number,    q4 number    );    insert into t_y_q_amt_1 values(2015,   100,  110,  130, 100);    insert into t_y_q_amt_1 values(2016,   200,  150,  100,  300);  commit;    */    -- 1.子查询关联    select t1.y, t1.amt as q1, t2.amt as q2, t3.amt as q3, t4.amt as q4      from (select a.* from t_y_q_amt a where q = 1) t1, -- 第一季度           (select a.* from t_y_q_amt a where q = 2) t2, -- 第二季度           (select a.* from t_y_q_amt a where q = 3) t3, -- 第三季度           (select a.* from t_y_q_amt a where q = 4) t4  -- 第四季度     where t1.y = t2.y       and t1.y = t3.y       and t1.y = t4.y;    -- 2.条件控制转换    select a.y,           sum(case when a.q=1 then a.amt end) as q1,           sum(case when a.q=2 then a.amt end) as q2,           sum(case when a.q=3 then a.amt end) as q3,           sum(case when a.q=4 then a.amt end) as q4      from t_y_q_amt a     group by a.y;    select a.y,           sum(decode(a.q, 1, a.amt)) as q1,           sum(decode(a.q, 2, a.amt)) as q2,           sum(decode(a.q, 3, a.amt)) as q3,           sum(decode(a.q, 4, a.amt)) as q4      from t_y_q_amt a     group by a.y;    （2）行转列    select y, 1 q, q1 amt     from t_y_q_amt_1 t1    union    select y, 2 q, q2 amt     from t_y_q_amt_1 t1    union    select y, 3 q, q3 amt     from t_y_q_amt_1 t1    union    select y, 4 q, q4 amt     from t_y_q_amt_1 t1;  select t.y, t.q1 as amt, 1 as q    from t_y_q_amt_1 t  union all  select t.y, t.q2 as amt, 2 as q    from t_y_q_amt_1 t  union all  select t.y, t.q3 as amt, 3 as q    from t_y_q_amt_1 t  union all  select t.y, t.q4 as amt, 4 as q    from t_y_q_amt_1 t;    pivot 函数：行转列函数 -- https://www.cnblogs.com/xiao02fang/p/9705609.html      语法：pivot(任一聚合函数(列) for 需要转的列的值所在列名 in (需转为列名的值))；    unpivot函数：列转行函数      语法：unpivot(新的列名1[多列中的值转为新增列中值] for 新的列名2[解释:多列的列名转为新增列中值] in (需转为行的列名))；    执行原理：将pivot函数或unpivot函数接在查询结果集的后面。相当于对结果集进行处理。-- 原始表 t_y_q_amt    y      q      amt      产品  ...    2015   1      100    2015   2      110    2015   3      130    2015   4      100    2016   1      200    2016   2      150    2016   3      100    2016   4      300    -- pivot 行转列    select *      from t_y_q_amt pivot(sum(amt) for q in(1 as q1,                                             2 as q2,                                             3 as q3,                                             4 as q4));    -- 逐行分析  y        q1    q2    q3    q4  2015     100   110   130   100  2016     200   150   100   300    -- unpivot 列转行    select *      from t_y_q_amt_1 unpivot(amt for q in(q1 as 1,q2 as 2,q3 as 3,q4 as 4));    练习：按部门统计每种工作下面员工的工资总额（每种工作显示一列）--行转列with temp as(select '四川省' nation ,'成都市' as city,'第一' as ranking from dual union allselect '四川省' nation ,'绵阳市' as city,'第二' as ranking from dual union allselect '四川省' nation ,'德阳市' as city,'第三' as ranking from dual union allselect '四川省' nation ,'宜宾市' as city,'第四' as ranking from dual union allselect '湖北省' nation ,'武汉市' as city,'第一' as ranking from dual union allselect '湖北省' nation ,'宜昌市' as city,'第二' as ranking from dual union allselect '湖北省' nation ,'襄阳市' as city,'第三' as ranking from dual)select *  from (    select * -- nation ,city ,ranking      from temp      )  pivot(max(city) for ranking in ('第一' as 第一,'第二' as 第二,'第三' as 第三,'第四' as 第四) )--列转行with temp as(select '四川省' as nation,'成都市' as 第一,'绵阳市' as 第二,'德阳市' as 第三,'宜宾市' as 第四 from dual union allselect '湖北省' as nation,'武汉市' as 第一,'宜昌市' as 第二,'襄阳市' as 第三,''       as 第四 from dual)select nation, name,title  from temp  unpivot(       name for title in (第一,第二,第三,第四)  )  -- 13.表的约束  按照约束用途分类：  1.primary key：主键约束 （非空、唯一）          -- 身份证号  2.foreign key：外键约束 （受外部表主键的约束）  3.check：      检查约束 （只能为空或约束的值）  -- 性别  4.unique：     唯一约束 （不重复、可多行为空）  5.not null：   非空约束 （不能为空）  （1）primary key （主键只有一个, 但是可以对多个字段组合建立一个主键）  create table t_china_id (  id_no varchar2(18) primary key,   -- 系统给定主键名  id_name varchar2(60),  birth_day date,  address varchar2(200),  tel number(11)  );  insert into t_china_id(id_no) values('1');  insert into t_china_id(id_no) values('1'); -- 重复值报错  insert into t_china_id(id_no) values('');  -- 空值报错  例如： alter table ODSAPD.APD_TRUANS_UPDATE_REC add constraint APD_TRUANS_UPDATE_REC_KEY primary key (CASE_NO);  --  create table t_china_id_1 (  id_no varchar2(18),  id_name varchar2(60),  birth_day date,  address varchar2(200),  tel number(11),  constraint pr_in_no primary key(id_no,id_name)-- 自己给定主键名  );  insert into t_china_id_1(id_no) values('1');  -- 主键字段 id_name 为空报错  insert into t_china_id_1(id_no,id_name) values('1','a');  insert into t_china_id_1(id_no,id_name) values('1','b');  -- 向表中添加主键约束  alter table student add primary key(studentid);   （2）foreign key （外部表要先建立主键，外键约束的字段值只能包含在外部表主键值中）  create table dept_1   (deptno number(2,0),    dname  varchar2(14),    loc    varchar2(13),    constraint pk_deptno_1 primary key (deptno)   );  create table emp_1(  empno  number(4,0),  ename  varchar2(10),  deptno number(2,0),  -- deptno number(2,0) references dept_1(deptno),  constraint pk_empno_1 primary key (empno),  constraint fk_deptno_1 foreign key (deptno) references dept_1(deptno)  );  insert into emp_1(empno,ename,deptno) values (1,'a',null);  -- 可以插入数据  insert into emp_1(empno,ename,deptno) values (2,'b',10);    -- 报错,外部表主键没有10的值  insert into dept_1(deptno,dname,loc) values (10,'aa','bb');  insert into emp_1(empno,ename,deptno) values (2,'b',10);    -- 可以插入数据    delete from dept_1 where deptno=10;                         -- 报错，字表已经引用外部表10号部门的值   （3）check -- 检查约束 （只能为空或约束的值）  -- 性别  create table t_china_id_2 (  id_no varchar2(18) primary key,  id_name varchar2(60),  sex varchar2(4),  -- sex varchar2(4) check(sex='男' or sex='女'),  tel number(11),  constraint ck_sex check(sex='男' or sex='女')  );  insert into t_china_id_2(id_no,sex) values('1','');      -- 可以为空  insert into t_china_id_2(id_no,sex) values('2','未知');  -- 不能为检查约束外的值  insert into t_china_id_2(id_no,sex) values('3','男');   （3）unique -- 唯一约束 （不重复、可多行为空）（主键不能和唯一约束组合）  create table t_china_id_3 (  id_no varchar2(18) primary key,  id_name varchar2(60),  sex varchar2(4),  -- tel number(11) unique,  tel number(11),  constraint uq_tel unique(tel)  );  insert into t_china_id_3(id_no,tel) values('1',null);      -- 可以为空  insert into t_china_id_3(id_no,tel) values('2',null);      -- 可以为空  insert into t_china_id_3(id_no,tel) values('3',null);      -- 可以为空   （4）not null -- 非空约束 （不能为空）   （5）约束组合使用（主键不能和唯一约束组合）  create table t_china_id_4 (  id_no varchar2(18) primary key check(length(id_no)=18),  id_name varchar2(60)  );  insert into t_china_id_4(id_no,id_name) values('1','张三');      -- 主键中的检查约束不满足18位, 报错  insert into t_china_id_4(id_no,id_name) values('123456789876543210','张三');  -- alter table命令添加约束  alter table 表名 add constraint 约束名 primary key(列名1[,列名2...])  alter table 主表名 add constraint 约束名 foreign key(列名1[,列名2...]) references 从表名(列名1[,列名2...])  alter table 表名 add constraint 约束名 check(条件)  alter table 表名 add constraint 约束名 unique(列名)  alter table 表名 modify 列名 not null  alter table 表名 drop constraint 约束名  -- 有数据不满足约束的时候,不能创建约束  create table t_test_1 (  sno varchar2(10),  sname varchar2(20)  );  insert into t_test_1(sno, sname) values ('1','张三');  insert into t_test_1(sno, sname) values ('1','李四');  alter table t_test_1 add constraint pk_t_test_1_sno primary key (sno);   -- 报错  练习：  1.建立2张表，学生表和课程表  2.学生表的学号为主键，课程表的课程号为主键  3.学生表的课程号建立外键，对应的为课程表的课程号（课程表的课程号为学生表的课程号的外键）  要求：建好表后通过语句添加主外键    -- 14.临时表  创建oracle临时表，可以有两种类型的临时表：  会话级临时表（1.其它会话查询不到数据 2.本会话关掉后也查询不到数据）  事务级临时表（1.当进行事务提交或者事务回滚的时候，临时表中的数据将自行被截断          2.其它会话查询不到数据          3.本会话关掉后也查询不到数据）  1）会话级临时表  create global temporary table table_name   (col1 type1,col2 type2...) on commit preserve rows;  2）事务级临时表  create global temporary table table_name  (col1 type1,col2 type2...) on commit delete rows;  举例：会话  create global temporary table student_1  (stu_id number(5),  class_id  number(5),  stu_name varchar2(8),  stu_memo varchar2(200)) on commit preserve rows ;  insert into student_1 values(1,2,'a','随便');  commit;  select * from student_1;  举例：事务  create global temporary table classes_1  (class_id number(5),  class_name varchar2(8),  class_memo varchar2(200)) on commit delete rows ;  insert into classes_1 values(1,'a','随便');  select * from classes_1;  commit;  update classes_1 set class_memo ='很好啊嘟嘟嘟嘟嘟哒哒哒哒哒';  commit;第4章 表空间、数据库对象  1.表空间  （1）系统表空间  （2）临时表空间  （3）用户表空间  （4）undo表空间  （5）样例表空间  （6）系统副本空间  2.同义词  私有同义词：拥有create synonym权限的用户（包括非管理员用户）即可创建私有同义词，创建的私有同义词只能由当前用户使用。  公有同义词：系统管理员可以创建公有同义词，公有同义词可以被所有用户访问。  创建同义词  create [or replace] [public] synonym [schema.]synonym_name  for [schema.]object_name  -- 创建用户要sys用户权限  create user test_1 identified by test_1 account unlock;  grant connect,resource to test_1;  -- 取不到其它用户下面的表  select * from  scott.emp;  -- sys 用户  -- select * from dba_synonyms;  create or replace public synonym emp_copy for scott.emp;    -- scott用户  select * from  emp_copy;  -- test_1 用户（看不到）  select * from  scott.emp;  select * from  emp_copy;  -- sys授权  grant select on emp_copy to public;  -- test_1 用户  select * from  scott.emp;  select * from  emp_copy;  3.序列  序列(sequence)是用来生成连续的整数数据的对象。-- 一般当做主键（像订单明细中）  创建序列  create sequence sequence_name  [minvalue num|nominvalue]    -- 最小值 默认是 1  [maxvalue num|nomaxvalue]    -- 最大值 默认是 9999999999999999999999999999 -- 里面是 28 个 9  [start with num]             -- 从某一个整数开始，升序默认值是1，降序默认值是-1,默认是从 1 开始的，  [increment by increment]     -- 增长数，默认是 1  [cycle|nocycle]              -- 表示如果升序达到最大值后，从最小值重新开始；如果是降序序列，达到最小值后，从最大值重新开始 -- 默认 无这个条件  [cache num|nocache]          -- 预先在内存中生成序列号/不预先在内存中生成序列号  -- 最简单创建  create sequence s_1;      创建之后，再打开看脚本，如下：      create sequence S_1      minvalue 1      maxvalue 9999999999999999999999999999      start with 1      increment by 1      cache 20;  序列使用  --访问下一个值（初始创建后要先申明）  select s_1.nextval from dual;  --访问当前值  select s_1.currval from dual;  -- 生成序列号  create sequence s_2  start with 2  minvalue 1  maxvalue 10  increment by 2  cycle  cache 2  序列修改和删除  --序列修改  alter sequence s_1  maxvalue 10000  minvalue -300  --删除序列  drop sequence s_1;  4.视图 -- 1.关键字段可以隐藏 2.根据基表实时更新 3.不占用空间 4.可以处理复杂的表关联  视图（view）实际上是一张或者多张表上的预定义查询，这些表称为基表。  从视图中查询信息与从表中查询信息的方法完全相同。只需要简单的select…from即可。  创建视图  create [or replace] [{force|noforce}] view view_name  as  select查询  [with read only constraint]  视图  --创建视图(sys授权 grant create view to scott;)    create or replace view v_emp_dept    as    select e.empno, e.ename, e.job, e.hiredate, e.deptno, d.dname      from emp_bak e      join dept_bak d      on e.deptno = d.deptno    with read only  -- 更改基础表  delete from emp_bak;  --通过视图查询  select * from v_emp_dept where deptno=10;    5、索引(1.索引可以提高查询的效率 2.占用空间 3.数据增删改时需要更新索引，因此索引对增删改时会有负面影响)  前提：表数据量比较大的时候，查询比较慢  1.如果表中的某些字段经常被查询并作为查询的条件出现时，就应该考虑为该列创建索引。  2.有一条基本的准则是：当任何单个查询要检索的行少于或者等于整个表行数的10%时，索引就非常有用。    （1）索引种类    唯一索引（用的最多）  1、何时创建：当某列任意两行的值都不相同  2、当建立 primary key(主键)或者unique constraint(唯一约束)时，唯一索引将被自动建立  组合索引  1、何时创建：当两个或多个列经常一起出现在where条件中时，则在这些列上同时创建  2、组合索引中列的顺序是任意的，也无需相邻。但是建议将最频繁访问的列放在列表的最前面  基于函数的索引  1、何时创建：在where条件语句中包含函数或者表达式时  2、函数包括：算数表达式、pl/sql函数、程序包函数、sql函数、用户自定义函数。  /*位图索引  1、何时创建：列中有非常多的重复的值时候。例如某列保存了 “性别”信息。  where 条件中包含了很多or操作符。较少的update操作，因为要相应的跟新所有的bitmap  反向键索引  键压缩索引  索引组织表(iot)  分区索引  */  创建索引  create [unique] index index_name on table_name(column_name[,column_name…])  -- 唯一索引    create unique index u_inx_emp_bak_empno on emp_bak(empno);    -- 组合索引(字段顺序有关）    create index inx_emp_bak_deptno_job on emp_bak(deptno,job);    -- 基于函数的索引    create index inx_emp_bak_ename on emp_bak(substr(ename,-1,1));  索引失效  1.隐士转换  select * from emp_bak where empno = '7934';  -- 改 select * from emp_bak where empno = 7934;  2.字段引用函数  select * from emp_bak where to_char(empno) = '7934';  select * from emp_bak where max(empno) = 7934;  3.null 值判断  select * from emp_bak where empno is not null;  -- 改select * from emp_bak where empno>0;  4.索引列进行运算  select * from emp_bak where empno + 10 = 7944;  -- 改select * from emp_bak where empno = 7944 -10;  select * from emp_bak where empno <> 7944;  -- 改select * from emp_bak where empno > 7944 or empno < 7944;  5.like首字母未知（末尾字母知道）  select * from emp_bak where ename like '%s';  -- 改select * from emp_bak where substr(ename,-1,1) ='s';  6.组合索引(字段顺序有关）  select * from emp_bak where deptno=10;                    -- 部分索引  select * from emp_bak where deptno=10 and job='salesman'; -- 全部索引  select * from emp_bak where job='salesman';               -- 索引失效    (前面字段理解为楼栋的编号,后面字段理解为房间号)-- Oracle 建立索引及SQL优化 https://www.cnblogs.com/tianmingt/articles/4444885.html数据库索引 index  6.表分区（大表拆成多张小表）  前提：表数据量比较大的时候，查询比较慢  优点：1.提高查询效率 2.增强可用性 3.维护方便（只对某些有问题分区数据维护，不用对整张表维护）  缺点：2.分区表需要维护（维护创建的分区）  表分区的几种类型及操作方法  1.范围分区：range  2.列表分区：list  3.散列（哈希）分区：hash  4.组合分区  范围分区：  按入职日期进行范围分区  create table myemp  (    empno  number(4)  primary key,    ename varchar2(10),    hiredate date,    deptno number(7)  )  partition  by  range (hiredate)  (      partition  part1 values  less  than (to_date('1981-1-1','yyyy/mm/dd')),      partition  part2 values  less  than (to_date('1982-1-1','yyyy/mm/dd')),      partition  part3 values  less  than (to_date('1983-1-1','yyyy/mm/dd')),      partition  part4 values  less  than (to_date('1988-1-1','yyyy/mm/dd')),      partition  part5 values  less  than (maxvalue)                          --默认最大  );  -- select * from user_tab_partitions where table_name='myemp'; 查看分区表情况  insert into myemp(empno,            ename,            hiredate,            deptno)     values(1,'张三',date'1980-1-1',10);  insert into myemp(empno,            ename,            hiredate,            deptno)     values(2,'李四',date'1981-10-02',20);  insert into myemp(empno,            ename,            hiredate,            deptno)     values(3,'王五',date'1982-11-03',30);  insert into myemp(empno,            ename,            hiredate,            deptno)     values(4,'李蕾',date'1983-07-08',40);  insert into myemp(empno,            ename,            hiredate,            deptno)     values(5,'李华',date'1987-09-09',40);  insert into myemp(empno,            ename,            hiredate,            deptno)     values(6,'赵四',date'1989-11-03',50);  -- 查看分区数据  select * from myemp partition(part5);  列表分区：  该分区的特点是某列的值比较少并且不会经常变动，基于这样的特点我们可以采用列表分区。  按deptno进行list分区  create table myemp2  (    empno  number(4)  primary key,    ename varchar2(10),    hiredate date,    deptno number(7)  )  partition by list (deptno)  (      partition myemp_deptno_10  values (10) ,      partition myemp_deptno_20  values (20) ,      partition myemp_deptno_30  values (30) ,      partition myemp_deptno_40  values (40)  );  -- select * from user_tab_partitions where table_name='myemp2'; 查看分区表情况  insert into myemp2(empno,            ename,            hiredate,            deptno)     values(1,'张三',date'1980-1-1',10);  insert into myemp2(empno,            ename,            hiredate,            deptno)     values(2,'李四',date'1981-10-02',20);  insert into myemp2(empno,            ename,            hiredate,            deptno)     values(3,'王五',date'1982-11-03',30);  insert into myemp2(empno,            ename,            hiredate,            deptno)     values(4,'李蕾',date'1983-07-08',40);  insert into myemp2(empno,            ename,            hiredate,            deptno)     values(5,'李华',date'1987-09-09',40);  insert into myemp2(empno,            ename,            hiredate,            deptno)     values(6,'赵四',date'1989-11-03',50);  -- 数据无法插入  散列分区/hash 分区:  组合分区：  这种分区是基于两种分区的组合，分区之中的分区被称为子分区。  按入职日期进行范围分区，再按deptno进行list子分区  create table myemp4  (    empno  number(4)  primary key,    ename varchar2(10),    hiredate date,    deptno number(7,2)  )  partition by range(hiredate) subpartition by list(deptno)  (     partition p1 values less than(to_date('1981-01-01','yyyy-mm-dd'))        (          subpartition p1a values (10) ,          subpartition p1b values (20),          subpartition p1c values (30),          subpartition p1d values (40)        ),     partition p2 values less than (to_date('1982-01-01','yyyy-mm-dd'))        (         subpartition p2a values (10) ,          subpartition p2b values (20),          subpartition p2c values (30),          subpartition p2d values (40)        ),     partition p3 values less than (to_date('1983-01-01','yyyy-mm-dd'))        (         subpartition p3a values (10) ,          subpartition p3b values (20),          subpartition p3c values (30),          subpartition p3d values (40)        ),     partition p4 values less than (to_date('1988-01-01','yyyy-mm-dd'))        (         subpartition  p4a values (10) ,          subpartition p4b values (20),          subpartition p4c values (30),          subpartition p4d values (40)        ),     partition p5 values less  than (maxvalue)        (          subpartition p5a values (10) ,          subpartition p5b values (20),          subpartition p5c values (30),          subpartition p5d values (40)        )  );  -- select * from user_tab_partitions where table_name='myemp4'; 查看分区表情况  有关表分区的一些维护性操作:  /*   create table sales    (    pro_id number(10)  primary key,    sal_dt date,    sal_deptno number(10)    )    partition  by  range (sal_dt) subpartition by list (sal_deptno)    (      partition  p1 values  less  than (to_date('1981-1-1','yyyy/mm/dd'))      (subpartition p1sub1 values(10)      ),      partition  p2 values  less  than (to_date('1982-1-1','yyyy/mm/dd'))      (subpartition p2sub1 values(10)      )    );  */  1)添加分区（原有的分区表没有给定默认分区的前提）  以下代码给sales表添加了一个p3分区  alter table sales add partition p3 values less than(to_date('2003-01-01','yyyy/mm/dd'));  alter table sales add partition p4 values less than(to_date('2000-01-01','yyyy/mm/dd'));   -- 报错  注意：以上添加的分区界限应该高于最后一个分区界限。  以下代码给sales表的p2分区添加了一个p2sub2子分区    alter table sales modify partition p2 add subpartition p2sub2 values(20);  2)删除分区  以下代码删除了p3表分区：  alter table sales drop partition p3;   在以下代码删除了p4sub1子分区：  alter table sales drop subpartition p4sub1;   注意：如果删除的分区是表中唯一的分区，那么此分区将不能被删除，要想删除此分区，必须删除表。  create table myemp5  (    empno  number(4)  primary key,    ename varchar2(10),    hiredate date,    deptno number(7)  )  partition by list (deptno)  (      partition myemp_deptno_10  values (10)  );  -- select * from user_tab_partitions where table_name='myemp5'; 查看分区表情况  alter table myemp5 drop partition myemp_deptno_10;  3)截断分区  截断某个分区是指删除某个分区中的数据，并不会删除分区，也不会删除其它分区中的数据。当表中即使只有一个分区时，也可以截断该分区。通过以下代码截断分区：  alter table sales truncate partition p2;  通过以下代码截断子分区：  alter table sales truncate subpartition p2sub2;   4)合并分区  合并分区是将相邻的分区合并成一个分区，结果分区将采用较高分区的界限，值得注意的是，不能将分区合并到界限较低的分区。以下代码实现了p1 p2分区的合并：  alter table sales merge partitions p1,p2 into partition p2;  5)拆分分区  拆分分区将一个分区拆分两个新分区，拆分后原来分区不再存在。注意不能对hash类型的分区进行拆分。  alter table sales sblit partition p2 at(to_date('2003-02-01','yyyy-mm-dd')) into (partition p21,partition p22);  6)接合分区(coalesca)  结合分区是将散列分区中的数据接合到其它分区中，当散列分区中的数据比较大时，可以增加散列分区，然后进行接合，  值得注意的是，接合分区只能用于散列分区中。通过以下代码进行接合分区：  alter table sales coalesca partition;  7)重命名表分区  以下代码将p21更改为p2  alter table sales rename partition p21 to p2;   -- drop table myemp_test;  create table myemp_test  (    empno  number(4)  primary key,    ename varchar2(10),    hiredate date,    deptno number(7)  )  partition by list (deptno)  (  partition myemp_test_deptno_10 values(10)  -- partition part_default values(default)  -- 给默认分区  );  -- select * from user_tab_partitions where table_name='myemp_test'; 只有默认分区  insert into myemp_test(empno,            ename,            hiredate,            deptno)     values(1,'张三',date'1980-1-1',10);  insert into myemp_test(empno,            ename,            hiredate,            deptno)     values(2,'李四',date'1981-10-02',20); -- 插不进数据  alter table myemp_test add partition myemp_test_deptno_20 values(20); -- 如果建立了默认分区没法再创建其它分区  insert into myemp_test(empno,            ename,            hiredate,            deptno)     values(2,'李四',date'1981-10-02',20); -- 可以插入数据  总结：  1.非分区表, 不能直接改为分区表（通过建立分区表，将旧表数据导入到分区表）  2.创建了分区表, 给了默认分区, 不能添加其它分区  3.建完的分区表, 数据插入只能是当前分区所能包含的数据第5章 pl/sql程序设计      1.pl/sql介绍      2.pl/sql基础    语法结构：pl/sql块的语法    [declare       --declaration statements]①    begin       --executable statements  ②    [exception       --exception statements]  ③    end;      特殊符号：      := 赋值      .. 连续值      ** 求幂    -- 1    begin      dbms_output.put_line('0701bi课程！');    end;        -- 2    declare     v_sql varchar2(100) := '0701bi课程！';    begin      dbms_output.put_line(v_sql);    end;    -- 3    declare     v_1 number :=10;     v_2 number ;     v_3 number;    begin      v_2 :=0;      v_3 := v_1/v_2;      dbms_output.put_line(v_3);    exception      when zero_divide      then dbms_output.put_line('分母为0！');    end;      -- 错误      declare        v_1 number :=2;        v_2 number :=3;        v_3 number :=v_1**v_2;        v_3 := 10;   -- declare 中只能是声明的时候赋初值（此赋值只能放在begin中）      begin        dbms_output.put_line(v_3);      end;      2.1声明与赋值    声明变量必须指明变量的数据类型，也可以声明变量时对变量初始化，变量声明必须在声明部分。    变量名 数据类型[ :=初始值]    代码演示：声明变量    declare      sname varchar2(20) :='jerry';      sno number default 520;      cname varchar2(10);    begin      cname :='bi';      dbms_output.put_line(sname||sno||cname);   --  默认是以字符串打印，字符串连接的方式连接多个变量    end;      2.2 声明常量        常量在声明时赋予初值，并且在运行时不允许重新赋值。使用constant关键字声明常量。    declare      pi constant number := 3.14;      r    number default 3;      area number;    begin      pi := 3;                    -- 报错，常量不可重新赋值      area := pi * r * r;      dbms_output.put_line(area);    end;      2.3 隐式游标赋值      对变量赋值还可以使用select…into 语句从数据库中查询数据对变量进行赋值。      但是查询的结果只能是一行记录，不能是零行或者多行记录。    例题1：打印出emp中员工编号为7369的姓名和工资。    declare      v_ename varchar2(20);      v_sal number(10,2);    begin      select ename,sal      into v_ename,v_sal       -- 隐式游标赋值      from emp where empno=7369;      dbms_output.put_line('员工名称：'||v_ename||' 员工工资：'||v_sal);    end;      注意：使用select…into语句对变量赋值，要求查询的结果必须是一行，不能是多行或者没有记录。    2.4 声明属性数据类型    %rowtype：引用数据库中的一行（所有字段）作为数据类型。    %type：   引用数据库中的某列的数据类型或某个变量的数据类型。    例题1：打印出emp中员工编号为7369的姓名和工资。    -- rowtype    declare      v_emp emp%rowtype;    begin      select ename,sal      into v_emp.ename,v_emp.sal      from emp       where empno=7369;      dbms_output.put_line(v_emp.ename||'--'||v_emp.sal);    end;        -- 不建议如下写法    declare      v_emp emp%rowtype;    begin      select *      into v_emp      from emp       where empno=7369;      dbms_output.put_line(v_emp.ename||'--'||v_emp.sal);    end;    -- type    declare      v_ename emp.ename%type;      v_sal   emp.sal%type;    begin      select ename,sal      into v_ename,v_sal      from emp       where empno=7369;      dbms_output.put_line(v_ename||'--'||v_sal);    end;        练习：1.计算192除以3后的余数并打印出来        2.打印员工7499的员工编号、工作职位和部门名称      3.条件控制      3.1 if 语句      -- if-then    if 条件 then       --条件结构体    end if;    declare      v_num1 number:= 10;      v_num2 number:= 30;    begin      if v_num1>=v_num2 then      dbms_output.put_line(v_num1 ||'>=' ||v_num2);      end if;    end;    -- if-then-else    if 条件 then       --条件成立结构体    else       --条件不成立结构体    end if;    declare      v_num1 number:= 10;      v_num2 number:= 30;    begin      if v_num1>=v_num2 then      dbms_output.put_line(v_num1 ||'>=' ||v_num2);      else      dbms_output.put_line(v_num1 ||'<' ||v_num2);      end if;    end;    -- if-then-elsif    if 条件1 then       --条件1成立结构体    elsif 条件2 then       --条件2成立结构体    else       --以上条件都不成立结构体    end if;    begin      if  1=2 then       dbms_output.put_line('1=2');      elsif 1=3 then       dbms_output.put_line('1=3');      elsif 1=4 then       dbms_output.put_line('1=4');      else  -- 可以没有此分支,有此分支一定不要加条件判断       dbms_output.put_line('1=1');      end if;    end;    3.2 case 语句    case    when 表达式1 then 语句序列1;    when 表达式2 then 语句序列2;    when 表达式3 then 语句序列3;    ……    else 语句序列n;    end case;    begin      case      when 1 = 2 then        dbms_output.put_line('1=2');      when 1 = 3 then        dbms_output.put_line('1=3');      when 1 = 4 then        dbms_output.put_line('1=4');      else        -- 可以没有此分支但是以上语句中必须有一个条件为真,如果有此分支一定不要加条件判断        dbms_output.put_line('1=1');      end case;    end;      练习：1.查找员工编号7521的工资，如果工资小于1500则打印其编号和名字，            如果工资大于1500且小于2000则打印其编号、名字和工资，        否则打印其编号、名字和奖金。              2.查找员工编号7521的工资，如果工资小于1500，若其奖金小于500则奖金再加上100,否则奖金加上50;              如果工资大于等于1500，若其奖金小于200则奖金加上80，否则奖金减去50.（提示：通过update更新comm）      4.循环控制    pl/sql提供了丰富的循环结构来重复执行一些列语句。oracle提供的循环类型有：    1.无条件循环loop-end loop语句    2.while循环语句    3.for循环语句    在上面的三类循环中exit用来强制结束循环。    -- loop循环    loop      --循环体    end loop；    语法格式：    1.循环体在loop和end loop之间，在每个loop循环体中，首先执行循环体中的语句序列，执行完后再重新开始执行。    2.在loop循环中可以使用exit或者[exit when 条件]的形式终止循环。否则该循环就是死循环。    例题1：打印1到100数值（换行）    declare      v_num number :=1;    begin      loop        exit when v_num>100;          -- 退出条件          dbms_output.put_line(v_num);          v_num := v_num + 1;           -- 自增      end loop;    end;    -- while循环（先判断条件，条件成立再执行循环体）    while 条件 loop      --循环体    end loop;    declare      v_num number :=1;    begin      while v_num<=100 loop            dbms_output.put_line(v_num);        v_num := v_num + 1;           -- 自增      end loop;    end;    -- for循环    for 循环变量 in [reverse] 循环下限..循环上限 loop loop    --循环体    end loop；        for循环需要预先确定的循环次数，可通过给循环变量指定下限和上限来确定循环运行的次数，然后循环变量在每次循环中递增（或者递减）    begin      for v_num in 1..100 loop        --  可以不声明变量，数值只能从小到大            dbms_output.put_line(v_num);      end loop;    end;        练习1：打印1到100之间的奇数        练习2：打印10次当前时间(sysdate)        练习3：执行 -1+2-3+4-5+...+100 的值(奇数前是负号，偶数前为+号)    -- 练习3 答案    declare      v_num number := -1;      sum_num number := 0;    begin      loop        exit when abs(v_num) >= 101;        sum_num := sum_num + v_num;        v_num := (abs(v_num)+ 1)*power(-1,abs(v_num)+1);        end loop;        dbms_output.put_line(sum_num);      end;    5.游标    游标的类型有两种：隐式游标和显示游标。    pl/sql会为所有的sql数据操作声明一个隐式的游标，包括只返回一条记录的查询操作。    显示游标四个步骤：    1.声明    2.打开游标    3.逐行获取数据    4.关闭游标    语法结构：声明游标    cursor 游标名[(参数1 数据类型[，参数2 数据类型...])]    is select 语句;  --游标的声明    语法结构：执行游标    open 游标名[(实际参数1[，实际参数2...])];  --打开游标    fetch 游标名 into 变量名1[，变量名2...];    或    fetch 游标名 into 记录变量;  --提取数据    close 游标名;  --关闭游标（千万别忘了！）    游标属性：%found 和 %notfound    %found:    用于判断游标是否从结果集中提取数据。如果提取到数据，则返回值为true，否则返回值为false。    %notfound:    该属性与%found相反，如果提取到数据则返回值为false；如果没有，则返回值为trun。      练习1：查询10号部门所有员工的姓名和工资并打印（pl/sql）    -- 隐式游标赋值错误    declare      v_ename emp.ename%type;      v_sal   emp.sal%type;    begin      select ename,sal        into v_ename,v_sal    -- 多值无法插入        from emp       where deptno=10;      dbms_output.put_line(v_ename||v_sal);    end;    -- 显式游标    declare      cursor c_emp is                   -- 先声明游标（放在declare中）      select ename,sal      from emp       where deptno=10;      v_emp c_emp%rowtype;              -- 声明变量为游标行类型    begin      -- dbms_output.put_line(c_emp);   -- 没法直接打印结果集(表的结果集)      open c_emp;                       -- 打开游标      loop                              -- 游标为结果集所以要有循环            exit when c_emp%notfound;       -- 退出循环条件      fetch c_emp into v_emp;        -- dbms_output.put_line(c_emp); -- 没法直接打印结果集(单行的结果集)        dbms_output.put_line(v_emp.ename||' -- '||v_emp.sal);   -- 只能从变量中取字段      -- exit when c_emp%notfound;        -- 放在最后打印结果会多出最后一行      end loop;      close c_emp;    end;    --for循环    declare      cursor c_emp is      select ename,sal        from emp       where deptno=10;    begin      for v_emp in c_emp loop        dbms_output.put_line('姓名: ' || v_emp.ename || '工资: ' || v_emp.sal);      end loop;    end;    -- 游标参数    例题1：打印某个部门的员工姓名和工资    declare      cursor c_emp(p_deptno emp.deptno%type) is   -- 游标参数      select ename,sal      from emp       where deptno=p_deptno;      v_deptno emp.deptno%type := &部门号;        -- 键盘输入变量值（键盘输入值时，字符型要加单引号）    begin      for v_emp in c_emp(v_deptno) loop    -- 打开游标的时候传入      dbms_output.put_line('姓名: ' || v_emp.ename || '工资: ' || v_emp.sal);      end loop;    end;执行dbms_output.putline(变量名)的时候，报错 ORA-20000：ORU-10027：buffer overflow,limit of 2000 bytes.应该是变量大小超过了dbms_output.putline的缓冲区最大值。解决办法1：修改缓冲区最大值    SQL>set   serveroutput   on   size   1000000  解决办法2：    在 begin 后面加上 dbms_output.enable(buffer_size => null) ，表示输出缓冲区不受限。-- 示例：        declare          cursor cur is            select a.legal_tax_code, a.legal_name              from tax_corpor_info a             where a.entity_id like 'LE%'               and a.entity_id not like '%测试%'               and a.entity_name not like '%测试%';          --v_cur tax_corpor_info%rowtype;          v_cur_code tax_corpor_info.legal_tax_code%type;          v_cur_name tax_corpor_info.legal_name%type;        begin          dbms_output.enable(buffer_size => null); -- 表示输出缓冲区不受限，此句可不加           open cur;          loop            exit when cur%notfound;            fetch cur              into v_cur_code, v_cur_name;            dbms_output.put_line('法人编码为：' ||v_cur_code || '，名称为：' ||v_cur_name);          end loop;          close cur;        end;      6.异常处理    语法格式：异常处理    begin      --可执行部分      exception   -- 异常处理开始        when 异常名1 then           --对应异常处理        when 异常名2 then           --对应异常处理         ……        when others then           --其他异常处理    end;    declare      v_enmae emp.ename%type;    begin      select ename      into v_enmae      from emp       where deptno=10;       dbms_output.put_line('返回单行值');      exception   -- 异常处理开始          when too_many_rows then         dbms_output.put_line('select into 返回多行值！');    end;      7.pl/sql 中的dml和ddl语言      -- dml        create table emp_01 as select * from emp;    begin       delete from emp_01 where deptno=10;       update emp_01 set sal=8888 where deptno=20;       insert into emp_01(empno,deptno) values(1111,40);       commit;    end;        select * from emp_01;        -- ddl    begin       truncate table emp_01;   -- ddl不能直接在pl/sql块中使用    end;      8.动态sql        在pl/sql程序开发中，可以使用dml语句和事务控制语句，但是还有很多语句（比如ddl语句）不能直接在pl/sql中执行。    这些语句可以使用动态sql来实现。    语法格式：动态sql    execute immediate 动态语句字符串    [into 变量列表]    [using 参数列表]      -- 式例1    begin      execute immediate 'create table yyy as select * from emp';    -- 语句最后不要加分号;    end;      或者    declare      v_sql varchar2(100) :='create table yyy as select * from emp'    begin      execute immediate v_sql;    end;    -- 式例2    create table t_tmp_1 (    sno number,    sname varchar(20)    );    -- insert into t_tmp_1 values(1, 'a''lin');    select * from t_tmp_1;    -- 转义    declare      v_sql varchar2(100) := 'insert into t_tmp_1 values(1, ''a''''lin'')';    begin      dbms_output.put_line(v_sql);      execute immediate v_sql;      commit;    end;    -- 用参数传入    declare      v_empno  emp.empno%type := &输入员工编号;      v_deptno emp.deptno%type := &输入部门编号;      v_sal    emp.sal%type;    begin      execute immediate 'select sal from emp where empno = :1 and deptno= :2'    -- :1和:2为参数名，用后面 using 的值替换      into v_sal      using v_empno, v_deptno;      dbms_output.put_line(v_sal);    end;    -- 关于 dbms_output.put 和 dbms_output.put_line    begin      dbms_output.put('0710');    -- 不换行（后面必须有dbms_output.put_line，才能打印）      dbms_output.put_line('bi');      dbms_output.put_line('hahaha');    end;    --    begin      dbms_output.put('0710');      dbms_output.put('bi');      dbms_output.put_line('');    end;    -- 双重循环    begin      for x in 1..3 loop      for y in 4..6 loop        dbms_output.put_line(x||'*'||y||'='||x*y);      end loop;      end loop;    end;      9.创建存储过程    语法格式：创建存储过程    create [or replace] procedure 过程名(参数1 [in|out|in out] 数据类型，参数2 [in|out|in out] 数据类型……)    is|as    pl/sql过程体;      三种参数：传入(in或省去),传出(out),传入或传出(in out)    注意: 1.数据类型不要带字符长度也不用括号 varchar2/number/date          2.存储过程不带参数,过程名后不用括号,is|as 后可以直接跟声明内容,不要declare          3.为in的参数,不能在过程中被赋值,只能作为调用传入值          4.省去参数类型则默认为in传入          5.in out 参数必需在声明时候赋初值,调用中用变量名    语法格式：调用存储过程    begin      过程名[(参数)];    end;    -- 1.不带参数        例题1：从emp表中显示该部门的所有雇员的姓名，工作和薪水。    create or replace procedure sp_emp_deptno_1    is      cursor c_emp_deptno is      select ename, job, sal from emp;    begin      for v_emp_deptno in c_emp_deptno loop        dbms_output.put_line('姓名：' || v_emp_deptno.ename ||                   '工作：' || v_emp_deptno.job ||                   '工资：' || v_emp_deptno.sal);      end loop;    end;    -- 调用    begin      sp_emp_deptno_1;    end;    -- 2.只带传入参数    例题2：接收一个部门号，显示该部门的所有雇员的姓名，工作和薪水。    create or replace procedure sp_emp_info (p_deptno in emp.deptno%type)    is      cursor c_emp is      select ename,job,sal from emp where deptno=p_deptno;    begin      for v_emp in c_emp loop        dbms_output.put_line('姓名:'|| v_emp.ename ||' 工作:' || v_emp.job || ' 薪水:' || v_emp.sal);      end loop;    end;    -- 调用    begin      sp_emp_info(10);    end;    或者    declare          v_deptno  emp.deptno%type :=10;        begin          sp_emp_info (v_deptno);        end;    或者    begin      sp_emp_info(p_deptno => 10);  -- 参数定向调用（多个参数可以改变顺序）    end;    -- 3.带传入和传出参数    例题3：查询某个部门中的某个职位员工的姓名、工资、入职日期。（只考虑单行情况）    create or replace procedure sp_myemp(p_deptno    in  number,                       p_hiredate  out date,                       p_sal       out number,                       p_job_ename in out varchar2)                         ----------参数不能定义长度    is    begin      select e.ename, e.sal, e.hiredate      into p_job_ename, p_sal, p_hiredate  -- p_job_ename 当out参数 传出ename      from emp e       where e.deptno = p_deptno       and e.job = p_job_ename;            -- p_job_ename 当in参数  传入job      dbms_output.put_line(p_job_ename || ' ' || p_sal || ' ' || p_hiredate);    end;    --调用存储过程    declare      v_job_ename varchar2(100) :='manager';  -- in out 参数此处先声明并赋值      v_hiredate date;      v_sal number;    begin      sp_myemp(20, v_hiredate, v_sal, v_job_ename);   -- 存储过程参数要对应（个数和类型）    end;    练习：1.传入部门号和工作类型，返回员工编号和对应工资      10.自定义函数    语法格式：创建函数    create [or replace] function 函数名(参数1 数据类型，参数2，[in|out|in out] 数据类型……)    return 返回的数据类型   -- 不带数据类型长度    is|as    pl/sql函数体; --里面必须要有一个return子句      例题1：传入两个参数,返回最大值    create or replace function fun_max (p_num1 in number, p_num2 in number)        return number        is        begin            if p_num1>p_num2 then          return p_num1;      else          return p_num2;            end if;        end;    调用：    select fun_max(12,20) from dual;    select fun_max(12,20) from emp;  -- 每行返回一个值    练习：1.输入2个整数，返回最小到最大数之间的连乘（两个整数在1到20之间）;              2.输入3个整数，取中位数。        create or replace function fun_jz(p_n1 in number, p_n2 in number)        return varchar2    -- 返回字符串        is          v_m number := 1;        begin          if p_n1 > 20 or p_n2 > 20 or p_n1 < 1 or p_n2 < 1 then          return '超出值范围!';   -- 返回字符串          elsif p_n1 > p_n2 then          for i in p_n2 .. p_n1 loop            v_m := i * v_m;          end loop;          return v_m;     -- 数值隐式转换为字符串返回          else          for i in p_n1 .. p_n2 loop            v_m := i * v_m;          end loop;          return v_m;     -- 数值隐式转换为字符串返回          end if;        end;         create or replace function fun_1(p_num1 number, p_num2 number, p_num3 number)          return number is          v_num1  number;          v_num2  number;          v_num3  number;        begin          v_num1 := (p_num1 - p_num2) * (p_num1 - p_num3);          v_num2 := (p_num2 - p_num1) * (p_num2 - p_num3);          v_num3 := (p_num3 - p_num1) * (p_num3 - p_num2);          if v_num1 <= 0 then          return p_num1;          elsif v_num2 <= 0 then          return p_num2;          else          return p_num3;          end if;        end;        select fun_1(12,13,12) from dual;    11.创建包    包就是把相关的存储过程、函数、变量、常量和游标等pl/sql程序组合在一起，    并赋予一定的管理功能的程序块。    一个程序包由两部分组成：包定义和包体。    其中包定义部分声明包内数据类型、变量、常量、游标、子程序和函数等元素，    这些元素为包的共有元素。包主体则定义了包定义部分的具体实现。    注意：包声明的对象不一定要在包体中去使用,但是包体中的对象一定要在包声明中定义并保持一致。    语法格式：创建包头    create [or replace] package 包名    is|as      变量、常量及数据类型定义;      游标定义头部;      函数、过程的定义和参数列表以及返回类型;    end [包名];    语法格式：创建包体    create [or replace] package body 包名    is|as      procedure 过程名(参数)      is|as      begin      过程体;      end [过程名];      function 函数名(参数) return 类型      is|as      begin      函数体;      end [函数名];    end;    例题1：创建一个包, 包中含有存储过程和函数    create or replace package pk_mypackage    is      a number;      procedure my_sp(p_empno in number);      function my_fun(p_num1 in number , p_num2 in number) return number;    end;    create or replace package body pk_mypackage    is      -- 通过员工号找员工姓名和工资存储过程      procedure my_sp(p_empno in number)    -- 和包中的声明要对应      is      v_ename emp.ename%type;      v_sal   emp.sal%type;      begin      select ename, sal into v_ename, v_sal from emp where empno=p_empno;      dbms_output.put_line('姓名：'|| v_ename ||'工资：'|| v_sal);      exception        when no_data_found then          dbms_output.put_line('select into语句中没有返回任何记录!');        when too_many_rows then          dbms_output.put_line('select into语句中返回多于1条记录!');      end;      -- 比较大小函数      function my_fun(p_num1 in number , p_num2 in number)      return number      is      begin      if p_num1>p_num2 then        return p_num1;      else        return p_num2;      end if;      end;    end;    -- 用户下面的表    select * from user_tables;    -- 用户下面的过程体    select * from user_procedures where object_type ='package'  ;    select * from user_procedures where object_type ='function' ;    select * from user_procedures where object_type ='procedure';    -- 调用存储过程    begin      pk_mypackage.my_sp(&员工编号);    end;        -- 调用函数    select pk_mypackage.my_fun(10,20) from dual;    12.创建日志    日志是用来追溯问题的，记录整个程序的运行情况，知道哪个环节报错了，    记录每一步花了多少时间，判断哪一步性能不好，从而对程序进行修改和优化。    人为创建的日志区别于oracle系统自带的日志，后者调用的成本比较高。    通常报错的时候，会有很多条报错信息，第一条是真正报错的原因。    一般sp里都会有调用日志语句。    语法格式：创建日志    --创建日志表    create table log_record(     log_id number,     sp_name varchar2(100),     cycle_id number,     step number,     finish_time date,     remarks varchar2(100));    --创建序列用于log_id    create sequence seq_log_id;    --创建序列用于cycle_id    create sequence seq_cycle_id;    -- 创建存储过程日志记录    create or replace procedure sp_log(p_sp_name  varchar2,                                       p_cycle_id number,                                       p_step     number,                                       p_remarks  varchar2)    is    begin      insert into log_record      (log_id, sp_name, cycle_id, step, finish_time, remarks)      values      (seq_log_id.nextval, p_sp_name, p_cycle_id, p_step, sysdate, p_remarks);      commit;    end;    begin      sp_log('sp_log',1,1,'日志测试');    end;        select * from log_record;    -- 跑某个存储过程的时候调用日志存储过程    /*    create table empemp as select * from emp where 1=2;    */    create or replace procedure sp_truncate_insert_empemp    is      p_sp_name varchar2(100) := 'sp_truncate_insert_empemp';      p_cycle_id number := seq_cycle_id.nextval;    begin      -- 调用日志存储过程      sp_log(p_sp_name, p_cycle_id, 1, '开始执行存储过程');      execute immediate 'truncate table empemp';      sp_log(p_sp_name, p_cycle_id, 2, '清空empemp表数据');      insert into empemp select * from emp;      sp_log(p_sp_name, p_cycle_id, 3, '全量插入emp数据到empemp表');      commit;    end;    begin      sp_truncate_insert_empemp;    end;    select * from empemp;    select * from log_record;    练习1.触发器记录对emp表的员工编号字段的数据变更情况        2：删除emp表各个部门的经理的相关信息，插入自己的相关信息到emp表，记录存储过程的操作步骤到日志表(存储过程实现)        4个步骤记录到日志表：        1).开始        2).删除        3).插入        4).执行完毕       （日志通过调用sp_log(p_sp_name  varchar2, p_cycle_id number, p_step number, p_remarks varchar2)插入）        3.实现每插入500条数据提交一次      4.创建一张表,包含日期字段,对日期字段通过调用存储过程循环建立分区（3个参数,表名,开始日期,结束日期)    13.触发器简介      触发器的定义就是说某个条件成立的时候，触发器里面所定义的语句就会被自动的执行。      触发器可以分为:      语句级触发器和行级触发器。      具体举例：      1、 在一个表中定义的语句级的触发器，当这个表被删除时，程序就会自动执行触发器里面定义的操作过程。        这个就是删除表的操作就是触发器执行的条件了。      2、 在一个表中定义了行级的触发器，那当这个表中一行数据发生变化的时候，比如删除了一行记录，那触发器也会被自动执行了。      --触发器语法      语法：      create [or replace] trigger trigger_name      {before | after} trigger_event      on table_name      [for each row]      [when trigger_condition]      trigger_body      语法解释：      trigger_name：触发器名称      before | after : 指定触发器是在触发事件发生之前触发或者发生之后触发      trigger_event：触发事件，在dml触发器中主要为insert、update、delete等      table_name：表名，表示发生触发器作用的对象      for each row：指定创建的是行级触发器，若没有该子句则创建的是语句级触发器      when trigger_condition：添加的触发条件      trigger_body：触发体，是标准的pl/sql语句块            例题1：      create table student_123   --创建student表      (        id_no     number(19),    --id_no        stu_no    varchar2(20),  --学号        stu_name  varchar2(32),  --姓名        stu_age   number,        --年龄        stu_major varchar2(32)   --专业      );      create table stu_log_123     --创建stu_log表，用于记录对student表的操作日志      (        log_id     number,         --日志id        log_action varchar2(100),  --操作名称        log_date   date,           --操作时间        log_message   varchar2(32) --日志信息      );      行级触发器（after触发器）      创建触发器：将对student_123表的操作都记录到stu_log_123表中      （of 用于指定一个或多个字段，指定字段被更新时才会触发触发器）      create or replace trigger modify_stu      after insert or delete or update of stu_name  -- 字段      on student_123                                -- 表      for each row  -- 行级触发      begin        if inserting then           insert into stu_log_123 values(1,'insert',sysdate,:new.stu_name);        elsif deleting then           insert into stu_log_123 values(2,'delete',sysdate,:old.stu_name);        elsif updating then          insert into stu_log_123 values(3,'update_old',sysdate,:old.stu_name);          insert into stu_log_123  values(4,'update_new',sysdate,:new.stu_name);         end if;      end;      insert into student_123 values(1,'no2','李四',21,'数学系');  --插入一条数据      delete student_123 where stu_name='张三';                    --删除一条数据      update student_123 set stu_age=19 where stu_name='李四';     --修改李四的年龄      update student_123 set stu_name='王二' where stu_name='李四';--修改李四的名称      select * from student_123;      select * from stu_log_123;      语句级触发器（before触发器）：用来控制对表的修改      create or replace trigger modify_stu_table      before insert or update or delete on student_123      begin         if deleting then         raise_application_error(-20001,'该表不允许删除数据');         elsif updating then         raise_application_error(-20002,'该表不允许修改数据');        elsif inserting then         raise_application_error(-20003,'该表不允许插入数据');        end if;      end;      delete from student_123;      drop table student_123;      -- 表字段的自增序列（触发器+序列）        create table t_test(sno number,sname varchar2(20));      create sequence s_test;      create or replace trigger t_test_sno        before insert on t_test -- 表        for each row -- 行级触发      declare        nextid number;      begin        if :new.sno is null or :new.sno = 0 then --sno是列名        select s_test.nextval --s_test 序列          into nextid          from dual;        :new.sno := nextid;        end if;      end;           select * from t_test;           insert into t_test(sname) values('张三');           insert into t_test(sname) values('李四');    练习1.触发器记录对emp表的员工编号字段的数据变更情况        2：删除emp表各个部门的经理的相关信息，插入自己的相关信息到emp表，记录存储过程的操作步骤到日志表(存储过程实现)        4个步骤记录到日志表：        1).开始        2).删除        3).插入        4).执行完毕       （日志通过调用 sp_log (p_sp_name  varchar2, p_cycle_id number, p_step number, p_remarks varchar2)插入）        3.实现每插入500条数据提交一次        4.创建一张表,包含日期字段,对日期字段通过调用存储过程循环建立分区（3个参数,表名,开始日期,结束日期)      14.同步数据     （1）全量同步     （2）增量同步    （一般从业务库采集数据，今天凌晨12之后抽取数据：      1.业务数据昨日交易截止      2.业务交易在凌晨12发生很少或者不发生，不会影响业务系统的性能      全量：抽取昨天以及之前的数据;      增量：抽取昨天增量数据。     ）    全量：将目标表的数据全部删除，再将源系统的数据全部插入目标表。    缺点：此方法保证了数据的质量，但是对于数据量大的表而言，性能太差。    --全量抽取    例题1：同步emp数据到目标表t_myemp    create table t_myemp as select * from emp where 1=2;    alter table t_myemp add last_syn_date date;    create or replace procedure sp_syn_emp    is    /*    源表：    emp    目标表：  t_myemp    创建人：  wh    创建时间：2019-07-01    修改时间：2019-07-10    修改说明：增加目标表字段last_syn_date    功能说明：从emp全量抽取数据到t_myemp中    */    begin      -- delete from t_myemp;      execute immediate 'truncate table t_myemp';      insert into t_myemp(empno,                ename,                job,                mgr,                hiredate,                sal,                comm,                deptno,                last_syn_date                )             select empno,                ename,                job,                mgr,                hiredate,                sal,                comm,                deptno,                sysdate             from emp;      commit;    end;    /    begin      sp_syn_emp;    end;    /    1.尽量不使用*（不直观; 源表和目标表结构字段不对应;源表如果有新增字段，目标表没有及时调整结构，会报错）    2.表最好别名，不易产生分歧。    工作中做全量抽取时，通常先判断源表是否有数据，再执行。    如果源表没有数据了，同步之后目标表也没有数据了，只能从硬盘里找回数据，成本代价高    --增量抽取（按时间增量）    只需抽取新增的或修改的数据。此方法性能好，但容易遗漏。    有时源表更新的字段，在目标表中不存在，则不需要更新。以时间戳取增量，对源表删除的数据无能为力。    --1.源系统表    create table t_emp_source as select * from emp where 1=2;    alter table t_emp_source add created_date date;  -- 创建时间    alter table t_emp_source add updated_date date;  -- 更新时间    --2.目标表    create table t_emp_syn as select * from t_emp_source where 1=2;    alter table t_emp_syn add syn_date date;  -- 同步时间    -- 2019-06-17 业务表t_emp_source进入2条数据    insert into t_emp_source(empno,                 ename,                 job,                 mgr,                 hiredate,                 sal,                 comm,                 deptno,                 created_date,                 updated_date)              select empno,                 ename,                 job,                 mgr,                 hiredate,                 sal,                 comm,                 deptno,                 date'2019-06-17',                 date'2019-06-17'              from emp               where empno in (7369, 7499);    commit;    select * from t_emp_source;    --select * from t_emp_syn;    -- 2019-06-18 凌晨12后开始全量同步一次业务库数据t_emp_source到目标表t_emp_syn    create or replace procedure sp_emp_syn    is    /*    源表：    t_emp_source    目标表：  t_emp_syn    创建人：  wh    创建时间：2019-06-18    修改时间：    修改说明：    功能说明：从t_emp_source全量抽取数据到t_emp_syn中    */    begin      -- delete from t_emp_syn;      execute immediate 'truncate table t_emp_syn';      insert into t_emp_syn(empno,                ename,                job,                mgr,                hiredate,                sal,                comm,                deptno,                created_date,                updated_date,                syn_date)             select   empno,                ename,                job,                mgr,                hiredate,                sal,                comm,                deptno,                created_date,                updated_date,                date'2019-06-18'             from t_emp_source;      commit;    end;    /    begin      sp_emp_syn;    end;    /    --select * from t_emp_source;    select * from t_emp_syn;    -- 2019-06-18 t_emp_source表只更改7369的数据，并且新增12条数据    update t_emp_source set comm = 999 where empno=7369;    update t_emp_source set updated_date=date'2019-06-18' where empno=7369;    insert into t_emp_source(empno,                 ename,                 job,                 mgr,                 hiredate,                 sal,                 comm,                 deptno,                 created_date,                 updated_date)              select empno,                 ename,                 job,                 mgr,                 hiredate,                 sal,                 comm,                 deptno,                 date'2019-06-18',                 date'2019-06-18'              from emp               where empno not in (7369, 7499);    commit;    -- 2019-06-19 凌晨12后开始增量同步18号更新的业务库数据t_emp_source到目标表t_emp_syn,7369奖金更改999，7499数据不变，其它为新增数据    --方法一 merge into    语法格式：merge（不是所有数据库都通用）;    merge into 目标表    using (增量)    on (匹配字段) -- on 后面的关联条件要用括号包起来    when matched then update set   --update和set之间不需要加表名    when not matched then insert values;    --insert和values之间不需要加into 表名    create or replace procedure sp_emp_add_1(p_start_date varchar2,                         p_end_date varchar2)    is      v_start_date date := to_date(p_start_date, 'yyyy-mm-dd hh24:mi:ss');      v_end_date   date := to_date(p_end_date, 'yyyy-mm-dd hh24:mi:ss');    begin      merge into t_emp_syn e      using (select             empno,             ename,             job,             mgr,             hiredate,             sal,             comm,             deptno,             created_date,             updated_date               from t_emp_source          where updated_date>=v_start_date            and updated_date<v_end_date         ) t       on (e.empno=t.empno)       when matched then update set            -- e.empno=t.empno,  -- 主键关联字段不要更新            e.ename=t.ename,            e.job=t.job,            e.mgr=t.mgr,            e.hiredate=t.hiredate,            e.sal=t.sal,            e.comm=t.comm,            e.deptno=t.deptno,            e.created_date=t.created_date,            e.updated_date=t.updated_date,            e.syn_date=v_end_date       when not matched then       insert                (e.empno,                 e.ename,                 e.job,                 e.mgr,                 e.hiredate,                 e.sal,                 e.comm,                 e.deptno,                 e.created_date,                 e.updated_date,                 e.syn_date)                   values                (t.empno,                 t.ename,                 t.job,                 t.mgr,                 t.hiredate,                 t.sal,                 t.comm,                 t.deptno,                 t.created_date,                 t.updated_date,                 v_end_date);            -- commit;    end;    /        begin      sp_emp_add_1('2019-06-18','2019-06-19');    end;    /    --方法二 删除更新的再全部插入    先将目标表的记录在增量范围之内的删除，然后再将增量插入    create or replace procedure sp_emp_add_2(p_start_date varchar2,p_end_date varchar2)        is      v_start_date date := to_date(p_start_date, 'yyyy-mm-dd hh24:mi:ss');      v_end_date   date := to_date(p_end_date, 'yyyy-mm-dd hh24:mi:ss');    begin      delete from t_emp_syn where empno in             (select empno                from t_emp_source               where updated_date >= v_start_date                 and updated_date <    v_end_date);      insert into t_emp_syn(empno,                  ename,                  job,                  mgr,                  hiredate,                  sal,                  comm,                  deptno,                  created_date,                  updated_date,                  syn_date)             select                 empno,                 ename,                 job,                 mgr,                 hiredate,                 sal,                 comm,                 deptno,                 created_date,                 updated_date,                 v_end_date               from t_emp_source              where updated_date >= v_start_date                and updated_date <    v_end_date;                     -- commit;    end;    /        begin      sp_emp_add_2('2019-06-18','2019-06-19');    end;    /    -- 方法三 游标    create or replace procedure sp_emp_add_3(p_start_date varchar2,                 p_end_date varchar2) is        v_start_date date := to_date(p_start_date, 'yyyy-mm-dd hh24:mi:ss');        v_end_date   date := to_date(p_end_date, 'yyyy-mm-dd hh24:mi:ss');        cursor c_update is        select e.empno,           e.ename,           e.job,           e.mgr,           e.hiredate,           e.sal,           e.comm,           e.deptno,           e.created_date,           e.updated_date        from t_emp_source e         where e.updated_date >= v_start_date         and e.updated_date < v_end_date;        ct number;      begin        for x in c_update loop        select count(1) into ct from t_emp_syn a where a.empno = x.empno;        if ct = 1 then        update t_emp_syn a         set a.ename       = x.ename,           a.job         = x.job,           a.mgr         = x.mgr,           a.hiredate    = x.hiredate,           a.sal         = x.sal,           a.comm        = x.comm,           a.deptno      = x.deptno,           a.created_date = x.created_date,           a.updated_date = x.updated_date,           a.syn_date = date'2019-06-19'         where a.empno = x.empno;        elsif ct = 0 then        insert into t_emp_syn a        (a.empno,         a.ename,         a.job,         a.mgr,         a.hiredate,         a.sal,         a.comm,         a.deptno,         a.created_date,         a.updated_date,         a.syn_date)        values        (x.empno,         x.ename,         x.job,         x.mgr,         x.hiredate,         x.sal,         x.comm,         x.deptno,         x.created_date,         x.updated_date,         v_end_date);        end if;        end loop;        commit;      end;    /        begin      sp_emp_add_3('2019-06-18','2019-06-19');    end;    /    两张表a、b关联抽数如何抽取？    --方法1    两张表全量关联，a表的时间或b表的时间在时间范围内    --方法2    (a表的增量与b表的全量关联)union all(b表的增量与a表的全量关联)    --方法3    (a的增量主键union all b增量主键group by 主键)left join a on 主键 left join b on 主键    merge 的灵活应用;    若数据量大时，merge删除比delete好。;    merge into emp e    using (select * from emp where deptno = 10) s    on (s.empno = e.empno)    when matched then  update set e.comm = e.comm delete where 1 = 1;   --set随便改，后面接delete  15.建表注意    create table orcl_emp_syn    ("empno" number(4,0),      "ename" varchar2(10),      "job" varchar2(9),      "mgr" number(4,0),      "hiredate" date,      "sal" number(7,2),      "comm" number(7,2),      "deptno" number(2,0),      "etl_date" date    );    create table "orcl_emp_syn"    ("empno" number(4,0),      "ename" varchar2(10),      "job" varchar2(9)    );    select * from orcl_emp_syn;    select * from "orcl_emp_syn";    16.insert all    -- 多条插入    create table t1(product_id number, product_name varchar2(80),p_month number);    insert into t1 values(111, '苹果',1);    insert into t1 values(222, '橘子',1);    insert into t1 values(333, '香蕉',1);    commit;    create table t2 as select * from t1 where 1=2;    insert all      into t2      values (product_id, product_name,p_month)      into t2      values (product_id, product_name,p_month+1)      into t2      values (product_id, product_name,p_month+2)      into t2      values (product_id, product_name,p_month+3)    select product_id, product_name, p_month    from t1;    commit;    select * from t2 order by product_id, product_name, p_month;    -- 多表插入        create table apple_orders as select * from t1 where 1=2;        create table orange_orders as select * from t1 where 1=2;        create table banana_orders as select * from t1 where 1=2;    insert all      when product_id = 111 then        into apple_orders      when product_id = 222 then        into orange_orders      else        into banana_orders      select product_id, product_name, p_month        from t1;    commit;    select * from apple_orders;    select * from orange_orders;    select * from banana_orders;第六章 bi理论    先预习数据仓库、建模    https://blog.csdn.net/sjmz30071360/article/details/80822085    https://segmentfault.com/a/1190000012882641  1.数据库和数据仓库    数据库一般是oltp作数据交易的业务数据源系统，    数据仓库一般是olap作数据分析的数据库。  2.bi系统结构常见    业务数据库1   --|              |-- dm1    业务数据库2   --|——>ods——>dw——>|-- dm2    ...           --|              |-- dm3    |_______________|_____________________|        |                  |      源系统          数据仓库架构体系    3.数据采集（etl）    一般采用etl工具：datastage、informatica、kettle    为什么业务库到ods不采用存储过程？    采集相关问题：      （1）调度时间：一般在凌晨      （2）抽取频率：按天、按月等（今天抽取业务库昨天的增量,本月初抽取上个月的数据）      （3）抽取方式：增量或全量  4.建模      一、建模方法       （1）三范式建模（一般在dw层建模）：https://blog.csdn.net/charlesyoosky/article/details/89204229        https://www.cnblogs.com/thybk/p/7340094.html      第一范式（无重复的列）: /*保证每列的原子性，每一列都不可分割*/      定义：数据库表的每一列都是不可分割的原子数据项，而不能是集合，数组，记录等非原子数据项。          如果实体中的某个属性有多个值时，必须拆分为不同的属性          通俗解释：一个字段只存储一项信息，一个字段不可拆分          例如 字段- 住址：广东省广州市天河区冼村XX街道XX号，这是一个很具体的字段值，                           但是它是可以拆分的，这就不符合第一范式，需要查分成以下几个字段：省份/XX市/XX区/XX村/XX街道/XX号               第二范式（属性完全依赖于业务主键）：/*保证一张表只描述一件事情*/      定义：满足第一范式前提，当存在多个主键的时候，才会发生不符合第二范式的情况。          比如有两个主键，不能存在这样的属性，它只依赖于其中一个主键，这就是不符合第二范式         通俗解释：任意一个字段都只依赖表中的同一个字段，就是一张表只讲一件事，可以理解为维度表，每个表都有自己的故事，不会出现其他的故事，否则就会出现冗余的情况               第三范式（属性不能传递依赖于主属性）:/*保证每列都和主键直接相关*/      定义：满足第二范式前提，如果某一属性依赖于其他非主键属性，          而其他非主键属性又依赖于主键，那么这个属性就是间接依赖于主键，这被称作传递依赖于主属性。          通俗理解：一张表最多只存2层同类型信息       （2）维度建模（一般在dm层建模）        https://blog.csdn.net/mark_wu2000/article/details/82668787        https://www.cnblogs.com/benchen/p/6011721.html        事实表（维度和度量）        维度表（描述维度的属性）        --事实表        事实表存储了从业务活动或事件提炼出来的性能度量，        它主要包含维度表的外键和连续变化的可加性数值或半可加事实。        事实表产生于业务过程中而不是业务过程的描述性信息。        它一般是行多列少，占了数据仓库的90%的空间。        在维度模型中也有表示多对多关系的事实，其他都是维度表。        事实表粒度        事实表的粒度是产生事实行的度量事件的业务定义。        粒度确定了事实表的业务主键， 事实表的所有度量值必须具有相同的粒度。        --维度表        常见维度        日期、地区、客户、产品等        维度表是对业务过程的上下文描述，主要包含代理键、文本信息和离散的数字。        它是进入事实表的入口，丰富的维度属性给出了对事实表的分析切割能力，它一般是行少列多。        如果属性值是离散的，用于过滤和标记的，就放到维度表里，        如果是属性值是连续取值，用于计算的，就放到事实表中。        -- 例子        select * from emp; -- 事实表        select * from dept;-- 维度表        1.粒度：员工编号        2.维度：（1）员工维度【姓名、性别、电话、证件号等】            （2）部门维度【编号、名称等】            （3）日期【日、周、月、季度、年等】        3.度量：工资、奖金        （1）星型模型        星型模型是以事实表为中心，所有的维度表直接连接在事实表上，        星型模型的特点是数据组织直观，执行效率高。        （2）雪花模型        雪花模型的维度表可以拥有其他维度表的，        虽然这种模型相比星型模型更规范一些，        但是由于这种模型不太容易理解，维护成本比较高，        而且性能方面需要关联多层维表，性能也比星型模型要低。        （3）星座模型        星座模型是星型模型延伸而来，星型模型是基于一张事实表的，        而星座模型是基于多张事实表的，而且共享维度信息。    二、建模工具：er-win、powerdesigner        通过对业务熟悉,采取相关的模型概念,设计表结构以及表之间的关联关系。      最后导出建表语句到相应的数据库中生成表结构。    5.报表展现（表格和图形）    报表工具：cognos、tableau、powerbi、finereport    设计报表的表结构,编写存储过程或sql加工报表数据,用工具制作报表。  6.跨库查询数据和编写存储过程      database links（不同库） 区分同义词synonyms（同库不同用户）    /* database link（ods数据库的sys用户下创建）       连接到orcl数据库的scott用户      【楼当做orcle的数据仓库体系，楼中每一层当做不同数据库，每一层中的房间当做不同的用户】    */    -- 创建    create public database link db_orcl    connect to scott identified by "123456"  -- 数字密码"123456"    using '192.168.0.222:1521/orcl';         -- 需要连接的数据ip/端口/数据库名    -- 查询    select * from emp@db_orcl;    -- 删除    drop public database link db_orcl;       -- 删除database link    select * from emp@db_orcl;    7.定时调度作业      （1）数据采集的定时调度         1.kettle 中job可以设置定时任务       2.linux 定时任务crontab       3.第三方调度工具 ctm(control_m)、azkaban、moia等等      （2）设置存储过程定时作业（dbms_job）      该过程用于建立一个新的作业，当建立作业的时候，需要通过设置相应的参数来告诉oracle要执行的内容，要执行的时间，要执行任务的间隔。如下格式：      dbms_job.submit(         job out binary_interger,                  -- 用于指定作业编号         what in varchar2，                        -- 用于指定作业要执行的操作         next_date in date default sysdate,        -- 用于指定该操作的下一次运行的日期         interval in varchar2 default 'null',      -- 用于指定该操作的时间间隔         no_parse in boolean default false,        -- 用于指定是否需要解析与作业相关的过程         instance in binary_integer default any_instance,  -- 用于指定哪个例程可以运行作业？         force in boolean default false            -- 用于指定是否强制运行与作业相关的例程      );        -- 举例        create table testjob(        id number constraint testjob_id_pk primary key,        name varchar2(30),        ct_dt date        );        --如果序列存在就删除，然后创建一个序列        create sequence testjob_id_seq;        --创建一个序列，每一次向表中插入一条数据，并且表中的id字段值使用序列指定        create or replace procedure insert_job is        begin        insert into testjob        values          (testjob_id_seq.nextval, 'test' || testjob_id_seq.currval,sysdate);        commit;        end;        declare        jobno binary_integer;        begin        --提交,操作的时间间隔设置为1分钟        dbms_job.submit(jobno,'insert_job();',sysdate,'sysdate+1/(24*60*6)');        --打印序列号        dbms_output.put_line('jobno='||jobno);        --运行        dbms_job.run(jobno);        end;        -- 删除        begin        dbms_job.remove(jobno);  -- jobno为具体数值        end;  8.查看执行计划（执行计划窗口运行sql)    1.箭头下一步可以查看执行先后顺序    2.看缩进：      （1）缩进最多的最先执行（缩进相同时，最上面的最先执行，接着执行其父节点）      （2）执行父节点的并列节点的子节点（多个并列节点，从上到下，接着执行其父节点）      （3）执行父节点的上一个节点      （4）依次类推，执行完所有节点为止        问题1：存储过程比较耗时，怎么解决问题？        （1）从日志记录表查看存储过程每一段的耗时，将耗时比较长的语句找出来        （2）先看各个单表查询耗时(表被锁或被占用)        （3）关联字段是否建立索引        （4）索引是否失效        （5）执行计划查看关联机制        （6）调整关联机制（强制改变）        注意：关联字段如果没有全部建立索引（都要全表扫描）        select e.empno,d.dname        from dept d,emp e         where d.deptno=e.deptno;        关联字段如果全部建立索引（驱动表全表扫描从表走索引）        一般来说：from 最后面的表作为驱动表，所以最后面的表要放小表*******        同理：where 条件中执行顺序为从后往前，所以能够过滤掉大部分数据的条件写在最后*******        -- 例如1亿的数据量包含3年的日期数据        select *          from 表         where 性别='男'           and 日期=date'2019-08-07';    9.关联机制      https://www.cnblogs.com/polestar/p/4132911.html    在oracle中，关联机制有三种方式：嵌套循环、哈希连接、（归并）排序合并连接    嵌套循环关联（nested loops join（nl））：是指依次从驱动表中提取一条记录，遍历被探查表，将匹配的记录放入待展示的缓存区中。    优点：适用广，占用内存小，展现快    缺点：需要不停地从硬盘中读取扫描表，性能不好    注意：把两张表最终需要关联的数据对比，大表适合做被探查表，因为可以减少从硬盘读取扫描表的次数。    哈希关联（hash join（hj））：计算出整张被探查表关联字段的哈希值，这些哈希值和整张被探查表一起放入缓存区，                                 然后从驱动表逐条取记录，计算出关联字段对应的哈希值，                                 再与被探查表的哈希值匹配,匹配上了再精准匹配每一条记录。    优点：性能好，匹配次数大大减少    缺点：只适用于等值关联，占用内存较大    注意：把两张表最终需要关联的数据对比，小表适合做被探查表，因为怕缓存不够。如果缓存足够的前提下，大表适合做被探查表。    排序合并连接(sort merge join (smj) )：是指将关联的a表跟b表分别进行排序，生成临时的两张表后，随机取一张表逐条抽取记录与另一张表匹配。    优点：适合有索引的两张表或者不等关联    缺点：排序性能消耗大，占用内存大      -- 关联字段没建立索引(两张表数据量很小)      -- hash join (table access full)      select e.empno,d.dname        from emp e, dept d       where e.deptno=d.deptno;      -- 关联字段都建立索引(两张表数据量很小)      -- create index emp_deptno on emp(deptno);      -- nested loops (table access full)      select e.empno,d.dname        from emp e, dept d       where e.deptno=d.deptno;      驱动表：dept 全表逐行扫描（table access full  -- sort join）      从表：  emp 走索引       （index full scan    -- merge join)    10.hints    --常用hints    1、/*+ parallel(表名1,并行数)[(表名2,并行数)……] */ --指定开启多少个并行|并发（一般为2、4、8……）    2、/*+ index(表名,索引名) */ --指定索引    3、/*+ full(表名) */ --指定全表扫描    4、/*+ use_nl(表名1，表名2) */ --指定用nested loop连接    5、/*+ use_hash(表名1，表名2) */ --指定用hash连接    6、/*+ use_merge(表名1，表名2) */ --指定用sort merge join    7、/*+ leading(表名1，表名2) */ --指定表1作为驱动表    8、/*+ append */ --数据直接插入到高水位上面(与insert连用)直接往后面插，无视前面的空位置        select /*+ use_merge(e,d)*/ *          from emp e, dept d         where e.deptno=d.deptno;    11.缓慢变化维      https://www.cnblogs.com/xqzt/p/4472005.html    在从 oltp 业务数据库向 dw 数据仓库抽取数据的过程中，    特别是第一次导入之后的每一次增量抽取往往会遇到这样的问题：    业务数据库中的一些数据发生了更改，到底要不要将这些变化也反映到数据仓库中？    在数据仓库中，哪些数据应该随之变化，哪些可以不用变化？    考虑到这些变化，在数据仓库中的维度表又应该如何设计以满足这些需要。    type 1 scd :不记录历史数据,新数据覆盖旧数据    type 2 scd: 保存多条记录,直接新添一条记录，同时保留原有记录，并用单独的专用的字段保存区别    type 3 scd：添加历史列，用不同的字段保存变化痕迹.它只能保存两次变化记录.适用于变化不超过两次的维度。    算法：（拉链表算法其实就是以前遇到过的缓慢变化维的其中一种情况，用存储过程实现的话稍微麻烦点。）    1采集当日全量数据到nd(newday)表；（业务库当前所有数据的最新状态）    2可从历史表中取出昨日全量数据存储到od(oldday)表;（数据同步的拉链表）    3(nd-od)就是当日新增和变化的数据，也就是当天的增量，用w_i表示；    4(od-nd)为状态到此结束需要封链的数据，用w_u表示；    5将w_i表的内容全部插入到历史表中，这些是新增记录，start_date为当天，而end_date为max值；    6对历史表进行w_u部份的更新操作，start_date保持不变，而end_date改为当天，也就是关链操作；  12.项目经验与流程    外包：    甲乙方项目经理    乙方人员构成：    （1~2）数据采集（业务库到ods）    （2+1）后台开发人员（ods-dw 数据库开发etl，ods-dw数据仓库建模）    （2+1）后台开发人员（dw-dm 数据库开发etl，dw-dm数据仓库建模）    （2~3）前台开发人员（报表开发）     数据仓库建模-- 设计人员（ta|sa）：表结构设计、源表到目标表的映射关系、抽取规则>>mapping    （1）需求分析人员（ba）：（偏后台的）维度、指标、粒度、取数范围、怎么取增量、跑数频率>>rs文档（require specification）      维度：看问题的角度（时间+地区+产品  时间+产品+部门）      指标：（衡量的标准）销售额，采购金额      粒度：统计粒度，例如统计人口（统计到户还是统计到人还是村）      取数范围：（重要的保留）过滤不需要的字段    流程：    1.需求评审（技术实现，工具展现，人力情况，后期维护等等）    2.开发文档制定（项目经理）    3.开发、自测    4.测试组测试    5.上线    6.维护    需求评审：开发、测试、产品（功能、数据能否实现）    开发：    根据业务需求，从数据库找到需要的数据来源（表以及字段），编写存储过程    （自测，项目经理看代码规范、以及业务是否理解正确）    测试部分：测试组会进行数据测试以及功能测试（测试会跟产品进行沟通）    上线：    一周一个版本（修复、小报表上线、上线时候甲方运维到机房上线）    新旧版本迭代1个月；    上线后进行数据以及功能验证，出现bug无法解决的放在下一个版本上线；    自测:存储过程空跑，历史数据验证    后期维护：与甲方产品人员对接（有问题从数据源查起，后期添加指标）    乙方外包：    12个人：    项目负责人——1人；    业务库到ods数据采集——1人；  etl工程师：kettle、datastage、informatica    ods到dw数据建模——1人；      数据仓库工程师：范式建模powdesigner    ods到dw开发存储过程——2人；  etl工程师：存储过程（基于pl/sql、python、perl）    dw到dm数据建模——1人；       数据集市: 维度建模powdesigner    dw到dm开发存储过程2人；     etl工程师：存储过程（基于pl/sql、python、perl）    dm到报表展现——2人；         报表开发工程师：finereport、cognos、tableau、powerbi（写sql、存储过程）    调度运维人员——1人；         moia、moia_control    项目助理——1人    甲方自研：    1个项目经理和1个产品专员    1个数仓架构    2个数据仓库工程师（数仓和集市建模）    3个etl工程师（1个数据采集并且配置调度2个编写存储过程）    项目：    1.项目主要内容（目的）    2.项目实施流程（每一步怎么实现）    3.项目中的职位（你是负责哪一块）    4.项目维护（跟踪处理问题）    sql开发工具：pl/sql developer    etl工具：datastage、informatica、kettle    调度工具：moia、control-m（ctm）、linkdo（平安自研）、azkaban    报表工具：oracle biee、bo、水晶报表、帆软、cognos    建模工具：er-win、powerdesigner    配置管理工具：rational clearcase、rtc、svn、git        数据采集问题：    1.用工具还是写存储过程？  -- 不同的数据库采用工具，同一种数据库可以写存储过程同步数据    2.数据采集的频率？        -- 按天，按月，按时？    3.数据采集的逻辑？        -- 全量，增量    4.全量和增量数据量多大？  -- 单表数据量    5.数据采集的流程？        -- 1.业务需求分析（取数规则，字段等）2.业务库表结构 3.ods建立表 4.etl作业 5.配置调度    6.遇到的问题？            -- 1.数据类型问题（布尔类型0/1 同步后变为是/否） 2.小数精度问题 3.编码问题（乱码） 4.数据量问题    7.清洗的数据？            -- 1.缺少 2.重复 3.错误 4.多余    8.数据质量管理？    9.当前同步表有多少？    10.调度配置问题？         -- 1.依赖配置 2.监控    数据建模问题：    1.什么工具建模？    2.建模方法？    3.模型有哪些？    4.设计多少张表？    5.主题和指标？    6.维度和事实表？    写存储过程问题：    1.项目里面写了多少个存储过程？    2.最长的代码写了多少？    3.处理数据量有多大？    4.存储过程比较慢怎么定位和优化？    5.结合项目谈谈最复杂的存储过程？    6.存储过程中游标？批量插入数据？多进程问题？    报表开发问题：    1.开发工具？    2.设计表和写sql问题？    3.需求对接分析？    4.数据核对？    关于项目面试:    1.项目简介    2.人员构成    3.数据库的架构    4.你负责的内容    5.数据量的大小（多少张表、最大单表行数或占的空间）    6.存储过程最多写了多少行    7.最复杂的存储过程    8.项目开发中遇到的最大问题    9.你做过对于整个项目比较突出的贡献    10.会结合他们的项目提出某个问题让你解答    13.linux基础命名      进入目录：cd 目录名称    清屏：    clear    展示目录下文件： ls -l    创建文件：  touch 文件名    创建文件夹：mkdir 文件夹    编辑文件：  vi 文件名    查看文件内容： cat  文件名                   more 文件名                   head -10 文件名             tail -10 文件名    下载文件:   sz 文件名    上传文件：  rz 文件名    复制文件：  cp 文件名  目录    文件改名：  mv 文件名  新的文件名    移动文件：  mv 文件名  目录    删除文件：  rm 文件名    删除文件夹：rmdir -p 文件夹    解压文件：  tar -zxvf /tmp/etc.tar.gz /etc    压缩文件：  tar -zcvf /tmp/etc.tar.gz /etc    linux设置定时作业：crontab -e    Linux 文件基本属性: chown修改所属组 和 chmod修改文件属性命令  14.高水平位     https://1050113483.iteye.com/blog/2257808  15.物化视图       https://blog.csdn.net/joshua_peng1985/article/details/6213593  16.关联方式实战（不考虑where过滤）：    a表 14条数据    b表 4条数据    1.内关联 有多少条数据？    范围：0~56    --0条数据（关联不上）    select e.*,d.*      from emp e      join dept d      on e.empno=d.deptno;    -- 14条数据（全部都关联上）    -- 56条数据（笛卡尔积）    select e.*,d.*      from emp e      join dept d      on 1=1;    2.左关联 有多少条数据？（a为主表）    范围：14~56    -- 14条数据    没有关联上是14条;    正常关联，a和b关联上的字段都不重复    -- 56条数据（笛卡尔积）    select e.*,d.*      from emp e      left join dept d      on 1=1; --其中的'1=1'，代表无条件匹配，全部成立  17.删除数据     （1）从1亿条数据删除重复500万数据？          -- rowid     （2）从1亿条数据删除某个条件下的500万数据？  -- merge     （3）快速清除表中所有数据？(不可追回数据)    -- truncate     （4）表数据量不大,有条件的删除表中某些数据？ -- delete    18.去重的方法有哪些？  19.分析函数？行列转换？分页？排序？  20.其它    18.去重的方法有哪些？  19.分析函数？行列转换？分页？排序？  20.其它  21. 什么索引失效，你怎么判定      /*    1.大批量，大吞吐量    2.并行：merge    3.统计函数：rank,rollup, cube    4.move data:tts tablespace,merge/exchage partition,external table    5.不用cursor;用view, global temp table    6.mqt,可以有效加快统计类报表的速度    hw建议：    1.使用游标来处理逻辑，用游标处理的效率是最慢的方式，游标遍历次数太多，用view, global temp table，统计函数。    2.使用dblink,用mqt。    3.索引太多，造成insert, delete太慢，    4.全部是串行操作，应该允许采用并行，可以采用merge。    5.消除人为不能使用索引的情况和建立复合索引    6.表连接尽量使用 hash join.    */  --补充  coalesce 函数，返回表达式列表中的第一个非空表达式。  coalesce(expr1, expr2, expr3….. exprn)，依次参考各参数表达式，遇到非null值即停止并返回该值。如果所有的表达式都是空值，最终将返回一个空值。使用COALESCE在于大部分包含空值的表达式最终将返回空值。  greatest(value1, value2, value3, ...)返回value值列表中最大的一个值  least(value1, value2, value3, ...)   返回value值列表中最小的一个值  nvl2函数的格式如下：NVL2(expr1,expr2, expr3)，含义是：如果该函数的第一个参数为空那么显示第二个参数的值，如果第一个参数的值不为空，则显示第三个参数的值。  nullif(exp1,expr2)，NULLIF函数的作用是如果exp1和exp2相等则返回空(NULL)，否则返回第一个值  ---------  ceil(n) 取大于等于数值n的最小整数；--向上取整  floor(n)取小于等于数值n的最大整数；--向下取整  -------------  cast，数据类型的转换，如：select cast('123.4567' as number(10,2))  as num from dual----------123.46  --  varchar2(size type)，size最大为4000，type可以是char也可以是byte，不标明type时默认是byte（如：name  VARCHAR2(60)）。  nvarchar2(size)，size最大值为2000，单位是字符  varchar2最多存放4000字节的数据，最多可以可以存入4000个字母，或最多存入2000个汉字  （数据库字符集编码是GBK时，varchar2最多能存放2000个汉字，数据库字符集编码是UTF-8时，那就最多只能存放1333个汉字，呵呵，以为最大2000个汉字的傻了吧！）  nvarchar2（size），size最大值为2000，单位是字符，而且不管是汉字还是字母，每个字符的长度都是2个字节。  所以 nvarchar2 类型的数据最多能存放2000个汉字，也最多只能存放2000个字母。并且 NVARCHAR2 不受数据库字符集的影响。  对于超出一定长度的文本，基本上用 long 型存储（数据类型），比如数据字典表中的许多字段    /*+PARRALLEL(10)*/    insert/*+append nologging*/ into    TO_DATE(to_char(date'2014-12-31','yyyy/mm/dd')||' 23:59:59','YYYY/MM/DD HH24:MI:SS')    修改表：更改表名：alter table old_table_name rename to new_table_name;添加新字段：alter table 表名 add(字段名 字段类型 默认值 是否为空);alter table user add (age number(6));alter table user add (course varchar2(30) default '空' not null);修改字段的数据类型：alter table 表名 modify (字段名 字段类型 默认值 是否为空);alter table user modify (age number(8));修改字段名：alter table 表名 rename  column  列名 to 新列名;alter table user rename column course to newcourse;删除字段：alter table 表名 drop column 字段名；alter table user drop column course;删除表格：drop table table_name; -- 这个是将删除的表格放进回收器，如果还要继续清除，则如下写drop table table_name purge;  -- purge 就是在回收站也清除这个数据删除存储过程：drop procedure 存储过程名字drop procedure fhbi.sp_case_stat_sum_his_0728 purge;删除视图：drop view 视图名字drop view blfact.bl_ln_case_mortgage_info;purge 的介绍：    清空回收站：purge recyclebin;comment 字段comment on column scott.emp.emp_no is '员工编号';--查看数据库表和字段描述select t1.owner,t1.table_name,t1.comments,t2.column_name,t2.comments  from all_tab_comments t1  left join all_col_comments t2    on t1.table_name = t2.table_name   and t1.owner = t2.owner where 1 = 1   --and t1.owner like 'ods%'   --and t2.comments like '%土地%'   and t2.column_name = 'COMP_TOTAL_TNR' order by t1.table_name;-- 查看存储过程的文字select *  from user_source a where a.text like '%21%' ;--  ORA-14047  当你执行alter table hr.test_id 的时候,你已经告诉oracle你要修改哪个用户下的哪个表了,  所以在rename to 的时候就不需要在指定用户名称了.如果在写用户名的话,oracle也许会认为你是要把hr下的test_id 表改名存储到其他用户下面去,oracle不允许这么做!--  oracle查询停了，就会做自动的回滚--  oracle 报错：12801，该怎么解决？--查看表格占用的空间的大小-- 帆软的参数，空值选全部         WHERE 1=1           ${IF(LEN(CUST_NAME)=0,""," AND D.CUST_NAME LIKE '%"+CUST_NAME+"%'")} -- SELECT distinct to_char(to_date(to_char(period_id),'yyyy/mm/dd'),'yyyy-mm-dd') as 数据日期  FROM dmfact.dm_rpt_pl_daily_ovdue_ft T WHERE EXISTS (               SELECT ORG_AUTH_ID                 FROM FINEBI.FH_USER_ORG_CONFIG A                WHERE USER_ID ='${fr_userid}'   --- 在帆软的数据集中，可以放入帆软前端的账户的用户名                  AND APPLY_REPORT = 'RPT070'                  AND T.ORG_ID = DECODE(A.ORG_AUTH_ID,999,T.ORG_ID,A.ORG_AUTH_ID)                ) ORDER BY 数据日期 DESC  -- ORACLE 的解释器-- 分析表格，   https://blog.csdn.net/dataminer_2007/article/details/40795377?depth_1-utm_source=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-4&utm_source=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-4  --查看临时表空间    select tablespace_name          ,file_name          ,bytes/1024/1024/1024 as file_size -- 大小          ,autoextensible          ,USER_BYTES/1024/1024/1024 as USER_size -- 已用     from dba_temp_files  --释放临时表空间  解决办法：杀死一些正在进行的占用临时表空间较大的进程，      -- 查找该进程的相对应的编号：         select b.sid,b.serial#,b.osuser,b.username,b.machine,b.terminal,b.program,a.owner,a.object,b.status,b.sql_exec_start,      'alter system kill session '''||b.sid ||','||b.serial#||''';' as kill_command          from gv$access a,gv$session b         where 1 = 1           and a.sid=b.sid           and b.status='ACTIVE'           and a.type in('PROCEDURE','PACKAGE')           and a.owner not in('SYS','MDSYS','SYSTEM','XDB')       ;       杀死进程的语句： alter system kill session '2691,93'       -- 释放临时表空间    alter tablespace temp shrink space;  rollup 函数  cube 函数  grouping 函数     解释:grouping函数用来区分NULL值，这里NULL值有2种情况，一是原本表中的数据就为NULL，二是由 rollup、cube、grouping sets 生成的NULL值。          当为第一种情况中的空值时，grouping(NULL)返回0；当为第二种情况中的空值时，grouping(NULL)返回1排序（1）—rank()      :数据有重复，序号重复并别相同，后续序号中断。排序（2）-dense_rank():数据有重复，序号重复并列相同，后续序号不中断排序（2）-row_number():数据有重复，序号不重复，后续序号始终递增--分析函数汇总：https://blog.csdn.net/cc_0101/article/details/80884076?depth_1-utm_source=distribute.pc_relevant.none-task-blog-BlogCommendFromBaidu-2&utm_source=distribute.pc_relevant.none-task-blog-BlogCommendFromBaidu-2  sqlldr userid=fhtmp/fhtmp_123@BIAUDIT   control=load.ctl   log=load.log-------------------------------------------帆软的参数，空值选全部https://help.finereport.com/doc-view-2394.html${if(len(area) == 0,"","and 货主地区 = '" + area + "'")} where t1.period_id >= to_number(replace('${period_id}','-',''))   and t1.period_id <= to_number(replace('${period_id}','-',''))   --档案号   ${IF(LEN(CASE_NUMBER)=0,""," AND A.CASE_NUMBER in ('"+REPLACE(CASE_NUMBER,",","','")+"')")}   --截止日期   ${IF(LEN(END_DATE)=0,""," and A.DISBURSE_DATE <= TO_DATE(replace('"+END_DATE+"','-','/'),'YYYY/MM/DD')" )}   --合伙人协议编号   ${IF(LEN(PART_CODE)=0,""," AND TRIM(G.PARTNERAGREEMENTNO) in ('"+REPLACE(PART_CODE,",","','")+"')")}   --合伙人名称   ${IF(LEN(PART_NAME)=0,""," AND I.ID IN ("+PART_NAME+")")}   --机构   AND B.ORG_ID IN (${ORG_ID})   --二级业务类型   ${IF(LEN(FP_ID)=0,""," AND B.FINANCIAL_PRODUCT_ID in ("+FP_ID+")")}   --实际垫资日期段   ${IF(LEN(DISBURSE_START_DATE)=0,""," AND A.DISBURSE_DATE >= TO_DATE(replace('"+DISBURSE_START_DATE+"','-','/'),'YYYY/MM/DD')")}   ${IF(LEN(DISBURSE_END_DATE)=0,""," AND A.DISBURSE_DATE <= TO_DATE(replace('"+DISBURSE_END_DATE+"','-','/'),'YYYY/MM/DD')")}   --本金截止状态   ${IF(LEN(PR_STATUS)=0,"",SWITCH(PR_STATUS,"未完全收回","and G.PayforMoneyOut > NVL(C.AGG_REPAY_PR_AMT,0)"                                            ,"已全部收回","and G.PAYFORMONEYOUT <= NVL(C.AGG_REPAY_PR_AMT,0)"                                            ,"未有回款"  ,"and 0 = NVL(C.AGG_REPAY_PR_AMT,0)"                                  ))}--------------------------------------------  11g的orcl；下载链接:https://pan.baidu.com/s/1o4aZYVEkwfehIA5RFF-KJg 密码:hef9  +REPLACE(CASE_NUMBER,",","','")+  replace(trim(replace(t1.suppl_actual_date,chr(9),'')),'/','-') -- 替换换行符，再替换'/'成'-'  select distinct        a2.org_short_name_chs  as 机构  from odsjoin.jn_ln_case_info a1  left join bldim.bl_org_dim a2    on a1.org_id = a2.org_id_rcs where 1 = 1   -- 机构   -- and a2.org_id in (${org_id}) -- 2020-05-31 注掉   ${if(len(org_id)=0,""," and a2.org_id in ("'+org_id+'")")} -- 2020-05-31 廖翠玉要求，调整机构参数，允许为空，空选时为全部--   and a1.order_taking_date >= date'2020-04-19'               -- 2020-06-03 廖翠玉反馈只要新系统放款的案件   ${if(len(org_id) == 0,""," and a2.org_id in ("+org_id+")")}   ${if(len(org_id) == 0,"","and a2.org_id = '" + org_id + "'")}   ${if(len(org_id) == 0,"","and a2.org_id  in ('" + (org_id) + "')")}   ${if(len(参数名) == 0,"","and 传参字段  in ('" + (参数名) + "')")}   ${if(len(area) == 0,"","and 货主地区 = '" + area + "'")}   -- treelayer函数 定义：返回一个树对象 TreeObject 第 n 层的值，一般为树数据集，或下拉树、视图树等树对象，并且可以设置返回值类型及分隔符。   -- https://help.finereport.com/doc-view-838.html   ${if(len(aa)==0,""," and 货主城市 in ("+"'"+treelayer(aa,true,"\',\'")+"'"+")")}   ${if(len(aa)==0,""," and 货主城市 in ("+"'"+replace(aa,',',"','")+"'"+")")}   +treelayer(aa,true,"\',\'")+   另：下拉树属性中不勾选结果返回完整层次路径，只返回叶子节点，   这个时候过滤中不需要用到 treelayer 这个函数 ，可以把("+"'"+treelayer(aa,true,"\',\'")+"'"+")修改为("+"'"+replace(aa,',',"','")+"'"+")。,  ','3,77,78,79,80,81${if(len(org_id) = 0,""," and a2.org_id in ("+"'"+replace(org_id,',',',')+"'"+")"              123如何把字符串里面的半角分号"," ，替换成 "','"，例如这个字符串  中,国,人   ，replace('中,国,人',',','','')select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;  --显示当前时间select trunc(sysdate,'year') from dual; --截取到年（本年的第一天）-- PL/SQL Developer 中的F5 快捷键，可以打开已经存在的查询语句的执行计划-- 时间相减，得出的结果用“天时分秒”展示，目前找不到可用的函数，只能用拼接的方式达成select    (extract(day    from (to_date('2012-03-30 00:00:00','yyyy-mm-dd hh24:mi:ss') -to_timestamp('2012-03-29 00:00:00','yyyy-mm-dd hh24:mi:ss')) day to second )   || '天'   ||extract(hour   from (to_date('2012-03-30 00:00:00','yyyy-mm-dd hh24:mi:ss') -     to_date('2012-03-29 00:00:00','yyyy-mm-dd hh24:mi:ss')) day to second )   || '时'   ||extract(minute from (to_date('2012-03-30 23:23:15','yyyy-mm-dd hh24:mi:ss') -     to_date('2012-03-29 00:23:00','yyyy-mm-dd hh24:mi:ss')) day to second )   || '分'   ||extract(second from (to_date('2012-03-30 23:23:15','yyyy-mm-dd hh24:mi:ss') -     to_date('2012-03-29 00:23:00','yyyy-mm-dd hh24:mi:ss')) day to second )   || '秒')  as timespanfrom dualselect  (extract(day    from (to_date('2012-03-30 00:00:00','yyyy-mm-dd hh24:mi:ss') - to_date('2012-03-29 00:00:00','yyyy-mm-dd hh24:mi:ss')) day to second )|| '天'||   extract(hour   from (to_date('2012-03-30 00:00:00','yyyy-mm-dd hh24:mi:ss') - to_date('2012-03-29 00:00:00','yyyy-mm-dd hh24:mi:ss')) day to second )|| '时'||   extract(minute from (to_date('2012-03-30 23:23:15','yyyy-mm-dd hh24:mi:ss') - to_date('2012-03-29 00:23:00','yyyy-mm-dd hh24:mi:ss')) day to second )|| '分'||   extract(second from (to_date('2012-03-30 23:23:15','yyyy-mm-dd hh24:mi:ss') - to_date('2012-03-29 00:23:00','yyyy-mm-dd hh24:mi:ss')) day to second )|| '秒'   )  as timespanfrom dualMysql 时间、时间戳、时间字符串相互转换**将时间字符串转为时间**    SELECT STR_TO_DATE('2018-09-21','%Y-%m-%d %H:%i:%s');    结果为：2018-09-21 00:00:00**将时间转为时间字符串**    SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s');    结果为：2018-09-21 14:23:57**将时间转为时间戳**    SELECT UNIX_TIMESTAMP(NOW());    结果为：1537511187**将时间字符串转为时间戳**    SELECT UNIX_TIMESTAMP('2018-09-21');    结果为：1537459200注意：这里的2018-09-21是等于2018-09-21 00:00:00的        相当于对2018-09-21 00:00:00进行时间戳的转换**将时间戳转为时间**    SELECT FROM_UNIXTIME(1537511735);    结果为：2018-09-21 14:35:35**将时间戳转为时间字符串**    SELECT FROM_UNIXTIME(1537511735,'%Y-%m-%d');    结果为：2018-09-21https://help.finereport.com/finereport8.0/doc-view-876.html   -- 常见日期处理函数在输出时,控制输出结果的缓冲大小由DBMS_OUTPUT.ENABLE控制,buffer size默认为20000,每行最大的限制是32k,后面的一个例子是用来说明存储过程是在缓存了所有数据后才将结果返回。所以当我们使用游标进行输出时，如果结果很多，将会超过这个值报ORA-20000, ORU-10027: Buffer overflow, limit of <buf_limit> bytes这个错误解决方法：在存储过程begin后面加 : DBMS_OUTPUT.ENABLE (buffer_size=>null) 表示没有限制; end换行的符号：\r\n  -- 必须是小写，然后选择“扩展”-- 表分析IP地址  ： https://www.cnblogs.com/wangrui1587165/p/9835592.html-- 分析语句35 用的是这个：analyze table odsjoin.jn_ln_con_adv_dtl compute statistics for table for all indexes for all columns;-- NOTEPAD 添加关键字菜单栏的设置 ==> 语言格式设置 ==> 选择对应的语言 ==> 在“样式”选择 KEYWORD ==> 在自定义关键字的框里面，添加关键字  即可-- ORACLE 中，存储过程放在包里，和不放在包里的优缺点  1. 放在包里不好维护，如果包里面的一个存储过程出错，整个包都会死掉，  2. 如果不放在包里，只要做好主题域、层次上的区分，一样可以达成“包” 的效果，也更好的维护。-- 正则表达式https://blog.csdn.net/weixin_41728561/article/details/80385326-- 重点-- 以下语句，可以拿到当前月的每一天的日期select trunc(sysdate,'mm') + rownum - 1 as cnt      --,rownum as cnt1      --,to_number (to_char (last_day (sysdate), 'dd')) as cnt2  from dual  connect by rownum <= to_number (to_char (last_day (sysdate), 'dd'))connect by ：https://blog.csdn.net/wang_yunj/article/details/51040029-- 同时生成两个临时表，或者更过with rpt_tmp as (    select t1.*      from dmfact.dm_pns_steg_pane_ovdue_sum t1 -- 战略合伙人逾期统计表    ),rpt_tmp_2 as (    select t1.*      from dmfact.dm_pns_steg_pane_ovdue_sum t1 -- 战略合伙人逾期统计表    ),rpt_tmp_3 as (    select t1.*      from dmfact.dm_pns_steg_pane_ovdue_sum t1 -- 战略合伙人逾期统计表    ),rpt_tmp_4 as (    select t1.*      from dmfact.dm_pns_steg_pane_ovdue_sum t1 -- 战略合伙人逾期统计表    )    select      from-- 帆软的查询限制var start = this.options.form.getWidgetByName("period_start").getValue(); -- 控件赋值var end = this.options.form.getWidgetByName("period_end").getValue();     -- 控件赋值if( start > end){   //判断报告开始日期是否大于报告结束日期  alert("错误,报告开始时间不能大于报告结束时间");   //报告开始时间不能大于报告结束时间  return false;}var startdate = new Date(start);   //将报告开始日期转化为Date型var enddate = new Date(end);    //将报告结束日期转化成Date型var subdate = (enddate-startdate)/ (1000 *60 *60 *24);   //将两个日期相减得出的毫秒数转化为天数if(subdate>365){    //判断结束日期是否超过开始日期后365天alert("错误,报告日期区间必须在报告开始日期之后的一年之内");    //报告日期区间必须在报告开始日期之后的一年之内return false;}-- 最近连续登陆天数计算实现方式select name,count(1) as `用户最近连续登陆天数`from (    select name        ,date        ,first_value(date) over (partition by name order by date desc) as last_login_date       -- 用户最近登陆日期        ,DATE_ADD(            first_value(date) over (partition by name order by date desc)                                           -- 用户最近登陆日期            ,INTERVAL -row_number() over (partition by name order by date desc)+1  day            ) date_line                                                                 -- 以用户最近登陆日期做降序排序，算出连续间隔1、2、3...n天的日期    from aaa) twhere date = date_line  -- 限制 date = date_line 得到用户最近连续活跃日期group by name;-- 下面这个实现方式稍微复杂一些，但不仅是能计算“最近的连续登陆天数”，还能算“任意时间段的连续登陆天数”，扩展性更强with t as(    select *        ,min(last_date) over (partition by name order by date desc) as date_set    from (        select name            ,date            ,if(DATEDIFF(lag(date) over (partition by name order by date desc),date)=1,null,date) as last_date        from aaa    ) t)select name,date_set,count(*) as `连续登陆天数`from t-- where date_group='2020-10-19'    最近一天登陆用户的连续登陆天数group by name,date_set;2021-01-11 记录需要学习的知识1. 拉链表2. 机构树3. 正则表达式的使用 start with    connect by4. 宽表和窄表5. CROSS JOIN  笛卡尔积 关联6. 帆软的控件  字符串 和 数字 输入，还有机构树的输入7. 任务计划程序8. 安装 帆软的模板设计器-- 2021-02-241. 如何深入浅出理解数据仓库建模？ http://baijiahao.baidu.com/s?id=1649349568939493055&wfr=spider&for=pc-- 2021-12-07type  is record-- 2022-07-12analyze-- 2022-09-19B树 和 B+树 的区别-- 2022-10-08设计字典表字段如下：    序号    主题域    --     字段名    -- field_code    字段描述  -- field_name    维度编码  -- item_code    维度描述  -- item_name    是否有效  --     备用字段1    备用字段2    备用字段3    备用字段4    备用字段5    创建时间    创建人    索引1：主题域    索引2：字段名