This section describes how to manipulate data in a table containing abstract data types.
When you use a plug-in, you can create a UAP by specifying the facility provided by the plug-in. This makes it possible to manipulate multimedia data such as documents and spatial data.
The following examples use the HiRDB Text Search Plug-in.
An example of using a Boolean predicate for data retrieval is explained as follows.
SELECT medicine-ID FROM MEDICINE_MANAGEMENT_TABLE
WHERE contains(operation-manual,'attached-document-data[efficacy{"headache"}]') IS TRUE
An example of data updating is explained as follows.
UPDATE MEDICINE_MANAGEMENT_TABLE SET operation-manual = SGMLTEXT(:sgml)
WHERE medicine-ID = 'medicine-2'
EXEC SQL BEGIN DECLARE SECTION; 1
SQL TYPE IS BLOB(300K)sgml; 1
EXEC SQL END DECLARE SECTION; 1
strcpy(sgml.sgml_data,char_ptr_pointing_to_a_sgml_text); 2
sgml.sgml_length =
strlen(char_ptr_pointing_to_a_sgml_text); 3
An example of data deletion is explained as follows:
DELETE FROM MEDICINE_MANAGEMENT_TABLE
WHERE medicine-ID = 'medicine-2'
An example of data insertion is explained as follows.
INSERT INTO MEDICINE_MANAGEMENT_TABLE (medicine-ID,operation-manual)
VALUES('medicine-25',SGMLTEXT(:sgml))
EXEC SQL BEGIN DECLARE SECTION; 1
SQL TYPE IS BLOB(300K)sgml; 1
EXEC SQL END DECLARE SECTION; 1
strcpy(sgml.sgml_data,char_ptr_pointing_to_a_sgml_text); 2
sgml.sgml_length =
strlen(char_ptr_pointing_to_a_sgml_text); 3
Creating a UAP by specifying the functions provided by HiRDB XML Extension allows you to manipulate XML data.
Examples of XML data retrieval follow.
SELECT book-ID, XMLSERIALIZE(BOOK INFORMATION AS VARCHAR(32000))
FROM Book Control Table
WHERE book-ID = 126513592
SELECT book-ID,
XMLSERIALIZE(
XMLQUERY('/Book Information/Title'
PASSING BY VALUE BOOK INFORMATION
RETURNING SEQUENCE EMPTY ON EMPTY)
AS VARCHAR(32000))
FROM Book Control Table
WHERE XMLEXISTS('/Book Information[category="database"]'
PASSING BY VALUE BOOK Information)
SELECT XMLSERIALIZE(
XMLAGG(
XMLQUERY('/Book Information/Title'
PASSING BY VALUEBOOK INFORMATION
RETURNING SEQUENCE EMPTY ON EMPTY)
)
AS VARCHAR(32000))
FROM Book Control Table
WHERE XMLEXISTS('/Book Information[category="database"]'
PASSING BY VALUE BOOK INFORMATION)
SELECT
XMLSERIALIZE(
XMLQUERY(
'$BOOKS/Book information [category=$BOOKS/book information[title="Complete SQL Guide"]/category]'
PASSING BY VALUE XMLAGG(BOOK INFORMATION) AS BOOKS
RETURNING SEQUENCE EMPTY ON EMPTY)
AS VARCHAR(32000))
FROM Book Control Table
A routine or a component specification is used to manipulate data of a table containing a user-defined abstract data type. A component specification is used to manipulate the attribute of a column that comprises an abstract data type. The following examples manipulate the data of a table containing a user-defined abstract data type.
An example of retrieving a column of a table containing a user-defined abstract data type is explained as follows.
SELECT employee-no
FROM STAFF_TABLE
WHERE service-years (employee)>=20
An example of updating a column of a table containing a user-defined abstract data type is explained as follows.
UPDATE STAFF_TABLE
SET employee.position ='CHIEF'
WHERE employee_no = '900123'
An example of deleting a column of a table containing a user-defined abstract data type is explained as follows:
DELETE FROM STAFF_TABLE
WHERE employee..position='general'
An example of insertion of data into a table containing a user-defined abstract data type is explained as follows.
INSERT INTO STAFF_TABLE
VALUES ('990070',t_employee('Mary Moore',
'F',
'GENERAL',
'1999-04-01',
:xidphoto AS BLOB,
140000
))