SQL> ? [Comando] //ayuda de los comandos -> ? INDEX

CREATE SEQUENCE CODIGONUMERO
START WITH 1
INCREMENT BY 1
/

CREATE OR REPLACE FUNCTION CREACODIGO
(p_nombre IN personas.nombre%TYPE)
RETURN CHAR
IS
v_posicion1 NUMBER(2) :=0;
v_posicion2 NUMBER(2) :=0;
v_codigo CHAR(10);
BEGIN
v_posicion1:= INSTR(p_nombre,' ',1,1);
v_posicion2:= INSTR(p_nombre,' ',1,2);
v_codigo:=SUBSTR(p_nombre,1,1)||SUBSTR(p_nombre,v_posicion1+1,1)||SUBSTR(p_nombre,v_posicion2+1,1)||'_'||TRIM(TO_CHAR(codigonumero.NEXTVAL,'000000'));
RETURN v_codigo;
END;

CREATE OR REPLACE PROCEDURE ingpersona
(p_nombre IN personas.nombre%TYPE)
IS
v_codigo personas.codigo%TYPE;
BEGIN
v_codigo:=CREACODIGO(p_nombre);
INSERT INTO personas (codigo, nombre)
VALUES (UPPER(v_codigo),UPPER(p_nombre));
COMMIT;
END;
//

begin
DBMS_OUTPUT.PUT_LINE
(‘hola’);
END;

set serveroutput on

declare v_myname varchar2(20);
begin
DBMS_OUTPUT.PUT_LINE('My name es: '|| v_myname);
v_myname := 'john';
DBMS_OUTPUT.PUT_LINE('My name es: '|| v_myname);
end;
/

USO DE VARIABLES EXTERNAS

SQL> VARIABLE NOMBRE VARCHAR2(100)
SQL> BEGIN
2  SELECT LAST_NAME
3  INTO :NOMBRE
4  FROM HR.EMPLOYEES
5  WHERE EMPLOYEE_ID=101;
6  END;
7  /

atributo %TYPE

CREATE OR REPLACE
PROCEDURE proc1
(p_employeeId IN hr.employees.employee_id%TYPE)
IS
v_nombre hr.employees.first_name%TYPE;
v_apellido hr.employees.last_name%TYPE;
v_salario hr.employees.salary%TYPE;
BEGIN
SELECT first_name, last_name, salary
INTO v_nombre, v_apellido, v_salario
FROM hr.employees
WHERE employee_id = p_employeeId;
DBMS_OUTPUT.PUT_LINE('nombre : '||v_nombre||' '||v_apellido);
DBMS_OUTPUT.PUT_LINE('Salario: '||TO_CHAR(v_salario,'$99,999.99'));
END;
/



BUCLES

CONDICIONAL:

IF (condición) THEN
//codigo
END IF;

IF (condición) THEN
//código
ELSE
//código
END IF;

IF (condición) THEN
//código
ELSIF (condicion2)
    //código
ELSEIF (condicion3)
    //codigo
ENDIF

ITERATIVO

LOOP
    //código
EXIT WHEN (condición de salida)
END LOOP;

FOR (variable) IN (n..M) LOOP (REVERSE)
    //código
END LOOP;

WHILE (condicion) LOOP
    //condicion
END LOOP

CURSORES

CURSOR cur_variable
IS
    (consulta SQL);
BEGIN

END;


CREATE OR REPLACE PROCEDURE BONOMAGICO
IS
CURSOR cur_personas
  is
  select * from personas;
  BEGIN
      FOR I IN cur_personas LOOP
      IF I.SEXO = 'M' THEN
          DBMS_OUTPUT.PUT_LINE(I.NOMBRE ||' $180.000');
      ELSE
          DBMS_OUTPUT.PUT_LINE(I.NOMBRE ||' $200.000');
      END IF;
      END LOOP;
END;
/


Cálculo de Factorial:

CREATE OR REPLACE PROCEDURE FACTORIAL
(p_num IN NUMBER)
IS
v_resultado NUMBER(36):=1;
BEGIN
FOR I IN 1..p_num LOOP
  v_resultado:= v_resultado * I;
  DBMS_OUTPUT.PUT_LINE(v_resultado);
END LOOP;
END


CREATE OR REPLACE PROCEDURE BONOMAGICO
IS
v_numH number(2):= 0;
v_numM number(2):= 0;
CURSOR cur_personas
 is
 select * from personas;
 BEGIN
     FOR I IN cur_personas LOOP
     IF I.SEXO = 'M' THEN
         DBMS_OUTPUT.PUT_LINE(I.NOMBRE ||' $180.000');
      v_numH := v_numH + 1;
     ELSE
         DBMS_OUTPUT.PUT_LINE(I.NOMBRE ||' $200.000');
      v_numM := v_numM + 1;
     END IF;
  EXIT WHEN (v_numH = 5) AND (v_numM = 5) ;
     END LOOP;
END;
/





CREATE OR REPLACE PROCEDURE ORDENA (
p_dato1 IN number,
p_dato2 IN number,
p_dato3 IN number)
IS
BEGIN
IF (p_dato1 > p_dato2) then
  ORDENA (p_dato1,p_dato3,p_dato2);
elsif p_dato1 >p_dato3 then
  ORDENA (p_dato2,p_dato3,p_dato1);
else
END IF;
  DBMS_OUTPUT.PUT_LINE(p_dato1 || p_dato2 || p_dato3);
END;
/

CREATE OR REPLACE PROCEDURE ORDENA (
p_dato1 IN number,
p_dato2 IN number,
p_dato3 IN number)
IS
BEGIN
IF (p_dato1 > p_dato2) then
 ORDENA (p_dato1,p_dato3,p_dato2);
elsif p_dato1 >p_dato3 then
 ORDENA (p_dato2,p_dato3,p_dato1);
else
  ORDENA (p_dato3,p_dato2,p_dato1);
END IF;
 DBMS_OUTPUT.PUT_LINE(p_dato1 || p_dato2 || p_dato3);
END


TAREA
IN: employee ID
OUT: Entregar apellido , salario, depto, % de la empresa q es el depto


Package

CREATE OR REPLACE PACKAGE OPERACION

IS
FUNCTION SUMA(A IN NUMBER,B IN NUMBER) RETURN NUMBER;
FUNCTION SUMA(A IN NUMBER, B IN NUMBER, C IN NUMBER) RETURN NUMBER;
FUNCTION MULTI(A IN NUMBER, B IN NUMBER) RETURN NUMBER;
PROCEDURE OPERAR(A IN NUMBER DEFAULT 0, B IN NUMBER DEFAULT 0, C IN NUMBER DEFAULT 0);
END;
/

CREATE OR REPLACE PACKAGE BODY OPERACION
IS
FUNCTION SUMA(A IN NUMBER,B IN NUMBER) RETURN NUMBER
  IS
  C NUMBER:= 0;
  BEGIN
  C:=A+B;
  RETURN C;
  END;
FUNCTION SUMA(A IN NUMBER, B IN NUMBER, C IN NUMBER) RETURN NUMBER
  IS
  BEGIN
  RETURN A + B + C;
  END;
FUNCTION MULTI(A IN NUMBER, B IN NUMBER) RETURN NUMBER
  IS
  BEGIN
  RETURN A*B;
  END;
PROCEDURE OPERAR(A IN NUMBER DEFAULT 0, B IN NUMBER DEFAULT 0, C IN NUMBER DEFAULT 0)
  IS
  D NUMBER:=C;
  BEGIN
  DBMS_OUTPUT.PUT_LINE(SUMA(A,B));
  DBMS_OUTPUT.PUT_LINE(SUMA(A,B,C));
  IF D=0 THEN
      D:=1;
  END IF;
  DBMS_OUTPUT.PUT_LINE(MULTI(A,B));
  DBMS_OUTPUT.PUT_LINE(MULTI((MULTI(A,B)),D));
  END;
END;
/

SELECT OPERACION.SUMA(3,4,5)
FROM DUAL
/

EXECUTE OPERACION.OPERAR(2,3)
/

TRIGGER

TIEMPO:  TIMMING -> AFTER, BEFORE
EVENTOS: EVENT -> INSERT, DELETE, UPDATE

CREATE OR REPLACE TRIGGER TRLOG
AFTER DELETE
ON PERSONAS
BEGIN
INSERT INTO BITACORA
VALUES (SYSDATE,'ELIMINAR',USER);
END;
/

CREATE OR REPLACE TRIGGER TRLOG
AFTER DELETE OR UPDATE OR INSERT
ON PERSONAS
BEGIN
CASE
 WHEN INSERTING THEN
      INSERT INTO BITACORA VALUES (SYSDATE,'INSERTAR',USER);
 WHEN UPDATING THEN
       INSERT INTO BITACORA VALUES (SYSDATE,'ACTUALIZAR',USER);
 WHEN DELETING THEN
       INSERT INTO BITACORA VALUES (SYSDATE,'ELIMINAR',USER);
END CASE;
END;
/


RAISE_APPLICATION_ERROR(-20000,”mensaje”);



si cambia el sueldo , registrar el sueldo antiguo y sueldo nuevo.


TRIGER PARA NO PERMITIR TRABAJO EN RANGO HORARIO O DE DIA

CREATE OR REPLACE TRIGGER horario
BEFORE INSERT OR DELETE OR UPDATE ON activos
BEGIN
IF TO_CHAR(SYSDATE, 'DAY') IN ('SUNDAY','SATURDAY')) OR (TO_CHAR (SYSDATE,'HH:MI')='12:30')
THEN
RAISE_APLICATION_ERROR(-2004,"NO PERMITIDO");
END IF;
IF;

COMO SE USAN LAS EXPRESIONES REGULARES EN PL/SQL

QUE SE ACEPTE EL NUMERO de rut como corresponde

////////////////////////////////////////////////////////////gENERAR HISTORIALES
SQL> CREATE TABLE ACTIVOS
 2  AS
 3  SELECT EMPLOYEE_ID AS CODIGO,
 4  LAST_NAME AS NOMBRE,
 5  SALARY AS SUELDO
 6  FROM EMPLOYEES;

 1  CREATE TABLE INACTIVOS(
 2  CODIGO NUMBER(6),
 3  NOMBRE VARCHAR2(25) NOT NULL,
 4  SUELDO NUMBER(8,2),
 5* FECHADESPIDO DATE)

SQL> CREATE TABLE HISTSALARIO     
 2  (CODIGO NUMBER(6),
 3  NOMBRE VARCHAR2(25) NOT NULL,
 4  SUELDOANTIGUO NUMBER(8,2),
 5  SUELDONUEVO NUMBER(8,2),
 6  FECHACAMBIO DATE)
 7  /

SQL> CREATE OR REPLACE TRIGGER TRG_DESPIDO
 2  AFTER DELETE ON ACTIVOS
 3  FOR EACH ROW
 4  BEGIN
 5      INSERT INTO INACTIVOS(CODIGO,NOMBRE,SUELDO,FECHADESPIDO)
 6      VALUES(:OLD.CODIGO, :OLD.NOMBRE , :OLD.SUELDO, SYSDATE);
 7  END;
 8  /

 1  CREATE OR REPLACE TRIGGER TR_UPDATE
 2  AFTER UPDATE OF "SUELDO" ON ACTIVOS  ///TOMAR CAMPOS ESPESIFICOS
 3  FOR EACH ROW
 4  BEGIN
 5      INSERT INTO HISTSALARIO
 6      VALUES (:OLD.CODIGO,:OLD.NOMBRE,:OLD.SUELDO,:NEW.SUELDO,SYSDATE);
 7* END;

//////////////////////////////////SEGUROS CONTRA USUARIOS

 1  CREATE OR REPLACE TRIGGER ANTIJUANITO
 2  BEFORE INSERT OR DELETE OR UPDATE ON ACTIVOS
 3  BEGIN
 4   IF USER='JUANITO' THEN
 5      RAISE_APPLICATION_ERROR(-20005,'YA NOS HABIAN ADVERTIDO DE USTED');
 6  END IF;
 7* END;
SQL> /

////////////////////////////////SEGUROS POR FECHA

    CREATE OR REPLACE TRIGGER ANTIJUANITO
    BEFORE INSERT OR DELETE OR UPDATE ON ACTIVOS
    BEGIN
    IF (TO_CHAR(SYSDATE,'DAY') IN ('SUNDAY','SATURDAY'))
      OR (TO_CHAR(SYSDATE,'HH:MI')='8:34') THEN
     RAISE_APPLICATION_ERROR(-20004,'HORARIO NO PERMITIDO');
  END IF;
  END;


//////////////////////////COMO UTILIZAR EXPRESIONES REGULARES // INGRESO DE RUT

//////////////////////////////////////////////////Trabajop en clases

create table producto(
codigo varchar2(10) primary key,
nombre varchar2(20),
stock_actual number(5),
precio number(8))
/
CREATE TABLE MOVIMIENTOS(
NumCorrelativo varchar2(9) primary key,
fecha date,
tipo_movimiento varchar2(10),
codigo varchar2(10),
stockAntiguo number(6),
stockNuevo number(6),
cantMovimiento number(4),
foreign key(codigo)references producto(codigo))
/

SQL> CREATE SEQUENCE CODIGOPRODUCTO
 2  START WITH 100
 3  INCREMENT BY 1;



CREATE OR REPLACE FUNCTION CREACODIGO
(P_NOMBRE IN PRODUCTO.NOMBRE%TYPE)// y deve recibir un parametro de un lado....
RETURN CHAR // ojo q siempre deve llevar el return una funcion
IS
V_CODIGO CHAR(7);
BEGIN
V_CODIGO:=SUBSTR(P_NOMBRE,0,3)||'-'||TRIM(TO_CHAR(CODIGOPRODUCTO.NEXTVAL));
RETURN V_CODIGO;
END;

 1   CREATE OR REPLACE PROCEDURE INGPRODUCTO
 2   (P_NOMBRE IN PRODUCTO.NOMBRE%TYPE)
 3   IS
 4  V_CODIGO PRODUCTO.CODIGO%TYPE;
 5  BEGIN
 6  V_CODIGO:=CREACODIGO(P_NOMBRE);
 7  INSERT INTO PRODUCTO (CODIGO,NOMBRE,STOCK_ACTUAL,PRECIO)
 8  VALUES(V_CODIGO,P_NOMBRE,0,1000);
 9   COMMIT;
10* END;
11  /
///////////////////////// Ultima Guia///////////////////////////////////////
SQL> update empleado set rango='A' where sueldo <2001;
 1* update empleado set rango='A' where sueldo between 2001 and 5000
SQL> / 
 1* update empleado set rango='B' where sueldo between 2001 and 5000
SQL> /
1* update empleado set rango='C' where sueldo between 5001 and 10000
SQL> /
1* update empleado set rango='D' where sueldo between 10001 and 13000
SQL> /
1* update empleado set rango='E' where sueldo between 13001 and 15000
SQL> /
 1* update empleado set rango='F' where sueldo >15001
SQL> /
////////////////////////CREAR SECUENCIA PARA CODIGO EMPLEADO////
1  create sequence seq_empleado
2  start with 207
  3* increment by 1



////////////////////////FUNCION PARA ASIGNAR RANGO A NUEVO EMPLEADO//////////
create or replace function rangoempleado
(p_sueldo in empleado.sueldo%type)
return varchar2
is
v_rango varchar2(1);
begin
case
when p_sueldo between 0 and 2000 then v_rango:='A';
when p_sueldo between 2001 and 5000 then v_rango:='B';
when p_sueldo between 5001 and 10000 then v_rango:='C';
when p_sueldo between 10001 and 13000 then v_rango:='D';
when p_sueldo between 13001 and 15000 then v_rango:='E';
when p_sueldo > 15001 then v_rango:='F';
end case;
return v_rango;
end;
/
////////////////////////////PROCEDIMIENTO INGRESAR NUEVO EMPLEADO////////////
create or replace procedure nuevoEmpleado
(
p_nombre in empleado.nombre%type,
p_salario in empleado.sueldo%type,
p_departamento in empleado.depcodigo%type)
is
begin
insert into empleado 
values (seq_empleado.nextval,p_nombre,p_salario,p_departamento,rangoempleado(p_salario));
end;

///////////////////////////////////////////////////CAMBIO DEPTO
create or replace procedure cambiodep
(p_jefe in empleado.codigo%type,
p_nuevodep in empleado.depcodigo%type)
is
v_jefe empleado.codigo%type;
begin
select MANAGER_ID into v_jefe
from depto
where MANAGER_ID =p_jefe;
update empleado 
set depcodigo=p_nuevodep
where codigo=v_jefe;
update empleado
set depcodigo=p_nuevodep
where depcodigo=p_nuevodep;
end;
/
/////////////////////////////////////ACTUALIZAR SUELDO
create or replace procedure descuentosueldo
(p_dep_id in depto.department_id%type)
is
v_jefe empleado.codigo%type;
begin
select manager_id into v_jefe 
from depto 
where department_id =p_dep_id;
update empleado 
set sueldo=sueldo-(sueldo*0.1)
where depcodigo=p_dep_id and codigo not in v_jefe;
end;
/////////////////////// CREACION TABLA ACTIVOS
CREATE TABLE ACTIVOS
AS
(SELECT EMPLOYEE_ID CODIGO,FIRST_NAME NOMBRE,SALARY SUELDO,
CASE
WHEN SALARY BETWEEN 0 AND 2000 THEN 'A'
WHEN SALARY BETWEEN 2001 AND 5000 THEN 'B'
WHEN SALARY BETWEEN 5001 AND 10000 THEN 'C'
WHEN SALARY BETWEEN 10001 AND 15000 THEN 'D'
ELSE 'E'
END RANGO,DEPARTMENT_ID DEPTOcodigo 
FROM EMPLOYEES);
/////////////////////////////////// CREACION TABLA DEPTO
CREATE TABLE DEPTO
AS
SELECT * FROM DEPARTMENTS;
ALTER TABLE DEPTO
ADD PRIMARY KEY (DEPARTMENT_ID);
//////////RELACIONAR TABLA DEPTO Y ACTIVOS
ALTER TABLE ACTIVOS
ADD CONSTRAINT FK_DEPTO
FOREIGN KEY (DEPTOcodigo) REFERENCES DEPTO(DEPARTMENT_ID);
// CREACION TABLA RANGOSUELDO
CREATE TABLE RANGOSUELDO (
RANGO CHAR(1) NOT NULL,
SALARIOMINIMO NUMBER(8,2),
SALARIOMAXIMO NUMBER(8,2),
CONSTRAINT PK_RANGO 
PRIMARY KEY(RANGO));
////// POBLAR TABLA RANGOSUELDO
INSERT INTO RANGOSUELDO VALUES('A',0,2000);
INSERT INTO RANGOSUELDO VALUES('B',2001,5000);
INSERT INTO RANGOSUELDO VALUES('C',5001,10000);
INSERT INTO RANGOSUELDO VALUES('D',10001,15000);
INSERT INTO RANGOSUELDO VALUES('E',15000,99999);
// CREACION TABLA F5SUELDO
    CREATE TABLE F5SUELDO(
    USUARIO_ACTUALIZADOR VARCHAR2(20),
    USUARIO_MODIFICADO VARCHAR2(20),
    SUELDO_ANTIGUO NUMBER(8,2),
    SUELDO_NUEVO NUMBER(8,2),
    FECHA DATE);
//	CREACION TRIGGER TR_F5SUELDO
        CREATE OR REPLACE TRIGGER TR_F5SUELDO
        AFTER UPDATE OF "SUELDO" ON ACTIVOS
        FOR EACH ROW
            BEGIN
                   INSERT INTO F5SUELDO (USUARIO_ACTUALIZADOR,USUARIO_MODIFICADO,SUELDO_ANTIGUO,SUELDO_NUEVO,FECHA) 
		   VALUES(USER,:OLD.NOMBRE,:OLD.SUELDO,:NEW.SUELDO,SYSDATE);
            END;
// CREACION PROCEDIMIENTO PRO_F5RANGO
      CREATE OR REPLACE PROCEDURE PRO_F5RANGO
       (P_CODIGO ACTIVOS.CODIGO%TYPE,P_SUELDO ACTIVOS.SUELDO%TYPE)
       IS
               V_AMIN NUMBER(8,2);
               V_BMIN NUMBER(8,2);
               V_CMIN NUMBER(8,2);
               V_DMIN NUMBER(8,2);
               V_EMIN NUMBER(8,2);
               V_AMAX NUMBER(8,2);
              V_BMAX NUMBER(8,2);
              V_CMAX NUMBER(8,2);
              V_DMAX NUMBER(8,2);
              V_EMAX NUMBER(8,2);
              V_RANGO CHAR(1);
      BEGIN
              SELECT SALARIOMINIMO,SALARIOMAXIMO INTO V_AMIN,V_AMAX FROM RANGOSUELDO WHERE RANGO ='A';
              SELECT SALARIOMINIMO,SALARIOMAXIMO INTO V_BMIN,V_BMAX FROM RANGOSUELDO WHERE RANGO ='B';
              SELECT SALARIOMINIMO,SALARIOMAXIMO INTO V_CMIN,V_CMAX FROM RANGOSUELDO WHERE RANGO ='C';
              SELECT SALARIOMINIMO,SALARIOMAXIMO INTO V_DMIN,V_DMAX FROM RANGOSUELDO WHERE RANGO ='D';
              SELECT SALARIOMINIMO,SALARIOMAXIMO INTO V_EMIN,V_EMAX FROM RANGOSUELDO WHERE RANGO ='E';
                 IF P_SUELDO BETWEEN V_AMIN AND V_AMAX THEN V_RANGO :='A';
                 END IF;
                 IF P_SUELDO BETWEEN V_BMIN AND V_BMAX THEN V_RANGO :='B';
                 END IF;
                 IF P_SUELDO BETWEEN V_CMIN AND V_CMAX THEN V_RANGO :='C';
                 END IF;
                 IF P_SUELDO BETWEEN V_DMIN AND V_DMAX THEN V_RANGO :='D';
                 END IF;
                 IF P_SUELDO >= V_EMIN THEN V_RANGO :='E';
                 END IF;
              UPDATE ACTIVOS
              SET RANGO = V_RANGO
              WHERE CODIGO = P_CODIGO ;
     END;
//  CREACION PROCEDIMIENTO PRO_CAMBIODEPTO

	CREATE OR REPLACE PROCEDURE PRO_CAMBIODEPTO
	(P_DEPTOMANAGER ACTIVOS.DEPTOcodigo%TYPE,P_NUEVODEPTO ACTIVOS.DEPTOcodigo%TYPE)
	IS
		V_MANAGER ACTIVOS.codigo%TYPE;
	BEGIN
		SELECT MANAGER_ID INTO V_MANAGER FROM DEPTO
		WHERE DEPARTMENT_ID= P_DEPTOMANAGER ;
                	UPDATE ACTIVOS
		SET DEPTOcodigo = P_NUEVODEPTO
		WHERE CODIGO = V_MANAGER ;
		UPDATE ACTIVOS
		SET DEPTOcodigo= P_NUEVODEPTO
		WHERE DEPTOcodigo = P_DEPTOMANAGER ;
	END;
// CREACION PROCEDIMIENTO PRO_DESCUENTODEPARTAMENTO
          CREATE OR REPLACE PROCEDURE PRO_DESCUENTODEPARTAMENTO
           (P_DEPTO IN ACTIVOS.DEPTOcodigo%TYPE , P_DESCUENTO IN NUMBER)
          IS
                   V_MANAGER ACTIVOS.CODIGO%TYPE;
           BEGIN
                   SELECT MANAGER_ID INTO V_MANAGER FROM DEPTO WHERE DEPARTMENT_ID=P_DEPTO;
                   UPDATE ACTIVOS
                   SET SUELDO=(SUELDO*(100-P_DESCUENTO))/100
                   WHERE DEPTOcodigo= P_DEPTO AND CODIGO NOT IN (V_MANAGER);
       END;
// CREACION TRIGER RANGO AUTOMATICO AL INTERTAR TR_RANGOINSERT
	CREATE OR REPLACE TRIGGER TR_RANGOINSERT
	AFTER INSERT ON ACTIVOS
	OR REACH ROW
	BEGIN
		execute pro_f5sueldo(:new.codigo,:new.sueldo);
	END;