共计 4471 个字符，预计需要花费 12 分钟才能阅读完成。
本篇内容主要讲解“数据库中的记录类型判断非空的办法是什么”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让丸趣 TV 小编来带大家学习“数据库中的记录类型判断非空的办法是什么”吧!
我创建了如下的包：
CREATE OR REPLACE PACKAGE plch_pkg
TYPE info_rt IS RECORD
( name VARCHAR2 (100)
, dob DATE
);
TYPE info_plus_rt IS RECORD
( group_name VARCHAR2 (100)
, group_total NUMBER
, more_info info_rt
);
/哪些选项在执行后将会显示 NOT NULL ?
(A)
DECLARE
l_my_record plch_pkg.info_plus_rt;
BEGIN
l_my_record.group_name := Oracle Bloggers
l_my_record.more_info.dob := SYSDATE;
IF l_my_record IS NOT NULL
THEN
DBMS_OUTPUT.put_line ( NOT NULL
ELSE
DBMS_OUTPUT.put_line ( NULL
END IF;
/SQL DECLARE
2 l_my_record plch_pkg.info_plus_rt;
3 BEGIN
4 l_my_record.group_name := Oracle Bloggers
5 l_my_record.more_info.dob := SYSDATE;
6
7 IF l_my_record IS NOT NULL
8 THEN
9 DBMS_OUTPUT.put_line ( NOT NULL
10 ELSE
11 DBMS_OUTPUT.put_line ( NULL
12 END IF;
13 END;
14 /
DECLARE
l_my_record plch_pkg.info_plus_rt;
BEGIN
l_my_record.group_name := Oracle Bloggers
l_my_record.more_info.dob := SYSDATE;
IF l_my_record IS NOT NULL
THEN
DBMS_OUTPUT.put_line ( NOT NULL
ELSE
DBMS_OUTPUT.put_line ( NULL
END IF;
ORA-06550: 第 7 行, 第 7 列:
PLS-00306: 调用 IS NOT NULL 时参数个数或类型错误
ORA-06550: 第 7 行, 第 4 列:
PL/SQL: Statement ignored
SQL(B)
DECLARE
l_my_record plch_pkg.info_plus_rt;
BEGIN
l_my_record.group_name := Oracle Bloggers
l_my_record.more_info.dob := SYSDATE;
IF l_my_record.group_name IS NOT NULL
OR l_my_record.group_total IS NOT NULL
OR l_my_record.more_info IS NOT NULL
THEN
DBMS_OUTPUT.put_line ( NOT NULL
ELSE
DBMS_OUTPUT.put_line ( NULL
END IF;
/SQL DECLARE
2 l_my_record plch_pkg.info_plus_rt;
3 BEGIN
4 l_my_record.group_name := Oracle Bloggers
5 l_my_record.more_info.dob := SYSDATE;
6
7 IF l_my_record.group_name IS NOT NULL
8 OR l_my_record.group_total IS NOT NULL
9 OR l_my_record.more_info IS NOT NULL
10 THEN
11 DBMS_OUTPUT.put_line ( NOT NULL
12 ELSE
13 DBMS_OUTPUT.put_line ( NULL
14 END IF;
15 END;
16 /
DECLARE
l_my_record plch_pkg.info_plus_rt;
BEGIN
l_my_record.group_name := Oracle Bloggers
l_my_record.more_info.dob := SYSDATE;
IF l_my_record.group_name IS NOT NULL
OR l_my_record.group_total IS NOT NULL
OR l_my_record.more_info IS NOT NULL
THEN
DBMS_OUTPUT.put_line ( NOT NULL
ELSE
DBMS_OUTPUT.put_line ( NULL
END IF;
ORA-06550: 第 9 行, 第 10 列:
PLS-00306: 调用 IS NOT NULL 时参数个数或类型错误
ORA-06550: 第 7 行, 第 4 列:
PL/SQL: Statement ignored
SQL(C)
DECLARE
l_my_record plch_pkg.info_plus_rt;
BEGIN
l_my_record.group_name := Oracle Bloggers
l_my_record.more_info.dob := SYSDATE;
IF l_my_record.group_name IS NOT NULL
OR l_my_record.group_total IS NOT NULL
OR l_my_record.more_info.name IS NOT NULL
OR l_my_record.more_info.dob IS NOT NULL
THEN
DBMS_OUTPUT.put_line ( NOT NULL
ELSE
DBMS_OUTPUT.put_line ( NULL
END IF;
/SQL DECLARE
2 l_my_record plch_pkg.info_plus_rt;
3 BEGIN
4 l_my_record.group_name := Oracle Bloggers
5 l_my_record.more_info.dob := SYSDATE;
6
7 IF l_my_record.group_name IS NOT NULL
8 OR l_my_record.group_total IS NOT NULL
9 OR l_my_record.more_info.name IS NOT NULL
10 OR l_my_record.more_info.dob IS NOT NULL
11 THEN
12 DBMS_OUTPUT.put_line ( NOT NULL
13 ELSE
14 DBMS_OUTPUT.put_line ( NULL
15 END IF;
16 END;
17 /
NOT NULL
PL/SQL procedure successfully completed
SQL(D)
CREATE OR REPLACE FUNCTION plch_not_null_rec (rec_in IN plch_pkg.info_plus_rt)
RETURN BOOLEAN
BEGIN
RETURN rec_in.group_name IS NOT NULL
OR rec_in.group_total IS NOT NULL
OR rec_in.more_info.name IS NOT NULL
OR rec_in.more_info.dob IS NOT NULL;
DECLARE
l_my_record plch_pkg.info_plus_rt;
BEGIN
l_my_record.group_name := Oracle Bloggers
l_my_record.more_info.dob := SYSDATE;
IF plch_not_null_rec (l_my_record)
THEN
DBMS_OUTPUT.put_line ( NOT NULL
ELSE
DBMS_OUTPUT.put_line ( NULL
END IF;
/SQL CREATE OR REPLACE FUNCTION plch_not_null_rec (rec_in IN plch_pkg.info_plus_rt)
2 RETURN BOOLEAN
3 IS
4 BEGIN
5 RETURN rec_in.group_name IS NOT NULL
6 OR rec_in.group_total IS NOT NULL
7 OR rec_in.more_info.name IS NOT NULL
8 OR rec_in.more_info.dob IS NOT NULL;
9 END;
10 /
Function created
SQL DECLARE
2 l_my_record plch_pkg.info_plus_rt;
3 BEGIN
4 l_my_record.group_name := Oracle Bloggers
5 l_my_record.more_info.dob := SYSDATE;
6
7 IF plch_not_null_rec (l_my_record)
8 THEN
9 DBMS_OUTPUT.put_line ( NOT NULL
10 ELSE
11 DBMS_OUTPUT.put_line ( NULL
12 END IF;
13 END;
14 /
NOT NULL
PL/SQL procedure successfully completed
SQL 答案 CD
对记录类型不能使用 IS NOT NULL 判断，否则会出现：PLS-00306: wrong number or types of arguments in call to IS NOT NULL
这导致 AB 出现异常。Steven Feuerstein 建议你采用 D 的做法以便于重用代码。到此，相信大家对“数据库中的记录类型判断非空的办法是什么”有了更深的了解，不妨来实际操作一番吧！这里是丸趣 TV 网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！
正文完
