[Oracle] PL/SQL学习笔记

-- 1. 使用一个变量
declare
  -- Local variables here
  v_name varchar2(50);
begin
  -- Test statements here
  select t.user_name into v_name
  from pay_mer_order t
  where t.id=3530816;

  dbms_output.put_line(v_name);
end;

-- 2. 使用多个变量
declare
  -- Local variables here
  v_name varchar2(50);
  v_trans_no varchar2(50);
  v_app_code varchar2(50);
begin
  -- Test statements here
  select t.user_name, t.pay_brh_trans_no, t.app_code
         into v_name, v_trans_no, v_app_code
  from pay_mer_order t
  where t.id=3530816;

  dbms_output.put_line(v_name || ',' || v_trans_no || ',' || v_app_code);
end;

--3. 自定义记录类型
declare
  -- 自定义一个记录类型
  type order_info is record(
  v_name varchar2(50),
  v_trans_no varchar2(50),
  v_app_code varchar2(50));
  --声明自定义记录类型的变量
  v_tmp_record order_info;
begin
  select t.user_name, t.pay_brh_trans_no, t.app_code
         into v_tmp_record
  from pay_mer_order t
  where t.id=3530816;

  dbms_output.put_line(v_tmp_record.v_name || ',' || v_tmp_record.v_trans_no || ',' || v_tmp_record.v_app_code);
end;

-- 4. 使用%type定义变量，动态的获取数据的声明类型
declare
  -- 定义一个记录类型
  type order_info is record(
  v_name pay_mer_order.user_name%type,
  v_trans_no pay_mer_order.pay_brh_trans_no%type,
  v_app_code pay_mer_order.app_code%type);
  --声明自定义记录类型的变量
  v_tmp_record order_info;
begin
  select t.user_name, t.pay_brh_trans_no, t.app_code
         into v_tmp_record
  from pay_mer_order t
  where t.id=3530816;

  dbms_output.put_line(v_tmp_record.v_name || ',' || v_tmp_record.v_trans_no || ',' || v_tmp_record.v_app_code);
end;

-- 5. 使用%rowtype定义变量，动态的获取数据的声明类型
declare
  -- 声明一个记录类型的变量
  v_tmp_record pay_mer_order%rowtype;
begin
  select t.*
         into v_tmp_record
  from pay_mer_order t
  where t.id=3530816;

  dbms_output.put_line(v_tmp_record.user_name || ',' || v_tmp_record.pay_brh_trans_no || ',' || v_tmp_record.app_code);
end;

-- 6. 赋值语句：通过变量实现查询语句
declare
  -- 声明一个记录类型的变量
  v_tmp_record pay_mer_order%rowtype;
  v_order_info_id pay_mer_order.id%type;
begin
    v_order_info_id := 3530816;
    select t.*
         into v_tmp_record
    from pay_mer_order t
    where t.id=v_order_info_id;

    dbms_output.put_line(v_tmp_record.user_name || ',' || v_tmp_record.pay_brh_trans_no || ',' || v_tmp_record.app_code);
end;

--7. 通过变量实现DELET, INSERT, UPDATE等操作
declare
  -- 声明一个记录类型的变量
  v_order_info_id pay_mer_order.id%type;
begin
    v_order_info_id := 3530816;
    delete
    from pay_mer_order t
    where t.id=v_order_info_id;
    commit;
end;

--8. 使用IF... THEN ... ELSIF ...THEN...ELSE...END IF;
--要求: 查询出 150 号 员工的工资, 若其工资大于或等于 10000 则打印 'salary >= 10000';  若在 5000 到 10000 之间, 则打印 '5000<= salary < 10000'; 否则打印 'salary < 5000'
 --(方法一)
declare
    v_salary employees.salary%type;
begin
    --通过 select ... into ... 语句为变量赋值
    select salary into v_salary
    from employees
    where employee_id = 150;
    dbms_output.put_line('salary: ' || v_salary);
    -- 打印变量的值
    if v_salary >= 10000 then
        dbms_output.put_line('salary >= 10000');
    elsif v_salary >= 5000 then
        dbms_output.put_line('5000 <= salary < 10000');
    else     dbms_output.put_line('salary < 5000');
    end if;
--(方法二)
declare
    v_emp_name employees.last_name%type;
    v_emp_sal employees.salary%type;
    v_emp_sal_level varchar2(20);
begin
    select last_name,salary into v_emp_name,v_emp_sal
    from employees
    where employee_id = 150;

    if(v_emp_sal >= 10000) then
        v_emp_sal_level := 'salary >= 10000';
    elsif(v_emp_sal >= 5000) then
        v_emp_sal_level := '5000<= salary < 10000';
    else v_emp_sal_level := 'salary < 5000';
    end if;

    dbms_output.put_line(v_emp_name||','||v_emp_sal||','||v_emp_sal);
end;  

--9. 使用 CASE ... WHEN ... THEN ...ELSE ... END 完成上面的任务
declare
    v_sal employees.salary%type;
    v_msg varchar2(50);
    begin
    select salary into v_sal
        from employees
        where employee_id = 150;
    --case 不能向下面这样用
    /*
    case v_sal
        when salary >= 10000 then
            v_msg := '>=10000'
        when salary >= 5000 then
            v_msg := '5000<= salary < 10000'
        else v_msg := 'salary < 5000'
    end;
    */
    v_msg :=
        case trunc(v_sal / 5000)
            when 0 then 'salary < 5000'
            when 1 then '5000<= salary < 10000'
            else 'salary >= 10000'
        end;
    dbms_output.put_line(v_sal ||','||v_msg);
end; 

 --10. 使用 CASE ... WHEN ... THEN ... ELSE ... END;
 --要求: 查询出 122 号员工的 JOB_ID, 若其值为 'IT_PROG', 则打印 'GRADE: A';  'AC_MGT', 打印 'GRADE B',  'AC_ACCOUNT', 打印 'GRADE C';  否则打印 'GRADE D'
declare
--声明变量
    v_grade char(1);
    v_job_id employees.job_id%type;
begin
    select job_id into v_job_id
        from employees
        where employee_id = 122;
    dbms_output.put_line('job_id: ' || v_job_id);
    --根据 v_job_id 的取值, 利用 case 字句为 v_grade 赋值
v_grade :=
    case v_job_id
        when 'IT_PROG' then 'A'
        when 'AC_MGT' then 'B'
        when 'AC_ACCOUNT' then 'C'
        else 'D'
    end;
    dbms_output.put_line('GRADE: ' || v_grade);
end;

--11. 使用循环语句打印1-100.（三种方式）
--a. LOOP...EXIT WHEN ... END LOOP
declare
    v_i number(3) :=1;
begin
    loop
        dbms_output.put_line(v_i);
        exit when v_i = 100;
        v_i := v_i + 1;
    end loop;
end;
--b. WHILE...LOOP...END LOOP
declare
    v_i number(3) := 1;
    while v_i <= 100 loop
        dbms_output.put_line(v_i);
        v_i := v_i + i;
    end loop;
end;
--c. FOR...IN...LOOP
begin
    for i in 1..100 loop
        dbms_oupput_put_line(i);
    end loop;
end;

--12. 综合使用if, while语句，打印1-100之间的所有素数
--（素数：有且公有两个正约数的整数，2， 3， 5，7，11，13...）
declare
    v_i number(3) := 2;
    v_j number(3) := 2;
    v_flag number(1) := 0;
begin
    while v_i < 101 loop
        v_j := 2;
        while v_j < v_i loop
            if(mod(v_i, v_j) = 0) then
                v_flag := 1;
            end if;
            exit when v_flag = 1;
        end loop;
        if(v_flag = 0) then
            dbms_output.put_line(v_i);
        end if;
        v_i := v_i + 1;
    end loop;
end;

--13. 使用for in实现12
declare
    v_flag number(1) := 0;
begin
    for i in 2..100 loop
        for j in 2...i loop
            if(mod(i, j) = 0) then
                v_flag := 1;
            end if;
            exit when v_flag = 1;
        end loop;
        if(v_flag = 0) then
            dbms_output.put_line(i);
        end if;
    end loop;
end;

--14. goto
declare
    v_flag number(1) := 0;
begin
    for i in 2..100 loop
        v_flag := 1;
        for j in 2..sqrt(i) loop
            if i mod j = 0 then
                v_flag := 0;
                goto label;
            end if;
        end loop;
        <<label>>
        if v_flag = 1 then
            dbms_output.put_line(i);
        end if;
    end loop;
end;

---------------------------------------------------------------------------- 

--游标的使用
--游标1. 打印出80部门的所有的员工的工资
declare
    --a. 定义游标
    cursor salary_cursor is select salary from employees where department_i = 80;
    v_salary employees.salary%type;
begin
    --b. 打开游标
    open salary_cursor;

    --c. 提取游标
    fetch salary_cursor into v_salary;

    --d. 对游标进行循环操作：判断游标中是否有下一条记录
    while salary_cursor%found loop
        dbms_output.put_line('salary: ' || v_salary);
        fetch salary_cursor into v_salary;
    end loop;

    --e. 关闭游标
    close salary_cursor;
end;

--游标2. 打印出80部门的所有员工的工资：Xxx's salary is: xxx
declare
    cursor salary_cursor is select e.salary, e.last_name from employees e where department_i = 80;
    v_sal number(10);
    v_name varchar2(20);
begin
    open salary_cursor;
    fetch salray_cursor into v_sal, v_name;
    while salary_cursor%found loop
        dbms_output.put_line(v_name || '、s salary is: ' || v_sal);
        fetch salary_cursor into v_sal, v_name;
    end loop;
    close salary_cursor;
end;
--游标3：打印同manage_id为100的员工的last_name, email, salary信息（使用游标，记录类型）
declare
    type v_emplyee is record(
        name emplyees.last_name%type,
        mail emplyees.email%type,
        sal emplyees.salary%type);

    v_employee_info v_employee;
    cursor salary_cursor is select e.last_name, e.email, e.salary from employees e where e.manage_id = 100;
begin
    open salary_cursor;
    fetch salary_cursor into v_emplyeee_info;
    while salary_cursor%found loop
        dbms_output.put_line(v_employee_info.name || '、s salary is: ' || v_employee_info.mail ', email is: ' || v_employee_info.mail);
        fetch salary_cursor into v_employee_info;
    end loop;
    close salary_cursor;
end;
--游标4：使用for .. in
declare
    type v_emplyee is record(
        name emplyees.last_name%type,
        mail emplyees.email%type,
        sal emplyees.salary%type);

    v_employee_info v_employee;
    cursor salary_cursor is select e.last_name, e.email, e.salary from employees e where e.manage_id = 100;
begin
    for v_employee_info in salary_cursor loop
        dbms_output.put_line(v_employee_info.name || '、s salary is: ' || v_employee_info.mail ', email is: ' || v_employee_info.mail);
    end loop;
end;
--游标5：利用游标，调整公司中员工的工资
--0~5000 5%, 5000~10000 3%, 10000~15000 2%, 150~ 1%
declare
    salary_info employees&rowtype
    cursor salary_adjust_cursor is select e.* from employees
    v_adjust number(4, 2);
begin
    for salary_info in salary_adjust_cursor loop
        if(salary_info.salary <= 5000) then
            v_adjust := 0.05;
        elsif(salary_info.salary <= 10000) then
            v_adjust := 0.03;
        elsif(salary_info.salary <= 15000) then
            v_adjust := 0.02;
        else
            v_adjust := 0.01;
        end if;
        update employees e set e.salary=(s.salary + e.salary * v_adjust) where t.id = salary_info.id;
    end loop;
end;

--游标6：带参数的游标
declare
    salary_info employees&rowtype
    cursor salary_adjust_cursor(dept_id number, sal number) is
        select salary + 1000 sal, employee_id id from employees e
        where t.department_id = dept_id and salary > sal;
    v_adjust number(4, 2);
begin
    for salary_info in salary_adjust_cursor(sal => 4000, dept_id => 80) loop
        if(salary_info.salary <= 5000) then
            v_adjust := 0.05;
        elsif(salary_info.salary <= 10000) then
            v_adjust := 0.03;
        elsif(salary_info.salary <= 15000) then
            v_adjust := 0.02;
        else
            v_adjust := 0.01;
        end if;
        update employees e set e.salary=(s.salary + e.salary * v_adjust) where t.id = salary_info.id;
    end loop;
end;

--游标7：隐式游标，更新指定员工salary涨10%,如果该员工没找到，则打印“查无此人”
begin
    update employees e set salary = salary + salary * 0.1
        where e.employees_id = 1055;
    if sql%notfound then
        dbms_output.put_line('查无此人！');
    end if;
end;
---------------------------------------------------------------------------- 

--异常处理1
declare
    v_sal employees.salary%type;
begin
    select salary into v_sal
        from employees e where e.employee_id > 100;
    dbms_output.put_line(v_sal);
    exception when Too_many_rows then dbms_output.put_line('输出的行数太多了');
end;

--非预定义异常2
declare
    v_sal employees.salary%type;
    --声明一个异常
    delete_mgr_excep exception;
    --把自定义的异常和oracle的错误关联起来
    PRAGMA EXCEPTION INIT(delete_mgr_excep, -2292);
begin
    delete from employees e where e.employee_id = 100;

    select salary into v_sal
    from employees where employee_id > 100;
    dbms_output.put_line(v_sal);

    exception
        when Too_many_rows then dbms_output.put_line('输出的行数太多了');
        when delete_mgr_excep then dbms_output.put_line('Manager不能直接被删除');
end;

--用户自定义异常3
declare
    v_sal employees.salary%type;
    --声明一个异常
    delete_mgr_excep exception;
    --把自定义的异常和oracle的错误关联起来
    PRAGMA EXCEPTION INIT(delete_mgr_excep, -2292);
    --声明一个异常
    too_high_sal exception;
begin
    select salary into v_sal
    from employees where employee_id > 100;
    dbms_output.put_line(v_sal);

    if(v_sql > 1000) then
        raise too_high_sal;
    end if;

    exception
        when Too_many_rows then dbms_output.put_line('输出的行数太多了');
        when delete_mgr_excep then dbms_output.put_line('Manager不能直接被删除');
        --处理异常
        when too_high_sal then dbms_output.put_line('工资过高了');
end;

--异常的基本程序4
declare
    v_sal employees.salary%type;
begin
    select salary into v_sal from employees where employee_id=1000;
    dbms_output.put_line('salary: ' || v_sal);
    exception when No_data_found then dbms_output.put_line('未找到数据');
end;
----------------------------------------------------------------------------
--存储过程和函数
--存储函数：有返回值，创建完成后，通过select function() from dual;执行
--存储过程：由于没有返回值，创建完成后，不能使用select语句，只能使用pl/sql块执行
--函数的声明（有参数的写在小括号里）
create or replace function func_name(v_param varchar2)
--返回值类型
return varchar2 is
--PL/SQL块变量、记录类型、游标的声明（类似于前面的declare的部分）
begin
--函数体（可以实现crud操作，返回值需要return)
return 'hello world' || v_param;
end;

--存储函数1：helloworld
create or replace function hello_func
return varchar2 is
begin
    return 'hello world';
end;
--执行函数
begin
    dbms_output.put_line(hello_func());
end;
--或者
select hello_func() from dual;

--存储函数2：参数输入
create or replace function hello_func(v_logo varchar2)
return varchar2
is
begin
    return 'hello world ' || v_logo;
end;
--存储函数3：使用OUT型的参数，因为函数只能有一个返回值，PL/SQL程序可以通过OUT型的参数实现多个返回值
--要求: 定义一个函数: 获取给定部门的工资总和 和 该部门的员工总数(定义为 OUT 类型的参数).
--要求: 部门号定义为参数, 工资总额定义为返回值.
create or replace function sum_sql(dept_id number, total_count out number)
    return number
is
    cursor sal_cursor is select salary from employees
        where department_id = dept_id;
    v_sum_sal number(8) := 0;
begin
    total_count := 0;
    for c in sal_cursor loop
        v_sum_sal := v_sum_sal + c.salary;
        total_count := total_count + 1;
    end loop;
return v_sum_sal;
end;
--执行函数
declare v_total number(3) := 0;
begin
    dbms_output.put_line(sum_sal(80, v_total));
    dbms_output.put_line(v_total);
end;
---------------------------------------------------------------------------- 

--触发器1：helloworld触发器
create or replace trigger hello_trigger
    after update on employees
begin
    dbms_output.put_line('hello world..');
end;
--在执行以下更新语句之后会打出hello world
update employees set salary = salary + 1000;
--触发嚣2：行触发器
create or replace trigger employees_trigger
    after update on employees for each row
begin
    dbms_output.put_line('修改了一条记录');
end;
--触发嚣2：语句级触发器：一个update/delete/insert语句只使触发器执行一次
create or replace trigger employees_trigger
    after update on employees
begin
    dbms_output.put_line('修改了一条记录');
end;
--触发嚣3：使用:new, :old修饰符
create or replace trigger employees_trigger
    after update on employees for each row
begin
    dbms_output.put_line('old salary: ' || :old.salary || ', new salary: ' || :new.salary);
end;