dbd_lab11_last_export

--------------------------------------------------------
--  File created - Monday-December-05-2016
--------------------------------------------------------
--------------------------------------------------------
--  DDL for Table DIRECTORIES
--------------------------------------------------------

  CREATE TABLE "DIRECTORIES"
   (	"DIRECTORYID" NUMBER(20,0),
	"NAME" VARCHAR2(20 BYTE),
	"PARENTID" NUMBER
   ) ;
--------------------------------------------------------
--  DDL for Table FILES
--------------------------------------------------------

  CREATE TABLE "FILES"
   (	"FILEID" NUMBER(20,0),
	"NAME" VARCHAR2(20 BYTE),
	"DIRECTORYID" NUMBER(20,0),
	"CONTENT" VARCHAR2(200 BYTE)
   ) ;
REM INSERTING into DIRECTORIES
Insert into DIRECTORIES (DIRECTORYID,NAME,PARENTID) values (10,'documentaries',3);
Insert into DIRECTORIES (DIRECTORYID,NAME,PARENTID) values (11,'A',1);
Insert into DIRECTORIES (DIRECTORYID,NAME,PARENTID) values (12,'B',7);
Insert into DIRECTORIES (DIRECTORYID,NAME,PARENTID) values (1,'music',null);
Insert into DIRECTORIES (DIRECTORYID,NAME,PARENTID) values (2,'genres',1);
Insert into DIRECTORIES (DIRECTORYID,NAME,PARENTID) values (3,'movies',null);
Insert into DIRECTORIES (DIRECTORYID,NAME,PARENTID) values (4,'documents',null);
Insert into DIRECTORIES (DIRECTORYID,NAME,PARENTID) values (5,'books',4);
Insert into DIRECTORIES (DIRECTORYID,NAME,PARENTID) values (6,'english books',5);
Insert into DIRECTORIES (DIRECTORYID,NAME,PARENTID) values (7,'pop',2);
Insert into DIRECTORIES (DIRECTORYID,NAME,PARENTID) values (8,'jazz',2);
Insert into DIRECTORIES (DIRECTORYID,NAME,PARENTID) values (9,'oldies',7);
REM INSERTING into FILES
Insert into FILES (FILEID,NAME,DIRECTORYID,CONTENT) values (8,'best_jazz',8,'nopnop');
Insert into FILES (FILEID,NAME,DIRECTORYID,CONTENT) values (1,'inna.mp3',7,'def');
Insert into FILES (FILEID,NAME,DIRECTORYID,CONTENT) values (2,'pitbull.wav',7,'ghi');
Insert into FILES (FILEID,NAME,DIRECTORYID,CONTENT) values (3,'titanic.avi',3,'j');
Insert into FILES (FILEID,NAME,DIRECTORYID,CONTENT) values (4,'wall-e.avi',3,'k');
Insert into FILES (FILEID,NAME,DIRECTORYID,CONTENT) values (5,'Rich dad Poor dad',5,'l');
Insert into FILES (FILEID,NAME,DIRECTORYID,CONTENT) values (6,'Life of Pi',6,'m');
Insert into FILES (FILEID,NAME,DIRECTORYID,CONTENT) values (7,'jazz.mp3',8,'nopnop');
--------------------------------------------------------
--  DDL for Index DIRECTORIES_PK
--------------------------------------------------------

  CREATE UNIQUE INDEX "DIRECTORIES_PK" ON "DIRECTORIES" ("DIRECTORYID")
  ;
--------------------------------------------------------
--  DDL for Index FILES_PK
--------------------------------------------------------

  CREATE UNIQUE INDEX "FILES_PK" ON "FILES" ("FILEID")
  ;
--------------------------------------------------------
--  Constraints for Table FILES
--------------------------------------------------------

  ALTER TABLE "FILES" ADD CONSTRAINT "FILES_PK" PRIMARY KEY ("FILEID") ENABLE;

  ALTER TABLE "FILES" MODIFY ("FILEID" NOT NULL ENABLE);
--------------------------------------------------------
--  Constraints for Table DIRECTORIES
--------------------------------------------------------

  ALTER TABLE "DIRECTORIES" ADD CONSTRAINT "DIRECTORIES_PK" PRIMARY KEY ("DIRECTORYID") ENABLE;

  ALTER TABLE "DIRECTORIES" MODIFY ("DIRECTORYID" NOT NULL ENABLE);
--------------------------------------------------------
--  Ref Constraints for Table DIRECTORIES
--------------------------------------------------------

  ALTER TABLE "DIRECTORIES" ADD CONSTRAINT "DIRECTORIES_DIRECTORIES_FK1" FOREIGN KEY ("PARENTID")
	  REFERENCES "DIRECTORIES" ("DIRECTORYID") ENABLE;
--------------------------------------------------------
--  Ref Constraints for Table FILES
--------------------------------------------------------

  ALTER TABLE "FILES" ADD FOREIGN KEY ("DIRECTORYID")
	  REFERENCES "DIRECTORIES" ("DIRECTORYID") ON DELETE CASCADE ENABLE;
--------------------------------------------------------
--  DDL for Function TINC
--------------------------------------------------------

  CREATE OR REPLACE FUNCTION "TINC"
(
  NAME_IN IN VARCHAR2
) RETURN VARCHAR2 AS
  total_val NUMBER(20);
BEGIN
  total_val := 0;

   FOR emp in (SELECT income FROM employee WHERE name = name_in)
   LOOP
      total_val := total_val + emp.income;
   END LOOP;
   RETURN total_val;
END TINC;

/

--------------------------------------------------------
--  DDL for Procedure COMPUTETOTALINCOME
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "COMPUTETOTALINCOME"
AS
BEGIN
   dbms_output.put_line(TINC('B'));
END;

BEGIN
  computeTotalIncome();
END;

/

--------------------------------------------------------
--  DDL for Procedure LS
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "LS"
(
  DIRID IN directories.directoryid%TYPE
) AS
norows EXCEPTION;
CURSOR results IS
    select name
    from (select directoryid, name, parentid from directories
          union
          select 0, name, directoryid from files) res
    where parentid=dirid;
BEGIN
  if SQL%ROWCOUNT=0 then
    raise norows;
  else
    dbms_output.put_line('Directories and files found in the directory with id '||dirid||':');
    FOR rec in results LOOP
      dbms_output.put_line(rec.name);
    END LOOP;
  end if;
EXCEPTION
  when norows then
    dbms_output.put_line('Directory with id '||dirid||' is empty.');
END LS;

/