oracle存储过程入门教程

oracle存储过程入门教程一.概述　　Oracle存储过程开发的要点是：一.概述

Oracle存储过程开发的要点是：

使用Notepad文本编辑器，用Oracle PL/SQL编程语言写一个存储过程;

在Oracle数据库中创建一个存储过程;

在Oracle数据库中使用SQL*Plus工具运行存储过程;

在Oracle数据库中修改存储过程;

通过编译错误调试存储过程;

删除存储过程;

二.环境配置

包括以下内容：

?? 一个文本编辑器Notepad;

?? Oracle SQL*Plus工具，提交Oracle SQL和PL/SQL语句到Oracle database。

?? Oracle 10g express数据库，它是免费使用的版本;

需 要的技巧：

?? SQL基础知识,包括插入、修改、删除等

?? 使用Oracle's SQL*Plus工具的基本技巧;

?? 使用Oracle's PL/SQL编程语言的基本技巧;

三.写一个 存储过程

存储过程使用Oracle's PL/SQL 程序语言编写，让我们写一个什么工作都不做的存储过程，我们可以编译和运行它而不用担心会对数据库产生任何损害。

在Notepad, 写下：

CREATE OR REPLACE PROCEDURE skeleton

IS

BEGIN

NULL;

END;

把文件存为skeleton.sql.

让我们一 行行遍历这个存储过程:

1 CREATE OR REPLACE PROCEDURE skeleton

2 IS

3 BEGIN

4 NULL;

5 END;

行1:

CREATE OR REPLACE PROCEDURE 是一个SQL语句通知Oracle数据库去创建一个叫做skeleton存储过程,如果存在就覆盖它;

行2:

IS关键词表明后面将跟随一个PL/SQL体。

行3:

BEGIN关键词表明PL/SQL体的开始。

行4:

NULL PL/SQL语句表明什么事都不做，这句不能删去，因为PL/SQL体中至少需要有一句;

行5:

END关键词表明 PL/SQL体的结束四.创建一个存储过程

SQL语句CREATE OR REPLACE PROCEDURE在Oracle数据库中创建、编译和保存一个存储过程。

从Window打开SQL*Plus并且从SQL*Plus登录到你的数据库;打开skeleton.sql文件.

在SQL>命令提示符下输入以下命令：

SQL>@skeleton

SQL>/

SQL*Plus装载skeleton.sql文件的内容到SQL*Plus缓冲区并且执行SQL*Plus语句;SQL*Plus会通知你存储过程已经被成功地创建。

现在你的存储过程被创 建，编译和保存在你的Oracle数据库，我们可以运行它。

五.运行一个存储过程

从SQL*Plus 命令行提示符运行你的存储过程使用EXECUTE命令，如下:

SQL> EXECUTE skeleton;

SQL*Plus 输出一下信息确信存储过程成功执行: PL/SQL procedure successfully completed.

你也可以在一个无名PL/SQL块内运行你的存储过程，在SQL*Plus命令提示符下，它看起来像：

SQL> BEGIN

2 SKELETON;

3 END;

4 /

现在我们已经运行了我们的存储过程，我们如何修改它 呢?

六.修改一个存储过程

让我们写一个输出字符串“Hello World!”的存储过程，用Notepad打开你的skeleton.sql文件，.用DBMS_OUTPUT.PUT_LINE过程调用去替换NULL语句，如下所示:

CREATE OR REPLACE PROCEDURE skeleton

IS

BEGIN

DBMS_OUTPUT.PUT_LINE('Hello World!');

END;

保存到文件skeleton.sql.

从SQL*Plus命令行,打开文件skeleton.sql .

SQL> @skeleton

SQL>

1 CREATE OR REPLACE PROCEDURE skeleton

2 IS

3 BEGIN

4 DBMS_OUTPUT.PUT_LINE('Hello World!');

5* END;

SQL> /

SQL*Plus 通知你存储过程成功创建并输出提示信息：Procedure created.

SQL>

用EXECUTE 命令运行你的存储过程:

SQL> EXECUTE skeleton;

SQL*Plus显 示存储过程运行成功：PL/SQL procedure successfully completed.

我们想要的输出字符串 "Hello World!"没有出来，在显示一个DBMS_OUTPUT.PUT_LINE结果前需要运行一个SET命令，在SQL*Plus命令行提示符,键入：

SQL> SET SERVEROUTPUT ON

再次执行你的存储过程：

SQL> EXECUTE skeleton;

现在结果输出了：Hello World!

PL/SQL procedure successfully completed.

七.调试一个存储过程

当调试一 个存储过程时，遵循一样的步骤，修改SQL文件，创建存储过程，执行存储过程，根据编译器反馈的出错信息进行修改，这一步是非常繁琐的，需要依靠经验。

在实际的商用存储过程的开发调试过程中，由于涉及很多表、类型、光标、循环、条件等复杂的逻辑，和PL/SQL语句的灵活运用，编译时会产生很多错误提 示信息，程序员在根据这些错误信息定位，进行修正，再编译最后得到正确的结构;八.放弃一个存储过程

如果在数据库中你不在需要一个存储过程你可以删除它，SQL语句DROP PROCEDURE完成从数据库中删除一个存储过程，DROP PROCEDURE在SQL中被归类为数据定义语言(DDL)类操作，其他的例子有CREATE, ALTER, RENAME和TRUNCATE。.

在SQL*Plus 命令提示符下，使用DROP PROCEDURE SQL语句删除你的叫做skeleton的存储过程：

SQL> DROP PROCEDURE skeleton;

SQL*Plus assures us the procedure has been removed:

Procedure dropped.

八, 查询结果赋值

    1：当返回的是单个值时，定义一个变量，把查询出的值赋个这个变量，我们可以通过这个变量使用查询出的结果。

          countnum          number;

          select count(1) into countnum from userTable;

     2： 游标

         用来查询数据库，获取记录集合（结果集）的指针，我们所说的游标通常是指显式游标，因此从现在起没有特别指明的情况，我们所说的游标都是指显式游标。要在程序中使用游标，必须首先声明游标分类：

     

游标属性：

%FOUND：变量最后从游标中获取记录的时候，在结果集中找到了记录。
%NOTFOUND：变量最后从游标中获取记录的时候，在结果集中没有找到记录。
%ROWCOUNT：当前时刻已经从游标中获取的记录数量。
%ISOPEN：是否打开。

 

静态游标：
分为显式游标和隐式游标。

REF游标：
是一种引用类型，类似于指针。

显式游标：

CURSOR 游标名 ( 参数 ) [返回值类型] IS
Select 语句

 

生命周期：

在大多数时候我们在设计程序的时候都遵循下面的步骤：
　1、打开游标 open cs1;
　2、开始循环 while cs1%found loop | for column_name in .. LOOP
　3、从游标中取值 fetch .. into.. |
　4、检查那一行被返回
　5、处理
　6、关闭循环 end loop;
　7、关闭游标 if cs1&isopen then close cs1;

选项：参数和返回类型

set serveroutput on
declare
cursor emp_cur ( p_deptid in number) is
select * from employees where department_id = p_deptid;

l_emp employees%rowtype;
begin
dbms_output.put_line('Getting employees from department 30');
open emp_cur(30);
loop
fetch emp_cur into l_emp;
exit when emp_cur%notfound;
dbms_output.put_line('Employee id '|| l_emp.employee_id || ' is ');
dbms_output.put_line(l_emp.first_name || ' ' || l_emp.last_name);
end loop;
close emp_cur;

dbms_output.put_line('Getting employees from department 90');
open emp_cur(90);
loop
fetch emp_cur into l_emp;
exit when emp_cur%notfound;
dbms_output.put_line('Employee id '|| l_emp.employee_id || ' is ');
dbms_output.put_line(l_emp.first_name || ' ' || l_emp.last_name);
end loop;
close emp_cur;
end;

 

总结

本文详细讨论了如何使用Oracle工具开发Oracle商用存储过程的步骤。 最后在存储过程的使用中可能是程序直接调用，也可能被触发器调用。