Desenvolvimento Código Aberto

A linguagem de banco de dados SQL permite que você retorne um resultado de dados completo de uma única vez, mas as vezes precisamos manipular este resultado em tempo de execução, uma linha de cada vez, deste modo podemos utilizar um recurso chamado Cursor para realizar este procedimento.

Basicamente todos os bancos de dados possuem este recurso, entretanto cada um deles possui também suas peculiaridades e funcionalidades diferenciadas, mas no geral funcionam da seguinte maneira: a instrução DECLARE CURSOR define os atributos de um cursor do servidor SQL, como o seu comportamento de rolagem e a consulta usada para construir o conjunto de resultados no qual o cursor funciona. A instrução OPEN popula o conjunto de resultados e FETCH retorna uma linha do conjunto de resultados. A instrução CLOSE libera o conjunto de resultados atual associado ao cursor. A declaração DEALLOCATE libera os recursos usados ​​pelo cursor.

Preparando o Banco de dados

Utilizaremos uma modelagem básica contendo três tabelas básicas sem chave primaria, sem chave estrangeira, permitido assim que nosso exemplo possua dados duplicados,  basicamente utilizaremos apenas a logica relacional para brincar com os dados utilizando uma Stored procedure contendo um cursor.

Relacionamentos

A tabela de FUNCIONARIOS contem dados de cadastro e o salario de cada funcionário, a tabela DESCONTO possui os valores de porcentagens de desconto para cada funcionário, e a tabela SALARIO contem os lançamentos de salários e deve ser alimentada mês a mês para cada funcionário.  Utilize os Scripts abaixo para criar as tabelas e os dados iniciais.

 

Algo Extremamente Útil  Sobre Cursores

Cursores são mais rápidos do que os looping efetuados dentro da linguagem de programação de sua preferencia, funcionam basicamente da mesma maneira, porem residem no motor do banco de dados dentro de uma Stored Procedure.

Os Cursores utilizam memoria e também necessitam de um laço para rolar registro a registro do resultado de dados e manipula-lo, é de extrema importância que você utilize cursores somente quando necessário. Caso queira que seu programa tenha uma ótima performance, antes de optar por um cursor é necessário saber que os motores dos bancos de dados são projetados para manipular dados em massa, manipulação de dados registro a registro devem ser utilizados somente quando não houver possibilidade de usar instruções SQL, para ilustrar esta explicação vamos escrever um script SQL que executa o mesmo procedimento do cursor utilizando apenas um acesso ao banco de dados, você encontra este script logo após o código Java.

Exemplo:

Neste exemplo criamos um cursor que reside dentro de uma Stored Procedure, calcula o desconto do salario dos funcionários e insere os valores líquidos em uma tabela de lançamento, após efetuar o procedimento que seria mensal, uma query exibe o relatório em uma grade de dados.

Cursor

O cursor seleciona dados da tabela de FUNCIONARIOS e da tabela de DESCONTO, através de um looping, alimenta os dados relevantes dentro de variáveis e insere os lançamentos na tabela de SALARIO efetuando o calculo do desconto.

SQL

Oracle

create table Funcionarios
(     

    ID_Funcionario  NUMBER(5),
    Nome            VARCHAR2(30),
    Sobrenome       VARCHAR2(70),
    Cargo           VARCHAR2(30),
    Salario         NUMBER(9,2)

);

Insert into FUNCIONARIOS values (1,'Steve','Gates','Programador',2550.56);
Insert into FUNCIONARIOS values (2,'Bill','Jobs','Diretor',5143.71);

-- Cria tabela com a porcentagem de descontos
Create table DESCONTO (
  ID_FUNCIONARIO NUMBER,
  PORCENTAGEM NUMBER(9,2));

-- Insere porcentagem por  funcionario
Insert Into DESCONTO Values (1, 5 );
Insert Into DESCONTO Values (2, 8 );

-- Cria tabela de lançamentos de descontos
Create table SALARIO (
  ID_FUNCIONARIO NUMBER,
  DATA_LANC  DATE,
  VDESCONTO NUMBER(9,2));

-- Lista lançamentos por funcionario
select * from salario;

-- Desenvolvimento Aberto - Cursor explicito
create or replace Procedure CalculoDesconto  is
-- Declara cursor
Cursor calculo is
  Select A.ID_FUNCIONARIO, A.SALARIO, B.PORCENTAGEM
  from FUNCIONARIOS A, DESCONTO B
  Where
  A.ID_FUNCIONARIO = B.ID_FUNCIONARIO;

  -- Declara variáveis
  pID NUMBER;
  pSalario NUMBER(9,2);
  pPorcentagem NUMBER(9,2);

  -- Abre cursor
  begin
    open calculo;
      -- Cria laço
      loop
        -- Alimenta variáveis
        fetch calculo into pID, pSalario, pPorcentagem;
        EXIT WHEN calculo%NOTFOUND;

        -- Insere valores da tabela
        Insert into SALARIO
        values  (
                 pID,
                 SYSDATE,
                 (pSalario * pPorcentagem)/100);

      end loop;
    -- Fecha cursor
    close calculo;  

  end;

DB2

create table Funcionarios
(     

    ID_Funcionario  INTEGER,
    Nome            VARCHAR(30),
    Sobrenome       VARCHAR(70),
    Cargo           VARCHAR(30),
    Salario         NUMERIC(9,2)

);

Insert into FUNCIONARIOS values (1,'Steve','Gates','Programador',2550.56);
Insert into FUNCIONARIOS values (2,'Bill','Jobs','Diretor',5143.71);

-- Cria tabela com a porcentagem de descontos
Create table DESCONTO (
  ID_FUNCIONARIO INTEGER,
  PORCENTAGEM DECIMAL(9,2));

-- Insere porcentagem por  funcionario
Insert Into DESCONTO Values (1, 5 );
Insert Into DESCONTO Values (2, 8 );

-- Cria tabela de lançamentos de descontos
Create table SALARIO (
  ID_FUNCIONARIO INTEGER,
  DATA_LANC  DATE,
  VDESCONTO DECIMAL(9,2));

-- Lista lançamentos por funcionario
select * from desconto;

-- Desencolcimento Aberto - Cursor explicito
CREATE PROCEDURE calculodesconto ()
	DYNAMIC RESULT SETS 1	

P1: BEGIN

	-- Declara variáveis
	 DECLARE pID INTEGER;
	 DECLARE pSalario DECIMAL(9,2);
	 DECLARE pPorcentagem DECIMAL(9,2);
	 DECLARE eof SMALLINT DEFAULT 0;

	-- Declara cursor
	DECLARE calculo CURSOR WITH RETURN for
	Select A.ID_FUNCIONARIO, A.SALARIO, B.PORCENTAGEM
    from FUNCIONARIOS A, DESCONTO B
    Where
    A.ID_FUNCIONARIO = B.ID_FUNCIONARIO;

   -- Declara handler para final de arquivo
   DECLARE CONTINUE HANDLER FOR NOT FOUND SET eof = 1;

	-- Abre cursor
	OPEN calculo;

	-- Cria label e executa looping
	fim:
	LOOP
	  -- Alimenta valores nas variáveis
	  FETCH calculo into pID, pSalario, pPorcentagem;
	  	  IF eof <> 0 THEN LEAVE fim;
	      END IF;	  

	      -- Insere dados na tabela
	      Insert into SALARIO values (
	        pID,
	        Current date,
	        (pSalario * pPorcentagem)/100);

	END LOOP fim;
	CLOSE calculo;
END P1

Mssql

create table Funcionarios
(     

    ID_Funcionario  Int,
    Nome            VARCHAR(30),
    Sobrenome       VARCHAR(70),
    Cargo           VARCHAR(30),
    Salario         Decimal(9,2)

);

Insert into FUNCIONARIOS values (1,'Steve','Gates','Programador',2550.56);
Insert into FUNCIONARIOS values (2,'Bill','Jobs','Diretor',5143.71);

-- Cria tabela com a porcentagem de descontos
Create table DESCONTO (
  ID_FUNCIONARIO INT,
  PORCENTAGEM DECIMAL(9,2));

-- Insere porcentagem por  funcionario
Insert Into DESCONTO Values (1, 5 );
Insert Into DESCONTO Values (2, 8 );

-- Cria tabela de lançamentos de descontos
Create table SALARIO (
  ID_FUNCIONARIO INT,
  DATA_LANC  DATE,
  VDESCONTO DECIMAL(9,2));

-- Lista lançamentos por funcionario
select * from salario;

-- Desenvolvimento Aberto - cursor explicito
Create Procedure CALCULODESCONTO
AS
BEGIN
-- Declara Variáveis
DECLARE @pID INT,
        @pSalario DECIMAL(9,2),
		@pPorcentagem DECIMAL(9,2);

-- Declara cursor
DECLARE calculo CURSOR FOR
   Select A.ID_FUNCIONARIO, A.SALARIO, B.PORCENTAGEM
   from FUNCIONARIOS A,   DESCONTO B
   Where
   A.ID_FUNCIONARIO = B.ID_FUNCIONARIO;

   -- Abre cursor
   Open calculo;

   -- Alimenta -
   FETCH NEXT FROM calculo INTO @pID, @pSalario, @pPorcentagem;

   While @@FETCH_STATUS = 0
   BEGIN
      Insert into SALARIO values (
	        @pID,
	        GETDATE(),
	        (@pSalario * @pPorcentagem)/100);

      FETCH NEXT FROM calculo INTO @pID, @pSalario, @pPorcentagem;
   END

   -- Fecha conteudo do cursor
   Close calculo;
   -- Desaloca cursor da memória
   Deallocate calculo;
END

Java

import java.awt.Container;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.awt.event.ItemEvent;
import java.awt.event.ItemListener;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;

import javax.swing.BorderFactory;
import javax.swing.Box;
import javax.swing.BoxLayout;
import javax.swing.ButtonGroup;
import javax.swing.JButton;
import javax.swing.JFrame;
import javax.swing.JPanel;
import javax.swing.JRadioButton;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.table.DefaultTableModel;

public class Cursor implements ActionListener, ItemListener {
	// Cria componentes
	private JTable tabela;
	private JRadioButton banco1;
	private JRadioButton banco2;
	private JRadioButton banco3;
	private JButton botao;

	// Declara objetos de conexão
	private static Connection conn;
	private static Statement query;
	private static String bancodedados;

	public void conectar(String login, String senha) {

		// Verifica strings de conexão

		// ORACLE
		if (bancodedados == "oracle") {
			try {
				// Define Driver de conexão JDBC thin
				Class.forName("oracle.jdbc.driver.OracleDriver");
				conn = DriverManager.getConnection(
						"jdbc:oracle:thin:@localhost:1521:xe", login, senha);

				// Executa pedido SQL
				query = conn.createStatement();

			}

			catch (ClassNotFoundException ex) {
				ex.printStackTrace();
			}

			catch (SQLException ex) {
				ex.printStackTrace();
			}
		}

		// DB2
		if (bancodedados == "db2") {
			try {
				// Define Driver de conexão JDBC
				Class.forName("com.ibm.db2.jcc.DB2Driver");
				conn = DriverManager.getConnection(
						"jdbc:derby:net://localhost:50000/deva", login, senha);

				// Executa pedido SQL
				query = conn.createStatement();
				// JOptionPane.showMessageDialog(menssagem,
				// "Conexão Efetuada com sucesso!");
			}

			catch (ClassNotFoundException ex) {
				ex.printStackTrace();
			}

			catch (SQLException ex) {
				// JOptionPane.showMessageDialog(menssagem, "Erro na conexão!");
				ex.printStackTrace();
			}

		}

		// MICROSOFT SQL SERVER
		if (bancodedados == "mssql") {
			try {
				// Define Driver de conexão JDBC
				String URL = "jdbc:sqlserver://localhost\\SQLEXPRESS:1433;databaseName=devaberto"
						+ ";user=" + login + ";password=" + senha;

				Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
				conn = DriverManager.getConnection(URL);

				// Executa pedido SQL
				query = conn.createStatement();

			}

			catch (ClassNotFoundException ex) {
				ex.printStackTrace();
			}

			catch (SQLException ex) {
				ex.printStackTrace();
			}

		}

	}

	public void executaProcedure() throws SQLException {
		// Cria nova instrução SQL
		CallableStatement proc = null;

		proc = conn.prepareCall("{call CalculoDesconto()}");
		// Executa stored procedure
		proc.execute();

	}

	public ResultSet retornaTabela() throws SQLException {
		// Cria nova instrução SQL
		Statement query;
		query = conn.createStatement();
		String sql;

		// Verfica banco de dados e passa script SQL
		sql = "Select A.ID_FUNCIONARIO, " + "A.NOME, " + " A.CARGO, "
				+ " A.SALARIO, " + "B.PORCENTAGEM, " + "C.VDESCONTO, "
				+ "C.DATA_LANC,  " + "A.SALARIO - C.VDESCONTO AS SLIQUIDO "
				+ "from FUNCIONARIOS A, DESCONTO B, SALARIO C " + "Where "
				+ "A.ID_FUNCIONARIO = B.ID_FUNCIONARIO  AND "
				+ "A.ID_FUNCIONARIO = C.ID_FUNCIONARIO";

		// Executa Script
		ResultSet dados = query.executeQuery(sql);

		// Retorna set de dados
		return dados;
	}

	// Modelo
	public static DefaultTableModel criaTableModel(ResultSet rs)
			throws SQLException {

		// Cria um modelo de tabela
		ResultSetMetaData metaData = rs.getMetaData();

		// Retorna as colunas
		Vector<String> colunas = new Vector<String>();

		int columnCount = metaData.getColumnCount();

		for (int column = 1; column <= columnCount; column++) {
			colunas.add(metaData.getColumnName(column));
		}

		// Retorna dados
		Vector<Vector<Object>> dados = new Vector<Vector<Object>>();

		while (rs.next()) {
			Vector<Object> vector = new Vector<Object>();

			for (int columnIndex = 1; columnIndex <= columnCount; columnIndex++) {
				vector.add(rs.getObject(columnIndex));
			}
			dados.add(vector);
		}

		return new DefaultTableModel(dados, colunas);

	}

	public void itemStateChanged(ItemEvent arg0) {

		// Verifica item banco de dados selecionado
		Object fonte = arg0.getItemSelectable();
		int estado = arg0.getStateChange();

		if (estado == arg0.SELECTED) {

			if (fonte == banco1) {
				bancodedados = "oracle";
			}

			if (fonte == banco2) {
				bancodedados = "db2";
			}

			if (fonte == banco3) {
				bancodedados = "mssql";
			}

		}

	}

	public void actionPerformed(ActionEvent arg0) {
		// Efetua login no banco de dados
		Cursor acesso = new Cursor();

		if (bancodedados == "oracle") {
			acesso.conectar("daberto", "p@55w0rd");
		}

		if (bancodedados == "db2") {
			acesso.conectar("db2admin", "p@55w0rd");
		}

		if (bancodedados == "mssql") {
			acesso.conectar("devaberto", "p@55w0rd");
		}

		try {
			// Executa Stored Procedure
			acesso.executaProcedure();
			// Executa Intrução SQL
			tabela.setModel(criaTableModel(acesso.retornaTabela()));
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

	public Container criaPainel() throws SQLException {
		// Cria painel principal
		JPanel painel = new JPanel();

		// Seleciona layout
		painel.setLayout(new BoxLayout(painel, BoxLayout.PAGE_AXIS));
		painel.setBorder(BorderFactory.createEmptyBorder(20, 20, 20, 20));

		// Cria painel de escolha de conexão
		JPanel pescolha = new JPanel();

		pescolha.setLayout(new BoxLayout(pescolha, BoxLayout.LINE_AXIS));
		pescolha.setBorder(BorderFactory.createEmptyBorder(10, 10, 10, 10));

		ButtonGroup grupo = new ButtonGroup();

		// Cria componentes de radio
		banco1 = new JRadioButton("Oracle");
		banco2 = new JRadioButton("IBM DB2");
		banco3 = new JRadioButton("Microsoft SQL");

		// Agrupa botões de radio
		grupo.add(banco1);
		grupo.add(banco2);
		grupo.add(banco3);

		// Cria ouvinte dos botões
		banco1.addItemListener(this);
		banco2.addItemListener(this);
		banco3.addItemListener(this);

		// Seleciona primeira conexão
		banco1.setSelected(true);

		// Adiciona botões ao painel
		pescolha.add(banco1);
		pescolha.add(banco2);
		pescolha.add(banco3);

		// Efetua primeira conexão ao banco (ORACLE)
		Cursor acesso = new Cursor();

		acesso.conectar("daberto", "p@55w0rd");

		// Cria modelo de tabela
		tabela = new JTable(criaTableModel(acesso.retornaTabela()));

		// Adiciona um painel de rolagem
		JScrollPane rolar = new JScrollPane(tabela);

		// Cria painel do botão
		JPanel pbotao = new JPanel();

		// Seleciona layout
		pbotao.setLayout(new BoxLayout(pbotao, BoxLayout.PAGE_AXIS));

		// cria botão
		botao = new JButton("Executar Stored Procedure");
		botao.addActionListener(this);

		// Adiciona botão ao painel
		pbotao.add(botao);

		// componentes ao painel principal
		painel.add(pescolha);
		painel.add(rolar);
		painel.add(Box.createVerticalStrut(10));
		painel.add(botao);

		return painel;
	}

	public static void criaGUI() {
		// Cria formulario
		JFrame formulario = new JFrame("Desenvolvimento Aberto - Cursor");
		formulario.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);

		// cria painel de conteudo
		Cursor acesso = new Cursor();

		try {
			formulario.setContentPane(acesso.criaPainel());
		}

		catch (SQLException e) {
			e.printStackTrace();
		}

		// Exibe o formulario
		formulario.setSize(700, 300);
		formulario.setVisible(true);
	}

	public static void main(String[] args) {
		javax.swing.SwingUtilities.invokeLater(new Runnable() {

			@Override
			public void run() {

				// Cria e mostra a GUI
				criaGUI();

			}
		});
	}
}

SQL – A instrução a seguir, substitui o cursor em um único acesso. (Oracle)

-- Este script SQL efetua o mesmo procedimento do cursor
-- utilizando apenas um acesso ao banco de dados.
-- é importante projetar seu acesso a dados para obter alta performance
-- você deve utilizar cursores apenas quando realmente for necessário.
--
-- Para os outros bancos substitua o campo SYSDATE
-- Para DB2 use Current date
-- Para MSSQL use GETDATE() 

Insert into SALARIO
Select A.ID_FUNCIONARIO,SYSDATE, ((A.SALARIO * B.PORCENTAGEM)/100)
  from FUNCIONARIOS A, DESCONTO B
  Where
  A.ID_FUNCIONARIO = B.ID_FUNCIONARIO;