spool ora_ex3
set echo on
set pagesize 66
set linesize 80
/* NANNETTE THACKER */

/* this file demonstrates how to create procedures and a sequence generator */


/* create the customer table -- nkt 3/27/97 */
create table customer
(       customer_number       number,
        customer_name         varchar2(25) constraint unique_custname
UNIQUE,
        street_name           varchar2(25),
        city                  varchar2(25),
        state                 char(2),
        zip                   varchar2(10),
        tax_rate              number,
        constraint pk_customer_number PRIMARY KEY(customer_number)
                using index TABLESPACE users
)
        STORAGE(INITIAL 1K
                NEXT 1K
                PCTINCREASE 0)
        TABLESPACE USERS;

comment on table customer is
'create 3/27/97 by NKT. Primary key is customer_number.' ;


comment on column customer.customer_name is
'Unique field, which is weird, since there could be a lot of John
Smiths.' ;


/* create a sequence generator for the new customer_number field */
create sequence new_number
        increment by 1
        start with 1
        maxvalue 9999
        nocycle
        nocache
        order ;

/* ---------------------------------------------------------------*/
/* ---------------------------------------------------------------*/
/* ---------------------------------------------------------------*/
/* ---------------------------------------------------------------*/
/* ---------------------------------------------------------------*/

/* create a procedure named add/cust that allows a customer to be added.
Input the information given with the next customer_number, and a
tax_rate
calculated as follows:
        state in (CA,WA,NV) tax_rate = 2.5
        state in (NJ,FL,OR) tax_rate = 3.5
        all others tax_rate = 2.0
Handle all errors.
Input is: customer_name, street_name, city, state, zip

*/

create or replace procedure add_cust
 (gcustname in varchar2,
 gstreetname in varchar2,
 gcity in varchar2,
 gstate in char,
 gzip in varchar2)
as
new_custnum customer.customer_number%type ;
cursor newcustnum is
	select new_number.nextval from dual ;
BEGIN
	OPEN newcustnum;
	fetch newcustnum into new_custnum ;
	close newcustnum ;
	IF gstate = 'CA' or gstate = 'WA' or gstate = 'NV' THEN
	  INSERT INTO customer VALUES
		(new_custnum,
gcustname,gstreetname,gcity,gstate,gzip,2.5);
	ELSIF gstate = 'NJ' OR gstate = 'FL' or gstate = 'OR' THEN
	  INSERT INTO customer VALUES
		(new_custnum,
gcustname,gstreetname,gcity,gstate,gzip,3.5);
	ELSE 
	  INSERT INTO customer VALUES
		(new_custnum,
gcustname,gstreetname,gcity,gstate,gzip,2.0);
	END IF ;
	EXCEPTION
		WHEN DUP_VAL_ON_INDEX THEN
			raise_application_error(-20025,
				'You tried to enter a name that has
already been entered.');
		WHEN CURSOR_ALREADY_OPEN THEN
			raise_application_error(-20025,
				'The cursor, newcustnum, is already
opened.');
		WHEN OTHERS THEN
			raise_application_error(-20026,
				'An unknown error occurred.');

END ;
/
show errors ;

execute add_cust('Boeing','1845 Farimount','Wichita','WA','67216');
execute add_cust('Cessna','1845 Fairmount','Wichita','OR','67216');
execute add_cust('Lear Jet','1845 Fallmount','Wichita','KS','67216');
-- hit dupe val error
execute add_cust('Lear Jet','1845 Fallmount','Wichita','ME','67216');

select * from customer ;

/* ---------------------------------------------------------------*/
/* ---------------------------------------------------------------*/
/* ---------------------------------------------------------------*/
/* ---------------------------------------------------------------*/
/* ---------------------------------------------------------------*/


create or replace function exist_cust(existnumber IN number)
RETURN boolean 
IS
returnval boolean ;
existnum customer.customer_number%type ;
	BEGIN
		SELECT customer_number INTO existnum from customer 
			where customer_number = existnumber ;
			return(TRUE);
	EXCEPTION
		WHEN NO_DATA_FOUND THEN
			raise_application_error(-20005,
				'Customer number ' ||
to_char(existnumber) || ' was not found.');
			return(FALSE);
		WHEN OTHERS THEN
			raise_application_error(-20006,
				'An unknown error occurred.');
			return(FALSE);
	END;
/

show errors ;

/* ---------------------------------------------------------------*/
/* ---------------------------------------------------------------*/
/* ---------------------------------------------------------------*/
/* ---------------------------------------------------------------*/
/* ---------------------------------------------------------------*/


create or replace procedure del_cust(delnumber IN number)
AS
	BEGIN
		IF exist_cust(delnumber) THEN
			DELETE from customer where customer_number =
delnumber ;
		END IF ;
	/* take exceptions out of here, as exist_cust passes exception
		back to here and causes it to drop to the when others...
*/
	END;
/

show errors ;


/* ---------------------------------------------------------------*/
/* ---------------------------------------------------------------*/
/* ---------------------------------------------------------------*/
/* ---------------------------------------------------------------*/
/* ---------------------------------------------------------------*/

INSERT INTO customer VALUES
	(205, 'Nannette K. Thacker','4560 S.
Hydraulic','Wichita','KS','67216',2.0);
INSERT INTO customer VALUES
	(206, 'Esther Thacker','4560 S.
Hydraulic','Wichita','KS','67216',2.0);
INSERT INTO customer VALUES
	(207, 'Hannah Thacker','4560 S.
Hydraulic','Wichita','KS','67216',2.0);

select * from customer ;
execute del_cust(205);
execute del_cust(206);
execute del_cust(207);
execute del_cust(150);
select * from customer ;



/* ---------------------------------------------------------------*/
/* ---------------------------------------------------------------*/
/* ---------------------------------------------------------------*/
/* ---------------------------------------------------------------*/
/* ---------------------------------------------------------------*/

drop table customer cascade constraints ; 
drop sequence new_number ;
drop procedure add_cust	  ;
drop procedure del_cust	  ;
drop function exist_cust ;


spool off