Oracle数据库-过程、函数与包

Create Procedure语法

CREATE [OR REPLACE] PROCEDURE [schema.] procedure
[
(argument [IN|OUT|IN OUT] [NOCOPY] datatype 
[,argument [IN|OUT|IN OUT] [NOCOPY] datatype]...)
]
[AUTHID{CURRENT_USER|DEFINER}]
{IS|AS}
{pl/sql_subprogram_body | 
LANGUAGE {JAVA NAME 'string' | C [NAME name] LIBRARY lib_name}
};

　　示例，创建一个NEW_BOOK过程：

create or replace procedure new_book (aTitle in varchar2,
                                      aPublisher in varchar2,
                                     aCategoryName in varchar2)
as
begin
    insert into bookself (title,publisher,category_name,rating)
        values (aTitle,aPublisher,aCategoryName,null);
    delete from book_order
        where title = aTitle;
end;
/

　　在这个存储过程中，实现了向bookself表中插入记录、从book_order表中删除记录两个DML操作。在这个例子中，通过使用存储过程，确保了新书添加到书架时自动从待订购书籍列表中移除。
　　如果一个存储过程已经存在，可以通过CREATE OR REPLACE PROCEDURE命令来替换它，好处是EXECUTE权限将被保持。
　　存储过程中，输入/输出参数必须使用 IN/OUT/IN OUT 限定符显式指定，默认为IN。
　　AUTHID指身份验证的类型，即定义者权限（默认）或调用者权限。
　　NOCOPY关键字告诉Oracle将变量值尽快传给用户。
　　在默认条件下，过程由一个用 PL/SQL 编写的代码块组成（即调用过程时将执行的代码）。在上述示例中，代码块是

begin
    insert into bookself (title,publisher,category_name,rating)
        values (aTitle,aPublisher,aCategoryName,null);
    delete from book_order
        where title = aTitle;
end;

　　代码块可以为任意DML语句，不能为DDL语句。
　　LANGUAGE表示用来编写代码的语言，例如可以使用Java代码。
　　实际上存储过程本身比较简单，关键是对代码块中的DML语言及其逻辑的掌握。

CREATE FUNCTION语法

　　CREATE FUNCTION的语法比CREATE PROCEDURE的语法更复杂，大致如下：
　　

CREATE [OR REPLACE] FUNCTION [schema.] function
[(argument [IN | OUT | IN OUT] [NOCOPY] datatype
 [,argument [IN | OUT | IN OUT] [NOCOPY] datatype]...
)]
RETURN datatype
[
 {invoker_rights_clause | DETERMINISTIC | parallel_enable_clause}
 [invoker_rights_clause | DETERMINISTIC | parallel_enable_clause]...
]
{
    {AGGREGATE | PIPELINED} USING [schema.] implementation_type | 
    [PIPELINED] {IS | AS} 
    {pl/sql_function_body | call_spec}
};

　　其中，RETURN关键字指定函数的返回值数据类型，每个函数必须有一条RETURN子句，因为函数必须给调用环境返回一个值。
　　下面的示例展示了一个名为 OVERDUE_CHARGES的函数，它根据对BOOKSELF_CHECKOUT表的计算结果，返回某人的过期图书滞纳金。输入为姓名，输出为结算金额。

create or replace function overdue_charges (aName in varchar2)
return number
is
owed_amount number(10,2);
begin
    select sum (((returned_date - checkout_date) - 14) * 0.2)
    into owed_amount
        from bookself_checkout
        where name = aName;
    return(owed_amount);
end;
/

在过程中引用远程表

　　通过过程中的数据库链接，可以访问远程表。只需将表名替换为“表名@数据库链接”即可。可以为远程表创建一个本地同义词，如：
　　

create synonym bookshelf_remote for bookshelf@remote_connect;