SQL Server 2008 - Stored Procedures no SQL Server

O que é uma Stored Procedure?

Iremos definir de uma forma prática e objetiva. As denominadas “Stored Procedures” (Procedimentos Armazenados) como o próprio nome diz, é uma seqüência de comandos SQL compilados e armazenados no Banco de Dados.

  

Tipos de Stored Procedures

Nesta matéria abordaremos os dois principais tipos de Stored Procedures no SQL Server, System Procedures (Procedimentos Armazenados do Sistema) e User Stored Procedures (Procedures Locais ou de Usuários). É importante salientar que existem outros dois tipos de Stored Procedures, as Procedures Temporárias e as Estendidas, esta última é implementada como uma DLL, mas neste artigo falaremos um pouco das duas primeiras.

  

Utilizando o Banco de Dados Northwind

Utilizaremos o Banco exemplo Northwind para trabalhar nesta matéria, para quem não possui instalado poderá ser baixado gratuitamente pelo site da Microsoft, no seguinte link:

http://download.microsoft.com/download/d/e/5/de57f203-3140-43e0-9473-d694d45bbd9b/SQL2000SampleDb.msi

Depois que rodarmos este executável, por padrão, será criado uma pasta no seguinte diretório:

C:\SQL Server 2000 Sample Databases

Copie os arquivos NORTHWND.LDF (Arquivo de Log) e NORTHWND.MDF (Arquivo de Dados) para a pasta padrão:

C:\Arquivos de programas\Microsoft SQL Server\MSSQL.1\MSSQL\Data

Utilizaremos a ferramenta padrão do SQL Server chamada “SQL Server Management Studio Express” para podermos registrar o Banco de Dados NorthWind. Depois de aberto este gerenciador, encontrará uma janela idêntica com a Figura 01.

Figura 01. SQL Server Management Studio Express.

Na região esquerda registraremos o banco NorthWind, para isto clique com o botão direito em “Databases” e escolha Attach...No botão Add adicionaremos o arquivo NorthWind.MDF e automaticamente será incluído o NorthWind.LDF, logo após dê um ok para finalizar esta operação. Veja Figura 02 e 03.

Figura 02. Registrando o Banco de Dados.

Figura 03. Banco de Dados NorthWind.

Observe: Caso já possua o Banco Registrado não é necessário realizar os passos descritos acima.

Executando Stored Procedures

Podemos utilizar o comando abaixo:

execute nome_da_SP

          parâmetro 1,

          parâmetro 2,

          etc...  

ou apenas a abreviação

exec sp_nome_da_SP

          parâmetro 1,

          parâmetro 2,

          etc...

  

System Procedures

As System Procedures são armazenadas no Banco de Dados, no nosso caso no Database Nortwind (vide Figura 04 ). Elas são iniciadas pelo prefixo sp e são criadas automaticamente na instalação do SQL Server. Logo abaixo abordaremos algumas das principais Stored Procedures para um melhor entendimento:

Figura 04. System Stored Procedures.

 sp_databases

Este procedimento é útil para mostrar os nomes dos Databases existentes no servidor.

exec sp_databases

Teremos um resultado parecido com o da Figura 05.

Figura 05. sp_databases.

sp_columns

Retorna informações das colunas de tabelas ou views.

Sp_columns

       [@table_name=] nome_objeto

       [, [@table_owner =] nome_dono]

       [, [@table_qualifier =] nome_banco]

       [, [@column_name =] nome_coluna]

       [, [@ODBCVer =] verODBC]

Argumento                             Descrição

@table_name                          Nome da tabela ou View

@table_owner                         Nome do dono da tabela

@table_qualifier                     Nome do banco de dados

@column_name                      Nome da coluna

@ODBCVer                                      Versão da ODBC utilizada

Se desejarmos retornar todos os campos de uma determinada tabela utilizaremos o comando abaixo:

exec sp_columns categories

Figura 06. Retornando os campos da tabela Categories.

Podemos também verificar se um campo existe na tabela Categories.

execute sp_columns

             Categories,

             dbo,

             Northwind,

             CategoryID

Neste caso verificaremos se o campo CategoryID existe no Banco de Dados NorthWind.

sp_tables

Retorna a lista de tabelas e visões que podem ser utilizados no ambiente atual.

Sp_tables

      [[@name=] ‘nome_objeto’]

      [,[@owner =] ‘nome_dono’]

      [,[@qualifier =] ‘nome_bd’]

Argumento                             Descrição

@name                                    Nome da tabela ou View

@owner                                  Nome do dono da tabela ou View

@qualifier                               Nome do banco de dados

 

Retornando todos os objetos

exec sp_tables

Figura 07. Retornando as tabelas.

Agora passaremos os parâmetros para verificarmos se a tabela ou a visão existe em determinado Database.

exec sp_tables

        Categories,

        dbo,

        Northwind

sp_stored_procedures

Esta retorna a lista de Stores Procedures no ambiente corrente.

sp_stored_procedures

      [[@sp_name=] ‘nome_da_procedure’]

      [,[@sp_owner =] ‘nome_dono’]

      [,[@sp_qualifier =] ‘nome_bd’]

  

Argumento                             Descrição

@sp_name                                    Nome da Procedure

@sp_owner                                  Nome do dono da Procedure

@sp_qualifier                               Nome do banco de dados

Retornando todas as Procedures

exec sp_stored_procedures

Figura 08. Retornando as SP.

  

Verificando se determinanda procedure existe na Base de Dados.

exec sp_stored_procedures

            CustOrderHist,

            dbo,

            Northwind

Importante ressaltar que o Banco de Dados Sql Server possui diversas Procedures de Sistemas e que são muito úteis na utilização de nossos sistemas. Este é um recurso muito interessante deste Banco de Dados, nesta parte da matéria procurei demostrar exemplos práticos de algumas das principais System Procedures.

User Stored Procedures (USP)

Os denominadas Procedimentos de usuários são criados no Banco de Dados para utilização do sistema. Uma nomenclatura recomendada para criação dos mesmos seria a utilização do prefixo “usp_” .  O uso de nomenclaturas é um princípio geralmente ignorado pela maioria das pessoas, mas que é altamente recomendado, pois servirá para a distinção em relação aos outros tipos de Stored Procedures encontradas. Um exemplo disto é a padronização no nome das System Procedures, são iniciadas pelo prefixo sp_.

Criando uma “Stored Procedure”

Para Criarmos uma Stored Procedure deveremos seguir o modelo abaixo:

create procedure <Nome da procedure>

<Onde irão os parâmetros de entrada seguido do tipo de campo>

as

< Irá a instrução SQL, caso possui um retorno o mesmo será o resultado da execução da Stored Procedure>

Seguindo o modelo acima, montaremos uma Stored Procedure que possui um parâmetro de Entrada e como retorno o resultado do comando select.

Procedure de Seleção

create procedure usp_BuscaCliente

@nome varchar(50)

as

select *

from dbo.Customers

where ContactTitle like '%' + @nome + '%'

Notem que o parâmetro de Entrada é precedido do caracter @.

Para executá-la:

exec usp_BuscaCliente 'Owner'

A Figura 09 demonstra o retorno da execução do procedimento usp_BuscaCliente.

Figura 09. Retorno da USP.

Procedure de Inserção

Criaremos uma Stored Procedure para inserir registros na tabela Customers, neste caso iremos inserir apenas em três campos, veja abaixo como ficaria:

create procedure usp_InsereCliente

@ID nchar(5), @Company nvarchar(40), @Contact nvarchar(30)

as

Insert into Customers (CustomerID, CompanyName, Contactname)

values

(@ID, @Company, @Contact)

Para executá-la:

exec usp_InsereEmpregado 10000, 'THE CLUB', 'THIAGO CAVALHEIRO MONTEBUGNOLI'

Procedure de Deleção  

Excluiremos os registros cujo ID é informado pelo parâmetro de entrada.

create procedure usp_ExcluiCliente

@ID nchar(5)

as

delete from Customers where CustomerID = @ID

Para executá-la:

exec sp_excluicliente 10000

  

Procedure de Atualização

Atualizamos o registro ContactName para Null cujo ID é informado por parâmetro de entrada

create procedure usp_AtualizaCliente

@ID nchar(5)

as

Update Customers set ContactName = null where CustomerID = @ID

Para Executá-la:

exec usp_atualizaCliente 10000

  

Conclusão

Vimos nesta matéria os dois principais tipos de Stored Procedure existente no SQL Server, verificando alguns exemplos práticos de sua utilização. Finalizo esta matéria dizendo que a utilização de Stored Procedures é sempre uma boa idéia.

Sucesso a todos e até a próxima!

Firebird - Conhecendo as Subqueries no Firebird

O que é uma Subquery?

Podemos definir como um comando SELECT dentro de outro comando SELECT, UPDATE, DELETE ou INSERT, é muito utilizado e sua principal finalidade é a de retornar para a Query principal um conjunto de linhas, ou seja, a subquery é executada antes da consulta principal. As Subqueries podem correlacionar tanto com tabelas distintas quanto consigo mesma.

Temos dois tipos de subqueries (subconsultas):

• Subquery que retorna uma única linha. Estas utilizam os operadores = , > , >= , < , <= e <>.

• Subquery que retorna várias linhas. Utiliza o operador IN.

Utilidade

As denominadas Subqueries são muito úteis e essenciais para o trabalho com Bancos de Dados, neste caso estaremos montando e aprendendo a utilizá-la no Firebird.  

Para demonstrar seu poderio iremos criar duas tabelas: uma chamada TBFORNECEDOR e outra TBPRODUTO, teremos um relacionamento 1 -> N entre estas duas entidades. Figura 01,  veja abaixo a instrução SQL:

Figura 01. Relacionamento 1 para N.

CREATE TABLE TBFORNECEDOR (

    COD_FORNECEDOR   INTEGER NOT NULL,

    NOME_FORNECEDOR  VARCHAR(60)

);

CREATE TABLE TBPRODUTO (

    COD_PRODUTO     INTEGER NOT NULL,

    NOME_PRODUTO    VARCHAR(60),

    VALOR           NUMERIC(15,2),

    COD_FORNECEDOR  INTEGER

);

Preencheremos com dados.

COD_FORNECEDOR    NOME_FORNECEDOR

=================     ===================

                                       1     THE CLUB

                                       2     FATEC

        3    ACTIVENET

        4    AVARÉ 

COD_PRODUTO    COD_FORNECEDOR    NOME_PRODUTO    VALOR     DATA

==============    =================    ===============    ======     ========

                               1                                             1      SUPORTE                     80,00         10/10/2007

                               2                                             1      REVISTA                      80,00         08/10/2007

                               3                                             3      INTERNET                   60,00         25/10/2007

                               4                                             3      INSTALAÇÃO             55,00         27/10/2007

Utilizando em SELECT

Recuperando os fornecedores que possuem registros na tabela de produtos.

select *

from TBFORNECEDOR

where COD_FORNECEDOR in (select COD_FORNECEDOR from TBPRODUTO)

Poderíamos também utilizar a cláusula Exists que o resultado seria o mesmo.

select *

from TBFORNECEDOR F

where not exists (select COD_FORNECEDOR from TBPRODUTO P where F.cod_fornecedor=P.cod_fornecedor)

COD_FORNECEDOR    NOME_FORNECEDOR

=================     ===================

                                       1     THE CLUB

                                       3    ACTIVENET

  

Importante: O Operador in ou a cláusula Exists é utilizado quando temos mais de um resultado, caso contrário poderia utilizar o sinal de (=).

Poderíamos facilmente recuperar os registros que não possuem referência na tabela de fornecedor utilizando o not in ou not exists.

select *

from TBFORNECEDOR

where COD_FORNECEDOR not in (select COD_FORNECEDOR from TBPRODUTO)

ou

select *

from TBFORNECEDOR F

where not Exists (select COD_FORNECEDOR from TBPRODUTO P where F.cod_fornecedor=P.cod_fornecedor)

COD_FORNECEDOR    NOME_FORNECEDOR

=================     ===================

2         FATEC

4     AVARÉ

Podermos utilizar a cláusula where em nosso SubSelect, abaixo traremos os fornecedores que possuem os valores dos produtos com valor inferior a 60.

select *

from TBFORNECEDOR

where COD_FORNECEDOR in (select COD_FORNECEDOR from TBPRODUTO where valor <= 60)

COD_FORNECEDOR    NOME_FORNECEDOR

=================     ===================

3         ACTIVENET

Utilizando em UPDATE

Iremos atualizar todos os fornecedores que tiverem o código na tabela de produtos.

update tbfornecedor set nome_fornecedor = 'The Club'

where cod_fornecedor in (select cod_fornecedor from tbproduto)

 Utilizando em DELETE

Deletaremos todos os fornecedores que tiverem o código na tabela de produtos

delete from tbfornecedor

where cod_fornecedor in (select cod_fornecedor from tbproduto)

  

Exemplos de utilização

A seguir iremos simular algumas situações e resolvê-las com as denominadas subqueries.

  

Excluindo registros repetidos

Suponhamos que temos uma tabela com o código repetido, causado por uma falha de integridade referencial, assim teríamos que excluí-los. Para resolver este problema faremos um Delete seguido de um select agrupando pelo código e utilizando a cláusula having para nos trazer apenas os registros repetidos.

Delete from TBPRODUTO

where (COD_FORNECEDOR) in (select COD_FORNECEDOR from TBPRODUTO group by COD_FORNECEDOR having count(*)>1)

  

Trazendo os registros que possuem a maior data

Imaginem que precisamos trazer apenas os registros da tabela de produto que possuem a maior data, inicialmente faremos um select na tabela de produtos, com os campos desejados, em seguida outro trazendo a maior data agrupando pelo código. E por final na query principal, agrupamos pelos campos informados anteriormente.

select cod_fornecedor, nome_produto, data from tbproduto

where data in

(select max(data) from tbproduto group by cod_fornecedor)

group by cod_fornecedor, nome_produto, data

COD_FORNECEDOR    NOME_PRODUTO    DATA

 =================    ===============    =======

                                         1      SUPORTE                  10/10/2007

                                         3      INSTALAÇÃO          27/10/2007

Copiando registros de uma tabela para outra de mesma estrutura

Várias vezes precisamos transferir os registros de uma tabela para outra, para isto temos vários recursos, tais como: utilizar o Delphi, por Stored Procedures, entre outras coisas. Mas a maneira mais fácil e rápida seria utilizando subqueries.

O primeiro passo é fazer um insert na tabela destino, no nosso a produto, informar os campos entre parênteses e depois realizar um select na tabela origem.

Observação: a quantidade e tipo de campos deverá ser a mesma da tabela destino com a de origem.

insert into produto

(cod_produto, nome_produto, valor, cod_fornecedor)

select cod_produto, nome_produto, valor, cod_fornecedor from tbproduto

  

Conclusão

Pudemos aprender um pouco sobre subqueries e perceber a importância delas em nossos sistemas. Verificamos alguns exemplos práticos a fim de poder aprender e colocá-las em prática. Sucesso a todos e até a próxima. 

SQL Server 2008 - Manipulando Expressões no SQL Server

Nesta matéria começarei a abordar algumas das importantes características deste Banco de Dados. É interessante ressaltar que cada Banco de Dados possui suas características particulares, procurarei ser o mais claro e prático possível dando destaque para as principais expressões no SQL Server.

Utilizando Campos Calculados

Esta é uma característica muito utilizada, ou seja, um comando SELECT poderá retornar nas colunas um valor calculado. Por exemplo, suponha que exista uma tabela de produtos e que precisemos retornar seu valor + 25%, ficaria fácil. Veja abaixo a instrução SQL:

SELECT PRODUTO, VALOR, (VALOR*1.25) AS VALORCALCULADO FROM TBPRODUTOS

Poderia ser utilizada com as expressões de adição, subtração, divisão e multiplicação. Veja logo abaixo um exemplo bem simples:

SELECT (VALOR * QUANTIDADE) AS VALORCALCULADO FROM TBPRODUTOS

Multiplicamos o campo valor com o campo quantidade. Este mesmo mecanismo serve para todos os operadores matemáticos.

Utilizando Funções

Estudaremos algumas das principais funções, tais como: funções matemáticas, de caracteres e de Data/Hora.

Utilizaremos a Base de Dados chamada Northwind para eventuais testes. Este Banco de Dados já vem por padrão com o SQL Server.

Funções Matemáticas

Abs

Esta função retorna o valor absoluto de um número.

Exemplo: SELECT ABS(REPORTSTO) FROM EMPLOYEES

Power

Recebe dois parâmetros, o primeiro seria o valor a ser elevado e o segundo a quantidade de vezes que iremos multiplicar este valor. (Figura 01)

Exemplo: SELECT POWER(REPORTSTO,2) FROM EMPLOYEES

Figura 01. Exemplo de utilização da função Power.

Sqrt

Retorna a raiz quadrada de um determinado valor.

Exemplo: select SQRT(UNITSINSTOCK) FROM PRODUCTS

PI

Retornar o valor do PI.

Exemplo: SELECT PI() FROM TABELA

Round

Função para arredondar valores, recebe dois parâmetros, o valor a ser arredondado e a quantidade de casas decimais.

Exemplo: SELECT ROUND(FREIGHT,1) AS VALOR FROM ORDERS

Funções de Caracteres

A concatenação de campos é uma tarefa muito utilizada e simples de ser feita. Utiliza-se o sinal de + para fazer isto, veja abaixo um exemplo:

Exemplo: SELECT (FIRSTNAME + ' ' + LASTNAME) AS "NOME COMPLETO" FROM EMPLOYEES

Teremos um resultado semelhante ao da Figura 02.

Figura 02. Concatenando Campos.

Lower

Recebe como parâmetro uma string e a retorna com caracteres minúsculos.

Exemplo: SELECT LOWER(FIRSTNAME) AS "MINÚSCULAS" FROM EMPLOYEES

Obs: A função UPPER converte para maiúsculas.

Ltrim

Recebe como parâmetro uma string. Esta função tem como objetivo remover os espaços em brancos à esquerda de uma string.

Exemplo: SELECT LTRIM('       THECLUB') FROM TABELA

A Função RTRIM tem a mesma funcionalidade, o único diferencial é que os espaços em brancos removidos são da direita.

Replicate

Recebe como parâmetro uma string e a quantidade de vezes a ser repetida a mesma.

Exemplo: SELECT REPLICATE(FIRSTNAME,2) FROM EMPLOYEES

Substring

Esta é uma função de grande utilidade, retorna pedaços de uma string. Recebe três  parâmetros, sendo: a string, a posição inicial e a quantidade de caracteres a serem mostrados. 

Exemplo: SELECT SUBSTRING(FIRSTNAME,1,5) FROM EMPLOYEES

DataLength

Esta função tem como parâmetro de entrada uma string e de retorno a quantidade de caracteres da mesma.

Exemplo: SELECT DATALENGTH(FIRSTNAME) FROM EMPLOYEES

Funções de Data/Hora

Um detalhe importante é que as funções de data trabalham referenciando unidades de data. As mais comuns são:

- year (ano)
- month (mês)
- Day (dia)

Getdate

A função GETDATE retorna a data e a hora atuais do sistema. Podemos digitar o seguinte comando para obter a data e hora atual:

Exemplo: SELECT GETDATE()

DateAdd

Esta função DATEADD adiciona dias, meses ou anos em uma data. Recebe como primeiro parâmetro a unidade a ser adicionada, o segundo a quantidade e o terceiro o campo do tipo Data. Veja Figura 03.

Exemplo: SELECT DATEADD(YEAR,2, HIREDATE) “DATA” FROM EMPLOYEES

Figura 03. Função Dateadd.

Também poderia ser utilizada dateadd(month,2, hiredate) para adicionar meses a uma data ou dateadd(day,2,hiredate) para adicionar dias.

Datepart

A função DATEPART retorna a parte especificada de uma data como um inteiro. Recebe como parâmetro a unidade e o campo do tipo data.

Exemplos:

SELECT DATEPART(YEAR, HIREDATE) FROM EMPLOYEES

O resultado será o ano do campo hiredate.

                  

SELECT DATEPART(MONTH, HIREDATE) FROM EMPLOYEES

O resultado será o mês do campo hiredate.

                                    

SELECT DATEPART(DAY, HIREDATE) FROM EMPLOYEES        

O resultado será o dia do campo hiredate.

                 

Datediff 

A função DATEDIFF calcula a diferença entre duas datas retornando o resultado como um inteiro. Poderá retornar a diferença entre dias, meses ou anos.

Exemplos:

SELECT DATEDIFF(YEAR,BIRTHDATE,HIREDATE) FROM EMPLOYEES

O resultado será a diferença em anos destas duas datas.

SELECT DATEDIFF(MONTH,BIRTHDATE,HIREDATE) FROM EMPLOYEES

O resultado será a diferença em meses destas duas datas.

SELECT DATEDIFF(DAY,BIRTHDATE,HIREDATE) FROM EMPLOYEES

O resultado será a diferença em dias destas duas datas.

Convertendo dados

A função CONVERT permite converter um tipo de dado para outro. Recebe como

primeiro parâmetro o tipo de dado e o segundo o campo a ser convertido.

Exemplo:

SELECT CONVERT(CHAR,EMPLOYEEID) FROM EMPLOYEES

Neste exemplo irá converter campos do tipo numérico para char.

Conclusão

Procurei demonstrar nesta matéria um pouco das expressões mais utilizadas no SQL Server. O Help do mesmo é muito rico e abrangente, uma dica que deixo aqui seria a de procurar respostas no próprio Help, como sintaxes de funções, exemplos de utilizações entre outras coisas.

Sucesso e até a próxima pessoal!

Delphi - ReportBuilder – Construindo um relatório com quebra de Grupo

Nesta matéria estarei abordando um pouco mais sobre este gerador de relatórios. Montarei um relatório com quebra de grupo utilizando a tabela Department da Base de Dados Employee geralmente encontrado na seguinte pasta:

C:\Arquivos de programas\Firebird\Firebird_1_5\examples

Para iniciarmos a criação de nosso relatório primeiramente abriremos o Delphi 7 para criação de nosso Formulário onde chamaremos o Relatório. Adicionaremos os componentes de conexão e de acesso aos dados. Coloque um SqlConnection e ligue-o ao Database Empoloyee. Insira um SqlDataset e em sua propriedade Sqlconnection coloque como Sqlconnection1, em seguida defina em CommandText a instrução SQL:

SELECT DEPT_NO, PHONE_NO, DEPARTMENT, LOCATION FROM DEPARTMENT

ORDER BY LOCATION

Insira um DatasetProvider e ligue-o ao SqlDataset pela propriedade DataSet e logo em seguida coloque um ClientDataset alterando sua propriedade ProviderName para DataSetProvider1. Coloque um DataSource e através da propriedade DataSet deixe como ClientDataSet1.

Insira em seu formulário os seguintes componentes do ReportBuilder: ppDBPipeline e ppReport e configure suas propriedades.

ppDBPipeline

DataSource = Datasource1

Name = ppDBDepartment

PPReport

DataPipeline = ppDBDepartment

DeviceType = Screen

Name = ppGrupo

PreviewFormSettings - WindowState = wsMaximized (teremos o relatório Maximizado)

                                   - ZoomSetting = zs100Percent (zoom de 100%)

Depois de configurado estes componentes Insira um Dbgrid para visualizarmos os registros e um Botão para chamarmos o relatório. O seu formulário deverá ficar parecido com o da Figura 01.

Figura 01. Formulário para chamar o relatório.  

Coloque o código abaixo no botão Visualizar, para assim podermos chamarmos nosso relatório.

procedure TForm1.BitBtn1Click(Sender: TObject);

begin

  ppGrupo.Print;

end;

Montando o Relatório

Dê um duplo clique sobre o componente ppGrupo para montarmos o relatório. Percebam que por padrão o ReportBuilder vem com as bandas Header, Detail e Footer, portanto iremos adicionar mais algumas, para isto clique no menu Report e adicione uma banda Title e logo em seguida uma Group. (veja Figura 02)

Figura 02. Adicionando uma banda do tipo Group.

No combobox Groups defina como DB.Department.LOCATION em seguida clique em Add. Pronto, criamos uma Banda do tipo Group Header e outra do tipo group Footer sendo agrupadas pelo campo LOCATION.

Na banda Title coloque um componente Label e outro Shape com as seguintes configurações:

Label1

Appearance - Color = clSilver

Text - Caption = Agrupamento por Localização

Font – Arial, Preto e tamanho 16

Shape1

Appearance - Brush - Color = clSilver

                    - Pen – Color = clSilver

                    - Shape = stRectangle

Na banda Header coloque 3 componentes Labels e um chamado Line.

Label2

Text - Caption = Dept_No

        - Font = Arial, Negrito e tamanho 10

Label3

Text - Caption = Department

        - Font = Arial, Negrito e tamanho 10

Label4

Text - Caption = Phone_No

        - Font = Arial, Negrito e tamanho 10

Line1

Appearance - Pen - Color = clSilver

Na Banda Group Header coloque um Dbtext, para podermos agrupar nosso relatório por LOCATION.

Dbtext1

Data - DataField = LOCATION

        - DataPipeline = DBDepartment

Text - Font = Arial, Negrito , Vermelho, Sublinhado e tamanho 10

Na banda Detail insira mais 3 Dbtexts.

Dbtext2

Data - DataField = DEPT_NO

        - DataPipeline = DBDepartment

Dbtext3

Data - DataField = DEPARTMENT

        - DataPipeline = DBDepartment

Dbtext4

Data - DataField = PHONE_NO

        - DataPipeline = DBDepartment

Na Group Footer, coloque um Label e um DbCalc.

Label5

Text - Caption = Total por Grupo:

Text - Font = Arial, Negrito , Vermelho, Sublinhado e tamanho 10

DbCalc

Calculations - DbCalcType = dcCount

Text - Font = Arial, Negrito , Vermelho, Sublinhado e tamanho 10

E por final colocaremos na banda Footer dois componentes do tipo SystemVariable e um Shape, para darmos um visual bacana em nosso relatório.

SystemVariable1

Appearance - Color = clSilver

                    - VarType = vtDateTime

Text - Font = Arial, Negrito e tamanho 10

SystemVariable2

Appearance - Color = clSilver

                    - VarType = vtPageSetDesc

Text - Font = Arial, Negrito e tamanho 10

Shape2

Appearance - Brush - Color = clSilver

                    - Pen – Color = clSilver

                    - Shape = stRectangle

Após configurar todos estes componentes, seu relatório deve estar parecido com o da  Figura 03.

Figura 03. Relatório Pronto.

           

Em seguida dê um Save All e execute o projeto. A Figura 04 mostrará nosso relatório em run-time.

Figura 04. Relatório em Run-time.

Conclusão

Nesta matéria vimos a facilidade para montar relatórios no ReportBuider, puderam perceber que com pouco esforço montamos um relatório com quebra de grupo. Espero que os senhores tenham gostado. Sucesso a todos e até a próxima.

Delphi - ReportBuilder – Conhecendo os Recursos deste Gerador de Relatórios

Nesta matéria iremos conhecer este poderoso gerador de relatório denominado ReportBuilder. Ele se diferencia da forma de trabalhar dos outros geradores de relatório como o Quick Report, ou até mesmo o Fortes Report. Vale salientar a imensa quantidade de recursos e a facilidade de construir relatórios possuindo algumas características importantes que vale salientar, como:

A fácil instalação e manuseio dos componentes, sua utilização difere muito do Quick Report, ou seja não precisa de muito adaptação do programador.

Procurarei abordar os principais componentes e demonstrar seu poderio seguido de uns exemplos bem simples de sua utilização.

Entendendo o Funcionamento

Observe na Figura 01 o esquema de como é o funcionamento do ReportBuilder.

Figura 01. Equação do ReportBuilder.

Em seguida irei dar uma breve explicação da dita “Equação do ReportBuilder”.

Data Access: Acesso aos dados, seria a recuperação dos dados de uma base de dados qualquer, seja por tabelas ou outra série de dados de forma organizada.

Data Process: Processamento dos dados, camada intermediária para o processamento dos dados.

Report Layout: Layout do relatório, é a disposição e o comportamento dos componentes dentro do relatório.

Report Output: Saída do relatório, ou seja o resultado final para o usuário.

A instalação

Infelizmente ele não é gratuito, mas possui uma versão trial no seguinte site http://www.digital-metaphors.com/ que iremos desfrutá-lo neste artigo. Utilizaremos a versão Server ( a mais completa), para isto entre no site acima e através da aba Download escolha “Server 10.06 for Delphi 7” e em seguida baixe o seu instalador. A instalação é bem simples, feche o Delphi 7 e dê um duplo clique em seu arquivo de instalação. Leia atentamente sua Licença (lembrando que estamos utilizando uma versão demo) e em seguida clique em next. Aparecerá algumas janelas para escolher o diretório de instalação, entre outras configurações. Instale tudo como padrão.

Conhecendo os componentes

Abra o Delphi 7 e localize a paleta chamada RBuilder, veja a Figura 02.

Figura 02. Paleta de componentes.

Irei dar uma breve explicação dos principais componentes encontrados nesta paleta.

ppDBPipeline: Utilizado para acesso aos dados. Componente compatível com Tdataset. O ppDBPipeline é conectado através da propriedade Datasource.

ppBDEPipeline: Utilizado especificamente para acesso aos dados utilizando o BDE. As características são as mesmas no descrito acima.

ppTextPipeline: Utilizado para acessar arquivos textos. As características são parecidas com os descritos acima.

ppJITPipeline: Quando ligado a propriedade DataPipeline de qualquer componente de relatório Data-Aware, esse componente pode ser usado para acessar dados armazenados em objetos do Delphi.

ppReport: O principal componente do ReportBuilder. Ao clicar com o botão direito escolhendo Report Designer... é possível verificar o layout do relatório.

ppViewer: É um objeto pouco utilizado, útil para que você possa substituir o formulário padrão utilizado pelo ReportBuilder para visualização e impressão de relatórios por formulários criados.

ppArchiveReader: Componente utilizado para ler relatórios salvos com extensão (.raf)

ppDBArchiveReader: Componente utilizado para manipular relatórios salvos em tabelas do Banco de Dados.

ppDesigner: Utilizado para que os usuários finais possam customizar seus relatórios junto com sua aplicação.

ppReportExplorer: Permite que os usuários finais salve e organize seus relatórios em uma estrutura de diretórios.

ppDataDictionary: É possível controlar as tabelas e campos que o usuário pode ver, como também fornece relacionamentos default entre tabelas.

Neste momento iremos conhecer alguns dos principais recursos deste gerador de relatórios para adiante montarmos um exemplo simples. Para isto abra o Delphi 7 e coloque no formulário um componente chamado ppReport e em seguida dê um duplo clique. Aparecerá uma mensagem indicando que se trata de uma cópia Demo e mais algumas informações relevantes para poder obter o produto completo. Veja a Figura 03 o layout.

Figura 03. Design do ReportBuilder.

Logo abaixo irei explicar e apresentar alguns dos principais recursos desta ferramenta.

Figura 04. Barra de Ferramentas.

A barra de ferramentas é dividida em três segmentos:

Na parte esquerda da barra encontram-se os componentes de interface, na região central os componentes ditos Data-Awares e na direita os relacionados aos tipos de bandas.

 Label, Memo e Richtext: A mesma funcionalidade dos componentes da própria IDE do Delphi.

System Variable: Recursos do sistema. Interessante, permite colocar a data atual, data e hora atual, nome do documento, número de páginas entre outras coisas.

Poderá  ser alterado através da propriedade VarType e seu formato é controlado pela propriedade DisplayFormat, veja Figura 05 abaixo:

Figura 05. System Variable.

Shape: Permite colocar figuras geométricas no relatório como (quadrados, retângulos, círculos, elipses).

Lines: Inserir linhas, útil para fazer divisões entre os campos ou entre as bandas do relatório.

Barcode e 2DBarcode: Código de barras, recurso muito interessante em se tratando de geradores de relatórios, um exemplo disto é o Quick Report, é necessário componentes de terceiros para obter este recurso.

CheckBox: Possibilidade da utilização do checkBox dentro do relatório.

Chart: Criação de gráficos.

Os recursos apresentados na região central da barra de tarefas possuem quase a mesma finalidade, a diferença é que eles têm vínculo ao Banco de Dados.

Region: Define uma área especifica do relatório para ser controlada.

Subreport: Utilizado para criar relatórios do tipo mestre-detalhe, possibilitando criar várias abas dividindo-o em várias partes. A propriedade PrintBehavior determina o tipo de banda a ser criada.

Page Break: Como o próprio nome diz, serve para fazer quebra de páginas.

Crosstab: Componente útil para fazer relatórios com referência cruzada.

É importante ressaltar que o ReportBuilder possui Eventos para serem utilizados nas bandas, mas especificamente nesta matéria não utilizaremos nenhum evento.

Montando um projeto de exemplo

Depois de aprendermos e conhecermos um pouquinho sobre os recursos, iremos a partir daqui montar um exemplo simples de relatório do tipo Mestre-detalhe.

Abra o Delphi 7 vá em File/New/Application e adicione no formulário um componente SqlConnection. Vale ressaltar que estaremos utilizando o Firebird 1.54 e seu Banco de Dados padrão (EMPLOYEE.FDB), para quem não sabe ele encontra-se na seguinte pasta C:\Arquivos de programas\Firebird\Firebird_1_5\examples\EMPLOYEE.FDB.

Pegue um SqlDataset altere seu nome para SqlDepartment e ligue-o ao Sqlconnection através de sua propriedade Sqlconnection.

Em sua propriedade CommandText iremos colocar a instrução SQL para obter os campos da tabela Department. Veja abaixo:

SELECT DEPT_NO, DEPARTMENT, LOCATION FROM DEPARTMENT

Neste momento colocaremos um componente Datasetprovider e ligaremos a propriedade Dataset ao SqlDepartment e em seguida coloque um Clientdataset, nomeie para ClientDepartment e coloque em sua propriedade Providername como Datasetprovider1.

Coloque mais um componente Sqldataset e o nomeie para SqlEmployee, ligue sua propriedade Sqlconnection para Sqlconnection1 e em sua propriedade CommandText coloque o seguinte código SQL:

SELECT FIRST_NAME, LAST_NAME, HIRE_DATE, DEPT_NO FROM EMPLOYEE WHERE DEPT_NO = :DEPT_NO

Em seguida deveremos ligar este SqlDepartment com o SqlEmployee e será feito isto através de um datasource. Coloque no formulário um componente Datasource e dê o nome de DSDepEmp. Em sua propriedade dataset coloque como SqlDepartment, no SqlEmployee coloque a propriedade Datasource para DSDepEmp. Coloque mais um componente Clientdataset e altere seu nome para ClientEmployee.

Adicione os Fields tanto nos componentes SqlDatasets quanto nos ClientDatasets. Não esqueça de setar a propriedade DataSetField do ClientEmployee para ClientDepartmentSQLEmployee. Veja a Figura 06 como está ficando nosso formulário. 

Figura 06. Mestre Detalhe.

Criamos apenas um relacionamento entre estas duas tabelas. Adicione mais dois Datasources os renomeie para DSDepartment e DSEmployee e ligue-os aos respectivos Clientdatasets.

Agora iremos utilizar os componentes do ReportBuilder. Coloque um componente ppDBPipeline (utilizado para acesso aos dados) e o nomeie para ppDBDepartment e coloque na propriedade Datasource para DSDepartment. Após isto coloque mais um componente ppDBPipeline e o nomeie para ppDBEmployee ligando ao Datasource DSEmployee e a propriedade MasterDataPipeline deixe como ppDBDepartment (seria a tabela mestre).

Por último coloque um componente ppReport e ligue sua propriedade Datapipeline para ppDBDepartment.

Veja na Figura 07 como está ficando nosso exemplo.

Figura 07. Mestre Detalhe

Para visualizarmos os dados insira nos formulários dois Dbgrids e altere suas propriedades Datasource respectivamente para DSDepartment e  DSEmployee e finalmente iremos inserir uma botão para chamar nosso relatório. Veja abaixo o código e a Figura 08.

procedure TForm1.BitBtn1Click(Sender: TObject);

begin

  ppReport1.Print;

end;

Figura 08. Configuração dos Componentes.

Montando o relatório

Dê um duplo clique sobre o componente ppReport para abrir o Report Designer. Por padrão ele vem com as bandas Header, Detail e Footer. Adicione na banda Header um componente Label com o nome de “Exemplo – Mestre Detalhe” e um System variable para podermos pegar a data atual. Em seguida na banda Detail coloque mais 2 Labels e 2 Dbtext para pegar os seguintes campos da tabela Department: Dept_No e Department, nomeie os Labels e ligue os Dbtexts com respectivos campos. Veja abaixo a Figura 09 como está ficando nosso relatório.

Figura 09. Montando o relatório.

Adicione na banda Detail um componente Subreport e ligue-o à tabela Employee. Percebam que foi criado uma aba na região inferior da tela chamada Subreport1:Dbemployee, é através desta aba que iremos configurar a outra parte do nosso relatório. Clique nesta aba e coloque mais 3 Labels  e 3 Dbtexts, com os seguintes nomes First_Name, Last_Name e Hiredate e ligandos os Dbtexts aos seguintes campos: Fisrt_name, Last_Name e Hiredate respectivamente.

Para completar nosso exemplo insira um componente System Variable, altere sua propriedade vartype para vtPageSetDesc (serve para ter uma contagem do tipo: página atual por total de páginas) e o coloque no formulário principal na banda Footer.

Rode nosso exemplo, clique no botão Preview e teremos como resultado nosso relatório parecido com a Figura 10.

Figura 10. Exemplo Pronto.

Conclusão

Montamos nesta matéria sem muito esforço, um exemplo simples com este gerador de relatórios. Procurei explorar e demonstrar poucos dos muitos recursos encontrados nele. Espero que tenham gostado e até a próxima.