oracle过程、函数、触发器、程序包学习笔记

1、PL/SQL块结构(创建表test_plsql)

[declare]

--声明部分，可选

Begin

--执行部分，必须

[exception]

--异常处理部分，可选

[end]

 

2、数据类型与定义变量和常量

特殊数据类型

%TYPE类型：声明一个与列名称相同的数据类型。

Eg；declare var_job emp.job%TYPE;

Record类型：类似于C/C++的结构体。

%ROWTYPE：结合%TYPE和RECORD的优点。

 

3、定义变量和常量

定义变量：<变量名><数据类型>[(长度):=<初始值>];

Eg：var_countryname varchar2(50):=’中国’;

定义常量：<常量名>constant<数据类型>:=<常量值>;

Eg：con_day constant integer:=365;

 

4、流程控制语句

选择语句：if...then...endif/if...then...else...endif/if...then...elsif/case<>when<>then;...end case/

declare

i int:=0;

j int:=100;

begin

loop

       ifi>=0 and i<=20 then

       insertinto test_plsql(x,y,z,ps) values(i,i,2*i,'i>=0 and i<=20');

       elsifi>20 and i<=40 then

       insertinto test_plsql(x,y,z,ps) values(i,i,3*i,'i>20 and i<=40');

       elsifi>40 and i<=70 then

       insertinto test_plsql(x,y,z,ps) values(i,i,5*i,'i>40 and i<=70');

       else

       insertinto test_plsql(x,y,z,ps) values(i,i,i,'i>70 and i<=100');

       endif;

i:=i+1;

exit when i>100;

end loop;

end;

/

循环语句：

Loop

plsql_sentence;

Exit when end_condition_exp

End loop;

 

While condition_expression loop

plsql_sentence;

End loop;

 

For variable_counter_name in [reverse]lower_limit...upper_limit loop

plsql_sentence;

End loop;

 

Eg:

declare

end_condition int:=10;

i int:=1;

begin

loop

insert intotest_plsql(x,y,z,ps)values(i,i+1,2*i+1,'loop insert');

i:=i+1;

exit when i>end_condition;

end loop;

end;

/

5、PL/SQL游标

 游标：主要用于服务器上，处理由客户端发送给服务器端的SQL语句，或是批处理、存储过程、触发器的数据请求。

显示游标：由用户声明和操作，通常用于操作查询结果集

隐式游标：系统自动创建，默认名SQL，用来判断更新数据行和删除数据行的情况。

REF游标：

 

显示游标：

申明游标打开游标读取游标数据为空？关闭游标

声明游标：

Cursor cur_name[input_parameter1([,input_parameter2]...])

[return ret_type]

Is select_sentence;

--input_parameter1的定义和初始化同变量的定义和初始化；

 

 

打开游标：

Open cur_name[(para_value1[,para_value2]...)];

--para_value1有值则为改值，无值则为初始化值。

 

读取游标：

Fetch cur_name into {varible};

--varible RECORD类型变量，oracle使用记录型变量来保存游标中的数据；

 

判断数据是否为空：(游标属性)

%found：如果sql语句至少影响到一行数据，则该属性为true，否则为false。

%notfound：与%found属性的功能相反

%rowcount：返回受SQL语句影响的行数

%isopen：判断游标是否已经打开，当游标已经打开时返回ture，关闭时则为false

 

Eg：

Declare

var_test test_plsql.ps%type;

cursor cur_test(var_init invarchar2:='i>70 and i<=100')

is select id,x,y,z,ps

from test_plsql

where ps=var_init;

type record_test is record

(

       var_idtest_plsql.id%type,

       var_xtest_plsql.x%type,

       var_ytest_plsql.y%type,

       var_ztest_plsql.z%type,

       var_pstest_plsql.ps%type

);

 test_row record_test;

begin

var_test:='i>=0 and i<=20';

open cur_test(var_test);

fetch cur_test into test_row;

while cur_test %found loop

       insertinto test_plsql2(id,x,y,z,ps)

       values(test_row.var_id,test_row.var_x,test_row.var_y,test_row.var_z,test_row.var_ps);

fetch cur_test into test_row;

end loop;

close cur_test;

end;

/

 

 

隐式游标：

在执行SQL语句时，oracle自动创建，为内存中处理该语句的工作区域，主要用于处理数据操纵语句（update，delete）的执行结果，特殊情况下也可用于处理select语句。隐式游标也有属性，使用时需使用隐式游标默认名sql。经常用来判断更新数据行和删除数据行的情况。

 

 

通过for语句循环游标

显示结合：

for var_auto_record in cur_name loop

plsqlsentence；

end loop;

隐式结合：

for var_auto_record in selectsentence loop

plsqlsentence；

end loop;

 

注意：使用游标的for循环中，可以声明游标，但不用打开、读取、关闭游标等操作，这些由oracle系统内部自动完成。

 

6、异常处理

预定义异常；

错误编号异常；

业务逻辑异常；

 

 

7、过程、函数、触发器和包

 

存储过程：（pro_insert(1,2,3,4,5)）

基本语法格式：

create [or replace] procedure pro_name[(para1[,para2]...)] is|as

begin

plsql_sentences;

[exception]

[dowith_sentences;]

end [pro_name];

 

Eg

create or replace procedure pro_insert(

para_id in number,

para_x in number,

para_y in number,

para_z in number,

para_ps in varchar2

) is

null_exception exception;

begin

insert into test_plsql(id,x,y,z,ps)

values(para_id ,para_x,para_y,para_z,para_ps);

if para_id is null then

       raisenull_exception;

end if;

exception

       whennull_exception then

       rollback;

end pro_insert;

/

 

存储过程参数：

IN 模式参数，in参数可定义默认值（in_parain varchar2 default ‘hhh’）

OUT模式参数：可使用 print out_para\select :out_para from dual 查看。

IN OUT模式参数

 

函数：

创建函数：

create [or replace] function fun_name[(para1[,para2]...)]return data_type is|as

[inner_variable]

begin

plsql_sentences;

[exception]

[dowith_sentences;]

end [fun_name];

删除函数：

Drop function fun_name;

 

触发器：

通过触发事件触发运行，执行DML语句/DDL语句/引发数据库事件/引发用户事件

创建触发器：

create [or replace] trigger tri_name

[before|after|instead of]tri_event

on table_name|view_name|user_name|db_name

[for each row][when tri_condition]

begin

plsql_sentences;

end tri_name;

 

 

 

触发器分类：

行级触发器：针对DML操作所影响的每一行数据都执行一次触发器。使用for each row。

应用：用来自动生成主键ID，给数据表生成主键值。

1、创建序列：

create sequence seq_name;

2、创建行级触发器;

create or replace triggertri_name 

before insert

on table_name

for each row

begin

  select seq_name.nextval

  into :new.column_name

--列标识符（新值标识符，:old.column_name旧值标识符）

  from dual;

end;

Trigger created

3、删除触发器：

drop triggertri_name;

 

语句级触发器：针对一条DML语句而引起的触发器的执行，不执行for each row子句。

create or replace trigger tri_log

after insert or update or delete

on test_plsql

declare

       var_tagvarchar2(20);

begin

       ifinserting then

       var_tag:='插入';

       elsifupdating(ps) then //是否更新了ps这一列。

       var_tag:='更新';

       elsifdeleting then

       var_tag:='删除';

       endif;

       insertinto plsql_log values(var_tag,sysdate);

end tri_log;

/

 

替换触发器：触发时机（insteadof），定义在视图非表上--将对师徒的操作替换成对记标的                 操作

用户事件触发器：因进行DDL操作或用户登录、退出等操作引起。

系统事件触发器：

 

 

程序包：

由PL/SQL程序元素（如变量、类型）和匿名PL\SQL块（如游标）、命名PL\SQL块（存储过程和函数）组成。

程序包规范：

create [or replace] package pack_name is

[declare_var];

[declare_type];

[declare_cursor];

[declare_function];

[declare_procedure];

End [pack_name];

 

程序包主体：

create [or replace] package body pack_nameis

[inner_var];

[cursor_body]

[function_title]

{

begin

fun_plsql;

[exception]

[dowith_sentences;]

end[fun_name]

}

[]

{

begin

pro_plsql;

[exception]

[dowith_sentences;]

end [pro_name]

}

...

end [pack_name];