Procedures - Ajuda

[Julia]

Oi pessoal eu sou nova com banco de dados e preciso de ajuda na criaçao de procedures... eu simplesmente não sei como utiliza-las e tenho o seguinte trabalho para a faculdade

Criar quatro (4) procedimentos, duas (2) funções e quatro (4) triggers, onde:

1. Não serão consideradas rotinas com objetivo exclusivo de incluir, alterar ou excluir dados em uma única tabela, ou seja, rotinas cujo propósito podem ser implementadas através de um único comando SQL;
2. Quando houver necessidade, deverão ser tratadas todas as exceções que a rotina pode gerar;
3. Criar, no mínimo, duas rotinas que utilizem cursores;

agradeço desde já a aqueles que puderem me ajudar bjus

[Trevisolli]

Júlia, bom dia.

Você tentou dar uma procurada aqui no fórum, por estes tópicos (Trigger, Procedure ou Function) ?

Com certeza irá achar exemplo destes objetos prontos.

Qualquer dúvida, manda pra gente.

[Tineks]

Olá Julia, tudo bom?

Como o Trevisolli disse, aqui no fórum existe muitos exemplos relacionado a procedures, functions, triggers.. porem você precisa ter definido o que você precisa montar, qual será a finalidade delas. você vai simular o q? um sistema comercial, farmacia, locadora? o primeiro passo é ter esse definição em mãos, ai sim você poderá partir pro desenvolvimento.

Bem, você disse q não será considerado rotinas com o intuito de incluir, excluir... já que isso não será permitido você vai precisar pensar em algumas regras pra suas procedures, functions, por exemplo, imagine um sistema pra locadora, você poderia criar uma função que retornaria a disponibilidade de um filme, ou uma procedure que pesquisaria todos os clientes com filmes atrasado, e por ai vai...

qualquer dúvida é só falar.!!

[]'s

[Julia]

é o seguinte... eu possuo as seguintes tabelas!

Selecionar tudo

create table TipoProduto(
codTipoProduto number(3) not null,
descTipoProduto varchar(30) not null,
Constraint codTipoProduto primary key (codTipoProduto)
);

create table TipoSubProduto(
codTipoProduto number(3) not null,
codTipoSubProduto number(3) not null,
descTipoSubProduto varchar(40) not null,
Constraint codTipoSubProduto primary key (codTipoSubProduto,codTipoProduto),
Constraint codTipoProduto_fk1 foreign key (codTipoProduto)references TipoProduto(codTipoProduto)  
);


create table Produto(
codProduto number(3) not null,
nomeProduto varchar(40) not null,
statusProduto varchar(40) not null,
descricaoProduto varchar(40) not null,
codTipoProduto number(3) not null,
codTipoSubProduto number(3) not null,

Constraint codProduto_pk primary key (codProduto),
Constraint Produto_fk foreign key (codTipoProduto, codTipoSubProduto) references tipoSubProduto(codTipoProduto, codTipoSubProduto)  
);


create table Venda(
codVenda number(3) not null,
nomeComprador varchar(40) not null,
valorTotal number(3) not null,

Constraint Venda_pk primary key (codVenda)
);


create table Compra(
codCompra number(3) not null,
dataCompra date not null,
nomeFornecedor varchar(40) not null,
valorTotal number(3) not null,

Constraint Compra_pk primary key (codCompra)
);


create table Medida(
codMedida number(3) not null,
descMedida varchar(40) not null,

Constraint Medida_pk primary key (codMedida)
);


create table EstoqueSaida(
codVenda number(3) not null,
codProduto number(3) not null,
dataVenda date not null,
quantidade number(3) not null,
codMedida number(3) not null,
valor number(3) not null,

Constraint EstoqueSaida_pk primary key (codVenda, codProduto),
Constraint EstoqueSaida_fk1 foreign key (codVenda) references Venda(codVenda),
Constraint EstoqueSaida_fk2 foreign key (codProduto) references Produto(codProduto),
Constraint EstoqueSaida_fk3 foreign key (codMedida) references Medida(codMedida)
);

create table ProdutoPreco(

codProduto number(3)not null,
dataInicioPreco date not null,
dataFimPreco date not null,
codMedida number(3) not null,
preço number(3) not null,

Constraint ProdutoPreco_pk primary key (codProduto, dataInicioPreco),
Constraint ProdutoPreco_fk1 foreign key (codProduto) references produto(codProduto),
Constraint ProdutoPreco_fk2 foreign key (codMedida) references Medida(codMedida)
);


Create table EstoqueEntrada(
codCompra number(3) not null,
codProduto number(3) not null,
dataValidade date not null,
quantidade number(3) not null,
codMedida number(3) not null,
valorUnitario number(3) not null,

Constraint EstoqueEntrada_pk primary key (codCompra, codProduto),
Constraint EstoqueEntrada_fk1 foreign key (codCompra) references Compra(codCompra),
Constraint EstoqueEntrada_fk2 foreign key (codProduto) references Produto(codProduto),
Constraint EstoqueEntrada_fk3 foreign key (codMedida) references Medida(codMedida)
);

e o meu trabalho na facul seria criar procedimentos,funcoes e triggers baseado nessas tabelas, as functions eu consegui, triggers está em andamento só q as procedures eu não consegui fazer-las e muito menos executa-las já procurei bastante sobre isso só que nenhum dos exemplos me serviu peço a ajuda de vocês o meu exercicio pede q elas tenham (EXECPTIONS,E CURSORES) agraço desde já obrigada!

[Tineks]

Olá Julia,

se você conseguiu criar as FUNCTIONS provavelmente não terá dificuldades em criar PROCEDURES, você teria ai o codigo de alguma procedure que você tentou montar? se tiver coloque ela aqui no forum, assim podemos fazer os acertos nela, ficando mais de você entender como ela funciona..

[]'s

[Julia]

ai esta mas não sei bem ao certo se ela esta correta pois eu não consegui executa-la e outra eu preciso implementar nela a exception e o cursor e não tenho a minima ideia de como fazer

Selecionar tudo

CREATE OR REPLACE PROCEDURE teste1 (codtipo number, desctipo varchar2) is
begin
insert into TIPOPRODUTO(codTipoProduto) values (codTipo);
insert into TipoProduto(descTipoProduto) values(desctipo);
end;

[Julia]

é algo bem simples apenas com inserção eu gostaria de implementa-la mas não sei cmo fazer isssu!

[Tineks]

Julia, vai aqui um exemplo parecido com o q você precisa.
nesse caso está sendo feito um insert, e caso já exista aquele código de produto na base ele vai fazer um update, coloquei isso pois você tinha falado q precisada de um caso utilizando EXECPTION..

Selecionar tudo

CREATE OR REPLACE PROCEDURE prc_TESTE (
   prc_codigo      NUMBER,
   prc_descricao   VARCHAR2
)
IS
BEGIN
   INSERT INTO tipoproduto
               (codigo, descricao
               )
        VALUES (prc_codigo, prc_descricao
               );
EXCEPTION
   /* DEU ERRO DE PRIMARY KEY, COMO JÁ EXISTE ESSE REGISTRO VAMOS ALTERÁ-LO */
   WHEN DUP_VAL_ON_INDEX
   THEN
      BEGIN
         UPDATE tipoproduto
            SET descricao = prc_descricao
          WHERE codigo = prc_codigo;
      EXCEPTION
         /* SE DER ERRO AO DAR O UPDATE */
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line
               (   'Ocorreu erro ao alterar o registro. Descricao do erro : '
                || SQLERRM
               );
      END;
   /* CASO SEJA OUTRO TIPO DE ERRO */
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line
               (   'Ocorreu erro ao incluir o registro. Descricao do erro : '
                || SQLERRM
               );
END;

pra rodar essa procedure você pode utiliazar o comando abaixo..

Selecionar tudo

BEGIN
   prc_TESTE (1, 'Açucar');
END;

qualquer dúvida é só falar.
[]'s

[Julia]

aaaaiiiiii brigadaoooooo mesmuuuuuuuu você é um amorrrrr ^^! agora se não for pedi dimais eu gostaria de uma ajuda em relaçao a uma procedure com cursores...

pois essa sua procedure eu vou utiliza-la para fazer as outras com exception! teria cmo você me ajudar?

[Trevisolli]

Júlia,

Fiz o seguinte, ao invés de verificar a duplicidade com DUP_VAL_ON_INDEX, fiz com um cursor, para que você tenha EXCEPTION e CURSOR na mesma procedure, utilizando a procedure que o Tineks lhe passou como exemplo.

Segue abaixo.

Selecionar tudo

CREATE OR REPLACE PROCEDURE prc_TESTE ( 
   prc_codigo      NUMBER, 
   prc_descricao   VARCHAR2 
) 
IS 

  CURSOR CUR_EXISTE
    IS 
      SELECT COUNT(1)
        FROM TIPOPRODUTO
       WHERE CODIGO = PRC_CODIGO; 
       
  V_EXISTE NUMBER(10) := 0;      
  
BEGIN 
   
   OPEN CUR_EXISTE;
   FETCH CUR_EXISTE INTO V_EXISTE;
   CLOSE CUR_EXISTE;
   
   IF V_EXISTE > 0 
			THEN
      /* DEU ERRO DE PRIMARY KEY, COMO JÁ EXISTE ESSE REGISTRO VAMOS ALTERÁ-LO */ 
      BEGIN 
         UPDATE tipoproduto 
            SET descricao = prc_descricao 
          WHERE codigo = prc_codigo; 
      EXCEPTION 
         WHEN OTHERS THEN 
						      DBMS_OUTPUT.put_line 
						               (   'Ocorreu erro ao ALTERAR o registro. Descricao do erro : ' 
						                || SQLERRM 
						               );			
						END;																					   			
			END IF; 

   INSERT INTO tipoproduto 
               (codigo, descricao 
               ) 
        VALUES (prc_codigo, prc_descricao 
               ); 
EXCEPTION 
   /* CASO SEJA OUTRO TIPO DE ERRO */ 
   WHEN OTHERS 
   THEN 
      DBMS_OUTPUT.put_line 
               (   'Ocorreu erro ao incluir o registro. Descricao do erro : ' 
                || SQLERRM 
               ); 
END; 

Qualquer dúvida, manda pra gente.

[Julia]

Muitooo Obrigadaaa! vou utiliza-la como exemplo! obrigada mesmu pessoal bjinhos!

[Julia]

aaaaa brigadao mesmu pessoal foram de muitaaaaaaaaaa ajuda! bjussss!

[Marcelo Sim de Oliveira]

Gente eu tenho mesmo trabalho que o autor do post porem preciso de 5 triggers, 3 procedures e 3 funções e não estou conseguindo fazer, achei esse exemplo perfeito porem quando tentei implementar as outras partes não consegui será que alguém poderia me ajudar estou com muita urgencia.