Oracle超详细笔记9--存储过程与函数

于 2019-12-27 10:49:56 发布
阅读量1.5k 收藏 3
点赞数
分类专栏： Oracle 文章标签： oracle
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/QilanAllen/article/details/103728081
版权

一、存储过程

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

1.基本语法如下：

create or replace procedure 过程名

[<参数列表>] {is | as}

[<局部变量声明>]

 begin

     程序语句段；

 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)

iS

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)

AS

  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)

AS

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)

AS

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;

 

二、函数

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

AS

    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

AS

  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;

 

三、程序包

程序包的创建

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

1.包头

语法如下：

CREATE [OR  REPLACE]  PACKAGE  <程序包名>

{ IS | AS }

[<声明部分>]

END [<程序包名>];

说明：

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

 

例：

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

CREATE OR REPLACE PACKAGE pkg_emp

AS

  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

AS

    PROCEDURE update_sal(p_empno NUMBER, p_sal NUMBER)

    AS

      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)

AS

  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;

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值