Monografia - Tesis

En Construccion....

Instalación Forms Builder Oracle 10G

Ejercicios y mas Ejercicios

Aqui encontraras muchos ejercicios resueltos de todos los temas, se que en cualquier momento tener la guia de lo que deseamos realizar es buena técnica...Espero les sirvan de mucha ayuda.

 

--parcial--1

select * from tab;

--tab muestra las tablas del esquema

 select * from cat;--muestra los catalogos del esquema 

--2 la descripcion del comntenido de la tabla departmentsdesc departments;
select department_id ||'  ' || department_name as "NOMBRE DEPTO"from departments;

--3select distinct department_id from departments;

--4select e.last_name, d.department_name from employees e join departments don e.department_id = d.department_idwhere d.department_id in (10 , 60);

--5select first_name, TO_CHAR(salary*12, '$9,999,999.00') SALARY, to_char(hire_date, 'fmdd day Month YYYY') as fechafrom employees;

--6 select l.city, e.first_name, e.last_name, e.salaryfrom employees e join departments don e.department_id = d.department_idjoin locations lon d.location_id = l.location_idwhere e.commission_pct is  null order by salary; 

--7select sysdate from dual;

--8select upper (last_name), length (first_name) from employeeswhere  first_name like '%J' and first_name like '%M';

--9select last_name,  trunc (months_between  (sysdate , hire_date)) as "meses trabajados"from employees;

--10select last_name, first_name , trim (('sin comision')) as "sin comision"from employeeswhere commission_pct is null ;

--11select c.country_name, d.department_name, e.first_namefrom employees e join departments don e.department_id = d.department_idjoin locations lon d.location_id = l.location_idjoin countries c on l.country_id = c.country_idwhere d.department_id not in ('60');

--12select emp.first_name , jef.first_namefrom employees emp join employees jefon emp.manager_id = jef.employee_id;

--13select r.region_name, e.first_name, e.emailfrom employees e join departments don e.department_id = d.department_idjoin locations lon d.location_id = l.location_idjoin countries c on l.country_id = c.country_idjoin regions ron r.region_id = c.region_idwhere e.email like '@%';

--14--select d.department_name ||'  '|| e. first_name  ,lpad (to_char (trunc (months_between  (sysdate , hire_date)/12))) select d.department_name ||'  '|| e. first_name  ,lpad ((months_between  (sysdate , hire_date))) from employees e join departments don e.department_id = d.department_id;
select last_name, lpad (salary, 15, '*')from employees;

--15SELECT distinct job_title,      CASE job_id WHEN 'AD_PRES0' THEN 'A'                WHEN 'ST_MAN' THEN 'B'                WHEN 'IT_PROG' THEN 'C'                WHEN 'SA_REP' THEN 'D'                WHEN 'ST_CLERK' THEN 'E'                WHEN job_id THEN 'no'    ELSE JOB_ID END "COCO"FROM JOBS;

---------------------
--PRACTICA 4 PARTE 1--1

muestre el nombre de empleado y el id  y nombre de los departamentos select employees.last_name, departments.department_id, departments.department_namefrom employees , departments where employees.department_id = departments.department_id;--1.2select e.last_name, d.department_id, d.department_namefrom employees e join departments don (e.department_id = d.department_id);

--2 muestra todos los trabajos en el departamento 30 incluyedo la localizacion select distinct e.job_id, d.department_id, d.location_idfrom employees e , departments dwhere e.department_id = d.department_idand d.department_id= 30;

--2.2select distinct e.job_id, d.department_id, d.location_idfrom employees e join departments don (e.department_id = d.department_id)where d.department_id = 30;--3  muestre el apellido  el nombre del departamento el id de la localozacion y la ciudad, de los q tengan comisionselect e.last_name, d.department_name, l.location_id, l.cityfrom employees e , departments d, locations lwhere e.department_id = d.department_id      and d.location_id = l.location_id     and e.commission_pct is not null;

--3.2select e.last_name, d.department_name, l.location_id, l.cityfrom employees e join departments d 
---------------------------

select c.country_name, e.last_name, e.first_name, e.hire_datefrom employees e join departments d join locations l join countries con l.country_id = c.country_idon d.location_id = l.location_idon e.department_id = d.department_idwhere hire_date between '01-01-80' and '31-10-88';
select e.last_name, e.salaryfrom employees e join jobs j  join job_history jhon j.job_id = jh.job_idon e.job_id =j.job_id;
select e.last_name, e.salaryfrom employees e , jobs j, job_history jhwhere e.job_id =j.job_id  and j.job_id = jh.job_id(+);

select count (*) , max (e.salary), min (e.commission_pct), avg(e.salary), e.last_name, d.department_name from employees e join departments d on e.department_id  = d.department_id where hire_date > '31/10/1988' and d.department_name = 'Accounting' group by e.last_name, d.department_name; 

select department_name from departments;
-----------------------------

select * from tab;

select last_name ,nvl(to_char(commission_pct), 'sin comision') from employees;select d.department_name, concat ( e.first_name , e.last_name), rpad(' ', trunc(months_between(sysdate ,hire_date)/12)+1,'*')from employees e join departments don e.department_id = d.department_id;SELECT employee_id, last_name, salary, department_idFROM employeesWHERE employee_id = &employee_num ;SELECT last_name, department_id, salary*12FROM employeesWHERE job_id = '&job_title';
SELECT employee_id, last_name, job_id,&column_name, FROM employeesWHERE &conditionORDER BY &order_column ;
SELECT job_id "Job",SUM(DECODE(department_id , 20, salary)) "Dept 20",SUM(DECODE(department_id , 50, salary)) "Dept 50",SUM(DECODE(department_id , 80, salary)) "Dept 80",SUM(DECODE(department_id , 90, salary)) "Dept 90",SUM(salary) "Total"FROM employeesGROUP BY job_id;

--practica 8--1

crea un atabla llamada my_employee
create table MY_EMPLOYEE   (ID number(4) not null,  last_name varchar2(25),  first_name varchar2(25),  userid varchar2(8),  salary number(9,2));
  commit;

--2 describala desc my_employee;

--3 insert into my_employee (id,last_name, first_name, userid, salary) values (1, 'Patel', 'Rralph', 'rpatel', 895);insert into my_employee (id,last_name, first_name, userid, salary) values (2, 'Dancs', 'Betty', 'bdancs', 860);insert into my_employee (id,last_name, first_name, userid, salary) values (3, 'Biri', 'Ben', 'bbiri', 1100);insert into my_employee (id,last_name, first_name, userid, salary) values (4, 'Newman', 'Chad', 'cnewman', 750);insert into my_employee (id,last_name, first_name, userid, salary) values (5, 'Ropeburn', 'Audrey', 'aropebur', 1550);

--4 execute

--5 commitcommit;

--6--crear un .sql--10update my_employeeset last_name = 'Drexler'where id = 3;

--11 update my_employeeset salary  = 1000 where salary < 900;

--12create table tab1as  (select last_name apellido, salary salario, email correo from employees);
drop table tab1;select * from mi_primer_tabla;
alter table tab1  add nombre varchar2(25) not null;truncate table tab1;
alter table mi_primer_tabla--rename tab1 to mi_primer_tabla;rename column salario to plata; 
alter table mi_primer_tabladrop column plata; commit;select * from all_tab_comments where table_name = 'MI_PRIMER_TABLA';comment on table MI_PRIMER_TABLA is 'cojjjjj1';

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

/*PRACTICA 14 Presentada por Jeimy Natalia Suarez 

drop table member;

drop table title;drop table title_copy;

drop table rental;drop table reservation;

drop SEQUENCE MEMBER_ID_SEQ;

drop SEQUENCE TITLE_ID_SEQ;

drop VIEW TITLE_AVAIL;--1

--a. Tabla Member, con sus correspondientes constraints.
create table Member(member_id number(10) constraint memberr_id_pk primary key, first_name varchar2(25),last_name VARCHAR2(25) CONSTRAINT member_last_name_nn NOT NULL,address varchar2(100), city varchar2 (30), phone varchar2(15), join_date DATE DEFAULT SYSDATE);

--create table correcto.

--b. 
create table title(title_id number (10) constraint title_id_pk primary key,title varchar2(60) constraint tittle_title_nn not null, description varchar2(400) constraint title_description_nn not null,rating varchar2(4) constraint title_rating_ck check (rating in ('G', 'PG', 'R', 'NC17', 'NR')), category varchar2(20) constraint title_category_ck check (category in ('DRAMA', 'COMEDY', 'ACTION','CHILD', 'SCIFI', 'DOCUMENTARY')), release_date date);

--create table correcto.--c
create table title_copy(copy_id number (10), title_id number (10),status varchar2(15)constraint title_copy_status_nn not null constraint title_copy_status_ck check (status in ('AVAILABLE', 'DESTROYED','RENTED','RESERVED')));--create table correcto.
alter table title_copyadd constraint copy_id_pk_ primary key(copy_id,title_id);--alter table title_copy correcto.
alter table title_copyadd constraint title_copy_title_id_fk foreign key (title_id) references title (title_id);--alter table title_copy correcto.

--d.
create table rental(book_date date default sysdate,member_id number(10),copy_id number (10) ,act_ret_date date,exp_ret_date date default sysdate + 2,title_id number (10));--create table correcto.
alter table rental add constraint renta_book_date_pk primary key (book_date, member_id, copy_id, title_id);--alter table rental correcto.
alter table rental add constraint ren_member_id_fk foreign key (member_id) references member (member_id);--alter table rental correcto.
alter table rental add constraint rent_copy_id_fk foreign key (copy_id, title_id) references title_copy (copy_id ,title_id);-- alter table rental correcto.

--e
create table reservation(res_date date,member_id number(10),title_id number(10));--create table correcto.
alter table reservationadd constraint reservation_res_date_pk primary key (res_date, member_id, title_id);--alter table reservation correcto.
alter table reservationadd constraint reservation_member_id_fk foreign key (member_id) references member (member_id);--alter table reservation correcto.
alter table reservationadd constraint reservation_title_id_fk foreign key (title_id) references title (title_id);--alter table reservation correcto.

--2.
select table_name from user_tableswhere  table_name in ( 'MEMBER','RENTAL', 'RESERVATION','TITLE','TITLE_COPY')
TABLE_NAME                     

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

--4.A
INSERT INTO TITLEVALUES (TITLE_ID_SEQ.NEXTVAL, 'Willie and Christmas Too', 'All of Willie’s friends make a Christmas list for Santa, but Willie has yet to add his own wish list','G','CHILD','05-OCT-1995')

1 rows inserted

INSERT INTO TITLEVALUES (TITLE_ID_SEQ.NEXTVAL, 'Alien Again', 'Yet another installation ofscience fiction history. Can the heroine save the planet from the alien life form?','R','SCIFI','19-MAY-1995')

1 rows inserted

INSERT INTO TITLE VALUES (TITLE_ID_SEQ.NEXTVAL, 'The Glob', 'A meteor crashes near a small American town and unleashes carnivorous goo in this classiC','NR','SCIFI', '12-08-1995')

1 rows inserted

INSERT INTO TITLE VALUES (TITLE_ID_SEQ.NEXTVAL, 'My Day Off', 'AWith a little luck and a lot of ingenuity, a teenager skips school for a day in New York','PG','COMEDY','12-JUL-1995')

1 rows insertedINSERT INTO TITLE VALUES (TITLE_ID_SEQ.NEXTVAL, 'Miracles on Ice', 'AWsix-year-old has doubts about Santa Claus, but she discovers that miracles really do exist.','PG','DRAMA','12-SEP-1995')
1 rows inserted

INSERT INTO TITLE VALUES (TITLE_ID_SEQ.NEXTVAL, 'Soda Gang', 'After discovering a cache of drugs, a young couple find themselves pitted against a vicious gang.','NR','ACTION','01-JUN-1995')

1 rows inserted

--4.B
INSERT INTO MEMBER VALUES (MEMBER_ID_SEQ.NEXTVAL, 'Carmen' , 'Velasquez', '283 King Street','Seattle','206-899-6666','08-MAR-1990')

1 rows inserted

INSERT INTO MEMBER VALUES (MEMBER_ID_SEQ.NEXTVAL, 'LaDoris', 'Ngao', '5 Modrany ','Bratislava ','586-355-8882','08-MAR-1990')

1 rows inserted

INSERT INTO MEMBER VALUES (MEMBER_ID_SEQ.NEXTVAL, 'Midori', 'Nagayama','68 ViaCentrale ','Sao Paolo ','254-852-5764','17-JUN-1991')

1 rows inserted

INSERT INTO MEMBER VALUES (MEMBER_ID_SEQ.NEXTVAL, 'Mark', 'Quick-to-See','6921 King Way','Lagos','63-559-7777','07-04-1990')

1 rows inserted

INSERT INTO MEMBER VALUES (MEMBER_ID_SEQ.NEXTVAL, 'Audr', 'Ropeburn' , '86 Chu Street' , 'Hong Kong' , '41-559-87' , '18-06-1991')

1 rows inserted

INSERT INTO MEMBER VALUES (MEMBER_ID_SEQ.NEXTVAL, 'Molly',' Urguhart ','3035 Laurier ','Quebec',' 418-542-9988',' 18-06-1991')

1 rows inserted

--4.c

                                   
CREATE VIEW TITLE_AVAILAS   select t.title ,tc.copy_id, tc.status, r.exp_ret_date  from title t join title_copy tc  on t.title_id = tc.title_id  join rental r  on tc.copy_id =  r.copy_id and tc.title_id = r.title_id
CREATE VIEW correcto.--6

--6.a
INSERT INTO TITLEVALUES (TITLE_ID_SEQ.NEXTVAL, 'Interstellar Wars', 'Futuristic interstellaraction movie. Can the rebels save the humans from the evil empire?','PG','SCIFI','07-JUL-77')

1 rows inserted

insert into title_copyvalues ('1','98','AVAILABLE')

1 rows inserted

insert into title_copyvalues ('2','98','AVAILABLE')

1 rows inserted

--6.b
insert into reservationvalues (sysdate, '101', '98')

1 rows inserted

insert into reservationvalues (sysdate, '104', '97')

1 rows inserted

commit
commited

--6.c
INSERT INTO rental(title_id, copy_id, member_id)VALUES (98,1,101)

1 rows inserted

UPDATE title_copySET status= 'RENTED'WHERE title_id = 98AND copy_id = 1
1 rows updatedDELETEFROM reservationWHERE member_id = 101
1 rows deletedSELECT * FROM title_availORDER BY title, copy_id

---suconsultas

SELECT last_nameFROM employeesWHERE salary =              (SELECT salary                FROM employees                WHERE last_name = 'Abel');

--empleados cuya job histori la fecha final seeste entre 1-01-80 31-10-98
select employee_id from 
;

select salary ,last_name, employee_idfrom employeeswhere employee_id in (  select employee_id   from job_history  where end_date between '01/01/1900' and '31/10/2000');  select distinct e.salary , e.last_name, e.employee_idfrom EMPLOYEES e join job_history jhon E.EMPLOYEE_ID = jh.employee_id  where end_date between '01/01/1900' and '31/10/2000';   

 -----traiga la ciudad donde el salario sea > a 1000select city from locations where location_id in  (select location_id from departments where department_id in     (select department_id from employees where salary > 10000));
select distinct city from LOCATIONS l join DEPARTMENTS don l.location_id = D.LOCATION_IDwhere department_id in (select department_id from EMPLOYEES where salary > 1000);

--empleados q el codigo postal no sea 2901 3095
select employee_id from employees where department_id in  (select department_id from departments where location_id in    (select location_id from locations where postal_code not in ('1730' ,'2901', '3095')));          

 --practica 6--1 Escribe una consulta que mostrar el apellido y fecha de contratación de los empleados en el mismo --departamento que . Excluir Zlotkey......select last_name, hire_date from employees where department_id =  (select department_id from employees where last_name = 'Zlotkey')and last_name <> 'Zlotkey';

--2 Mostrar el numero, el apellido de todos los  los empleados quienes ganan mas que en promedio --salarial, ordenarlo ascendentemente.  select employee_id , last_name , salary from employees where salary  <     (select avg (salary) from employees) order by salary;

  --3 Mostrar una consulta que muestre el numero y el apellido de todos los empleados quienes trabajan --en los departamento,  cuyo apellido contiene una u. select employee_id, last_name FROM employees where department_id in     (select department_id from employees where last_name like '%u%');

--4 Muestra el apellido, numero del departamento, y el id del trabajo  de todos los empleados,  --quienes el departamento esta localizado en 1700.select last_name , department_id, job_id FROM employees where DEPARTMENT_ID in(select department_id from DEPARTMENTS where location_id = 1700);

--5 Muestre el apellido y salario  de cada empleado, quienes reportan a kingselect last_name from EMPLOYEES where manager_id in  (select employee_id FROM EMPLOYEES where last_name = 'King');

--6 Muestre el numero del departamento, apellido y job_id en el departamento ejecutivoselect department_id, last_name , job_id from employees where department_id =   (select department_id from departments where DEPARTMENT_NAME = 'Executive');

--7 Modifica el punto 3, aumentandole el empleado que gane mas q el promedio.select employee_id, last_name, salary from employees WHERE department_id IN   (SELECT department_id FROM employees WHERE last_name like '%u%')    AND salary > (select round( avg (salary)) from employees);    




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

apellidos de los empleados que ganan mas de 12000select last_name, salary from employeeswhere salary > 12000;--Muestra apellidos y departamentos de los empleados que sean con codigo igual a 176select last_name, department_idfrom employeeswhere employee_id = 176;--3select last_name, salaryfrom employees where salary <= 5000 or salary >= 12000;where salary not between 5000 and 12000; 

--4 select last_name, job_id, hire_datefrom employeeswhere hire_date between '20/02/98' and '01/03/98'order by hire_date;

--Fecha actualselect sysdate from dual;--5select last_name as nombre , department_id as platafrom employeeswhere department_id in (20,50)order by first_name;--6select last_name as nombre , salary as platafrom employeeswhere (salary <= 5000 or salary >= 12000)  and (department_id in (20,50));

--7select last_name, hire_datefrom employeeswhere hire_date LIKE '%94';--8select last_name, job_idfrom employeeswhere manager_id is null;
---9select last_name, salary , commission_pctfrom employeeswhere commission_pct is not nullorder by salary desc , commission_pct desc;

--10select first_name, last_name, salary , commission_pctfrom employeeswhere last_name like '__a%';

-11select last_name, salary , commission_pctfrom employeeswhere last_name like '%a%' and last_name like '__e%';
-12select last_name, job_id, salaryfrom employeeswhere job_id IN ('ST_CLERK','SA_REP') and salary not in (2500, 3500, 7000) ;
-13select commission_pct, last_name as nombre , salary as platafrom employeeswhere commission_pct = 0.2;

desc employees;

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

PRACTICA 3  PARTE 1--1

IMPRIME LA FECHA ACTUAL

 select sysdate as fechafrom dual ;

--2 seleccione los campoy y al salario increntelo con el 15% y redondee el salariy select employee_id, last_name, salary as salario,round (salary*0.15) as "new salary"from employees;

--3 ejecure el 2--4 agrege una columba con  el inbremento del salaro select employee_id, last_name, salary as salario,(salary +(salary*0.15)) as "nuevo salario ", (salary*0.15) as incremento  from employees;

--5muestre el aplellido la primera en mayuscual y las demas en minusculas y muestre su tamaño de caracteres de los  empleados q inician con A, J, Mselect INITCAP (last_name), length (last_name) as tamaño  from employees  where last_name like 'J%' or last_name like 'A%' or last_name like 'M%';

--6 muestra los empleados contradados desde la fecha de inicio hasta hoy em meses select last_name,  trunc (months_between  (sysdate , hire_date)) as "meses trabajados"from employees;

--7 concatene con || el apellido el salario y la frace pero le gustaria ganar y multiplique el salario por 3 select last_name || ' gana $'||salary || ' '||'pero le gustaria ganar'|| ' $' || (salary*3) as "salario soñado" from employees;

--8 Muestra el apellido, el salario formateado con * hasta completar 15 caracteres.select last_name, lpad (salary, 15, '*')from employees;
--9select last_name, hire_date,to_char ( next_day( add_months (hire_date, 6),'lunes'), 'fmday ",the" ddspth "of" month "," yyyy')from employees;
desc employees; 

--10 select last_name, hire_date, day hire_datefrom employees;
----------------------------
--practica 5

--1 verdadero

--2 falso

--3 verdadero

--4select max (salary) , min (salary),  sum (salary), round (avg (salary))from employees ;--5select job_id , max (salary) , min (salary),  sum (salary), round (avg (salary))from employees group by job_id ;--6select job_id, count (job_id)from employees group by job_id;

--7select count (manager_id)from employees;--8select max (salary) - min (salary) as diferenciafrom employees;

--9 select manager_id , min(salary)from employeeswhere manager_id is not nullgroup by manager_id;

--10--MONBRE DEMPO  LOCACION NOMBRE EMPLEADOselect count (e.employee_id) as empleado , d.department_name, d.location_id, avg (e.salary)from employees e join departments don e.department_id =  d.department_id group by d.department_name, d.location_id;

--11 ?SELECT COUNT(*) total,SUM(DECODE(TO_CHAR(hire_date, 'YYYY'),1995,1,0))"1995",SUM(DECODE(TO_CHAR(hire_date, 'YYYY'),1996,1,0))"1996",SUM(DECODE(TO_CHAR(hire_date, 'YYYY'),1997,1,0))"1997",SUM(DECODE(TO_CHAR(hire_date, 'YYYY'),1998,1,0))"1998"FROM employees;--12 ?SELECT job_id "Job",SUM(DECODE(department_id , 20, salary)) "Dept 20",SUM(DECODE(department_id , 50, salary)) "Dept 50",SUM(DECODE(department_id , 80, salary)) "Dept 80",SUM(DECODE(department_id , 90, salary)) "Dept 90",SUM(salary) "Total"FROM employeesGROUP BY job_id;
-----------------------------

select * from tab;select last_name ,nvl(to_char(commission_pct), 'sin comision') from employees;select d.department_name, concat ( e.first_name , e.last_name), rpad(' ', trunc(months_between(sysdate ,hire_date)/12)+1,'*')from employees e join departments don e.department_id = d.department_id;SELECT employee_id, last_name, salary, department_idFROM employeesWHERE employee_id = &employee_num ;SELECT last_name, department_id, salary*12FROM employeesWHERE job_id = '&job_title';

SELECT employee_id, last_name, job_id,&column_name, FROM employeesWHERE &conditionORDER BY &order_column ;

SELECT job_id "Job",SUM(DECODE(department_id , 20, salary)) "Dept 20",SUM(DECODE(department_id , 50, salary)) "Dept 50",SUM(DECODE(department_id , 80, salary)) "Dept 80",SUM(DECODE(department_id , 90, salary)) "Dept 90",SUM(salary) "Total"FROM employeesGROUP BY job_id;

--practica 8--1

crea un atabla llamada my_employee
create table MY_EMPLOYEE   (ID number(4) not null,  last_name varchar2(25),  first_name varchar2(25),  userid varchar2(8),  salary number(9,2));
  commit;--2 describala desc my_employee;

--3insert into my_employee (id,last_name, first_name, userid, salary) values (1, 'Patel', 'Rralph', 'rpatel', 895);insert into my_employee (id,last_name, first_name, userid, salary) values (2, 'Dancs', 'Betty', 'bdancs', 860);insert into my_employee (id,last_name, first_name, userid, salary) values (3, 'Biri', 'Ben', 'bbiri', 1100);insert into my_employee (id,last_name, first_name, userid, salary) values (4, 'Newman', 'Chad', 'cnewman', 750);insert into my_employee (id,last_name, first_name, userid, salary) values (5, 'Ropeburn', 'Audrey', 'aropebur', 1550);

--4 execute 

--5 commitcommit;

--6--crear un .sql--10update my_employeeset last_name = 'Drexler'where id = 3;

--11update my_employeeset salary  = 1000 where salary < 900;

--12

select * from my_employee;

Procedimientos y Triggers

Un procedimiento almacenado es un conjunto de instrucciones en PL/SQL, que pueden ser llamado usando el nombre que se le haya asignado.

La sintaxis para crear un procedimiento es la siguiente:

CREATE [OR REPLACE] PROCEDURE name [(param [IN|OUT|IN OUT|] datatype) . . .]
[IS|AS] pl/sql_subprogram

El uso de OR REPLACE permite sobreescribir un procedimiento existente. Si se omite, y el procedimiento ya existe, se producirá un error. Los modificadores IN, OUT, IN OUT indican si el parametro es de entrada, salida o ambos.

A continuación se presenta un ejemplo de creación de un procedimiento:

SQL> CREATE PROCEDURE credit (acc_no IN NUMBER, amount IN NUMBER)
1> AS BEGIN
2> UPDATE accounts
3> SET balance = balance + amount
4> WHERE account_id = acc_no;
5> END;

Este procedimiento actualizará la(s) tupla(s) con numero de cuenta igual al parámetro acc_no con un incremento de amount en el balance de dicha cuenta.

Si se desea eliminar (borrar) un procedimiento almacenado, se usa la instrucción:

SQL> DROP PROCEDURE name;

 

EJERCICIO:

cree una tapla imp con 3 columnas id_emp, dept_id, impuesto
cree un procedimiento para registrar el impuesto a pagar por los salarios devengados, teniendo en cuenta;
* si el salarios es menor a 2500 -> 5% impuesto
* si el salario esta entre 2500 y 3999 -> 6% de impuesto 
* si el salario esta entre 4000 y 8000 -> 8% de impuesto 
* si el salario esta entre 8000 y -> 10% de impuesto 
*/

drop table imp;
create table imp 
( id_emp number(6), dept_id number (4), impuesto number (10,8));

--------------
create or replace procedure imp_sal is
  cursor impuesto is 
    select employee_id, department_id, salary from employees; 

  v_imp number:= 0;
begin
    for imp_rec in impuesto loop
    if  imp_rec.salary  < 2500 then
      v_imp := (imp_rec.salary*0.5);
    end if;
    if  imp_rec.salary  >=2500 and imp_rec.salary <= 3999 then
      v_imp := (imp_rec.salary*0.6);
    end if;
    if  imp_rec.salary  >=4000 and imp_rec.salary <= 8000 then
      v_imp := (imp_rec.salary*0.8);
    end if;
    if  imp_rec.salary  > 8000 then
      v_imp := (imp_rec.salary*0.10);
    end if;
  insert into imp values (imp_rec.employee_id, imp_rec.department_id, v_imp); 
  end loop;
end imp_sal;
  
  
---LLAMADO

begin
imp_sal; 
end;

Procedimientos almacenados
    Un procedimiento es un subprograma que ejecuta una acción especifica y que no devuelve ningún valor. Un procedimiento tiene un nombre, un conjunto de parámetros (opcional) y un bloque de código.
    La sintaxis de un procedimiento almacenado es la siguiente:
 

CREATE [OR REPLACE] 
PROCEDURE  [(  [IN|OUT|IN OUT] , 
                               [IN|OUT|IN OUT] , ...)] 
IS
  -- Declaracion de variables locales
BEGIN
  -- Sentencias
[EXCEPTION]
  -- Sentencias control de excepcion
END [];
 
    El uso de OR REPLACE permite sobreescribir un procedimiento existente. Si se omite, y el 
procedimiento existe, se producirá, un error.
    La sintaxis es muy parecida a la de un bloque anónimo, salvo porque se reemplaza la seccion DECLARE por la secuencia PROCEDURE ... IS en la especificación del procedimiento.
    Debemos especificar el tipo de datos de cada parámetro. Al especificar el tipo de dato del parámetro no debemos especificar la longitud del tipo.
    Los parámetros pueden ser de entrada (IN), de salida (OUT) o de entrada salida (IN OUT). El valor por defecto es IN, y se toma ese valor en caso de que no especifiquemos nada.
 
 

CREATE OR REPLACE 
PROCEDURE Actualiza_Saldo(cuenta NUMBER, 
                          new_saldo NUMBER)
IS
  -- Declaracion de variables locales
BEGIN
  -- Sentencias
  UPDATE SALDOS_CUENTAS 
	SET SALDO = new_saldo, 
            FX_ACTUALIZACION = SYSDATE
  WHERE CO_CUENTA = cuenta;

END Actualiza_Saldo; 
También podemos asignar un valor por defecto a los parámetros, utilizando la clausula DEFAULT
 o el operador de asiganción (:=) .
 
 

CREATE OR REPLACE 
PROCEDURE Actualiza_Saldo(cuenta NUMBER, 
                          new_saldo NUMBER DEFAULT 10 )
IS
  -- Declaracion de variables locales
BEGIN
  -- Sentencias
  UPDATE SALDOS_CUENTAS 
	SET SALDO = new_saldo, 
            FX_ACTUALIZACION = SYSDATE
  WHERE CO_CUENTA = cuenta;

END Actualiza_Saldo; 
 
    Una vez creado y compilado el procedimiento almacenado podemos ejecutarlo. Si el sistema nos indica que el procedimiento se ha creado con errores de compilación podemos ver estos errores de compilacion con la orden SHOW ERRORS en SQL *Plus.
    Existen dos formas de pasar argumentos a un procedimiento almacenado a la hora de ejecutarlo (en realidad es válido para cualquier subprograma). Estas son:
Notación posicional: Se pasan los valores de los parámetros en el mismo orden en que el procedure los define.
 
 

BEGIN
	Actualiza_Saldo(200501,2500);
        COMMIT;

END; 
 
Notación nominal:Se pasan los valores en cualquier orden nombrando explicitamente el parámetro.
 
BEGIN
Actualiza_Saldo(cuenta => 200501,new_saldo  => 2500);
 	COMMIT;

END;

TRIGGERS

Un trigger es un bloque PL/SQL asociado a una tabla, que se ejecuta cuando una determinada instrucción en SQL se va a ejecutar sobre dicha tabla.
La sintaxis para crear un trigger es la siguiente:
CREATE [OR REPLACE] TRIGGER 
{BEFORE|AFTER} {DELETE|INSERT|UPDATE [OF col1, col2, . . ., colN]
[OR {DELETE|INSERT|UPDATE [OF col1, col2, . . ., colN]. . .]}
ON table
[REFERENCING OLD AS oldname, NEW as newname]
[FOR EACH ROW [WHEN (condition)]]
pl/sql_block
El uso de OR REPLACE permite sobreescribir un trigger existente. Si se omite, y el trigger existe, se producirá, un error.
El modificador FOR EACH ROW indica que el trigger se disparará cada vez que se desee hacer operaciones sobre una fila de la tabla. Si se acompaña del modificador WHEN, se establece una restricción; el trigger solo actuará, sobre las filas que satisfagan la restricción.
A continuación se presenta un ejemplo de creación de un trigger:
SQL> CREATE TRIGGER salary_check
1> BEFORE
2> INSERT OR UPDATE OF sal, job
3> ON employee
4> FOR EACH ROW
5> WHEN (new.job <> 'PRESIDENT')
6> DECLARE
7> minsal NUMBER
8> maxsal NUMBER
9> BEGIN
10> /* Se obtienen los valores minimo y maximo para el salario de */
11> /* un cargo determinado, usando la tabla sal_guide */
12> SELECT minsal, maxsal
13> INTO minsal, maxsal
14> FROM sal_guide
15> WHERE job = :new.job
16> /* Si el salario del empleado a insertar/modificar esta por */
17> /* debajo del minimo, o por encima del maximo, se genera */
19> /* un error. */
20> IF (:new.sal < minsal OR :new.sal > maxsal)
21> THEN raise_application_error(-20601, 'Salary '||:new.sal||
22> ' out of range for job '||:new.job||' for employee '||
23> :new.ename);
24> END IF;
25 > END;
Este trigger impide que se agregue o modifique un empleado con el sueldo mayor o menor que los valores maximo y minimo respectivamente para su cargo. Se agrega la restricción de que el trigger no se dispararán si el cargo es PRESIDENTE.
Si se desea eliminar (borrar) un trigger, se usa la instrucción:
SQL> DROP TRIGGER name
 
                                                              EJERCICIOS RESUELTOS

create table Cuenta_emp 
(id_dept number (4), 
nombre_dept varchar2 (25),
num_emp number (4),
sal_prom number (20));


2.Cree un procedimiento para llenar la tabla recien creada, de tal forma que en los siguientes campos se guarde la siguiente información:
ID_DEPT: ID del departamento
NOMBRE_DEPT: Nombre del departamento
NUM_EMP: Numero de empleados en ese departamento
SAL_PROM: Promedio de salarios en ese departamento.

llenar esta unica tabla

set serveroutput on
create or replace procedure llenar 
(p_id cuenta_emp.id_dept%type,
p_nombre cuenta_emp.nombre_dept%type,
p_numero cuenta_emp.num_emp%type,
p_salario cuenta_emp.sal_prom%type)
is

begin
insert into cuenta_emp (id_dept, nombre_dept, num_emp, sal_prom)
values (p_id, p_nombre, p_numero, p_salario);
exception
when others then
dbms_output.put_line('Se produjo un error');
end llenar;

-------
Llenarla con datos de las tablas Employees y Departments

create or replace procedure llenar (p_id number)is
 
   cursor llenar is
   select d.department_id, d.department_name, count (e.employee_id), avg(e.salary)
   from employees e join departments d 
   on d.department_id = e.department_id
   where d.department_id = p_id
   group by d.department_id, d.department_name;
 
 v_ID_DEPT departments.department_id%TYPE;
 v_NOMBRE_DEPT departments.department_name%type;
 v_NUM_EMP number;
 v_SAL_PROM number;
 
   begin
   open llenar;
   fetch llenar into v_id_dept, v_nombre_dept, v_num_emp, v_sal_prom;
   insert into cuenta_emp values (v_id_dept, v_nombre_dept, v_num_emp, v_sal_prom);
   close llenar;
end;

Llamado al Procedimiento

SET SERVEROUTPUT ON
   DECLARE
   V_id_depto EMPLOYEES.DEPARTMENT_ID%TYPE:=0;  
   BEGIN
   V_id_depto:= &nume_dpto;
   llenar (V_id_depto);
   dbms_output.put_line ('se inserto '||v_id_depto);
end;

select * from cuenta_emp;

3. Realice un trigger de tal forma que se mantengan actualizados los datos en la tabla recien creada, de acuerdo a los posibles cambios que puedan presentarse en los registros de la tabla EMPLOYEES y DEPARTMENTS.

create or replace trigger actual
before update on cuenta_emp
for each row
begin
insert into cuenta_emp (id_dept, nombre_dept, num_emp, sal_prom)
values (:new.id_dept, :new.nombre_dept, :new.num_emp, :new.sal_prom);
end;

Excepciones

Las excepciones en el sistema gestor Oracle y el lenguaje PL/SQL, nos ayudan a detectar y tratar errores en tiempo de ejecución.

Por Sara Alvarez

Atención: Contenido exclusivo de DesarrolloWeb.com. No reproducir. Copyright.

En este artículo del Manual de PL/SQL de Oracle vamos a ver lo que son las excepciones, para qué sirven y cómo utilizarlas. Daremos un repaso también a los tipos de excepciones, las excepciones definidas por el usuario y la sintaxis con la que tenemos que especificarlas.

Por último, de paso que vemos cosas acerca del tratamiento de errores en PL/SQL, explicaremos el RAISE_APPLICATION_ERROR, un componente del sistema gestor de base de datos Oracle que ayuda a gestionar errores y sus mensajes de error.

Qué son las excepciones en Oracle

Las excepciones, presentes en la mayoría de los lenguajes de programación, sirven para tratar errores en tiempo de ejecución. En el sistema que nos ocupa, Oracle, sirven también para definir qué se debe hacer frente a errores en sentencias definidas por el usuario. Cuando se produce un error PL/SQL levanta una excepción y pasa el control a la sección excepción correspondiente al bloque PL/SQL.

El formato sería el siguiente:

BEGIN
   .........
   ......
   ......
EXCEPTION
   WHEN <nombre_excepción> THEN
      <instrucciones>;
   ......
   [WHEN OTHERS THEN <instrucciones>;]
END;

Excepciones predefinidas

Son aquellas que se disparan automáticamente al producirse determinados errores. Estas son las más comunes:

too_many_rows: Se produce cuando select … into devuelve más de una fila. 
no_data_found: se produce cuando un select …. into no devuelve ninguna fila. 
login_denied: error cuando intentamos conectarnos a Oracle con un login y clave no validos. 
not_logged_on: se produce cuando intentamos acceder a la base de datos sin estar conectados. 
program_error: se produce cuando hay un problema interno en la ejecución del programa. 
value_error: se produce cuando hay un error aritmético o de conversión. 
zero_divide: se puede cuando hay una división entre 0. 
dupval_on_index: se crea cuando se intenta almacenar un valor que crearía duplicados en la clave primaria o en una columna con restricción UNIQUE. 
invalid_number: se produce cuando se intenta convertir una cadena a un valor numérico.

Hay alguna más pero estas son las más utilizadas y tenemos que tener en cuenta que no es necesario declararlas en la sección DECLARE.

Excepciones definidas por el usuario

Son aquellas que crea el usuario. Para ello se requieren tres pasos: 

1. Definición: se realiza en la zona de DECLARE con el siguiente formato: nombre_excepción EXCEPTION
2. Disparar o levantar la excepción mediante la orden raise: RAISE ;
3. Tratar la excepción en el apartado EXCEPTION: WHEN THEN ;

Para que esto quede más claro ponemos un ejemplo a continuación.

DECLARE
...
Importe_mal EXCEPTION;
...
BEGIN
...
IF precio NOT BETWEEN mínimo and máximo THEN
   RAISE importe_mal;
END IF;
...
EXCEPTION
   WHEN importe_mal THEN DBMS_OUTPUT.PUT_LINE("Importe incorrecto");
   ...
END;

Otras excepciones

Existen otros errores internos de Oracle que no tienen asignada una excepción, sino un código de error y un mensaje, a los que se accede mediante funciones SQLCODE y SQLERRM. Cuando se produce un error de estos se trasfiere directamente el control a la sección EXCEPTION donde se tratara el error en la clausula WHEN OTHERS de la siguiente forma:

WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error'||SQLCODE||SQLERRM.)

Utilización de RAISE_APPLICATION_ERROR

En el paquete DBMS_STANDARD se incluye un procedimiento llamado RAISE_APPLICATION_ERROR que nos sirve para levantar errores y definir mensajes de error. Su formato es el siguiente:

RAISE_APPLICATION_ERROR(numero_error,mensaje_error);

Es importante saber que el numero de error esta comprendido entre -20000 y -20999 y el mensaje es una cadena de caracteres de hasta 512 bytes. 
Este procedimiento crea una excepción que solo puede ser tratada en WHEN OTHERS.

Ponemos un ejemplo para que nos quede más claro.

CREATE or REPLACE PROCEDURE subir_horas (emple NUMBER, horas_subir NUMBER)
IS
   horas_actuales NUMBER;
BEGIN
   Select horas into horas_actuales from empleados where id_empleado=emple;
   if horas_actuales is NULL then
      RAISE_APPLICATION_ERROR(-20010,'No tiene horas');
   else
      update empleados set horas=horas_actuales + horas_subir where id_empleado=emple;
   end if;
End subir_horas;

Manejo de excepciones

    En PL/SQL una advertencia o condición de error es llamada una excepción.

    Las excepciones se controlan dentro de su propio bloque.La estructura de bloque de una excepción se muestra a continuación.

 

DECLARE  -- Declaraciones BEGIN   -- Ejecucion EXCEPTION   -- Excepcion END;

 

    Cuando ocurre un error, se ejecuta la porción del programa marcada por el bloque EXCEPTION, transfiriéndose el control a ese bloque de sentencias.

    El siguiente ejemplo muestra un bloque de excepciones que captura las excepciones NO_DATA_FOUND yZERO_DIVIDE. Cualquier otra excepcion será capturada en el bloque WHEN OTHERS THEN.

 

DECLARE  -- Declaraciones BEGIN   -- Ejecucion EXCEPTION WHEN NO_DATA_FOUND THEN   -- Se ejecuta cuando ocurre una excepcion de tipo NO_DATA_FOUND WHEN ZERO_DIVIDE THEN   -- Se ejecuta cuando ocurre una excepcion de tipo ZERO_DIVIDE WHEN OTHERS THEN   -- Se ejecuta cuando ocurre una excepcion de un tipo no tratado -- en los bloques anteriores END;

 

    Como ya hemos dicho cuando ocurre un error, se ejecuta el bloque EXCEPTION, transfiriéndose el control a las sentencias del bloque. Una vez finalizada la ejecución del bloque de EXCEPTION no se continua ejecutando el bloque anterior.

    Si existe un bloque de excepcion apropiado para el tipo de excepción se ejecuta dicho bloque. Si no existe un bloque de control de excepciones adecuado al tipo de excepcion se ejecutará el bloque de excepcion WHEN OTHERS THEN (si existe!). WHEN OTHERS debe ser el último manejador de excepciones.

    Las excepciones pueden ser definidas en forma interna o explícitamente por el usuario. Ejemplos de excepciones definidas en forma interna son la división por cero y la falta de memoria en tiempo de ejecución. Estas mismas condiciones excepcionales tienen sus propio tipos y pueden ser referenciadas por ellos:ZERO_DIVIDE y STORAGE_ERROR.

    Las excepciones definidas por el usuario deben ser alcanzadas explícitamente utilizando la sentencia RAISE.

    Con las excepciones se pueden manejar los errores cómodamente sin necesidad de mantener múltiples chequeos por cada sentencia escrita. También provee claridad en el código ya que permite mantener las rutinas correspondientes al tratamiento de los errores de forma separada de la lógica del negocio.

Excepciones predefinidas 

    PL/SQL proporciona un gran número de excepciones predefinidas que permiten controlar las condiciones de error más habituales.

    Las excepciones predefinidas no necesitan ser declaradas. Simplemente se utilizan cuando estas son lanzadas por algún error determinado.

    La siguiente es la lista de las excepciones predeterminadas por PL/SQL y una breve descripción de cuándo son accionadas:

 

Excepcion	Se ejecuta ...	SQLCODE
ACCESS_INTO_NULL	El programa intentó asignar valores a los atributos de un objeto no inicializado	-6530
COLLECTION_IS_NULL	El programa intentó asignar valores a una tabla anidada aún no inicializada	-6531
CURSOR_ALREADY_OPEN	El programa intentó abrir un cursor que ya se encontraba abierto. Recuerde que un cursor de ciclo FOR automáticamente lo abre y ello no se debe especificar con la sentencia OPEN	-6511
DUP_VAL_ON_INDEX	El programa intentó almacenar valores duplicados en una columna que se mantiene con restricción de integridad de un índice único (unique index)	-1
INVALID_CURSOR	El programa intentó efectuar una operación no válida sobre un cursor	-1001
INVALID_NUMBER	En una sentencia SQL, la conversión de una cadena de caracteres hacia un número falla cuando esa cadena no representa un número válido	-1722
LOGIN_DENIED	El programa intentó conectarse a Oracle con un nombre de usuario o password inválido	-1017
NO_DATA_FOUND	Una sentencia SELECT INTO no devolvió valores o el programa referenció un elemento no inicializado en una tabla indexada	100
NOT_LOGGED_ON	El programa efectuó una llamada a Oracle sin estar conectado	-1012
PROGRAM_ERROR	PL/SQL tiene un problema interno	-6501
ROWTYPE_MISMATCH	Los elementos de una asignación (el valor a asignar y la variable que lo contendrá) tienen tipos incompatibles. También se presenta este error cuando un parámetro pasado a un subprograma no es del tipo esperado	-6504
SELF_IS_NULL	El parámetro SELF (el primero que es pasado a un método MEMBER) es nulo	-30625
STORAGE_ERROR	La memoria se terminó o está corrupta	-6500
SUBSCRIPT_BEYOND_COUNT	El programa está tratando de referenciar un elemento de un arreglo indexado que se encuentra en una posición más grande que el número real de elementos de la colección	-6533
SUBSCRIPT_OUTSIDE_LIMIT	El programa está referenciando un elemento de un arreglo utilizando un número fuera del rango permitido (por ejemplo, el elemento “-1”)	-6532
SYS_INVALID_ROWID	La conversión de una cadena de caracteres hacia un tipo rowid falló porque la cadena no representa un número	-1410
TIMEOUT_ON_RESOURCE	Se excedió el tiempo máximo de espera por un recurso en Oracle	-51
TOO_MANY_ROWS	Una sentencia SELECT INTO devuelve más de una fila	-1422
VALUE_ERROR	Ocurrió un error aritmético, de conversión o truncamiento. Por ejemplo, sucede cuando se intenta calzar un valor muy grande dentro de una variable más pequeña	-6502
ZERO_DIVIDE	El programa intentó efectuar una división por cero	-1476

 

Excepciones definidas por el usuario 

    PL/SQL permite al usuario definir sus propias excepciones, las que deberán ser declaradas y lanzadas explícitamente utilizando la sentencia RAISE.

    Las excepciones deben ser declaradas en el segmento DECLARE de un bloque, subprograma o paquete. Se declara una excepción como cualquier otra variable, asignandole el tipo EXCEPTION. Las mismas reglas de alcance aplican tanto sobre variables como sobre las excepciones.

 

DECLARE  -- Declaraciones MyExcepcion EXCEPTION; BEGIN   -- Ejecucion EXCEPTION   -- Excepcion END;

 

Reglas de Alcance:

Una excepcion es válida dentro de su ambito de alcance, es decir el bloque o programa donde ha sido declarada. Las excepciones predefinidas son siempre válidas.

Como las variables, una excepción declarada en un bloque es local a ese bloque y global a todos los sub-bloques que comprende.

La sentencia RAISE

    La sentencia RAISE permite lanzar una excepción en forma explícita. Es posible utilizar esta sentencia en cualquier lugar que se encuentre dentro del alcance de la excepción.

 

DECLARE  -- Declaramos una excepcion identificada por VALOR_NEGATIVO VALOR_NEGATIVO EXCEPTION; valor NUMBER; BEGIN   -- Ejecucion valor := -1; IF valor < 0 THEN RAISE VALOR_NEGATIVO; END IF; EXCEPTION   -- Excepcion WHEN VALOR_NEGATIVO THEN dbms_output.put_line('El valor no puede ser negativo'); END;

 

Con la sentencia RAISE podemos lanzar una excepción definida por el usuario o predefinida, siendo el comportamiento habitual lanzar excepciones definidas por el usuario.

    Recordar la existencia de la excepción OTHERS, que simboliza cualquier condición de excepción que no ha sido declarada. Se utiliza comúnmente para controlar cualquier tipo de error que no ha sido previsto. En ese caso, es común observar la sentencia ROLLBACK en el grupo de sentencias de la excepción o alguna de las funciones SQLCODE – SQLERRM, que se detallan en el próximo punto.

Uso de SQLCODE y SQLERRM

Al manejar una excepción es posible usar las funciones predefinidas SQLCode y SQLERRM para aclarar al usuario la situación de error acontecida.

SQLcode devuelve el número del error de Oracle y un 0 (cero) en caso de exito al ejecutarse una sentencia SQL.

Por otra parte, SQLERRM devuelve el correspondiente mensaje de error.

Estas funciones son muy útiles cuando se utilizan en el bloque de excepciones, para aclarar el significado de la excepción OTHERS.

Estas funciones no pueden ser utilizadas directamente en una sentencia SQL, pero sí se puede asignar su valor a alguna variable de programa y luego usar esta última en alguna sentencia.

 

DECLARE   err_num NUMBER;   err_msg VARCHAR2(255); result NUMBER; BEGIN   SELECT 1/0 INTO result FROM DUAL;   EXCEPTION  WHEN OTHERS THEN     err_num := SQLCODE;   err_msg := SQLERRM;   DBMS_OUTPUT.put_line('Error:'||TO_CHAR(err_num));   DBMS_OUTPUT.put_line(err_msg); END;

 

También es posible entregarle a la función SQLERRM un número negativo que represente un error de Oracle y ésta devolverá el mensaje asociado.

 

DECLARE   msg VARCHAR2(255); BEGIN   msg := SQLERRM(-1403);   DBMS_OUTPUT.put_line(MSG); END;

 

 

 

Cursores

Los cursores se utilizan en PL/SQL para manejar las sentencias SELECT. Un cursoresta formado por un conjunto de registros devueltos por una instrucción SQL del tipoSELECT. Desde un punto de visto interno a la base de datos Oracle, los cursores son segmentos de memoria utilizados para realizar operaciones con los registros devueltos tras ejecutar una sentencia SELECT.

Se pueden distinguir dos tipos de cursores:

- Cursores implícitos: Se utilizan cuando la sentencia SELECT devuelve un solo registro y su formato es como sigue:

     DECLARE
       lsalario empleados.salario%TYPE; 
       ldni empleados.dni%TYPE; 
     BEGIN
       SELECT salario, dni
       INTO lsalario, ldni
       FROM empleados
       WHERE nombre = 'Juan'
       AND apellidos = 'Rodrigo Comas';
       /* Resto de sentencias del bloque */
     END;

Nota: Mucha gente considera que las sentencias UPDATE, dentro de un bloquePLSQL, son también cursores implícitos, no obstante, yo prefiero no incluirlas dentro de este concepto.

- Cursores explícitos: Se utilizan cuando la sentencia SELECT puede devolver varios registros. También se pueden utilizar en consultas que devuelvan un solo registro por razones de eficiencia con respecto a los cursores implícitos, eficiencia que mejorará especialmente si el cursor explícito se tiene que ejecutar varias veces dentro del bloque de código PL/SQL.

Un cursor explícito tiene que ser definido previamente como cualquier otra variablePLSQL y debe serle asignado un nombre. Veamos un ejemplo que muestra el DNI y el salario de los trabajadores incluidos en la tabla empleados:

     DECLARE
       CURSOR cemp IS 
       SELECT salario, dni
       FROM empleados;
       cepm_rec cemp%ROWTYPE; 
     BEGIN
       FOR cemp_rec IN cemp
       LOOP
         DBMS_OUTPUT.PUT_LINE
           (cemp_rec.dni || ' ' || cemp_rec.salario);
       END LOOP;
     END;

Los cursores explícitos admiten el uso de parámetros. Los parámetros deben declararse junto con el cursor. Por ejemplo: 

     DECLARE
       CURSOR cemp(pnombre IN VARCHAR2) IS 
       SELECT salario, dni
       FROM empleados
       WHERE nombre = pnombre;
       cepm_rec cemp%ROWTYPE; 
       vnombre VARCHAR2(20); 
     BEGIN
       vnombre := 'Juan';
       DBMS_OUTPUT.PUT_LINE
         ('Sueldo de los empleados con nombre ' || vnombre);
       FOR cemp_rec IN cemp(vnombre)
       LOOP
         DBMS_OUTPUT.PUT_LINE
           (cemp_rec.dni || ' ' || cemp_rec.salario);
       END LOOP;
     END;

En los ejemplos anteriores los cursores se han controlado con la sentencia FOR pero también pueden controlarse mediante el uso de las sentencias OPEN, FETCH yCLOSE. La sentencia OPEN identifica el cursor que se tiene que utilizar. La sentenciaFETCH pone, registro a registro, los valores devueltos por el cursor en las variables correspondientes, variables que pueden estar constituidas por una lista de variables o un registro PLSQL (este es el caso de los ejemplos que incluyo en este artículo). Por último, la sentencia CLOSE cierra el cursor y libera la memoria reservada. Veamos como quedaría nuestro ejemplo utilizando este tipo de sentencias en lugar de utilizar la sentencia FOR:

     DECLARE
       CURSOR cemp(pnombre IN VARCHAR2) IS 
       SELECT salario, dni
       FROM empleados
       WHERE nombre = pnombre;
       cepm_rec cemp%ROWTYPE; 
       vnombre VARCHAR2(20); 
     BEGIN
       vnombre := 'Juan';
       DBMS_OUTPUT.PUT_LINE
         ('Sueldo de los empleados con nombre ' || vnombre);
       OPEN cemp(vnombre);
       LOOP
         FETCH cemp INTO cemp_rec;
         DBMS_OUTPUT.PUT_LINE
           (cemp_rec.dni || ' ' || cemp_rec.salario);
         EXIT WHEN cemp%NOTFOUND; -- Último registro.
       END LOOP;
       DBMS_OUTPUT.PUT_LINE
         ('Número de empleados procesados ' || cemp%ROWCOUNT);
       CLOSE cemp;
     END;

Sólo me queda señalar que existe una tercera opción para manejar cursores que a mí, particularmente, no me gusta utilizar pero que no quiero omitir:

     DECLARE
       TYPE ecursor IS REF CURSOR RETURN empleados%ROWTYPE;
       cemp ecursor;
       cepm_rec empleados%ROWTYPE; 
     BEGIN
       OPEN cemp FOR SELECT * FROM empleados;
       FOR cemp_rec IN cemp
       LOOP
         DBMS_OUTPUT.PUT_LINE
           (cemp_rec.dni || ' ' || cemp_rec.salario);
       END LOOP;
     END;

Finalmente sólo mencionar que existen cuatro tipos de atributos que nos permiten controlar la ejecución de un cursor:

- %ISOPEN: Devuelve "true" si el cursor está abierto.
- %FOUND: Devuelve "true" si el registro fue satisfactoriamente procesado.
- %NOTFOUND: Devuelve "true" si el registro no pudo ser procesado. Normalmente esto ocurre cuando ya se han procesado todos los registros devueltos por el cursor.
- %ROWCOUNT: Devuelve el número de registros que han sido procesados hasta ese momento.

 

PL/SQL
Definición de Cursor

• Útiles para las consultas que devuelven más de una fila.
• Son declarados y nombrados por el programador, y manipulados por medio de sentencias específicas en las acciones ejecutables del bloque.

PL/SQL
Control de Cursores

• 1º. Crear un área SQL específica  DECLARE
• 2º. Identificar el juego activo  OPEN
• 3º. Cargar la fila actual en variables  FETCH
• 4º. Si todavía existen filas sin leer, volver a 3º.
• 5º. Si no existen más filas a leer  CLOSE

PL/SQL
Declaración del Cursor

• Sintaxis:

• No incluya la cláusula INTO en la declaración del cursor.
• Si es necesario procesar filas en algún orden, incluya la cláusula ORDER BY.

PL/SQL
Ejemplo de Declaración

PL/SQL
Apertura del Cursor

• Sintaxis:

• Si la consulta no devuelve ninguna fila, no se producirá ninguna excepción al abrir el cursor.
• Para los cursores declarados con la cláusula FOR UPDATE, la sentencia OPEN bloquea estas filas.

PL/SQL
Recuperación de Datos

• Sintaxis:

• Incluya, en el FETCH, el mismo número de variables que las definidas en el cursor, y en el mismo orden.
• Compruebe si el cursor tiene filas.

PL/SQL
Cierre del Cursor

• Sintaxis:

• Cierre el cursor una vez completado el procesamiento de las filas.
• Si es necesario, vuelva a abrir el cursor.
• No intente recuperar los datos de un cursor una vez que ha sido cerrado (INVALID_CURSOR).

PL/SQL
Atributos de Cursores

• Proporcionan información de estado del cursor:

PL/SQL
El Atributo %ISOPEN

• Utilice el atributo de cursor %ISOPEN antes de ejecutar una recuperación para comprobar si el cursor está abierto.
• Ejemplo:

PL/SQL
Atributos %NOTFOUND, %ROWCOUNT

• Utilice %ROWCOUNT para recuperar un número exacto de filas.
• Utilice %NOTFOUND para determinar cuándo salir del bucle de lectura del cursor.
• Antes de la primera recuperación, %NOTFOUND es NULL, así que si FETCH no se ejecuta nunca satisfactoriamente, no se saldría nunca del bucle de lectura.

PL/SQL
Ejemplo %NOTFOUND, %ROWCOUNT

• Ejemplo:

PL/SQL
Bucles FOR de Cursor

• Sintaxis:

• Apertura, recuperación y cierre implícitos.
• No declare el registro, está declarado implícitamente.

PL/SQL
Cursores con Parámetros

• Sintaxis:

• Permite abrir un cursor varias veces con un juego activo distinto cada vez.

PL/SQL
Cursores con Parámetros

• Cada parámetro formal de la declaración del cursor debe tener un parámetro real correspondiente en la sentencia OPEN.
• La sintaxis de los parámetros es:

PL/SQL
Cursores con Parámetros

• Transfiera el número de departamento y el cargo a la cláusula WHERE:

PL/SQL
Cláusula FOR UPDATE

• Sintaxis:

• El bloqueo explícito le permite denegar el acceso mientras dura una transacción.
• Bloquee las filas antes de la actualización o supresión.
• La cláusula FOR UPDATE es la última cláusula de una sentencia SELECT, incluso después del ORDER BY.
• NOWAIT devuelve un error de Oracle si las filas han sido bloqueadas por otra sesión, de lo contrario se espera.

PL/SQL
Cláusula WHERE CURRENT OF

• Sintaxis:

• Incluya la cláusula FOR UPDATE en la definición del cursor para bloquear las filas.
• Especifique WHERE CURRENT OF en la sentencia UPDATE o DELETE para referirse a la fila actual del cursor.

 

 

EJERCICIOS RESUELTOS

 

cusrsor 
set serveroutput on
declare 
cursor nombre is
  select e.first_name, e.last_name, d.department_name
  from employees e join departments d 
  on e.department_id = d.department_id;
  v_nombre employees.first_name%type;
  v_apellido employees.last_name%type;
  c_nomdepto departments.department_name%type;
  cont number (2):=0;
begin
open nombre;
  while cont < 10 loop
  fetch nombre into v_nombre, v_apellido, c_nomdepto;
  dbms_output.put_line ('el nombre dek empleado es: '||v_nombre||'Su apellido es' ||v_apellido||'y su depto es:'||c_nomdepto);
  cont:= cont +1;
  end loop;
close nombre;
end;

  --2
--traiga todos los empleados y el nombre del pais q esten en los deptos: sales 60 e it 80 
set serveroutput on
declare
cursor pais is 
select e.employee_id, c.country_name
from employees e join departments d 
on e.department_id = d.department_id
join locations l 
on d.location_id = l.location_id
join countries c
on l.country_id = c.country_id
where d.department_name in ('Sales','IT');
empleado employees.employee_id%type;
npais countries.country_name%type;
cont number(10):=0;
begin 
open pais;

while cont < 50 loop
  fetch pais into empleado, npais;
  EXIT WHEN pais%NOTFOUND;
  dbms_output.put_line('el id de emlpeado es: '||empleado||'y su pais es '||npais); 
    cont:= cont +1;
  end loop;
close pais;
end;

----

 

taller  pl
--parte1
--1 evalue cada una de las declaraciones. determina cualles de los temas son legales explique
--a 
	declare
  v_id number (4);
  --si es permitido ya q es la forma correcta de declararlo con su tipo de dato 
--b
  declare 
  v_x, v_y, v_z varchar (10);
  --NO es permitido ya q no se puede declarar mas de una variable con un solo tipo de dato
--c
  declare 
  v_britdate date not null;
  --si porque se esta colocando una restriccion a un campo
--d
  declare v_in_stock boolean := 1;
  --NO porq le esta asignando al tipo de dato booleano el valor no permitido debe colocarce "true" o "false"
---2 en cada una de las siguientes asignaciones, indicar si la declaración de tipo dato es válida y 
--  válide de lo que el resultado será
--a 
  declare 
  v_days_to_go := v_due_date - sysdate;
-- Esta asignacion es valida unicamente si las dos variables con tipo date, si la variable inicial es de tipo "Varchar", se debe convertir al tipo correcto.
-B
 v_sender := USER || ':'|| to_char(v_dept_no);
-- Es permitido, ya que es una cadena de caracteres, pues se esta convirtiendo una variable de tipo number a caracter. 
--C
 v_sum := $100,000 + $250,000;
--Esta asignacion no es permitida ya que el formato de los valores, requiere un formato especial, en cuanto se refiere a "$" y ",", por tal motivo, esta asignacio arrojara un error.

--D
 v_flag := TRUE;
 --Esta asignacion si es valida, ya que anteriormente pudo ser declarada con tipo de dato Boolean.

--E
 v_n1 := v_n2 > (2 * v_n3);
--esta asignacion es valida, ya que a la variable inicial le estoy asignando operaciones matematicas, obviamente declaradas enteriormente con tipo de dato

--F
 v_value := NULL;
--Esta asignacion es permitida, ya que le estoy asignando un valor nulo a la variable.

--3
--Crear un bloque anonimo llamado "My PL/SQL Block Works" 

set serveroutput on
declare
scrip varchar2(100);
begin
scrip:= '&Mensaje';
dbms_output.put_line (scrip);
end;

--4.

set serveroutput on
declare
Numero number(10);
Caracter varchar2 (100);
begin
Caracter:= '&Caracter';
numero:=to_number(substr(caracter, 3, 5));
dbms_output.put_line (Numero);
dbms_output.put_line (caracter);
end;
--fin

Bloques Anonimos

Dentro de la seccion DECLARE de un bloque anónimo podemos declarar funciones y procedimientos almacenados y ejecutarlos desde el bloque de ejecución del script.

Este tipo de subprogramas son menos conocidos que los procedimientos almacenados, funciones y triggers, pero son enormemente útiles.

El siguiente ejemplo declara y ejecuta utiliza una funcion (fn_multiplica_x2).

 

DECLARE       idx NUMBER;        FUNCTION fn_multiplica_x2(num NUMBER)        RETURN NUMBER        IS          result NUMBER;        BEGIN          result := num *2;          return result;        END fn_multiplica_x2; BEGIN      FOR idx IN 1..10      LOOP        dbms_output.put_line ('Llamada a la funcion ... '||TO_CHAR(fn_multiplica_x2(idx)));      END LOOP;      END;

 

 Notese que se utiliza la funcion TO_CHAR para convertir el resultado de la función fn_multiplica_x2 (numérico) en alfanumérico y poder mostrar el resultado por pantalla.

Los bloques PL/SQL son unidades de código que se envían al servidor para que sean procesadas conjuntamente, obviamente esto es mejor para el rendimiento del sistema que si se envían las sentencias SQL separadamente una a una. Es decir, cuando se requiere la ejecución de un bloque PL/SQL, este es enviado al servidor PL/SQL donde se ejecuta el código PL/SQL y las sentencias SQL son enviadas al servidor Oracle, como el servidor PL/SQL reside en el servidor Oracle, el código se ejecuta eficientemente.

Un bloque de código PL/SQL contiene las siguientes secciones:

 

   1. La sección de declaraciones donde se definen los distintos objetos que se van a utilizar en el bloque (variables, constantes, cursores, etcétera). Esta sección es opcional.
   2. La sección de instrucciones. Sección obligatoria que contiene las sentencias ejecutables de SQL y PL/SQL.
   3. La sección de excepciones. Es una sección opcional donde se tratan todos los errores que puedan surgir a la hora de ejecutar las sentencias SQL y PL/SQL de la sección de instrucciones.

Ejemplo de bloque:

 
  DECLARE IS / AS
     [declaraciones]
  BEGIN
     [instrucciones]
  EXCEPTION
     [tratamiento de excepciones]
  END;

Por otro lado, hay cuatro tipos de bloques PL/SQL pero esto será objeto de otro artículo. Como adelanto del mismo diré que los cuatro tipos son:

   1. Bloques anónimos.
   2. Procedimientos.
   3. Funciones.
   4. Triggers.

 

 

EJERCICIOS RESUELTOS

 

Ejecutar el comando en el lab04_1.sql archivo para crear la tabla de mensajes. Escriba un PL / SQL
bloque para insertar números en la tabla de mensajes.
a. Introduzca los números 1 a 10, excepto 6 y 8.
b. Comprometerse antes de la final de la cuadra.
c. Seleccione de la tabla de mensajes para comprobar que el bloque PL / SQL trabajado
*/

DECLARE
v_country_id locations.country_id%TYPE := ’CA’;
v_location_id locations.location_id%TYPE;
v_city locations.city%TYPE := ’Montreal’;
BEGIN
SELECT MAX(location_id) INTO v_location_id
FROM locations
WHERE country_id = v_country_id;
FOR i IN 1..3 LOOP
INSERT INTO locations(location_id, city, country_id)
VALUES((v_location_id + i), v_city, v_country_id );
END LOOP;
--2
/*
Crear un bloque que calcula el importe de la comisión de un empleado determinado, basándose en el salario del empleado.
a. Utilice el comando DEFINE para proporcionar la identificación de empleado. 
DEFINE p_empno = 100
b. Si el sueldo del empleado es menor de $ 5,000, mostrar la cantidad de la bonificación para el empleado el 10% del sueldo.
c. Si el sueldo del empleado es de entre $ 5,000 y $ 10,000, aparecen con la cantidad de bonificación para el empleado el 15% del sueldo.
d. Si el salario del trabajador supera los $ 10.000, mostrar la cantidad de la bonificación para el empleado como 20% del sueldo.
e. Si el sueldo del empleado es NULL, la pantalla del importe de la bonificación para el empleado como 0.
f. Pruebe el bloque PL / SQL para cada caso con los casos de prueba siguiente, y comprobar cada bono de cantidad.
*/
set serveroutput on
define p_empno =100;
declare
sal number;
cursor salarios is
select salary
from employees;
salario employees.salary%type:='&p_empno';
begin
open salarios;
fetch salarios into salario;
if 
salario < 5000 then
sal:=(salario * 0.1);
elsif salario < 5000 and salario >=1000 then
sal:= salario * 0.15;
elsif salario > 1000 then
sal:= salario * 0.20;
elsif salario is null then
sal:= 0;
end if;
dbms_output.put_line (salario|| '      ' ||sal);
close salarios;
end;



--3
/*
Crear una tabla EMP que es una réplica de la tabla EMPLEADOS. Agregar una nueva columna, ESTRELLAS, de VARCHAR2 tipo de datos y la 
duración del 50 al EMP tabla para almacenar asterisco (*).
*/
create table EMP as
 select * from emp;
commit;
alter table emp 
add  (ESTRELLAS varchar2(50));
desc emp;

/*
--4
Crear un bloque que recompensa a un empleado si se añade un asterisco en los STARS
columna por cada $ 1000 de salario del empleado. 
a. Utilice el comando DEFINE para proporcionar la identificación de empleado. Pasar el valor al bloque PL / SQL
a través de un iSQL * Plus variable de sustitución.
b. Inicializar una variable v_asterisk que contiene un valor NULL.
c. Anexar un asterisco a la cadena por cada $ 1000 de la cantidad de sueldos. Por ejemplo, si el empleado
tiene una cantidad de sueldos de $ 8000, la cadena de asteriscos que contienen ocho asteriscos. Si el empleado
tiene una cantidad salario de 12.500 dólares, la cadena de asteriscos que contienen 13 asteriscos.
d. Actualización de la columna ESTRELLAS para el empleado con la cadena de asteriscos.
e. Commit.
f. Prueba del bloque para los siguientes valores:
DEFINE p_empno = 104
DEFINE p_empno = 174
DEFINE p_empno = 176
*/

set serveroutput on

declare
emp employees.employee_id%TYPE;
sal employees.salary%TYPE;
est varchar2 (1000);
begin 
  select employee_id, salary , to_char (rpad(' ', trunc (salary/1000) + 1, '*'))
  into emp, sal, est
  from employees
  where employee_id = 204;
  dbms_output.put_line (emp|| ' '|| sal|| ' '|| est);
  
end;

Bloques Anonimos

Dentro de la seccion DECLARE de un bloque anónimo podemos declarar funciones y procedimientos almacenados y ejecutarlos desde el bloque de ejecución del script.

Este tipo de subprogramas son menos conocidos que los procedimientos almacenados, funciones y triggers, pero son enormemente útiles.

El siguiente ejemplo declara y ejecuta utiliza una funcion (fn_multiplica_x2).

 

DECLARE       idx NUMBER;        FUNCTION fn_multiplica_x2(num NUMBER)        RETURN NUMBER        IS          result NUMBER;        BEGIN          result := num *2;          return result;        END fn_multiplica_x2; BEGIN      FOR idx IN 1..10      LOOP        dbms_output.put_line ('Llamada a la funcion ... '||TO_CHAR(fn_multiplica_x2(idx)));      END LOOP;      END;

 

 Notese que se utiliza la funcion TO_CHAR para convertir el resultado de la función fn_multiplica_x2 (numérico) en alfanumérico y poder mostrar el resultado por pantalla.

Los bloques PL/SQL son unidades de código que se envían al servidor para que sean procesadas conjuntamente, obviamente esto es mejor para el rendimiento del sistema que si se envían las sentencias SQL separadamente una a una. Es decir, cuando se requiere la ejecución de un bloque PL/SQL, este es enviado al servidor PL/SQL donde se ejecuta el código PL/SQL y las sentencias SQL son enviadas al servidor Oracle, como el servidor PL/SQL reside en el servidor Oracle, el código se ejecuta eficientemente.

Un bloque de código PL/SQL contiene las siguientes secciones:

 

   1. La sección de declaraciones donde se definen los distintos objetos que se van a utilizar en el bloque (variables, constantes, cursores, etcétera). Esta sección es opcional.
   2. La sección de instrucciones. Sección obligatoria que contiene las sentencias ejecutables de SQL y PL/SQL.
   3. La sección de excepciones. Es una sección opcional donde se tratan todos los errores que puedan surgir a la hora de ejecutar las sentencias SQL y PL/SQL de la sección de instrucciones.

Ejemplo de bloque:

 
  DECLARE IS / AS
     [declaraciones]
  BEGIN
     [instrucciones]
  EXCEPTION
     [tratamiento de excepciones]
  END;

Por otro lado, hay cuatro tipos de bloques PL/SQL pero esto será objeto de otro artículo. Como adelanto del mismo diré que los cuatro tipos son:

   1. Bloques anónimos.
   2. Procedimientos.
   3. Funciones.
   4. Triggers.

 

 

EJERCICIOS RESUELTOS

 

Ejecutar el comando en el lab04_1.sql archivo para crear la tabla de mensajes. Escriba un PL / SQL
bloque para insertar números en la tabla de mensajes.
a. Introduzca los números 1 a 10, excepto 6 y 8.
b. Comprometerse antes de la final de la cuadra.
c. Seleccione de la tabla de mensajes para comprobar que el bloque PL / SQL trabajado
*/

DECLARE
v_country_id locations.country_id%TYPE := ’CA’;
v_location_id locations.location_id%TYPE;
v_city locations.city%TYPE := ’Montreal’;
BEGIN
SELECT MAX(location_id) INTO v_location_id
FROM locations
WHERE country_id = v_country_id;
FOR i IN 1..3 LOOP
INSERT INTO locations(location_id, city, country_id)
VALUES((v_location_id + i), v_city, v_country_id );
END LOOP;
--2
/*
Crear un bloque que calcula el importe de la comisión de un empleado determinado, basándose en el salario del empleado.
a. Utilice el comando DEFINE para proporcionar la identificación de empleado. 
DEFINE p_empno = 100
b. Si el sueldo del empleado es menor de $ 5,000, mostrar la cantidad de la bonificación para el empleado el 10% del sueldo.
c. Si el sueldo del empleado es de entre $ 5,000 y $ 10,000, aparecen con la cantidad de bonificación para el empleado el 15% del sueldo.
d. Si el salario del trabajador supera los $ 10.000, mostrar la cantidad de la bonificación para el empleado como 20% del sueldo.
e. Si el sueldo del empleado es NULL, la pantalla del importe de la bonificación para el empleado como 0.
f. Pruebe el bloque PL / SQL para cada caso con los casos de prueba siguiente, y comprobar cada bono de cantidad.
*/
set serveroutput on
define p_empno =100;
declare
sal number;
cursor salarios is
select salary
from employees;
salario employees.salary%type:='&p_empno';
begin
open salarios;
fetch salarios into salario;
if 
salario < 5000 then
sal:=(salario * 0.1);
elsif salario < 5000 and salario >=1000 then
sal:= salario * 0.15;
elsif salario > 1000 then
sal:= salario * 0.20;
elsif salario is null then
sal:= 0;
end if;
dbms_output.put_line (salario|| '      ' ||sal);
close salarios;
end;



--3
/*
Crear una tabla EMP que es una réplica de la tabla EMPLEADOS. Agregar una nueva columna, ESTRELLAS, de VARCHAR2 tipo de datos y la 
duración del 50 al EMP tabla para almacenar asterisco (*).
*/
create table EMP as
 select * from emp;
commit;
alter table emp 
add  (ESTRELLAS varchar2(50));
desc emp;

/*
--4
Crear un bloque que recompensa a un empleado si se añade un asterisco en los STARS
columna por cada $ 1000 de salario del empleado. 
a. Utilice el comando DEFINE para proporcionar la identificación de empleado. Pasar el valor al bloque PL / SQL
a través de un iSQL * Plus variable de sustitución.
b. Inicializar una variable v_asterisk que contiene un valor NULL.
c. Anexar un asterisco a la cadena por cada $ 1000 de la cantidad de sueldos. Por ejemplo, si el empleado
tiene una cantidad de sueldos de $ 8000, la cadena de asteriscos que contienen ocho asteriscos. Si el empleado
tiene una cantidad salario de 12.500 dólares, la cadena de asteriscos que contienen 13 asteriscos.
d. Actualización de la columna ESTRELLAS para el empleado con la cadena de asteriscos.
e. Commit.
f. Prueba del bloque para los siguientes valores:
DEFINE p_empno = 104
DEFINE p_empno = 174
DEFINE p_empno = 176
*/

set serveroutput on

declare
emp employees.employee_id%TYPE;
sal employees.salary%TYPE;
est varchar2 (1000);
begin 
  select employee_id, salary , to_char (rpad(' ', trunc (salary/1000) + 1, '*'))
  into emp, sal, est
  from employees
  where employee_id = 204;
  dbms_output.put_line (emp|| ' '|| sal|| ' '|| est);
  
end;