- Rewrite the following IF statements so that you do not use an IF statement to set the value of no_revenue . What is the difference between these two statements? How does that difference affect your answer?
IF total_sales <= 0 THEN no_revenue := TRUE; ELSE no_revenue := FALSE; END IF; IF total_sales <= 0 THEN no_revenue := TRUE; ELSIF total_sales > 0 THEN no_revenue := FALSE; END IF;
- Rewrite the following IF statement to work as efficiently as possible under all conditions, given the following information: the calc_totalsnumeric function takes three minutes to return its value, while the overdue_balance Boolean function returns TRUE/FALSE in less than a second.
IF calc_totals (1994, company_id_in => 1005) AND NOT overdue_balance (company_id_in => 1005) THEN display_sales_figures (1005); ELSE contact_vendor; END IF;
- Rewrite the following IF statement to get rid of unnecessary nested IFs:
IF salary < 10000 THEN bonus := 2000; ELSE IF salary < 20000 THEN bonus := 1500; ELSE IF salary < 40000 THEN bonus := 1000; ELSE bonus := 500; END IF; END IF; END IF;
- Which procedure will never be executed in this IF statement?
IF (order_date > SYSDATE) AND order_total >= min_order_total THEN fill_order (order_id, 'HIGH PRIORITY'); ELSIF (order_date < SYSDATE) OR (order_date = SYSDATE) THEN fill_order (order_id, 'LOW PRIORITY'); ELSIF order_date <= SYSDATE AND order_total < min_order_total THEN queue_order_for_addtl_parts (order_id); ELSIF order_total = 0 THEN disp_message (' No items have been placed in this order!'); END IF;
A.1.2 Loops
- How many times does the following loop execute?
FOR year_index IN REVERSE 12 .. 1 LOOP calc_sales (year_index); END LOOP:
- Select the type of loop (FOR, WHILE, simple) appropriate to meet each of the following requirements:
- Set the status of each company whose company IDs are stored in a PL/SQL table to closed.
- For each of twenty years in the loan-processing cycle, calculate the outstanding loan balance for the specified customer. If the customer is a preferred vendor, stop the calculations after twelve years.
- Display the name and address of each employee returned by the cursor.
- Scan through the list of employees in the PL/SQL table, keeping count of all salaries greater than $50,000. Don't even start the scan, though, if the table is empty or if today is a Saturday or if the first employee in the PL/SQL table is the president of the company.
- Identify the problems with (or areas for improvement in) the following loops. How would you change the loop to improve it?
FOR i IN 1 .. 100 LOOP calc_totals (i); IF i > 75 THEN EXIT; END IF; END LOOP;
OPEN emp_cur; FETCH emp_cur INTO emp_rec; WHILE emp_cur%FOUND LOOP calc_totals (emp_rec.salary); FETCH emp_cur INTO emp_rec; EXIT WHEN emp_rec.salary > 100000; END LOOP; CLOSE emp_cur;
FOR a_counter IN lo_val .. hi_val LOOP IF a_counter > lo_val * 2 THEN hi_val := lo_val; END IF; END LOOP;
DECLARE CURSOR emp_cur IS SELECT salary FROM emp; emp_rec emp_cur%ROWTYPE BEGIN OPEN emp_cur; LOOP FETCH emp_cur INTO emp_rec; EXIT WHEN emp_cur%NOTFOUND; calc_totals (emp_rec.salary); END LOOP; CLOSE emp_cur; END;
WHILE no_more_data LOOP read_next_line (text); no_more_data := text IS NULL; EXIT WHEN no_more_data; END LOOP;
FOR month_index IN 1 .. 12 LOOP UPDATE monthly_sales SET pct_of_sales = 100 WHERE company_id = 10006 AND month_number = month_index; END LOOP;
DECLARE CURSOR emp_cur IS SELECT ... ; BEGIN FOR emp_rec IN emp_cur LOOP calc_totals (emp_rec.salary); END LOOP; IF emp_rec.salary < 10000 THEN DBMS_OUTPUT.PUT_LINE ('Give ''em a raise!'); END IF; CLOSE emp_cur; END;
DECLARE CURSOR checked_out_cur IS SELECT pet_id, name, checkout_date FROM occupancy WHERE checkout_date IS NOT NULL; BEGIN FOR checked_out_rec IN checked_out_cur LOOP INSERT INTO occupancy_history (pet_id, name, checkout_date) VALUES (checked_out_rec.pet_id, checked_out_rec.name, checked_out_rec.checkout_date); END LOOP; END;
-
DECLARE end_of_analysis BOOLEAN := FALSE; CURSOR analysis_cursor IS SELECT ...; analysis_rec analysis_cursor%ROWTYPE; next_analysis_step NUMBER; PROCEDURE get_next_record (step_out OUT NUMBER) IS BEGIN FETCH analysis_cursor INTO analysis_rec; IF analysis_rec.status = 'FINAL' THEN step_out := 1; ELSE step_out := 0; END IF; END; BEGIN OPEN analysis_cursor; WHILE NOT end_of_analysis LOOP get_next_record (next_analysis_step); IF analysis_cursor%NOTFOUND AND next_analysis_step IS NULL THEN end_of_analysis := TRUE; ELSE perform_analysis; END IF; END LOOP; END;
- Rewrite the following loop so that you do not use a loop at all.
FOR i IN 1 .. 2 LOOP IF i = 1 THEN give_bonus (president_id, 2000000); ELSIF i = 2 THEN give_bonus (ceo_id, 5000000); END IF; END LOOP;
- What statement would you remove from this block? Why?
DECLARE CURSOR emp_cur IS SELECT ename, deptno, empno FROM emp WHERE sal < 2500; emp_rec emp_cur%ROWTYPE; BEGIN FOR emp_rec IN emp_cur LOOP give_raise (emp_rec.empno, 10000); END LOOP; END;
A.1.3 Exception Handling
- In each of the following PL/SQL blocks, a VALUE_ERROR exception is raised (usually by an attempt to place too large a value into a local variable). Identify which exception handler (if any -- the exception could also go unhandled) will handle the exception by writing down the message that will be displayed by the call to PUT_LINE in the exception handler. Explain your choice.
DECLARE string_of_5_chars VARCHAR2(5); BEGIN string_of_5_chars := 'Steven'; END;
DECLARE string_of_5_chars VARCHAR2(5); BEGIN BEGIN string_of_5_chars := 'Steven'; EXCEPTION WHEN VALUE_ERROR THEN DBMS_OUTPUT.PUT_LINE ('Inner block'); END; EXCEPTION WHEN VALUE_ERROR THEN DBMS_OUTPUT.PUT_LINE ('Outer block'); END;
DECLARE string_of_5_chars VARCHAR2(5) := 'Eli'; BEGIN BEGIN string_of_5_chars := 'Steven'; EXCEPTION WHEN VALUE_ERROR THEN DBMS_OUTPUT.PUT_LINE ('Inner block'); END; EXCEPTION WHEN VALUE_ERROR THEN DBMS_OUTPUT.PUT_LINE ('Outer block'); END;
DECLARE string_of_5_chars VARCHAR2(5) := 'Eli'; BEGIN DECLARE string_of_3_chars VARCHAR2(3) := 'Chris'; BEGIN string_of_5_chars := 'Veva'; EXCEPTION WHEN VALUE_ERROR THEN DBMS_OUTPUT.PUT_LINE ('Inner block'); END; EXCEPTION WHEN VALUE_ERROR THEN DBMS_OUTPUT.PUT_LINE ('Outer block'); END;
DECLARE string_of_5_chars VARCHAR2(5); BEGIN BEGIN string_of_5_chars := 'Steven'; EXCEPTION WHEN VALUE_ERROR THEN RAISE NO_DATA_FOUND; WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE ('Inner block'); END; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE ('Outer block'); END;
- Write a PL/SQL block that allows all of the following SQL DML statements to execute, even if any of the others fail:
UPDATE emp SET empno = 100 WHERE empno > 5000; DELETE FROM dept WHERE deptno = 10; DELETE FROM emp WHERE deptno = 10;
- Write a PL/SQL block that handles by name the following Oracle error:
ORA-1014: ORACLE shutdown in progress.
The exception handler should, in turn, raise a VALUE_ERROR exception. Hint: use the EXCEPTION INIT pragma. - When the following block is executed, which of the two messages shown below are displayed? Explain your choice.Message from Exception HandlerOutput from Unhandled Exception
Predefined or programmer-defined?
Error at line 1: ORA-1403: no data found ORA-6512: at line 5
DECLARE d VARCHAR2(1); /* Create exception with a predefined name. */ no_data_found EXCEPTION; BEGIN SELECT dummy INTO d FROM dual WHERE 1=2; IF d IS NULL THEN /* || Raise my own exception, not the predefined || STANDARD exception of the same name. */ RAISE no_data_found; END IF; EXCEPTION /* This handler only responds to the RAISE statement. */ WHEN no_data_found THEN DBMS_OUTPUT.PUT_LINE ('Predefined or programmer-defined?'); END;
- I create the getval package as shown below. I then call DBMS_OUTPUT.PUT_LINE to display the value returned by the getval.getfunction. What is displayed on the screen?
CREATE OR REPLACE PACKAGE getval IS FUNCTION get RETURN VARCHAR2; END getval; / CREATE OR REPLACE PACKAGE BODY getval IS v VARCHAR2(1) := 'abc'; FUNCTION get RETURN VARCHAR2 IS BEGIN RETURN v; END; BEGIN NULL; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE ('Trapped!'); END getval; /
A.1.4 Cursors
- What cursor-related statements are missing from the following block?
DECLARE CURSOR emp_cur IS SELECT * FROM emp; BEGIN OPEN emp_cur; FETCH emp_cur INTO emp_rec; END;
- What statement should be removed from the following block?
DECLARE CURSOR emp_cur IS SELECT * FROM emp; emp_rec emp_cur%ROWTYPE; BEGIN FOR emp_rec IN emp_cur LOOP give_raise (emp_rec.empno); END LOOP; END;
- Name the cursor attribute (along with the cursor name) you would use (if any) for each of the following requirements:
- If the FETCH did not return any records from the company_cur cursor, exit the loop.
- If the number of rows deleted exceeded 100, notify the manager.
- If the emp_cur cursor is already open, fetch the next record. Otherwise, open the cursor.
- If the FETCH returns a row from the sales_cur cursor, display the total sales information.
- I use an implicit cursor SELECT statement to obtain the latest date of sales for my store number 45067. If no data is fetched or returned by the SELECT, display a warning.
- What message is displayed in the following block if the SELECT statement does not return a row?
PROCEDURE display_dname (emp_in IN INTEGER) IS department# dept.deptno%TYPE := NULL; BEGIN SELECT deptno INTO department# FROM emp WHERE empno = emp_in; IF department# IS NULL THEN DBMS_OUTPUT.PUT_LINE ('Dept is not found!'); ELSE DBMS_OUTPUT.PUT_LINE ('Dept is ' || TO_CHAR (department#)); END IF; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE ('No data found'); END;
- What message is displayed in the following block if there are no employees in department 15?
PROCEDURE display_dept_count IS total_count INTEGER := 0; BEGIN SELECT COUNT(*) INTO total_count FROM emp WHERE deptno = 15; IF total_count = 0 THEN DBMS_OUTPUT.PUT_LINE ('No employees in department!'); ELSE DBMS_OUTPUT.PUT_LINE ('Count of employees in dept 15 = ' || TO_CHAR (total_count)); END IF; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE ('No data found'); END;
- If you fetch past the last record in a cursor's result set, what will happen?
- How would you change the SELECT statement in the following block's cursor so that the block can display the sum of salaries in each department?
DECLARE CURSOR tot_cur IS SELECT deptno, SUM (sal) FROM emp GROUP BY deptno; BEGIN FOR tot_rec IN tot_cur LOOP DBMS_OUTPUT.PUT_LINE ('Total is: ' || tot_rec.total_sales); END LOOP; END;
- Rewrite the following block to use a cursor parameter. Then rewrite to use a local module, as well as a cursor parameter.
DECLARE CURSOR dept10_cur IS SELECT dname, SUM (sal) total_sales FROM emp WHERE deptno = 10; dept10_rec dept10_cur%ROWTYPE; CURSOR dept20_cur IS SELECT dname, SUM (sal) FROM emp WHERE deptno = 20; dept20_rec dept20_cur%ROWTYPE; BEGIN OPEN dept10_cur; FETCH dept10_cur INTO dept10_rec; DBMS_OUTPUT.PUT_LINE ('Total for department 10 is: ' || tot_rec.total_sales); CLOSE dept10_cur; OPEN dept20_cur; FETCH dept20_cur INTO dept20_rec; DBMS_OUTPUT.PUT_LINE ('Total for department 20 is: ' || tot_rec.total_sales); CLOSE dept20_cur; END;
- Place the following cursor inside a package, declaring the cursor as a public element (in the specification). The SELECT statement contains all of the columns in the emp table, in the same order.
CURSOR emp_cur (dept_in IN INTEGER) IS SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno FROM emp WHERE deptno = dept_in;
No comments:
Post a Comment