PROCEDURE and FUNCTION

最新推荐文章于 2022-03-11 18:51:49 发布

原创最新推荐文章于 2022-03-11 18:51:49 发布 · 461 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#function #parameters #exception #list

Oracle_Developer 专栏收录该内容

5 篇文章

订阅专栏

本文介绍如何使用PL/SQL创建过程和函数，并通过具体示例展示了如何定义与实现这些程序单元，包括输入输出参数的使用。

有阵子没用了,都给忘了.温故知新!

/* In parameter_list parameters are separated by comma(,) */
CREATE OR REPLACE PROCEDURE PROCEDURE_NAME(parameter_list)
/*
BOOLEAN: return type (varchar2,number)
'AS' can be replace by 'IS'. The same as their role.
*/
RETURN BOOLEAN AS
/*Declarative section is here*/
BEGIN
/*Executable section is here*/
EXCEPTION
/*Exception section is here*/
END PROCEDURE_NAME;

/* In parameter_list parameters are separated by comma(,) */
CREATE OR REPLACE FUNCTION FUNCTION_NAME(parameter_list)
/*
BOOLEAN: return type (varchar2,number)
'AS' can be replace by 'IS'. The same as their role.
*/
RETURN BOOLEAN AS
/*Declarative section is here*/
BEGIN
/*Executable section is here*/
RETURN expression; -- Necessary for FUNTION
EXCEPTION
/*Exception section is here*/
END FUNCTION_NAME;

parameter_list_argument:
TYPE: IN ,OUT , IN OUT
/*
1. 与OUT或者IN OUT模式的形式参数相关联的实际参数必须是一个变量,而不能是一个常量或表达式
2. IN模式的形式参数不能被修改
*/

CREATE OR REPLACE FUNCTION GET_ROLES_BY_RES(P_RES_ID     IN VARCHAR2)
RETURN VARCHAR2 AS
ROLE_NAMES VARCHAR2(10000);
CURSOR ROLE_RESULT IS
      SELECT ROLE_NAME
        FROM IES_ROLE IR, Ies_Privs IP
       WHERE IR.ROLE_ID = IP.Role_Id
         AND IP.RES_ID = P_RES_ID
       ORDER BY IR.ROLE_ID;
BEGIN
ROLE_NAMES := '';
    --OPEN ROLE_RESULT;
    FOR REC IN ROLE_RESULT LOOP
      ROLE_NAMES := REC.ROLE_NAME || ';' || ROLE_NAMES;
    END LOOP;
    --CLOSE ROLE_RESULT;
RETURN ROLE_NAMES;
END GET_ROLES_BY_RES;