`

PLSQL_存储过程和函数

 
阅读更多

转自:http://czmmiao.iteye.com/blog/1815000

存储过程概述

存储过程是子程序的一种类型,能够完成一些任务,作为schema对象存储于数据库。是一个有名字的PL/SQL代码块,支持接收或不接受参数,同时也支持参数输出。一个存储过程通常包含定义部分,执行部分,Exception部分,可以被其他子程序调用,也可以被重用。
过程定义
CREATE [OR REPLACE]PROCEDURE procedure_name
[(argument_name [IN | OUT | IN OUT] argument_type)]
AS | IS
BEGIN
    procedure_body;
END [procedure_name];
存储过程中参数的类型
IN:表示是一个输入参数,可以指定缺省值。如省略参数类型,则缺省为in类型
OUT:表示是一个输出参数
IN OUT:既可以作为一个输入参数,也可以作为一个输出参数来输出结果
过程调用
EXECUTE |CALL procedure_name [(argument_list)]

例:定义一个过程,以JOB为参数,查询该JOB的最高工资、最低工资、平均工资。

create or replace procedure query_sal(v_job in emp.job%type)
   as
   v_min_sal emp.sal%type;
   v_max_sal emp.sal%type;
   v_avg_sal emp.sal%type;
begin
   select min(sal) into v_min_sal from emp where job = v_job;
   select max(sal) into v_max_sal from emp where job = v_job;
   select avg(sal) into v_avg_sal from emp where job = v_job;
   dbms_output.put_line('This job is minimum salary is ' || v_min_sal);
   dbms_output.put_line('This job is maximum salary is ' || v_max_sal);
   dbms_output.put_line('This job is average salary is ' || v_avg_sal);
exception
   when no_data_found then
     dbms_output.put_line('Not Record Found');
end;

SQL> set serveroutput on
SQL> exec query_sal('SALESMAN');
This job is minimum salary is 1250
This job is maximum salary is 1600
This job is average salary is 1400
PL/SQL procedure successfully completed.

参数及其传递方式
在 建立过程时,传递的参数为可选项,如果省略参数选项,则过程为无参过程(定义时不指定参数,调用时也不需要参数)。如果指定参数选项,则过程为有参过程 (定义时需要指定参数名字、模式、数据类型,调时时需要给出对应的参数值),定义时的参数,称为形参,调用时的参数称为实参。

无参过程
create or replace procedure display_systime
as
begin
  dbms_output.put_line('Current Time is ' || sysdate);
end;
SQL> exec display_systime;
Current Time is 24-FEB-13

有参过程
定义时需要指定参数的名字、模式、数据类型
例:定义一个添加记录的过程(全部为输入参数)
create or replace procedure add_emp
(
v_no in emp.empno%type,
v_name in emp.ename%type,
v_dept in emp.deptno%type default 20   --缺省的部门号
)
as
begin
  insert into emp (empno,ename,deptno) values(v_no,v_name,v_dept);
exception
  when dup_val_on_index then
    dbms_output.put_line('Record Exists');
end ;

SQL> exec add_emp(7369,'TEST',20);    --调用
Record Exists

例:定义一个输入员工编号,修改记录,再返回修改后的结果(姓名和工资)。

create or replace procedure ed_emp
 (
 v_no in emp.empno%type,    --定义了一个in类型,二个out类型的参数
 v_name out emp.ename%type,
 v_sal out emp.sal%type)
 as
 begin
   update emp set sal = sal + 100 where empno = v_no;
   select ename,sal into v_name,v_sal from emp where empno = v_no;
 exception
   when no_data_found then
     dbms_output.put_line('Not Data Found');
 end;
 /
Procedure created.

SQL>VARIABLE t_name varchar2(20);
SQL>VARIABLE t_sal number;
SQL> exec ed_emp(7369,:t_name,:t_sal);
PL/SQL procedure successfully completed.
SQL> print t_name
T_NAME
--------------------------------------------------------------------------------
SMITH
SQL> print t_sal
     T_SAL
----------
       900
例:IN OUT类型参数的使用

create or replace procedure comp
(num1 in out number,num2 in out number)
as
  v1 number;
  v2 number;
begin
  v1 := num1 + num2;
  v2 := num1 * num2;
  num1 := v1;
  num2 := v2;
end;
SQL> var v1 number
SQL> var v2 number
SQL> exec :v1 := 3
PL/SQL procedure successfully completed.
SQL> exec :v2 := 5
PL/SQL procedure successfully completed.
SQL> exec comp(:v1,:v2);
SQL> print v1 v2
        V1
----------
         8
        V2
----------
        15
SQL> exec comp(:v1,:v2);
PL/SQL procedure successfully completed.
SQL> print v1 v2
        V1
----------
        23
        V2
----------
       120

可以看到in out类型的参数既作为输入参数又作为输出参数。

存储过程参数的传递方式:
按位置传递:
实参按顺序将值传给形参
EXECUTE ED_EMP(7900,:t_name,:t_sal);
EXECUTE ED_EMP(8000,'TEST2',20);
按名字传递
EXECUTE ED_EMP(v_name=>'ABCDE',v_dept=>10,v_no=>8003); 
混合传递
EXECUTE ED_EMP(8005,v_dept=>20,v_name=>'TEST5');

过程管理
查看系统过程信息
DBA_OBJECTS
DBA_PROCEDURES
DBA_SOURCE
使用desc procedure_name 查看存储过程的参数信息
SQL>desc ed_emp;
PROCEDURE ed_emp
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 V_NO                           NUMBER(4)               IN
 V_NAME                         VARCHAR2(10)            OUT
 V_SAL                          NUMBER(7,2)             OUT 
从dba_objects获得存储过程的信息
SQL>select owner,object_name,object_type,status from dba_objects where object_name = 'ED_EMP';
OWNER                          OBJECT_NAME          OBJECT_TYPE     STATUS
------------------------------ -------------------- --------------- -------
SCOTT                          ED_EMP               PROCEDURE       VALID 
SQL>select object_name,procedure_name,interface,authid from user_procedures;
OBJECT_NAME          PROCEDURE_NAME                 INT AUTHID
-------------------- ------------------------------ --- ------------
DISPLAY_SAL                                         NO  DEFINER
ED_EMP                                              NO  DEFINER
查看存储过程的源代码
SQL>select line, text from user_source where name='ED_EMP';
LINE        TEXT
---------- --------------------------------------------------------------------------------
  1         PROCEDURE ed_emp
  2               (
  3               v_no IN emp.empno%TYPE,   
  4               v_name OUT emp.ename%TYPE,
  5               v_sal OUT emp.sal%TYPE
  6               )
  7               AS
  8               BEGIN
  9                 UPDATE emp SET sal=sal+100 WHERE empno=v_no;
 10                 SELECT ename,sal INTO v_name,v_sal FROM emp WHERE empno=v_no;
 11               EXCEPTION
 12                 WHEN NO_DATA_FOUND THEN
 13                   DBMS_OUTPUT.PUT_LINE('NOT FOUND RECORD!');
 14               END ed_emp;
查看错误信息
SHOW ERRORS
函数概述

函数通常用于返回特定的数据。其实质是一个有名字的PL/SQL块,作为一个schema对象存储于数据库,可以被反复执行。函数通常被作为一个表达式来调用或存储过程的一个参数,具有返回值。
一、建立函数的语法
CREATE [ OR REPLACE ] FUNCTION function_name
    (argument1 [mode1] datatype1,
     argument2 [mode2] datetype2,
     ...)
RETURN datatype
IS | AS
    [local_variable_declarations;...]
BEGIN
    --actions;
    RETURN expression;
END [function_name];
建立函数的几点注意事项
1.指定参数数据类型时(argument),不能指定其长度
2.函数头部必须指定return子句,函数体内至少要包含一条return语句
3.可以指定in参数,也可以指定out参数,以及in out 参数
4.可以为参数指定缺省值。指定缺省值时使用default关键字。如arg1 varchar2 default 'SCOTT'
使用函数的优点:
1.增加了代码的灵活性,可以完成一些较为复杂的任务,以及仅仅通过SQL无法完成的任务
2.可以直接将函数使用到where子句中来过滤数据
3.可以作为存储过程的参数使用,是存储过程的一种补充

建立函数
建立不带参数的函数

create or replace function get_user
return  varchar2
as
  v_user varchar2(20);
begin
  select username into v_user from user_users;
  return v_user;
end;

使用全局变量接收函数的返回值

SQL> var v1 varchar2(20);
SQL> exec :v1 := get_user;
PL/SQL procedure successfully completed.
SQL> print v1
V1
--------------------------------------
SCOTT
使用本地变量接收函数的返回值

SQL> declare user_name varchar2(20);                    
  2   begin
  3   user_name := get_user();
  4   dbms_output.put_line('Current User: ' || user_name);
  5  end;
  6  /
Current User: SCOTT
PL/SQL procedure successfully completed.

在SQL语句中直接调用函数

SQL> select get_user from dual;
GET_USER
------------------------------------
SCOTT
使用dbms_output调用函数(此调用作为存储过程的一个参数来进行调用)  
SQL> set serveroutput on;
SQL> exec dbms_output.put_line('Current user: '||get_user);
Current user: SCOTT  

建立带有in 参数的函数

create or replace function raise_sal(name in varchar2)
return number
as
  new_sal emp.sal%type;
begin
  select sal * 1.2 into new_sal from emp 
  where upper(ename) = upper(name);
  return new_sal;
exception
  when no_data_found then
    raise_application_error(-20000,'Current Employee does not exists');
end;

 

SQL>  select sal,raise_sal('SCOTT') from emp where ename='SCOTT';
       SAL RAISE_SAL('SCOTT')
---------- ------------------
      3000               3600

 

SQL>  select sal,raise_sal('SCOTTT') from emp where ename='SCOTT';
 select sal,raise_sal('SCOTTT') from emp where ename='SCOTT'
            *
ERROR at line 1:
ORA-20000: Current Employee does not exists
ORA-06512: at "SCOTT.RAISE_SAL", line 11

建立带有out参数的函数
create or replace function get_info
(name varchar2,titile out varchar2)
return varchar2
as
  deptname dept.dname%type;
begin
  select e.job,d.dname into titile,deptname
  from emp e,dept d
  where e.deptno = d.deptno
  and upper(e.ename) = upper(name);
  return deptname;
exception
  when no_data_found then
    raise_application_error(-20000,'Current Employee does not exists');
end;
/

注意对于使用out参数的函数,不能使用SQL语句来调用。而必须定义变量接收out参数和函数的返回值。
调用如下

SQL> var job varchar2(20);
SQL> var dname varchar2(20);
SQL> exec :dname := get_info('scott',:job);
PL/SQL procedure successfully completed.
SQL> print dname job
DNAME
-------------------------------------------------
RESEARCH
JOB
-------------------------------------------------
ANALYST
SQL> select get_info('scott') from dual
  2  ;
select get_info('scott') from dual
       *
ERROR at line 1:
ORA-06553: PLS-306: wrong number or types of arguments in call to 'GET_INFO'

建立带有in out参数的函数
create or replace function comp
(num1 number,num2 in out number)
return number
as
  v_result number(6);
  v_remainder number;
begin
  v_result := num1 * num2;
  v_remainder := mod(num1,num2);
  num2 := v_remainder;
  return v_result;
exception
  when zero_divide then
    raise_application_error(-20000,'Divsion by zero');
end;
/

SQL> var n1 number    
SQL> var n2 number
SQL> exec :n2 := 10;
PL/SQL procedure successfully completed.
SQL> exec :n1 := comp(16,:n2);
PL/SQL procedure successfully completed.
SQL> print n1 n2
        N1
----------
       160
        N2
----------
         6
函数的调用及限制
1.函数的调用(其具体调用方法参照上面的演示)
a.使用全局变量接收函数的返回值
b.使用本地变量接受函数的返回值
c.在SQL语句中直接调用函数
d.使用dbms_output调用函数
注:函数在调用的时候需要按位置指定参数,没有存储过程参数传递灵活必须具有execute 函数的权限
2.函数在SQL中调用的主要场合
由于函数必须要返回数据,因此只能作为表达式的一部分调用。此外函数可以在SQL语句的以下部分调用
a. select 命令的选择列表或子查询中
b. 条件表达式where, having子句中
c. connect by , start with ,order by 以及group by 子句中
d. insert 命令的values子句中
f. update 命令的set 子句中
3.函数在SQL中调用的限制
a. SQL语句中只能调用存储在服务器端的函数,而不能调用存储于客户端的函数
b. SQL语句中调用的函数只能带有输入参数IN,而不能带有输出参数OUT 以及输入输出参数IN OUT
c. SQL语句中调用的函数只能使用SQL支持的标准数据类型,不能使用PL/SQL特有的类型,如boolean,table,record等
d. SQL语句中调用的函数不能包含insert ,update 和delete 语句
创建一张表tb_emp

SQL> create table tb_emp as select * from emp;
Table created.

创建一个函数,用于删除tb_emp表中指定的empno号的雇员信息,并返回其薪资
SQL> create or replace function del_emp
  2  (no number)
  3  return number
  4  as
  5    v_sal emp.sal%type;
  6  begin
  7    select sal into v_sal from emp where empno = no;
  8    delete tb_emp where empno = no;
  9    return v_sal;
 10  end;
 11  /
Function created.

使用SQL语句调用时,收到了错误信息,在内部查询内不能完成DML操作
SQL> select del_emp(7788) from dual;
select del_emp(7788) from dual
       *
ERROR at line 1:
ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at "SCOTT.DEL_EMP", line 8

使用exec执行时函数被成功执行
SQL> var n1 number
SQL> exec :n1 := del_emp(7788);
PL/SQL procedure successfully completed.
SQL> print n1
        N1
----------
      3000

函数的管理 
函数使用了与存储过程相关的视图,可以从系统视图中获得函数的相关信息
DBA_OBJECTS
DBA_SOURCE
USER_OBJECTS
USER_SOURCE

查看函数的源码

SQL>select line,text from user_source where name='DEL_EMP' order by line

      LINE TEXT
---------- ------------------------------------------------------------
         1 function del_emp
         2 (no number)
         3 return number
         4 as
         5   v_sal emp.sal%type;
         6 begin
         7   select sal into v_sal from emp where empno = no;
         8   delete tb_emp where empno = no;
         9   return v_sal;
        10 end;
10 rows selected.

查看函数的参数信息

SQL> desc del_emp;
FUNCTION del_emp RETURNS NUMBER
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 NO                             NUMBER                  IN

函数与存储过程的差异
存储过程                                                           函数
----------------------------------                               -------------------------------
不能被作为表达式调用                                     只能作为表达式被调用
声明头部关键字为procedure                            声明头部关键字为function
声明头部不包含return关键字来描述返回类型         头部必须包含return关键字,且PL/SQL块中至少包含一个有效的return语句
可以通过out,in out返回零个或多个值               通过return语句返回一个与头部声明中类型一致的值,也可使用in,in out返回值
SQL语句中不可调用存储过程                        SQL语句可以调用函数
多用于数据库中完成特定的操作,如删除,更新,插入等DML操作     多用于特定的数据如选择等

分享到:
评论

相关推荐

    Oracle_PLSQL_存储过程

    - **存储过程作用:** 在Oracle中,存储过程可以用于封装这些规则和逻辑,使之成为可重用的组件。 **创建权限:** - **CREATE PROCEDURE权限:** 允许用户创建存储过程。 - **CREATE ANY PROCEDURE权限:** 允许...

    PLSQL_YouCan!_plsql_

    5. **存储过程与函数**:存储过程是一组预编译的SQL和PLSQL语句,可以接受参数并返回结果。函数类似于存储过程,但必须返回一个值。它们提高了代码的重用性和效率。 6. **包(PACKAGE)**:包是组织PLSQL对象(如...

    PLSQL_X64 64位plsql

    PLSQL(Procedural Language/Structured Query Language)是Oracle数据库专为开发人员设计的一种过程化SQL语言,主要用于在Oracle环境中编写存储过程、函数、触发器等数据库对象。64位的PLSQL指的是在64位操作系统上...

    PLSQL.rar_oracle_pl sql_plsql_plsql java_sql

    通过JDBC,Java开发者可以编写SQL语句,调用PLSQL存储过程,执行批量更新,并处理数据库连接。 文件列表中的“PL SQL.PDF”可能是一份详细的PLSQL参考指南或教程,涵盖了上述所有概念和实践。PDF格式通常用于电子书...

    PLSQL_Developer10.zip

    - **对象浏览器**:允许用户直观地浏览和管理数据库对象,如表、视图、存储过程、函数和触发器等。 - **数据查看器**:可以实时查看和编辑数据库中的数据,支持筛选、排序和格式化显示。 - **调试器**:集成的...

    PLSQL.rar_plsql_plsql java

    8. **PLSQL与Java的交互**:在Java应用程序中,可以通过JDBC(Java Database Connectivity)接口调用PLSQL存储过程和函数,实现数据库操作。使用CallableStatement对象可以执行预编译的PLSQL语句。 9. **PLSQL的...

    PLSQL_oracle数据库

    例如,了解如何声明变量、控制流程(如IF-THEN-ELSE,WHILE循环,CASE语句)、处理异常(EXCEPTION块),以及如何调用存储过程和函数等。 此外,为了提高性能和优化数据库操作,还需要掌握索引的创建和使用、表分区...

    PLSQL_Developer_cngr

    它还能自动生成如存储过程、函数、触发器、包和类型等数据库对象的代码模板。 2. **调试**:内置的调试器允许你在代码执行过程中设置断点、查看变量值、单步执行和调用堆栈,这对于查找和修复程序错误非常有用。 3...

    PLSQL_Developer8.0绿色版64位

    PL/SQL的主要功能包括创建存储过程、函数、触发器和包,用于数据库的业务逻辑处理和数据操作。 2. 64位支持:64位系统允许应用程序访问更大的内存空间,处理更大规模的数据,这对于处理大数据量的数据库操作和复杂...

    PLSQL_Developer_7.1

    函数和存储过程是数据库中的自定义代码单元,用于执行特定的任务,而PLSQL Developer提供了一个直观的界面,允许用户通过拖放和填写参数来创建和编辑这些对象。触发器是自动执行的数据库事件响应程序,同样可以在...

    plsql教程大全(语法入门、操作plsql、存储过程、函数、触发器、游标、包、返回类型)

    语法入门、操作plsql、存储过程、函数、触发器、游标、包、返回类型;一个execle表轻松搞定。

    plsql.zip_plsql_zip

    PLSQL支持子程序(包括函数和过程)、记录类型、游标、异常处理和动态SQL,这些特性使得开发者能够编写高效、健壮且可维护的代码。 在实际开发中,PLSQL常用于实现数据库级别的业务规则,例如在数据插入、更新或...

    PLSQL.rar_Java plsql_pl sql java p_plsql_plsql java

    - **CallableStatement**:用于执行存储过程和函数,设置输入/输出参数。 - **PreparedStatement**:预编译SQL语句,提高性能,也可执行PL/SQL块。 - **结果集处理**:使用ResultSet对象获取查询结果,进行遍历和...

    plsql_Developer(中文版)

    工具允许用户方便地查看、创建、修改和删除各种数据库对象,如表、视图、索引、序列、同义词、存储过程、函数等。用户可以通过直观的图形界面来操作,减少了手动编写SQL语句的需求。 3. **数据查询与浏览** 提供...

    PLSQL+instantclient_11_2.rar解压可用

    在Oracle环境中,PL/SQL是开发者用来编写存储过程、函数、触发器、游标等数据库对象的主要工具。 Instant Client是Oracle公司提供的一种轻量级的Oracle客户端软件,它不需要完整的Oracle数据库服务器安装,仅包含...

    Plsql_win7_32位客户端.zip

    开发者可以利用PL/SQL创建存储过程、触发器、函数和包,这些是数据库中的可重用代码单元,用于执行复杂的数据操作和业务逻辑。 在Windows 7环境下安装PL/SQL Developer 32位版,你需要确保系统已安装了Oracle Data ...

    PLSQL_plsqldev9.zip

    该软件支持创建存储过程、函数、触发器、包、类型等,同时具有代码高亮、自动完成、错误检查和数据库连接管理等功能,极大地提高了开发效率。 "chinese.exe"可能表示这个版本的PL/SQL Developer是中文版,便于中文...

    PLSQL_Developer Version 11.0.6.1776

    1. PL/SQL语言:PLSQL是Oracle数据库支持的一种过程化编程语言,结合了SQL查询语句和传统编程语言的特性,如控制结构、变量和函数。PLSQL Developer提供了一个完整的环境来编写、调试、运行和管理PL/SQL代码。 2. ...

    PLSQL.rar_pl\sql_plsql_plsql java 分析_语法分析

    Java应用程序可以通过JDBC驱动连接到Oracle数据库,并执行PLSQL存储过程或查询。同时,PLSQL也可以调用Java对象,通过Oracle的Java存根(Java Stubs)技术,将Java类编译为数据库中的可执行对象,实现PLSQL对Java...

Global site tag (gtag.js) - Google Analytics