tag:blogger.com,1999:blog-61004194450907901992024-03-01T22:07:48.380-03:00SQL BrasilCesar Blummhttp://www.blogger.com/profile/17590220931166448799noreply@blogger.comBlogger102125tag:blogger.com,1999:blog-6100419445090790199.post-87682231035559511912015-01-14T08:37:00.002-02:002015-01-14T08:38:20.418-02:00Consulta do CharacterSet do OracleO character set do database no Oracle define a forma como os valores serão armazenados no banco de dados.<br />
Para consultar qual o valor que foi configurado na instalação do database execute a instrução:<br />
<i><br /></i>
<i>SELECT * FROM NLS_DATABASE_PARAMETERS</i><br />
<i> WHERE PARAMETER='NLS_CHARACTERSET';</i><br />
<i><br /></i>
<br />
<pre>PARAMETER VALUE
------------------------------ ----------------------------------------
<strong>NLS_CHARACTERSET WE8ISO8859P1</strong></pre>
<pre><strong>
</strong></pre>
Cesar Blummhttp://www.blogger.com/profile/17590220931166448799noreply@blogger.com1tag:blogger.com,1999:blog-6100419445090790199.post-52326106149321907142013-06-25T14:21:00.001-03:002013-06-25T14:21:58.730-03:00SQL Server 2014 CTP1A Microsoft disponibilizou em seu site a versão Community Preview 1 da nova versão do SQL Server. No link a seguir é possível iniciar o download do instalador.<br />
<br />
<a href="http://www.microsoft.com/en-us/sqlserver/sql-server-2014.aspx">http://www.microsoft.com/en-us/sqlserver/sql-server-2014.aspx</a><br />
<br />
No link a seguir tem como fazer o download do guia do produto: <a href="http://www.microsoft.com/en-us/download/details.aspx?id=39269">http://www.microsoft.com/en-us/download/details.aspx?id=39269</a><br />
<br />
Nos próximos posts vou escrever sobre algumas das novidades desta versão.<br />
<div>
<br /></div>
Cesar Blummhttp://www.blogger.com/profile/17590220931166448799noreply@blogger.com1tag:blogger.com,1999:blog-6100419445090790199.post-50731269267044943842013-06-14T14:35:00.001-03:002013-06-14T14:43:03.287-03:00Database SQL Server no estado "Restoring"Quando é realizado o procedimento de restore de uma database no SQL Server o estado do database fica como "restoring", entretanto algumas vezes ele não sai deste estado mesmo tendo terminado o processo normalmente.<br />
<div>
Para retornar o database para o estado normal execute a seguinte instrução:</div>
<div>
<br /></div>
<div>
<span style="color: blue;">RESTORE</span><span style="background-color: white;"> <span style="color: blue;">DATABASE</span></span> <span style="background-color: white;"><span style="color: #cccccc;">NomeDatabase</span></span> <span style="color: blue;">WITH RECOVERY</span></div>
<div>
<br /></div>
Cesar Blummhttp://www.blogger.com/profile/17590220931166448799noreply@blogger.com23tag:blogger.com,1999:blog-6100419445090790199.post-46536498027243608422012-12-06T11:39:00.001-02:002012-12-06T11:39:24.806-02:00Consulta separando Nome e SobrenomeAbaixo um exemplo para separar o nome e o último sobrenome de um campo com o nome completo:<br />
<br />
Select substring(nome,1,patindex('% %',nome)) 'Nome',<br />
substring(nome,(len(nome) - (patindex('% %',reverse(nome))))+2,patindex('% %',reverse(nome)) ) 'Sobrenome'<br />
From Funcionarios<br />
<br />
Cesar Blummhttp://www.blogger.com/profile/17590220931166448799noreply@blogger.com2tag:blogger.com,1999:blog-6100419445090790199.post-31677906827024605772012-08-17T17:08:00.000-03:002012-08-17T17:08:42.950-03:00SQL Saturday 147Falta uma semana para o SQL Saturday 147 em Recife - PE.<br />Todos que trabalham com SQL Server e tiverem condições, devem participar. <br />O evento é gratuito e os assuntos propostos são bastante relevantes para os profissionais da área.<br />No dia anterior 24/08/2012 ocorrerão mini-cursos e no dia 25/08/2012 as palestras do evento.<br />Vale a pena conferir.<br />Inscreva-se já (<a href="http://www.sqlsaturday.com/147/eventhome.aspx">http://www.sqlsaturday.com/147/eventhome.aspx</a>)<br />Abaixo palestras do dia 25/08/2012:<br />
<br />
<table border="1" cellpadding="0" cellspacing="0" class="MsoTableLightShadingAccent1" style="border-collapse: collapse; border: currentColor; mso-border-bottom-alt: solid #4F81BD 1.0pt; mso-border-bottom-themecolor: accent1; mso-border-top-alt: solid #4F81BD 1.0pt; mso-border-top-themecolor: accent1; mso-padding-alt: 0cm 5.4pt 0cm 5.4pt; mso-yfti-tbllook: 1184; width: 100%px;">
<tbody>
<tr style="mso-yfti-firstrow: yes; mso-yfti-irow: -1;">
<td style="background-color: transparent; border-color: rgb(79, 129, 189) rgb(0, 0, 0); border-style: solid none; border-width: 1pt 0px; mso-border-bottom-themecolor: accent1; mso-border-top-themecolor: accent1; padding: 0cm 5.4pt;" valign="top">
<div align="center" class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-yfti-cnfc: 5; text-align: center; vertical-align: baseline;">
<b><span style="color: white; font-family: "Times New Roman","serif"; font-size: 10pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: PT-BR;">Start Time<o:p></o:p></span></b></div>
</td>
<td style="background-color: transparent; border-color: rgb(79, 129, 189) rgb(0, 0, 0); border-style: solid none; border-width: 1pt 0px; mso-border-bottom-themecolor: accent1; mso-border-top-themecolor: accent1; padding: 0cm 5.4pt;" valign="top">
<div align="center" class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-yfti-cnfc: 1; text-align: center; vertical-align: baseline;">
<b><span style="color: black; font-family: "Times New Roman","serif"; font-size: 10pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: PT-BR; mso-themecolor: text1;">Auditorium - Room: Auditorium<o:p></o:p></span></b></div>
</td>
<td style="background-color: transparent; border-color: rgb(79, 129, 189) rgb(0, 0, 0); border-style: solid none; border-width: 1pt 0px; mso-border-bottom-themecolor: accent1; mso-border-top-themecolor: accent1; padding: 0cm 5.4pt;" valign="top">
<div align="center" class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-yfti-cnfc: 1; text-align: center; vertical-align: baseline;">
<b><span style="color: black; font-family: "Times New Roman","serif"; font-size: 10pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: PT-BR; mso-themecolor: text1;">Lab 2 - Room: Lab 2<o:p></o:p></span></b></div>
</td>
</tr>
<tr style="mso-yfti-irow: 0;">
<td style="background: rgb(211, 223, 238); border: 0px rgb(0, 0, 0); mso-background-themecolor: accent1; mso-background-themetint: 63; padding: 0cm 5.4pt;" valign="top">
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-yfti-cnfc: 68; vertical-align: baseline;">
<b><span style="color: #717171; font-family: "Times New Roman","serif"; font-size: 10pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: PT-BR;">08:30 AM<o:p></o:p></span></b></div>
</td>
<td style="background: rgb(211, 223, 238); border: 0px rgb(0, 0, 0); mso-background-themecolor: accent1; mso-background-themetint: 63; padding: 0cm 5.4pt;" valign="top">
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-yfti-cnfc: 64; vertical-align: baseline;">
<span style="color: #717171; font-family: "Times New Roman","serif"; font-size: 10pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: PT-BR;"><a href="http://www.sqlsaturday.com/viewsession.aspx?sat=147&sessionid=10400"><span lang="EN-US" style="border: 1pt windowtext; color: #0055ff; mso-ansi-language: EN-US; mso-border-alt: none windowtext 0cm; padding: 0cm; text-decoration: none; text-underline: none;">SQL Sat #147<br />
Registration<br />
Level: Beginner</span></a></span><span lang="EN-US" style="color: #717171; font-family: "Times New Roman","serif"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: PT-BR;"><o:p></o:p></span></div>
</td>
<td style="background: rgb(211, 223, 238); border: 0px rgb(0, 0, 0); mso-background-themecolor: accent1; mso-background-themetint: 63; padding: 0cm 5.4pt;" valign="top">
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-yfti-cnfc: 64; vertical-align: baseline;">
<span style="color: #717171; font-family: "Times New Roman","serif"; font-size: 10pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: PT-BR;"><a href="http://www.sqlsaturday.com/viewsession.aspx?sat=147&sessionid=10400"><span lang="EN-US" style="border: 1pt windowtext; color: #0055ff; mso-ansi-language: EN-US; mso-border-alt: none windowtext 0cm; padding: 0cm; text-decoration: none; text-underline: none;">SQL Sat #147<br />
Registration<br />
Level: Beginner</span></a></span><span lang="EN-US" style="color: #717171; font-family: "Times New Roman","serif"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: PT-BR;"><o:p></o:p></span></div>
</td>
</tr>
<tr style="mso-yfti-irow: 1;">
<td style="background-color: transparent; border: 0px rgb(0, 0, 0); padding: 0cm 5.4pt;" valign="top">
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-yfti-cnfc: 4; vertical-align: baseline;">
<b><span style="color: #717171; font-family: "Times New Roman","serif"; font-size: 10pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: PT-BR;">09:00 AM<o:p></o:p></span></b></div>
</td>
<td style="background-color: transparent; border: 0px rgb(0, 0, 0); padding: 0cm 5.4pt;" valign="top">
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; vertical-align: baseline;">
<span style="color: #717171; font-family: "Times New Roman","serif"; font-size: 10pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: PT-BR;"><a href="http://www.sqlsaturday.com/viewsession.aspx?sat=147&sessionid=10749"><span lang="EN-US" style="border: 1pt windowtext; color: #0055ff; mso-ansi-language: EN-US; mso-border-alt: none windowtext 0cm; padding: 0cm; text-decoration: none; text-underline: none;">Denny Cherry<br />
Reading the SQL Server Execution Plan<br />
Level: Beginner</span></a></span><span lang="EN-US" style="color: #717171; font-family: "Times New Roman","serif"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: PT-BR;"><o:p></o:p></span></div>
</td>
<td style="background-color: transparent; border: 0px rgb(0, 0, 0); padding: 0cm 5.4pt;" valign="top">
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; vertical-align: baseline;">
<span style="color: #717171; font-family: "Times New Roman","serif"; font-size: 10pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: PT-BR;"><a href="http://www.sqlsaturday.com/viewsession.aspx?sat=147&sessionid=10869"><span style="border: 1pt windowtext; color: #0055ff; mso-border-alt: none windowtext 0cm; padding: 0cm; text-decoration: none; text-underline: none;">Roberval Ranches<br />
Usando DQS e SSIS para automatizar carga de dados<br />
Level: Beginner</span></a><o:p></o:p></span></div>
</td>
</tr>
<tr style="mso-yfti-irow: 2;">
<td style="background: rgb(211, 223, 238); border: 0px rgb(0, 0, 0); mso-background-themecolor: accent1; mso-background-themetint: 63; padding: 0cm 5.4pt;" valign="top">
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-yfti-cnfc: 68; vertical-align: baseline;">
<b><span style="color: #717171; font-family: "Times New Roman","serif"; font-size: 10pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: PT-BR;">10:15 AM<o:p></o:p></span></b></div>
</td>
<td style="background: rgb(211, 223, 238); border: 0px rgb(0, 0, 0); mso-background-themecolor: accent1; mso-background-themetint: 63; padding: 0cm 5.4pt;" valign="top">
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-yfti-cnfc: 64; vertical-align: baseline;">
<span style="color: #717171; font-family: "Times New Roman","serif"; font-size: 10pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: PT-BR;"><a href="http://www.sqlsaturday.com/viewsession.aspx?sat=147&sessionid=9925"><span style="border: 1pt windowtext; color: #0055ff; mso-border-alt: none windowtext 0cm; padding: 0cm; text-decoration: none; text-underline: none;">Marcondes Alexandre<br />
Administrando sua base de dados no SQL Azure<br />
Level: Intermediate</span></a><o:p></o:p></span></div>
</td>
<td style="background: rgb(211, 223, 238); border: 0px rgb(0, 0, 0); mso-background-themecolor: accent1; mso-background-themetint: 63; padding: 0cm 5.4pt;" valign="top">
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-yfti-cnfc: 64; vertical-align: baseline;">
<span style="color: #717171; font-family: "Times New Roman","serif"; font-size: 10pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: PT-BR;"><a href="http://www.sqlsaturday.com/viewsession.aspx?sat=147&sessionid=9837"><span style="border: 1pt windowtext; color: #0055ff; mso-border-alt: none windowtext 0cm; padding: 0cm; text-decoration: none; text-underline: none;">Vladimir Magalhães<br />
Trabalhando com dados geográficos no SSRS 2012<br />
Level: Intermediate</span></a><o:p></o:p></span></div>
</td>
</tr>
<tr style="mso-yfti-irow: 3;">
<td style="background-color: transparent; border: 0px rgb(0, 0, 0); padding: 0cm 5.4pt;" valign="top">
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-yfti-cnfc: 4; vertical-align: baseline;">
<b><span style="color: #717171; font-family: "Times New Roman","serif"; font-size: 10pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: PT-BR;">11:30 AM<o:p></o:p></span></b></div>
</td>
<td style="background-color: transparent; border: 0px rgb(0, 0, 0); padding: 0cm 5.4pt;" valign="top">
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; vertical-align: baseline;">
<span style="color: #717171; font-family: "Times New Roman","serif"; font-size: 10pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: PT-BR;"><a href="http://www.sqlsaturday.com/viewsession.aspx?sat=147&sessionid=10533"><span lang="EN-US" style="border: 1pt windowtext; color: #0055ff; mso-ansi-language: EN-US; mso-border-alt: none windowtext 0cm; padding: 0cm; text-decoration: none; text-underline: none;">Cesar Blumm<br />
Replicação Hands On<br />
Level: Beginner</span></a></span><span lang="EN-US" style="color: #717171; font-family: "Times New Roman","serif"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: PT-BR;"><o:p></o:p></span></div>
</td>
<td style="background-color: transparent; border: 0px rgb(0, 0, 0); padding: 0cm 5.4pt;" valign="top">
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; vertical-align: baseline;">
<span style="color: #717171; font-family: "Times New Roman","serif"; font-size: 10pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: PT-BR;"><a href="http://www.sqlsaturday.com/viewsession.aspx?sat=147&sessionid=10751"><span lang="EN-US" style="border: 1pt windowtext; color: #0055ff; mso-ansi-language: EN-US; mso-border-alt: none windowtext 0cm; padding: 0cm; text-decoration: none; text-underline: none;">Denny Cherry<br />
Where should I be encrypting my data<br />
Level: Beginner</span></a></span><span lang="EN-US" style="color: #717171; font-family: "Times New Roman","serif"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: PT-BR;"><o:p></o:p></span></div>
</td>
</tr>
<tr style="mso-yfti-irow: 4;">
<td style="background: rgb(211, 223, 238); border: 0px rgb(0, 0, 0); mso-background-themecolor: accent1; mso-background-themetint: 63; padding: 0cm 5.4pt;" valign="top">
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-yfti-cnfc: 68; vertical-align: baseline;">
<b><span style="color: #717171; font-family: "Times New Roman","serif"; font-size: 10pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: PT-BR;">02:00 PM<o:p></o:p></span></b></div>
</td>
<td style="background: rgb(211, 223, 238); border: 0px rgb(0, 0, 0); mso-background-themecolor: accent1; mso-background-themetint: 63; padding: 0cm 5.4pt;" valign="top">
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-yfti-cnfc: 64; vertical-align: baseline;">
<span style="color: #717171; font-family: "Times New Roman","serif"; font-size: 10pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: PT-BR;"><a href="http://www.sqlsaturday.com/viewsession.aspx?sat=147&sessionid=10290"><span style="border: 1pt windowtext; color: #0055ff; mso-border-alt: none windowtext 0cm; padding: 0cm; text-decoration: none; text-underline: none;">Demétrio Silva<br />
Uma introdução ao Powershell<br />
Level: Beginner</span></a><o:p></o:p></span></div>
</td>
<td style="background: rgb(211, 223, 238); border: 0px rgb(0, 0, 0); mso-background-themecolor: accent1; mso-background-themetint: 63; padding: 0cm 5.4pt;" valign="top">
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-yfti-cnfc: 64; vertical-align: baseline;">
<span style="color: #717171; font-family: "Times New Roman","serif"; font-size: 10pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: PT-BR;"><a href="http://www.sqlsaturday.com/viewsession.aspx?sat=147&sessionid=9924"><span style="border: 1pt windowtext; color: #0055ff; mso-border-alt: none windowtext 0cm; padding: 0cm; text-decoration: none; text-underline: none;">Marcondes Alexandre<br />
Desenvolvendo para Windows Azure<br />
Level: Intermediate</span></a><o:p></o:p></span></div>
</td>
</tr>
<tr style="mso-yfti-irow: 5;">
<td style="background-color: transparent; border: 0px rgb(0, 0, 0); padding: 0cm 5.4pt;" valign="top">
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-yfti-cnfc: 4; vertical-align: baseline;">
<b><span style="color: #717171; font-family: "Times New Roman","serif"; font-size: 10pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: PT-BR;">03:15 PM<o:p></o:p></span></b></div>
</td>
<td style="background-color: transparent; border: 0px rgb(0, 0, 0); padding: 0cm 5.4pt;" valign="top">
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; vertical-align: baseline;">
<span style="color: #717171; font-family: "Times New Roman","serif"; font-size: 10pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: PT-BR;"><a href="http://www.sqlsaturday.com/viewsession.aspx?sat=147&sessionid=11213"><span style="border: 1pt windowtext; color: #0055ff; mso-border-alt: none windowtext 0cm; padding: 0cm; text-decoration: none; text-underline: none;">Oduvaldo Zeferino<br />
Confio Ignite V8 – Otimização de Desempenho de SQL<br />
Level: Intermediate</span></a><o:p></o:p></span></div>
</td>
<td style="background-color: transparent; border: 0px rgb(0, 0, 0); padding: 0cm 5.4pt;" valign="top">
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; vertical-align: baseline;">
<span style="color: #717171; font-family: "Times New Roman","serif"; font-size: 10pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: PT-BR;"><a href="http://www.sqlsaturday.com/viewsession.aspx?sat=147&sessionid=9836"><span lang="EN-US" style="border: 1pt windowtext; color: #0055ff; mso-ansi-language: EN-US; mso-border-alt: none windowtext 0cm; padding: 0cm; text-decoration: none; text-underline: none;">Laerte Junior<br />
Troubleshooting SQL Server com PowerShell<br />
Level: Advanced</span></a></span><span lang="EN-US" style="color: #717171; font-family: "Times New Roman","serif"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: PT-BR;"><o:p></o:p></span></div>
</td>
</tr>
<tr style="mso-yfti-irow: 6;">
<td style="background: rgb(211, 223, 238); border: 0px rgb(0, 0, 0); mso-background-themecolor: accent1; mso-background-themetint: 63; padding: 0cm 5.4pt;" valign="top">
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-yfti-cnfc: 68; vertical-align: baseline;">
<b><span style="color: #717171; font-family: "Times New Roman","serif"; font-size: 10pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: PT-BR;">04:30 PM<o:p></o:p></span></b></div>
</td>
<td style="background: rgb(211, 223, 238); border: 0px rgb(0, 0, 0); mso-background-themecolor: accent1; mso-background-themetint: 63; padding: 0cm 5.4pt;" valign="top">
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-yfti-cnfc: 64; vertical-align: baseline;">
<span style="color: #717171; font-family: "Times New Roman","serif"; font-size: 10pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: PT-BR;"><a href="http://www.sqlsaturday.com/viewsession.aspx?sat=147&sessionid=10456"><span style="border: 1pt windowtext; color: #0055ff; mso-border-alt: none windowtext 0cm; padding: 0cm; text-decoration: none; text-underline: none;">Fabiano Neves Amorim<br />
7 maneiras diferentes para matar um DBA do coração<br />
Level: Intermediate</span></a><o:p></o:p></span></div>
</td>
<td style="background: rgb(211, 223, 238); border: 0px rgb(0, 0, 0); mso-background-themecolor: accent1; mso-background-themetint: 63; padding: 0cm 5.4pt;" valign="top">
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-yfti-cnfc: 64; vertical-align: baseline;">
<span style="color: #717171; font-family: "Times New Roman","serif"; font-size: 10pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: PT-BR;"><a href="http://www.sqlsaturday.com/viewsession.aspx?sat=147&sessionid=9028"><span style="border: 1pt windowtext; color: #0055ff; mso-border-alt: none windowtext 0cm; padding: 0cm; text-decoration: none; text-underline: none;">Marcus Vinícius
Bittencourt<br />
Problemas de fragmentação no Transaction Log<br />
Level: Intermediate</span></a><o:p></o:p></span></div>
</td>
</tr>
<tr style="mso-yfti-irow: 7;">
<td style="background-color: transparent; border: 0px rgb(0, 0, 0); padding: 0cm 5.4pt;" valign="top">
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-yfti-cnfc: 4; vertical-align: baseline;">
<b><span style="color: #717171; font-family: "Times New Roman","serif"; font-size: 10pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: PT-BR;">05:45 PM<o:p></o:p></span></b></div>
</td>
<td style="background-color: transparent; border: 0px rgb(0, 0, 0); padding: 0cm 5.4pt;" valign="top">
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; vertical-align: baseline;">
<span style="color: #717171; font-family: "Times New Roman","serif"; font-size: 10pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: PT-BR;"><a href="http://www.sqlsaturday.com/viewsession.aspx?sat=147&sessionid=9230"><span style="border: 1pt windowtext; color: #0055ff; mso-border-alt: none windowtext 0cm; padding: 0cm; text-decoration: none; text-underline: none;">Diego Nogare<br />
Integração completa do pacote de BI do SQL 2012<br />
Level: Intermediate</span></a><o:p></o:p></span></div>
</td>
<td style="background-color: transparent; border: 0px rgb(0, 0, 0); padding: 0cm 5.4pt;" valign="top">
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; vertical-align: baseline;">
<span style="color: #717171; font-family: "Times New Roman","serif"; font-size: 10pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: PT-BR;"><a href="http://www.sqlsaturday.com/viewsession.aspx?sat=147&sessionid=10750"><span lang="EN-US" style="border: 1pt windowtext; color: #0055ff; mso-ansi-language: EN-US; mso-border-alt: none windowtext 0cm; padding: 0cm; text-decoration: none; text-underline: none;">Denny Cherry<br />
Table Indexing for the .NET Developer<br />
Level: Intermediate</span></a></span><span lang="EN-US" style="color: #717171; font-family: "Times New Roman","serif"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: PT-BR;"><o:p></o:p></span></div>
</td>
</tr>
<tr style="mso-yfti-irow: 8; mso-yfti-lastrow: yes;">
<td style="background: rgb(211, 223, 238); border-color: rgb(0, 0, 0) rgb(0, 0, 0) rgb(79, 129, 189); border-style: none none solid; border-width: 0px 0px 1pt; mso-background-themecolor: accent1; mso-background-themetint: 63; mso-border-bottom-themecolor: accent1; padding: 0cm 5.4pt;" valign="top">
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-yfti-cnfc: 68; vertical-align: baseline;">
<b><span style="color: #717171; font-family: "Times New Roman","serif"; font-size: 10pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: PT-BR;">07:00 PM<o:p></o:p></span></b></div>
</td>
<td style="background: rgb(211, 223, 238); border-color: rgb(0, 0, 0) rgb(0, 0, 0) rgb(79, 129, 189); border-style: none none solid; border-width: 0px 0px 1pt; mso-background-themecolor: accent1; mso-background-themetint: 63; mso-border-bottom-themecolor: accent1; padding: 0cm 5.4pt;" valign="top">
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-yfti-cnfc: 64; vertical-align: baseline;">
<span style="color: #717171; font-family: "Times New Roman","serif"; font-size: 10pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: PT-BR;"><a href="http://www.sqlsaturday.com/viewsession.aspx?sat=147&sessionid=10709"><span lang="EN-US" style="border: 1pt windowtext; color: #0055ff; mso-ansi-language: EN-US; mso-border-alt: none windowtext 0cm; padding: 0cm; text-decoration: none; text-underline: none;">Fábio Ávila<br />
Closing Session and Raffle<br />
Level: Beginner</span></a></span><span lang="EN-US" style="color: #717171; font-family: "Times New Roman","serif"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: PT-BR;"><o:p></o:p></span></div>
</td>
<td style="background: rgb(211, 223, 238); border-color: rgb(0, 0, 0) rgb(0, 0, 0) rgb(79, 129, 189); border-style: none none solid; border-width: 0px 0px 1pt; mso-background-themecolor: accent1; mso-background-themetint: 63; mso-border-bottom-themecolor: accent1; padding: 0cm 5.4pt;" valign="top"></td>
</tr>
</tbody></table>
Cesar Blummhttp://www.blogger.com/profile/17590220931166448799noreply@blogger.com0tag:blogger.com,1999:blog-6100419445090790199.post-71822914461726351402012-06-28T10:46:00.000-03:002012-06-28T10:46:25.740-03:00Removendo caracteres de nova linha e tab em consultas no Oracle<br />
Para remover caracteres de nova linha e tabulação (TAB) em textos no Oracle utilize a função REPLACE.<br />
<br />
Sintaxe da função:<br />
<i>REPLACE (String_Original, String_para_Alterar, [String_Destino])</i><br />
<span class="Apple-tab-span" style="white-space: pre;"> </span><br />
<span class="Apple-tab-span" style="white-space: pre;"> </span><i>String_Original</i>: Conteúdo original<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span><i>String_para_Alterar</i>: String que será pesquisa na String_Original<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span><i>String_Destino</i>: parâmetro opcional que indica qual conteúdo deve ficar em todas as ocorrências de String_para_Alterar na String_Original.<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span><br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>Exemplo: Select Replace('Fusca 1980','1980','1985') Retorna: 'Fusca 1985'<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span> Select Replace('Fusca 1980','1980') Retorna: 'Fusca '<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span><br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>Para resolver o problema proposto neste post são "encadeados" vários replaces para obter o resultado esperado:<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span><br />
<i>REPLACE(REPLACE(REPLACE(String_Original, CHR(10)), CHR(13)), CHR(9)) </i><br />Cesar Blummhttp://www.blogger.com/profile/17590220931166448799noreply@blogger.com0tag:blogger.com,1999:blog-6100419445090790199.post-87802327280790816512012-06-25T10:46:00.000-03:002012-06-25T10:47:41.448-03:00SQL Saturday 147<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgrJtv-8e_Hrs00RJ5UakIPqNSYcFqd6BMZAP8u9kvpl0qPalF_gHdOnvq6FOuUlV2JSTPLugtTmtPBcMDZI1ceZbEx9u5R2trM345we9fHWjVa9OOSbrskeMWxcU7F13MVW08fbWYT15DV/s1600/sqlsat147_web.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgrJtv-8e_Hrs00RJ5UakIPqNSYcFqd6BMZAP8u9kvpl0qPalF_gHdOnvq6FOuUlV2JSTPLugtTmtPBcMDZI1ceZbEx9u5R2trM345we9fHWjVa9OOSbrskeMWxcU7F13MVW08fbWYT15DV/s1600/sqlsat147_web.png" /></a>Nos dias 24 e 25 de agosto de 2012 ocorrerá em Recife a conferência SQL Saturday 147 (<a href="http://www.sqlsaturday.com/147/eventhome.aspx">http://www.sqlsaturday.com/147/eventhome.aspx</a>).<br />
<br />
SQL Saturday são eventos de um dia inteiro de treinamento gratuito, com uma grande variedade de temas e com vários níveis de conhecimento, para atender todos os interessados em SQL Server.<br />
<br />
Este evento é organizado pelo SQL Pass (<a href="http://www.sqlpass.org/">http://www.sqlpass.org/</a>) que é uma organização independente de profissionais SQL Server com mais de 100.000 afiliados no mundo.<br />
<br />
No dia 25 as 11:30 estarei apresentando junto com o Marcus Vinicius Bittencourt (@mvbitt) como fazer replicação de informações no SQL Server e demonstrando na prática como replicar informações do Oracle para o SQL Server.<br />
<br />
Neste link: <a href="http://www.sqlsaturday.com/147/schedule.aspx">http://www.sqlsaturday.com/147/schedule.aspx</a> tem a agenda do evento com as palestras disponíveis.<br />
<br />
Este evento é gratuito, portanto se tiveres interesse se inscreva logo para garantir a sua vaga.Cesar Blummhttp://www.blogger.com/profile/17590220931166448799noreply@blogger.com0tag:blogger.com,1999:blog-6100419445090790199.post-61582636967910984822012-06-01T17:14:00.000-03:002012-06-01T17:14:12.510-03:00SQL Server 2012 Restrições na Sintaxe do Raiserror Ao migrar um banco de dados SQL Server 2008 R2 para outro servidor com a versão SQL Server 2012, nos deparamos com alguns cancelamentos logo após a autenticação do aplicativo.<br />
<br /> Após uma análise de onde ocorria o problema chegamos em uma trigger que tinha os tratamentos de erros no formato:<br />
<br /><em>RAISERROR integer 'string'</em><br />
<br /> Não foi dificil depois disto concluir que este formato foi descontinuado nesta versão do banco de dados, devendo agora ser utilizado como RAISERROR(...).<br />
<br />
Para maiores detalhes sobre as features descontinuadas no SQL Server 2012, acesse o link: <a href="http://technet.microsoft.com/en-us/library/ms144262(SQL.110).aspx">http://technet.microsoft.com/en-us/library/ms144262(SQL.110).aspx</a>Cesar Blummhttp://www.blogger.com/profile/17590220931166448799noreply@blogger.com1tag:blogger.com,1999:blog-6100419445090790199.post-38342199226265823302012-05-16T11:56:00.001-03:002015-04-02T08:24:07.183-03:00Script de backup de todas as databases no SQL ServerNas versões pagas do SQL Server, existem assistentes que facilitam o agendamento de tarefas de backup que gravem todos os databases existentes, sem a preocupação de que ao criar novas databases precise alterar estes procedimentos.<br />
Em ambientes sem estes recursos, ou onde você prefirar criar manualmente estas atividades, sugiro o script abaixo, que irá selecionar todos os databases existentes na instância do servidor e executar para cada um a instrução de backup.<br />
Para automatizar este procedimento copie o código abaixo e coloque em um arquivo neste exemplo chamado BACKUP.SQL.<br />
Observe no início do script que tem o destino dos arquivos gerados pela rotina, altere conforme a tua necessidade.<br />
<br />
<pre class="brush:sql">DECLARE @name VARCHAR(150) -- Nome do Database
DECLARE @path VARCHAR(256) -- Caminho do arquivo de backup
DECLARE @fileName VARCHAR(256) -- Arquivo do backup
-- Define caminho de destino do backup
SET @path = 'D:\Backup\'
-- Cria um cursor para selecionar todas as databases,
-- excluindo model, msdb e tempdb
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('model','msdb','tempdb')
-- Abre o cursor e faz a primeira leitura
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
-- Loop de leitura das databases selecionadas
WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + '.BAK'
-- Executa o backup para o database
BACKUP DATABASE @name TO DISK = @fileName WITH FORMAT;
FETCH NEXT FROM db_cursor INTO @name
END
-- Libera recursos alocados pelo cursor
CLOSE db_cursor
DEALLOCATE db_cursor
</pre>
<div class="brush:sql">
A seguir crie outro arquivo chamado BACKUP.BAT com o conteúdo abaixo:</div>
<div class="brush:sql">
</div>
<pre class="brush:sql">osql -E -S <em>Servidor</em> -i "d:\backup\sql\BackupBancos.SQL"
</pre>
Agora basta criar um agendamento do próprio Windows chamando o arquivo BACKUP.BAT.Cesar Blummhttp://www.blogger.com/profile/17590220931166448799noreply@blogger.com14tag:blogger.com,1999:blog-6100419445090790199.post-64969020089462660152012-05-04T10:25:00.001-03:002012-05-04T10:25:56.941-03:00Webcast sobre Replicação no SQL ServerNo dia 04 de maio de 2012 será realizada uma webcast para tratar de replicação, junto com o Marcus Vinicius @mvbitt serei um dos apresentadores do evento, quem tiver dispobilidade abaixo resumo do evento e link de inscrição:<br />
<br />
<div style="background: white;">
<strong><span style="color: #4e4e4e; font-family: "Trebuchet MS","sans-serif";">Sexta – Feira (04/05)</span></strong><span style="color: #4e4e4e; font-family: "Trebuchet MS","sans-serif"; font-size: 10pt; line-height: 150%;"><o:p></o:p></span></div>
<br />
<div style="background: white;">
<strong><span style="color: #4e4e4e; font-family: "Trebuchet MS","sans-serif"; font-size: 10pt; line-height: 150%;">Palestrante: </span></strong><span style="color: #4e4e4e; font-family: "Trebuchet MS","sans-serif"; font-size: 10pt; line-height: 150%;">Cesar Blumm (<a href="https://twitter.com/#!/cesarblumm"><span style="color: blue;">@cesarblumm</span></a>)
e Marcus Vinícius Bittencourt (<a href="http://twitter.com/#!/mvbitt"><span style="color: blue;">@mvbitt</span></a>)
<br />
<strong><span style="font-family: "Trebuchet MS","sans-serif";">Palestra: </span></strong>Replicação
na Prática <br />
<strong><span style="font-family: "Trebuchet MS","sans-serif";">Descrição: </span></strong>Uma
visão geral sobre replicação e suas formas de publicações. Será criada uma
replicação na prática passo-a-passo. <br />
<strong><span style="font-family: "Trebuchet MS","sans-serif";">Horário: </span></strong>20:00
a 21:00 <br />
<strong><span style="font-family: "Trebuchet MS","sans-serif";">Link para
inscrição: </span></strong><a href="https://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032512282&Culture=pt-BR" title="https://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032512282&Culture=pt-BR"><strong><span style="color: #0071bb; font-family: "Trebuchet MS","sans-serif";">https://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032512282&Culture=pt-BR</span></strong></a><o:p></o:p></span></div>Cesar Blummhttp://www.blogger.com/profile/17590220931166448799noreply@blogger.com0tag:blogger.com,1999:blog-6100419445090790199.post-38418908417164951972012-04-21T20:33:00.000-03:002012-04-21T20:34:56.339-03:00Reunião do SQL Server RS em Abril/2012<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi7pyKfmZXpo95Lxnb6l5CDVy_z3JEXARCW1pGAxG63xqGsv5Jcdy-vfrlaPbfWWiEqr_RTINewW7HVt8D23fAM3HnexWVUMhUCOmD-_ANL30AW1WLqq9PGCaB5gja8ppL1pjxcDtNPmK2Q/s1600/Cabecalho-sql_server_rs5.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="65" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi7pyKfmZXpo95Lxnb6l5CDVy_z3JEXARCW1pGAxG63xqGsv5Jcdy-vfrlaPbfWWiEqr_RTINewW7HVt8D23fAM3HnexWVUMhUCOmD-_ANL30AW1WLqq9PGCaB5gja8ppL1pjxcDtNPmK2Q/s320/Cabecalho-sql_server_rs5.jpg" width="320" /></a></div>
No próximo dia 27/04/2012 será realizado mais um encontro de usuários de SQL Server do RS, na Flexxo (Av. Rio Branco 105, Caxias do Sul - RS).<br />
O Crespi (Especialista SQL Server) falará sobre Troubleshoot (Database Engine).<br />
<br />
Maiores informações no site do grupo: <a href="http://sqlserverrs.com.br/">http://sqlserverrs.com.br</a>.Cesar Blummhttp://www.blogger.com/profile/17590220931166448799noreply@blogger.com0tag:blogger.com,1999:blog-6100419445090790199.post-36751795757545440032012-01-02T14:59:00.000-02:002012-01-02T14:59:52.146-02:00Insert condicional em várias tabelas<div style="text-align: justify;">
No post anterior mostrei com usar o INSERT para inserir em várias tabelas. Utilizado daquela maneira, cada linha é inserida em todas as tabelas incluídas no comando. Mas outra variação de INSERT permite utilizar expressões lógicas para indicar em qual tabela uma linha deve ser inserida. Sua sintaxe é:</div>
<br />
<span style="font-family: 'Courier New', Courier, monospace;"><b>INSERT[ALL]</b></span><br />
<span style="font-family: 'Courier New', Courier, monospace;"><b>WHEN <condição 1="" lógica=""></condição></b></span><br />
<span style="font-family: 'Courier New', Courier, monospace;"><b>THEN INTO <tabela 1=""> VALUES (lista de valores)</tabela></b></span><br />
<br />
<span style="font-family: 'Courier New', Courier, monospace;"><b>WHEN <condição 2="" lógica=""></condição></b></span><br />
<span style="font-family: 'Courier New', Courier, monospace;"><b>THEN INTO <tabela 2=""> VALUES (lista de valores)</tabela></b></span><br />
<br />
<span style="font-family: 'Courier New', Courier, monospace;"><b>...</b></span><br />
<span style="font-family: 'Courier New', Courier, monospace;"><b>[ELSE INTO <tabela n=""> VALUES (lista de valores)]</tabela></b></span><br />
<span style="font-family: 'Courier New', Courier, monospace;"><b>SELECT .... ;</b></span><br />
<br />
<div style="text-align: justify;">
Cada condição lógica controla se uma linha específica deve ser inserida na respectiva tabela. a palavra chave ALL indica se todas as condições lógicas devem ser avaliadas, permitindo que a linha seja inserida em mais de uma tabela (sempre que a condição for verdadeira). Se ALL for omitida, a linha é inserida na primeira tabela cuja respectiva condição lógica for verdadeira. A claúsula ELSE é opcional permite inserir a linha na tabela caso todas as condições lógicas resultem falso. Uma consulta SELECT é obrigatória e pode ser simples ou tão complexa quanto necessário.</div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
Por exemplo, digamos que há três tabelas VENDAS, VENDAS_2011 e VENDAS_HIST. a primeira armazena vendas realizadas em 2012, a segunda vendas de 2011 e a terceira vendas realizadas em qualquer outro ano. As três tabelas tem exatamente a mesma estrutura. O INSERT abaixo utiliza as condições lógicas para definir em qual tabela cada linha gerada pelo SELECT deve ser inserida.</div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
</div>
<b><span style="font-family: 'Courier New', Courier, monospace;">INSERT </span></b><br />
<b><span style="font-family: 'Courier New', Courier, monospace;"> WHEN EXTRACT(YEAR FROM data_venda) = 2012 THEN</span></b><br />
<b><span style="font-family: 'Courier New', Courier, monospace;"> INTO vendas VALUES (vendas_id, data_venda, valor)</span></b><br />
<b><span style="font-family: 'Courier New', Courier, monospace;"> WHEN EXTRACT(YEAR FROM data_venda) = 2011 THEN</span></b><br />
<b><span style="font-family: 'Courier New', Courier, monospace;"> INTO vendas_2011 VALUES (vendas_id, data_venda, valor*0.95)</span></b><br />
<b><span style="font-family: 'Courier New', Courier, monospace;"> ELSE INTO vendas_hist VALUES (vendas_id, data_venda, valor*0.9)</span></b><br />
<b><span style="font-family: 'Courier New', Courier, monospace;">SELECT level vendas_id,</span></b><br />
<b><span style="font-family: 'Courier New', Courier, monospace;"> TRUNC(sysdate - dbms_random.value(0, 500)) data_venda,</span></b><br />
<b><span style="font-family: 'Courier New', Courier, monospace;"> TRUNC(dbms_random.value(1000, 2000), 2) valor</span></b><br />
<b><span style="font-family: 'Courier New', Courier, monospace;"> FROM dual</span></b><br />
<b><span style="font-family: 'Courier New', Courier, monospace;"> CONNECT BY level <= 100; </span></b><br />
<br />
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
O SELECT resulta em 100 linhas representando vendas realizadas entre hoje e 500 dias atrás e valor entre 1000 e 2000. As condições lógicas no INSERT controlam em qual tabela cada linha deve ser inserida. Nesta caso, como cada linha deve ser inserida em apenas uma tabela, a palavra chave ALL é desnecessária. Note que o valor de vendas é multiplicado por diferentes fatores em cada INSERT, apenas para demonstrar que é possível alterar os valores. Também seria possível utilizar tabelas com diferentes colunas. </div>
<br />
Há algumas restrições quanto ao SELECT, a mais relevante é quanto ao uso de sequences. O comando abaixo retorna um erro:<br />
<br />
<br />
<span style="font-family: 'Courier New', Courier, monospace;"><b>INSERT </b></span><br />
<span style="font-family: 'Courier New', Courier, monospace;"><b> WHEN EXTRACT(YEAR FROM data_venda) = 2012 THEN</b></span><br />
<span style="font-family: 'Courier New', Courier, monospace;"><b> INTO vendas VALUES (vendas_id, data_venda, valor)</b></span><br />
<span style="font-family: 'Courier New', Courier, monospace;"><b> WHEN EXTRACT(YEAR FROM data_venda) = 2011 THEN</b></span><br />
<span style="font-family: 'Courier New', Courier, monospace;"><b> INTO vendas_2011 VALUES (vendas_id, data_venda, valor*0.95)</b></span><br />
<span style="font-family: 'Courier New', Courier, monospace;"><b> ELSE INTO vendas_hist VALUES (vendas_id, data_venda, valor*0.9)</b></span><br />
<span style="font-family: 'Courier New', Courier, monospace;"><b>SELECT <span style="color: red;">vendas_seq.nextval</span> vendas_id,</b></span><br />
<span style="font-family: 'Courier New', Courier, monospace;"><b> TRUNC(sysdate - dbms_random.value(0, 500)) data_venda,</b></span><br />
<span style="font-family: 'Courier New', Courier, monospace;"><b> TRUNC(dbms_random.value(1000, 2000), 2) valor</b></span><br />
<span style="font-family: 'Courier New', Courier, monospace;"><b> FROM dual</b></span><br />
<span style="font-family: 'Courier New', Courier, monospace;"><b> CONNECT BY level <= 100;</b></span><br />
<br />
<br />
<span style="font-family: 'Courier New', Courier, monospace;"><b>SQL Error: ORA-02287: sequence number not allowed here</b></span><br />
<span style="font-family: 'Courier New', Courier, monospace;"><b>02287. 00000 - "sequence number not allowed here"</b></span><br />
<span style="font-family: 'Courier New', Courier, monospace;"><b>*Cause: The specified sequence number (CURRVAL or NEXTVAL) is inappropriate</b></span><br />
<span style="font-family: 'Courier New', Courier, monospace;"><b> here in the statement.</b></span><br />
<span style="font-family: 'Courier New', Courier, monospace;"><b>*Action: Remove the sequence number.</b></span><br />
<br />
Entre as soluções possíveis, uma das simples é criar uma função para encapsular a sequence e depois usar a função no comando SELECT.<br />
<br />
<br />
<span style="font-family: 'Courier New', Courier, monospace;"><b>CREATE OR REPLACE</b></span><br />
<span style="font-family: 'Courier New', Courier, monospace;"><b> FUNCTION new_vendas_id</b></span><br />
<span style="font-family: 'Courier New', Courier, monospace;"><b> RETURN NUMBER</b></span><br />
<span style="font-family: 'Courier New', Courier, monospace;"><b> IS</b></span><br />
<span style="font-family: 'Courier New', Courier, monospace;"><b> retVal NUMBER;</b></span><br />
<span style="font-family: 'Courier New', Courier, monospace;"><b> BEGIN</b></span><br />
<span style="font-family: 'Courier New', Courier, monospace;"><b> SELECT vendas_seq.nextval INTO retVal FROM dual;</b></span><br />
<span style="font-family: 'Courier New', Courier, monospace;"><b> RETURN retVal;</b></span><br />
<span style="font-family: 'Courier New', Courier, monospace;"><b> END;</b></span><br />
<span style="font-family: 'Courier New', Courier, monospace;"><b><br /></b></span><br />
<br />
<span style="font-family: 'Courier New', Courier, monospace;"><b>INSERT </b></span><br />
<span style="font-family: 'Courier New', Courier, monospace;"><b> WHEN EXTRACT(YEAR FROM data_venda) = 2012 THEN</b></span><br />
<span style="font-family: 'Courier New', Courier, monospace;"><b> INTO vendas VALUES (vendas_id, data_venda, valor)</b></span><br />
<span style="font-family: 'Courier New', Courier, monospace;"><b> WHEN EXTRACT(YEAR FROM data_venda) = 2011 THEN</b></span><br />
<span style="font-family: 'Courier New', Courier, monospace;"><b> INTO vendas_2011 VALUES (vendas_id, data_venda, valor*0.95)</b></span><br />
<span style="font-family: 'Courier New', Courier, monospace;"><b> ELSE INTO vendas_hist VALUES (vendas_id, data_venda, valor*0.9)</b></span><br />
<span style="font-family: 'Courier New', Courier, monospace;"><b>SELECT <span style="color: red;">new_vendas_id</span> vendas_id,</b></span><br />
<span style="font-family: 'Courier New', Courier, monospace;"><b> TRUNC(sysdate - dbms_random.value(0, 500)) data_venda,</b></span><br />
<span style="font-family: 'Courier New', Courier, monospace;"><b> TRUNC(dbms_random.value(1000, 2000), 2) valor</b></span><br />
<span style="font-family: 'Courier New', Courier, monospace;"><b> FROM dual</b></span><br />
<span style="font-family: 'Courier New', Courier, monospace;"><b> CONNECT BY level <= 100; </b></span><br />
<br />
<br />
<br />
Esta solução para contornar o erro ORA-02287 pode ser útil em várias outras situações não relacionadas ao INSERT.<br />
<br />
<br />
<br />
<br />Miguel Fornarihttp://www.blogger.com/profile/14892590771484626747noreply@blogger.com0tag:blogger.com,1999:blog-6100419445090790199.post-85974948598474979172011-12-23T01:15:00.000-02:002011-12-23T01:15:00.226-02:00Insert em várias tabelas<div style="text-align: justify;">
O comando INSERT é amplamente utilizado para inserir dados em tabelas. Com pequenas variações, há duas formas bastante conhecidas:</div>
<br />
<span style="font-family: 'Courier New', Courier, monospace;"><b>I<span style="font-size: x-small;">NSERT INTO Contabil.Vendas(Id, Produto_ID, Qty, Data_Venda)</span></b></span><br />
<span style="font-family: 'Courier New', Courier, monospace; font-size: x-small;"><b>VALUES (Contabil.Vendas_Seq.Nextval, 1020, 5, Sysdate);</b></span><br />
<br />
e<br />
<br />
<span style="font-family: 'Courier New', Courier, monospace;"><b>I<span style="font-size: x-small;">NSERT INTO Data_Whse.Vendas</span></b></span><br />
<span style="font-family: 'Courier New', Courier, monospace; font-size: x-small;"><b>(Produto_ID, Produto_Grupo_ID, Qty, Data_Venda)</b></span><br />
<span style="font-family: 'Courier New', Courier, monospace; font-size: x-small;"><b>SELECT Produto_ID, Produto_Grupo_ID, Qty, trunc(Data_Venda, 'HH') </b></span><br />
<span style="font-family: 'Courier New', Courier, monospace; font-size: x-small;"><b> FROM Contabil.Vendas V,</b></span><br />
<span style="font-family: 'Courier New', Courier, monospace; font-size: x-small;"><b> Contabil.Produtos P</b></span><br />
<span style="font-family: 'Courier New', Courier, monospace; font-size: x-small;"><b> WHERE V.Produto_ID = P.Produto_ID</b></span><br />
<span style="font-family: 'Courier New', Courier, monospace; font-size: x-small;"><b> AND Trunc(V.DataVenda) = Trunc(Sysdate)-1;</b></span><br />
<br />
<div style="text-align: justify;">
A primeira forma insere uma única linha na tabela Vendas (esquema Contabil); a segunda forma insere muitas linhas na tabela Vendas (esquema Data_Whse). Tipicamente a primeira seria parte da aplicação, a segunda de um processo diário que copia dados de um esquema para outro (Contabil para Data_Whse).</div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
Mas o Oracle permite uma variação do comando INSERT que poderia ser muito útil numa sitaução semelhante porque permite, em um único comando, inserir dados em várias tabelas. </div>
<br />
<span style="font-family: 'Courier New', Courier, monospace;"><b>I<span style="font-size: x-small;">NSERT ALL </span></b></span><br />
<b><span style="font-size: x-small;"><span style="font-family: 'Courier New', Courier, monospace;"> INTO </span><span style="font-family: 'Courier New', Courier, monospace;">Contabil.Vendas(Id, Produto_ID, Qty, Data_Venda)</span></span></b><br />
<span style="font-family: 'Courier New', Courier, monospace;"><b><span style="font-size: x-small;"> (Vendas_ID, Produto_ID, Qty, Data_Venda)</span></b></span><br />
<b><span style="font-size: x-small;"><span style="font-family: 'Courier New', Courier, monospace;"> INTO </span><span style="font-family: 'Courier New', Courier, monospace;">Data_Whse.Vendas</span><span style="font-family: 'Courier New', Courier, monospace;">(Produto_ID, Produto_Grupo_ID, Qty, Data_Venda)</span></span></b><br />
<b style="font-family: 'Courier New', Courier, monospace;"><span style="font-size: x-small;"> (Produto_ID, Produto_Grupo_ID, Qty, Trunc(Data_Venda) )</span></b><br />
<b style="font-family: 'Courier New', Courier, monospace;"><span style="font-size: x-small;">SELECT 20 as Vendas_ID, P.Produto_ID, P.Produto_Grupo_ID, </span></b><br />
<b style="font-family: 'Courier New', Courier, monospace;"><span style="font-size: x-small;"> 5 as QTY, Sysdate as Data_Venda</span></b><br />
<b style="font-family: 'Courier New', Courier, monospace;"><span style="font-size: x-small;"> FROM Contabil.Produto P</span></b><br />
<b style="font-family: 'Courier New', Courier, monospace;"><span style="font-size: x-small;"> WHERE P.Produto_ID = 1;</span></b><br />
<b style="font-family: 'Courier New', Courier, monospace;"> </b><br />
<div style="text-align: justify;">
<span style="font-family: inherit;">Com este INSERT, duas linhas são inseridas, uma em cada tabela, num único passo. A lista de colunas em cada tabela pode ser diferente e os valores podem ser manipulados, como n caso da coluna Data_Venda: uma tabela receberá o valor Sysdate, outra o valor truncado.</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;"><br /></span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">A sintaxe permite inserir em muitas tabelas ao mesmo tempo. O SELECT pode retornar várias linhas. Mas a combinação correta é sempre INSERT ALL/SELECT.</span><b style="font-family: 'Courier New', Courier, monospace;"> </b></div>
<div style="text-align: justify;">
<b style="font-family: 'Courier New', Courier, monospace;"><br /></b></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">Há algumas poucas restrições, a mais importante é que o SELECT não pode utilizar uma sequence diretamente. Note que coloquei o valor 20 (fixo) para Vendas_ID. É óbvio que isto funciona porque o SELECT retorna uma única linha neste exemplo.</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;"><br /></span></div>
<div style="text-align: justify;">
Mas digamos que 5 itens de cada produto da empresa tenha sido vendido. Neste caso o SELECT teria que retornar muitas linhas e é necessário utilizar a sequence. Eis a solução:</div>
<br />
<br />
<span style="font-family: 'Courier New', Courier, monospace;"><b>I<span style="font-size: x-small;">NSERT ALL </span></b></span><br />
<b><span style="font-size: x-small;"><span style="font-family: 'Courier New', Courier, monospace;"> INTO </span><span style="font-family: 'Courier New', Courier, monospace;">Contabil.Vendas(Id, Produto_ID, Qty, Data_Venda)</span></span></b><br />
<span style="font-size: x-small;"><span style="font-family: 'Courier New', Courier, monospace;"><b> (</b></span>
<b style="font-family: 'Courier New', Courier, monospace;">Contabil.Vendas_Seq.Nextval</b> <span style="font-family: 'Courier New', Courier, monospace;"><b>, Produto_ID, Qty, Data_Venda)</b></span></span><br />
<b><span style="font-size: x-small;"><span style="font-family: 'Courier New', Courier, monospace;"> INTO </span><span style="font-family: 'Courier New', Courier, monospace;">Data_Whse.Vendas</span><span style="font-family: 'Courier New', Courier, monospace;">(Produto_ID, Produto_Grupo_ID, Qty, Data_Venda)</span></span></b><br />
<b style="font-family: 'Courier New', Courier, monospace;"><span style="font-size: x-small;"> (Produto_ID, Produto_Grupo_ID, Qty, Trunc(Data_Venda, 'HH') )</span></b><br />
<b style="font-family: 'Courier New', Courier, monospace;"><span style="font-size: x-small;">SELECT P.Produto_ID, P.Produto_Grupo_ID, </span></b><br />
<b style="font-family: 'Courier New', Courier, monospace;"><span style="font-size: x-small;"> 5 as QTY, Sysdate as Data_Venda</span></b><br />
<b style="font-family: 'Courier New', Courier, monospace;"><span style="font-size: x-small;"> FROM Contabil.Produto P;</span></b><br />
<b style="font-family: 'Courier New', Courier, monospace;"><br /></b><br />
<div style="text-align: justify;">
<span style="font-family: inherit;">Potencialmente, este SELECT reduz a necessidade de escrever código complementar, seja em triggers ou em outras procedures. O dado é inserido a partir de um único ponto em todas as tabelas onde é necessário, facilitando a compreensão e manutenção do sistema. E, claro, trata-se de uma única transação, ou seja, ou insere em todas as tabelas ou não insere em nenhuma.</span><b style="font-family: 'Courier New', Courier, monospace;"> </b></div>
<br />
<br />Miguel Fornarihttp://www.blogger.com/profile/14892590771484626747noreply@blogger.com0tag:blogger.com,1999:blog-6100419445090790199.post-71758075471262205832011-12-20T15:47:00.000-02:002011-12-20T15:47:58.224-02:00Particionando arquivos de exportação no OracleO utilitário exp do oracle é muitas vezes utilizado como uma ferramenta auxiliar para estratégias de backup. <br />
Algumas vezes o espaço disponível em um único disco não é suficiente para armazenar todo o conteúdo exportado pelo utilitário, o texto abaixo explica como resolver esta situação e complementa uma dúvida que recebi por e-mail que perguntava como particionar o arquivo de exportação em vários arquivos.<br />
Como exemplo considere que o exp deva gerar um arquivo de 9G e precisaria quebrar em três arquivos de 3G.<br />
<br />
Para resolver esta situação utilize os parâmetros <strong>file</strong> e <strong>filesize</strong>:<br />
<br />
<strong>File</strong> - relacione os nomes dos três arquivos que devem ser gerados, separados por vírgulas.<br />
<strong>Filesize</strong> - indique o tamanho máximo dos arquivos que serão gerados.<br />
<br />
Exemplo:<br />
exp <em>usuario/senha</em> file<em>=/tmp/arq01.dmp,/tmp1/arq02.dmp,/tmp2/arq03.dmp</em> log<em>=/tmp/exporta.log</em> filesize=<em>3G</em><br />
<br />
Quando a exportação precisar gerar um quarto arquivo (exportação maior do que 9G), se não estiver previsto no parâmetro file, o utilitário irá solicitar o nome dele na tela.Cesar Blummhttp://www.blogger.com/profile/17590220931166448799noreply@blogger.com4tag:blogger.com,1999:blog-6100419445090790199.post-52295380811730950882011-12-13T10:24:00.001-02:002011-12-13T10:24:27.772-02:00Há 100 anos no Polo Sul<table cellpadding="0" cellspacing="0" class="tr-caption-container" style="float: left; margin-right: 1em; text-align: left;"><tbody>
<tr><td style="text-align: center;"><a href="http://upload.wikimedia.org/wikipedia/commons/thumb/e/e3/Pole-observation.jpg/220px-Pole-observation.jpg" imageanchor="1" style="clear: left; margin-bottom: 1em; margin-left: auto; margin-right: auto;"><img border="0" src="http://upload.wikimedia.org/wikipedia/commons/thumb/e/e3/Pole-observation.jpg/220px-Pole-observation.jpg" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">Amundsen e equipe fazendo medições para<br />confirmar que chegaram ao local correto.</td></tr>
</tbody></table>
<div style="text-align: justify;">
Totalmente fora do assunto deste blog, mas podemos dizer que os protagonistas desta aventura são alguns dos nossos heróis preferidos. Pois amanhã, dia 14 de Dezembro de 2011, completa-se 100 anos da chegada de Amundsen ao Polo Sul!</div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
Amundsen pesquisou e experimentou alternativas para chegar ao Polo Sul. Escolheu trenós puxados por cachorros. Toda equipe e alguns cachorros sobreviveram o percurso completo, ida e volta.</div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
Seu concorrente, Scott, acreditava que com esforço e coragem superaria qualquer obstáculo. Chegou ao Polo Sul na metade de Janeiro, apenas para encontrar a bandeira da Noruega marcando o ponto exato. Não retornou a tempo para o acampamento e morreu congelado. </div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
Portanto, pode-se colocar muitas horas de esforço na solução de um problema, mas é fundamental ter conhecimento, explorar alternativas e escolher aquelas que garantem maior chance de sucesso. Inclusive em problemas de BD.</div>Miguel Fornarihttp://www.blogger.com/profile/14892590771484626747noreply@blogger.com3tag:blogger.com,1999:blog-6100419445090790199.post-28145739356099587002011-12-02T00:26:00.001-02:002011-12-02T01:07:29.269-02:00SELECT FROM SAMPLE<div style="text-align: justify;">
Um recurso muito interessante e pouco conhecido da clausula FROM é a opção SAMPLE. Ela deve ser acompanhada de um número entre (0,100), mas nunca nos limites 0 e 100. Este número indica a probabilidade individual de cada linha da tabela retornar na resposta da query. Por exemplo:</div>
<div>
<br /></div>
<div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="color: blue;">select </span>obj_id, object_name</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> <span class="Apple-style-span" style="color: blue;">from</span> my_objs <span class="Apple-style-span" style="color: blue;">sample</span>(10);</span></div>
</div>
<div>
<br /></div>
<div>
Detalhando um pouco, priemiro vou criar uma tabela e verificar o número de linhas.</div>
<div>
<br /></div>
<div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="color: blue;">create sequence</span> my_obj_seq;</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><br /></span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="color: blue;">create table</span> my_objs <span class="Apple-style-span" style="color: blue;">as</span></span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="color: blue;">select</span> my_obj_seq.nextval obj_id, owner, object_name, object_id</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> <span class="Apple-style-span" style="color: blue;">from</span> all_objects;</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> </span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="color: blue;">select </span>count(*)</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> <span class="Apple-style-span" style="color: blue;">from</span> my_objs;</span></div>
</div>
<div>
<br /></div>
<div>
No meu Oracle retornou 17.900. Agora tente algo simples como:</div>
<div>
<br /></div>
<div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="color: blue;">select </span>obj_id, object_name</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> <span class="Apple-style-span" style="color: blue;">from </span>my_objs;</span></div>
</div>
<div>
<br /></div>
<div>
Obviamente todas as linhas da tabela retornarão na resposta. Incluindo o SAMPLE, temos:</div>
<div>
<br /></div>
<div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="color: blue;">select</span> obj_id, object_name</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> <span class="Apple-style-span" style="color: blue;">from</span> my_objs <span class="Apple-style-span" style="color: blue;">sample</span>(10);</span></div>
</div>
<div>
<br /></div>
<div style="text-align: justify;">
E a chance de cada linha estar na resposta é de apenas 10%. Se executar repetidas vezes, o conjunto resposta será diferente a cada execução por que, para cada linha, o Oracle decide inclui-la ou não na resposta. Ou seja, a resposta passa a ser aleatória.</div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
Neste ponto, você executa:</div>
<div>
<br /></div>
<div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="color: blue;">select</span> count(*) <span class="Apple-style-span" style="color: blue;">from</span>( </span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="color: blue;">select </span>obj_id, object_name</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> <span class="Apple-style-span" style="color: blue;">from </span>my_objs <span class="Apple-style-span" style="color: blue;">sample</span>(10);</span></div>
</div>
<div>
<br /></div>
<div>
E a resposta é 1.755! Opa, mas isto não é 10% de 17.900. Repetindo o mesmo SELECT COUNT(*) a resposta é 1.829! E na terceira vez é 1.793! </div>
<div>
<br /></div>
<div>
Estaria tudo errado? Não deveria ser exatamente 10% do total de linhas na tabela, nesta caso exatos 1.790? </div>
<div>
<br /></div>
<div style="text-align: justify;">
O Oracle está correto. A chance de cada linha estar na resposta é 10%, mas eventualmente haverá mais linhas na resposta - digamos que deram sorte - ou menos linhas - estavam com azar. Se você precisar de um número exato, basta utilizar uma consulta aninhada:</div>
<div>
<br /></div>
<div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="color: blue;">select </span>* <span class="Apple-style-span" style="color: blue;">from</span>(</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="color: blue;">select</span> obj_id, object_name</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> <span class="Apple-style-span" style="color: blue;">from </span>my_objs <span class="Apple-style-span" style="color: blue;">sample</span>(100))</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="color: blue;">where</span> <span class="Apple-style-span" style="color: blue;">rownum</span> <= 10; </span></div>
</div>
<div>
<br /></div>
<div style="text-align: justify;">
Eu utilizei este recurso para selecionar um subconjunto aleatório de linhas da tabela. Isto fazia parte de um teste que seria repetido diversas vezes, mas era importante que o conjunto de dados fosse diferente a cada execução, afinal seria desnecessário calcular várias vezes o mesmo valor.</div>
<div>
<br /></div>
<div>
<br /></div>Miguel Fornarihttp://www.blogger.com/profile/14892590771484626747noreply@blogger.com6tag:blogger.com,1999:blog-6100419445090790199.post-20206113994843919942011-11-28T22:14:00.001-02:002011-11-28T22:19:34.555-02:00Sequence no SQL Server 2012<span style="font-family: Calibri;">Aproveitando o tema abordado pelo colega Miguel sobre <a href="http://sqlbrasil.blogspot.com/2011/11/alterar-valor-de-uma-sequence.html" target="_blank">“Alterar valor de uma Sequence” </a>, vou comentar que na versão do SQL Server 2012 foi
incluído este recurso já tão utilizado no Oracle.<o:p></o:p></span><br />
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="font-family: Calibri;">Para quem não conhece a sequence é um contador que gera
valores normalmente utilizados para inicializar chaves primárias de tabelas,
seguindo critérios estabelecidos no momento da criação da sequence.<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="font-family: Calibri;">Uma das vantagens sobre colunas do tipo Identity é que uma
sequence pode ser utilizada por várias tabelas.<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; tab-stops: 45.8pt 91.6pt 137.4pt 183.2pt 229.0pt 274.8pt 320.6pt 366.4pt 412.2pt 458.0pt 503.8pt 549.6pt 595.4pt 641.2pt 687.0pt 732.8pt; word-break: break-all;">
<span lang="EN-US" style="color: black; font-family: Consolas; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: PT-BR;">Sintaxe:<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; tab-stops: 45.8pt 91.6pt 137.4pt 183.2pt 229.0pt 274.8pt 320.6pt 366.4pt 412.2pt 458.0pt 503.8pt 549.6pt 595.4pt 641.2pt 687.0pt 732.8pt; word-break: break-all;">
<span lang="EN-US" style="color: black; font-family: Consolas; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: PT-BR;">CREATE SEQUENCE [schema_name . ]
sequence_name<o:p></o:p></span></div>
<span lang="EN-US" style="color: black; font-family: Consolas; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: PT-BR;"><span style="mso-spacerun: yes;"> </span>[
AS [ built_in_integer_type | user-defined_integer_type ] ]<o:p></o:p></span><br />
<span lang="EN-US" style="color: black; font-family: Consolas; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: PT-BR;"><span style="mso-spacerun: yes;"> </span>[
START WITH <constant> ]<o:p></o:p></constant></span><br />
<span lang="EN-US" style="color: black; font-family: Consolas; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: PT-BR;"><span style="mso-spacerun: yes;"> </span>[
INCREMENT BY <constant> ]<o:p></o:p></constant></span><br />
<span lang="EN-US" style="color: black; font-family: Consolas; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: PT-BR;"><span style="mso-spacerun: yes;"> </span>[ {
MINVALUE [ <constant> ] } | { NO MINVALUE } ]<o:p></o:p></constant></span><br />
<span lang="EN-US" style="color: black; font-family: Consolas; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: PT-BR;"><span style="mso-spacerun: yes;"> </span>[ {
MAXVALUE [ <constant> ] } | { NO MAXVALUE } ]<o:p></o:p></constant></span><br />
<span lang="EN-US" style="color: black; font-family: Consolas; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: PT-BR;"><span style="mso-spacerun: yes;"> </span>[
CYCLE | { NO CYCLE } ]<o:p></o:p></span><br />
<span lang="EN-US" style="color: black; font-family: Consolas; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: PT-BR;"><span style="mso-spacerun: yes;"> </span>[ {
CACHE [ <constant> ] } | { NO CACHE } ]<o:p></o:p></constant></span><br />
<span lang="EN-US" style="color: black; font-family: Consolas; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: PT-BR;"><span style="mso-spacerun: yes;"> </span></span><span style="color: black; font-family: Consolas; font-size: 10pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: PT-BR;">[ ; ]<o:p></o:p></span><br />
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="font-family: Calibri;">Para explicar o funcionamento deste recurso vou utilizar o
exemplo abaixo:<o:p></o:p></span></div>
<span style="font-family: Calibri;">Considere uma tabela com dois campos: MesAno e TotalSalario
que não possui chave, vamos incluir uma coluna ID do tipo inteiro, e
colocaremos como valor default o valor atual de uma Sequence (IdSalarios)
criada abaixo. Também serão atualizados todos os registros previamente existentes
com um valor da sequence:<o:p></o:p></span><br />
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-layout-grid-align: none;">
<span style="color: green; font-family: Consolas; font-size: 9.5pt;">-- Script de criação da Tabela</span><span style="font-family: Consolas; font-size: 9.5pt;"><o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-layout-grid-align: none;">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">CREATE</span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: blue;">TABLE</span> <span style="color: teal;">[dbo]</span><span style="color: grey;">.</span><span style="color: teal;">[TotalSalarios]</span><span style="color: grey;">(</span><o:p></o:p></span></div>
<span style="font-family: Consolas; font-size: 9.5pt;"><span style="mso-tab-count: 1;"> </span><span style="color: teal;">[MesAno]</span> <span style="color: teal;">[datetime]</span> <span style="color: grey;">NOT</span> <span style="color: grey;">NULL,</span><o:p></o:p></span><br />
<span style="font-family: Consolas; font-size: 9.5pt;"><span style="mso-tab-count: 1;"> </span><span style="color: teal;">[TotalSalario]</span> <span style="color: teal;">[decimal]</span><span style="color: grey;">(</span>11<span style="color: grey;">,</span> 2<span style="color: grey;">)</span> <span style="color: grey;">NULL);</span><o:p></o:p></span><br />
<br />
<table border="1" cellpadding="0" cellspacing="0" class="MsoTableLightShadingAccent1" style="border-collapse: collapse; border: currentColor; mso-border-bottom-alt: solid #4F81BD 1.0pt; mso-border-bottom-themecolor: accent1; mso-border-top-alt: solid #4F81BD 1.0pt; mso-border-top-themecolor: accent1; mso-padding-alt: 0cm 5.4pt 0cm 5.4pt; mso-yfti-tbllook: 1184;">
<tbody>
<tr style="mso-yfti-firstrow: yes; mso-yfti-irow: -1;">
<td style="background-color: transparent; border-color: rgb(79, 129, 189) rgb(0, 0, 0); border-style: solid none; border-width: 1pt 0px; mso-border-bottom-themecolor: accent1; mso-border-top-themecolor: accent1; padding: 0cm 5.4pt; width: 125.9pt;" valign="top" width="168">
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-yfti-cnfc: 5;">
<b><span style="color: #365f91; mso-themecolor: accent1; mso-themeshade: 191;"><span style="font-family: Calibri;">MesAno<o:p></o:p></span></span></b></div>
</td>
<td style="background-color: transparent; border-color: rgb(79, 129, 189) rgb(0, 0, 0); border-style: solid none; border-width: 1pt 0px; mso-border-bottom-themecolor: accent1; mso-border-top-themecolor: accent1; padding: 0cm 5.4pt; width: 77.95pt;" valign="top" width="104">
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-yfti-cnfc: 1;">
<b><span style="color: #365f91; mso-themecolor: accent1; mso-themeshade: 191;"><span style="font-family: Calibri;">TotalSalario<o:p></o:p></span></span></b></div>
</td>
</tr>
<tr style="mso-yfti-irow: 0;">
<td style="background: rgb(211, 223, 238); border: 0px rgb(0, 0, 0); mso-background-themecolor: accent1; mso-background-themetint: 63; padding: 0cm 5.4pt; width: 125.9pt;" valign="top" width="168">
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-yfti-cnfc: 68;">
<b><span style="color: #365f91; mso-themecolor: accent1; mso-themeshade: 191;"><span style="font-family: Calibri;">2011-01-01 00:00:00.000<o:p></o:p></span></span></b></div>
</td>
<td style="background: rgb(211, 223, 238); border: 0px rgb(0, 0, 0); mso-background-themecolor: accent1; mso-background-themetint: 63; padding: 0cm 5.4pt; width: 77.95pt;" valign="top" width="104">
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-yfti-cnfc: 64;">
<span style="color: #365f91; mso-themecolor: accent1; mso-themeshade: 191;"><span style="font-family: Calibri;">89500.00<o:p></o:p></span></span></div>
</td>
</tr>
<tr style="mso-yfti-irow: 1;">
<td style="background-color: transparent; border: 0px rgb(0, 0, 0); padding: 0cm 5.4pt; width: 125.9pt;" valign="top" width="168">
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-yfti-cnfc: 4;">
<b><span style="color: #365f91; mso-themecolor: accent1; mso-themeshade: 191;"><span style="font-family: Calibri;">2011-02-01 00:00:00.000<o:p></o:p></span></span></b></div>
</td>
<td style="background-color: transparent; border: 0px rgb(0, 0, 0); padding: 0cm 5.4pt; width: 77.95pt;" valign="top" width="104">
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt;">
<span style="color: #365f91; mso-themecolor: accent1; mso-themeshade: 191;"><span style="font-family: Calibri;">91200.00<o:p></o:p></span></span></div>
</td>
</tr>
<tr style="mso-yfti-irow: 2;">
<td style="background: rgb(211, 223, 238); border: 0px rgb(0, 0, 0); mso-background-themecolor: accent1; mso-background-themetint: 63; padding: 0cm 5.4pt; width: 125.9pt;" valign="top" width="168">
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-yfti-cnfc: 68;">
<b><span style="color: #365f91; mso-themecolor: accent1; mso-themeshade: 191;"><span style="font-family: Calibri;">2011-03-01 00:00:00.000<o:p></o:p></span></span></b></div>
</td>
<td style="background: rgb(211, 223, 238); border: 0px rgb(0, 0, 0); mso-background-themecolor: accent1; mso-background-themetint: 63; padding: 0cm 5.4pt; width: 77.95pt;" valign="top" width="104">
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-yfti-cnfc: 64;">
<span style="color: #365f91; mso-themecolor: accent1; mso-themeshade: 191;"><span style="font-family: Calibri;">93200.00<o:p></o:p></span></span></div>
</td>
</tr>
<tr style="mso-yfti-irow: 3;">
<td style="background-color: transparent; border: 0px rgb(0, 0, 0); padding: 0cm 5.4pt; width: 125.9pt;" valign="top" width="168">
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-yfti-cnfc: 4;">
<b><span style="color: #365f91; mso-themecolor: accent1; mso-themeshade: 191;"><span style="font-family: Calibri;">2011-04-01 00:00:00.000<o:p></o:p></span></span></b></div>
</td>
<td style="background-color: transparent; border: 0px rgb(0, 0, 0); padding: 0cm 5.4pt; width: 77.95pt;" valign="top" width="104">
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt;">
<span style="color: #365f91; mso-themecolor: accent1; mso-themeshade: 191;"><span style="font-family: Calibri;">97200.00<o:p></o:p></span></span></div>
</td>
</tr>
<tr style="mso-yfti-irow: 4;">
<td style="background: rgb(211, 223, 238); border: 0px rgb(0, 0, 0); mso-background-themecolor: accent1; mso-background-themetint: 63; padding: 0cm 5.4pt; width: 125.9pt;" valign="top" width="168">
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-yfti-cnfc: 68;">
<b><span style="color: #365f91; mso-themecolor: accent1; mso-themeshade: 191;"><span style="font-family: Calibri;">2011-05-01 00:00:00.000<o:p></o:p></span></span></b></div>
</td>
<td style="background: rgb(211, 223, 238); border: 0px rgb(0, 0, 0); mso-background-themecolor: accent1; mso-background-themetint: 63; padding: 0cm 5.4pt; width: 77.95pt;" valign="top" width="104">
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-yfti-cnfc: 64;">
<span style="color: #365f91; mso-themecolor: accent1; mso-themeshade: 191;"><span style="font-family: Calibri;">101250.00<o:p></o:p></span></span></div>
</td>
</tr>
<tr style="mso-yfti-irow: 5;">
<td style="background-color: transparent; border: 0px rgb(0, 0, 0); padding: 0cm 5.4pt; width: 125.9pt;" valign="top" width="168">
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-yfti-cnfc: 4;">
<b><span style="color: #365f91; mso-themecolor: accent1; mso-themeshade: 191;"><span style="font-family: Calibri;">2011-06-01 00:00:00.000<o:p></o:p></span></span></b></div>
</td>
<td style="background-color: transparent; border: 0px rgb(0, 0, 0); padding: 0cm 5.4pt; width: 77.95pt;" valign="top" width="104">
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt;">
<span style="color: #365f91; mso-themecolor: accent1; mso-themeshade: 191;"><span style="font-family: Calibri;">103000.00<o:p></o:p></span></span></div>
</td>
</tr>
<tr style="mso-yfti-irow: 6;">
<td style="background: rgb(211, 223, 238); border: 0px rgb(0, 0, 0); mso-background-themecolor: accent1; mso-background-themetint: 63; padding: 0cm 5.4pt; width: 125.9pt;" valign="top" width="168">
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-yfti-cnfc: 68;">
<b><span style="color: #365f91; mso-themecolor: accent1; mso-themeshade: 191;"><span style="font-family: Calibri;">2011-07-01 00:00:00.000<o:p></o:p></span></span></b></div>
</td>
<td style="background: rgb(211, 223, 238); border: 0px rgb(0, 0, 0); mso-background-themecolor: accent1; mso-background-themetint: 63; padding: 0cm 5.4pt; width: 77.95pt;" valign="top" width="104">
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-yfti-cnfc: 64;">
<span style="color: #365f91; mso-themecolor: accent1; mso-themeshade: 191;"><span style="font-family: Calibri;">103000.00<o:p></o:p></span></span></div>
</td>
</tr>
<tr style="mso-yfti-irow: 7; mso-yfti-lastrow: yes;">
<td style="background-color: transparent; border-color: rgb(0, 0, 0) rgb(0, 0, 0) rgb(79, 129, 189); border-style: none none solid; border-width: 0px 0px 1pt; mso-border-bottom-themecolor: accent1; padding: 0cm 5.4pt; width: 125.9pt;" valign="top" width="168">
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-yfti-cnfc: 4;">
<b><span style="color: #365f91; mso-themecolor: accent1; mso-themeshade: 191;"><span style="font-family: Calibri;">2011-08-01 00:00:00.000<o:p></o:p></span></span></b></div>
</td>
<td style="background-color: transparent; border-color: rgb(0, 0, 0) rgb(0, 0, 0) rgb(79, 129, 189); border-style: none none solid; border-width: 0px 0px 1pt; mso-border-bottom-themecolor: accent1; padding: 0cm 5.4pt; width: 77.95pt;" valign="top" width="104">
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt;">
<span style="color: #365f91; mso-themecolor: accent1; mso-themeshade: 191;"><span style="font-family: Calibri;">105020.00<o:p></o:p></span></span></div>
</td>
</tr>
</tbody></table>
T<span style="font-family: Calibri;">abela 1 - Conteúdo atual da tabela<o:p></o:p></span><br />
<span style="font-family: Consolas; font-size: 9.5pt;"><o:p> </o:p></span><br />
<span style="color: green; font-family: Consolas; font-size: 9.5pt;">-- Criação da Sequence com o nome
IdSalarios</span><span style="font-family: Consolas; font-size: 9.5pt;"><o:p></o:p></span><br />
<br />
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">CREATE</span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: teal;">SEQUENCE</span> <span style="color: teal;">dbo</span><span style="color: grey;">.</span><span style="color: teal;">IdSalarios</span> <span style="color: blue;">AS</span> <span style="color: blue;">INT</span> <o:p></o:p></span><br />
<span style="font-family: Consolas; font-size: 9.5pt;"><span style="mso-spacerun: yes;"> </span><span style="color: teal;">MINVALUE</span> 1<span style="mso-spacerun: yes;"> </span><span style="color: green;">-- Menor valor da Sequence</span><o:p></o:p></span><br />
<span style="font-family: Consolas; font-size: 9.5pt;"><span style="mso-spacerun: yes;"> </span><span style="color: teal;">MAXVALUE</span> 10000 <span style="color: green;">--<span style="mso-spacerun: yes;"> </span>Maior valor da sequence<span style="mso-spacerun: yes;"> </span></span><o:p></o:p></span><br />
<span style="font-family: Consolas; font-size: 9.5pt;"><span style="mso-spacerun: yes;"> </span><span style="color: teal;">START</span> <span style="color: blue;">WITH</span> 1<span style="mso-spacerun: yes;"> </span><span style="color: green;">-- Valor inicial
da Sequence</span><o:p></o:p></span><br />
<span style="font-family: Consolas; font-size: 9.5pt;"><span style="mso-spacerun: yes;"> </span><span style="color: teal;">CYCLE</span><span style="color: grey;">;</span><span style="mso-spacerun: yes;"> </span><span style="color: green;">-- Indica
para a sequence reiniciar do menor </span></span><br />
<span style="font-family: Consolas; font-size: 9.5pt;"><span style="color: green;"> -- valor (1) quando atingir o</span></span><br />
<span style="font-family: Consolas; font-size: 9.5pt;"><span style="color: green;"> -- maior valor
(10000)</span></span><br />
<br />
<span style="color: green; font-family: Consolas; font-size: 9.5pt;">-- Criação da coluna ID</span><span style="font-family: Consolas; font-size: 9.5pt;"><o:p></o:p></span><br />
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">Alter</span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: blue;">Table</span> <span style="color: teal;">TotalSalarios</span> <span style="color: blue;">Add</span> <span style="color: teal;">ID</span> <span style="color: blue;">int</span><span style="color: grey;">;</span><o:p></o:p></span><br />
<span style="font-family: Consolas; font-size: 9.5pt;"><o:p> </o:p></span><br />
<span style="color: green; font-family: Consolas; font-size: 9.5pt;">-- Criando uma constraint para definir
o valor da sequence como valor default da coluna ID</span><span style="font-family: Consolas; font-size: 9.5pt;"><o:p></o:p></span><br />
<span lang="EN-US" style="color: blue; font-family: Consolas; font-size: 9.5pt; mso-ansi-language: EN-US;">ALTER</span><span lang="EN-US" style="font-family: Consolas; font-size: 9.5pt; mso-ansi-language: EN-US;">
<span style="color: blue;">TABLE</span> <span style="color: teal;">TotalSalarios</span><span style="mso-spacerun: yes;"> </span><o:p></o:p></span><br />
<span lang="EN-US" style="font-family: Consolas; font-size: 9.5pt; mso-ansi-language: EN-US;"><span style="mso-spacerun: yes;"> </span><span style="color: blue;">ADD</span> <span style="color: blue;">CONSTRAINT</span> <span style="color: teal;">Seq_IDSalarios</span>
<span style="color: blue;">DEFAULT </span><span style="color: grey;">(</span><span style="color: blue;">NEXT</span> <span style="color: teal;">VALUE</span> <span style="color: blue;">FOR</span> <span style="color: teal;">IdSalarios</span><span style="color: grey;">)</span> <span style="color: blue;">FOR</span> <span style="color: teal;">Id</span><o:p></o:p></span><br />
<span style="font-family: Consolas; font-size: 9.5pt;"><o:p> </o:p></span><br />
<span style="color: green; font-family: Consolas; font-size: 9.5pt;">-- Inicializando os valores existentes
com o próximo valor da sequence</span><span style="font-family: Consolas; font-size: 9.5pt;"><o:p></o:p></span><br />
<span lang="EN-US" style="color: blue; font-family: Consolas; font-size: 9.5pt; mso-ansi-language: EN-US;">Update</span><span lang="EN-US" style="font-family: Consolas; font-size: 9.5pt; mso-ansi-language: EN-US;">
<span style="color: teal;">TotalSalarios</span> <o:p></o:p></span><br />
<span lang="EN-US" style="font-family: Consolas; font-size: 9.5pt; mso-ansi-language: EN-US;"><span style="mso-spacerun: yes;"> </span><span style="color: blue;">set</span> <span style="color: teal;">ID</span> <span style="color: grey;">=</span> <span style="color: blue;">Next</span> <span style="color: teal;">Value</span> <span style="color: blue;">For</span> <span style="color: teal;">IdSalarios</span><span style="color: grey;">;</span><o:p></o:p></span><br />
<span lang="EN-US" style="font-family: Consolas; font-size: 9.5pt; mso-ansi-language: EN-US;"><o:p> </o:p></span><br />
<span style="color: green; font-family: Consolas; font-size: 9.5pt;">-- Inserindo um registro novo deixando
sem informar valor para ID</span><span style="font-family: Consolas; font-size: 9.5pt;"><o:p></o:p></span><br />
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">Insert</span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: blue;">into</span> <span style="color: teal;">TotalSalarios</span><span style="color: blue;"> </span><span style="color: grey;">(</span><span style="color: teal;">MesAno</span><span style="color: grey;">,</span> <span style="color: teal;">TotalSalario</span><span style="color: grey;">)</span> <span style="color: blue;">Values</span><span style="color: grey;">(</span><span style="color: red;">'2011-09-01'</span><span style="color: grey;">,</span>11000<span style="color: grey;">);</span><o:p></o:p></span><br />
<br />
<br />
<table border="1" cellpadding="0" cellspacing="0" class="MsoTableLightShadingAccent1" style="border-collapse: collapse; border: currentColor; mso-border-bottom-alt: solid #4F81BD 1.0pt; mso-border-bottom-themecolor: accent1; mso-border-top-alt: solid #4F81BD 1.0pt; mso-border-top-themecolor: accent1; mso-padding-alt: 0cm 5.4pt 0cm 5.4pt; mso-yfti-tbllook: 1184;">
<tbody>
<tr style="mso-yfti-firstrow: yes; mso-yfti-irow: -1;">
<td style="background-color: transparent; border-color: rgb(79, 129, 189) rgb(0, 0, 0); border-style: solid none; border-width: 1pt 0px; mso-border-bottom-themecolor: accent1; mso-border-top-themecolor: accent1; padding: 0cm 5.4pt; width: 125.9pt;" valign="top" width="168">
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-yfti-cnfc: 5;">
<b><span style="color: #365f91; mso-themecolor: accent1; mso-themeshade: 191;"><span style="font-family: Calibri;">MesAno<o:p></o:p></span></span></b></div>
</td>
<td style="background-color: transparent; border-color: rgb(79, 129, 189) rgb(0, 0, 0); border-style: solid none; border-width: 1pt 0px; mso-border-bottom-themecolor: accent1; mso-border-top-themecolor: accent1; padding: 0cm 5.4pt; width: 77.95pt;" valign="top" width="104">
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-yfti-cnfc: 1;">
<b><span style="color: #365f91; mso-themecolor: accent1; mso-themeshade: 191;"><span style="font-family: Calibri;">TotalSalario<o:p></o:p></span></span></b></div>
</td>
</tr>
<tr style="mso-yfti-irow: 0;">
<td style="background: rgb(211, 223, 238); border: 0px rgb(0, 0, 0); mso-background-themecolor: accent1; mso-background-themetint: 63; padding: 0cm 5.4pt; width: 125.9pt;" valign="top" width="168">
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-yfti-cnfc: 68;">
<b><span style="color: #365f91; mso-themecolor: accent1; mso-themeshade: 191;"><span style="font-family: Calibri;">2011-01-01 00:00:00.000<o:p></o:p></span></span></b></div>
</td>
<td style="background: rgb(211, 223, 238); border: 0px rgb(0, 0, 0); mso-background-themecolor: accent1; mso-background-themetint: 63; padding: 0cm 5.4pt; width: 77.95pt;" valign="top" width="104">
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-yfti-cnfc: 64;">
<span style="color: #365f91; mso-themecolor: accent1; mso-themeshade: 191;"><span style="font-family: Calibri;">89500.00<o:p></o:p></span></span></div>
</td>
</tr>
<tr style="mso-yfti-irow: 1;">
<td style="background-color: transparent; border: 0px rgb(0, 0, 0); padding: 0cm 5.4pt; width: 125.9pt;" valign="top" width="168">
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-yfti-cnfc: 4;">
<b><span style="color: #365f91; mso-themecolor: accent1; mso-themeshade: 191;"><span style="font-family: Calibri;">2011-02-01 00:00:00.000<o:p></o:p></span></span></b></div>
</td>
<td style="background-color: transparent; border: 0px rgb(0, 0, 0); padding: 0cm 5.4pt; width: 77.95pt;" valign="top" width="104">
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt;">
<span style="color: #365f91; mso-themecolor: accent1; mso-themeshade: 191;"><span style="font-family: Calibri;">91200.00<o:p></o:p></span></span></div>
</td>
</tr>
<tr style="mso-yfti-irow: 2;">
<td style="background: rgb(211, 223, 238); border: 0px rgb(0, 0, 0); mso-background-themecolor: accent1; mso-background-themetint: 63; padding: 0cm 5.4pt; width: 125.9pt;" valign="top" width="168">
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-yfti-cnfc: 68;">
<b><span style="color: #365f91; mso-themecolor: accent1; mso-themeshade: 191;"><span style="font-family: Calibri;">2011-03-01 00:00:00.000<o:p></o:p></span></span></b></div>
</td>
<td style="background: rgb(211, 223, 238); border: 0px rgb(0, 0, 0); mso-background-themecolor: accent1; mso-background-themetint: 63; padding: 0cm 5.4pt; width: 77.95pt;" valign="top" width="104">
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-yfti-cnfc: 64;">
<span style="color: #365f91; mso-themecolor: accent1; mso-themeshade: 191;"><span style="font-family: Calibri;">93200.00<o:p></o:p></span></span></div>
</td>
</tr>
<tr style="mso-yfti-irow: 3;">
<td style="background-color: transparent; border: 0px rgb(0, 0, 0); padding: 0cm 5.4pt; width: 125.9pt;" valign="top" width="168">
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-yfti-cnfc: 4;">
<b><span style="color: #365f91; mso-themecolor: accent1; mso-themeshade: 191;"><span style="font-family: Calibri;">2011-04-01 00:00:00.000<o:p></o:p></span></span></b></div>
</td>
<td style="background-color: transparent; border: 0px rgb(0, 0, 0); padding: 0cm 5.4pt; width: 77.95pt;" valign="top" width="104">
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt;">
<span style="color: #365f91; mso-themecolor: accent1; mso-themeshade: 191;"><span style="font-family: Calibri;">97200.00<o:p></o:p></span></span></div>
</td>
</tr>
<tr style="mso-yfti-irow: 4;">
<td style="background: rgb(211, 223, 238); border: 0px rgb(0, 0, 0); mso-background-themecolor: accent1; mso-background-themetint: 63; padding: 0cm 5.4pt; width: 125.9pt;" valign="top" width="168">
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-yfti-cnfc: 68;">
<b><span style="color: #365f91; mso-themecolor: accent1; mso-themeshade: 191;"><span style="font-family: Calibri;">2011-05-01 00:00:00.000<o:p></o:p></span></span></b></div>
</td>
<td style="background: rgb(211, 223, 238); border: 0px rgb(0, 0, 0); mso-background-themecolor: accent1; mso-background-themetint: 63; padding: 0cm 5.4pt; width: 77.95pt;" valign="top" width="104">
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-yfti-cnfc: 64;">
<span style="color: #365f91; mso-themecolor: accent1; mso-themeshade: 191;"><span style="font-family: Calibri;">101250.00<o:p></o:p></span></span></div>
</td>
</tr>
<tr style="mso-yfti-irow: 5;">
<td style="background-color: transparent; border: 0px rgb(0, 0, 0); padding: 0cm 5.4pt; width: 125.9pt;" valign="top" width="168">
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-yfti-cnfc: 4;">
<b><span style="color: #365f91; mso-themecolor: accent1; mso-themeshade: 191;"><span style="font-family: Calibri;">2011-06-01 00:00:00.000<o:p></o:p></span></span></b></div>
</td>
<td style="background-color: transparent; border: 0px rgb(0, 0, 0); padding: 0cm 5.4pt; width: 77.95pt;" valign="top" width="104">
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt;">
<span style="color: #365f91; mso-themecolor: accent1; mso-themeshade: 191;"><span style="font-family: Calibri;">103000.00<o:p></o:p></span></span></div>
</td>
</tr>
<tr style="mso-yfti-irow: 6;">
<td style="background: rgb(211, 223, 238); border: 0px rgb(0, 0, 0); mso-background-themecolor: accent1; mso-background-themetint: 63; padding: 0cm 5.4pt; width: 125.9pt;" valign="top" width="168">
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-yfti-cnfc: 68;">
<b><span style="color: #365f91; mso-themecolor: accent1; mso-themeshade: 191;"><span style="font-family: Calibri;">2011-07-01 00:00:00.000<o:p></o:p></span></span></b></div>
</td>
<td style="background: rgb(211, 223, 238); border: 0px rgb(0, 0, 0); mso-background-themecolor: accent1; mso-background-themetint: 63; padding: 0cm 5.4pt; width: 77.95pt;" valign="top" width="104">
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-yfti-cnfc: 64;">
<span style="color: #365f91; mso-themecolor: accent1; mso-themeshade: 191;"><span style="font-family: Calibri;">103000.00<o:p></o:p></span></span></div>
</td>
</tr>
<tr style="mso-yfti-irow: 7;">
<td style="background-color: transparent; border: 0px rgb(0, 0, 0); padding: 0cm 5.4pt; width: 125.9pt;" valign="top" width="168">
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-yfti-cnfc: 4;">
<b><span style="color: #365f91; mso-themecolor: accent1; mso-themeshade: 191;"><span style="font-family: Calibri;">2011-08-01 00:00:00.000<o:p></o:p></span></span></b></div>
</td>
<td style="background-color: transparent; border: 0px rgb(0, 0, 0); padding: 0cm 5.4pt; width: 77.95pt;" valign="top" width="104">
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt;">
<span style="color: #365f91; mso-themecolor: accent1; mso-themeshade: 191;"><span style="font-family: Calibri;">105020.00<o:p></o:p></span></span></div>
</td>
</tr>
<tr style="mso-yfti-irow: 8; mso-yfti-lastrow: yes;">
<td style="background: rgb(211, 223, 238); border-color: rgb(0, 0, 0) rgb(0, 0, 0) rgb(79, 129, 189); border-style: none none solid; border-width: 0px 0px 1pt; mso-background-themecolor: accent1; mso-background-themetint: 63; mso-border-bottom-themecolor: accent1; padding: 0cm 5.4pt; width: 125.9pt;" valign="top" width="168">
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-yfti-cnfc: 68;">
<b><span style="color: #365f91; mso-themecolor: accent1; mso-themeshade: 191;"><span style="font-family: Calibri;">2011-09-01 00:00:00.000<o:p></o:p></span></span></b></div>
</td>
<td style="background: rgb(211, 223, 238); border-color: rgb(0, 0, 0) rgb(0, 0, 0) rgb(79, 129, 189); border-style: none none solid; border-width: 0px 0px 1pt; mso-background-themecolor: accent1; mso-background-themetint: 63; mso-border-bottom-themecolor: accent1; padding: 0cm 5.4pt; width: 77.95pt;" valign="top" width="104">
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-yfti-cnfc: 64;">
<span style="color: #365f91; mso-themecolor: accent1; mso-themeshade: 191;"><span style="font-family: Calibri;">11000.00<o:p></o:p></span></span></div>
</td>
</tr>
</tbody></table>
<span style="font-family: Calibri;">Tabela 2 - Conteúdo final da tabela<o:p></o:p></span><br />Cesar Blummhttp://www.blogger.com/profile/17590220931166448799noreply@blogger.com0tag:blogger.com,1999:blog-6100419445090790199.post-81743737525003115842011-11-28T07:30:00.000-02:002011-11-28T07:30:02.652-02:00Alterar valor de uma sequence<div style="text-align: justify;">
Acho que este cenário é bem comum: a chave primária de uma tabela tem seus valores definidos a partir de uma sequence. Por exemplo, os valores na tabela <i>Customer</i>, coluna <i>Customer_ID</i> são obtidos da sequence <i>Customer_Seq</i>. Se tudo funcionar adequadamente, o valor máximo na coluna é igual ao valor atual da sequence. E na próxima inserção de uma linha, a sequence retorna um valor maior (provavelmente um número acima). </div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
Pois bem, por diferentes motivos isto pode não ocorrer. Um caso comum ocorre no ambiente de desenvolvimento quando a aplicação ainda em testes iniciais contem um erro, insere muitas linhas sem utilizar a sequence e perde esta sincronia. Em produção já enfrentei um cenário mais complexo envolvendo duas aplicações inserindo dados concorrentemente em um BD. De qualquer modo, aparece uma necessidade: "adiantar a sequence até o ponto máximo da tabela". </div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
O modo simples seria dropar e recriar a sequence. Mas há problemas de segurança, concorrência, necessidade de recompilar pacotes, etc... que podem tornar a operação não muito simples. </div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
A solução óbvia é escrever um pequeno loop para adiantar a sequence <i>n </i>vezes. Atenção, se <i>n </i>for grande, isto pode demorar algum tempo.</div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
Bem, depois de ocorrer algumas vezes, desenvolvi uma procedure para me ajudar. São três parâmetros: nome da tabela, nome da coluna e nome da sequence. A idéia básica é obter a diferença entre o valor máximo na tabela, que suponho ser maior, e o valor atual na sequence. Então, alterar o incremento da sequence para a diferença e incrementar a sequence uma única vez cobrindo toda diferença. Apenas para não afetar a sequence de maneira definitiva, é necessário saber o incremento utilizado no início e redefini-lo no final. </div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
Vantagens: </div>
<div style="text-align: justify;">
</div>
<ul>
<li>em poucos passos o valor da sequence é corrigido </li>
<li>tempo de execução não depende da diferença</li>
<li>a sequence continua existindo, bem como seus grants</li>
<li>não é necessário recompilar procedures ou funções</li>
<li>e uma vez escrita a procedure, basta chamá-la.</li>
</ul>
Para chamar a procedure, basta uma linha como;<br />
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
<span class="Apple-style-span" style="color: blue;">exec </span>move_sequence('<span class="Apple-style-span" style="color: purple;">customer</span>', '<span class="Apple-style-span" style="color: purple;">customer_id</span>', '<span class="Apple-style-span" style="color: purple;">customer_seq</span>');</div>
<div style="text-align: justify;">
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;"><br /></span></div>
<div style="text-align: justify;">
<span class="Apple-style-span" style="font-family: inherit;">Aqui a procedure, com alguns <i>put_line</i> apenas para ajudar a sua compreensão:</span></div>
<div style="text-align: justify;">
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;"><br /></span></div>
<div style="text-align: justify;">
</div>
<br />
<span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">create or replace</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="color: blue;">procedure</span> move_sequence(p_table_name <span class="Apple-style-span" style="color: blue;">varchar2</span>, p_column_name <span class="Apple-style-span" style="color: blue;">varchar2</span>, p_sequence_name <span class="Apple-style-span" style="color: blue;">varchar2</span> )</span><br />
<span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">as</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="color: blue;"> </span>sqlc <span class="Apple-style-span" style="color: blue;">varchar2</span>(500);</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> max_column_value <span class="Apple-style-span" style="color: blue;">number</span>;</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> curr_seq_value <span class="Apple-style-span" style="color: blue;">number</span>;</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> diff <span class="Apple-style-span" style="color: blue;">number</span>;</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> curr_seq_increment <span class="Apple-style-span" style="color: blue;">number</span>;</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><br /></span><br />
<span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">begin</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><br /></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> sqlc := '<span class="Apple-style-span" style="color: purple;">select max(</span>'||p_column_name||'<span class="Apple-style-span" style="color: purple;">) from '</span>||p_table_name;</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> <span class="Apple-style-span" style="color: blue;">execute immediate</span> sqlc <span class="Apple-style-span" style="color: blue;">into</span> max_column_value;</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> </span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> dbms_output.put_line('<span class="Apple-style-span" style="color: purple;">Max Current Value: </span>'||max_column_value);</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><br /></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> <span class="Apple-style-span" style="color: blue;">select </span>us.increment_by</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> <span class="Apple-style-span" style="color: blue;">into </span>curr_seq_increment</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> <span class="Apple-style-span" style="color: blue;">from </span>user_sequences us</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> <span class="Apple-style-span" style="color: blue;">where </span>sequence_name = upper(p_sequence_name); </span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> </span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> sqlc := '<span class="Apple-style-span" style="color: purple;">select </span>'||p_sequence_name||'<span class="Apple-style-span" style="color: purple;">.nextval from dual</span>';</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> <span class="Apple-style-span" style="color: blue;">execute immediate</span> sqlc <span class="Apple-style-span" style="color: blue;">into</span> curr_seq_value;</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> </span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> dbms_output.put_line('<span class="Apple-style-span" style="color: purple;">Seq Current Value: </span>'||curr_seq_value);</span><br />
<br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> diff := max_column_value - curr_seq_value;</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> sqlc := '<span class="Apple-style-span" style="color: purple;">alter sequence </span>'||p_sequence_name||'<span class="Apple-style-span" style="color: purple;"> increment by</span> '||diff;</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> <span class="Apple-style-span" style="color: blue;">execute immediate</span> sqlc;</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> </span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> sqlc := '<span class="Apple-style-span" style="color: purple;">select </span>'||p_sequence_name||'<span class="Apple-style-span" style="color: purple;">.nextval from dual</span>';</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> <span class="Apple-style-span" style="color: blue;">execute immediate</span> sqlc <span class="Apple-style-span" style="color: blue;">into </span>curr_seq_value;</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> </span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> dbms_output.put_line('<span class="Apple-style-span" style="color: purple;">New Seq Current Value:</span> ' ||curr_seq_value);</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> </span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> sqlc := '<span class="Apple-style-span" style="color: purple;">alter sequence </span>'||p_sequence_name||'<span class="Apple-style-span" style="color: purple;"> increment by</span> '||curr_seq_increment;</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> <span class="Apple-style-span" style="color: blue;">execute immediate</span> sqlc;</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> </span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="color: blue;">end</span>;</span><br />
<br />
<div style="text-align: justify;">
Uma variação simples é resetar a sequence para o valor inicial. Esta eu deixo por conta do leitor. Alguns detalhes podem ser incluídos, como um parâmetro para o nome do esquema. </div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
<br /></div>
<br />
<br />Miguel Fornarihttp://www.blogger.com/profile/14892590771484626747noreply@blogger.com0tag:blogger.com,1999:blog-6100419445090790199.post-26117153455957520732011-11-27T00:05:00.001-02:002011-11-27T00:37:35.206-02:00Insert: valores default e nulos<div style="text-align: justify;">
Este post tem por objetivo esclarecer um pequeno erro, mas infelizmente comum, relativo ao comando INSERT, valores default e nulos. </div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
Comecemos pela declaração da tabela. Como é fácil notar, a terceira coluna chama-se <i>dtNasc</i> e tem sysdate como valor default. </div>
<br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="color: blue;">create table</span> pessoa </span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">(id <span class="Apple-style-span" style="color: blue;">number primary key</span>, </span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> nome <span class="Apple-style-span" style="color: blue;">varchar2</span>(50), </span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> dtNasc <span class="Apple-style-span" style="color: blue;">date default</span> trunc(<span class="Apple-style-span" style="color: blue;">sysdate</span>)); </span><br />
<br />
<div style="text-align: justify;">
Agora uma pequena sequencia de INSERTs, com uma alteração na foram como valores são inseridos na coluna dtNasc: </div>
<br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="color: blue;">insert into</span> pessoa(id, nome, dtnasc) </span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="color: blue;">values</span>(1, '<span class="Apple-style-span" style="color: purple;">João</span>', trunc(sysdate-20) );</span><br />
<br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="color: blue;">insert into</span> pessoa(id, nome) </span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="color: blue;">values</span>(2, '<span class="Apple-style-span" style="color: purple;">Maria</span>');
</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><br /></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="color: blue;">insert into</span> pessoa(id, nome, dtnasc) </span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="color: blue;">values</span>(3, '<span class="Apple-style-span" style="color: purple;">João</span>', null ); </span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><br /></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="color: blue;">commit</span>; </span><br />
<br />
E para ver o resultado, um SELECT:<br />
<br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="color: blue;">select </span>* <span class="Apple-style-span" style="color: blue;">from</span> pessoa; </span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><br /></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">ID NOME DTNASC </span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">----- ---------- ------------ </span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">1 João 06-NOV-11 </span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">2 Maria 26-NOV-11 </span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">3 João </span><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> </span><br />
<br />
<div style="text-align: justify;">
O primeiro INSERT define claramente um valor para <i>dtNasc</i>, no caso 06-NOV-2011, sobrescrevendo o valor default.
O segundo nem inclui a coluna na lista, muito menos define um valor. Neste caso, o valor default é assumido.
Já o terceiro também define explicitamente o valor NULL. A intenção do usuário é clara: o valor é desconhecido, não assuma o valor default. Este é justamente o erro que comentei no início, pois muitos assumem que seria definido o valor default. </div>
<br />
<div style="text-align: justify;">
Se já pode causar alguma confusão quando utilizando diretamente SQL, mais atenção é necessária quando a aplicação é desenvolvida com Hibernate ou algum outro framework para acesso a dados por que há diferenças no comportamento se a coluna está ou não mapeada e como certos parâmetros do framework são setados. Não vou entrar nas várias alternativas de cada framework, mas é importante estar atento.</div>Miguel Fornarihttp://www.blogger.com/profile/14892590771484626747noreply@blogger.com0tag:blogger.com,1999:blog-6100419445090790199.post-52050285358388148692011-11-17T00:23:00.001-02:002011-11-17T01:05:56.034-02:00Objetos dependentes<div style="text-align: justify;">
A alteração de um objeto (tabela, visão, procedure, ...) pode afetar outros objetos e obrigar, no mínimo , a recompilá-los. Assim, antes de iniciar a alteração é útil verificar quais outros objetos poderão ser afetados. Ou talvez muito antes disto, quando estiver avaliando custos e riscos de alterar o esquema para suportar um novo requisito. Para tanto, basta uma consulta a visão DBA_DEPENDENCIES (ou às equivalentes ALL_DEPENDENCIES e USER_DEPENDENCIES).</div>
<br />
<br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="color: blue;">SELECT</span><span class="Apple-style-span" style="color: blue;"> </span><span class="Apple-style-span" style="color: blue;">* </span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="color: blue;">FROM </span><span class="Apple-style-span" style="color: #444444;">dba_dependencies</span> </span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="color: blue;">WHERE </span><span class="Apple-style-span" style="color: #444444;">referenced_owner</span><span class="Apple-style-span" style="color: #666666;"> =</span> '<span class="Apple-style-span" style="color: purple;">HR</span>'</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> <span class="Apple-style-span" style="color: blue;">AND </span><span class="Apple-style-span" style="color: #444444;">referenced_name =</span> '<span class="Apple-style-span" style="color: purple;">JOB_HISTORY</span>';</span><br />
<br />
<div style="text-align: justify;">
Esta consulta retorna uma única linha: a procedure ADD_JOB_HISTORY. Examinando o código fonte desta procedure, nota-se que há um comando de INSERT na tabela. Parece-me razoável que, se você incluir uma coluna na tabela, a procedure seja alteradap ara incluir um valor nesta nova coluna. E se por acaso remover uma coluna da tabela, a procedure obrigatoriamente terá que ser alterada.</div>
<br />
<div style="text-align: justify;">
Agora um passo a mais: como saber quais objetos a procedure pode afetar? Uma solução simples seria aplicar novamente a mesma consulta, apenas usando o nome da procedure. Para um esquema simples, até pode ser, mas num esquema complexo, com centenas de objetos, seria um esforço manual desnecessário e sujeito a erros. Muito mais conveniente e elegante é usar a consulta abaixo e deixar que o Oracle resolva este problema. </div>
<br />
<span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">SELECT *</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="color: blue;">FROM</span> <span class="Apple-style-span" style="color: #444444;">dba_dependencies</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="color: blue;">START WITH</span> <span class="Apple-style-span" style="color: #444444;">referenced_</span><span class="Apple-style-span" style="color: #444444;">owner =</span> '<span class="Apple-style-span" style="color: purple;">HR</span>' </span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="color: blue;"> AND </span><span class="Apple-style-span" style="color: #444444;">referenced_name =</span> '<span class="Apple-style-span" style="color: purple;">JOB_HISTORY</span>'</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="color: blue;">CONNECT BY</span> <span class="Apple-style-span" style="color: #444444;">prior owner = referenced_owner</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="color: blue;"> AND </span><span class="Apple-style-span" style="color: #444444;">prior name = referenced_name;</span> </span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><br /></span><br />
<div style="text-align: justify;">
A consulta retorna duas linhas: a procedure ADD_JOB_HISTORY e a trigger UPDATE_JOB_HISTORY, que tem uma chamada para a procedure. </div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
Por tratar-se de uma consulta hierárquica, se houver um ciclo no grafo de dependências, o Oracle retornará um erro. Neste caso, é possível incluir um controle para limitar a recursão. Por exemplo:</div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
</div>
<div style="text-align: -webkit-auto;">
<span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">SELECT *</span></div>
<div style="text-align: -webkit-auto;">
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="color: blue;">FROM</span> <span class="Apple-style-span" style="color: #444444;">dba_dependencies</span></span></div>
<div style="text-align: -webkit-auto;">
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="color: blue;">START WITH</span> <span class="Apple-style-span" style="color: #444444;">referenced_</span><span class="Apple-style-span" style="color: #444444;">owner =</span> '<span class="Apple-style-span" style="color: purple;">SYS</span>' </span></div>
<div style="text-align: -webkit-auto;">
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="color: blue;"> AND </span><span class="Apple-style-span" style="color: #444444;">referenced_name =</span> '<span class="Apple-style-span" style="color: purple;">OBJ$</span>'</span></div>
<div style="text-align: -webkit-auto;">
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="color: blue;">CONNECT BY</span> <span class="Apple-style-span" style="color: #444444;">prior owner = referenced_owner</span></span></div>
<div style="text-align: -webkit-auto;">
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="color: blue;"> AND </span><span class="Apple-style-span" style="color: #444444;">prior name = referenced_name</span></span></div>
<div style="text-align: -webkit-auto;">
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="color: #444444;"> </span><span class="Apple-style-span" style="color: blue;">AND </span><span class="Apple-style-span" style="color: #444444;">level < 4;</span></span></div>
<br />
<br />
O valor 4 é o limite para este caso específico (tabela SYS.OBJ$). Se aumentar para 5, fecha-se um ciclo no grafo e um erro é retornado. Outros objetos podem permitir outros limites.<br />
<br />Miguel Fornarihttp://www.blogger.com/profile/14892590771484626747noreply@blogger.com1tag:blogger.com,1999:blog-6100419445090790199.post-39542438849031237842011-11-16T09:21:00.001-02:002011-11-16T09:32:01.037-02:00Novo grupo de usuários SQL Server do RSFoi criado um grupo de usuários SQL Server do Rio Grande do Sul, por enquanto as pessoas envolvidas são André Pretto @prettoandre), Cesar Blumm (@cesarblumm), Marcus Vinicius Bittencourt (@mvbitt) e Rodrigo Crespi (@crespidb).<br />
<br />
Dia 19/11/2011 terá um encontro do grupo para apresentar o assunto Auditoria de Banco de Dados SQL Server de Acordo com a SoX com o membro <a href="http://www.twitter.com/prettoandre" target="_blank">André Pretto</a>.<br />
<br />
Twitter: @sqlserverrs<br />
Blog: <a href="http://sqlserverrs.com.br/">http://sqlserverrs.com.br</a><br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgsHrlUhVEQMrLioUMBmgGylGqOYCZmOB_HaR6VrYwgTX-a8dJqQaB9vKOwftL8LLIZrxGRFYqNSQXHU2-cdfdU9wr4bKszc63vCH-XkBX3v7aFWGv14chMVQ06rsUUUbqFS4uT6LNa31ap/s1600/convite1.jpg" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="320" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgsHrlUhVEQMrLioUMBmgGylGqOYCZmOB_HaR6VrYwgTX-a8dJqQaB9vKOwftL8LLIZrxGRFYqNSQXHU2-cdfdU9wr4bKszc63vCH-XkBX3v7aFWGv14chMVQ06rsUUUbqFS4uT6LNa31ap/s320/convite1.jpg" width="293" /></a></div>Cesar Blummhttp://www.blogger.com/profile/17590220931166448799noreply@blogger.com0tag:blogger.com,1999:blog-6100419445090790199.post-17069664431166047622011-11-16T00:28:00.001-02:002011-11-16T01:07:55.818-02:00Dicionário de Dados do Oracle - a visão DICTIONARY<div style="text-align: justify;">
O dicionário de dados de todo SGBD armazena informações sobre o esquema de dados criado pelos usuários. No caso do Oracle é um conjunto de tabelas e visões criadas no esquema SYS. </div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
A visão DICTIONARY é especialmente interessante por que contém a lista de visões que formam o próprio dicionário de dados. Ou seja, seria o meta-meta-esquema de dados. </div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
A consulta abaixo retorna o número de visões para certos grupos de informações. </div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
</div>
<span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">SELECT </span><br />
<span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">CASE WHEN SUBSTR(</span><span class="Apple-style-span" style="color: #666666; font-family: 'Courier New', Courier, monospace;">TABLE_NAME</span><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">, 1, 5) = '</span><span class="Apple-style-span" style="color: purple; font-family: 'Courier New', Courier, monospace;">USER_</span><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">' THEN '</span><span class="Apple-style-span" style="color: purple; font-family: 'Courier New', Courier, monospace;">USER_</span><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">'</span><br />
<span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;"> WHEN SUBSTR(</span><span class="Apple-style-span" style="color: #666666; font-family: 'Courier New', Courier, monospace;">TABLE_NAME</span><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">, 1, 4) = '</span><span class="Apple-style-span" style="color: purple; font-family: 'Courier New', Courier, monospace;">ALL_</span><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">' THEN '</span><span class="Apple-style-span" style="color: purple; font-family: 'Courier New', Courier, monospace;">ALL_</span><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">'</span><br />
<span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;"> WHEN SUBSTR(</span><span class="Apple-style-span" style="color: #666666; font-family: 'Courier New', Courier, monospace;">TABLE_NAME</span><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">, 1, 4) = '</span><span class="Apple-style-span" style="color: purple; font-family: 'Courier New', Courier, monospace;">DBA_</span><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">' THEN '</span><span class="Apple-style-span" style="color: purple; font-family: 'Courier New', Courier, monospace;">DBA_</span><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">'</span><br />
<span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;"> WHEN SUBSTR(</span><span class="Apple-style-span" style="color: #666666; font-family: 'Courier New', Courier, monospace;">TABLE_NAME</span><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">, 1, 3) = '</span><span class="Apple-style-span" style="color: purple; font-family: 'Courier New', Courier, monospace;">V_$</span><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">' THEN '</span><span class="Apple-style-span" style="color: purple; font-family: 'Courier New', Courier, monospace;">V_$</span><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">'</span><br />
<span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;"> WHEN SUBSTR(</span><span class="Apple-style-span" style="color: #666666; font-family: 'Courier New', Courier, monospace;">TABLE_NAME</span><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">, 1, 2) = '</span><span class="Apple-style-span" style="color: purple; font-family: 'Courier New', Courier, monospace;">V$</span><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">' THEN '</span><span class="Apple-style-span" style="color: purple; font-family: 'Courier New', Courier, monospace;">V$</span><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">'</span><br />
<span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;"> WHEN SUBSTR(</span><span class="Apple-style-span" style="color: #666666; font-family: 'Courier New', Courier, monospace;">TABLE_NAME</span><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">, 1, 4) = '</span><span class="Apple-style-span" style="color: purple; font-family: 'Courier New', Courier, monospace;">GV_$</span><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">' THEN '</span><span class="Apple-style-span" style="color: purple; font-family: 'Courier New', Courier, monospace;">GV_$</span><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">'</span><br />
<span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;"> WHEN SUBSTR(</span><span class="Apple-style-span" style="color: #666666; font-family: 'Courier New', Courier, monospace;">TABLE_NAME</span><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">, 1, 3) = '</span><span class="Apple-style-span" style="color: purple; font-family: 'Courier New', Courier, monospace;">GV$</span><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">' THEN '</span><span class="Apple-style-span" style="color: purple; font-family: 'Courier New', Courier, monospace;">GV$</span><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">' </span><br />
<span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;"> ELSE '</span><span class="Apple-style-span" style="color: purple; font-family: 'Courier New', Courier, monospace;">Others</span><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">'</span><br />
<span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">END PREFIX </span><br />
<span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">, COUNT</span><span class="Apple-style-span" style="color: #666666; font-family: 'Courier New', Courier, monospace;">(*)</span><br />
<span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">FROM DICTIONARY</span><br />
<span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">GROUP BY </span><br />
<span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">CASE </span><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">WHEN SUBSTR(</span><span class="Apple-style-span" style="color: #666666; font-family: 'Courier New', Courier, monospace;">TABLE_NAME</span><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">, 1, 5) = '</span><span class="Apple-style-span" style="color: purple; font-family: 'Courier New', Courier, monospace;">USER_</span><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">' THEN '</span><span class="Apple-style-span" style="color: purple; font-family: 'Courier New', Courier, monospace;">USER_</span><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">'</span><br />
<span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;"> WHEN SUBSTR(</span><span class="Apple-style-span" style="color: #666666; font-family: 'Courier New', Courier, monospace;">TABLE_NAME</span><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">, 1, 4) = '</span><span class="Apple-style-span" style="color: purple; font-family: 'Courier New', Courier, monospace;">ALL_</span><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">' THEN '</span><span class="Apple-style-span" style="color: purple; font-family: 'Courier New', Courier, monospace;">ALL_</span><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">'</span><br />
<span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;"> WHEN SUBSTR(</span><span class="Apple-style-span" style="color: #666666; font-family: 'Courier New', Courier, monospace;">TABLE_NAME</span><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">, 1, 4) = '</span><span class="Apple-style-span" style="color: purple; font-family: 'Courier New', Courier, monospace;">DBA_</span><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">' THEN '</span><span class="Apple-style-span" style="color: purple; font-family: 'Courier New', Courier, monospace;">DBA_</span><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">'</span><br />
<span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;"> WHEN SUBSTR(</span><span class="Apple-style-span" style="color: #666666; font-family: 'Courier New', Courier, monospace;">TABLE_NAME</span><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">, 1, 3) = '</span><span class="Apple-style-span" style="color: purple; font-family: 'Courier New', Courier, monospace;">V_$</span><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">' THEN '</span><span class="Apple-style-span" style="color: purple; font-family: 'Courier New', Courier, monospace;">V_$</span><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">'</span><br />
<span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;"> WHEN SUBSTR(</span><span class="Apple-style-span" style="color: #666666; font-family: 'Courier New', Courier, monospace;">TABLE_NAME</span><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">, 1, 2) = '</span><span class="Apple-style-span" style="color: purple; font-family: 'Courier New', Courier, monospace;">V$</span><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">' THEN '</span><span class="Apple-style-span" style="color: purple; font-family: 'Courier New', Courier, monospace;">V$</span><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">'</span><br />
<span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;"> WHEN SUBSTR(</span><span class="Apple-style-span" style="color: #666666; font-family: 'Courier New', Courier, monospace;">TABLE_NAME</span><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">, 1, 4) = '</span><span class="Apple-style-span" style="color: purple; font-family: 'Courier New', Courier, monospace;">GV_$</span><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">' THEN '</span><span class="Apple-style-span" style="color: purple; font-family: 'Courier New', Courier, monospace;">GV_$</span><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">'</span><br />
<span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;"> WHEN SUBSTR(</span><span class="Apple-style-span" style="color: #666666; font-family: 'Courier New', Courier, monospace;">TABLE_NAME</span><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">, 1, 3) = '</span><span class="Apple-style-span" style="color: purple; font-family: 'Courier New', Courier, monospace;">GV$</span><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">' THEN '</span><span class="Apple-style-span" style="color: purple; font-family: 'Courier New', Courier, monospace;">GV$</span><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">' </span><br />
<span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;"> ELSE '</span><span class="Apple-style-span" style="color: purple; font-family: 'Courier New', Courier, monospace;">Others</span><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">'</span><br />
<span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">END;</span><br />
<span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;"><br /></span><br />
O número de visões em cada grupo pode variar dependendo da versão de Oracle instalada. Os números abaixo correspondem ao Oracle 11g XE que estou utilizando.<br />
<br />
<table border="1" cellspacing="0" cols="3">
<colgroup><col width="86"></col><col width="77"></col><col width="341"></col></colgroup>
<tbody>
<tr>
<td align="CENTER" bgcolor="#C0C0C0" height="18" width="86"><b>Prefix</b></td>
<td align="CENTER" bgcolor="#C0C0C0" width="77"><b>Count(*)</b></td>
<td align="CENTER" bgcolor="#C0C0C0" width="341"><b>Descrição</b></td>
</tr>
<tr>
<td align="LEFT" height="18">USER_</td>
<td align="CENTER" sdnum="1033;" sdval="361">361</td>
<td align="LEFT">Objetos pertencentes ao usuário corrente.</td>
</tr>
<tr>
<td align="LEFT" height="18">DBA_</td>
<td align="CENTER" sdnum="1033;" sdval="703">703</td>
<td align="LEFT">Todos objetos na base de dados.</td>
</tr>
<tr>
<td align="LEFT" height="34">ALL_</td>
<td align="CENTER" sdnum="1033;" sdval="339">339</td>
<td align="LEFT">Objetos que o usuário corrente tem privilégios de acesso, seja no seu esquema ou em outro.</td>
</tr>
<tr>
<td align="LEFT" height="18">V$</td>
<td align="CENTER" sdnum="1033;" sdval="618">618</td>
<td align="LEFT">Informações dinâmicas de desempenho.</td>
</tr>
<tr>
<td align="LEFT" height="34">GV$</td>
<td align="CENTER" sdnum="1033;" sdval="491">491</td>
<td align="LEFT">Informações de desempenho global do sistema, especialmente úteis em RACs.</td>
</tr>
<tr>
<td align="LEFT" height="18">Others</td>
<td align="CENTER" sdnum="1033;" sdval="39">39</td>
<td align="LEFT">Variados usos</td>
</tr>
</tbody>
</table>
<br />
<div style="text-align: justify;">
Caso você não lembre do nome de determinadas visões do dicionário de dados, você pode utilizar a visão DICTIONARY e escrever consultas como <span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="color: blue;">SELECT * FROM DICTIONARY WHERE</span> <span class="Apple-style-span" style="color: #666666;">TABLE_NAME</span> <span class="Apple-style-span" style="color: blue;">LIKE</span> '<span class="Apple-style-span" style="color: purple;">%INDEX%</span>'</span>. Afinal, ninguem é obrigado a lembrar o nome de 2000 visões, mas certametne é muito útil ter certo conhecimento do dicionário de dados.</div>Miguel Fornarihttp://www.blogger.com/profile/14892590771484626747noreply@blogger.com0tag:blogger.com,1999:blog-6100419445090790199.post-26502383216949528602011-11-14T17:51:00.001-02:002011-11-14T19:02:15.557-02:00Subtotais em Oracle SQL - parte III<div style="text-align: justify;">
A combinação de GROUP BY com ROLLUP, CUBE ou GROUPING_SETS resulta em dois tipos de linhas: agregadas, aquelas com valores em todas as colunas do GROUP BY; e super-agregadas, as linhas de subtotais ou totais.</div>
<div>
<div style="text-align: justify;">
<br /></div>
</div>
<div>
<div style="text-align: justify;">
A função GROUPING permite facilmente dferenciar as duas, pois retorna o valor 0 se a linha representar um valor agregado e o valor 1 caso seja um super-agregado. Veja na consulta abaixo os valroes da primeira e segunda coluna. A última linha, por ser o grande total, tem valor 1 para ambas. </div>
</div>
<div>
<br /></div>
<div>
<span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; font-size: 13px; line-height: 20px;"><span class="Apple-style-span" style="color: blue;">SELECT</span> </span><span class="Apple-style-span" style="color: #666666; font-family: 'Courier New', Courier, monospace;">grouping(department_name) grp_dept,</span></div>
<div>
<span class="Apple-style-span" style="color: #666666; font-family: 'Courier New', Courier, monospace;"> grouping(TO_CHAR(hire_date, '</span><span class="Apple-style-span" style="color: purple; font-family: 'Courier New', Courier, monospace;">yyyy</span><span class="Apple-style-span" style="color: #666666; font-family: 'Courier New', Courier, monospace;">')) grp_hd,</span></div>
<div>
<span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; font-size: 13px; line-height: 20px;"> department_name,</span><br />
<span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; font-size: 13px; line-height: 20px;"> TO_CHAR(hire_date, '<span class="Apple-style-span" style="color: purple;">yyyy</span>') hire_year,</span><br />
<span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; font-size: 13px; line-height: 20px;"> SUM(salary)</span><br />
<span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; font-size: 13px; line-height: 20px;"><span class="Apple-style-span" style="color: blue;">FROM </span>hr.employees emp</span><br />
<div style="background-color: white; color: #555555; font-family: Arial, Helvetica, sans-serif; font-size: 13px; line-height: 20px; text-align: justify;">
<span class="Apple-style-span" style="background-color: white; font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="color: blue;">JOIN </span>HR.departments dept USING (department_id)</span></div>
<span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; font-size: 13px; line-height: 20px;"><span class="Apple-style-span" style="color: blue;">JOIN</span> HR.jobs j USING (job_id)</span><br />
<span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; font-size: 13px; line-height: 20px;"><span class="Apple-style-span" style="color: blue;">WHERE</span> department_name IN ( '<span class="Apple-style-span" style="color: purple;">IT</span>', '<span class="Apple-style-span" style="color: purple;">Sales</span>')</span><br />
<span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; font-size: 13px; line-height: 20px;"><span class="Apple-style-span" style="color: blue;">GROUP BY CUBE</span>(department_name, TO_CHAR(hire_date, '<span class="Apple-style-span" style="color: purple;">yyyy</span>'))</span><br />
<span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; font-size: 13px; line-height: 20px;"><span class="Apple-style-span" style="color: blue;">ORDER BY</span> 1,2;</span><br />
<span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; font-size: 13px; line-height: 20px;"><br /></span></div>
<div>
<table border="1" cellspacing="0" cols="5">
<colgroup><col width="86"></col><col width="86"></col><col width="143"></col><col width="86"></col><col width="86"></col></colgroup>
<tbody>
<tr>
<td align="CENTER" bgcolor="#C0C0C0" height="17" width="86"><b>Grp_Dept</b></td>
<td align="CENTER" bgcolor="#C0C0C0" width="86"><b>Grp_HD</b></td>
<td align="CENTER" bgcolor="#C0C0C0" width="143"><b>Department_Name</b></td>
<td align="CENTER" bgcolor="#C0C0C0" width="86"><b>Hire_Year</b></td>
<td align="CENTER" bgcolor="#C0C0C0" width="86"><b>SUM(Salary)</b></td>
</tr>
<tr>
<td align="RIGHT" height="17" sdnum="1033;" sdval="0">0</td>
<td align="RIGHT" sdnum="1033;" sdval="0">0</td>
<td align="LEFT">IT</td>
<td align="RIGHT" sdnum="1033;" sdval="2005">2005</td>
<td align="RIGHT" sdnum="1033;" sdval="4800">4800</td>
</tr>
<tr>
<td align="RIGHT" height="17" sdnum="1033;" sdval="0">0</td>
<td align="RIGHT" sdnum="1033;" sdval="0">0</td>
<td align="LEFT">Sales</td>
<td align="RIGHT" sdnum="1033;" sdval="2007">2007</td>
<td align="RIGHT" sdnum="1033;" sdval="42200">42200</td>
</tr>
<tr>
<td align="RIGHT" height="17" sdnum="1033;" sdval="0">0</td>
<td align="RIGHT" sdnum="1033;" sdval="0">0</td>
<td align="LEFT">Sales</td>
<td align="RIGHT" sdnum="1033;" sdval="2006">2006</td>
<td align="RIGHT" sdnum="1033;" sdval="59100">59100</td>
</tr>
<tr>
<td align="RIGHT" height="17" sdnum="1033;" sdval="0">0</td>
<td align="RIGHT" sdnum="1033;" sdval="0">0</td>
<td align="LEFT">Sales</td>
<td align="RIGHT" sdnum="1033;" sdval="2005">2005</td>
<td align="RIGHT" sdnum="1033;" sdval="100300">100300</td>
</tr>
<tr>
<td align="RIGHT" height="17" sdnum="1033;" sdval="0">0</td>
<td align="RIGHT" sdnum="1033;" sdval="0">0</td>
<td align="LEFT">Sales</td>
<td align="RIGHT" sdnum="1033;" sdval="2004">2004</td>
<td align="RIGHT" sdnum="1033;" sdval="53500">53500</td>
</tr>
<tr>
<td align="RIGHT" height="17" sdnum="1033;" sdval="0">0</td>
<td align="RIGHT" sdnum="1033;" sdval="0">0</td>
<td align="LEFT">IT</td>
<td align="RIGHT" sdnum="1033;" sdval="2007">2007</td>
<td align="RIGHT" sdnum="1033;" sdval="10200">10200</td>
</tr>
<tr>
<td align="RIGHT" height="17" sdnum="1033;" sdval="0">0</td>
<td align="RIGHT" sdnum="1033;" sdval="0">0</td>
<td align="LEFT">IT</td>
<td align="RIGHT" sdnum="1033;" sdval="2006">2006</td>
<td align="RIGHT" sdnum="1033;" sdval="13800">13800</td>
</tr>
<tr>
<td align="RIGHT" height="17" sdnum="1033;" sdval="0">0</td>
<td align="RIGHT" sdnum="1033;" sdval="0">0</td>
<td align="LEFT">Sales</td>
<td align="RIGHT" sdnum="1033;" sdval="2008">2008</td>
<td align="RIGHT" sdnum="1033;" sdval="49400">49400</td>
</tr>
<tr>
<td align="RIGHT" height="17" sdnum="1033;" sdval="0">0</td>
<td align="RIGHT" sdnum="1033;" sdval="1">1</td>
<td align="LEFT">Sales</td>
<td align="LEFT"><br /></td>
<td align="RIGHT" sdnum="1033;" sdval="304500">304500</td>
</tr>
<tr>
<td align="RIGHT" height="17" sdnum="1033;" sdval="0">0</td>
<td align="RIGHT" sdnum="1033;" sdval="1">1</td>
<td align="LEFT">IT</td>
<td align="LEFT"><br /></td>
<td align="RIGHT" sdnum="1033;" sdval="28800">28800</td>
</tr>
<tr>
<td align="RIGHT" height="17" sdnum="1033;" sdval="1">1</td>
<td align="RIGHT" sdnum="1033;" sdval="0">0</td>
<td align="LEFT"><br /></td>
<td align="RIGHT" sdnum="1033;" sdval="2008">2008</td>
<td align="RIGHT" sdnum="1033;" sdval="49400">49400</td>
</tr>
<tr>
<td align="RIGHT" height="17" sdnum="1033;" sdval="1">1</td>
<td align="RIGHT" sdnum="1033;" sdval="0">0</td>
<td align="LEFT"><br /></td>
<td align="RIGHT" sdnum="1033;" sdval="2007">2007</td>
<td align="RIGHT" sdnum="1033;" sdval="52400">52400</td>
</tr>
<tr>
<td align="RIGHT" height="17" sdnum="1033;" sdval="1">1</td>
<td align="RIGHT" sdnum="1033;" sdval="0">0</td>
<td align="LEFT"><br /></td>
<td align="RIGHT" sdnum="1033;" sdval="2006">2006</td>
<td align="RIGHT" sdnum="1033;" sdval="72900">72900</td>
</tr>
<tr>
<td align="RIGHT" height="17" sdnum="1033;" sdval="1">1</td>
<td align="RIGHT" sdnum="1033;" sdval="0">0</td>
<td align="LEFT"><br /></td>
<td align="RIGHT" sdnum="1033;" sdval="2005">2005</td>
<td align="RIGHT" sdnum="1033;" sdval="105100">105100</td>
</tr>
<tr>
<td align="RIGHT" height="17" sdnum="1033;" sdval="1">1</td>
<td align="RIGHT" sdnum="1033;" sdval="0">0</td>
<td align="LEFT"><br /></td>
<td align="RIGHT" sdnum="1033;" sdval="2004">2004</td>
<td align="RIGHT" sdnum="1033;" sdval="53500">53500</td>
</tr>
<tr>
<td align="RIGHT" height="17" sdnum="1033;" sdval="1">1</td>
<td align="RIGHT" sdnum="1033;" sdval="1">1</td>
<td align="LEFT"><br /></td>
<td align="LEFT"><br /></td>
<td align="RIGHT" sdnum="1033;" sdval="333300">333300</td>
</tr>
</tbody>
</table>
</div>
<div>
<span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; font-size: 13px; line-height: 20px;"><br /></span><br />
<div style="text-align: justify;">
Obviamente 0 ou 1 podem ser úteis para algum tipo de processamento automático, mas caso queira mostrar um resultado final para usuários, é mais conveniente substituir por mensagens adequadas. Por exemplo, a primeira coluna é resultado da função GROUPING numa combinação com CASE para definir um valor que o usuário final receberá:</div>
<br />
<div>
<span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; font-size: 13px; line-height: 20px;"><span class="Apple-style-span" style="color: blue;">SELECT</span> </span><span class="Apple-style-span" style="color: #666666; font-family: 'Courier New', Courier, monospace;">case when grouping(department_name) = 1 AND grouping(TO_CHAR(hire_date, '</span><span class="Apple-style-span" style="color: purple; font-family: 'Courier New', Courier, monospace;">yyyy</span><span class="Apple-style-span" style="color: #666666; font-family: 'Courier New', Courier, monospace;">')) = 1 then '</span><span class="Apple-style-span" style="color: purple; font-family: 'Courier New', Courier, monospace;">Grande total</span><span class="Apple-style-span" style="color: #666666; font-family: 'Courier New', Courier, monospace;">'</span><br />
<span class="Apple-style-span" style="color: #666666; font-family: 'Courier New', Courier, monospace;"> when grouping(department_name) = 1 then '</span><span class="Apple-style-span" style="color: purple; font-family: 'Courier New', Courier, monospace;">Subtotal por ano</span><span class="Apple-style-span" style="color: #666666; font-family: 'Courier New', Courier, monospace;">'</span><br />
<span class="Apple-style-span" style="color: #666666; font-family: 'Courier New', Courier, monospace;"> when grouping(TO_CHAR(hire_date, '</span><span class="Apple-style-span" style="color: purple; font-family: 'Courier New', Courier, monospace;">yyyy</span><span class="Apple-style-span" style="color: #666666; font-family: 'Courier New', Courier, monospace;">')) = 1 then '</span><span class="Apple-style-span" style="color: purple; font-family: 'Courier New', Courier, monospace;">Subtotal por departamento</span><span class="Apple-style-span" style="color: #666666; font-family: 'Courier New', Courier, monospace;">'</span><br />
<span class="Apple-style-span" style="color: #666666; font-family: 'Courier New', Courier, monospace;"> else null</span><br />
<span class="Apple-style-span" style="color: #666666; font-family: 'Courier New', Courier, monospace;"> end column_type</span><span class="Apple-style-span" style="color: #666666; font-family: 'Courier New', Courier, monospace; font-size: x-small;">,</span></div>
<div>
<span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; font-size: 13px; line-height: 20px;"> department_name,</span><br />
<span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; font-size: 13px; line-height: 20px;"> TO_CHAR(hire_date, '<span class="Apple-style-span" style="color: purple;">yyyy</span>') hire_year,</span><br />
<span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; font-size: 13px; line-height: 20px;"> SUM(salary)</span><br />
<span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; font-size: 13px; line-height: 20px;"><span class="Apple-style-span" style="color: blue;">FROM </span>hr.employees emp</span><br />
<div style="background-color: white; color: #555555; font-family: Arial, Helvetica, sans-serif; font-size: 13px; line-height: 20px; text-align: justify;">
<span class="Apple-style-span" style="background-color: white; font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="color: blue;">JOIN </span>HR.departments dept USING (department_id)</span></div>
<span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; font-size: 13px; line-height: 20px;"><span class="Apple-style-span" style="color: blue;">JOIN</span> HR.jobs j USING (job_id)</span><br />
<span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; font-size: 13px; line-height: 20px;"><span class="Apple-style-span" style="color: blue;">WHERE</span> department_name IN ( '<span class="Apple-style-span" style="color: purple;">IT</span>', '<span class="Apple-style-span" style="color: purple;">Sales</span>')</span><br />
<span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; font-size: 13px; line-height: 20px;"><span class="Apple-style-span" style="color: blue;">GROUP BY CUBE</span>(department_name, TO_CHAR(hire_date, '<span class="Apple-style-span" style="color: purple;">yyyy</span>'))</span><br />
<span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; font-size: 13px; line-height: 20px;"><span class="Apple-style-span" style="color: blue;">ORDER BY</span> 1,2;</span></div>
<br />
<table border="1" cellspacing="0" cols="4">
<colgroup><col width="178"></col><col width="143"></col><col width="86"></col><col width="86"></col></colgroup>
<tbody>
<tr>
<td align="CENTER" bgcolor="#C0C0C0" height="17" width="178"><b>Column_Type</b></td>
<td align="CENTER" bgcolor="#C0C0C0" width="143"><b>Department_Name</b></td>
<td align="CENTER" bgcolor="#C0C0C0" width="86"><b>Hire_Year</b></td>
<td align="CENTER" bgcolor="#C0C0C0" width="86"><b>SUM(Salary)</b></td>
</tr>
<tr>
<td align="LEFT" height="17"></td>
<td align="LEFT">IT</td>
<td align="RIGHT" sdnum="1033;" sdval="2005">2005</td>
<td align="RIGHT" sdnum="1033;" sdval="4800">4800</td>
</tr>
<tr>
<td align="LEFT" height="17"></td>
<td align="LEFT">IT</td>
<td align="RIGHT" sdnum="1033;" sdval="2006">2006</td>
<td align="RIGHT" sdnum="1033;" sdval="13800">13800</td>
</tr>
<tr>
<td align="LEFT" height="17"></td>
<td align="LEFT">IT</td>
<td align="RIGHT" sdnum="1033;" sdval="2007">2007</td>
<td align="RIGHT" sdnum="1033;" sdval="10200">10200</td>
</tr>
<tr>
<td align="LEFT" height="17">Subtotal by department</td>
<td align="LEFT">IT</td>
<td align="RIGHT" sdnum="1033;" sdval="2005"></td>
<td align="RIGHT" sdnum="1033;" sdval="28800">28800</td>
</tr>
<tr>
<td align="LEFT" height="17"></td>
<td align="LEFT">Sales</td>
<td align="RIGHT" sdnum="1033;" sdval="2004">2004</td>
<td align="RIGHT" sdnum="1033;" sdval="53500">53500</td>
</tr>
<tr>
<td align="LEFT" height="17"></td>
<td align="LEFT">Sales</td>
<td align="RIGHT" sdnum="1033;" sdval="2005">2005</td>
<td align="RIGHT" sdnum="1033;" sdval="100300">100300</td>
</tr>
<tr>
<td align="LEFT" height="17"></td>
<td align="LEFT">Sales</td>
<td align="RIGHT" sdnum="1033;" sdval="2006">2006</td>
<td align="RIGHT" sdnum="1033;" sdval="59100">59100</td>
</tr>
<tr>
<td align="LEFT" height="17"></td>
<td align="LEFT">Sales</td>
<td align="RIGHT" sdnum="1033;" sdval="2007">2007</td>
<td align="RIGHT" sdnum="1033;" sdval="42200">42200</td>
</tr>
<tr>
<td align="LEFT" height="17"></td>
<td align="LEFT">Sales</td>
<td align="RIGHT" sdnum="1033;" sdval="2008">2008</td>
<td align="RIGHT" sdnum="1033;" sdval="49400">49400</td>
</tr>
<tr>
<td align="LEFT" height="17">Subtotal by department</td>
<td align="LEFT">Sales</td>
<td align="LEFT"><br /></td>
<td align="RIGHT" sdnum="1033;" sdval="304500">304500</td>
</tr>
<tr>
<td align="LEFT" height="17">Subtotal by year</td>
<td align="LEFT"><br /></td>
<td align="RIGHT" sdnum="1033;" sdval="2004">2004</td>
<td align="RIGHT" sdnum="1033;" sdval="53500">53500</td>
</tr>
<tr>
<td align="LEFT" height="17">Subtotal by year</td>
<td align="LEFT"><br /></td>
<td align="RIGHT" sdnum="1033;" sdval="2005">2005</td>
<td align="RIGHT" sdnum="1033;" sdval="105100">105100</td>
</tr>
<tr>
<td align="LEFT" height="17">Subtotal by year</td>
<td align="LEFT"><br /></td>
<td align="RIGHT" sdnum="1033;" sdval="2006">2006</td>
<td align="RIGHT" sdnum="1033;" sdval="72900">72900</td>
</tr>
<tr>
<td align="LEFT" height="17">Subtotal by year</td>
<td align="LEFT"><br /></td>
<td align="RIGHT" sdnum="1033;" sdval="2007">2007</td>
<td align="RIGHT" sdnum="1033;" sdval="52400">52400</td>
</tr>
<tr>
<td align="LEFT" height="17">Subtotal by year</td>
<td align="LEFT"><br /></td>
<td align="RIGHT" sdnum="1033;" sdval="2008">2008</td>
<td align="RIGHT" sdnum="1033;" sdval="49400">49400</td>
</tr>
<tr>
<td align="LEFT" height="17">Grand total</td>
<td align="LEFT"><br /></td>
<td align="LEFT"><br /></td>
<td align="RIGHT" sdnum="1033;" sdval="333300">333300</td>
</tr>
</tbody>
</table>
<br />
<div style="text-align: justify;">
Por fim, uma nota referente a clausula HAVING: ela filtra linhas agregadas que atendam (ou não) o predicado, mas todas linhas são consideradas para calcular os subtotais e totais. Isto pode resultar em uma certa inconsistência nos dados. Por exemplo, a consulta abaixo retorna duas linhas para os anos 2006 e 2007 em que mais de um funcionário foi contratado. Mas o valor na linha e subtotal (e total) é resultado da soma de três anos!</div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
<span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; line-height: 20px;"><span class="Apple-style-span" style="color: blue;">SELECT</span> department_name,</span><br />
<span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; line-height: 20px; text-align: -webkit-auto;"> TO_CHAR(hire_date, '<span class="Apple-style-span" style="color: purple;">yyyy</span>') hire_year,</span><br />
<span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; line-height: 20px; text-align: -webkit-auto;"> SUM(salary)</span><br />
<span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; line-height: 20px; text-align: -webkit-auto;"><span class="Apple-style-span" style="color: blue;">FROM </span>hr.employees emp</span><br />
<span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; line-height: 20px; text-align: -webkit-auto;"><span class="Apple-style-span" style="color: blue;">JOIN </span>HR.departments dept USING (department_id)</span><br />
<span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; line-height: 20px; text-align: -webkit-auto;"><span class="Apple-style-span" style="color: blue;">JOIN</span> HR.jobs j USING (job_id)</span><br />
<span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; line-height: 20px; text-align: -webkit-auto;"><span class="Apple-style-span" style="color: blue;">WHERE</span> department_name IN ( '<span class="Apple-style-span" style="color: purple;">IT</span>')</span><br />
<span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; line-height: 20px; text-align: -webkit-auto;"><span class="Apple-style-span" style="color: blue;">GROUP BY </span>department_name, </span><span class="Apple-style-span" style="background-color: white; color: blue; font-family: 'Courier New', Courier, monospace; line-height: 20px; text-align: -webkit-auto;">ROLLUP(</span><span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; line-height: 20px; text-align: -webkit-auto;">TO_CHAR(hire_date, '</span><span class="Apple-style-span" style="background-color: white; color: purple; font-family: 'Courier New', Courier, monospace; line-height: 20px; text-align: -webkit-auto;">yyyy</span><span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; line-height: 20px; text-align: -webkit-auto;">'))</span></div>
<div style="text-align: justify;">
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="color: blue;">HAVING COUNT</span><span class="Apple-style-span" style="color: #666666;">(*) > 1</span></span><br />
<span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; line-height: 20px; text-align: -webkit-auto;"><span class="Apple-style-span" style="color: blue;">ORDER BY</span> 1,2;</span></div>
<div style="text-align: justify;">
<span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; font-size: 13px; line-height: 20px; text-align: -webkit-auto;"><br /></span></div>
<table border="1" cellspacing="0" cols="3">
<colgroup><col width="167"></col><col width="91"></col><col width="86"></col></colgroup>
<tbody>
<tr>
<td align="CENTER" bgcolor="#C0C0C0" height="17" width="167"><b>Department_Name</b></td>
<td align="CENTER" bgcolor="#C0C0C0" width="91"><b>Hire_Year</b></td>
<td align="CENTER" bgcolor="#C0C0C0" width="86"><b>SUM(Salary)</b></td>
</tr>
<tr>
<td align="LEFT" height="17">IT</td>
<td align="RIGHT">2006</td>
<td align="RIGHT">13800</td>
</tr>
<tr>
<td align="LEFT" height="17">IT</td>
<td align="RIGHT">2007</td>
<td align="RIGHT">10200</td>
</tr>
<tr>
<td align="LEFT" height="17">IT</td>
<td align="LEFT"><br /></td>
<td align="RIGHT">28800</td>
</tr>
<tr>
<td align="LEFT" height="17"><br /></td>
<td align="LEFT"><br /></td>
<td align="RIGHT">28800</td></tr>
</tbody></table>
<div style="text-align: justify;">
<span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; font-size: 13px; line-height: 20px; text-align: -webkit-auto;"><br /></span></div>
<div style="text-align: justify;">
Uma possível solução é utilizar subconsultas:</div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
<span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; line-height: 20px;"><span class="Apple-style-span" style="color: blue;">SELECT</span> department_name,</span><br />
<span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; line-height: 20px; text-align: -webkit-auto;"> hire_year,</span><br />
<span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; line-height: 20px; text-align: -webkit-auto;"> SUM(sum_salary)</span></div>
<div style="text-align: justify;">
<span class="Apple-style-span" style="background-color: white; font-family: 'Courier New', Courier, monospace; line-height: 20px; text-align: -webkit-auto;"><span class="Apple-style-span" style="color: blue;">FROM(</span></span></div>
<div style="text-align: justify;">
</div>
<span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; line-height: 20px;"><span class="Apple-style-span" style="color: blue;"> SELECT</span> department_name,</span><br />
<span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; line-height: 20px; text-align: -webkit-auto;"> TO_CHAR(hire_date, '<span class="Apple-style-span" style="color: purple;">yyyy</span>') hire_year,</span><br />
<span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; line-height: 20px; text-align: -webkit-auto;"> SUM(salary) sum_salary</span><br />
<span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; line-height: 20px; text-align: -webkit-auto;"><span class="Apple-style-span" style="color: blue;"> FROM </span>hr.employees emp</span><br />
<span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; line-height: 20px; text-align: -webkit-auto;"><span class="Apple-style-span" style="color: blue;"> JOIN </span>HR.departments dept USING (department_id)</span><br />
<span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; line-height: 20px; text-align: -webkit-auto;"><span class="Apple-style-span" style="color: blue;"> JOIN</span> HR.jobs j USING (job_id)</span><br />
<span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; line-height: 20px; text-align: -webkit-auto;"><span class="Apple-style-span" style="color: blue;"> WHERE</span> department_name IN ( '<span class="Apple-style-span" style="color: purple;">IT</span>')</span><br />
<span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; line-height: 20px; text-align: -webkit-auto;"><span class="Apple-style-span" style="color: blue;"> GROUP BY </span>department_name, </span><span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; line-height: 20px; text-align: -webkit-auto;">TO_CHAR(hire_date, '</span><span class="Apple-style-span" style="background-color: white; color: purple; font-family: 'Courier New', Courier, monospace; line-height: 20px; text-align: -webkit-auto;">yyyy</span><span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; line-height: 20px; text-align: -webkit-auto;">')</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="color: blue;"> HAVING COUNT</span><span class="Apple-style-span" style="color: #666666;">(*) > 1)</span></span><br />
<div style="text-align: justify;">
<span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; line-height: 20px;"><span class="Apple-style-span" style="color: blue;">GROUP BY ROLLUP(</span>department_name, hire_year</span><span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; line-height: 20px; text-align: -webkit-auto;">)</span></div>
<span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; line-height: 20px; text-align: -webkit-auto;"><span class="Apple-style-span" style="color: blue;">ORDER BY</span> 1,2;</span><br />
<br />
<div style="text-align: justify;">
<table border="1" cellspacing="0" cols="3">
<colgroup><col width="167"></col><col width="91"></col><col width="86"></col></colgroup>
<tbody>
<tr>
<td align="CENTER" bgcolor="#C0C0C0" height="17" width="167"><b>Department_Name</b></td>
<td align="CENTER" bgcolor="#C0C0C0" width="91"><b>Hire_Year</b></td>
<td align="CENTER" bgcolor="#C0C0C0" width="86"><b>SUM(Salary)</b></td>
</tr>
<tr>
<td align="LEFT" height="17">IT</td>
<td align="RIGHT">2006</td>
<td align="RIGHT">13800</td>
</tr>
<tr>
<td align="LEFT" height="17">IT</td>
<td align="RIGHT">2007</td>
<td align="RIGHT">10200</td>
</tr>
<tr>
<td align="LEFT" height="17">IT</td>
<td align="LEFT"><br /></td>
<td align="RIGHT">24000</td>
</tr>
<tr>
<td align="LEFT" height="17"><br /></td>
<td align="LEFT"><br /></td>
<td align="RIGHT">24000</td>
</tr>
</tbody>
</table>
<br />
Espero que tenha sido claro nas explicações. Por favor, qualquer dúvida, sugestão, etc... utilizem os comentários.<br />
<br /></div>
<table border="1" cellspacing="10" cols="3" frame="void">
<colgroup><col width="75"></col><col width="75"></col><col width="75"></col></colgroup>
<tbody>
<tr>
<td align="LEFT" height="17" width="75"><a href="http://sqlbrasil.blogspot.com/2011/11/subtotais-em-oracle-sql-parte-i.html">Parte 1</a></td>
<td align="LEFT" width="75"><a href="http://sqlbrasil.blogspot.com/2011/11/subtotais-em-oracle-sql-parte-ii.html">Parte 2</a></td>
<td align="LEFT" width="75"><a href="http://sqlbrasil.blogspot.com/2011/11/subtotais-em-orcle-parte-iii.html">Parte 3</a></td>
</tr>
</tbody>
</table>
</div>Miguel Fornarihttp://www.blogger.com/profile/14892590771484626747noreply@blogger.com0tag:blogger.com,1999:blog-6100419445090790199.post-87657999524013334282011-11-10T20:28:00.001-02:002011-11-14T19:01:45.407-02:00SUBTOTAIS EM ORACLE SQL - PARTE II<div style="text-align: justify;">
Outra clausula que pode ser utilizada é o GROUP BY CUBE. A principal diferença é que ela retorna subtotais individuais para cada coluna listada, enquanto o ROLLUP retorna subtotais agregando apenas a última coluna. Ou seja, em geral, CUBE retornará mais linahs que ROLLUP. Por exemplo:</div>
<br />
<span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; font-size: 13px; line-height: 20px;"><span class="Apple-style-span" style="color: blue;">SELECT</span> department_name,</span><br />
<span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; font-size: 13px; line-height: 20px;"> TO_CHAR(hire_date, '<span class="Apple-style-span" style="color: purple;">yyyy</span>') hire_year,</span><br />
<span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; font-size: 13px; line-height: 20px;"> SUM(salary)</span><br />
<span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; font-size: 13px; line-height: 20px;"><span class="Apple-style-span" style="color: blue;">FROM </span>hr.employees emp</span><br />
<div style="text-align: justify;">
<span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; font-size: 13px; line-height: 20px;"><span class="Apple-style-span" style="color: blue;">JOIN </span>HR.departments dept USING (department_id)</span></div>
<span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; font-size: 13px; line-height: 20px;"><span class="Apple-style-span" style="color: blue;">JOIN</span> HR.jobs j USING (job_id)</span><br />
<span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; font-size: 13px; line-height: 20px;"><span class="Apple-style-span" style="color: blue;">WHERE</span> department_name IN ( '<span class="Apple-style-span" style="color: purple;">IT</span>', '<span class="Apple-style-span" style="color: purple;">Sales</span>')</span><br />
<span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; font-size: 13px; line-height: 20px;"><span class="Apple-style-span" style="color: blue;">GROUP BY CUBE</span>(department_name, TO_CHAR(hire_date, '<span class="Apple-style-span" style="color: purple;">yyyy</span>'))</span><br />
<span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; font-size: 13px; line-height: 20px;"><span class="Apple-style-span" style="color: blue;">ORDER BY</span> 1,2;</span><br />
<br />
Resposta - linhas verdes mostram subtotais por Department_Name; azuis, por Hire_Year; e amarela, o grande total.:<br />
<br />
<table border="1" cellspacing="0" cols="3">
<colgroup><col width="135"></col><col width="86"></col><col width="86"></col></colgroup>
<tbody>
<tr>
<td align="CENTER" bgcolor="#C0C0C0" height="17" width="135"><b>Department_Name</b></td>
<td align="CENTER" bgcolor="#C0C0C0" width="86"><b>Hire_Year</b></td>
<td align="CENTER" bgcolor="#C0C0C0" width="86"><b>SUM(Salary)</b></td>
</tr>
<tr>
<td align="CENTER" height="17">IT</td>
<td align="CENTER" sdnum="1033;" sdval="2005">2005</td>
<td align="RIGHT" sdnum="1033;" sdval="4800">4800</td>
</tr>
<tr>
<td align="CENTER" height="17">IT</td>
<td align="CENTER" sdnum="1033;" sdval="2006">2006</td>
<td align="RIGHT" sdnum="1033;" sdval="13800">13800</td>
</tr>
<tr>
<td align="CENTER" height="17">IT</td>
<td align="CENTER" sdnum="1033;" sdval="2007">2007</td>
<td align="RIGHT" sdnum="1033;" sdval="10200">10200</td>
</tr>
<tr>
<td align="CENTER" bgcolor="#94BD5E" height="17">IT</td>
<td align="CENTER" bgcolor="#94BD5E"><br /></td>
<td align="RIGHT" bgcolor="#94BD5E" sdnum="1033;" sdval="28800">28800</td>
</tr>
<tr>
<td align="CENTER" height="17">Sales</td>
<td align="CENTER" sdnum="1033;" sdval="2004">2004</td>
<td align="RIGHT" sdnum="1033;" sdval="53500">53500</td>
</tr>
<tr>
<td align="CENTER" height="17">Sales</td>
<td align="CENTER" sdnum="1033;" sdval="2005">2005</td>
<td align="RIGHT" sdnum="1033;" sdval="100300">100300</td>
</tr>
<tr>
<td align="CENTER" height="17">Sales</td>
<td align="CENTER" sdnum="1033;" sdval="2006">2006</td>
<td align="RIGHT" sdnum="1033;" sdval="59100">59100</td>
</tr>
<tr>
<td align="CENTER" height="17">Sales</td>
<td align="CENTER" sdnum="1033;" sdval="2007">2007</td>
<td align="RIGHT" sdnum="1033;" sdval="42200">42200</td>
</tr>
<tr>
<td align="CENTER" height="17">Sales</td>
<td align="CENTER" sdnum="1033;" sdval="2008">2008</td>
<td align="RIGHT" sdnum="1033;" sdval="49400">49400</td>
</tr>
<tr>
<td align="CENTER" bgcolor="#94BD5E" height="17">Sales</td>
<td align="CENTER" bgcolor="#94BD5E"><br /></td>
<td align="RIGHT" bgcolor="#94BD5E" sdnum="1033;" sdval="304500">304500</td>
</tr>
<tr>
<td align="CENTER" bgcolor="#99CCFF" height="17"><br /></td>
<td align="CENTER" bgcolor="#99CCFF" sdnum="1033;" sdval="2004">2004</td>
<td align="RIGHT" bgcolor="#99CCFF" sdnum="1033;" sdval="53500">53500</td>
</tr>
<tr>
<td align="CENTER" bgcolor="#99CCFF" height="17"><br /></td>
<td align="CENTER" bgcolor="#99CCFF" sdnum="1033;" sdval="2005">2005</td>
<td align="RIGHT" bgcolor="#99CCFF" sdnum="1033;" sdval="105100">105100</td>
</tr>
<tr>
<td align="CENTER" bgcolor="#99CCFF" height="17"><br /></td>
<td align="CENTER" bgcolor="#99CCFF" sdnum="1033;" sdval="2006">2006</td>
<td align="RIGHT" bgcolor="#99CCFF" sdnum="1033;" sdval="72900">72900</td>
</tr>
<tr>
<td align="CENTER" bgcolor="#99CCFF" height="17"><br /></td>
<td align="CENTER" bgcolor="#99CCFF" sdnum="1033;" sdval="2007">2007</td>
<td align="RIGHT" bgcolor="#99CCFF" sdnum="1033;" sdval="52400">52400</td>
</tr>
<tr>
<td align="CENTER" bgcolor="#99CCFF" height="17"><br /></td>
<td align="CENTER" bgcolor="#99CCFF" sdnum="1033;" sdval="2008">2008</td>
<td align="RIGHT" bgcolor="#99CCFF" sdnum="1033;" sdval="49400">49400</td>
</tr>
<tr>
<td align="CENTER" bgcolor="#E6E64C" height="17"><br /></td>
<td align="CENTER" bgcolor="#E6E64C"><br /></td>
<td align="RIGHT" bgcolor="#E6E64C" sdnum="1033;" sdval="333300">333300</td>
</tr>
</tbody>
</table>
<br />
<div style="text-align: justify;">
Digamos agora que outras colunas relativas a departamento são necessárias, como endereço e cidade. Obviamente, cada departamento tem apenas um endereço e cidade e talvez não seja necessário calcular subtotais separados para cada uma destas colunas. Para resolver esta situação, existe o GROUP BY GROUPING SETS. </div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
A consulta abaixo define dois grupos para calcular subtotais, cada grupo definido por parenteses. O primeiro inclui três colunas: Department_Name, Street_Address e City. O segundo apenas uma coluna: Hire_Year.</div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
</div>
<div style="text-align: -webkit-auto;">
<span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; font-size: 13px; line-height: 20px;"><span class="Apple-style-span" style="color: blue;">SELECT</span> department_name, </span></div>
<div style="text-align: -webkit-auto;">
<span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; font-size: 13px; line-height: 20px;"> street_address, </span></div>
<div style="text-align: -webkit-auto;">
<span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; font-size: 13px; line-height: 20px;"> city,</span></div>
<div style="text-align: -webkit-auto;">
<span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; font-size: 13px; line-height: 20px;"> TO_CHAR(hire_date, '<span class="Apple-style-span" style="color: purple;">yyyy</span>') hire_year,</span></div>
<div style="text-align: -webkit-auto;">
<span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; font-size: 13px; line-height: 20px;"> SUM(salary)</span></div>
<div style="text-align: -webkit-auto;">
<span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; font-size: 13px; line-height: 20px;"><span class="Apple-style-span" style="color: blue;">FROM </span>hr.employees emp</span></div>
<span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; font-size: 13px; line-height: 20px;"><span class="Apple-style-span" style="color: blue;">JOIN </span>HR.departments dept USING (department_id)</span><br />
<div style="text-align: -webkit-auto;">
<span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; font-size: 13px; line-height: 20px;"><span class="Apple-style-span" style="color: blue;">JOIN</span> HR.jobs j USING (job_id)</span><br />
<span class="Apple-style-span" style="background-color: white; color: blue; font-family: 'Courier New', Courier, monospace; font-size: 13px; line-height: 20px;">JOIN</span><span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; font-size: 13px; line-height: 20px;"> HR.locations loc USING (location_id)</span></div>
<div style="text-align: -webkit-auto;">
<span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; font-size: 13px; line-height: 20px;"><span class="Apple-style-span" style="color: blue;">WHERE</span> department_name IN ( '<span class="Apple-style-span" style="color: purple;">IT</span>', '<span class="Apple-style-span" style="color: purple;">Sales</span>')</span></div>
<div style="text-align: -webkit-auto;">
<span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; font-size: 13px; line-height: 20px;"><span class="Apple-style-span" style="color: blue;">GROUP BY GROUPING SETS</span>((department_name, street_address, city) , </span></div>
<div style="text-align: -webkit-auto;">
<span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; font-size: 13px; line-height: 20px;"> (TO_CHAR(hire_date, '<span class="Apple-style-span" style="color: purple;">yyyy</span>'))</span></div>
<div style="text-align: -webkit-auto;">
<span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; font-size: 13px; line-height: 20px;"> )</span></div>
<div style="text-align: -webkit-auto;">
<span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; font-size: 13px; line-height: 20px;"><span class="Apple-style-span" style="color: blue;">ORDER BY</span> 1,4;</span></div>
<div style="text-align: -webkit-auto;">
<span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; font-size: 13px; line-height: 20px;"><br /></span></div>
<div style="text-align: -webkit-auto;">
A consulta retorna apenas linhas de subtotais para os grupos indicados.<br />
<br /></div>
<table border="1" cellspacing="0" cols="5">
<colgroup><col width="135"></col><col width="275"></col><col width="86"></col><col width="86"></col><col width="86"></col></colgroup>
<tbody>
<tr>
<td align="CENTER" bgcolor="#C0C0C0" height="17" width="135"><b>Department_Name</b></td>
<td align="CENTER" bgcolor="#C0C0C0" width="275"><b>Street_Address</b></td>
<td align="CENTER" bgcolor="#C0C0C0" width="86"><b>City</b></td>
<td align="CENTER" bgcolor="#C0C0C0" width="86"><b>Hire_Year</b></td>
<td align="CENTER" bgcolor="#C0C0C0" width="86"><b>SUM(Salary)</b></td>
</tr>
<tr>
<td align="LEFT" bgcolor="#94BD5E" height="17">IT</td>
<td align="LEFT" bgcolor="#94BD5E">2014 Jabberwocky Rd</td>
<td align="LEFT" bgcolor="#94BD5E">Southlake</td>
<td align="LEFT" bgcolor="#94BD5E"><br /></td>
<td align="RIGHT" bgcolor="#94BD5E" sdnum="1033;" sdval="28800">28800</td>
</tr>
<tr>
<td align="LEFT" bgcolor="#94BD5E" height="17">Sales</td>
<td align="LEFT" bgcolor="#94BD5E">Magdalen Centre, The Oxford Science Park</td>
<td align="LEFT" bgcolor="#94BD5E">Oxford</td>
<td align="LEFT" bgcolor="#94BD5E"><br /></td>
<td align="RIGHT" bgcolor="#94BD5E" sdnum="1033;" sdval="304500">304500</td>
</tr>
<tr>
<td align="LEFT" bgcolor="#E6E64C" height="17"><br /></td>
<td align="LEFT" bgcolor="#E6E64C"><br /></td>
<td align="LEFT" bgcolor="#E6E64C"><br /></td>
<td align="RIGHT" bgcolor="#E6E64C" sdnum="1033;" sdval="2004">2004</td>
<td align="RIGHT" bgcolor="#E6E64C" sdnum="1033;" sdval="53500">53500</td>
</tr>
<tr>
<td align="LEFT" bgcolor="#E6E64C" height="17"><br /></td>
<td align="LEFT" bgcolor="#E6E64C"><br /></td>
<td align="LEFT" bgcolor="#E6E64C"><br /></td>
<td align="RIGHT" bgcolor="#E6E64C" sdnum="1033;" sdval="2005">2005</td>
<td align="RIGHT" bgcolor="#E6E64C" sdnum="1033;" sdval="105100">105100</td>
</tr>
<tr>
<td align="LEFT" bgcolor="#E6E64C" height="17"><br /></td>
<td align="LEFT" bgcolor="#E6E64C"><br /></td>
<td align="LEFT" bgcolor="#E6E64C"><br /></td>
<td align="RIGHT" bgcolor="#E6E64C" sdnum="1033;" sdval="2006">2006</td>
<td align="RIGHT" bgcolor="#E6E64C" sdnum="1033;" sdval="72900">72900</td>
</tr>
<tr>
<td align="LEFT" bgcolor="#E6E64C" height="17"><br /></td>
<td align="LEFT" bgcolor="#E6E64C"><br /></td>
<td align="LEFT" bgcolor="#E6E64C"><br /></td>
<td align="RIGHT" bgcolor="#E6E64C" sdnum="1033;" sdval="2007">2007</td>
<td align="RIGHT" bgcolor="#E6E64C" sdnum="1033;" sdval="52400">52400</td>
</tr>
<tr>
<td align="LEFT" bgcolor="#E6E64C" height="17"><br /></td>
<td align="LEFT" bgcolor="#E6E64C"><br /></td>
<td align="LEFT" bgcolor="#E6E64C"><br /></td>
<td align="RIGHT" bgcolor="#E6E64C" sdnum="1033;" sdval="2008">2008</td>
<td align="RIGHT" bgcolor="#E6E64C" sdnum="1033;" sdval="49400">49400</td>
</tr>
</tbody>
</table>
<br />
<div>
E como obter as linhas de detalhe, que foram somadas? Simples, basta combinar com uma consulta baseada apenas em GROUP BY através de UNION ALL:</div>
<div>
<br /></div>
<div>
<div>
<span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; font-size: 13px; line-height: 20px;"><span class="Apple-style-span" style="color: blue;">SELECT</span> department_name, </span></div>
<div>
<span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; font-size: 13px; line-height: 20px;"> street_address, </span></div>
<div>
<span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; font-size: 13px; line-height: 20px;"> city,</span></div>
<div>
<span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; font-size: 13px; line-height: 20px;"> TO_CHAR(hire_date, '<span class="Apple-style-span" style="color: purple;">yyyy</span>') hire_year,</span></div>
<div>
<span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; font-size: 13px; line-height: 20px;"> SUM(salary)</span></div>
<div>
<span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; font-size: 13px; line-height: 20px;"><span class="Apple-style-span" style="color: blue;">FROM </span>hr.employees emp</span></div>
<span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; font-size: 13px; line-height: 20px;"><span class="Apple-style-span" style="color: blue;">JOIN </span>HR.departments dept USING (department_id)</span><br />
<div>
<span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; font-size: 13px; line-height: 20px;"><span class="Apple-style-span" style="color: blue;">JOIN</span> HR.jobs j USING (job_id)</span></div>
<div>
<span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; font-size: 13px; line-height: 20px;"><span class="Apple-style-span" style="color: blue;">WHERE</span> department_name IN ( '<span class="Apple-style-span" style="color: purple;">IT</span>', '<span class="Apple-style-span" style="color: purple;">Sales</span>')</span></div>
<div>
<span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; font-size: 13px; line-height: 20px;"><span class="Apple-style-span" style="color: blue;">GROUP BY </span>department_name, street_address, city, </span><span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; font-size: 13px; line-height: 20px;">TO_CHAR(hire_date, '</span><span class="Apple-style-span" style="background-color: white; color: purple; font-family: 'Courier New', Courier, monospace; font-size: 13px; line-height: 20px;">yyyy</span><span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; font-size: 13px; line-height: 20px;">')</span></div>
<div>
<span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; font-size: 13px; line-height: 20px;"><span class="Apple-style-span" style="color: blue;">ORDER BY</span> 1,4;</span></div>
</div>
<div>
<div>
<span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; font-size: 13px; line-height: 20px;"><span class="Apple-style-span" style="color: blue;">UNION ALL</span></span><br />
<span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; font-size: 13px; line-height: 20px;"><span class="Apple-style-span" style="color: blue;">SELECT</span> department_name, </span></div>
<div>
<span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; font-size: 13px; line-height: 20px;"> street_address, </span></div>
<div>
<span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; font-size: 13px; line-height: 20px;"> city,</span></div>
<div>
<span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; font-size: 13px; line-height: 20px;"> TO_CHAR(hire_date, '<span class="Apple-style-span" style="color: purple;">yyyy</span>') hire_year,</span></div>
<div>
<span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; font-size: 13px; line-height: 20px;"> SUM(salary)</span></div>
<div>
<span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; font-size: 13px; line-height: 20px;"><span class="Apple-style-span" style="color: blue;">FROM </span>hr.employees emp</span></div>
<span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; font-size: 13px; line-height: 20px;"><span class="Apple-style-span" style="color: blue;">JOIN </span>HR.departments dept USING (department_id)</span><br />
<div>
<span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; font-size: 13px; line-height: 20px;"><span class="Apple-style-span" style="color: blue;">JOIN</span> HR.jobs j USING (job_id)</span></div>
<div>
<span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; font-size: 13px; line-height: 20px;"><span class="Apple-style-span" style="color: blue;">WHERE</span> department_name IN ( '<span class="Apple-style-span" style="color: purple;">IT</span>', '<span class="Apple-style-span" style="color: purple;">Sales</span>')</span></div>
<div>
<span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; font-size: 13px; line-height: 20px;"><span class="Apple-style-span" style="color: blue;">GROUP BY GROUPING SETS</span>((department_name, street_address, city) , </span></div>
<div>
<span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; font-size: 13px; line-height: 20px;"> (TO_CHAR(hire_date, '<span class="Apple-style-span" style="color: purple;">yyyy</span>'))</span></div>
<div>
<span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; font-size: 13px; line-height: 20px;"> )</span></div>
<div>
<span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; font-size: 13px; line-height: 20px;"><span class="Apple-style-span" style="color: blue;">ORDER BY</span> 1,4;</span><br />
<span class="Apple-style-span" style="background-color: white; color: #555555; font-family: 'Courier New', Courier, monospace; font-size: 13px; line-height: 20px;"><br /></span><br />
<span class="Apple-style-span" style="font-family: inherit;"><span class="Apple-style-span" style="line-height: 20px;">Na terceira parte mostrarei a função GROUPING, muito útil para formatar resultados incluindo linhas de detalhe e linhas de subtotal.</span></span><br />
<span class="Apple-style-span" style="line-height: 20px;"><br /></span></div>
<table border="1" cellspacing="10" cols="3" frame="void">
<colgroup><col width="75"></col><col width="75"></col><col width="75"></col></colgroup>
<tbody>
<tr>
<td align="LEFT" height="17" width="75"><a href="http://sqlbrasil.blogspot.com/2011/11/subtotais-em-oracle-sql-parte-i.html">Parte 1</a></td>
<td align="LEFT" width="75"><a href="http://sqlbrasil.blogspot.com/2011/11/subtotais-em-oracle-sql-parte-ii.html">Parte 2</a></td>
<td align="LEFT" width="75"><a href="http://sqlbrasil.blogspot.com/2011/11/subtotais-em-orcle-parte-iii.html">Parte 3</a></td>
</tr>
</tbody>
</table>
</div>Miguel Fornarihttp://www.blogger.com/profile/14892590771484626747noreply@blogger.com0tag:blogger.com,1999:blog-6100419445090790199.post-4950622186782582852011-11-09T19:01:00.000-02:002011-11-14T19:01:21.871-02:00Subtotais em Oracle SQL - parte IA clausula GROUP BY é bastante conhecida para obter dados agregados. Por exemplo, a consulta abaixo obtem a soma dos salários por departamento e ano de contratação:<br />
<br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="color: blue;">SELECT </span>department_name,</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> TO_CHAR(hire_date, '<span class="Apple-style-span" style="color: purple;">yyyy</span>') hire_year,</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> SUM(salary)</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="color: blue;">FROM </span>hr.employees emp</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="color: blue;">JOIN</span> HR.departments dept USING (department_id)</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="color: blue;">JOIN </span>HR.jobs j USING (job_id)</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="color: blue;">WHERE</span> department_name IN ( '<span class="Apple-style-span" style="color: purple;">IT</span>', '<span class="Apple-style-span" style="color: purple;">Sales</span>')</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="color: blue;">GROUP BY</span> department_name,</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> TO_CHAR(hire_date, '<span class="Apple-style-span" style="color: purple;">yyyy</span>')</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="color: blue;">ORDER BY</span> 1,2;</span><br />
<br />
<table border="1" cellpadding="0" cellspacing="0">
<tbody>
<tr> <th align="CENTER" bgcolor="#C0C0C0" width="146">Department_Name</th>
<th align="CENTER" bgcolor="#C0C0C0" width="84">Hire_Year</th>
<th align="CENTER" bgcolor="#C0C0C0" width="102">SUM(Salary)</th>
</tr>
</tbody><tbody id="data">
<tr>
<td>IT</td>
<td>2005</td>
<td align="right">4800</td>
</tr>
<tr>
<td>IT</td>
<td>2006</td>
<td align="right">13800</td>
</tr>
<tr>
<td>IT</td>
<td>2007</td>
<td align="right">10200</td>
</tr>
<tr>
<td>Sales</td>
<td>2004</td>
<td align="right">53500</td>
</tr>
<tr>
<td>Sales</td>
<td>2005</td>
<td align="right">100300</td>
</tr>
<tr>
<td>Sales</td>
<td>2006</td>
<td align="right">59100</td>
</tr>
<tr>
<td>Sales</td>
<td>2007</td>
<td align="right">42200</td>
</tr>
<tr>
<td>Sales</td>
<td>2008</td>
<td align="right">49400</td>
</tr>
</tbody></table>
<br />
Mas se estiver preparando um report, é provável que o requisito inclua linhas de subtotais. Neste ponto entra uma opção bem menos conhecida, o GROUP BY ROLLUP. Veja a mesma consulta utilizando esta opção. As linhas destacadas em verde são novas e representam subtotais e o total geral.<br />
<br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="color: blue;">SELECT</span> department_name,</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> TO_CHAR(hire_date, '<span class="Apple-style-span" style="color: purple;">yyyy</span>') hire_year,</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> SUM(salary)</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="color: blue;">FROM </span>hr.employees emp</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="color: blue;">JOIN </span>HR.departments dept USING (department_id)</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="color: blue;">JOIN</span> HR.jobs j USING (job_id)</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="color: blue;">WHERE</span> department_name IN ( '<span class="Apple-style-span" style="color: purple;">IT</span>', '<span class="Apple-style-span" style="color: purple;">Sales</span>')</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="color: blue;">GROUP BY </span>department_name, </span><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">ROLLUP(</span><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">TO_CHAR(hire_date, '</span><span class="Apple-style-span" style="color: purple; font-family: 'Courier New', Courier, monospace;">yyyy</span><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">'))</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="color: blue;">ORDER BY</span> 1,2;</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><br /></span>
<br />
<table border="1" cellpadding="0" cellspacing="0">
<tbody>
<tr> <th align="CENTER" bgcolor="#C0C0C0" width="146">Department_Name</th>
<th align="CENTER" bgcolor="#C0C0C0" width="84">Hire_Year</th>
<th align="CENTER" bgcolor="#C0C0C0" width="102">SUM(Salary)</th>
</tr>
</tbody><tbody id="data">
<tr>
<td>IT</td>
<td>2005</td>
<td align="right">4800</td>
</tr>
<tr>
<td>IT</td>
<td>2006</td>
<td align="right">13800</td>
</tr>
<tr>
<td>IT</td>
<td>2007</td>
<td align="right">10200</td>
</tr>
<tr>
<td bgcolor="#66cdaa">IT</td>
<td bgcolor="#66cdaa"></td>
<td align="right" bgcolor="#66cdaa">28800</td>
</tr>
<tr>
<td>Sales</td>
<td>2004</td>
<td align="right">53500</td>
</tr>
<tr>
<td>Sales</td>
<td>2005</td>
<td align="right">100300</td>
</tr>
<tr>
<td>Sales</td>
<td>2006</td>
<td align="right">59100</td>
</tr>
<tr>
<td>Sales</td>
<td>2007</td>
<td align="right">42200</td>
</tr>
<tr>
<td>Sales</td>
<td>2008</td>
<td align="right">49400</td>
</tr>
<tr>
<td bgcolor="#66cdaa">Sales</td>
<td bgcolor="#66cdaa"></td>
<td align="right" bgcolor="#66cdaa">304500</td>
</tr>
<tr>
<td bgcolor="#66cdaa"></td>
<td bgcolor="#66cdaa"></td>
<td align="right" bgcolor="#66cdaa">333300</td>
</tr>
</tbody></table>
<br />
<div>
O ROLLUP não precisa incluir todas as colunas. Neste caso, a consulta retorna subtotais apenas para as colunas não citadas no ROLLUP. Ou, em outros termos, os totais deconsideram os valores individuais das colunas incluídas no ROLLUP. Veja o exemplo abaixo:</div>
<br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="color: blue;">SELECT</span> department_name,</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> TO_CHAR(hire_date, '<span class="Apple-style-span" style="color: purple;">yyyy</span>') hire_year,</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> SUM(salary)</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="color: blue;">FROM </span>hr.employees emp</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="color: blue;">JOIN </span>HR.departments dept USING (department_id)</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="color: blue;">JOIN</span> HR.jobs j USING (job_id)</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="color: blue;">WHERE</span> department_name IN ( '<span class="Apple-style-span" style="color: purple;">IT</span>', '<span class="Apple-style-span" style="color: purple;">Sales</span>')</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="color: blue;">GROUP BY ROLLUP</span>(department_name, TO_CHAR(hire_date, '<span class="Apple-style-span" style="color: purple;">yyyy</span>'))</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="color: blue;">ORDER BY</span> 1,2;</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><br /></span>
<br />
<table border="1" cellpadding="0" cellspacing="0">
<tbody>
<tr> <th align="CENTER" bgcolor="#C0C0C0" width="146">Department_Name</th>
<th align="CENTER" bgcolor="#C0C0C0" width="84">Hire_Year</th>
<th align="CENTER" bgcolor="#C0C0C0" width="102">SUM(Salary)</th>
</tr>
</tbody><tbody id="data">
<tr>
<td>IT</td>
<td>2005</td>
<td align="right">4800</td>
</tr>
<tr>
<td>IT</td>
<td>2006</td>
<td align="right">13800</td>
</tr>
<tr>
<td>IT</td>
<td>2007</td>
<td align="right">10200</td>
</tr>
<tr>
<td bgcolor="#66cdaa">IT</td>
<td bgcolor="#66cdaa"></td>
<td align="right" bgcolor="#66cdaa">28800</td>
</tr>
<tr>
<td>Sales</td>
<td>2004</td>
<td align="right">53500</td>
</tr>
<tr>
<td>Sales</td>
<td>2005</td>
<td align="right">100300</td>
</tr>
<tr>
<td>Sales</td>
<td>2006</td>
<td align="right">59100</td>
</tr>
<tr>
<td>Sales</td>
<td>2007</td>
<td align="right">42200</td>
</tr>
<tr>
<td>Sales</td>
<td>2008</td>
<td align="right">49400</td>
</tr>
<tr>
<td bgcolor="#66cdaa">Sales</td>
<td bgcolor="#66cdaa"></td>
<td align="right" bgcolor="#66cdaa">304500</td>
</tr>
</tbody></table>
<div>
<br /></div>
<div>
Para finalizar, todos os exemplos utilizaram a função SUM, mas é possível utilizar qualquer outra função, como MIN, MAX, AVG e COUNT.<br />
<br />
Na parte II falarei sobre o CUBE, outra variação de GROUP BY que pode ser útil para obter subtotais e totais.</div>
<div>
<br />
<table border="1" cellspacing="10" cols="3" frame="void">
<colgroup><col width="75"></col><col width="75"></col><col width="75"></col></colgroup>
<tbody>
<tr>
<td align="LEFT" height="17" width="75"><a href="http://sqlbrasil.blogspot.com/2011/11/subtotais-em-oracle-sql-parte-i.html">Parte 1</a></td>
<td align="LEFT" width="75"><a href="http://sqlbrasil.blogspot.com/2011/11/subtotais-em-oracle-sql-parte-ii.html">Parte 2</a></td>
<td align="LEFT" width="75"><a href="http://sqlbrasil.blogspot.com/2011/11/subtotais-em-orcle-parte-iii.html">Parte 3</a></td>
</tr>
</tbody>
</table>
<br /></div>
<div>
<br /></div>Miguel Fornarihttp://www.blogger.com/profile/14892590771484626747noreply@blogger.com0