比较Oracle过程中的两个值

Question

我很不擅长，你能帮我吗？我得看看现金是否>价格和现金从"；portafoglio"；从"；学生；表和价格取自"；costo"；从"；corso"；表，但它给了我几个错误

CREATE OR REPLACE FUNCTION PAGA_CORSO 
(
MAT IN NUMBER 
, COR IN NUMBER 
, DAT IN DATE 
, cash DOUBLE PRECISION
, price DOUBLE PRECISION
) RETURN BOOLEAN AS 
BEGIN
SELECT portafoglio
INTO cash
FROM studente
WHERE matricola=MAT;

SELECT costo
INTO price
FROM corso
WHERE codicecorso=COR;

IF cash >= price THEN
RETURN FALSE;
ELSE
RETURN TRUE;
END IF;
END PAGA_CORSO;

谢谢！

Answer 1

您缺少OUT子句，因为您希望返回值"out"；功能的

SQL> CREATE OR REPLACE FUNCTION PAGA_CORSO
2  (
3    MAT IN NUMBER
4  , COR IN NUMBER
5  , DAT IN DATE
6  , cash DOUBLE PRECISION
7  , price DOUBLE PRECISION
8  ) RETURN BOOLEAN AS
9  BEGIN
10      SELECT sal
11      INTO cash
12      FROM emp
13      WHERE empno=MAT;
14
15      SELECT deptno
16      INTO price
17      FROM dept
18      WHERE deptno=COR;
19
20      IF cash >= price THEN
21              RETURN FALSE;
22          ELSE
23              RETURN TRUE;
24      END IF;
25  END PAGA_CORSO;
26  /
Warning: Function created with compilation errors.
SQL>
SQL> sho err
Errors for FUNCTION PAGA_CORSO:
LINE/COL ERROR
-------- -----------------------------------------------------------------
10/5     PL/SQL: SQL Statement ignored
11/10    PLS-00403: expression 'CASH' cannot be used as an INTO-target of
a SELECT/FETCH statement
12/5     PL/SQL: ORA-00904: : invalid identifier
15/5     PL/SQL: SQL Statement ignored
16/10    PLS-00403: expression 'PRICE' cannot be used as an INTO-target of
a SELECT/FETCH statement
17/5     PL/SQL: ORA-00904: : invalid identifier

现在我们将OUT添加到的定义中

SQL> CREATE OR REPLACE FUNCTION PAGA_CORSO
2  (
3    MAT IN NUMBER
4  , COR IN NUMBER
5  , DAT IN DATE
6  , cash out DOUBLE PRECISION
7  , price out DOUBLE PRECISION
8  ) RETURN BOOLEAN AS
9  BEGIN
10      SELECT sal
11      INTO cash
12      FROM emp
13      WHERE empno=MAT;
14
15      SELECT deptno
16      INTO price
17      FROM dept
18      WHERE deptno=COR;
19
20      IF cash >= price THEN
21              RETURN FALSE;
22          ELSE
23              RETURN TRUE;
24      END IF;
25  END PAGA_CORSO;
26  /
Function created.

此外；双精度"；是罕见的——我建议你可能只想选择NUMBER。