### 加入dbDao.com Oracle技术学习QQ群：171092051

## 如果自己搞不定可以找诗檀软件专业ORACLE数据库修复团队成员帮您恢复!

## 诗檀软件专业数据库修复团队

## 服务热线 ： 400-690-3643 备用电话： 13764045638 邮箱：service@parnassusdata.com

I try to use subtypes as much as I can and after reading the “All Things Oracle” article of Patrick Barel on PL/SQL subtypes, I want to add something to his article about the use of subtypes.

Booleans are a PL/SQL datatype and doesn’t exist in SQL. This means that when your PL/SQL function returns a boolean, you can’t use it in SQL.

Here’s an example…

I create a package with a function that returns a BOOLEAN.

CREATE OR REPLACE PACKAGE test_boolean IS FUNCTION is_equal( p_value_1 IN NUMBER , p_value_2 IN NUMBER ) RETURN BOOLEAN; END test_boolean; / CREATE OR REPLACE PACKAGE BODY test_boolean IS FUNCTION is_equal( p_value_1 IN NUMBER , p_value_2 IN NUMBER ) RETURN BOOLEAN IS l_return BOOLEAN; BEGIN IF p_value_1 = p_value_2 THEN l_return := TRUE; ELSE l_return := FALSE; END IF; RETURN l_return; END is_equal; END test_boolean; /

Easy to use in PL/SQL:

BEGIN IF test_boolean.is_equal(1,1) THEN dbms_output.put_line('The parameters are equal.'); END IF; END; / The parameters are equal.

But when you use this in SQL, for example in a query:

SELECT test_boolean.is_equal(1,1) FROM dual;

You’ll receive some errors:

ORA-06552: PL/SQL: Statement ignored ORA-06553: PLS-382: expression is of wrong type

When you want to use the function also in SQL, you usualy create a wrapper function.

CREATE OR REPLACE PACKAGE test_boolean IS FUNCTION is_equal( p_value_1 IN NUMBER , p_value_2 IN NUMBER ) RETURN BOOLEAN; FUNCTION is_equal_num( p_value_1 IN NUMBER , p_value_2 IN NUMBER ) RETURN NUMBER; END test_boolean; / CREATE OR REPLACE PACKAGE BODY test_boolean IS FUNCTION is_equal( p_value_1 IN NUMBER , p_value_2 IN NUMBER ) RETURN BOOLEAN IS l_return BOOLEAN; BEGIN IF p_value_1 = p_value_2 THEN l_return := TRUE; ELSE l_return := FALSE; END IF; RETURN l_return; END is_equal; FUNCTION is_equal_num( p_value_1 IN NUMBER , p_value_2 IN NUMBER ) RETURN NUMBER IS l_return NUMBER(1); BEGIN IF is_equal(p_value_1, p_value_2) THEN l_return := 1; ELSE l_return := 0; END IF; RETURN l_return; END is_equal_num; END test_boolean; /

This one still works:

BEGIN IF test_boolean.is_equal(1,1) THEN dbms_output.put_line('The parameters are equal.'); END IF; END; / The parameters are equal.

But now this one is also working:

SELECT test_boolean.is_equal_num(1,1) is_equal FROM dual; IS_EQUAL -------- 1

Instead of using two different functions, just combine these two in one. The “is_equal_num” function will work for both PL/SQL and SQL, but you can’t use “IF test_boolean.is_equal_num(1,1) THEN” anymore, so you’ll have to compare with a value. You’ll have to code “IF test_boolean.is_equal_num(1,1) = 1 THEN”.

Of course, you don’t need to use a NUMBER datatype, you can use any SQL datatype, e.g. VARCHAR2(1) with values ‘Y’ and ‘N’. It doesn’t really matter which you are using, but it’s a good idea to have a standard. And this standard can be defined using subtypes.

Here’s what I do…

I create a package in which I define my subtypes, but I also define constants to use with this subtype. For booleans there are always two possible values: true or false, 1 or 0, ‘Y’ or ‘N’, …

This is what my package looks like:

CREATE OR REPLACE PACKAGE subtypes IS SUBTYPE st_boolean IS pls_integer range 0..1; gc_boolean_false CONSTANT st_boolean := 0; gc_boolean_true CONSTANT st_boolean := 1; END; /

I changed my function in my package to use my own booleans from the subtypes package:

CREATE OR REPLACE PACKAGE test_boolean IS FUNCTION is_equal( p_value_1 IN NUMBER , p_value_2 IN NUMBER ) RETURN subtypes.st_boolean; END test_boolean; / CREATE OR REPLACE PACKAGE BODY test_boolean IS FUNCTION is_equal( p_value_1 IN NUMBER , p_value_2 IN NUMBER ) RETURN subtypes.st_boolean IS l_return subtypes.st_boolean; BEGIN IF p_value_1 = p_value_2 THEN l_return := subtypes.gc_boolean_true; ELSE l_return := subtypes.gc_boolean_false; END IF; RETURN l_return; END is_equal; END test_boolean; /

Both the test cases will work without a wrapper function(all though now we’ll have to compare in PL/SQL):

BEGIN IF test_boolean.is_equal(1,1) = subtypes.gc_boolean_true THEN dbms_output.put_line('The parameters are equal.'); END IF; END; / The parameters are equal.

SELECT test_boolean.is_equal(1,1) is_equal FROM dual; IS_EQUAL -------- 1

When you want to change the subtypes, you don’t have to change any code besides the subtypes package:

CREATE OR REPLACE PACKAGE subtypes IS SUBTYPE st_boolean IS VARCHAR2(1); gc_boolean_false CONSTANT st_boolean := 'N'; gc_boolean_true CONSTANT st_boolean := 'Y'; END; /

BEGIN IF test_boolean.is_equal(1,1) = subtypes.gc_boolean_true THEN dbms_output.put_line('The parameters are equal.'); END IF; END; / The parameters are equal.

But this time in SQL the outcome is a VARCHAR2(1) and contains ‘Y’:

SELECT test_boolean.is_equal(1,1) is_equal FROM dual; IS_EQUAL -------- Y

You can also use a DATE datatype for your boolean. Give it a try!

(But you shouldn’t use a date in a “real” application environment, it’s not the most suitable datatype)