--plsql基础

-- 声明及变量
-- 由declare开始,begin程序体开始end结束
declare
  result number;
begin
  result := 10 + 3 * 4 + 40;
  dbms_output.put_line('result:' || result);
end;


-- %type
declare
  v_ename emp.ename%type;
  v_sal   emp.sal%type;
  c_tax_rate constant number(3, 2) := 0.03;
  v_sal_tax v_sal%type;
begin
  select ename, sal into v_ename, v_sal from emp where empno = &eno;
  v_sal_tax := v_sal * c_tax_rate;
  dbms_output.put_line('雇员名字:' || v_ename);
  dbms_output.put_line('雇员薪水:' || v_sal);
  dbms_output.put_line('雇员所得税:' || v_sal_tax);
end;

-- %rowtype
declare
  v_emp emp%rowtype;
begin
  select * into v_emp from emp where empno=&eno;
  dbms_output.put_line('雇员名字:'||v_emp.ename);
  dbms_output.put_line('雇员薪水:'||v_emp.sal);
  dbms_output.put_line('雇员工种:'||v_emp.job);
end;

-- record
-- 类似于structure
-- 定义的语法 type record_type is record(v1 data_type1,v2 data_type2,vn data_typen);
declare
  type emp_record_type is record(
    name   emp.ename%type,
    salary emp.sal%type,
    job    emp.job%type);
  emp_record emp_record_type;
begin
  select ename, sal, job into emp_record from emp where empno = &eno;
  dbms_output.put_line('雇员名:' || emp_record.name);
  dbms_output.put_line('工资:' || emp_record.salary);
  dbms_output.put_line('岗位:' || emp_record.job);
end;


-- table类似于数组
-- 定义的语法 type table_type is table of data_type index by binary_integer;
declare
  type emp_table_type is table of emp.ename%type index by binary_integer;
  emp_table emp_table_type;
begin
  select ename into emp_table(-1) from emp where empno = &eno;
  emp_table(0) := 'jack';
  emp_table(1) := 'lucy';
  dbms_output.put_line('emp_table(-1)雇员名:' || emp_table(-1));
  dbms_output.put_line('emp_table(0):' || emp_table(0));
  dbms_output.put_line('emp_table(1):' || emp_table(1));
end;


-- table
-- 数字类型的数组
DECLARE
  type array is table of number index by binary_integer;
  firstnumber  array;
  secondnumber array;
BEGIN
  firstnumber(0) := '123456';
  firstnumber(1) := '888888';
  secondnumber(0) := '222222';
  secondnumber(1) := '666666';
  for i in 0 .. firstnumber.count - 1 loop
    DBMS_OUTPUT.PUT_LINE('i=' || i ||
                         ', firstnumber[0]= ' || firstnumber(i) ||
                         ', secondnumber= ' || secondnumber(i));
  end loop;
END;



-- 条件控制语句示例1,if语句
declare
  v_sal number(7, 2);
begin
  select sal into v_sal from emp where ename = 'SCOTT';
  dbms_output.put_line('scott salary:' || v_sal);
  if v_sal < 100 then
    dbms_output.put_line('scott 工资低于1000');
  else
    if 1000 <= v_sal and v_sal < 2000 then
      dbms_output.put_line('scott1在1000到2000之间');
    else
      dbms_output.put_line('scott1工资高于2000');
    end if;
  end if;
end;


-- 条件控制语句示例2,case语句
declare
  v_sal number(7, 2);
begin
  select sal into v_sal from emp where ename = 'SCOTT';
  dbms_output.put_line('scott工资:' || v_sal);
  case
    when v_sal < 1000 then
      dbms_output.put_line('scott工资低于1000');
    when 1000 <= v_sal and v_sal < 2000 then
      dbms_output.put_line('scott工资在1000到2000之间');
    else
      dbms_output.put_line('scott工资高于2000');
  end case;
end;


-- 条件控制语句示例2,case语句
declare
  v_grade char := 'a';
begin
  case v_grade
    when 'a' then
      dbms_output.put_line('excellent');
    when 'b' then
      dbms_output.put_line('very good');
    when 'c' then
      dbms_output.put_line('good');
    else
      dbms_output.put_line('no such grade');
  end case;
end;



-- 循环控制语句示例1,while 语句
declare
  v_i number := 1;
  v_s number := 0;
begin
  while v_i <= 100 loop
    v_s := v_s + v_i;
    v_i := v_i + 1;
  end loop;
  dbms_output.put_line(v_s);
end;

-- 循环控制语句示例2,for 语句
declare
  v_s number := 0;
begin
  for v_i in 1 .. 100 loop
    v_s := v_s + v_i;
  end loop;
  dbms_output.put_line(v_s);
end;

-- 循环控制语句示例3,loop 语句
declare
  v_i number := 1;
  v_s number := 0;
begin
  loop
    v_s := v_s + v_i;
    v_i := v_i + 1;
    exit when v_i > 100;
  end loop;
  dbms_output.put_line(v_s);
end;


-- 顺序控制例子
-- 语法:GOTO 标签
declare
  v_i number := 0;
  v_s number := 0;
begin
  <<label_1>>
  v_i := v_i + 1;
  if v_i <= 100 then
    v_s := v_s + v_i;
    goto label_1;
  end if;
  dbms_output.put_line(v_s);
end;


-- 顺序控制例子
-- 查询emp表,如果sal小于或等于3000,则给其加上1%的补助
declare
  v_empno emp.empno%type;
  v_ename emp.ename%type;
  v_sal   emp.sal%type;
begin
  v_empno := &emp_no;
  select ename, sal into v_ename, v_sal from emp where empno = v_empno;
  if v_sal <= 3000 then
    update emp set comm = v_sal * 0.1 where empno = v_empno;
    dbms_output.put_line(v_ename || '补助' || v_sal * 0.1);
  else
    null;
  end if;
end;



-- 异常处理
declare
  v_ename emp.ename%type;
begin
  select ename into v_ename from emp where empno = &emp_no;
  dbms_output.put_line('雇员名' || v_ename);
exception
  when no_data_found then
    dbms_output.put_line('雇员号不存在,请重新输入' || v_ename);
end;


-- 预定义异常的例子
-- 其出现的异常:no_data_found
-- SELECT INTO 语句没有返回任何行,或者程序引用一个嵌套表中已经被删除的元素,
-- 或索引表中一个没有被初始化的元素
declare
  v_sal emp.sal%type;
begin
  select sal into v_sal from emp where empno = &emp_no;
  case
    when v_sal < 1000 then
      update emp set sal = v_sal + 100 where empno = &emp_no;
    when v_sal < 2000 then
      update emp set sal = v_sal + 150 where empno = &emp_no;
  end case;
exception
  when case_not_found then
    dbms_output.put_line('在CASE语句中缺少' || v_sal || '相关的条件');
end;


-- 异常处理示例
-- 异常处理名称:dup_val_on_index
-- 向有唯一性索引约束的列插入重复值
select * from dept where deptno = 52 declare v_dept dept%rowtype;
begin
  insert into dept values (50, 'demo1', 'bj1');
  insert into dept values (50, 'demo2', 'bj2');
exception
  when dup_val_on_index then
    dbms_output.put_line('违反了部门编号不能重复的约束');
end;


-- 异常处理示例
-- 异常名称:too_many_rows
-- SELECT INTO 语句返回多行
declare
  v_ename emp.ename%type;
begin
  select ename into v_ename from emp where sal = &sal;
exception
  when too_many_rows then
    dbms_output.put_line('返回了多行,请使用游标');
end;


-- 预定义异常错误是有限的,
-- 非预定义异常,我们需要用pragma exception_init(exception_name,oracle_error_code)语法来定义异常与oracle错误代码挂钩。
declare
  key_null_error exception;
  pragma exception_init(key_null_error, -1400);
begin
  insert into dept values (null, 'demo1', 'bj1');
exception
  when key_null_error then
    dbms_output.put_line('不能向部门编号列插入null');
end;


-- 非预定义异常示例
-- 可以直接在others段中使用sqlcode和sqlerrm
declare
  v_sqlcode number;
  v_sqlerrm varchar2(2048);
begin
  insert into dept values (null, 'demo1', 'bj1');
exception
  when no_data_found then
    dbms_output.put_line('没有找到数据');
  when others then
    if sqlcode = -1400 then
      v_sqlcode := sqlcode;
      v_sqlerrm := sqlerrm;
      dbms_output.put_line('不能向部门编号列插入null');
      dbms_output.put_line(v_sqlerrm);
    end if;
end;


-- 自定义异常
-- 必须要声明,并且必须要用RAISE语句显式地引发
declare
  v_sal number;
  e_sal_error exception;
begin
  select sal into v_sal from emp where empno = &emp_no;
  if v_sal <= 2500 then
    dbms_output.put_line('该雇员工资:' || v_sal);
    raise e_sal_error;
  end if;
exception
  when e_sal_error then
    dbms_output.put_line('该雇员工资高于2500了');
end;


-- 自定义错误代码例子
-- 语法:
-- Raise_application_error(error_code,message[,ture|false]
-- 其中:
-- Error_code 是自定义的错误代码(-20000~-20999)之间
-- Messagebox是自定义错误消息
-- True 将自定义错误消息添加到错误消息栈中
create or replace procedure query_comm_if_null(v_no in emp.empno%type) is
  v_comm emp.comm%type;
begin
  select comm into v_comm from emp where empno = v_no;
  if v_comm is null or v_comm = 0 then
    raise_application_error(-20001, '该雇员无补助');
  end if;
exception
  when no_data_found then
    dbms_output.put_line('没有该雇员:' || v_no);
end query_comm_if_null;



-- 定义存储过程
create or replace procedure pro_name(v_no in emp.empno%type,
                         v_name out emp.ename%type,
                         v_sal out emp.sal%type)
is
e_sal_error exception;
begin
  select ename,sal into v_name,v_sal from emp where empno = v_no;
  if v_sal>1500 then
    dbms_output.put_line(v_name||':'||v_sal);
    raise e_sal_error;
  end if;
exception
    when no_data_found then
       dbms_output.put_line('djjf'||v_no);
    when e_sal_error then
       dbms_output.put_line(v_no);
end pro_name;
 
-- 调用过程
declare
    v_a1 emp.ename%type;
    v_a2 emp.sal%type;
begin
    pro_name(v_no=>7221,v_name=>v_a1,v_sal=>v_a2);
end;

-- 查看过程
Select text from user_source where name='pro_name';

-- 删除过程
drop procedure pro_name;



-- 定义函数
create or replace function fun_salary_by_deptno
(v_dept_no in emp.deptno%type,v_emp_cnt out number)
return number
is
  v_sum number(10,2);
  begin
    select sum(sal),count(*) into v_sum,v_emp_cnt from emp
      where deptno = v_dept_no;
    return v_sum;
end fun_salary_by_deptno;


-- pl/sql中调用函数
declare
v_a1 emp.deptno%type;
v_a2 number;
v_sum number(10,2);
begin
select count(*) into v_a2 from emp where deptno=10;
v_sum := fun_salary_by_deptno(v_emp_cnt => v_a1,v_dept_no => 10);
if v_a1 = 10 then
  dbms_output.put_line('该部门无人');
else
  dbms_output.put_line('该部门工资总和:'||v_sum||';人数:'||v_a2);
end if;
end;

-- 函数的查看
select text from user_source where name = 'FUN_SALARY_BY_DEPTNO'

-- 删除函数
drop function fun_salary_by_deptno;



-- 创建程序包
create or replace package pkg_wujin is pragma serially_reusable;
    v_empno number(4);   
    v_ename varchar2(30);
    v_age   number(3);   
    function fun_wj(vemp in emp.empno%type, vename out emp.ename%type) return number;
end pkg_wujin;

-- 调用包的公有变量
begin
  pkg_wujin.v_empno := 086;
  pkg_wujin.v_ename := 'wujin';
  pkg_wujin.v_age := 30;
  dbms_output.put_line('工号 ' || pkg_wujin.v_empno);
  dbms_output.put_line('姓名 ' || pkg_wujin.v_ename);
  dbms_output.put_line('年龄 ' || pkg_wujin.v_age);
end;


-- 创建包体
create or replace package body pkg_wujin is pragma serially_reusable;
   v_empnoo number(4);
   function fun_wj(vemp in emp.empno%type, vename out emp.ename%type) return number is
   begin
       v_empno := 555;
       return v_empno;
   end fun_wj;
end pkg_wujin;

-- 调用包的公有函数(续一)
declare
  vv emp.ename%type;
  vre number;
begin
  vre := pkg_wujin.fun_wj(111, vv);
  dbms_output.put_line(vre);
end;

-- 删除程序包
drop package my_pkg;



-- 处理多行结果集的查询,可以使用游标。
-- 步骤为:声明-打开-取行-关闭游标
declare
  tempsal emp.sal%type;
  -- 1
  cursor tempcursor is
    select * from emp where sal > tempsal;
  cursorrecord tempcursor%rowtype;
begin
  tempsal := 800;
  -- 2
  open tempcursor;
  -- 3
  fetch tempcursor
    into cursorrecord;
  dbms_output.put_line(to_char(cursorrecord.deptno));
  dbms_output.put_line(to_char(cursorrecord.empno));
  -- 4
  close tempcursor;
end;

-- 游标与while循环例子
declare
  cursor tempcursor(tempsal emp.sal%type) is
    select * from emp where sal > tempsal;
  cursorrecord tempcursor%rowtype;
begin
  open tempcursor(800);
  fetch tempcursor into cursorrecord;
  while tempcursor%found loop
    dbms_output.put_line(to_char(cursorrecord.deptno || '  ' || cursorrecord.ename));
    fetch tempcursor into cursorrecord;
  end loop;
  close tempcursor;
end;


-- 游标与FOR循环
declare
  cursor tempcursor(tempsal emp.sal%type) is
    select * from emp where sal > tempsal;
begin
  for cursorrecord in tempcursor(800) loop
    dbms_output.put_line(to_char(cursorrecord.deptno || '  ' || cursorrecord.ename));
  end loop;
end;

-- 可更新游标
declare
  v_sal emp.sal%type;
  cursor cur_emp is select * from emp for update of sal;
  --cursor cur_emp is select * from emp for update of sal wait 2; --等待n秒 数据行未解锁 就ora - 54   
begin
  for cr in cur_emp loop
    select sal into v_sal from emp where empno = 7369;
   
    update emp set sal = sal + v_sal where current of cur_emp;
  end loop;
end;


-- %isopen属性
-- 测试游标是否打开,如果没有打开游标就使用fetch语句,将提示错误信息。
declare tempsal scott.emp.sal%type;
  cursor tempcursor is select * from scott.emp where sal>tempsal;
  cursorrecord tempcursor%rowtype;
begin
  tempsal:=800;
  open tempcursor;
  if tempcursor%isopen then
     fetch tempcursor into cursorrecord;
     dbms_output.put_line(to_char(cursorrecord.deptno));
  else
     dbms_output.put_line('游标没有打开!');
  end if;
  close tempcursor;
end;

-- %found属性
-- 测试前一个fetch语句是否有值
declare
  tempsal scott.emp.sal%type;
  cursor tempcursor is select * from scott.emp where sal > tempsal;
  cursorrecord tempcursor%rowtype;
begin
  tempsal := 800;
  open tempcursor;
  if tempcursor%isopen then
    fetch tempcursor
      into cursorrecord;
    if tempcursor%found then
      dbms_output.put_line(to_char(cursorrecord.deptno));
    else
      dbms_output.put_line('没有取到值');
    end if;
  else
    dbms_output.put_line('游标没有打开!');
  end if;
  close tempcursor;
end;

-- 使用游标更新或删除数据1
declare
  v_emp_rec emp00%rowtype;
  cursor c1 is select * from emp for update;
begin
  open c1;
  loop
    fetch c1
      into v_emp_rec;
    exit when c1%notfound;
    if v_emp_rec.deptno >= 30 then
      update emp
         set comm = 300 -- 修改数据
       where current of c1;
    end if;
  end loop;
  commit; -- 提交已经修改的数据
  close c1;
end;

-- 返回游标的行数
-- %ROWCOUNT属性
Declare
  tempsal scott.emp.sal%type;
  cursor tempcursor is
    select * from scott.emp where sal > tempsal;
  cursorrecord tempcursor%rowtype;
  rowcounts    number;
begin
  tempsal := 800;
  open tempcursor;
  fetch tempcursor into cursorrecord;
  if tempcursor%found then
    dbms_output.put_line(to_char(cursorrecord.deptno));
    dbms_output.put_line(to_char(tempcursor%rowcount));
  else
    dbms_output.put_line('没有数据');
  end if;
  close tempcursor;
end;

-- 指针游标返回结果集
create or replace package PKG_EMP as
  type cr_emp is ref cursor;
  procedure PRO_EMP(vdeptno in emp.deptno%type, vc_emp out cr_emp);
end PKG_EMP;

create or replace package body PKG_EMP as
  procedure PRO_EMP(vdeptno in emp.deptno%type, vc_emp out cr_emp) is
    sqlstr varchar2(100);
  begin
    if vdeptno = 0 then
      open vc_emp for select * from emp;
    else
      sqlstr := 'select * from emp where deptno = :vdept';
      open vc_emp for sqlstr using vdeptno;
    end if;
  end PRO_EMP;
begin
  null;
end PKG_EMP;

/********************************* 或者 *************************************
create or replace package body PKG_EMP as
  procedure PRO_EMP(vdeptno in emp.deptno%type, vc_emp out cr_emp) is
    sqlstr varchar2(100);
  begin
    if vdeptno = 0 then
      open vc_emp for select * from emp;
    else
      sqlstr := 'select * from emp where deptno =' || vdeptno;
      open vc_emp for sqlstr;
    end if;
  end PRO_EMP;
begin
  null;
end PKG_EMP;
*****************************************************************************/

declare
  v_emp emp%rowtype;
  cr       PKG_EMP.cr_emp;
begin
  PKG_EMP.PRO_EMP(0, cr);
  fetch cr into v_emp;
  while cr%found loop
    dbms_output.put_line(v_emp.empno || '  ' || v_emp.ename || '  ' || v_emp.sal);
    fetch cr into v_emp;
  end loop;
end;


-- 创建语句级触发器
create or replace trigger my_triger
  before insert or update or delete on emp
begin
  if (to_char(sysdate, 'day') in ('星期六', '星期日')) or
     (to_char(sysdate, 'HH24') not between 8 and 18) then
    raise_application_error(-20001, '不是上班时间,不能修改emp表');
  end if;
end;

delete from emp;


-- 创建行级触发器
create or replace trigger tr_emp_sal_comm
  before update of sal, comm or delete on emp
  for each row
  when (old.job = 'SALESMAN')
begin
  case
    when updating('sal') then
      if :new.sal < :old.sal then
        raise_application_error(-20001, '销售人员工资只能涨不能降');
      end if;
    when updating('comm') then
      if :new.comm < :old.comm then
        raise_application_error(-20002, '销售人员补助只能涨不能降');
      end if;
    when deleting then
      raise_application_error(-20003, '不能删除emp表的销售人员记录');
  end case;
end;

update emp set comm=800 where job='SalesMan';