這是我的 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;
/
這就是我呼叫函式的方式
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;
/
每當我運行它時，它都不會顯示基于計算的總工資
uj5u.com熱心網友回復：
當然它什么也不顯示；您將某些內容與 NULL 相乘，結果為 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.
測驗：
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>
現在你得到了結果，但是 - 在我看來 - 這是錯誤的。您不應該將傭金百分比除以 100 嗎？
v_salary := v_salary (v_salary * v_comm/100);
然后你會得到105結果（根據我的樣本資料），看起來好多了。
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/533105.html
標籤：sql甲骨文plsql
上一篇：SQL計數命令查詢