sql - 将表中的单个值分配给 ORACLE PL/SQL 中声明的变量时出错
问题描述
我正在为 Oracle DB 10g/11g 编写程序,我需要为变量分配特定的日期值。代码如下。
DECLARE
date_from DATE := NULL;
DECLARE
d_count NUMBER := 0;
BEGIN
SELECT COUNT(*) INTO d_count FROM TABLE_X;
IF d_count = 0 THEN
SELECT MIN(TRX_DATE) INTO date_from FROM TABLE_Y;
ELSE
SELECT MAX(TRX_DATE) INTO date_from FROM TABLE_X;
END IF;
END;
当我运行它时,我收到了这个错误。
Error starting at line : 1 in command -
DECLARE
date_from DATE := NULL;
DECLARE
d_count NUMBER := 0;
BEGIN
SELECT COUNT(*) INTO d_count FROM TABLE_X;
IF d_count = 0 THEN
SELECT MIN(TRX_DATE) INTO date_from FROM TABLE_Y;
ELSE
SELECT MAX(TRX_DATE) INTO date_from FROM TABLE_X;
END IF;
END;
Error report -
ORA-06550: line 3, column 1:
PLS-00103: Encountered the symbol "DECLARE" when expecting one of the following:
begin function pragma procedure subtype type <and identifier>
<a double-quoted delimited-identifier> current cursor delete
exist prior
The symbol "begin" was substituted for "DECLARE" to continue.
ORA-06550: line 13, column 4:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:
( begin case declare and exception exit for goto if loop mod
null pragma raise return select update while with
<an identifier><a double-quoted
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usuallz a PL/SQL compilation error.
*Action:
我有 T-SQL 经验,但对 OracleDB 中的 PL/SQL 非常陌生。提前致谢。
解决方案
您使用了额外的声明。这应该没问题:
DECLARE
date_from DATE := NULL;
d_count NUMBER := 0;
BEGIN
SELECT COUNT(*) INTO d_count FROM TABLE_X;
IF d_count = 0 THEN
SELECT MIN(TRX_DATE) INTO date_from FROM TABLE_Y;
ELSE
SELECT MAX(TRX_DATE) INTO date_from FROM TABLE_X;
END IF;
END;
脚本的基本模板:
declare
-- Local variables here
i integer;
begin
-- statements here
end;
程序的基本模板:
procedure TEST(Name in out type, Name in out type, ...) is
begin
end TEST;
推荐阅读
- android - Android:更新模拟版本后单元测试失败
- python - 'fieldsets[4][1]' Django 中有重复的字段
- java - Java Soap WS 从请求数据中获取空值
- java - 改造多个 JSON 响应
- javascript - 带有气泡菜单扩展的 Tiptap
- python - 随机种子在导入 Salabim 时给出相同的值
- java - 在 Java 方法声明中的方法名称后插入换行符
- java - 更新 minSDK 到 23 后,作为 Jitsi 中的 minSDK(在当前应用中使用),Edittext 字段无法正常工作
- sorting - Grafana 8排序图例
- junit - SpigotMC java.lang.NoSuchMethodError: org.yaml.snakeyaml.LoaderOptions.setMaxAliasesForCollections(I)V