PLSQL语法&&游标&&存储过程/存储函数&&异常&&触发器

版权声明:本文为博主原创文章,转载请注明出处。 https://blog.csdn.net/rocky_03/article/details/60769684

什么是PL/SQL?

结构化查询语言(Structured Query Language,简称SQL)是用来访问关系型数据库一种通用语言,属于第四代语言(4GL),其执行特点是非过程化,即不用指明执行的具体方法和途径,而是简单地调用相应语句来直接取得结果即可。显然,这种不关注任何实现细节的语言对于开发者来说有着极大的便利。然而,有些复杂的业务流程要求相应的程序来描述,这种情况下4GL就有些无能为力了。PL/SQL的出现正是为了解决这一问题,PL/SQL是一种过程化语言,属于第三代语言,它与C、 C++、Java等语言一样关注于处理细节,可以用来实现比较复杂的业务逻辑。

1. 变量命名规则

这里写图片描述

2. helloworld

declare
    --声明的变量,类型,游标
begin
    --程序处理部分(类似于java的main()方法)
    dbms_output.put_line('helloworld');
exception
    --针对于begin块中处理的异常,提供处理机制
    -- when ... then ...
    -- when ... then ...
end;

输出:

helloworld

3. 简单的查询操作

declare
    --声明变量
v_sal number(10,2);
-- v_sal employees.salary%type;
v_email varcgar2(20);
-- v_email employees.email%type;
V_hire_date date;
-- v_hire_date employees.hire_date%type;
begin
    -- sql语句的操作
    select salary,email,hire_date, into v_sal,v_email,v_hire_date
    from employees where employee_id=100;
    dbms_output.put_line(v_sal||','||v_email||','||v_hire_date);

3.1 创建一个对象,存放需要查询的值作为成员变量

type [对象名] is record(
v_xxx [类型]
)

declare
    --声明变量
    type emp_record is record(
    v_sal number(10,2),
    v_email varcgar2(20),
    v_hire_date date
    );
    --定义 一个记录类型的成员变量
    v_emp_record emp_record;

begin
    -- sql语句的操作
    select salary,email,hire_date, into v_emp_record
    from employees where employee_id=100;
    dbms_output.put_line(v_emp_record.v_sal||','||v_emp_record.v_email||','||v_emp_record.v_hire_date);

3.2 使用 %rowtype

declare
--声明一个记录类型的变量
  v_emp_record employees%rowtype;
begin
  --通过 select ... into ... 语句为变量赋值
 select * into v_emp_record
 from employees
 where employee_id = 186;

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

3.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 || ', ' ||  
                                        v_emp_record.salary || ', ' ||  v_emp_record.job_id  || ', ' ||  
                                        v_emp_record.hire_date);
end;

3.4 通过变量实现DELETE、INSERT、UPDATE等操作

declare
  v_emp_id employees.employee_id%type;

begin
  v_emp_id := 109;
  delete from employees
  where employee_id = v_emp_id;
  --commit;
end; 

4.流程控制

4.1 条件判断(两种)

方式一: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;

方式二:case … when … then … 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;

要求:

查询出 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; 

4.2 循环结构(三种)

使用循环语句打印 1 - 100.(三种方式

方式一: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;

方式二: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;

方式三for i in … loop … end loop;

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

练习

输出100以内的素数

declare
  v_i number(3) :=2;
  v_j number(2) :=2;
  --标记值, 若为 1 则是素数, 否则不是
  v_flg number(1):=1;
begin
  while(v_i <= 100) loop
         while(v_j < sqrt(v_i)) loop
                   if(mod(v_i,v_j)=0) then 
                      v_flg :=0;
                   end if;
               v_j := v_j+1; 
         end loop;
         if (v_flg=1) then 
           dbms_output.put_line(v_i);
         end if;
         v_i :=v_i+1;
         v_flg := 1;
         v_j:=2;
  end loop;
end;

输出:

SQL> /
2
...........
97
PL/SQL procedure successfully completed

4.3goto、exit

goto

同样拿素数来举列子

declare
v_flg number(1):=0;
begin 
  for i in 2 .. 100 loop
    v_flg := 1;
    for j in 2 .. sqrt(i) loop
      if mod(i,j)=0 then 
        v_flg :=0;
        -- use lable
        goto label;
      end if;
    end loop;

    <<label>>
    if v_flg = 1 then
      dbms_output.put_line(i);
    end if;

  end loop;
end;

exit

相当于 Java中个break,跳出循环
举例说明:
打印1——100的自然数,当打印到50时,跳出循环,输出“打印结束”

begin
  for i in 1..100 loop
      dbms_output.put_line(i);
      if(i mod 50 = 0) then 
      dbms_output.put_line('打印结束');
      -- 跳出循环
      exit;
      end if;
  end loop;
end;

5. 游标

类似于Java的Iterator

  • 定义游标:cursor [游标名] is select XXX
  • 打开游标:open [游标名];
  • 提取游标:fetch [游标名] into [变量名];
  • 获得游标下一个:[游标名]%found
  • 关闭游标:close [游标名]

练习1:

打印出 80 部门的所有的员工的工资:salary: xxx

declare
  --1. 定义游标
  cursor salary_cursor is select salary from employees where department_id = 80;
  v_salary employees.salary%type;
begin
 --2. 打开游标
 open salary_cursor;

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

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

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

练习2:

打印出 manager_id 为 100 的员工的 employee_id,last_name, salary 信息(使用游标, 记录类型)

declare
cursor emp_cursor is select employee_id,last_name,salary from employees where department_id = 80;
type emp_record is record(
     id employees.employee_id%type,
     name employees.last_name%type,
     salary employees.salary%type
);

v_emp_record emp_record;
begin
  open emp_cursor;
  fetch emp_cursor into v_emp_record;
  while(emp_cursor%found) loop
       dbms_output.put_line('id:'||v_emp_record.id||' name: ' || v_emp_record.name||' salary:'||v_emp_record.salary);
       fetch emp_cursor into v_emp_record;
  end loop;
end;

练习3

利用游标, 调整公司中员工的工资:

工资范围       调整基数
0 - 5000       5%
5000 - 10000   3%
10000 - 15000  2%
15000 -        1%
declare
    --定义游标
    cursor emp_sal_cursor is select salary, employee_id from employees;

    --定义基数变量
    v_temp number(4, 2);

    --定义存放游标值的变量
    v_sal employees.salary%type;
    v_id employees.employee_id%type;
begin
    --打开游标
    open emp_sal_cursor;

    --提取游标
    fetch emp_sal_cursor into v_sal, v_id;

    --处理游标的循环操作
    while emp_sal_cursor%found loop
          --判断员工的工资, 执行 update 操作
          --dbms_output.put_line(v_id || ': ' || v_sal);

          if v_sal <= 5000 then
             v_temp := 0.05;
          elsif v_sal<= 10000 then
             v_temp := 0.03;   
          elsif v_sal <= 15000 then
             v_temp := 0.02;
          else
             v_temp := 0.01;
          end if;

          update employees set salary = salary * (1 + v_temp) where employee_id = v_id; 

          fetch emp_sal_cursor into v_sal, v_id;
    end loop;
    --关闭游标
    close emp_sal_cursor;
end;

利用 for 循环遍历 游标

使用 for 遍历游标的话就不用再打开游标或者关闭游标了,相关操作会自动进行

练习4

同样用上述练习3的例子

declare
    --定义游标
    cursor emp_sal_cursor is select salary, employee_id id from employees;

    --定义基数变量
    v_temp number(4, 2);
begin
    --处理游标的循环操作
    for c in emp_sal_cursor loop
          --判断员工的工资, 执行 update 操作

          if c.salary <= 5000 then
             v_temp := 0.05;
          elsif c.salary <= 10000 then
             v_temp := 0.03;   
          elsif c.salary <= 15000 then
             v_temp := 0.02;
          else
             v_temp := 0.01;
          end if;

          --dbms_output.put_line(v_id || ': ' || v_sal || ', ' || temp);
          update employees set salary = salary * (1 + v_temp) where employee_id = c.id;
    end loop;
end;

带参数的游标

同样用上述例子说明:


declare
    --定义游标
    cursor emp_sal_cursor(dept_id number, sal number) is 
           select salary + 1000 sal, employee_id id 
           from employees 
           where department_id = dept_id and salary > sal;

    --定义基数变量
    v_temp number(4, 2);
begin
    --处理游标的循环操作sal => 4000表示4000赋值给sal,这个是形参变量的复制操作,不是比较运算
    for c in emp_sal_cursor(sal => 4000, dept_id => 80) loop
          --判断员工的工资, 执行 update 操作
          --dbms_output.put_line(c.id || ': ' || c.sal);

          if c.sal <= 5000 then
             v_temp := 0.05;
          elsif c.sal <= 10000 then
             v_temp := 0.03;   
          elsif c.sal <= 15000 then
             v_temp := 0.02;
          else
             v_temp := 0.01;
          end if;

          update employees set salary = salary * (1 + v_temp) where employee_id = c.id;
    end loop;
end;

6.异常的处理(三种)

预定义异常

在预定义异常的表中能找到的异常

练习1

declare

  v_sal employees.salary%type;
begin
  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('输出的行数太多了');
end;

非预定义异常

在预定义异常表中没有的

练习2

declare

  v_sal employees.salary%type;
  --声明一个异常
  delete_mgr_excep exception;
  --把自定义的异常和oracle的错误代码号关联起来
  PRAGMA EXCEPTION_INIT(delete_mgr_excep,-2292);
begin
  delete from employees
  where 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;

用户自定义异常

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;

  if v_sal > 1000 then
    -- 满足条件时,抛出异常
     raise too_high_sal;
  end if;

  delete 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不能直接被删除');
  --处理异常
  when too_high_sal then dbms_output.put_line('工资过高了');
end;

练习

更新指定员工工资,如工资小于300,则加100;对 NO_DATA_FOUND 异常, TOO_MANY_ROWS 进行处理.

declare
   v_sal employees.salary%type;
begin
   select salary into v_sal from employees where employee_id = 100;

   if(v_sal < 300) then update employees set salary = salary + 100 where employee_id = 100;
   else dbms_output.put_line('工资大于300');
   end if;
exception
   when no_data_found then dbms_output.put_line('未找到数据');
    when too_many_rows then dbms_output.put_line('输出的数据行太多');
end;

7. 存储函数(又返回值),存储过程(无返回值)

  • 存储函数:有返回值,创建完成后,通过select function() from dual;执行
  • 存储过程:由于没有返回值,创建完成后,不能使用select语句,只能使用pl/sql块执行

存储函数

[格式]

--函数的声明(有参数的写在小括号里)
create or replace function func_name(v_param varchar2)
    --返回值类型
    return varchar2
is 
--PL/SQL块变量、记录类型、游标的声明(类似于前面的declare的部分)
begin
    --函数体(可以实现增删改查等操作,返回值需要return)
       return 'helloworld'|| v_logo;
end;

练习1

写一个返回hellorle字符串的的函数

create or replace function fun1 
return varchar2
is
begin 
  return 'helloworld';
end;

练习2

传入一个两数字,然后返回他们的和

create or replace function fun2(a number,b number) 
return number
is 
v_sum number :=0;
begin
 v_sum := a + b; 
 return v_sum;
end;

调用函数:

SQL> select fun2(1,2) from dual;
 FUN2(1,2)
----------
         3

练习3

定义一个函数: 获取给定部门的工资总和, 要求:部门号定义为参数, 工资总额定义为返回值.

create or replace function sum_sal(dept_id number )
return number
is
cursor sal_cursor is select salary from employees where department_id = dept_id;
v_sum number(8):=0;
begin
  for c in sal_cursor loop
    v_sum :=v_sum + c.salary; 
  end loop;
  return v_sum;

end;

输出;

SQL> select sum_sal(80) from dual;
SUM_SAL(80)
-----------
     305300

OUT 型的参数

因为函数只能有一个返回值, PL/SQL 程序可以通过 OUT 型的参数实现有多个返回值
例子:
定义一个函数: 获取给定部门的工资总和 和 该部门的员工总数(定义为 OUT 类型的参数).
要求: 部门号定义为参数, 工资总额定义为返回值.

create or replace function sum_sal2(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 
-- 该变量接受的值就是total_count
  v_total number(3) := 0;

begin
    dbms_output.put_line(sum_sal2(80, v_total));
    dbms_output.put_line(v_total);
end;

输出:

SQL> /
305300
34
PL/SQL procedure successfully completed

存储过程

定义一个存储过程: 获取给定部门的工资总和(通过 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(8):=0;
begin
  sum_sal_procedure(80,v_sum_sal);
  dbms_output.put_line('sum salary: ' || v_sum_sal);

end;

输出:

SQL> /
sum salary: 305300
PL/SQL procedure successfully completed

例子3

自定义一个存储过程完成以下操作:
对给定部门(作为输入参数)的员工进行加薪操作, 若其到公司的时间在 (? , 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 = c.sal * (1 + a) where employee_id = c.id;
       end loop;       
end;

调用该函数:

declare 

v_cost number(8) :=0;
begin
  add_sal_procedure(80,v_cost);
  dbms_output.put_line('cost is :'||v_cost);
end;

测试结果:

cost is :6129
PL/SQL procedure successfully completed

8. 触发器

触发事件

触发器的实例

首先创建一个表

create table emp1(
emp_id number(3),
emp_name varchar2(6),
emp_age number(3)
)

然后定义一个触发器

create or replace trigger emp1_insert_trigger
-- after表示执行之后触发
after
insert on emp1
begin
  dbms_output.put_line('emp1 insert new column!!!');
end;

执行insert操作,测试是否执行触发器

insert into emp1 values(1,'bart',22);
emp1 insert new column!!!
1 row inserted

发现触发器在插入数据之后,执行了

使用 :new, :old 修饰符

  • :new 表示旧 的值
  • :old 表示新的值
    例子说明
create or replace trigger emp1_update_trigger
-- after表示执行之后触发
after
update on emp1
for each row
begin
  dbms_output.put_line('emp1 insert new column!!!');
  dbms_output.put_line('old age: ' || :old.emp_age || ', new age: ' || :new.emp_age);
end;

测试:

SQL> update emp1 set emp_name='lisa',emp_age=24 where emp_id=1;
emp1 insert new column!!!
old age: 23, new age: 24
1 row updated

利用触发器备份表

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

县创建两张表,一个查询创建的 my_emp 另外一个空表 my_emp_bak

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

创建触发器,实现备份操作

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;

操作测试触发器

select * from my_emp;
 ID NAME                             SAL
------- ------------------------- ----------
    174 Abel                        11330.00
    175 Hutton                       9064.00
   .....
    192 Bell                         4000.00
    193 Everett                      3900.00
    194 McCain                       3200.00
     ID NAME                             SAL
------- ------------------------- ----------
    195 Jones                        2800.00
SQL> delete from my_emp where id=194;
1 row deleted

SQL> select * from my_emp_bak;
     ID NAME                             SAL
------- ------------------------- ----------
    194 McCain                       3200.00

测试表明,删除的数据确实备份在了表my_emp_bak中

没有更多推荐了,返回首页