INSTITUTO SUPERIOR TECNOLGICO PRIVADO CIBERTEC
DIRECCIN ACADMICA
CARRERA PROFESIONALES
CURSO
: Base de datos Avanzado 2 - Oracle
SEMESTRE : 2015-31
CICLO
: Quinto
PROGRAMACION PL/SQL PARTE 2 en sql plus:
Al trmino de la unidad, el alumno construye programas estructurados utilizando el lenguaje
PL/SQL dentro del manejador de base de datos Oracle. Incorpora cursores para procesar
grandes volmenes de informacin y gestiona los posibles errores de ejecucin con el uso de
excepciones.
Importante: Se recomienda inmediatamente despus de crear el spool, definir los siguientes
comando los cuales se harn por nica vez en cada spool
SQL> set linesize 250
SQL> set serveroutput on
SQL> set verify of
set linesize 250: coloca el nro de caracteres por linea en el spool, esto hara que se vea
mas ordenado y legible el txt
set serveroutput on: hace que se habilite el pintado de mensajes para todo lo que se
mande en el DBMS_OUTPUT.PUT_LINE
set verify off: para no mostrar por pantalla el valor anterior de la variable
Recordar:
Tener especial cuidado con las sentencias que deben de llevar punto y coma
(;)
1. Generar un bloque que consulte el nombre de un empleado, el cual es
consultado por su cdigo este debe de ser ingresado por el usuario
Obs: Para hacer esto se usan las variables de sustitucin, en este caso la
palabra clave es '&V_empNro' el carcter & es el que indica la solicitud
de variable, lo dems V_empNro es el nombre de la variable
SQL> set linesize 250
SQL> set serveroutput on
SQL> DECLARE
2
Vnom emp.ename%TYPE;
3 BEGIN
4
select ename into Vnom from emp where empno= '&V_empNro';
5
DBMS_OUTPUT.PUT_LINE (Vnom);
6 END;
7 /
Introduzca un valor para v_empnro: 7369
antiguo 4: select ename into Vnom from emp where empno=
'&V_empNro';
nuevo 4: select ename into Vnom from emp where empno= '7369';
SMITH
Procedimiento PL/SQL terminado correctamente.
Ejercicio Propuesto:
Ing CIP Richard Leonardo Berrocal Navarro
Elaborado solo con fines acadmicos
12
INSTITUTO SUPERIOR TECNOLGICO PRIVADO CIBERTEC
DIRECCIN ACADMICA
CARRERA PROFESIONALES
-
Cree un bloque que muestre el cdigo de departamento y su nombre
para una localidad ingresada por el usuario. El usuario debe ingresar
CHICAGO
Cree un bloque que permita consulta la tabla de grados salariales, el
usuario debe ingresar 2 datos necesarios: rango menor y rango
mayor, por ejemplo 2001 y 3000
2. Solicitar una fecha al usuario y mostrarla, declarando la variable de
sustitucin
SQL> DECLARE
2 v_fecha DATE:='&fecha';
3 BEGIN
4 DBMS_OUTPUT.PUT_LINE('La fecha introducida es: '||
TO_CHAR(v_fecha));
5 END;
6 /
Introduzca un valor para fecha: 11/05/2015
La fecha introducida es: 11/05/15
Procedimiento PL/SQL terminado correctamente.
3. Uso de if/else anidados
SQL> DECLARE
2
v_comision number:= &comision;
3 BEGIN
4
IF v_comision IS NULL THEN
5
dbms_output.put_line('La comisin es nula');
6
ELSIF v_comision > 0 THEN
7
dbms_output.put_line('Comisin es mayor a cero');
8
ELSIF v_comision < 0 THEN
9
dbms_output.put_line('Comisin es negativa');
10
ELSE
11
dbms_output.put_line('Comisin es cero.');
12
END IF;
13 END;
14 /
Introduzca un valor para comision: 0
Comisin es cero.
Procedimiento PL/SQL terminado correctamente.
4. Uso de Case y when para hacer una operacin anloga al if/else
SQL> DECLARE
2
v_comision number:= &comision;
3 BEGIN
4 CASE
5 WHEN v_comision IS NULL THEN
6
dbms_output.put_line('La comisin es nula');
7 WHEN v_comision > 0 THEN
8
dbms_output.put_line('Comisin es mayor a cero');
Ing CIP Richard Leonardo Berrocal Navarro
Elaborado solo con fines acadmicos
12
INSTITUTO SUPERIOR TECNOLGICO PRIVADO CIBERTEC
DIRECCIN ACADMICA
CARRERA PROFESIONALES
9 WHEN v_comision < 0 THEN
10
dbms_output.put_line('Comisin es negativa');
11 ELSE
12
dbms_output.put_line('Comisin es cero.');
13 END CASE;
14 END;
15 /
Introduzca un valor para comision: -9
Comisin es negativa
Procedimiento PL/SQL terminado correctamente.
5. Variante en el uso de case/when
SQL> DECLARE
2
v_comision number := &comision;
3 BEGIN
4
CASE v_comision
5
WHEN NULL THEN
6
dbms_output.put_line('La comisin es nula');
7
WHEN 10 THEN
8
dbms_output.put_line('Comisin es diez');
9
WHEN 20 THEN
10
dbms_output.put_line('Comisin es veinte');
11
ELSE
12
dbms_output.put_line('Comisin no es ni 10 ni 20.');
13
END CASE;
14 END;
15 /
Introduzca un valor para comision: 25
Comisin no es ni 10 ni 20.
Procedimiento PL/SQL terminado correctamente.
6. Genara la tabla de multiplicar dado un nmero ingresado por el usuario:
SQL> DECLARE
2 v_numero PLS_INTEGER ;
3 v_texto VARCHAR2(40) ;
4 BEGIN
5 v_numero := &Numero;
6 FOR i in 1..12 LOOP
7
v_texto := v_numero ||' x '|| i ;
8
v_texto := v_texto || ' = ' ;
9
v_texto := v_texto || v_numero * i ;
10
DBMS_OUTPUT.PUT_LINE( v_texto) ;
11 END LOOP ;
12 END ;
13 /
Introduzca un valor para numero: 9
9x1=9
9 x 2 = 18
9 x 3 = 27
Ing CIP Richard Leonardo Berrocal Navarro
Elaborado solo con fines acadmicos
12
INSTITUTO SUPERIOR TECNOLGICO PRIVADO CIBERTEC
DIRECCIN ACADMICA
CARRERA PROFESIONALES
9
9
9
9
9
9
9
9
9
x
x
x
x
x
x
x
x
x
4 = 36
5 = 45
6 = 54
7 = 63
8 = 72
9 = 81
10 = 90
11 = 99
12 = 108
Procedimiento PL/SQL terminado correctamente.
Ejercicio Propuesto:
Cree un bloque que genere la tabla de multiplicar del 2 al 12 (estos 2
valores deben de ser solicitados por el usuario.)
7. Crear tabla_temporal1 de campos contador y observa. Luego poblarlo con
registros haciendo uso del bucle loop
SQL> create table tabla_temporal1 (
2 contador number,
3 observa varchar2(30));
Tabla creada.
SQL> DECLARE
2
v_contador BINARY_INTEGER:=1;
3 BEGIN
4
LOOP
5
INSERT INTO tabla_temporal1
6
VALUES (v_contador,'ejemplo '||v_contador);
7
v_contador:=v_contador+1;
8
EXIT WHEN v_contador>50;
9
END LOOP;
10
commit;
11 END;
12 /
Procedimiento PL/SQL terminado correctamente.
SQL> select * from tabla_temporal1;
CONTADOR OBSERVA
---------- -----------------------------1 ejemplo 1
2 ejemplo 2
3 ejemplo 3
4 ejemplo 4
5 ejemplo 5
8. Hacer un bucle que pinte los valores del 1 al 20, usando loop
SQL> DECLARE
Ing CIP Richard Leonardo Berrocal Navarro
Elaborado solo con fines acadmicos
12
INSTITUTO SUPERIOR TECNOLGICO PRIVADO CIBERTEC
DIRECCIN ACADMICA
CARRERA PROFESIONALES
2
v_cont_n
NUMBER:=0;
3 BEGIN
4
Dbms_output.put_line('Inicio del Loop : ' ||
TO_CHAR(sysdate,'HH24:MI:SS'));
5
LOOP
6
Dbms_output.put_line('El contador es : ' || v_cont_n);
7
v_cont_n := v_cont_n + 1;
8
EXIT WHEN v_cont_n = 20;
9
END LOOP;
10
Dbms_output.put_line('Fin del Loop : ' ||
TO_CHAR(sysdate,'HH24:MI:SS'));
11 END;
12 /
Inicio del Loop : 00:53:20
El contador es : 0
El contador es : 1
El contador es : 2
El contador es : 3
El contador es : 4
El contador es : 5
El contador es : 6
El contador es : 7
El contador es : 8
El contador es : 9
El contador es : 10
El contador es : 11
El contador es : 12
El contador es : 13
El contador es : 14
El contador es : 15
El contador es : 16
El contador es : 17
El contador es : 18
El contador es : 19
Fin del Loop : 00:53:20
Procedimiento PL/SQL terminado correctamente.
9. Crear tabla_temporal2 de campos contador y observa. Luego poblarlo con
registros haciendo uso del bucle loop
SQL> DECLARE
2
v_contador BINARY_INTEGER:=1;
3 BEGIN
4
WHILE v_contador<=10 LOOP
5
INSERT INTO tabla_temporal2
6
VALUES (v_contador,'ejemplo while'||v_contador);
7
v_contador:=v_contador+1;
8
END LOOP;
9 commit;
10 END;
11 /
Ing CIP Richard Leonardo Berrocal Navarro
Elaborado solo con fines acadmicos
12
INSTITUTO SUPERIOR TECNOLGICO PRIVADO CIBERTEC
DIRECCIN ACADMICA
CARRERA PROFESIONALES
Procedimiento PL/SQL terminado correctamente.
SQL> select * from tabla_temporal2;
CONTADOR OBSERVA
---------- -------------------1 ejemplo while1
2 ejemplo while2
3 ejemplo while3
4 ejemplo while4
5 ejemplo while5
6 ejemplo while6
7 ejemplo while7
8 ejemplo while8
9 ejemplo while9
10 ejemplo while10
10 filas seleccionadas.
10. De los nmeros del 1 al 20, hacer un bloque que te diga si es par o
impar
SQL> DECLARE
2
v_cont_n
NUMBER:=1;
3 BEGIN
4
WHILE v_cont_n <= 20 LOOP
5
IF MOD(v_cont_n,2) = 0 THEN
6
Dbms_output.put_line(v_cont_n || ' es par.');
7
ELSE
8
Dbms_output.put_line(v_cont_n || ' es impar.');
9
END IF;
10
v_cont_n := v_cont_n + 1;
11
END LOOP;
12 END;
13 /
1 es impar.
2 es par.
3 es impar.
4 es par.
5 es impar.
6 es par.
7 es impar.
8 es par.
9 es impar.
10 es par.
11 es impar.
12 es par.
13 es impar.
14 es par.
15 es impar.
16 es par.
17 es impar.
18 es par.
Ing CIP Richard Leonardo Berrocal Navarro
Elaborado solo con fines acadmicos
12
INSTITUTO SUPERIOR TECNOLGICO PRIVADO CIBERTEC
DIRECCIN ACADMICA
CARRERA PROFESIONALES
19 es impar.
20 es par.
Procedimiento PL/SQL terminado correctamente.
11. Dados los nmeros del 1 al 100, mostrar los que sean mltiplo de 3,
mltiplo de 5, mltiplo de ambos y lo que no sean mltiplo de ninguno
SQL> BEGIN
2
FOR i IN 1..100 LOOP
3
IF MOD(i,3) = 0 AND MOD(i,5) = 0 THEN
4
Dbms_output.put_line(i || ' es mltiplo de
5
ELSIF MOD(I,3) = 0 THEN
6
Dbms_output.put_line(i || ' es mltiplo de
7
ELSIF MOD(I,5) = 0 THEN
8
Dbms_output.put_line(i || ' es mltiplo de
9
ELSE
10
Dbms_output.put_line(i || ' no es mltiplo
5.');
11
END IF;
12
END LOOP;
13 END;
14 /
1 no es mltiplo ni de 3 ni de 5.
2 no es mltiplo ni de 3 ni de 5.
3 es mltiplo de 3.
4 no es mltiplo ni de 3 ni de 5.
5 es mltiplo de 5.
6 es mltiplo de 3.
7 no es mltiplo ni de 3 ni de 5.
8 no es mltiplo ni de 3 ni de 5.
9 es mltiplo de 3.
10 es mltiplo de 5.
11 no es mltiplo ni de 3 ni de 5.
12 es mltiplo de 3.
13 no es mltiplo ni de 3 ni de 5.
14 no es mltiplo ni de 3 ni de 5.
15 es mltiplo de 3 y de 5.
16 no es mltiplo ni de 3 ni de 5.
17 no es mltiplo ni de 3 ni de 5.
18 es mltiplo de 3.
19 no es mltiplo ni de 3 ni de 5.
20 es mltiplo de 5.
21 es mltiplo de 3.
22 no es mltiplo ni de 3 ni de 5.
23 no es mltiplo ni de 3 ni de 5.
24 es mltiplo de 3.
25 es mltiplo de 5.
26 no es mltiplo ni de 3 ni de 5.
27 es mltiplo de 3.
28 no es mltiplo ni de 3 ni de 5.
29 no es mltiplo ni de 3 ni de 5.
30 es mltiplo de 3 y de 5.
Ing CIP Richard Leonardo Berrocal Navarro
Elaborado solo con fines acadmicos
3 y de 5.');
3.');
5.');
ni de 3 ni de
12
INSTITUTO SUPERIOR TECNOLGICO PRIVADO CIBERTEC
DIRECCIN ACADMICA
CARRERA PROFESIONALES
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
no es mltiplo
no es mltiplo
es mltiplo de
no es mltiplo
es mltiplo de
es mltiplo de
no es mltiplo
no es mltiplo
es mltiplo de
es mltiplo de
no es mltiplo
es mltiplo de
no es mltiplo
no es mltiplo
es mltiplo de
no es mltiplo
no es mltiplo
es mltiplo de
no es mltiplo
es mltiplo de
es mltiplo de
no es mltiplo
no es mltiplo
es mltiplo de
es mltiplo de
no es mltiplo
es mltiplo de
no es mltiplo
no es mltiplo
es mltiplo de
no es mltiplo
no es mltiplo
es mltiplo de
no es mltiplo
es mltiplo de
es mltiplo de
no es mltiplo
no es mltiplo
es mltiplo de
es mltiplo de
no es mltiplo
es mltiplo de
no es mltiplo
no es mltiplo
es mltiplo de
no es mltiplo
no es mltiplo
es mltiplo de
no es mltiplo
es mltiplo de
es mltiplo de
no es mltiplo
no es mltiplo
ni de 3 ni
ni de 3 ni
3.
ni de 3 ni
5.
3.
ni de 3 ni
ni de 3 ni
3.
5.
ni de 3 ni
3.
ni de 3 ni
ni de 3 ni
3 y de 5.
ni de 3 ni
ni de 3 ni
3.
ni de 3 ni
5.
3.
ni de 3 ni
ni de 3 ni
3.
5.
ni de 3 ni
3.
ni de 3 ni
ni de 3 ni
3 y de 5.
ni de 3 ni
ni de 3 ni
3.
ni de 3 ni
5.
3.
ni de 3 ni
ni de 3 ni
3.
5.
ni de 3 ni
3.
ni de 3 ni
ni de 3 ni
3 y de 5.
ni de 3 ni
ni de 3 ni
3.
ni de 3 ni
5.
3.
ni de 3 ni
ni de 3 ni
de 5.
de 5.
de 5.
de 5.
de 5.
de 5.
de 5.
de 5.
de 5.
de 5.
de 5.
de 5.
de 5.
de 5.
de 5.
de 5.
de 5.
de 5.
de 5.
de 5.
de 5.
de 5.
de 5.
de 5.
de 5.
de 5.
de 5.
de 5.
de 5.
Ing CIP Richard Leonardo Berrocal Navarro
Elaborado solo con fines acadmicos
12
INSTITUTO SUPERIOR TECNOLGICO PRIVADO CIBERTEC
DIRECCIN ACADMICA
CARRERA PROFESIONALES
84 es mltiplo de 3.
85 es mltiplo de 5.
86 no es mltiplo ni de 3 ni
87 es mltiplo de 3.
88 no es mltiplo ni de 3 ni
89 no es mltiplo ni de 3 ni
90 es mltiplo de 3 y de 5.
91 no es mltiplo ni de 3 ni
92 no es mltiplo ni de 3 ni
93 es mltiplo de 3.
94 no es mltiplo ni de 3 ni
95 es mltiplo de 5.
96 es mltiplo de 3.
97 no es mltiplo ni de 3 ni
98 no es mltiplo ni de 3 ni
99 es mltiplo de 3.
100 es mltiplo de 5.
de 5.
de 5.
de 5.
de 5.
de 5.
de 5.
de 5.
de 5.
Procedimiento PL/SQL terminado correctamente.
12. Crear tabla_temporal3 de campos contador y observa. Luego poblarlo
con registros haciendo uso del bucle for
SQL> begin
2 for v_contador in 1..20 loop
3
insert into tabla_temporal3 values (v_contador,'Ejemplo for '||
v_contador);
4
end loop;
5 commit;
6 end;
7 /
Procedimiento PL/SQL terminado correctamente.
SQL> select * from tabla_temporal3;
CONTADOR OBSERVA
---------- -------------------1 Ejemplo for 1
2 Ejemplo for 2
3 Ejemplo for 3
4 Ejemplo for 4
5 Ejemplo for 5
6 Ejemplo for 6
7 Ejemplo for 7
8 Ejemplo for 8
9 Ejemplo for 9
10 Ejemplo for 10
11 Ejemplo for 11
CONTADOR OBSERVA
---------- -------------------12 Ejemplo for 12
Ing CIP Richard Leonardo Berrocal Navarro
Elaborado solo con fines acadmicos
12
INSTITUTO SUPERIOR TECNOLGICO PRIVADO CIBERTEC
DIRECCIN ACADMICA
CARRERA PROFESIONALES
13
14
15
16
17
18
19
20
Ejemplo
Ejemplo
Ejemplo
Ejemplo
Ejemplo
Ejemplo
Ejemplo
Ejemplo
for
for
for
for
for
for
for
for
13
14
15
16
17
18
19
20
20 filas seleccionadas.
13. Crear un bucle que pinte el cdigo de empleado y su nombre de la tabla
emp
SQL> begin
2
for v in (select * from emp) loop
3
dbms_output.put_line(v.empno || ' ' || v.ename);
4
end loop;
5 end;
6 /
7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
7788 SCOTT
7839 KING
7844 TURNER
7876 ADAMS
7900 JAMES
7902 FORD
7934 MILLER
14. Pintar el 1er mltiplo de 13 del grupo de nmeros comprendidos entre
50 y 100
SQL> DECLARE
2
v_num
NUMBER(3);
3 BEGIN
4
FOR v_cont IN 50..100 LOOP
5
if MOD(v_cont,13) =0 then
6
v_num := v_cont;
7
dbms_output.new_line;
8
GOTO bloque1;
9
End If;
10
END LOOP;
11
<<BLOQUE1>>
12
dbms_output.put_line(' Primer mltiplo de 13 : ' || v_num);
13 END;
14 /
Ing CIP Richard Leonardo Berrocal Navarro
Elaborado solo con fines acadmicos
12
INSTITUTO SUPERIOR TECNOLGICO PRIVADO CIBERTEC
DIRECCIN ACADMICA
CARRERA PROFESIONALES
Primer mltiplo de 13 : 52
Procedimiento PL/SQL terminado correctamente.
Ejercicio Propuesto:
- Cree un bloque que le solicite su cdigo de alumno y su apellido
paterno y lo muestre, haga uso de variables de sustitucin
-
Dada la siguiente tabla
TABLA: CANDIDATOS
ID_DOCENTE
NUMBER;
NOMBRES
VARCHAR2(50);
APELLIDOS
VARCHAR2(50);
FECHA_NACIMIENTO DATE;
SALARIO
NUMBER(8,2);
ANTIGUEDAD
INTEGER;
a. Crear un bloque PL/SQL que inserte 05 registros a la tabla CANDIDATOS. Todos
los datos debe ser solicitados por pantalla y deber validar el id, los campos deben
ser consistente. Tomar como referencia los siguientes registros:
(001,Alan, Garca Perez, 01-02-1950,14000, 20)
(001,Keyko, Fujimori, 01-02-1975,18000, 8)
(001,Alejandro, Toledo, 01-02-1940,20000, 30)
(001,Pedro pablo, kuchinsky, 01-02-1935,30000, 40)
(001,Nadine, Heredia, 01-02-1976,15000, 20)
b. Crear un bloque PL/SQL que muestre por pantalla cuntos CANDIDATOS nacieron
entre los aos 1945 y cuntos en el ao 1980
Implemente un programa PL/SQL que reciba los datos de un nuevo
grado salarial y los inserte en la tabla SALGRADE. Debe verificar que
este nuevo rango no coincida con ninguno de los previamente
ingresados.
Implemente un programa PL/SQL que reciba como dato el id de una
orden y actualice el total de la orden a partir de la suma de los
subtotales de sus tems asociados.
Implemente un programa PL/SQL que reciba como data el cdigo de
un empleado y le asigne como comisin el 5% de sus ventas directas
as como el 1% de las ventas totales del rea a la que pertenece.
Usando el tipo de dato tabla PLSQL registrar todos los nmeros
impares entre 1 y 30 luego visualizarlos por pantalla.
Hacer un programa que calcule el factorial de un nmero.
Hacer un programa PL/SQL que actualice cada uno de los empleados
teniendo en consideracin que si el da de ingreso es par
debo
Ing CIP Richard Leonardo Berrocal Navarro
Elaborado solo con fines acadmicos
12
INSTITUTO SUPERIOR TECNOLGICO PRIVADO CIBERTEC
DIRECCIN ACADMICA
CARRERA PROFESIONALES
aumentarle el 10% del mximo salario de lo contrario
adicionarle el 5% del salario promedio.
-
solo debo
Hacer un programa PL/SQL que pinte los mltiplos de 19 del rango
del 1 al 200 (valores ingresados por el usuario)
Ing CIP Richard Leonardo Berrocal Navarro
Elaborado solo con fines acadmicos
12
