PROCEDURE and FUNCTION

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

 

/* 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;