CREATE PROCEDURE FILE_(
   IN V_FILE_ID bigint 
   )
BEGIN
    DECLARE GLOBAL TEMPORARY TABLE tmp(file_id bigint);     

    WITH cte(file_id)
    AS (
       SELECT file_id
       FROM files
       WHERE file_id = V_FILE_ID
       UNION ALL
       SELECT f.file_id
       FROM cte, files f
       WHERE cte.file_id = f.parent_file_id 
    )
    SELECT *
    INTO tmp
    FROM cte;
     
END@

CREATE PROCEDURE FILE_ADD
  (IN V_NAME VARCHAR( 255 ),
    IN V_DESC VARCHAR( 1024 ),
    IN V_PARENT_FILE_ID INTEGER,
    IN V_FILE_TYPE_ID INTEGER,
    IN V_CREATOR_ID INTEGER,
    IN V_IS_NEEDS_VERSION INTEGER,
    IN V_IS_NEEDS_LOG INTEGER,
    IN V_DATA BLOB( 1048576 ))
begin
 SET v_FILE_TYPE_ID = COALESCE(v_FILE_TYPE_ID,  0 );

 INSERT INTO Files
 (NAME, "DESC", PARENT_FILE_ID, FILE_TYPE_ID, IS_DELETED, CREATOR_ID, IS_NEEDS_VERSION, IS_NEEDS_LOG)
 VALUES(v_Name, v_DESC, v_PARENT_FILE_ID, v_FILE_TYPE_ID, default,v_CREATOR_ID, v_IS_NEEDS_VERSION, v_IS_NEEDS_LOG);

 IF v_FILE_TYPE_ID =  0  THEN
  INSERT INTO FILE_DATA(FILE_ID, "VERSION", "DATA")
  VALUES(IDENTITY_VAL_LOCAL(),  1 , v_data);
 END IF;

end@