oracle 存储过程_oracle dclare-CSDN博客

是Oracle系统的核心语言，现在Oracle的许多部件由其写成。可使用的SQL语句：

INSERT,UPDATE,DELETE,SELECT...INTO,COMMIT,ROLLBACK,SAVEPOINT（设置保存点）。

/*1.数据定义语言（DDL），包括CREATE（创建）命令、ALTER（修改）命令、DROP（删除）命令等。

\2. 数据操纵语言（DML），包括INSERT（插入）命令、UPDATE（更新）命令、DELETE（删除）命令、SELECT …FOR UPDATE（查询）等。

\3. 数据查询语言（DQL），包括基本查询语句、Order By子句、Group By子句等。

\4. 事务控制语言（TCL），包括COMMIT（提交）命令、SAVEPOINT（保存点）命令、ROLLBACK（回滚）命令。

\5. 数据控制语言（DCL），GRANT（授权）命令、REVOKE（撤销）命令。*/

2.结构和组成元素

2.1组成

PL/SQL的基本单位叫做块，三个部分组成：声明部分、执行部分、异常处理部分。

2.2结构

declare

/* 声明部分：在此声明PL/SQL用到的变量，类型及游标，以及局部的存储过程和函数 */

begin

/* 执行部分：过程及SQL语句，即程序的主要部分 */

exception

/* 执行异常部分：错误处理 */

end;

执行部分是必须的
编辑前set serveroutput on /不写没有执行结果/
赋值为 “：=”

2.3.变量命名(建议内容)

变量：V_name（V开头）常量：C_name

游标变量：Name_cursor 异常：E_name

记录类型：Name_record

2.4%TYPE与%rowtype

%TYPE

定义一个变量，其数据类型与已经定义的某个数据变量的类型相同。

例：V_test aev.test%type ;

变量表名字段名
%rowtype PL/SQL 提供%ROWTYPE操作符, 返回一个记录类型, 其数据类型和数据库表的数据结构相一致。使用%ROWTYPE特性的优点在于：所引用的数据库中列的个数和数据类型可以不必知道；所引用的数据库中列的个数和数据类型可以实时改变。取出一个列的记录可以存到使用rowtype 定义的变量里。

例：empmsg emp%rowtype ; -- 定义一个empmsg变量存一行的数据

2.5空格、换行符、回车表示

chr(32) chr(10) chr(13)

2.6记录类型

type.... is record(...);

3.流程控制

3.1 条件判断（两种）

方式一：if...then elsif then ... else ... end if;
方式二：case...when...then...end;

3.2 循环结构（三种）

简单循环：loop...exit when ...end loop;

Loop

要执行的语句；

EXIT WHEN<条件语句>； /条件满足，退出循环语句/

END LOOP；



declare

--初始化条件

v_i number(3) := 1;

begin

loop

--循环体

dbms_output.put_line(v_i);

--循环条件

exit when v_i = 100;

--迭代条件

v_i :=v-i+1;

end loop;

end;



begin

for c in 1..100 loop

dbms_output.put_line(c);

end loop;

end;
WHILE 循环(推荐)：while...loop ... end loop;

WHILE <布尔表达式> LOOP

要执行的语句；

END LOOP；



declare

v_i number(3) := 2;

v_j number(3) := 2;

v_flag number(1) :=2;

begin

while v_i <= 100 loop

while v_j <= sqrt (v_i) loop

if mod(v_i,v_j) = 0 then v_flag :=0;

end if;

end loop;

if v_flag = 1 then dbms_output.put_line(v_i);

end if;

v_j := 2;

v_i := v_i + 1;

v_flag := 1;

end loop;

end;
数字式循环：for i in ... loop end loop;

for 循环计数器 in[REVERSE] 下限 .. 上限 LOOP

要执行的语句；

END LOOP；
go to 、exit

plsql中GOTO语句是无条件跳转到指定的的标号取得意思。

语法： GOTO lablel；

<<label>> /标号是用<< >> 括起来的标识符/



begin

for v_i in 2 .. 100 loop

for v_j in 2 .. sqrt(v_i) loop

if mod(v_i, v_j) = 0

then v_flag :=0;

goto label;

end if;

end loop;

<<label>>

if v_flag = 1;

then dbms_output.put _line(v_i);

end if;

v_flag :=1;

end loop;

4.游标的使用（类似java的Iterator）

游标属性

%FOUND 布尔型属性，当最近一次读记录时成功返回，则值为TURE；

%NOTFOUND 布尔型属性,与%FOUND相反；

%ISOPEN 布尔型属性,当游标已打开时返回TURE；

%ROWCOUUNT 数字型属性，返回已从游标中读取的记录数。

定义概念

游标:实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。

游标可以被看作是一个查询结果集（可以是零条、一条或由相关的选择语句检索出的多条记录）和结果集中指向特定记录的游标位置组成的一个临时文件，提供了在查询结果集中向前或向后浏览数据、处理结果集中数据的能力。

有了游标，用户就可以访问结果集中任意一行数据，在将游标放置到某行之后，可以在该行或从该位置的行块上执行操作。

当决定对结果集进行处理时，必须声明一个指向该结果集的游标。

关系数据库管理系统实质是面向集合的，在MS SQL SERVER 中并没有一种描述表中单一记录的表达形式，除非使用where 子句来限制只有一条记录被选中。

因此我们必须借助于游标来进行面向单条记录的数据处理。由此可见，游标允许应用程序对查询语句select 返回的行结果集中每一行进行相同或不同的操作，而不是一次对整个结果集进行同一种操作；它还提供对基于游标位置而对表中数据进行删除或更新的能力；而且，正是游标把作为面向集合的数据库管理系统和面向行的程序设计两者联系起来，使两个数据处理方式能够进行沟通。

游标使用

主要分为两大类:

(1)声明游标

游标在使用之前必须先声明，可用DECLARE CURSOR语句声明游标，其一般格式为：

DECLARE INSENSITIVECURSOR <游标名>

IS <SELECT-查询块>

[FOR{READ ONLY|UPDATE[OF<列名>[,<列名>...]]}]

SCROLL——可以使用任何方法来存取数据，允许更新和删除。

FOR<SELECT-查询块>——使用SELECT查询块来定义一个游标。

FOR READ ONLY或FOR UPDATE——说明是只读的游标还是可修改的游标。

(2)打开游标

使用OPEN语句的功能打开游标，实际上就是执行相应的SELECT查询语句，将查询结果放到缓冲区。这时游标处于活动状态，游标指针指向第一条记录。其一般格式为：

OPEN<游标名>

提取游标

FETCH语句是从游标中提取特定的一行。其一般格式为：

FETCH

<游标名>

[INTO：<主变量1>,<主变量2>...]

说明：NEXT——如果是在OPEN后第一次执行FETCH命令，则返回结果集的第一行，否则使游标的指针指向结果集的下一行，NEXT是默认的选项。

PRIOR——返回结果集当前行的前一行。

FIRST——返回结果集的第一行。

LAST——返回结果集的最后一行。

ABSOLUTE n——如果n是正数，返回结果集的第n行，如果n是负数，则返回结果集的倒数第n行

RELATIVE n——如果n是正数，返回当前行后的第n行，如果n是负数，则返回当前行开始倒数的第n行。

INTO——该语句的功能是把游标取出的当前记录送入到主变量，INTO后的主变量要与在DECLARE CURSOR中SELECT的字段相对应。

(3)关闭游标

使用CLOSE 语句关闭游标，其一般格式为：

CLOSE<游标名>

(4)释放游标

使用DEALLOCATE语句释放游标，其一般格式为：

DEALLOCATE<游标名>。

declare

v_sal employees.salary%type;

--定义游标

cursor emp_sal_cursor

is select salary from employees where department_id = 80;

begin

--打开游标

open emp_sal_cursor；

--提取游标

fetch emp_sal_cursor into v_sal；

while emp_sal_cursor%found

loop

dbms_output.put_line('salary:'||v_sal);

fetch emp_sal_cursor into v_sal;

end loop;

--关闭游标

close emp_sal_cursor;

end;

第二种游标使用方法

是使用FOR循环语句，FOR循环是对查询结果的遍历，无需打开和关闭游标，也不需要抓取游标并下移，所以语句更加简洁。

示例如下：

declare

cursor cur_1 is

select ename from emp;

begin

for i in cur_1 loop

dbms_output.put_line(i.ename);

end loop;

end;

5.异常的处理方式（三种）

5.1异常概念

异常情况处理（exception）是用来处理正常执行过程中未预料的事件，程序块的异常处理预定义的错误和自定义错误，由于PL/SQl程序块一旦产生异常而没有指出如何处理时，程序块就会自动终止整个程序运行。

5.2三种异常类型

(1)预定义（Predefined）错误

Oracle预定义的异常情况大约有24个。无需在程序中定义，由Oracle自动将其引发。

(2)非预定义错误

即其他标准的Oracle错误。需要用户在程序中进行定义，然后由Oracle自动将其引发。

(3) 用户定义（User_define）错误

程序执行过程中，出现编程人员认为的非正常情况。需要用户在程序中定义，然后显示地在程序中将其引发。

5.3结构（一般放在程序的后半部分）

EXCEPTION

WHEN first_exception THEN <code to handle first exception>

WHEN second_exception THEN <code to handle second exception>

WHEN OTHERS THEN <code to handle others exception>

//OTHERS 必须放在其他所有异常后面。

6.存储函数和存储过程

6.1定义

oracle提供可以把PL/SQL程序存储在数据库中，并可以在任何地方来运行它，这样就叫做存储过程和函数。区别：存储函数总是向调用者返回数据，而存储过程不返回数据。

6.2格式

--函数的声明(有参数写在小括号里面）

create or replace function func_name(v_param varchar 2)

--返回值类型

return varchar 2

--pL/SQL块变量、记录类型、游标的声明

begin

--函数体（可以实现增删改查等操作，返回值需要return）

return ' helloworld' || v_logo;

end;

7.触发器（Triggers）

7.1定义

触发器（trigger）在数据库中以独立的对象存储，它的执行不是由程序调用，也不是手工启动，而是由事件来启动运行，即触发器是当某个事件发生时自动地隐式运行。

触发器不能接收参数。所以运行触发器就叫做触发或者点火。比如当对一个表或者视图进行操作（ insert，delete， update）时就会激活它执行。触发器经常用于加强数据的完整性约束和业务规则等。

7.2触发器组成

触发事件：在任何情况下触发TRIGGER；（INSERT、UPDATE、DELETE）

触发时间：事件发生之前（BEFORE)或是事件触发之后（AFTER）

触发器本身：即该触发器被触发之后的目的和意图，正是其本身要做的事情。

触发频率：说明触发器定义的动作被执行的次数。分为：

语句级（statement）触发器：事件发生时该触发器只执行一。

行级（Row）触发器：事件发生时，对受到该操作影响的每一行数据，触发器都单独执行一次。

7.3创建触发器

CREATE [OR REPLACE] TRIGGER trigger_name

{BEFORE | AFTER }

{INSERT | DELETE | UPDATE[OF column [,column ...]]}

ON [schema.] table_name

[FOR EACH ROW]

[WHEN condition ]

trigger_body;

！其他！

1.sqrt函数

返回一个数的平方根
语法：sqrt(test);

2.mod函数

含义：取余；
a mod b 等价于 a%b