StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POOracle SQL academic issue. Cursors, Loops and Functions
text
Body
copied!<p>These are the requirements of the question:</p> <p>Create a PL/SQL function called <code>findtotalcarmodels</code> to return the total number of cars belonging to a particular model. The function should have a <em>single IN parameter as</em> <code>model_name</code>. You should then use an explicit cursor to count the number of cars belonging to that car model and return the final count. You must <strong>NOT</strong> use any implicit cursors, table joins, subqueries, set operators, group functions or SQL functions (such as COUNT) to create this function.</p> <p>Now, write ONE PL/SQL anonymous block that provides a report about car details. The full specification to create the PL/SQL anonymous block is as follows:</p> <p>• Using an explicit cursor, retrieve all car registration, cost and model name details (registration, cost & model_name) from the <code>I_CAR</code> table.</p> <p>• If the cost of the car is less than or equal to $50,000, we determine car cost category as “Budget Car”. If the car cost is between $50,000 and $100,000, the car cost category is “Standard Car”. For all other cars costing more than $100,000; the car cost category is “Premium Car”. Declare a local variable <code>v_carcategory as VARCHAR2(40)</code> to store car cost category.</p> <p>• Call the function <code>findtotalcarmodels</code> to get the total number of cars belonging to the model name for the car and store them in a local variable named <code>v_totalcars</code>.</p> <p>• Use an explicit cursor with a parameter that accepts car registration to find the most recent reservation made on the car. You will have to look at the <code>date_reserved</code> column from <code>I_BOOKING</code> table here. You cannot use the <code>MAX</code> function. Compare all the relevant dates to find the most recent one.</p> <p>• Display a report showing car registration, car cost category, car model name, total number of cars belonging to that model and the most recent reservation made on that car.</p> <p>• Finally, create an exception handler which fires when no rows are found. The exception handler should output the following message to the screen: “No rows found”. </p> <p>Important Notes:</p> <p>• You must NOT use any implicit cursors, table joins, subqueries, set operators, group functions or SQL functions (such as COUNT) to create the PL/SQL function or the PL/SQL anonymous block.</p> <p>• The PL/SQL anonymous block must be ONE block only. Do NOT write a block to perform each task of the specification above.</p> <p>This is what I have so far which I know is so far from correct it isn't even funny:</p> <p><strong>FUNCTION:</strong></p> <pre><code>CREATE OR REPLACE Function findtotalcarmodels (model_name_in IN varchar2) RETURN NUMBER IS counter INTEGER := 0; CURSOR car_count_cur IS SELECT model_name FROM i_car WHERE model_name = model_name_in; Rec_car_details car_count_cur%ROWTYPE; BEGIN OPEN car_count_cur; LOOP FETCH car_count_cur INTO Rec_car_details; EXIT WHEN car_count_cur%NOTFOUND; counter := counter + 1; END LOOP; CLOSE car_count_cur; RETURN counter; END; </code></pre> <p><strong>PL/SQL:</strong></p> <pre><code>SET SERVEROUTPUT ON FORMAT WRAP SIZE 12000 Declare v_model VARCHAR2(40); v_cost NUMBER; v_reg VARCHAR2(10); v_carcategory VARCHAR2(40); v_totalcars NUMBER; cursor carcur IS SELECT * FROM i_car; v_car carcur%ROWTYPE; Begin FOR v_car IN carcur LOOP Select Registration, cost, model_name, findtotalcarmodels(model_name) INTO v_reg, v_cost, v_model,v_totalcars from i_car WHERE registration = v_car.registration; If v_cost <=50000 THEN v_carcategory := 'Budget Car'; End IF; If v_cost BETWEEN 50000 AND 100000 THEN v_carcategory := 'Standard Car'; End IF; If v_cost >100000 THEN v_carcategory := 'Premium Car'; End If; DBMS_OUTPUT.PUT_LINE('Registration:'|| ' '|| v_car.registration); DBMS_OUTPUT.PUT_LINE('Cost:'|| '$' ||v_car.Cost); DBMS_OUTPUT.PUT_LINE('Model Name:'|| ' '||v_car.model_name); DBMS_OUTPUT.PUT_LINE('Car Category:'|| ' '||v_carcategory); DBMS_OUTPUT.PUT_LINE('Total number of Cars:'|| ' '||v_totalcars); DBMS_OUTPUT.NEW_LINE; END LOOP; End; </code></pre> <p>EDIT: The code above is where I am at now. The only issue that remains is to get the second explicit cursor in there to return the most recent rental date. This is the main part that has me stumped.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload