program a cao pls ql
Post on 12-Oct-2015
32 Views
Preview:
TRANSCRIPT
-
5/21/2018 Program a Cao Pls Ql
1/65
Introduo programaoPL/SQL
Maio 2009
Marcus Williammarcuswlima@gmail.com
Todos os direitos de cpia reservados. No permitida a distribuio fsica ou eletrnica deste materialsem a permisso expressa do autor.
-
5/21/2018 Program a Cao Pls Ql
2/65
-
5/21/2018 Program a Cao Pls Ql
3/65
I
SumrioApresentao ______________________________________________________ IV
Organizao Trabalho _____________________________________________________ IV
Autor ___________________________________________________________________ IV
Perfil Programador PL/SQL _______________________________________________ V
Arquitetura Banco de Dados Oracle _________________________________________ V
Parte I - Comandos SQL
1 Comandos SQL__________________________________________________ 7
2 Implementando consultas SQL simples _______________________________ 8
2.1 Construo Bsica ___________________________________________________ 8
2.2 Colunas ___________________________________________________________ 8
2.3 Expresses Aritmticas (Operadores aritmticos) _________________________ 9
2.4
Operador de Concatenao ___________________________________________ 9
3 Restringindo e ordenando dados ___________________________________ 10
3.1 Clusula WHERE __________________________________________________ 10
3.2 Operadores de comparao __________________________________________ 10
3.3 Operadores Lgicos ________________________________________________ 11
3.4 Precedncia _______________________________________________________ 11
3.5 Order By _________________________________________________________ 11
4 Funes de uma linha ___________________________________________ 12
4.1 Funes de Caracter ________________________________________________ 12
4.2
Funes Numricas _________________________________________________ 13
4.3 Funes de Data ___________________________________________________ 13
4.4 Funes de Converso ______________________________________________ 144.4.1
TO_CHAR(X[,format_model]) _________________________________________ 144.4.2 TO_DATE(string,formatao)________________________________________ 15
4.5 Expresses Condicionais Gerais ______________________________________ 164.5.1
NVL__________________________________________________________________ 164.5.2 DECODE _____________________________________________________________ 164.5.3 CASE________________________________________________________________ 16
5 Mostrando dados de vrias tabelas __________________________________ 18
5.1
Produto Cartesiano _________________________________________________ 18
5.2 Alias de Tabela ____________________________________________________ 19
5.3 Outer-JOIN _______________________________________________________ 20
5.4 Self-JOIN _________________________________________________________ 20
6 Funes de grupo _______________________________________________ 21
7 Subconsultas __________________________________________________ 23
7.1 Operador IN ______________________________________________________ 24
-
5/21/2018 Program a Cao Pls Ql
4/65
II
7.2 Operador ANY ____________________________________________________ 24
7.3 Operador ALL ____________________________________________________ 24
8 Manipulando dados (DML) _______________________________________ 25
8.1 INSERT __________________________________________________________ 25
8.2 UPDATE _________________________________________________________ 25
8.3 DELETE _________________________________________________________ 26
9 Controle de transao____________________________________________ 27
Parte II - Programao PL/SQL
10 Bloco PL/SQL _________________________________________________ 28
11 Programao PL/SQL ___________________________________________ 30
11.1 Comentrios ______________________________________________________ 30
11.2 Declaraes (seo de declarao) _____________________________________ 30
11.3
Tipos de Dados ____________________________________________________ 31
11.4 Assinalar Valores __________________________________________________ 32
11.5 Controle de Fluxo __________________________________________________ 3211.5.1 IF-THEN____________________________________________________________ 3311.5.2
IF-THEN-ELSE______________________________________________________ 3311.5.3 IF-THEN-ELSIF _____________________________________________________ 33
11.6 Controle de Repetio_______________________________________________ 3411.6.1
LOOP Simples______________________________________________________ 3411.6.2
WHILE-LOOP_______________________________________________________ 3411.6.3 FOR - LOOP________________________________________________________ 35
11.7 Labels ____________________________________________________________ 35
11.8 Cursores __________________________________________________________ 36
11.8.1
Controlando Cursores Explcitos ____________________________________ 36
11.8.2 Declarando um Cursor (DECLARE)___________________________________ 3611.8.3
Abr indo um Cursor (OPEN)__________________________________________ 3711.8.4 Extraindo dados do Cursor (FETCH) _________________________________ 3711.8.5 Fechando do Cursor (CLOSE) _______________________________________ 3711.8.6 Atr ibutos do Curso r Exp lc ito ________________________________________ 3811.8.7 LOOP Simples X Cursor_____________________________________________ 3911.8.8 LOOP While X Cursor_______________________________________________ 3911.8.9
LOOP For X Curso r_________________________________________________ 3911.8.10
LOOP For Implcitos________________________________________________ 4011.8.11 Cursores Implcitos_________________________________________________ 40
12 Tratamento de Exceo __________________________________________ 41
12.1
Tratando X Propagando_____________________________________________ 41
12.2 Tratamento de Excees _____________________________________________ 42
12.3 Excees PL/SQL Pr-definidas ou internas ____________________________ 43
12.4
Excees PL/SQL definidas pelo Usurio _______________________________ 44
12.5 Comando RAISE_APLLICATION_ERROR ___________________________ 44
12.6 Pragma EXCEPTION_INIT _________________________________________ 45
12.7 SQLCODE, SQLERRM _____________________________________________ 45
-
5/21/2018 Program a Cao Pls Ql
5/65
III
Parte III - Objetos Procedurais
13 Stored Subprograms _____________________________________________ 47
13.1 Stored Procedure __________________________________________________ 4713.1.1
Parmetros Stored Procedures______________________________________ 4813.1.2 Especificando valores de parmetros________________________________ 49
13.2 Stored Function ____________________________________________________ 5013.2.1
Locais permitidos para uso de Functions _____________________________ 51
14 Package ______________________________________________________ 52
15 Database Trigger _______________________________________________ 55
15.1 Elementos_________________________________________________________ 55
15.2 Predicado Condicional ______________________________________________ 56
15.3 Trigger de Linha ___________________________________________________ 5615.3.1 Qualificadores (:new, :old) __________________________________________ 5615.3.2 Clusula WHEN_____________________________________________________ 58
Parte IV - Apndices
A Oracle Net _____________________________________________________ 59
A.1 - Arquitetura _________________________________________________________ 59
A.2 - Configurao ________________________________________________________ 60
A.3 - Principais Problemas _________________________________________________ 61
B Schema HR (Human Resource) ____________________________________ 63
-
5/21/2018 Program a Cao Pls Ql
6/65
IV
Apresentao
Este material tem a inteno de mostrar quais so os mnimosconhecimentos necessrios para a construo dos primeiros objetos escritos emlinguagem PL/SQL como procedures, functons, packages e triggers.
Os livros disponveis no mercado voltados para programao Oracle,
inclusive a documententao oficial, so abrangentes e neles contm uma grandequantidade de informaes, que num primeiro momento podem dificultar oaprendizado da linguagem PL/SQL. Neste material, sem dvida, temos apenas oessencial para a iniciao na programao PL/SQL, para que esta experincia sejarpida e direta.
O leitor dever ter conhecimentos fundamentados em lgica deprogramao, e iniciais de banco de dados relacional, tendo em vista que omaterial mostrar como as estruturas de programao so aplicadas programao PL/SQL quanto a sintaxe.
O material foi concebido luz de vrios anos de experincia atuando emdesenvolvimento para Oracle e apresenta as informaes em uma seqnciapensada adequada para o objetivo do material. Faz parte tambm do material um
arquivo simples de texto com todos os exemplos listados na apostila para evitarredigitao dos mesmos.
Organizao Trabalho
Parte IAbordaremos a construo das primeiras instrues SQL, manipulando os
dados obtidos atravs de funes de linhas e funes de grupo. Consultascomplexas com vrias tabelas sero abordadas, assim como subqueries,manipulao de dados (insert, update, delete) e controle de transao.Parte II
Iniciaremos a programao, e ser apresentado como as estruturas de
programao se comportam no PL/SQL, como se d a manipulao dos famososcursores PL/SQL e finalizando com tratamento de erros. Todas essas atividadesse daro em blocos PL/SQL annimos que so a unidade bsica da programaoPL/SQL.Parte III
Continuando a programao, abordaremos a criao e compilao dosprincipais objetos procedurais (procedures, function, package, triggers), conheceras suas especificidades e suas finalidades.Parte IV
Como apndice, porm no menos importante, sero mostrados os conceitospara uma correta configurao de um software Oracle Client.
AutorMarcus William, Tecnlogo em Processamento de Dados, com mais de 10
anos na rea de TI, convive h mais de sete anos com produtos Oracle, autodidatano aprendizado da programao PL/SQL, foi coordenador dos programadoresPL/SQL em 2000 no DETRAN-PA e atualmente chefia da Diviso de Banco deDados do TJE-PA.
-
5/21/2018 Program a Cao Pls Ql
7/65
V
Perfil Programador PL/SQL
O programador PL/SQL o profissional da rea de TI especializado emcriar e manter as construes procedurais de um SGDB ORACLE, estes escritosem linguagem PL/SQL. O PL/SQL no um produto separado, sim umatecnologia integrada ao SGDB Oracle e est disponvel desda verso 6.
Uma das principais finalidades da linguagem PL/SQL a construo deStored Procedures, que so unidades de manipulao de dados com scopodefinido de aes, e estaro disponveis aos usurios do SGDB segundo umapoltica de acesso e privilgios customizada. A principal finalidade das StoredProcedures prover procedimentos armazenados no SGDB de fcil utilizao,aliviando assim, a carga de processamento no cliente. Os principais tipos deStored Procedure se do na forma de procedure, function e package.
Para se criar ou compilar um objeto no Oracle, o programador deversubmeter ao banco o comando de criao deste objeto, comando este que contertodos os atributos e inclusive a lgica prevista para o objeto. Se o programadortem o privilgio de criar os seus objetos, ele o far, caso contrrio essa tarefa responsabilidade do DBA (DataBase Administrator). Uma forma de facilitar este
trabalho registrar o comando de criao em scripts para fcil recriao quandonecessrio. comum escutar Vamos alterar a procedure. A rigor um objeto procedural
no alterado e sim recriado ou recompilado. Se a alterao consiste em apenasna adio de uma linha, ento todo o comando de compilao, adicionado da novalinha, dever ser reapreciado/compilado pelo Oracle.
Existem alguns excelentes aplicativos IDE (Integrated DevelopmentEnvironment) que auxiliam a gerncia dos scripts de construo de objetosprocedurais.
Produto Fabricante URLPL/SQL Developer Allroundautomations www.allroundautomations.com/plsqldev.htmlSQL Developer Oracle www.oracle.com/technology/products/database/sql_developer/index.htmlSQL Navigator Quest www.quest.com/sql_navigator
Obs.:O programa PL/SQL Developer de prefrncia do autor, no por achar mais eficiente e sim porestar acostumado.
Arquitetura Banco de Dados Oracle
necessrio para o programador PL/SQL entender a estrutura dearmazenamento de objetos disponveis no Oracle a fim de executar suasatividades.
Vamos comear a entender a diferena entre Banco de Dados eInstncia. Em um SGDB Oracle, a rigor, o termo Banco de Dados se aplica aosarquivos fsicos que integram o mesmo. Assim sendo, em uma conversa com o
suporte Oracle o atendente entender banco de dados como os arquivosformadores do Oracle.Instncia o conjunto de processos de memria armazenados em forma
voltil, que so responsveis pela gerncia de todas as atividades incidente econsequente no banco de dados (arquivos fsicos). Quando um usurio cria umaconexo no banco, na realidade ele est se conectando a uma instncia, que lheservir de ponte de trabalho ao banco de dados.
O schema uma representao lgica organizacional que aglutina umaporo de objetos do banco de dados. Quando criamos um usurio estamostambm criando um schema inicialmente vazio. Em geral, convencionado que
-
5/21/2018 Program a Cao Pls Ql
8/65
VI
um ou mais schemas contemplem os objetos de produo de um determinadosistema e que os demais usurios criados sejam utilizados apenas para fins deconexo e uso dos objetos dos schemas de produo. Para que isso acontea,uma poltica de privilgios dever ser implementada.
Toda essa arquitetura de responsabilidade do Administrador de Banco deDados - DBA. E no dia-a-dia, estas definies se confundem. No difcilencontrar algum usando o termo banco de dados para uma instncia ou umschema. Isso no um pecado. O que se deve ter em mente a real diferenaentre estes conceitos.
Um programador PL/SQL dever receber as seguintes informaes do DBApara dar incio s suas atividades:
host ou endereo IP servidor de banco de dados; usurio de banco de dados; senha de acesso; identificao do servio ou nome do banco de dados.
Com estas informaes, o programador dever saber configurar o softwareOracle Client instalado na sua estao de trabalho a fim de acessar o banco dedados informado pelo DBA. O usurio de banco de dados informado dever terprivilgios de compilao e recompilao de objetos procedurais seguindo apolitica de privilgios imposta ao banco de dados. boa prtica, o DBA informarum banco de desenvolvimento ao invz de um banco de produo.
Obs:. No apndice A so apresentados os aspectos mais profundos quanto configurao declientes Oracle.
-
5/21/2018 Program a Cao Pls Ql
9/65
_Introduo Programao PL/SQL ______________________marcuswlima@gmail.com
7Comandos SQL
Parte I - Comandos SQL
1 Comandos SQL
Structured Query Language (SQL) um conjunto de instrues com asquais os programas e usurios acessam as informaes dos bancos de dadosrelacionais, como o Oracle. As aplicaes permitem aos usurios manipulao nosdados sem o efetivo uso de instrues SQL, no entanto essas aplicaesseguramente usam SQL para executar as requisies dos usurios.
O Dr. E.F. Codd em Junho de 1970 publicou o estudo "A Relational Modelof Data for Large Shared Data Banks". Este modelo de banco proposto agoraaceito como definitivo pelos grandes softwares gerenciadores de banco de dados(SGDB). A linguagem SEQUEL (Structured English Query Language) foidesenvolvida pela IBM aproveitando o modelo de Codd, em seguida setransformou SQL. Em 1979 a empresa Rational Software (atualmente OracleCorporation) lanou a primeira implementao de banco de dados relacional vivelcomercialmente.
Existem comits que trabalham no sentido de padronizar como a indstriade software ir considerar o SQL em termos de sintaxe. Os principais comits soANSI (American National Standards Institute) e o ISO/IEC (InternationalOrganization for Standardization/ International Electrotechnical Commission). AOracle se esfora para estar de acordo com os padres sugeridos e participaativamente deste comits de padro SQL. As verses Oracle10g(Enterprise,Standard, Express, Standard One) foram projetadas para estarem emconformidade ao pado ANSI-2003. Verifique a tabela de confirmidade em :
http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14200/ap_standard_sql.htm#g21788
Obs: Sero apresentados apenas os comandos SQL de consulta e manipulao de dados, oscomandos de manuteno das estruturas (CREATE , ALTER) no pertencem ao escopo destecurso.
-
5/21/2018 Program a Cao Pls Ql
10/65
_Introduo Programao PL/SQL ______________________marcuswlima@gmail.com
8Implementando consultas SQL simples
2 Implementando consultas SQL simples
Para extrairmos e consultarmos dados das tabelas de um banco relacional,usamos a instruo SELECT, que faz uma pesquisa nas estruturas relacionais dobanco de dados e retorna valores na forma de linhas e colunas. Uma consulta SQLpode tambm ser nominada simplesmente como query.
2.1 Construo Bsica
SELECT {*|coluna1 [apelido], coluna2 [apelido]}FROM tabela
Figura 2.1 Sintaxe Select
Diretrizes: SQL no fazem distino entre maisculas e minsculas; SQL podem estar em uma ou mais linhas; As palavras reservadas no podem ser abreviadas; Normalmente as clausulas so colocadas em linhas diferentes (boa prtica); Guias e identaes so usadas para aumentar a legibilidade.
2.2 Colunas
Quanto s colunas temos duas alternativas a primeira selecionar todas ascolunas em uma nica consulta SQL, a outra mais elegante selecionar apenasas colunas interessantes, cada coluna pode ter um aliasassociado. Alis so muitoteis para clculos matemticos.
SQL>select *2 from departments3 /
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID------------- ------------------------------ ---------- -----------10 Administration 200 170020 Marketing 201 1800
SQL>select first_name, salary salario_mensal2 from employees3 /
FIRST_NAME SALARIO_MENSAL-------------------- --------------Steven 26400Steven 24000
Exemplo 2.1 Select Colunas
-
5/21/2018 Program a Cao Pls Ql
11/65
_Introduo Programao PL/SQL ______________________marcuswlima@gmail.com
9Implementando consultas SQL simples
2.3 Expresses Aritmticas (Operadores aritmticos)
possvel criar expresses com colunas do tipo NUMBERe DATEusandooperadores aritmticos, na seguinte procedncia ( *, /, +, - ).
SQL>select first_name, salary , (salary * 20) / 100 porcentagem
2 from employees3 /
FIRST_NAME SALARY PORCENTAGEM-------------------- ---------- -----------Steven 26400 5280Neena 17000 3400
Exemplo 2.2 Expresses
Para adicionarmos 1(um) dia ao valor de um coluna do tipo DATEdevemosutilizar o operador +da seguinte forma valor_data+1, e para adicionar uma hora aseguinte sintaxe valor_data+(1/24)
SQL>select first_name, hire_date, hire_date+365 data_exame2 from employees3 /
FIRST_NAME HIRE_DATE DATA_EXAME-------------------- ----------- -----------Steven 17-JUN-1987 16-JUN-1988Steven 17-JUN-1987 16-JUN-1988
Exemplo 2.3 Expresses Data
Obs.: Um valor nulo no o mesmo que zero ou um espao em branco. **QUALQUER VALOROPERACIONADO COM NULO RESULTA NULO**. Este problema remediado pela funo NVLver(4.5.1)
2.4 Operador de Concatenao
No Oracle o operador ||executa contatenao de dois strings de caracteres,e poder ser usado nas clusulas SELECT, WHER e ORDER BY. Utilizada naclusula SELECT de uma consulta SQL, resultar em um campo do tipo caracter.
SQL>select first_name||' '||last_name nome_completo2 from employees3 /
NOME_COMPLETO----------------------------------------------Steven KingNeena Kochhar
Exemplo 2.4 Concatenao
-
5/21/2018 Program a Cao Pls Ql
12/65
_Introduo Programao PL/SQL ______________________marcuswlima@gmail.com
10Restringindo e ordenando dados
3 Restr ingindo e ordenando dados
3.1 Clusula WHERE
possvel restringir as linhas retornadas da consulta SQL utilizando a
clusula WHERE. As linhas da tabela s estaro no retorno da consulta SQL se acondio da clusula WHERE aplicada linha for atendida com sucesso. Aclusula WHERE seguida de uma expresso condicional. O Oracle aplicacondio s linhas da(s) tabela(s) de clusula FROM. Cada aplicao de linhagera um valor boolean. As linhas que geram valores TRUE formaro o datasetderetorno
SQL>select first_name,job_id, department_id2 from employees3 where job_id = 'ST_CLERK'4 /
FIRST_NAME JOB_ID DEPARTMENT_ID-------------------- ---------- -------------Trenna ST_CLERK 50Curtis ST_CLERK 50
Exemplo 3.1 Clusula WHERE
3.2 Operadores de comparao
Operadores de comparao comparam dois valores ou expresses eretornando um resultado de valor Boolean. A tabela 3.1 ilustra os operadores decomparao mais recorrentes. Geralmente os operadores so usados na clusulaWHERE.
Tabela 3.1. Operadores de Comparao.Operador Signifi cado Exemplo= Igual a SELECT *
FROM EMPLOYEESWHERE LAST_NAME=SCOOT
> Maior que SELECT LAST_NAMEFROM EMPLOYEESWHERE SALARY > 2000
>= Maior ou igual a SELECT FIRST_NAME,SALARYFROM EMPLOYEESWHERE SALARY >= 2000
< Menor que SELECT LAST_NAMEFROM EMPLOYEESWHERE SALARY < 2000
-
5/21/2018 Program a Cao Pls Ql
13/65
_Introduo Programao PL/SQL ______________________marcuswlima@gmail.com
11Restringindo e ordenando dados
3.3 Operadores Lgicos
Operadores lgicos so usados para combinar ou alterar o resultado deuma ou mais comparaes (ver 3.2). O produto desta operao ser um valorbooleano e no escopo deste curso ser utilizado para determinar quais linhasestaro no resultado da consulta.
Tabela 3.2. Operadores LgicosOperador Signifi cado ExemploNOT Retorna TRUE se a condio seguinte for FALSE SELECT *
FROM EMPLOYEESWHERE NOT (SALARY < 10000)
AND Retorna TRUE se a condio de componentes foremTRUE
SELECT *FROM EMPLOYEESWHERE SALARY > 10000AND LAST_NAME LIKE H%
OR Retorna TRUE se cada condio de componentesforem TRUE
SELECT FIRST_NAME,SALARYFROM EMPLOYEESWHERE LAST_NAME = THOMAS
OR LAST_NAME = JACOB
3.4 Precedncia
Mltiplos operadores podem formar uma expresso vlida. Os operadorescom maior precedncia sero avaliados antes dos operadores de menorprecedncia, seguindo a tabela 3.3.
Tabela 3.3. Precedncia de Operadores.Operador Propsito*, / Multiplicao, diviso+, -, || Adio, subtrao, concatenao=, !=, , =, IS NULL, LIKE, BETWEEN, IN ComparaoNOT NegaoAND ConjunoOR DisjunoObs.: Parnteses podero ser utilizados para alterar a precedncia de avaliao.
3.5 Order By
A ordem das linhas retornadas em um resultado de consulta indefinida. Aclusula ORDER BY pode ser utilizada para classificar as linhas. A clusulaORDER BY deve ser colocada aps a clusula WHERE. Por definio o resultadoser exibido de forma crescente e atravs da palavra-chave DESC a ordem invertida. O resultado pode tambm ser ordenado por vrias colunas
SQL>select first_name,job_id, department_id2 from employees3 order by department_id, salary DESC4 /
FIRST_NAME JOB_ID DEPARTMENT_ID-------------------- ---------- -------------Jennifer AD_ASST 10Michael MK_MAN 20
Exemplo 3.2 Clusula Order BY
Obs.: Sem o uso da clucula ORDER BY o resultado da consulta incerto.
-
5/21/2018 Program a Cao Pls Ql
14/65
_Introduo Programao PL/SQL ______________________marcuswlima@gmail.com
12Funes de uma linha
4 Funes de uma linha
Em instrues SQL as funes de uma linha so usadas principalmentepara manipular os valores que sero apresentados. Estas aceitam um ou maisargumentos e retornam um ou nico valor para cada linha do dataset gerado pelaconsulta. Um argumento pode ser um dos seguintes itens, constantes, valorvarivel, nome de coluna, expresso.
Quanto aos recursos de funes de uma linha Atua em cada linha retornada da consulta. Retorna um resultado por linha. Podem receber zero, um ou mais argumentos. Podem ser usados em clusulas SELECT, WHERE, ORDER BY.
Obs.: Funes de Linha podem ser usadas em instrues DMLs ver(8).
4.1 Funes de Caracter
Tabela 3.4. Funes CaracterFuno ObjetivoLower(string) Converte valores de caracteres alfabticos para letras maisculasUpper(string) Converte valores de caracteres alfabticos para letras maisculasInitcap(string) Converte os valores de caracteres alfabtico para usar maiscula na
primeira letra de cada palavra e todas as outras letras em minsculasConcat(string1, string2) Contatena o primeiro valor com o segundo valor. Equivalente ao operador
||Substr(string,m,[n]) Retorna caracteres especficos a partir do valor de caractere comeando
na posio m, at n caracteres depois (Se m for nagativo, a conta iniciano final do valor de caracterres. Se n for omitido, so retornados todoscaracteres at, o final da string)
Length(char) Retorna o nmero de caracteresInstr(string , substring) Retorna a posio numria do substringlpad(string,n,string) Preenche o valor de caracteres justificando direita a uma largura total
de n posies de caracter
Tabela 3.4. Funes Caracter - ResultadosFuno ResutadoLOWER('SQL Course') sql courseUPPER('SQL Course') SQL COURSEINITCAP('SQL Course') Sql CourseCONCAT('Hello','World') HelloWorldSUBSTR('HelloWorld',1,5) HelloLENGTH('HelloWorld') 10INSTR('HelloWorld','W') 6LPAD(salary,10,'*') *****24000RPAD(salary,10,'*') 24000*****
SQL>SELECT employee_id, CONCAT(first_name, last_name) NAME,2 job_id, LENGTH (last_name),3 INSTR(last_name, 'a') "Contains 'a'?"4 FROM employees5 /
EMPLOYEE_ID NAME JOB_ID LENGTH(LAST_NAME) Contains 'a'?----------- ------------------ ---------- ----------------- -------------
100 StevenKing AD_PRES 4 0101 NeenaKochhar AD_VP 7 6102 LexDe Haan AD_VP 7 5
Exemplo 4.1 Funes de linha
-
5/21/2018 Program a Cao Pls Ql
15/65
_Introduo Programao PL/SQL ______________________marcuswlima@gmail.com
13Funes de uma linha
4.2 Funes Numricas
Tabela 3.4. Funes NumricasFuno Objetivo Argum ento(s) ResultadoROUND Arredonda valor para determinado decimal (45.926,2) 45.93TRUNC Trunca valor para determinado decimal (45.926,2) 45.92MOD Retorna o restante da diviso (1600,300) 100
SQL>SELECT ROUND(45.923,2), ROUND(45.923,0),2 ROUND(45.923,-1)3 FROM DUAL;
ROUND(45.923,2) ROUND(45.923,0) ROUND(45.923,-1)--------------- --------------- ----------------
45,92 46 50
1 linha selecionada.
SQL>SELECT TRUNC(45.923,2), TRUNC(45.923),2 TRUNC(45.923,-2)3 FROM DUAL;
TRUNC(45.923,2) TRUNC(45.923) TRUNC(45.923,-2)--------------- ------------- ----------------
45,92 45 0
1 linha selecionada.
SQL>SELECT last_name, salary, MOD(salary, 5000)2 FROM employees3 WHERE job_id = 'SA_REP';
LAST_NAME SALARY MOD(SALARY,5000)------------------------- ---------- ----------------Abel 11000 1000Taylor 8600 3600Grant 7000 2000
3 linhas selecionadas.
Exemplo 4.2 Funes Numricas
Tabela DUALEm todos os bancos de dados Oracle existe uma tabela chamada DUAL, aparentemente
irrelevante. No entento, ela til quando se deseja retornar um valor pontual, sendo principalmenteusada para a execuo de instrues SQL que no necessitam de tabela base.
4.3 Funes de Data
Tabela 3.4. Funes DataFuno Objetivo Argumento(s) ResultadoMonths_between Nmero de meses entre duas datas (01-SET-95,11-JAN-94) 19.674194add_months Adiciona meses de calendrio para a
data(11-JAN-94,6) 11-JUL-94
last_day ltimo dia do ms (01-SET-95) 30-SET-95Round Data de arredondamento (25-JUL-95,mm)
(25-JUL-95,yy)01-AGO-9501-JAN-95
Trunc Data para truncada (25-JUL-95,mm)(25-JUL-95,yy)
01-JUL-9501-JAN-95
Devemos saber informar ao ambiente de trabalho como todas essasinformaes sero apresentadas, segundo as nossas necessidades. Uma dasformas atravs dos seguintes comandos.
ALTER SESSION SET NLS_DATE_LANGUAGE=PORTUGUESE;ALTER SESSION SET NLS_DATE_FORMAT=DD-MON-YYYY;
Figura 4.1 Configurando o ambiente
-
5/21/2018 Program a Cao Pls Ql
16/65
_Introduo Programao PL/SQL ______________________marcuswlima@gmail.com
14Funes de uma linha
Para manipular valores do tipo DATE em um formato diferente do padroestabelecido pelo ambiente de trabalho, se faz necessrio o uso da funoTO_CHAR com os elementos de Format Model corretos.
Para o curso ser utilizado o Format Model DD-MON-YYYY. Verifique emhttp://download-east.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements004.htm#i34924
todos os Format Model disponveis valores do tipo DATE.
4.4 Funes de Converso
4.4.1 TO_CHAR(X[,format_model])Onde X um valor do tipo DATE ou NUMBER e format_model uma
string que descreve o formato de como o argumento X ser apresentado. Existemformataes especficas para o tipo DATE e outras para o tipo NUMBER.
Tabela 3.5. Principais Format Model para tipo DATEElemento Descrioyyyy Ano com 4 dgitosmm Ms (01-12)dd Dia do ms (1-31)
hh24 Hora do dia (0-23)mi Minuto(0-59)ss Segundo(0-59)day dia semana por extensomon Abreviao do msmonth Nome extenso do ms
SQL>SELECT2 SYSDATE data1,3 TO_CHAR(SYSDATE,'DD/MM/YYYY hh24:mi:ss') data2,4 TO_CHAR(SYSDATE,'DD/Mon/YYYY') data3,5 TO_CHAR(SYSDATE,'fm"Belm, "DD" de "month" de "yyyy') data46 FROM DUAL7 /
DATA1 DATA2 DATA3 DATA4----------- ------------------- ----------- ------------------------------
27-OUT-2006 27/10/2006 19:58:09 27/Out/2006 Belm, 27 de outubro de 2006
1 linha selecionada.
Exemplo 4.3 Funes TO_CHAR
Diretrizes para datas: O Format Model deve estar entre aspas simples e fazer distino entre maisculas e
minsculas. O Format Model e o valor do argumento devem estar separados por vrgula. Para remover os espaos em branco ou suprimir os zeros esquerda, use o modo
de preenchimento fm. Qualquer string pode ser adicionada ao Format Model delimitado por aspas duplas.
Quando aos valores do tipo NUMBER, se necessrio, deveremos informar
ao ambiente o separador de milhar e decimal do padro brasileiro, atravs docomando:
ALTER SESSION SET SET NLS_NUMERIC_CHARACTERS=',.';
Figura 4.2 Configurando o ambiente
-
5/21/2018 Program a Cao Pls Ql
17/65
_Introduo Programao PL/SQL ______________________marcuswlima@gmail.com
15Funes de uma linha
Tabela 3.5. Principais Format Model para tipo NUMBERElemento Descrio9 Represnta um nmero0 Fora que um Zero seja mostradoG Separador de MilharD Separador de decimalRN Nmero Romano
SQL>select to_char(1234.5),2 to_char(1234.5,'99999.00'),3 to_char(1234.5,'99g999d00'),4 to_char(1234.5,'RN')5 from dual6 /
TO_CHA TO_CHAR(1 TO_CHAR(12 TO_CHAR(1234.5,------ --------- ---------- ---------------1234,5 1234.50 1.234,50 MCCXXXV
1 linha selecionada.
Exemplo 4.4 Funes TO_CHAR
4.4.2 TO_DATE(string,formatao)Onde string a informao que se deseja transformar para valor do tipo
DATE e formatao o FORMAT MODEL que indica com como o Oracle deverreconhecer a string apresentada no primeiro parmetro.
SQL>select FIRST_NAME,HIRE_DATE2 from employees3 where HIRE_DATE = to_date('07/06/1994','dd/mm/yyyy')4 /
FIRST_NAME HIRE_DATE-------------------- -----------Shelley 07-JUN-1994William 07-JUN-1994
2 linhas selecionadas.
SQL>insert into job_history2 (EMPLOYEE_ID3 ,START_DATE
4 ,END_DATE5 ,JOB_ID6 ,DEPARTMENT_ID)7 values8 (1749 ,to_date('05/novembro/1974','dd/month/yyyy')
10 ,to_date('10abr1978','ddmonyyyy')11 ,'AD_VP'12 ,80)13 /
1 linha criada.
Exemplo 4.5 Funes TO_DATE
Obs.:Para converso de dados, existe tambm o TO_NUMBER, no entando mais prtico se valerda converso implcita executada pelo Oracle. Tente usar o comando SELECT 1 + 1 FROM DUAL
-
5/21/2018 Program a Cao Pls Ql
18/65
_Introduo Programao PL/SQL ______________________marcuswlima@gmail.com
16Funes de uma linha
4.5 Expresses Condic ionais Gerais
4.5.1 NVLConverte valores nulos para um valor real, a funo est apta a trabalhar
com os tipos de dados DATE, CHARACTER e NUMBER. Os parmetros
informados devem ser do mesmo tipo de dado.
SQL>select salary, commission_pct, salary+commission_pct, salary+nvl(commission_pct,0)2 from employees3 where EMPLOYEE_ID in (144,149)4 /
SALARY COMMISSION_PCT SALARY+COMMISSION_PCT SALARY+NVL(COMMISSION_PCT,0)---------- -------------- --------------------- ----------------------------
10500 ,2 10500,2 10500,22500 2500
2 linhas selecionadas.
Exemplo 4.6 Funes NVL
4.5.2 DECODE
DECODE(expr,search1,result1
[,search2,result2,...,,searchN,resulN]
[,default])
Figura 4.3 DECODE
A expresso DECODE trabalha de um modo similar lgica IF-THEN-ELSE. A expresso DECODE compara expra todos searchum por vez. Se exprigual to searchento o Oracle retorna o resultcorrespondente. Se no encontrarnenhuma correspendncia ento o Oracle retorna default. Neste caso, se defultestiver omitida o Oracle retornar null
SQL>select2 job_id,3 salary,4 DECODE(job_id,5 'IT_PROG',salary*1.1,6 'ST_MAN',salary*1.2,7 'MK_REP',salary*1.3,8 salary) realuste9 from employees
10 /
Exemplo 4.7 DECODE
4.5.3 CASE
CASE expr WHEN compare1 THEN resul1[WHEN compare2 THEN resul2WHEN compar2N THEN resulNELSE resulElse
END
Figura 4.4 CASE
Em uma expresso CASE, o Oracle pesquisa a partir da primeira clusulaWHEN no qual expr igual a comparee retorna result. Se nenhuma das clusulasWHEN for selecionada e uma clusula ELSE existir, ento o Oracle retornarresultElse.
-
5/21/2018 Program a Cao Pls Ql
19/65
_Introduo Programao PL/SQL ______________________marcuswlima@gmail.com
17Funes de uma linha
SQL>select2 job_id,3 salary,4 CASE job_id5 WHEN 'IT_PROG' THEN salary*1.16 WHEN 'ST_MAN' THEN salary*1.27 WHEN 'MK_REP' THEN salary*1.38 ELSE salary9 END
10 from employees11 /
Exemplo 4.8 CASE
Obs: D preferncia ao comando CASE ao invs do DECODE. O comando CASE mais poderoso
Obs: No link http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14200/functions.htm#i1482196 encontra-se a relao de todas as funes de uma linha disponveis nas verses Oracle10g
Obs: No captuto 15 ser apresentado como criar e programar as suas prpias funes
-
5/21/2018 Program a Cao Pls Ql
20/65
_Introduo Programao PL/SQL ______________________marcuswlima@gmail.com
18Mostrando dados de vrias tabelas
5 Mostrando dados de vrias tabelas
Existem momentos em que faz necessrio o uso de dados a partir de maisde uma tabela, neste caso usaremos condies especiais chamadas de JOINs. Aslinhas de uma tabela podem ser relacionadas s linhas de outra tabela de acordocom os valores comuns existentes nas colunas correspondentes, que em geral socolunas de chave primria e estrangeira.
SELECT table1.column, table2.columnFROM table1, table2WHERE table1.column1 = table2.column2;
Figura 5.1 Join
Diretrizes para Joins: Ao se escrever uma instruo SELECT que combine mais de uma tabela, interessante
deixar claro a que tabela o campo pertence, posicionando o nome da tabela antes donome do campo.
No obrigatrio o posicionamento o nome da tabela antes do nome do campo, porm,se uma mesma coluna pertence a mais de uma tabela, deve-se prefixar a coluna com o
nome da tabela. Para combinar ntabelas se fez necessrio no mnimo n-1condies de JOIN
SQL>SELECT employees.employee_id, employees.last_name,2 employees.department_id, departments.department_id,3 departments.location_id4 FROM employees, departments5 WHERE employees.department_id = departments.department_id;
EMPLOYEE_ID LAST_NAME DEPARTMENT_ID DEPARTMENT_ID LOCATION_ID----------- ------------------------- ------------- ------------- -----------
1000 King 90 90 1700100 King 90 90 1700
Exemplo 5.1 Join
possvel tambm adicionar condies de filtros s condies de JOINpara restringe mais ainda as linhas obtidas. No exemplo abaixo, seroapresentados o nome e o departamento do funcionrio Matos.
SQL>SELECT last_name, employees.department_id,2 department_name3 FROM employees, departments4 WHERE employees.department_id = departments.department_id5 AND last_name = 'Matos';
LAST_NAME DEPARTMENT_ID DEPARTMENT_NAME------------------------- ------------- ------------------------------Matos 50 Shipping
1 linha selecionada.
Exemplo 5.2 Join / AND
5.1 Produto Cartesiano
Quando um JOIN for completamente invlido ou omitido, o resultado daconsulta SQL ser um produto cartesiano no qual sero exibidas todascombinaes de linhas de todas as tabelas envolvidas na consulta. O Produtocartesiano tende a gerar um grande nmero de linhas e seu resultado raramente til. Apresentamos o produto cartesiano aqui por finalidades didticas.
-
5/21/2018 Program a Cao Pls Ql
21/65
_Introduo Programao PL/SQL ______________________marcuswlima@gmail.com
19Mostrando dados de vrias tabelas
SQL>SELECT last_name, department_name dept_name2 FROM employees, departments;
LAST_NAME DEPT_NAME------------------------- ------------------------------King AdministrationKing AdministrationKochhar Administration....Higgins ContractingGietz Contracting
168 linhas selecionadas.
Exemplo 5.3 Produto Cartesiano
5.2 Alias de Tabela
Para qualificar as colunas possvel utilizar alias de tabela ao invez donome da tabela. Assim como os aliasde coluna do outro nome coluna, os aliasde tabela tem a mesma funo. Os alias de tabela so definidos na clusulaFROM. O nome da tabela especificado totalmente seguido do seu alias.
SQL>SELECT e.employee_id, e.last_name, e.department_id,2 d.department_id, d.location_id3 FROM employees e , departments d4 WHERE e.department_id = d.department_id;
EMPLOYEE_ID LAST_NAME DEPARTMENT_ID DEPARTMENT_ID LOCATION_ID----------- ------------------------- ------------- ------------- -----------
100 King 90 90 1700101 Kochhar 90 90 1700
Exemplo 5.4 Alias tabela
Na instruo SQL do exemplo 5.4 foram definidos os alias e para a tabelaemployeese alias d para a tabela departments.
Obs: Perceba quanto o uso de alias de tabela trs legibilidade construo de consultas SQLcomparando o exemplo 5.4 e o exemplo 5.1
SQL>SELECT2 e.last_name,3 d.department_name,4 l.city5 FROM6 employees e,7 departments d,8 locations l9 WHERE
10 e.department_id = d.department_id11 AND d.location_id = l.location_id;
LAST_NAME DEPARTMENT_NAME CITY------------------------- ------------------------------ ---------------------King Executive SeattleKochhar Executive SeattleDe Haan Executive SeattleHunold IT Southlake
Exemplo 5.5 Join em mais de uma tabela.Diretrizes para Joins: Os alias de tabela no ultrapassam 30 posies; um alias de tabela poder substituir o nome da tabela em todas as clusulas do SQL. os alias devem ser sugestivos. No utilizem algo com T1, T2, T3,... palavras reservadas no podem ser utilizadas como alias nenhum. Algo como DESC
alusivo a descrio.
-
5/21/2018 Program a Cao Pls Ql
22/65
_Introduo Programao PL/SQL ______________________marcuswlima@gmail.com
20Mostrando dados de vrias tabelas
5.3 Outer-JOIN
Se a linha no satisfaz a condio da clausula WHERE, no aparecer noresultado do Select. Nestas condies, estas linhas podero aparecer se operadorde outer joinfor utilizado no JOIN. O operador (+)dever ser posicionado no ladojoin onde a informao deficiente.
SELECT e.last_name, e.department_id, d.department_nameFROM employees e, departments dWHERE e.department_id = d.department_id(+)
Exemplo 5.6 OUTER JOIN
5.4 Self-JOIN
As vezes, necessrio a execuo do um join de uma tabela com elamesma. Desta forma a tabela aparecer duas vezes na clusula FROM e naclusula WHERE existir a restrio referente ao self-join. Neste caso o uso dealias de tabela imperativo.
SQL>SELECT worker.last_name || ' works for ' || manager.last_name2 FROM employees worker, employees manager3 WHERE worker.manager_id = manager.employee_id ;
WORKER.LAST_NAME||'WORKSFOR'||MANAGER.LAST_NAME-------------------------------------------------------------Kochhar works for KingDe Haan works for KingHunold works for De Haan
Exemplo 5.7 Self JOIN
-
5/21/2018 Program a Cao Pls Ql
23/65
_Introduo Programao PL/SQL ______________________marcuswlima@gmail.com
21Funes de grupo
6 Funes de grupo
De modo diferente das funes de uma nica linha, as funes de grupooperam em conjunto de linhas para fornecer um resultado por grupo. Essesconjuntos podem ser uma tabela inteira ou a mesma dividida em grupos menores.
Tabela 3.5. Funes de Grupo.Funo DescrioAVG Valor mdioCOUNT Nmero de linhasMAX Valor MximoMIN Valor MnimoSUM Soma de Valores
SQL>SQL>SELECT AVG(salary), MAX(salary), MIN(salary), SUM(salary)
2 FROM employees3 WHERE job_id LIKE '%REP%';
AVG(SALARY) MAX(SALARY) MIN(SALARY) SUM(SALARY)----------- ----------- ----------- -----------
8150 11000 6000 32600
SQL>SQL>SELECT MIN(hire_date), MAX(hire_date)2 FROM employees;
MIN(HIRE_DA MAX(HIRE_DA----------- -----------17-JUN-1987 29-JAN-2000SQL>SQL>SELECT COUNT(*)
2 FROM employees3 WHERE department_id = 50;
COUNT(*)----------
5SQL>SQL>SELECT COUNT(commission_pct)
2 FROM employees;
COUNT(COMMISSION_PCT)---------------------
4
Exemplo 6.1 Group by Tabela Inteira
atravz da clusula GROUP BY que dividimos as linhas de uma tabelaem grupo menores. Em seguida, poder ser aplicado a esses grupos formados asfunes de grupo, gerando assim informaes sumrias para cada grupo.
Primeiramente deve ser determinada a identificao do grupo. A identificaodo grupo pode ser uma coluna, vrias colunas, uma expresso usando colunas ouvrias expresses usando colunas. O Oracle considerar no grupo todas as linhasque atendenrem a clusula WHERE caso esta exista, e ento ser aplicada afuno de grupo ao grupo caso exista.
SELECT department_id, AVG(salary)FROM employeesGROUP BY department_id ;
SELECT department_id dept_id, job_id, SUM(salary)FROM employeesGROUP BY department_id, job_id ;
Exemplo 6.2 Group by criando grupos
Na figura 6.2 tempos 3(trs) exemplos de consulta utilizando funes degrupo. Na primeira o resultado ser agrupado pelo valor do campo department_id,e a cada grupo ser aferida a mdia aritmtica do campo salary. A quantidade de
-
5/21/2018 Program a Cao Pls Ql
24/65
_Introduo Programao PL/SQL ______________________marcuswlima@gmail.com
22Funes de grupo
linhas retornada no primeiro exemplo ser em funo da quantidade de valoresexistentes na coluna department_id. No segundo exemplo apresentamos um grupomais sofisticado com dois campos (department_id , job_id) e a estes grupos seraplicado a somatria do campo salary.
Diretrizes para Joins: Usando a clusula WHERE, linhas sero eliminadas antes de serem organizadas me
grupo. No permitido o uso de alias na clusula GROUP BY . Quando se deseja um campo esteja no retorno do SQL este dever estar na clusula
GROUP BY. possvel criar agrupamentos de mais de um campo. Funes de grupo no devem ser utilizadas na clusula WHERE e sim na clusula
HAVING.
Para se excluir um grupo inteiro criado pela clusula GROUP BY,deveremos usar a clusula HAVING, que executa um trabalho parecido com aclusula WHERE que elimina as linhas, este, no entanto, elimina grupos.
SQL>SELECT department_id, MAX(salary)2 FROM employees3 GROUP BY department_id4 HAVING MAX(salary)>10000 ;
DEPARTMENT_ID MAX(SALARY)------------- -----------
90 2640020 1300080 11000
SQL>SQL>SQL>SELECT job_id, SUM(salary) PAYROLL
2 FROM employees3 WHERE job_id NOT LIKE '%REP%'4 GROUP BY job_id5 HAVING SUM(salary) > 130006 ORDER BY SUM(salary);
JOB_ID PAYROLL---------- ----------IT_PROG 19200AD_PRES 50400
Exemplo 6.3 HAVING
-
5/21/2018 Program a Cao Pls Ql
25/65
_Introduo Programao PL/SQL ______________________marcuswlima@gmail.com
23Subconsultas
7 Subconsultas
Uma subconsulta uma instruo SELECT incorporada a outra instruoSELECT. O uso de subconsultas torna possvel a construo de sofisticadasinstrues e so teis quando precisamos selecionar linhas de uma tabela comuma condio que dependa dos dados na prpria tabela. Tambm podem serchamadas de subqueries ou consulta interna.
Tabela 7.1. Tipos de SubconsulltasTipo Descrio OperadoresSubquerie de uma nicalinha
O resultado da consulta internaretorna apenas uma linha
=, >, >=, 4 (SELECT salary5 FROM employees6 WHERE last_name = 'Abel')7 /
LAST_NAME-------------------------KingKochharDe Haan
SQL>SELECT last_name, job_id, salary2 FROM employees3 WHERE job_id =4 (SELECT job_id5 FROM employees6 WHERE employee_id = 141)7 AND salary >8 (SELECT salary9 FROM employees
10 WHERE employee_id = 143);
LAST_NAME JOB_ID SALARY------------------------- ---------- ----------Rajs ST_CLERK 3500Davies ST_CLERK 3100
Exemplo 7.1 Subquerie
-
5/21/2018 Program a Cao Pls Ql
26/65
_Introduo Programao PL/SQL ______________________marcuswlima@gmail.com
24Subconsultas
7.1 Operador IN
SQL>SELECT last_name, salary, department_id2 FROM employees3 WHERE salary IN (SELECT MIN(salary)4 FROM employees5 GROUP BY department_id);
LAST_NAME SALARY DEPARTMENT_ID------------------------- ---------- -------------Grant 7000De Haan 17000 90Kochhar 17000 90
SQL>SELECT last_name, salary, department_id2 FROM employees3 WHERE salary IN (7000,17000,6000,8300,2500,8600,4200,4400)4 /
LAST_NAME SALARY DEPARTMENT_ID------------------------- ---------- -------------Kochhar 17000 90De Haan 17000 90
Exemplo 7.2 Operador IN
7.2 Operador ANY
SELECT employee_id, last_name, job_id, salaryFROM employeesWHERE salary < ANY
(SELECT salaryFROM employeesWHERE job_id = 'IT_PROG')
AND job_id 'IT_PROG';
Obs.: < ANY(...)significa menor que o maior da lista e > ANY(...)significa mais do que o mnimo
7.3 Operador ALL
SELECT employee_id, last_name, job_id, salaryFROM employeesWHERE salary < ALL
(SELECT salaryFROM employeesWHERE job_id = 'IT_PROG')
AND job_id 'IT_PROG';
Obs.: > ALL(...)significa mais do que o maior valor listado e < ALL(...)significa manos do que omenor valor listado ca mais do que o mnimoObs.: Em uma instruo com subconsulta, a consulta interna a primeira a ser resolvida.
Obs: Em http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14200/toc.htmencontramos todas as possibildiades com o comando SQL disponveis nas verses Oracle10g
-
5/21/2018 Program a Cao Pls Ql
27/65
_Introduo Programao PL/SQL ______________________marcuswlima@gmail.com
25Manipulando dados (DML)
8 Manipulando dados (DML)
8.1 INSERT
INSERT INTO tabela [ (campo1, campo2, ..., campo n) ]values (valor_campo1, valor_campo2, ..., valor_campo n)
Figura 8.1 Insert
A instruo INSERT serve para adicionar linhas em uma determinadatabela. Como voc pode observar a lista de campos da tabela no obrigatria, noentanto, se voc optar por supri-la dever ter em mente a sua estrutura da tabela,pois dever fornec-los na mesma ordem.
SQL>insert into regions2 (region_id,region_name)3 values4 (5, 'Africa')5 /
1 linha criada.
SQL>SQL>insert into countries
2 values3 ('BR', 'Brasil',50)4 /
insert into countries*ERRO na linha 1:ORA-02291: restrio de integridade (HR.COUNTR_REG_FK) violada - chave me no
localizada
Exemplo 8.1 Insert
Obs.: encontramos todas as opes do comando INSERT disponveis nas verses Oracle10g em
http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_9014.htm#i2163698
8.2 UPDATE
UPDATE TabelaSET Campo1 = valor_campo1, campo2 = valor_campo2,
...[ WHERE ]
Figura 8.2 Update
A instruo UPDATE altera valores de campos de uma tabela, de acordocom uma condio fornecida, se esta condio for suprida, toda a tabela seratualizada. As regras que governam a restrio de linhas nas consultas sotambm aplicveis nas instrues UPDATE (Ver 3.1, 3.2, 3.3, 3.4).
-
5/21/2018 Program a Cao Pls Ql
28/65
_Introduo Programao PL/SQL ______________________marcuswlima@gmail.com
26Manipulando dados (DML)
SQL>update locations2 set city = 'Belm'3 where LOCATION_ID = 25004 /
x linha criada.
SQL>SQL>update locations
2 set country_id = 'AG'3 where LOCATION_ID = 14004 /
update locations*ERRO na linha 1:ORA-02291: restrio de integridade (HR.LOC_C_ID_FK) violada - chave me no localizada
Exemplo 8.2 Update
Obs.: encontramos todas as opes do comando UPDATE disponveis nas verses Oracle10g emhttp://download-east.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_10007.htm#i2067715
8.3 DELETE
DELETE {tabela} [ WHERE ]
Figura 8.3 Delete
A instruo DELETE exclui um ou mais registros de acordo com a condiofornecida, similarmente ao UPDATE se esta condio for suprida todos os dadosde sua tabela sero apagados!!!. As regras que governam a restrio de linhasnas consultas so tambm aplicveis nas instrues DELETE (Ver 3.1, 3.2, 3.3,3.4).
SQL>delete regions2 where region_id = 53 /
1 linha deletada.
SQL>delete locations2 where LOCATION_ID=25003 /
delete locations*ERRO na linha 1:ORA-02292: restrio de integridade (HR.DEPT_LOC_FK) violada - registro filho localizado
Exemplo 8.3 Delete
Obs.: encontramos todas as opes do comando DELETE disponveis nas verses Oracle10g em
http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_8005.htm#i2117787
-
5/21/2018 Program a Cao Pls Ql
29/65
_Introduo Programao PL/SQL ______________________marcuswlima@gmail.com
27Controle de transao
9 Controle de transao
Transao uma unidade lgica de trabalho que compreende uma ou maisinstrues DML executadas em funo das atividades de um usurio do sistema.O conjunto de DMLs que devem estar contidas em uma transao determinadopelas regras de nogcio.
O conjunto de inseres, alteraes e excluses efetivadas pelas instruesSQL pertencentes a uma transao pode ser confirmadas (COMMIT) oudesconsideradas (ROLLBACK). Uma transao se inicia com o primeiro comandoDML executado. A transao termina quando confirmada ou desconsiderada.
Para ilustrar o conceito de transao, podemos considerar o velhoexemplo de atividades de banco de dados para instituies finaceiras. Quando umcliente do banco transfere valores da conta poupana para a conta corrente, atransao deve consistir no mnimo de 3(trs) operaes.
Decrementar da conta poupana Incrementar na conta corrente Registrar a transao
O Oracle deve prover duas situaes. Se as trs operaes conseguiramser bem executadas afim de fornecer o adequado balanceamento nas contas,estas devero ser aplicadas (COMMIT) ao banco de dados. Entretanto se algumproblema como saldo insuficiente, nmero de conta invlida or falha de hardwareimpedir no mnimo uma atividade da transao, ento a transao inteira deverser desconsiderada (ROLLBACK) afim de assegurar o adequado balanceamentonas contas.
Um savepoint permite dividir uma transao em vrias partes e marcar umdeterminado ponto da transao que permitir ao programador um rollback totalonde toda a transao ser desnconsiderada ou rollback parcial onde tudo o quefoi executado aps o savepoint ser desconsiderado. Todo o savepoint tem umnome associado a ele.
1 BEGIN2 INSERT1;3 INSERT2;4 SAVEPOINT exemplo_transao;5 UPDATE1;6 UPDATE2;7 IF teste THEN8 ROLLBACK; (rollback total)9 ELSE
10 ROLLBACK TO exemplo_transao; (rollback parcial)11 END IF;12 END;
Figura 9.2 SavePoint
Os comandos COMMIT e ROLLBACK respectivamente confiram oudesconsirem as transaes segundo a lgica de programao imposta.
Obs.:No link a seguir encontramos a completa referncia do comando COMMIThttp://download-east.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_4010.htm#i2060233
Obs.:No link a seguir encontramos a completa referncia do comando ROLLBACK
http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_9021.htm#i2104635
Obs.:No link a seguir encontramos a completa referncia sobre transaes Oraclehttp://download-east.oracle.com/docs/cd/B19306_01/server.102/b14220/transact.htm#i6564
-
5/21/2018 Program a Cao Pls Ql
30/65
_Introduo Programao PL/SQL ______________________marcuswlima@gmail.com
28Bloco PL/SQL
Parte II - Programao PL/SQL
10 Bloco PL/SQL
PL/SQL(Procedural Language/SQL) a linguagem procedural desenvolvidapela Oracle que utilizada para montar os blocos PL/SQL.
Um bloco PL/SQL consiste de um conjunto de instrues SQL (SELECT,INSERT, UPDATE, DELETE) ou comandos PL/SQL, e desempenha uma funolgica nica, afim de resolver um problema especfico ou executar um conjunto detarefas afins. O Bloco PL/SQL tambm pode ser referenciado com Unidade dePrograma PL/SQL
Os blocos PL/SQL so qualificados em bloco annimo e Stored Procedure.
O bloco annimo No tem nome No est armazenado no SGDB Geralmente est armazenada na aplicao.
Stored SubProgramas Utiliza a estrutura do bloco annimo com base. Esto armazenados no SGDB, A eles atribudo um nome que poder ser utilizado nas aplicaes
ou por outros objetos do banco de dados
A estrutura de um bloco PL/SQL constituida de trs sees:a) SEO DE DECLAO (DECLARE) - Nesta seo so definidos os objetos
PL/SQL como variveis, constantes, cursores e excees definidas pelousurio que podero ser utilizadas dentro do bloco.
b) SEO DE EXECUES (BEGIN..END;) - Nesta seo contemplar asequncia de comandos PL/SQL e instrues SQL do bloco.
c) SEO DE TRATAMENTO DE ERRO (EXCEPTION) - Nesta seo serotratados os erros definidos e levantados pelo prprio bloco e os erros geradospela execuo do bloco (O captulo 12 abordar o tratamento de exees noPL/SQL)
[DECLARE-- declaraes]
BEGIN-- instrues e comnados
[EXCEPTION-- tratamentos de erro]
END;
Figura 10.1 Sees de um bloco PL/SQL
-
5/21/2018 Program a Cao Pls Ql
31/65
_Introduo Programao PL/SQL ______________________marcuswlima@gmail.com
29Bloco PL/SQL
Diretrizes: Apenas a seo de execuo obrigatria. As palavras chaves, DECLARE, BEGIN, EXCEOPTION no so seguidas por ponto-e-
vrgula, mas END e todas as outras instrues PL/SQL requerem ponto-e-vrgula. No existe bloco sem algum comando vlido. Pode existir aninhamento de bloco, no entanto, esta funcionalidade restrita seo de
Execuo e Seo de Tratamento de Erro. As lnhas da seo de execuo devem ser finalizadas com ; (ponto-e-vgula)
Maiores detalhes sobre a programao PL/SQL podero ser encontradasem http://download-east.oracle.com/docs/cd/B19306_01/appdev.102/b14261/toc.htm
-
5/21/2018 Program a Cao Pls Ql
32/65
_Introduo Programao PL/SQL ______________________marcuswlima@gmail.com
30Programao PL/SQL
11 Programao PL/SQL
11.1 Comentrios
Os comentrios em PL/SQL so de dois tipos
a) Uma Linha: utiliza-se o delimitador --. A partir de dois hfens tudo o que forescrito at o final da linha considerado comentrio.
b) Mltiplas linhas: utiliza-se o delimitador /* para abrir e */ para fechar. Tudo etodas as linhas que estiverem entre os dois delimitadores sero ignorados naexecuor.
1 BEGIN2 -- comentando apenas uma linha3 COMANDO1;4 /* comentando5 vrias6 linhas */7 COMANDO2;8 COMANDO3; -- o resto ser ignorado9 END;
10 /
Exemplo 11.1 Uso de comentrio
11.2 Declaraes (seo de declarao)
Para utilizar variveis e constantes no seu programa, voc deve declar-losanteriormente. na seo DECLARE que so declaradas as variveis econstantes.
1 DECLARE2 nVIVenda NUMBER(16,2);3 cNmVendedor VARCHAR2(40);4 dDtVenda DATE:=SYSDATE;5 mMultiplic CONSTANT NUMBER:=100; --constante6 BEGIN7 NULL;9 END;10 /
Exemplo 11.2 Declarao de Objetos
As declaraes no exemplo 2.2 foram: nVIVenda do tipo numrico tamanho 16 e 2 casas decimais, cNmVendedor do tipo numrico de tamanho varivel at 40
caracteres, dDTVenda do tipo data e a constante mMultiplic do tipo numrica com valor 100.
O escopo de uma varivel a parte do programa onde a varivel pode seracessada. Para uma varivel PL/SQL, isso ocorre a partir da declarao devariveis at o final do bloco. Variveis declaradas em um bloco externo soacessveis apenas neste bloco e em qualquer sub-bloco contido neste, pormvariveis declaradas no sub-bloco no so acessveis pelo bloco externo.
-
5/21/2018 Program a Cao Pls Ql
33/65
_Introduo Programao PL/SQL ______________________marcuswlima@gmail.com
31Programao PL/SQL
Pacote DBMS_OUTPUT
Na programao PL/SQL no existe nenhuma funcionalidade de entrada ou sada. Pararemediar isso, usaremos no aplicativo SQL*Plus o Supplied Package DBMS_OUTPUT quefornecer apenasa capacidade de dar sadas para mensagens na tela. Isso feito por meio de doispassos
1.Permitir a sada no SQL*Plus com o comando setserveroutputSET SERVEROUTPUT {ON | OFF}
2.Dentro do programa PL/SQL, utilize o procedure DBMS_OUTPUT.PUT_LINE. Essaprocedure adicionar o argumento informado ao buffer de sada.
Com esses passos completados, a sada impressa na tela do SQL*Plus depois que o blocofor completamente executado. Durante a execuo, o buffer preenchido pelas chamadas deDBMS_OUTPUT.PUT_LINE. O SQL*Plus no recupera o contedo do buffer e no o imprime atque o controle retorne para o SQL*Plus, depois que o bloco terminou a execuo.
SQL> SET SERVEROUTPUT ONSQL> BEGIN2 DBMS_OUTPUT.PUT_LINE(Hello from PL/SQL);3 END;
4 /Hello from PL/SQL
PL/SQL procedure successfully completed.
SQL>
11.3 Tipos de Dados
Tipo DescrioVARCHAR2[(tamanho_maximo)]
Tipo bsico para dados cararter de tamanho varivel com at 32.767bytes. No h tamanho default para as constantes e variveisVARCHAR2
NUMBER[(preciso,escala)]
Admite nmero de ponto fixo e flutuante.
DATE Tipo bsico para datas e horas. Valores DATE incluem a hora do diaem segundos desde a meia-noite. A faixa entre 01/01/4712 A.C e31/12/9999 D.C.
CHAR[(tamanho_maximo)]
Tipo bsico para dados cararter de tamanho fixo. Se voc noespecificar o tamanho_maximo, o tamanho default Ser definido com 1.
BOOLEAN Tipo bsico para dados que armazena um dos trs possveis valoresusados para clculos lgicos: TRUE, FALSE, NULL.
BINARY_INTEGER Inteiros com sinal entre -2.147.483.647 a 2.147.483.647
PL_INTERGER Inteiros com sinal entre -2.147.483.647 a 2.147.483.647. Valores comPL_INTERGER requerem menos espao e so mais rpidos que osvalore NUMBER e BINARY_INTERGER
O comando %TYPEnos d a possibilidade de associarmos ao tipo de umavarivel o tipo de uma coluna de uma tabela, desta forma, automaticamente avarivel assumir o tipo de dado da coluna.
O comando %ROWTYPEcriar uma estrutura de registro idntica estruturade uma tabela.
-
5/21/2018 Program a Cao Pls Ql
34/65
_Introduo Programao PL/SQL ______________________marcuswlima@gmail.com
32Programao PL/SQL
DECLARENome_varivel nome_tabela.nome_coluna%TYPE; -- varivelNome_registro nome_tabela%ROWTYPE; -- registro
BEGIN-- instrues e comnados
END;
Figura 11.1 Uso de %TYPE e %ROWTYPE
Nesta abordagem o cdigo fica mais ligado estrutura e no ser necessriaa reescrita do cdigo quando o tipo de coluna for alterado.
11.4 Assinalar Valores
Voc pode assinalar valores a uma varivel de duas formas. A primeira formautiliza o operador := (sinal de dois pontos seguido do sinal de igual). Assim avarivel posicionada esquerda do operador receber o valor da expressoposicionada direita.
1 DECLARE2 nSalario NUMBER;3 nSalarioAtual NUMBER;
4 aRegionName regions.region_name%TYPE;5 dHoje DATE;6 nAnoBi BOOLEAN:=TRUE;7 BEGIN8 nSalario := 400;9 nSalarioAtual := F_SALARY(103) * 0.10;
10 aRegionName := 'ASIA';11 dHoje := SYSDATE;12 END;13 /
Exemplo 11.3 Assinalar valores por operador
A Segunda forma de assinalar valor a uma varivel atravs de um resultadode SELECT que ser transferido assinalado varivel.
Um SELECT que assinala valor a uma varivel obrigatoriamente deverretornar uma e somente uma linha, caso contrrio, um erro de execuo ser
disparado, NO_DATA_FOUND se no for retornada nenhuma linha eTOO_MANY_ROWS se mais de uma linha for retornada (ver 12.3)
1 DECLARE2 bonus10 NUMBER;3 bonus20 NUMBER;4 emp_id NUMBER:=206;5 BEGIN6 SELECT salary * 0.107 INTO bonus108 FROM employees9 WHERE employee_id = emp_id;
1011 SELECT salary * 0.10, salary * 0.2012 INTO bonus10, bonus2013 FROM employees14 WHERE employee_id = emp_id;1516 DBMS_OUTPUT.PUT_LINE('SALARIO COM 10% DE BONUS :'|| bonus10);
17 DBMS_OUTPUT.PUT_LINE('SALARIO COM 20% DE BONUS :'|| bonus20);18 END;19 /
Exemplo 11.4 Assinalar valores por SELECT
11.5 Contro le de Fluxo
Este conjunto de comandos permite testar uma condio e, dependendo se acondio falsa ou verdadeira, ser tomada uma determinada direo de fluxo. Ocontrole de fluco se d em trs formas: IF-THEN, IF-THEN-ELSE,IF-THEN-ELSIF.
-
5/21/2018 Program a Cao Pls Ql
35/65
_Introduo Programao PL/SQL ______________________marcuswlima@gmail.com
33Programao PL/SQL
11.5.1 IF-THEN a forma mais simples. Testa a condio especificada aps o IF e, caso seja
verdadeira, executa o comando alm do THEN. Caso no seja, executa as aesaps o END IF (note que devem ser escritos separados).
1 DECLARE2 v_first_name employees.first_name%TYPE;3 v_salary employees.salary%TYPE;4 BEGIN5 SELECT first_name,salary6 INTO v_first_name,v_salary7 FROM employees8 WHERE employee_id = 142;9
10 IF v_salary > 3000 THEN11 DBMS_OUTPUT.put_line('Salario acima de US$ 3,000');12 DBMS_OUTPUT.put_line('Teste IF-THEN');13 END IF;14 END;15 /
Exemplo 11.5 IF-THEN
Obs.:Note que cada linha de ao dentro do IF deve ser terminada com ponto-e-vrgula(;) e apenasaps o END IF que se coloca o ponto-e-vrgula final do comando IF.
11.5.2 IF-THEN-ELSEAqui, acrescenta-se a palavra-chave ELSE para determinar o que deve ser
feito caso a condio seja falsa. Dessa forma, o fluxo seguir para os comandosaps o THEN caso a condio seja verdadeira, e aps o ELSE caso seja falsa.
1 DECLARE2 v_first_name employees.first_name%TYPE;3 v_commission_pct employees.commission_pct%TYPE;4 BEGIN5 SELECT first_name,commission_pct6 INTO v_first_name,v_commission_pct7 FROM employees8 WHERE employee_id = 174;9
10 IF v_commission_pct IS NULL THEN11 DBMS_OUTPUT.put_line('Sem comisso');12 DBMS_OUTPUT.put_line('outra ao');13 ELSE14 DBMS_OUTPUT.put_line('Comisso de '||v_commission_pct*100||'%');15 DBMS_OUTPUT.put_line(' outra ao ');16 END IF;17 END;18 /
Exemplo 11.6 IF-THEN-ELSE
Obs.:Note que h um ponto-e-vrgula somente aps cada linha de ao e aps o END IF.
11.5.3 IF-THEN-ELSIFQuando se deseja testar diversas condies utilizando um mesmo IF, utiliza-
se ELSIF. Assim, pode-se aps cada ELSIF, testar nova condio que, caso seja
verdadeira, executar as respectivas aes.
-
5/21/2018 Program a Cao Pls Ql
36/65
_Introduo Programao PL/SQL ______________________marcuswlima@gmail.com
34Programao PL/SQL
1 DECLARE2 Vencimentos NUMBER;3 BEGIN4 vencimentos := F_SALARY(101); -- dedues5 IF vencimentos 10000 AND vencimentos 15000 AND vencimentos =5;9 END LOOP;
10 END;11 /
Exemplo 11.9 LOOP simples
11.6.2 WHILE-LOOPEste comando permite testar uma condio antes de iniciar a seqncia de
aes de repetio. Ao final de LOOP, testada a condio novamente e, caso
-
5/21/2018 Program a Cao Pls Ql
37/65
_Introduo Programao PL/SQL ______________________marcuswlima@gmail.com
35Programao PL/SQL
verdadeira, continua a seqncia de aes dentro do LOOP ou sai, caso sejafalsa, executando o que estiver aps o END LOOP.
1 DECLARE2 x NUMBER;3 BEGIN4 X := 1;5 WHILE X < 5 LOOP
6 X := X + 1;7 END LOOP;8 END;9 /
Exemplo 11.10 WHILE - LOOP
11.6.3 FOR - LOOPUtilize este comando sempre que voc souber preveamente o nmero de
vezes que um LOOP deve ser executado. A cada comando FOR-LOOP existe umavarivel controladora que em cada interao assumir todos os valores interiros(variando de 1 em 1) contidos entre o limite inicial e o limite final.
FOR c o n t a d o r IN [REVERSE] i n i c i o ..fimLOOPcomando1;
comando2;END LOOP;
c o n t a d o r Varivel que ter seu valor incrementado.[REVERSE] Indica que se deve diminuir ao invs de aumentar O
contador. O valor de inicio deve ser maior que ofim, pois o valor ser decrescido a cada repetio
i n i c i o intervalo inicial de repetiofim intervalor final da repetio
Figura 11.2 FOR-LOOP
1 DECLARE2 Y NUMBER := 1;3 BEGIN4 FOR X IN 1..5 LOOP5 Y := Y + X ;6 DBMS_OUTPUT.PUT_LINE(x);7 END LOOP;8 END;9 /
Exemplo 11.11 FOR-LOOP.
No necessidade de declarar a varivel controladora, isso feitoimplicitamente pelo comando FOR-LOOP. Podemos utilizar a varivel controladoracomo uma varivel normal, no entanto, no podemos assinalar valores varivelcontroladora. O escopo de visibilidade na varivel controladora apenas dentro dolao.
Caso o nmero de vezes que deva ser repetida a seqncia de aes sejafruto de um clculo, voc poder substituir tanto o intervalo superior quanto osuperior por variveis, mas no se esquea que esses valores devem ser semprenmeros inteiros.
Obs.: Voc poder utilizar o EXIT WHEN condio para terminar prematuramente o FOR-LOOP.
11.7 Labels
Os labels so utilizados para melhorar a leitura do programa PL/SQL. Labelsso aplicados a blocos ou LOOPs. Um label deve preceder imediatamente umbloco ou LOOP e deve ser delimitado por . A clusula END ou END LOOP
-
5/21/2018 Program a Cao Pls Ql
38/65
_Introduo Programao PL/SQL ______________________marcuswlima@gmail.com
36Programao PL/SQL
pode fazer referncia do label. O uso de labels vantajoso quando existem vriosblocos aninhados.
1 DECLARE2 v_dept NUMBER(2);3 v_emp_count NUMBER(2);4 BEGIN5
6 FOR cont IN 1..6 LOOP7 8 BEGIN9 SELECT DEPARTMENT_ID
10 INTO v_dept11 FROM DEPARTMENTS12 WHERE DEPARTMENT_ID = cont * 10;13 14 BEGIN15 SELECT count(*)16 INTO v_emp_count17 FROM EMPLOYEES18 WHERE DEPARTMENT_ID = v_dept;19 END bloco_contador;20 DBMS_OUTPUT.PUT_LINE('Existe(m) '||v_emp_count||' empregados no departamento
'||v_dept);21 END bloco_selecao;22 END LOOP seis_tentativas;23 END;24 /
Exemplo 11.12 Labels
11.8 Cursores
Em alguns casos necessitamos de espaos de armazenamento maiscomplexos que as variveis, como uma matriz de informao resultada de umaconsulta SQL, neste case se faz necessrio o uso de cursores.
Os cursores em PL/SQL podem ser explcitos e implcitos. O PL/SQL declaraum cursor implicitamente para toda instruo DML (UPDATE, INSERT, DELETE,SELECT...INTO), incluindo consultas que retornam apenas uma linha. Asconsultas que retornam mais de uma linha devero ser declaradas explicitamente.
Cursores explcitos so indicados quando necessrio um controle no
processamento do mesmo.
11.8.1 Controlando Cursores ExplcitosDe acordo com a figura 11.3, quatro so os comandos que controlam o
processamento de um cursor.
Figura 11.3 Controle de Processamento de Cursores
11.8.2 Declarando um Cursor (DECLARE)Quando declaramos um cursor associado a ele um nome e a consulta SQL
que ser processada por este cursor. Assim como as variveis, os cursores devemser declarados na seo DECLARE.
O escopo de validade dos cursores o mesmo de uma varivel (ver 11.2).Cursores declarados em um bloco externo so acessveis apenas neste bloco eem qualquer sub-bloco contido neste, porm cursores declarados no sub-blocono so acessveis pelo bloco externo.
-
5/21/2018 Program a Cao Pls Ql
39/65
_Introduo Programao PL/SQL ______________________marcuswlima@gmail.com
37Programao PL/SQL
CURSOR nome_cursor[(parametro1 tipo,parametro2 tipo,...,,parametroN tipo)]
IS Instruo_SQL;
Figura 11.4 Declarando um cursor
Os cursores podem ser definidos com parmetros e para cada parmetrodevem ser escolhidos um nome e um tipo de dado(ver 11.3).
11.8.3 Abr indo um Cursor (OPEN)O comando OPEN abre o cursor, executa a consulta associada a ele e gera o
conjunto ativo, que consiste de todas as linhas que atendem os critrios depesquisa da consulta associada ao cursor. Para gerenciar o conjunto ativo existeum ponteiro que registra qual linha est passvel do comando FETCH. Aps oOPEN o FETCH atuar sobre a primeira linha do conjunto ativo.
OPEN nome_cursor[(var1,var2,...)];
Figura 11.5 Abrindo um cursor
11.8.4 Ext raindo dados do Cursor (FETCH)Extrair os dados do cursor o evento onde os dados da linha atual do
conjunto ativo so copiados para variveis ou registros e a cada FETCH realizado,o ponteiro passar a apontar para a linha seguinte do conjunto ativo.
FETCH nome_cursor INTO [var1,var2,...|record_name];
Figura 11.6 Fetch Cursor
Diretrizes Inclua o mesmo nmero de variveis na clusula INTO da instruo FETCH do que as
colunas na instruo SELECT e certifique-se que os tipos de dados so compatveis Faa a correspondncia de cada varivel para coincidir com as posies das colunas Registros podem ser utilizados. O tipo %ROWTYPE pode ser associado ao cursor ou
diretamente a uma tabela. Os campos do cursor devem ser idnticos aos campos doregistro usado em quantidade e tipo
11.8.5 Fechando do Cursor (CLOSE)O comando CLOSE desativa o cursor e libera o conjunto ativo. Esta etapa
permite que o cursor seja reaberto, se necessrio, para gerar um outro conjuntoativo.
CLOSE nome_cursor;
Figura 11.6 Fechando um cursor
-
5/21/2018 Program a Cao Pls Ql
40/65
_Introduo Programao PL/SQL ______________________marcuswlima@gmail.com
38Programao PL/SQL
1 DECLARE2 V_empno NUMBER;3 V_ename VARCHAR2(100);4 CURSOR cEmplyee IS5 SELECT employee_id,first_name6 FROM EMPLOYEES;7 rEmployee cEmplyee%ROWTYPE;8 CURSOR cEmplyeeJob9 (p_job varchar)
10 IS11 SELECT first_name12 FROM EMPLOYEES13 WHERE Job_id = p_job;14 BEGIN15 OPEN cEmplyee;16 FETCH cEmplyee INTO V_empno, V_ename;17 DBMS_OUTPUT.PUT_LINE(V_ename);18 FETCH cEmplyee INTO V_empno, V_ename;19 DBMS_OUTPUT.PUT_LINE(V_ename);20 FETCH cEmplyee INTO V_empno, V_ename;21 DBMS_OUTPUT.PUT_LINE(V_ename);22 FETCH cEmplyee INTO rEmployee ;23 DBMS_OUTPUT.PUT_LINE(rEmployee.first_name);24 CLOSE cEmplyee;2526 OPEN cEmplyeeJob('SALESMAN');27 FETCH cEmplyeeJob INTO V_ename;28 DBMS_OUTPUT.PUT_LINE(V_ename);29 FETCH cEmplyeeJob INTO V_ename;30 DBMS_OUTPUT.PUT_LINE(V_ename);31 CLOSE cEmplyeeJob;32
33 OPEN cEmplyeeJob('MANAGER');34 FETCH cEmplyeeJob INTO V_ename;35 DBMS_OUTPUT.PUT_LINE(V_ename);36 FETCH cEmplyeeJob INTO V_ename;37 DBMS_OUTPUT.PUT_LINE(V_ename);38 CLOSE cEmplyeeJob;39 END;40 /
Exemplo 11.13 Cursores
Obs: No exemplo acima, as linhas 8 at 13 mostram a declarao de um cursor com parmetro enas linhas 15, 26 e 33 mostra o open do cursor. A linha 7 mostra um registro recebendo aestrutura de linha de um cursor (isso poderia ser feito a uma tabela) e as linhas 22 e 23 mostram ofetch para o registro e o uso do valor do registro
11.8.6 Atr ibutos do Cursor Explcito
Quando anexados ao nome do cursor, esses atributos retornaminformaes teis sobre a execuo de uma instruo de manipulao de dados.
Atr ibuto Tipo Descr io%ISOPEN Booleano Ser avaliado para TRUE se o cursor estiver aberto%NOTFOUND Booleano Ser avaliado para TRUE se a extrao mais recente no
retornar linha.%FOUND Booleano Ser avaliado para TRUE se a extrao mais recente retornar
linha.%ROWCOUNT Numerico Ser avaliado para o nmero total de linhas retornadas at o
momento.
-
5/21/2018 Program a Cao Pls Ql
41/65
_Introduo Programao PL/SQL ______________________marcuswlima@gmail.com
39Programao PL/SQL
11.8.7 LOOP Simples X CursorNeste primeiro estilo de loop de busca, a sintaxe de loop simples utilizada
para processamento do cursor. Atributos explcitos de cursor so utilizados paracontrolar o nmero de vezes que o loop executado.
1 DECLARE2 CURSOR cEmpregados IS
3 SELECT first_name FROM employees;4 aName employees.first_name%TYPE;5 BEGIN6 OPEN cEmpregados;7 LOOP8 FETCH cEmpregados INTO aName;9 EXIT WHEN cEmpregados%NOTFOUND;
10 DBMS_OUTPUT.PUT_LINE(aName);11 END LOOP;12 CLOSE cEmpregados;13 END;14 /
Exemplo 11.14 Cursores Loop Simples
11.8.8 LOOP While X CursorO mesmo Exemplo 11.14 poderia ser escrito utilizando a sintaxe
WHILE..LOOP, da seguinte maneira.
1 DECLARE2 CURSOR cCidades IS3 SELECT * FROM locations;4 rCity locations%ROWTYPE;5 BEGIN6 OPEN cCidades;7 FETCH cCidades INTO rCity;8 WHILE cCidades%FOUND LOOP9 DBMS_OUTPUT.PUT_LINE(rCity.city||' - '||rCity.state_province);
10 FETCH cCidades INTO rCity;11 END LOOP;12 CLOSE cCidades;13 END;14 /
Exemplo 11.15 Cursores Loop While
11.8.9 LOOP For X CursorOs dois exemplos de LOOPs descritos anteriormente requerem umprocessamento explcito de cursor por meio de instrues OPEN, FETCH, CLOSEver (11.8.1). A programao PL/SQL fornece um tipo de LOOP mais eficiente, quetrata implicitamente o processamento de cursor.
1 DECLARE2 CURSOR cCargos IS3 SELECT job_title, job_id4 FROM jobs;5 BEGIN6 FOR rCargo IN cCargos LOOP7 DBMS_OUTPUT.PUT_LINE(rCargo.job_id||' - '||rCargo.job_title);8 END LOOP;9 END;
10 /
Exemplo 11.16 Cursor FOR
Observaes O registro rCargo no declarado, sua declarao executada implicitamente,
recebe o tipo cCargos%ROWTYPE e o seu escopo apenas o LOOP. O cursor cCargos processado implicitamente, sendo desnecessrio os
comandos OPEN, FETCH, CLOSE.
-
5/21/2018 Program a Cao Pls Ql
42/65
_Introduo Programao PL/SQL ______________________marcuswlima@gmail.com
40Programao PL/SQL
11.8.10 LOOP For Implc itosAlm do registro, o prprio cursor pode ser implicitamente declarado. A
consulta SQL geradora do conjunto ativo apresentada em de parnteses dentroda prpria instruo FOR, e neste caso, tanto o registro com o cursor soimplicitamente declarados.
1 BEGIN2 FOR rDepartamento IN (SELECT d.department_id, d.department_name3 FROM departments d) LOOP4 DBMS_OUTPUT.PUT_LINE(rDepartamento.department_name);5 END LOOP;6 END;7 /
Exemplo 11.17 Cursor FOR Implcito
11.8.11 Cursores ImplcitosExistem os cursor implcitos que so criados para processar as instrues
INSERT, UPDATE, DELETE, SELECT...INTO e so manipulados a revelia doprogramador. Neste caso apenas o atributo %ROWCOUTN interessante para ainstruo UPDATE. O cursor implcito representado pela palavra reservada SQL.
1 BEGIN2 UPDATE jobs3 SET MAX_SALARY = MAX_SALARY+1004 WHERE MAX_SALARY < 9000;56 DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT||' linhas salarios foram atualizadas');7 END;8 /
Exemplo 11.18 Cursor Implcito
-
5/21/2018 Program a Cao Pls Ql
43/65
_Introduo Programao PL/SQL ______________________marcuswlima@gmail.com
41Tratamento de Exceo
12 Tratamento de Exceo
Qualquer boa linguagem de programao deve ter a capacidade de tratarerros de run-time e, se possvel, recuperar-se deles. A programao PL/SQLimplementa essa funcionalidade por meio de excees PL/SQL e tratamento deexcees.
Para se entender como o PL/SQL trabalha com os tratamentos de exees,ser necessrio o entendimento dos seguintes conceitos:
Erro de run-time ou exeo situao adversa que ocasiona interrupona execuo do programa podendo ser motivada por falha na concepodo sistema, codificao equivocada, falha de hardware, ou outro motivo.
Exceo PL/SQL o objeto de programao PL/SQL que nos permiteevitar a interrupo abrupta do programa caso este seja acomedido de umerro de run-time.
Tratamento da exeo Indica qual ao ou quais aes devero sertomadas quando o programa for acomedido de um erro de run-time.
Quando o Oracle apresenta um erro de run-time ou exeo, este sempre seracompanhado de um cgido uma mensagem. Todos os possveis erros de run-time ou excees podem ser consultados na lista de erros Oracle em http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14219/toc.htm . A cada cgido est associadoalm de uma mensagem, uma causa e aes a serem tomadas. Essasinformaes so extremamente importantes no dia-a-dia de um programadorPL/SQL.
Quando criamos um programa, a rigor no sabemos, antes de coloc-lo emproduo quais erros de run-time ou exceo podem ocorrer no momento daexecuo. No entanto, em alguns casos podemos vislumbrar ou imaginar umconjunto pequeno de erros de run-time ou exceo que podero ocorrer naexecuo. A qualidade do programa se dar em funo da habilidade que o
programa ter para tratar os eventuais erros. Para cada erro vislumbrado ouimagnado dever existir um tratamento espefico, e isso perfeitamenteprogramvel no PL/SQL.
12.1 Tratando X Propagando
Quando um erro de run-time ou exeo ocorre, o controle do bloco PL/SQLdeixa a seo de execuo(BEGIN) e passa compulsivamente para a seo deexceo(EXCEPTION). Se existe algum tratamento especfico para o erro de run-time (atravz de um objeto EXCEPTION), este ser capturado e tratado (seexistir o devido tratamento) pelo bloco e a execuo do bloco terminar semapresentar o erro de run-time (isso no quer dizer que o bloco executou com
sucesso todas as suas atividades). Se por outro lado, este tratamento no existe,ou no bloco no existe a seo EXCEPTION, ento o bloco propagar outransferiro erro de run-time para bloco que o contm ou para o ambiente que oexecutou.
-
5/21/2018 Program a Cao Pls Ql
44/65
_Introduo Programao PL/SQL ______________________marcuswlima@gmail.com
42Tratamento de Exceo
BEGIN
comando1;comando2;BEGINcomando3;comando4;
EXCEPTIONtratamento1;tratamento2;
END bloco_de_dentro;Comando5;Comando6;
EXCEPTIONtratamento3;tratamento4;
END bloco_de_fora;
Figura 12.1 Capturando X Propagando
Tabela 12.1. Tabela de Comportamento.Premissa p/ tratamento Quando Tratado Quando Propagado
tratamento1 e tratamento2serem capazes de capturartodo e qualquer erro deruntime gerado porcomando3 ou comando 4
O comando5 executado, dandocontinuidade execuo.
O recebe o run-time e ficaencaregado de propagarou capturar e oscomando5 e comando6no so executados.
tratamento3 e tratamento4serem capazes de capturartodo e qualquer erro deruntime gerado porcomando1, comando2 ou
Procedimentoconcludo comsucesso
O ambiente queexecutou o bloco mostrao cdigo de erro.
12.2 Tratamento de Excees
atravez do objeto exeo PL/SQLque temos a possibilidade de qualificaras exees e aplic-las o devido tratamento. na seo de exceo que ocorremos tratamentos de erros de run-time como ilustrado pelo exemplo 12.1.
Para cada exeo provvel dever exisitr um tratamento atravz de umaclusula WHEN que estar associada a uma exeo PL/SQL, seguida por umaseqncia de instrues a serem executadas quando o run-time ocorrer, querepresentam efetivamento o tratamento do erro de run-time.
A clusula WHEN OTHERS, se usada, deve ser posicionada com ltimotratamento, e utilizada para tratar alguma exeo que no encontrar o devidotratamento nas clusulas WHEN. Devemos pensar a seo de exceo como umcomando IF, ELSIF, ELSE. Cada WHEN corresponde a um IF ou ELSIF, e WHENOTHERS corresponde ao ELSE.
-
5/21/2018 Program a Cao Pls Ql
45/65
_Introduo Programao PL/SQL ______________________marcuswlima@gmail.com
43Tratamento de Exceo
1 DECLARE2 v_employee_id employees.employee_id%TYPE;3 v_first_name employees.first_name%TYPE:='Steven';4 BEGIN5 SELECT employee_id6 INTO v_employee_id7 FROM employees8 WHERE first_name = v_first_name;9 EXCEPTION
10 WHEN NO_DATA_FOUND THEN11 DBMS_OUTPUT.PUT_LINE('Primeiro nome no encontrado');12 WHEN TOO_MANY_ROWS THEN13 DBMS_OUTPUT.PUT_LINE('Retornou mais de uma linha');14 WHEN OTHERS THEN15 DBMS_OUTPUT.PUT_LINE('Problemas ao executar o procedimento!!!');15 END;16 /
Exemplo 12.1 Tratamento de Erros
No exemplo 12.1 apresenta um programa com uma instruo SELECT(linha5) e trs tramatentos de exeo (linhas 10, 12 e 14). Sendo dois tratamentosespecficos para determinadas exees e o terceiro para tratar toda e qualquerexceo inesperada. Desta forma o programa sempre terminar a execuo semapresentar erro algum.
Existem 2(dois) tipos de objetos exeo PL/SQL Pr-definidos ou internos Definido pelo usurio
Diretrizes A palavra EXCEPTION inicia a seo de tratamento de excees. So permitidos vrios tratamentos de exceo Somente um tratamento processado antes de se sair do bloco WHEN OTHERS se existir sempre deve ser o ltimo tratamento.
12.3 Excees PL/SQL Pr-definidas ou internas
As exees pre-definidas so disparados automaticamente pelo programa
quando este gera um erro de run-time. Do grupo de erros Oracle catalogados,existe um grupo menor para as quais foram criadas excees pr-definidas ouinternas que podem ser tratadas diretamente na seo EXCEPTION, sem anecessidade de declarao na seo DECLARE.
Os erros de run-time que tm exceo associada podem ter um tratamentoespecfico e previlegiado atravs da clusula WHEN, os demais, que formam omaior grupo, s podero ser tratados na clusula OTHERS. Veremos a frente comremediar isso (ver 12.6)
Na tabela 12.2 mostra os mais recorrentes e em http://download-east.oracle.com/docs/cd/B14117_01/appdev.101/b10807/07_errs.htm#i7014 encontram-se todas as excees pr-definidas
Tabela 12.2. Excees Pr-definidas.Exeption OracleError Raise whenDUP_VAL_ON_INDEX ORA-00001 O seu programa tentou armazenar valores duplicados em
uma coluna com restrio de UNIQUEINVALID_CURSOR ORA-01001 O seu programa tentou um operao ilegal de cursores
como fechar um cursos j fechado.NO_DATA_FOUND ORA-01403 Uma instruo SELECT INTO retornou nenhuma linha.TOO_MANY_ROWS ORA-01422 Uma instruo SELECT INTO retornou mais de uma linha.ZERO_DIVIDE ORA-01476 O seu programa tentou efetuar uma diviso por 0(zero)
-
5/21/2018 Program a Cao Pls Ql
46/65
_Introduo Programao PL/SQL ______________________marcuswlima@gmail.com
44Tratamento de Exceo
12.4 Excees PL/SQL definidas pelo Usurio
Para que uma exceo seja definida pelo programador, esta deve serdeclarada explicitamente na seo DECLARE e so acionadas atravs docomando RAISE.
O escopo de validade deste tipo de exceo o mesmo de uma varivel (ver
11.2). Excees declaradas em um bloco externo so acessveis apenas nestebloco e em qualquer sub-bloco contido neste, porm excees declaradas no sub-bloco no so acessveis pelo bloco externo.
1 DECLARE2 e_Emp_Invalido EXCEPTION;3 v_sal employees.salary%TYPE:=2000;4 v_employee_id employees.employee_id%TYPE:=5;5 BEGIN6 UPDATE employees7 SET salary = v_sal8 WHERE employee_id = v_employee_id;9 IF SQL%NOTFOUND THEN
10 RAISE e_Emp_Invalido;11 END IF;12 EXCEPTION13 WHEN e_Emp_Invalido THEN14 DBMS_OUTPUT.PUT_LINE('Este funcionario naum existe');
15 END;16 /
Exemplo 12.2 Exceo declarada
No exemplo 12.2.o programa declara a exeo PL/SQL e_emp_invlido nalinha 2, aciona a exeo na linha 10 e a trata na linha 13. Percebam que otratamento dado s exees PL/SQL definidas pelo usurio identica aotratamento dado s exees PL/SQL pr-definidas.
Exees PL/SQL definidas so uteis para tratar sutiaes relativa ao negciocomo saldo insuficiente, cliente j cadastradoou cdigo j utilizado.
Obs.: Se uma exceo definida pro usurio for disparada e o devido tratamento no existir na seoEXCEPTION o bloco retornar o seguinte erro ORA-06510: PL/SQL: exceo no-manipulada
definida pelo usurio
12.5 Comando RAISE_APLLICATION_ERROR
Em alguns casos se faz necessrio forar um erro de run-time. Para issousamos o comando RAISE_APPLICATION_ERROR, que nos permite interrompera execuo de um programa, gerando erro de run-time e assinal-lo um cdigo euma mensagem. Para usarmos RAISE_APPLICATION_ERROR devemos utilizar aseguinte sintaxe:
raise_application_error(error_number, message);
Figura 12.1 Tratamento de Erros
onde error_number um numero negativo e inteiro na faixa de -20999 a -20000e message a mensagem customizada pelo programador.
-
5/21/2018 Program a Cao Pls Ql
47/65
_Introduo Programao PL/SQL ______________________marcuswlima@gmail.com
45Tratamento de Exceo
1 DECLARE2 nQdt NUMBER;3 BEGIN4 SELECT COUNT(*)5 INTO nQdt6 FROM employees;7 IF nQdt < 100 THEN8 RAISE_APPLICATION_ERROR(-20000,'Ainda no Existem 100 funcionarios');9 END IF;
10 END;11 /
Exemplo 12.3 RAISE_APPLICATION_ERROR
Obs.: Excees geradas por RAISE_APPLICATION_ERROR no estaro na lista erros Oracle esero capturadas e tratadas na clusula WHEN OTHERS.
12.6 Pragma EXCEPTION_INIT
Para tratar os erros de run-time que no tem exceo PL/SQL pr-definida(ver 12.3), deveramos usar a clusula OTHERS ou PRAGMA EXCEPTION_INIT.Nesta segunda abordagem, o compilador associa uma exceo declarada pelousurio (ver 12.4) com um cdigo de erro de run-time mapeado na lista de erros
Oracle.
1 DECLARE2 e_emp_remain EXCEPTION;3 PRAGMA EXCEPTION_INIT(e_emp_remain,-2292);4 V_department_id departments.department_id%TYPE:=60;5 nQdt NUMBER;6 BEGIN7 DELETE departments8 WHERE department_id = V_department_id;9 IF SQL%NOTFOUND THEN
10 RAISE_APPLICATION_ERROR(-20001,'O depatamento '||V_department_id||' noexiste');
11 END IF;12 EXCEPTION13 WHEN e_emp_remain THEN14 SELECT COUNT(*)15 INTO nQdt16 FROM employees
17 WHERE department_id = V_department_id;18 DBMS_OUTPUT.PUT_LINE('No possivel a remoo do departamento '|| V_department_id||'. Nele existe(m) '||nQdt||' funcionario(s)');
19 END;20 /
Exemplo 12.4 Pragma EXCEPTION_INIT
No exemplo 12.4 o comando PRAGMA EXCEPTION_INIT (linha 3) associaORA-2292 exeo PL/SQL e_emp_remain declarada na linha 2. Apartir destaassociao os erros de run-time ORA-2292 podero ser tratados pela exeoPL/SQL e_emp_remain(linha 13).
Esta modalidade de tratamento interessante para os erros de chaveestrangeira ORA-02292: restrio de integridade violada - registro filhoLocalizado e ORA-02291: restrio de integridade violada - chave me no
localizada.
12.7 SQLCODE, SQLERRM
Quando ocorre um erro de run-time ocorre, voc pode identificar o cdigo e amensagem do erro associado atravs das funes SQLCODE e SQLERRM. Aobteno do cdigo erro e consulta na lista de erros Oracle ajuda na resoluo deerros de programao.
-
5/21/2018 Program a Cao Pls Ql
48/65
_Introduo Programao PL/SQL ______________________marcuswlima@gmail.com
46Tratamento de Exceo
Tabela 12.3. SQLCODE, SQLERRMFuno DescrioSQLCODE Retorna o nmero de cdigo de erroSQLERRM Retorna os dados de caracteres que contm a mensagem associada ao nmero
de erro
1 DECLARE
2 V_country_id countries.country_id%TYPE:='CA';3 BEGIN4 DELETE countries5 WHERE country_id = V_country_id;6 EXCEPTION7 WHEN OTHERS THEN8 DBMS_OUTPUT.PUT_LINE('Cdigo -> '||SQLCODE);9 DBMS_OUTPUT.PUT_LINE('Mensagem -> '||SQLERRM);
10 END;11 /
Exemplo 12.5 SQLCODE, SQLERRM
-
5/21/2018 Program a Cao Pls Ql
49/65
_Introduo Programao PL/SQL ______________________marcuswlima@gmail.com
47Stored Subprograms
Parte III - Objetos Procedurais
13 Stored Subprograms
Subprogramas so compilados e armazenados no banco de dados Oracle,esto disponveis para leitura e execuo. Uma vez compilados se tornam objetosde schema na forma de stored procedure ou stores funciton, que podem seracessados pelos usurios e aplicaes conectados do banco de dados.
O comando CREATE [OR REPLACE] PROCEDURE nos permite criar umaprocedure no banco de dados e o comando CREATE [OR REPLACE] FU
top related