SQL_Commands/comandos_aula.sql at master · guerrasz/SQL_Commands · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96


--
-- Tabela DUAL, interna do SGBDR Oracle
--
SELECT * FROM DUAL;

SELECT TO_CHAR(TO_DATE( '21/05/1882', 'DD/MM/YYYY'),'DAY')
FROM  T_SIP_DEPTO;

CREATE SEQUENCE SQ_T_SIP_DEPTO
START WITH 16
INCREMENT BY 1
MAXVALUE 20
NOCYCLE;

INSERT INTO T_SIP_DEPTO( CD_DEPTO, NM_DEPTO, SG_DEPTO, ST_DEPTO)
VALUES (SQ_T_SIP_DEPTO.NEXTVAL, 'Marketing Digital', 'DMG', 'A');

SELECT SQ_T_SIP_DEPTO.CURRVAL FROM DUAL;

INSERT INTO T_SIP_DEPTO( CD_DEPTO, NM_DEPTO, SG_DEPTO, ST_DEPTO)
VALUES (SQ_T_SIP_DEPTO.NEXTVAL, 'Automação Industrial', 'DAI', 'I');

INSERT INTO T_SIP_DEPTO( CD_DEPTO, NM_DEPTO, SG_DEPTO, ST_DEPTO)
VALUES (SQ_T_SIP_DEPTO.NEXTVAL, 'SAC', 'SAC', 'A');

INSERT INTO T_SIP_DEPTO( CD_DEPTO, NM_DEPTO, SG_DEPTO, ST_DEPTO)
VALUES (SQ_T_SIP_DEPTO.NEXTVAL, 'Custos', 'DCU', 'I');

INSERT INTO T_SIP_DEPTO( CD_DEPTO, NM_DEPTO, SG_DEPTO, ST_DEPTO)
VALUES (SQ_T_SIP_DEPTO.NEXTVAL, 'Estoque', 'DES', 'I');

INSERT INTO T_SIP_DEPTO( CD_DEPTO, NM_DEPTO, SG_DEPTO, ST_DEPTO)
VALUES (SQ_T_SIP_DEPTO.NEXTVAL, 'Embalagem', 'DEM', 'I');

Error starting at line : 21 in command -
INSERT INTO T_SIP_DEPTO( CD_DEPTO, NM_DEPTO, SG_DEPTO, ST_DEPTO)
VALUES (SQ_T_SIP_DEPTO.NEXTVAL, 'Embalagem', 'DEM', 'I')
Error report -
ORA-08004: sequence SQ_T_SIP_DEPTO.NEXTVAL
exceeds MAXVALUE and cannot be instantiated

CREATE SEQUENCE SQ_T_SIP_FUNCIONARIO
START WITH 1
INCREMENT BY 1
NOCYCLE;

INSERT INTO T_SIP_FUNCIONARIO
(NR_MATRICULA, CD_DEPTO, NM_FUNCIONARIO, DT_NASCIMENTO,
DT_ADMISSAO, VL_SALARIO)
VALUES
(SQ_T_SIP_FUNCIONARIO.NEXTVAL,
SQ_T_SIP_DEPTO.CURRVAL,
'Siaht Sepol',
SYSDATE-13555,
SYSDATE,
DBMS_RANDOM( 13454, 31000)
);

SELECT SQ_T_SIP_FUNCIONARIO.CURRVAL FROM DUAL;

SELECT * FROM T_SIP_FUNCIONARIO;

ALTER SEQUENCE SQ_T_SIP_DEPTO MAXVALUE 99;

--
-- INSERT ALL
--
INSERT ALL
	INTO T_SIP_DEPTO(CD_DEPTO, NM_DEPTO, SG_DEPTO, ST_DEPTO)
	VALUES (SQ_T_SIP_DEPTO.NEXTVAL, 'Lobby Relações Especiais', 'DMG', 'A')
	INTO T_SIP_FUNCIONARIO(NR_MATRICULA, CD_DEPTO, NM_FUNCIONARIO, DT_NASCIMENTO,
							DT_ADMISSAO, VL_SALARIO)
	VALUES(	SQ_T_SIP_FUNCIONARIO.NEXTVAL,
			SQ_T_SIP_DEPTO.CURRVAL,
			'Ovatsug Avlis',
			SYSDATE-18000,
			SYSDATE,
			DBMS_RANDOM.VALUE( 13454, 31000)
            )
	INTO T_SIP_DEPENDENTE(NR_MATRICULA, CD_DEPENDENTE, NM_DEPENDENTE, DT_NASCIMENTO)
	VALUES(	SQ_T_SIP_FUNCIONARIO.CURRVAL,
			1,
			'Leinad Avlis',
			SYSDATE-3650)
	INTO T_SIP_DEPENDENTE(NR_MATRICULA, CD_DEPENDENTE, NM_DEPENDENTE, DT_NASCIMENTO)
	VALUES(	SQ_T_SIP_FUNCIONARIO.CURRVAL,
			2,
			'Ana Avlis',
			SYSDATE-1650)
SELECT * FROM DUAL;

--
-- Identico ao MySQL e SQLServer (auto identify)
--