忽略 PL/SQL 语句:缺少表达式

Question

我有一个带有 3 个表的小数据库：帐户、account_statements和帐户操作。我需要知道：

1. 该期间的所有信贷业务

2. 佩里昂的所有Debet操作

3. 任何日期的余额

我的脚本是：

CREATE TABLE account 
(
id number(10) NOT NULL,
account number(20) NOT NULL,
CONSTRAINT account_id PRIMARY KEY (id)
);

CREATE TABLE account_statements
(
id number(10) NOT NULL,
account_id number(10) NOT NULL,
statement_date date,
inbalance number(20,2),
outbalance number (20,2),
CONSTRAINT statement_id PRIMARY KEY (id),
CONSTRAINT account_statements_foreign_id FOREIGN KEY (account_id) REFERENCES account(id)
);

CREATE TABLE account_operations
(
id number(10) NOT NULL,
account_id number(10) NOT NULL,
operdate date,
summ number(20,2),
opertype char(6),
CONSTRAINT operations_id PRIMARY KEY (id),
CONSTRAINT account_operations_foreign_id FOREIGN KEY (account_id) REFERENCES account(id)
);
CREATE OR REPLACE FUNCTION Get_debet_on_period (
v_startdate IN date,
v_enddate IN date,
v_account IN account.account%TYPE)
RETURN number
IS
v_debet_summ number(20,2);
BEGIN
SELECT SUM(summ) INTO v_debet_summ
FROM account_operations ao,
account a
WHERE ao.operdate between v_startdate AND v_enddate
AND ao.opertype='DEBET'
AND a.account=v_account
AND ao.account_id=a.id;
RETURN v_debet_summ;
END;
CREATE OR REPLACE FUNCTION Get_credit_on_period (
v_startdate IN date,
v_enddate IN date,
v_account IN account.account%TYPE)
RETURN number
IS
v_credit_summ number(20,2);
BEGIN
SELECT SUM(summ) INTO v_credit_summ
FROM account_operations ao,
account a
WHERE ao.operdate between v_startdate AND v_enddate
AND ao.opertype='CREDIT'
AND a.account=v_account
AND ao.account_id=a.id;
RETURN v_credit_summ;
END;
CREATE OR REPLACE FUNCTION Get_balance_on_date (
v_date IN date,
v_account IN account.account%TYPE)
RETURN number
IS 
v_balance_summ number(20,2);
v_startdate date; 
v_startsumm number;
BEGIN
SELECT MAX(as.statement_date) INTO v_startdate
FROM account_statements as, account a
WHERE a.id=as.account_id
AND a.account=v_account
AND as.statement_date<v_date;
IF v_startdate IS NOT NULL THEN
SELECT as.outbalance INTO v_startsumm
FROM account_statement as, account a
WHERE a.id=as.account_id
AND a.account=v_account
AND as.statement_date=v_statement_date;
v_balance_summ:=v_startsumm+Get_credit_on_period(v_startdate, v_date, v_account)-Get_debet_on_period(v_startdate, v_date, v_account);
ELSE
v_startsumm:=0;
v_balance_summ:=Get_credit_on_period(v_startdate, v_date, v_account)-Get_debet_on_period(v_startdate, v_date, v_account);
END IF;
RETURN v_balance_summ;
END;

我在函数Get_balance_on_date中有一个错误：

错误： 函数 GET_BALANCE_ON_DATE 行/列： 10/4 PL/SQL： SQL 忽略语句 行/列： 10/15 PL/SQL： ORA-00936： 缺失 表达式 行/列： 16/7 PL/SQL： SQL 语句忽略 行/列： 16/14 PL/SQL： ORA-00936： 缺少表达式

Answer 1

你的函数get_balance_on_date不编译。

1. 您正在使用表account_statement，但是您创建了以复数形式命名的表account_statements。
2. 您使用保留关键字AS作为别名"account_statements为"。您应该将别名更改为其他名称。
3. 您正在使用未声明的变量"v_statement_date"。

为您修复的功能：

CREATE OR REPLACE FUNCTION get_balance_on_date
(
v_date    IN DATE
,v_account IN account.account%TYPE
) RETURN NUMBER IS
v_balance_summ NUMBER(20, 2);
v_startdate    DATE;
v_startsumm    NUMBER;
v_statement_date DATE; -- Remove this if you don't need, created this for function to compile
BEGIN
SELECT MAX(stm.statement_date)
INTO v_startdate
FROM account_statements stm
,account            a
WHERE a.id = stm.account_id
AND a.account = v_account
AND stm.statement_date < v_date;
IF v_startdate IS NOT NULL
THEN
SELECT stm.outbalance
INTO v_startsumm
FROM account_statements stm
,account           a
WHERE a.id = stm.account_id
AND a.account = v_account
AND stm.statement_date = v_statement_date;
v_balance_summ := v_startsumm +
get_credit_on_period(v_startdate, v_date, v_account) -
get_debet_on_period(v_startdate, v_date, v_account);
ELSE
v_balance_summ := get_credit_on_period(v_startdate, v_date, v_account) -
get_debet_on_period(v_startdate, v_date, v_account);
END IF;
RETURN v_balance_summ;
END;

Answer 2

您使用保留关键字作为别名

FROM account_statements as, account a

as必须更改为其他内容，因为它是SQL语言的一部分。