第3回：PHP＆Oracleの利点と欠点 (3/3)

--画像用のテーブル

CREATE TABLE DB_IMAGE_LIBRARY(
  IMG_ID   NUMBER,
  CAT_ID   VARCHAR2(2 BYTE),
  FILE_NAME   VARCHAR2(50 BYTE),
  SIZE      NUMBER,
  MIME_TYPE   VARCHAR2(50 BYTE),
  IMG_BIN BLOB,
  WIDTH   NUMBER,
  HEIGHT   NUMBER,
  SHORT_DESC   VARCHAR2(255 BYTE),
  DATE_ADDED   DATE DEFAULT SYSDATE,
  DATE_MODIFIED   DATE DEFAULT SYSDATE,
  C_OPERATOR_ID   VARCHAR2(10 BYTE),
STORAGE (initial 50K next 50K pctincrease 0)
TABLESPACE DB_TABLE_DATA
LOB (IMG_BIN) STORE AS (TABLESPACE IMAGE_DATA
   STORAGE(initial 100K next 100K pctincrease 0));


--画像データを取得するためのパッケージ

CREATE OR REPLACE PACKAGE PKG_IMAGE_MGMT AS
    TYPE my_ref_cursor IS REF CURSOR;

   PROCEDURE SP_GET_IMAGE   (img_id IN NUMBER,
      my_cursor OUT my_ref_cursor);
END;
/

CREATE OR REPLACE PACKAGE BODY PKG_IMAGE_MGMT AS
   TYPE my_ref_cursor IS REF CURSOR;
   CREATE OR REPLACE PROCEDURE SP_GET_IMAGE(
         img_id IN NUMBER,
         my_cursor OUT my_ref_cursor) AS
   BEGIN
   OPEN my_cursor FOR
      SELECT   
         IMG_BIN,
         MIME_TYPE,
         FILE_NAME,
         WIDTH,
         HEIGHT,
         SHORT_DESC
      FROM      web.DB_IMAGE_LIBRARY
      WHERE      IMG_ID = img_id;
   END SP_GET_IMAGE;
END;
/