tablas

create user tablas_se identified by tablas_se
default tablespace users
temporary tablespace temp
quota unlimited on users;

create user tablas_sc identified by tablas_sc
default tablespace users
temporary tablespace temp
quota unlimited on users;

create user tablas_sm identified by tablas_sm
default tablespace users
temporary tablespace temp
quota unlimited on users;

grant create session, create table to tablas_sc;
grant create session, create table to tablas_sm;
grant create session, create table to tablas_se;

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

New connection
Name: Conexion SC
User: tablas_sc
Pass: tablas_sc
Connection type: TNS
Network Alias: BD04/Test

(hacer lo mismo con sm y se)
-------------------------------------------------

** alterar pass
alter user tablas_se by tablas_se

-------------------------------------------------
** DESDE TABLAS_SC **

create table Facultades(
IdFacultad number constraint pk_Facultades primary key,
NombreFacultad varchar2(30),
Edificio varchar2(3));

** Para revisar si existe, desde system
select owner, object_name, object_type
from dba_objects
where owner like 'TABLAS%';
**

Create table Carreras(
IdCarrera number constraint pk_Carreras primary key,
NombreCarrera varchar2(30),
Facultad constraint fk1_Carreras references Facultades);

grant references on carreras to tablas_se;

Create table CursosC(
Carrera constraint fk1_CursosC references Carreras,
IdCurso number,
NombreCurso varchar2(30),
Creditos number,
constraint pk_CursosC primary key(Carrera, IdCurso));

grant references on cursosc to tablas_sm;

select object_name, object_type
from user_objects;

** DESDE TABLAS_SE **

create table estudiantes(
Carnet number constraint pk_estudiantes primary key,
NombreEstudiante varchar2(30),
Sexo varchar2(1),
Telefono varchar2(7),
Provincia varchar2(30));

grant references on estudiantes to tablas_sm;

Create table asociado(
Carrera constraint fk1_Asociado references tablas_sc.Carreras,
Estudiante constraint fk2_Asociado references Estudiantes,
Desde date,
Hasta date,
constraint pk_asociado primary key (Carrera, Estudiante));


** DESDE SYSTEM **
from dba_tab_privs
where grantee like 'TABLAS%' or grantor like 'TABLAS%';

** DESDE TABLAS_SM **

Create table Grupos(
Carrera number,
Curso number,
NumeroGrupo number,
Cuatrimestre varchar2(30),
Aula varchar2(4),
Horario varchar2(10),
Capacidad number,
constraint fk1_Grupos foreign key(Carrera,Curso) references tablas_sc.CursosC,
constraint pk_Grupos primary key(Carrera,Curso,NumeroGrupo,Cuatrimestre));

-----------

user: sys as sysdba
pass: test/bd04
server: test/bd04

alter user system identified by test; -- o bd04
alter user system account unlock;


------------- desde system

create user plsql identified by plsql
default tablespace users
temporary tablespace temp
quota unlimited on users;

grant create session, create table, create procedure to plsql;


-------------- nueva conexion

name: Conexion PLSQL
user/pass: plsql
server: test/bd04

---------------- desde plsql

DECLARE
    x  NUMBER := 100;
BEGIN
    FOR i IN 1..10 LOOP
        IF MOD(i,2) = 0 THEN     -- i is even
            INSERT INTO temp VALUES (i, x, 'i is even');
        ELSE
            INSERT INTO temp VALUES (i, x, 'i is odd');
        END IF;

        x := x + 100;
    END LOOP;
    COMMIT;
END;
/




select *
from temp;

select *
from emp;

delete temp;




DECLARE
    CURSOR c1 is
        SELECT ename, empno, sal FROM emp
        ORDER BY sal DESC;   -- start with highest-paid employee
    my_ename  CHAR(10);
    my_empno  NUMBER(4);
    my_sal    NUMBER(7,2);

BEGIN
    OPEN c1;

    FOR i IN 1..5 LOOP
        FETCH c1 INTO my_ename, my_empno, my_sal;
        EXIT WHEN c1%NOTFOUND;   /* in case the number requested is more *
                                  * than the total number of employees   */
        INSERT INTO temp VALUES (my_sal, my_empno, my_ename);
        COMMIT;
    END LOOP;

    CLOSE c1;
END;
/






select *
from temp
order by 1 desc;

delete temp;






DECLARE
    x        NUMBER := 0;
    counter  NUMBER := 0;
BEGIN
    FOR i IN 1..4 LOOP
        x := x + 1000;
        counter := counter + 1;
        INSERT INTO temp VALUES (x, counter, 'in OUTER loop');

        /* start an inner block */
        DECLARE
            x  NUMBER := 0;   -- this is a local version of x
        BEGIN
            FOR i IN 1..4 LOOP
                x := x + 1;   -- this increments the local x
                counter := counter + 1;
                INSERT INTO temp VALUES (x, counter, 'inner loop');
            END LOOP;
        END;

    END LOOP;
    COMMIT;
END;
/






select * from temp;