The following example shows an SQL procedure used to split a CLOB in half and then insert the halves into two separate tables.
First the table definitions:
CREATE TABLE tabc1 ( a1 INTEGER, b1 CLOB); CREATE TABLE tabc2 ( a2 INTEGER, b2 CLOB);
The following SQL code defines the procedure:
CREATE PROCEDURE clobsplitter(IN a1 INTEGER, IN a2 INTEGER, INOUT b CLOB ) BEGIN DECLARE localclob CLOB; DECLARE len INTEGER; SET len = CHARACTERS(b); SET localclob = SUBSTR(b, 1, len/2); INSERT tabc1 (:a1, localclob); INSERT tabc2 (:a2, SUBSTR(b, len/2 + 1)); SET b = localclob || SUBSTR(b, len/2 + 1); END;
The stages in the process followed by this procedure are as follows:
- The first half of CLOB b is assigned to local variable named localclob.
- The contents of CLOB are inserted into table tabc1.
- The second half of CLOB b is inserted into table tabc2 without using a local LOB variable.
- Local variable localclob is concatenated with the second half of b and assigned to b.
- The contents of b are returned to the application.
If clobsplitter is called by another procedure, then b is passed to the other procedure.