oracle-day05

-- 创建表tb_client  id,名字name,年龄age,每年生日日期eybirthday
create table tb_client(
id number(6)primary key,
name varchar2(20),
age number(6),
eybirthday date
)
-- 编写函数fun_batch_add_client(v_count number)使用循环向表tb_client中添加数据
-- 函数要求
-- 1,v_count等于几就想表中插入几条数据
-- 2,如果表中数据的数量和v_count相等,就让函数返回"数据已经存在",否则插入
-- 3,插入数据ID和年龄随着循环变化,每年生日日期eybirthday显示为生日当天,
-- 如:年龄为1时,10条数据,eybirthday则显示2008-11-02,为2时显示2009-11-02....

  create or replace function fun_batch_add_client(v_count number)
  return varchar2
  is 
  t_start number(10);
  t_temp number(2);
  t_mouth number(6);
  begin 
    t_mouth:=v_count;
    t_start:=v_count;
     select count(*) into t_temp from tb_client;
      if t_temp=t_start then
        return '数据已经存在';
      else 
        delete from tb_client;    
        for i in 1..t_start loop
         insert into tb_client values(i,'name'||i,i,add_months(sysdate,-i*12));
         end loop;
         commit;
         return '正常'; 
       end if;
  end;
  
  
  begin 
    fun_batch_add_client(5);
    select * from tb_client;
    end;

  select add_months(sysdate,-12*1) from dual;
 ------------------------------------------------------

-- 存储过程返回游标
create or replace procedure pro_cursor1(v_deptno number,csr_emp out sys_refcursor)
is 
begin
  open csr_emp for select * from emp where deptno=v_deptno;--打开赋值
end;

--游标变量
declare
type csr_type is ref cursor;--定义游标类型
t_csr_data csr_type;--定义游标，声明类型属于游标
t_dept_row dept%rowtype;
t_emp_row emp%rowtype;
begin
  open t_csr_data for select * from dept;
  loop
    fetch t_csr_data into t_dept_row;
    exit when t_csr_data%notfound;
    dbms_output.put_line('部门名称'||t_dept_row.dname);
    end loop;
    close t_csr_data;
    --
  open t_csr_data for select * from emp;
  loop
    fetch t_csr_data into t_emp_row;
    exit when t_csr_data%notfound;
    dbms_output.put_line('部门名称'||t_emp_row.ename);
    end loop;
    close t_csr_data;
end;
----------------------------------------------------

--游标使用(一次取一行)
declare
-- 声明变量，常量，异常，游标
cursor csr_emp is select * from emp where deptno=10;--定义游标
t_emp_row emp%rowtype; --定义行变量
begin
  open csr_emp;--打开游标
  loop--提取数据
    fetch csr_emp into t_emp_row;--一次取一行
    exit when csr_emp%notfound;
    dbms_output.put_line('员工编号:'||t_emp_row.empno||'   姓名:'||t_emp_row.ename);
  end loop;
   close csr_emp;
end;

--游标使用(一次性全部取出)
declare
 cursor crs_dept is select * from dept;
 type table_dept is table of dept%rowtype index by binary_integer;--定义table类型
 t_table_dept table_dept;
begin
  open crs_dept;
  fetch crs_dept bulk collect into t_table_dept;
  close crs_dept;
  for i in t_table_dept.first..t_table_dept.last loop
     dbms_output.put_line('名称:'||t_table_dept(i).dname||'   位置:'||t_table_dept(i).loc);
    end loop;
end;

-- 游标带参数
declare
cursor csr_emp(v_deptno number,v_job varchar2) is select * from emp where deptno=v_deptno and job=v_job;--定义游标
  t_emp_row emp%rowtype; --定义行变量
begin
  open csr_emp(10,'CLERK');--打开游标
  loop--提取数据
    fetch csr_emp into t_emp_row;--一次取一行
    exit when csr_emp%notfound;
    dbms_output.put_line('员工编号:'||t_emp_row.empno||'   姓名:'||t_emp_row.ename);
  end loop;
   close csr_emp;
end;

--游标简化
declare
cursor crs_dept is select * from dept;
begin
  for i in crs_dept loop
    dbms_output.put_line('部门名称:'||i.dname);
    end loop;
  end;

--第二种
declare
begin
  for i in (select * from dept) loop
    dbms_output.put_line('部门名称:'||i.dname);
    end loop;
  end;
----------------------------------------------------

-- 触发器 (5,6不能修改emp表)
create or replace trigger tgr_emp
before --触发时间
insert or update or delete --触发事件
on emp --触发对象
begin
   if to_char(sysdate,'day') in ('星期五','星期六') then
   raise_application_error(-20001,'周五六不允许修改员工表');--错误码可以重复(-20000-20999)
   end if;
end;

--启用触发器
alter trigger tgr_emp enable;
--禁用触发器
alter trigger tgr_emp disable;

--
create or replace trigger tgr_emp1
before --触发时间
insert or update or delete --触发事件
on emp --触发对象
begin
   if to_char(sysdate,'day') in ('星期五','星期六') then
     if inserting then
         raise_application_error(-20001,'周五六不允许添加员工');
      elsif updating then
         raise_application_error(-20002,'周五六不允许修改员工');
      else 
         raise_application_error(-20003,'周五六不允许删除员工');
       end if;
   end if;
end;

--行级触发器
create or replace trigger tgr_emp_deptno30_limit
before --触发时间
update of sal,comm or delete --触发事件
on emp --触发对象
for each row 
  when(old.deptno=30)--行号为30的部门编号触发
begin
   if updating('sal') and :new.sal<:old.sal then
     raise_application_error(-20001,'部门为30的员工工资不能降低');
    elsif updating('comm') and :new.comm<:old.comm then
      raise_application_error(-20002,'部门为30的员工奖金不能降低');
     elsif deleting then
       raise_application_error(-20003,'部门为30的员工不能辞退');
     else 
       return;
    end if;
end;