DBMS LAB - SUBJECT CODE:10PMCA52 EX.

NO: 1 DATE: 10-07-2013 DDL &DML Queries in SQL PLUS AIM: To implement the DDL Queries in SQL PLUS. DDL Queries: 1. Command: CREATE TABLE Syntax: CREATE TABLE table_name( Column_name[ owner_name ] table_name); 2. Command: INSERT INTO Syntax: INSERT INTO table_name VALUES (value1, value2 ...) 3. Command: SELECT FROM Syntax: SELECT column_name(s) FROM table_name 4. Command: ALTER TABLE Syntax: ALTER TABLE [owner_name ] table_name [ADD column_namedatatype attributes] [MODIFY {column_namedatatype | column_constraint}] 5. Command: UPDATE Syntax: UPDATE table_name SET column_name = new_value WHERE column_name = some_value 6. Command: DROP TABLE Syntax: DROP TABLE table name; DEPT OF MCA, VELS UNIVERSITY, PALLAVARAM, CHENNAI 117. 1

DBMS LAB - SUBJECT CODE:10PMCA52

SQL> INSERT INTO IIIMCA VALUES(&REGNO,'&NAME','&CLASS',&M1,&M2,&M3); Enter value for regno: 11304001 Enter value for name: SUKUMAR.A Enter value for class: III MCA Enter value for m1: 60 Enter value for m2: 70 Enter value for m3: 80 old 1: INSERT INTO IIIMCA VALUES(&REGNO,'&NAME','&CLASS',&M1,&M2,&M3) new 1: INSERT INTO IIIMCA VALUES(11304001,'SUKUMAR.A','III MCA',60 ,70,80) 1 row created. SQL> / Enter value for regno: 11304002 Enter value for name: ANEES.A Enter value for class: III MCA Enter value for m1: 55 Enter value for m2: 65 Enter value for m3: 70 old 1: INSERT INTO IIIMCA VALUES(&REGNO,'&NAME','&CLASS',&M1,&M2,&M3) new 1: INSERT INTO IIIMCA VALUES(11304002,'ANEES.A','III MCA',55,65,70) 1 row created. DEPT OF MCA, VELS UNIVERSITY, PALLAVARAM, CHENNAI 117. 2

DBMS LAB - SUBJECT CODE:10PMCA52 SQL> / Enter value for regno: 11304003 Enter value for name: ARASU.R Enter value for class: III MCA Enter value for m1: 65 Enter value for m2: 75 Enter value for m3: 80 old 1: INSERT INTO IIIMCA VALUES(&REGNO,'&NAME','&CLASS',&M1,&M2,&M3) new 1: INSERT INTO IIIMCA VALUES(11304003,'ARASU.R','III MCA',65,75,80) 1 row created. SQL> / Enter value for regno: 11304006 Enter value for name: BHUVANESWARI.J Enter value for class: III MCA Enter value for m1: 68 Enter value for m2: 70 Enter value for m3: 90 old 1: INSERT INTO IIIMCA VALUES(&REGNO,'&NAME','&CLASS',&M1,&M2,&M3) new 1: INSERT INTO IIIMCA VALUES(11304006,'BHUVANESWARI.J','III MCA',68,70,90) 1 row created. SQL> / Enter value for regno: 11304011 Enter value for name: GIRIJA.S Enter value for class: III MCA Enter value for m1: 70 Enter value for m2: 80 Enter value for m3: 90 old 1: INSERT INTO IIIMCA VALUES(&REGNO,'&NAME','&CLASS',&M1,&M2,&M3) new 1: INSERT INTO IIIMCA VALUES(11304011,'GIRIJA.S','III MCA',70,80,90) 1 row created.

DEPT OF MCA, VELS UNIVERSITY, PALLAVARAM, CHENNAI 117.

DBMS LAB - SUBJECT CODE:10PMCA52

SQL> ALTER TABLE IIIMCA ADD(TOT NUMBER(3), AVE NUMBER(5,2)); Table altered. SQL> SELECT * FROM IIIMCA;

SQL> UPDATE IIIMCA SET TOT=M1+M2+M3; 5 rows updated.

SQL> UPDATE IIIMCA SET AVE=TOT/3; 5 rows updated.

DEPT OF MCA, VELS UNIVERSITY, PALLAVARAM, CHENNAI 117.

DBMS LAB - SUBJECT CODE:10PMCA52

SQL> ALTER TABLE IIIMCA ADD(RES VARCHAR2(4)); Table altered.

SQL> UPDATE IIIMCA SET RES='PASS'; 5 rows updated.

SQL> CREATE INDEX NAMEIDX ON IIIMCA(NAME); Index created.

RESULT: The DDL & DML Queries in SQL PLUS are implemented.

DEPT OF MCA, VELS UNIVERSITY, PALLAVARAM, CHENNAI 117.

DBMS LAB - SUBJECT CODE:10PMCA52 EX.NO: 2 DATE: 31-07-2013 RELATIONAL OPERATORS & AGGREGATE FUNCTIONS AIM: To create a table and manipulate the table using logical and comparison operators.

SQL Aggregate Functions

SQL aggregate functions return a single value, calculated from values in a column.

AVG() - Returns the average value COUNT() - Returns the number of rows MAX() - Returns the largest value MIN() - Returns the smallest value SUM() - Returns the sum

SQL AVG() Syntax SELECT AVG(column_name) FROM table_name SQL COUNT(column_name) Syntax The COUNT(column_name) function returns the number of values (NULL values will not be counted) of the specified column: SELECT COUNT(column_name) FROM table_name; SQL COUNT(*) Syntax The COUNT(*) function returns the number of records in a table: SELECT COUNT(*) FROM table_name;

DEPT OF MCA, VELS UNIVERSITY, PALLAVARAM, CHENNAI 117.

DBMS LAB - SUBJECT CODE:10PMCA52 SQL COUNT(DISTINCT column_name) Syntax The COUNT(DISTINCT column_name) function returns the number of distinct values of the specified column: SELECT COUNT(DISTINCT column_name) FROM table_name; The MAX() Function The MAX() function returns the largest value of the selected column. SQL MAX() Syntax SELECT MAX(column_name) FROM table_name; The MIN() Function The MIN() function returns the smallest value of the selected column. SQL MIN() Syntax SELECT MIN(column_name) FROM table_name; The SUM() Function The SUM() function returns the total sum of a numeric column. SQL SUM() Syntax SELECT SUM(column_name) FROM table_name;

DEPT OF MCA, VELS UNIVERSITY, PALLAVARAM, CHENNAI 117.

DBMS LAB - SUBJECT CODE:10PMCA52

DEPT OF MCA, VELS UNIVERSITY, PALLAVARAM, CHENNAI 117.

DBMS LAB - SUBJECT CODE:10PMCA52

RESULT: Thus the Relational Operators and Aggregate Funcitions in SQL PLUS areimplemented.

DEPT OF MCA, VELS UNIVERSITY, PALLAVARAM, CHENNAI 117.

DBMS LAB - SUBJECT CODE:10PMCA52 EX.NO: 3 DATE: 07-08-2013 JOIN & SET OPERATIONS AIM: To create a table and manipulate the table using join operators. Syntax: INNER JOIN SELECT field1, field2, field3 FROM first_table INNER JOIN second_table ON first_table.keyfield = second_table.foreign_keyfield Syntax:LEFT OUTER JOIN SELECT field1, field2, field3 FROM first_table LEFT OUTER JOIN second_table ON first_table.keyfield = second_table.foreign_keyfield Syntax: RIGHT OUTERJOIN SELECT field1, field2, field3 FROM first_table RIGHT OUTER JOIN second_table ON first_table.keyfield = second_table.foreign_keyfield Syntax: FULL OUTERJOIN SELECT field1, field2, field3 FROM first_table FULLOUTER JOIN second_table ON first_table.keyfield = second_table.foreign_keyfield

DEPT OF MCA, VELS UNIVERSITY, PALLAVARAM, CHENNAI 117.

10

DBMS LAB - SUBJECT CODE:10PMCA52 SQL> create table employees( 2 eid number(4), 3 name varchar2(20) 4 ); Table created.

SQL> insert into employees values(&eid,'&name'); Enter value for eid: 1001 Enter value for name: Raman old 1: insert into employees values(&eid,'&name') new 1: insert into employees values(1001,'Raman') 1 row created. SQL> / Enter value for eid: 1002 Enter value for name: Kannan old 1: insert into employees values(&eid,'&name') new 1: insert into employees values(1002,'Kannan') 1 row created. SQL> / Enter value for eid: 1003 Enter value for name: Maha old 1: insert into employees values(&eid,'&name') new 1: insert into employees values(1003,'Maha') 1 row created.

DEPT OF MCA, VELS UNIVERSITY, PALLAVARAM, CHENNAI 117.

11

DBMS LAB - SUBJECT CODE:10PMCA52 SQL> / Enter value for eid: 1004 Enter value for name: Sowmiya old 1: insert into employees values(&eid,'&name') new 1: insert into employees values(1004,'Sowmiya') 1 row created. SQL> commit; Commit complete.

SQL> create table dept( 2 deptvarchar(10), 3 job varchar2(15), 4 eid number(4) 5 ); Table created.

SQL> insert into dept values('&dept','&job',&eid); Enter value for dept: System Enter value for job: Admin Enter value for eid: 1001 old 1: insert into dept values('&dept','&job',&eid) new 1: insert into dept values('System','Admin',1001) 1 row created. DEPT OF MCA, VELS UNIVERSITY, PALLAVARAM, CHENNAI 117. 12

DBMS LAB - SUBJECT CODE:10PMCA52 SQL> / Enter value for dept: Sales Enter value for job: Manager Enter value for eid: 1002 old 1: insert into dept values('&dept','&job',&eid) new 1: insert into dept values('Sales','Manager',1002) 1 row created. SQL> / Enter value for dept: Purchase Enter value for job: Incharge Enter value for eid: 1003 old 1: insert into dept values('&dept','&job',&eid) new 1: insert into dept values('Purchase','Incharge',1003) 1 row created. SQL> / Enter value for dept: MCA Enter value for job: Testing Enter value for eid: 1005 old 1: insert into dept values('&dept','&job',&eid) new 1: insert into dept values('MCA','Testing',1005) 1 row created. SQL> / Enter value for dept: HR Enter value for job: HR Enter value for eid: 1006 old 1: insert into dept values('&dept','&job',&eid) new 1: insert into dept values('HR','HR',1006) 1 row created.

DEPT OF MCA, VELS UNIVERSITY, PALLAVARAM, CHENNAI 117.

13

DBMS LAB - SUBJECT CODE:10PMCA52

SQL> --INNER JOIN

SQL> --LEFT OUTER JOIN

SQL> --RIGHT OUTER JOIN

DEPT OF MCA, VELS UNIVERSITY, PALLAVARAM, CHENNAI 117.

14

DBMS LAB - SUBJECT CODE:10PMCA52 SQL> --FULL OUTER / NATURAL JOIN

SQL>--SET OPERATORS
Syntax: UNION SQL Query 1 UNION SQL Query 2

DEPT OF MCA, VELS UNIVERSITY, PALLAVARAM, CHENNAI 117.

15

DBMS LAB - SUBJECT CODE:10PMCA52

Syntax: UNION ALL SQL Query 1 UNION ALL SQL Query 2

DEPT OF MCA, VELS UNIVERSITY, PALLAVARAM, CHENNAI 117.

16

DBMS LAB - SUBJECT CODE:10PMCA52 Syntax: INTERSECT SQL Query 1 INTERSECT SQL Query 2

Syntax: MINUS SQL Query 1 MINUS SQL Query 2

RESULT: The Different types of Join Queries and Set Opertors in SQL PLUSare implemented.

DEPT OF MCA, VELS UNIVERSITY, PALLAVARAM, CHENNAI 117.

17

DBMS LAB - SUBJECT CODE:10PMCA52 EX.NO:4 DATE: 21-08-2013 STUDENT DATABASE IN PL/SQL AIM: To create a table for student database and update the table using PL/SQL block. SQL> CREATE TABLE MCA1( RNO NUMBER(8), NAME VARCHAR2(15), CLASS VARCHAR2(10), M1 NUMBER(2), M2 NUMBER(2), M3 NUMBER(2), TOT NUMBER(3), AVE NUMBER(5,2), RES VARCHAR2(4), GRADE VARCHAR2(5) );

DEPT OF MCA, VELS UNIVERSITY, PALLAVARAM, CHENNAI 117.

18

DBMS LAB - SUBJECT CODE:10PMCA52 SQL> SET SERVEROUT ON; SQL> declare 2 dreg number(8); 3 dname varchar2(15); 4 dm1 number(3); 5 dm2 number(3); 6 dm3 number(3); 7 dtot number(3); 8 dave number(5,2); 9 dres varchar2(6); 10 dgrade varchar2(6); 11 begin 12 dreg:=&dreg; 13 dname := '&dname'; 14 dm1:=&dm1; 15 dm2:=&dm2; 16 dm3:= &dm3; 17 dtot:= dm1+dm2+dm3; 18 dave:=dtot/3; 19 if(dm1>=50 and dm2>=50 and dm3>=50) then 20 dres:='PASS'; 21 else 22 dres:='FAIL'; 23 end if; 24 if dave>=75 then 25 dgrade:='A'; 26 elsif(dave>=60 and dave<75) then 27 dgrade:='B'; 28 elsif(dave>=50 and dave<60) then 29 dgrade:='C'; 30 else 31 dgrade:='RA'; 32 end if; 33 insert into MCA2 (rno, name, m1,m2,m3,tot, ave, res, grade) 34 values (dreg, dname, dm1, dm2, dm3, dtot, dave, dres, dgrade); 35 end; 36 / Enter value for dreg: 10304011 DEPT OF MCA, VELS UNIVERSITY, PALLAVARAM, CHENNAI 117. 19

DBMS LAB - SUBJECT CODE:10PMCA52 old 12: dreg:=&dreg; new 12: dreg:=10304011; Enter value for dname: GIRIJA old 13: dname := '&dname'; new 13: dname := 'GIRIJA'; Enter value for dm1: 80 old 14: dm1:=&dm1; new 14: dm1:=80; Enter value for dm2: 90 old 15: dm2:=&dm2; new 15: dm2:=90; Enter value for dm3: 95 old 16: dm3:= &dm3; new 16: dm3:= 95; PL/SQL procedure successfully completed. SQL> / Enter value for dreg: 10304006 old 12: dreg:=&dreg; new 12: dreg:=10304006; Enter value for dname: BHUVANA old 13: dname := '&dname'; new 13: dname := 'BHUVANA'; Enter value for dm1: 75 old 14: dm1:=&dm1; new 14: dm1:=75; Enter value for dm2: 80 old 15: dm2:=&dm2; new 15: dm2:=80; Enter value for dm3: 85 old 16: dm3:= &dm3; new 16: dm3:= 85; PL/SQL procedure successfully completed.

DEPT OF MCA, VELS UNIVERSITY, PALLAVARAM, CHENNAI 117.

20

DBMS LAB - SUBJECT CODE:10PMCA52 SQL> / Enter value for dreg: 11304037 old 12: dreg:=&dreg; new 12: dreg:=11304037; Enter value for dname: PRIYA old 13: dname := '&dname'; new 13: dname := 'PRIYA'; Enter value for dm1: 80 old 14: dm1:=&dm1; new 14: dm1:=80; Enter value for dm2: 90 old 15: dm2:=&dm2; new 15: dm2:=90; Enter value for dm3: 95 old 16: dm3:= &dm3; new 16: dm3:= 95; PL/SQL procedure successfully completed. SQL> / Enter value for dreg: 11304003 old 12: dreg:=&dreg; new 12: dreg:=11304003; Enter value for dname: ARASU old 13: dname := '&dname'; new 13: dname := 'ARASU'; Enter value for dm1: 70 old 14: dm1:=&dm1; new 14: dm1:=70; Enter value for dm2: 80 old 15: dm2:=&dm2; new 15: dm2:=80; Enter value for dm3: 90 old 16: dm3:= &dm3; new 16: dm3:= 90; PL/SQL procedure successfully completed.

DEPT OF MCA, VELS UNIVERSITY, PALLAVARAM, CHENNAI 117.

21

DBMS LAB - SUBJECT CODE:10PMCA52

RESULT: The Student table was created, updated and executed in PL/SQL.

DEPT OF MCA, VELS UNIVERSITY, PALLAVARAM, CHENNAI 117.

22

DBMS LAB - SUBJECT CODE:10PMCA52 EX.NO:5 DATE: 04-09-2013 EMPLOYEE PAYROLL IN PL/SQL AIM: To create a table for employee payroll and update the table using PL/SQL block. SQL> CREATE TABLE EMPL1( 2 ENO NUMBER(4) PRIMARY KEY, NAME VARCHAR2(15), 3 DEPT VARCHAR2(10), JOB VARCHAR2(10), 4 BASIC NUMBER(7,2), DA NUMBER(7,2), 5 TA NUMBER(7,2), HRA NUMBER(7,2), 6 CCA NUMBER(7,2), PF NUMBER(7,2), 7 LIC NUMBER(7,2), NETAMT NUMBER(9,2) 8 ); Table created.

DEPT OF MCA, VELS UNIVERSITY, PALLAVARAM, CHENNAI 117.

23

DBMS LAB - SUBJECT CODE:10PMCA52 SQL>--DECLARE BLOCK 1 declare 2 dno number(5); 3 dname varchar2(15); 4 ddept varchar2(10); 5 djob varchar2(10); 6 dbasic number(8,2); 7 dda number(8,2); 8 dta number(8,2); 9 dhra number(8,2); 10 dcca number(8,2); 11 dpf number(8,2); 12 dlic number(8,2); 13 dnetamt number(9,2); 14 begin 15 dno:=&dno; 16 dname := '&dname'; 17 ddept:='&ddept'; 18 djob:='&djob'; 19 dbasic:= &dbasic; 20 dda:= dbasic*60/100; 21 dta:=dbasic*10/100; 22 dhra:=dbasic*15/100; 23 dcca:=dbasic*20/100; 24 dpf:=dbasic*12/100; 25 dlic:=dbasic*11/100; 26 dnetamt:=dbasic+dda+dta+dhra+dcca-(dpf+dlic); 27 insert into empl1(eno, name,dept,job,basic,da,ta,hra,cca,pf, lic,netamt) 28 values(dno,dname,ddept,djob,dbasic,dda,dta,dhra,dcca,dpf,dlic,dnetamt); 29 end; 30 /

DEPT OF MCA, VELS UNIVERSITY, PALLAVARAM, CHENNAI 117.

24

DBMS LAB - SUBJECT CODE:10PMCA52 Enter value for dno: 1005 old 15: dno:=&dno; new 15: dno:=1005; Enter value for dname: KANNAN old 16: dname := '&dname'; new 16: dname := 'KANNAN'; Enter value for ddept: SYSTEM old 17: ddept:='&ddept'; new 17: ddept:='SYSTEM'; Enter value for djob: ANALYST old 18: djob:='&djob'; new 18: djob:='ANALYST'; Enter value for dbasic: 15000 old 19: dbasic:= &dbasic; new 19: dbasic:= 15000; PL/SQL procedure successfully completed. SQL> / Enter value for dno: 1006 old 15: dno:=&dno; new 15: dno:=1006; Enter value for dname: VINOTHRAJ old 16: dname := '&dname'; new 16: dname := 'VINOTHRAJ'; Enter value for ddept: IT old 17: ddept:='&ddept'; new 17: ddept:='IT'; Enter value for djob: S/W PROF old 18: djob:='&djob'; new 18: djob:='S/W PROF'; Enter value for dbasic: 16000 old 19: dbasic:= &dbasic; new 19: dbasic:= 16000; PL/SQL procedure successfully completed.

DEPT OF MCA, VELS UNIVERSITY, PALLAVARAM, CHENNAI 117.

25

DBMS LAB - SUBJECT CODE:10PMCA52 SQL> / Enter value for dno: 1007 old 15: dno:=&dno; new 15: dno:=1007; Enter value for dname: NIJAZ old 16: dname := '&dname'; new 16: dname := 'NIJAZ'; Enter value for ddept: IT old 17: ddept:='&ddept'; new 17: ddept:='IT'; Enter value for djob: HR old 18: djob:='&djob'; new 18: djob:='HR'; Enter value for dbasic: 20000 old 19: dbasic:= &dbasic; new 19: dbasic:= 20000; PL/SQL procedure successfully completed.

RESULT: The Employee table was created, updated and executed in PL/SQL.

DEPT OF MCA, VELS UNIVERSITY, PALLAVARAM, CHENNAI 117.

26

DBMS LAB - SUBJECT CODE:10PMCA52 EX.NO: 6 DATE: 18-09-2013 PROCEDURES AND FUNCTIONS AIM: To study and execute the procedures and functions in PL/SQL.

SYNTAX:PROCEDURE: create or replace procedure <Proc_name> [parameter list] is <local declarations>; begin (executable statements) [exception] (exception handlers) end;

A procedure has two parts namely, specification and body. The procedurespecification begins with keyword procedure and ends with procedure name or parameter list.The procedure body begins with keyword is and ends with the keyword end. It can also includedeclarative, executable and exceptional parts with in the keywords are and end. Syntax toexecute a procedure is. SQL> exec <proc_name> (parameters); FUNCTION: A function is a subprogram that computes a value. The syntax for creating a functionis given below. create or replace function <function_name> [argument] returndatatype is (local declaration) begin DEPT OF MCA, VELS UNIVERSITY, PALLAVARAM, CHENNAI 117. 27

DBMS LAB - SUBJECT CODE:10PMCA52 (executable statements) [exception] (exception handlers) end; where arguments can be in, out or inout. Similar to a procedure, a function also has two parts namely, the function specificationand the function body. The function specification begins with the keyword function and endswith the return clause. The function body begins with the keyword is and ends with the keywordend.

DEPT OF MCA, VELS UNIVERSITY, PALLAVARAM, CHENNAI 117.

28

DBMS LAB - SUBJECT CODE:10PMCA52

SQL> --TABLE CREATION SQL> CREATE TABLE DBUSER ( 2 USER_ID NUMBER (5) NOT NULL, 3 USERNAME VARCHAR2 (20) NOT NULL, 4 CREATED_BY VARCHAR2 (20) NOT NULL, 5 CREATED_DATE DATE NOT NULL, 6 PRIMARY KEY ( USER_ID ) );

Table created. SQL> INSERT INTO DBUSERVALUES(&USER_ID,'&USERNAME','&CREATED_BY','&CREATED_DATE'); Enter value for user_id: 50001 Enter value for username: RAGAVAN Enter value for created_by: MCADEPT Enter value for created_date: 24-OCT-2013 old 1: INSERT INTO DBUSER VALUES(&USER_ID,'&USERNAME','&CREATED_BY','&CREATED_DATE') new 1: INSERT INTO DBUSER VALUES(50001,'RAGAVAN','MCADEPT','24-OCT-2013') 1 row created. SQL> / Enter value for user_id: 50002 Enter value for username: ARASU Enter value for created_by: MCADEPT Enter value for created_date: 23-OCT-2013 old 1: INSERT INTO DBUSER VALUES(&USER_ID,'&USERNAME','&CREATED_BY','&CREATED_DATE') new 1: INSERT INTO DBUSER VALUES(50002,'ARASU','MCADEPT','23-OCT-2013') 1 row created. SQL> / Enter value for user_id: 50003

DEPT OF MCA, VELS UNIVERSITY, PALLAVARAM, CHENNAI 117.

29

DBMS LAB - SUBJECT CODE:10PMCA52

Enter value for username: BAVAN Enter value for created_by: MCADEPT Enter value for created_date: 22-OCT-2013 old 1: INSERT INTO DBUSER VALUES(&USER_ID,'&USERNAME','&CREATED_BY','&CREATED_DATE') new 1: INSERT INTO DBUSER VALUES(50003,'BAVAN','MCADEPT','22-OCT-2013') 1 row created. SQL> / Enter value for user_id: 50004 Enter value for username: BHUVANA Enter value for created_by: MCADEPT Enter value for created_date: 24-OCT-2013 old 1: INSERT INTO DBUSER VALUES(&USER_ID,'&USERNAME','&CREATED_BY','&CREATED_DATE') new 1: INSERT INTO DBUSER VALUES(50004,'BHUVANA','MCADEPT','24-OCT-2013') 1 row created. SQL> / Enter value for user_id: 50005 Enter value for username: KUMAR Enter value for created_by: MCADEPT Enter value for created_date: 22-OCT-2013 old 1: INSERT INTO DBUSER VALUES(&USER_ID,'&USERNAME','&CREATED_BY','&CREATED_DATE') new 1: INSERT INTO DBUSER VALUES(50005,'KUMAR','MCADEPT','22-OCT-2013') 1 row created.

DEPT OF MCA, VELS UNIVERSITY, PALLAVARAM, CHENNAI 117.

30

DBMS LAB - SUBJECT CODE:10PMCA52

SQL> --STORED PROCEDURE SQL> CREATE OR REPLACE PROCEDURE getDBUSERByUserId( 2 p_userid IN DBUSER.USER_ID%TYPE, 3 o_username OUT DBUSER.USERNAME%TYPE, 4 o_createdby OUT DBUSER.CREATED_BY%TYPE, 5 o_date OUT DBUSER.CREATED_DATE%TYPE) 6 IS 7 BEGIN 8 9 SELECT USERNAME , CREATED_BY, CREATED_DATE 10 INTO o_username, o_createdby, o_date 11 FROM DBUSER WHERE USER_ID = p_userid; 12 13 END; 14 / Procedure created. SQL> SET SERVEROUTPUT ON; SQL> --CALLS FROM PL/SQL SQL> DECLARE 2 o_username DBUSER.USERNAME%TYPE; 3 o_createdby DBUSER.CREATED_BY%TYPE; 4 o_date DBUSER.CREATED_DATE%TYPE; 5 BEGIN 6 7 getDBUSERByUserId(50001,o_username,o_createdby,o_date); 8 9 DBMS_OUTPUT.PUT_LINE('username : ' || o_username); 10 DBMS_OUTPUT.PUT_LINE('createdby : ' || o_createdby); 11 DBMS_OUTPUT.PUT_LINE('createddate : ' || o_date); 12 13 END; 14 / username : RAGAVAN createdby : MCADEPT createddate : 24-OCT-13 PL/SQL procedure successfully completed.

DEPT OF MCA, VELS UNIVERSITY, PALLAVARAM, CHENNAI 117.

31

DBMS LAB - SUBJECT CODE:10PMCA52 SYNTAX: FUNCTION CREATE [OR REPLACE] FUNCTION function_name [parameters] RETURN return_datatype; IS Declaration_section BEGIN Execution_section Return return_variable; EXCEPTION exception section Return return_variable; END;

SQL> CREATE OR REPLACE FUNCTION totalemp 2 RETURN number IS 3 total number(5) := 0; 4 BEGIN 5 SELECT count(*) into total 6 FROM empl1; 7 RETURN total; 8 END; 9 / PL/SQL procedure successfully completed. DEPT OF MCA, VELS UNIVERSITY, PALLAVARAM, CHENNAI 117. 32

DBMS LAB - SUBJECT CODE:10PMCA52 SQL> DECLARE 2 c number(5); 3 BEGIN 4 c := totalemp(); 5 dbms_output.put_line('Total no. of Employees: ' || c); 6 END; 7 / Total no. of Employees: 5 Function created.

RESULT: The procedures and functions were studied and executed in PL/SQL.

DEPT OF MCA, VELS UNIVERSITY, PALLAVARAM, CHENNAI 117.

33

DBMS LAB - SUBJECT CODE:10PMCA52 EX.NO: 7 DATE: 08-10-2013 CURSORS AIM: To study the PL/SAQL programs and to write the PL/SAQL programs using cursors. DEFINITION AND SYNTAX: 1. PL/SQL: PL/SQL stands for procedural structural language/SQL. PL/SQLextends SQL byadding control structures found in other structural languages.PL/SQL can be used in the Oracle relational database in the Oracle server and in the client sideapplication development tools. SYNTAX: APL/SQL block can divide into three parts namely, i) Declarative part, ii) Executable part, and iii) Exception handling part. The order is shown below: DECLARE Declarations BEGIN Executable statements EXCEPTION Handlers END;

DEPT OF MCA, VELS UNIVERSITY, PALLAVARAM, CHENNAI 117.

34

DBMS LAB - SUBJECT CODE:10PMCA52 Objects can be declared in declarative part, which can be used in executablepart for further manipulation. All procedural statements are included betweenthe BEGIN and END statements. Errors that occur during the exception are handled by theexception handlers. 2. ATTRIBUTES: Attributes allows us to refer data types and objects from database. PL/SQLconstants and variables have attributes. The following are the types of attributes supported by thePL/SQL, %type %row type %type: %type is used when declaring variables that refer to database columns. SYNTAX: Variablenametablename.Column %type; %rowtype: %rowtype attributes provides a record type that rapresents a row in the tablw. Therecord can store an entire row of data selected from the table or fetched by a cursor. SYNTAX: Variablenametablename %rowtype; 3. CURSORS: Oracle allocates a memory known as the context area for the processing of theSQL statements. A cursor is a pointer or handle to the context area. Through the cursor, aPL/SQL program can control the context area and what happens to it as the statement isprocessed.

DEPT OF MCA, VELS UNIVERSITY, PALLAVARAM, CHENNAI 117.

35

DBMS LAB - SUBJECT CODE:10PMCA52 The three types of the cursors are Static cursors Dynamic cursors REF cursors Static cursors are the ones whose select statements are known at the compile time. These arefurther classified into Explicit cursors Implicit cursors

DEPT OF MCA, VELS UNIVERSITY, PALLAVARAM, CHENNAI 117.

36

DBMS LAB - SUBJECT CODE:10PMCA52 SQL>--CURSORS

SQL> DECLARE var_rows number(5); 2 BEGIN 3 UPDATE empl1 4 SET eno = eno + 1000; 5 IF SQL%NOTFOUND THEN 6 dbms_output.put_line('None of the salaries where updated'); 7 ELSIF SQL%FOUND THEN 8 var_rows := SQL%ROWCOUNT; 9 dbms_output.put_line('Employee ID for ' || var_rows || 'employees are updated'); 10 END IF; 11 END; 12 / Employee ID for 5employees are updated PL/SQL procedure successfully completed.

DEPT OF MCA, VELS UNIVERSITY, PALLAVARAM, CHENNAI 117.

37

DBMS LAB - SUBJECT CODE:10PMCA52

SQL> DECLARE 2 emp_rec empl1%rowtype; 3 CURSOR emp_cur IS 4 SELECT * FROM empl1 5 WHERE eno > 100; 6 BEGIN 7 OPEN emp_cur; 8 FETCH emp_cur INTO emp_rec; 9 dbms_output.put_line (emp_rec.name || ' ' || emp_rec.dept); 10 CLOSE emp_cur; 11 END; 12 / RAGHU SALES PL/SQL procedure successfully completed. SQL> DECLARE 2 salary number(9,2); 3 BEGIN 4 SELECT netamt INTO salary FROM empl1 WHERE eno=&eno; 5 IF SQL%FOUND THEN 6 DBMS_OUTPUT.PUT_LINE('Record Found'); 7 DBMS_OUTPUT.PUT_LINE('NET AMOUNT = ' || salary); 8 END IF; 9 EXCEPTION 10 WHEN NO_DATA_FOUND THEN 11 DBMS_OUTPUT.PUT_LINE('Record Not Found'); 12 END; 13 / Enter value for eno: 31005 old 4: SELECT netamt INTO salary FROM empl1 WHERE eno=&eno; new 4: SELECT netamt INTO salary FROM empl1 WHERE eno=31005; Record Found NET AMOUNT = 16380 PL/SQL procedure successfully completed.

DEPT OF MCA, VELS UNIVERSITY, PALLAVARAM, CHENNAI 117.

38

DBMS LAB - SUBJECT CODE:10PMCA52 SQL> DECLARE 2 salary number(9,2); 3 BEGIN 4 SELECT NETAMT INTO salary FROM empL1 WHERE eno=&eno; 5 IF SQL%NOTFOUND THEN 6 DBMS_OUTPUT.PUT_LINE('Record Not Found'); 7 ELSE 8 DBMS_OUTPUT.PUT_LINE('Record Found'); 9 DBMS_OUTPUT.PUT_LINE('Salary = ' || salary); 10 END IF; 11 END; 12 / Enter value for eno: 31005 old 4: SELECT NETAMT INTO salary FROM empL1 WHERE eno=&eno; new 4: SELECT NETAMT INTO salary FROM empL1 WHERE eno=31005; Record Found Salary = 16380 PL/SQL procedure successfully completed.

SQL> BEGIN 2 UPDATE empL1 SET netamt = netamt +1000; 3 DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || 'Records Updated'); 4 END; 5 / 5Records Updated PL/SQL procedure successfully completed.

DEPT OF MCA, VELS UNIVERSITY, PALLAVARAM, CHENNAI 117.

39

DBMS LAB - SUBJECT CODE:10PMCA52

SQL> DECLARE 2 CURSOR empdata IS 3 SELECT name, dept, netamt FROM empl1 WHERE eno = &no; 4 BEGIN 5 FOR rec IN empdata 6 LOOP 7 DBMS_OUTPUT.PUT_LINE(rec.name || rec.dept || rec.netamt); 8 END LOOP; 9 END; 10 / Enter value for no: 31003 old 3: SELECT name, dept, netamt FROM empl1 WHERE eno = &no; new 3: SELECT name, dept, netamt FROM empl1 WHERE eno = 31003; ANANDSYSTEM24660 PL/SQL procedure successfully completed. SQL> DECLARE 2 CURSOR empdata IS 3 SELECT name, dept, netamt FROM empl1 WHERE eno = &no; 4 BEGIN 5 FOR rec IN empdata 6 LOOP 7 DBMS_OUTPUT.PUT_LINE(rec.name || ' '|| rec.dept ||' '|| rec.netamt); 8 END LOOP; 9 END; 10 / Enter value for no: 31003 old 3: SELECT name, dept, netamt FROM empl1 WHERE eno = &no; new 3: SELECT name, dept, netamt FROM empl1 WHERE eno = 31003; ANAND SYSTEM 24660 PL/SQL procedure successfully completed.

RESULT: Thus the procedures Extension with Cursor were studied and executed inPL/SQL. DEPT OF MCA, VELS UNIVERSITY, PALLAVARAM, CHENNAI 117. 40

DBMS LAB - SUBJECT CODE:10PMCA52 EX.NO: 8 DATE: 22-10-2013 EMBEDDED SQL JDBC ODBC CONNECTION AIM: To write a JAVA program to implement embedded SQL. ALGORITHM: 1. Start the program. 2. Create Database using Oracle with the following fields, 3. Next create the DSN by the following steps: Select Administrative Tools option from Control Panel. Then click on DataSource(ODBC), which displays a dialog box named ODBC DataSourceAdministrator in which click Add button. 4. Go to command prompt and type the JAVA program. 5. Compile and run the program.

DEPT OF MCA, VELS UNIVERSITY, PALLAVARAM, CHENNAI 117.

41

DBMS LAB - SUBJECT CODE:10PMCA52 SQL> Create table jdbc( 2 Rno number(5), 3 Name varchar2(15), 4 Dept varchar2(10) 5 ); import java.io.*; import java.sql.*; public class jdeg { public static void main(String args[])throws IOException { BufferedReader br=new BufferedReader(new InputStreamReader(System.in)); String rollno,nam,dep,mark; System.out.println("Enter the values(rno,name,dept)to insert into table"); rollno=br.readLine(); nam=br.readLine(); dep=br.readLine(); try{ Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); Connection con=DriverManager.getConnection("jdbc:odbc:VEERA","mca","manager"); Statement st=con.createStatement(); st.executeUpdate("insert into jdbc values('"+rollno+"','"+nam+"','"+dep+"')"); ResultSet rs=st.executeQuery("select * from jdbc"); System.out.println(); System.out.println(); System.out.println("RNO\t\tNAME\t\tDEPT"); System.out.println("***\t\t****\t\t****"); while(rs.next()){ System.out.print(rs.getString("rno")+"\t"); System.out.print(rs.getString("name")+"\t\t"); System.out.print(rs.getString("dept")+"\t"); System.out.println(); } } catch(Exception e) { System.out.println(e); } } DEPT OF MCA, VELS UNIVERSITY, PALLAVARAM, CHENNAI 117. 42

DBMS LAB - SUBJECT CODE:10PMCA52

RESULT: The JAVA program to implement embedded SQL has been executed successfully.

DEPT OF MCA, VELS UNIVERSITY, PALLAVARAM, CHENNAI 117.

43