S-JIS[2006-01-09/2012-07-07] �ύX����

Oracle����SQL����

�����Ă����Ȃ��ƖY��Ă��܂��悤�ȁAOracle�̂�����Ɩʓ|��SQL�⓮��̃����ł��B

�@ ������̎w����@ " ' & 2007-09-15
�@ NULL�̈��� null 2010-12-29
�֐� �e���֐��E�W�v�֐��E���͊֐� �@ 2010-12-29
DML SELECT�� select 2010-09-01
INSERT�� insert 2010-07-24
UPDATE�� update 2010-07-24
DELETE�� delete 2010-07-24
MERGE�� merge 2010-06-16
WHERE�� where�iexists�Ein�Elike�j 2010-12-29
���₢���킹�̃r���[����` with ���O as 2020-05-12
�r���̃^�C�~���O lock�iSELECT FOR UPDATE�j 2009-01-19
���s�v��E���v����E�q���g autotrace on/traceonly explain 2010-10-23
DDL �\�̈�̍쐬�E�ύX create tablespace 2008-01-14
���[�U�[�̍쐬 create user 2008-04-15
�����̕t�^�E�폜 grant/revoke 2007-09-22
�e�[�u���̍쐬�E�ύX�E�폜 create table/alter table/drop 2012-07-07
����������̒�`�E�m�F constraint 2010-02-19
�C���f�b�N�X�̍쐬�E�m�F create index 2010-07-29
�V�m�j���̍쐬 create synonym 2007-09-22
DB�����N�̍쐬 create database link 2008-10-30
�V�[�P���X�̍쐬 create sequence 2010-12-29
�}�e���A���C�Y�h�r���[�̍쐬 create materialized view 2009-01-17
�p�[�e�B�V�������� partition 2010-07-20
���[�U�[��`�^ create type 2009-05-02
Java�v���O�����̌Ăяo�� create or replace java source 2005-05-15
���T�C�N���r�� �idrop�jflashback/purge 2010-02-19
�@ �Z�b�V�����폜 alter system kill session 2009-03-02
�@ DB�̗e�ʌ��ς��� �@ 2007-10-30

������

Oracle�̕�����i���p���j�̈����ɂ‚��āB[2006-05-26]

�\�� ���e ��
������ �@ ���ʎq�B�p�������ŏ����ƁA�啶���ɕϊ�����Ĉ�����B
select empno from emp;
"������" �_�u���N�H�[�e�[�V���� ���ʎq�B�_�u���N�H�[�e�[�V�����ň͂ނƑ啶���ɕϊ�����Ȃ��B�Ȃ̂ŁA��������n�C�t���Ƃ������L�����g����悤�ɂȂ�B
select "EMPNO" from "EMP";
'������' �V���O���N�H�[�e�[�V���� ������B
SQL> select 'string' from dual;

'STRIN
------
string
&������
&���l
�A���p�T���h �i�f�t�H���g�́j�u���ϐ��̎g�p�B[/2006-07-08] �@
:������ �R���� �o�C���h�ϐ��̎g�p�B[2007-09-15] �@
�@ �G�X�P�[�v���� [2007-06-26] �Ⴆ���u���ϐ��̕������̂��́i�u&�v�j�� �g�������ꍇ�A�G�X�P�[�v������ݒ肵�āA������g�p����B
�i�f�t�H���g�ł̓G�X�P�[�v�̓I�t�i�G�X�P�[�v�͎g�p�ł��Ȃ��B�f�t�H���g�̃G�X�P�[�v�����Ƃ������͖̂����j�j
SQL> set escape '\'
SQL> select '\&str' from dual;

'&ST	��
----	 �u���ϐ��̕��������̂܂܏o��
&str	��
SQL> set escape off		���G�X�P�[�v���I�t�ɂ����
SQL> select '\&str' from dual;
str�ɒl����͂��Ă�������: abc	���u���ϐ��̓��͂����߂���
�� 1: select '\&str' from dual
�V 1: select '\abc' from dual

'\AB
----
\abc
��U�G�X�P�[�v�������w�肷��ƁA���̃G�X�P�[�v�������̂��̂��g���ꍇ���G�X�P�[�v����K�v������B
SQL> set escape "/"	�������OK
SQL> set escape "/"	������x�ڂ̓G���[
SP2-0272: escape�����ɉp�����܂��͋󔒂͎g�p�ł��܂���B
SQL> set escape "//"	���ȑO�̃G�X�P�[�v�����ŃG�X�P�[�v�v
SQL> 

NULL�Ƃ̉��Z

NULL�͉����������Ƃ�\���f�[�^�l�Ȃ̂ŁA����������B[2007-09-23]

NULL���l�����Z���s���ƁA���ʂ�NULL�ɂȂ�B[/2008-04-29]
Oracle�ł͋󕶎���u''�v��NULL�Ƃ��Ĉ�����B������̌������s���ꍇ�͋󕶎���Ƃ��Ĉ����A���̕����񂻂̂܂܂ƂȂ�B[2010-01-27]
WHERE�ŏ������f�i��r���Z�j�Ɏg���Ă����ꍇ�ANULL�Ƃ̉��Z�i�uIS NULL�v�ȊO�j�́u�U�ifalse�j�v�ƂȂ�B[/2010-12-29]

�uORDER BY�v�̃\�[�g���ڂł́ANULL�͍ő�l�Ƃ��Ĉ�����B�iASC�ł͖����ADESC�ł͐擪�ɂȂ�j
�uorder by ���� nulls first�v�Ƃ����w��������NULL���擪�ɗ���悤�ɂȂ�B[2007-09-28]
���l�Ɂunulls last�v�Ŗ����ɗ���悤�ɂȂ�B[2008-04-29]

�uGROUP BY�v�Ŏg���W�v�֐��iCOUNT�EMIN�EMAX��SUM���j�ł��ANULL�͖��������B
NULL��ORDER BY�ōő�l�Ƃ��Ĉ����Ă��邪�AMAX�ł͍ő�l�ɂȂ�Ȃ��B�i�S����NULL�Ȃ�NULL�ɂȂ����Ⴄ���ǁc�j

[/2008-04-29] �� ���� ���l
NULL���̂��� SET NULL '*NULL*'
SELECT NULL FROM DUAL;
*NULL* �@
NULL�Ƃ̉��Z SELECT 1 + NULL FROM DUAL; *NULL* NULL�Ƃ̉��Z���ʂ�NULL�ɂȂ�B
�����񌋍� SELECT 'ABC' || '' FROM DUAL;
SELECT 'ABC' || NULL FROM DUAL;
ABC �����񌋍��ł�NULL�͋󕶎��񈵂��B[2010-01-27]
SELECT NULL || NULL FROM DUAL; *NULL* �󕶎����NULL���������B
SELECT '' || '' FROM DUAL; *NULL*
SELECT '' FROM DUAL; *NULL*
�W�v�֐� SELECT MAX(NULL) FROM DUAL; *NULL* �Ώۂ��S��NULL�̏ꍇ��NULL�ɂȂ�B
SELECT COUNT(NULL) FROM DUAL; 0 �J�E���g��NULL����0���i�ΏۂȂ��j�ɂȂ�B
WHERE���� SELECT 'TRUE' FROM DUAL WHERE NULL = 0; �������� NULL�Ƃ̔�r���Z�͏�ɋU�B
���ɁuNULL=NULL�v���U�ɂȂ�̂������B
NULL���ǂ�����m�肽���ꍇ�́uIS NULL�v
NULL�łȂ����Ƃ́uIS NOT NULL�v���g���B
SELECT 'TRUE' FROM DUAL WHERE NULL <> 0; ��������
SELECT 'TRUE' FROM DUAL WHERE NULL = NULL; ��������
SELECT 'TRUE' FROM DUAL WHERE NULL <> NULL; ��������
SELECT 'TRUE' FROM DUAL WHERE NULL IS NULL; TRUE
SELECT 'TRUE' FROM DUAL WHERE NULL IS NOT NULL; ��������
SELECT 'TRUE' FROM DUAL WHERE 'AA' IS NOT NULL; TRUE
�󕶎����r SELECT 'TRUE' FROM DUAL WHERE 'ABC' <> ''; �������� �󕶎����NULL���������B
�u=''�v��u<>''�v�Ŕ�r�ł��Ȃ����Ƃ������B
SELECT 'TRUE' FROM DUAL WHERE '' <> ''; ��������
SELECT 'TRUE' FROM DUAL WHERE '' = ''; ��������
SELECT 'TRUE' FROM DUAL WHERE '' IS NULL; TRUE
�\�[�g SELECT * FROM (
 SELECT 'VWXYZ'  FROM DUAL UNION
 SELECT NULL     FROM DUAL UNION
 SELECT 'ABCDEF' FROM DUAL
) ORDER BY 1 ASC;
ABCDEF
VWXYZ
*NULL*
�uNULLS�v�̎w������Ȃ��ƁA
NULL�͍ő�l�ł��邩�̂��Ƃ�������B
�uNULLS�v���w�肷��ƁA
�����E�~���Ɋւ�炸 �w��ɏ]���B
SELECT * FROM (
 SELECT 'VWXYZ'  FROM DUAL UNION
 SELECT NULL     FROM DUAL UNION
 SELECT 'ABCDEF' FROM DUAL
) ORDER BY 1 ASC NULLS FIRST;
*NULL*
ABCDEF
VWXYZ
SELECT * FROM (
 SELECT 'VWXYZ'  FROM DUAL UNION
 SELECT NULL     FROM DUAL UNION
 SELECT 'ABCDEF' FROM DUAL
) ORDER BY 1 DESC NULLS FIRST;
*NULL*
VWXYZ
ABCDEF
SELECT * FROM (
 SELECT 'VWXYZ'  FROM DUAL UNION
 SELECT NULL     FROM DUAL UNION
 SELECT 'ABCDEF' FROM DUAL
) ORDER BY 1 DESC NULLS LAST;
VWXYZ
ABCDEF
*NULL*

�Q�l�F


DB�̗e�ʌ��ς���

�e�[�u�����̗e�ʌ��ς�����v�Z���Ă����y�[�W��OTN�ɂ���炵���B[2007-10-30]


�Q�l�����N


Oracle�ڎ��֖߂� / �Z�p�����֖߂�
���[���̑��M��F�Ђ�����

�@

�@

�@

�@

�@

�@

�@

�@

�@

�@

�@

�@

�@

�@

�@

�@