Oracle Ocean

Thursday, December 16, 2010

Emp and Dept Sample Tables and Data

--
-- Copyright (c) Oracle Corporation 1988, 2000. All Rights Reserved.
--
-- NAME
--   demobld.sql
--
-- DESCRIPTION
--   This script creates the SQL*Plus demonstration tables in the
--   current schema. It should be STARTed by each user wishing to
--   access the tables. To remove the tables use the demodrop.sql
--   script.
--
-- USAGE
--    From within SQL*Plus, enter:
--        START demobld.sql

SET TERMOUT ON
PROMPT Building demonstration tables. Please wait.
SET TERMOUT OFF

DROP TABLE EMP;
DROP TABLE DEPT;
DROP TABLE BONUS;
DROP TABLE SALGRADE;
DROP TABLE DUMMY;

CREATE TABLE EMP
       (EMPNO NUMBER(4) NOT NULL,
        ENAME VARCHAR2(10),
        JOB VARCHAR2(9),
        MGR NUMBER(4),
        HIREDATE DATE,
        SAL NUMBER(7, 2),
        COMM NUMBER(7, 2),
        DEPTNO NUMBER(2));

INSERT INTO EMP VALUES
        (7369, 'SMITH', 'CLERK',     7902,
        TO_DATE('17-DEC-1980', 'DD-MON-YYYY'), 800, NULL, 20);
INSERT INTO EMP VALUES
        (7499, 'ALLEN', 'SALESMAN', 7698,
        TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600, 300, 30);
INSERT INTO EMP VALUES
        (7521, 'WARD',   'SALESMAN', 7698,
        TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250, 500, 30);
INSERT INTO EMP VALUES
        (7566, 'JONES', 'MANAGER',   7839,
        TO_DATE('2-APR-1981', 'DD-MON-YYYY'), 2975, NULL, 20);
INSERT INTO EMP VALUES
        (7654, 'MARTIN', 'SALESMAN', 7698,
        TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);
INSERT INTO EMP VALUES
        (7698, 'BLAKE', 'MANAGER',   7839,
        TO_DATE('1-MAY-1981', 'DD-MON-YYYY'), 2850, NULL, 30);
INSERT INTO EMP VALUES
        (7782, 'CLARK', 'MANAGER',   7839,
        TO_DATE('9-JUN-1981', 'DD-MON-YYYY'), 2450, NULL, 10);
INSERT INTO EMP VALUES
        (7788, 'SCOTT', 'ANALYST',   7566,
        TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMP VALUES
        (7839, 'KING',   'PRESIDENT', NULL,
        TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);
INSERT INTO EMP VALUES
        (7844, 'TURNER', 'SALESMAN', 7698,
        TO_DATE('8-SEP-1981', 'DD-MON-YYYY'), 1500,    0, 30);
INSERT INTO EMP VALUES
        (7876, 'ADAMS', 'CLERK',     7788,
        TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);
INSERT INTO EMP VALUES
        (7900, 'JAMES', 'CLERK',     7698,
        TO_DATE('3-DEC-1981', 'DD-MON-YYYY'),   950, NULL, 30);
INSERT INTO EMP VALUES
        (7902, 'FORD',   'ANALYST',   7566,
        TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMP VALUES
        (7934, 'MILLER', 'CLERK',     7782,
        TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);

CREATE TABLE DEPT
       (DEPTNO NUMBER(2),
        DNAME VARCHAR2(14),
        LOC VARCHAR2(13) );

INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO DEPT VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON');

CREATE TABLE BONUS
        (ENAME VARCHAR2(10),
         JOB   VARCHAR2(9),
         SAL   NUMBER,
         COMM NUMBER);

CREATE TABLE SALGRADE
        (GRADE NUMBER,
         LOSAL NUMBER,
         HISAL NUMBER);

INSERT INTO SALGRADE VALUES (1, 700, 1200);
INSERT INTO SALGRADE VALUES (2, 1201, 1400);
INSERT INTO SALGRADE VALUES (3, 1401, 2000);
INSERT INTO SALGRADE VALUES (4, 2001, 3000);
INSERT INTO SALGRADE VALUES (5, 3001, 9999);

CREATE TABLE DUMMY
(DUMMY NUMBER);

INSERT INTO DUMMY VALUES (0);

COMMIT;

SET TERMOUT ON
PROMPT Demonstration table build is complete.

Bind variables - The key to application performance

To understand bind variables, consider an application that generates thousands of SELECT statements against a table; for example:

SELECT fname, lname, pcode FROM cust WHERE id = 674;
SELECT fname, lname, pcode FROM cust WHERE id = 234;
SELECT fname, lname, pcode FROM cust WHERE id = 332;

Each time the query is submitted, Oracle first checks it in the shared pool to see whether this statement has been submitted before. If it has, the execution plan that this statement previously used is retrieved, and the SQL is executed. If the statement cannot be found in the shared pool, Oracle has to go through the process of parsing the statement, working out the various execution paths and coming up with an optimal access plan before it can be executed. This process is known as a hard parse.

When looking for a matching statement in the shared pool, only statements that exactly match the text of the statements are considered; so, if every SQL statement you submit is unique (in that the predicate changes each time, from id = 674 to id=234 and so on) then you'll never get a match, and every statement you submit will need to be hard parsed. Hard parsing is very CPU intensive, and involves obtaining latches on key shared memory areas.

The way to get Oracle to reuse the execution plans for these statements is to use bind variables. Bind variables are substitution variables that are used in place of literals (such as 674, 234, 332) and that have the effect of sending exactly the same SQL to Oracle every time the query is executed. For example, in our application, we would just submit

SELECT fname, lname, pcode FROM cust WHERE id = :cust_no;

and this time we would be able to reuse the execution plan every time, reducing the latch activity in the SGA, and therefore the total CPU activity, which has the effect of allowing our application to scale up to many users on a large dataset.

1. Bind Variables in SQL*Plus

In SQL*Plus you can use bind variables as follows:

SQL> variable deptno numberSQL> exec :deptno := 10SQL> select * from emp where deptno = :deptno;

What we've done to the SELECT statement now is take the literal value out of it, and replace it with a placeholder (our bind variable), with SQL*Plus passing the value of the bind variable to Oracle when the statement is processed.

2. Bind Variables in PL/SQL

Taking PL/SQL first of all, the good news is that PL/SQL itself takes care of most of the issues to do with bind variables, to the point where most code that you write already uses bind variables without you knowing. Take, for example, the following bit of PL/SQL:

CREATE OR REPLACE PROCEDURE update_sal(p_empno in number)
AS
BEGIN
   UPDATE emp
      SET sal     = sal*2
   WHERE empno   = p_empno;
   COMMIT;
END;
/

Now you might be thinking that you've got to replace the p_empno with a bind variable. However, the good news is that every reference to a PL/SQL variable is in fact a bind variable.

3. Bind Variable in Dynamic SQL

In fact, the only time you need to consciously decide to use bind variables when working with PL/SQL is when using Dynamic SQL.

Dynamic SQL, allows you to execute a string containing SQL using the EXECUTE IMMEDIATE command. For next example would always require a hard parse when it is submitted:

CREATE OR REPLACE PROCEDURE update_sal(p_empno in number)
AS

BEGIN
EXECUTE IMMEDIATE
'UPDATE emp

SET sal = sal*2

WHERE empno = '||p_empno;
COMMIT;
END;
/

The way to use bind variables instead is to change the EXECUTE IMMEDIATE command as follows:

CREATE OR REPLACE PROCEDURE update_sal(p_empno in number)
AS
BEGIN
EXECUTE IMMEDIATE
'UPDATE emp

SET sal = sal*2

WHERE empno = :x'

USING p_empno;
COMMIT;
END;
/

And that's all there is to it. One thing to bear in mind, though, is that you can't substitute actual object names (tables, views, columns etc) with bind variables - you can only substitute literals.

Performance Killer

Just to give you a tiny idea of how huge of a difference this can make performance wise, you only need to run a very small test:

SET SERVEROUTPUT ON;
DECLARE
   type rc       IS REF CURSOR;
   l_rc           rc;
   l_dummy        all_objects.object_name%TYPE;
   l_start        NUMBER DEFAULT DBMS_UTILITY.GET_TIME;
BEGIN
   FOR i in 1 .. 5000
   LOOP
      OPEN l_rc

FOR 'SELECT object_name
            FROM all_objects
            WHERE object_id = ' || i;
      FETCH l_rc INTO l_dummy;
      CLOSE l_rc;
   END LOOP;
   DBMS_OUTPUT.PUT_LINE(ROUND((DBMS_UTILITY.GET_TIME - l_start)/100, 2) ||' Seconds...' );
END;
/
166.62 Seconds...

Performance Winner:

DECLARE
   type rc       IS REF CURSOR;
   l_rc           rc;
   l_dummy        all_objects.object_name%TYPE;
   l_start        NUMBER DEFAULT DBMS_UTILITY.GET_TIME;
BEGIN
   FOR i in 1 .. 5000
   LOOP
      OPEN l_rc

FOR 'SELECT object_name
FROM all_objects
WHERE object_id = :x'

USING i;

      FETCH l_rc INTO l_dummy;
      CLOSE l_rc;
   END LOOP;
   DBMS_OUTPUT.PUT_LINE(ROUND((DBMS_UTILITY.GET_TIME - l_start)/100, 2) ||' Seconds...' );
END;
/

0.72 Seconds...

Improvement in Performance is pretty dramatic (230 times). The fact is that not only does this execute much faster (we spent more time PARSING our queries then actually EXECUTING them!) it will let more users use your system simultaneously.

DDL Statement v/s Commit Statement

Some important facts about DDL and Commit statements

Whenever a DDL statement is executed Oracle internally does the following processing:

Parsing of the DDL statement (Checks for the Syntax of the statement)
Commit (If parsing is successful)
Execute the DDL
Commit

It means Oracle issues an implicit commit BEFORE (After Successful parsing) and AFTER execution of any data definition language (DDL, Like CREATE TABLE…, ALTER TABLE…, DROP TABLE…, ANALYZE TABLE…, CREATE PROCEDURE/SEQUENCE etc. etc.) statement.

So, even if your DDL statement parse successfully but fails during execution, you cannot roll back the previous transactions because the server issued a commit.

Let us check this by an example.

SQL> CREATE TABLE DDL_COMMIT(test_value VARCHAR2(100));

Table created.

SQL> INSERT INTO DDL_COMMIT(test_value) VALUES('INSERT BEFORE DDL STATEMENT');

1 row created.

--This DDL statement is syntactically correct hence will be parsed successfully and will issue an internal commit.

SQL> DROP TABLE XYZ;

DROP TABLE XYZ

ERROR at line 1:

ORA-00942: table or view does not exist

--This Rollback statement can’t rollback the last INSERT as it is already committed to database.

SQL> ROLLBACK;

Rollback complete.

SQL> SELECT *

2 FROM DDL_COMMIT;

TEST_VALUE

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

INSERT BEFORE DDL STATEMENT

Now let us check what happens to the transactions when a syntactically incorrect DDL is issued.

SQL> INSERT INTO DDL_COMMIT(test_value) VALUES('INSERT BEFORE Syntactically In-Correct DDL STATEMENT');

1 row created.

SQL> SELECT *

2 FROM DDL_COMMIT;

TEST_VALUE

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

INSERT BEFORE DDL STATEMENT

INSERT BEFORE Syntactically In-Correct DDL STATEMENT

--This DDL statement is syntactically in-correct hence will fail during parsing itself and internal commit will not be issued.

SQL> DROP TABLE XYZ ABC;

DROP TABLE XYZ ABC

ERROR at line 1:

ORA-00933: SQL command not properly ended

--This Rollback statement will rollback the last INSERT

SQL> ROLLBACK;

Rollback complete.

SQL> SELECT *

2 FROM DDL_COMMIT;

TEST_VALUE

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

INSERT BEFORE DDL STATEMENT

Since DDL “DROP TABLE XYZ” was executed after the first record inserted into table. It got committed. You can see that even DDL command (DROP TABLE XYZ;) fails during execution the data gets committed into database.

However the 2^nd DDL statement “DROP TABLE XYZ ABC” which is syntactically incorrect fails at the time of parsing itself hence the 2^nd record didn’t get automatically committed to database.

So whenever you use any DDL statement in any of your program using DYNAMIC SQL (Using EXCECUTE IMMEDIATE) take care of the Transaction commit/rollback before that DDL statement.

If it is really necessary to use a DDL in PL/SQL program, it is advisable to use a separate procedure with Autonomous Transaction for that DDL so that it does not commit the previous transactions.

Benefits of PLSQL over SQL

One of the benefits, mentioned below, might be very useful in term of performance improvement while inserting huge number of records through multiple INSERT statements script.

The SQL statements are sent to the Oracle server one at a time. Each SQL statement results in a separate call to the Oracle server and hence higher performance overhead.

However if all these statements are grouped into one PLSQL block, all statements are passed to the Oracle server as one call and hence giving a significant performance improvement by reducing the network traffic.

You can test it yourself.

Execute the attached 2 scripts, SQL.sql and PLSQL.sql (Place these in C drive) one after another and you will see the significant difference between the times taken by 2 scripts.

SQL > @C:\SQL.sql

SQL_INSERT_START_TIME

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

23-NOV-2010 10:15:35

SQL_INSERT_END_TIME

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

23-NOV-2010 10:19:50

Here time taken to insert 250 records (As present in the script) is 255 Seconds.

SQL > @C:\PLSQL.sql

PLSQL_INSERT_START_TIME

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

23-NOV-2010 10:21:21

PLSQL_INSERT_END_TIME

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

23-NOV-2010 10:21:25

SQL >

Here time taken to insert 250 records (As present in the script) is just 4 Seconds.

Execution time is reduced by 64 times J J

-- SQL.sql

SET TIMING OFF

SET FEEDBACK OFF

SET TERMOUT OFF

DROP TABLE emp;

CREATE TABLE emp

(

empid NUMBER

,empname VARCHAR2(10)

,deptid NUMBER

);

SET TERMOUT ON

SELECT TO_CHAR(SYSDATE,'DD-MON-YYYY HH:MI:SS') SQL_INSERT_START_TIME

FROM DUAL;

SET TERMOUT OFF

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(1,'A1',1001);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(2,'A2',1002);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(3,'A3',1003);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(4,'A4',1004);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(5,'A5',1005);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(6,'A6',1006);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(7,'A7',1007);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(8,'A8',1008);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(9,'A9',1009);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(10,'A10',1010);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(11,'A11',1011);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(12,'A12',1012);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(13,'A13',1013);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(14,'A14',1014);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(15,'A15',1015);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(16,'A16',1016);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(17,'A17',1017);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(18,'A18',1018);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(19,'A19',1019);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(20,'A20',1020);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(21,'A21',1021);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(22,'A22',1022);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(23,'A23',1023);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(24,'A24',1024);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(25,'A25',1025);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(26,'A26',1026);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(27,'A27',1027);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(28,'A28',1028);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(29,'A29',1029);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(30,'A30',1030);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(31,'A31',1031);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(32,'A32',1032);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(33,'A33',1033);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(34,'A34',1034);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(35,'A35',1035);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(36,'A36',1036);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(37,'A37',1037);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(38,'A38',1038);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(39,'A39',1039);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(40,'A40',1040);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(41,'A41',1041);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(42,'A42',1042);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(43,'A43',1043);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(44,'A44',1044);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(45,'A45',1045);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(46,'A46',1046);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(47,'A47',1047);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(48,'A48',1048);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(49,'A49',1049);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(50,'A50',1050);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(51,'A51',1051);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(52,'A52',1052);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(53,'A53',1053);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(54,'A54',1054);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(55,'A55',1055);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(56,'A56',1056);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(57,'A57',1057);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(58,'A58',1058);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(59,'A59',1059);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(60,'A60',1060);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(61,'A61',1061);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(62,'A62',1062);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(63,'A63',1063);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(64,'A64',1064);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(65,'A65',1065);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(66,'A66',1066);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(67,'A67',1067);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(68,'A68',1068);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(69,'A69',1069);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(70,'A70',1070);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(71,'A71',1071);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(72,'A72',1072);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(73,'A73',1073);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(74,'A74',1074);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(75,'A75',1075);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(76,'A76',1076);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(77,'A77',1077);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(78,'A78',1078);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(79,'A79',1079);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(80,'A80',1080);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(81,'A81',1081);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(82,'A82',1082);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(83,'A83',1083);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(84,'A84',1084);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(85,'A85',1085);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(86,'A86',1086);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(87,'A87',1087);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(88,'A88',1088);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(89,'A89',1089);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(90,'A90',1090);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(91,'A91',1091);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(92,'A92',1092);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(93,'A93',1093);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(94,'A94',1094);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(95,'A95',1095);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(96,'A96',1096);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(97,'A97',1097);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(98,'A98',1098);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(99,'A99',1099);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(100,'A100',1100);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(101,'A101',1101);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(102,'A102',1102);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(103,'A103',1103);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(104,'A104',1104);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(105,'A105',1105);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(106,'A106',1106);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(107,'A107',1107);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(108,'A108',1108);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(109,'A109',1109);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(110,'A110',1110);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(111,'A111',1111);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(112,'A112',1112);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(113,'A113',1113);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(114,'A114',1114);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(115,'A115',1115);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(116,'A116',1116);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(117,'A117',1117);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(118,'A118',1118);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(119,'A119',1119);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(120,'A120',1120);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(121,'A121',1121);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(122,'A122',1122);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(123,'A123',1123);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(124,'A124',1124);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(125,'A125',1125);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(126,'A126',1126);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(127,'A127',1127);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(128,'A128',1128);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(129,'A129',1129);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(130,'A130',1130);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(131,'A131',1131);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(132,'A132',1132);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(133,'A133',1133);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(134,'A134',1134);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(135,'A135',1135);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(136,'A136',1136);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(137,'A137',1137);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(138,'A138',1138);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(139,'A139',1139);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(140,'A140',1140);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(141,'A141',1141);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(142,'A142',1142);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(143,'A143',1143);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(144,'A144',1144);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(145,'A145',1145);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(146,'A146',1146);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(147,'A147',1147);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(148,'A148',1148);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(149,'A149',1149);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(150,'A150',1150);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(151,'A151',1151);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(152,'A152',1152);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(153,'A153',1153);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(154,'A154',1154);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(155,'A155',1155);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(156,'A156',1156);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(157,'A157',1157);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(158,'A158',1158);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(159,'A159',1159);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(160,'A160',1160);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(161,'A161',1161);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(162,'A162',1162);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(163,'A163',1163);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(164,'A164',1164);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(165,'A165',1165);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(166,'A166',1166);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(167,'A167',1167);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(168,'A168',1168);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(169,'A169',1169);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(170,'A170',1170);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(171,'A171',1171);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(172,'A172',1172);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(173,'A173',1173);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(174,'A174',1174);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(175,'A175',1175);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(176,'A176',1176);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(177,'A177',1177);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(178,'A178',1178);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(179,'A179',1179);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(180,'A180',1180);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(181,'A181',1181);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(182,'A182',1182);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(183,'A183',1183);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(184,'A184',1184);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(185,'A185',1185);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(186,'A186',1186);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(187,'A187',1187);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(188,'A188',1188);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(189,'A189',1189);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(190,'A190',1190);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(191,'A191',1191);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(192,'A192',1192);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(193,'A193',1193);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(194,'A194',1194);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(195,'A195',1195);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(196,'A196',1196);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(197,'A197',1197);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(198,'A198',1198);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(199,'A199',1199);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(200,'A200',1200);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(201,'A201',1201);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(202,'A202',1202);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(203,'A203',1203);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(204,'A204',1204);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(205,'A205',1205);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(206,'A206',1206);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(207,'A207',1207);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(208,'A208',1208);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(209,'A209',1209);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(210,'A210',1210);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(211,'A211',1211);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(212,'A212',1212);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(213,'A213',1213);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(214,'A214',1214);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(215,'A215',1215);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(216,'A216',1216);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(217,'A217',1217);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(218,'A218',1218);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(219,'A219',1219);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(220,'A220',1220);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(221,'A221',1221);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(222,'A222',1222);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(223,'A223',1223);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(224,'A224',1224);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(225,'A225',1225);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(226,'A226',1226);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(227,'A227',1227);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(228,'A228',1228);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(229,'A229',1229);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(230,'A230',1230);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(231,'A231',1231);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(232,'A232',1232);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(233,'A233',1233);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(234,'A234',1234);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(235,'A235',1235);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(236,'A236',1236);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(237,'A237',1237);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(238,'A238',1238);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(239,'A239',1239);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(240,'A240',1240);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(241,'A241',1241);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(242,'A242',1242);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(243,'A243',1243);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(244,'A244',1244);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(245,'A245',1245);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(246,'A246',1246);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(247,'A247',1247);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(248,'A248',1248);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(249,'A249',1249);

INSERT INTO EMP (EMPID,EMPNAME,DEPTID) VALUES(250,'A250',1250);

SET TERMOUT ON

SELECT TO_CHAR(SYSDATE,'DD-MON-YYYY HH:MI:SS') SQL_INSERT_END_TIME

FROM DUAL;

SET FEEDBACK ON

/*******************************************************************************/

--PLSQL.sql

SET TIMING OFF

SET FEEDBACK OFF

SET TERMOUT OFF

DROP TABLE emp;

CREATE TABLE emp

(

empid NUMBER

,empname VARCHAR2(10)

,deptid NUMBER

);

SET TERMOUT ON

SELECT TO_CHAR(SYSDATE,'DD-MON-YYYY HH:MI:SS') PLSQL_INSERT_START_TIME

FROM DUAL;

SET TERMOUT OFF

BEGIN