本文共 1781 字，大约阅读时间需要 5 分钟。

?????????????????????????????SQL?????????????SQL> grant CREATE ANY DIRECTORY to user;??????????????????SQL> conn user/password;?????????????SQL> create or replace directory BLOBDIR as '/home/oracle/picDir';?????????????????????????????photo_dump??????create or replace procedure photo_dump(IDENTITYID in varchar2,                                   filename in varchar2) is???????????l_file UTL_FILE.FILE_TYPE;l_buffer RAW(32767);l_amount BINARY_INTEGER := 32767;l_pos INTEGER := 1;l_blob BLOB;l_blob_len INTEGER;???????begin    SELECT key_value    INTO l_blob    FROM blob_map_t    WHERE key_id = IDENTITYID;        l_blob_len := DBMS_LOB.GETLENGTH(l_blob);        l_file := UTL_FILE.FOPEN('BLOBDIR', filename, 'wb', 32767);        while l_pos < l_blob_len loop        DBMS_LOB.READ(l_blob, l_amount, l_pos, l_buffer);        UTL_FILE.PUT_RAW(l_file, l_buffer, TRUE);        l_pos := l_pos + l_amount;    end loop;        UTL_FILE.FCLOSE(l_file);        exception    when others then        dbms_output.put_line(SQLERRM);        if UTL_FILE.IS_OPEN(l_file) then            UTL_FILE.FCLOSE(l_file);        end if;        raise;end photo_dump;???????????????out_put_photo??????create or replace procedure out_put_photo is???????IDENTITYID varchar2(300);FILENAME varchar2(300);?????cursor cur is    select key_id, key_name    from blob_map_t;??????cur_result cur%rowtype;?????begin    if cur%isopen = false then        open cur;    end if;        loop        fetch cur        into cur_result;        exit when cur%notfound;                IDENTITYID := cur_result.key_id;        FILENAME := cur_result.key_name;                photo_dump(identityid => IDENTITYID, filename => FILENAME || '.jpg');    end loop;        close cur;end out_put_photo;??????????begin    out_put_photo();end;