Oracle PL/SQL编程指南-优快云博客

本文链接：https://blog.youkuaiyun.com/m0_63223374/article/details/124513021

本文介绍了PL/SQL的基础知识，包括变量、常量、%type和%rowtype的使用，控制结构如if、case、loop、while和for，异常处理机制以及自定义函数的创建。内容详细阐述了PL/SQL的语法结构和优点，是学习Oracle数据库编程的好资源。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1.PL/SQL 简介

PL/SQL 是过程语言(Procedural Language)与结构化查询语言(SQL)结合而成的编程语言
PL/SQL 是对 SQL 的扩展
支持多种数据类型，如大对象和集合类型，可使用条件和循环等控制结构
可用于创建存储过程、触发器和程序包，给SQL语句的执行添加程序逻辑
与 Oracle 服务器和 Oracle 工具紧密集成，具备可移植性、灵活性和安全性

2.变量和常量

2.1变量

变量语法结构：

declare
	变量名  数据类型；
begin
    执行代码块内容；   
end；

declare 
  变量名1 数据类型；
  变量名2 数据类型
begin 
  给变量赋值
end

赋值方法：

方式一：“:=”

方式二：“select into 语句”

案例：

--声明一个变量，并赋值、打印
declare
  eno number(4);
  myEname varchar2(10);
begin
  eno:=7369;
  select ename into myEname from emp where empno=eno;
  dbms_output.put_line(myEname);
end;

-- 根据给定的员工编号查询员工名字，并输出。
declare
	sid number(7);          --声明变量 sid 用来保存员工编号(未初始化值)
	sname varchar(10);  --声明变量 sname 用来保存查询出来的员工名字，未初始化值
begin
	sid:=7499;                 --通过“:=”给变量 sid 赋值
	select ename into sname from emp where empno=sid;           --通过 select into 语句给 sname 变量赋值
	dbms_output.put_line('要查询的名字是：'||sname);              --打印出结果。
end;

--在声明变量的同时也可以给变量赋值，如：sid number(7):=7499;

2.2常量

语法结构

declare
	变量名1 constant 数据类型 := 值；
begin
	执行代码块内容；
end；

常量与变量的区别

常量声明的时候要在数据类型前面加 constant 关键字，表示常量的意思；
常量必须初始值，且值不能被修改；

案例：

--声明一个变量，将emp表中的ename 赋值到变量中并打印
declare 
    eno number(4):=7369;
    myEname varchar2(10);
    
begin
  select ename into myEname from emp where empno=eno;
  dbms_output.put_line('名字是' ||myEname);
end;



--根据给定的员工编号查询员工名字，并输出。
declare
	sid constant number(7) := 7499;     --声明常量 sid 用来保存员工编号，并且初始化值
	sname varchar(10);           --声明变量 sname 用来保存查询出来的员工名字，未初始化值
begin
	select ename into sname from emp where empno=sid;          --通过 select into 语句给 sname 变量赋值
	dbms_output.put_line('要查询的名字是：'||sname);            --打印出结果。
end;

3.%type与%rowtype

用于引用数据库列的数据类型，以及表示表中一行的记录类型

3.1%type

作用：用来引用数据表里面某个字段的数据类型；
%type - 引用变量和数据库列的数据类型
通俗解释：当我们想要声明一个变量来保存表内某个字段值但是又不知道该变量用什么数据类型的时候，我们就可以引用表内该字段的数据类型来作为变量的数据类型。

案例：

--案例演示：根据员工编号查询出员工的姓名和入职日期。
declaresid number(7) :=7499;
sname varchar2(10);
mydate emp.hiredate%type;--通过%type引用 emp 表里面 hiredate 字段的数据类型。
begin
       select ename,hiredate into sname,mydate from emp
       whereempno=sid;
       dbms_output.put_line('名字是：'||sname||'，入职日期是：'||mydate);
end;

3.2%rowtype

作用：可以声明一个基于表的行对象，也称为记录对象。
%rowtype - 提供表示表中一行的记录类型

案例：


--根据员工编号查询出名字和岗位(用一个变量保存)。
declaresid number :=7499;
mysum emp%rowtype;
begin
       select * into mysum from emp where empno=sid;
       dbms_output.put_line('名字是：'||mysum.ename||'，岗位是：'||mysum.job);
end;

3.3使用属性类型的优点

不需要知道被引用的表列的具体类型
如果被引用对象的数据类型发生改变，pl/sql 变量的数据类型也随之改变

--%type
icode itemfile.itemcode%type;
--%rowtype
emp_rec scott.emp%rowtype;

4.控制结构

4.1.条件控制语句

4.4.1 if()语句

Oracle里得到判断和java里的判断语句差不多Java里是if()elseif()else
在Oracle里就是if()后面加then然后elseif()在Oracle里变成了elsif()then
注意：这里有一点其实大家很容易看错了就是elsif()then这里很容易就把它写成elseif()
语法结构：

if(条件1) then
	条件1成立执行代码块
elsif(条件2) then
	条件2成立执行代码块.......
else
	以上条件都不成立执行代码块
end if;

案例:


--根据学号查询薪水，如果薪水大于3500则“要纳税”，小于则“要努力”，等于则“刚刚好”。
declaresid number:=7499;
mysal emp.sal%type;
begin
     select sal into mysal from emp where empno=sid;
     if(mysal>3500) then
        dbms_output.put_line('要纳税');
    elsif(mysal=3500) then
       dbms_output.put_line('刚刚好');
    else
      dbms_output.put_line('要努力');
    end if;
end;

4.1.2case语句

语法结构

begin
	case '&变量'
		when '值1' then 执行代码块1；
		when '值2' then 执行代码块2；
		when '值3' then 执行代码块3；
		when '值4' then 执行代码块4；
		..............
		else 当以上值都不满足的时候执行代码块
		end case;
end;

“&”：作用相等于Java里面的Scanner；

案例：


--输入等级，如果等级是 A，则输出：优异A；如果等级是 B，则输出：优异B；如果等级是 C，则输出：优异；否则，输出：成绩太差了！
begin
    case '&scode'
    when 'A' then dbms_output.put_line('优异A');
    when 'B' then dbms_output.put_line('优异B');
    when 'C' then dbms_output.put_line('优异C');
    when 'D' then dbms_output.put_line('优异D');
    else dbms_output.put_line('成绩太差了，来个F吧！！！');
    end case;
end;

4.2循环控制语句

循环控制用于重复执行一系列语句
循环控制语句包括：
loop、exit 和 exit when
循环控制的三种类型：

loop - 无条件循环
while - 根据条件循环
for - 循环固定的次数

4.2.1 loop循环

loop
sequence_of_statements
end loop;


--输出1-100之间所有数字；
declare
	i number:=0;
begin
<<aloop>>                              --给loop循环取别名：aloop
        loop
           dbms_output.put_line(i);     --输出每一个值
           i:=i+1;                                   --i的值在叠加
   exit aloop when i=101;        --当i等于100的时候aloop循环退出 
       end loop;
end;


--求1-100之间的和。
declarei number(7):=0;
sumb number(7):=0;               --保存和，注意这里不能定义sum
begin
<<myloop>>                             --给loop循环取
loop
    sumb:=sumb+i;
    i:=i+1;
    exit myloop when i=101;
	end loop;
	dbms_output.put_line(sumb);   --循环完成后输出最终求的和
end;

4.2.2 while循环

while condition loop
sequence_of_statements
end loop;


--求1-100之间的和。
declarei number(8):=0;
mysum number(8):=0;
begin
      while(i<=100) loop
            mysum:=mysum+i;
            i:=i+1;
      end loop;
      dbms_output.put_line(mysum); --循环完成后输出最终求的和
end;

4.2.3 for循环

for counter in [reverse] value1…value2
loop
sequence_of_statements
end loop;

--案例：求1-100之间的和。
declaremysum number(7):=0;
begin
      for i in 1..100 loop   
          mysum:=mysum+i;
      end loop;
      dbms_output.put_line(mysum);
end;

5.异常处理

5.1预定义异常/系统自带异常.

在运行程序时出现的错误叫做异常
发生异常后，语句将停止执行，控制权转移到 pl/sql 块的异常处理部分
异常有两种类型：

预定义异常 - 当 pl/sql 程序违反 oracle 规则或超越系统限制时隐式引发
用户定义异常 - 用户可以在 pl/sql 块的声明部分定义异常，自定义的异常通过 raise 语句显式引发

5.1.1 too_many_rows.u

too_many_rows：行太多。
意为：实际返回的行数超出请求的行数。

案例：

declaresid number:=7499;
mysum emp%rowtype;
begin
    select * into mysum from emp;
    dbms_output.put_line('名字是：'||mysum.ename||'，岗位是：'||mysum.job);
end;


--处理异常：
declaresid number:=7499;
mysum emp%rowtype;
begin
  select * into mysum from emp;
  dbms_output.put_line('名字是：'||mysum.ename||'，岗位是：'||mysum.job);
  exception when too_many_rows then
            dbms_output.put_line('太多行');
end;

5.1.2 no_data_found.

no_data_found：行太少。
意为：未查询到数据。

案例：

declaresid number:=0000;
mysal emp.sal%type;
begin
    select sal into mysal from emp where empno=sid;
    if(mysal>3500) then
    dbms_output.put_line('要纳税');
    else
      dbms_output.put_line('要努力');
    end if;
end;


--处理异常：
declaresid number:=0;
mysum emp%rowtype;
begin
  select * into mysum from emp where empno=sid;
  dbms_output.put_line('名字是：'||mysum.ename||'，岗位是：'||mysum.job);
  exception when no_data_found then
            dbms_output.put_line('行太少');
end;

5.2自定义异常.

语法：

declare
	异常变量 exception;
  begin
    if(触发异常条件) then
       raise 异常变量;    
    else
      未触发异常执行的代码块
    end if;
    exception when 异常变量 then
             触发异常执行的代码块
  end;

案例：

--根据工号查询工资，如果工资低于3500则抛出异常：‘你的工资太低了’。否则就输出：‘你的工资可以哟’。
declare
  sid number:=7369;
  mye exception;
  mysal emp.sal%type;
  begin
    select sal into mysal from emp where empno=sid;
    if(mysal<3500)then
       raise mye;
    else
      dbms_output.put_line('你的工资可以哟');
    end if;
    exception when mye then
              dbms_output.put_line('你的工资太低了');
  end;

6.自定义函数

语法结构

create [or replace] function 函数名 (参数1,参数2)
return 返回值类型  is|as   
定义返回变量(要取长度)；
begin
  函数要执行的sql语句
  return 变量名;          
end;

案例：

调用函数：select fun_name(7369) from dual;

--创建函数
CREATE OR REPLACE FUNCTION fun_hello
  RETURN  VARCHAR2
IS
BEGIN
  RETURN '朋友，您好';
END;


--定义函数：通过学号返回学生的名字。
create or replace function  fun_name(sid number)      --函数名，带参数
  return varchar2 is               --返回值的类型，注意这里不能指定数据类型的长度
  sname varchar2(10);          --定义返回的变量(要取长度)
  begin
      select ename into sname from emp where empno=sid;
      return sname;                   --返回定义的变量sname
end;

7.总结

PL/SQL 是一种可移植的高性能事务处理语言
PL/SQL 引擎驻留在 Oracle 服务器中
PL/SQL 块由声明部分、可执行部分和异常处理部分组成
PL/SQL 数据类型包括标量数据类型、LOB 数据类型和属性类型
控制结构包括条件控制、循环控制和顺序控制
运行时出现的错误叫做异常
异常可以分为预定义异常和用户定义的异常
函数的编写及使用