PL/SQL是一种编程语言,称为过程化SQL语言(Proceduaral Language/SQL),PL/SQL 是Oracle数据库对SQL语言的扩展。MySql目前不支持PL/SQL。
程序结构:
PL/SQL程序都是以块(block)为基本单位,整个PL/SQL块分为三部分:声明部分(用declare开头)、执行部分(以begin开头)和异常处理部分(以Exception开头)。其中执行部分是必须的,其他两个部分可选。
DECLARE --标记声明部分
…… --此处用来定义常量、变量、类型和游标等
BEGIN --标记程序体部分开始
…… --此处用来编写各种PL/SQL语句、函数和存储过程
EXCEPTION --标记异常处理部分开始
…… --此处用来编写异常处理代码
END; --标记程序体部分结束
示例:编写计算圆面积的PL/SQL块。
SET SERVEROUTPUT ON
DECLARE
PI CONSTANT NUMBER(6,5) := 3.14159;
--声明两个变量并赋初值
r FLOAT := 2;
area FLOAT;
BEGIN
area := PI * r * r;
DBMS_OUTPUT.PUT_LINE('Area =' || area);
END;
(一)存储过程
在Oracle中,可以在数据库中定义子程序,在子程序中将一些固定的操作集中起来,由Oracle数据库服务器完成,以完成某个特定的功能。这种子程序称为存储过程(ProceDure)。使用存储过程具有如下优点:
(1)存储过程在服务器运行,执行速度快。
(2)存储过程执行一次后驻留在oracle数据库服务器的高速Cache中,以后再次执行存储过程时,只需要从高速Cache中调用已经编译好的代码即可,从而提高了系统的性能。
(3)存储过程确保了数据库的安全。使用存储过程,可以在禁止用户直接访问应用程序中的某些数据表的情况下,授权执行访问这些数据表的存储过程。
(4)自动完成需要预先执行的任务。存储过程可以设置为系统启动时自动执行,而不必在系统启动后再进行一些手动操作,从而方便了用户的使用,可以自动完成一些需要预先执行的任务。
创建存储过程:
CREATE [ OR REPLACE ] PROCEDURE 过程名
( 参数名 in | out | in out 数据类型 [:=初始值],
参数名 in | out | in out 数据类型 [:=初始值],…
参数名 in | out | in out 数据类型 [:=初始值]
)
IS | AS
[ <局部变量声明> ]
BEGIN
<过程体>
END [ <过程名> ];
存储过程的调用:
在sqlplus中调用:
execute 过程名(参数1,参数2,…)
在程序中调用:
过程名(参数1,参数2,…);
带输入参数的存储过程:
Create or replace procedure p_insert
( v_xh in xs_kc.xh%type,
v_kch in xs_kc.kch%type,
v_cj in xs_kc.cj%type
)
As
begin
insert into xs_kc values(v_xh,v_kch,v_cj);
exception
when dup_val_on_index then
dbms_output.put_line(‘该学生这门课程成绩已存在!’);
End;
带输出参数的存储过程:
Create or replace procedure p_num
( v_xh in xs_kc.xh%type,
v_num out number
)
As
begin
select count(*) into v_num
from xs_kc
where xh=v_xh and cj<60
End;
注意:
输入参数(in类型)的值在调用时传入过程中,在过程中不能再赋值。
输出参数(out类型)在过程中一定要有对其进行赋值的语句,过程结束后其值传递到外界。
调用时,一定要用变量接收输入参数传出的值。
(二)函数
创建函数:
CREATE [ OR REPLACE ] FUNCTION 函数名
( 参数名 in | out | in out 数据类型 [:=初始值],
参数名 in | out | in out 数据类型 [:=初始值],…
参数名 in | out | in out 数据类型 [:=初始值]
)
RETURN 返回值数据类型
IS | AS
[ <局部变量声明> ]
BEGIN
<函数体>
RETURN 返回值
END [ <函数名> ];
带输出参数的函数:
Create or replace function f_num
( v_xh in xs_kc.xh%type,v_xm out xs.xm%type)
Return number
As
v_num number;
Begin
select xm into v_xm from xs where xh=v_xh;
select count(*) into v_num
from xs_kc
where xh=v_xh and cj<60
return v_num;
End;
函数的调用:
函数的调用必须作为表达式的一部分出现,不能作为单独的语句出现。
过程的调用必须作为独立的语句出现。
(三)存储过程和函数的区别
相同点:1、创建语法结构相似,都可以有多个传入参数和传出参数。
2、都是一次编译,多次执行。
不同点:1、存储过程定义关键字用procedure,函数定义用function。
2、存储过程中不能用return返回值,函数中可以而且函数中必须有return字句。
3、 执行方式不同,存储过程的执行方式有两种,函数可以当做表达式使用,例如放在select中(select 函数名() from 表名)。
总之,在使用的时候,如果只有一个返回值用函数,否则使用存储过程。