BD Laboratorio09

Copyright © Oracle Corporation, 2008. All rights reserved.

Mas en PL/SQL:

Estructuras de Control y SQL Empotrado

26-2 Copyright © Oracle Corporation, 2008. All rights reserved.

Objetivos

Luego de completar esta lección, usted debería estarcapacitado para hacer lo siguiente:

• Entender el uso de varias estructuras de control de programación en PL/SQL

• Usar sentencias de toma de decisiones basado en varias opciones

• Trabajar con sentencias enlazadas para ejecutarun conjunto de sentencias repetitivas.

• Empotrar sentencias SQL dentro de un block PL/SQL para que interactúe con el servidor Oracle.


Estructuras de Control

En un lenguaje procedural como PL/SQL, existentres estructuras básicas de control de programa-ción:

1. En una estructura secuencial, una serie de instruccio-nes son ejecutadas desde el inicio al fin en un orden lineal.

2. La estructura de selección es tambien conocida como una estructura de decisión o una estructura IF. Envuel-ve condiciones con un resultado TRUE (verdadero) o FALSE (falso)

3. En una estructura looping o de lazos, una serie de ins-trucciones es ejecutado repetidamente.


Estructuras de Control: SELECCION

DECLARE

v_dia VARCHAR2(10):= '&dia';

BEGIN

IF (v_dia = 'Domingo') THEN

DBMS_OUTPUT.PUT_LINE('Domingo es feriado!');

END IF;

END;

/

Enter value for dia: Domingo

Domingo es feriado!

PL/SQL procedure successfully completed.

Una simple sentencia IF


SET SERVEROUTPUT ON

DECLARE

v_edad number(2):= '&edad';

BEGIN

IF (v_edad >=18) THEN

DBMS_OUTPUT.PUT_LINE('EDAD: '||v_edad ||'-ADULTO');

ELSE

DBMS_OUTPUT.PUT_LINE('EDAD: '||v_edad ||'-MENOR');

END IF;

END;

Enter value for edad: 21

EDAD: 21-ADULTO


SQL> /


EDAD: 12-MENOR


Sentencia IF … ELSE … END IF


SELECT Lname, Fname,

DECODE(positionId,

1, Salary*1.2,

2, Salary*1.15,

3, Salary*1.05,

Salary) "Salario Nuevo"

FROM employee;

LNAME FNAME Salario Nuevo

--------------- --------------- -------------

Smith John 349800

Houston Larry 172500

Roberts Sandi 94875

McCall Alex 69825

Dev Derek 92000

Shaw Jinku 28175

Garner Stanley 51750

Chen Sunny 38500

Sentencia DECODE


DECLARE

v_pos NUMBER(1):= &Posicion;

BEGIN

IF (v_pos=1) THEN

DBMS_OUTPUT.PUT_LINE('20% incremento');

ELSIF v_pos=2 THEN


ELSIF v_pos=3 THEN


ELSIF v_pos=4 THEN


ELSE

DBMS_OUTPUT.PUT_LINE('NO incremento');

END IF;

END;

Enter value for posicion: 2

15% incremento


Sentencia ELSIF


DECLARE

n NUMBER(3):= &nota;

calificacion CHAR;

BEGIN

IF n >= 90 AND n <= 100 THEN

calificacion := 'A';

END IF;

IF n >= 80 AND n <= 89 THEN

calificacion := 'B';

END IF;


calificacion := 'C';

END IF;


calificacion := 'D';

END IF;


calificacion := 'F';

END IF;

IF n < 0 AND n > 100 THEN

calificacion := 'U';

END IF;

DBMS_OUTPUT.PUT_LINE('Nota es ' || TO_CHAR(n));

DBMS_OUTPUT.PUT_LINE('Calificación es ' || calificacion);

END;

Enter value for nota: 93

Nota es 93

Calificación es A


Simple IF

con condiciones

múltiples


DECLARE

n NUMBER(3):= &nota;

calificacion CHAR;

BEGIN

IF n >= 90 AND n <= 100 THEN

calificacion := 'A';

ELSIF n >= 80 AND n <= 89 THEN

calificacion := 'B';

ELSIF n >= 70 THEN

calificacion := 'C';

ELSIF n >= 60 THEN

calificacion := 'D';

ELSIF n >= 0 THEN

calificacion := 'F';

ELSIF n < 0 AND n > 100 THEN

calificacion := 'U';

END IF;

DBMS_OUTPUT.PUT_LINE('Nota es ' || TO_CHAR(n));

DBMS_OUTPUT.PUT_LINE('Calificación es ' || calificacion);

END;

Enter value for nota: 77

Nota es 77

Calificación es C


Sentencia ELSIF


DECLARE /* Ejemplo de CASE */

v_num NUMBER:= &cualquier_num;

v_residuo NUMBER;

BEGIN

v_residuo:= MOD(v_num, 2);

CASE v_residuo

WHEN 0 THEN DBMS_OUTPUT.PUT_LINE(v_num || ' es PAR');

ELSE DBMS_OUTPUT.PUT_LINE(v_num || ' es IMPAR');

END CASE;

END;

Enter value for cualquier_num: 5

5 es IMPAR


Sentencia CASE


DECLARE /* Ejemplo de CASE buscado */

v_num NUMBER:= &cualquier_num;

BEGIN

CASE

WHEN MOD(v_num, 2) = 0 THEN

DBMS_OUTPUT.PUT_LINE(v_num || ' es PAR');

ELSE

DBMS_OUTPUT.PUT_LINE(v_num || ' es IMPAR');

END CASE;

END;

Enter value for cualquier_num: 5

5 es IMPAR


Sentencia CASE buscado


DECLARE

v_genero CHAR:= '&sexo';

v_edad NUMBER(2):= '&edad';

v_recargo NUMBER(3,2);

BEGIN

IF (v_genero = 'M' AND v_edad >= 25) THEN

v_recargo:= 0.05;

END IF;

IF (v_genero = 'M' AND v_edad < 25) THEN

v_recargo:= 0.10;

END IF;

IF (v_genero = 'F' AND v_edad >= 25) THEN

v_recargo:= 0.03;

END IF;

IF (v_genero = 'F' AND v_edad < 25) THEN

v_recargo:= 0.06;

END IF;

DBMS_OUTPUT.PUT_LINE('GENERO :'||v_genero);

DBMS_OUTPUT.PUT_LINE('EDAD :'|| TO_CHAR(v_edad));

DBMS_OUTPUT.PUT_LINE('RECARGO :'|| TO_CHAR(v_recargo));

END;

Enter value for sexo: F


GENERO :F

EDAD :18

RECARGO :.06


IF anidado:

IF simple

con condiciones

múltiples


DECLARE

v_genero CHAR:= '&sexo';

v_edad NUMBER(2):= '&edad';

v_recargo NUMBER(3,2);

BEGIN

IF (v_genero = 'M') THEN /* HOMBRE */

IF (v_edad >= 25) THEN

v_recargo:= 0.05;

ELSE

v_recargo:= 0.10;

END IF;

ELSE /* MUJER */

IF (v_edad >= 25) THEN

v_recargo:= 0.03;

ELSE

v_recargo:= 0.06;

END IF;

END IF;

DBMS_OUTPUT.PUT_LINE('GENERO :'||v_genero);

DBMS_OUTPUT.PUT_LINE('EDAD :'|| TO_CHAR(v_edad));

DBMS_OUTPUT.PUT_LINE('RECARGO :'|| TO_CHAR(v_recargo));

END;

Enter value for sexo: F


GENERO :F

EDAD :18

RECARGO :.06


Sentencia

IF anidado


SET SERVEROUTPUT ON

DECLARE

v_conteo NUMBER(2);

v_suma NUMBER(2):= 0;

v_promedio NUMBER(3,1);

BEGIN

v_conteo:= 1; /* contador inicializado */

LOOP

v_suma:= v_suma + v_conteo;

v_conteo:= v_conteo + 1; /* contador incrementado */

EXIT WHEN v_conteo > 10; /* condición */

END LOOP;

v_promedio:= v_suma/(v_conteo -1);

DBMS_OUTPUT.PUT_LINE('Promedio del 1 al 10 es '

||TO_CHAR(v_promedio));

END;

Promedio del 1 al 10 es 5.5


Estructuras de Control: LOOPING (Bucle)


SET SERVEROUTPUT ON

DECLARE

v_conteo NUMBER(2);



BEGIN

v_conteo:= 1; /* contador inicializado */

WHILE v_conteo <= 10 LOOP /* condición */


v_conteo:= v_conteo + 1; /* contador incrementado */

END LOOP;

v_promedio:= v_suma/(v_conteo -1);



END;



Contador Controlado por un Loop WHILE


DECLARE

v_conteo NUMBER(2);



BEGIN

FOR v_conteo IN 1..10 LOOP


END LOOP;

v_promedio:= v_suma/10;



END;



Loop FOR


DECLARE

v_apellido employee.Lname%TYPE;

v_nombre employee.Fname%TYPE;

v_salario employee.Salary%TYPE;

BEGIN

SELECT Lname, Fname, Salary

INTO v_apellido, v_nombre, v_salario

FROM employee

WHERE employeeId = 200;

DBMS_OUTPUT.PUT_LINE

('Nombre empleado:'||v_nombre||' '||v_apellido);

DBMS_OUTPUT.PUT_LINE

('Salario: '||TO_CHAR(v_salario));

END;

Nombre empleado:Jinku Shaw

Salario: 24500


SQL dentro de PL/SQL


BEGIN

INSERT INTO employee

(employeeId, Lname, Fname, Salary, DeptId)

VALUES

(employee_employeeId_seq.NEXTVAL, 'RAI',

'AISH', 90000, dept_deptId_seq.CURRVAL);

COMMIT;

END;

PL/SQL procedure successfully completed. END;

Manipulación de Datos en PL/SQL

Sentencia INSERT


DECLARE

v_deptId dept.deptId%TYPE

BEGIN

SELECT deptId

INTO v_deptId

FROM dept

WHERE UPPER(deptname) = '&dept_nombre'

DELETE FROM employee

WHERE deptId = v_deptId;

COMMIT;

END;

Enter value for dept_nombre: IT


Sentencia DELETE


DECLARE

v_incremento NUMBER:= &Increm_decimal

BEGIN

UPDATE employee

SET Salary = Salary * (1 + v_incremento)

WHERE employeeId = &empleado_ID;

COMMIT;

END;

Enter value for increm_decimal: 0.15

Enter value for empleado_ID: 545


Sentencia UPDATE


Modelo de Base de Datos

cd Data Model

customer

+ cus_code: int

+ cus_lname: char

+ cus_initial: char

+ cus_areacode: char

+ cus_phone: char

+ cus_balance: float

inv oice

+ inv_number: int

- cus_code: customer

+ inv_date: char

line

- inv_number: invoice

+ line_number: int

- p_code: product

+ line_units: float

+ line_price: float

product

+ p_code: char

+ p_descript: char

+ p_indate: char

+ p_qoh: int

+ p_min: int

+ p_price: float

+ p_discount: float

- v_code: vendor

v endor

+ v_code: int

+ v_name: char

+ v_contact: char

+ v_areacode: char

+ v_phone: char

- v_state: char

+ v_order: char

1

genera

0..* 1

contiene

1..*

0..*

encontrado_en

1

0..*

suministra

1


Ejemplo de Block anónimo

SELECT * FROM vendor;

VEND_NUMBER VEND_NAME VEND_CONTACT VEN VEND_PHO VE V

----------- ----------------------------------- --------------- --- -------- -- -

21225 Bryson, Inc. Smithson 615 223-3234 TN Y

21226 SuperLoo, Inc. Flushing 904 215-8995 FL N

21231 D E Supply Singh 615 228-3245 TN Y

21344 Gomez Bros. Ortega 615 889-2546 KY N

22567 Dome Supply Smith 901 678-1419 GA N

23119 Randsets Ltd. Anderson 901 678-3998 GA Y

24004 Brackman Bros. Browning 615 228-1410 TN N

24288 ORDVA, Inc. Hakford 615 898-1234 TN Y

25443 B K, Inc. Smith 904 227-0093 FL N

25501 Damal Supplies Smythe 615 890-3529 TN N

25595 Rubicon Systems Orton 904 456-0092 FL Y

11 rows selected.



BEGIN

INSERT INTO vendor

VALUES (25678, 'Microsoft Corp. ','Bill Gates','765','546-8484','WA','N');

END;

/


SET SERVEROUTPUT ON

BEGIN

INSERT INTO vendor

VALUES (25772,'Clue Store','Issac Hayes','456','323-2009','VA','N');

DBMS_OUTPUT.PUT_LINE('Nuevo vendedor agregado!');

END;

/

Nuevo vendedor agregado!




SELECT * FROM vendor;

VEND_NUMBER VEND_NAME VEND_CONTACT VEN VEND_PHO VE V

----------- ------------------------------ --------------- --- -------- -- -

25678 Microsoft Corp. Bill Gates 765 546-8484 WA N

25772 Clue Store Issac Hayes 456 323-2009 VA N

21225 Bryson, Inc. Smithson 615 223-3234 TN Y

21226 SuperLoo, Inc. Flushing 904 215-8995 FL N

21231 D E Supply Singh 615 228-3245 TN Y

21344 Gomez Bros. Ortega 615 889-2546 KY N

22567 Dome Supply Smith 901 678-1419 GA N

23119 Randsets Ltd. Anderson 901 678-3998 GA Y

24004 Brackman Bros. Browning 615 228-1410 TN N

24288 ORDVA, Inc. Hakford 615 898-1234 TN Y

25443 B K, Inc. Smith 904 227-0093 FL N

25501 Damal Supplies Smythe 615 890-3529 TN N

25595 Rubicon Systems Orton 904 456-0092 FL Y

13 rows selected.


Block Anónimo con Variables y Lazos


set serveroutput on size 1000000

DECLARE

type tabla_estudiantes is table of estudiantes%rowtype

index by binary_integer;

e_tabla tabla_estudiantes;

cursor e is select carnet, nombres, apellidos, email

from estudiantes;

e_reg e%rowtype;

i binary integer;

BEGIN

i:= 1;

for e_reg in e loop

exit when e%notfound;

e_tabla(i) := e_reg;

i:= i + 1;

end loop;

for i in 1..e_tabla.count loop

dbms_output.put_line(

‘e_tabla(‘ || i || ‘).apellido = ‘ || e_tabla(i).apellido);

end loop;

END;

/

Ejemplo de Block anónimo con Tablas

1. Escriba un block PL/SQL para encontrar si un año es un año bisiesto. Un año bisiesto es diisible

por 4 pero no por 100, o es divisible por 400. Por ejemplo, 2000 y 2004 son años bisiestos, pero

1900 y 2001 no son años bisiestos. (Sugerencia: La función MOD(n,d) divide n por d y retorna el

residuo entero de la operación)

2. Escriba un block PL/SQL para imprimir a todos los números impares entre 1 y 10 usando un loop

básico.

3. Use un loop FOR , imprima los valores 10 al 1 en orden inverso.

4. Cree una tabla llamado ITEM con una columna llamada ItemNum con el tipo de dato NUMBER.

Escriba un programa PL/SQL para insertar valores del 1 al 5 para ItemNum

5. Ingreses un número con una variable d sustitución, y luego imprima su tabla de multiplicación

usando un loop WHILE.

6. Ingrese un número de mes entre 1 y 12 y un año de cuatro dígitos, e imprima el número de días,

en aquel mes. Para Febrero (mes = 2), chequee por año bisiesto para mostrar el número de días

igual a 28 o 29.

7. Use un block PL/SQL para eliminar el número de ítem 4 de la tabla ITEM creado en el laboratorio

4.

8. Escriba un block PL/SQL que pida al usuario un ID válido de usuario. Recupere los nombres de

los empleados, descripción de su calificación, salario y comisión. Imprima el nombre,

calificación, y la suma del salario y comisión

9. Usted fue a una tienda de videos y rentó un DVD que es retornable en 3 días contado desde la

fecha que se renta. Ingrese la fecha de renta, mes de la renta, año de la renta. Calcule e imprima

la fecha de retorno, mes de retorno, y año de retorno. Poer ejemplo:

Fecha de la

Renta

Mes de la

Renta

Año de la

renta

Fecha de

Retorno

Mes de

retorno

Año de

Retorno

2 12 2003 5 12 2003

27 2 2000 1 3 2000

30 12 2003 2 1 2004

BASES DE DATOS: Laboratorio09 Martes 03 de Noviembre del 2009

Al terminar el Laboratorio envíe sus respuestas al e-mail: [email protected]

Use el formato siguiente en su envio: BD-Lab09_Nombre_Apellido

mailto:[email protected]

BD Laboratorio09

Documents

Transcript of BD Laboratorio09