Oracle_PL/SQL

PL/SQL编程 （Procedure language /SQL）

PLSQL 是oracle对sql语言的过程化扩展，在sql命令语言中增加了过程处理语句（分支，循环等），使得SQL语言具有过程处理能力，把sql语言的数据操作能力与过程语言的数据处理能力结合起来，使得PLSQL 面向过程但比过程语言简单 高效 灵活和实用。

1、PL/SQL的语法

declare
	程序的声明部分：变量  常量  游标 
begin
	语句;
exception
	异常处理语句;
end;

1 . 常量和变量的定义
变量的类型就是oralce中建表时字段可使用的类型：char varchar2 date number boolean long blob clob

变量：

变量名  数据类型（长度） ；

常量：

变量名 constant 类型 := 值；

引用变量：

myname  emp.ename%type;-- myname变量的类型和emp表中的ename字段的类型一致；

记录类型变量：

--格式
	变量名 表名%rowtype；

--例：
	employee  emp%rowtype;--employee的类型 是emp表中的一行  

2 . if 分支语句

-- 语法一：
if  条件  then  
	语句  ;
end if;

--语法二：
if  条件  then   语句;
else 
	语句；
end if;

-- 语法三：
if 条件  then
	语句;
elsif 条件  then
语句;
else
	语句;
end if;

3 . loop 循环语句

-- 语法一：
while  条件 loop
	语句
end loop;
--语法二：
loop
   exit[when 条件]
   语句;
 end loop; 
-- 语法三：
for  起始值  IN 范围（起始值..结束值） loop
	语句;
end loop;

2、游标 Cursor
游标可以返回存储的多条数据

-- 格式：
Cursor 游标名[参数名  数据类型，参数名，数据类型....] is select  语句
-- 例如： 
cursor c1 is select * from emp;

游标的使用步骤：

• 打开游标： open 游标名（打开游标 执行查询）
• 取一行游标的值： fetch 游标名 into 行变量
  • 游标的迭代取值的结束方式： exit when 游标名%notfound；
• 关闭游标： close 游标名（释放资源）

--查询emp表中员工的编号和姓名
declare
  cursor pc is select * from emp;--声明游标
  --声明一个变量  一个记录类型的变量
  pemp emp%rowtype;
 begin
   --打开游标
   open pc;
   loop  fetch pc into pemp ;--遍历游标
   exit when pc%notfound;--结束遍历的条件
   dbms_output.put_line(pemp.empno||'--'||pemp.ename);
   end loop;--结束循环
   close pc;--关闭游标
 end;

3、 存储过程
存储过程（stored procedure） 是在大型数据库系统中，一组为了完成特定功能的SQL的语句集，经编译之后存储在数据库中，用户通过指定的存储过程的名字并给出参数（如果有参数）来执行他。存储过程是数据库中的一个重要的对象，任何一个设计良好的数据库应用程序都应该用到存储过程。

1 . 创建存储过程

--  语法：
create [or replace] procedure 过程名[(参数名 in/out/inout 数据类型)] AS
begin
	程序体；
end；

create [or replace] procedure 过程名 [(参数名 in/out 数据类型)] IS 
begin
	程序体；
end 过程名；

4、存储函数

create or replace function 函数名 （参数名 in 类型，参数名 in 类型） return 类型 is
结果变量 类型；
begin
return 结果变量；
end 函数名

**存储过程和存储函数的区别：**是否有返回值
存储过程没有返回值，函数可以有返回值
存储过程和函数中都可以通过out指定一个或多个输出参数，可以利用out参数在过程和函数中返回多个值

调用存储过程和函数时，参数的传递：
1）名称传参法：

begin
   addSal(eno => 7902); -- 把值(7902)赋给eno,进行调用
   commit;
end;

2）位置传参法：

declare
 income number;--定义一个变量  接收函数的返回值
begin
   income := empIncome(7902);
   dbms_output.put_line(income);
 end;

5、触发器（trigger）
数据库的触发器是一个与表相关联，存储的PL/SQL程序。当一个特定的数据库操作语句（insert，update，delete）在指定的表上发生时。Oracle会自动执行触发器中定义的语句

触发器的一般用处：

• 数据确认
• 实施复杂的安全性检查
• 做审计，跟踪表上所做的数据操作
• 数据备份和同步

触发器的类型：

• 语句级触发器：
  在指定的操作语句之前或之后执行一次，不管这条语句影响了多少行
• 行级触发器：
  触发语句作用的每一行记录都被触发，在行级触发器中使用 old 和 new 伪记录变量 识别值的状态

语法：

create or replace trigger 触发器名
	before|after
	delete|insert|update [of 列名] on 表名
	[for each row [when (条件)]]
begin
	程序体
end 触发器名； 

伪变量：（:new）和（:old）
:new：一个引用为最新的列值
:old ：一个引用为以前的列值

insert ：只有:new，没有:old ，所有字段都为将要插入的值
update：二者都有，更新前的值都在:old 中，更新后的值都在:new 中
delete：删除以前的值在:old 中，删除以后 :new 中的值都为null