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

复习内容：

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

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

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

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

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

举例2：

declare
  v_sal number(10); （注意，这里声明的空间大小不能比原表中的小）
  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_line(v_sal||','||v_email||','||v_hire_date);
end;
或者：
declare
  v_sal employees.salary%type;
  v_email employees.email%type;
  v_hire_date employees.hire_date%type;
begin
  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);
end;

记录：

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
  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);
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.会写一个触发器

复习记录类型：

declare
type emp_record is record(
  -- v_emp_id employees.employee_id%type,
  -- v_sal employees.salary%type
  v_emp_id number(10) := 120,
  v_sal number(10,2) :=12000
);
  v_emp_record emp_record;
begin
  -- select employee_id,salary into v_emp_record from employees where employee_id = 123;
  dbms_output.put_line('employee_id:'||v_emp_record.v_emp_id||' '||'salary:'||
  v_emp_record.v_sal);
end;

也可以升级一下，要是想对表的所有列都输出，则：（须注意输出的列名要跟表中的列名要一样）

declare
  v_emp_record employees%rowtype;
begin
  select * into v_emp_record from employees where employee_id = 123;
  dbms_output.put_line('employee_id:'||v_emp_record.employee_id||' '||'salary:'||
  v_emp_record.salary);
end;
使用记录来执行update操作：
declare
  v_emp_id number(10);
begin
  v_emp_id :=123;
  update employees
  set salary = salary + 100
  where employee_id = v_emp_id;
  dbms_output.put_line('执行成功！~~');
end;

流程控制：
查询150号员工的工资，若其工资大于或等于10000 则打印‘salary >= 10000';
若在5000到10000之间，则打印‘5000 <= salary <10000';否则打印‘salary < 5000'

declare
  v_sal employees.salary%type;
begin
  select salary into v_sal from employees where employee_id =150;
  if v_sal >= 10000 then dbms_output.put_line('salary >= 10000');
  elsif v_sal > 5000 then dbms_output.put_line('10000 > salary >= 5000');
  else dbms_output.put_line('salary < 5000');
  end if;
  dbms_output.put_line('salary:'||v_sal);
end;
利用case ... when ... then ... when ...then ... else ... end实现上题;
declare
  v_sal employees.salary%type;
  v_temp varchar2(20);
begin
  select salary into v_sal from employees where employee_id =150;
  v_temp :=
  case trunc(v_sal/5000) when 0 then 'salary < 5000'
                  when 1 then '5000 <= salary < 10000'
                  else 'salary >= 10000'
                  end;
  dbms_output.put_line('salary:'||v_sal||' '||v_temp);
end;

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

declare
  v_job_id employees.job_id%type;
  v_temp varchar2(20);
begin
  select job_id into v_job_id from employees where employee_id =122;
  v_temp :=
  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('job_id:'||v_job_id||' '||v_temp);
end;

使用循环语句打印：1-100

declare
  v_i number(5) :=1;

begin
  loop
  dbms_output.put_line(v_i);
  exit when v_i >=100;
  v_i := v_i + 1;
  end loop;
end;
使用while实现：
declare
  v_i number(5) :=1;
begin
  while v_i <= 100 loop
   dbms_output.put_line(v_i);
   v_i := v_i + 1;
  end loop;
end;
使用for...in...loop...end loop;实现：
begin
  for c in 1..100 loop
   dbms_output.put_line(c);
  end loop;
end;

输出2-100之间的质数

declare
  v_i number(3):= 2;
  v_j number(3):= 2;
  v_flag 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_flag:=0;
    end if;
    v_j:= v_j+1;
   end loop;
  if v_flag = 1 then dbms_output.put_line(v_i);
  end if;
  v_j :=2;
  v_i := v_i + 1;
  v_flag := 1;
  end loop;
end;

利用for循环实现输出2-100之间的质数：

declare
  v_flag number(1):= 1;
begin
  for v_i in 2..100 loop
   for v_j in 2..sqrt(v_i) loop
    if mod(v_i,v_j)=0 then v_flag:=0;
    end if;
   end loop;
   if v_flag=1 then dbms_output.put_line(v_i);
   end if;
   v_flag := 1;
  end loop;
end;

可以用goto改进一下：

declare
  v_flag number(1):= 1;
begin
  for v_i in 2..100 loop
    for v_j in 2..sqrt(v_i) loop
     if mod(v_i,v_j)=0 then v_flag:=0;
     goto label;
     end if;
   end loop;
   <<label>>
   if v_flag=1 then dbms_output.put_line(v_i);
   end if;
   v_flag := 1;
  end loop;
end;

打印1-100的自然数，当打印到50时，跳出循环 ，输出‘打印结束'：

begin
  for i in 1..100 loop
   if i=50 then goto label;
   end if;
  dbms_output.put_line(i);
  end loop;
<<label>>
  dbms_output.put_line('打印结束');
end;
或者：
begin
  for i in 1..100 loop
   if i=50 then dbms_output.put_line('打印结束');
   exit;
   end if;
  dbms_output.put_line(i);
  end loop;
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;
可以进行优化如下：

declare
v_empid employees.employee_id%type;
v_lastName employees.last_name%type;
v_sal employees.salary%type;
cursor emp_sal_cursor is select employee_id,last_name,salary from employees where
department_id = 80;
begin
open emp_sal_cursor;
fetch emp_sal_cursor into v_empid,v_lastName,v_sal;
while emp_sal_cursor%found loop
dbms_output.put_line('employee_id:'||v_empid||', '||'last_name:'||v_lastName||',
'||'salary:'||v_sal);
fetch emp_sal_cursor into v_empid,v_lastName,v_sal;
end loop;
close emp_sal_cursor;
end;

或者使用记录再优化一下：

declare
type emp_record is record(
v_empid employees.employee_id%type,
v_lastName employees.last_name%type,
v_sal employees.salary%type
);
v_emp_record emp_record;
cursor emp_sal_cursor is select employee_id,last_name,salary from employees where
department_id = 80;
begin
open emp_sal_cursor;
fetch emp_sal_cursor into v_emp_record;
while emp_sal_cursor%found loop
dbms_output.put_line('employee_id:'||v_emp_record.v_empid||', '||'last_name:'||
v_emp_record.v_lastName||', '||'salary:'||v_emp_record.v_sal);
fetch emp_sal_cursor into v_emp_record;
end loop;
close emp_sal_cursor;
end;

可以使用for循环最优化：（注意：在for循环中它会自动的打开游标、提取游标，当提取完里面的数据后也会自动
的关闭游标）

declare
cursor emp_sal_cursor is select employee_id,last_name,salary from employees where
department_id = 80;
begin
for c in emp_sal_cursor loop
dbms_output.put_line('employee_id:'||c.employee_id||', '||'last_name:'||c.last_name||',
'||'salary:'||c.salary);
end loop;
end;

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

declare
  cursor emp_cursor is select employee_id,salary from employees;
  v_empid employees.employee_id%type;
  v_sal employees.salary%type;
  v_temp number(4,2);
begin
  open emp_cursor;
  fetch emp_cursor into v_empid,v_sal;
  while emp_cursor%found loop
   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;
  dbms_output.put_line(v_empid||','||v_sal);
  update employees
  set salary = salary * (1+v_temp)
  where employee_id = v_empid;
  fetch emp_cursor into v_empid,v_sal;
  end loop;
  close emp_cursor;
end;

用for循环实现

declare
  cursor emp_cursor is select employee_id,salary from employees;
  v_temp number(4,2);
begin
  for c in emp_cursor loop
   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;
  update employees
  set salary = salary * (1+v_temp)
  where employee_id = c.employee_id;
  end loop;
end;

隐式游标：更新员工salary（涨工资10），如果该员工没有找到，则打印“查无此人”信息：

begin
  update employees
  set salary = salary + 10
  where employee_id = 1001;
   if sql%notfound then dbms_output.put_line('查无此人');
   end if;
end;

异常：
预定义异常：（有24个预定义异常，可查表）

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('输出的行数过多');
  when others then dbms_output.put_line('出现其它的异常了');
end;

非预定义异常：

declare
  e_deleteid_exception exception;
  pragma exception_init(e_deleteid_exception,-2292);
begin
  delete from employees
  where employee_id = 100;
exception
  when e_deleteid_exception then dbms_output.put_line('违反了完整性约束，故不能删除此用户');
  when others then dbms_output.put_line('出现其它的异常了');
end;

用户自定义异常：

declare
  e_sal_hight 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_sal_hight;
  end if;
exception
  when e_sal_hight then dbms_output.put_line('工资太高了');
  when others then dbms_output.put_line('出现其它的异常了');
end;

通过select...into...查询某人的工资，若没找到则打印出“未找到此数据”：

declare
  v_sal employees.salary%type;
begin
  select salary into v_sal from employees where employee_id = 1001;
exception
  when no_data_found then dbms_output.put_line('未找到此数据');
  when others 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 = 1001;
  if v_sal < 300 then update employees set salary = salary + 100 where employee_id =101;
  end if;
exception
  when no_data_found then dbms_output.put_line('未找到此数据');
  when too_many_rows then dbms_output.put_line('输出的行数太多了');
  when others then dbms_output.put_line('出现其它的异常了');
end;

自定义异常：
更新指定员工工资，增加100；若指定员工不在，则抛出异常：NO_RESULT;

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

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

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

带参数的存储函数：

create or replace function hello_world1(v_logo varchar2)
return varchar2
is
begin
  return 'helloworld'||v_logo;
end;
调用：
select hello_world1('shellway') from dual
或者：
begin
  dbms_output.put_line(hello_world1('shellway'));
end;

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

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

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

create or replace function add_param(v_num1 number,v_num2 number)
return number
is
  v_num3 number(10);
begin
  v_num3 := v_num1 + v_num2;
  return v_num3;
end;
调用：
select add_param(2,5) from dual;
或者：
begin
  dbms_output.put_line(add_param(5,4));
end;

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

create or replace function get_sal(dept_id number)
return number
is
  v_sumsal number(10) := 0;
  cursor salary_cursor is select salary from employees where department_id = dept_id;
begin
  for c in salary_cursor loop
  v_sumsal := v_sumsal + c.salary;
  end loop;
  return v_sumsal;
end;
调用：
select get_sal(80) from dual;

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

create or replace function get_sal(dept_id number,total_count out number)
return number
is
 v_sumsal number(10) := 0;
 cursor salary_cursor is select salary from employees where department_id = dept_id;
begin
  total_count := 0;
  for c in salary_cursor loop
    v_sumsal := v_sumsal + c.salary;
    total_count := total_count + 1;
  end loop;
  return v_sumsal;
end;
调用：
declare
  v_count number(4);
begin
  dbms_output.put_line(get_sal(80,v_count));
  dbms_output.put_line(v_count);
end;

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

create or replace procedure get_sal1(dept_id number,sumsal out number)
is
 cursor salary_cursor is select salary from employees where department_id = dept_id;
begin
 sumsal := 0;
 for c in salary_cursor loop
   sumsal := sumsal + c.salary;
 end loop;
 dbms_output.put_line(sumsal);
end;
调用：
declare
  v_sal number(10):=0;
begin
 get_sal1(80,v_sal);
end;

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

                                                                                          （95，98）                  3%

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

create or replace procedure add_sal(dept_id number,temp out number)
is
  cursor sal_cursor is select employee_id,salary,hire_date
  from employees where department_id = dept_id;
  v_temp number(4,2):=0;
begin
  temp := 0;
  for c in sal_cursor loop
    if to_char(c.hire_date,'yyyy') < '1995' then v_temp:=0.05;
    elsif to_char(c.hire_date,'yyyy') < '1998' then v_temp:=0.03;
    else v_temp:=0.01;
    end if;

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

  temp := temp + c.salary*v_temp;
  end loop;
  dbms_output.put_line(temp);
end;
调用：
declare
  v_i number(10):=0;
begin
  add_sal(80,v_i);
end;

触发器：
触发事件：在INSERT,UPDATE,DELETE情况下会触发TRIGGER
触发时间：该TRIGGER是在触发事件发生之前(BEFORE)还是之后（AFTER）
触发器本身：该TRIGGER被触发之后的目的和意图，正是触发器本身要做的事情，如PL/SQL块
触发频率：有语句级(STATEMENT)触发器和行级(ROW)触发器
写一个简单的触发器：

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

使用:new,:old修饰符：

1、
create table emp1
as
select employee_id,salary,email from employees where department_id = 80;
2、
create or replace trigger update_emp_trigger2
after
  update on emp1
for each row
begin
  dbms_output.put_line('old salary:'||:old.salary||'new salary:'||:new.salary);
end;
3、
update emp1 set salary = salary + 100 ；

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

1、创建my_emp表：
create table my_emp
as
select employee_id,salary from employees ;
2、创建my_emp_bak表：
create table my_emp_bak
as
select employee_id,salary from employees where 1=2;
3、检查创建的表中的记录：
select * from my_emp
select * from my_emp_bak
4、创建一个触发器：
create or replace trigger delete_emp_trigger
before
  delete on my_emp
for each row
begin
  insert into my_emp_bak
  values(:old.employee_id,:old.salary);
end;
5、执行含有触发器时间的语句：
delete from my_emp
6、检查触发器执行后的结果：
select * from my_emp
select * from my_emp_bak

以上是小编为您精心准备的的内容，在的博客、问答、公众号、人物、课程等栏目也有的相关内容，欢迎继续使用右上角搜索按钮进行搜索oracle
， pl/sql
， 触发器
， 流程控制
， 基本语法
， 记录类型
， 游标的使用
异常处理机制
，以便于您获取更多的相关知识。

时间： 2024-12-21 22:21:58

oracle复习笔记之PL/SQL程序所要了解的知识点_oracle的相关文章

Oracle数据库之PL/SQL程序基础设计

一.PL/SQL块结构 前边我们已经介绍了PL/SQL块的结构,再来回顾一下: DECLARE /* * 声明部分--定义常量.变量.复杂数据类型.游标.用户自定义异常 */ BEGIN /* * 执行部分--PL/SQL语句和SQL语句 */ EXCEPTION /* * 异常处理部分--处理运行异常 */ END; /*块结束标记 */ 要实现PL/SQL程序设计,先介绍如下的基本内容: 二.标识符 PL/SQL程序设计中的标识符定义与SQL的标识符定义的要求相同: 标识符名第一个字符必须为

写PL/SQL程序时碰到的一些问题

程序|问题 记的上次写PL/SQL程序还是刚毕业不久,还用的是Oracle 7 for Novell后来只是偶尔用一下oralce, PL/SQL的一些语法已经全忘了,这不,碰到好些低级的问题,谨记下,希望不会再忘记. 1.有for update类型的Cursor定义Cursor时,加了for update,因为需要打开cursor后还要对这些数据进行修改和删除,但在修改和删除数据后在关闭cursor前就commit,就出现了下面的错误:ORA-01002: 读取违反顺序ORA-06512: 在

PL/SQL程序结构

什么是PL/SQL程序     前面第4章学习的标准化的SQL语言对数据库进行各种操作,每次只能执行一条语句,语句以英文的分号";"为结束标识,这样使用起来很不方便,同时效率较低,这是因为Oracle数据库系统不像VB.VC这样的程序设计语言,它侧重于后台数据库的管理,因此提供的编程能力较弱,而结构化编程语言对数据库的支持能力又较弱,如果一些稍微复杂点的管理任务都要借助编程语言来实现的话,这对管理员来讲是很大的负担.    正是在这种需求的驱使下,从Oracle 6开始,Oracle公

Oracle数据库之开发PL/SQL子程序和包

PL/SQL块分为匿名块与命名块,命名块又包含子程序.包和触发器. 过程和函数统称为PL/SQL子程序,我们可以将商业逻辑.企业规则写成过程或函数保存到数据库中,以便共享. 过程和函数均存储在数据库中,并通过参数与其调用者交换信息.过程和函数的唯一区别是函数总向调用者返回数据,而过程不返回数据. 1. 存储过程概念 存储过程(Stored Procedure)是在大型数据库系统中,一组为了完成特定功能的SQL语句集,存储在数据库中.经过第一次编译后再次调用不需要再次编译,用户通过指定存储过程的名

Oracle 10G:PL/SQL正规表达式（正则表达式）手册_oracle

Oracle 的正规表达式的实施是以各种 SQL 函数和一个 WHERE 子句操作符的形式出现的.如果您不熟悉正规表达式,那么这篇文章可以让您了解一下这种新的极其强大然而表面上有点神秘的功能.已经对正规表达式很熟悉的读者可以了解如何在 Oracle SQL 语言的环境中应用这种功能. 什么是正规表达式? 正规表达式由一个或多个字符型文字和/或元字符组成.在最简单的格式下,正规表达式仅由字符文字组成,如正规表达式 cat.它被读作字母 c,接着是字母 a 和 t,这种模式匹配 cat.locati

pl/sql程序编写中遇到的一些问题及解决办法

程序|解决|问题 1.在pl/sql中,order by子句中的条件可以使用变量! DECLARE v_orderbystr VARCHAR2(30); v_userid VARCHAR2(30); v_username VARCHAR2(30); v_gender NUMBER; v_rownum NUMBER; TYPE tcur IS REF CURSOR; results tcur; BEGIN v_rownum:=0; v_orderbystr:='username';    OPEN

实用的备份PL/SQL程序工具

备份|程序 /*ligang1000@hotmail.com*/   功能: 用于备份当前用户所拥有的所有PL/SQL objects (包括 TYPE,TYPE BODY, PROCEDURE , FUNCTION, PACKAGE, PACKAGE BODY or JAVA SOURCE )   原理: 对USER_SOURCE数据字典的调用,得到所有的 PL/SQL 代码.   使用方法举例: 1.     在C盘建立目录C:\EXPORT 2.     将EXPORT_SOURCE.SQ

PL/SQL程序中调用Java代码（转）

  主要是学习PL/SQL调用JAVA的方法. 平台:WINDOWS 1.首先使用IDE写好需要调用的java代码,再添加"create or replace and compile java source named 名字 as",然后在PL/SQL中执行   create or replace and compile java source named getosmac as import java.io.InputStreamReader; import java.io.Line

PL/SQL学习笔记(三) ——PL/SQL代码编写规则

养成良好的代码编写习惯,PL/SQL代码编写规则: 1.标识符命名规则 1)定义变量时以v_作为前缀,如v_sal 2)定义常量时以c_作为前缀,如c_rate 3)定义游标时,建议使用_cursor作为后缀,如emp_cursor 4)定义异常时,以e_作为前缀,如e_integrity_error 5)定义PL/SQL表类型时,使用_table_type作为后缀,如sal_table_type. 6)定义表变量时,以_table作为后缀,如sal_table 7)同样,定义PL/SQL记录型