Mega Code Archive
Using PLSQL to Create Functions to Access Elements
<![CDATA[
SQL>
SQL> CREATE OR REPLACE TYPE mem_type IS VARRAY(10) of VARCHAR2(15)
2 /
Type created.
SQL>
SQL> CREATE TABLE club (Name VARCHAR2(10),
2 Address VARCHAR2(20),
3 City VARCHAR2(20),
4 Phone VARCHAR2(8),
5 Members mem_type)
6 /
Table created.
SQL>
SQL> INSERT INTO club VALUES ('AL','111 First St.','Mobile',
2 '222-2222', mem_type('Brenda','Richard'));
1 row created.
SQL>
SQL> INSERT INTO club VALUES ('FL','222 Second St.','Orlando',
2 '333-3333', mem_type('Gen','John','Steph','JJ'));
1 row created.
SQL>
SQL> CREATE OR REPLACE FUNCTION vs (vlist club.members%type, sub integer)
2 RETURN VARCHAR2
3 IS
4 BEGIN
5 IF sub <= vlist.last THEN
6 RETURN vlist(sub);
7 END IF;
8 RETURN NULL;
9 END vs;
10 /
Function created.
SQL>
SQL> SELECT vs(members,2)
2 FROM club
3 /
VS(MEMBERS,2)
-----------------
Richard
John
SQL> SELECT DECODE(vs(members,3),null,'No members',vs(members,3))
2 FROM club
3 WHERE name IN ('FL', 'MD')
4 /
DECODE(VS(MEMBERS,3),NULL,'NOMEMBERS',VS(MEMBERS,3))
----------------------------------------------------
Steph
SQL>
SQL> drop table club;
Table dropped.
SQL> drop type mem_type;
Type dropped.
SQL>
]]>