Oracle数据库之PL/SQL过程与函数

原创 2015年06月17日 14:00:45

Oracle数据库之PL/SQL过程与函数

PL/SQL块分为匿名块与命名块,命名块又包含子程序、包和触发器。

过程和函数统称为PL/SQL子程序,我们可以将商业逻辑、企业规则写成过程或函数保存到数据库中,以便共享。

过程和函数均存储在数据库中,并通过参数与其调用者交换信息。过程和函数的唯一区别是函数总向调用者返回数据,而过程不返回数据。

1. 存储过程概念

存储过程(Stored Procedure)是在大型数据库系统中,一组为了完成特定功能的SQL语句集,存储在数据库中。经过第一次编译后再次调用不需要再次编译,用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。

存储过程是数据库中的一个重要对象,任何一个设计良好的数据库应用程序都应该用到存储过程。

2. 创建过程

语法:

CREATE [ OR REPLACE ] PROCEDURE [ schema. ] procedure_name
   [ ( parameter_declaration [, parameter_declaration ]... ) ]
   [ invoker_rights_clause ]
   { IS | AS }
   { [ declare_section ] body | call_spec | EXTERNAL} ;

说明:

procedure_name:过程名称。

parameter_declaration:参数声明,格式如下:

parameter_name [ [ IN ] datatype [ { := | DEFAULT } expression ]
          | { OUT | IN OUT } [ NOCOPY ] datatype 

IN:输入参数。

OUT:输出参数。

IN OUT:输入输出参数。

invoker_rights_clause:这个过程使用谁的权限运行,格式:

AUTHID { CURRENT_USER | DEFINER }

declare_section:声明部分。

body:过程块主体,执行部分。

一般只有在确认procedure_name过程是新过程或是要更新的过程时,才使用OR REPALCE关键字,否则容易删除有用的过程。

示例1:

CREATE PROCEDURE remove_emp (employee_id NUMBER) AS
   tot_emps NUMBER;
   BEGIN
      DELETE FROM employees
      WHERE employees.employee_id = remove_emp.employee_id;
      tot_emps := tot_emps - 1;
   END;

示例2:

CREATE OR REPLACE PROCEDURE insert_emp(
   v_empno     in employees.employee_id%TYPE,
   v_firstname in employees.first_name%TYPE,
   v_lastname  in employees.last_name%TYPE,
   v_deptno    in employees.department_id%TYPE
   )
AS
   empno_remaining EXCEPTION;
   PRAGMA EXCEPTION_INIT(empno_remaining, -1);
BEGIN
   INSERT INTO EMPLOYEES(EMPLOYEE_ID, FIRST_NAME, LAST_NAME, HIRE_DATE,DEPARTMENT_ID)
   VALUES(v_empno, v_firstname,v_lastname, sysdate, v_deptno);
   DBMS_OUTPUT.PUT_LINE('插入成功!');
EXCEPTION
   WHEN empno_remaining THEN
      DBMS_OUTPUT.PUT_LINE('违反数据完整性约束!');
      DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
END;

3. 使用过程参数

当建立过程时,既可以指定过程参数,也可以不提供任何参数。

过程参数包括输入参数、输出参数和输入输出参数,其中输入参数(IN)用于接收调用环境的输入数据,输出参数(OUT)用于将输出数据传递到调用环境,而输入输出参数(IN OUT)不仅要接收输入数据,而且还要输出数据到调用环境。

3.1 带有输入参数的过程

通过使用输入参数,可以将应用程序数据传递到过程。当定义过程参数时,默认参数模式是输入参数,另外可以使用IN关键字显式定义输入参数。

示例:

CREATE OR REPLACE PROCEDURE insert_emp(
    empno employee.empno%TYPE,
    ename employee.ename%TYPE,
    job employee.job%TYPE,
    sal employee.sal%TYPE,
    comm IN employee.comm%TYPE,
    deptno IN employee.deptno%TYPE
    )
IS
BEGIN
    INSERT INTO employee VALUES(empno, ename, job, sal, comm, depno);
END;

3.2 带有输出参数的过程

通过在过程中使用输出参数,可以将数据或消息传递到调用环境和应用程序。当定义输出参数时,需要指定参数模式OUT。

示例:

CREATE OR REPLACE PROCEDURE update_sal(
    eno NUMBER,
    salary NUMBER,
    name out VARCHAR2) 
IS
BEGIN
    UPDATE employee SET sal=salary WHERE empno=eno
    RETURNING ename INTO name;
END;

3.3 带有输入输出参数的过程

通过在过程中使用输入输出参数,可以在调用过程时输入数据到过程,在调用结束后输出数据到调用环境和应用程序。当定义输入输出参数时,需要指定参数模式为IN OUT。

示例:

CREATE OR REPLACE PROCEDURE divide(
    num1 IN OUT NUMBER,
    num2 IN OUT NUMBER) 
IS
    v1 NUMBER;
    v2 NUMBER;
BEGIN
    v1 := trunc(num1 / num2);
    v2 := mod(num1,num2);
    num1 := v1;
    num2 := v2;
END; 

4. 调用过程

当在SQL*PLUS中调用过程时,需要使用CALL或者EXECUTE命令,而在PL/SQL块中过程可以直接引用。

ORACLE使用EXECUTE语句来调用存储过程语法:

EXEC[UTE] procedure_name(parameter1, parameter2, …);

示例1:

-- 调用删除员工的过程
EXEC remove_emp(1);

-- 调用插入员工的过程
EXECUTE insert_emp(1, 'tommy', 'lin', 2);

示例2:

DECLARE
    v_name employee.ename%type;
BEGIN
    update_sal(&eno,&salary,v_name);
    dbms_output.put_line('姓名:'||v_name);
END;

5. 函数介绍

函数是一段独立的PL/SQL程序代码,它执行某个特定的、明确的任务。通常,函数将处理从程序的调用部分传递给它的信息,然后返回单个值。信息通过称为参数的特殊标识符传递给函数,然后通过RETURN语句返回。

6. 创建函数

语法:

CREATE [ OR REPLACE ] FUNCTION [ schema. ] function_name
  [ ( parameter_declaration [, parameter_declaration]... ) 
  ]
  RETURN datatype
  [ { invoker_rights_clause
    | DETERMINISTIC
    | parallel_enable_clause
    | RESULT_CACHE  [ relies_on_clause ]
    }...
  ]
  { { AGGREGATE | PIPELINED }  USING [ schema. ] implementation_type
  | [ PIPELINED ] { IS | AS } { [ declare_section ] body 
                              | call_spec
                              | EXTERNAL
                              }
  } ;

示例1:

CREATE FUNCTION get_bal(acc_no IN NUMBER) 
   RETURN NUMBER 
   IS
     acc_bal NUMBER(11,2);
   BEGIN 
      SELECT order_total INTO acc_bal FROM orders 
      WHERE customer_id = acc_no; 
      RETURN(acc_bal); 
    END;

函数参数也有输入、输出、输入输出三种模式:IN、OUT、IN OUT是形参的模式。若省略,则为IN模式。

和过程一样,IN模式的形参只能将实参传递给形参,进入函数内部,但只能读不能写,函数返回时实参的值不变。

OUT模式的形参会忽略调用时的实参值(或说该形参的初始值总是NULL),但在函数内部可以被读或写,函数返回时形参的值会赋予给实参。

IN OUT具有前两种模式的特性,即调用时,实参的值总是传递给形参,结束时,形参的值传递给实参。

调用时,对于IN模式的实参可以是常量或变量,但对于OUT和IN OUT模式的实参必须是变量。

示例2:

CREATE OR REPLACE FUNCTION get_salary(
  dept_no IN NUMBER DEFAULT 1,
  emp_count OUT NUMBER)
  RETURN NUMBER
IS
  V_sum NUMBER;
BEGIN
  SELECT SUM(SALARY), count(*) INTO V_sum, emp_count FROM EMPLOYEES
  WHERE DEPARTMENT_ID=dept_no;
  RETURN v_sum;
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('数据不存在');
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('其它异常:');
      DBMS_OUTPUT.PUT_LINE('错误号:' || SQLCODE||',错误消息:'||SQLERRM);
END get_salary;

7. 函数调用

语法:

function_name([[parameter_name1 =>] value1[, [parameter_name2 =>] value2, ...]]);

示例1:

DECLARE
  v_num NUMBER;
  v_sum NUMBER;
BEGIN
  v_sum := get_salary(27, v_num);
  DBMS_OUTPUT.PUT_LINE('部门27的工资总和:'||v_sum||',人数为:'||v_num);
END;

示例2:

DECLARE
  v_num NUMBER;
  v_sum NUMBER;
BEGIN
  v_sum := get_salary(dept_no => 27, emp_count => v_num);
  DBMS_OUTPUT.PUT_LINE('部门27的工资总和:'||v_sum||',人数为:'||v_num);
END;

示例3:

DECLARE
  v_num NUMBER;
  v_sum NUMBER;
BEGIN
  v_sum := get_salary(emp_count => v_num);
  DBMS_OUTPUT.PUT_LINE('部门27的工资总和:'||v_sum||',人数为:'||v_num);
END;

8. 删除过程或函数

删除过程语法:

DROP PROCEDURE [schema.]procudure_name;

删除函数语法:

DROP FUNCTION [schema.]function_name;

9. 过程与函数比较

过程 函数
作为PL/SQL语句执行 作为表达式的一部分执行
在规范中不包含RETURN子句 必须在规范中包含RETURN子句
不返回任何值 必须返回单个值
可以RETURN语句,但是与函数不同,它不能用于返回值 必须包含至少一条RETURN语句

过程与函数的相同功能有:

  1. 都使用IN模式的参数传入数据、OUT模式的参数返回数据。
  2. 输入参数都可以接受默认值,都可以传值或传引导。
  3. 调用时的实际参数都可以使用位置表示法、名称表示法或组合方法。
  4. 都有声明部分、执行部分和异常处理部分。
  5. 其管理过程都有创建、编译、授权、删除、显示依赖关系等。
版权声明:本文为博主原创文章,未经博主允许不得转载。

在命令窗口中编写pl/sql编写函数,并执行调用

--函数案例--输入雇员的姓名,返回雇员的年薪create function sp_fun2(spName varchar2)returnnumber yearSal number(7,2);begi...
  • Dean_Deng
  • Dean_Deng
  • 2011年07月23日 17:15
  • 8587

PL/SQL的函数定义和使用

函数的结构 声明部分:包括类型、变量、游标 执行部分:完成功能而编写的SQL语句或则是PL/SQL代码块 异常处理部分 创建函数 CREATE [ OR REPLACE] FUNCTION ...
  • xuenandong
  • xuenandong
  • 2012年04月10日 14:08
  • 1184

PL/SQL --> 动态SQL调用包中函数或过程

动态SQL主要是用于针对不同的条件或查询任务来生成不同的SQL语句。最常用的方法是直接使用EXECUTE IMMEDIATE来执行动态SQL语句字符串或字符串变量。但是对于系统自定义的包或用户自定的包...
  • robinson_0612
  • robinson_0612
  • 2013年09月17日 17:28
  • 5912

oracle编写 函数和存储过程

转载自:  http://blog.csdn.net/lailai186/article/details/19115123 6.1 引言 6.2 创建函数 6.3 存储过程 6.3.1 创建过程 ...
  • Denghejing
  • Denghejing
  • 2016年09月28日 16:47
  • 1820

pl/sql 过程、函数

pl/sql中proceduce过程与func函数均为一个程序块,是为完成特定功能的pl/sql语句集。并可接受三种类型的参数。 相同点在于: 1.均可使用out类型参数返回值。 2.均由声明、执行和...
  • Alexander_Frank
  • Alexander_Frank
  • 2016年04月11日 15:05
  • 715

PL/SQL函数总结

number数字类型函数ABS(x) 函数,此函数用来返回一个数的绝对值。ACOS(x)函数,返回X的反余弦值。X范围从1到-1,输入值从0到派,以弧度为单位。ASIN(x)函数,返回X的反正弦值。X...
  • hanchi8008
  • hanchi8008
  • 2006年10月25日 09:26
  • 4805

浅析PL/SQL过程化语言编程要点

PL/SQL作为过程化语言的特性,是对面向集合操作的sql语言的有力补充,那么因为PL/SQL与sql有着千丝万缕的联系,可能让我们对PL/SQL产生一些误解,在工作中和论坛的问题中发现很多,咱们是否...
  • bailao1
  • bailao1
  • 2015年06月24日 14:45
  • 737

SQL自定义函数function

用户定义自定义函数像内置函数一样返回标量值,也可以将结果集用表格变量返回。 sql函数必须有返回值。标量函数:返回一个标量值。 表格值函数{内联表格值函数、多表格值函数}:返回行集(即返回多个值)...
  • qq_23833037
  • qq_23833037
  • 2016年11月15日 14:37
  • 6234

SQL中存储过程和函数的标准写法

之前一直用的存储过程,今天忽然又接触到跑算法获取返回值的问题,想到可以用函数,好久没用过一时忘了怎么写,还要现查,于是就记录下来,顺便连存储过程一块啦。 存储过程: CREATE PROC...
  • u012630420
  • u012630420
  • 2017年03月01日 16:23
  • 1497

[oracle]pl/sql in/out变量的使用和过程中过程的调用

--in/out 类型变量的使用 以及过程中调用过程,获取out类型的返回值 --建一张表books --简单过程一 插入一条记录  create table books(bookid number,...
  • lzz957748332
  • lzz957748332
  • 2014年07月31日 00:29
  • 2314
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:Oracle数据库之PL/SQL过程与函数
举报原因:
原因补充:

(最多只允许输入30个字)