本篇内容主要讲解“数据库中的记录类型判断非空的办法是什么”,感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷,实用性强。下面就让小编来带大家学习“数据库中的记录类型判断非空的办法是什么”吧!
我创建了如下的包:
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的做法以便于重用代码。
到此,相信大家对“数据库中的记录类型判断非空的办法是什么”有了更深的了解,不妨来实际操作一番吧!这里是亿速云网站,更多相关内容可以进入相关频道进行查询,关注我们,继续学习!
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。