Introdução à PL/SQL: Características, Vantagens e Código de Exemplo, Study Guides, Projects, Research of Data Mining

Download Introdução à PL/SQL: Características, Vantagens e Código de Exemplo and more Study Guides, Projects, Research Data Mining in PDF only on Docsity! INTRODUGAO AO PL/SQL ALEX JOSE SILVA RECIFE/2015 indice Introdugao Ao PI/Sq Entendendo a linguagem. Principais caracteristicas da linguagem PL/SQL. @ Blocos de cddigo... @ Declaragao de variavei e Atribuindo valores a varidveis e Declaracao de constantes e Cursor. e Cursor FOR Loops. @ Atributos. e Estruturas de controle m IF - THEN - ELSIF - ELSE - END IF. ™@ CASE - ELSE - END CASE @ FOR LOOP - END LOOP... @ WHILE LOOP - END LOOP.. m EXIT WHEN LOO @ Modularizacao. @ Procedure. @ Function.. Arquitetura PL/SQL... @ Blocos anGnimos. e Subprogramas armazenados. @ Gatilhos....... Ferramenta Sql*Plus.. Principais caracteristicas da linguagem PL/SQL e Blocos de cédigo A linguagem PL/SQL é baseada em blocos, como a linguagem Pascal, cada unidade basica de cddigo e composta por blocos que podem conter novos blocos. Um bloco pode conter declaragdes e comandos. A figura abaixo mostra que um bloco PL/SQL contém trés partes, a declaracao, execucao e excecdo. Apenas a parte de execugao é obrigatoria. e Declaragao de variaveis As variaveis podem ser de qualquer tipo SQL, como CHAR, NUMBER, DATE ou de qualquer tipo PL/SQL, como BOOLEAN. Por exemplo, para declarar duas varidveis fazemos o seguinte: part_no NUMBER(4); in_stock BOOLEAN; Vocé também pode declarar outros tipos no bloco de declaracao, como registros por exemplo. e Atribuindo valores a variaveis Existem trés formas de atribuir valores a uma variavel em PL/SQL, sao elas: 1. Usando o operador := valid_id := FALSE; tax := price * tax_rate; bonus := current_salary * 0.10; wages := gross_pay(emp_id, st_hrs, ot_hrs) — deductions; 2. Usando um comando SELECT SELECT sal * 0.10 INTO bonus FROM emp WHERE empno = emp_id; 3. Usando a passagem de parametros por referéncia(OUT, IN OUT) DECLARE my_sal REAL(7,2); PROCEDURE adjust_salary(emp_id INT, salary INOUT REAL) IS ... BEGIN SELECT AVG(sal) INTO my_sal FROM emp; adjust_salary(7788, my_sal); -- atribui um novo valor a my_sal e Declaragao de constantes A declaragaéo de uma constante é parecida com a declaracéo de uma variavel, basta acrescentar a palavra CONSTANT e o TIPO, exemplo: credit_limit CONSTANT REAL := 5000.00; e Cursor O Oracle usa areas de trabalho para executar comandos SQL e armazenar as informag6es processadas. Uma construgao do PL/SQL chamada de CURSOR permite que vocé de um nome e acesse a informagao armazenada. Para qualquer comando SQL o PL/SQL cria um cursor implicitamente, porém se vocé quiser processar individualmente as linhas que retornam de uma consulta vocé deve criar explicitamente um cursor. DECLARE CURSOR c1 IS SELECT empno, ename, job FROM emp WHERE deptno = 20; No cursor declarado acima as linhas que retornam desse SELECT poderao ser processadas individualmente, através de um ponteiro vocé podera caminhar na tabela resultado do SELECT. e Cursor FOR Loops Para navegar no resultado de um cursor a forma mais simples é utilizar o la~o FOR LOOP, com ele vocé navega nos resultados de um cursor até que o ponteiro atinja a Ultima linha da tabela resultado do SELECT. Outra forma de se navegar num Cursor é usar os comando OPEN, FETCH e CLOSE, porém o FOR LOOP é a forma mais simples. DECLARE CURSOR c1 IS SELECT ename, sal, hiredate, deptno FROM emp; BEGIN FOR emp_rec IN cl LOOP salary_total := salary_total + emp_rec.sal; END LOOP; Note que para referenciar as colunas da tabela resultado do Cursor no FOR criamos um registro que sera usado para acessar a coluna da linha em que o ponteiro esta posicionado. No exemplo acima, o registro emp_rec é usado para acessar a coluna sal do Cursor. e Atributos As variaveis e os cursores do PL/SQL podem usar atributos para definir seus tipos por referéncia, isso significa que, ao invés de definir um tipo vocé pode referenciar 0 tipo de uma coluna ou de toda uma tabela, exemplo: o %TYPE Este atributo é muito usado para definir tipos de varidveis que vao receber valores de uma coluna. Vamos assumir que exista um coluna chamada title em uma tabela chamada books, para declarar uma varidvel my_title do mesmo tipo da coluna title basta usar o atributo %TYPE da seguinte forma no bloco de declaracao de variaveis: my_title books.title%TYPE Uma das vantagens dessa notacao é que se 0 tipo da tabela for alterada o tipo da variavel também sera mudado. © %ROWTYPE Se vocé precisar de um registro que represente uma linha de uma tabela, use 0 atributo %ROWTYPE, com ele vocé podera representar toda uma linha de uma tabela, exemplo: DECLARE dept_rec dept%ROWTY PE; -- declaragao de um registro Para referenciar um campo do registro use a notagao de pontos, exemplo: my_deptno := dept_rec.deptno; e Estruturas de controle ‘Vamos agora falar das estruturas de controle da linguagem PL/SQL © Condigées m IF - THEN - ELSIF - ELSE - END IF DECLARE acct_balance NUMBER(11,2); acct CONSTANT NUMBER{(4) := 3; debit_amt CONSTANT NUMBER(5,2) := 500.00; BEGIN SELECT bal INTO acct_balance FROM accounts WHERE account_id = acct FOR UPDATE OF bal; e Modularizagao © Subprogramas A linguagem PL/SQL possui dois tipos de subprogramas procedures e functions. m= Procedure 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; = Function CREATE OR REPLACE FUNCTION area_quadrado(base IN NUMBER, altura IN NUMBER) RETURN NUMBER IS BEGIN RETURN (base * altura) ** 2; END; © Pacotes A linguagem PL/SQL permite a vocé agrupar tipos, varidveis, cursores e subprogramas em um pacote. Um pacote é dividido em duas partes: especificagao e corpo. A especificagao é a interface do pacote, nela estéo declarados os tipos, constantes, varidveis, excegGes, cursores e subprogramas disponiveis pra uso. No corpo temos a implementacao das especificagdes declaradas na interface. 10 CREATE PACKAGE emp_actions AS -- package specification PROCEDURE hire_employee (empno NUMBER, ename CHAR, ...); PROCEDURE fire_employee (emp_id NUMBER); END emp_actions; CREATE PACKAGE BODY emp_actions AS -- package body PROCEDURE hire_employee (empno NUMBER, ename CHAR, ...) IS BEGIN INSERT INTO emp VALUES (empno, ename, ...); END hire_employee; PROCEDURE fire_employee (emp_id NUMBER) IS BEGIN DELETE FROM emp WHERE empno = emp_id; END fire_employee; END emp_actions; Arquitetura PLISQL O PL/SQL em sua versao compilado e run-time é uma tecnologia, nado é um produto independente. Essa tecnologia é como um engine que compila e executa os blocos PL/SQL, este engine pode estar instalado num banco de dados Oracle ou em uma ferramenta de desenvolvimento, como Oracle Forms ou Oracle Report. e Blocos anénimos Quando programas enviam blocos PL/SQL para Oracle, eles sao considerados como bloco an6nimos, ao receber esses blocos 0 Oracle ira compilar e executar os blocos PL/SQL. e Subprogramas armazenados Quando um subprograma é armazenado no Oracle ele esta pronto para ser executado. Armazenar um subprograma oferece uma produtividade superior, alta performance, otimizagao de memoria, integridade com aplicacao e alta seguranca. Armazenando cddigo PL/SQL em bibliotecas vocé pode compartilhar esse codigo em diversos sistemas, diminuindo a redundancia de cédigo e aumentando a produtividade. Subprogramas sao armazenados prontos para serem executados, nao havendo a necessidade de se fazer 0 parse, nem de compilar novamente, sendo assim, quando um subprograma que esta armazenado é chamado ele é executado imediatamente pelo engine PL/SQL, tirando proveito também da memoria compartilhada, pois apenas uma copia do programa sera carregado na meméria. e Gatilhos Gatilhos sao subprogramas associados a uma tabela, visdo ou evento. Em cada instancia, vocé pode ter um gatilho disparado automaticamente antes ou depois de um comando INSERT, UPDATE ou DELETE que tenha afetado uma tabela. Por exemplo, o gatilho abaixo é disparado sempre que o salario da tabela de empregado é alterado. 11 CREATE TRIGGER audit_sal AFTER UPDATE OF sal ON emp FOR EACH ROW BEGIN INSERT INTO emp_audit VALUES ... END; Vantagens da linguagem PL/SQL e Suporte a linguagem SQL e Suporte a linguagem orientada a objetos e Alta performance e Alta produtividade @ Portabilidade e Integragao com Oracle e Seguranga A linguagem SQL se tornou um padrao em linguagem de manipulacao de dados por ser uma linguagem flexivel, poderosa e facil de aprender. Com poucas palavras em inglés, SELECT, INSERT, UPDATE e DELETE podemos manipular facilmente os dados em um banco de dados. A problema da linguagem SQL é que ela nao é procedural, vocé tem que executar um comando por vez. A linguagem PL/SQL permite que vocé use todo o poder da linguagem SQL, de forma procedural num ambiente altamente integrado com o Oracle e seus recursos de forma rapida e segura, reduzindo também o trafego na rede. Criando um programa em PL/SQL vocé pode roda-lo em qualquer ambiente que o Oracle rode. Ferramenta Sql*Plus O SQL*Plus é um ambiente no qual vocé pode realizar as seguintes tarefas: e Executar instrugdes SQL para manipular dados no banco de dados; e Formatar, calcular, armazenar e imprimir resultados de consulta em formularios; e Criar arquivos de script para armazenar instrugdes SQL para uso repetitivo; Comando SQL> connect ddestftigerddesomaptio Conectar ao banco de dados Exibir a estrutura de uma tabela SELECT * FROM emp; Selecionar Tabela Executar um comando SQL/ Repetir 0 comando SQL que esta no buffer save [nome_arquivo] [rep|] @[nomeSaixquivabmando SQL que esta no buffer Executar o arquivo salvo ed Editar o comando SQL que esta no buffer Armazena a saida de uma consul spool [nome_arquivo] spool off Mostra o erro com detalhes Sair do ambiente show error exit Sair do SQL*Plus 12