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

什么是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...........97PL/SQL procedure successfully completed

4.3goto、exit

goto

同样拿素数来举列子

declarev_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 信息(使用游标, 记录类型)

declarecursor 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 varchar2is --PL/SQL块变量、记录类型、游标的声明(类似于前面的declare的部分)begin    --函数体(可以实现增删改查等操作，返回值需要return)       return 'helloworld'|| v_logo;end;

练习1

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

create or replace function fun1 return varchar2isbegin   return 'helloworld';end;

练习2

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

create or replace function fun2(a number,b number) return numberis 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 numberiscursor 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 numberiscursor 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> /30530034PL/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: 305300PL/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 :6129PL/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表示执行之后触发afterinsert on emp1begin  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表示执行之后触发afterupdate on emp1for each rowbegin  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: 241 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 employeescreate 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_triggerbefore delete on my_emp-- 操作每一行的时候都会执行for each rowbegin 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.00SQL> delete from my_emp where id=194;1 row deletedSQL> select * from my_emp_bak;     ID NAME                             SAL------- ------------------------- ----------    194 McCain                       3200.00

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