oracle存储过程详细介绍(创建,删除存储过程,参数传递等)_oracle存储过程怎么删除

oracle

存储过程

86 浏览量更新于2023-03-16 评论 1 收藏 141KB PDF 举报

身份认证购VIP最低享 7 折!

领优惠券(最高得80元）

资源详情

资源评论

资源推荐

oracle 存储过程详细介绍存储过程详细介绍(创建创建,删除存储过程删除存储过程,参数传递等参数传递等)

主要介绍了oracle 创建,删除存储过程,参数传递,创建,删除存储函数,存储过程和函数的查看,包,系统包等相关资

料,需要的朋友可以参考下

oracle 创建,删除存储过程,参数传递,创建,删除存储函数,存储过程和函数的查看,包,系统包

认识存储过程和函数认识存储过程和函数

存储过程和函数也是一种PL/SQL块，是存入数据库的PL/SQL块。但存储过程和函数不同于已经介绍过的PL/SQL程序，我们

通常把PL/SQL程序称为无名块，而存储过程和函数是以命名的方式存储于数据库中的。和PL/SQL程序相比，存储过程有很多

优点，具体归纳如下：

* 存储过程和函数以命名的数据库对象形式存储于数据库当中。存储在数据库中的优点是很明显的，因为代码不保存在本地，

用户可以在任何客户机上登录到数据库，并调用或修改代码。

* 存储过程和函数可由数据库提供安全保证，要想使用存储过程和函数，需要有存储过程和函数的所有者的授权，只有被授权

的用户或创建者本身才能执行存储过程或调用函数。

* 存储过程和函数的信息是写入数据字典的，所以存储过程可以看作是一个公用模块，用户编写的PL/SQL程序或其他存储过

程都可以调用它(但存储过程和函数不能调用PL/SQL程序)。一个重复使用的功能，可以设计成为存储过程，比如：显示一张

工资统计表，可以设计成为存储过程；一个经常调用的计算，可以设计成为存储函数；根据雇员编号返回雇员的姓名，可以设

计成存储函数。

* 像其他高级语言的过程和函数一样，可以传递参数给存储过程或函数，参数的传递也有多种方式。存储过程可以有返回值，

也可以没有返回值，存储过程的返回值必须通过参数带回；函数有一定的数据类型，像其他的标准函数一样，我们可以通过对

函数名的调用返回函数值。

存储过程和函数需要进行编译，以排除语法错误，只有编译通过才能调用。

创建和删除存储过程

创建存储过程，需要有CREATE PROCEDURE或CREATE ANY PROCEDURE的系统权限。该权限可由系统管理员授予。创

建一个存储过程的基本语句如下：

CREATE [OR REPLACE] PROCEDURE 存储过程名[(参数[IN|OUT|IN OUT] 数据类型...)]

{AS|IS}

[说明部分]

BEGIN

可执行部分

[EXCEPTION

错误处理部分]

END [过程名];

其中：

可选关键字OR REPLACE 表示如果存储过程已经存在，则用新的存储过程覆盖，通常用于存储过程的重建。

参数部分用于定义多个参数(如果没有参数，就可以省略)。参数有三种形式：IN、OUT和IN OUT。如果没有指明参数的形

式，则默认为IN。

关键字AS也可以写成IS，后跟过程的说明部分，可以在此定义过程的局部变量。

编写存储过程可以使用任何文本编辑器或直接在SQL*Plus环境下进行，编写好的存储过程必须要在SQL*Plus环境下进行编

译，生成编译代码，原代码和编译代码在编译过程中都会被存入数据库。编译成功的存储过程就可以在Oracle环境下进行调用

了。

一个存储过程在不需要时可以删除。删除存储过程的人是过程的创建者或者拥有DROP ANY PROCEDURE系统权限的人。删

除存储过程的语法如下：

DROP PROCEDURE 存储过程名；

如果要重新编译一个存储过程，则只能是过程的创建者或者拥有ALTER ANY PROCEDURE系统权限的人。语法如下：

ALTER PROCEDURE 存储过程名 COMPILE；

执行(或调用)存储过程的人是过程的创建者或是拥有EXECUTE ANY PROCEDURE系统权限的人或是被拥有者授予

EXECUTE权限的人。执行的方法如下：

方法1：

EXECUTE 模式名.存储过程名[(参数...)];

方法2：

BEGIN

模式名.存储过程名[(参数...)];

END;

传递的参数必须与定义的参数类型、个数和顺序一致(如果参数定义了默认值，则调用时可以省略参数)。参数可以是变量、常

量或表达式，用法参见下一节。

如果是调用本账户下的存储过程，则模式名可以省略。要调用其他账户编写的存储过程，则模式名必须要添加。

以下是一个生成和调用简单存储过程的训练。注意要事先授予创建存储过程的权限。

【训练1】创建一个显示雇员总人数的存储过程。

步骤1：登录SCOTT账户(或学生个人账户)。

步骤2：在SQL*Plus输入区中，输入以下存储过程：

复制代码代码如下:

CREATE OR REPLACE PROCEDURE EMP_COUNT

V_TOTAL NUMBER(10);

BEGIN

SELECT COUNT(*) INTO V_TOTAL FROM EMP;

DBMS_OUTPUT.PUT_LINE('雇员总人数为：'||V_TOTAL);

END;

步骤3：按“执行”按钮进行编译。

如果存在错误，就会显示:

警告: 创建的过程带有编译错误。

如果存在错误，对脚本进行修改，直到没有错误产生。

如果编译结果正确，将显示：

复制代码代码如下:

过程已创建。

步骤4：调用存储过程，在输入区中输入以下语句并执行：

复制代码代码如下:

EXECUTE EMP_COUNT;

显示结果为：

复制代码代码如下:

雇员总人数为：14

PL/SQL 过程已成功完成。

说明：在该训练中，V_TOTAL变量是存储过程定义的局部变量，用于接收查询到的雇员总人数。

注意：在SQL*Plus中输入存储过程，按“执行”按钮是进行编译，不是执行存储过程。

如果在存储过程中引用了其他用户的对象，比如表，则必须有其他用户授予的对象访问权限。一个存储过程一旦编译成功，

就可以由其他用户或程序来引用。但存储过程或函数的所有者必须授予其他用户执行该过程的权限。

存储过程没有参数，在调用时，直接写过程名即可。

【训练2】在PL/SQL程序中调用存储过程。

步骤1：登录SCOTT账户。

步骤2：授权STUDENT账户使用该存储过程，即在SQL*Plus输入区中，输入以下的命令：

复制代码代码如下:

GRANT EXECUTE ON EMP_COUNT TO STUDENT

授权成功。

步骤3：登录STUDENT账户，在SQL*Plus输入区中输入以下程序：

复制代码代码如下:

SET SERVEROUTPUT ON

BEGIN

SCOTT.EMP_COUNT;

END;

步骤4：执行以上程序，结果为：

复制代码代码如下:

雇员总人数为：14

PL/SQL 过程已成功完成。

说明：在本例中，存储过程是由SCOTT账户创建的，STUDEN账户获得SCOTT账户的授权后，才能调用该存储过程。

注意：在程序中调用存储过程，使用了第二种语法。

【训练3】编写显示雇员信息的存储过程EMP_LIST，并引用EMP_COUNT存储过程。

步骤1：在SQL*Plus输入区中输入并编译以下存储过程：

复制代码代码如下:

CREATE OR REPLACE PROCEDURE EMP_LIST

CURSOR emp_cursor IS

SELECT empno,ename FROM emp;

BEGIN

FOR Emp_record IN emp_cursor LOOP

DBMS_OUTPUT.PUT_LINE(Emp_record.empno||Emp_record.ename);

END LOOP;

EMP_COUNT;

END;

执行结果：

过程已创建。

步骤2：调用存储过程，在输入区中输入以下语句并执行：

复制代码代码如下:

EXECUTE EMP_LIST

显示结果为：

复制代码代码如下:

7369SMITH

7499ALLEN

7521WARD

7566JONES

执行结果：

雇员总人数为：14

PL/SQL 过程已成功完成。

说明：以上的EMP_LIST存储过程中定义并使用了游标，用来循环显示所有雇员的信息。然后调用已经成功编译的存储过程

EMP_COUNT，用来附加显示雇员总人数。通过EXECUTE命令来执行EMP_LIST存储过程。

【练习1】编写显示部门信息的存储过程DEPT_LIST，要求统计出部门个数。

参数传递

参数的作用是向存储过程传递数据，或从存储过程获得返回结果。正确的使用参数可以大大增加存储过程的灵活性和通用性。

参数的类型有三种，如下所示。

复制代码代码如下:

IN 定义一个输入参数变量，用于传递参数给存储过程

OUT 定义一个输出参数变量，用于从存储过程获取数据

IN OUT 定义一个输入、输出参数变量，兼有以上两者的功能

参数的定义形式和作用如下：

参数名 IN 数据类型 DEFAULT 值；

定义一个输入参数变量，用于传递参数给存储过程。在调用存储过程时，主程序的实际参数可以是常量、有值变量或表达式

等。DEFAULT 关键字为可选项，用来设定参数的默认值。如果在调用存储过程时不指明参数，则参数变量取默认值。在存储

过程中，输入变量接收主程序传递的值，但不能对其进行赋值。

参数名 OUT 数据类型；

定义一个输出参数变量，用于从存储过程获取数据，即变量从存储过程中返回值给主程序。

在调用存储过程时，主程序的实际参数只能是一个变量，而不能是常量或表达式。在存储过程中，参数变量只能被赋值而不能

将其用于赋值，在存储过程中必须给输出变量至少赋值一次。

参数名 IN OUT 数据类型 DEFAULT 值；

定义一个输入、输出参数变量，兼有以上两者的功能。在调用存储过程时，主程序的实际参数只能是一个变量，而不能是常量

或表达式。DEFAULT 关键字为可选项，用来设定参数的默认值。在存储过程中，变量接收主程序传递的值，同时可以参加赋

值运算，也可以对其进行赋值。在存储过程中必须给变量至少赋值一次。

如果省略IN、OUT或IN OUT，则默认模式是IN。

【训练1】编写给雇员增加工资的存储过程CHANGE_SALARY，通过IN类型的参数传递要增加工资的雇员编号和增加的工资

额。

步骤1：登录SCOTT账户。

步骤2：在SQL*Plus输入区中输入以下存储过程并执行：

复制代码代码如下:

CREATE OR REPLACE PROCEDURE CHANGE_SALARY(P_EMPNO IN NUMBER DEFAULT 7788,P_RAISE NUMBER

DEFAULT 10)

V_ENAME VARCHAR2(10);

V_SAL NUMBER(5);

BEGIN

SELECT ENAME,SAL INTO V_ENAME,V_SAL FROM EMP WHERE EMPNO=P_EMPNO;

UPDATE EMP SET SAL=SAL+P_RAISE WHERE EMPNO=P_EMPNO;

DBMS_OUTPUT.PUT_LINE('雇员'||V_ENAME||'的工资被改为'||TO_CHAR(V_SAL+P_RAISE));

COMMIT;

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('发生错误，修改失败！');

ROLLBACK;

END;

执行结果为：

过程已创建。

步骤3：调用存储过程，在输入区中输入以下语句并执行：

复制代码代码如下:

EXECUTE CHANGE_SALARY(7788,80)

显示结果为：

雇员SCOTT的工资被改为3080

说明：从执行结果可以看到，雇员SCOTT的工资已由原来的3000改为3080。

参数的值由调用者传递，传递的参数的个数、类型和顺序应该和定义的一致。如果顺序不一致，可以采用以下调用方法。如上

例，执行语句可以改为：

EXECUTE CHANGE_SALARY(P_RAISE=>80,P_EMPNO=>7788);

可以看出传递参数的顺序发生了变化，并且明确指出了参数名和要传递的值，=>运算符左侧是参数名，右侧是参数表达式，

这种赋值方法的意义较清楚。

【练习1】创建插入雇员的存储过程INSERT_EMP，并将雇员编号等作为参数。

在设计存储过程的时候，也可以为参数设定默认值，这样调用者就可以不传递或少传递参数了。

【训练2】调用存储过程CHANGE_SALARY，不传递参数，使用默认参数值。

在SQL*Plus输入区中输入以下命令并执行：

复制代码代码如下:

EXECUTE CHANGE_SALARY

显示结果为：

雇员SCOTT的工资被改为3090

说明：在存储过程的调用中没有传递参数，而是采用了默认值7788和10，即默认雇员号为7788，增加的工资为10。

【训练3】使用OUT类型的参数返回存储过程的结果。

步骤1：登录SCOTT账户。

步骤2：在SQL*Plus输入区中输入并编译以下存储过程：

复制代码代码如下:

CREATE OR REPLACE PROCEDURE EMP_COUNT(P_TOTAL OUT NUMBER)

BEGIN

SELECT COUNT(*) INTO P_TOTAL FROM EMP;

END;

执行结果为：

1.过程已创建。

剩余15页未读，继续阅读

weixin_38653878

粉丝: 1
资源: 941

会员权益专享

oracle 存储过程详细介绍(创建,删除存储过程,参数传递等)

评论0

会员权益专享

最新资源

oracle 存储过程详细介绍(创建,删除存储过程,参数传递等)

评论0

ORACLE存储过程实现添加、修改、删除、查询

ORACLE如何使用存储过程实现添加、修改、删除、查询

在SQL存储过程中给条件变量加上单引号

使用Oracle存储过程实现定时任务调度

Oracle数据库中存储过程的使用及优化

Oracle数据库触发器与存储过程开发

Oracle 12c中的存储过程与触发器

ADO.NET中的存储过程与参数化查询的最佳实践

oracle存储过程考试题

oracle存储过程教程

oracle存储过程的调用

oracle存储过程批量更新

mybatis调用oracle存储过程

oracle存储过程参数默认值

ORACLE创建存储过程详解

oracle存储过程调用另一个存储过程

oracle调用存储过程

mybatis执行oracle存储过程 参数

ORACLE存储过程详解

oracle存储过程如何将out类型参数的结果传递给调用者

会员权益专享

最新资源

mybatis执行oracle存储过程参数