oracle复习笔记之PL/SQL程序所要知道的知识点

发布时间：2021-11-26 17:26:27 所属栏目：PHP教程来源：互联网

导读：PL/SQL的基本语法、记录类型、流程控制、游标的使用、异常处理机制、存储函数/存储过程、触发器。为方便大家跟着我的笔记练习，为此提供数据库表文件给大家下载为了要有输出的结果，在写PL/SQL程序前都在先运行这一句： set serveroutput on 结构： decla

PL/SQL的基本语法、记录类型、流程控制、游标的使用、

异常处理机制、存储函数/存储过程、触发器。

为方便大家跟着我的笔记练习，为此提供数据库表文件给大家下载

为了要有输出的结果，在写PL/SQL程序前都在先运行这一句：
set serveroutput on
结构：
declare
--声明变量、类型、游标
begin
--程序的执行部分（类似于java里的main()方法）
exception
--针对begin块中出现的异常，提供处理的机制
--when...then...
--when...then...
end;
举例1：

1
declare
2
  v_sal number(10); （注意每句话后面别忘记了分号，跟java中的一样）
3
begin
4
  select salary into v_sal from employees where employee_id = 100;
5
  dbms_output.put_line(v_sal);
6
end;
举例2：

01
declare
02
  v_sal number(10); （注意，这里声明的空间大小不能比原表中的小）
03
  v_email varchar2(20);
04
  v_hire_date date;
05
begin
06
  select salary,email,hire_date into v_sal,v_email,v_hire_date from employees where employee_id =
07
100;
08
  dbms_output.put_line(v_sal||','||v_email||','||v_hire_date);
09
end;
10
或者：
11
declare
12
  v_sal employees.salary%type;
13
  v_email employees.email%type;
14
  v_hire_date employees.hire_date%type;
15
begin
16
  select salary,email,hire_date into v_sal,v_email,v_hire_date from employees where employee_id =
17
100;
18
  dbms_output.put_line(v_sal||','||v_email||','||v_hire_date);
19
end;
记录：

01
declare
02
  type emp_record is record(
03
   v_sal employees.salary%type,
04
   v_email employees.email%type,
05
   v_hire_date employees.hire_date%type
06
  );
07
  v_emp_record emp_record;
08
begin
09
  select salary,email,hire_date into v_emp_record from employees where employee_id = 100;
10
  dbms_output.put_line(v_emp_record.v_sal||','||v_emp_record.v_email||','||
11
  v_emp_record.v_hire_date);
12
end;
1、pl/sql基本的语法格式
2、记录类型 type ... is ...record(,,,);
3、流程控制：
3.1 条件判断（两种）
方式一： if ... then elseif then ... else ... end if;
方式二： case ... when ... then ...end;
3.2 循环结构（三种）
方式一：loop ... exit when ... end loop;
方式二：while ... loop ... end loop;
方式三：for i in ... loop ... end loop;
3.3 goto、exit
4.游标的使用（类似于java中的Iterator）
5.异常的处理

6.会写一个存储函数（有返回值）、存储过程（没有返回值）
7.会写一个触发器

复习记录类型：

01
declare
02
type emp_record is record(
03
  -- v_emp_id employees.employee_id%type,
04
  -- v_sal employees.salary%type
05
  v_emp_id number(10) := 120,
06
  v_sal number(10,2) :=12000
07
);
08
  v_emp_record emp_record;
09
begin
10
  -- select employee_id,salary into v_emp_record from employees where employee_id = 123;
11
  dbms_output.put_line('employee_id:'||v_emp_record.v_emp_id||' '||'salary:'||
12
  v_emp_record.v_sal);
13
end;
也可以升级一下，要是想对表的所有列都输出，则：（须注意输出的列名要跟表中的列名要一样）

01
declare
02
  v_emp_record employees%rowtype;
03
begin
04
  select * into v_emp_record from employees where employee_id = 123;
05
  dbms_output.put_line('employee_id:'||v_emp_record.employee_id||' '||'salary:'||
06
  v_emp_record.salary);
07
end;
08
使用记录来执行update操作：
09
declare
10
  v_emp_id number(10);
11
begin
12
  v_emp_id :=123;
13
  update employees
14
  set salary = salary + 100
15
  where employee_id = v_emp_id;
16
  dbms_output.put_line('执行成功！~~');
17
end;
流程控制：
查询150号员工的工资，若其工资大于或等于10000 则打印‘salary >= 10000';
若在5000到10000之间，则打印‘5000 <= salary <10000';否则打印‘salary < 5000'

01
declare
02
  v_sal employees.salary%type;
03
begin
04
  select salary into v_sal from employees where employee_id =150;
05
  if v_sal >= 10000 then dbms_output.put_line('salary >= 10000');
06
  elsif v_sal > 5000 then dbms_output.put_line('10000 > salary >= 5000');
07
  else dbms_output.put_line('salary < 5000');
08
  end if;
09
  dbms_output.put_line('salary:'||v_sal);
10
end;
11
利用case ... when ... then ... when ...then ... else ... end实现上题;
12
declare
13
  v_sal employees.salary%type;
14
  v_temp varchar2(20);
15
begin
16
  select salary into v_sal from employees where employee_id =150;
17
  v_temp :=
18
  case trunc(v_sal/5000) when 0 then 'salary < 5000'
19
                  when 1 then '5000 <= salary < 10000'
20
                  else 'salary >= 10000'
21
                  end;
22
  dbms_output.put_line('salary:'||v_sal||' '||v_temp);
23
end;

查询出122号员工的job_id,若其值为 ‘IT_PROG', 则打印‘GRADE:A'
                                                ‘AC_MGT', 则打印‘GRADE:B'
                                                ‘AC_ACCOUNT', 则打印‘GRADE:B'
                                                 否则打印‘GRADE:D'

01
declare
02
  v_job_id employees.job_id%type;
03
  v_temp varchar2(20);
04
begin
05
  select job_id into v_job_id from employees where employee_id =122;
06
  v_temp :=
07
  case v_job_id when 'IT_PROG' then 'A'
08
            when 'AC_MGT' then 'B'
09
            when 'AC_ACCOUNT' then 'C'
10
            else 'D'
11
            end;
12
  dbms_output.put_line('job_id:'||v_job_id||' '||v_temp);
13
end;
使用循环语句打印：1-100

01
declare
02
  v_i number(5) :=1;
03

04
begin
05
  loop
06
  dbms_output.put_line(v_i);
07
  exit when v_i >=100;
08
  v_i := v_i + 1;
09
  end loop;
10
end;
11
使用while实现：
12
declare
13
  v_i number(5) :=1;
14
begin
15
  while v_i <= 100 loop
16
   dbms_output.put_line(v_i);
17
   v_i := v_i + 1;
18
  end loop;
19
end;
20
使用for...in...loop...end loop;实现：
21
begin
22
  for c in 1..100 loop
23
   dbms_output.put_line(c);
24
  end loop;
25
end;

输出2-100之间的质数

01
declare
02
  v_i number(3):= 2;
03
  v_j number(3):= 2;
04
  v_flag number(1):= 1;
05
begin
06
  while v_i<=100 loop
07
   while v_j<=sqrt(v_i) loop
08
    if mod(v_i,v_j)=0 then v_flag:=0;
09
    end if;
10
    v_j:= v_j+1;
11
   end loop;
12
  if v_flag = 1 then dbms_output.put_line(v_i);
13
  end if;
14
  v_j :=2;
15
  v_i := v_i + 1;
16
  v_flag := 1;
17
  end loop;
18
end;
利用for循环实现输出2-100之间的质数：

01
declare
02
  v_flag number(1):= 1;
03
begin
04
  for v_i in 2..100 loop
05
   for v_j in 2..sqrt(v_i) loop
06
    if mod(v_i,v_j)=0 then v_flag:=0;
07
    end if;
08
   end loop;
09
   if v_flag=1 then dbms_output.put_line(v_i);
10
   end if;
11
   v_flag := 1;
12
  end loop;
13
end;
可以用goto改进一下：

01
declare
02
  v_flag number(1):= 1;
03
begin
04
  for v_i in 2..100 loop
05
    for v_j in 2..sqrt(v_i) loop
06
     if mod(v_i,v_j)=0 then v_flag:=0;
07
     goto label;
08
     end if;
09
   end loop;
10
   <<label>>
11
   if v_flag=1 then dbms_output.put_line(v_i);
12
   end if;
13
   v_flag := 1;
14
  end loop;
15
end;
打印1-100的自然数，当打印到50时，跳出循环，输出‘打印结束'：

01
begin
02
  for i in 1..100 loop
03
   if i=50 then goto label;
04
   end if;
05
  dbms_output.put_line(i);
06
  end loop;
07
<<label>>
08
  dbms_output.put_line('打印结束');
09
end;
10
或者：
11
begin
12
  for i in 1..100 loop
13
   if i=50 then dbms_output.put_line('打印结束');
14
   exit;
15
   end if;
16
  dbms_output.put_line(i);
17
  end loop;
18
end;

游标：
打印出80部门的所有的员工的工资：salary:XXX
declare
v_sal employees.salary%type;
--定义游标
cursor emp_sal_cursor is select salary from employees where department_id = 80;
begin
--打开游标
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;
可以进行优化如下：

01
declare
02
v_empid employees.employee_id%type;
03
v_lastName employees.last_name%type;
04
v_sal employees.salary%type;
05
cursor emp_sal_cursor is select employee_id,last_name,salary from employees where
06
department_id = 80;
07
begin
08
open emp_sal_cursor;
09
fetch emp_sal_cursor into v_empid,v_lastName,v_sal;
10
while emp_sal_cursor%found loop
11
dbms_output.put_line('employee_id:'||v_empid||', '||'last_name:'||v_lastName||',
12
'||'salary:'||v_sal);
13
fetch emp_sal_cursor into v_empid,v_lastName,v_sal;
14
end loop;
15
close emp_sal_cursor;
16
end;
或者使用记录再优化一下：

01
declare
02
type emp_record is record(
03
v_empid employees.employee_id%type,
04
v_lastName employees.last_name%type,
05
v_sal employees.salary%type
06
);
07
v_emp_record emp_record;
08
cursor emp_sal_cursor is select employee_id,last_name,salary from employees where
09
department_id = 80;
10
begin
11
open emp_sal_cursor;
12
fetch emp_sal_cursor into v_emp_record;
13
while emp_sal_cursor%found loop
14
dbms_output.put_line('employee_id:'||v_emp_record.v_empid||', '||'last_name:'||
15
v_emp_record.v_lastName||', '||'salary:'||v_emp_record.v_sal);
16
fetch emp_sal_cursor into v_emp_record;
17
end loop;
18
close emp_sal_cursor;
19
end;
可以使用for循环最优化：（注意：在for循环中它会自动的打开游标、提取游标，当提取完里面的数据后也会自动
的关闭游标）

1
declare
2
cursor emp_sal_cursor is select employee_id,last_name,salary from employees where
3
department_id = 80;
4
begin
5
for c in emp_sal_cursor loop
6
dbms_output.put_line('employee_id:'||c.employee_id||', '||'last_name:'||c.last_name||',
7
'||'salary:'||c.salary);
8
end loop;
9
end;

利用游标，调整公司中员工的工资：
工资范围            调整基数
0 - 5000              5%
5000 - 10000       3%
10000 - 15000     2%
15000 -               1%
实现：

01
declare
02
  cursor emp_cursor is select employee_id,salary from employees;
03
  v_empid employees.employee_id%type;
04
  v_sal employees.salary%type;
05
  v_temp number(4,2);
06
begin
07
  open emp_cursor;
08
  fetch emp_cursor into v_empid,v_sal;
09
  while emp_cursor%found loop
10
   if v_sal < 5000 then v_temp:=0.05;
11
   elsif v_sal < 10000 then v_temp:=0.03;
12
   elsif v_sal < 15000 then v_temp:=0.02;
13
   else v_temp:=0.01;
14
   end if;
15
  dbms_output.put_line(v_empid||','||v_sal);
16
  update employees
17
  set salary = salary * (1+v_temp)
18
  where employee_id = v_empid;
19
  fetch emp_cursor into v_empid,v_sal;
20
  end loop;
21
  close emp_cursor;
22
end;
用for循环实现

01
declare
02
  cursor emp_cursor is select employee_id,salary from employees;
03
  v_temp number(4,2);
04
begin
05
  for c in emp_cursor loop
06
   if c.salary <5000 then v_temp:=0.05;
07
   elsif c.salary <10000 then v_temp:=0.03;
08
   elsif c.salary <15000 then v_temp:=0.02;
09
   else v_temp:=0.01;
10
  end if;
11
  update employees
12
  set salary = salary * (1+v_temp)
13
  where employee_id = c.employee_id;
14
  end loop;
15
end;
隐式游标：更新员工salary（涨工资10），如果该员工没有找到，则打印“查无此人”信息：

1
begin
2
  update employees
3
  set salary = salary + 10
4
  where employee_id = 1001;
5
   if sql%notfound then dbms_output.put_line('查无此人');
6
   end if;
7
end;
异常：
预定义异常：（有24个预定义异常，可查表）

01
declare
02
  v_sal employees.salary%type;
03
begin
04
  select salary into v_sal from employees
05
  where employee_id > 100;
06
  dbms_output.put_line(v_sal);
07
exception
08
  when too_many_rows then dbms_output.put_line('输出的行数过多');
09
  when others then dbms_output.put_line('出现其它的异常了');
10
end;
非预定义异常：

01
declare
02
  e_deleteid_exception exception;
03
  pragma exception_init(e_deleteid_exception,-2292);
04
begin
05
  delete from employees
06
  where employee_id = 100;
07
exception
08
  when e_deleteid_exception then dbms_output.put_line('违反了完整性约束，故不能删除此用户');
09
  when others then dbms_output.put_line('出现其它的异常了');
10
end;
用户自定义异常：

01
declare
02
  e_sal_hight exception;
03
  v_sal employees.salary%type;
04
begin
05
  select salary into v_sal from employees where employee_id = 100;
06
  if v_sal > 10000 then raise e_sal_hight;
07
  end if;
08
exception
09
  when e_sal_hight then dbms_output.put_line('工资太高了');
10
  when others then dbms_output.put_line('出现其它的异常了');
11
end;
通过select...into...查询某人的工资，若没找到则打印出“未找到此数据”：

01
declare
02
  v_sal employees.salary%type;
03
begin
04
  select salary into v_sal from employees where employee_id = 1001;
05
exception
06
  when no_data_found then dbms_output.put_line('未找到此数据');
07
  when others then dbms_output.put_line('出现其它的异常了');
08
end;
09
更新指定员工工资，如工资小于300，则加100，对NO_DATA_FOUND异常，TOO_MANY_ROWS进行处理。
10
declare
11
  v_sal employees.salary%type;
12
begin
13
  select salary into v_sal from employees where employee_id = 1001;
14
  if v_sal < 300 then update employees set salary = salary + 100 where employee_id =101;
15
  end if;
16
exception
17
  when no_data_found then dbms_output.put_line('未找到此数据');
18
  when too_many_rows then dbms_output.put_line('输出的行数太多了');
19
  when others then dbms_output.put_line('出现其它的异常了');
20
end;
自定义异常：
更新指定员工工资，增加100；若指定员工不在，则抛出异常：NO_RESULT;

01
declare
02
  no_result exception;
03
begin
04
  update employees set salary = salary + 100 where employee_id = 1001;
05
  if sql%notfound then raise no_result;
06
  end if;
07
exception
08
  when no_result then dbms_output.put_line('查无此数据,更新失败');
09
  when others then dbms_output.put_line('出现其它异常');
10
end;

存储过程：
写个简单的hello_world存储函数

01
create or replace function hello_world
02
return varchar2
03
is （相当于declare，可以在其后面定义变量、记录、游标）
04
begin
05
  return 'helloworld';
06
end;
07
存储函数的调用：
08
begin
09
  dbms_output.put_line(hello_world);
10
end;
11
或者：
12
select hello_world from dual;

带参数的存储函数：

01
create or replace function hello_world1(v_logo varchar2)
02
return varchar2
03
is
04
begin
05
  return 'helloworld'||v_logo;
06
end;
07
调用：
08
select hello_world1('shellway') from dual
09
或者：
10
begin
11
  dbms_output.put_line(hello_world1('shellway'));
12
end;

定义一个获取系统时间的函数：

1
create or replace function get_sysdate
2
return varchar2
3
is
4
begin
5
  return to_char(sysdate,'yyyy-MM-dd HH24:mi:ss');
6
end;

定义带参数的函数，两个数相加

01
create or replace function add_param(v_num1 number,v_num2 number)
02
return number
03
is
04
  v_num3 number(10);
05
begin
06
  v_num3 := v_num1 + v_num2;
07
  return v_num3;
08
end;
09
调用：
10
select add_param(2,5) from dual;
11
或者：
12
begin
13
  dbms_output.put_line(add_param(5,4));
14
end;

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

01
create or replace function get_sal(dept_id number)
02
return number
03
is
04
  v_sumsal number(10) := 0;
05
  cursor salary_cursor is select salary from employees where department_id = dept_id;
06
begin
07
  for c in salary_cursor loop
08
  v_sumsal := v_sumsal + c.salary;
09
  end loop;
10
  return v_sumsal;
11
end;
12
调用：
13
select get_sal(80) from dual;

定义一个函数：获取给定部门的工资总和和该部门的员工总数（定义为OUT类型的参数）。
要求：部门号定义为参数，工资总额定义为返回值。

01
create or replace function get_sal(dept_id number,total_count out number)
02
return number
03
is
04
v_sumsal number(10) := 0;
05
cursor salary_cursor is select salary from employees where department_id = dept_id;
06
begin
07
  total_count := 0;
08
  for c in salary_cursor loop
09
    v_sumsal := v_sumsal + c.salary;
10
    total_count := total_count + 1;
11
  end loop;
12
  return v_sumsal;
13
end;
14
调用：
15
declare
16
  v_count number(4);
17
begin
18
  dbms_output.put_line(get_sal(80,v_count));
19
  dbms_output.put_line(v_count);
20
end;

定义一个存储过程：获取给定部门的工资总和（通过out参数），要求部门号和工资总额定义为参数。
（注意：存储过程和存储函数是不一样的，存储函数有返回值而存储过程没有，调用时候存储过程直接调用）

01
create or replace procedure get_sal1(dept_id number,sumsal out number)
02
is
03
cursor salary_cursor is select salary from employees where department_id = dept_id;
04
begin
05
sumsal := 0;
06
for c in salary_cursor loop
07
   sumsal := sumsal + c.salary;
08
end loop;
09
dbms_output.put_line(sumsal);
10
end;
11
调用：
12
declare
13
  v_sal number(10):=0;
14
begin
15
get_sal1(80,v_sal);
16
end;

对给定部门（作为输入参数）的员工进行加薪操作，若其到公司的时间在（？，95）期间，为其加薪5%

                                                                                          （95，98）                  3%

                                                                                          （98，？）                   1%
得到以下返回结果：为此次加薪公司每月额外付出多少成三（定义一个OUT型的输出参数）

01
create or replace procedure add_sal(dept_id number,temp out number)
02
is
03
  cursor sal_cursor is select employee_id,salary,hire_date
04
  from employees where department_id = dept_id;
05
  v_temp number(4,2):=0;
06
begin
07
  temp := 0;
08
  for c in sal_cursor loop
09
    if to_char(c.hire_date,'yyyy') < '1995' then v_temp:=0.05;
10
    elsif to_char(c.hire_date,'yyyy') < '1998' then v_temp:=0.03;
11
    else v_temp:=0.01;
12
    end if;
13

14
  update employees
15
  set salary = salary * (1+v_temp)
16
  where employee_id = c.employee_id;
17

18
  temp := temp + c.salary*v_temp;
19
  end loop;
20
  dbms_output.put_line(temp);
21
end;
22
调用：
23
declare
24
  v_i number(10):=0;
25
begin
26
  add_sal(80,v_i);
27
end;
触发器：
触发事件：在INSERT,UPDATE,DELETE情况下会触发TRIGGER
触发时间：该TRIGGER是在触发事件发生之前(BEFORE)还是之后（AFTER）
触发器本身：该TRIGGER被触发之后的目的和意图，正是触发器本身要做的事情，如PL/SQL块
触发频率：有语句级(STATEMENT)触发器和行级(ROW)触发器
写一个简单的触发器：

1
create or replace trigger update_emp_trigger
2
after
3
  update on employees
4
for each row (行级触发器，即每更新一条记录就会输出一次'helloworld'，若没有这语句则是语句级触发器)
5
begin
6
  dbms_output.put_line('helloworld');
7
end;

使用:new,:old修饰符：

01
1、
02
create table emp1
03
as
04
select employee_id,salary,email from employees where department_id = 80;
05
2、
06
create or replace trigger update_emp_trigger2
07
after
08
  update on emp1
09
for each row
10
begin
11
  dbms_output.put_line('old salary:'||:old.salary||'new salary:'||:new.salary);
12
end;
13
3、
14
update emp1 set salary = salary + 100 ；

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

01
1、创建my_emp表：
02
create table my_emp
03
as
04
select employee_id,salary from employees ;
05
2、创建my_emp_bak表：
06
create table my_emp_bak
07
as
08
select employee_id,salary from employees where 1=2;
09
3、检查创建的表中的记录：
10
select * from my_emp
11
select * from my_emp_bak
12
4、创建一个触发器：
13
create or replace trigger delete_emp_trigger
14
before
15
  delete on my_emp
16
for each row
17
begin
18
  insert into my_emp_bak
19
  values(:old.employee_id,:old.salary);
20
end;
21
5、执行含有触发器时间的语句：
22
delete from my_emp
23
6、检查触发器执行后的结果：
24
select * from my_emp
25
select * from my_emp_bak

（编辑：云计算网_泰州站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!

php批量命名文件技巧	php读取文件内容几种正
php中读取大文件实现方	php打开远程文件的方法