本篇内容主要讲解“数据库中的记录类型判断非空的办法是什么”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“数据库中的记录类型判断非空的办法是什么”吧!
我创建了如下的包：
CREATE OR REPLACE PACKAGE plch_pkg
IS
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
);
END;
/哪些选项在执行后将会显示 "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;
END;
/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;
END;
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;
END;
/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;
END;
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;
END;
/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
IS
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;
END;
/
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;
END;
/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的做法以便于重用代码。到此，相信大家对“数据库中的记录类型判断非空的办法是什么”有了更深的了解，不妨来实际操作一番吧！这里是亿速云网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！
