-
Which is Better and Why it is better?
Which procedure is the good one?Why
SQL> select count(*) from emp ;
COUNT(*)
----------
917504
CREATE OR REPLACE PROCEDURE emp_sal_cur (p_empno IN emp.empno%TYPE)
IS
CURSOR c1
IS
SELECT ename, job, hiredate, sal, deptno
FROM emp
WHERE empno = p_empno;
v_avgsal emp.sal%TYPE;
BEGIN
FOR crec IN c1
LOOP
DBMS_OUTPUT.put_line ('Ename : ' || crec.ename);
DBMS_OUTPUT.put_line ('Salary: ' || crec.sal);
DBMS_OUTPUT.put_line ('Job : ' || crec.job);
DBMS_OUTPUT.put_line ('Hiredate : ' || crec.hiredate);
SELECT AVG (sal)
INTO v_avgsal
FROM emp
WHERE deptno = crec.deptno;
IF crec.sal > v_avgsal
THEN
DBMS_OUTPUT.put_line ( 'Employee''s salary is more than the '
|| 'department average of '
|| v_avgsal
);
ELSE
DBMS_OUTPUT.put_line ( 'Employee''s salary does not exceed the '
|| 'department average of '
|| v_avgsal
);
END IF;
DBMS_OUTPUT.put_line ('-----------------------------');
END LOOP;
END;
CREATE OR REPLACE PROCEDURE emp_sal_query (
p_empno IN emp.empno%TYPE
)
IS
TYPE emp_typ IS RECORD (
ename emp.ename%TYPE,
job emp.job%TYPE,
hiredate emp.hiredate%TYPE,
sal emp.sal%TYPE,
deptno emp.deptno%TYPE
);
r_emp emp_typ;
v_avgsal emp.sal%TYPE;
BEGIN
SELECT ename, job, hiredate, sal, deptno
INTO r_emp.ename, r_emp.job, r_emp.hiredate, r_emp.sal, r_emp.deptno
FROM emp WHERE empno = p_empno;
DBMS_OUTPUT.PUT_LINE('Employee # : ' || p_empno);
DBMS_OUTPUT.PUT_LINE('Name : ' || r_emp.ename);
DBMS_OUTPUT.PUT_LINE('Job : ' || r_emp.job);
DBMS_OUTPUT.PUT_LINE('Hire Date : ' || r_emp.hiredate);
DBMS_OUTPUT.PUT_LINE('Salary : ' || r_emp.sal);
DBMS_OUTPUT.PUT_LINE('Dept # : ' || r_emp.deptno);
SELECT AVG(sal) INTO v_avgsal
FROM emp WHERE deptno = r_emp.deptno;
IF r_emp.sal > v_avgsal THEN
DBMS_OUTPUT.PUT_LINE('Employee''s salary is more than the '
|| 'department average of ' || v_avgsal);
ELSE
DBMS_OUTPUT.PUT_LINE('Employee''s salary does not exceed the '
|| 'department average of ' || v_avgsal);
END IF;
END;
/
SQL> exec EMP_SAL_CUR(10002);
Ename : CLARK
Salary: 2450
Job : MANAGER
Hiredate : 09-JUN-81
Employee's salary does not exceed the department average of 2916.67
-----------------------------
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.84
SQL> exec EMP_SAL_QUERY (10002);
Employee # : 10002
Name : CLARK
Job : MANAGER
Hire Date : 09-JUN-81
Salary : 2450
Dept # : 10
Employee's salary does not exceed the department average of 2916.67
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.90
-
Sorry, but they both fail. SELECTing the Average inside the loop is bad coding, and the entire thing can be done in a single SQL statement anyway. So tell your professor to try a little harder with the questions from now on. But thanks for playing, and better luck next test.
- Chris
-
-
It confuses to me
Not so well coded
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|