Tutorial de PL/SQLPara Oracle 9 por Miguel Rodrigues Fornari

Sumrio1. Introduo....................................................................................................................3 2. Estrutura geral de um programa PL/SQL...................................................................3 2.1 Execuo de PL/SQL............................................................................................4 3. Variveis e constantes.................................................................................................4 3.1 Vetores...................................................................................................................5 3.2 Registros................................................................................................................6 3.3 Atribuio de valores variveis..........................................................................6 3.4 Declarao de Constantes.....................................................................................6 4. Cursores.......................................................................................................................7 4.1 Laos FOR em Cursores.......................................................................................8 4.2 Cursores Dinmicos..............................................................................................9 4.2.1 Manipulao de cursores dinmicos............................................................10 5. Estruturas de Controle de Fluxo................................................................................10 5.1 Comando IF.........................................................................................................11 5.2 Comando CASE..................................................................................................11 5.3 Laos de iterao.................................................................................................11 5.3.1 Comando FOR-LOOP.................................................................................11 5.3.2 Comando WHILE-LOOP............................................................................12 5.3.3 Comando GOTO..........................................................................................12 6. Modularidade.............................................................................................................12 6.1 Subprogramas......................................................................................................13 6.2 Pacotes.................................................................................................................14 6.3 Tratamento de excees......................................................................................15 7. Funes pr-definidas...............................................................................................17 7.1 Funes de valor simples....................................................................................17 7.1.1 Funes numricas.......................................................................................18 7.1.2 Funes de caracteres retornando caracteres...............................................18 7.1.3 Funes de caracteres que retornam valores numricos............................19 7.1.4 Funes de data............................................................................................19 7.1.5 Funes de converso..................................................................................20 8. Visualizao de valores na tela.................................................................................20

1. IntroduoEste tutorial procurar resumir brevemente os principais pontos de PL/SQL, para o SGBD Oracle, verso 9. No se trata de material completo, com todos os detalhes de sintaxe e semntica. apenas um complemento para as aulas da disciplina de Bancos de Dados II, do curso de Sistemas de Informao, na ULBRA. Se o leitor necessitar, sugiro o material disponvel no site Technet-Oracle (http://technet.oracle.com), especialmente o manual de usurio do PL/SQL (http://technet.oracle.com/docs/products/oracle9i/doc_library/release2/appdev.920/a96624/to c.htm), do qual os exemplos foram copiados e alguns trechos traduzidos.

2. Estrutura geral de um programa PL/SQLUm modo interessante de entender PL/SQL olhando inicialmente um programa exemplo. O programa abaixo processa uma ordem de compra de "Tennis Racket".DECLARE qty_on_hand NUMBER(5); BEGIN SELECT quantity INTO qty_on_hand FROM inventory WHERE product = 'TENNIS RACKET' FOR UPDATE OF quantity; IF qty_on_hand > 0 THEN -- check quantity UPDATE inventory SET quantity = quantity - 1 WHERE product = 'TENNIS RACKET'; INSERT INTO purchase_record VALUES ('Tennis racket purchased', SYSDATE); ELSE INSERT INTO purchase_record VALUES ('Out of tennis rackets', SYSDATE); END IF; COMMIT; END;

Primeiro, declara uma varivel to tipo NUMBER(5) para armazenar a quantidade de raquetes existentes. Ento seleciona a quantidade existente em estoque, que est armazenada no banco, atribuindo o valor varivel qty_on_hand. Se a quantidade for maior que zero, atualiza o estoque e registra a venda, seno informa a falta do item. PL/SQL trata-se de uma linguagem completa, no sentido em que possui comandos para declarao e manipulao de variveis e controle de fluxo de processamento, definir procedimentos e funes, tratar erros, alm de integrar comandos SQL. No h comandos para vdeo ou teclado, que devero ser controlados por outra linguagem. PL/SQL uma linguagem estruturada em blocos (procedimentos, funes e blocos no nomeados), que podem conter zero ou mais sub-blocos. Tipicamente, cada bloco corresponde a um problema a ser resolvido. Em cada bloco possvel declarar variveis de escopo local, que deixam de existir quando o bloco termina.

Tipicamente, cada bloco composto de trs partes: declaraes, comandos e tratamento de excees. Apenas a seo de comandos obrigatria.[DECLARE declaraes] BEGIN comandos [EXCEPTIONS tratamento de excees] END;

2.1 Execuo de PL/SQL A execuo de trechos de programas utilizando PL/SQL est integrada com o banco de dados. Em tempo de execuo, Oracle identifica uma chamada a um bloco de comandos PL/SQL, identifica comandos SQL que podem ser resolvidos pelo engine do banco envia o restante do cdigo para o excutor de PL/SQL, como mostra figura 1.

FIGURA 1. Esquema de execuo de blocos de comandos PL/SQL. Um procedimento ou funo pode ser criado no banco de dados e utilizado por todas as aplicaes e usurios que tiverem direito, atravs de um comando CALL. Estes procedimentos so chamados de Stored Procedures.

3. Variveis e constantesPL/SQL permite declarar variveis e constantes e utiliz-las em qualquer comando ou expresso dentro do seu escopo. As variveis devem ser declaradas antes de serem utilizadas. Variveis podem ser de qualquer tipo pr-existente no SQL, a saber: CHAR(num_caracteres): permite armazenar strings com um tamanho mximo delimitado. VARCHAR2(num_caracteres): permite armazenar strings com um tamanho mximo delimitado. A diferena com anterior est em no armazenar caracteres em branco ao final da string. Disto resulta algumas situaes no habituais, aps as atribuies realizadas.

C1 CHAR(10); VC1 VARCHAR2(10); VC2 VARCHAR2(10); C1 = 'RICO ' VC1 = 'RICO ' VC2 = 'RICO'

A partir das declaraes das variveis e atribuies acima, tem-se que VC1 diferente que C1, pois apenas C1 conserva os espaos em branco ao final, enquanto VC1 no os conserva, porm VC1 = VC2. BYNARY_INTEGER: nmeros inteiros entre -231 at 231 -1 . NUMBER([nm_dgitos, preciso]): suporta nmeros entre 1-130 e 10125, com um certo nmero de casas decimais, especificadas no parmetro preciso.

Este tipo possui alguns subtipos. DOUBLE PRECISION e FLOAT permitem at 38 casas decimais de preciso, enquanto REAL permite apenas 18 casas decimais. INT, INTEGER e SMALLINT representam nmeros inteiros de at 18 dgitos. PLS_INTEGER: tambm armazena nmeros inteiros entre 2-31 at 231 -1, porm as funes matemticas operam com maior eficincia sobre variveis deste tipo, sendo prefervel para clculos mais complexos. BOOLEAN: variveis booleanas podem armazenar os valores TRUE, FALSE e NULL. DATE: armazena valores de data, incluindo o horrio. O formato default para datas est definido em um parmetro do Oracle, chamado NLS_DATE_FORMAT. TIMESTAMP: este tipo armazena a data, hora, minutos e segundos. Por exemplo, '1999-06-22 07:48:53.275'. TIMESTAMP WITH TIME ZONE: este tipo inclui a zona de tempo. Por exemplo, '1999-10-31 09:42:37.114 +02:00'.

A declarao de variveis est mostrada nos exemplos abaixo, com o nome da varivel seguido pelo tipo.part_no NUMBER(4); in_stock BOOLEAN;

3.1 Vetores O tipo VARRAY permite declarar vetores, compostos por uma coleo ordenada de elementos do mesmo tipo. Cada elemento possui um ndice nico, que indica sua posio no vetor. No necessrio indicar um tamanho mximo para o vetor. Depois de criado o tipo, podem ser declaradas uma ou mais variveis do tipo. A sintaxe de criao do tipo :

TYPE type_name IS VARRAY [(limite_tamanho) ] OF element_type [NOT NULL];

Para se referir a um elemento, utiliza-se o ndice entre parnteses. Por exemplo:DECLARE TYPE Staff IS VARRAY OF Employee; staffer Employee; FUNCTION new_hires (hiredate DATE) RETURN Staff IS BEGIN ... END; BEGIN staffer := new_hires('10-NOV-98')(5); END;

3.2 Registros Registros permitem a composio de variveis de tipos diferentes em um mesmo grupo lgico. Os nomes dos campos devem ser nicos. Um campo pode ser de um tipo registro ou vetor, de modo ortogonal. Uma vez criado o tipo registro, podem ser declaradas variveis deste tipo. Por exemplo:DECLARE TYPE TimeRec IS RECORD (hours SMALLINT, minutes SMALLINT); TYPE MeetingTyp IS RECORD ( date_held DATE, duration TimeRec, -- nested record location VARCHAR2(20), purpose VARCHAR2(50));

3.3 Atribuio de valores variveis possvel atribuir valores a variveis de trs maneiras. A mais comum o operador de atribuio (:=), como mostram os exemplos abaixo.tax := price * tax_rate; valid_id := FALSE; bonus := current_salary * 0.10; wages := gross_pay(emp_id, st_hrs, ot_hrs) - deductions;

A segunda maneira de atribuir valores utilizando o comando SELECT atribuindo seu retorno uma varivel. O comando SELECT deve ser construdo de modo a retornar um valor, no uma lista de valores. Por exemplo, atribuir 10% do salrio para a varivel bonus.SELECT sal * 0.10 INTO bonus FROM emp WHERE empno = emp_id;

A terceira maneira de atribuir valores atravs de passagem de parmetros em chamadas de procedimentos e funes, o que ser visto mais adiante. 3.4 Declarao de Constantes A declarao de constantes semelhante de variveis, acrescentando a palavra chave CONSTANT e, imediatamente, atribuindo o valor. Por exemplo,

credit_limit CONSTANT REAL := 5000.00;

4. CursoresOracle utiliza reas de trabalho para executar comandos SQL e armazenar a informao processada. Um cursor permite percorrer esta rea de trabalho para ler as informaes armazenadas. H dois tipos de cursores: implcitos e explcitos. Cursores implcitos so criados pelo Oracle a cada execuo de um comando SQL, mas no podem ser utilizados em um programa em PL/SQL. Cursores explcitos podem ser manipulados em um programa PL/SQL. A declarao de um cursor segue a sintaxe DECLARE CURSOR nome_cursor IS comando_select; Por exemplo,DECLARE CURSOR c1 IS SELECT empno, ename, job FROM emp WHERE deptno = 20;

A declarao do cursor associa um comando SQL a ele. Um curso permitir ler, tupla a tupla, o resultado de um consulta. A semelhana de um arquivo, o cursor deve ser aberto, percorrer uma a uma cada tupla, at o final do conjunto de dados.

FIGURA 1. Cursor O programa de utilizar os comandos OPEN, FETCH e CLOSE. O comando OPEN executa a consulta associada ao cursor, obtm um conjunto de tuplas como resposta a consulta e posiciona o cursor na primeira tupla. O comando FETCH retorna os valores da tupla corrente e avana uma posio na lista. O comando CLOSE desabilita o cursor, liberando o espao de memria ocupado pelo conjunto resposta. A sintaxe do comando OPEN :OPEN nome_cursor;

A sintaxe do comando CLOSE :CLOSE nome_cursor;

E do comando FETCH FETCH nome_curso INTO [lista_variveis].

A lista de variveis pode conter uma ou mais variveis. H duas alternativas: utilizar variveis individuais, de tipos correspondentes ao retorno do SELECT; ou utilizar uma

varivel de um tipo registro adequado ao SELECT. Para o cursor declarado acima, possvel:FETCH c1 INTO my_empno, my_ename, my_deptno;

ouFETCH c1 INTO my_record;

Todos cursores possuem quatro propriedades que podem ser teis em um programa:%FOUND: indica se a ltima operao FETCH encontrou uma tupla. Seu valor verdadeiro at a ltima tupla. %NOTFOUND: indica se a ltima operao FETCH no encontrou uma tupla.

Seu valor

falso at a ltima tupla.%ISOPEN: indica se o cursor est aberto ou no. %ROWCOUNT: retorna o nmero de tuplas do cursor.

Para testar se o cursor retornou algum valor vlido ou j atingiu o final do conjunto, pode-se testar a propriedade FOUND do cursor. Por exemplo,FETCH C1 INTO .... WHILE C1%FOUND LOOP Comandos FETCH C1 INTO .... END LOOP

Um cursor pode, ainda, ter parmetros. Deste modo, a definio do cursor fica alterada:DECLARE CURSOR emp_cursor(dnum NUMBER) IS SELECT sal, comm FROM emp WHERE deptno = dnum;

A inicializao do cursor, que pode ser por um comando OPEN ou FOR-LOOP feita passando o valor do parmetro. Por exemplo:FOR emp_record IN emp_cursor(20) LOOP END IF; END LOOP;

4.1 Laos FOR em Cursores Em muitas situaes, percorrer todas as tuplas resultantes de uma tabela pode ser realizado com o comando FOR, e no com OPEN-FETCH-CLOSE. Um lao FOR em

cursores exige uma varivel, que declarada implicitamente dentro do comando. No exemplo abaixo, a varivel emp_rec do tipo registro. Para referir campos individuais da varivel, utilizada a notao ponto (.).DECLARE CURSOR c1 IS SELECT ename, sal, hiredate, deptno FROM emp; ... BEGIN FOR emp_rec IN c1 LOOP ... salary_total := salary_total + emp_rec.sal; END LOOP;

4.2 Cursores Dinmicos Embora a maioria dos programas em PL/SQL execute comandos previsveis, que permitem uma declarao esttica de um cursor, em algumas situaes necessrio alterar dinamicamente, em tempo de execuo, o comando SELECT que define o cursor. As seguintes situaes so tpicas:

necessrio utilizar um comando de definio de dados (DDL), como o CREATE, um comando de atribuio ou revogao de direitos (GRANT e REVOKE) OU UM comando de CONTROLE de sesso (ALTER SESSION). Em PL/SQL, estes comandos so necessariamente dinmicos. necessrio compor a clausula WHERE de acordo com condies que o usurio escolhe ou no na interface.

O comando adequado o EXECUTE IMMEDIATE, cuja sintaxe :EXECUTE IMMEDIATE comando_sql [INTO {varivel1, varivel2, . ]... | varivel_registro}] [USING parmetro1, parmetro2, ...};

O comando SQL especificado na primeira. Na clausula INTO, indica-se as variveis que recebero o retorno do comando, ou apenas uma varivel do tipo registro. Na clausula USING indica-se os parmetros do comando. Abaixo h um conjunto de exemplos: EXECUTE IMMEDIATE 'ALTER SESSION SET SQL_TRACE TRUE'; - executa o comando ALTER SESSION. EXECUTE IMMEDIATE 'CREATE TABLE bonus (id NUMBER, amt NUMBER)'; executa o comando de criao de uma tabela. sql_stmt := 'INSERT INTO dept VALUES (:1, :2, :3)'; EXECUTE IMMEDIATE sql_stmt USING dept_id, dept_name, location; - executa o comando INSERT, colocando os valores passados por parmetro na clausula USING.

sql_stmt := 'SELECT * FROM emp WHERE empno = :1'; EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id; - executa o commando SELECT, atribuindoa resposta varivel emp_rec. A identificao do empregado passada por parmetro. EXECUTE IMMEDIATE 'DELETE FROM dept WHERE deptno = :num' USING dept_id; - retira uma tupla da tabela dept.

4.2.1 Manipulao de cursores dinmicos No caso de comandos SELECT que retornem mais de uma tupla no conjunto resposta, necessrio utilizar os comandos OPEN-FETCH-CLOSE para percorrer, tupla a tupla, o conjunto resposta. Para o comando OPEN utiliza-se a seguinte variao sinttica:OPEN {varivel_cursor } FOR string_sql [USING argumento1[, argumento2]...];

Uma varivel do tipo cursor deve declarada, a priori. O comando SQL montado em uma varivel do tipo string, conforme a necessidade do programa, e colocado na clausula FOR. Se houverem parmetros no comando SQL, os argumentos so passados na clausula USING. O exemplo abaixo ilustra este caso. Veja que a varivel emp_cv declarada como sendo do tipo cursor e no comando SELECT h um parmetro, cujo valor atribudo na clausula USING.DECLARE TYPE EmpCurTyp IS REF CURSOR; -- define weak REF CURSOR type emp_cv EmpCurTyp; -- declare cursor variable my_ename VARCHAR2(15); my_sal NUMBER := 1000; BEGIN OPEN emp_cv FOR -- open cursor variable 'SELECT ename, sal FROM emp WHERE sal > :s' USING my_sal; ... END;

Os comandos FETCH e CLOSE permanecem semelhantes, como mostra o trecho de cdigo a seguir:FETCH emp_cv INTO my_ename, my_sal; WHILE emp_cv%FOUND = TRUE LOOP - - comandos FETCH emp_cv INTO my_ename, my_sal; END LOOP; CLOSE emp_cv; -- close cursor variable

5. Estruturas de Controle de FluxoAs estruturas de controle de fluxo de comandos permitem definir a execuo dos comandos de acordo com uma seqncia adequada. PL/SQL possui os comandos IF-THENELSE, CASE, FOR-LOOP, WHILE-LOOP, EXIT-WHEN, e GOTO.

5.1 Comando IF O comando IF-THEN-ELSE permite escolher entre dois caminhos alternativas, de acordo com uma condio booleana. Por exemplo,IF acct_balance >= debit_amt THEN UPDATE accounts SET bal = bal - debit_amt WHERE account_id = acct; ELSE INSERT INTO temp VALUES (acct, acct_balance, 'Insufficient funds'); -- insert account, current balance, and message END IF;

5.2 Comando CASE Para escolher entre vrias alternativas (valores, comandos), pode utilizar o comando CASE. Cada condio expressa em uma clausula WHEN e a ao associada atravs da palavra-chave THEN. Por exemplo, o comando abaixo possui quatro alternativas, uma para quadrados (square), para crculos (circle), retngulos (rectangle) e outra alternativa para os demais casos (ELSE).-- This CASE statement performs different actions based -- on a set of conditional tests. CASE WHEN shape = 'square' THEN area := side * side; WHEN shape = 'circle' THEN BEGIN area := pi * (radius * radius); DBMS_OUTPUT.PUT_LINE('Value is not exact because pi is irrational.'); END; WHEN shape = 'rectangle' THEN area := length * width; ELSE BEGIN DBMS_OUTPUT.PUT_LINE('No formula to calculate area of a' || shape); RAISE PROGRAM_ERROR; END; END CASE;

5.3 Laos de iterao Em PL/SQL existem os seguintes laos de iterao: 5.3.1 Comando FOR-LOOP O comando FOR-LOOP permite executar uma seqncia de comandos um nmero determinado de vezes. A sintaxe :FOR varivel IN limite_inferior..limite superior LOOP -- comandos END LOOP;

Por exemplo, o comando FOR-LOOP repete 500 vezes o comando INSERT.FOR num IN 1..500 LOOP INSERT INTO roots VALUES (num, SQRT(num)); END LOOP;

5.3.2 Comando WHILE-LOOP O comando WHILE-LOOP executa uma seqncia de comandos at que uma condio lgica resulte falsa. A condio lgica avaliada antes da execuo dos comandos. A sintaxe :WHILE condio_Lgica LOOP Comandos END LOOP;

Por exemplo, no trecho abaixo o comando SELECT executado enquanto a varivel for menor que 2500.WHILE salary 90 THEN GOTO calc_raise; -- branch to label END IF; ... IF job_title = 'SALESMAN' THEN -- control resumes here amount := commission * 0.25; ELSE amount := salary * 0.10; END IF;

6. ModularidadeModularidade permite dividir uma aplicao em trechos menores, mais fceis de serem desenvolvidos e testados. PL/SQL permite a criao de procedimentos, funes e pacotes.

6.1 Subprogramas PL/SQL possui dois tipos de subprogramas, chamados procedimentos e funes, que podem receber parmetros. Um subprograma deve ser declarado antes de ser chamado. A declarao de um procedimento segue a seguinte sintaxe:PROCEDURE nome_procedimento [(lista_de_parmetros)] IS [declaraes locais] BEGIN comandos [ EXCEPTION tratamento de excees ] END ;

Onde,lista_de_parmetros := [IN | OUT | INOUT] nome_parmetro1 tipo_parmetro1 [, [IN | OUT | INOUT] nome_parmetro2 tipo_parmetro2] ... [, [IN | OUT | INOUT] nome_parmetroN tipo_parmetroN]

A lista de parmetros opcional e pode ter um ou mais parmetros. Cada parmetro pode ser IN, OUT ou INOUT. Parmetros IN so o tipo default, permitem receber um valor para o procedimento utilizar. Parmetros OUT tm seu valor alterado durante o procedimento e parmetros INOUT combinam os dois tipos. O exemplo abaixo ilustra um procedimento.PROCEDURE award_bonus (emp_id NUMBER) IS bonus REAL; comm_missing EXCEPTION; BEGIN -- executable part starts here SELECT comm * 0.15 INTO bonus FROM emp WHERE empno = emp_id; IF bonus IS NULL THEN RAISE comm_missing; ELSE UPDATE payroll SET pay = pay + bonus WHERE empno = emp_id; END IF; EXCEPTION -- exception-handling part starts here WHEN comm_missing THEN ... END award_bonus;

A chamada de uma funo realizada apenas atravs do nome do procedimento e os parmetros. Os parmetros so posicionais, como no PASCAL e C. Por exemplo,BEGIN .... award_bnus(304); . END;

A sintaxe para funes inclui o tipo de retorno da funo, como abaixo:FUNCTION function_name [ ( lista_de_parmetros ) ] RETURN tipo_de_dados

[declaraes locais] BEGIN comandos [ EXCEPTION tratamento de excees ] END ;

O exemplo abaixo ilustra uma funo. O comando RETURN especfica o valor retornado pela funo.FUNCTION sal_ok (salary REAL, title VARCHAR2) RETURN BOOLEAN IS min_sal REAL; max_sal REAL; BEGIN SELECT losal, hisal INTO min_sal, max_sal FROM sals WHERE job = title; RETURN (salary >= min_sal) AND (salary exec lista_vendas(10); Ordem : 1 Nota Fiscal: 1011 Data da Venda: 20/08/02 Valor: R$300 Ordem : 2 Nota Fiscal: 1010 Data da Venda: 20/08/02 Valor: R$200 Ordem : 3 Nota Fiscal: 3 Data da Venda: 19/08/02 Valor: R$50 Procedimento PL/SQL concludo com sucesso