数据库中的记录类型判断非空的办法是什么

本篇内容主要讲解"数据库中的记录类型判断非空的办法是什么"，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习"数据库中的记录类型判断非空的办法是什么"吧!

我创建了如下的包：

CREATE OR REPLACE PACKAGE plch_pkgIS   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 ignoredSQL>

(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 ignoredSQL>

(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 NULLPL/SQL procedure successfully completedSQL>

(D)

CREATE OR REPLACE FUNCTION plch_not_null_rec (rec_in IN plch_pkg.info_plus_rt)   RETURN BOOLEANISBEGIN   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 createdSQL> 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 NULLPL/SQL procedure successfully completedSQL>

答案 CD对记录类型不能使用IS NOT NULL判断，否则会出现：PLS-00306: wrong number or types of arguments in call to 'IS NOT NULL'这导致AB出现异常。Steven Feuerstein建议你采用D的做法以便于重用代码。

到此，相信大家对"数据库中的记录类型判断非空的办法是什么"有了更深的了解，不妨来实际操作一番吧！这里是网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！