OCP考试051题库1、2、8题笔记

1.关于coalesce函数：

功能 ：
返回列表中的第一个非空表达式。

 

注意的地方：

1）  如果所有参数均为 NULL，则 COALESCE 返回 NULL。 

2）  所有的参数的数据类型必须完全相同。

 

2.decode函数

Syntax

The syntax for the decode function is:

decode( expression , search , result [, search , result]... [, default] )

expression is the value to compare.

search is the value that is compared against expression.

result is the value returned, if expression is equal to search.

default is optional. If no matches are found, the decode will return default. If default is omitted, then the decode statement will return null (if no matches are found).

Question: One of our viewers wanted to know how to use the decode function to compare two dates (ie: date1 and date2), where if date1 > date2, the decode function should return date2. Otherwise, the decode function should return date1.

Answer: To accomplish this, use the decode function as follows:

decode((date1 - date2) - abs(date1 - date2), 0, date2, date1)

The formula below would equal 0, if date1 is greater than date2:

(date1 - date2) - abs(date1 - date2)

Helpful Tip: One of our viewers suggested combining the  with the decode function as follows:

The date example above could be modified as follows:

DECODE(SIGN(date1-date2), 1, date2, date1)

The SIGN/DECODE combination is also helpful for numeric comparisons e.g. Sales Bonuses

DECODE(SIGN(actual-target), -1, 'NO Bonus for you', 0,'Just made it', 1, 'Congrats, you are a winner')

---

Question: I would like to know if it's possible to use decode for ranges of numbers, ie 1-10 = 'category 1', 11-20 = 'category 2', rather than having to individually decode each number.

Answer: Unfortunately, you can not use the decode for ranges of numbers. However, you can try to create a formula that will evaluate to one number for a given range, and another number for the next range, and so on.

For example:

SELECT supplier_id,

decode(trunc ((supplier_id - 1) / 10), 0, 'category 1',

                                       1, 'category 2',

                                       2, 'category 3',

                                       'unknown') result

FROM suppliers;

In this example, based on the formula:

trunc ((supplier_id - 1) / 10

The formula will evaluate to 0, if the supplier_id is between 1 and 10.
The formula will evaluate to 1, if the supplier_id is between 11 and 20.
The formula will evaluate to 2, if the supplier_id is between 21 and 30.

and so on...

---

Question: I need to write a decode statement that will return the following:

If yrs_of_service < 1 then return 0.04

If yrs_of_service >= 1 and < 5 then return 0.04

If yrs_of_service > 5 then return 0.06

How can I do this?

Answer: You will need to create a formula that will evaluate to a single number for each one of your ranges.

For example:

SELECT emp_name,

decode(trunc (( yrs_of_service + 3) / 4), 0, 0.04,

                                          1, 0.04,

                                          0.06) as perc_value

FROM employees;

---

Question: Is there a limit to the number of arguments that you can have in one DECODE statement? I'm getting an error, "ORA-00939: too many arguments for function".

Answer: Yes, the maximum number of components that you can have in a decode function is 255. This includes the expression, search, and result arguments.

8.instr函数，substr函数

INSTR方法的格式为
INSTR(源字符串, 目标字符串, 起始位置, 匹配序号)

例如：INSTR('CORPORATE FLOOR','OR', 3, 2)中，源字符串为'CORPORATE FLOOR', 目标字符串为'OR'，起始位置为3，取第2个匹配项的位置。

默认查找顺序为从左到右。当起始位置为负数的时候，从右边开始查找。

所以SELECT INSTR('CORPORATE FLOOR', 'OR', -1, 1) "Instring" FROM DUAL的显示结果是

Instring
——————
14

Syntax

The syntax for the substr function is:

substr( string, start_position, [ length ] )

string is the source string.

start_position is the position for extraction. The first position in the string is always 1.

length is optional. It is the number of characters to extract. If this parameter is omitted, substr will return the entire string.

Note

If start_position is 0, then substr treats start_position as 1 (ie: the first position in the string).

If start_position is a positive number, then substr starts from the beginning of the string.

If start_position is a negative number, then substr starts from the end of the string and counts backwards.

If length is a negative number, then substr will return a NULL value.

Applies To

·         Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

For Example

substr('This is a test', 6, 2)	would return 'is'
substr('This is a test', 6)	would return 'is a test'
substr('TechOnTheNet', 1, 4)	would return 'Tech'
substr('TechOnTheNet', -3, 3)	would return 'Net'
substr('TechOnTheNet', -6, 3)	would return 'The'
substr('TechOnTheNet', -8, 2)	would return 'On'