funcoes.sql
4.03 KB
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
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
DELIMITER |
CREATE FUNCTION extrair(t1 varchar(50) CHARSET latin1, t2 datetime)
RETURNS integer(100)
DETERMINISTIC
BEGIN
IF (t1='SECOND') THEN RETURN EXTRACT(SECOND FROM t2);
ELSEIF (t1='MINUTE') THEN RETURN EXTRACT(MINUTE FROM t2);
ELSEIF (t1='HOUR') THEN RETURN EXTRACT(HOUR FROM t2);
ELSEIF (t1='DAY') THEN RETURN EXTRACT(DAY FROM t2);
ELSEIF (t1='WEEK') THEN RETURN EXTRACT(WEEK FROM t2);
ELSEIF (t1='MONTH') THEN RETURN EXTRACT(MONTH FROM t2);
ELSEIF (t1='QUARTER') THEN RETURN EXTRACT(QUARTER FROM t2);
ELSEIF (t1='YEAR') THEN RETURN EXTRACT(YEAR FROM t2);
ELSEIF (t1='HOUR_MINUTE') THEN RETURN EXTRACT(HOUR_MINUTE FROM t2);
END IF;
END|
DELIMITER |
CREATE FUNCTION tempo_unix(t1 datetime)
RETURNS integer(100)
DETERMINISTIC
BEGIN
IF (t1) THEN RETURN UNIX_TIMESTAMP(t1);
ELSE RETURN UNIX_TIMESTAMP();
END IF;
END|
DELIMITER |
CREATE FUNCTION em_dias(t1 datetime)
RETURNS integer(100)
DETERMINISTIC
BEGIN
RETURN TO_DAYS(t1);
END|
DELIMITER |
CREATE FUNCTION dia(t1 datetime)
RETURNS integer(10)
DETERMINISTIC
BEGIN
RETURN DAY(t1);
END|
DELIMITER |
CREATE FUNCTION semana_ano(t1 datetime)
RETURNS integer(10)
DETERMINISTIC
BEGIN
RETURN WEEKOFYEAR(t1);
END|
DELIMITER |
CREATE FUNCTION ano(t1 datetime)
RETURNS integer(10)
DETERMINISTIC
BEGIN
RETURN YEAR(t1);
END|
DELIMITER |
CREATE FUNCTION mes(t1 datetime)
RETURNS integer(10)
DETERMINISTIC
BEGIN
RETURN MONTH(t1);
END|
DELIMITER |
CREATE FUNCTION dia_semana(t1 datetime)
RETURNS integer(10)
DETERMINISTIC
BEGIN
RETURN WEEKDAY(t1);
END|
DELIMITER |
CREATE FUNCTION adiciona_data(t1 datetime, t2 float(100,3), t3 varchar(50) CHARSET latin1)
RETURNS datetime
DETERMINISTIC
BEGIN
IF (t3='SECOND') THEN RETURN TIMESTAMPADD(SECOND,t2,t1);
ELSEIF (t3='MINUTE') THEN RETURN TIMESTAMPADD(MINUTE,t2,t1);
ELSEIF (t3='HOUR') THEN RETURN TIMESTAMPADD(HOUR,t2,t1);
ELSEIF (t3='DAY') THEN RETURN TIMESTAMPADD(DAY,t2,t1);
ELSEIF (t3='WEEK') THEN RETURN TIMESTAMPADD(WEEK,t2,t1);
ELSEIF (t3='MONTH') THEN RETURN TIMESTAMPADD(MONTH,t2,t1);
ELSEIF (t3='QUARTER') THEN RETURN TIMESTAMPADD(QUARTER,t2,t1);
ELSEIF (t3='YEAR') THEN RETURN TIMESTAMPADD(YEAR,t2,t1);
END IF;
END|
DELIMITER |
CREATE FUNCTION diferenca_data(t1 datetime, t2 datetime)
RETURNS integer(10)
DETERMINISTIC
BEGIN
RETURN DATEDIFF(t1, t2);
END|
DELIMITER |
CREATE FUNCTION diferenca_tempo(t1 time, t2 time)
RETURNS time
DETERMINISTIC
BEGIN
RETURN TIMEDIFF(t1, t2);
END|
DELIMITER |
CREATE FUNCTION tamanho_caractere(t1 text CHARSET latin1)
RETURNS int(100)
DETERMINISTIC
BEGIN
RETURN CHAR_LENGTH(t1);
END|
DELIMITER |
CREATE FUNCTION tempo_em_segundos(t1 time)
RETURNS integer(100)
DETERMINISTIC
BEGIN
RETURN TIME_TO_SEC(t1);
END|
DELIMITER |
CREATE FUNCTION formatar_data(t1 datetime, t2 varchar(255) CHARSET latin1)
RETURNS varchar(255) CHARSET latin1
DETERMINISTIC
BEGIN
return DATE_FORMAT(t1, t2);
END|
DELIMITER |
CREATE FUNCTION concatenar_dois(t1 varchar(255) CHARSET latin1, t2 varchar(255) CHARSET latin1)
RETURNS text CHARSET latin1
DETERMINISTIC
BEGIN
RETURN CONCAT(t1, t2);
END|
DELIMITER |
CREATE FUNCTION concatenar_tres(t1 varchar(255) CHARSET latin1, t2 varchar(255) CHARSET latin1, t3 varchar(255) CHARSET latin1)
RETURNS text CHARSET latin1
DETERMINISTIC
BEGIN
RETURN CONCAT(t1, t2, t3);
END|
DELIMITER |
CREATE FUNCTION concatenar_quatro(t1 varchar(255) CHARSET latin1, t2 varchar(255) CHARSET latin1, t3 varchar(255) CHARSET latin1, t4 varchar(255)CHARSET latin1)
RETURNS text CHARSET latin1
DETERMINISTIC
BEGIN
RETURN CONCAT(t1, t2, t3, t4);
END|
DELIMITER |
CREATE FUNCTION concatenar_cinco(t1 varchar(255) CHARSET latin1, t2 varchar(255) CHARSET latin1, t3 varchar(255) CHARSET latin1, t4 varchar(255) CHARSET latin1, t5 varchar(255) CHARSET latin1)
RETURNS text CHARSET latin1
DETERMINISTIC
BEGIN
RETURN CONCAT(t1, t2, t3, t4, t5);
END|
DELIMITER |
CREATE FUNCTION strmes(t1 datetime)
RETURNS VARCHAR(2)
DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
RETURN LPAD(MONTH(t1),2,'0');
END|