PL/SQL编程

邮氧海猫

已于 2024-05-19 13:34:30 修改

阅读量756

点赞数 11

文章标签： sql 数据库 oracle

于 2024-05-19 13:32:20 首次发布

本文链接：https://blog.csdn.net/Block_yang/article/details/139041297

版权

对于标准化的SQL语言对数据库进行进行各种操作，每次只能执行一条语句，语句以英文的分号“；”为结束标识。这是因为Oracle数据库系统不像VB，VC这样的程序设计语言，侧重在于后台数据库的管理，因此提供的编程能力较弱，而结构化编程语言对数据库的支持能力又较弱。在这种要求的驱使下，Oracle公司在标准SQL语言的基础上发展了自己的PL/SQL语言，将变量、控制结构、过程和函数等结构化程序设计的要素引入了SQL语言中，这样就能够编制比较复杂的SQL程序了，利用PL/SQL语言编写的程序也称为PL/SQL程序块。

注意：PISQL是专用于Oracle服务器，在SQL基础之上，添加了一些过程化控制语句，叫PLSQL。

1、PLSQL组成

PLSQL由数据类型、常量和表达式、条件和循环控制语句、游标和异常处理构成。

总体结构：

declare
	定义语句段:以declare为标识，在该部分定义程序中要使用的常量、变量、游标等
begin
	执行语句段:以begin为开始标识。该部分是每个pl/sql程序所必备的，包含了对数据库的操作语句和各种流程控制语句
exception
	异常处理语句段:该部分包含在这行部分里面，以exception为标识，对程序执行中产生的异常情况进行处理（有的程序比较简单，往往省略异常处理部分。
end

①、数据类型

基本数据类型	功能
number	数字型
int	整数型
Pls_integer	整数型，产生溢出时出现错误
Char	定长字符型，最大255个字符
varchar2	变长字符型，最大2000个字符
Long	变长字符型，最大2GB
Date	日期型
Boolean	布尔型（TRUE，FALSE，NULL)
Binary_integer	整数型，表示带符号的整数

复合数据类型	功能
%type	用于匹配其字段类型和数据表中字段的数据类型
记录类型变量	多个基本数据类型捆绑在一起的记录数据类型
%rowtype	使用%rowtype可以使变量获得整个记录的数据类型
游标	从数据表中提取出来的数据结果集使用游标类型接收，便于操作

②、常量

格式：

常量名 类型标识符 [not null]:=值；

注意：常量名与后面的变量名都必须以字母开头，不能有空格，不能超过30个字符长度，同时不能和保留字同名，常（变）量名称不分大小写，在字母后面可以带数字或特殊字符。括号内的not null为可选参数，若选用，表明该常（变）量不能为空值。

例如：定义名为pi的数字型常量，长度为9。

declare
pi constant number(9):=3.1415926;
begin
commit;
end;

③、表达式

(1)、数值表达式
PL/SQL程序中的数值表达式是由数值型常数、变量、函数和算术运算符组成的，可以使用的算术运算符包括+（加法）、-（减法）、*（乘法）、/（除法）和**（乘方）等。

(2)、字符表达式
字符表达式由字符型、变量、函数和字符运算符组成，惟一可以使用的字符运算符就是连接运算符“||”
。
(3)、关系表达式
关系表达式由字符表达式或数值表达式与关系运算符组成，关系型表达式运算符两边的表达式的数据类型必须一致。可以使用的关系运算符包括以下9种。

运算符	作用
=	等于（不是赋值运算符:=)
like	类似于
in	在…之中
<=	小于等于
>=	大于等于
!=	不等于
between	在…之间

(4)、逻辑表达式
逻辑表达式由逻辑常数、变量、函数和逻辑运算符组成，常见的逻辑运算符包括以下3种：

运算符	功能
NOT	逻辑非
OR	逻辑或
AND	逻辑和

(5)、重要的几个函数
PL/SQL程序中提供了很多函数供扩展功能，除了标准SQL语言的函数可以使用外，最常见的数据类型转换函数有以下3个。

运算符	功能
to_char	将其他类型数据转换为字符型
to_date	将其他类型的数据转换为日期型
to_number	将其他类型数据转换为数值型

例如：

-- 赋值
declare
 e emp.empno%type;
 e_name emp.ename%type;
 e_sal emp.sal%type; -- 拷贝emp表中sal这一列的数据类型
begin
  e := &e;
  -- 使用查询语句赋值  into 左边是表中的列，右边是声明的变量
  select ename,sal into e_name,e_sal from emp where empno = e;
  -- 这种写法不对
 -- select ename into e_name,sal into e_sal from emp where empno = e;
  
  dbms_output.put_line('姓名：'||e_name);
  dbms_output.put_line('工资：'||e_sal);
  
end;


-- 案例 
declare
 emp_sal   emp.sal%type;
 emp_name  emp.ename%type; 
begin
  select sal into emp_sal from emp where empno = &e;
  select ename,sal into emp_name,emp_sal from emp where sal > emp_sal;
  dbms_output.put_line(emp_name||'，工资：'||emp_sal);
end;

④、条件控制语句

(1)、if

declare
  numA int := 3;
  numB int := 2;
begin
  if numA > numB 
    then
    dbms_output.put_line('numA > numB');
  end if;
end;

(2)、if - else

declare
 numA int := 1;
 numB int := 2;
begin
  if numA > numB
   then
    dbms_output.put_line('numA > numB');
   else
    dbms_output.put_line('numA < numB');
  end if;
end;

(3)、if - elsif -else

declare
    numA int := 1;
    numB int := 1;
begin
  if numA > numB then
    dbms_output.put_line('numA > numB');
  elsif numA = numB then
    dbms_output.put_line('numA = numB');
  else
    dbms_output.put_line('numA < numB');
  end if;
end;

(4)、if嵌套

declare
A int :=11;
B int :=8;
begin
  if A>=B then
    if A=B then
      dbms_output.put_line('A=B');
      end if;
     if A>B then
       dbms_output.put_line('A>B');
       end if;
  end if;
end;

(5)、案例

declare
e_sal emp.sal%type;
e_name emp.ename%type;
begin
  select sal,ename into e_sal,e_name from emp where empno=&e;
  dbms_output.put_line(e_name||':'||e_sal);
  -- 2000<sal<2500  小康
  -- 2500<sal<5000 中康
  --else 很富有或很贫困
  if e_sal>2000 and e_sal<2500 then
    dbms_output.put_line('小康');
   elsif e_sal>2500 and e_sal<5000 then
     dbms_output.put_line('中康');
    else
      dbms_output.put_line('很富有或者很贫穷');
end if;
end;

⑤、循环控制语句

循环结构是按照一定逻辑条件执行一组命令，PL/SQL中可以有4种基本循环结构,在它们基础上又可以演变出许嵌套循环控制。

(1)、loop

语法为：

loop..exit..end loop
	或者
loop..exit..when..end loop

例如：

declare
A int :=0;
begin
  loop
    exit when A=10;
    A:=A+1;
    dbms_output.put_line('循环：'||A);
  end loop;
end;

(2)、loop+if

declare
A int :=0;
begin
  loop 
    if A =10 then
      exit;
      end if;
      A :=A+1;
      dbms_output.put_line('循环：'||A);
   end loop;
end;

(3)、while
语法为：

while..loop..end loop

例如：

declare
A int :=0;
begin
  while A<10 loop
    A:=A+1;
    dbms_output.put_line('循环：'||A);
    end loop;
end;

(4)、for
语法为：

for..in..loop..end

例如：

– 按照顺序输出

declare
A int :=0;
begin
  for A in 1..10 loop
    dbms_output.put_line('循环：'||A);
    end loop;
end;

– 逆序输出

declare
A int :=0;
begin
  for A in reverse 1..10 loop
    dbms_output.put_line('循环：'||A);
    end loop;
end;

注意：Reverse选项强制循环变量从终止值开始，每次循环减1，直到起始值。

⑥、游标

游标是从数据表中提取出来的数据，以**临时表(也叫做假表)**的形式存放在内存中，在游标中有一个数据指针，在初始状态下指向的是首记录，利用fetch语句可以移动该指针，从而对游标中的数据进行各种操作，然后将操作结果写回数据表中。游标作为一种数据类型，首先必须进行定义。

定义游标

cursor 游标名 is select 语句;

打开游标

open 游标名；

打开游标的过程有以下两个步骤。
1）、将符合条件的记录送入内存。
2）、将指针指向第一条记录。
提取游标

fetch 游标名 into 变量名1，变量名2，
.....;
 或
 fetch 游标名 into 记录型变量名；

关闭游标

close 游标名;

注意：使用完游标后，需要关闭游标。

⑦、游标的属性

属性	功能
%isopen	测试游标是否打开，如果没有打开游标就是用fetch语句并提示错误。
%found	游标是否找到一条记录。如游标找到记录其值为True，反之为False.
%notfound	游标没有找到记录，是%FOUND属性的逻辑非。
%rowcount	返回提取游标记录的行数。

实例如下：

-- 游标：可以处理多行多列的数据，将数据从硬盘转移到了内存上，俗称虚拟表
declare
 cursor mycursor is select * from emp e inner join dept d on e.deptno = d.deptno;
 cursor_row mycursor%rowtype;  -- 定义游标行类型，绝对可以存放下本次查询的行结果
begin
 -- 打开游标 ，获得数据  
 open mycursor;
 
 if mycursor%isopen then
    loop
        -- 获取数据
        fetch mycursor into cursor_row;  
    
       --如果没有获取到数据，退出
        if mycursor%notfound then
           exit;
        end if;
        -- 在内存中输出数据
        dbms_output.put_line(cursor_row.ename||','||cursor_row.dname);
    
    end loop;  
 
    -- 获取数据完成后，查看一共获取到了多少条数据
    dbms_output.put_line(mycursor%rowcount);
    
    --关闭游标
    close mycursor;
 end if;

end;

⑧、异常

在设计PL/SQL程序时，经常会发生这样或那样的错误，异常处理就是针对错误进行处理的程序段，Oracle中的异常处理分为系统定义异常处理和自定义异常处理两部分。

（1）系统定义异常

注：例如编号为ORA-06530的异常简略版编号为—6530

异常名称	编号
ACCESS_INTO_NULL	ORA-06530
CASE_NOT_FOUND	ORA-06592
COLLECTION_IS_NULL	ORA-06531
CURSOR_ALREADY_OPEN	ORA-06511
DUP_VAL_ON_INDEX	ORA-00001
INVALID_CURSOR	ORA-01001

（2）用户定义异常

用户可以在自己的应用程序中创建可触发及可处理的异常。

declare
 异常名 exception;
 触发异常处理的语法是：
 raise 异常名；

触发异常处理后，可以定义异常处理部分:

Exception
	when 异常名1	then
	 异常处理语句段1；
 	when 异常名2 then
 	 异常处理语句段2；

例如：

declare
  myerror exception;
begin
  dbms_output.put_line('before');
  if 1 < 2 then
       dbms_output.put_line('exception');
     raise myerror; -- 抛出异常，后面的代码不执行了，而是转入到exception代码中进行执行
  end if;
  dbms_output.put_line('after');
  
  -- 处理异常
  exception
    when myerror then
        dbms_output.put_line('处理中....');
end;

实例如下：

-- 输入一个员工编号，薪资低于2500 或者高于5000 抛出异常
declare
   sal_error exception;
   v_sal emp.sal%type;
begin
   select sal into v_sal from emp where empno = &e;
   if v_sal < 2500 or v_sal > 5000 then
     raise sal_error;
   end if; 
   dbms_output.put_line('工资正常');
   
   exception
     when sal_error then
          dbms_output.put_line('工资不正常');       
   
end;

select * from emp;