什么是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；输出：
helloworld3. 简单的查询操作
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 completed4.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)
-----------
305300OUT 型的参数
因为函数只能有一个返回值, 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 completed8. 触发器
触发器的实例
首先创建一个表
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中
扫一扫
1619
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
