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.
Cursor – SQL
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.
Cursor – IBM Data Studio
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;
Ricardo Mantovani
Desenvolvimento Código Aberto 