lob_schema.sql

oracle 11 源代码

-- This script does the following:
--   1. Creates lob_user
--   2. Creates the database tables
--   3. Populates the database tables with sample data
--   4. Creates the PL/SQL code

-- attempt to drop the user (this will generate an error
-- if the user does not yet exist; do not worry about this
-- error); this statement is included so that you do not have
-- to manually run the DROP before recreating the schema
DROP USER lob_user CASCADE;

-- create lob_user
CREATE USER lob_user IDENTIFIED BY lob_password;

-- allow the user to connect, create database objects and
-- create directory objects (for the BFILEs)
GRANT connect, resource, create any directory TO lob_user;

-- connect as lob_user
CONNECT lob_user/lob_password;

-- create the tables
CREATE TABLE clob_content (
  id          INTEGER PRIMARY KEY,
  clob_column CLOB NOT NULL
);

CREATE TABLE blob_content (
  id          INTEGER PRIMARY KEY,
  blob_column BLOB NOT NULL
);

CREATE TABLE bfile_content (
  id           INTEGER PRIMARY KEY,
  bfile_column BFILE NOT NULL
);

CREATE TABLE long_content (
  id          INTEGER PRIMARY KEY,
  long_column LONG NOT NULL
);

CREATE TABLE long_raw_content (
  id              INTEGER PRIMARY KEY,
  long_raw_column LONG RAW NOT NULL
);

-- create the BFILE directory
CREATE DIRECTORY SAMPLE_FILES_DIR AS 'C:\sample_files';

-- populate tables with sample data
INSERT INTO clob_content (
  id, clob_column
) VALUES (
  1, TO_CLOB('Creeps in this petty pace')
);

INSERT INTO clob_content (
  id, clob_column
) VALUES (
  2, TO_CLOB(' from day to day')
);

INSERT INTO blob_content (
  id, blob_column
) VALUES (
  1, TO_BLOB('100111010101011111')
);

INSERT INTO blob_content (
  id, blob_column
) VALUES (
  2, TO_BLOB('A0FFB71CF90DE')
);

INSERT INTO bfile_content (
  id, bfile_column
) VALUES (
  1, BFILENAME('SAMPLE_FILES_DIR', 'textContent.txt')
);

INSERT INTO bfile_content (
  id, bfile_column
) VALUES (
  2, BFILENAME('SAMPLE_FILES_DIR', 'binaryContent.doc')
);

INSERT INTO long_content (
  id, long_column
) VALUES (
  1, 'Creeps in this petty pace'
);

INSERT INTO long_content (
  id, long_column
) VALUES (
  2, ' from day to day'
);

INSERT INTO long_raw_content (
  id, long_raw_column
) VALUES (
  1, '100111010101011111'
);

INSERT INTO long_raw_content (
  id, long_raw_column
) VALUES (
  2, 'A0FFB71CF90DE'
);

COMMIT;

-- create the PL/SQL code
CREATE PROCEDURE get_clob_locator(
  p_clob IN OUT CLOB,
  p_id   IN INTEGER
) AS
BEGIN
  -- get the LOB locator and store it in p_clob
  SELECT clob_column
  INTO p_clob
  FROM clob_content
  WHERE id = p_id;
END get_clob_locator;
/

CREATE PROCEDURE get_blob_locator(
  p_blob IN OUT BLOB,
  p_id   IN INTEGER
) AS
BEGIN
  -- get the LOB locator and store it in p_blob
  SELECT blob_column
  INTO p_blob
  FROM blob_content
  WHERE id = p_id;
END get_blob_locator;
/

CREATE PROCEDURE read_clob_example(
  p_id IN INTEGER
) AS
  v_clob CLOB;
  v_offset INTEGER := 1;
  v_amount INTEGER := 50;
  v_char_buffer VARCHAR2(50);
BEGIN
  -- get the LOB locator and store it in v_clob
  get_clob_locator(v_clob, p_id);

  -- read the contents of v_clob into v_char_buffer, starting at
  -- the v_offset position and read a total of v_amount characters
  DBMS_LOB.READ(v_clob, v_amount, v_offset, v_char_buffer);

  -- display the contents of v_char_buffer
  DBMS_OUTPUT.PUT_LINE('v_char_buffer = ' || v_char_buffer);
  DBMS_OUTPUT.PUT_LINE('v_amount = ' || v_amount);
END read_clob_example;
/

CREATE PROCEDURE read_blob_example(
  p_id IN INTEGER
) AS
  v_blob BLOB;
  v_offset INTEGER := 1;
  v_amount INTEGER := 25;
  v_binary_buffer RAW(25);
BEGIN
  -- get the LOB locator and store it in v_blob
  get_blob_locator(v_blob, p_id);

  -- read the contents of v_blob into v_binary_buffer, starting at
  -- the v_offset position and read a total of v_amount bytes
  DBMS_LOB.READ(v_blob, v_amount, v_offset, v_binary_buffer);

  -- display the contents of v_binary_buffer
  DBMS_OUTPUT.PUT_LINE('v_binary_buffer = ' || v_binary_buffer);
  DBMS_OUTPUT.PUT_LINE('v_amount = ' || v_amount);
END read_blob_example;
/

CREATE PROCEDURE write_example(
  p_id IN INTEGER
) AS
  v_clob CLOB;
  v_offset INTEGER := 7;
  v_amount INTEGER := 6;
  v_char_buffer VARCHAR2(10) := 'pretty';
BEGIN
  -- get the LOB locator into v_clob for update (for update
  -- because the LOB is written to using WRITE() later)
  SELECT clob_column
  INTO v_clob
  FROM clob_content
  WHERE id = p_id
  FOR UPDATE;

  -- read and display the contents of the CLOB
  read_clob_example(p_id);

  -- write the characters in v_char_buffer to v_clob, starting 
  -- at the v_offset position and write a total of v_amount characters
  DBMS_LOB.WRITE(v_clob, v_amount, v_offset, v_char_buffer);

  -- read and display the contents of the CLOB
  -- and then rollback the write
  read_clob_example(p_id);
  ROLLBACK;
END write_example;
/

CREATE PROCEDURE append_example AS
  v_src_clob CLOB;
  v_dest_clob CLOB;
BEGIN
  -- get the LOB locator for the CLOB in row #2 of
  -- the clob_content table into v_src_clob
  get_clob_locator(v_src_clob, 2);

  -- get the LOB locator for the CLOB in row #1 of
  -- the clob_content table into v_dest_clob for update
  -- (for update because the CLOB will be added to using
  -- APPEND() later)
  SELECT clob_column
  INTO v_dest_clob
  FROM clob_content
  WHERE id = 1
  FOR UPDATE;

  -- read and display the contents of CLOB #1
  read_clob_example(1);

  -- use APPEND() to copy the contents of v_src_clob to v_dest_clob
  DBMS_LOB.APPEND(v_dest_clob, v_src_clob);

  -- read and display the contents of CLOB #1
  -- and then rollback the change
  read_clob_example(1);
  ROLLBACK;
END append_example;
/

CREATE PROCEDURE compare_example AS
  v_clob1 CLOB;
  v_clob2 CLOB;
  v_return INTEGER;
BEGIN
  -- get the LOB locators
  get_clob_locator(v_clob1, 1);
  get_clob_locator(v_clob2, 2);

  -- compare v_clob1 with v_clob2 (COMPARE() returns 1
  -- because the contents of v_clob1 and v_clob2 are different)
  DBMS_OUTPUT.PUT_LINE('Comparing v_clob1 with v_clob2');
  v_return := DBMS_LOB.COMPARE(v_clob1, v_clob2);
  DBMS_OUTPUT.PUT_LINE('v_return = ' || v_return);

  -- compare v_clob1 with v_clob1 (COMPARE() returns 0
  -- because the contents are the same)
  DBMS_OUTPUT.PUT_LINE('Comparing v_clob1 with v_clob1');
  v_return := DBMS_LOB.COMPARE(v_clob1, v_clob1);
  DBMS_OUTPUT.PUT_LINE('v_return = ' || v_return);
END compare_example;
/

CREATE PROCEDURE copy_example AS
  v_src_clob CLOB;
  v_dest_clob CLOB;
  v_src_offset INTEGER := 1;
  v_dest_offset INTEGER := 7;
  v_amount INTEGER := 5;
BEGIN
  -- get the LOB locator for the CLOB in row #2 of
  -- the clob_content table into v_dest_clob
  get_clob_locator(v_src_clob, 2);

  -- get the LOB locator for the CLOB in row #1 of
  -- the clob_content table into v_dest_clob for update
  -- (for update because the CLOB will be added to using
  -- COPY() later)
  SELECT clob_column
  INTO v_dest_clob
  FROM clob_content
  WHERE id = 1
  FOR UPDATE;

  -- read and display the contents of CLOB #1
  read_clob_example(1);

  -- copy characters to v_dest_clob from v_src_clob using COPY(),
  -- starting at the offsets specified by v_dest_offset and
  -- v_src_offset for a total of v_amount characters
  DBMS_LOB.COPY(
    v_dest_clob, v_src_clob,
    v_amount, v_dest_offset, v_src_offset
  );

  -- read and display the contents of CLOB #1
  -- and then rollback the change
  read_clob_example(1);
  ROLLBACK;
END copy_example;
/

CREATE PROCEDURE temporary_lob_example AS
  v_clob CLOB;
  v_amount INTEGER;
  v_offset INTEGER := 1;
  v_char_buffer VARCHAR2(17) := 'Juliet is the sun';
BEGIN
  -- use CREATETEMPORARY() to create a temporary CLOB named v_clob
  DBMS_LOB.CREATETEMPORARY(v_clob, TRUE);

  -- use WRITE() to write the contents of v_char_buffer to v_clob
  v_amount := LENGTH(v_char_buffer);
  DBMS_LOB.WRITE(v_clob, v_amount, v_offset, v_char_buffer);

  -- use ISTEMPORARY() to check if v_clob is temporary
  IF (DBMS_LOB.ISTEMPORARY(v_clob) = 1) THEN
    DBMS_OUTPUT.PUT_LINE('v_clob is temporary');
  END IF;

  -- use READ() to read the contents of v_clob into v_char_buffer
  DBMS_LOB.READ(
    v_clob, v_amount, v_offset, v_char_buffer
  );
  DBMS_OUTPUT.PUT_LINE('v_char_buffer = ' || v_char_buffer);

  -- use FREETEMPORARY() to free v_clob
  DBMS_LOB.FREETEMPORARY(v_clob);
END temporary_lob_example;
/

CREATE PROCEDURE erase_example AS
  v_clob CLOB;
  v_offset INTEGER := 2;
  v_amount INTEGER := 5;
BEGIN
  -- get the LOB locator for the CLOB in row #1 of
  -- the clob_content table into v_dest_clob for update
  -- (for update because the CLOB will be erased using
  -- ERASE() later)
  SELECT clob_column
  INTO v_clob
  FROM clob_content
  WHERE id = 1
  FOR UPDATE;

  -- read and display the contents of CLOB #1
  read_clob_example(1);

  -- use ERASE() to erase a total of v_amount characters
  -- from v_clob, starting at v_offset
  DBMS_LOB.ERASE(v_clob, v_amount, v_offset);

  -- read and display the contents of CLOB #1
  -- and then rollback the change
  read_clob_example(1);
  ROLLBACK;
END erase_example;
/

CREATE PROCEDURE instr_example AS
  v_clob CLOB;
  v_char_buffer VARCHAR2(50) := 'It is the east and Juliet is the sun';
  v_pattern VARCHAR2(5);
  v_offset INTEGER := 1;
  v_amount INTEGER;
  v_occurrence INTEGER;