数据库从入门到删库跑路 - - PL/SQL
1.PL/SQL(Procedure Language & Structured Query Language)
-
定义:一种高级数据库程序设计语言,该程序专门用于在各种环境下对ORACLE数据库进行访问,由于该语言集成于数据库服务器中,所以PL/SQL可以对数据进行快速高效的处理
-
PL/SQL块
- 声明部分
- 执行部分
- 异常处理部分
-
变量命名在PL/SQL中有特殊的讲究
- 程序变量 V_name
- 程序常量 C_name
- 游标变量 Name_cursor
- 异常标识 E_name
- 表类型 Name_table_type
- 表 Name_table
- 记录类型 Name_record
- SQL*PLUS 替代变量 P_name
- 绑定变量 G_name
DECLARE
// 声明部分
BEGIN
// 执行部分
EXCEPTION
// 执行异常部分
END;
其中执行部分是必须的
1.写一个简单的PL/SQL语句
a.命令行窗口处输入:set serveroutput on
b.
declare
--- 声明的变量,类型,游标
begin
--- 程序的执行部分(类似于java的main()方法)
dbms_output.put_line('Hello World');
exception
--- 针对begin块出现的异常,提供处理的机制
--- when .... then
--- when .... then
end;
2.查询100号员工的工资
DECLARE
v_sal NUMBER(10,2);
v_email VARCHAR2(20);
v_hire_date date;
BEGIN
select salary,email,hire_date into v_sal,v_email,v_hire_date from employees where employee_id=100'
dbms_output.put_lines(v_sal||','||v_email||','||v_hire_date);
END
注:在声明部分获取变量类型的方式: v_sal employees.salary%type;
使用%type特性的优点:
- 所引用的数据索引的数据类型可以不必知道
- 所引用的数据库列的数据类型可以实时改变
2.记录类型
- 定义:记录类型是把逻辑相关的数据作为一个单元存储起来,称作PL/SQL RECORD 的域(FIELD),其作用是存放互不相同但逻辑相关的信息
declare
--- 声明一个记录类型
type emp_record is record(
v_sal employees.salary%type,
v_email employees.email%type,
v_hire_date employees.hire_date%type
);
--- 定义一个记录类型
v_emp_record emp_record;
begin
---sql语句的操作: select...into...from...where...
select salary,email,hire_date into v_emp_record from employees where employee_id=100;
dbms_output.println(v_emp_record.v_sal||','||v_emp_record.v_email||','||v_emp_record.v_hiredate)
end;
eg2
declare
v_sal number(5,2):=0;
v_emp_id number(10);
begin
select salary,employee_id into v_sal,v_employee_id
from employees
where employee_id=123
dbms_output.println('employeeid:'||v_emp_id||'salary:'||v_sal);
end;
3.流程控制
- 条件判断
- if…then elsif then…else…end if;
- case…when…when…end;
- 循环结构
- loop…exit when… end loop;
- while…loop…end loop
- for i in … loop…end
- goto 相当于break,exit
1.if类型
查询出150号员工的工资,若其工资大于等于10000 则打印'salary>=10000';
若在500到10000之间,则打印'5000<=salary<10000' 否则打印'salary<5000'
declare
v_salary employees.salary%type;
begin
select salary into v_salary from employees where employee_id=150;
if v_sal>=10000 then dbms_output.put_line('salary>=100000');
elsif v_sal>=5000 then dbms_output.put_line('5000<salary<10000');
else dmbs_output.put_line('salary<5000');
end if;
end;
2.case类型(缺点:只能对离散的值有用)
eg1.
declare
v_sal employee.salary%type
v_temp varchar(30)
begin
select salary into v_sal from employees where employee_id=150
v_temp:=
case trunc(v_sal/100) when 0 then 'salary<=5000'
when 1 then '5000<=salary<10000'
else 'salary?=10000'
end;
dbms_output.put_line(v_sal||','||v_temp);
end;
eg2.
decare
v_job_id varchar2(10)
v_temp varchar2(10)
begin
select job_id into v_job_id from employee employee_id=122
v_temp:=
case v_job_id when 'IT_PROG' then 'A'
when 'AC_NGT' then 'B'
when 'AC_ACCOUNT' then 'C'
else 'D'
end;
dbms_output.put_line(v_job_id,||','|| v_temp)
end
3. loop循环
declare
v_i number(5):=1;
begin
loop
dbms_output_println(v_i);
exit when v_i>=100;
v_i=v_i+1
end loop;
end;
4. while循环
declare
v_i number(5):=1;
begin
while v_i<=100 loop
dbms_output_println(v_i);
v_i=v_i+1;
end loop;
end;
5.for循环
beign
for c in 1..100 loop
dbms_output.printline(c);
end loop;
end;
4.游标的使用(类似于java中的Iterator)
- 显示游标
- 隐式游标
打印出80部门的所有员工的工资:salary:xxx
declare
v_sal employees.salary%type
begin
--- 定义游标
cursor emp_sal_cursor is select salary from employees where department_id=80
-- 打开游标
open emp_sal_cursor
-- 提取游标
fetch emp_sal_cursor into v_sal;
while emp_sal_cursor%found loop
dbms_output.put_line('salary:'|| v_sal)
fetch emp_sal_cursor into v_sal
end loop;
-- 关闭游标
close emp_sal_cursor;
end;
5.异常的处理(三种方式)
- 异常处理:是用来处理正常执行过程中未预料的时间,程序块的异常处理预定义的错误和自定义错误和自定义错误
- 异常错误类型:
- 预定义(Predefined) 错误
- 非预定义的错误
- 用户定义错误
1. 预定义异常
declare
v_salary employees.salary%type;
begin
select salary into v_salary
from employees
where employee_id>100
dbms_output.println('查无此人')
exception
when too_many then dbms_output.println('输出的行数太多了!!');
when others then dbms_output.prinrln('出现其他类型的异常!');
end;
2.非预定异常
delete from employee
wheres where_id=100 ----产生外键异常
declare
e_deleteid_exception exception;
pragma exception_init(e_delete_id_exception,-2292);
begin
delete from employees where employee_id=100
exception
when e_deleteid_exception then dbms_output.println('违反完整性约束条件,不可删除此用户!!')
3.用户自定义异常
declare
e_too_high_sal exception;
v_sal employees.salary%type;
begin
select salary into v_sal from employees where employee_id=100;
if v_sal>10000 then
raise e_too_high_sal;
end if;
exception
when e_too_high dbms_output.println('工资太高了!!');
end;
6.存储函数(有返回值)与存储过程(无返回值)
1.存储函数基本格式
create or replace function func_name(dept_id,number,sal)
return number
is
--- 函数使用过程中,需要声明的变量,记录类型,cursor
begin
--- 函数的执行体
exception
end;
2.写一个简单的存储函数
create or replace function hello_world
return varchar2
is
begin
return 'Hello World'
end;
3.存储函数例2
create or replace function hello_word(v_logo varchar2)
return varchar2
is
begin
return 'Helloworld'||v_logo;
end;
调用:hello_world(' xiaoyao')
7.触发器
- 是由一个事件来启动运行的
- 触发事件组成:
- 触发事件
- 触发时间
- 触发器本省
- 触发频率
1. 创建一个简单的触发器
create or replace trigger update_emp_trigger
after
update on employees
for each row; // 每行执行
begin
dbms_output.println('Hello World')
end;
2.插入数据时执行触发器
create or replace trigger insert_emp_trigger
after
dinsert on employees
begin
dbms_output.println('Hello World')
end;
3.打印新的值与旧的值:old,:new