-- Introduction
-- =============

-- Create table
-- insert data

-- Simple Store procedure & Function with single rows
-- Stored procedure
-- Stored function

-- Check the stored procedure status

-- Client 
-- 1.sql*plus
-- 2.php

-- Packing function & procedure to package and return multi row data
-- stored single row procedure
-- stored multi rows procedure
-- stored function

-- Client 
-- 1.sql*plus
-- 2.php

-- REF: 
-- 1. " http://otn.oracle.com/docs/products/oracle9i/doc_library/release2/appdev.920/a96624/06_ora.htm#1510 " 
-- 2. " http://www.php.net/manual/en/function.ocinewcursor.php "
-- 3. " http://www.php.net "

CREATE TABLE myfriends(
fid number(2),
fname varchar2(30), 
ftel varchar2(20), 
fbirthday date,
gid varchar2(3)
);

CREATE TABLE mygroups(
gid number(2),
gname varchar2(30),
gdesc varchar2(50)
);


insert into myfriends values(1,'teddy','111111',to_date('10-10-1970','DD-MM-YYYY'),1);
insert into myfriends values(2,'nicole','333333',to_date('10-10-1975','DD-MM-YYYY'),1); 
insert into myfriends values(3,'tyranno','333333',to_date('10-12-1969','DD-MM-YYYY,'),2); 
insert into myfriends values(4,'sefjl','444444',to_date('10-09-1970','DD-MM-YYYY'),2); 
insert into myfriends values(5,'fuju','555555',to_date('10-10-1970','DD-MM-YYYY'),1); 
insert into myfriends values(6,'kouprex','666666',to_date('10-10-1975','DD-MM-YYYY'),2); 
insert into myfriends values(7,'cupid','777777',to_date('10-12-1975','DD-MM-YYYY'),1); 
insert into myfriends values(8,'runma','888888',to_date('10-09-1975','DD-MM-YYYY'),1); 
insert into myfriends values(9,'spidix','888888',to_date('10-09-1970','DD-MM-YYYY'),2); 

insert into mygroups values(1,'SALES','Sales group');
insert into mygroups values(2,'SUPPORTS','Supports group');

commit;


CREATE	OR REPLACE PROCEDURE get_single_row_details(var_fname in varchar2, var_ftel out varchar2) 
IS 
var_temp_tel varchar2(20); 
BEGIN 
	SELECT ftel 
	INTO var_temp_tel 
	FROM myfriends 
	WHERE fname = var_fname; 

	var_ftel := var_temp_tel; 
	return; 
END;
/
CREATE OR REPLACE FUNCTION count_friends (in_group_id IN mygroups.gid%type) 
RETURN number
IS 
var_temp_count NUMBER(2); 
BEGIN 
	select count(*) into var_temp_count from myfriends where gid = in_group_id; 
	return var_temp_count;
END;
/
-- Checking the function and procedure status
-- ==========================================
select object_name,status from user_objects where object_name like '%GET_%' or object_name like '%COUNT_%'; 

-- SQL * PLUS (Single Row )
-- ==========
COLUMN FID HEADING Number 
COLUMN FNAME HEADING Name 
COLUMN FTEL HEADING Telephone 
COLUMN FBIRTHDAY HEADING Birthday 
COLUMN GNAME HEADING Group
SET AUTOPRINT ON 

-- Procedure
-- =========
VARIABLE  RETVAL VARCHAR2(20)
EXEC get_single_row_details('teddy',:RETVAL ); 

-- Function
-- ========
variable myvar number
exec :myvar := count_friends(1);
print :myvar

-- OR

select  count_friends(1) from dual;

-- PHP program /oci8/sf-single.php
-- PHP program /oci8/sp-single.php
-- =======================================

-- Create a package for hodling function , procedure ( single and multi rows )

CREATE OR REPLACE PACKAGE myfriends_data AS 
  TYPE MyFriendsRecTyp IS RECORD ( 
      fid    myfriends.fid%type, 
      fname  myfriends.fname%type, 
      ftel myfriends.ftel%type, 
      fbirthday myfriends.fbirthday%type, 
      gid  myfriends.gid%type
   ); 

  TYPE MyFriendsCurTyp IS REF CURSOR RETURN MyFriendsRecTyp; 

  PROCEDURE get_single_row_details(var_fname in varchar2,var_ftel out varchar2);
  PROCEDURE get_multi_rows_details(var_gid IN NUMBER,cv_myfriends IN OUT MyFriendsCurTyp);
  FUNCTION  count_friends(in_group_id IN mygroups.gid%type) Return Number;

END myfriends_data; 

 
CREATE OR REPLACE PACKAGE  BODY myfriends_data AS 
PROCEDURE get_single_row_details(var_fname in varchar2, var_ftel out varchar2) 
IS 
var_temp_tel varchar2(20); 
BEGIN 
	SELECT ftel 
	INTO var_temp_tel 
	FROM myfriends 
	WHERE fname = var_fname; 

	var_ftel := var_temp_tel; 
	return; 
END get_single_row_details;

FUNCTION count_friends (in_group_id IN mygroups.gid%type) 
RETURN number
IS 
var_temp_count NUMBER(2); 
BEGIN 
	select count(*) into var_temp_count from myfriends where gid = in_group_id; 
	return var_temp_count;
END count_friends;

PROCEDURE get_multi_rows_details(var_gid IN NUMBER, cv_myfriends IN OUT MyFriendsCurTyp) 
IS 
BEGIN 
      OPEN cv_myfriends FOR 
         SELECT fid, fname, ftel, fbirthday,mf.gid FROM myfriends mf, mygroups mg 
            WHERE mf.gid = var_gid AND mf.gid = mg.gid 
            ORDER BY fid; 
END get_multi_rows_details; 

END myfriends_data;

-- End creating package

-- SQL * PLUS (package for Single Row and multi row)
-- ==========
COLUMN FID HEADING Number 
COLUMN FNAME HEADING Name 
COLUMN FTEL HEADING Telephone 
COLUMN FBIRTHDAY HEADING Birthday 
COLUMN GNAME HEADING Group
SET AUTOPRINT ON 

-- Procedure
-- =========
VARIABLE  RETVAL VARCHAR2(20)
VARIABLE cv REFCURSOR 

EXEC myfriends_data.get_single_row_details('teddy',:RETVAL ); 
EXEC myfriends_data.get_multi_rows_details(1,:cv ); 

-- Function
-- ========
variable myvar number
exec :myvar := myfriends_data.count_friends(1);
print :myvar

-- OR

select  myfriends_data.count_friends(1) from dual;

-- =============END ==========================