《Oracle PL/SQL开发指南》学习笔记28——源码调试——PL/SQL基础知识（第十一部分——函数和过程）

 

1. 存储函数的原型

CREATE OR REPLACE [{EDITIONABLE | NONEDITIONABLE}]
[schema.] FUNCTION function_name
( parameter [IN][OUT] [NOCOPY] {sql_data_type | plsql_data_type}
[,parameter [IN][OUT] [NOCOPY] {sql_data_type | plsql_data_type}]
[, ... ] )
RETURN {sql_data_type | plsql_data_type}
[ACCESSIBLE BY
( [{FUNCTION | PROCEDURE | PACKAGE | TYPE}] [schema.]unit_name)
[,[{FUNCTION | PROCEDURE | PACKAGE | TYPE}] [schema.]unit_name)]
[,... ]]])
[AUTHID [DEFINER | CURRENT_USER]]
[DETERMINISTIC | PARALLEL_ENABLED]
[PIPELINED]
[RESULT_CACHE [RELIES ON table_name]] IS
declaration_statements;
BEGIN
execution_statements
RETURN variable;
[EXCEPTION]
exception_handling_statements
END [function_name];

可以从伪表DUAL查询返回值为SQL数据类型（或PL/SQL类型）的函数：

SELECT some_function[(actual_parameter [, ...])]
FROM dual;

SELECT some_function[(formal_parameter => actual_parameter)]
FROM dual;

可以使用CALL语句将函数的返回值捕获到一个绑定变量中：

CALL some_function[(actual_parameter [, ...])]
INTO some_session_bind_variable;

示例：

-- Queries working with the function.

SQL> SELECT join_strings('Hello','World') FROM dual;

JOIN_STRINGS('HELLO','WORLD')
--------------------------------------------------------------------------------
Hello World.

SQL> SELECT join_strings('Hello','World') JOINED_STRING FROM dual;

JOINED_STRING
--------------------
Hello World.

SQL> VARIABLE session_var VARCHAR2(30);
SQL> CALL join_strings('Hello','World') INTO :session_var;

Call completed.

SQL> SELECT :session_var FROM dual;

:SESSION_VAR
--------------------
Hello World.

2. 存储过程的原型

CREATE OR REPLACE [{EDITIONABLE | NONEDITIONABLE}]
[schema.] PROCEDURE procedure_name
( parameter [IN][OUT] [NOCOPY] {sql_data_type | plsql_data_type}
[,parameter [IN][OUT] [NOCOPY] {sql_data_type | plsql_data_type}]
[, ... ] )
[ACCESSIBLE BY
( [{FUNCTION | PROCEDURE | PACKAGE | TYPE}] [schema.]unit_name)
[,[{FUNCTION | PROCEDURE | PACKAGE | TYPE}] [schema.]unit_name)]
[,... ]]])
[ AUTHID DEFINER | CURRENT_USER ] IS
declaration_statements
BEGIN
execution_statements;
[EXCEPTION]
exception_handling_statements
END procedure_name;

示例:

SQL> ed
Wrote file afiedt.buf

  1  -- Create or replace formatting procedure.
  2  CREATE OR REPLACE PROCEDURE format_string
  3  ( string_in IN OUT VARCHAR2 ) IS
  4  BEGIN
  5    string_in := '['||string_in||']';
  6* END;
SQL> /

Procedure created.

SQL> VARIABLE session_var VARCHAR2(30)
SQL> CALL format_string(:session_var);

Call completed.

SQL> CALL join_strings('Hello','World') INTO :session_var;

Call completed.

SQL> CALL format_string(:session_var);

Call completed.

SQL> SELECT :session_var FROM dual;

:SESSION_VAR
--------------------
[Hello World.]