基础

PL/SQL结构

1.声明 2.执行 3.异常

declare
 变量名字 类型;
begin
 程序运行
exception
 when 异常 then
  处理
end;

语法

字符集

不区别大小写
包括：
	所有大小写字母
	数字
	空白符
	符号：+-*等

分隔符

分隔符	描述
+,-, *, /	加法，减法/负，乘法，除法
%	游标属性绑定或代表任意字符的通配符
'	字符串分隔符
.	从属关系
,	表达式或列表分隔符
:	主机变量指示符
"	引用标识符分隔符
=	关系等
@	数据库链接符
;	声明或语句终止符
:=	赋值运算符
=>	位置定位符
ΙΙ	字符串连接运算符
**	指数运算符
<<, >>	标签分隔符(开始和结束)
/, /	多行注释分隔符(开始和结束)
--	单行注释指示符
..	范围运算符
<, >, <=, >=	关系运算符
<>, '=, ~=, ^=	不同版本的”不等于”运算符

常量

1.字符型
2.数字
3.布尔
4.日期

注释

单行 --
多行 /* */

初值

每一行只能定义一个变量
constant代表常量必须赋初值
not null 关键字不行赋初值
:= / default 赋初值

sql语句

SELECT语句

select into语句注意这个语句一次只能记录一个信息如果没有查到则会发生NO_DATA_FOUND异常如果查到多个则会产生TOO_MANY_ROWS异常查询b员工的信息和id为109员工的名字

declare
 v_emp emp%rowtype;
 v_name emp.name%type;
begin
 select * into v_emp from emp where name='b';
 dbms_output.put_line(v_emp.id||' '||v_emp.salary);
 select name into v_name from emp where id = 109;
 dbms_output.put_line(v_name);
end;

DML语句

pl/sql中对dml语句进行了扩展，允许使用变量

控制结构

选择判断

if

if 条件 then 语句;
elsif 条件2 then 语句2;
else 语句3;
end if;

条件中：如果值为null则视为flase

case 语句

case
 when 条件 then ..;
 when ... then ...;
end case;

循环

简单循环

loop
 .....;
 exit [when 退出条件];
end loop;

while

while 进入条件 loop
 .....;
end loop;

for

for 循环变量 in {reverse} 下界..上界
 .....;
end loop;

1.循环变量不需要显示的定义 2.系统默认变量从下界到上界，如果使用reverse 则从上界到下界递减 3.循环变量只能在循环用

例子求1-100之间偶数和

//普通语句
declare
 v_count binary_integer :=1;
 v_sum number :=0;
begin
 loop
  if mod(v_count,2)=0 then
   v_sum:=v_sum+v_count;
  end if;
  v_count := v_count + 1;
  exit when v_count > 100;
 end loop;
 dbms_output.put_line(v_sum);
end;

declare
 v_count binary_integer :=1;
 v_sum number :=0;
begin
 while v_count <=100 loop
  if mod(v_count,2)=0 then
   v_sum:=v_sum+v_count;
  end if;
  v_count := v_count + 1;
 end loop;
 dbms_output.put_line(v_sum);
end;

 declare
 v_sum number :=0;
begin
 for v_count in 1..100 loop
  if mod(v_count,2)=0 then
   v_sum:=v_sum+v_count;
  end if;
 end loop;
    dbms_output.put_line(v_sum);
end;

跳转结构

goto实现
<<>>标记
goto 标记;

pl/sql内部可以跳转，内可以跳到外，外不能跳到内

输入

虽然我不知道sql需要这种输入干嘛，不过要考，还是搞一下。

输入可以在定义阶段和代码阶段来进行,通过&符号来表示
变量:=&提示;

输出

 dbms_output.put_line(输出的内容)；

游标

前面我们知道了select into一次只能查询一条语句。
这里介绍利用游标处理select语句返回多行数据

还可以通过select……bulk collect into处理多行数据

游标分为：
	显示游标：用户定义，多行select查询
	隐式游标：系统自动，DML和单行select查询

显示游标

定义游标

语法：

 cursor 名字 is select查询

1.必须在声明部分定义 2.定义时可以引用PL/SQL变量，但必须在游标定义前 3.定义时没有生成数据，只是保存在数据字典中。 4.可以使用名字%rowtype 定义记录类型的变量。

打开游标

 open 变量名

1.只有打开游标才会，查询 2.一旦打开就无法再次打开，除非关闭 3.如果游标定义中变量值发生改变，则只能在下次打开才能其作用

检索游标/遍历

 fetch 游标名 into 行名

关闭游标

 close 名

游标属性

属性	数据类型	含义
%ISOPEN	布尔	游标是否打开
%FOUND	布尔	判断最近一次使用fetch语句是否有数据
%NOTFOUND	布尔	判断最近一次是否没有数据，没有返回true
%ROWCOUNT	数值	检索个数
%BULK_ROWCOUNT	数值	第i给元素所影响的行数

循环检索实例

输出各个部门的平均工资普通循环

declare
 cursor c_dep IS select d_id,avg(salary) avgsfrom emps group by d_id;
 v_dep c_dep%rowtype;
begin
 open c_dep;
 loop
  fetch c_dep into v_dep;
  exit when c_dep%notfound;
  dbms_output.put_line(v_dep.avgs||' '||v_dep.d_id);
 end loop;
 closse c_dep;
end;

在这里插入图片描述 while循环

declare
 cursor c_dep IS select d_id,avg(salary) avgsfrom emps group by d_id;
 v_dep c_dep%rowtype;
begin
 open c_dep;
 while c_dep%found loop
  fetch c_dep into v_dep;
  dbms_output.put_line(v_dep.avgs||' '||v_dep.d_id);
 end loop;
 closse c_dep;
end;

for 循环 for循环会自动打开检索和关闭游标

declare
 cursor c_dep IS select d_id,avg(salary) avgsfrom emps group by d_id;
begin
 for v_dep in c_dep loop
  dbms_output.put_line(v_dep.avgs||' '||v_dep.d_id);
 end loop;
end;

对于for循环而已，都是自动进行，所以可以不在声明部分定义游标，而是直接在for语句中使用，上面代码可以写成这样。

begin
 for v_dep in (select d_id,avg(salary) avgsfrom emps group by d_id) loop
  dbms_output.put_line(v_dep.avgs||' '||v_dep.d_id);
 end loop;
end;

隐式游标

所有的sql语句都有一个执行的缓冲区，隐式游标就是该缓冲区的指针，又称sql游标

属性

属性	类型	说明
SQL%isopen	布尔	是否打开
SQL%found	布尔	判断是否对数据库产生影响，如果插入、删除、修改或者查到数据则true
SQL%notfound	布尔	判断是否没有产生影响
SQL%rowcount	布尔	影响行数

游标变量

定义

引用类型

type 名字 is ref cursor [return 类型]

系统预留一个叫sys_refcursor的引用类型

变量

名字 引用类型名字；

打开游标变量

open 游标 for select的sql；

检索游标

 loop
  fetch 名 into ...;
  exit when 名%notfound；
 end loop；

只能用简单循环和while，不能用for。

关闭游标

close 名；

异常

异常概述

异常分为：预定异常、非预定异常、用户异常。常见异常：这些例举的是我遇到的较多的，详细的可以去搜。

预定义异常	oracle错误	说明
too_mangy_rows	ora-01422	返回多行数据
no_data_found	ora-01403	没有发现数据
zero_divide	ora-01476	除数为0

异常处理

异常定义

异常变量 exception;

还需要与一个oracle错误相关联 pragma exception_init(异常变量，错误号(-20999~-20000))

异常抛出

raise 异常;

异常捕获

exception
 when 异常 then 处理;
 ...;
end;

others异常处理器

总是作为最后的一个异常处理器，处理没有被其他异常捕获的异常。其中有2个函数

函数	描述
sqlcode	当前错误码
sqlerrm	错误文本

例题

1.要求根据输入的不同表名进行不同处理，若表名为employees，则显示高于10号部门平均工资的员工信息。若表名为departments，则显示各个部门的人数。

完成这个需求有这样几个sql 查询高于10号部门平均工资的员工信息 select * from employees where salary > (select avg(salary) from employees where department_id =10); 各个部门的人数 select count(*) num ,department_id from employees group by department_id;

declare
 v_table char(20);
 type t_cursor is ref cursor;
 v_cursor t_cursor;
 v_emp employees%rowtype;
 v_deptno employees.department_id%type;
 v_num number;
begin
 v_table:='$table_name';
 if v_table = 'employees' then
  open v_cursor for select * from employees where salary > (
   select avg(salary) from employees where department_id =10);
  while v_cursor%notfound loop
   fetch v_cursor into v_emp;
   dbms_output.put_line(v_emp....);
  end loop
 elseif v_table ='departments' then
  open v_emp for select count(*) num ,department_id from employees group by department_id;
  while v_cursor%notfound loop
   fetch v_cursor into v_num,v_deptno;
   dbms_output.put_line(v_deptno||' '||v_num);
  end loop
 else
  raise_application_error(-20000,'Input must be ''emp'' or ''dept''');
 end if
 close v_cursor
end;