有阵子没用了,都给忘了.温故知新!
/* 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 FUNTIONEXCEPTION/*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;
