Re: Cursor Immediate Help Please

Good thing you are not one of my students. We'd be having a nice long discussion about ethics about now.

The point in take a class is to learn something. That is accomplished by studying, meeting challenges head-on, and overcoming them.

Not by getting some anonymous person on the internet to do your homework for you. I'm with Sybrand except that even if you did pay my consulting fee I wouldn't get you out of this mess. Open those books and get what you are paying for ... an education.

Daniel Morgan

Beau Leo wrote:

> -- drop the table
> DROP TABLE ORDERS;
> DROP TABLE CAR;
> DROP TABLE CUSTOMER;
> DROP TABLE SALES;
>
> -- drop the sequences
> DROP SEQUENCE CUST_SEQ;
> DROP SEQUENCE SALES_SEQ;
> DROP SEQUENCE ORDERS_SEQ;
> DROP SEQUENCE VIN_SEQ;
>
> -- create the sequences
> CREATE SEQUENCE CUST_SEQ INCREMENT BY 1;
> CREATE SEQUENCE SALES_SEQ INCREMENT BY 1;
> CREATE SEQUENCE ORDERS_SEQ INCREMENT BY 1;
> CREATE SEQUENCE VIN_SEQ INCREMENT BY 1;
>
> --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);
>
> -- ** note 5 records inserted already ** --
>
> 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);
>
> -- ** note 5 records already inserted ** --
>
> 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);
>
> -- **note 5 records inserted** --
>
> 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);
>
> -- ** note 5 records inserted ** --
>
> 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 Wed Feb 13 2002 - 11:41:32 CST