oracle触发器函数,Oracle 中的PL/SQL基础、存储过程、触发器、函数、包（学习笔记）...

一、PL/SQL是什么？

PL/SQL(procedural language/SQL)是oracle在sql上的扩展，pl/sql不仅允许嵌入sql语言，而且可以定义常量和变量，允许使用条件语句和循环语句，允许使用例外处理各种错误，这使得它的功能十分强大。但是移植性不好。

1. 实例1 只包括执行部分的PL/SQL块set serveroutput on;

begin

dbms_output.put_line('asdasdasdas');

end;

/

set serverout 选项为打开显示。

dbms_output为oracle提供的包

2. 实例2 包含输入的PL/SQL块declare

v_name varchar2(10);

v_sal number(7,2);

begin

select ename,sal into v_name,v_sal from emp where empno=&no;

dbms_output.put_line('雇员名:'||v_name);

dbms_output.put_line('薪水:'||v_sal);

end;

/

说明：首先声明了类型为varchar2的v_name变量和number的v_sal，在pl/sql语句块中执行搜索，用empno搜索出emp表的ename和sal字段的值，&no表示要在执行过程中输入empno这个参数。把查询的ename值存入v_name中，sal放入v_sal中，然后再显示出来。

3. 实例3 包含异常处理的PL/SQL块declare

v_name varchar2(10);

v_sal number(7,2);

begin

select ename,sal into v_name,v_sal from emp where empno=&no;

dbms_output.put_line('雇员名:'||v_name);

dbms_output.put_line('薪水:'||v_sal);

exception

when no_data_found then

dbms_output.put_line('输入数据有误！');

end;

/

这个例子前面与实例2完全相同，只是加上了异常处理部分。这是一个完整的PL/SQL块。

二、过程(存储过程)

存储过程用于执行特定的操作。建立过程时，既可以指定输入参数(in)，也可以指定输出参数(out)。通过在过程中使用输入参数，可以将数据传递到执行部分；通过使用输出参数，可以将执行部分数据传递到应用环境中。在sqlplus中使用create procedure命令 创建过程。create procedure procedure_name(name varchar2,sala number) is

begin

update emp set sal=sala where ename=name;

end;

/

说明： 创建一个名为procedure_name的过程，它又2个输入参数，varchar2类型的name和number类型的sala，begin与end之间的是plsql块。

三、函数

函数用于返回特定的数据，在建立函数时，头部必须包含return子句，而函数体内必须包含return返回的数据，可以用create function 命令建立函数。实例：create function function_name(name varchar2)

return number is

salary number(7,2);

begin

select sal*12 into salary from emp where ename=name;

return salary;

end;

/

四、包

包用于在逻辑上组合函数和过程，由包规范和包体组成。可以用create package 命令创建包。

实例：create package package_name is

procedure procedure_name(name varchar2,sal number);

function function_name(name varchar2) return number;

end;

/

包的规范只包含对函数和过程的说明，但是没有函数和过程的实现代码，包体用于实现包规范中的函数和过程，建立包体使用create package body命令。create package body package_name is

procedure procedure_name(name varchar2,sala number) is  --创建过程

begin

update emp set sal=sala where ename=name;

end;

function function_name(name varchar2)    --创建函数

return number is

salary number(7,2);

begin

select sal*12 into salary from emp where ename=name;

return salary;

end;

end;

/  --结束

使用包内的函数或者过程：exce package_name.procedure_name('SMITH',500); --调用过程

var sal number;  --声明变量

call package_name.function_name('SMITH') into:sal;  --调用函数

五、触发器

触发器指隐含执行的过程，当创建触发器时，必须指定触发的条件和触发的操作。常用的触发事件包括insert、update、delete，而触发操作实际就是一个PL/SQL块，可以使用create trigger命令来创建一个触发器。

六、变量

在编写PL/SQL程序时可以定义变量和常量，主要包括：

1.标量类型(scalar)

2.复合类型(composite)

3.参照类型(reference)

4.lob(large object)

(1) 标量定义案例：

定义一个变长字符串：v_name varchar2(10);

定义一个小数，范围-9999.99到9999.99： v_num number(6,2);

定义一个小数并赋值： v_num number(5,2):=10.6; ":="相当于“=”，是赋值符号

定义一个日期类数据: v_date date;

定义一个布尔变量,不能为空，初始值为true：v_bool boolean not null default true;

(2)复合变量介绍：

用于存放多个值的变量，类似于高级语言的结构体，主要包括：PL/SQL记录，PL/SQL表、嵌套表、varray。

自定义一个record类型type type_name is record( name varchar2(10),sal number(5,2) );

自定义一个PL/SQL表类型type type_name is table of table_name.field_name%type index by binary_integer;

(3)参照类型：

参照变量是指用于存放数值指针的变量，通过使用参照变量，可以使应用程序共享相同的对象，从而降低内存占用的空间。在编写PL/SQL程序时，可以使用游标变量和对象类型变量2种参照变量类型。

游标变量：使用游标时，当定义游标时不需要指定相应的selec语句但是当使用游标时(open时)需要指定select语句，这样一个游标就与select语句结合了。declare  --声明

type type_name is ref cursor;  --自定义type_name为游标类型

cursor_name type_name ;        --声明一个名为cursor_name的type_name游标

v_ename emp.ename%type;        --声明一个与emp表中的ename类型相同的变量v_ename

v_sal emp.sal%type;            --声明一个与emp表中的sal类型相同的变量v_sal

begin --执行

open cursor_name for select ename,sal from emp where deptno=&no;

--打开游标，并指向select语句

loop  --循环

fetch cursor_name into v_ename,v_sal;--取出游标中的数据到变量

dbms_output.put_line('名字:'||v_name||'工资:'||v_sal);  --显示

exit when cursor_name%notfound; --判断游标是否为空，为空就退出

end loop;

end;

/

或者这样声明游标：declare

cursor cus is   --声明游标并指向

select ename,sal from emp where deptno=&no;

v_ename emp.ename%type;

v_sal emp.sal%type;

begin

open cus;

loop

fetch cus into v_ename,v_sal;

dbms_output.put_line('name:'||v_ename||'sal:'||v_sal);

exit when cus%notfound;

end loop;

end;

/