oracle命令方式看存储过程,Oracle超详细笔记9--存储过程与函数

叁歲伎倆 2023-01-18 06:55 134阅读 0赞

1、存储过程数据库

存储过程是一种命名的PL/SQL块，它能够传递参数，存储在数据库中，也能够被用户调用。函数

1.基本语法以下：get

create or replace procedure 过程名io

\[\] \{is | as\}编译

\[\]table

beginfunction

程序语句段；test

end 过程名；变量

说明：软件

(1)使用REPLACE关键字表示若是要建立的过程已经存在，则将该存在的过程替换为当前定义的过程。

(2)参数列表的格式以下：

\[IN|OUT|IN OUT\] \[:=\]

IN参数类型表示此参数接受外部过程传递来的值；

OUT参数类型表示此参数将在过程当中被赋值，并传递给过程体外；

IN OUT参数类型表示此参数同时具有IN和OUT参数的特性。

(3)局部变量声明中定义的变量只在该过程当中有效。

例 ：

建立一个存储过程,该过程能够向某表中添加记录。

\--建立表

Create table mytest(bname varchar2(30),singer varchar2(10));

\--建立过程

CREATE PROCEDURE proc1 is

BEGIN

insert into mytest(‘风吹麦浪’,’孙俪’);

END;

\--执行过程

Exec proc1;

例:

编写一个过程,输入员工名,新工资,能够修改员工工资

CREATE OR REPLACE PROCEDURE updatesal

(sname varchar2, newsal NUMBER)

BEGIN

update emp set sal=newsal where ename=sname;

END updatesal;

\--执行

Exec updatesal(‘SCOTT’,10);

Call updatesal(‘scott’,10);

例:

查询某部门平均工资

CREATE OR REPLACE PROCEDURE showavgsal

(p\_deptno in NUMBER)

v\_sal NUMBER(6,2);

BEGIN

SELECT avg(sal) INTO v\_sal FROM emp

WHERE deptno=p\_deptno;

DBMS\_OUTPUT.PUT\_LINE(v\_sal);

END showavgsal;

\--执行

Exec showavgsal(10);

Call showavgsal(10);

例：

建立一个存储过程，以部门号为参数，查询该部门的平均工资，并输出该部门中比平均工资高的员工号、员工名。

CREATE OR REPLACE PROCEDURE show\_emp

(p\_deptno emp.deptno%TYPE)

v\_sal emp.sal%TYPE;

BEGIN

SELECT avg(sal) INTO v\_sal FROM emp

WHERE deptno=p\_deptno;

DBMS\_OUTPUT.PUT\_LINE(p\_deptno||' '||'average salary is:'|| v\_sal);

FOR v\_emp IN (SELECT \* FROM emp WHERE

deptno=p\_deptno AND sal>v\_sal) LOOP

DBMS\_OUTPUT.PUT\_LINE(v\_emp.empno||' '||v\_emp.ename);

END LOOP;

EXCEPTION

WHEN NO\_DATA\_FOUND THEN

DBMS\_OUTPUT.PUT\_LINE('The department doesn’t exists!');

END show\_emp;

2.存储过程的调用

调用存储过程使用EXECUTE命令。如：

SQL>execute  show\_emp(10);

SQL>call  show\_emp(10);

在SQL\*PLUS中调用

EXEC  procedure\_name(parameter\_list)

EXECUTE show\_emp(10)

在PL/SQL块中调用

BEGIN

procedure\_name(parameter\_list);

END；

注意

在PL/SQL程序中，存储过程能够做为一个独立的表达式被调用。

一般，存储过程不须要返回值，若是须要返回一个值能够经过函数调用实现。可是，若是但愿返回多个值，可使用OUT或IN OUT模式参数来实现。

例：

建立一个存储过程，以部门号为参数，返回该部门的人数和平均工资。

CREATE OR REPLACE PROCEDURE return\_deptinfo(

p\_deptno emp.deptno%TYPE,

p\_avgsal OUT emp.sal%TYPE,

p\_count  OUT emp.sal%TYPE)

BEGIN

SELECT avg(sal),count(\*) INTO p\_avgsal,p\_count

FROM emp

WHERE deptno=p\_deptno;

EXCEPTION

WHEN NO\_DATA\_FOUND THEN

DBMS\_OUTPUT.PUT\_LINE('The department don’’t exists!');

END return\_deptinfo;

调用存储过程做为一个独立的表达式被调用

Declare

v\_avgsal emp.sal%type;

v\_count number;

Begin

show\_emp(20);

return\_deptinfo(10,v\_avgsal,v\_count);

dbms\_output.put\_line(v\_avgsal||’   ‘||v\_count);

End;

调用存储过程使用EXECUTE命令

如：

SQL>execute  empproc('001 ');

SQL>execute  get\_emp;

例：

执行书本例11-3的存储过程

empproc1

SQL> var c varchar2(50);

SQL> exec empproc1('001',25,:c);

PL/SQL 过程已成功完成。

SQL> print c;

存储过程执行成功！

3.删除存储过程

可使用DROP PROCEDURE命令，如：

DROP  PROCEDURE  empproc;

4.存储过程的管理

修改存储过程

CREATE OR REPLACE PROCEDURE procedure\_name

查看存储过程及其源代码

查询数据字典视图USER\_SOURCE

SELECT name,text FROM user\_source

WHERE type='PROCEDURE';

从新编译存储过程

ALTER PROCEDURE…COMPILE

ALTER PROCEDURE show\_emp COMPILE;

删除存储过程

DROP PROCEDURE

DROP PROCEDURE show\_emp;

2、函数

1.建立函数

函数与过程有不少类似的地方，都是以编译后的形式存储在数据库中的代码块但也有一些差异，但函数必须返回一个值，必须以合法的运算式的方式进行调用，而不能做为独立执行语句调用。

具体语法以下：

create or replace function 函数名

\[\]

return  type

\{is | as\}

begin

函数体；

end 函数名；

说明：

(1)参数列表的格式以下：

\[IN|OUT|IN OUT\] \[:=\]

IN参数类型表示此参数接受外部过程传递来的值；

OUT参数类型表示此参数将在过程当中被赋值，并传递给过程体外；

IN OUT参数类型表示此参数同时具有IN和OUT参数的特性。

(2)type表示返回值的数据类型

建立一个以部门号为参数，返回该部门最高工资的函数。

CREATE OR REPLACE FUNCTION return\_maxsal

(p\_deptno emp.deptno%TYPE)

RETURN emp.sal%TYPE

v\_maxsal emp.sal%TYPE;

BEGIN

SELECT max(sal) INTO v\_maxsal FROM emp

WHERE deptno=p\_deptno;

RETURN v\_maxsal;

EXCEPTION

WHEN NO\_DATA\_FOUND THEN

DBMS\_OUTPUT.PUT\_LINE('The deptno is invalid!');

END return\_maxsal;

若是须要函数返回多个值，可使用OUT或IN OUT模式参数。

例：

建立一个函数，以部门号为参数，返回部门名、部门人数及部门平均工资。

CREATE OR REPLACE FUNCTION ret\_deptinfo(

p\_deptno dept.deptno%TYPE,

p\_num OUT NUMBER,

p\_avg OUT NUMBER)

RETURN dept.dname%TYPE

v\_dname dept.dname%TYPE;

BEGIN

SELECT dname INTO v\_dname FROM dept

WHERE deptno=p\_deptno;

SELECT count(\*),avg(sal) INTO p\_num,p\_avg

FROM emp WHERE deptno=p\_deptno;

RETURN v\_dname;

END ret\_maxsal;

2.函数的调用

在SQL语句中调用函数

在PL/SQL中调用函数

注意

函数只能做为表达式的一部分被调用。

例

经过return\_maxsal函数的调用，输出各个部门的最高工资；经过ret\_deptinfo函数调用，输出各个部门名、部门人数及平均工资。

DECLARE

v\_maxsal emp.sal%TYPE;

v\_avgsal emp.sal%TYPE;

v\_num    NUMBER;

v\_dname  dept.dname%TYPE;

BEGIN

FOR v\_dept IN (SELECT DISTINCT deptno FROM emp) LOOP

v\_maxsal:=ret\_maxsal(v\_dept.deptno);

v\_dname:=ret\_deptinfo(v\_dept.deptno,v\_num,v\_avgsal);

DBMS\_OUTPUT.PUT\_LINE(v\_dname||' '||v\_maxsal||' '||

v\_avgsal||' '||v\_num);

END LOOP;

END;

3.函数的管理

函数的修改

CREATE OR REPLACE FUNCTION function\_name

查看函数及其源代码

查询数据字典视图USER\_SOURCE

SELECT name,text FROM user\_source

WHERE type='FUNCTION';

函数重编译

ALTER FUNCTION…COMPILE

ALTER FUNCTION ret\_maxsal COMPILE;

删除函数

DROP FUNCTION

DROP FUNCTION ret\_maxsal;

3、程序包

程序包的建立

在Oracle 11g中，能够把过程和函数封装起来，做为一个独立的单元，以完成相应的操做，这个独立的单元被称为程序包，包通常是过程和函数的集合,对过程和函数进行更好的封装,通常不针对字段。包的构成包括包头和包体，包头仅仅只是对包中的方法进行说明, 包体是对包头中定义的过程、函数的具体实现。

1.包头

语法以下：

CREATE \[OR  REPLACE\]  PACKAGE

\{ IS | AS \}

\[\]

END \[\];

说明：

声明部分能够包括类型、变量、过程、函数和游标的说明

例：

建立一个软件包，包括2个变量、2个过程和1个异常。

CREATE OR REPLACE PACKAGE pkg\_emp

minsal   NUMBER;

maxsal   NUMBER;

e\_beyondbound  EXCEPTION;

PROCEDURE update\_sal(

p\_empno NUMBER, p\_sal NUMBER);

PROCEDURE add\_employee(

p\_empno NUMBER,p\_sal NUMBER);

END pkg\_emp;

2．包体

可使用CREATE PACKAGE BODY语句建立包体部分

基本语法以下：

CREATE \[OR  REPLACE\]  PACKAGE BODY

\{ IS | AS \}

\[\]

END \[\];

说明：

create or replace package后面的名称必须和create or replace package body后面的名称保持一致。

CREATE OR REPLACE PACKAGE BODY pkg\_emp

PROCEDURE update\_sal(p\_empno NUMBER, p\_sal NUMBER)

BEGIN

SELECT min(sal), max(sal) INTO minsal,maxsal FROM emp;

IF p\_sal BETWEEN minsal AND maxsal THEN

UPDATE emp SET sal=p\_sal WHERE empno=p\_empno;

IF SQL%NOTFOUND THEN

RAISE\_APPLICATION\_ERROR(-20000,'The employee

doesn''t exist');

END IF;

ELSE

RAISE e\_beyondbound;

END IF;

EXCEPTION

WHEN e\_beyondbound THEN

DBMS\_OUTPUT.PUT\_LINE('The salary is beyond bound! ');

END update\_sal;

PROCEDURE add\_employee(p\_empno NUMBER,p\_sal NUMBER)

BEGIN

SELECT min(sal), max(sal) INTO minsal,maxsal FROM emp;

IF p\_sal BETWEEN minsal AND maxsal THEN

INSERT INTO emp(empno,sal) VALUES(p\_empno,p\_sal);

ELSE

RAISE e\_beyondbound;

END IF;

EXCEPTION

WHEN e\_beyondbound THEN

DBMS\_OUTPUT.PUT\_LINE('The salary is beyond bound! ');

END add\_employee;

END pkg\_emp;

3.程序包的调用

可使用下列方法调用程序包中的过程：

〈方案名〉.〈程序包名〉.〈过程名〉

例：

调用程序包MyPackage的函数：

SET ServerOutput ON;

DECLARE

varPwd Users.UserPwd%Type;

BEGIN

varPwd:=CDL.MyPackage.GetPwd('Admin ');

dbms\_output.put\_line(varPwd);

END;

例：

调用软件包pkg\_emp中的过程update\_sal，修改7844员工工资为3000。调用add\_employee添加一个员工号为1357，工资为4000的员工。

BEGIN

pkg\_emp.update\_sal(7844,3000);

pkg\_emp.add\_employee(1357,4000);

END;

4.程序包的删除

可使用DROP PACKAGE BODY命令删除程序包体，如：

DROP PACKAGE BODY CDL.MyPackage;

可使用DROP PACKAGE命令删除程序包，如：

DROP PACKAGE CDL.MyPackage;

oracle命令方式看存储过程,Oracle超详细笔记9--存储过程与函数

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 oracle存储过程----入门存储过程

相关 oracle——笔记——存储过程简介

相关 oracle命令方式看存储过程,Oracle超详细笔记9--存储过程与函数

相关 Java调用Oracle存储函数与存储过程

相关 oracle存储过程

相关 Oracle 存储过程和存储函数学习笔记

相关 oracle教程27 存储过程与函数（存储过程与存储函数的创建）

相关 oracle教程29 存储过程与函数（存储过程与函数的比较）

相关 oracle教程26 存储过程与函数（存储过程与存储函数的概述）

相关 Oracle 存储过程、存储函数与 JDBC 调用