API tools faq
paste
Advertisement
hamacker

Script para uma tabela de feriados

hamacker
Mar 4th, 2016
308
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 11.26 KB | None | 0 0
raw download clone embed print report
  1. CREATE TABLE AGENDA (
  2.     ID_AGENDA       D_ROWID,
  3.     ID_COLABORADOR  D_ROWID,
  4.     DT_INICIAL      D_DATA NOT NULL,
  5.     DT_FINAL        D_DATA NOT NULL,
  6.     DESCRICAO       D_TEXTO,
  7.     LAST_UPDATE     D_CURRENT_TIME,
  8.     LAST_OWNER      D_CURRENT_USER,
  9.     STATUS          D_STATUS,
  10.     MODIFICADO_EM   COMPUTED BY (CAST((SUBSTRING(CAST(LAST_UPDATE AS VARCHAR(30)) FROM 1 FOR 10)||' por '||LAST_OWNER) AS VARCHAR(40))),
  11.     STATUS_COM      COMPUTED BY ((
  12.   CASE
  13.       WHEN STATUS='A' THEN 'Aberto'
  14.       WHEN STATUS='F' THEN 'Permanente'
  15.       WHEN STATUS='C' THEN 'Cancelado'
  16.   END))
  17. );
  18.  
  19.  
  20.  
  21.  
  22. /******************************************************************************/
  23. /***                           Unique Constraints                           ***/
  24. /******************************************************************************/
  25.  
  26. ALTER TABLE AGENDA ADD CONSTRAINT UNQ1_AGENDA UNIQUE (ID_COLABORADOR, DT_INICIAL, DT_FINAL);
  27.  
  28. ALTER TABLE AGENDA ADD CONSTRAINT PK_AGENDA PRIMARY KEY (ID_AGENDA);
  29.  
  30. UPDATE OR INSERT INTO AGENDA (ID_AGENDA, ID_COLABORADOR, DT_INICIAL, DT_FINAL, DESCRICAO, LAST_UPDATE, LAST_OWNER, STATUS)
  31.                       VALUES (2, 0, '2012-04-30', '2012-05-01', '01/05/2012 dia do trabalhador imenda de feriado', '2013-04-22 13:58:00', 'SYSDBA', 'F')
  32.                     MATCHING (ID_AGENDA);
  33. UPDATE OR INSERT INTO AGENDA (ID_AGENDA, ID_COLABORADOR, DT_INICIAL, DT_FINAL, DESCRICAO, LAST_UPDATE, LAST_OWNER, STATUS)
  34.                       VALUES (3, 0, '2012-06-07', '2012-06-07', '07/06/2012 Corpus Christi', '2013-04-22 14:01:00', 'SYSDBA', 'F')
  35.                     MATCHING (ID_AGENDA);
  36. UPDATE OR INSERT INTO AGENDA (ID_AGENDA, ID_COLABORADOR, DT_INICIAL, DT_FINAL, DESCRICAO, LAST_UPDATE, LAST_OWNER, STATUS)
  37.                       VALUES (4, 0, '2012-06-07', '2012-09-09', '07 de setembro - independencia - imenda de feriado', '2013-04-22 14:02:00', 'SYSDBA', 'F')
  38.                     MATCHING (ID_AGENDA);
  39. UPDATE OR INSERT INTO AGENDA (ID_AGENDA, ID_COLABORADOR, DT_INICIAL, DT_FINAL, DESCRICAO, LAST_UPDATE, LAST_OWNER, STATUS)
  40.                       VALUES (5, 0, '2012-10-12', '2012-10-14', '12 outubro  Padroeira do Brasil imenda de feriado', '2013-04-22 14:03:00', 'SYSDBA', 'F')
  41.                     MATCHING (ID_AGENDA);
  42. UPDATE OR INSERT INTO AGENDA (ID_AGENDA, ID_COLABORADOR, DT_INICIAL, DT_FINAL, DESCRICAO, LAST_UPDATE, LAST_OWNER, STATUS)
  43.                       VALUES (6, 0, '2012-11-02', '2012-11-04', '2 novembro  Finados imenda de feriado', '2013-04-22 14:04:00', 'SYSDBA', 'F')
  44.                     MATCHING (ID_AGENDA);
  45. UPDATE OR INSERT INTO AGENDA (ID_AGENDA, ID_COLABORADOR, DT_INICIAL, DT_FINAL, DESCRICAO, LAST_UPDATE, LAST_OWNER, STATUS)
  46.                       VALUES (7, 0, '2012-11-15', '2012-11-15', '15 novembro  Proclamação da Repúblic..', '2013-04-22 14:05:00', 'SYSDBA', 'F')
  47.                     MATCHING (ID_AGENDA);
  48. UPDATE OR INSERT INTO AGENDA (ID_AGENDA, ID_COLABORADOR, DT_INICIAL, DT_FINAL, DESCRICAO, LAST_UPDATE, LAST_OWNER, STATUS)
  49.                       VALUES (8, 0, '2012-12-22', '2013-01-02', 'Ferias coletiva', '2013-04-22 14:07:00', 'SYSDBA', 'F')
  50.                     MATCHING (ID_AGENDA);
  51. UPDATE OR INSERT INTO AGENDA (ID_AGENDA, ID_COLABORADOR, DT_INICIAL, DT_FINAL, DESCRICAO, LAST_UPDATE, LAST_OWNER, STATUS)
  52.                       VALUES (9, 0, '2013-02-11', '2013-02-12', 'Carnaval', '2013-04-22 14:10:00', 'SYSDBA', 'F')
  53.                     MATCHING (ID_AGENDA);
  54. UPDATE OR INSERT INTO AGENDA (ID_AGENDA, ID_COLABORADOR, DT_INICIAL, DT_FINAL, DESCRICAO, LAST_UPDATE, LAST_OWNER, STATUS)
  55.                       VALUES (10, 0, '2013-03-29', '2013-03-31', '
  56. 29/03 -  Sexta-feira Santa', '2013-04-22 14:13:00', 'SYSDBA', 'F')
  57.                     MATCHING (ID_AGENDA);
  58. UPDATE OR INSERT INTO AGENDA (ID_AGENDA, ID_COLABORADOR, DT_INICIAL, DT_FINAL, DESCRICAO, LAST_UPDATE, LAST_OWNER, STATUS)
  59.                       VALUES (11, 0, '2013-05-01', '2013-05-01', '01/05 - dia do trabalhador', '2013-04-22 14:14:00', 'SYSDBA', 'F')
  60.                     MATCHING (ID_AGENDA);
  61. UPDATE OR INSERT INTO AGENDA (ID_AGENDA, ID_COLABORADOR, DT_INICIAL, DT_FINAL, DESCRICAO, LAST_UPDATE, LAST_OWNER, STATUS)
  62.                       VALUES (12, 0, '2013-05-30', '2013-06-02', '30/05/2013 Corpus Christi imenda de feriado', '2013-04-22 14:16:00', 'SYSDBA', 'F')
  63.                     MATCHING (ID_AGENDA);
  64. UPDATE OR INSERT INTO AGENDA (ID_AGENDA, ID_COLABORADOR, DT_INICIAL, DT_FINAL, DESCRICAO, LAST_UPDATE, LAST_OWNER, STATUS)
  65.                       VALUES (13, 0, '2013-11-15', '2013-11-17', '15 novembro  Proclamação da Repúblic.. imenda de feriado', '2013-04-22 14:20:00', 'SYSDBA', 'F')
  66.                     MATCHING (ID_AGENDA);
  67. UPDATE OR INSERT INTO AGENDA (ID_AGENDA, ID_COLABORADOR, DT_INICIAL, DT_FINAL, DESCRICAO, LAST_UPDATE, LAST_OWNER, STATUS)
  68.                       VALUES (14, 0, '2013-12-23', '2014-01-02', 'Ferias coletivas', '2013-04-22 14:24:00', 'SYSDBA', 'F')
  69.                     MATCHING (ID_AGENDA);
  70. UPDATE OR INSERT INTO AGENDA (ID_AGENDA, ID_COLABORADOR, DT_INICIAL, DT_FINAL, DESCRICAO, LAST_UPDATE, LAST_OWNER, STATUS)
  71.                       VALUES (1, 0, '2012-04-06', '2012-04-08', 'Imenda de feriado 06/04 sexta feira santa', '2013-04-22 13:57:00', 'SYSDBA', 'F')
  72.                     MATCHING (ID_AGENDA);
  73. UPDATE OR INSERT INTO AGENDA (ID_AGENDA, ID_COLABORADOR, DT_INICIAL, DT_FINAL, DESCRICAO, LAST_UPDATE, LAST_OWNER, STATUS)
  74.                       VALUES (45, 0, '2014-03-03', '2014-03-04', 'CARNAVAL', '2014-02-21 16:28:59', 'SYSDBA', 'F')
  75.                     MATCHING (ID_AGENDA);
  76. UPDATE OR INSERT INTO AGENDA (ID_AGENDA, ID_COLABORADOR, DT_INICIAL, DT_FINAL, DESCRICAO, LAST_UPDATE, LAST_OWNER, STATUS)
  77.                       VALUES (59, 0, '2014-07-09', '2014-07-09', 'FERIADO', '2014-07-10 17:24:14', 'SYSDBARM', 'F')
  78.                     MATCHING (ID_AGENDA);
  79. UPDATE OR INSERT INTO AGENDA (ID_AGENDA, ID_COLABORADOR, DT_INICIAL, DT_FINAL, DESCRICAO, LAST_UPDATE, LAST_OWNER, STATUS)
  80.                       VALUES (84, 0, '2014-12-24', '2015-01-04', 'Férias coletivas', '2014-12-15 09:52:37', 'SYSDBA', 'F')
  81.                     MATCHING (ID_AGENDA);
  82. UPDATE OR INSERT INTO AGENDA (ID_AGENDA, ID_COLABORADOR, DT_INICIAL, DT_FINAL, DESCRICAO, LAST_UPDATE, LAST_OWNER, STATUS)
  83.                       VALUES (89, 0, '2015-02-16', '2015-02-17', 'CARNAVAL', '2015-01-26 09:58:00', 'SYSDBA', 'F')
  84.                     MATCHING (ID_AGENDA);
  85. UPDATE OR INSERT INTO AGENDA (ID_AGENDA, ID_COLABORADOR, DT_INICIAL, DT_FINAL, DESCRICAO, LAST_UPDATE, LAST_OWNER, STATUS)
  86.                       VALUES (102, 0, '2015-04-03', '2015-04-05', 'SEXTA FEIRA SANTA E PASCOA', '2015-03-30 08:17:15', 'SYSDBA', 'F')
  87.                     MATCHING (ID_AGENDA);
  88. UPDATE OR INSERT INTO AGENDA (ID_AGENDA, ID_COLABORADOR, DT_INICIAL, DT_FINAL, DESCRICAO, LAST_UPDATE, LAST_OWNER, STATUS)
  89.                       VALUES (103, 0, '2015-04-20', '2015-04-22', 'DIA PONTE E TIRADENTES', '2015-04-13 08:07:27', 'SYSDBA', 'F')
  90.                     MATCHING (ID_AGENDA);
  91. UPDATE OR INSERT INTO AGENDA (ID_AGENDA, ID_COLABORADOR, DT_INICIAL, DT_FINAL, DESCRICAO, LAST_UPDATE, LAST_OWNER, STATUS)
  92.                       VALUES (106, 0, '2015-06-04', '2015-06-06', 'FERIADO E DIA PONTE CORPUS CHRISTI', '2015-04-13 08:09:44', 'SYSDBA', 'F')
  93.                     MATCHING (ID_AGENDA);
  94. UPDATE OR INSERT INTO AGENDA (ID_AGENDA, ID_COLABORADOR, DT_INICIAL, DT_FINAL, DESCRICAO, LAST_UPDATE, LAST_OWNER, STATUS)
  95.                       VALUES (105, 0, '2015-05-01', '2015-05-02', 'FERIADO DIA DO TRABALHO', '2015-04-13 08:08:25', 'SYSDBA', 'F')
  96.                     MATCHING (ID_AGENDA);
  97. UPDATE OR INSERT INTO AGENDA (ID_AGENDA, ID_COLABORADOR, DT_INICIAL, DT_FINAL, DESCRICAO, LAST_UPDATE, LAST_OWNER, STATUS)
  98.                       VALUES (107, 0, '2015-07-09', '2015-07-11', 'FERIADO E DIA PONTE REVOLUÇÃO DE 32.', '2015-04-13 08:29:03', 'SYSDBA', 'F')
  99.                     MATCHING (ID_AGENDA);
  100. UPDATE OR INSERT INTO AGENDA (ID_AGENDA, ID_COLABORADOR, DT_INICIAL, DT_FINAL, DESCRICAO, LAST_UPDATE, LAST_OWNER, STATUS)
  101.                       VALUES (108, 0, '2015-12-24', '2016-01-04', 'DIAS PONTES DE FIM DE ANO.', '2015-04-13 08:31:35', 'SYSDBA', 'A')
  102.                     MATCHING (ID_AGENDA);
  103. UPDATE OR INSERT INTO AGENDA (ID_AGENDA, ID_COLABORADOR, DT_INICIAL, DT_FINAL, DESCRICAO, LAST_UPDATE, LAST_OWNER, STATUS)
  104.                       VALUES (124, 0, '2015-09-06', '2015-09-07', 'FERIADO', '2015-08-31 09:09:12', 'SYSDBA', 'F')
  105.                     MATCHING (ID_AGENDA);
  106. UPDATE OR INSERT INTO AGENDA (ID_AGENDA, ID_COLABORADOR, DT_INICIAL, DT_FINAL, DESCRICAO, LAST_UPDATE, LAST_OWNER, STATUS)
  107.                       VALUES (125, 0, '2015-10-11', '2015-10-12', 'FERIADO, NOSSA SENHORA APARECIDA', '2015-08-31 09:11:25', 'SYSDBA', 'F')
  108.                     MATCHING (ID_AGENDA);
  109. UPDATE OR INSERT INTO AGENDA (ID_AGENDA, ID_COLABORADOR, DT_INICIAL, DT_FINAL, DESCRICAO, LAST_UPDATE, LAST_OWNER, STATUS)
  110.                       VALUES (126, 0, '2015-11-01', '2015-11-02', 'FERIADO, FINADOS', '2015-08-31 09:14:13', 'SYSDBA', 'F')
  111.                     MATCHING (ID_AGENDA);
  112.  
  113. COMMIT WORK;
  114.  
  115. /* Uma procediure para incluir algo nessa tabela seria assim: */
  116. CREATE OR ALTER PROCEDURE sp_agenda (
  117.     p_acao VARCHAR(32),
  118.     p_id_agenda BIGINT = 0,
  119.     p_id_colaborador BIGINT = 0,
  120.     p_dt_inicial DATE = CURRENT_DATE,
  121.     p_dt_final DATE = CURRENT_DATE,
  122.     p_descricao d_texto = '',
  123.     p_status CHAR(1) = 'A')
  124. RETURNS (
  125.     result_value BIGINT)
  126. AS
  127. DECLARE variable l_status VARCHAR(1) = '';
  128. DECLARE variable l_id_colaborador BIGINT = 0;
  129. DECLARE variable l_dt_inicial DATE = CURRENT_DATE;
  130. DECLARE variable l_dt_final DATE = CURRENT_DATE;
  131. DECLARE variable l_permitido INTEGER = 0;
  132. BEGIN
  133.   result_value=0;
  134.   IF (:p_acao NOT IN ('INCLUIR','ALTERAR','EXCLUIR')) THEN
  135.   BEGIN
  136.     exception ERR 'Ação solicitada ('||:p_acao||') está fora dos parametros permitidos !';
  137.     suspend;
  138.     exit;
  139.   END
  140.  
  141.   l_id_colaborador=:p_id_colaborador;
  142.  
  143.   IF (:p_acao='INCLUIR') THEN
  144.   BEGIN
  145.     SELECT COALESCE(MAX(id_agenda),0) FROM agenda INTO :p_id_agenda;
  146.     p_id_agenda=:p_id_agenda+1;
  147.     INSERT INTO agenda(
  148.       id_agenda,
  149.       id_colaborador,
  150.       dt_inicial,
  151.       dt_final,
  152.       descricao,
  153.       last_update,
  154.       last_owner,
  155.       STATUS)
  156.     VALUES(
  157.       :p_id_agenda,
  158.       :p_id_colaborador,
  159.       :p_dt_inicial,
  160.       :p_dt_final,
  161.       :p_descricao,
  162.       CURRENT_TIMESTAMP,
  163.       CURRENT_USER,
  164.       'A');
  165.     result_value=1;
  166.   END
  167.  
  168.   IF (:p_acao IN ('ALTERAR','EXCLUIR')) THEN
  169.   BEGIN
  170.     SELECT dt_inicial, dt_final, STATUS FROM agenda
  171.     WHERE id_agenda=:p_id_agenda
  172.     INTO :l_dt_inicial, :l_dt_final, :l_status;
  173.     IF (:l_status = 'F') THEN
  174.     BEGIN
  175.       exception ERR 'Esse agendamento é considerado [Permanente], isso quer dizer que não pode ser modificado porque já está em uso!'
  176.       suspend;
  177.       exit;
  178.      END
  179.   END
  180.  
  181.   IF (:p_acao='ALTERAR') THEN
  182.   BEGIN
  183.     UPDATE agenda
  184.     SET descricao=:p_descricao,
  185.         dt_inicial=:p_dt_inicial,
  186.         dt_final=:p_dt_final,
  187.         last_update=CURRENT_TIMESTAMP,
  188.         last_owner=CURRENT_USER,
  189.         STATUS=:p_status
  190.     WHERE id_agenda=:p_id_agenda;
  191.     result_value=1;
  192.   END
  193.  
  194.   IF (:p_acao='EXCLUIR') THEN
  195.   BEGIN
  196.     DELETE FROM agenda
  197.     WHERE id_agenda=:p_id_agenda AND status<>'F';
  198.     result_value=1;
  199.   END
  200.   suspend;
  201. END
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!