当前位置:  数据库>oracle

PL/SQL之存储过程和函数

    来源: 互联网  发布时间:2017-06-24

    本文导语: 1、创建存储过程 1.1语法:CREATE[OR REPLACE] PROCEDURE [schema.] procedure_name[(argument[{IN|OUT|IN OUT}] datatype[,...])] {IS|AS} pl/sql_body; procedure_name为存储过程的名称,argument是参数名,datatype是对应参数的数据类型,pl/sql_body是该存储过程真正进行...

1、创建存储过程

1.1语法:
CREATE[OR REPLACE] PROCEDURE [schema.] procedure_name[(argument[{IN|OUT|IN OUT}] datatype[,...])] {IS|AS} pl/sql_body;

procedure_name为存储过程的名称,
argument是参数名,
datatype是对应参数的数据类型,
pl/sql_body是该存储过程真正进行的处理操作的PL/SQL块,
OR REPLACE是可选项,如果存在一个同名的存储过程,则先删除后创建,
关键字IS|AS是等价的,用来引出过程体。

1)存储过程的参数
创建存储过程
CREATE OR REPLACE PROCEDURE UpdateAuths(
    p_AuthsCode auths.author_code%TYPE,
    p_AuthsSalary auths.salary%TYPE)
AS
BEGIN
    UPDATE auths
        SET salary=p_AuthsSalary
        WHERE author_code=p_AuthsCode;
    COMMIT;
END UpdateAuths;

调用存储过程
DECLARE
    v_authorcode auths.author_code%TYPE:='A0001';
    v_salary auths.salary%TYPE:=350;
BEGIN
    UpdateAuths(v_authorcode,v_salary);
END;

形参和实参
p_AuthsSalary,p_AuthsCode为形式参数
v_authorcode,v_salary为实际参数

关键字代表参数的三种不同模式
IN:当调用存储过程的时候,该模式的形参接收对应实参的值,并且该形参是只读的,即不能被修改。如果在创建存储过程时没有指定参数的模式,则默认为IN。

OUT:在存储过程中,该形参被认为是只能写,即只能为其赋值。在存储过程中不能读它的值。返回时,将该形参值传给相应的实参。

IN OUT:该模式是前两种模式的合并。

2)存储过程的处理部分
    一个PL/SQL块,该块包含定义部分、可执行部分以及异常处理部分,其中可执行部分是必须有的。
CREATE OR REPLACE 过程名
(
    --参数定义部分
)   
IS
    --局部变量定义部分
BEGIN
    --可执行部分
EXCEPTION
    --异常处理部分
END 过程名;

在END后加上存储过程名是可选的,只是为了增加程序的可读性。如果加上,则必须要和前面的存储过程名相同。

1.2存储过程的参数
1)参数的数据类型
    在定义一个存储过程参数时,不能指定CHAR类型和VARCHAR2类型形参的长度,也不能指定NUMBER形参的精度和标度。这些约束有实参来传递。
   
CREATE OR REPLACE PROCEDURE proc_auths(
    --参数定义了类型长度,这将产生编译错误。
    p_code IN OUT VARCHAR2(6),
    P_salary OUT NUMBER(8,2)) AS
BEGIN
    ...
END proc_auths;

应修改为:
CREATE OR REPLACE PROCEDURE proc_auths(
    p_code IN OUT VARCHAR2,
    P_salary OUT NUMBER) AS
BEGIN
    SELECT salary INTO p_salary
        FROM auths
        WHERE author_code=p_code;
END proc_auths;

调用存储过程:
DECLARE
    v_code VARCHAR2(6);
    v_salary NUMBER(8,2);
BEGIN
    v_code:='A0001';
    proc_auths(v_code,v_salary);
END;

如果使用%TYPE为参数定义类型,那么该参数将具有定义在形参上而不是通过实参传递的数据长度。
CREATE OR REPLACE PROCEDURE query_salary(
    p_code IN OUT auths.author_code%TYPE,
    P_salary OUT auths.salary%TYPE) AS
BEGIN
    ...
END query_salary;

如上面的存储过程,由于auths表中的author_code字段长度为6,因此p_code的长度也为6。
2)参数的传值方式
位置表示法
    实参通过位置与形参进行联系。
名称表示法
    实参是与形参的名称进行联系。

定义一个存储过程
CREATE OR REPLACE PROCEDURE insert_auths(
    p_code auths.author_code%TYPE,
    p_name auths.name%TYPE) AS
BEGIN
    INSERT INTO auths(author_code,name) VALUES(p_code,p_name);
END insert_auths;

DECLARE
    v_code VARCHAR2(6);
    v_name VARCHAR2(12);
BEGIN
    v_code:='A0001';
    v_name:='张三';
    --使用位置表示法调用
    insert_auths(v_code,v_name);
    --使用命名表示法调用
    insert_auths(p_name-->v_name,p_code-->v_code);
END;

两种表示法可以混合使用。但是,当调用存储过程中出现了第一个命名表示法的参数时,后面的参数也必须使用命名表示法传值。

3)参数的缺省值
类似于变量的声明,一个过程或函数的形参可以有缺省值。如果参数有缺省值,那么在调用时就可以不用给它传值,只使用缺省值。如果给它传值,则实参的值代替缺省的值。

参数缺省值声明如下:
parameter [mode] datatype {:=|DEFAULT} initial_value

parameter是形参名称
mode是参数模式
datatype是参数的类型
initial_value用来为形参指定缺省值
使用关键字DEFAULT或":="来指定一个缺省值。

CREATE OR REPLACE PROCEDURE insert_auths(
    p_code auths.author_code%TYPE :='A0001',
    p_name auths.name%TYPE DEFAULT '张三'
) AS
BEGIN
    ...
END insert_auths;

2、创建函数

函数和存储过程非常类型,都有三种模式的参数。他们都可以被存储在数据库中,并且在快中调用。

存储过程只能作为一个PL/SQL语句调用,而函数作为表达式的一部分调用。并且它们的定义部分、可执行部分和异常处理部分都是不同的。

2.1 创建函数的语法

CREATE [OR REPLACE] FUNCTION schema.function
    [(argument[{IN|OUT|IN OUT}]datatype[,...])]
RETURN return_datatype {IS|AS}
PL/SQL_body;

function是函数名
argument是参数名
datatype是参数的类型
return_datatype是函数返回值的类型
PL/SQL_body是函数的处理部分

参数列表时可选的。在没有参数的情况下,函数的定义与调用都没有圆括号。但返回值类型是必需的,因为函数是作为表达式的一部分调用,必须返回一个值。

CREATE OR REPLACE FUNCTION SalaryStat(
    p_Sex auths.sex%TYPE)
RETURN BOOLEAN IS
    v_MaxAuthors NUMBER;
    v_ReturnValue BOOLEAN;
BEGIN
    SELECT COUNT(author_code)
        INTO v_MaxAuthors
        FROM auths;
    IF v_MaxAuthors> 10 THEN
        v_ReturnValue:=TRUE;
    ELSE
        v_ReturnValue:=FALSE;
    END IF;
    RETURN v_ReturnValue;
END SalaryStat;

DECLARE CURSOR c_Auths IS
    SELECT distinet sex
        FROM auths;
BEGIN
    FOR v_AuthsRecord IN c_Auths LOOP
        IF SalaryStat(v_AuthsRecord.sex) THEN
            UPDATE auths
                SET salary = salary-50
                WHERE sex=v_AuthsRecord.sex;
        END IF;
    END LOOP;
END;

2.2函数的返回值
    在函数内,是通过RETURN语句来返回值的。
    RETURN expression;
   
expression是准备返回的值。如果expression的类型和函数头中指定的类型不符,expression类型会自动转换。RETURN语句被执行后,控制权立刻返回给调用环境。

在函数体重可以有多条RETURN语句,但还只能有一条被执行。在函数结束的时候,如果没有执行RETURN语句会产生错误。

CREATE OR REPLACE FUNCTION SalaryStat(
    p_Sex auths.sex%TYPE)
RETURN VARCHAR2 IS
    v_MaxAuthors NUMBER;
BEGIN
    SELECT COUNT(author_code)
        INTO v_MaxAuthors
        FROM auths;
    IF v_MaxAuthors> 10 THEN
        RETURN v_ReturnValue;
    ELSE
        RETURN v_ReturnValue;
    END IF;
END SalaryStat;

RETURN也可以用在存储过程中。在这种情况下,它没有参数。当执行了不带参数的RETURN语句后,立刻将控制权返回到调用环境,并将OUT和IN OUT模式的形参的当前值传给实参,然后继续执行调用存储过程后的语句。

函数和存储过程的相同点:
都可以通过OUT模式的参数返回一个活多个值。
代码都有定义部分、可执行部分和异常部分。
都可以使用缺省值。
都可以用位置表示法和命名表示法调用。

一般情况下,如果只有一个返回值,使用函数;如果有多个返回值则使用存储过程。

3、删除过程与函数

删除过程
DROP PROCEDURE procedure_name;

删除函数
DROP FUNCTION function_name;

4、库存子程序和局部子程序

    前面所讲的子程序都是存储在数据库中的子程序,即库存子程序。这些子程序都是有CREATE命令创建的,并可在其它的PL/SQL快中调用。他们在创建时要要进行编译,并将编译后的代码存储在数据库中。当子程序被调用时,编译后的代码从数据库中读出并执行。
   
    一个子程序也可以在块的定义部分创建,这样的子程序被叫作局部子程序。
   
DECLARE
    CURSOR c_AllAuthors IS
        SELECT name,sex
        FROM auths;
    v_FormattedName VARCHAR2(60);
    /*函数将返回带性别的作家名,性别用括号括起放在名字的后面*/
    FUNCTION FormatName(p_Name IN VARCHAR2,p_Sex IN NUMBER) RETURN VARCHAR2 IS
        v_Sex VARCHAR2(16);
    BEGIN
        IF p_Sex=1 THEN
            v_Sex:='男';
        ELSE
            v_Sex:='女';
        END IF;
        RETURN p_Name ||'('||v_Sex||')';
    END FormatName;
--块的执行部分开始
BEGIN
    FOR v_AuthsRecord IN c_AllAuthors LOOP
        v_FormattedName:=FormatName(v_AuthsRecord.name,v_AuthsRecord.sex);
        DBMS_OUTPUT.PUT_LINE(v_FormattedName);--显示结果
    END LOOP;
END;

上面的示例,��无名块的定义部分穿件了FormatName函数。这个函数只在创建它的块中可用,它的作用域从创建它开始到块结束,其他的块不能调用它。

局部子程序只能在定义部分的最后被创建,如将FormatName函数移到c_AllAuthors的前面,会出现编译错误。

Oracle数据库之PL/SQL程序基础设计 

PL/SQL Developer实用技巧分享

使用PL/Scope分析PL/SQL代码 

在PL/SQL中使用日期类型

从一个案例看PL/SQL代码片的编译与运行


    
 
 

您可能感兴趣的文章:

  • 在SQL中有函数,比如COUNT(*),怎样通过JDBC调用获得函数值????
  • SQL中含函数,比如COUNT(*),怎样在JDBC接口中取出函数值????
  • sql server中ISNULL函数与Mysql中IFNULL函数的用法
  • sql数据库不能直接用instr函数
  • T-SQL常用的聚合函数
  • sql server中自定义一个简单的 split 函数
  • 初学者问题:标准的sql里有处理日期的函数吗?
  • access与sql server几个常用函数的区别(图文)
  • 分享:sql server 取日期的年月日部分的函数
  • sql函数实现递归查询示例
  • T-SQL常用的数学函数
  • T-SQL实例 函数结果设置为列别名
  • SQL Server中的数据复制到的Access中的函数
  • C处理SQL命令字符串的函数哪里有?
  • SQL中函数 replace 的参数1的数据类型ntext无效的解决方法
  • SQL中有count这个函数,请问如何将这个值取出
  • 高分求java.sql类库的类说明,函数及方法!
  • php is_numberic函数产生sql注入漏洞怎么解决
  • sql函数CAST与CONVERT的区别
  • SQL的SUBSTR()函数使用介绍
  • 在SQL Server中使用SQL语句查询一个存储过程被其它所有的存储过程引用的存储过程名
  • java调用sql server存储过程,如何取存储过程返回码(不是结果集)
  • 急!如何从一个shell文件中传递一个参数到sql文件中,不能用存储过程
  • 在SQL server 2000中用JAVA实现图片存储
  • 用SQL统计SQLServe表存储空间大小的代码
  • 返回SQL执行时间的存储过程
  • java能否调用SQL-SERVER存储过程呢?如果能?如何调?
  • MySQL 存储过程中执行动态SQL语句的方法
  • 浅析SQL Server中包含事务的存储过程
  • SQL SERVER 利用存储过程查看角色和用户信息的写法
  • sql server中sp_reset_connection存储过程的作用
  •  
    本站(WWW.)旨在分享和传播互联网科技相关的资讯和技术,将尽最大努力为读者提供更好的信息聚合和浏览方式。
    本站(WWW.)站内文章除注明原创外,均为转载、整理或搜集自网络。欢迎任何形式的转载,转载请注明出处。












  • 相关文章推荐
  • (X86/X64)安装sql server 2005 过程中提示“无法启动sql server的 启动”的解决方法
  • 请教各位,如何在JSP中调用SQL 过程?
  • oracle sql执行过程(流程图)
  • sql2005 存储过程分页示例代码
  • sql server 存储过程使用 IF ELSE 的例子
  • SQL Server创建链接服务器的存储过程示例分享
  • sql server 存储过程中If Else的用法举例
  • sql2005 存储过程的简单例子
  • SQL Server存储过程入门学习
  • sql server 存储过程的异常处理
  • sql2005中创建CLR存储过程的方法详解
  • 在SQL Server 2005中创建CLR存储过程的详细介绍
  • 通过sql存储过程发送邮件的方法
  • sql存储过程详解
  • sql存储过程 求平均值和总和
  • Sql Server使用cursor处理重复数据过程详解
  • 一次SQL调优数据库性能问题后的过程(300W)
  • sql server存储过程分页示例
  • sql 判断数据库,表,存储过程等是否存在的代码
  • Oracle过程中执行动态SQL或DDL语句
  • java命名空间java.sql接口statement的类成员方法: executeupdate定义及介绍
  • 请问,这是什么错误!java.sql.SQLException: [Microsoft][ODBC SQL Server Driver][Named Pipes]??????? SQL Server?虽然分少,但一定给,只要您是前5名回复者中最好的以为!
  • java命名空间java.sql接口connection的类成员方法: nativesql定义及介绍
  • SQL查询分析工具 SQL Workbench/J
  • java命名空间java.sql接口preparedstatement的类成员方法: executeupdate定义及介绍
  • oracle导出sql语句的结果集和保存执行的sql语句(深入分析)
  • java命名空间java.sql接口rowid的类成员方法: getbytes定义及介绍
  • SQL Server统计SQL语句执行时间的脚本
  • java命名空间java.sql接口ref的类成员方法: getbasetypename定义及介绍
  • SQL客户端软件 PKLite SQL Client
  • java命名空间java.sql接口databasemetadata的类成员方法: getsqlkeywords定义及介绍


  • 站内导航:


    特别声明:169IT网站部分信息来自互联网,如果侵犯您的权利,请及时告知,本站将立即删除!

    ©2012-2021,,E-mail:www_#163.com(请将#改为@)

    浙ICP备11055608号-3