lab9_DBD_export_work

--------------------------------------------------------
--  File created - Monday-November-21-2016
--------------------------------------------------------
--------------------------------------------------------
--  DDL for Table EMPLOYEE
--------------------------------------------------------

  CREATE TABLE "EMPLOYEE"
   (	"ID" NUMBER(20,0),
	"NAME" VARCHAR2(20 BYTE),
	"INCOME" NUMBER(20,0)
   ) ;
REM INSERTING into EMPLOYEE
Insert into EMPLOYEE (ID,NAME,INCOME) values (1,'A',10);
Insert into EMPLOYEE (ID,NAME,INCOME) values (2,'B',20);
Insert into EMPLOYEE (ID,NAME,INCOME) values (3,'C',10);
Insert into EMPLOYEE (ID,NAME,INCOME) values (4,'A',40);
--------------------------------------------------------
--  DDL for Index EMPLOYEE_PK
--------------------------------------------------------

  CREATE UNIQUE INDEX "EMPLOYEE_PK" ON "EMPLOYEE" ("ID")
  ;
--------------------------------------------------------
--  Constraints for Table EMPLOYEE
--------------------------------------------------------

  ALTER TABLE "EMPLOYEE" ADD CONSTRAINT "EMPLOYEE_PK" PRIMARY KEY ("ID") ENABLE;

  ALTER TABLE "EMPLOYEE" MODIFY ("ID" NOT NULL 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;
   --dbms_output.put_line(total_val);
   RETURN total_val;
END TINC;

/

--------------------------------------------------------
--  DDL for Function TOTALINCOME
--------------------------------------------------------

  CREATE OR REPLACE FUNCTION "TOTALINCOME"
   ( name_in IN varchar2 )
   RETURN varchar2
IS
   total_val number(20);

DECLARE
  total_val NUMBER := 0;
  cursor c1 is
     SELECT income
     FROM employee
     WHERE name = name_in;
BEGIN
   FOR emp in c1
   LOOP
      total_val := total_val + emp.income;
   END LOOP;
   dbms_output.put_line(total_val);
   --RETURN total_val;
END;

/

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

  CREATE OR REPLACE PROCEDURE "COMPUTEINCOME"
AS
BEGIN
   dbms_output.put_line('Hello World!');
END;
execute computeIncome;

/

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

  CREATE OR REPLACE PROCEDURE "COMPUTETOTAL"
AS
BEGIN
--   TotalIncome('A');
  TINC('das');
END;

EXECUTE computeTotal;

/

--------------------------------------------------------
--  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 GREETINGS
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "GREETINGS"
AS
BEGIN
   dbms_output.put_line('Hello World!');
END;

/