sql变量类型条件判断+循环+光标（游标）的基本使用

oracle数据库的常用数据类型有varchar2,number,date等，除了这些基本类型，还有2中特殊的类型：

 declare
    --定义引用型变量
    pname emp.ename%type;
    psal  emp.sal%type;
  begin
    --使用引用型变量
    select ename, sal into pname, psal from emp where empno = 7839;
    dbms_output.put_line(pname || '员工的薪水为：' || psal);
  end;

--记录型变量
declare
  emp_rec emp%rowtype;
begin
  select * into emp_rec from emp where empno = 7839;
  dbms_output.put_line(emp_rec.ename || '的薪水是：' || emp_rec.sal);
end;

plsql程序判断的语法：
if 语句
1. if 条件 then 语句1;
语句2;
end if;

2.if 条件 then 语句序列1;
else 语句序列2;
end if;

3.if 条件 then 语句；
elsif 语句 then 语句;
else 语句;
end if;
下面的程序接收一个键盘输入，并判断键盘输入的内容：

accept num prompt'请输入一个数字';
declare
  pnum number := #
begin
  if pnum = 1 then
    dbms_output.put_line('您输入的数字是1');
  elsif pnum = 2 then
    dbms_output.put_line('您输入的数字是2');
  elsif pnum = 3 then
    dbms_output.put_line('您输入的数字是3');
  else
    dbms_output.put_line('请输入正确的数字');
  end if;
end;

plsql程序循环的语法规则使用示例：使用循环打印1-10
1.使用while循环

declare
  pnum number := 1;
begin
  while pnum <= 5 loop
    dbms_output.put_line(pnum);
    pnum:= pnum + 1;
  end loop;
end;

2.使用loop循环

declare
  pnum number := 1;
begin
  loop
    exit when pnum > 8;
    dbms_output.put_line(pnum);
    pnum := pnum + 1;
  end loop;
end;

3.使用for循环

declare
  pnum number := 1;
begin
  for pnum in 1 .. 10 loop
    dbms_output.put_line(pnum);
  end loop;
end;

当sql查询的结果是一个结果集的时候需要使用光标，光标使用时需要定义，然后打开，最后关闭。
光标(游标)的使用 fetch的作用：1.把当前指针指向的记录返回 2.将指针指向下一条记录

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;
select * from emp_back order by sal desc;
--使用光标给员工涨工资
declare
  cursor cemp is
    select empno, job, sal from emp_back;
  pempno emp_back.empno%type;
  pjob   emp_back.job%type;
  psal   emp_back.sal%type;
begin
  open cemp;
  loop
    fetch cemp
      into pempno, pjob, psal;
    exit when cemp%notfound;
    if pjob = 'PRESIDENT' then
      update emp_back set sal = sal - 0.3 where empno = pempno;
    elsif pjob = 'ANALYST' then
      update emp_back set sal = sal - 0.2 where empno = pempno;
    else
      update emp_back set sal = sal - 0.1 where empno = pempno;
    end if;
  end loop;
  close cemp;
  --oracle 默认的事务隔离级别为read committed 所以这里需要提交事务
  commit;
  dbms_output.put_line('涨工资完成');
end;
rollback;

一、光标的属性：
1. %found 光标取到值
2. %notfound 光标取不到值
3. %isopen 判断光标是否打开
4. %rowcount 影响的行数
二、光标的限制 默认情况下，oracle数据库只允许在同一个会话中，打开300个光标
isopen的使用：

declare
  cursor cemp is
    select ename, job, sal from emp_back;
  pename emp_back.ename%type;
  pjob   emp_back.job%type;
  psal   emp_back.sal%type;
begin
  open cemp;
  if cemp%isopen then
    dbms_output.put_line('光标处于打开状态');
  else
    dbms_output.put_line('光标处于关闭状态');
  end if;
  close cemp;
end;

rowcount的使用：

declare
  cursor cemp is
    select ename, job, sal from emp_back;
  pename emp_back.ename%type;
  pjob   emp_back.job%type;
  psal   emp_back.sal%type;
begin
  open cemp;
  loop
    fetch cemp
      into pename, pjob, psal;
    exit when cemp%notfound;
    dbms_output.put_line('受影响行数：' || cemp%rowcount);
  end loop;
  close cemp;
end;

带参数的光标(查询指定部门的所有员工姓名)

declare
  cursor cemp(dno number) is
    select ename from emp where deptno = dno;
  pename emp.ename%type;
begin
  open cemp(20);
  loop
    fetch cemp
      into pename;
    exit when cemp%notfound;
    dbms_output.put_line(pename);
  end loop;
  close cemp;
end;

plsql中的例外 exception后的then相当于是一个{}后面可以加多句话

--1.no_data_found例外
declare
  pename emp.ename%type;
begin
  select ename into pename from emp where empno = 1234;
exception
  when no_data_found then
    dbms_output.put_line('未找到该员工');
  when others then
    dbms_output.put_line('其他错误');
end;
--2.too_many_rows例外
declare
  pename emp.ename%type;
begin
  select ename into pename from emp where deptno = 10;
exception
  when too_many_rows then
    dbms_output.put_line('匹配错误行数');
  when others then
    dbms_output.put_line('其他错误');
end;
--3.zero_divide
declare
  pnum number;
begin
  pnum := 1 / 0;
exception
  when zero_divide then
    dbms_output.put_line('1. 零不能做除数');
    dbms_output.put_line('2. 0不能做除数');
  when others then
    dbms_output.put_line('其他错误');
end;
--4.value_error
declare
  pnum number;
begin
  pnum := 'abc';
exception
  when value_error then
    dbms_output.put_line('算术转换异常');
  when others then
    dbms_output.put_line('其他错误');
end;
--5.自定义异常
declare
  cursor cemp is
    select ename from emp where empno = 1111;
  pename emp.ename%type;
  no_emp_found exception;
begin
  open cemp;
  --取值
  fetch cemp
    into pename;
  --如果未取到，抛出异常
  if cemp%notfound then
    raise no_emp_found;
  end if;
  --关闭光标
  --自定义的异常被捕获，跳出这一步，oracle会自动启动pmon(process monitor)来关闭
  close cemp;
exception
  --捕获异常
  when no_emp_found then
    dbms_output.put_line('未发现该员工');
  when others then
    dbms_output.put_line('其他错误');
end;

查询统计每年入职的员工人数：

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 count(*) Total,
       sum(case to_char(hiredate, 'yyyy')
             when '1980' then
              1
             else
              0
           end) "1980",
       sum(case to_char(hiredate, 'yyyy')
             when '1981' then
              1
             else
              0
           end) "1981",
       sum(case to_char(hiredate, 'yyyy')
             when '1982' then
              1
             else
              0
           end) "1982",
       sum(case to_char(hiredate, 'yyyy')
             when '1987' then
              1
             else
              0
           end) "1987"
  from emp;
--使用游标查询每年入职员工人数
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:' || (count80 + count81 + count82 + count87));
  dbms_output.put_line('1980:' || count80);
  dbms_output.put_line('1981:' || count81);
  dbms_output.put_line('1982:' || count82);
  dbms_output.put_line('1987:' || count87);
end;

给员工涨工资
从最低工资开始涨起，每人涨10%，涨后工资总额不超过50000
打印涨工资人数和涨后工资总额

select count(*) from emp_back order by sal;
declare
  cursor cemp is
    select empno, sal from emp_back order by sal;
  pempno    emp_back.empno%type;
  psal      emp_back.sal%type;
  count_emp number := 0;
  sum_sal   number;
begin
  open cemp;
  select sum(sal) into sum_sal from emp_back;
  loop
    exit when sum_sal > 50000;
    fetch cemp
      into pempno, psal;
    exit when cemp%notfound;
    update emp_back set sal = sal * 1.1 where empno = pempno;
    count_emp := count_emp + 1;
    sum_sal   := sum_sal + psal * 0.1;
  end loop;
  close cemp;
  --提交事务
  commit;
  dbms_output.put_line('涨工资人数：' || count_emp || '  涨后工资总额：' || sum_sal);
end;
rollback;

查询某部门特定工资范围内的人数，以及该部门的工资总额

select d.deptno 部门,
       sum(case
             when sal < 3000 then
              1
             else
              0
           end) 小于3000数,
       sum(case
             when sal >= 3000 and sal <= 6000 then
              1
             else
              0
           end) "3000-6000",
       sum(case
             when sal > 6000 then
              1
             else
              0
           end) 大于6000,
       nvl(sum(sal), 0) 工资总额
  from emp e
 right join dept d
    on e.deptno = d.deptno
 group by d.deptno
 order by d.deptno;