How to call the function in PL/SQL?-CodePudding

This is my code for PL/SQL.

CREATE OR REPLACE FUNCTION t_sal(p_emp in employees.employee_id%type)
                                     
RETURN NUMBER IS
    v_salary employees.salary%type;
    v_comm employees.commission_pct%type;   
    CURSOR c_get_salary IS
    SELECT salary from employees
    where employee_id=p_emp;
BEGIN

    OPEN c_get_salary;
    FETCH c_get_salary into
    v_salary;
    

    CLOSE c_get_salary;
    v_salary:=v_salary   (v_salary * v_comm);
    RETURN v_salary;
END t_sal;
/

This is how I called my function

SET SERVEROUTPUT ON;
DECLARE
    v_employeeid employees.employee_id%type:=102;
    v_a number(20);
BEGIN   
    v_a:=t_sal(v_employeeid);
    dbms_output.put_line('Total salary is: ' || v_a);     
END;
/

Whenever I run it, it does not show the total salary based on the computation

CodePudding user response：

Of course it doesn't display anything; you're multiplying something with a NULL which results in NULL.

SQL> CREATE TABLE employees
  2  AS
  3     SELECT 102 employee_id, 100 salary, 5 commission_pct FROM DUAL;

Table created.

SQL> CREATE OR REPLACE FUNCTION t_sal (p_emp IN employees.employee_id%TYPE)
  2     RETURN NUMBER
  3  IS
  4     v_salary  employees.salary%TYPE;
  5     v_comm    employees.commission_pct%TYPE;
  6
  7     CURSOR c_get_salary IS
  8        SELECT salary, commission_pct           --> missing commission_pct
  9          FROM employees
 10         WHERE employee_id = p_emp;
 11  BEGIN
 12     OPEN c_get_salary;
 13
 14     FETCH c_get_salary INTO v_salary, v_comm;  --> missing v_comm
 15
 16
 17     CLOSE c_get_salary;
 18
 19     v_salary := v_salary   (v_salary * v_comm);
 20     RETURN v_salary;
 21  END t_sal;
 22  /

Function created.

Testing:

SQL> SET SERVEROUTPUT ON;
SQL>
SQL> DECLARE
  2     v_employeeid  employees.employee_id%TYPE := 102;
  3     v_a           NUMBER (20);
  4  BEGIN
  5     v_a := t_sal (v_employeeid);
  6     DBMS_OUTPUT.put_line ('Total salary is: ' || v_a);
  7  END;
  8  /
Total salary is: 600

PL/SQL procedure successfully completed.

SQL>

Now you got the result but - in my opinion - it is wrong. Shouldn't you be dividing the commission percentage by 100?

v_salary := v_salary   (v_salary * v_comm/100);

Then you'd get 105 as result (according to my sample data), which looks way better.