4RZU9sxn

1-- EN CONSOLA
2sqlplus "/ as sysdba"
3
4-- CREAR USUAIO
5CREATE USER usuario IDENTIFIED BY contrasena;
6
7-- PERMISOS A USUARIO
8GRANT ALL PRIVILEGES TO usuario;
9GRANT CONNECT,RESOURCE TO usuario;
10GRANT SELECT, INSERT, UPDATE, DELETE ON tabla TO usuario;
11
12-- REACTIVAR USUARIO
13ALTER USER usuario IDENTIFIED BY contrasena ACCOUNT UNLOCK;
14
15-- CONSULTAR CATALOGO
16SELECT * FROM cat;
17
18-- DESCRIBIR ESTRUCTURA DE UNA TABLA
19DESCRIBE tabla;
20
21--- OBTENER DATOS DE DUAL (SISTEMA)
22SELECT SYSDATE FROM dual;
23
24-- ACTIVAR SALIDA DE CONSOLA
25set serveroutput on 
26
27-- CONCATENAR
28SELECT 'FECHA: ' || SYSDATE FROM DUAL
29
30-- INSERTAR METODO IMPLICITO
31-- Se omiten las columnas que aceptan valores nulos.
32Script: insert into departments(department_id, department_name) values(301, 'Departamento 301');
33
34-- INSERTAR METODO EXPLICITO
35-- Especificamos la palabra clave NULL en las columnas donde queremos insertar un valor nulo.
36insert into departments values(302, 'Departamento 302', NULL, NULL);
37
38-- Insertando Valores Especiales
39insert into employees (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id) values(250, 'Gustavo', 'Coronel', 'gcoronel@miempresa.com', '511.481.1070', sysdate, 'FI_MGR', 14000, NULL, 102, 100);
40
41-- Insertando Valores Específicos de Fecha
42insert into employees values(251, 'Ricardo', 'Marcelo', 'rmarcelo@techsoft.com', '511.555.4567', to_date('FEB 4, 2005', 'MON DD, YYYY'), 'AC_ACCOUNT', 11000, NULL, 100, 30);
43
44-- Copiando Filas Desde Otra Tabla
45-- tabla test
46create table test
47(
48id number(6) primary key,
49name varchar2(20),
50salary number(8,2)
51);
52
53-- Insertando datos a test desde employees
54insert into test (id, name, salary)
55select employee_id, first_name, salary
56from employees where department_id = 30;
57
58
59-- Insertando hacia Múltiples Tablas
60insert all
61when department_id = 50 then into test50 (id, name, salary) values(employee_id, first_name, salary)
62when department_id = 80 then into test80 (id, name, salary) values (employee_id, first_name, salary)
63else into test(id, name, salary) values(employee_id, first_name, salary)
64select department_id, employee_id, first_name, salary
65from employees;
66
67--Actualizando una Columna de una Tabla
68--Incrementar el salario de todos los empleados en 10%.
69update employees set salary = salary * 1.10;
70
71-- Actualizando Columnas con Subconsultas
72--Pat Fay (Employee_id = 202) ha sido trasladada al mismo departamento del empleado 203, y su salario tiene que ser el máximo permitido en su puesto de trabajo.
73update employees
74set department_id = (select department_id from employees
75where employee_id = 203),
76salary = (select max_salary from jobs
77where jobs.job_id = employees.job_id)
78where employee_id = 202;
79
80-- Uso de Subconsultas para eliminación
81-- Eliminar los empleados que tienen el salario máximo en cada puesto de trabajo.
82delete from copia_emp where salary = (select max_salary from jobs where jobs.job_id = copia_emp.job_id);
83
84-- ELIMINA TODOS LOS DATOS DE TABLA
85truncate table libros;
86delete from libros;
87
88-- CREAR TABLA
89CREATE TABLE TABLA
90(
91  CAMPO1  INTEGER NOT NULL,
92  CAMPO2 VARCHAR2(100),
93  CAMPO3       NUMBER(8,2),
94  CONSTRAINT nombre_pk PRIMARY KEY (CAMPO1),
95  CONSTRAINT campo_cnst CHECK (CAMPO1 > CAMPO2),
96  CONSTRAINT campo_u UNIQUE(CAMPO3),
97  CONSTRAINT nombre_fk FOREIGN KEY (CAMPO2) REFERENCES TABLA2(CAMPO2)
98);
99
100-- CAMBIAR UNA COLUMNA A NOT NULL O NULL
101Alter Table NombreTabla
102Modify ( NombreColumna [NOT] NULL );
103
104
105-- FUNCION
106create or replace function fnSuma( a number, b number ) return number
107is
108c number;
109begin
110c := a + b;
111return c;
112end;
113
114-- EJECUCION DE FUNCION
115select fnSuma(12,25) from dual;
116
117-- PROCEDIMIENTO
118create or replace procedure prSuma( a number, b number )
119is
120c number;
121begin
122c := a + b;
123dbms_output.put_line( c );
124end;
125
126-- EJECUCION DE PROCEDIMIENTO
127begin prSuma(15,15);
128end;
129--tambien se usa
130execute prSuma(15,15);
131
132-- BLOQUE ANONIMO
133Declare
134sFecha Varchar2(40);
135Begin
136select to_char(sysdate,'dd/mm/yyyy hh24:mm:ss')
137into sFecha from dual;
138dbms_output.put_line( 'Hoy es: ' || sFecha );
139End;
140
141-- DECLARACION DE VARIABLES
142v_NroAsiento := 78;
143v_Descuento := fnSuma(18,16);
144
145-- CONSULTA EN PROCEDIMIENTO
146create or replace procedure pr101(p_empno number)
147is
148v_ename varchar2(10);
149begin
150select ename into v_ename from emp where empno = p_empno;
151dbms_output.put_line(v_ename);
152end;
153
154
155-- FUNCION PARA RETORNAR EL MISMO TIPO DE DATO
156create or replace function fn101(p_deptno dept.deptno%type)
157return dept.dname%type
158is
159v_dname dept.dname%type;
160begin
161select dname into v_dname from dept where deptno = p_deptno;
162return(v_dname);
163end;
164
165-- EJECUCION DE FUNCION
166select fn101(10) from dual;
167
168
169-- ESTRUCTURA IF
170create or replace function fn102 (n1 number, n2 number, n3 number) return number
171is
172mayor number := 0;
173begin
174if (n1>mayor) then
175mayor := n1;
176end if;
177if (n2>mayor) then
178mayor := n2;
179end if;
180if (n3>mayor) then
181mayor := n3;
182end if;
183return mayor;
184end;
185
186-- ESTRUCTURA IF
187create or replace function fn103 (n1 number, n2 number, n3 number) return number
188is
189mayor number;
190begin
191if (n1>n2) then
192mayor := n1;
193else
194mayor := n2;
195end if;
196if (n3>mayor) then
197mayor := n3;
198end if;
199return mayor;
200end;
201
202-- ESTRUCTURA IF
203create or replace function fn104 (p_empno emp.empno%type) return varchar2
204is
205v_sal emp.sal%type; v_msg varchar2(30);
206begin
207select sal into v_sal from emp where empno = p_empno;
208if (v_sal<2500) then
209v_msg := 'Salario Bajo';
210elsif (v_sal<4000) then
211v_msg := 'Salario Regular';
212else
213v_msg := 'Salario Bueno';
214end if;
215v_msg := to_char(v_sal) || ' - ' || v_msg;
216return v_msg;
217end;
218
219--TABLA TEMPORAL
220create global temporary table TEMPORAL (
2212 id number primary key,
2223 dato varchar2(30)
2234 ) on commit preserve rows;
224
225-- BUCLE LOOP
226create or replace function fn107 (n number) return number
227is
228f number := 1;
229cont number := n;
230begin
231loop
232f := f * cont;
233cont := cont - 1;
234exit when (cont=0);
235end loop;
236return f;
237end;
238
239-- CREAR SECUENCIA
240create sequence sqtest;
241
242
243-- BUCLE WHILE
244create or replace procedure pr102 (n number)
245is
246k number := 0;
247begin
248while (k<n) loop
249insert into TEMPORAL( id,dato )
250values( sqtest.nextval, 'Gustavo Coronel' );
251k := k + 1;
252end loop;
253commit;
254dbms_output.put_line('Proceso Ejecutado');
255end;
256
257-- BUCLE FOR
258create or replace function fn108 ( n number ) return number
259is
260f number := 1;
261begin
262for k in 1 .. n loop
263f := f * k;
264end loop;
265return f;
266end;
267
268-- BUCLE FOR
269create or replace procedure pr103 ( n number, msg varchar2 )
270is
271k number := 1000;
272begin
273for k in 1 .. n loop
274dbms_output.put_line( k || ' – ' || msg );
275end loop;
276dbms_output.put_line( 'k = ' || k );
277end;
278
279-- FOR EN REVERSA
280create or replace procedure pr104 ( n number )
281is
282cad varchar2(30);
283begin
284for k in reverse 1 .. 12 loop
285cad := n || ' x ' || k || ' = ' || (n*k);
286dbms_output.put_line( cad );
287end loop;
288end;
289
290-- ETIQUETA Y SALTO DE ETIQUETA
291create or replace function fn109( b number, p number ) return number
292is
293r number := 1;
294k number := 0;
295begin
296loop
297k := k + 1;
298r := r * b;
299if (k=p) then
300goto fin;
301end if;
302end loop;
303<<fin>>
304return r;
305end;
306
307
308-- NUMERO IMPAR CON NULL
309create or replace function fn110( n number )
310return varchar2
311is
312rtn varchar2(30) := '';
313begin
314if ( mod(n,2) = 0 ) then
315null;
316else
317rtn := n || ' es impar';
318end if;
319return rtn;
320end;
321
322-- REGISTROS
323create or replace procedure pr105( cod emp.empno%type )
324is
325type reg is record (
326nombre emp.ename%type,
327salario emp.sal%type
328);
329r reg;
330begin
331select ename, sal into r
332from emp where empno = cod;
333dbms_output.put_line( 'Nombre: ' || r.nombre );
334dbms_output.put_line( 'Salario: ' || r.salario );
335end;
336
337--ROWTYPE
338--Se utiliza para declarar registros con la misma estructura de una tabla.
339create or replace procedure pr106( cod dept.deptno%type )
340is
341r dept%rowtype;
342begin
343select * into r
344from dept where deptno = cod;
345dbms_output.put_line('Codigo: ' || r.deptno);
346dbms_output.put_line('Nombre: ' || r.dname);
347dbms_output.put_line('Localización: ' || r.loc);
348end;
349
350
351-- MOSTRAR LOS QUE SON MENORES O IGUALES QUE 10,15,20,25
352SELECT columna FROM tabla WHERE campo_columna <= ANY (10,15,20,25);
353
354-- MOSTRAR LOS QUE SON MAYORES QUE 10,15,20,25
355SELECT columna FROM tabla WHERE campo_columna >= ALL (10,15,20,25);
356
357-- MOSTRAR LOS QUE NO SON MAYORES QUE 10
358SELECT columna FROM tabla WHERE NOT (campo_columna >= 10);
359
360-- MOSTRAR LOS QUE PERTENEZCAN A 10
361SELECT columna FROM tabla WHERE campo_columna IN (10);
362
363-- MOSTRAR LOS QUE SE ENCUENTREN ENTRE 10 Y 20
364SELECT columna FROM tabla WHERE campo_columna BETWEEN 10 AND 20;
365
366-- MOSTRAR SI EXISTE
367SELECT columna FROM tabla WHERE EXISTS (SELECT columna2 FROM tabla2 WHERE condicion);
368
369-- MOSTRAR SI ES NULO
370SELECT columna FROM tabla WHERE campo is null;
371
372-- MOSTRAR LOS QUE EMPIECEN 'XX%'
373-- MOSTRAR LOS QUE TERMINEN '%XX'
374-- MOSTRAR LOS QUE CONTENGAN '%X%'
375SELECT columna FROM tabla WHERE campo_columna LIKE 'XX%';
376
377-- ORDENAR POR
378SELECT columna FROM tabla WHERE campo_columna ORDER BY columna1 ASC, columna2 DESC;
379SELECT columna FROM tabla WHERE campo_columna ORDER BY columna1 ASC NULL FIRST;
380
381-- CASE FORMATO 1
382SELECT columna1, columna2,
383CASE columna2 
384WHEN 1 then 'A' 
385WHEN 2 then 'B'
386WHEN 3 then 'C' 
387ELSE 'D' 
388END AS nombre_nueva_columna 
389FROM tabla WHERE condicion;
390
391-- CASE FORMATO 2
392SELECT columna1, columna2,
393CASE columna2 
394WHEN condicion1 then 'A' 
395WHEN condicion2 then 'B'
396WHEN condicion3 then 'C' 
397ELSE 'D' 
398END AS nombre_nueva_columna 
399FROM tabla WHERE condicion;
400
401-- SUMAR EN CONSULTAR
402SELECT campos_columna, (columna1 + columna2) AS nueva_columna FROM tabla;
403
404-- REEMPLAZAR NULO POR OTRO VALOR
405SELECT campos_columna, NVL(campo_columna,nuevo_valor) AS nueva_columna FROM tabla;
406
407-- PRIMERA LETRA MAYUSCULA
408SELECT INITCAP(columna) FROM tabla;
409
410-- SIN DECIMALES
411SELECT TRUNC(columna) FROM TABLA;
412
413-- FORMATO DE FECHA
414ALTER SESSION SET nls_date_format='DD-Mon-YYYY HH24:MI:SS'
415
416-- ADICIONAR MESES A LA FECHA
417SELECT SYSDATE, ADD_MONTHS(SYSDATE,3) FROM dual;
418
419-- FECHA ACTUAL
420select current_date from dual;
421
422-- FECHA Y HORA ACTUAL
423select current_timestamp from dual;
424
425-- EXTRAER ANIO ACTUAL
426select sysdate as Hoy, extract(year from sysdate) as Año2 from dual;
427
428-- EXTRAER MESES
429select sysdate as Hoy, extract(month from sysdate) as Mes2 from dual;
430
431-- EXTRAER DIA
432select sysdate as Hoy, extract(day from sysdate) as Día2 from dual;
433
434-- MESES ENTRE FECHAS
435select months_between('19-Abr-2005','19-Dic-2004') from dual;
436
437-- Muestre el dia de hoy, el ultimo dia del mes y el primer dia del siguiente mes
438select sysdate as Hoy, last_day(sysdate) as fin_del_mes, last_day(sysdate) + 1 as proxino_mes from dual;
439
440-- Convierte una expresión a un tipo de dato específico.
441select cast(sysdate as varchar2(24)) from dual;
442
443-- IDIOMA DE LAS FECHAS
444select to_char(sysdate, 'Day, Month YYYY','NLS_DATE_LANGUAGE=English') from dual;
445
446-- Conversión de Datos Numéricos
447select to_char(15.6789,'99,999.00') from dual;
448select to_char(45.78234,'00,000.00') from dual;
449select to_char(346.4567,'L99,999.00') from dual;
450
451-- Convierte una cadena con una fecha a un dato de tipo fecha.
452select to_date('15-01-2005','DD-MM-YYYY') from dual;
453
454--Convierte una cadena numérica a su respectivo valor numérico.
455select to_number('15.45','999.99') from dual;
456
457-- Obtiene el promedio de una columna o expresión. Se puede aplicar la cláusula DISTINCT.
458select avg(salary) from employees where department_id = 30;
459
460-- Cuenta las filas de una consulta. Se puede aplicar DISTINCT.
461select count(*) from departments;
462
463-- Retorna el máximo valor de una columna ó expresión
464select max(salary) from employees where department_id = 80;
465
466-- HAVING
467-- Mostrar los Departamentos que tienen más de 10 empleados.
468select department_id as Departamento, count(*) as Empleados from employees group by department_id having count(*) > 10;
469
470-- Mostrar los puestos de trabajo de los que solo hay un empleado en la empresa.
471select job_id as Puesto, count(*) as Empleados from employees group by job_id having count(*) = 1;
472
473-- WITH
474-- Muestre los datos de los empleados que son jefes(código,nombre, apellido y salario)
475With Codigosjefes
476As (Select Distinct Manager_Id Codjefe From Employees)
477Select Employee_Id,First_Name, Last_Name, Salary From Employees,CodigosJefes WHERE Employee_Id=codjefe;
478
479-- Muestre los datos de los empleados que ganan más que su jefe
480With Jefes As
481(select employee_id codjefe,last_name apejefe,first_name nomjefe, salary saljefe
482From Employees Where Employee_Id In (Select Distinct Manager_Id From Employees))
483Select Employee_Id,First_Name, Last_Name, Salary,Codjefe,Apejefe,Nomjefe,Saljefe
484From Employees,Jefes
485Where Employees.manager_id=jefes.Codjefe and Employees.salary>jefes.saljefe;
486
487-- Encuentre todos los departamentos(codigos) donde el salario total es mayor que el promedio del salario total de todos los departamentos.
488
489with dept_total (department_id, value) as
490(Select Department_Id, Sum(Salary) from employees group by department_id),
491dept_total_avg(value) as (select avg(value) from dept_total)
492select department_id from dept_total, dept_total_avg where dept_total.value > dept_total_avg.value;
493
494-- SUBCONSULTA DE UNA SOLA FILA
495-- Muestre los empleados cuyo salario es mayor al promedio
496select last_name, first_name, salary from employees where salary = (select max(salary) from employees);
497
498-- SUBCONSULTA DE MULTIPLE FILA
499-- Muestre los empleados y el departamento a que pertenecen si en él trabaja alguien llamado John.
500select last_name, first_name, department_id from employees where department_id in ( select department_id from employees where first_name = 'John' );
501
502-- SUBCONSULTA CORRELACIONADA
503-- Mostrar los empleados que ganan mas en cada departamento.
504select department_id, last_name, salary from employees e1 where salary = ( select max(salary) from employees e2 where e1.department_id = e2.department_id );
505
506-- SUBCONSULTA ESCALAR
507-- Subconsulta Escalar en una Expresión CASE
508-- Listar las ciudades, su código de país, y si es de la India ó no.
509select city, country_id, ( case when country_id in ( select country_id from countries where country_name = 'India' ) then 'Indian' else 'Non-Indian' end) as "India?" from locations where city like 'B%';
510
511-- Subconsulta Escalar en la Cláusula SELECT
512-- Mostrar los numeros y nombres de los departamentos con el salario maximo que perciben sus empleados.
513select department_id, department_name, ( select max(salary) from employees e
514where e.department_id = d.department_id ) as "Salario Maximo" from departments d;
515
516-- Subconsultas Escalares en las Cláusulas SELECT y WHERE
517-- Listar los nombres de los departamentos y el nombre de sus jefes para todos los departamentos que están en Estados Unidos (United States of America) y Canadá (Canada).
518select department_name, manager_id, ( Select last_name from employees e where e.employee_id = d.manager_id) as mgr_name from departments d where ( (select country_id from locations l where d.location_id = l.location_id) in (select country_id from countries c where c.country_name = 'United States of America' or c.country_name = 'Canada') ) and d.manager_id is not null;
519
520-- Subconsultas Escalares en la Cláusula ORDER BY
521-- Listar los nombres de las ciudades y ordenarlos por sus respectivos nombres de país.
522select country_id, city, state_province from locations l order by (select country_name from countries c where l.country_id = c.country_id);
523
524-- Múltiples Columnas en una Subconsulta
525-- Listar todas las ciudades ubicadas en Texas.
526Select Sty_Name From City Where (Cnt_Code, St_Code) In ( Select Cnt_Code, St_Code From State where st_name = 'TEXAS' );
527
528
529-- CATEGORIASL PL/SQL
530--1. DML Lenguaje de Manipulación de Datos: Select, Insert, Update, Delete.
531--2. DDL Lenguaje de Definición de Datos: Create, Alter, Drop, Grant.
532--3. Control de Transacciones: Commit, Rollback.
533--4. Control de Sesiones: Alter Session.
534--5. Control del Sistema: Alter System.
535
536--SQL DINAMICO
537--Permite ejecutar cualquier tipo de instrucción SQL desde PL/SQL.
538
539create or replace procedure pr107( cmd varchar2)
540is
541begin
542execute immediate cmd;
543end;
544
545exec pr107('create table t1 ( id number, dato varchar2(30) )');
546exec pr107('insert into t1 values( 1, ''Oracle is Powerful'' )');
547
548--PLSQL PROCEDIMIENTO SELECT
549create or replace procedure pr108(cod dept.deptno%type) is
550emps number;
551planilla number;
552begin
553select count(*), sum(sal) into emps, planilla
554from emp
555where deptno = cod;
556dbms_output.put_line('Empleados: ' || emps);
557dbms_output.put_line('Planilla: ' || planilla);
558end;
559
560--PLSQL PROCEDIMIENTO INSERT
561create or replace procedure pr109( cod number, nom varchar2, loc varchar2)
562is
563begin
564insert into dept values(cod, nom, loc);
565commit;
566dbms_output.put_line('Proceso OK');
567end;
568
569--PLSQL PROCEDIMIENTO UPDATE (EN ESTE CASO EN CONCRETO EL PROCEDIMIENTO NO RECIBE PARAMETROS)
570create or replace procedure pr110
571is
572begin
573update resumen
574set (emps,planilla) = (select count(*), sum(sal) from emp
575where emp.deptno = resumen.deptno);
576commit;
577dbms_output.put_line('Proceso Ok');
578end;
579
580--PLSQL PROCEDIMIENTO DELETE
581--Desarrollar un procedimiento para eliminar un departamento, primero debe verificar que no tenga registros relacionados en la tabla emp.
582create or replace procedure pr111(cod number)
583is
584cont number;
585begin
586select count(*) into cont from dept where deptno = cod;
587if cont = 0 then
588dbms_output.put_line('No existe');
589return;
590end if;
591select count(*) into cont from emp where deptno = cod;
592if cont > 0 then
593dbms_output.put_line('No puede se eliminado');
594return;
595end if;
596delete from dept where deptno = cod;
597commit;
598dbms_output.put_line('Proceso Ok');
599end;
600
601--CLAUSULA RETURNING
602--Sirve para obtener información de la última fila modificada, puede ser utilizado con las sentencias insert, update, y delete.
603create or replace procedure pr113(msg varchar2)
604is
605v_rowid rowid;
606v_id number;
607begin
608insert into TEMPORAL values(sqtest.nextval,msg)
609returning rowid, id into v_rowid, v_id;
610commit;
611dbms_output.put_line('RowId: ' || v_rowid);
612dbms_output.put_line('Id: ' || v_id);
613end;
614
615--CONECTAR DESDE UNA BD A OTRA CON ENLACE DE DATOS
616create public database link lnk_demo
617connect to usurio identified by contrasena
618using 'dbegcc';
619
620select employee_id, first_name
621from employees@lnk_demo
622where department_id = 30;
623
624--SINONIMO
625create or replace public synonym hr_emp
626for employees@lnk_demo;
627
628select employee_id, first_name
629from hr_emp
630where rownum = 1;
631
632--CURSORES
633--1. Declarar el cursor
634--2. Apertura del cursor
635--3. Extracción de los resultados
636--4. Cerrar el cursor
637
638create or replace procedure pr114
639is
640--DECLARANDO EL CURSOR
641cursor c_demo is select * from dept;
642r dept%rowtype;
643begin
644--APERTURANDO EL CURSOR
645open c_demo;
646--EXTRACCION DE DATOS
647fetch c_demo into r;
648--CERRANDO EL CURSOR
649close c_demo;
650dbms_output.put_line('deptno: ' || r.deptno);
651dbms_output.put_line('dname: ' || r.dname);
652dbms_output.put_line('loc: ' || r.loc);
653end;
654
655-- CURSOR: BUCLE DE EXTRACCION SIMPLE
656create or replace procedure pr115
657is
658cursor c_emp is select * from emp;
659r emp%rowtype;
660begin
661open c_emp;
662loop
663fetch c_emp into r;
664exit when c_emp%notfound;
665dbms_output.put_line(r.empno || ' - ' || r.ename);
666end loop;
667close c_emp;
668end;
669
670-- ATRIBUTOS DE CURSORES
671--%Found - Devuelve TRUE si la última sentencia fetch tuvo éxito.
672--%NotFound - Devuelve TRUE si la última sentencia fetch no tuvo éxito.
673--%IsOpen - Este atributo se utiliza para averiguar si un cursor esta abierto ó no.
674--%RowCount - Este atributo se utiliza para averiguar la cantidad de filas que se van extrayendo del cursor.
675
676-- CURSOR: BUCLE DE EXTRACCION WHILE
677create or replace procedure pr116(p_anio number, p_mes number)
678is
679cursor c_dept is select deptno from dept;
680v_deptno dept.deptno%type;
681cont number;
682v_emps number;
683v_planilla number;
684begin
685select count(*) into cont
686from planillames
687where anio = p_anio and mes = p_mes;
688if (cont > 0) then
689dbms_output.put_line('Ya esta procesado');
690return;
691end if;
692open c_dept;
693fetch c_dept into v_deptno;
694while c_dept%found loop
695select count(*), sum(sal) into v_emps, v_planilla
696from emp
697where deptno = v_deptno;
698insert into planillames
699values(p_anio, p_mes, v_deptno, v_emps, nvl(v_planilla,0));
700fetch c_dept into v_deptno;
701end loop;
702close c_dept;
703commit;
704dbms_output.put_line('Proceso ok.');
705end;
706
707
708-- CURSOR: BUCLE DE EXTRACCION FOR
709create or replace procedure pr117
710is
711cursor c_dept is select * from dept;
712emps number;
713planilla number;
714cad varchar2(100);
715begin
716for r in c_dept loop
717select count(*), sum(nvl(sal,0)) into emps, planilla
718from emp where deptno = r.deptno;
719cad := r.deptno || ' - ' || emps || ' - ' || nvl(planilla,0);
720dbms_output.put_line(cad);
721end loop;
722end;
723
724
725
726-- CURSOR: BUCLE DE EXTRACCION FOR IMPLICITO
727create or replace procedure pr118
728is
729prom number;
730begin
731for r in (select deptno from dept) loop
732select avg(nvl(sal,0)) into prom
733from emp where deptno = r.deptno;
734dbms_output.put_line(r.deptno || '-' || to_char(nvl(prom,0),'999,990.00'));
735end loop;
736end;
737
738exec pr118;
739
740--CURSOR SELECT FOR UPDATE
741create or replace procedure pr119
742is
743cursor c_demo is select * from emp for update wait 2;
744begin
745for r in c_demo loop
746dbms_output.put_line(r.empno || '-' || r.ename);
747end loop;
748end;
749
750-- CURSOR IMPLICITO
751create or replace procedure pr120(cod number, delta number)
752is
753begin
754update emp
755set sal = sal + delta
756where empno = cod;
757if sql%notfound then
758dbms_output.put_line('no existe');
759else
760commit;
761dbms_output.put_line('proceso ok');
762end if;
763end;
764
765
766--TRATAMIENTO DE ERRORES
767--EXCEPCIONES
768--Excepciones Predefinidas
769--INVALID_CURSOR Ocurre cuando se hace referencia a un cursor que esta cerrado.
770--CURSOR_ALREADY_OPEN Ocurre cuando se trata de abrir un cursor que ya esta abierto.
771--NO_DATA_FOUND Ocurre cuando una sentencia SELECT no retorna ninguna fila.
772--TOO_MANY_ROWS Ocurre cuando una sentencia SELECT retorna mas de una fila.
773--VALUE_ERROR Ocurre cuando hay conflicto de tipos de datos.
774
775
776create or replace procedure FindEmp( Cod Emp.EmpNo%Type )
777is
778Salario Emp.Sal%Type;
779Begin
780Select Sal Into Salario
781From Emp
782Where EmpNo = Cod;
783DBMS_Output.Put_Line( 'Salario: ' || Salario );
784Exception
785When No_Data_Found Then
786DBMS_Output.Put_Line( 'Código no existe.' );
787End;
788
789--RAISE
790create or replace procedure UpdateSalEmp
791( Codigo Emp.EmpNo%Type, Salario Emp.Sal%Type )
792is
793Cont Number;
794Begin
795Select Count(*) Into Cont
796From Emp
797Where EmpNo = Codigo;
798If (Cont=0) Then
799Raise No_Data_Found;
800End If;
801Update Emp
802Set Sal = Salario
803Where EmpNo = Codigo;
804Commit;
805DBMS_Output.Put_Line( 'Proceso OK' );
806Exception
807When No_Data_Found Then
808DBMS_Output.Put_Line( 'Código no existe.' );
809End;
810
811--EXCEPCION DE USUARIO
812create or replace procedure UpdateSalEmp2
813( Codigo Emp.EmpNo%Type, Salario Emp.Sal%Type )
814is
815Cont Number;
816Excep1 Exception;
817Begin
818Select Count(*) Into Cont
819From Emp
820Where EmpNo = Codigo;
821If (Cont=0) Then
822Raise Excep1;
823End If;
824Update Emp
825Set Sal = Salario
826Where EmpNo = Codigo;
827Commit;
828DBMS_Output.Put_Line( 'Proceso OK' );
829Exception
830When Excep1 Then
831DBMS_Output.Put_Line( 'Código no existe.' );
832End;
833
834--GENERACION DE MENSAJE DE ERROR
835create or replace procedure UpdateSalEmp3
836( Codigo Emp.EmpNo%Type, Salario Emp.Sal%Type )
837is
838Cont Number;
839Begin
840Select Count(*) Into Cont
841From Emp
842Where EmpNo = Codigo;
843If (Cont=0) Then
844Raise_Application_Error( -20000, 'No existe empleado.' );
845End If;
846Update Emp
847Set Sal = Salario
848Where EmpNo = Codigo;
849Commit;
850DBMS_Output.Put_Line( 'Proceso OK' );
851End;
852
853Create or Replace Procedure prTestOUT1
854( p_Raise IN Boolean, p_Dato Out Varchar2 )
855Is
856Excep1 Exception;
857Begin
858p_Dato := 'Alianza Campeon';
859If p_Raise Then
860Raise Excep1;
861Else
862Return;
863End If;
864End;
865
866Declare
867Rpta Varchar2(20) := 'Shakira';
868Begin
869DBMS_Output.Put_Line( 'Valor Inicial: ' || Rpta );
870prTestOUT1( true, Rpta );
871DBMS_Output.Put_Line( 'Valor Final: ' || Rpta );
872End;
873
874
875--NOCOPY
876Create or Replace Procedure prTestOUT2
877( p_Raise IN Boolean, p_Dato Out NOCOPY Varchar2 )
878Is
879Excep1 Exception;
880Begin
881p_Dato := 'Alianza Campeon';
882If p_Raise Then
883Raise Excep1;
884Else
885Return;
886End If;
887End;
888
889Declare
890Rpta Varchar2(20) := 'Shakira';
891Begin
892DBMS_Output.Put_Line( 'Valor Inicial: ' || Rpta );
893prTestOUT2( False, Rpta );
894DBMS_Output.Put_Line( 'Valor Final: ' || Rpta );
895End;
896
897--PAQUETES
898--Es una estructura PL/SQL que permite almacenar definiciones, funciones y procedimientos relacionados como una sola unidad.
899CREATE OR REPLACE PACKAGE testpackage as
900function suma( n1 in number, n2 in number ) return number;
901END testpackage;
902
903--CUERPO DEL PAQUETE
904CREATE OR REPLACE PACKAGE BODY testpackage as
905function suma( n1 in number, n2 in number ) return number
906as
907rtn number;
908begin
909rtn := n1 + n2;
910return rtn;
911end;
912END testpackage;
913
914select testpackage.suma( 12,13) from dual;
915
916declare
917v_suma number;
918begin
919v_suma := testpackage.suma( 12,13);
920dbms_output.put_line ( 'Suma: ' || v_suma );
921end;
922
923--DESENCADENADORES- TRIGGERS
924--NIVEL DE TABLA
925CREATE OR REPLACE TRIGGER tr_test_emp
926AFTER INSERT OR DELETE OR UPDATE ON emp
927BEGIN
928if inserting then
929dbms_output.put_line( 'nuevo empleado se ha insertado' );
930end if;
931if updating then
932dbms_output.put_line( 'un empleado se ha modificado' );
933end if;
934if deleting then
935dbms_output.put_line( 'un empleado se ha eliminado' );
936end if;
937END tr_test_emp;
938
939insert into emp( empno, ename ) values ( 1234, 'Sergio' );
940
941--NIVEL DE ESQUEMA
942Create or Replace Trigger tr_drop_object
943Before drop on Scott.Schema
944Begin
945Raise_Application_Error( -20000, ‘No se puede eliminar el objeto !!!’ );
946End;
947
948
949
950create or replace trigger TR_BitacoraEmp
951after insert or delete or update
952on emp
953referencing old as old new as new
954for each row
955declare
956begin
957if inserting then
958Insert into bitacora(ACCION,CODEMPLEADO,FECHA,USUARIO) values('inserto',:new.empno,sysdate,user);
959end if;
960if deleting then
961Insert into bitacora(ACCION,CODEMPLEADO,FECHA,USUARIO) values('elimino',:old.empno,sysdate,user);
962end if;
963if updating then
964Insert into bitacora(ACCION,CODEMPLEADO,FECHA,USUARIO) values('modifico',:old.empno,sysdate,user);
965end if;
966end;
967
968
969
970-- PARTE 1
971WITH SALARIO_PROMEDIO(DEPARTAMENTO_ID, PROMEDIO)
972AS
973(
974SELECT F.DEPARTMENT_ID,CAST(AVG(F.SALARY) AS DECIMAL(20,2))
975FROM EMPLOYEES F, DEPARTMENTS G
976WHERE F.DEPARTMENT_ID = G.DEPARTMENT_ID
977GROUP BY F.DEPARTMENT_ID
978)
979SELECT A.EMPLOYEE_ID, A.FIRST_NAME ||' '||A.LAST_NAME AS NOMBRES ,'$'|| CAST(SALARY AS DECIMAL(10,2)) AS SALARIO, H.PROMEDIO AS SALARIO_DEP_PROMEDIO, 
980B.DEPARTMENT_ID,E.REGION_NAME, D.COUNTRY_NAME, C.STATE_PROVINCE
981FROM EMPLOYEES A, DEPARTMENTS B, LOCATIONS C, COUNTRIES D, REGIONS E, SALARIO_PROMEDIO H
982WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID AND B.LOCATION_ID = C.LOCATION_ID AND C.COUNTRY_ID = D.COUNTRY_ID AND D.REGION_ID = E.REGION_ID
983AND H.DEPARTAMENTO_ID = A.DEPARTMENT_ID AND A.SALARY < H.PROMEDIO
984AND D.COUNTRY_NAME NOT IN ('Canada')
985AND B.DEPARTMENT_NAME NOT IN ('IT','Accounting')
986ORDER BY B.DEPARTMENT_ID DESC;
987
988--===============================================================================
989
990-- PARTE 2
991SELECT F.DEPARTMENT_ID,CAST(SUM(F.SALARY) AS DECIMAL(20,2)) AS SALARIO_TOTAL
992FROM EMPLOYEES F, DEPARTMENTS G
993WHERE F.DEPARTMENT_ID = G.DEPARTMENT_ID
994AND F.DEPARTMENT_ID >= 80
995GROUP BY F.DEPARTMENT_ID;