Exemplos de valores do SQLCODE

Segue abaixo os valores possíveis do SQLCODE na ocorrência de uma

exceção.

SQLCODE Descrição

0 Nenhuma exceção foi encontrada

1 User-defined exception

+100 NO_DATA_FOUND exception

8 Exercícios

Controle do fluxo de execução(IF/LOOP)

1) Criar uma função chamada FNC_EXISTE_KING do tipo boolean

Fazer com que a função retorne true se existe algum funcionário com o nome de KING.Senão

retorna false

Dicas:(utilizar)

- uma variável antes do inicio do bloco do tipo number

- a função COUNT

- a cláusula INTO

- IF/ELSE/END IF

- RETURN

2) Criar a função FNC_FAIXA_SALARIAL do tipo varchar2

Retornar ‘otimo’ se a soma dos salários for maior que 20.000

Retornar ‘bom’ se a soma dos salários for entre 10.000 e 19.999

Retornar ‘razoável’ se a soma dos salários for entre 5.000 e 9.999

Retornar ‘ruim’ caso nenhuma das condições acima seja satisfatória

Dicas:(utilizar)

- uma variável antes do inicio do bloco do mesmo tipo do campo sal da tabela emp(%

TYPE)

- SUM

- IF/ELSIF/ELSE/END IF

- a cláusula INTO

- RETURN

3) Criar a função FNC_VALOR_DUPLO do tipo number

Esta função irá duplicar um número(com valor inicial de 15) até que ele seja maior que

1000.Retornar o resultado do cálculo.

Dicas:(utilizar)

- uma variável to tipo number com valor inicial

- LOOP(basic)/END LOOP

- EXIT WHEN

- RETURN

4) Criar a função FNC_EXECUTA_5_VEZES do tipo boolean

Irá executar 5 vezes um comando de exponenciação 2 de um número com início

de 2.Retornar true se o número é maior 200 e false se é menor

Dicas:(utilizar)

- uma variável to tipo number com valor inicial

- FOR LOOP/END LOOP

- POWER

- IF/ELSE/END IF

- RETURN

5) Criar a função FNC_MENOR_10 do tipo number

Irá executar o comando de somar um valor(inicial 0) com uma constante(inicial 15)

enquanto este valor seja menor que 100.Retornar o valor calculado

Dicas:(utilizar)

- uma variável to tipo number com valor inicial

- uma constante

- WHILE LOOP/END LOOP

- RETURN

Cursores

1) Criar uma função chamada FNC_BUSCA_NOME do tipo varchar2

Ela retornará o nome do funcionário da tabela EMP que tem o código 30.

Dicas:

- CURSOR

- OPEN

- FETCH

- CLOSE

- RETURN

2) Criar uma função chamada FNC_BUSCA_DEPT do tipo varchar2

Ela retornará o código e nome do departamento concatenados do funcionário JAMES.

Dicas:

- CURSOR

- OPEN

- FETCH(com duas colunas)

- CONCAT ou ||(ex: VAR1 || VAR2 = VAR1VAR2)

- CLOSE

3) Criar uma função chamada FNC_EXISTE_FUNC do tipo boolean

Utilize o atributo %FOUND de um cursor que recebe a data como parâmetro para saber se

existe algum funcionário admitido na data de 09/06/1981.Se existir retornar TRUE,senão

FALSE.

Dicas:

- CURSOR

- OPEN

- FETCH

- %FOUND

- CLOSE

- RETURN

4) Criar uma função chamada FNC_TOTAL_ANALISTA do tipo number

Esta função retornará a soma de salários dos funcionários com o cargo de analista utilizando

uma estrutura de repetição com cursor declarado na seção DECLARATIVE.

Dicas:

- CURSOR

- FOR LOOP (EX: FOR emp_record in c1 LOOP)

- Operador “+”

- RETURN

5) Criar uma função chamada FNC_NEW_YORK

Esta função retorna quantos funcionários de um departamento localizado em NEW YORK(FOR

LOOP com subquery) tem salário maior que 2500(use OPEN recebendo como parâmetro o

código do funcionário)

Dicas:

- FOR LOOP com subquery

- CURSOR

- OPEN

- FETCH

- %FOUND

- CLOSE

PLSQL Records,PLSQL Tables, Functions Procedures

1) Criar uma função chamada FNC_JAMES_1981 do tipo boolean que retorne TRUE se o

funcionário JAMES foi admitido no ano de 1980(não usar nenhuma variável, somente

Record).Caso contrário retorna FALSE.

Dicas:

- %ROWTYPE(da tabela EMP)

- SELECT INTO

- RETURN

2) Criar uma procedure chamada PRC_EMP_JOVEM do tipo varchar2 que retorne o nome e

o salário(parâmetros IN/OUT) do funcionário KING ou JONES que seja mais jovem na

empresa.

Dicas:

- usar 2 records

- usar 2 SELECT INTO ou cursor que receba empno como parâmetro

- operador de comparação “<”

- RETURN

3) Criar uma função chamada FNC_CALCULA_DECIMO do tipo number

Popule uma PL/SQL table com o seguinte sql :

“SELECT * FROM EMP”

Retorne o salário do décimo elemento da lista.

Dicas:

- TABLE

- LOOP ou FOR LOOP

- Referência ao elemento (ex.: pltable(4).código)

- RETURN

4) Criar uma função chamada FNC_DUPLICA do tipo number que duplica uma PLSQL table

populada com o sql:

“SELECT * from dept” e soma ao deptno da PLSQL table duplicada o valor 10.

Apagar a primeira PLSQL table e retornar o maior valor de DEPTNO da segunda.

Dicas :

- TABLE(2 vezes)

- EXTEND

- LOOP ou FOR LOOP

- Operador “+”

- DELETE

5) Criar uma procedure chamada PRC_INSERE_DEPT a qual insere um departamento na

tabela DEPT com os valores :

DEPTNO = Max(deptno) + 1

DNAME = ‘DEPT DO ‘||nome do aluno logado(USER)

LOC = ‘POA’

Dicas:

- INSERT

- COMMIT(para gravar os dados no banco)

6) Criar uma procedure chamada PRC_PERCENTUAL que recebe como parâmetro de

entrada um percentual e de saída um número(salário).

Este percentual será usado para atualizar o salário do funcionário com maior salário fazendo

com que seja % maior que o segundo maior salário.

Dicas:

Parâmetros:

P_PERCENTUAL IN NUMBER

P_SAL OUT EMP.SAL%TYPE

Ex: se o percentual é 10.

O JAMES possui o maior salário

O KING possui o segundo maior salário

Atualizar o salário do JAMES para 10 % a mais que o salário do KING

Retornar ao parâmetro P_SAL o salário do funcionário com o segundo maior salário.

7) Criar uma procedure chamada PRC_VERIFICA que verifique se o valor retornado pela

função FNC_CALCULA_DECIMO é igual ao salário do funcionário retornado pela execução

da procedure PRC_PERCENTUAL(percentual de 10 %).Se for igual emitir mensagem

‘Salário do décimo funcionário é o segundo maior salário”, senão “Erro ao procurar salário

do décimo funcionário”.

Dicas:

- Atribuir a função a uma variável

- Executar a procedure passando dois parâmetros

- Avaliar o resultado do segundo parâmetro

Packages, Triggers

Criar uma Package chamada PCK_GERAL.(espec. e corpo).Criar uma variável pública com o

nome de lg_percentual number inicializado com 12.

2) Criar uma função chamada FNC_TESTA_CIDADE na package PCK_GERAL do tipo boolean

que recebe o nome da cidade e um parâmetro(P_TOTAL_SAL) do tipo número.Avaliar se a

diferença da soma dos salário dos departamentos da cidade com o parâmetro P_TOTAL_SAL é

maior que a variável pública LG_PERCENTUAL..Se sim retornar TRUE senão retornar FALSE.

3) Criar uma procedure chamada PRC_EXECUTA_FUNCAO na package PCK_GERAL que

executa a função FNC_TESTA_CIDADE passando como parâmetro a cidade do funcionário

“JAMES” e a soma de salários do seu departamento.Se a função retornar TRUE emitir a

mensagem ‘Função retornou um valor válido para a cidade do funcionário JAMES”, senão

“Função executada com erro”

Dicas:

- DBMS_OUTPUT.PUT_LINE

- RAISE_APPLICATION_ERROR

4) Criar uma trigger na tabela DEPT com o nome BI_DEPT que será disparada antes da

inserção de uma linha na tabela e que incrementa em 10 o novo número do departamento.

Dicas :

- BEFORE INSERT

- UPDATE

- :NEW

5) Criar uma trigger na tabela EMP chamada BU_EMP que será disparada antes da

atualização(UPDATE) da coluna SAL.Gerar um erro caso a diferença entre o salário novo e

antigo seja superior a 10 % ou o salário novo seja menor que o antigo

Dicas:

- BEFORE UPDATE OF nome da coluna

- :NEW/:OLD

- operador “>”

6) Criar uma trigger chamada BID_DEPT na tabela DEPT que será disparada antes da

inserção/deleção.Caso seja uma inserção deve gerar um erro se já existir um departamento

com o mesmo nome.Caso seja uma deleção deve-se gerar um erro se existe algum funcionário

associado ao departamento.

Dicas:

- BEFORE INSERT OR DELETE

- INSERTING

- DELETING

- SELECT

- RAISE_APPLICATION_ERROR(-20500,’MENSAGEM’)

7) Criar uma trigger chamada AI_DEPT na tabela DEPT que será disparada depois da inserção

na tabela DEPT.Ela deve inserir um empregado com os dados(fictícios) do user do

departamento inserido.

Dicas:

- AFTER INSERT

- INSERT

- :NEW

- USER

8) Criar uma trigger chamada BU2_EMP(before update) na tabela EMP que somente será

disparada se o empregado foi admitido depois de 1980.

Atualizar a comissão do funcionário para NULL se o novo salário for diferente do antigo.

Dicas:

- BEFORE UPDATE

- WHEN

- :NEW/:OLD

Exceptions

Crie uma função chamada FNC_ACHOU que retorne TRUE se achou um funcionário com o nome

de ‘PEDRO’.Senão retorne FALSE.

Dicas:

- SELECT INTO

- EXCEPTION

- NO_DATA_FOUND

2) Crie uma procedure chamada PRC_INSERE_FUNC que tem como objetivo inserir um

funcionário na tabela EMP com o empno de 7876(outros dados fictícios).

Caso ocorra um erro de chave duplicada emitir uma mensagem dizendo que o código já existe.Para

qualquer outro erro emitir a mensagem “Não é possível cadastrar funcionário”

Dicas:

- INSERT

- EXCEPTION

- DUP_VAL_ON_INDEX

- OTHERS

- DBMS_OUTPUT.PUT_LINE

3) Crie uma função chamada FNC_USER_EXCEP que retorna um número.

Esta função seleciona o código do departamento dos funcionários que tem o nome iniciado com

“J”.Se retornar mais de uma linha a função retornará 2.Se não retornar nenhuma linha a função

retornará 0.

Caso o código selecionado seja 30 gerar um erro definido na seção declaration chamada

exceção_30 e a função retorna(30).

Dicas:

- SELECT INTO

- TOO_MANY_ROWS

- NO_DATA_FOUND

- Excecao_30 EXCEPTION

- RAISE excecao_30

- WHEN excecao_30

4) Criar uma trigger chamada BU_DEPT que é disparada antes da atualização da tabela DEPT.

Caso o novo local não esteja na relação dos locais já cadastrados gerar um erro definido na seção

declarative e gerado via RAISE_APPLICATION_ERROR.

Tratar a exceção levantada atualizando o novo local para ‘NEW YORK’

Dicas:

- nome_da_exceção EXCEPTION

- PRAGMA_EXCEPTION_INIT(nome_da_exceção,numero menor que -

20000)

- SELECT COUNT

- RAISE_APPLICATION_ERROR

- EXCEPTION

- WHEN nome_da_exceção THEN

No documento Introdução ao PL/SQL (páginas 45-53)