Iniciar sesiónCerrar
Correo:
Contraseña:
Entrar
Recordar sesión en este navegador
Iniciar sesiónIniciar sesiónCrear cuentaCrear cuenta
LWP » Foros » SQL » Procedimiento PL/SQL

SQL - Procedimiento PL/SQL

Volver
Nuevo Tema
<<>>
 
Vista:
sin imagen de perfil

Procedimiento PL/SQL

Publicado por jose (7 intervenciones) el 15/04/2017 12:16:29
Hola, tengo que crear un procedimiento que reciba como parámetro el nombre de una localidad y muestre por pantalla un listado con el código, nombre y salario de los comerciales que trabajan en esa localidad.
Yo he hecho esto:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
create or replace PROCEDURE nom_loc(loc OFICINAS.LOCALIDAD%TYPE)
AS
CURSOR o_loc IS
SELECT CODIGO, DIRECCION, LOCALIDAD, PROVINCIA
FROM OFICINAS;
d_codigo oficinas.codigo%TYPE;
d_direccion oficinas.direccion%TYPE;
d_localidad oficinas.localidad%TYPE;
d_provincia oficinas.provincia%TYPE;
BEGIN
OPEN o_loc;
FETCH o_loc into d_codigo, d_direccion, d_localidad, d_provincia;
WHILE o_loc%FOUND LOOP
DBMS_OUTPUT.PUT_LINE(d_codigo||' ' ||d_direccion||' '||d_localidad||' '||d_provincia);
FETCH o_loc into d_codigo, d_direccion, d_localidad, d_provincia;
END LOOP;
CLOSE o_loc;
END nom_loc;

Y lo invoco de la siguiente manera:
1
2
3
4
5
6
7
8
9
10
DECLARE
  LOC VARCHAR2(50);
BEGIN
  LOC := 'Marbella';
 
  NOM_LOC(
    LOC => LOC
  );
--rollback;
END;

El problema es que me muestra tanto la oficina de jerez como la de marbella, estaria muy agradecido si alguien me pudiese ayudar.

El script es:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
create table Oficinas (
	codigo		varchar2(3) primary key,
	direccion	varchar2(50) not null,
	localidad	varchar2(50) not null,
	provincia	varchar2(20)
);
create table Vehiculos (
	matricula	varchar2(7) primary key,
	marca		varchar2(20) not null,
	modelo		varchar2(20),
	nKilometros	number(8) not null,
	fRevision	date
);
 
 
create table Comerciales (
	codigo		varchar2(3) primary key,
	nombre		varchar2(30) not null,
	salario		number(6,2) not null,
	hijos		number(2) not null,
	fNacimiento	date not null,
	codOficina	varchar2(3),
	vehiculoAsig varchar2(7),
    constraint com_ofi_fk foreign key (codOficina) references Oficinas(codigo) on delete cascade,
    constraint com_veh_fk foreign key (vehiculoAsig) references Vehiculos(matricula) on delete cascade
);
 
create table Productos (
	referencia		varchar2(6) primary key,
	nombre		varchar2(20) not null,
	descripcion	varchar2(100),
	precio		number(6,2) not null,
	descuento   number(2,0) not null
);
 
create table Vender (
	codComercial varchar2(3),
	refProducto	 varchar2(6),
	cantidad	 number(6),
	fecha		 date,
	primary key (codComercial, refProducto, fecha),
	constraint ven_co_fk foreign key (codComercial) references Comerciales(codigo) on delete cascade,
	constraint ven_ref_fk foreign key (refProducto) references Productos(referencia) on delete cascade
);
 
insert into Oficinas values ('101','Avda. Carlos III, 23' ,'Aguadulce','Almería');
insert into Oficinas values ('202','Avda Servero Ochoa, 33' ,'Marbella','Málaga');
insert into Oficinas values ('303','Avda. de Arcos, 43' ,'Jerez de la Frontera','Cádiz');
insert into Oficinas values ('404','Avda. de Lisboa, 53' ,'Andújar','Jaén');
 
insert into Vehiculos values ('0101BCD', 'Peugeot', '206', 110022, TO_DATE('2011/12/24', 'YYYY/MM/DD'));
insert into Vehiculos values ('0202CBD', 'Opel', 'Corsa', 80999, TO_DATE('2011/12/12', 'YYYY/MM/DD'));
insert into Vehiculos values ('0303DFD', 'Toyota', 'Auris', 101200, TO_DATE('2010/02/01', 'YYYY/MM/DD'));
insert into Vehiculos values ('0404FDF', 'Toyota', 'Corolla', 58230, TO_DATE('2011/11/20', 'YYYY/MM/DD'));
insert into Vehiculos values ('0505GHG', 'Renault', 'Laguna', 12234, TO_DATE('2012/12/04', 'YYYY/MM/DD'));
insert into Vehiculos values ('0606DFD', 'Renault', 'Clio', 148023, TO_DATE('2010/12/01', 'YYYY/MM/DD'));
insert into Vehiculos values ('0707BDF', 'Renault', 'Clio', 133012,TO_DATE('2010/10/15', 'YYYY/MM/DD'));
insert into Vehiculos values ('0808GGH', 'Seat', 'Ibiza', 45023, TO_DATE('2011/10/13', 'YYYY/MM/DD'));
insert into Vehiculos values ('0909DDL', 'Seat', 'Leon', 99283,TO_DATE('2010/03/20', 'YYYY/MM/DD'));
 
insert into Comerciales values ('111','Pedro Alonso Jiménez',1200.50,0, TO_DATE('1960/02/01', 'YYYY/MM/DD'),'101','0101BCD');
insert into Comerciales values ('222','Julia Pérez Arribas',1305.75,1,TO_DATE('1971/12/11', 'YYYY/MM/DD'),'101','0202CBD');
insert into Comerciales values ('333','Juan Lozano Gómez',1080.25,3,TO_DATE('1975/08/21', 'YYYY/MM/DD'),'202','0303DFD');
insert into Comerciales values ('444','Sandra Molina Sánchez',1120.00,2,TO_DATE('1969/05/09', 'YYYY/MM/DD'),'303','0404FDF');
insert into Comerciales values ('555','Salvador Beltrán Jiménez',975.50,0,TO_DATE('1980/10/11', 'YYYY/MM/DD'),'303','0505GHG');
insert into Comerciales values ('666','Beatriz Martín Gutiérrez',1175.00,1,TO_DATE('1970/11/06', 'YYYY/MM/DD'),'404','0606DFD');
insert into Comerciales values ('777','Eduardo Martínez Puig',1100.50,2,TO_DATE('1967/01/16', 'YYYY/MM/DD'),'101','0707BDF');
insert into Comerciales values ('888','Juan Antonio Ochando Serrano',1000.50,0,TO_DATE('1982/03/03', 'YYYY/MM/DD'),'202','0808GGH');
insert into Comerciales values ('999','Marina Pérez Blanco',1070.20,3,TO_DATE('1972/07/21', 'YYYY/MM/DD'),'303','0909DDL');
 
 
insert into Productos values ('AC0001', 'Abrigo Caballero', 'Piel Color Marrón',  120.50, 15);
insert into Productos values ('AS0001', 'Abrigo Señora', 'Piel Color Marrón',  110.75, 25);
insert into Productos values ('CC0001', 'Camisa Caballero', 'Cuadros',  35.99, 10);
insert into Productos values ('PC0001', 'Pantalón Caballero', 'Vaquero',  34.90, 35);
insert into Productos values ('PC0002', 'Pantalón Caballero', 'Pana',  25.90, 0);
insert into Productos values ('AC0002', 'Abrigo Caballero', 'Piel Color Negro',  120.50, 15);
insert into Productos values ('CC0002', 'Camisa Caballero', 'Lisa Color Blanco',  35.99, 10);
insert into Productos values ('CC0003', 'Camisa Caballero', 'Lisa Color Azul',  35.99, 10);
insert into Productos values ('AS0002', 'Abrigo Señora', 'Piel Color Negro',  120.75, 15);
insert into Productos values ('AS0003', 'Abrigo Señora', 'Ante  Color Marrón',  90.95, 35);
insert into Productos values ('PS0001', 'Pantalón Señora', 'Vaquero',  30.90, 30);
insert into Productos values ('PS0002', 'Pantalón Señora', 'Lino',  39.90, 40);
 
 
insert into Vender values ('111','PC0001',24,TO_DATE('2011/10/10', 'YYYY/MM/DD'));
insert into Vender values ('111','PC0002',48,TO_DATE('2011/03/22', 'YYYY/MM/DD'));
insert into Vender values ('111','CC0001',20,TO_DATE('2011/09/01', 'YYYY/MM/DD'));
insert into Vender values ('111','AS0001',12,TO_DATE('2011/10/03', 'YYYY/MM/DD'));
insert into Vender values ('222','AS0001',34,TO_DATE('2011/01/22', 'YYYY/MM/DD'));
insert into Vender values ('222','AC0001',60,TO_DATE('2011/09/06', 'YYYY/MM/DD'));
insert into Vender values ('222','AS0002',21,TO_DATE('2011/11/10', 'YYYY/MM/DD'));
insert into Vender values ('333','AC0001',15,TO_DATE('2011/02/18', 'YYYY/MM/DD'));
insert into Vender values ('333','AC0002',80,TO_DATE('2011/02/09', 'YYYY/MM/DD'));
insert into Vender values ('333','AC0002',12,TO_DATE('2011/02/23', 'YYYY/MM/DD'));
insert into Vender values ('333','CC0003',45,TO_DATE('2011/06/17', 'YYYY/MM/DD'));
insert into Vender values ('333','CC0003',10,TO_DATE('2011/06/09', 'YYYY/MM/DD'));
insert into Vender values ('444','CC0001',75,TO_DATE('2011/06/08', 'YYYY/MM/DD'));
insert into Vender values ('444','PC0001',60,TO_DATE('2011/01/19', 'YYYY/MM/DD'));
insert into Vender values ('555','PC0002',35,TO_DATE('2011/02/02', 'YYYY/MM/DD'));
insert into Vender values ('555','PS0001',23,TO_DATE('2011/02/28', 'YYYY/MM/DD'));
insert into Vender values ('555','CC0002',67,TO_DATE('2011/05/25', 'YYYY/MM/DD'));
insert into Vender values ('666','CC0002',21,TO_DATE('2011/05/11', 'YYYY/MM/DD'));
insert into Vender values ('666','AS0003',23,TO_DATE('2011/04/16', 'YYYY/MM/DD'));
insert into Vender values ('666','CC0001',33,TO_DATE('2011/04/18', 'YYYY/MM/DD'));
insert into Vender values ('666','CC0001',50,TO_DATE('2011/03/11', 'YYYY/MM/DD'));
insert into Vender values ('666','CC0002',78,TO_DATE('2011/03/02', 'YYYY/MM/DD'));
insert into Vender values ('777','AC0002',39,TO_DATE('2011/03/07', 'YYYY/MM/DD'));
insert into Vender values ('777','AS0003',18,TO_DATE('2011/05/11', 'YYYY/MM/DD'));
insert into Vender values ('888','AS0002',55,TO_DATE('2011/03/19', 'YYYY/MM/DD'));
insert into Vender values ('888','PC0001',78,TO_DATE('2011/06/20', 'YYYY/MM/DD'));
insert into Vender values ('888','PS0002',33,TO_DATE('2011/04/10', 'YYYY/MM/DD'));
insert into Vender values ('888','PC0002',76,TO_DATE('2011/02/01', 'YYYY/MM/DD'));
insert into Vender values ('888','CC0003',50,TO_DATE('2011/01/30', 'YYYY/MM/DD'));
insert into Vender values ('999','AS0003',60,TO_DATE('2011/01/30', 'YYYY/MM/DD'));
insert into Vender values ('999','AC0002',47,TO_DATE('2011/03/31', 'YYYY/MM/DD'));
insert into Vender values ('999','PC0001',80,TO_DATE('2011/03/22', 'YYYY/MM/DD'));
Valora esta pregunta
Me gusta: Está pregunta es útil y esta claraNo me gusta: Está pregunta no esta clara o no es útil
0
Responder
sin imagen de perfil
Val: 806
Bronce
Ha mantenido su posición en SQL (en relación al último mes)
Gráfica de SQL

Procedimiento PL/SQL

Publicado por leonardo_josue (1173 intervenciones) el 18/04/2017 16:38:57
Hola José:

Tienes varios detalles en tu Procedimiento, pero vamos a tratar de verlos uno por uno:

Primero, tienes definido un cursor de la siguiente manera:

1
2
3
CURSOR o_loc IS
SELECT CODIGO, DIRECCION, LOCALIDAD, PROVINCIA
FROM OFICINAS;

Aquí se observa que en realidad NO ESTÁS FILTRANDO SÓLO LAS OFICINAS QUE ENVÍAS COMO PARÁMETRO, sino que estás mostrando TODAS las oficinas, por lo tanto, debes incluir una cláusula WHERE para extraer sólo la parte que te interesa,

Segundo, según la redacción inicial de tu problema, lo que quieres es mostrar los COMERCIALES de cierta localidad, pero en tu procedimiento EN NINGÚN MOMENTO HACES REFERENCIA A LA TABLA DE COMERCIALES... lo que debes hacer es un JOIN entre tus tablas COMERCIALES y OFICINAS para poder extraer la información que necesitas.

Sería más o menos así:

1
2
3
4
5
6
7
8
SELECT
  codigo, nombre, salario
FROM
  Comerciales
INNER JOIN
  Oficinas ON Comerciales.codOficina = Oficinas.codigo
WHERE
  Oficinas.localidad = "aquí pones el parámetro que estás recibiendo en el procedimiento"

haz la prueba y nos comentas.

Saludos
Leo.
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
1
Comentar
sin imagen de perfil

Procedimiento PL/SQL

Publicado por Jose (7 intervenciones) el 18/04/2017 16:42:08
Ya si me ha salido, muchas gracias por tu ayuda Leo!
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar