oracle 061

Several implicit default options are selected when you use the ORDER BY clause. The most important of these is that unless DESC is specified, the sort order is assumed to be ascending. If null values occur in the sort column, the default sort order is assumed to be NULLS LAST for ascending sorts and NULLS FIRST for descending sorts
multiple NULL values can coexist in a column defined UNIQUE.
Any arithmetic calculation with a NULL value always returns NULL.
. The character concatenation operators ignore null, while the arithmetic operations involving null values always result in null.

A useful technique is to enclose the ampersand substitution variable in single quotes when dealing with character and date values

```sql
```


```sql
select TO_DATE('2019-04-16 00:00:01', 'YYYY-MM-DD HH24:MI:SS') -
TO_DATE('2019-04-15 00:00:00', 'YYYY-MM-DD HH24:MI:SS') as days
from dual

1.0000115740740740740740740740741

select Months_between(SYSDATE+61, SYSDATE) from dual;
MONTHS_BETWEEN(SYSDATE+61,SYSDATE)
1.96774193548387096774193548387096774194

select round(TO_DATE('04-JUL-19 11:59:59', 'DD-MON-RR HH24:MI:SS'), 'w') from dual;
01-JUL-19

select round(TO_DATE('04-JUL-19 12:00:00', 'DD-MON-RR HH24:MI:SS'), 'w') from dual;
08-JUL-19

```

```sql

SELECT 'Coda''''s favorite fetch toy is his orange ring' FROM DUAL;

Coda''s favorite fetch toy is his orange ring
```

```sql
CREATE TABLE "TEST" 
   (	"COLUMN1" VARCHAR2(20 BYTE) DEFAULT TO_CHAR("SEQ_1"."NEXTVAL")
   ) 

Table "TEST"は作成されました。
```

```sql
select &rest_of_statement;
```

```sql
ORDER BY 3 DESC NULLS LAST, 2 ASC;
```
not in (null) NEVER BE TRUE!!

```sql
select 1 as "a" from dual where 1  in (1, null);
a
1

select 1 from dual where 1  not in (2, null);
no data found
```

When using ampersand substitution variables in the following query, how many times will you be prompted to input a value for the variable called JOB the first time this query is executed?

SELECT FIRST_NAME, '&JOB'

FROM EMPLOYEES

WHERE JOB_ID LIKE '%'||&JOB||'%'

AND ‘&&JOB’ BETWEEN 'A' AND 'Z';

A.  0

B.  1

C.  2

D.  3

D. The first time this statement is executed, two single ampersand substitution variables are encountered before the third double ampersand substitution variable. If the first reference on line one of the query contained a double ampersand substitution, you would only be prompted to input a value once.

image   A, B, and C are incorrect since you are prompted thrice to input a value for the JOB substitution variable. In subsequent executions of this statement in the same session, you will not be prompted to input a value for this variable.


Implicit character to date conversions are possible when the character string conforms to the following date patterns: [D|DD] separator1 [MON|MONTH] separator2 [R|RR|YYYY]

```sql
select replace( to_char( TO_DATE('01-FEB-19'), 'DAY-MONTH-RR'), ' ','*') from dual;
FRIDAY***-FEBRUARY*-19

select to_char( TO_DATE('01-FEB-19'), 'DdSPTH-MONTH-RR') from dual;
First-FEBRUARY -19

select TO_CHAR(Sysdate, 'Yyyysp') from dual;
Two Thousand Nineteen

select TO_CHAR(Sysdate, 'Year') from dual;
Twenty Nineteen

fm是填充模式
如果是字符格式化，指定fm参数后将仅返回指定属性实际所占长度(不再以空格填充)。
如果是数值格式化，fm参数会自动舍弃被格式化元素的前置0。


--序数方式显示，并且fm方式显示,会把多余的前缀0和多余的空格去掉。
SQL> SELECT TO_CHAR(SYSDATE, 'fmDDTH') || ' of ' ||
  2         TO_CHAR(SYSDATE, 'fmMonth') || ', ' ||
  3         TO_CHAR(SYSDATE, 'YYYY') "Ides" 
  4    FROM DUAL; 

Ides
-----------
7TH of May, 2014

select TO_DATE('2010','YYYY') from dual;
01-JUL-10


select TO_NUMBER(123.56,'999.99') from dual;
123.56

select TO_NUMBER(123.56,'999.9') from dual;
ORA-01722: invalid number

TO_CHAR(123.56,'999.9') returns 123.6.

select NULLIF(1, '2') from dual;
ORA-00932: inconsistent datatypes: expected NUMBER got CHAR

select NULLIF(1, 2) from dual;
1

select COALESCE(1, '1') from dual;
ORA-00932: inconsistent datatypes: expected NUMBER got CHAR

select case 1 when 1 then 'a' end from dual;
a

select case 1 when '1' then 'a' end from dual;
ORA-00932: inconsistent datatypes: expected NUMBER got CHAR

select decode('1', 1, 'aaaa') from dual;
aaaa

select nvl('1', 1) from dual;
1

select nvl2('1', 2, 'a') from dual;
ORA-01722: invalid number

select * from dual
DUMMY
X
The DUAL table has one row and one column

create table T1 ( D1 date,
check(D1 > sysdate) );
ORA-02436: date or system variable wrongly specified in CHECK constraint 
A check constraint, unfortunately, cannot reference a function like SYSDATE. You would need to create a trigger that checked these values when DML occurs

```
LONGと定義されている列には、最大2GBまでの情報を含む可変長の文字データを格納できます

```sql
create table T1 ( C1 char, c2 char, 
unique (c1,c2) )

C1	C2
1	2
1	 -  
2	 - 
 - 	 - 
 - 	 - 

select * from t1 where c1 in (c2);
C1	C2
1	1
```

Oracle Database implicitly commits the current transaction before and after every DDL statement.