Connected to: Oracle9i Enterprise Edition Release 9.0.1.3.0 - Production With the Partitioning option JServer Release 9.0.1.3.0 - Production SQL> connect scott/tiger@viper.exzilla.net Connected. SQL> SQL> CREATE TABLE myfriends( 2 fid number(2), 3 fname varchar2(30), 4 ftel varchar2(20), 5 fbirthday date, 6 gid varchar2(3) 7 ); Table created. SQL> SQL> CREATE TABLE mygroups( 2 gid number(2), 3 gname varchar2(30), 4 gdesc varchar2(50) 5 ); Table created. SQL> SQL> SQL> insert into myfriends values(1,'teddy','111111',to_date('10-10-1970','DD-MM-YYYY'),1); 1 row created. SQL> insert into myfriends values(2,'nicole','333333',to_date('10-10-1975','DD-MM-YYYY'),1); 1 row created. SQL> insert into myfriends values(3,'tyranno','333333',to_date('10-12-1969','DD-MM-YYYY,'),2); 1 row created. SQL> insert into myfriends values(4,'sefjl','444444',to_date('10-09-1970','DD-MM-YYYY'),2); 1 row created. SQL> insert into myfriends values(5,'fuju','555555',to_date('10-10-1970','DD-MM-YYYY'),1); 1 row created. SQL> insert into myfriends values(6,'kouprex','666666',to_date('10-10-1975','DD-MM-YYYY'),2); 1 row created. SQL> insert into myfriends values(7,'cupid','777777',to_date('10-12-1975','DD-MM-YYYY'),1); 1 row created. SQL> insert into myfriends values(8,'runma','888888',to_date('10-09-1975','DD-MM-YYYY'),1); 1 row created. SQL> insert into myfriends values(9,'spidix','888888',to_date('10-09-1970','DD-MM-YYYY'),2); 1 row created. SQL> SQL> insert into mygroups values(1,'SALES','Sales group'); 1 row created. SQL> insert into mygroups values(2,'SUPPORTS','Supports group'); 1 row created. SQL> SQL> commit; Commit complete. SQL> CREATE OR REPLACE PROCEDURE get_single_row_details(var_fname in varchar2, var_ftel out varchar2 ) 2 IS 3 var_temp_tel varchar2(20); 4 BEGIN 5 SELECT ftel 6 INTO var_temp_tel 7 FROM myfriends 8 WHERE fname = var_fname; 9 10 var_ftel := var_temp_tel; 11 return; 12 END; 13 / Procedure created. SQL> CREATE OR REPLACE FUNCTION count_friends (in_group_id IN mygroups.gid%type) 2 RETURN number 3 IS 4 var_temp_count NUMBER(2); 5 BEGIN 6 select count(*) into var_temp_count from myfriends where gid = in_group_id; 7 return var_temp_count; 8 END; 9 / Function created. SQL> SQL> COLUMN FID HEADING Number SQL> COLUMN FNAME HEADING Name SQL> COLUMN FTEL HEADING Telephone SQL> COLUMN FBIRTHDAY HEADING Birthday SQL> COLUMN GNAME HEADING Group SQL> SET AUTOPRINT ON SQL> SQL> -- Procedure SQL> VARIABLE RETVAL VARCHAR2(20) SQL> EXEC get_single_row_details('teddy',:RETVAL ); PL/SQL procedure successfully completed. RETVAL -------------------------------- 111111 SQL> SQL> -- Function SQL> variable myvar number SQL> exec :myvar := count_friends(1); PL/SQL procedure successfully completed. MYVAR ---------- 5 SQL> print :myvar MYVAR ---------- 5 SQL> SQL> select count_friends(1) from dual; COUNT_FRIENDS(1) ---------------- 5 SQL> SQL> CREATE OR REPLACE PACKAGE myfriends_data AS 2 TYPE MyFriendsRecTyp IS RECORD ( 3 fid myfriends.fid%type, 4 fname myfriends.fname%type, 5 ftel myfriends.ftel%type, 6 fbirthday myfriends.fbirthday%type, 7 gid myfriends.gid%type 8 ); 9 10 TYPE MyFriendsCurTyp IS REF CURSOR RETURN MyFriendsRecTyp; 11 12 PROCEDURE get_single_row_details(var_fname in varchar2,var_ftel out varchar2); 13 PROCEDURE get_multi_rows_details(var_gid IN NUMBER,cv_myfriends IN OUT MyFriendsCurTyp); 14 FUNCTION count_friends(in_group_id IN mygroups.gid%type) Return Number; 15 16 END myfriends_data; 17 / Package created. SQL> CREATE OR REPLACE PACKAGE BODY myfriends_data AS 2 PROCEDURE get_single_row_details(var_fname in varchar2, var_ftel out varchar2) 3 IS 4 var_temp_tel varchar2(20); 5 BEGIN 6 SELECT ftel 7 INTO var_temp_tel 8 FROM myfriends 9 WHERE fname = var_fname; 10 11 var_ftel := var_temp_tel; 12 return; 13 END get_single_row_details; 14 15 FUNCTION count_friends (in_group_id IN mygroups.gid%type) 16 RETURN number 17 IS 18 var_temp_count NUMBER(2); 19 BEGIN 20 select count(*) into var_temp_count from myfriends where gid = in_group_id; 21 return var_temp_count; 22 END count_friends; 23 24 PROCEDURE get_multi_rows_details(var_gid IN NUMBER, cv_myfriends IN OUT MyFriendsCurTyp) 25 IS 26 BEGIN 27 OPEN cv_myfriends FOR 28 SELECT fid, fname, ftel, fbirthday,mf.gid FROM myfriends mf, mygroups mg 29 WHERE mf.gid = var_gid AND mf.gid = mg.gid 30 ORDER BY fid; 31 END get_multi_rows_details; 32 33 END myfriends_data; 34 / Package body created. SQL> SQL> VARIABLE RETVAL VARCHAR2(20) SQL> VARIABLE cv REFCURSOR SQL> SQL> EXEC myfriends_data.get_single_row_details('teddy',:RETVAL ); PL/SQL procedure successfully completed. RETVAL -------------------------------------------------------------------------------- 111111 SQL> EXEC myfriends_data.get_multi_rows_details(1,:cv ); PL/SQL procedure successfully completed. Number Name Telephone Birthday GID ---------- ------------------------------ -------------------- --------- --- 1 teddy 111111 10-OCT-70 1 2 nicole 333333 10-OCT-75 1 5 fuju 555555 10-OCT-70 1 7 cupid 777777 10-DEC-75 1 8 runma 888888 10-SEP-75 1 SQL> SQL> -- Function SQL> variable myvar number SQL> exec :myvar := myfriends_data.count_friends(1); PL/SQL procedure successfully completed. MYVAR ---------- 5 SQL> print :myvar MYVAR ---------- 5 SQL> SQL> -- OR SQL> SQL> select myfriends_data.count_friends(1) from dual; MYFRIENDS_DATA.COUNT_FRIENDS(1) ------------------------------- 5 SQL>