OraclePLSQL--“TYPE … RECORD” --- DECLARE

“TYPE … RECORD”  à DECLARE à PL/SQL

SCENARIO

By using “type—record” we want to extract the following information from our database

•       most  sale item “product_id” and least sale item “product_id”
•       most sale item” name” and  least sale item “name”
•      “list_price “of most sale item and “list_price” of least sale item
•       “nos. of order” of most sale item and “nos. of order ” of least sale item

and by this information we want to order these items for  our warehouse…

DECLARE

TYPE QUANTITY

IS RECORD

(PRODUCT_ID        PRODUCT_INFORMATION.PRODUCT_ID%TYPE,

PRODUCT_NAME      PRODUCT_INFORMATION.PRODUCT_NAME%TYPE,

TRANSLATED_NAME   PRODUCT_DESCRIPTIONS.TRANSLATED_NAME%TYPE,

QUANTITY          ORDER_ITEMS.QUANTITY%TYPE,

LIST_PRICE        PRODUCT_INFORMATION.LIST_PRICE%TYPE,

NEED  NUMBER(6,2)

);

MORENEED  QUANTITY;

LESSNEED QUANTITY;

BEGIN

SELECT C.PRODUCT_ID ,C.PRODUCT_NAME,O.TRANSLATED_NAME,QUANTITY,LIST_PRICE, 100

INTO  MORENEED

FROM PRODUCT_INFORMATION C ,PRODUCT_DESCRIPTIONS O,  ORDER_ITEMS T

WHERE c.PRODUCT_ID  = o.PRODUCT_ID

AND o.PRODUCT_ID = t.PRODUCT_ID

AND t.QUANTITY = (SELECT MAX(QUANTITY) FROM ORDER_ITEMS)

AND ROWNUM <= 1;

dbms_output.put_line ('MAX-ORDER_PRODUCT_NAME: ' || MORENEED.PRODUCT_NAME);

dbms_output.put_line ('MAX-ORDER_TRANSLATED_NAME: ' || MORENEED.TRANSLATED_NAME);

dbms_output.put_line ('MAX-ORDER_PRODUCT_ID: ' || MORENEED.PRODUCT_ID);

dbms_output.put_line ('MAX-ORDER_LIST_PRICE: ' || MORENEED.LIST_PRICE);

dbms_output.put_line ('MAX-ORDER-QUANTITY: ' || MORENEED.QUANTITY);

dbms_output.put_line ('QUANTITY_NEED: ' || MORENEED.NEED);

SELECT C.PRODUCT_ID ,C.PRODUCT_NAME,O.TRANSLATED_NAME,QUANTITY,LIST_PRICE, 5

INTO  LESSNEED

FROM PRODUCT_INFORMATION C ,PRODUCT_DESCRIPTIONS O,  ORDER_ITEMS T

WHERE c.PRODUCT_ID  = o.PRODUCT_ID

AND o.PRODUCT_ID = t.PRODUCT_ID

AND t.QUANTITY = (SELECT MIN(QUANTITY ) FROM ORDER_ITEMS)

AND ROWNUM <= 1;

dbms_output.put_line ('MIN-QUANTITY_PRODUCT_NAME: ' || LESSNEED.PRODUCT_NAME);

dbms_output.put_line ('MIN-QUANTITY_TRANSLATED_NAME: ' || LESSNEED.TRANSLATED_NAME);

dbms_output.put_line ('MIN-QUANTITY_PRODUCT_ID: ' || LESSNEED.PRODUCT_ID);

dbms_output.put_line ('MIN-QUANTITY_LIST_PRICE: ' || LESSNEED.LIST_PRICE);

dbms_output.put_line ('MIN-ORDER-QUANTITY: ' || LESSNEED.QUANTITY);

dbms_output.put_line ('QUANTITY_NEED: ' || LESSNEED.NEED);

END;

anonymous block completed

MAX-ORDER_PRODUCT_NAME: Chemicals - TCS

MAX-ORDER_TRANSLATED_NAME: ¿¿¿¿ ¿¿¿¿¿¿¿ - TCS

MAX-ORDER_PRODUCT_ID: 2365

MAX-ORDER_LIST_PRICE: 78

MAX-ORDER-QUANTITY: 209

QUANTITY_NEED: 100

MIN-QUANTITY_PRODUCT_NAME: MB - S900/650+

MIN-QUANTITY_TRANSLATED_NAME: MB - S900/650+

MIN-QUANTITY_PRODUCT_ID: 3114

MIN-QUANTITY_LIST_PRICE: 101

MIN-ORDER-QUANTITY: 0

QUANTITY_NEED: 5

OraclePLSQL--TYPE-TABLE Declare PL/SQL ---Scenario: We want to extract SSN of manager and worker from our database.

TYPE-TABLE   Declare PL/SQL

There are two steps required for define the tables in declare in pl/sql

1-A user defined table  i.e CustmName and this  difnes the data type of the data element  as well as the data type of the index.

2- The second step references the user-defined type declared in the first step and declares one or more instances of this type (orderList and addressList).

DECLARE

    TYPE CustmName

      IS TABLE OF customer.name%TYPE

      INDEX BY PLS_INTEGER;

     orderList        CustmName;

     addressList      CustmName;

…

Scenario;

We want to extract SSN of manager and worker from our database.

DECLARE

TYPE EmpSSNarray

IS TABLE OF employee.ssn%TYPE

INDEX BY PLS_INTEGER;

ManagementList       EmpSSNarray;

WorkerList           EmpSSNarray;

BEGIN

SELECT superssn

INTO ManagementList(1)

FROM employee

WHERE superssn IS NOT NULL

AND ROWNUM <=1;

SELECT superssn

INTO ManagementList(2)

FROM employee

WHERE superssn IS NOT NULL

AND ROWNUM <=1

AND superssn <> ManagementList(1);

SELECT essn

INTO WorkerList(1)

FROM works_on

WHERE hours IS NOT NULL

AND ROWNUM <=1

AND essn NOT IN (ManagementList(1), ManagementList(2));

SELECT essn

INTO WorkerList(2)

FROM works_on

WHERE hours IS NOT NULL

AND ROWNUM <=1

AND essn NOT IN (ManagementList(1), ManagementList(2), WorkerList(1));

dbms_output.put_line ('Managers are: ' || ManagementList(1) || ' ' ||

ManagementList(2));

dbms_output.put_line ('Workers are: ' || WorkerList(1) || ' ' ||

WorkerList(2));

END;

anonymous block completed

Managers are: 333445555 987654321

Workers are: 123456789 453453453

OraclePLSQL--LOOP ---PL/SQL

LOOP

DECLARE

TYPE EmpRecord

IS RECORD (ssn           employee.ssn%TYPE,

LName         employee.LName%TYPE,

DName         department.DName%TYPE,

BonusPayment  NUMBER(6));

InactiveEmp EmpRecord;

BEGIN

FOR i IN 1..3 LOOP

SELECT essn, LName, DName, 0

INTO InactiveEmp

FROM employee, department, works_on

WHERE employee.dno = department.dnumber

AND employee.ssn = works_on.essn

AND hours = (SELECT MIN(hours) FROM works_on)

AND ROWNUM <= 1;

UPDATE department

SET MgrSSN = NULL

WHERE MgrSSN = InactiveEmp.ssn;

UPDATE employee

SET SuperSSN = NULL

WHERE SuperSSN = InactiveEmp.ssn;

DELETE FROM dependent

WHERE essn = InactiveEmp.ssn;

DELETE FROM works_on

WHERE essn = InactiveEmp.ssn;

DELETE FROM employee

WHERE ssn = InactiveEmp.ssn;

COMMIT;

dbms_output.put_line ('Inactive employee has been transferred: ' ||

InactiveEmp.LName);

END LOOP;

END;

Inactive employee has been transferred: Wallace

Inactive employee has been transferred: English

Inactive employee has been transferred: Naraan

OraclePLSQL--GOTO ---PL/SQL

GOTO

The GOTO statement is simply the jump during program execution

               

DECLARE

TYPE EmployeeRecord

IS RECORD (ssn           employee.ssn%TYPE,

LName         employee.LName%TYPE,

DName         department.DName%TYPE,

sex           employee.sex%TYPE,

BonusPayment  NUMBER(6)

);

ActiveEmployee  EmployeeRecord;

InactiveEmployee EmployeeRecord;

BEGIN

<<LocateActive>>

SELECT ssn, LName, DName,sex, 5000

INTO ActiveEmployee

FROM employee, department, works_on

WHERE employee.dno = department.dnumber

AND employee.ssn = works_on.essn

AND hours = (SELECT MAX(hours) FROM works_on)

AND ROWNUM <= 1;

<<OutputActive>>

dbms_output.put_line ('Active employee name: ' || ActiveEmployee.LName);

dbms_output.put_line ('Active employee name: ' || ActiveEmployee.SSN);

dbms_output.put_line ('Active employee department: ' || ActiveEmployee.DName);

dbms_output.put_line ('Active employee gender: ' || ActiveEmployee.sex);

dbms_output.put_line ('Active employee bonus: ' || ActiveEmployee.BonusPayment);

<<LocateInactive>>

SELECT ssn, LName, DName,sex, 0

INTO InactiveEmployee

FROM employee, department, works_on

WHERE employee.dno = department.dnumber

AND employee.ssn = works_on.essn

AND hours = (SELECT MIN(hours) FROM works_on)

AND ROWNUM <= 1;

IF ActiveEmployee.ssn = InactiveEmployee.ssn THEN

GOTO Conclusion;

END IF;

<<OutputInactive>>

dbms_output.put_line ('Inactive employee name: ' || InactiveEmployee.LName);

dbms_output.put_line ('Inactive employee name: ' || ActiveEmployee.SSN);

dbms_output.put_line ('Inactive employee department: ' ||

InactiveEmployee.DName);

dbms_output.put_line ('Inactive employee gender: ' || ActiveEmployee.sex);

dbms_output.put_line ('Inactive employee bonus: ' ||

InactiveEmployee.BonusPayment);

<<Conclusion>>

NULL;

END;

anonymous block completed

Active employee name: Naraan

Active employee name: 666884444

Active employee department: Research

Active employee gender: M

Active employee bonus: 5000

Inactive employee name: Wallace

Inactive employee name: 666884444

Inactive employee department: Administration

Inactive employee gender: M

Inactive employee bonus: 0