20210811 Oracle日常

 ---------------------------------------------
 declare 
 --列类型
 name student.name%type;
 --行类型
  student_row student%rowtype;
 
 begin
 --给变量复制给变量
 select s.name into name from student s where s.id = 1;
 --查询一行数据赋值给变量student_row ,不能查询到多行，否则会保存
 select s.* into student_row from student s where s.id = 2;
 dbms_output.put_line('姓名:'||name);
 dbms_output.put_line('编号'||student_row.id||'姓名'||student_row.name);
   end;
   
 
 
 declare
 --声明复合类型名称
  type student_record is record(
          id student.id%type,
          name student.name%type,
          birthday student.birthday%type,
          create_time student.create_time%type
        
  );
  --声明符合类型变量student_row
  student_row student_record;
 begin
   --查询字段赋值给复合类型变量
   select s.id,s.name,s.birthday,s.create_time into  student_row from student s where
   s.id = 1;
   dbms_output.put_line('编号:'||student_row.id||'，姓名：'||student_row.name||'，生日：'||student_row.birthday||'创建时间'
   ||student_row.create_time);
 
   end;

--输入一个成绩，输出成绩等级
declare 
score number;
begin
  --接收键盘输入内容
  score :=&请从键盘输入成绩;
  if score >=90  then 
    dbms_output.put_line('优秀');
    elsif score >=80 then
     dbms_output.put_line('良好');
     elsif score >=70 then
     dbms_output.put_line('中等');
     elsif score >=60 then
     dbms_output.put_line('及格');
     else
        dbms_output.put_line('不及格');
    end if;
    
  end;
 
 

--输入成绩等级，输出对应的内容
declare
score varchar2(20) :='&输入成绩等级（A、B、C、D、E）';
begin
  case score
    when 'A' then
      dbms_output.put_line('优秀');
      when 'B' then
      dbms_output.put_line('良好');
      when 'C' then
      dbms_output.put_line('中等');
      when 'D' then
      dbms_output.put_line('及格');
      when 'E' then
      dbms_output.put_line('不及格');
      else
              dbms_output.put_line('输入错误');
    end case;
  end;
  

--计算1-100之和
declare 
he number :=0;
i number :=1;
begin
  loop
    he := he +i;
    --退出循环体
    exit when i=100;
    i:=i+1;
    
    end loop;
    dbms_output.put_line('总和:'||he);
  end;
  
--while 循环计算1-100之和
declare 
he number :=0;
i number :=1;
begin
 --只有符合条件才进入loop循环体
while i<=100 
  loop
     he := he +i;
     i :=i+1;
    end loop;
 
    dbms_output.put_line('总和:'||he);
  end;

declare 
jishu number :=0;
oushu number :=0;
i number :=1;
a number :=1;
b number :=2;
begin
  while i<=50
    loop
      
      jishu:=jishu+a;
      a :=a+2;
      i :=i+1;
     
      oushu:=oushu+b;
      b :=b+2;
      end loop;
      dbms_output.put_line('奇数和:'||jishu);
      dbms_output.put_line('偶数和:'||oushu);
  end;
  
 declare
 i number :=1;
 ji number :=0;
 ou number :=0;
 begin
 while i <=100
   loop
     if mod(i,2)=0 then
       ou:=ou+i;
       
       
       else
         ji:=ji+i;
     
         end if;
         i:=i+1;
         
         end loop;  
         dbms_output.put_line('奇数和:'||ji);
          dbms_output.put_line('偶数和:'||ou);
   end;
  
 
 
 --for循环计算1-100之和
 declare 
 he number :=0;
 
 begin
   for i in 1..100
     loop
        dbms_output.put_line('i:'||i);
       he :=he+i;
       end loop;
     dbms_output.put_line('总和:'||he);
   end;
   
 
--系统内置异常
--异常使用
declare
i number :=10;

begin
 
  dbms_output.put_line('i/0:'||(i/0));
  --异常处理
  exception
    --根据不同的异常类型进行处理，zero_divide除数为0异常0异常处理
    when zero_divide then
      dbms_output.put_line('除数不能为0');
      when others  then--其他异常
        dbms_output.put_line('其他异常');
        
  end;
 
 

--用户自定义异常的使用
declare
  --自定义异常，用关键字exception
  sex_exception exception;
  age_exception exception;
  sex varchar2(10);
  age number;
begin
  sex := '&请输入性别：';
  age := &请输入年龄;
  dbms_output.put_line(sex);
  dbms_output.put_line(age);
  if sex not in ('男', '女') then
    --抛出自定义异常
    raise sex_exception;
  end if;
  if age not between 0 and 150 then
    raise age_exception;
  end if;
exception
  --捕获自定义异常
  when sex_exception then
    --dbms_output.put_line('性别只能输入男或女');
    --弹出自定义错误消息窗口，第一个参数是错误代码，取值范围是-20001--20999，第二个参数是错误
    --信息错误描述，最大长度2048字符
    raise_application_error(-20001,'性别只能输入男或女');
  when age_exception then
   -- dbms_output.put_line('年龄只能输入0-150之间的数');
   raise_application_error(-20002,'年龄只能输入0-150之间的数');
end;
 

--给员工加工资，输入员工编号和要加的工资数，如果员工不存在，输出员工不存在

declare
  id1   number;
  getmn number;
  id2   number;
  id_exception exception;
begin
  id1   := '&请输入员工编号';
  getmn := '&请输入员工要加的工资';
  select s.employee_id into id2 from employees s where s.employee_id = id1;
  dbms_output.put_line(id2);
  if id2 is null then
    raise id_exception;
  end if;
  --select s.employee_id, s.salary from employees s where s.employee_id = 100;
  update employees s
     set s.salary = s.salary + getmn
   where s.employee_id = id1;
exception
  when NO_DATA_FOUND then
    dbms_output.put_line('员工不存在');
  
end;

--给员工加工资，输入员工编号和要加的工资数，如果员工不存在，输出员工不存在
declare
   emp_no employees.employee_id%type :=&员工编号;
   sal employees.salary%type :=&新加工资;
begin
  --给变量赋值，如果查询不到值报NO_DATA_FOUND异常，如果查询到多条数据报TOO_MANY_ROWS 异常
  select s.employee_id into emp_no from employees s where s.employee_id=emp_no;
  update employees s set s.salary=s.salary+sal where s.employee_id =emp_no;
  commit;--提交事务
  exception 
    when NO_DATA_FOUND then
      dbms_output.put_line('员工不存在');
  end;
  
  select * from employees s where s.employee_id = 1;

 --给员工加工资，输入员工编号和要加的工资数，如果员工不存在，输出员工不存在，要求工龄超过15年
declare
   emp_no employees.employee_id%type :=&员工编号;
   sal employees.salary%type :=&新加工资;
   hire employees.hire_date%type;
   age_exception exception;
begin
  --给变量赋值，如果查询不到值报NO_DATA_FOUND异常，如果查询到多条数据报TOO_MANY_ROWS 异常
  select s.employee_id into emp_no from employees s where s.employee_id=emp_no;
  select s.hire_date into hire from employees s where s.employee_id=emp_no;
  if add_months(hire,15*12)> sysdate  then  --add_months
    raise age_exception;
    
   end if;
  update employees s set s.salary=s.salary+sal where s.employee_id =emp_no;
  commit;--提交事务
  exception 
    when NO_DATA_FOUND then
      dbms_output.put_line('员工不存在');
     when age_exception then
      dbms_output.put_line('员工没用工资超过15年不给加工资');
  end;
  
  select * from employees s where s.employee_id = 105;

---------------------------------------------------------------------------------------------------------------------
--隐式游标常用属性使用 ,隐式游标固定名称sql，在执行dml语句（增删改查）式自动创建，打开，关闭
declare

begin
   update employees s set s.salary =s.salary+ &新加工资 where s.employee_id = &员工编号;
   if sql%found then
     --sql%rowcount影响到的行数
     dbms_output.put_line('成功更新'||sql%rowcount||'行数据');
     end if;
     commit;
     --sql%isopn 判断游标是否关闭，永远是false
     if sql%isopen then
      dbms_output.put_line('游标状态打开');
      else 
        dbms_output.put_line('游标状态关闭');
      end if;
  end;
 
  select * from employees s where s.employee_id = 105;

 
--使用隐式游标for循环遍历表所有的记录
declare 

begin
  --查询结果放到隐式游标中，使用for循环遍历游标的每一条数据
  for student_row in (select * from student)
    loop
      dbms_output.put_line('，编号：'||student_row.id||'，姓名：'||student_row.name||'，生日：'||
      student_row.birthday||'，创建时间：'||student_row.create_time);
      end loop;
  end;
  

--使用显示游标遍历表所有记录
declare 
student_row student%rowtype;
--使用关键字cursor声明游标变量student_cursor 并初始化值
cursor student_cursor is select * from student order by id;
begin
  --打开游标
  open student_cursor;
  loop
    --逐行读取数据赋值给变量student_row
    fetch student_cursor into student_row;
    --游标中没有数据退出循环
    exit when student_cursor%notfound;
    dbms_output.put_line('编号：'||student_row.id||'，姓名：'||student_row.name||'，生日：'||
      student_row.birthday||'，创建时间：'||student_row.create_time);
    end loop;
    --关闭游标
    close student_cursor;

  end;

--带参数的显示游标使用，根据部门编号查询员工信息
declare
 emp_row employees%rowtype;
 --声明带参数的游标，多个参数逗号隔开
 cursor emp_cursor(dept_no employees.department_id%type) is select e.* from 
 employees e where e.department_id =dept_no;
 
begin
  --打开游标，传递对应的参数值
  open emp_cursor(&部门编号);
  loop
    --逐行读取游标数据赋值给变量emp_row
    fetch emp_cursor into emp_row;
    
    --游标中没有数据退出循环
    exit when emp_cursor%notfound;
    dbms_output.put_line('员工编号：'||emp_row.employee_id||',员工薪水：'||emp_row.salary);
    end loop;
    --关闭游标
    close emp_cursor;
  end;

--带参数的显示游标使用，根据部门的编号所有的员工加薪

declare
 emp_row employees%rowtype;
 cursor emp_cursor(dept_no employees.department_id%type) is select e.* from employees e where e.department_id
 = dept_no for update;
 
 --声明带参数的游标，多个参数逗号隔开
begin
  open emp_cursor(&部门编号);
  loop
    fetch emp_cursor into emp_row;
    exit when emp_cursor%notfound;
    update employees e set e.salary = emp_row.salary +100 where current of emp_cursor;
    dbms_output.put_line('当前在更新员工'||emp_row.employee_id);
    end loop;
    close emp_cursor;
    commit;
  end;

select * from employees e where e.department_id =100;

--循环游标 = 显示游标+for循环
declare
 emp_row employees%rowtype;
 cursor emp_cursor is select * from employees;
 
begin
  for emp_row in emp_cursor
    loop
      dbms_output.put_line('员工编号;'||emp_row.employee_id||'，员工薪资：'||emp_row.salary);
    
    end loop;
  end;

--创建ref游标
declare
--声明frf游标类型emp_cursor
type emp_ref_cursor is ref cursor;
--使用emp_ref cousor 类型声明游标变量emp_cursor
emp_cursor emp_ref_cursor;
emp_row employees%rowtype;
begin
     --打开游标并初始化值
  open emp_cursor for select e.* from employees e;
  loop
    fetch emp_cursor into emp_row;
    exit when emp_cursor%notfound;
      dbms_output.put_line('员工编号;'||emp_row.employee_id||'，员工薪资：'||emp_row.salary);

    end loop;
    close emp_cursor;
  end;

--使用ref游标执行sql
declare
  --声明声明frf游标类型emp_cursor，指定ref游标给返回类型是emp表行类型
  type emp_ref_cursor is ref cursor ;
  emp_cursor emp_ref_cursor;
  emp_row employees%rowtype;
begin
  open emp_cursor for 'select e.* from employees e where e.department_id = :1' using &部门编号;
  loop
  fetch emp_cursor into emp_row;
   exit when emp_cursor%notfound;
      dbms_output.put_line('员工编号;'||emp_row.employee_id||'，员工薪资：'||emp_row.salary);
  end loop;
  close emp_cursor;
  end;

作业：
--使用显示游标完成，给各个部门最低工资的员工加薪1000
--使用ref游标完成

--使用plsql打印日期2021-01-01到2021-12-31所有星期天的日期（yyyy--MM--dd）;
--往表student中插入10条数据，id从1-100000，年龄是随机生成20-30之间