Repository navigation
Oracle 2 resumen.sql
Pierre Germain edited this page Jan 29, 2017
·
1 revision
RESUMEN PLSQL:
Operadores aritméticos
+ Suma
* Multiplicación
** Exponenciación
- Sustracción
/ División
Operadores relacionales
<> Distinto de
^= Distinto de
> Mayor que
¡= Distinto de
< Menor que
= Igual
Símbolos especiales
( ) Separadores de lista and NAME in (‘Jose’, ‘Juan’, ‘Pedro’)
; Fin de orden procedure_name(arg1, arg2)
‘ Cadena de caracteres if var1= ‘Jose’ ...
:= Asignación reg := reg+1
|| Concatenación nombre := ‘Juan’ || ‘Perez’
-- Esto es un comentario
/* esto es un comentario */
*** Tipos de Datos y Conversiones
** varchar2
nombre_variable varchar2(longitud);
campo varchar2(10) := "VALINICIAL";
** number
nombre_variable number(precision, escala);
campo number(12, 2);
** date
campo date;
** boolean
campo boolean;
** Uso de %TYPE
DECLARE
credito REAL(7,2);
debito credito%TYPE;
debito cuenta.debe%TYPE;
** Uso de %ROWTYPE
DECLARE
emp_rec emp%ROWTYPE;
CURSOR c1 IS SELECT deptno, dname, loc FROM dept;
dept_rec c1%ROWTYPE;
*** Estructura de bloque
DECLARE
...
BEGIN
...
EXCEPTION
END;
** Ejemplo:
DECLARE
qty_on_hand NUMBER(5);
BEGIN
SELECT quantity INTO qty_on_hand FROM inventory
WHERE product = 'TENNIS RACKET'
FOR UPDATE OF quantity;
IF qty_on_hand > 0 THEN -- check quantity
UPDATE inventory SET quantity = quantity - 1
WHERE product = 'TENNIS RACKET';
INSERT INTO purchase_record
VALUES ('Tennis racket purchased', SYSDATE);
ELSE
INSERT INTO purchase_record
VALUES ('Out of tennis rackets', SYSDATE);
END IF;
COMMIT;
END;
/
*** Estructuras del lenguaje
** IF - THEN
IF condición THEN
secuencia_de_sentencias
END IF;
IF condición THEN
calcular_bonus (emp_id)
UPDATE sueldos SET pago = pago + bonus WHERE emp_no = emp_id;
END IF;
** IF - THEN - ELSE
IF condición THEN
secuencia_de_sentencias_1
ELSE
secuencia_de_sentencias_2
END IF;
IF tipo_trans = ‘CR’ THEN
UPDATE cuentas SET balance = balance + credito WHERE (...)
ELSE
UPDATE cuentas SET balance = balance - debito WHERE (...)
END IF;
IF tipo_trans = ‘CR’ THEN
UPDATE cuentas SET balance = balance + credito WHERE (...)
ELSE
IF nuevo_balance >= minimo_balance THEN
UPDATE cuentas SET balance = balance - debito WHERE (...)
ELSE
RAISE fondos_insuficientes;
END IF;
END IF;
** IF - THEN - ELSIF
IF condición_1 THEN
secuencia_de_sentencias_1
ELSIF condición_2 THEN
secuencia_de_sentencias_2
ELSE
secuencia_de_sentencias_3
END IF;
BEGIN
(...)
IF sueldo > 50000 THEN
bonus : = 1500;
ELSIF sueldo > 35000 THEN
bonus : = 500;
ELSE
bonus : = 100;
END IF;
INSERT INTO sueldos VALUES (emp_id, bonus, ... );
END;
** LOOP CON EXIT
LOOP
IF ranking_credito < 3 THEN
(...)
EXIT; --Termina el loop inmediatamente
END IF;
END LOOP;
LOOP
FECTH c1 INTO (...)
EXIT WHEN c1%NOTFOUND; -- termina el loop si la condición es verdadera
(...)
END LOOP;
CLOSE c1;
** WHILE LOOP
WHILE condición LOOP
secuencia_de_sentencias
END LOOP;
** FOR LOOP
FOR contador IN [REVERSE] valor_minimo..valor_maximo LOOP
secuencia_de_sentencias
END LOOP;
- Un contador de loop tiene validez sólo dentro del ciclo
FOR j IN 1..10 LOOP
FETCH c1 INTO emprec;
EXIT WHEN c1%NOTFOUND;
(...)
END LOOP;
*** Cursores
Implicitos y explicitos (para consultas que devuelvan más de una fila).
Comandos: OPEN, FETCH, CLOSE
** Declaración
DECLARE
CURSOR nombre_cursor [ (parámetro1 [, parámetro2](...)) ]
[RETURN tipo_de_retorno] IS sentencia_select ;
nombre_del_parametro [IN] tipo_de_dato [ { := | DEFAULT} expresión ]
* Ejemplo:
DECLARE
CURSOR c1 IS SELECT empno, ename, job, sal
FROM emp WHERE sal>1000 ;
CURSOR c2 RETURN dept%ROWTYPE IS SELECT *
FROM dept WHERE deptno = 10 ;
(...)
** Apertura
DECLARE
CURSOR c1 IS SELECT ename, job FROM emp WHERE sal > 3000;
(...)
BEGIN
OPEN c1;
(...)
END;
** Paso de Parámetros:
DECLARE
emp_name emp.name%TYPE;
salary emp.sal%TYPE;
CURSOR c1 (name VARCHAR2, salary NUMBER) IS SELECT ...
-- Para abrir el cursor:
OPEN c1(emp_name, 3000);
OPEN c1("John", 1500);
OPEN c1(emp_name, salary);
** Recuperación de Filas
- Ejemplo:
FETCH c1 INTO my_empno, my_ename, my_deptno;
- Se suele usar en un LOOP:
LOOP
FETCH c1 INTO mi_registro;
EXIT WHEN c1%NOTFOUND;
--- se procesa el registro
END LOOP;
** Uso de %FOUND
LOOP
FETCH c1 INTO (...)
IF c1%FOUND THEN -- fetch exitoso
(...)
ELSE -- fetch falló; se sale del loop
EXIT;
END IF;
END LOOP;
** Uso de %NOTFOUND
LOOP
FETCH c1 INTO (...)
EXIT WHEN c1%NOTFOUND;
(...)
END LOOP;
** Uso de %ISOPEN
** Uso de %ROWCOUNT
** Cierre de un Cursor
CLOSE c1;
*** Excepciones
ACCESS_INTO_NULL
COLLECTION_IS_NULL
CURSOR_ALREADY_OPEN
DUP_VAL_ON_INDEX
INVALID_CURSOR
INVALID_NUMBER
LOGIN_DENIED
NO_DATA_FOUND
NOT_LOGGED_ON
PROGRAM_ERROR
ROWTYPE_MISMATCH
SELF_IS_NULL
STORAGE_ERROR
SUBSCRIPT_BEYOND_COUNT
SUBSCRIPT_OUTSIDE_LIMIT
SYS_INVALID_ROWID
TIMEOUT_ON_RESOURCE
TOO_MANY_ROWS
VALUE_ERROR
ZERO_DIVIDE
** Excepciones definidas por el usuario
* Declaración
DECLARE
error_01 EXCEPTION;
* La sentencia RAISE:
DECLARE
out_of_stock EXCEPTION; -- declaración de la excepción
total NUMBER(4);
BEGIN
(...)
IF total < 1 THEN
RAISE out_of_stock; -- llamada a la excepción
END IF;
EXCEPTION
WHEN out_of_stock THEN
-- manejar el error aquí
WHEN OTHERS THEN
(...)
END;
*** Subprogramas
* Ejemplo:
PROCEDURE debit_account (acct_id INTEGER, amount REAL) IS
old_balance REAL;
new_balance REAL;
overdrown EXCEPTION;
BEGIN
SELECT bal INTO old_balance FROM accts WHERE acct_no = acct_id;
new_balance := old_balance – amount;
IF new_balance < 0 THEN
RAISE overdrown;
ELSE
UPDATE accts SET bal = new_balance WHERE acct_no = acct_id;
END IF;
EXCEPTION
WHEN overdrown THEN
(...)
END debit_account;
** Procediemientos:
PROCEDURE nombre [ (parámetro [, parámetro, (...)] ) ] IS
[declaraciones_locales]
BEGIN
sentencias_ejecutables
[EXCEPTION
condiciones_de_excepción]
END [nombre] ;
nombre_parámetro [IN | OUT [NOCOPY] | IN OUT [NOCOPY] tipo_de_dato
[ { := | DEFAULT } expresión ]
* Uso Inválido:
PROCEDURE xxx (param01 CHAR(5)) IS (...)
* Uso válido: E s necesario restringir el largo de una cadena como la del ejemplo,
se puede corregir la situación codificando la llamada al procedimiento xxx de la
siguiente manera:
DECLARE
temp CHAR(5);
SUBTYPE Char5 IS temp%TYPE;
PROCEDURE xxx (param01 Char5) IS (...)
** Funciones:
FUNCTION nombre [ (parámetro [, parámetro, (...)] ) ]
RETURN tipo_de_dato IS
BEGIN
sentencias_ejecutables
[EXCEPTION
condiciones_de_excepción]
END [nombre] ;
* Ejemplo:
FUNCTION revisa_salario (salario REAL, cargo CHAR(10))
RETURN BOOLEAN IS
salario_minimo REAL;
salario_maximo REAL;
BEGIN
SELECT lowsal, highsal INTO salario_minimo, salario_maximo
FROM salarios WHERE job = cargo ;
RETURN (salario >= salario_minimo) AND
(salario <= salario_maximo)
END revisa_salario ;
nombre_parámetro [IN | OUT [NOCOPY] | IN OUT [NOCOPY] tipo_de_dato
[ { := | DEFAULT } expresión ]
* Invocación:
DECLARE
renta_actual REAL;
codcargo CHAR(10);
BEGIN
(...)
IF revisa_salario (renta_actual, codcargo) THEN (...)
** La sentencia RETURN
Esta sentencia termina inmediatamente la ejecución de un programa, retornando el control al
bloque de programa que lo llamó. No se debe confundir con la cláusula return de las funciones,
que especifica el tipo de dato devuelto por ella. En el caso de las funciones, esta sentencia debe contener un valor, que es aquel que
se va a devolver al programa que la llamó.
*** Creación de paquetes
Un paquete es un esquema u objeto que agrupa tipos de PL/SQL relacionados, ítems y
subprogramas. Los paquetes se constituyen de dos partes: la especificación y el cuerpo.
La especificación (1) es la interfaz con las aplicaciones. En ella es posible declarar los tipos,
variables, constantes, excepciones, cursores y subprogramas disponibles para su uso
posterior.
El cuerpo (2)define completamente a cursores y subprogramas e implementa lo que se declaró
inicialmente en la especificación.
-------------- Especificación (1) -----------------------
CREATE OR REPLACE PACKAGE emp_actions AS
TYPE EmpRecTyp IS RECORD (emp_id INTEGER, salary REAL);
CURSOR desc_salary RETURN EmpRecTyp;
PROCEDURE hire_employee (
ename VARCHAR2,
job VARCHAR2,
mgr NUMBER,
sal NUMBER,
comm NUMBER,
deptno NUMBER);
PROCEDURE fire_employee(emp_id NUMBER);
END emp_actions;
-------------- Cuerpo (2) -----------------------
CREATE OR REPLACE PACKAGE BODY emp_actions AS -- Cuerpo
CURSOR desc_salary RETURN EmpRecTyp IS
SELECT empno, sal FROM emp ORDER BY sal DESC;
PROCEDURE hire_employee (
ename VARCHAR2,
job VARCHAR2,
mgr NUMBER,
sal NUMBER,
comm NUMBER,
deptno NUMBER) IS
BEGIN
INSERT INTO emp VALUES (empno_seq.NEXTVAL, ename, job, mgr,
SYSDATE, sal, comm, deptno);
END hire_employee;
PROCEDURE fire_employee (emp_id NUMBER) IS
BEGIN
DELETE FROM emp WHERE empno = emp_id;
END fire_employee;
END emp_actions;
/******************* Ejemplos para probar con SQL*PLUS de PL/SQL *************/
-- Las tablas con las que vamos a trabajar son:
drop table pwb_accounts
/
create table pwb_accounts(
account_id number(4) not null,
bal number(11,2))
/
create unique index pwb_accounts_index on pwb_accounts (account_id)
/
drop table pwb_action
/
create table pwb_action(
account_id number(4) not null,
oper_type char(1) not null,
new_value number(11,2),
status char(45),
time_tag date not null)
/
drop table pwb_bins
/
create table pwb_bins(
bin_num number(2) not null,
part_num number(4),
amt_in_bin number(4))
/
drop table pwb_data_table
/
create table pwb_data_table(
exper_num number(2),
n1 number(5),
n2 number(5),
n3 number(5))
/
drop table pwb_emp
/
create table pwb_emp(
empno number(4) not null,
ename varchar2(10),
job varchar2(9),
mgr number(4),
hiredate date,
sal number(7,2),
comm number(7,2),
deptno number(2))
/
drop table pwb_inventory
/
create table pwb_inventory(
prod_id number(5) not null,
product char(15),
quantity number(5))
/
drop table pwb_journal
/
create table pwb_journal(
account_id number(4) not null,
action char(45) not null,
amount number(11,2),
date_tag date not null)
/
drop table pwb_num1_tab
/
create table pwb_num1_tab(
sequence number(3) not null,
num number(4))
/
drop table pwb_num2_tab
/
create table pwb_num2_tab(
sequence number(3) not null,
num number(4))
/
drop table pwb_purchase_record
/
create table pwb_purchase_record(
mesg char(45),
purch_date date)
/
drop table pwb_ratio
/
create table pwb_ratio(
sample_id number(3) not null,
ratio number)
/
drop table pwb_result_table
/
create table pwb_result_table(
sample_id number(3) not null,
x number,
y number)
/
drop table pwb_sum_tab
/
create table pwb_sum_tab(
sequence number(3) not null,
sum number(5))
/
drop table pwb_temp
/
create table pwb_temp(
num_col1 number(9,4),
num_col2 number(9,4),
char_col char(55))
/
-- Los datos que se introducen son:
delete from pwb_accounts
/
insert into pwb_accounts values (1,1000.00)
/
insert into pwb_accounts values (2,2000.00)
/
insert into pwb_accounts values (3,1500.00)
/
insert into pwb_accounts values (4,6500.00)
/
insert into pwb_accounts values (5,500.00)
/
delete from pwb_action
/
insert into pwb_action values
(3,'u',599,null,sysdate)
/
insert into pwb_action values
(6,'i',20099,null, sysdate)
/
insert into pwb_action values
(5,'d',null,null, sysdate)
/
insert into pwb_action values
(7,'u',1599,null, sysdate)
/
insert into pwb_action values
(1,'i',399,null,sysdate)
/
insert into pwb_action values
(9,'d',null,null,sysdate)
/
insert into pwb_action values
(10,'x',null,null,sysdate)
/
delete from pwb_bins
/
insert into pwb_bins values (1, 5469, 650)
/
insert into pwb_bins values (2, 7243, 450)
/
insert into pwb_bins values (3, 5469, 120)
/
insert into pwb_bins values (4, 5469, 300)
/
insert into pwb_bins values (5, 6085, 415)
/
insert into pwb_bins values (6, 5469, 280)
/
insert into pwb_bins values (7, 8159, 619)
/
delete from pwb_data_table
/
insert into pwb_data_table values
(1, 10, 167, 17)
/
insert into pwb_data_table values
(1, 16, 223, 35)
/
insert into pwb_data_table values
(2, 34, 547, 2)
/
insert into pwb_data_table values
(3, 23, 318, 11)
/
insert into pwb_data_table values
(1, 17, 266, 15)
/
insert into pwb_data_table values
(1, 20, 117, 9)
/
delete from pwb_emp
/
insert into pwb_emp values
(7369,'SMITH','CLERK',7902,TO_DATE('12-17-00','MM-DD-YY'),
800,NULL,20)
/
insert into pwb_emp values
(7499,'ALLEN','SALESMAN',7698,TO_DATE('02-20-01','MM-DD-YY'),
1600,300,30)
/
insert into pwb_emp values
(7521,'WARD','SALESMAN',7698,TO_DATE('02-22-01','MM-DD-YY'),
1250,500,30)
/
insert into pwb_emp values
(7566,'JONES','MANAGER',7839,TO_DATE('04-02-01','MM-DD-YY'),
2975,NULL,20)
/
insert into pwb_pwb_emp values
(7654,'MARTIN','SALESMAN',7698,TO_DATE('09-28-01','MM-DD-YY'),
1250,1400,30)
/
insert into pwb_emp values
(7698,'BLAKE','MANAGER',7839,TO_DATE('05-1-01','MM-DD-YY'),
2850,NULL,30)
/
insert into pwb_emp values
(7782,'CLARK','MANAGER',7839,TO_DATE('06-9-01','MM-DD-YY'),
2450,NULL,10)
/
insert into pwb_emp values
(7788,'SCOTT','ANALYST',7566,SYSDATE-5,3000,NULL,20)
/
insert into pwb_emp values
(7839,'KING','PRESIDENT',NULL,TO_DATE('11-17-01','MM-DD-YY'),
5000,NULL,10)
/
insert into pwb_emp values
(7844,'TURNER','SALESMAN',7698,TO_DATE('09-8-01','MM-DD-YY'),
1500,0,30)
/
insert into pwb_emp values
(7876,'ADAMS','CLERK',7788,SYSDATE-1,1100,NULL,20)
/
insert into pwb_emp values
(7900,'JAMES','CLERK',7698,TO_DATE('12-3-01','MM-DD-YY'),
950,NULL,30)
/
insert into pwb_emp values
(7902,'FORD','ANALYST',7566,TO_DATE('12-3-01','MM-DD-YY'),
3000,NULL,20)
/
insert into pwb_emp values
(7934,'MILLER','CLERK',7782,TO_DATE('01-23-02','MM-DD-YY'),
1300,NULL,10)
/
delete from pwb_inventory
/
insert into pwb_inventory values
(1234, 'TENNIS RACKET', 3)
/
insert into pwb_inventory values
(8159, 'GOLF CLUB', 4)
/
insert into pwb_inventory values
(2741, 'SOCCER BALL', 2)
/
delete from pwb_journal
/
delete from pwb_num1_tab
/
insert into pwb_num1_tab values (1, 5)
/
insert into pwb_num1_tab values (2, 7)
/
insert into pwb_num1_tab values (3, 4)
/
insert into pwb_num1_tab values (4, 9)
/
delete from pwb_num2_tab
/
insert into pwb_num2_tab values (1, 15)
/
insert into pwb_num2_tab values (2, 19)
/
insert into pwb_num2_tab values (3, 27)
/
delete from pwb_purchase_record
/
delete from pwb_ratio
/
delete from pwb_result_table
/
insert into pwb_result_table values (130, 70, 87)
/
insert into pwb_result_table values (131, 77, 194)
/
insert into pwb_result_table values (132, 73, 0)
/
insert into pwb_result_table values (133, 81, 98)
/
delete from pwb_sum_tab
/
delete from pwb_temp
/
commit
/
** A continuación, un conjunto de ejemplos de uso de PL/SQL para probar:
1. En la tabla emp incrementar el salario el 10% a los empleados que tengan una
comisión superior al 5% del salario
/*1. En la tabla emp incrementar el salario el 10% a los empleados que tengan una
comisión superior al 5% del salario.*/
/* Tabla implicada
PWB_EMP (Empno,Ename, Job, Mgr, Hiredate, sal, comm, deptno)
*/
/* Pseudocódigo:
SI COMM > (5% de SAL)
--> incrementar SAL un 10%
END_SI
*/
DECLARE
BEGIN
/* Mi manera de hacer el update
UPDATE pwb_emp
--SET SAL = (1.1 * SAL) WHERE COMM > (0.05*SAL);
*/
/* Update del Libro */
UPDATE pwb_EMP
SET SAL = SAL+SAL*(10/100)
WHERE COMM > (SAL*5/100);
/**/
/* Para volver a ponerlo cómo estaba
UPDATE pwb_emp
SET SAL = 1600 WHERE EMPNO = 7499; -- 1760
UPDATE pwb_emp
SET SAL = 1250 WHERE EMPNO = 7521; -- 1375
*/
END;
2.- Añadir la columna total2 y en ella escribir la suma del salario y la comisión de los
empleados con comisión distinta de 0.
/*2.- Añadir la columna total2 y en ella escribir la suma del salario y la comisión de los
empleados con comisión distinta de 0.*/
/* PISTA: uso EXECUTE INMEDIATE para poder alterar una tabla
desde dentro del programa PL/SQL */
/* Tabla implicada
PWB_EMP (Empno,Ename, Job, Mgr, Hiredate, sal, comm, deptno)
*/
/* Pseudocódigo:
1.) Añadir la columna total2
2.) Insertar en dicha columna (salario + comision) cuando comision <> 0
*/
---------------------------------------------------------------
/* Mi manera 1 de hacerlo : sólo actualizando la columna creada */
DECLARE
BEGIN
-- Quitamos y volvemos a crear la columna total2
EXECUTE IMMEDIATE('ALTER TABLE pwb_emp DROP (total2)');
EXECUTE IMMEDIATE('ALTER TABLE pwb_emp ADD (total2 NUMBER(7,2))');
UPDATE pwb_emp SET total2 = (sal + comm) WHERE (comm > 0);
END;
/**/
-----------------------------------------------------------------
/* Mi manera 2 de hacerlo : usando un cursor*/
DECLARE
CURSOR c1 IS SELECT sal, comm, total2
FROM pwb_emp WHERE comm <>0
for update of total2 ;
rec_c1 c1%ROWTYPE;
BEGIN
-- Quitamos y volvemos a crear la columna total2
EXECUTE IMMEDIATE('ALTER TABLE pwb_emp DROP (total2)');
EXECUTE IMMEDIATE('ALTER TABLE pwb_emp ADD (total2 NUMBER(7,2))');
OPEN c1;
LOOP
FETCH c1 INTO rec_c1;
IF c1%NOTFOUND
THEN EXIT;
ELSE
UPDATE pwb_emp SET total2 = (rec_c1.sal + rec_c1.comm)
where current of c1;
END IF;
END LOOP;
CLOSE c1;
END;
/**/
-----------------------------------------------------------------
/* Alternativa 1 libro : Con un cursor */
DECLARE
CURSOR CURSOR2 IS SELECT COMM,SAL FROM pwb_EMP
WHERE COMM<>0
FOR UPDATE;
BEGIN
-- Quitamos y volvemos a crear la columna total2
EXECUTE IMMEDIATE('ALTER TABLE pwb_emp DROP (total2)');
EXECUTE IMMEDIATE('ALTER TABLE pwb_emp ADD (total2 NUMBER(7,2))');
FOR REG IN CURSOR2 LOOP
UPDATE pwb_EMP
SET TOTAL2 = SAL+COMM
WHERE CURRENT OF CURSOR2;
END LOOP;
END;
/**/
------------------------------------------------------------------
/* Alternativa 2 libro : Con un cursor */
-- OJO: Para que funcione he añadido COMM <> 0 despues del FETCH
DECLARE
comm pwb_emp.comm%type;
sal pwb_emp.sal%type;
CURSOR CURSOR2 IS SELECT COMM,SAL FROM pwb_EMP
WHERE COMM<>0 AND COMM IS NOT NULL;
BEGIN
-- Quitamos y volvemos a crear la columna total2
EXECUTE IMMEDIATE('ALTER TABLE pwb_emp DROP (total2)');
EXECUTE IMMEDIATE('ALTER TABLE pwb_emp ADD (total2 NUMBER(7,2))');
open cursor2;
loop
fetch cursor2 into comm, sal;
exit when cursor2%NOTFOUND;
UPDATE pwb_EMP
SET TOTAL2 = SAL+COMM
WHERE comm = pwb_emp.comm AND sal = pwb_emp.sal AND comm <> 0;
end loop;
END;
/**/
3.- Insertar un empleado en la tabla EMP. Su número será superior a los existentes y la
fecha de incorporación a la empresa será la actual.
/* 3.- Insertar un empleado en la tabla EMP. Su número será superior a los existentes y la
fecha de incorporación a la empresa será la actual. */
---------------------------------------------------------------------------
/* Mi manera de hacerlo : usando un cursor : NO FUNCIONA, DA ERROR PLS-00302 */
DECLARE
CURSOR c1 IS SELECT MAX(empno)
FROM pwb_emp;
rec_c1 c1%ROWTYPE;
my_empno pwb_emp.empno%TYPE;
BEGIN
OPEN c1;
FETCH c1 INTO rec_c1;
IF c1%NOTFOUND THEN
INSERT INTO pwb_emp
VALUES(1,'Pierre','Bertramovisch','1111',SYSDATE,9999,NULL,20,NULL);
ELSE
my_empno := rec_c1;
INSERT INTO pwb_emp
VALUES(0000,'Pierre','Willfrado','2222',SYSDATE,7777,NULL,30,NULL);
END IF;
CLOSE c1;
END;
/**/
---------------------------------------------------------------------------
/* Mi manera de hacerlo : usando una SELECT normal y corriente */
DECLARE
my_empno pwb_emp.empno%TYPE;
BEGIN
SELECT MAX(pwb_emp.EMPNO) INTO my_empno FROM pwb_emp;
INSERT INTO pwb_emp VALUES(my_empno+1,'Pierre','Willkov','2222',SYSDATE,7777,NULL,30,NULL);
END;
/**/
---------------------------------------------------------------------------
/* Manera de hacerlo el libro : */
DECLARE
NUM_EMPLEADO pwb_EMP.EMPNO%TYPE;
FECHA pwb_EMP.HIREDATE%TYPE;
BEGIN
SELECT MAX(EMPNO) INTO NUM_EMPLEADO FROM pwb_EMP;
SELECT SYSDATE INTO FECHA FROM DUAL;
NUM_EMPLEADO:=NUM_EMPLEADO +1;
INSERT INTO pwb_EMP
VALUES (NUM_EMPLEADO,'PEDRO','MATEMATIC',7839,
FECHA,3000,NULL,20,NULL);
END;
/**/
4.- Realizar un procedimiento para cambiar la fecha por el número de año.
/*4.- Realizar un procedimiento
para cambiar la fecha por el número de año. */
/* Version del libro: No funciona, se queda pensando */
DECLARE
FECHA VARCHAR2(4);
CURSOR CURSOR1 IS SELECT TO_CHAR(HIREDATE,'YYYY') FROM pwb_EMP FOR
UPDATE;
BEGIN
OPEN CURSOR1;
LOOP
FETCH CURSOR1 INTO FECHA;
EXIT WHEN CURSOR1%NOTFOUND;
UPDATE pwb_EMP
SET HIREDATE='FECHA' WHERE CURRENT OF CURSOR1;
END LOOP;
CLOSE CURSOR1;
END;
/**/
------------------------------------------------------------------------
/* Version modificada : Tampoco funciona, se queda pensando*/
DECLARE
FECHA VARCHAR2(4);
CURSOR CURSOR1 IS SELECT TO_CHAR(HIREDATE,'YYYY') FROM pwb_EMP FOR
UPDATE;
BEGIN
OPEN CURSOR1;
LOOP
FETCH CURSOR1 INTO FECHA;
IF CURSOR1%NOTFOUND THEN EXIT;
-- ELSE
-- UPDATE pwb_EMP SET HIREDATE= TO_DATE(FECHA) WHERE CURRENT OF CURSOR1;
END IF;
END LOOP;
CLOSE CURSOR1;
END;
5.- Añadir un nuevo empleado en la tabla emp. El número de empleado será el del último + 10. La fecha la actual, el departamento el 40.
/* 5.- Añadir un nuevo empleado en la tabla emp.
El número de empleado será el del último + 10.
La fecha la actual, el departamento el 40.*/
/* Mi Version */
DECLARE
NUM_EMPLEADO pwb_EMP.EMPNO%TYPE;
FECHA pwb_EMP.HIREDATE%TYPE;
BEGIN
SELECT MAX(empno) INTO num_empleado FROM pwb_emp;
num_empleado := num_empleado + 10;
INSERT INTO pwb_EMP
VALUES (num_empleado,'PIERRE','MATIC',7839,
SYSDATE,3000,NULL,40,NULL);
END;
---------------------------------------------------------
/* Version del Libro */
DECLARE
EMPLEADO pwb_EMP.EMPNO%TYPE;
FECHAA pwb_EMP.HIREDATE%TYPE;
PROCEDURE EJERCICIO_5(FECHA IN pwb_EMP.HIREDATE%TYPE,
NUMERO_EMP IN pwb_EMP.EMPNO%TYPE,
DEPARTAMENTO IN pwb_EMP.DEPTNO%TYPE) IS
BEGIN
INSERT INTO pwb_EMP VALUES
(NUMERO_EMP,'INDRALO','JOB',NULL,FECHA,999,NULL,DEPARTAMENTO,00);
END EJERCICIO_5;
FUNCTION TRANSFORMACION1 RETURN NUMBER IS
RETORNO pwb_EMP.EMPNO%TYPE;
MAYOR_NUMERO pwb_EMP.EMPNO%TYPE;
BEGIN
SELECT MAX(EMPNO) INTO MAYOR_NUMERO FROM pwb_EMP;
RETORNO:=MAYOR_NUMERO+10;
RETURN(RETORNO);
END TRANSFORMACION1;
FUNCTION TRANSFORMACION2 RETURN DATE IS
RETORNO pwb_EMP.HIREDATE%TYPE;
BEGIN
RETORNO:=SYSDATE;
RETURN(RETORNO);
END TRANSFORMACION2;
BEGIN
EMPLEADO := TRANSFORMACION1;
FECHAA := TRANSFORMACION2;
EJERCICIO_5(FECHAA,EMPLEADO,40);
END;
/**/
6.- Buscar todos los empleados que tienen un salario + comisión superior a 2000 y
asignarles como nuevo salario esta suma. Sólo para los que tienen comisión.
/*6.- Buscar todos los empleados
que tienen un salario + comisión superior a 2000 y
asignarles como nuevo salario esta suma.
Sólo para los que tienen comisión. */
-------------------------------------------------------------
/* A mi manera */
DECLARE
CURSOR c1 IS SELECT empno FROM pwb_emp
WHERE ((sal+comm)>2000)
AND (comm > 0)
-- AND (comm <> NULL) -- no funciona con eso
FOR UPDATE;
BEGIN
FOR REG IN c1 LOOP
DBMS_OUTPUT.put_line('dentro del bucle');
UPDATE pwb_EMP
SET pwb_emp.SAL = SAL+COMM
WHERE CURRENT OF c1;
END LOOP;
END;
---------------------------------------------------------------------
/* A la manera del libro: Usando cursor y procedimiento */
DECLARE
PROCEDURE ACTUALIZAR IS
CURSOR C1 IS SELECT ENAME,SAL,COMM FROM pwb_EMP
WHERE SAL+COMM>2000 FOR UPDATE;
REGISTRO C1%ROWTYPE;
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO REGISTRO;
EXIT WHEN C1%NOTFOUND;
UPDATE pwb_EMP
SET SAL=SAL+COMM WHERE CURRENT OF C1;
END LOOP;
CLOSE C1;
END ACTUALIZAR;
BEGIN
ACTUALIZAR;
END;
/**/
7.- Crear un procedimiento almacenado que haga una venta, decrementando en 1 la
cantidad de productos disponibles, pero si la cantidad es 0, se introduce un mensaje en
una tabla de registro que indica las ventas que se hacen y que registra si hay falta de
productos.
/*7.- Crear un procedimiento almacenado que haga una venta,
decrementando en 1 la cantidad de productos disponibles,
pero si la cantidad es 0, se introduce un mensaje en
una tabla de registro que indica las ventas que se hacen
y que registra si hay falta de
productos.*/
/* Mi Manera: No terminado */
DECLARE
PROCEDURE comprar (producto_id IN pwb_inventory.prod_id%TYPE) IS
CURSOR C1 IS SELECT quantity FROM pwb_inventory
WHERE prod_id = producto_id FOR UPDATE;
REGISTRO C1%ROWTYPE;
BEGIN
OPEN C1;
FETCH C1 INTO REGISTRO;
IF -- hay cantidad suficiente
UPDATE pwb_inventory
SET QUANTITY = (QUANTITY-1) WHERE CURRENT OF C1;
ELSE -- no hay cantidad suficiente
UPDATE pwb_EMP
SET SAL=SAL+COMM WHERE CURRENT OF C1;
END IF
CLOSE C1;
END comprar;
BEGIN
comprar(1234);
END;
/**/
/* Manera del libro */
CREATE OR REPLACE PROCEDURE VENDE(articulo char) IS
cantidad NUMBER(5);
BEGIN
dbms_output.put_line(articulo);
SELECT quantity INTO cantidad FROM inventory
WHERE product = articulo
FOR UPDATE OF quantity;
IF cantidad > 0 THEN
UPDATE inventory SET quantity = quantity - 1
WHERE product = 'TENNIS RACKET';
INSERT INTO purchase_record
VALUES (articulo || ' purchased', SYSDATE);
ELSE
INSERT INTO purchase_record
VALUES ('Out of ' || articulo, SYSDATE);
END IF;
COMMIT;
END;
/**/
Para ejecutar execute vende(‘TENNIS RACKET’);