Oracle数据库开发PLSQL基础

PLSQL程序设计

概述

Oracle SQL Develop的安装使用

PL/SQL的作用和第一个PL/SQL程序

• 作用：操作Oracle 数据库效率最高，为后续触发器，存储过程，存储函数做铺垫

• PL/SQL(Procedure Language/SQL),PLSQL是Oracle对sql语言的过程化扩展

  指在SQL命令语言中增加了过程处理语句(如分支，循环)，使得SQL语言具有过程处理能力
  SQL语言的数据操纵能力+过程语言的数据处理能力==》PL/SQL的提出
  Oracle:PL/SQL
  DB2:SQL/PL
  SQL Server:Transac-SQL(T-SQL)
  

• 第一个PL/SQL语句语法结构

  第一个PL/SQL程序 Hello World
  declare
  begin
  dbms_output.put_line('Hello World');
  end;
  /
  --打开输出开关
  set serveroutput on
  /
  

PL/SQL基础语法

PL/SQL基本变量类型

• PL/SQL的程序结构

  declare
  --说明部分(变量说明，光标的申明，例外说明)
  begin
  语句序列(DML语句)
  exception
  例外处理语句
  end;
  /
  

• 定义基本变量

  类型：char,carchar2,date,number,boolean,long
  举例：
  var1 char(15);
  married boolean:=true;
  psal number(7,2);
  

PL/SQL引用型变量和记录型变量

• 引用型变量

  my_name emp.ename%type;
  示例：
  --引用型变量
  set serveroutput on
  declare
  pname emp.ename%type;
  psal emp.sal%type;
  begin
  --得到7839的姓名和薪水
  select ename,sal into pname,psal from emp where empno=7839;
  --打印姓名和薪水
  dbms_output.put_line(pname||'薪水是'||psal);
  end;
  /
  

• 记录型变量

  emp_rec emp%rowtype;
  记录型变量分量的引用：
  emp_rec.ename:='ADAMS';
  示例：
  --使用记录型变量，查询并打印7839的姓名和薪水
  set serveroutput on
  declare
  --定义记录型变量：注意代表一行
  emp_rec emp%rowtype;
  begin
  --得到7839这一行的信息
  select * into emp_rec from emp where empno=7839;
  --打印薪水和姓名这两个字段
  dbms_output.put_line(emp_rec.ename||'薪水是'||emp_rec.sal);
  end;
  /
  

PL/SQL中if语句的使用

• 语法结构

  格式一：
  IF 条件 THEN 语句1;
  语句2;
  END IF;
  格式二：
  IF 条件 THEN 语句序列1;
  ELSE 语句序列2;
  END IF;
  格式三:
  IF 条件 THEN 语句;
  ELSIF 语句 THEN 语句;
  ELSE 语句；
  END IF;
  

• 示例

  /*
  判断用户从键盘输入的数字
  1，如何使用if语句
  2.接受一个键盘输入的(字符串)
  */
  set serveroutput on
  --接受一个键盘输入
  --num:地址值，含义是：在该地址上保存了输入的值
  accept num prompt'请输入一个数字: '
  declare
  --定义变量保存用户从键盘输入的数字
  pnum number:=#
  begin
  --执行if进行语句判断
  if pnum=0 then dbms_output.put_line('您输入的是0');
  elsif pnum=1 then dbms_output.put_line('您输入的是1');
  elsif pnum=2 then dbms_output.put_line('您输入的是2');
  else dbms_output.put_line('其他数字');
  end if;
  end;
  /
  

PL/SQL循环语句的使用

• 语法结构

  WHILE循环：
  WHILE 条件 LOOP
  ...
  END LOOP;
  LOOP循环：
  LOOP
  EXIT[when 条件];
  ...
  End loop;
  FOR循环:
  FOR I IN 1..3 LOOP
  语句序列;
  END LOOP;
  

• 示例：

  WHILE 循环的使用:
  --使用while循环打印数字的1-10
  set serveroutput on
  declare
  --定义循环变量
  pnum number:=1;
  begin
  while pnum<=10 loop
  --循环体
  dbms_output.put_line(pnum);
  --使该变量+1
  pnum:=pnum+1;
  end loop;
  end;
  /
  Loop循环的使用
  --使用Loop循环打印数字的1-10
  set serveroutput on
  declare 
  --定义循环变量
  pnum number:=1;
  begin
  loop
  --退出循环的条件：循环变量》10
  exit when pnum>10;
  --打印该变量的的值
  dbms_output.put_line(pnum);
  --循环变量自增1
  pnum:=pnum+1;
  end loop;
  end;
  /
  FOR循环的使用：
  --使用for循环打印数字的1-10
  set serveroutput on
  declare
  pnum number:=1;
  begin
  for pnum in 1..10 loop
  dbms_output.put_line(pnum);
  end loop;
  end;
  /
  

光标

• 概念：就是一个结果集(Result Set)

• 光标的属性

  1.光标的属性
  %found
  %notfound
  %isopen 判断光标是否打开
  %rowcount 影响的行数
  2.光标数的限制
  默认情况下，oracle数据库只允许在同一个会话中，打开300个光标
  show parameter cursor;
  修改光标数的限制：
  alter system set open_cursors=400 scope=both;
  scope的取值：both,memory,spfile(数据库需要重启)
  

• 带参数的光标

  --查询某个部门员工的姓名
  set serveroutput on
  declare
  cursor cemp(dno number) is select ename from emp where deptno=dno;
  pename emp.ename%type;
  begin
  open cemp(10);
  loop
  fetch cemp into pename;
  exit when cemp%notfound;
  dbms_output.put_line(pename);
  end loop;
  close cemp;
  end;
  /--
  

• 语法结构：

  CURSOR 光标名[(参数名 数据类型[,参数名，数据类型]..)]
  IS SELECT 语句;
  

• 案例一

  示例:
  --查询并打印员工的姓名和薪水
  /*
  1.光标的属性
  %found %notfound
  
  */
  set serveroutput on
  declare
      cursor cemp is select ename,sal from emp;
      --定义光标
      pename emp.ename%type;
      psal emp.sal%type;
  begin
  open cemp;
  --开启光标
  loop
  fetch cemp into pename,psal;
  --取值
  exit when cemp%notfound;
  dbms_output.put_line(pename||'的薪水是'||psal);
  end loop;
  close cemp;
  --关闭光标
  end;
  /
  

• 案例二：

  --给员工涨工资，总裁1000，经理800，其他400
  set serveroutput on
  declare
  cursor cemp is select empno,empjob from emp;
  pempno emp.empno%type;
  pjob emp.empjob%type;
  begin
  rollback;
  open cemp;
  loop
  fetch cemp into pempno,pjob;
  exit when cemp%notfound;
  if pjob ='PRESIDENT' then update emp set sal=sal+1000 where empno=pempno;
  elsif pjob='MANAGER' then update emp set sal=sal+800 where empno=pempno;
  else update emp set sal=sal+400 where empno=pempno;
  end if;
  end loop;
  close cemp;
  commit;
  dbms_output.put_line('涨工资完成');
  end;
  /
  

例外

• 例外是程序设计语言提供的一种功能，用来增强程序的健壮性和容错性

• 例外的分类（x系统例外，自定义例外）

  1. No_data_found(没有找到数据)
  2. Too_many_rows(select…into语句匹配多个行)
  3. Zero_Divide
  4. Value_error
  5. Timeout_on_resource(在等待资源时发生超时)

• 示例：

  --系统例外：no_data_found
  set serveroutput on
  declare
  pname emp.ename%type;
  begin
  select ename into pname from emp where empno=1234;
  exception
  when no_data_found then dbms_output.put_line('没有找到该员工');
  when others then dbms_output.put_line('其他例外');
  end;
  /
  --系统例外：Too_many_rows
  

• 自定义例外

  定义变量，类型是exception

  使用raise抛出自定义例外

  --自定义例外，查询50号部门的员工信息
  set serveroutput on
  declare
  cursor cemp is select ename from emp where deptno=50;
  pname emp.ename%type;
  --自定义例外
  no_emp_found exception;
  begin
  open cemp;
  fetch cemp into pname;
  if cemp%notfound then
  raise no_emp_found;
  end if;
  close cemp;
  exception
  when no_emp_found  then dbms_output.put_line('没有找到');
  when others then dbms_output.put_line('其他例外');
  end;
  /
  
  

案例集锦

瀑布模型

1.需求分析
2.设计(概要设计，详细设计)
3.编码(coding)
4.测试(testing)
5.上线

示例：

1. 统计每年入职的员工人数

   --统计每年入职的员工人数
   /*
   SQL语句
   select to_char(hiredate,'yyyy') from emp;
   cursor
   */
   set serveroutput on
   declare
   cursor cemp is select to_char(hiredate,'yyyy') from emp;
   phiredate varchar2(4);
   count80 number:=0;
   count81 number:=0;
   count82 number:=0;
   count87 number:=0;
   begin
   open cemp;
   loop
   fetch cemp into phiredate;
   exit when cemp%notfound;
   if phiredate='1980' then count80:=count80+1;
   elsif phiredate='1981' then count81:=count81+1;
   elsif phiredate='1982' then count82:=count82+1;
   else count87:=count87+1;
   end if;
   end loop;
   close cemp;
   dbms_output.put_line('Total:'||(count81+count82+count80+count87));
   dbms_output.put_line('80年的人数:'||count80);
   dbms_output.put_line('81年的人数:'||count81);
   dbms_output.put_line('82年的人数:'||count82);
   dbms_output.put_line('87年的人数:'||count87);
   end;
   /
   

2. 员工涨工资问题

   /*为员工涨工资，从最工资涨起每人涨10%，但工资总额不能超过5万元，
   请计算涨工资的人数和涨工资后的工资总额，并输出涨工资的人数及工资总额
   SQL语句
   select empno,sal from emp order by sal;
   -->光标 -->循环  ---》推出循环  --》推出循环 1.工资总额》5万  2.not%notfound
   变量：1，初始值 2.如何得到
   countEmp mumber:=0;
   salTotal number;
   1.select sum(sal) into salTotal from emp;
   2.涨侯的工资总额=涨前的工资总额+sal*0.1;
   */
   set serveroutput on
   declare cursor cemp is select empno,sal from emp order by sal;
   pempno emp.empno%type;
   psal emp.sal%type;
   --涨工资的人数：
   countEmp number:=0;
   --涨后的工资总额
   salTotal number;
   begin
   rollback;
   --得到工资总额的初始值
   select sum(sal) into salTotal from emp;
   open cemp;
   loop
   exit when salTotal>50000;
   fetch cemp into pempno,psal;
   exit when cemp%notfound;
   --涨工资
   if saltotal<50000 
   then
   update emp set sal=sal*1.1 where empno=pempno;
   end if;
   countEMP:=countEmp+1;
   --涨后的工资总额
   salTotal:=salTotal+psal*0.1;
   
   end loop;
   close cemp;
   commit;
   dbms_output.put_line('人数'||countEmp||'涨后的工资总额:'||salTotal);
   end;
   /
   

3. 设计两张表的员工涨工资问题

4. 成绩统计

Oracle数据库开发之函数

函数的作用

• 方便数据的统计
• 处理查询结果

函数的分类

• 内置函数

  1. 数值函数

     四舍五入函数：
     ROUND(n，m),省略m:0,m>0：小数点后m位，m<0:小数点前几位
     示例：
     select round(23.4),round(23.45,1),round(23.45,-1) from dual;
     结果：23.4，23.4，20
     注意:dual是一个虚拟表，用来构成select的语法规则，oracle保证dual里面永远只有一条记录，可以用它来做很多事情。
     1、查看当前用户，可以执行下面语句 select user from dual;
     2、用来调用系统函数
     取整函数：
     CEIL(n):取最大值
     FLOOR(n):取最小值
     示例：
     select ceil(23.45),floor(23.45) from dual;
     结果：24，23
     常用计算：
     ABS(n):取绝对值
     示例：
     select abs(-100),abs(100),abs(0) from dual;
     MOD(m,n):取余函数
     示例：
     select mod(5,2),mod(5,null) from dual;
     结果：1，null
     POWER(m,n):返回m的n次幂
     示例：
     select power(2,10) from dual;
     结果：1024
     SQRT(n):开方
     select SQRT(64) from dual;
     结果：8
     三角函数：
     SIN(n),ASIN(n)
     COS(n),ACOS(n)
     TAN(n),ATAN(n)
     

  2. 字符函数(应用函数)

     大小写转换函数
     UPPER(char)
     LOWER(char)
     INITCAP(char):首字母大写
     示例：
     select upper('hello'),lower('WORLD'),INITCAP('justin') from dual;
     获取子符串函数：
     SUBSTR(char,[m,n]):m为负数，表示从字符串的尾部开始截取
     示例：
     select substr('abcde',0),substr('abcde',3,3),substr('abcde',3),substr('abcde',-3,3) from dual;
     结果：abcde,cde,cde,cde
     获取字符串长度函数:
     示例：
     LENGTH(char);
     select length('abcd ') from dual;
     结果;4
     字符串连接函数:
     CONCAT(char1,char2)
     select concat('a','bcde') from dual;
     select 'a'||'bcde' from dual;
     与||操作符的作用一样
     示例：
     去除字串函数：
     TRIM(c2 from c1):从c1里面去除一个字符c2
     示例：
     select trim('a' from 'abcde') from dual;
     结果;bcde
     LTRIM(c1[,c2]):从c1里面去除一个字符c2.从头部开始去除
     示例：
     select ltrim('abababab','a') from dual;
     结果：bababab
     RTRIM(c1[,c2]):从c1里面去除一个字符c2.从尾部开始去除
     示例：
     select RTRIM('ababababaaa','a') from dual;
     结果：abababab
     TRIM(c1):去除空格
     select trim(' abcde   ') from dual;
     结果：abcd
     替换函数：
     REPLACE(char,s_string,[,r_string]),省略r_string用空格替换
     示例:
     select replace('abcdeaa','a','A') from dual;
     结果;AbcdeAA
     

  3. 日期函数

     • 系统时间函数

       SYSDATE：默认格式：DD-MON-RR
       示例：
       select sysdate from dual;
       

     • 日期操作

       ADD_MONTHS(date,i);
       示例：
       select add_months(sysdate,3),add_months(sysdate,-3) from dual;
       NEXT_DAY(date,char):下一个星期的日期
       示例;
       select next_day(sysdate,'星期一') from dual;
       LAST_DAY(date);
       select last_day(sysdate) from dual;
       MONTHS_BETWEEN(date1,date2):表示两个日期之间相隔的月份
       示例;
       select months_between('12-11月-19'，sysdate) from dual;
       EXTRACT(date from datetime):获取datetime中的年分或者月份，日期
       select extract(year from sysdate) from dual;
       

  4. 转换函数

     • 日期转换成字符的函数

       TO_CHAR(date[,fmt[,params]]):date转换的日期，fmt转换的格式，parms:日期语言
       示例：
       select to_char(sysdate,'YYYY-MM-DD') from dual;
       使用字符串截取月份：
       select substr(to_char(sysdate,'YYYY-MM-DD'),0,4) from dual;
       

     • 字符转换成日期的函数、

       TO_DATE(char[,fmt[,params]])
       示例：
       select to_date('2019-10-27','YYYY-MM-DD') from dual;
       结果：27-10月-19，注意to_date()按照系统默认格式显示，如果要显示指定的日期格式，一般将日期准换成字符使用to_char()函数
       

     • 数字转换成字符的函数

       TO_CHAR(number[,fmt])
       9:显示数字并忽略前面的0
       0：显示数字，位数不足，用0补齐
       .或D：显示小数点
       ,或G：显示千位符
       $:美元符号
       S：加正负号(前后都可以)
       示例：
       select to_char(12345.678,'$99,999.999') from dual;
       结果：$12,345.678
       

     • 字符转换成数字的函数

       TO_NUMBER(char[,fmt])：fmt可以省略，去除格式
       示例：
       select to_number('$1,000','$9999') from dual;
       结果：1000
       

• rownumber伪列

  • 因为Oracle中没有像mysql中一样可以使用limit函数来限制输出结果集的条数，如果我们要在oracle中限制输出的结果集那么我们就需要使用到rownumber

  • 使用时的注意点

    rownumber是一个伪列，当你每次向表中插入一条数据是会自动生成一个rowbnumber,
    1.rownumber对于等于某值的查询条件，注意无法查询到rownumber=n(n>1)的记录
    select rownumber,id,name from student where rownumber=1--有记录
    select rownumber,id,name from student where rownumber=2--没有记录
    2.rownumber对于大于某值的查询，这里要使用子查询，注意在子查询中rownumber一定要重新命名，否则无效
    select * from(select rownumber rn,id,name from student) where rn>2--有记录
    select * from(select rownumber,id,name from student) where rownumber>2--无记录
    3.rownumber 对于小于某值的查询，可以使用rownumber<n进行查询
    select rownumber,id,name from student where rownumber<3
    4.使用rownumber进行排序，有这么一种形况按照姓名进行排序，因为rownubmer的顺序是按照数据插入的顺序来编号的，所以我们可以使用一个子查询，将排序好的结果作为一个结果集，
    select rownumber,id,name from(select * from student order by name);
    

• 自定函数

• 分析函数

  分析函数用于计算基于组的某种联合值，它和聚合函数的不同是：对于每个组返回多行，而聚合函数对于每个组只返回一行

  开窗函数也属于分析函数，开窗函数指定了分析函数的窗口大小，这个数据窗口的大小可能随着行的变化而变化。oracle开窗函数，用的比较多的是over(),使用话通常是和order,partition by,row_number(),rank(),dense_rank()几个函数一起使用。

  oracle开窗函数的实现准备：
  --创建数据表
  create table t_score(
  stuId varchar2(20),
  stuName varchar2(50),
  classId number,
  score float
  );
  --插入数据
  insert into t_score values('111','小王',1,92);
  insert into t_score values('123','小李',1,90);
  insert into t_score values('134','小钱',1,92);
  insert into t_score values('145','小顺',1,100);
  --over函数和row_number()一起使用
  

select * from（select stuId,stuName,classId,row_number() over(partition by classId order by score desc) rn from t_score） where rn=1;
–over函数和rank函数一起使用
select stuId,stuName,classId, rank() over(partition by classId order by score desc) rn
from t_score;
–over 函数和dense_rank()函数一起使用
select stuId,stuName,classId,score,dense_rank() over(partition by classId order by score desc) rn from t_score;

### 开窗函数和聚合函数的区别

- 开窗函数与聚合函数的计算方式一样，开窗函数也是对行集组进行计算，但是它可以每组返回多个值，不像聚合函数一样每组只返回一个值

- 语法：

over(partition by column order by),over()前面是一个函数，如果前面是聚合函数则，order by 不能一起使用
为了方便理解聚合函数和开窗函数（分析函数）举个例子：
–使用聚合函数：
select sum(sal),deptno from emp group by deptno;–结果返回三条值，总共三个分组
select deptno,ename,sum(sal) over(partition by deptno) as rn from emp–总共返回14个值，总共有14条记录

- 开窗函数的分类

1. 排名开窗函数（窗口函数over()指定一组行，开窗函数计算从窗口函数输出的结果集计算各行的值）
   - row_number() 
   - DESNSE_RANK()
   - RANK()
2. 聚合开窗函数（开窗函数不需要使用GROUP BY就可以对数据进行分组，不能使用order by）
   - sun() AVG(),MAX(),MIN(),COUNT()

### 在查询语句中使用函数

- 在查询中使用字符函数

在员工信息表中查出员工的生日
select substr(hiredate,8,2) from emp where empno=7369;
将部门号01全部替换成信息技术
select replace(daptno,‘01’,‘信息技术’) from users;

- 在查询中使用数值函数

将员工年龄与10取余数
select mod(age,10) from users;

- 在查询中使用日期函数

取得员工的入职年份
查询出五月份入职的员工信息
select extract(year from regdate) from users;
select * from users where extract(moth from regdate)=5;

## Oracle高级查询

### 分组查询

1. 分组函数的概念

 1. 分组函数作用域一组函数，并对一组数据返回一个值

2. 分组函数的使用

 - 常见的分组函数

   ```
   AVG
   SUM
   select avg(sal),sum(sal) from emp;
   MIN
   MAX
   COUNT
   select count(distinct deptno) from dept;
   WM_CONCAT：行转列
   set linesize 200
   col 部门中员工的姓名 for a60
   select deptno as 部门号,wm_concat(ename) as 部门中员工的姓名 from emp group by deptno;
   注意分组函数不一定非要和group by函数使用，但是使用了group by 子句则一定具有分组函数
   ```

 - 分组函数与空值

   ```
   示例：
   求员工的平均工资三种方法有何不同：
   select sum(sal)/count(*) from emp;
   select sum(sal)/count(sal) from emp;
   select avg(sal) from emp;
   统计员工的平均奖金
   select sum(comm)/count(*) from emp;
   select sum(comm)/count(comm) from emp;
   select avg(comm) from emp;
   通过以上例子我们可以看出分组函数会自动忽略空值，如果使用NVL函数，可以使分组函数无法忽略空值
   select count(*),count(nvl(comm,0)) from emp;
   ```

3. 使用group by子句进行数据分组

 - 可以使用group by子句将表中的数据分成若干组

   ```
   查询每个部门的平均工资，要求显示部门号,平均工资
   select deptno,avg(sal) from emp group by deptno;
   注意：在select列表中所有未包含在组函数中的列都应该包含在GROUP BY 子句中
   使用多个列进行排序：
   示例按照不同部门不同职位的总工资
   select deptno,empjob,sum(sal) from emp group by deptno,empjob order by deptno;
   ```

4. 使用HAVING子句过滤分组的结果集

 - 使用having子句进行分组数据进行过滤

   ```
   求平均工资大于200的部门：
   select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;
   ```

 - where和having的区别

   ```
   不能在WHERE子句中使用组函数
   可以在having子句中使用子函数
   在SQL优化上来讲尽量使用where子句,wher使得分组记录数大大降低，从而提高效率
   示例：
   查询10号部门的平均工资
   select avg(sal) from emp where deptno=10 group by deptno;
   select avg(sal) from emp group by deptno having deptno=10;
   ```

 - 在分组查询中使用order by子句

   ```
   求每个部门的平均工资，要求显示：部门号，部门的平均工资,并且按照工资升序排列
   可以按照：列，别名，别名,表达式,序号进行排序
   SQLPLUS使用小技巧：
   --a 在原先的SQL语句后面追加，注意空格两个
   --ed 修改原先的SQL语句
   --/执行上条SQL语句
   --host cls清屏
   select deptno,avg(sal) from emp group by deptno order by avg(sal);
   在分组函数中进行嵌套
   select max(avg(sal)) from emp group by deptno;
   ```

 - group by语句的增强

   ```
   语法：
   group by rollup(a,b)
   等价于：
   group by a,b
   +
   group by a
   +
   group by null
   select deptno,empjob,sum(sal) from emp group by rollup(deptno,empjob);
   使用group by实现报表功能：
   break on deptno skip 2
   相同的deptno显示一个，不同deptno跳2
   select deptno,empjob,sum(sal) from emp group by rollup(deptno,empjob);
   ```

 - SQL*Plus实现报表功能

   ```
   title col 15 '我的报表' col 35 sql.pno//col 15空15个列显示报表的名字，‘我的报表’ sql。pno报表页码
   col deptno heading 部门号
   col job heading 职位
   col sum(sal) heading 工资总额
   break on deptno skip 1
   将以上文件保存到一个sql文件中
   获取：get C:\Users\Adopat\Desktop\1.sql
   执行：@ C:\Users\Adopat\Desktop\1.sql
   select deptno,empjob,sum(sal) from emp group by rollup(deptno,empjob);
   ```

### 多表查询

- 多表查询

- 笛卡尔积

笛卡尔集是多表查询的基础(列数相加，行数相乘)

为了避免使用笛卡尔集，可以在WHERE加入有效的连接条件，连接条件至少n-1个

- 多表查询的四种连接操作
1. 等值连接

   ```
   示例：查询员工的信息，要求显示员工号，姓名，月薪，部门名称
   select e.empno,e.ename,e.sal,d.dname
   from emp e,dept d
   where e.deptno=d.deptno;
   ```

2. 不等值连接

   ```
   示例：查询员工信息，要求显示：员工号，姓名，月薪，薪水的级别
   select e.empno,e.ename,e.sal,s.grade
   from emp e,salgrade s
   where e.sal between s.losal and s.hisal;
   ```

3. 外连接

   ```
   示例;按部门统计员工的人数，要求显示：部门号，部门名称，人数
   select d.deptno 部门号，d.dname 部门名称，count(e.empno) 人数
   from emp e,dept d
   where e.deptno=d.deptno
   group by d.deptno,d.dname;
   在这里使用等值连接导致数据缺失，因为emp表不存在40号部门的员工，但是在部门表中存在40号部门
   外连接：
   核心：通过外连接，把对于连接条件不成立的记录，仍包含在最后的结果中，
   左外连接：当连接条件不成立的时候，等号左边的表仍被包含
   右外连接：当条件不成立的时候，等号右边的表仍然被包含
   注意右外连接的写法：(=左边叫右外连接，)
   select d.deptno 部门号，d.dname 部门名称,count(e.empno) 人数
   from emp e,dept d
   where e.deptno(+)=d.deptno
   group by d.deptno,d.dname;
   注意左外连接的写法(=右边叫左外连接)
   select d.deptno 部门号，d.dname 部门名称,count(e.empno) 人数
   from emp e,dept d
   where e.deptno=d.deptno(+)
   group by d.deptno,d.dname;
   ```

4. 自连接

   ```
   示例：查询员工的姓名和员工老板的姓名
   select e.ename 员工姓名，b.ename 老板姓名
   from emp e,emp b
   where e.mgr=b.mgr;
   自连接的核心是通过别名来实现
   ```

   自连接存在的问题：

   ```
   自连接查询必然存在笛卡尔集，自连接是先生成笛卡尔集，然后根据设定的条件进行筛选数据，所以不适合操作大数据
   解决办法：层次查询
   某些情况下可以替代自连接查询，本质上是一个单表查询
   层次查询的原理
   书的深度：level
   select level,empno,ename,sal,mgr
   from emp
   connect by prior empno=mgr
   start with mgr is null
   order by 1;
   ```

### 子查询

- 子查询的分类
1. 按功能进行分类;
   - 标量子查询
   - 列子子查询
   - 行子子查询
   - 表子查询
2. 按位置进行分类：
   - where子查询
   - from子查询

- 子查询的概述

查询工资比scott工资高的员工
select * from emp where sal>(select sal from emp where ename=‘JAMES’);
注意‘’代表原义输出
在linux shell编程中``代表命令，’'代表原义输出，""解析里面的命令

- 子查询注意的10个问题

1.子查询语法中的小括号
2.子查询的书写风格
3.可以使用子查询的位置：where,select,having,from
注意select子查询必须是单行子查询
4.不可以使用子查询的位置:group by
5.强调：from后面的子查询
from后面的子查询相当于一张表
6.主查询和子查询可以不是同一张表:只需要子查询的表的结果主查询可以使用就行
select * from emp where deptno=(select deptno from dept where dname=‘SALES’)
7.一般不在子查询中，使用排序，但在Top-N分析问题中，必须对子查询进行排序
rownum 行号 伪列
行号只能使用<,<=;不能使用>,>=
select rownum,empno,ename,sal
from(select * from emp order by sal desc)
where rownum<=3;
8.一般先执行子查询，在执行主查询，但相关子查询除外
相关子查询
把主查询的值作为参数传递给子查询
select empno,ename,sal,(select avg(sal) from emp where deptno=e.deptno) as avgsal
from emp e
where sal>(select avg(sal) from emp where deptno=e.deptno);
9.单行子查询只能使用单行操作符，多行子查询只能使用多行操作符
单行子查询：=，<,>=,>,<,<=,<>
多行子查询:IN,ANY,ALL
查询员工信息，要求职位与7566员工一样，薪水大于7782员工的信息
select * from emp
where empjob=(select empjob from emp where empno=7566)
and sal>(select sal from emp where empno=7782);
多行子查询：
示例：查询部门名称是SALES和ACOUNTING的员工信息
select * from emp
where deptno in(select deptno from dept where dname=‘SALES’ or dname=‘ACCOUNTING’);
多表查询的方法：
select *
from emp e,dept d
where e.deptno=d.deptno and (d.dname=‘SALES’ or d.dname=‘ACCOUNTING’);
查询大于30号部门任意一人的员工信息
select *
from emp
where sal>any(select sal from emp where deptno=30);
等价于：
select *
from emp
where sal >(select min(sal) from emp where deptno=30);
10.注意：子查询中是null值的问题、
单行子查询的空值问题：：
多行子查询：子查询中如果有null值，不能使用not in,如果要使用要添加非空
select *
from emp
where empno not in(select mgr from emp where mgr is not null);

- 子查询的使用

- 子查询的类型

### 综合示例

- 示例一：分页查询显示员工信息：要求显示员工号，姓名，月薪，每页显示四条记录，显示第二页的员工，按照月薪降序排列

rownumber只能使用<,<=,不能使用>,>=
select r,empno,ename,sal
from(select rownum r,empno,ename,sal
from(select rownum,empno,ename,sal from emp order by sal desc) e1
where rownum<=8) e2
where r>=5;

- 示例二:找到员工表中薪水大于本部门平均薪水的员工

方法一：select e.empno,e.ename,e.sal,d.avgsal
from emp e,(select deptno,avg(sal) avgsal from emp group by deptno) d
where e.deptno=d.deptno and e.sal>d.avgsal;
方法二：；
explain plan for
select empno,ename,sal,(select avg(sal) from emp where deptno=e.deptno) avgsal
from emp e
where sal>(select avg(sal) from emp where deptno=e.deptno);
–查看执行结果：
select * from table(dbms_xplan.display);

- 按部门统计员工人数，按照如下格式进行输出

方法一：
select count() Total
sum(decode(to_char(hiredate,‘YYYY’),‘1980’,1,0)) “1980”
sum(decode(to_char(hiredate,‘YYYY’),‘1981’,1,0)) “1981”
sum(decode(to_char(hiredate,‘YYYY’),‘1982’,1,0)) “1982”
sum(decode(to_char(hiredate,‘YYYY’),‘1987’,1,0)) “1987”
from emp;
方法二：
select
(select count() from emp) Total
(select count() from emp where to_char(hiredate,‘yyyy’)=‘1980’) “1980”
(select count() from emp where to_char(hiredate,‘yyyy’)=‘1981’) “1981”
(select count() from emp where to_char(hiredate,‘yyyy’)=‘1982’) “1982”
(select count() from emp where to_char(hiredate,‘yyyy’)=‘1987’) “1987”
from dual;

## Oracle存储过程和自定义函数

### oracle对象

- 表，视图，索引，序列，同义词，存储过程和存储函数

### 第一个存储过程

1. 存储过程

 - 存储在数据库中共所有用户程序调用的子程序叫做存储过程，存储函数

 - 存储函数和存储过程的共同点：完成特定功能的程序，区别是存储函数有返回值

 - 语法结构

   ```
   create or replace procedure 过程名(参数列表)
   as
   PLSQL子程序体
   第一个存储过程：打印Hello World
   create or replace procedure sayhelloworld
   as
   begin
   dbms_output.put_line('hello world');
   end;
   /
   调用存储过程的函数：
   方法一：
   1.exec sayhelloworld();
   方法二：
   begin
   sayhelloworld();
   sayhelloworld();
   end;
   /
   带参数列表的存储过程：
   --给指定的员工涨100块钱工资，并打印涨前和涨后的工资
   create or replace procedure raisesal(eno in number)
   as
   --定义一个变量来保存涨工资前的薪水
   psal emp.sal%type;
   begin
   select sal into psal from emp where empno=eno
   --为员工涨工资
   update emp set sal=sal+100 where empno=eno;
   dbms_output.put_line('涨前的薪水：'||pasl||'涨后的薪水：'||psal+100);
   end;
   /
   
   
   
   ```

2. 存储函数

 语法结构：

create or replace function
return 函数值类型
as
PLSQL子程序体;
示例：
查询员工的年收入
create or replace function queryempincome(eno in number)
return number
as
–定义变量保存员工的薪水和奖金
psal emp.sal%type;
pcomm emp.comm%type;
begin
–得到员工的薪水和奖金、
select sal,comm into psal,pcomm from emp where empno=eno;
–直接返回年收入
return psal*12+nvl(pcomm,0);
end;
/
注意在PLSQL中空值的处理，使用nvl()函数将空值进行处理

3. In和Out参数

一般来讲存储过程和存储函数的区别在于存储函数有返回值，但是过程和函数都可以通过out指定一个或多个输出函数，我们可以利用out参数，在过程和函数中实现多个返回值

是么时候使用存储过程是么时候使用存储函数的一般原则

- 如果只有一个返回值，就是用存储函数，否则就使用存储过程(包含没有返回值或者多个返回值的情况)

示例：

create or replace procedure queryempinform(eno in number,
pename out varchar2,
psal out number,
pjob out varchar2)
as
begin
select ename,sal,empjob into pename,psal,pjob from emp where empno=eno;
end;
/

### 在应用中访问存储过程和存储函数

1. 访问存储函数
2. 访问存储过程