OraclePLSQL--LABELS -----Scenario we want to extract the most busy employee and least busy employee with their information.

LABELS

 Labels name are located any where within the block and aregenerally denoted by the <<lablesname>>

Scenario

 we  want to extract  the most busy employee and least busy employee with their information.

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;

<<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);

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

OraclePLSQL--DML Update & Transaction Control in PL/SQL Scenario: Let suppose we want to transfer the employee who is the least busy and we want to transfer into another task so we write the following program

DML Update & Transaction Control in PL/SQL

Scenario:

Let suppose we want to transfer the employee who is the least busy and we want to transfer into another task so we write the following program.

Tables

Ø  employee,

Ø  department

Ø  works_on

Ø  dependent

DECLARE

TYPE EmpolyeeRecord

IS RECORD (ssn           employee.ssn%TYPE,

LName         employee.LName%TYPE,

DName         department.DName%TYPE,

BonusPayment  NUMBER(6));

InactiveEmpolyee EmpolyeeRecord;

BEGIN

/*

Identify the one employee who has been the least active, based upon the number

of hours they have been working on projects. This will be the first employee

we want to remove from the existing COMPANY database and transfer them into

the new division.

*/

SELECT ssn, LName, DName, 0

INTO InactiveEmpolyee

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;

-- Remove this employee as a manager of any department.

UPDATE department

SET MgrSSN = NULL

WHERE MgrSSN = InactiveEmpolyee.ssn;

---Next, remove this employee as a supervisor of other employees.

UPDATE employee

SET SuperSSN = NULL

WHERE SuperSSN = InactiveEmpolyee.ssn;

-- Delete any dependents and all WORKS_ON rows.

DELETE FROM dependent

WHERE essn = InactiveEmpolyee.ssn;

DELETE FROM works_on

WHERE essn = InactiveEmpolyee.ssn;

-- Finally, delete this employee from the EMPLOYEE table itself.

DELETE FROM employee

WHERE ssn = InactiveEmpolyee.ssn;

-- Transaction control statement to complete the transaction

COMMIT;

dbms_output.put_line ('Least active employee has been transferred: ' ||

InactiveEmpolyee.LName);

END;

Executing this procedure several times then we will get the names of employee in the order of least busy

anonymous block completed

Least active employee has been transferred: Jabour

anonymous block completed

Least active employee has been transferred: Smith

OraclePLSQL--DATA MANIPULATION IN BEGIN CLAUSE pl/sql We want to extract the best employee name and his bdate,gender,ssn,dept name,hours to do work as well as bonus from these three tables and we want to evaluate whether or not lived in a big city or a small town and also evaluate his/her address.

DATA MANIPULATION IN BEGIN CLAUSE

BY SQL FUNCTION and REGULAR EXPRESSIONS

PL/SQL

By SQL function

·         UPPER() character function

·         ROUND() numeric function

·         MAX() function

REGULAR EXPRESSIONS

·         REGEXP_LIKE()

WE USING THREE TABLES

 EMPLOYEE, DEPARTMENT, WORKS_ON

Task

We  want to extract the best employee name and his bdate,gender,ssn,dept name,hours to do work as well as bonus from these three tables and we want to evaluate whether or not lived in a big city or a small town  and also evaluate his/her address.

DECLARE

TYPE EMPLOYEERecord

IS RECORD (ssn           employee.ssn%TYPE,

LName         employee.LName%TYPE,

DName         department.DName%TYPE,

SEX           employee.SEX%TYPE,

BDATE         employee.BDATE%TYPE,

address       employee.address%TYPE,

BonusPayment  NUMBER(10),

HOURS         WORKS_ON.HOURS%TYPE

);

BestEMPLOYEE  EMPLOYEERecord;

BEGIN

SELECT essn, LName, DName,SEX,BDATE,address, 799,HOURS

INTO BestEMPLOYEE

FROM employee, department, works_on

WHERE employee.dno = department.dnumber

AND employee.ssn = works_on.essn

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

;

dbms_output.put_line ('Best employee name: ' ||

UPPER(BestEMPLOYEE.LName));

dbms_output.put_line ('Best employee name dept: ' ||

UPPER(BestEMPLOYEE.DName));

dbms_output.put_line ('Best employee gender: ' ||

UPPER(BestEMPLOYEE.SEX));

dbms_output.put_line ('Best employee BDATE: ' ||

UPPER(BestEMPLOYEE.BDATE));

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

ROUND(BestEMPLOYEE.BonusPayment * 1.24, 4));

dbms_output.put_line ('Best employee SSN: ' ||

UPPER(BestEMPLOYEE.SSN));

dbms_output.put_line ('Best employee HOURS: ' ||

UPPER(BestEMPLOYEE.HOURS));

IF REGEXP_LIKE(BestEMPLOYEE.Address, '[humble|spring]', 'i') THEN

dbms_output.put_line ('Best employee does not live in a big city');

END IF;

dbms_output.put_line ('Best employee lives at ' || BestEMPLOYEE.Address);

END;

anonymous block completed

Best employee name: NARAAN

Best employee name dept: RESEARCH

Best employee gender: M

Best employee BDATE: 15-SEP-52

Best employee bonus: 990.76

Best employee SSN: 666884444

Best employee HOURS: 40

Best employee does not live in a big city

Best employee lives at 975 Fire Oak, Humble, TX

Oracle Sci - KNOWLEDGE INN : DATA MANIPULATION IN BEGIN CLAUSE BY SQL FUNCTION ...

Oracle Sci - KNOWLEDGE INN : DATA MANIPULATION IN BEGIN CLAUSE BY SQL FUNCTION ...: DATA MANIPULATION IN BEGIN CLAUSE BY SQL FUNCTION PL/SQL By SQL function       UPPER() character function         ROUND() num...

OraclePLSQL--DATA MANIPULATION IN BEGIN CLAUSE BY SQL FUNCTION

DATA MANIPULATION IN BEGIN CLAUSE

BY SQL FUNCTION

PL/SQL

By SQL function

•      UPPER() character function
•        ROUND() numeric function
•         MAX() function

WE USING THREE TABLES

 EMPLOYEE, DEPARTMENT, WORKS_ON

Task

we want to extract the best employee name and his bdate,gender,ssn,dept name,hours to do work as well as bonus from these three tables.

DECLARE

TYPE EMPLOYEERecord

IS RECORD

(ssn           employee.ssn%TYPE,

LName         employee.LName%TYPE,

DName         department.DName%TYPE,

SEX           employee.SEX%TYPE,

BDATE         employee.BDATE%TYPE,

BonusPayment  NUMBER(10),

HOURS         WORKS_ON.HOURS%TYPE

);

BestEMPLOYEE  EMPLOYEERecord;

BEGIN

SELECT essn, LName, DName,SEX,BDATE, 799,HOURS

INTO BestEMPLOYEE

FROM employee, department, works_on

WHERE employee.dno = department.dnumber

AND employee.ssn = works_on.essn

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

dbms_output.put_line ('Best employee name: ' ||

UPPER(BestEMPLOYEE.LName));

dbms_output.put_line ('Best employee name dept: ' ||

UPPER(BestEMPLOYEE.DName));

dbms_output.put_line ('Best employee gender: ' ||

UPPER(BestEMPLOYEE.SEX));

dbms_output.put_line ('Best employee BDATE: ' ||

UPPER(BestEMPLOYEE.BDATE));

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

ROUND(BestEMPLOYEE.BonusPayment * 1.24, 4));

dbms_output.put_line ('Best employee SSN: ' ||

UPPER(BestEMPLOYEE.SSN));

dbms_output.put_line ('Best employee HOURS: ' ||

UPPER(BestEMPLOYEE.HOURS));

END;

anonymous block completed

Best employee name: NARAAN

Best employee name dept: RESEARCH

Best employee gender: M

Best employee BDATE: 15-SEP-52

Best employee bonus: 990.76

Best employee SSN: 666884444

Best employee HOURS: 40

OraclePLSQL--uses of Cursors

PL/SQL Cursors

declare

v_COUNTRY_NAME varchar2(80);

cursor get_data is

    

select COUNTRY_NAME

from COUNTRIES;

begin

open get_data;

fetch get_data into v_COUNTRY_NAME ;

dbms_output.put_line(v_COUNTRY_NAME );

close get_data;

end;

anonymous block completed

United States of America

declare

--- v_prod_name varchar2(80);  -----NO NEED

cursor cur_get_data is

select COUNTRY_NAME

from COUNTRIES

WHERE COUNTRY_NAME LIKE 'S%';

begin

for i in cur_get_data

LOOP

dbms_output.put_line(i.COUNTRY_NAME);

END LOOP;

end;

anonymous block completed

Spain

South Africa

Saudi Arabia

Singapore

declare

cursor get_data is

select PROD_ID,UNIT_COST,UNIT_PRICE

from COSTS

WHERE UNIT_COST = 48.99 AND UNIT_PRICE = 62.91;

Begin

for i in get_data

LOOP

dbms_output.put_line( 'PROD_ID:' || i.PROD_ID ||' UNIT_COST: '|| i.UNIT_COST        

                      ||' UNIT_PRICE: '|| i.UNIT_PRICE );

END LOOP;

end;

anonymous block completed

PROD_ID:37 UNIT_COST: 48.99 UNIT_PRICE: 62.91

PROD_ID:37 UNIT_COST: 48.99 UNIT_PRICE: 62.91

PL/SQL CursorsAND If

Declare

cursor get_data is

select PROD_ID,UNIT_COST,UNIT_PRICE

from COSTS ;

Begin

for i in get_data

LOOP

if i.UNIT_COST > 48.99 then

dbms_output.put_line( 'PROD_ID:' || i.PROD_ID ||' UNIT_COST: '|| i.UNIT_COST ||' UNIT_PRICE: '|| i.UNIT_PRICE );

else

dbms_output.put_line( 'PROD_ID:' || i.PROD_ID ||' not_order: '|| i.UNIT_COST ||' not_order: '|| i.UNIT_PRICE );

end if;

END LOOP;

end;

anonymous block completed

PROD_ID:148 not_order: 18.64 not_order: 28.76

PROD_ID:148 not_order: 18.64 not_order: 29.39

PROD_ID:14 UNIT_COST: 904.18 UNIT_PRICE: 1159.99

PROD_ID:17 UNIT_COST: 988.79 UNIT_PRICE: 1516.93

declare

v_countries_count number;

begin

select count(*) into v_countries_count from COUNTRIES;

FOR i IN 1 ..v_countries_count

LOOP

dbms_output.put_line('COUNTRIES ' || i);

END LOOP;

end;

anonymous block completed

COUNTRIES 1

COUNTRIES 2

COUNTRIES 3

COUNTRIES 4

COUNTRIES 5

COUNTRIES 6

COUNTRIES 7

COUNTRIES 8

COUNTRIES 9

COUNTRIES 10

COUNTRIES 11

COUNTRIES 12

COUNTRIES 13

COUNTRIES 14

COUNTRIES 15

COUNTRIES 16

COUNTRIES 17

COUNTRIES 18

COUNTRIES 19

COUNTRIES 20

COUNTRIES 21

COUNTRIES 22

COUNTRIES 23