Need help creating PL/SQL procedure using function

[n33dh3lp]

Hello,
I am new to PL/SQL and having GREAT difficulty with the following:
Create a stored procedure which processes one employee record (pass
an employee number as a parameter to the procedure). Call your function,
TOTAL_PAY, created in a previous step and insert a record into the BONUS table if
the employee’s total pay is at least $2000. The record to be inserted should have an
increased amount for commission by 10% if they have a commission or it should set
it to $100 if commission is null. Otherwise (total pay is not at least 2000), display a
message with DBMS_OUTPUT.PUT_LINE which shows the employee’s name and
salary such as “Employee SMITH has total pay of $1800”.


I have created TOTAL_PAY as:

create or replace function total_pay (emp_no IN emp.empno%type)
return NUMBER
is 
CURSOR grp_cur
is 
select SUM (SAL + COMM)
from emp
where  empno = emp_no;
return_value NUMBER;
Begin
OPEN grp_cur;
FETCH grp_cur into return_value;
CLOSE grp_cur;
RETURN return_value;
END;

[pcbala]

Hi n33dh3lp,
I have understood your requirements. But, it is advice-able if you do your work within a PACKAGE. I have created it inside a single package. Please refer it you have not yet got the answer.

CREATE OR REPLACE
PACKAGE dummy_dummy AS
PROCEDURE try_emp_try 
(
p_empid IN VARCHAR2
);
 
FUNCTION dummy_total
(
p_empno IN  emp.empno%TYPE,
p_sal   IN  NUMBER,
p_comm  IN  NUMBER
)
RETURN NUMBER;
END;
 

The above is the package specification.
The below coding is the package body.

CREATE OR REPLACE
PACKAGE BODY dummy_dummy AS
PROCEDURE try_emp_try(p_empid IN VARCHAR2)IS
v_empno         NUMBER(10);
v_sal           NUMBER(10);
v_comm          NUMBER(10);
v_total         NUMBER(10);
v_name          VARCHAR2(30);
v_job           VARCHAR2(30);
v_mgr           VARCHAR2(30);
v_hiredate      VARCHAR2(30);
v_deptno        VARCHAR2(30);
v_sex           VARCHAR2(1);
v_dummy         VARCHAR2(1000);
 
CURSOR c_dummy IS
SELECT ename,mgr,hiredate,sal,comm,deptno,sex 
FROM emp
WHERE  empno=p_empid;
 
BEGIN
OPEN   c_dummy;
FETCH  c_dummy  INTO v_name,v_mgr,v_hiredate,v_sal,v_comm,v_deptno,v_sex;
CLOSE  c_dummy;
DBMS_OUTPUT.PUT_LINE('Details of '|| p_empid || ' are' || ' ' || v_name || ' ' || v_mgr || ' ' || v_hiredate || ' ' || v_sal || ' ' || v_comm || ' ' || v_deptno || ' ' || v_sex);
--v_total   :=  v_sal + v_comm;
v_empno   :=  p_empid;
--DBMS_OUTPUT.PUT_LINE('v_total ' ||v_total);
v_dummy   :=  dummy_total(v_empno,v_sal,v_comm);
END try_emp_try;
FUNCTION dummy_total(p_empno IN emp.empno%TYPE,p_sal IN NUMBER,p_comm NUMBER)
RETURN NUMBER
IS
v_sal   NUMBER(10);
v_comm  NUMBER(10);
v_bonus NUMBER(10);
BEGIN
v_sal   :=p_sal;
v_comm  :=p_comm;
v_bonus :=v_sal+(v_sal/10);
IF v_sal>2000 AND v_comm IS NOT NULL THEN
INSERT INTO dummy_bonus VALUES(p_empno,v_bonus);
DBMS_OUTPUT.PUT_LINE('After insert into bonus SINCE v_sal>2000 AND v_comm IS NOT NULL'||p_empno || ' ' ||v_bonus);
ELSIF v_sal>2000  AND v_comm IS NULL THEN
INSERT INTO dummy_bonus VALUES(p_empno,100);
DBMS_OUTPUT.PUT_LINE('After insert into bonus SINCE v_sal>2000 AND v_comm IS NULL'||p_empno || ' ' ||100);
ELSE
DBMS_OUTPUT.PUT_LINE('The total pay of the employee is < 2000 Rupees');
END IF;
RETURN v_sal;
END dummy_total;
END dummy_dummy;
 

Now, the package is built and ready.
I am going to run it now.
The bonus table that i created is dummy_bonus. Initailly, i do not have any values in it. I will run it for two cases.
1.Having >2000
2.Having >2000 and a NULL commision.
[1].

SET SERVEROUTPUT ON
BEGIN
dummy_dummy.try_emp_try(101);
END;
 

The output is,
Details of 101 are JANAKI 7841 09-MAR-10 16765 101 30 F
After insert into bonus SINCE v_sal>2000 AND v_comm IS NOT NULL101 18442.
[2].

SET SERVEROUTPUT ON
BEGIN
dummy_dummy.try_emp_try(111);
END;
 

The output is,
Details of 111 are priya 7841 09-MAR-10 5000 50 F
After insert into bonus SINCE v_sal>2000 AND v_comm IS NULL111 100
NOW, THE TASK IS COMPLETED.
The enteries in the dummy_bonus table which was initaially empty are,
111 100
101 18442
I hope that the above information were useful.
:-)