PLSQL

1、使用多个变量

declare
  --声明变量
  v_name varchar2(25);
  v_email varchar2(25);
  v_salary number(8, 2);
  v_job_id varchar2(10);
begin
  --通过 select ... into ... 语句为变量赋值
  --被赋值的变量与SELECT中的列名要一一对应
 select last_name, email, salary, job_id into v_name, v_email, v_salary, v_job_id
 from employees
 where employee_id = 186;

 -- 打印变量的值
 dbms_output.put_line(v_name || ', ' || v_email || ', ' ||  v_salary || ', ' ||  v_job_id);
end;

2、使用 %type 定义变量，动态的获取数据的声明类型

declare
  --定义一个记录类型
  type emp_record is record(
    v_name employees.last_name%type,
    v_email employees.email%type,
    v_salary employees.salary%type,
    v_job_id employees.job_id%type);
    
  --声明自定义记录类型的变量
  v_emp_record emp_record;
begin
  --通过 select ... into ... 语句为变量赋值
  select last_name, email, salary, job_id into v_emp_record
  from employees
  where employee_id = 186;

  -- 打印变量的值
  dbms_output.put_line(v_emp_record.v_name || ', ' || v_emp_record.v_email);
end;

3、赋值语句：通过变量实现查询语句

declare
  v_emp_record employees%rowtype;
  v_employee_id employees.employee_id%type;
begin
  --使用赋值符号位变量进行赋值
  v_employee_id := 186;

  --通过 select ... into ... 语句为变量赋值
 select * into v_emp_record
 from employees
 where employee_id = v_employee_id;

 -- 打印变量的值
 dbms_output.put_line(v_emp_record.last_name || ', ' || v_emp_record.email );
end;

4、使用循环语句打印 1 - 100.（三种方式）

--1).  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;

2). WHILE ... LOOP ... END LOOP
declare
       --初始化条件
       v_i number(3) := 1;
begin
       --循环条件
       while v_i <= 100 loop
	     --循环体
             dbms_output.put_line(v_i);
	     --迭代条件
             v_i := v_i + 1;
       end loop;
end; 

3).
begin
       for i in 1 .. 100 loop
             dbms_output.put_line(i);
       end loop;
end;

5、创建一个存储函数，返回当前的系统时间

create or replace function func1
return date
is
--定义变量
v_date date;
begin
	--函数体
	--v_date := sysdate;
    select sysdate into v_date from dual;
    dbms_output.put_line('我是函数哦');
    return v_date;

end;

6、定义带参数的函数: 两个数相加

create or replace function add_func(a number, b number)
return number
is
begin
  return (a + b);
end;

执行函数

begin
  dbms_output.put_line(add_func(12, 13));
end;
--或者
  --select add_func(12,13) from dual;

7、定义一个存储过程: 获取给定部门的工资总和(通过 out 参数), 要求:部门号和工资总额定义为参数

create or replace procedure sum_sal_procedure(dept_id number, v_sum_sal out number)
       is
       cursor sal_cursor is select salary from employees where department_id = dept_id;

begin
       v_sum_sal := 0;
       for c in sal_cursor loop
       --dbms_output.put_line(c.salary);
       v_sum_sal := v_sum_sal + c.salary;
   end loop;       

   dbms_output.put_line('sum salary: ' || v_sum_sal);

end;
--[执行]
declare 
     v_sum_sal number(10) := 0;
begin
     sum_sal_procedure(80,v_sum_sal);
end;

8、自定义一个存储过程完成以下操作:
对给定部门(作为输入参数)的员工进行加薪操作, 若其到公司的时间在 (? , 95) 期间, 为其加薪 %5
[95 , 98) %3
[98, ?) %1
得到以下返回结果: 为此次加薪公司每月需要额外付出多少成本(定义一个 OUT 型的输出参数).

create or replace procedure add_sal_procedure(dept_id number, temp out number)
is
   cursor sal_cursor is select employee_id id, hire_date hd, salary sal from employees where department_id = dept_id;
   a number(4, 2) := 0;

begin
   temp := 0;       
   for c in sal_cursor loop
       a := 0;    

if c.hd < to_date('1995-1-1', 'yyyy-mm-dd') then
      a := 0.05;
   elsif c.hd < to_date('1998-1-1', 'yyyy-mm-dd') then
      a := 0.03;
   else
      a := 0.01;
   end if;

   temp := temp + c.sal * a;
   update employees set salary = salary * (1 + a) where employee_id = c.id;

   end loop;       

end;

9、触发器的 helloworld: 编写一个触发器, 在向 emp 表中插入记录时, 打印 ‘helloworld’

create or replace trigger emp_trigger
after 
insert on emp
for each row
begin
       dbms_output.put_line('helloworld');
end;

10、行级触发器: 每更新 employees 表中的一条记录, 都会导致触发器执行

create or replace trigger employees_trigger
after 
update on employees
for each row
begin
       dbms_output.put_line('修改了一条记录!');
end;

11、语句级触发器: 一个 update/delete/insert 语句只使触发器执行一次

create or replace trigger employees_trigger
after 
update on employees
begin
       dbms_output.put_line('修改了一条记录!');
end;

12、使用 :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;

13、编写一个触发器, 在对 my_emp 记录进行删除的时候, 在 my_emp_bak 表中备份对应的记录

1). 准备工作:

create table my_emp as select employee_id id, last_name name, salary sal from employees
create table my_emp_bak as select employee_id id, last_name name, salary sal from employees where 1 = 2

2).

create or replace trigger bak_emp_trigger
       before delete on my_emp
       for each row
       
begin
       insert into my_emp_bak values(:old.id, :old.name, :old.sal);
end;