Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Cursor Immediate Help Please

Cursor Immediate Help Please

From: Beau Leo <beauseo_at_netzero.net>
Date: Wed, 13 Feb 2002 04:09:06 +0000 (UTC)
Message-ID: <f982eae9484b66076b9b0ca528ff212e.61814@mygate.mailgate.org> 






--DONE-- **WORKS**



CREATE TABLE SALES(



SALES_ID NUMBER,



DIST_ID CHAR(4),



F_NAME VARCHAR2(30),



L_NAME VARCHAR2(30),



POINTS NUMBER,



CONSTRAINT SALES_PK PRIMARY KEY(SALES_ID));


INSERT INTO SALES(SALES_ID, DIST_ID, F_NAME, L_NAME, POINTS)

VALUES(SALES_SEQ.NEXTVAL, 'DEPT', 'MARK', 'JONES', 30);


INSERT INTO SALES(SALES_ID, DIST_ID, F_NAME, L_NAME, POINTS)

VALUES(SALES_SEQ.NEXTVAL, 'DEPT', 'AMANDA', 'TUCKER', 45);


INSERT INTO SALES(SALES_ID, DIST_ID, F_NAME, L_NAME, POINTS)

VALUES(SALES_SEQ.NEXTVAL, 'EET', 'JAMES', 'JOHNSTON', 38);


INSERT INTO SALES(SALES_ID, DIST_ID, F_NAME, L_NAME, POINTS)

VALUES(SALES_SEQ.NEXTVAL, 'DEPT', 'TONYA', 'MCADOO', 40);


INSERT INTO SALES(SALES_ID, DIST_ID, F_NAME, L_NAME, POINTS)

VALUES(SALES_SEQ.NEXTVAL, 'EET', 'KARA', 'KILBY', 36);







INSERT INTO SALES(SALES_ID, DIST_ID, F_NAME, L_NAME, POINTS)

VALUES(SALES_SEQ.NEXTVAL, 'EET', 'HEATHER', 'HOWARD', 44);


INSERT INTO SALES(SALES_ID, DIST_ID, F_NAME, L_NAME, POINTS)

VALUES(SALES_SEQ.NEXTVAL, 'CAL', 'JOSHUA', 'SMITH', 42);


INSERT INTO SALES(SALES_ID, DIST_ID, F_NAME, L_NAME, POINTS)

VALUES(SALES_SEQ.NEXTVAL, 'CAL', 'MICHAEL', 'SCHMIDT', 38);


INSERT INTO SALES(SALES_ID, DIST_ID, F_NAME, L_NAME, POINTS)

VALUES(SALES_SEQ.NEXTVAL, 'CART', 'RALPH', 'CANNON', 34);


INSERT INTO SALES(SALES_ID, DIST_ID, F_NAME, L_NAME, POINTS)

VALUES(SALES_SEQ.NEXTVAL, 'EET', 'REBBECA', 'ABERNATHY', 41);



--DONE-- **WORKS**



CREATE TABLE CUSTOMER(



CUST_ID NUMBER ,



F_NAME VARCHAR2(30) ,



L_NAME VARCHAR2(30) ,



STREET_ADDRESS VARCHAR2(60),



CITY VARCHAR2(30),



STATE CHAR(2),



ZIP VARCHAR2(11),



COUNTRY VARCHAR2(18),



PHONE VARCHAR2(15),



EMAIL VARCHAR2(45),



ALT_EMAIL VARCHAR2(45),



WORK_PHONE VARCHAR2(15),



SALES_ID NUMBER,



VIN NUMBER,



ORDER_ID NUMBER,



CONSTRAINT CUSTOMER_PK PRIMARY KEY (CUST_ID),

CONSTRAINT CUSTOMER_FK FOREIGN KEY (SALES_ID) REFERENCES

SALES(SALES_ID));



INSERT INTO CUSTOMER(CUST_ID, F_NAME, L_NAME, STREET_ADDRESS, CITY,

STATE, ZIP, COUNTRY,



PHONE, EMAIL, ALT_EMAIL, WORK_PHONE, SALES_ID)

VALUES(CUST_SEQ.NEXTVAL, 'RUTH', 'WOLFFE', '654 WINTERLAKE DRIVE',



'MARIETTA', 'GA',


'34000-3332', 'USA', '678-222-6791', 'RWOLFE_at_HOTMAIL.COM',


'RWOLFE_at_CC.NET', '678-456-3211',




1);



INSERT INTO CUSTOMER(CUST_ID, F_NAME, L_NAME, STREET_ADDRESS, CITY,

STATE, ZIP, COUNTRY,



PHONE, EMAIL, ALT_EMAIL, WORK_PHONE, SALES_ID) 

VALUES(CUST_SEQ.NEXTVAL, 'MICHAEL', 'RHINEHEART', '2345 MILTON ROAD',



'ALPHARETTA', 'GA',


'30005-2232', 'USA', '770-332-9832', 'MIKE_at_ATT.NET',


'MRHINE_at_MINDSPRING.COM',


'678-334-5629', 2);




INSERT INTO CUSTOMER(CUST_ID, F_NAME, L_NAME, STREET_ADDRESS, CITY,

STATE, ZIP, COUNTRY,



PHONE, EMAIL, ALT_EMAIL, WORK_PHONE, SALES_ID)

VALUES(CUST_SEQ.NEXTVAL, 'AMY', 'JONES', '2311 TIMBERLAKE DRIVE',



'ALPHARETTA', 'GA',


'30004-2298', 'USA', '770-455-9678', 'AJONES_at_YAHOO.COM',


'AMYJ200_at_HOTMAIL.COM',


'770-322-9923', 3);




INSERT INTO CUSTOMER(CUST_ID, F_NAME, L_NAME, STREET_ADDRESS, CITY,

STATE, ZIP, COUNTRY,



PHONE, EMAIL, ALT_EMAIL, WORK_PHONE, SALES_ID)

VALUES(CUST_SEQ.NEXTVAL, 'MARTHA', 'WINTERS', '8443 PARK LANE',



'ROSEWELL', 'GA',


'34588-2212', 'USA', '678-398-4566', 'MWINTERS_at_MINDSPRING.COM',


'MARTHA_at_NETSCAPE.NET',


'770-888-3322', 4);




INSERT INTO CUSTOMER(CUST_ID, F_NAME, L_NAME, STREET_ADDRESS, CITY,

STATE, ZIP, COUNTRY,



PHONE, EMAIL, ALT_EMAIL, WORK_PHONE, SALES_ID)

VALUES(CUST_SEQ.NEXTVAL, 'RUTH', 'WOLFFE', '654 WINTERLAKE DRIVE',



'MARIETTA', 'GA',


'34000-3332', 'USA', '678-222-6791', 'RWOLFE_at_HOTMAIL.COM',


'RWOLFE_at_CC.NET', '678-456-3211',




5);






INSERT INTO CUSTOMER(CUST_ID, F_NAME, L_NAME, STREET_ADDRESS, CITY,

STATE, ZIP, COUNTRY,



PHONE, EMAIL, ALT_EMAIL, WORK_PHONE, SALES_ID)

VALUES(CUST_SEQ.NEXTVAL, 'MICKEY', 'CANTOR', 'WASHINGTON STREET ROAD',



'ATLANTA', 'GA',


'33444-1243', 'USA', '404-622-9992', 'MCANTOR_at_HOTMAIL.COM',


'MICKEYC_at_NETZERO.NET',


'235-334-3322', 6);




INSERT INTO CUSTOMER(CUST_ID, F_NAME, L_NAME, STREET_ADDRESS, CITY,

STATE, ZIP, COUNTRY,



PHONE, EMAIL, ALT_EMAIL, WORK_PHONE, SALES_ID)

VALUES(CUST_SEQ.NEXTVAL, 'JAMES', 'EDWARDS', '2322 ELM STREET',



'ATLANTA', 'GA',


'30004-3322', 'USA', '404-529-9934', 'JEDWARDS_at_TRAVELERS.COM',


'JAMES430_at_YAHOO.COM',


'770-445-9983', 7);




INSERT INTO CUSTOMER(CUST_ID, F_NAME, L_NAME, STREET_ADDRESS, CITY,

STATE, ZIP, COUNTRY,



PHONE, EMAIL, ALT_EMAIL, WORK_PHONE, SALES_ID)

VALUES(CUST_SEQ.NEXTVAL, 'MERIDITH', 'TAGAN', '168 CORNELL STREET',



'ROSEWELL', 'GA',


'30005-5622', 'USA', '678-845-2945', 'MTAGAN_at_SPRINT.COM',


'MERIDITHT33_at_HOTMAIL.COM',


'404-322-9985', 8);




INSERT INTO CUSTOMER(CUST_ID, F_NAME, L_NAME, STREET_ADDRESS, CITY,

STATE, ZIP, COUNTRY,



PHONE, EMAIL, ALT_EMAIL, WORK_PHONE, SALES_ID)

VALUES(CUST_SEQ.NEXTVAL, 'JAY', 'DUMARS', '3312 LYNN STREET', 'ATLANTA',



'GA', '30004-3321',

'USA', '404-360-3926', 'JDUMBARS_at_STAR.COM', 'JDUMBARS_at_DEVRY.EDU',


'404-562-9923', 9);




INSERT INTO CUSTOMER(CUST_ID, F_NAME, L_NAME, STREET_ADDRESS, CITY,

STATE, ZIP, COUNTRY,



PHONE, EMAIL, ALT_EMAIL, WORK_PHONE, SALES_ID)

VALUES(CUST_SEQ.NEXTVAL, 'JENNIFER', 'HARRISON', '456 OAK LANE',



'ROSEWELL', 'GA', '30004-3211',


'USA', '678-381-4682', 'JENNIFERHARRISON_at_SPRINT.COM' ,


'JHARISSON84_at_YAHOO.COM',


'404-592-7921', 10);




--DONE-- **WORKS**



CREATE TABLE CAR(



VIN NUMBER,



MODEL VARCHAR2(20),



MAKE VARCHAR2(20),



YEAR DATE,



COLOR VARCHAR2(15),



DESCRIPTION VARCHAR2(60),



CUST_ID NUMBER,



NUM_WITH_SAME_MAKE_MODEL NUMBER,



PRICE NUMBER,



CONSTRAINT CAR_PK PRIMARY KEY(VIN),



CONSTRAINT CAR_FK FOREIGN KEY(CUST_ID) REFERENCES CUSTOMER(CUST_ID));


INSERT INTO CAR(VIN, MODEL, MAKE, YEAR, COLOR, DESCRIPTION, CUST_ID,

NUM_WITH_SAME_MAKE_MODEL, PRICE)



VALUES(VIN_SEQ.NEXTVAL, 'TOYOTA', 'COROLLA', to_date('1998', 'YYYY'),


'BLACK',


'VERY GOOD VEICHLE WITH STABILITY AND LONGEVITY', 1, 0, 27500.00);



INSERT INTO CAR(VIN, MODEL, MAKE, YEAR, COLOR, DESCRIPTION, CUST_ID,

NUM_WITH_SAME_MAKE_MODEL, PRICE)



VALUES(VIN_SEQ.NEXTVAL, 'TOYOTA', 'CAMARY', to_date('1997', 'YYYY'),


'SILVER',


'STABLE VEICHLE WITH GREAT ROADSIDE HANDLING', 2, 0, 30000.00);



INSERT INTO CAR(VIN, MODEL, MAKE, YEAR, COLOR, DESCRIPTION, CUST_ID,

NUM_WITH_SAME_MAKE_MODEL, PRICE)



VALUES(VIN_SEQ.NEXTVAL, 'HONDA', 'ACCORD', to_date('2001','YYYY'),


'BLUE',


'FAST VEICHLE WITH GREAT MILEAGE', 3, 0, 28000.00);



INSERT INTO CAR(VIN, MODEL, MAKE, YEAR, COLOR, DESCRIPTION, CUST_ID,

NUM_WITH_SAME_MAKE_MODEL, PRICE)



VALUES(VIN_SEQ.NEXTVAL, 'HONDA', 'CIVIC', to_date('2002','YYYY'), 'RED',


'A GREAT CAR WITH EXTRA UTILITY FEATURES', 4, 0, 29000.00);



INSERT INTO CAR(VIN, MODEL, MAKE, YEAR, COLOR, DESCRIPTION, CUST_ID,

NUM_WITH_SAME_MAKE_MODEL, PRICE)



VALUES(VIN_SEQ.NEXTVAL, 'TOYOTA', 'CAMARY', to_date('1997','YYYY'),


'SILVER',


'STABLE VEICHLE WITH GREAT ROADSIDE HANDLING', 5, 0, 30000.00);








INSERT INTO CAR(VIN, MODEL, MAKE, YEAR, COLOR, DESCRIPTION, CUST_ID,

NUM_WITH_SAME_MAKE_MODEL, PRICE)



VALUES(VIN_SEQ.NEXTVAL, 'TOYOTA', 'COROLLA', to_date('1998', 'YYYY'),


'BLACK',


'VERY GOOD VEICHLE WITH STABILITY AND LONGEVITY', 6, 0, 27500.00);



INSERT INTO CAR(VIN, MODEL, MAKE, YEAR, COLOR, DESCRIPTION, CUST_ID,

NUM_WITH_SAME_MAKE_MODEL, PRICE)



VALUES(VIN_SEQ.NEXTVAL, 'TOYOTA', 'CAMARY', to_date('1997', 'YYYY'),


'SILVER',


'STABLE VEICHLE WITH GREAT ROADSIDE HANDLING', 7, 0, 30000.00);



INSERT INTO CAR(VIN, MODEL, MAKE, YEAR, COLOR, DESCRIPTION, CUST_ID,

NUM_WITH_SAME_MAKE_MODEL, PRICE)



VALUES(VIN_SEQ.NEXTVAL, 'HONDA', 'ACCORD', to_date('2001','YYYY'),


'BLUE',


'FAST VEICHLE WITH GREAT MILEAGE', 8, 0, 28000.00);



INSERT INTO CAR(VIN, MODEL, MAKE, YEAR, COLOR, DESCRIPTION, CUST_ID,

NUM_WITH_SAME_MAKE_MODEL, PRICE)



VALUES(VIN_SEQ.NEXTVAL, 'HONDA', 'CIVIC', to_date('2002','YYYY'), 'RED',


'A GREAT CAR WITH EXTRA UTILITY FEATURES', 9, 0, 29000.00);



INSERT INTO CAR(VIN, MODEL, MAKE, YEAR, COLOR, DESCRIPTION, CUST_ID,

NUM_WITH_SAME_MAKE_MODEL, PRICE)



VALUES(VIN_SEQ.NEXTVAL, 'TOYOTA', 'CAMARY', to_date('1997','YYYY'),


'SILVER',


'STABLE VEICHLE WITH GREAT ROADSIDE HANDLING', 10, 0, 30000.00);



--DONE-- **WORKS**



CREATE TABLE ORDERS(



ORDER_ID NUMBER,



CUST_ID NUMBER,



VIN NUMBER, 



SALES_ID NUMBER,



CONSTRAINT ORDERS_PK PRIMARY KEY(ORDER_ID),

CONSTRAINT ORDERS_FK_CUST FOREIGN KEY(CUST_ID) REFERENCES

CUSTOMER(CUST_ID),



CONSTRAINT ORDERS_FK_VIN FOREIGN KEY(VIN) REFERENCES CAR(VIN),

CONSTRAINT ORDERS_FK_SALES FOREIGN KEY(SALES_ID) REFERENCES

SALES(SALES_ID));



INSERT INTO ORDERS(ORDER_ID, CUST_ID, VIN, SALES_ID )

VALUES(ORDERS_SEQ.NEXTVAL, 1, 1, 1);


INSERT INTO ORDERS(ORDER_ID, CUST_ID, VIN, SALES_ID)

VALUES(ORDERS_SEQ.NEXTVAL, 2, 2, 2);


INSERT INTO ORDERS(ORDER_ID, CUST_ID, VIN, SALES_ID)

VALUES(ORDERS_SEQ.NEXTVAL, 3, 3, 3);


INSERT INTO ORDERS(ORDER_ID, CUST_ID, VIN, SALES_ID)

VALUES(ORDERS_SEQ.NEXTVAL, 4, 4, 4);


INSERT INTO ORDERS(ORDER_ID, CUST_ID, VIN, SALES_ID)

VALUES(ORDERS_SEQ.NEXTVAL, 5, 5, 5);







INSERT INTO ORDERS(ORDER_ID, CUST_ID, VIN, SALES_ID)

VALUES(ORDERS_SEQ.NEXTVAL, 6, 6, 6);


INSERT INTO ORDERS(ORDER_ID, CUST_ID, VIN, SALES_ID)

VALUES(ORDERS_SEQ.NEXTVAL, 7, 7, 7);


INSERT INTO ORDERS(ORDER_ID, CUST_ID, VIN, SALES_ID)

VALUES(ORDERS_SEQ.NEXTVAL, 8, 8, 8);


INSERT INTO ORDERS(ORDER_ID, CUST_ID, VIN, SALES_ID)

VALUES(ORDERS_SEQ.NEXTVAL, 9, 9, 9);


INSERT INTO ORDERS(ORDER_ID, CUST_ID, VIN, SALES_ID)

VALUES(ORDERS_SEQ.NEXTVAL, 10, 10, 10);



CREATE OR REPLACE FUNCTION PRICE_VIN(P_VIN CAR.VIN%TYPE)

RETURN NUMBER IS


V_PRICE CAR.PRICE%TYPE;



PRICE_TO_LOW EXCEPTION;


BEGIN


SELECT PRICE



INTO V_PRICE



FROM CAR



WHERE VIN = P_VIN;


IF V_PRICE < 40000 THEN


RAISE PRICE_TO_LOW;



END IF


EXCEPTION



WHEN PRICE_TO_LOW THEN



RAISE_APPLIATION_ERROR('NUMBER IS LESS THAN 40000');


RETURN V_PRICE;



END PRICE_VIN;









--**WORKS**--


CREATE OR REPLACE FUNCTION TOTAL_CARS(P_MODEL CAR.MODEL%TYPE, P_MAKE

CAR.MAKE%TYPE



) RETURN NUMBER IS


V_VIN CAR.VIN%TYPE;


BEGIN


SELECT COUNT(VIN)



INTO V_VIN



FROM CAR



WHERE MAKE = P_MAKE 



AND MODEL = P_MODEL;


RETURN V_VIN;



END TOTAL_CARS;







CREATE OR REPLACE FUNCTION GET_TOTAL_ORDERS()







--**WORKS**--



CREATE OR REPLACE PROCEDURE UPDATE_PRICE(P_VIN CAR.VIN%TYPE) IS


V_GET_PRICE CAR.PRICE%TYPE;


BEGIN


V_GET_PRICE := PRICE_VIN(P_VIN);


UPDATE CAR



SET PRICE = V_GET_PRICE + 2000



WHERE VIN = P_VIN;



COMMIT;



DBMS_OUTPUT.ENABLE;
DBMS_OUTPUT.PUT_LINE('THE PRICE FOR CAR IS ');
DBMS_OUTPUT.PUT_LINE(V_GET_PRICE);






END;









--**WORKS**--




CREATE OR REPLACE PROCEDURE UPDATE_CARS(P_MODEL CAR.MODEL%TYPE, P_MAKE

CAR.MAKE%TYPE) IS


V_GET_VEICHLES CAR.NUM_WITH_SAME_MAKE_MODEL%TYPE;

E_TOTAL_CARS EXCEPTION;


BEGIN


V_GET_VEICHLES := TOTAL_CARS(P_MODEL, P_MAKE);


IF V_GET_VEICHLES <= 3


RAISE E_TOTAL_CARS;



END IF;


EXCEPTION



RAISE_APPLICATION_ERROR('THE TOTAL NUMBER OF CARS IS LESS THAN OR EQUAL

TO THREE');


UPDATE CAR



SET NUM_WITH_SAME_MAKE_MODEL = V_GET_VEICHLES

WHERE MODEL = P_MODEL AND



MAKE = P_MAKE;


COMMIT;



END;





How can I write a code with a cursor for any one of the functions or
procedures? Please send me the results along with the cursor. that
assignment is due tomorrow!! Thanks in advance.



-- 
Posted via Mailgate.ORG Server - http://www.Mailgate.ORG


Received on Tue Feb 12 2002 - 22:09:06 CST












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US