ITPub博客

首页 > 数据库 > Oracle > Oracle 数据库 PL/SQL [函数 function , 存储过程 PROCEDURE , 触发器 Trigger , 任务 JOB] 入门教程

Oracle 数据库 PL/SQL [函数 function , 存储过程 PROCEDURE , 触发器 Trigger , 任务 JOB] 入门教程

原创 Oracle 作者:wyd2002 时间:2007-12-06 12:49:01 0 删除 编辑

<说明: 该文章是我半年前写的,保存格式是 TXT,如果有兴趣 COPY到TXT文件上,格式应该会好一些。>

PL/SQL(Procedural Language/SQL , 过程语言/SQL) 是结合了 Oracle 过程语言 和结构化查询语言(SQL)的一种扩展语言。

PL/SQL 可用来创建存储过程,触发器,程序包等,也用来处理业务规则,数据库事件或者给SQL命令的执行添加程序逻辑。

PL/SQL 与 Oracle服务器 和 Oracle工具紧密集成,因而具有可移植性和安全性。

PL/SQL的优点:
》支持 SQL
》支持面向对象的编程(OOP)
》更好的性能
》可移植性
》与SQL集成
》安全性

PL/SQL 体系结构:

PL/SQL 是一种块结构的语言,它将一组语句放在一个块中。构成PL/SQL程序的基本单元是逻辑块(如过程,函数和匿名块),该逻辑块可以包含任何数量的嵌套子块,每个逻辑块对应要解决的问题或子问题。

匿名块 是一个未在数据库中命名的PL/SQL块,在运行时被传递到PL/SQL引擎以便执行。

在PL/SQL块中可以使用SELECT ,INSERT ,UPDATE ,DELETE ,等DML语句,事物控制语句以及SQL函数等。
在PL/SQL块中不允许直接使用CREATE , DROP , ALTER 等 DDL语句,但是通过动态SQL来执行他们。

PL/SQL块可以分为 :
声明部分:

可执行部分:

异常处理部分:

结束符号。

[DECLARE
declarations]

BEGIN
executable statements

[EXCEPTION
exception handlers]

END;


PL/SQL 的 一些特征:
(1)PL/SQL对大小写不敏感,但是用户和用户的开发团队应该选者一个合适的编码标准,以确保最好地使用共享池。
(2)在PL/SQL块的可执行部分引用变量和常量前,必须先对其进行声明。变量和常量在PL/SQL块的声明部分声明,在PL/SQL块的可执行部分使用它们。


PL/SQL中的一些符号:
:= 赋值操作符号
|| 连接操作符号
-- 单行注释符号
/* ... */ 多行注释符号
<<,>> 标签分隔符号
.. 范围操作符号
** 求幂操作符号


声明变量:
声明变量时必须指定变量的数据类型,乐意在声明变量时初始化。
语法:variable_name data_type[(size)] [:=init_value];

变量的赋值:
可以在声明的时候赋值;
可以在自行部分赋值;
可以通过SELECT...INTO..语句从数据库中提取记录并且将侄赋给变量

常量的声明:
常量在声明的时候被赋予初始值:
语法:variable_name constant data_type := value;

OR : variable_name constant data_type default value;

一条语句只能声明一个变量。
例如: 以下声明是非法的:I,J number(4);
应该写成:I NUMBER(4);J NUMBER(4);


数据库PL/SQL 内置的数据类型:
标准数据类型:
数字 字符 布尔类型 日期类型
LOB类型:
组合数据类型:
应用数据类型:

用户还可以定义自己的子类型和使用属性类型,
属性类型(%TYPE %ROWTYPE)

数字数据类型:
分为三种:
BINARY_INTEGER , NUMBER 和 PLS_INTEGER;

BINARY_INTEGER 用于存储带符号的整数。范围是 -(2^31)-1 到 (2^31)-1
NATURAL 可以限制变量存储非负整数值,既自然数
NATURALN 可以限制变量存储自然数,并且非空
POSITIVE 可以限制变量存储正整数
POSITIVEN 可以限制变量存储正整数,并且非空
SIGNTYPE 可以限制变量只存储值 -1,0,1三个值

NUMBER 用于存储整数,定点数和浮点数。范围是 1E-130 到 10E125
NUMBER [(precision,scale)] precision:精度 scale:小数位数(后面的四舍五入)

NUMBER(precision) == NUMBER(precision,0)是声明不带小数的整数

子类型包括:
DECIMAL 用于声明最高精度为38位的十进制数字的定点数。
FLOAT 用于声明最高精度为126位的二进制数字的浮点数。
INTEGER 用于声明最高精度为38位的十进制数字的整数
REAL 用于声明最高精度为63位二进制数字(大约相当于18位十进制数字)的浮点数

PLS_INTEGER
用于存储带符号的整数。PLS_INTEGER 的大小范围介于-2^31 到 2^31 之间。与NUMBER 和 BINARY_INTEGER 相比,它的运算速度更快。

字符数据类型:
CHAR
语法:CHAR [(maximum_size[char|byte])]

举例 : CHAR(12 CHAR) 表示能存12个字符长度的内容
CHAR(16) 表示能存16个字节长度的内容(中文字符 占两个字节)
注意:最大不能超过32767个字节。

RAW 此类型用于存储二进制数据或字符串。
语法:RAW(maximum_size)

LONG

LONG ROW

VARCHAR2
此类型变量可以容纳可变长度字符串。其属性类似CARCHAR2数据库类型。
语法:VARCHAR2(maximum_size[char|BYTE])

日期时间数据类型
DATE

TIMESTAMP[precision]
precision 是精度。
precision 是可选参数。在指定精度参数时,它代表秒字段小数部分中的位数

布尔数据类型
BOOLEAN
本数据类型可以是用于存储逻辑值 true,false,null.他们不带任何参数。不能将boolean数据
插入到数据库列中,不能将列值提取或者选择到BOOLEAN 变量中。
只允许boolean变量执行逻辑操作。

定义记录类型变量
TYPE record_type IS RECORD(
Field1 type1 [NOT NULL] [:= exp1 ],
Field2 type2 [NOT NULL] [:= exp2 ],
. . . . . .
Fieldn typen [NOT NULL] [:= expn ] ) ;
使用定义的记录变量类型来声明变量:
record_name record_type;
---------
实例:
DECLARE
TYPE emp IS RECORD(
id s_emp.id%type,
name s_emp.name%type,
salary s_emp.salary%type);

v_emp emp;
BEGIN
select id,name,salary into v_emp from s_emp where id=10;
dbms_output.put_line('id='||v_emp.id||',name='||v_emp.name||',salary='||v_emp.salary);

END;
/
---------

属性类型:
%TYPE
引用某个变量或数据库列的数据库类型来声明变量。

例如:
icode itemfile.itemcode%TYPE;

%ROWTYPE
提供表示表中一行的记录类型。记录类型可以存储从表中选择或由游标提取的整行数据。
emp_rec emp%ROWTYPE;


逻辑比较
= 等于
<> != 不等于
< 小于
> 大于
<= 小于等于
>= 大于等于


控制结构:

条件控制:条件控制包括IF语句 和 CASE语句

IF语句
IF语句有3种形式:IF-THEN IF-THEN-ELSE IF-THEN-ELSIF

IF-THEN
IF condition THEN
sequence_of_statements;
END IF;

IF-THEN-ELSE
IF condition THEN
sequence_of_statements1
ELSE
sequence_of_statements2
END IF;

IF-THEN-ELSIF
IF condition1 THEN
sequence_of_statements1
ELSEIF condition2 THEN
sequence_of_statements2
ELSE
sequence_of_statements3
END IF;


CASE语句
结构紧凑的CASE语句用语根据条件将单个变量或者表达式与多个值进行比较。
它不接受WHEN子句中的比较运算符。
CASE语句使用选择器与WHEN子句中的表达式匹配而不是与多个布尔表达式匹配 。

CASE selector
WHEN expression1 THEN sequence_of_statements1;
WHEN expression2 THEN sequence_of_statements2;
WHEN expression3 THEN sequence_of_statements3;
WHEN expression4 THEN sequence_of_statements4;
...
[ELSE sequence_of_statementsN+1;]
END CASE;

举例:
SET SERVEROUTPUT ON --这样写是打开输出开关。如果没有打开就
BEGIN
CASE '&wyd'
when 'a' then dbms_output.put_line('aa');
when 'b' then dbms_output.put_line('bb');
when 'c' then dbms_output.put_line('cc');
when 'd' then dbms_output.put_line('dd');
else dbms_output.put_line('没有选项');
END CASE;
END;
/

我们输入a,打印出 aa.
我们输入A,打印出 ‘没有选项’。

循环控制:
循环控制包括: LOOP 和 EXIT语句。
使用EXIT语句可以立即退出循环。
使用EXIT WHEN 语句 可以根据条件结束循环。

循环共有3种类型,具体包括:
LOOP循环
语法: LOOP
sequence_of_statements;
END LOOP;

该循环将无限循环下去,为了能够结束,中间要加入判断,当判断达成,运行EXIT;

例子:
BEGIN
LOOP
IF &marks >60 THEN
DBMS_OUTPUT.PUT_LINE('已经通过');
EXIT;
ELSE DBMS_OUTPUT.PUT_LINE('没有通过');
END IF;
END LOOP;
END;

小心哦,输入小于60的数就会出现死循环哦!!


WHILE 循环
语法:
WHILE condition LOOP
sequence_of_statement;
END LOOP;

FOR 循环
语法:
FOR counter IN [REVERSE] value1 .. value2
LOOP
sequence_of_statements;
END LOOP;
reverse 在 for 循环中属于可选项,只有在需要对值从大到小执行循环时,才会使用reverse关键字。

例子:
SET SERVEROUTPUT ON
BEGIN
FOR QQ IN reverse 1..25
LOOP
DBMS_OUTPUT.PUT_LINE('你知道吗?'||QQ*2);
END LOOP;
END;
/

顺序控制:

GOTO语句
无条件的将控制权交到标签指定的语句。标签是用双尖括号括起来的标识符,在PL/SQL块内
必须具有唯一的名称,标签后必须紧跟着可执行语句或者PL/SQL块。GOTO语句不能跳转到IF
语句,CASE语句,LOOP语句或子块中。

NULL语句
什么也不做,只是将控制权转到下一条语句。NULL语句是可执行语句。NULL语句用在IF或其他语句
语法要求至少需要一条可执行语句。但又不需要执行操作的情况。

举例:(GOTO AND NULL)
DECLARE
wyd varchar2(30);
BEGIN
wyd:='&wyd';
IF wyd = 'gg' THEN
GOTO updation;
ELSE
GOTO quit;
END IF;
<>
DBMS_OUTPUT.PUT_LINE('我是001');
DBMS_OUTPUT.PUT_LINE('我是002');
<>
DBMS_OUTPUT.PUT_LINE('我是003');
DBMS_OUTPUT.PUT_LINE('我是004');
END;

结果提示:
如果我们输入 gg ,输出 我是001,我是002,我是003,我是004
如果我们输入其他,输出 我是003,我是004




动态SQL
在一般的PL/SQL程序开发中,可以使用SQL的DML语句和事物控制语句,但是DDL语句以及会话控制与却不能在
PL/SQL中直接使用,要想实现在PL/SQL中使用DDL语句以及会话控制语句,可以通过动态SQL来实现。

所谓动态SQL是指在PL/SQL块编译时SQL语句是不确定的,例如根据拥护输入参数的不同而执行不同的操作。
编译程序对动态语句部分不进行处理,只是在程序运行时动态地创建语句,对语句进行语法分析并且执行该语句。

ORALCE 中的动态SQL可以通过本地动态SQL命令来执行,也可以通过DBMS_SQL程序包来执行。

执行动态SQL的语法是:
EXECUTE IMMEDIATE dynamic_sql_string
[INTO define_variable_list]
[USING bind_argument_list];


举例:
SET SERVEROUTPUT ON
DECLARE
sql_stmt varchar2(200);
student_id student.id%TYPE:=1;
student_name student.name%TYPE;
student_class student.class%TYPE;
BEGIN
sql_stmt :='create table teacher (id number(10),name varchar2(30))';
DBMS_OUTPUT.PUT_LINE('sql_stmt='||sql_stmt);
DBMS_OUTPUT.PUT_LINE('要执行sql_stmt了!');
EXECUTE IMMEDIATE sql_stmt;

sql_stmt :='select name,class from student where id=:id';
DBMS_OUTPUT.PUT_LINE('sql_stmt='||sql_stmt);
DBMS_OUTPUT.PUT_LINE('要执行sql_stmt了!');

EXECUTE IMMEDIATE sql_stmt INTO student_name,student_class using student_id;
DBMS_OUTPUT.PUT_LINE('student_name='||student_name);
DBMS_OUTPUT.PUT_LINE('student_class'||student_class);
END;

EXECUTE IMMEDIATE 语句只能用于处理返回单行或没有返回的SQL语句,要处理返回多行的
动态SQL请使用REF游标的OPEN....FOR语句。


错误处理:
在运行程序时出现的错误叫做异常。发生异常后,语句将停止执行,PL/SQL引擎立即将控制权
转到PL/SQL块的异常部分处理。异常部分处理机制简化了代码中的错误检测。预定义的异常是
在运行时由系统自动引发的,而用户定义的异常必须使用RAISE语句显式引发。


PL/SQL预定义异常:
异常 | 说明
----------------------------------------------------------------------------------------------
ACCESS_INTO_NULL | 在未初始化对象时出现
----------------------------------------------------------------------------------------------
CASE_NOT_FOUND | 在CASE语句中的选项与用户输入的数据不匹配的时候出现
----------------------------------------------------------------------------------------------
COLLECTION_IS_NULL | 在给尚未初始化的表或数组赋值的时候出现
----------------------------------------------------------------------------------------------
CURSOR_ALREADY_OPEN | 在用户试图从新打开已经打开的游标时出现,在从新打开游标前必须先将其关闭
----------------------------------------------------------------------------------------------
DUP_VAL_ON_INDEX | 在用户试图将重复的值存储在时候唯一索引的数据库列中时出现
----------------------------------------------------------------------------------------------
INVALID_CURSOR | 在执行非法游标运算(如打开一个尚未打开的游标)时出现
----------------------------------------------------------------------------------------------
INVALID_NUMBER | 在将字符串转换为数字时出现
----------------------------------------------------------------------------------------------
LOGIN_DENIED | 在输入的用户名或密码无效时出现
----------------------------------------------------------------------------------------------
NO_DATA_FOUND | 在表中不存在请求的行时出现。此外,当程序引用已经删除的元素时
| 也会引发NO_DATA_FOUND 异常
----------------------------------------------------------------------------------------------
TOO_MANY_ROWS | 在执行SELECT INTO 语句 返回多行时出现
----------------------------------------------------------------------------------------------
VALUE_ERROR | 在产生大小限制错误时出现。例如,变量中的列值超出变量的大小
----------------------------------------------------------------------------------------------
ZERO_DIVIDE | 以零作除数时出现异常
----------------------------------------------------------------------------------------------

异常处理的语法:
BEGIN
sequence_of_statements;
EXCEPTION
WHEN THEN
sequence_of_statements1;
WHEN THEN
sequence_of_statements2;
....
WHEN OTHERS THEN
sequence_of_statements;
END;


自己定义异常,抛出异常 和 处理异常

DECLARE
e_1and1 EXCEPTION;
BEGIN
IF 1=1 THEN
RAISE e_nodata;
END IF;

EXCEPTION
WHEN e_1and1 THEN
DBMS_OUTPUT.PUT_LINE('无法识别该类');
END;




********************************************************************
******* 游标管理
********************************************************************
游标是构建在PL/SQL中,用来查询数据,获得记录集合的指针。他可以让开发者一次访问结果集中一行。

游标的分类:
游标分为:

静态游标
|
----隐式游标
|
----显式游标 (特殊的有循环游标)

REF游标(引用游标)

//**********************************

隐式游标

所有的SQL数据操纵语句 (QML)都有隐式声明游标,称为隐式游标。
隐式游标是用户不能直接命名和控制的游标。

隐式游标的四个属性:
%FOUND
只有用在DML语句影响一行的多行的时候,%FOUND属性返回TRUE,否则返回FALSE

%NOTFOUND
与%FOUND属性的作用相反,如果DML没有影响任何行,返回TRUE,否则返回FALSE

%ROWCOUNT
返回DML影响数据库表的行数,值是 0 到 多

%ISOPEN
返回游标是否被打开的消息。在执行SQL语句之后,Oracle自动关闭SQL游标,所以
隐式游标的%ISOPEN属性始终是关闭的。


SQL%FOUND

SQL%NOTFOUND

SQL%ROWCOUNT

SQL%ISOPEN

----------------
操作

set serveroutput on

begin
insert into s_emp(id,name,salary) values('1','tom','4000');
--要执行的sql语句
IF sql%found THEN
DBMS_OUTPUT.PUT_LINE('用sql%found得知表更新了!');
ELSE
DBMS_OUTPUT.PUT_LINE('用sql%found得知表没更新!');
END IF;
--用sql%found来判断是否更新数据库了
IF sql%notfound THEN
DBMS_OUTPUT.PUT_LINE('用sql%notfound 得知表没有更新!!');
ELSE
DBMS_OUTPUT.PUT_LINE('用sql%notfound 得知表更新了!!');
END IF;
DBMS_OUTPUT.PUT_LINE('表更新了---->'||sql%rowcount||'行');
--用sql%rowcount来查看更新了多少行
if sql%isopen then
dbms_output.put_line('cursor is open');
else
dbms_output.put_line('cursor is close');
end if;
--用sql%isopen来查看游标是否关闭了

DBMS_OUTPUT.PUT_LINE('end!!');
end;
/
----------------
----------------------------------------------------------------------------------------------

显式游标:

显式游标是用户声明的游标,查询返回的行集合可以包含 0行到多行。
显式游标的标准操作过程:

(1)声明游标。
(2)打开游标。
(3)从游标中获取记录。
(4)关闭游标。

------------------------
(1)声明游标:
CURSOR cursor_name [(parameter[,parameter]....)]
[RETURN return_type]
is select_statement;

cursor_name : 游标名称
parameter : 游标指定输入参数
return_type : 定义游标提取的行的类型
select_statement : 指游标定义的查询语句

(2)
OPEN cursor_name [(parameters)];

(3)
FETCH cursor_name INTO variables;
cursor_name : 指游标的名字
variables : 变量名

(4)
// 在处理完游标中的所有行之后,必须关闭游标,以释放分配给游标中的所有资源。
CLOSE cursor_name;

-----------

%FOUND
如果执行最后一条FETCH语句成功提取行,返回TRUE,否则返回FALSE
%NOTFOUND
如果执行最后一条FETCH语句成功提取行,返回FALSE,否则返回TRUE
%ISOPEN
如果游标关闭,返回FALSE,游标开启,返回TRUE
%ROWCOUNT
返回到目前位置游标提取的行数。当成功FETCH一行后,数量+1;

-------
实例:
set serveroutput on

declare
emp_id s_emp.id%type;
emp_name s_emp.name%type;
emp_salary s_emp.salary%type;
cursor emp_cur is
select id,name,salary from s_emp;
begin
open emp_cur;
loop
fetch emp_cur into emp_id,emp_name,emp_salary;
exit when emp_cur%notfound;
dbms_output.put_line('emp id -->'||emp_id||',emp name-->'||emp_name||',emp salary-->'||emp_salary);

end loop;
close emp_cur;

end;
/
-------

显式游标的特殊情况:循环游标

可以使用循环游标简化显式游标的处理代码。 循环游标隐式打开游标,自动从活动集获取行,然后在处理完所有行时关闭游标。
循环游标自动创建%ROWTYPE类型的变量并且将此变量用作记录索引。

语法:
FOR record_index IN cursor_name
LOOP
executable_statements;
END LOOP;

-----------
具体实例:
SET SERVEROUTPUT ON

DECLARE
CURSOR emp_cur is
select id,name,salary from s_emp;
BEGIN
--其中emp_rec 是一个存放一行的变量。我们在使用前不需要先声明。直接用
for emp_rec in emp_cur
LOOP
DBMS_OUTPUT.PUT_LINE('EMP ID-->'||emp_rec.id||' EMP NAME-->'||emp_rec.name||' EMP SALARY-->'||emp_rec.salary);
END LOOP;
END;

------------

REF 游标 (引用游标)

REF游标 可以在运行的时候决定执行何种查询。

使用过程:
1,创建游标

TYPE ref_cursor_name IS REF CURSOR [RETURN record_type];
注释:return 语句是可选择子句,用于指定游标提取结果集的返回类型。
如果没有return那么游标类型是一个弱类型。

2,在PLSQL的执行部分打开游标变量。用于打开REF游标的语法:
OPEN cursor_name FOR select_statement;

or OPEN cursor_name FOR dynamic_select_string [USING bing_argument_list];

----------
实例01
DECLARE
sqls VARCHAR2(200);
p_salary number:=8500;
id number;
name varchar2(50);
salary number;

TYPE cursor_salary IS REF CURSOR;
ref_cursor cursor_salary;
BEGIN
sqls :='select id from s_emp where salary>:1 ';
open ref_cursor FOR sqls USING p_salary;
DBMS_OUTPUT.PUT_LINE('薪水大于8500的人的ID有:');
LOOP
fetch ref_cursor into id;
EXIT WHEN ref_cursor%notfound;
DBMS_OUTPUT.PUT_LINE('ID='||id);
END LOOP;
--close ref_cursor;
END;
/

实例02
DECLARE
type emp is record(
id s_emp.id%type,
name s_emp.name%type,
salary s_emp.salary%type
);
p_salary number:=8500;
id number;
name varchar2(50);
salary number;

TYPE cursor_salary IS REF CURSOR RETURN emp;
ref_cursor cursor_salary;
BEGIN

open ref_cursor FOR select id,name,salary from s_emp where salary>8500;
DBMS_OUTPUT.PUT_LINE('薪水大于8500的人的ID有:');
LOOP
fetch ref_cursor into id,name,salary;
EXIT WHEN ref_cursor%notfound;
DBMS_OUTPUT.PUT_LINE('ID='||id||',name='||name||',salary='||salary);
END LOOP;
close ref_cursor;
END;
/

得出几点:
(1)在声明ref游标 的时候要用记录类型来声明 return
(2)游标查询结果

请登录后发表评论 登录
全部评论

注册时间:2007-12-12

  • 博文量
    7
  • 访问量
    118076