Which is Better and Why it is better?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Which is Better and Why it is better?

  1. #1
    Join Date
    Feb 2001
    Posts
    119

    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

  2. #2
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  3. #3
    Join Date
    Nov 2008
    Posts
    4
    they both are not well.

  4. #4
    Join Date
    Dec 2008
    Posts
    1
    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
  •  


Click Here to Expand Forum to Full Width