Consultas sql

declare
v_fecha date:='&fecha';
begin
DBMS_OUTPUT.PUT_LINE ('La Fecha es: ' ||to_char(v_fecha));
end;

[pic]

[pic]

declare
v_numero integer:=&Numero;
v_mensaje varchar2(40);
begin
if v_numero = 1then v_mensaje := 'Es uno';
DBMS_OUTPUT.PUT_LINE (v_mensaje);
end if;
end;

[pic]

--Ejemplo de IF-THEN-ELSE:
DECLARE
v_salario integer :=&Salario;
v_mensaje varchar2(50);
BEGINIF v_salario < 10000 THEN v_mensaje:='Empleado Publico';
DBMS_OUTPUT.PUT_LINE(v_mensaje);
ELSE
v_mensaje :='Empleado privado';
DBMS_OUTPUT.PUT_LINE(v_mensaje);
END IF;
END;

[pic]

DECLARE
v_salario integer :='Salario';
v_mensaje varchar2(90);
BEGIN
CASE WHEN v_salario < 10000 THEN v_mensaje :='Empleado Publico';
ELSE
v_mensaje :='Empleado Privado';
END CASE;DBMS_OUTPUT.PUT_LINE(v_mensaje);
END;
[pic]

--Ejemplo de Bucle
create table contador (numero integer);
DECLARE
v_contador binary_integer:=1;
BEGIN
LOOP
INSERT INTO contador VALUES (v_contador );
v_contador:=v_contador +1;
EXIT WHEN v_contador = 100;
END LOOP;
END;
[pic]

[pic]

[pic]

create table emp3 AS
select empno, ename, job, sal, deptno
from emp where 1=3;

[pic]

DECLARE
p_empnoemp.empno%TYPE;
p_ename emp.ename%TYPE;
p_job emp.job%TYPE;
p_sal emp.sal%TYPE;
p_deptno emp.deptno%TYPE;
CURSOR cname (p_deptno number) IS
SELECT empno, ename, job, sal, deptno
FROM emp WHERE deptno=p_deptno;
BEGIN
OPEN cname (30);
FETCH cname INTO p_empno, p_ename, p_job, p_sal, p_deptno;
INSERT INTO emp3 VALUES (p_empno, p_ename, p_job, p_sal, p_deptno);
CLOSE cname;
END;
[pic]DECLARE
p_empno emp.empno%TYPE;
p_ename emp.ename%TYPE;
p_job emp.job%TYPE;
p_sal emp.sal%TYPE;
p_deptno emp.deptno%TYPE;
BEGIN
SELECT empno, ename, job, sal, deptno INTO p_empno, p_ename, p_job, p_sal,
p_deptno
FROM empno WHERE empno=7369;
INSERT INTO emp3 VALUES (p_empno, p_ename, p_job, p_sal, p_deptno);
END;
[pic]

[pic]

--Ejemplo de SQL%ROWCOUNT:

BEGIN
UPDATE emp SET sal= 60000
WHERE deptno = 10;
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT||'Fueron Actualizados en la Tabla
emp');
END;
[pic]

--BUCLE FOR para Cursores:

DECLARE
CURSOR cback_emp IS
select empno, ename, job, sal, deptno
from emp;
BEGIN
FOR k IN cback_emp LOOP
INSERT INTO emp3 VALUES (k,empno, k.ename, k.job, k.sal, k.deptno);
END LOOP;
END;
[pic]

DECLARE
CURSOR c_emp IS
SELECT * FROMemp;
BEGIN
FOR fcld IN c_emp LOOP
DBMS_OUTPUT.PUT_LINE (fcld.ENAME ||'Usted Gana'|| fcld.sal);
END LOOP;
END;
[pic]

DECLARE
v_departamento emp.deptno%TYPE :='&v_deptno';
CURSOR c_salario (v_deptno emp.deptno%type)IS
SELECT ename, job, sal FROM emp WHERE deptno=v_deptno;
BEGIN
FOR fcld IN c_salario (v_departamento) LOOP
DBMS_OUTPUT.PUT_LINE ('Salario delDepartamento:'||v_departamento||'ES:
'||fcld.sal);
END LOOP;
END;
[pic]

[pic]

[pic]

[pic]

[pic]

[pic]

--Procedimiento que insertará en emp3 los empleados de un departamento dado:

CREATE OR REPLACE PROCEDURE pinsertemp(pe_deptno emp.deptno%TYPE)IS
p_empno emp.empno%TYPE;
p_ename emp.ename%TYPE;
p_job emp.job%TYPE;
p_sal emp.sal%TYPE;
p_deptno emp.deptno%TYPE;
CURSOR c_empleado IS
SELECT empno,ename, job, sal, deptno
FROM emp WHERE deptno=pe_deptno;
BEGIN
OPEN c_empleado; LOOP
FETCH c_empleado INTO p_empno, p_ename, p_job, p_sal, p_deptno;
INSERT INTO emp3 VALUES (p_empno, p_ename, p_job, p_sal, p_deptno);
END LOOP;
CLOSE c_empleado;
END;

[pic]

[pic]

[pic]

[pic]

[pic]

[pic]

[pic]

[pic]

[pic]

[pic]

[pic]

[pic]
[pic]

[pic]

CREATE ORREPLACE PROCEDURE aum_sal
IS
v_minimo pls_integer:=6400;
v_contador binary_integer;
BEGIN
UPDATE emp SET sal=sal*1.60
where sal < v_minimo;
DBMS_OUTPUT.PUT_LINE('Se Actualizaron: '||SQL%ROWCOUNT ||'Registros');
end;
[pic]

--Procedimiento con ROWTYPE, EXCEPCION, SQLERRM:

CREATE OR REPLACE PROCEDURE printout (cod number)
IS
jose hr.employees&ROWTYPE;
BEGIN
SELECT *
INTO jose
FROM...