Mega Code Archive
A procedure that uses EXISTS and LAST
<![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 PROCEDURE vs3 (sub integer)
2 IS
3 CURSOR vcur IS
4 SELECT name, members FROM club;
5 x varchar2(30);
6 BEGIN
7 FOR j IN vcur LOOP
8 x := j.name||' No Members';
9 IF j.members.exists(1) THEN -- exists
10 IF sub <= j.members.last THEN -- last
11 x := j.name||' '||j.members(sub);
12 -- access array element
13 ELSE
14 x := j.name||' Less than '||sub||' members';
15 END IF;
16 END IF;
17 dbms_output.put_line(x);
18 END LOOP;
19 END vs3;
20 /
Procedure created.
SQL>
SQL> exec vs3(1);
AL Brenda
FL Gen
PL/SQL procedure successfully completed.
SQL> exec vs3(2);
AL Richard
FL John
PL/SQL procedure successfully completed.
SQL> exec vs3(3);
AL Less than 3 members
FL Steph
PL/SQL procedure successfully completed.
SQL> exec vs3(4);
AL Less than 4 members
FL JJ
PL/SQL procedure successfully completed.
SQL>
SQL> drop table club;
Table dropped.
SQL> drop type mem_type;
Type dropped.
SQL>
]]>