这些代码都在如何解决此问题上有错误? [英] These bits of code all have errors in how can i fix this?

问题描述

VARCHAR2和(4)旁边有一个错误:

CREATE OR REPLACE FUNCTION Employee_exists
 (p_employee_id IN NUMBER)
RETURN VARCHAR2(4);
AS
BEGIN
        SELECT employee_id
        FROM employees
        WHERE employee_id = p_employee_id;

        RETURN 'true';

END Employee_exists;

在这里说，v_years_service脱节旁边有一个错误:

Here it says there is an error next to the decleration of v_years_service:

CREATE OR REPLACE FUNCTION Calculate_Bonus(p_salary IN NUMBER, p_startdate IN DATE)
RETURN NUMBER -- here it errors
AS
  v_years_service NUMBER;
BEGIN

  v_years_service := TRUNC(Months_Between(SYSDATE, v_startdate) /12);

  IF (v_years_service <2) THEN
        p_bonus := v_salary * 0.02;
  ELSE 
  IF (v_year_service <2) THEN
        p_bonus := v_salary * 0.04;
  ELSE
        p_bonus := v_salary * 0.05;

  RETURN p_bonus;
END IF;
END Calculate_Bonus;
/

show err

推荐答案

对于Employee_exists函数，请更改以下内容:

For the Employee_exists function, change this:

RETURN VARCHAR2(4)

对此:

RETURN VARCHAR

对于Calculate_bonus功能，请移动此行...

For the Calculate_bonus function, move this line...

v_years_service NUMBER;

...所以它在AS行下:

... so it's under the AS line:

CREATE OR REPLACE FUNCTION Calculate_bonus(p_salary IN NUMBER, p_startdate IN DATE)
RETURN NUMBER
AS
  v_years_service NUMBER; --here is where the error appears
BEGIN
  ... and the rest

最后，这是一行:

v_years_service = TRUNC(MONTHS_BETWEEN(SYSDATE, v_startdate) /12);

变量分配始终为:=，因此将其更改为:

The variable assignment is always :=, so change it to this:

v_years_service := TRUNC(MONTHS_BETWEEN(SYSDATE, v_startdate) /12);

---

附录A 还要注意@JoeW在您的问题下的评论. Joe正确地指出，您的ELSE条件将永远不会受到打击.

---

Addendum A Also note the comment from @JoeW under your question. Joe states - correctly - that your ELSE condition will never be hit.

附录B

这是全部功能.您可能还需要对返回值进行四舍五入，因为薪水乘以0.02或0.05可以得到分数美分.四舍五入，用RETURN ROUND(p_bonus, 2);代替RETURN p_bonus;.

Here's the full function. You may also want to round the return value, as a salary multiplied by 0.02 or 0.05 can give fractional cents. To round, substitute RETURN ROUND(p_bonus, 2); for RETURN p_bonus;.

CREATE OR REPLACE FUNCTION Calculate_Bonus(p_salary IN NUMBER, p_startdate IN DATE)
RETURN NUMBER
AS
  v_years_service NUMBER;
  v_bonus NUMBER;
BEGIN
  v_years_service := TRUNC(Months_Between(SYSDATE, p_startdate) /12);
  IF (v_years_service <2) THEN
        v_bonus := p_salary * 0.02;
  ELSE
        v_bonus := p_salary * 0.05;
  END IF;
  RETURN v_bonus;
END Calculate_Bonus;
/

这篇关于这些代码都在如何解决此问题上有错误?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！