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!

INTRODUÇÃO AO PL/SQL

ALEX JOSE SILVA

RECIFE/

Índice

• Introdução Ao Pl/Sql............................................................................................................................
  • Entendendo a linguagem..................................................................................................................
• Principais características da linguagem PL/SQL..................................................................................
  • ● Blocos de código..........................................................................................................................
  • ● Declaração de variáveis................................................................................................................
  • ● Atribuindo valores a variáveis......................................................................................................
  • ● Declaração de constantes..............................................................................................................
  • ● Cursor...........................................................................................................................................
  • ● Cursor FOR Loops.......................................................................................................................
  • ● Atributos.......................................................................................................................................
  • ● Estruturas de controle...................................................................................................................
    • ■ IF - THEN - ELSIF - ELSE - END IF.....................................................................................
    • ■ CASE - ELSE - END CASE...................................................................................................
    • ■ FOR LOOP - END LOOP.......................................................................................................
    • ■ WHILE LOOP - END LOOP..................................................................................................
    • ■ EXIT WHEN LOOP................................................................................................................
  • ● Modularização............................................................................................................................
    • ■ Procedure...............................................................................................................................
    • ■ Function.................................................................................................................................
• Arquitetura PL/SQL...........................................................................................................................
  • ● Blocos anônimos........................................................................................................................
  • ● Subprogramas armazenados.......................................................................................................
  • ● Gatilhos.......................................................................................................................................
• Ferramenta Sql*Plus...........................................................................................................................

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 -- checando a quantidade 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; Com o PL/SQL conseguimos fazer com que nosso banco de dados “pense”, com ela conseguimos colocar a inteligência das regras dentro do banco de dados, o banco passa não só a armazenar os dados, mas também a gerenciar a qualidade desses dados baseado nas regras que você desenvolver, seus dados terão mais qualidade e dados com qualidade garantirão uma informação com qualidade.

Entendendo a linguagem

Para entender melhor a linguagem vamos começar por um pequeno trecho de código. Esse pequeno trecho de código processa um pedido de uma raquete de tênis. Primeiro ele declara uma variável do tipo NUMBER para armazenar a quantidade de raquetes de tênis solicitadas. Depois ele pesquisa a quantidade de raquete de tênis que existem no estoque. Se a quantidade for maior que zero, o sistema atualiza o estoque e registra uma raquete de tênis na tabela de pedido, caso contrário, o programa registra a falta do produto na tabela de pedido. Com PL/SQL você pode combinar a força da manipulação de dados da linguagem SQL com a força do processamento de dados das linguagens procedurais, em um único lugar.

part_no NUMBER(4); in_stock BOOLEAN; valid_id := FALSE; tax := price * tax_rate; bonus := current_salary * 0.10; wages := gross_pay(emp_id, st_hrs, ot_hrs) – deductions; SELECT sal * 0. INTO bonus FROM emp WHERE empno = emp_id; DECLARE my_sal REAL(7,2); PROCEDURE adjust_salary(emp_id INT, salary IN OUT REAL) IS ...

Principais características da linguagem PL/SQL

● Blocos de código

A linguagem PL/SQL é baseada em blocos, como a linguagem Pascal, cada unidade básica de código e composta por blocos que podem conter novos blocos. Um bloco pode conter declarações e comandos. A figura abaixo mostra que um bloco PL/SQL contém três partes, a declaração, execução e exceção. Apenas a parte de execução é obrigatória.

● Declaração de variáveis

As variáveis podem ser de qualquer tipo SQL, como CHAR, NUMBER, DATE ou de qualquer tipo PL/SQL, como BOOLEAN. Por exemplo, para declarar duas variáveis fazemos o seguinte: Você também pode declarar outros tipos no bloco de declaração, como registros por exemplo.

● Atribuindo valores a variáveis

Existem três formas de atribuir valores a uma variável em PL/SQL, são elas:

1. Usando o operador :=
2. Usando um comando SELECT
3. Usando a passagem de parâmetros por referência(OUT, IN OUT)

DECLARE

dept_rec dept%ROWTYPE; -- declaração de um registro Para referenciar um campo do registro use a notação de pontos, exemplo: my_deptno := dept_rec.deptno; 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; exemplo acima, o registro emp_rec é usado para acessar a coluna sal do Cursor.

● Atributos

As variáveis 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 o tipo de uma coluna ou de toda uma tabela, exemplo: ○ %TYPE Este atributo é muito usado para definir tipos de variáveis que vão receber valores de uma coluna. Vamos assumir que exista um coluna chamada title em uma tabela chamada books, para declarar uma variável my_title do mesmo tipo da coluna title basta usar o atributo %TYPE da seguinte forma no bloco de declaração de variáveis: my_title books.title%TYPE Uma das vantagens dessa notação é que se o tipo da tabela for alterada o tipo da variável também será mudado. ○ %ROWTYPE Se você precisar de um registro que represente uma linha de uma tabela, use o atributo %ROWTYPE, com ele você poderá representar toda uma linha de uma tabela, exemplo:

● Estruturas de controle

Vamos agora falar das estruturas de controle da linguagem PL/SQL ○ Condições

■ IF - THEN - ELSIF - ELSE - END IF

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; FOR num IN 1..500 LOOP INSERT INTO roots VALUES (num, SQRT(num)); END LOOP; 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'); END IF; COMMIT; END;

■ CASE - ELSE - END CASE

-- This CASE statement performs different actions -- based on a set of conditional tests. ○ Laços:

■ FOR LOOP - END LOOP

PROCEDURE award_bonus (emp_id NUMBER) IS bonus REAL; comm_missing EXCEPTION; BEGIN -- executable part starts here SELECT comm * 0. 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; CREATE OR REPLACE FUNCTION area_quadrado(base IN NUMBER, altura IN NUMBER) RETURN NUMBER IS BEGIN RETURN (base * altura) ** 2; END;

● Modularização

○ Subprogramas A linguagem PL/SQL possui dois tipos de subprogramas procedures e functions.

■ Procedure

■ Function

○ Pacotes A linguagem PL/SQL permite a você agrupar tipos, variáveis, cursores e subprogramas em um pacote. Um pacote é dividido em duas partes: especificação e corpo. A especificação é a interface do pacote, nela estão declarados os tipos, constantes, variáveis, exceções, cursores e subprogramas disponíveis pra uso. No corpo temos a implementação das especificações declaradas na interface.

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 PL/SQL

O PL/SQL em sua versão compilado e run-time é uma tecnologia, não é 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.

● Blocos anônimos

Quando programas enviam blocos PL/SQL para Oracle, eles são considerados como bloco anônimos, ao receber esses blocos o Oracle irá compilar e executar os blocos PL/SQL.

● Subprogramas armazenados

Quando um subprograma é armazenado no Oracle ele esta pronto para ser executado. Armazenar um subprograma oferece uma produtividade superior, alta performance, otimização de memória, integridade com aplicação e alta segurança. Armazenando código PL/SQL em bibliotecas você pode compartilhar esse código em diversos sistemas, diminuindo a redundância de código e aumentando a produtividade. Subprogramas são armazenados prontos para serem executados, não havendo a necessidade de se fazer o 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 memória compartilhada, pois apenas uma cópia do programa sera carregado na memória.

● Gatilhos

Gatilhos são subprogramas associados a uma tabela, visão ou evento. Em cada instância, 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 salário da tabela de empregado é alterado.

SQL> SELECT * FROM salgrade; SQL> SELECT ename, job, sal Salario FROM emp; SQL> SELECT ename, mgr FROM emp WHERE mgr = NULL; SQL> SELECT ename, job, deptno FROM emp WHERE job = 'CLERK'; SQL> SELECT ename, job, sal Salario FROM emp WHERE sal BETWEEN 1000 AND 1500; SQL> SELECT empno, ename, mgr, deptno FROM emp WHERE ename IN ('FORD', 'ALLEN'); SQL> SELECT ename FROM emp WHERE ename LIKE 'S%'; SQL> SELECT e.ename, d.dname FROM emp e, dept d WHERE e.deptno = d.deptno; Diversão com grande SCOTT - Recordar SQL é viver: