oracle的学习Ⅴ（PL/SQL程序块）

最新推荐文章于 2022-12-15 15:50:52 发布

laluneX

最新推荐文章于 2022-12-15 15:50:52 发布

阅读量400

点赞数

分类专栏：数据库文章标签： oracle

本文链接：https://blog.csdn.net/weixin_45969777/article/details/109529928

版权

数据库专栏收录该内容

9 篇文章 0 订阅

订阅专栏

一、什么是PL/SQL块

PL/SQL（Program Language / Structure Query Language）程序控制块是指具用一定功能sql程序，它可以复用，不过要注意的是语句的缩进以增强语句的可读性

例如：

set serveroutput on;		// 开启屏幕打印

begin 				// 块开始标志
  dbms_output.put_line('hello world!');	# 利用dbms_output包里面的put_line方法进行输出
end;				// 块结束标志

要注意的是

put_line()方法只能输出一个值，也就是说put_line(a, b)是不可以的，只能put_line(a || ‘ ’ || b)
语句都要以分号结尾
=为比较运算符，而 := 为赋值运算符

二、块的组成部分

整体框架

declare – 声明部分：变量、常量、游标、数据类型等，该部分可以省略。
begin – 执行部分：这是块的开始标志，该部分不能省略。
exception – 异常处理部分，该部分可以省略。
end; – 结束标志，该部分不能省略。

变量声明

variable_name [constant] datatype[:=value1|default value2];

数据类型：

number,char,varchar2,date,boolean(true false null),lob (clob,blob)

标识符命名

通常以字母开头，最长不超过30个字符。
v_(variable) c_ (constant) rec_ (record) cur_ (cursor)

%type

声明一个变量与已有的变量或已有列数据类型一致，若要与已有列数据类型一致的话，就必须对列添加归属对象（如：emp.sal而不是sal）
它是为了方便获得未知变量或列的数据类型

%rowtype

声明一个变量与已有的记录或已有表结构保持一致，且声明出的这个变量是一个记录类型
它是为了方便在不知道某个表需要输出多少列的时候使用
它相当于加强的%type

例如1：

declare 
  v_bjxx varchar2(30); 		// 变量声明
  v_name varchar2(20); 
begin 				// 块开始
  v_bjxx:='abc';
  v_name:='a';
  dbms_output.put_line('班级信息:'||v_bjxx||'  '||'姓名：'||'  '||v_name);		// 输出结果
end;			// 块结束
/				// 反斜杠号执行代码块

例如2：

// 写一个PL/SQL程序,查询7788员工的姓名和工资。

declare 
  v_ename varchar2(10);
  v_sal   number(7,2);
begin 
  select ename,sal into v_ename,v_sal from emp where empno=7788;			// 把查询的结果赋给v_ename,v_sal变量
  dbms_output.put_line('7788姓名是：'||v_ename||'  '||v_sal);
end;
/			// 执行代码块

例如3：

// 写一个PL/SQL程序,查询7788员工的姓名和工资。

declare 
  v_ename emp.ename%type;			// 获取emp.ename的数据类型，下同
  v_sal   emp.sal%type;
begin 
  select ename,sal into v_ename,v_sal from emp where empno=7788;		// 查询的结果赋值给变量
  dbms_output.put_line('7788姓名是：'||v_ename||'  '||v_sal);
end;
/

例如4：

// 写一个PL/SQL程序,查询7788员工信息。

declare 
  rec_emp emp%rowtype;			// 获取emp.ename的数据类型，下同，注意这里的rec_emp为记录类型
begin 
  select * into rec_emp from emp where empno=7788;	// 查询的结果赋值给变量
  dbms_output.put_line('7788姓名是：'||rec_emp.ename||'  '||rec_emp.v_sal);		// 使用变量.原列名的方式获得数据
end;
/

三、哪些SQL语句可以用在PL/SQL 中使用呢

select – 一般在select中使用into将查询的指导入变量中
DML – 数据修改语言（insert、update、delete）
commit – DML的提交
rollback – DML的滚回

注意：DDL（create、alter、drop）是不可以在PL/SQL里面改使用的

四、流程控制语句（选择语句）

if语句

if 条件1 then 处理1;
elsif 条件2 then 处理2;
…
else 处理N;
end if;

注意这里是elsif，也要注意块的语句缩进
例子：

// 写一个PL/SQL程序，向 emp表中添加一条记录，若该记录存在，则提示记录已存在，否则添加该记录，并提示添加成功。

declare 			
  v_empno emp.empno%type;		// 变量声明
  v_count int;
begin 
  v_empno:=7700;			// 赋值
  select count(*) into v_count from emp where empno=v_empno;		// 判断emp表内是否有empno为7700的记录
  if v_count=1 then 	// 为1则已存在
     dbms_output.put_line(v_empno||'  '||'已存在');
  else					// 为0则不在，并将记录插入到表内
     insert into emp(empno) values(v_empno);
     commit;			// DML需要commit
     dbms_output.put_line(v_empno||' '||'记录添加成功 ');
  end if;
end;
/

case语句

简单case

case 表达式
when value1 then 处理1;
…
else 处理N;
end case;

搜索case

case
when 条件1 then 处理1;
…
else 处理N;
end case;

例子：

// 查询7788员工的工资，当工资超过3000，显示高工资，2000到3000之间，一般工资，否则低工资。

declare 
  v_sal emp.sal%type;
begin 
  select sal into v_sal from emp where empno=7788;
  case				// case语句，类似于if语句
  when v_sal>3000 then 
     dbms_output.put_line('高工资');
  when v_sal between 2000 and 3000 then 
     dbms_output.put_line('一般工资');
  else
     dbms_output.put_line('低工资');
  end case;
end;
/

这里的搜索case与上面的if和以前的case类似（只是结束符不同而已）

五、流程控制语句（循环语句）

简单循环

loop
循环体
exit when 条件;
end loop;

例如：

// 输出100以内偶数和。

declare
	v_sum number default 0;			// 定义变量并赋值
	v_i number := 2;
begin
	loop			// 开始循环
		v_sum := v_sum + v_i;
		v_i := v_i + 2;			// 增加
		exit when v_i > 100;	// 退出条件
	end loop;				// 循环结束标志
	dbms_output.put_line('结果为：' || v_sum);
end;
/

while循环

while 条件
loop
循环体
end loop;

例如：

// 输出100以内偶数和。

declare
	v_sum number default 0;			// 定义变量并赋值
	v_i number := 2;
begin
	while v_i <= 100				// 开始循环
		loop
			v_sum := v_sum + v_i;
			v_i := v_i + 2;			// 增加
		end loop;				// 循环结束标志
	dbms_output.put_line('结果为：' || v_sum);
end;
/

for 循环

for 循环控制变量 in [reverse] 下限…上限
loop
循环体
end loop;

例如：

// 输出100以内偶数和。

declare
	v_sum number default 0;
begin
	for v_i in 1..100				// 循环开始
		loop
			if mod(v_i, 2) = 0 then		// if判断是否为偶数
				v_sum := v_sum + v_i;
			end if;
		end loop;				// 循环结束
	dbms_output.put_line('结果为：' || v_sum);
end;
/

要注意的是：

这里面没有取余符号，只能用**mod()**函数
for里面的循环控制变量是不用声明的
for里面的下、上限是包括临界值的

六、游标

游标本质就是一个临时的内存块，用来存储某个查询的多条返回结果。它可以理解为多行的rowtype，且它分为隐式游标和显式游标。

隐式游标

当在PL/SQL块中执行SQL语句中时，隐式游标会在系统中自动生成，游标名 默认为SQL（sql）。
隐式游标属性：

isopen: DML执行过程中为真，结束为假。
rowcount： DML语句影响的行数。
found： SQL 语句执行成功为真，失败为假。
notfound：成功为假，失败为真。

隐式游标用法： sql%属性名
例子:

// 向EMP表添加一条记录，判断该记录是否存在，不存在添加，存在则给出相应提示。

declare 
   v_empno emp.empno%type;
begin 
   v_empno:=&v_empno;
   insert into emp(empno) values(v_empno);
   if sql%found then 
      commit;
      dbms_output.put_line(v_empno||' '||'添加成功');
   else
      rollback;
      dbms_output.put_line(v_empno||' '||'已 存在 '); 
   end if;
end;

显式游标

显式游标是用户自定义的。使用显式游标有四个步骤：

①声明游标：cursor cursor_name is select;
②打开游标：open cursor_name; --此时执行子查询，并读入内存缓冲区。
③提取数据：fetch cursor_name into 变量名1, 变量名2……；
④关闭游标：close cursor_name;

显式游标属性：

isopen: 游标打开为真，否则为假。
rowcount：当前提取的行数。
found：提取成功为真，失败为假
notfound：提取失败为真，成功为假

显式游标用法：游标名%属性名

例1：

// 查询所有员工的姓名和工资。

declare 			// 方法一
  cursor cur1 is select ename,sal from emp;
  v_ename emp.ename%type;
  v_sal emp.sal%type;
begin 
  open cur1;
  loop
    fetch cur1 into v_ename,v_sal;
     exit when cur1%notfound;
    dbms_output.put_line(v_ename||'   '||v_sal);
  end loop;
  close cur1;
end;

declare 				// 方法二
  cursor cur1 is select ename,sal from emp;
  v_ename emp.ename%type;
  v_sal emp.sal%type;
begin 
  open cur1;
  fetch cur1 into v_ename,v_sal;
  while cur1%found
  loop
     dbms_output.put_line(v_ename||'   '||v_sal);
     fetch cur1 into v_ename,v_sal;
  end loop;
  close cur1;
end;

例2：

// 查询所有员工的姓名，工资，工号，工龄，奖金。

declare 			// 方法一
  cursor cur1 is select ename,sal,empno,sysdate-hiredate a,hiredate,comm from emp;
  rec_cur1 cur1%rowtype;		// 这里可以是cursor类型的
begin 
  open cur1;
  fetch cur1 into rec_cur1;
  while cur1%found
  loop
     dbms_output.put_line(rec_cur1.ename||rec_cur1.sal);
     fetch cur1 into rec_cur1;
  end loop;
  close cur1;
end;

declare 				// 方法二
  cursor cur1 is select ename,sal,empno,sysdate-hiredate a,hiredate,comm from emp;
  rec_cur1 cur1%rowtype;
begin 
  for rec_cur1 in cur1
  loop 
         dbms_output.put_line(rec_cur1.ename||rec_cur1.sal);
  end loop;
end;


begin 					// 方法三，注意这里的cursor的四步都在for里面自动执行了
  for rec_cur1 in (select ename,sal,empno,sysdate-hiredate a,hiredate,comm from emp)
  loop 
         dbms_output.put_line(rec_cur1.ename||rec_cur1.sal);
  end loop;
end;

七、异常处理

系统预定义异常

类型有no_data_found、too_many_rows、invalid_cursor、zero_divide、others，注意这里的others类型要放在异常处理的最后面。不过这里有两个函数sqlcode()、sqlerrm()，它们分别为错误编号与错误信息
例如：

// 查询特定员工的工资（使用姓名查询）。

declare 
    v_empno emp.empno%type;
    v_sal emp.sal%type;
    v_ename emp.ename%type;
begin 
    v_empno:=&v_empno;
    select ename,sal into v_ename,v_sal from emp where empno=v_empno;
    dbms_output.put_line(v_empno||'的工资是：'||v_sal);
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('发生错误：'||'错误代码： '||sqlcode()||'  '||'错误信息'||sqlerrm());
end;

用户自定义异常

方法一

用户自定义异常分为三步骤： ①声明异常（异常名 exception;）②激活异常（raise 异常名;）③处理异常（when 异常名 then 处理;）

// 写一个用户自定义异常，实现与异常zero_divide同样的处理。

declare 
    v_a number;
    v_b number;
    zero_divide1 exception;			// 异常名定义
begin 
    v_a:=&v_a;			// 获得输入
    v_b:=&v_b;
    if v_b=0 then 			// 激活条件
        raise zero_divide1;		// 激活异常
    else 
    dbms_output.put_line(v_a/v_b);
    end if;
exception
    when zero_divide1 then 			// 处理异常
        dbms_output.put_line('v_b'||'不能为0');
end;

方法二
函数： raise_application_error(-a,b)， a代表// 写一个用户自定义异常，实现与异常zero_divide同样的处理。，范围为20000-20999, b代表错误信息。但是该函数有时会无法捕获错误

// 写一个用户自定义异常，实现与异常zero_divide同样的处理。

declare 
  v_a number;
  v_b number;
begin 
  v_a:=&v_a;
  v_b:=&v_b;
  if v_b=0 then 
    raise_application_error(-20001,'除数不能为0');	// 函数处理异常
  else
    dbms_output.put_line(v_a/v_b);
  end if;
end;

八、其他

开启屏幕的打印，该语句不能出现的PL/SQL块里面

set serveroutput on/off; 		// 开关屏幕输出

从键盘输入获得内容，注意如果要输入的内容为字符串或者日期类型的话就必须使用引号把输入内容括起来

v_names := '&v_name'				// v_name为变量名，这里的v_names是字符串类型的

// 例如：
select * from emp where empno = &empno;			// 不要加引号
select * from emp where ename = ‘&ename’;		// 要加引号

本文只用于学习与记录

laluneX

关注

0
点赞
踩
6

收藏

觉得还不错? 一键收藏
0
评论
oracle的学习Ⅴ（PL/SQL程序块）

一、什么是PL/SQL块PL/SQL（Program Language / Structure Query Language）程序控制块是指具用一定功能sql程序，它可以复用，不过要注意的是语句的缩进以增强语句的可读性例如：set serveroutput on; // 开启屏幕打印begin // 块开始标志 dbms_output.put_line('hello world!'); # 利用dbms_output包里面的put_line方法进行输出end; // 块结束标
复制链接

扫一扫