oracle-day04

-- 异常
-- 唯一行约束异常
declare
begin
  insert into dept values(10,'开发部','6楼');
  exception
    when dup_val_on_index then
      dbms_output.put_line('破坏主键约束');
 end;
 
-- 自定义异常
declare
  no_delete_data exception; --声明变量属于异常类型
begin
  delete from dept where deptno=50;
  if sql%notfound then-- 隐含游标 
    raise no_delete_data;-- raise 抛出异常
  end if;
   dbms_output.put_line('正常删除数据');
  exception --处理异常
    when no_delete_data then
       dbms_output.put_line('删除数据不存在');
end;

-- 事物 commit,rollback
begin 
  insert into dept values(1,'aaa','aa');
  insert into dept values(1,'bbb','bb');
  insert into dept values(3,'ccc','cc');
  insert into dept values(4,'ddd','dd');
  commit;
  exception 
    when dup_val_on_index then
      rollback; 
end;
-- 事物 savepoint，rollback to
begin 
  insert into dept values(1,'aaa','aa');
  savepoint pointA;-- 保存回滚点
  insert into dept values(1,'bbb','bb');
  insert into dept values(3,'ccc','cc');
  rollback to pointA;--回滚到回滚点
  insert into dept values(4,'ddd','dd');
end;
-- 死锁    A窗口
update dept set dname='aaa' where deptno=10;

update dept set dname='bbb' where deptno=20;--2

--B窗口
update dept set dname='aaa' where deptno=10;--1

update dept set dname='bbb' where deptno=20;--3
-------------------------------------------------------

--存储过程
--无参存储过程
create procedure pro_query_dept_bydno --存储过程名称
as 
--声明部分
t_dname dept.dname%type;
begin
  select dname into t_dname from dept where deptno=10;
  dbms_output.put_line('部门名称'||t_dname);
exception 
  when no_data_found then
     dbms_output.put_line('部门不存在');
end; 

--调用存储过程方法一
begin 
  pro_query_dept_bydno();
end;
--方法二 
call  pro_query_dept_bydno();

-- 方法三 在命令行调用
exec pro_query_dept_bydno();

-- 带有入参和出参的存储过程
create procedure pro_query_ename_byeno(v_empno in number,v_ename out varchar2)
as --in 入参 out 出参
begin
  select ename into v_ename from emp where empno=v_empno;
exception
  when no_data_found then
     dbms_output.put_line('工号'||v_empno||'不存在');
end;
-- 测试  
declare
t_ename emp.ename%type;
begin
  pro_query_ename_byeno(7369,t_ename);
  dbms_output.put_line(t_ename);
end;
  --------------------------------------------------

-- 动态添加数据 loop循环
create procedure pro_batch_add_dept(v_start number)
as 
t_start number(2);-- v_start输入值不能算数运算操作
t_end number(2);
begin
  t_start:=v_start;
  t_end:=t_start+3;
  loop
    if t_start>t_end then
      exit;
    end if;
    insert into dept values(t_start,'开发'||t_start||'部'，t_start||'楼');
    t_start:=t_start+1;
  end loop;
end;
  
-- 动态添加数据 loop循环 带while
create procedure pro_batch_add_dept1(v_start number)
as 
t_start number(2);-- v_start输入值不能算数运算操作
t_end number(2);
begin
  t_start:=v_start;
  t_end:=t_start+3;

  while t_start <= t_end loop
     insert into dept values(t_start,'开发'||t_start||'部'，t_start||'楼');
     t_start:=t_start+1;
   end loop;
end;

-- 动态添加数据 loop循环 带for
create procedure pro_batch_add_dept2(v_start number)
as 
t_start number(2);-- v_start输入值不能算数运算操作
t_end number(2);
begin
  t_start:=v_start;
  t_end:=t_start+3;

  for i in t_start..t_end loop
     insert into dept values(i,'开发'||i||'部'，i||'楼');   
   end loop;
end;
 ------------------------------------------------------

  -- 函数
  create or replace function fun_query_ename_byeno(v_empno emp.empno%type)
  return varchar2 -- 返回值类型
 is
  t_ename emp.ename%type;
begin
  select ename into t_ename from emp where empno = v_empno;
  return t_ename;
exception
  when no_data_found then
    return '编号为' || v_empno || '员工不存在';
end;

  -- 测试上面代码
  declare t_ename varchar2(200);
begin
  t_ename := fun_query_ename_byeno(7777); dbms_output.put_line(t_ename);end;

  -- 函数(带出参)
  create or replace function fun_query_ename_byeno1(v_empno emp.empno%type, v_job out emp.job%type) return varchar2 -- 返回值类型
 is
  t_ename emp.ename%type;
begin
  select ename, job into t_ename, v_job from emp where empno = v_empno; return t_ename;
exception
  when no_data_found then return '编号为' || v_empno || '员工不存在';
end;

  -- 测试
  declare t_ename varchar2(200); t_job emp.job%type;
begin
  t_ename := fun_query_ename_byeno1(7777, t_job); if t_job is
  null then dbms_output.put_line(t_ename); else dbms_output.put_line('姓名:' || t_ename || '   职位:' || t_job);
end if;
end;