DATA BASE MANAGEMENT SYSTEM

DATA BASE MANAGEMENT SYSTEM

DATA BASE – COLLECTION OF OBJECTS

OBJECT – TABLES

TABLES – COLLECTIONS OF RECORDS

DATA BASE

DATA BASE , WHICH IS USED TO STORE DATA AND RESTRICTS THE ACCESSIBILITY TO OTHER USERS, THAT MEANS IT PROVIDES THE SECURITY TO DATA.

DATA CAN BE STORED IN DATABASES USING MANY TOOLS

THEY ARE

DBASE
FOXPRO
MSACCESS
ORACLE
SQL SERVER

PROJECT CAN BE DESIGNED WITH 2 TOOLS

1) FRONT END

2) BACK END

1) FRONT END

WHICH CAN BE USED TO DESIGN THE FORMS

SOME FRONT_ENDS ARE

VB, VC++, VB.NET, JAVA ETC…,

2) BACK END

WHICH CAN BE USED TO ACCESS THE DATA FROM DATABASES

SOME BACK_ENDS ARE

FOXPRO, DBASE, MSACCESS, ORACLE AND SQLSERVER ETC…

ORACLE

ORACLE REPRESENTS - OAK RITY ANALYTICAL COMPUTABLE LOGIC ENGINE.

ORACLE VERSIONS

ORACLE 7

ORACLE 8

ORACLE 8i

ORACLE 9i

ORACLE 10g

ORACLE CAN BE DIVIDED INTO TWO PARTS

1) SQL (STRUCTURED QUERY LANGUAGE)

2) PL/SQL ( PROCEDURE LANGUAGE USING SQL)

STRUCTURED QUERY LANGUAGE (SQL)

DEVELOPED BY IBM WITH A NAME SEQUEL (STRUCTURED ENGLISH QUERY LANGUAGE)

USED TO INTERACT WITH THE DATABASE ENGINE TO PERFORM DATABASE RELATED OPERATIONS

CREATING TABLE
ALTERING TABLE
INSERTING DATA
RETRIEVING DATA
EXECUTING PROCEDURES ETC..

LATER IT IS ENHANCED BY ORACLE CORPORATION WITH THE NAME SQL.

SQL PARTS

SQL LANGUAGE DIVIDED TO INTO 4 PARTS

1) DATA DEFINITION LANGUAGE (DDL)

USED BY DATABASE DESIGNER TO CREATE DATABASE AND DATABASE RELATED OBJECTS (TABLES, VIEW, PROCEDURES ETC)

SOME COMMANDS UNDER THIS DDL ARE

CREATE, ALTER, DROP, TRUNCATE ETC.

2) DATA CONTROL LANGUAGE (DCL)

USED BY DATABASE ADMINSTRATOR TO PROVIDE THE SECURITY FRO THE DATABASE FROM USERS.

SOME COMMANDS UNDER THIS ARE

CREATE USER, GRANT, REVOKE, REVOKE ALL, DROP USER ETC.

3) DATA MANIPULATION LANGUAGE (DML)

USED BY APPLICATION DEVELOPER ON BEHALF END USER FOR MANIPULATING DATA INSIDE THE TABLES.

SOME COMMANDS UNDER THIS ARE

INSERT, UPDATE, DELETE ETC.

4) DATA RETRIEVAL LANGUAGE (DRL) / (DQL)

USED BY APPLICATION DEVELOPER ON DEHALF OF END USER FOR RETREIVING DATA FROM DATABASE.

ONLY ONE COMMAND – SELECT

5) TRANSACTION CONTROL LANGUAGE (TCL)

USED BY APPLICATION DEVELOPER ON BEHALF OF END USER FOR SAVING THE CHANGES DONE BY THE USER TO THE DATABASE.

COMMANDS ARE COMMIT, ROLLBACK, SAVEPOINT ETC.

THESE CAN BE USED AFTER DATA MANIPULATION COMMANDS ONLY.

PROCEDURE TO OPEN THE ORACLE SQL*PLUS EDITOR

1) START_MENU ->

ALLPROGRAMS ->

ORAHOME92 ->

APPLICATION DEVELOPMENT ->

+ SQLPLUS (CLICK)

2) TYPE THE USER NAME AND PASSWORD

USERNAME : SCOTT

PASSWORD: TIGER

HOSTSTRING:

DEFAULT USERS FOR ORACLE

USERNAME PASSWORD

SCOTT TIGER (NORMAL USER)

SYSTEM MANAGER (DBA)

SYS CHANGE_ON_INSTALL (DBA)

NOTE:

USER NAME --- REPRESENTS THE WHAT DATABASE TO CONNECT

PASSWORD ---- REPRESENTS THE DATABASE PASSWORD

HOST STRING – REPRESENTS THE ID OF SYSTEM WHERE DATABASE IS EXIST.

TO KNOW THE HOSSTRING(SID) OF CURRENT SYSTEM

START_MENU à

ALLPROGRAMS à

ORAHOME92 à

ENTERPRISE MANAGER CONSOLE (CLICK)

DATATYPES

NUMBER:

USED TO STORE NUMERIC VALUES INTO VARIABLES

SYNTAX:

VARIABLE NUMBER(SIZE);

EG: A NUMBER(3);

A:=123;

NUMBER(P,S):

USED TO STORE THE REAL VALUES.

SYNTAX:

VARIABLE NUMBER(PRECISION, SCALE);

EG: A NUMBER(4,2);

A : = 27.50;

DATE

USED TO STORE THE DATE FORMAT VALUES.

SYNTAX:

VARIABLE DATE;

EG: JOINDATE DATE;

JOINDATE : = ’27-SEP-07’; // DATE FORMAT: DD-MON-YY

CHAR:

USED TO STORE THE CHARACTERS UPTO 2000.

SYNTAX:

VARIABLE CHAR(SIZE);

EG: SNA CHAR(10);

SNA : = ‘ANIL’;

VARCHAR/VARCHAR2

USED TO STORE THE CHARACTERS UPTO 4000.

SYNTAX:

VARIABLE CHAR(SIZE);

EG: SNA VARCHAR2(10);

SNA : = ‘ANIL’;

LONG

USED TO STORE THE CHARACTERS UP 2GB

SYNTAX:

VARIABLE LONG;

EG: SNA LONG;

SNA : = ‘ANIL’;

LONG RAW

USED TO STORE THE LONG BINARY DATA UPTO 2GB.

SYNTAX:

VARIABLE LONGRAW;

LOBS

USED TO STORE THE LARGE DATA UPTO 4GB

THEY ARE CLOB, BLOB, NCLOB, BFILE.

SYNTAX: VARIABLE CLOB;

SQL COMMANDS

TO VIEW ALL OBJECTS FROM CURRENT DATABASE

SELECT * FROM TAB;

TO VIEW THE DATA OF THE TABLE

SELECT * FROM <table_name>;

EG: SELECT * FROM EMP;

TO CREATE THE NEW TABLE

CRETE TABLE <table_name>(COL1 DATATYPE, COL2 DATATYPE,……….);

EG: CREATE TABLE STUDENT(SNO NUMBER(4),SNA VARCHAR2(10),FEE NUMBER(7,2));

INSERTING THE DATA INTO A TABLE

INSERT INTO <table_name> VALUES(VAL1, VAL2, …….);

EG: INSERT INTO STUDENT VALUES(1,’ANIL’,5000);

INSERTING NUMBER OF ROWS INTO A TABLE

INSERT INTO <table_name> VALUES(&COL1,’&COL2’,’&COL3’);

EG: INSERT INTO STUDENT VALUES(&SNO,’&SNA’,&FEE);

INSERTING THE DATA INTO SPECIFIC COLUMNS OF THE TABLE

INSERT INTO <table_name>(col1,col2) VALUES(val1,val2);

EG: INSERT INTO STUDENT(SNO,SNA) VALUES(1,’ANIL’);

TO VIEW THE STUCTURE OF THE TABLE

DESC[RIBE] <table_name>;

EG: DESC EMP;

DESCRIBE EMP;

SELECT

BY USING SELECT WE CAN VIEW THE RECORDS OF THE TABLE.

SYNTAX:

SELECT */COL1,COL2 FROM <table_name> [WHERE CONDITION];

EG: SELECT * FROM EMP;

SLECT EMPNO, ENAME, JOB FROM EMP;

SELECT * FROM EMP WHERE JOB=’SALESMAN’;

TO VIEW THE DATA AS SORTING ORDER

SELECT * FROM <table_name> ORDER BY COL_NAME ASC/DESC;

EG: SELECT * FROM EMP ORDER BY ENAME ASC;

TO ADD THE NEW COLUMN INTO A TABLE

ALTER TABLE <table_name> ADD (COL1 DATATYPE);

EG: ALTER TABLE STUDENT ADD(COURSE VARCHAR2(10));

TO MODIFY THE DATATYPE OF EXISTING COLUMN

ALTER TABLE <table_name> MODIFY(old_col new_type);

EG: ALTER TABLE STUDENT MODIFY(SNO NUMBER(4));

TO DROP THE SPECIFIED COLUMN

ALTER TABLE <table_name> DROP COLUMN COL_NAME;

EG: ALTER TABLE STUDENT DROP COLUMN COURSE;

TO VIEW THE DATA AS SORTING ORDER

SELECT * FROM <table_name> ORDER BY COL_NAME ASC/DESC;

EG: SELECT * FROM EMP ORDER BY ENAME ASC;

TO ADD THE NEW COLUMN INTO A TABLE

ALTER TABLE <table_name> ADD (COL1 DATATYPE);

EG: ALTER TABLE STUDENT ADD(COURSE VARCHAR2(10));

TO MODIFY THE DATATYPE OF EXISTING COLUMN

ALTER TABLE <table_name> MODIFY(old_col new_type);

EG: ALTER TABLE STUDENT MODIFY(SNO NUMBER(4));

TO DROP THE SPECIFIED COLUMN

ALTER TABLE <table_name> DROP COLUMN COL_NAME;

EG: ALTER TABLE STUDENT DROP COLUMN COURSE;

UPDATE

BY USING UPDATE COMMAND WE CAN MODIFY THE RECORDS OF THE TABLE.

SYNTAX:

UPDATE <table_name> SET COL_NAME=EXP [WHERE CONDITION];

EG: UPDATE EMP SET SAL=SAL+1000;

UPDATE EMP SET SAL=SAL+1000 WHERE JOB=’SALESMAN’;

DELETE

BY USING DELETE WE CAN DELETE THE RECORDS OF THE TABLE.

SYNTAX: DELECT FROM <table_name> [WHERE <CONDITION>];

EG: DELETE FROM EMP;

DELETE FROM EMP WHERE JOB=’SALESMAN’;

DROP

BY USING DROP WE CAN DESTROY THE TABLE WITH DATA AND STRUCTURE.

SYNTAX:

DROP TABLE <table_name>

EG: DROP TABLE EMP;

RENAME THE TABLE_NAME

RENAME old_table TO new_table;

EG: RENAME EMPLOYEE TO EMP;

OPERATORS

1) ARITHMATIC OPERATORS

2) CHARACTER OPERATORS

3) COMPARISON OPERATORS

4) LOGICAL OPERATORS

5) SET OPERATORS

1) ARITHMATIC OPERATORS

WHICH ARE USED TO ARITHMATIC CALCULATIONS

+ ADDITION

- SUBTRACTION

* MULTIPLICATION

/ DIVISION

EG:

UPDATE EMP SET SAL=SAL+1000, COMM=SAL*5/100 WHERE JOB=’SALESMAN’;

2) CHARACTER OPERATORS

CONCATINATION OPERATOR ( || )

BY USING THIS OPERATOR WE CAN MERGE THE TWO STRINGS OR TWO COLUMN VALUES.

EG:

SELECT ‘RAVINDRA’ || ‘BABU’ FROM DUAL;

SELECT ‘RS.’ || SAL FROM EMP;

SELECT ENAME || ‘IS EARNING A SALARY OF RS.’|| SAL FROM EMP;

3) COMPARISON OPERATORS

=, >, <, <=, >=, <> OR !=

BETWEEN VAL1 AND VAL2 – DISPLAYS THE VALUES BETWEEN VALUE1 AND VALUE2

IN(VALUES) – DISPLAYS IN GIVEN LIST VALUES

ANY(VALUES) – DISPLAYS ANY ONE IN LIST OF VALUES

ALL(VALUES) – CONDITION MUST SATISFY THE ALL LIST OF VALUES.

NOTE:

ANY, ALL MUST BE PRECEEDS ANY ONE IN ARITHMATIC OPERATOR.

EGS:

SELECT * FROM EMP WHERE JOB=’SALESMAN’;

SELECT * FROM EMP WHERE SAL>=3000;

SELECT * FROM EMP WHERE SAL BETWEEN 2000 AND 3000;

SELECT * FROM EMP WHERE SAL IN(2000,3000);

SELECT * FROM EMP WHERE SAL>ANY(2000,3000);

SELECT * FROM EMP WHERE SAL>ALL(3000,5000);

4) LOGICAL OPERATORS

AND, OR, NOT

AND:

MUST SATISFY THE BOTH CONDITIONS.

EG: SELECT * FROM EMP WHERE DEPTNO=30 AND JOB=’SALESMAN’;

OR:

SATISFY THE ANY ONE OF THE GIVEN CONDITION.

EG: SELECT * FROM EMP WHERE JOB=’SALESMAN’ OR JOB=’CLERK’;

NOT:

DISPLAYS THE VALUES AS NOT PER CONDITION.

EG: SELECT * FROM EMP WHERE NOT JOB=’SALESMAN’;

5) SET OPERATORS

UNION, UNION ALL, INTERSECT, MINUS

UNION:

UNION MERGE THE TWO QUERIES OUTPUT IN SINGLE SET. ( IT DOES NOT DISPLAYS THE DUPLICATE VALUES)

EG: SELECT DEPTNO FROM EMP UNION SELECT DEPTNO FROM DEPT;

UNION ALL:

UNION ALL MERGES THE TWO QUERIES OUTPUT IN SINGLE SET WITH DUPLICATE VALUES.

EG: SELECT DEPTNO FRO M EMP UNION ALL SELECT DEPTNO FROM DEPT;

INTERSECT:

DISPLAYES THE COMMON VALUES IN BOTH QUERIES.

EG: SELECT DEPTNO FROM DEPT INTERSECT SELECT DEPTNO FROM EMP;

MINUS:

SUBSTRACTS THE FIRST SET INTO SECOND SET.

EG: SELECT DEPTNO FROM DEPT MINUS SELECT DEPTNO FROM EMP;

***********************************************************************************

FUNCTIONS

CLASSIFICATION OF FUNCTIONS

a) NUMERIC FUNCTIONS

b) CHARACTER FUNCTIONS

c) DATE FUNCTIONS

d) CONVERSION FUNCTIONS

e) GROUP FUNCTIONS

f) LIST FUNCTIONS

g) SPECIAL FUNCTIONS

a) NUMERIC FUNCTIONS

1) ABS(NUMBER):

RETURNS THE ABSOLUTE VALUE OF THE GIVEN NUMBER.

EG:

select abs(6) from dual;

o/p: 6

select abs(-6) from dual;

o/p: 6

2) SIGN(NUMBER) :

RETURNS THE SIGN OF THE GIVEN VALUE.

Positive 1

Negative -1

Zero 0

Eg:

Select sign(5) from dual;

res: 1

select sign(-5) from dual;

res: -1

select sign(0) from dual;

res: 0

3) MOD(VAL1,VAL2):

RETURNS THE REMAINDER OF THE GIVEN TWO VALUES

Eg:

Select mod(6,4) from dual;

Res: 2

4) POWER(M,N):

RETURNS THE VALUE OF THE M POWER N

Eg: select power(2,3) from dual;

Res: 8

5) SQRT(N):

RETURNS THE SQUARE ROOT OF THE GIVEN VALUE

Eg: select sqrt(16) from dual

Res: 4

6) CEIL(VALUE)

RETURNS ABOVE INTEGER OF THE GIVEN DECIMAL VALUE

Eg: select ceil(2.1), ceil(2.5), ceil(2.9) from dual;

Res: 3

7) FLOOR(VALUE) :

RETURNS BELOW INTEGER OF THE GIVEN DECIMAL VALUE

Eg: select floor(6.1), floor(6.5), floor(6.9) from dual;

Res: 6

8) ROUND(value):

RETURNS THE SMALLEST INTEGER IF THE VALUE IS .5 BELOW OTHER WISE RETURNS THE GREATEST INTEGER.

Eg:

Select round(2.5) from dual;

Res: 3

Select round(2.4) from dual;

Res: 2

Select round(2.7) from dual;

Res: 3

9) TRUNC(value):

REMOVES THE DECIMAL PLACES OF THE GIVEN FLOAT VALUE.

Eg:

Select trunc(1.222) from dual;

Res: 1

Select trunc(34.5678) from dual;

Res: 34.56

CHARACTER FUNCTIONS

1) UPPER()

THE FUNCTION CONVERTS THE GIVEN STRING OR COLUMN FROM LOWERCASE TO UPPERCASE.

Eg:

Select upper(‘ssit computer education’) from dual;

Res: SSIT COMPUTER EDUCATION

UPDATE EMP SET ENAME=UPPER(ENAME);

2) LENGTH(STRING/COLUMN)

Returns the length of the given string or column

Eg: select length(‘computer’) from dual;

Res: 8

3) SUBSTR(COLUMN/STRING,POS[,N]):

Returns the portion of string from starting position to number of characters.

Eg: select substr(‘ssit computer education’,6,8) from dual;

Res: computer.

4) INSTR(COLUMN/STRING,SUBSTR[,POS,LOCATION])

Returns the position of a substring in the given sentence or column.

Eg:

Select instr(‘ssit computer education’,’t’) from dual;

Res: 4

Select instr(‘ssit computer education’,’t’,1,3) from dual;

Res: 20

5) REPLACE(STRING, SEARCH_STRING, REP_STRING)

By using this function we can replace one string with another string.

Eg: select replace(‘ecit computer education’, ‘ecit’, ‘ssit’);

Res: ssit computer education

6) TRANSLATE (MAIN_STRING, SEARCH_STRING, REP_STRING)

This function translates the given word character by character.

Eg: select translate(‘ramarao’,’ra’,’pq’) from dual;

Res: pqmqpqo

7) LTRIM(STRING/COLUMN [,CHARACTER OR STRING])

This function removes the left side blank spaces of the given string or given character.

Eg:

Select ltrim(‘ ssit’) from dual;

Res: ssit

Select ltrim(‘*****ssit’,’*’) from dual;

Res: ssit

8) RTRIM(STRING [,STRING OR CHAR])

This function removes the right side blank spaces of the given string or column.

Eg:

Select rtrim(‘ssit******’,’*’) from dual;

Res: ssit

Select rtrim(‘ssit ‘) from dual;

Res: ssit

9) REVERSE(STRING/COLUMN)

THIS FUNCTION REVERSES THE GIVEN STRING OR COLUMN VALUES.

EG: SELECT REVERSE (‘SSIT COMPUTER EDUCATION’) FROM DUAL;

RES: NOITACUDE RETUPMOC TISS

SELECT ENAME, REVERSE(ENAME) FROM EMP;

10) CONCAT(STRING1, STRING2)

By using this function we can concats one string with another string.

Eg: select concat(‘rs.’, sal) from emp;

Res:

sal

rs.5000

rs.6000

DATE FUNCTIONS

1) SYSDATE

Returns the current system date

Eg: select sysdate from dual;

2) ADD_MONTHS(SYSDATE,N)

By using this function we can add or substract N no. of the months from the given date.

Eg:

Select add_months(sysdate,1) from dual;

Select add_months(sysdate,-1) from dual;

3) MONTHS_BETWEEN(DATE1,DATE2)

This function returns the difference in months between two dates.

Eg:

Select months_between(’12-jan-02’,’12-jan-00’) from dual;

Res: 24 months

Select ename, months_between(sysdate, hiredate)/12 “experience” from emp;

4) LAST_DAY(DATE)

This function returns the last day of the month in the given date.

Eg: select last_day(’01-jan-07’) from dual;

Res: 31-jan-07

5) NEXT_DAY(DATE,’DAY’)

This function returns the next day of the given day

Eg: select next_day(sysdate,’saturday’) from dual;

Res: 27-sep-07

CONVERSION FUNCTIONS

TO_NUMBER(STRING | COLUMN)

by using this function we can convert the given string or column into number format.

Eg:

Select to_number(‘123’) from dual;

Select to_number(ltrim(‘rs.123’,’rs.’))*10 “res” from dual;

Res: 1230

TO_CHAR(NUMBER,[fmFORMAT])

By using this function we can convert the given number in to string format and also converts the given NUMBER into specific number format and also convert the given date into specific date format.

a) CONVERT THE GIVEN NUMBER INTO STRING FORMAT

syntax:

to_char(number)

egs:

select to_char(12345) from dual;

res: ‘12345’

select substr(to_char(12345),2,3) from dual;

res: 234

b) CONVERT THE GIVEN NUMBER INTO SPECIFIC NUMBER FORMAT

syntax:

to_char(number,number_format);

NUMBER FORMATS

Format value output

99999 1234 1234

123456 ######

$9999 1234 $1234

9,999 1234 1,234

9999.99 1234 1234.00

$9,999.99 1234 $1,234.00

9999MI 1234 1234

-1234 1234-

9999PR 1234 1234

-1234 <1234>

EGS:

select to_char(12345,’99,999’) from dual;

res: 12,345

select to_char(12345,’99,999.99’) from dual;

res: 12,345.00

select to_char(12345,’$99,999.99’) from dual;

res: $12,345.00

select to_char(0012345,’B99999’) from dual;

res: 12345

select to_char(12345,’C99,999.99’) from dual;

res: USD12,345.00

select to_char(12345.67,’99999.99eeee’) from dual;

res: 1.23E+04

select to_char(0012345.6700000,’FM99,999.99’) from dual;

res: 12,345.67

select to_char(0012345.6700,’l99,999.99’) from dual;

res: $12,345.67

select to_char(-12345,’99,999mi’) from dual;

res: 12,345.67-

select to_char(-12345,’99,999pr’) from dual;

res: <12,345>

select to_char(-12345.67,’S99,999.99’) from dual;

res: -12,345.67

select to_char(12345.67,’S99,999.99’) from dual;

res: +12,345.67

select to_char(12345,’99999V99’) from dual;

res: 1234500

select to_char(26,’XX’) from dual;

res: 1A

c) CONVERT THE GIVEN DATE INTO DATE FORMAT

syntax:

TO_CHAR(DATE,[fm]DATE_FORMAT)

EG:

Select to_char(sysdate,’dd-mon-yy, hh:mi:ss a.m’) from dual;

Res: 27-09-07, 9:32:45 a.m

Select sysdate, to_char(sysdate,’fmday,month dd, YYYY’) as “Formated Date” from dual;

Res:

SYSDATE Formated Date

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

27-SEP-07 Saturday, SEPTEMBER 27, 2007

TODATE:

TO_DATE(STRING,’FORMAT’)

This function converts the given string into date format.

Select to_date(’27-09-07’,’dd-mm-yy’) from dual;

Res: 27-SEP-07

Insert into student(jdate) values(to_date(‘&jdate’,’dd-mm-yy’));

GROUP FUNCTIONS

SUM(COLUMN):

By using this function we can calculate the total of the given column values.

Eg: select sum(sal) “res” from emp;

AVG(COLUMN):

By using this function we can calculate the average of the given column values.

Eg: select avg(sal) “res” from emp;

MIN(COLUMN):

This function returns minimum value of the given column values.

Eg: select min(sal) from emp;

MAX(COLUMN):

This function returns the max value of the given column values.

Eg: select max(sal) from emp;

COUNT(*):

This function returns the total records of the given table.

Eg: select count(*) from emp;

COUNT(COLUMN):

This function returns the total values in the given column.

Eg: select count(sal) from emp;

LIST FUNCTIONS

LEAST(VALUES):

This function returns the least value of the given values.

Eg: select least(1,2,3) from dual

Res: 1

GREATEST(VALUES):

This function returns the greatest value of the given values.

Eg: select greatest(123) from dual;

Res: 3

SPECIAL FUNCTIONS

DECODE:

Decode function compares the expression to each search value one by one, if the search value equals to given expression it returns the corresponding result, if no match found it returns the default result.

Syntax:

Decode(expression, search, res[search,result,…[def_result]);

Egs:

Select decode(&n,1,’one’,2,’two’,3,’three’,4,’four’,’invalid no’) “result” from dual;

Res:

Enter value of n: 1

One

Enter value of n: 5

Invalid no.

NVL(COLUMN,exp1):

This function returns the exp1, if the column values are null otherwise it returns the column values.

Eg: select NVL(comm.,0) “comm.” From emp;

USING CASE EXPRESSION:

You use the CASE expression to perform if-then-else logic in SQL without having to use PL/SQL.

There are two types of CASE expressions.

1) simple case expressions.

2) Searched case expressions.

USING SIMPLE CASE EXPRESSIONS:

Case SEARCH_EXPRESSION

When exp1 then res1

When exp2 then res2

………..

When expn then resn

Else default_result

End;

Eg:

Select empno,ename,deptno,

Case deptno

When 10 then ‘accounting’

When 20 then ‘research’

When 30 then ‘sales’

Else ‘invalid deptno’

End as “dname”

From emp;

USING SEARCHED CASE EXPRESSIONS:

Case

When cond1 then res1

When cond2 then res2

……..

When condn then resn

Else default_result

End;

Eg:

Select empno,ename,deptno,

Case

When deptno=10 then ‘accounting’

When deptno=20 then ‘research’

When deptno=30 then ‘sales’

Else ‘invalid deptno’

End as “dname”

From emp;

GROUP BY:

Group by clause is used to group equal values of the list of columns into a single group. i.e. all the records with the same values for the columns that are mentioned in the group by clause will be made as single group.

Syntax:

Select group_function from <table_name> group by col_name;

Eg: select deptno,max(sal) from emp group by deptno;

USING GROUP BY AND HAVING CLAUSE TOGETHER:

By using having we can retrieve the specific records after grouping the columns.

Syntax:

Select group_function from <table_name> group by <col_name> having <condition>;

Eg: select job, sum(sal) from emp group by job having job=’SALESMAN’;

ROLLUP:

By using ROLLUP CLAUSE we can group the grouping sets.

Syntax:

Select <group_function> from <table_name> group by rollup(col_name);

Eg: select deptno,sum(sal) from emp group by rollup(deptno);

RES:

DEPTNO SUM(SAL)

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

10 8750

1 10875

1 9400

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

29025

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

CONSTRAINTS

Constraint is a condition which can be applied whenever the data inserted into a table.

Constraints can be defined in 2 ways.

1) COLUMN LEVEL

Syntax:

COLUMN_NAME DATATYPE[CONSTRAINT CONSTRAINT_NAME] CONSTRAINT_TYPE;

2) TABLE LEVEL

Syntax:

COL1 DATATYPE, COL2 DATATYPE, [CONSTRAINT CONSTRAINT_NAME] CONSTRAINT_TYPE( col1,col2,…….);

TYPES OF CONSTRAINTS

1) NOT NULL

2) UNIQUE

3) PRIMARY KEY

4) CHECK

5) FOREIGN KEY

NOT NULL:

If we create the column with using NOT NULL constraint it does not accept the null values in that column.

Eg: create table student(sno number(3) NOT NULL, sna varchar2(10));

UNIQUE:

by using UNIQUE constraint we can restrict the duplicate values.

Eg:

COLUMN_LEVEL:

Create table student(sno number(3) UNIQUE, sna varchar2(10));

TABLE LEVEL:

Create table student(sno number(3), sna varchar2(10), UNIQUE(sno,sna));

PRIMARY KEY:

By using PRIMARY KEY constraint we can’t insert the NULL and duplicate values.

COLUMN_LEVEL:

Create table student(sno number(3) PRIMARY KEY, sna varchar2(10));

TABLE LEVEL:

Create table student(sno number(3), sna varchar2(10), PRIMARY KEY(sno,sna));

CHECK:

CHECK is a condition which can be applied whenever the data inserted into a table.

Egs:

Create table student (sno number(3),sna varchar2(10),fee number(6,2) check(fee>=3000));

FOREIGN KEY:

By using FOREIGN KEY constraint we can provide the link between two tables. If we provide the link between master and child table we can’t insert the data into a child whether the record does’t exist in master table.

If we delete the master table record automatically the corresponding child record will be deleted.

Syntax:

PARENT TABLE:

COL_NAME DATATYPE PRIMARY KEY;

CHILD TABLE:

COL_NAME DATATYPE REFERENCES MASTER(COL_NAME)[ON DELETE CASCADE];

Eg:

Create table student (sno number(3) PRIMARY KEY, sna varchar2(10), grp varchar2(10));

Create table marks (sno number(3) REFERENCES student(sno) on DELETE cascade,

Total number(3));

ADD THE CONSTRAINTS:

By using alter keyword we can add the constraints to already defined columns of the table.

Syntax:

Alter table table_name add const_type(col_name);

Eg: alter table student add primary key (sno);

REMOVE THE CONSTRAINTS:

By using alter command we can drop constraint permanently.

Syntax:

Alter table table_name drop const_type(col_name);

Eg: Alter table student drop primary key

Alter table student drop unique(sno);

ENABLE AND DISABLE CONSTRAINTS:

By using alter command we can add or remove the constraint temporarily.

Syntax:

Alter table table_name ENABLE/DISABLE const_type(col_name);

Eg:

Alter table student ENABLE UNIQUE(SNO);

Alter table student DISABLE UNIQUE(SNO);

TO VIEW THE CONSTRAINTS OF THE PARTICULAR TABLE:

Select constraint_type, status, delete_rule,search_condition from user_constraints where table_name=’table’;

Eg:

Select constraint_type, status, search_condition from user_constraints where table_name=’EMP’;

DEFAULT:

By using default keyword we can initialize the DEFAULT values in the table.

If we not specify the column values the DEFAULT keyword initializes the DEFAULT values.

Syntax:

COLUMN_NAME DATATYPE DEFAULT <DEFAULT_VALUE>

Eg:

Create table student(sno number(3), sna varchar2(10), fee varchar2(10) DEFAULT 5000, jdate date DEFAULT SYSDATE);

JOINS

Joins which are used to combine the two tables.

TYPES OF JOINS:

1) CARTESIAN JOINS

2) EQUI JOINS

3) NON EQUI JOINS

4) SELF JOINS

5) OUTER JOINS

1) CARTESIAN JOINS (OR) CROSS JOIN:

The Cartesian join matches the every row of one table to every row of another table. In this join tables are joined without specify the condition.

Syntax:

Select table1.col1, table1.col2,table2.col1, table2.col2 from table1, table2;

Eg:

Slect faculty_info.faculty, course_info.course from faculty_info, course_info;

Select F.Faculty, C.Course from faculty_info F, Course_info C;

Select Faculty, course from faculty_info, course_info;

2) EQUI JOINS OR NATURAL JOINS:

In this join tables are joined by specifying the condition using EQUALTO operator between two columns of two tables.

Syntax:

Select table1.col1, table1.col2, table2.col1, table2.col2 from table1, table2 where table1.col1=table2.col1;

Eg:

Select emp.ename,emp.job, emp.sal, dept.dname from emp, dept where emp.deptno = dept.deptno;

3) NON EQUI JOINS

In this join tables are joined by specifying the condition with out using EQUAL operator between two columns of two tables.

Syntax:

Select table1.col1, table1.col2, table2.col1, table2.col2 from table1, table2 where table1.col_name between table2.col1 and table2.col2;

Eg:

Select emp.ename, emp.job, emp.sal, salgrade.grade from emp, salgrade where emp.sal between salgrade.losal and salgrade.hisal;

4) SELF JOINS

In this join rows are matched by selecting different columns of same table.

Syntax:

Select t1.col1, t1.col2, t2.col1, t2.col2 from table t1, table t2 where t1.column=t2.column;

Eg:

Select e1.ename “worker”, e2.ename “manager” from emp e1, emp e2 where e1.mgr=e2.empno;

5) OUTER JOINS

By using outer joins we can retrieve the records which does not matched in two tables.

TYPES OF OUTER JOINS:

1) LEFT OUTER JOINS

2) RIGHT OUTER JOINS

1) LEFT OUTER JOINS:

Syntax:

Select col1, col2,……. From table1, table2 where table2.column(+) = table1.column;

Eg:

Select emp.ename, dept.dname from emp, dept where dept.deptno(+)=emp.deptno;

2) RIGHT OUTER JOINS:

Syntax:

Select col1,col2,……. From table1, table2 where table2.column=table1.column(+);

Eg:

Select emp.ename, dept.dname from emp, dept where dept.deptno=emp.deptno(+);

SUBQUERIES

A query which contains another query that query is called subqueries.

Syntax:

Select <select _list> from table1 where <column_name> operator ( select select_list from table2);

Note:

1) The subquery (inner query) executes once before the main query.

2) The result of the sub query is used by the main query (outer query).

Egs:

1) W.A.Q. to display the employee information that who draw the highest salary of the employee table.

Select * from emp where sal = ( select max(sal) from emp);

2) W.A.Q. to display the employee information that who draws the second highest salary of the employee table.

Select * from emp where sal = ( select max(sal) from emp

Where sal<(select max(sal) from emp));

3) W.A.Q. to display the employee information that who working in accounting department.

Select * from emp where deptno=(select deptno from where dname=’ACCOUNTING’);

4) W.A.Q. to display the employee information that who have the same salary as ford.

Select * from emp where sal = ( select sal from emp where ename=’ford’);

5) W.A.Q. to display the employee info that who have the minimum salaries in each department.

Select * from emp where sal in(select min(sal) from emp group by deptno);

6) W.A.Q. to display the deptno that which department contains max no. of employees.

Select * from (

Select * from(

Select deptno, count(*) as cnt from emp group by deptno) order by cnt desc) where rownum=1;

CORRELATED SUB QUERIES:

Oracle performs a correlated subquery when the subquery references a column from a table referred to the parent statement. The parent statement can be a select, update or delecte statement.

Correlated subqueries are used for row by row processing. Each sub query is executed once for every row of the outer query.

Syntax:

Select <select_list> from table1 outer where column operator(select select_list from table2 where column=outer.column);

Egs:

1) W.A.Q. to display the maximum salaries of employee records in each department.

Select * from emp e where sal=(select max(sal) from emp where deptno=e.deptno);

2) W.A.Q. to display the employees whose salary is above of average salary in their department.

Select * from emp e where sal>(select avg(sal) from emp where deptno=e.deptno);

3) W.A.Q. to display the employee table records in ascending order as per dname.

Select * from emp e order by (select dname from dept d where e.deptno=d.deptno);

CORRELATED UPDATE:

Use a correlated sub query to update rows in one table based on rows from an other table.

Syntax:

Update table1 alias1 set column=(select col_name from table2 alias2 where alias1.col=alias2.col);

Eg:

Alter table emp add(dname varchar2(20));

Update emp e set dname=(select dname from dept where deptno=e.deptno);

ROWID

ROWID is a id which represents the address the of the record. Every row has different row id’s.

Syntax:

Select rowed from table_name;

Eg:

1) W.A.Q. to display the rowid of employee.

Select rowid from emp;

2) W.A.Q. to display the employee record of the given address.

Select * from emp where rowid=’aaahdtaabaaamusaaa’;

3) W.A.Q. to delete the duplicate records of the table

Delete from table_name where rowid not in (select min(rowid) from table_name group by col1,col2,……);

Eg:

Delete from student where rowid not in(select min(rowid) from student group by sno, sna, course);

ROWNUM

ROWNUM is a number which represents the position of the record.

Egs:

Select rownum from emp;

1) W.A.Q. to retrieve the first five records from the emp table.

Select * from emp where rownum<=5;

2) W.A.Q. to display the department info that which department have maximum employees.

Select * from(select * from(select deptno, count(*) cnt from emp group by deptno) order by cnt desc) where rownum=1;

INDEXES

An INDEX is an optional structure which can be designed to access the data much faster.

There are 2 types of indexes.

1) Simple index

2) Composite index

1) SIMPLE INDEX

An index which can be applied only one column that index is called simple index.

Syntax:

Create index idx_name on table(col_name);

Eg:

Create index idx_sno on student(sno);

2) COMPOSITE INDEX

An index which can be applied more than one column that index is called composite index.

Syntax:

Create index idx_name on table(col1,col2,…….);

Eg:

Create index idx_stu on student(sno,sna,course);

UNIQUE INDEX:

If we create the index as unique we can not insert the duplicate values in indexed column.

Syntax:

Create unique index idx_name on table(col1[,col2,…..]);

Eg:

Create unique index idx_stu on student (sno,sna,course);

TO RETRIEVE THE INDEXES FROM THE DATABASE:

Select index_name from user_indexes;

DROP THE INDEX:

Drop index index_name;

NOTE:

If we define the column with primary key constraint, by default it maintains a unique indexed key on that column.

SEQUENCES

A sequence is a database item which generates the sequential unique numbers automatically.

Is typically used to create primary key value and speeds up the efficiency of accessing sequence values when cached in memory.

If we create the sequence using cache accessing the sequence values fastly.

Syntax:

Create sequence sequence_name

[ start with value]

[ increment by value]

[minvalue value | nominvalue ]

[maxvalue value | nomaxvalue]

[cycle | nocycle]

[cache value | nocache ];

Eg:

Create sequence seq_sno start with 1 increment by 1 maxvalue 100 nocycle nocache;

In every sequence two types of pseudo columns are available.

1) NEXTVAL

2) CURRVAL

1) Nextval returns the next sequence number.

2) Currval returns the current sequence number.

Eg:

Select seq_sno.nextval from dual;

Select seq_sno.currval from dual;

INSERTING THE SEQUENCE VALUES INTO A TABLE:

Insert into table_name values (seq_name.nextval,&col2,&col3,……);

Eg:

Insert into student values(seq_sno.nextval, ‘&sna’,’&course’);

TO VIEW THE SEQUENCE NAMES LIST:

Select sequence_name from user_sequences;

DROP THE SEQUENCE:

Drop sequence seq_name;

Eg:

Drop sequence seq_sno;

VIEWS

Logically represents subsets of data from one or more tables.

By using views we can access the original table data and also provide the security to original table.

If we modify the view data automatically the corresponding original table data will be changed. By using views we can hide the original tables data.

TYPES OF VIEWS:

1) SIMPLE VIEWS

2) COMPLEX VIEWS

1) SIMPLE VIEWS:

If we select the one table columns in view that view is called as simple view.

2) COMPLEX VIEWS:

If we select the multiple tables columns in view that view is called as complex view.

Syntax:

CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW vw_name AS <SELECT query> [WITH CHECK OPTION/READ ONLY];

NOTE:

If we create the view with FORCE option the view will be created whether the table does not exist.

If we create the view with OR PRPLACE option if view will be already existed the present view will be existed in that previous view.

If we create the view with CHECK option we must create the view with where condition. If we insert the data in that view the view checks the current new record as per given where condition.

If we create the view with READ only option that view does not allowed data manipulations like insert, update and delete.

If we create the view with using JOINS the view automatically treated as read only, we can’t apply the data manipulations on that view.

RULES FOR PERFORMING DML OPERATION ON VIEW:

You can’t modify data in a view if it contains:

1) GROUP FUNCTIONS

2) THE DISTINCT KEYWORD

3) A GROUP BY CLAUSE

4) ROWNUM

5) JOINS

Eg:

SIMPLE VIEWS:

1) CREATE OR REPLACE FORCE VIEW VW_STU AS SELECT * FROM STUDENT;

2) CREATE OR REPLACE VIEW VW_EMP AS SELECT EMPNO, ENAME FROM EMP;

3) CREATE VIEW VW_EMP AS SELECT * FROM EMP;

4) CREATE OR REPLACE VIEW VW_EMP AS SELECT EMPNO, ENAME, JOB FROM EMP WHERE JOB=’MANAGER’ WITH CHECK OPTION;

5) CREATE OR REPLACE VIEW VW_EMP AS SELECT EMPNO, ENAME, JOB FROM EMP WITH READ ONLY;

COMPLEX VIEW OR INLINE VIEW:

6) CREATE OR REPLACE VIEW VW_EMP AS SELECT EMPNO, ENAME, DEPT.DEPTNO, DNAME FROM EMP, DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO

TO DISPLAY THE VIEWS LIST IN THE CURRENT DATABASE:

Select view_name from user_views;

DROP THE VIEW:

DROP view view_name;

Eg:

Drop view vw_emp;

SNAPSHOT

By using SNAPSHOT we can access the table data as like view and also apply the data manipulations as like table.

But the difference between view and snapshot is in view if we change the data automatically the corresponding the data will be modified in the original table. But in snapshot whenever we refresh the snapshot then the data will be modified.

SNAPSHOT WILL BE REFRESHED IN 2 TYPES:

1) BY MANUALLY

2) BY DATE WISE

Syntax:

CREATE SNAPSHOT <snapshot_name> REFRESH COMPLETE WITH [ROWID/PRIMARY KEY] [ START WITH SYSDATE NEXT SYSDATE+NO_OF_DAYS] [FOR UPDATE] AS <select query>;

NOTE:

If we create the snapshot on table that snapshot will be treated as read only view. That read only view we can’t apply the data manipulations.

If we want to apply the data manipulations on that snapshot then we create the snapshot using for update.

If the table contains primary key then we can create the snapshot using primary key other wise we can create the snapshot with rowid.

Egs:

1) CREATE SNAPSHOT SS_STU REFRESH COMPLETE WITH ROWID AS SELECT * FROM SUTDENT;

2) CREATE SNAPSHOST SS_STU REFRESH COMPLETE WITH PRIMARY KEY AS SELECT * FROM STUDENT;

3) CREATE SNAPSHOT SS_STU REFRESH COMPLETE WITH ROWID FOR UPDATE AS SELECT * FROM STUDENT;

4) CREATE SNAPSHOT SS_STU REFRESH COMPLETE WITH ROWID START WITH SYSDATE NEXT SYSDATE+7 FOR UPDATE AS SELECT * FROM STUDENT;

SNAPSHOT REFRESH:

1) If we create the ordinary snapshot we can refresh the manual.

Execute dbms_snapshot.refresh(‘snapshot_name’);

Eg:

Execute dbms_snapshot.refresh(‘ss_stu’);

2) if we create the snapshot with sysdate thre is not necessary to refresh the snapshot. The snapshot data automatically refreshed with the given date.

DROP THE SNAPSHOT:

Drop snapshot <snapshot_name>;

Eg:

Drop snapshot ss_stu;

SECURITY MANAGEMENT COMMANDS (DCL)

DATA BASE SECURITY

Data base security can be classified into 2 categories.

1) SYSTEM SECURITY

System security represents the control the accessibility on database object to another users.

2) DATA SECURITY

Data security represents the control the accessibility on objects data to another user.

PRIVILEGES

PRIVILEGES are nothing but permissions, which are used to provide the security to the data base objects and also provide the security to the data of the objects.

PRIVILEGES ARE 2 TYPES

1) SYSTEM PRIVILEGES:

Which are used to provide the permissions on objects.

2) OBJECT PRIVILEGES:

Which are used to provide the data manipulation permissions on objects to another users.

1) SYSTEM PRIVILEGES:

More than 100 privileges are available.

User for

v CREATING NEW USER

v REMOVING USRES

v REMOVING TABLES

v BACKUP OF TABLES.

EGS:

CREATE USER:

GRANTEE CAN CREATE OTHER ORACLE USERS.

DROP USER:

CRANTEE CAN DROP THE ANOTHER USER.

DROP ANY TABLE:

GRANTEE CAN DROP A TABLE IN ANY SCHEMA.

(Schema is nothing but a user contains the collection of tables, views, and sequences)

SELECT ANY TABLE:

GRANTEE CAN QUERY TABLES [,VIEWS, OR SNAPSHOTS] IN ANY SCHEMA.

CREATE ANY TABLE:

GRANTEE CAN CREATE TABLES IN ANY SCHEMA.

SHOW USER:

SHOW USER COMMAND DISPLAYS THE NAME OF CURRENT USER_NAME;

EG:

Show user;

CREATE A NEW USER:

CREATE USER user_name IDENTIFIED BY pass_word;

Eg:

Create user ggr identified by govind;

CONNECT TO USER:

CONNECT USER_NAME/PASSWORD;

EG:

Connect ggr/govind;

Note:

If we want to connect particular user we must grant the connect privilege to the user.

GRANT:

By using grant command we can grant the privileges to particular user and also to all users and also grant the privileges to particular role.

If we provide the privileges to another user we must connect to DBA.

1) GRANT THE SYSTEM PREVILEGES TO PARTICULAR USER:

Syntax:

GRANT <previlige> TO <user_name> [WITH ADMIN OPTION];

(with admin option provides to grant the privilege to another user that which can be taken from another user).

SYSTEM PRIVILEGES ALLOW YOU

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

CREATE SESSION TO CONNECT TO DATABASE

CREATE SEQUENCE TO CREATE A SEQUENCE

CREATE SYNONYM TO CREATE A SYNONYM.

CREATE TABLE TO CREATE A TABLE

CREATE ANY TABLE TO CREATE TABLE IN ANY SCHEMA.

DROP TABLE TO DROP A TABLE.

DROP ANY TABLE TO DROP A TABLE FROM ANY SCHEMA.

CREATE PROCEDURE TO CREATE A STORED PROCEDURE.

EXECUTE ANY PROCEDURE TO EXECUTE A PROCEDURE I ANY SCHEMA

CREATE USER TO CREATE A USER

CREATE VIEW TO CREATE A VIEW.

EG:

1) GRANT create session TO ggr;

2) GRANT create table TO ggr;

3) GRANT create table TO ggr WITH ADMIN OPTION;

2) GRANT THE SYSTEM PRIVILEGES TO ALL USERS:

Syntax:

GRANT <privilege> TO PUBLIC;

Eg:

GRANT create table TO PUBLIC;

(It provides the create table privilege to all users)

3) GRANT THE SYSTEM PRIVILEGE TO PARTICULAR ROLE:

Syntax:

GRANT <privilege> TO <role>;

Eg:

GREANT create table TO manager;

(here manager is a role which contains the privileges)

REVOKE:

By using REVOKE command we can cancel the privileges to particular user.

Syntax:

REVOKE <privilege> FROM <user_name>;

Eg:

REVOKE create table FROM ggr;

OBJECT PRIVILEGES:

An object privilege allows a user to perform certain actions on database objects, such as executing DML operations on tables.

OBJECT PRIVILEGE ALLOW USER

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

SELECT TO SELECT THE OBJECT DATA

INSERT TO INSERT THE DATA INTO OBJECTS

UPDATE TO MODIFY THE DATA OF OBJECTS

DELETE TO DELETE THE RECORDS IN OBJECTS

EXECUTE TO EXECUTE A SORED PROCEDURE

Syntax:

GRANT <object_priveges> ON <object_name> TO <user_name> [WITH GRANT OPTION];

(WITH GRANT OPTION provides that object privilege to another user)

Eg:

Grant select, insert on ggr.student to scott;

Grant select on student to scott with grant option;

(provides the select permission on student table to remaining users)

GRANT UPDATE ON SPECIFIC COLUMNS OF PARTICULAR TABLE TO ANOTHER USER:

Syntax:

GRANT UPDATE(COL1,COL2) ON <table_name> TO <user_name>;

Eg:

Grant update(sna,course) ON ggr.student TO scott;

GRANT ALL OBJECT PRIVILEGES TO USER:

Syntax:

GRANT ALL ON <object_name> TO <User_name> [WITH GRANT OPTION];

Eg:

Grant all on ggr.student to scott;

REVOKE THE OBJECT PRIVILEGES ON OBJECT FROM THE USER:

Syntax:

REVOKE <object_privilege> ON <object_name> FROM <user_name>;

Eg:

Revoke select on ggr.student from scott;

REVOKE THE ALL OBJECT RPIVILEGES ON OBJECT FROM USER:

Syntax:

REVOKE ALL ON <object_name> FROM <user_name>;

Eg:

Revoke all on ggr.student from scott;

ROLES

A ROLL is a group of privileges that we can assign that role to another user and also assigns to another role.

¨ Rather than assigning privileges one at a time to directly to a user, we can create a role, assign privileges to that role, and then grant that role to multiple users and roles.

¨ When we add or delete a privilege from a role, all users and roles assigned that role automatically receive or lose that privilege.

¨ We can assign multiple roles to a user or role.

¨ We can assign a password to a role.

CREATE ROLES:

To create a ROLE we must have create ROLE system privilege and also have create user system privilege with admin option to user.

Grant create role to ggr;

Grant create user to ggr with admin option;

Syntax:

CREATE ROLE <role_name> [IDENTIFIED BY <pass_word>];

Eg:

Create role stu_obj_role;

Create role ggr_sys_role;

Create role manager identified by govind;

ASSIGNING THE SYSTEM PRIVILEGES TO ROLE:

Syntax:

GRANT <sys_privileges> TO <role_name>;

Eg:

Grant create table, create drop TO ggr_sys_role;

ASSIGNING THE OBJECT PRIVILIGES ON OBJECT TO ROLE:

Syntax:

GRANT <obj_privileges> ON <table_name> TO <role_name>;

Eg:

Grant select, insert on ggr.student to stu_obj_role;

ASSIGNING MULTIPLE ROLES TO MAIN_ROLE:

Syntax:

GRANT ROLE1,ROLE2,…. TO <role_name>;

Eg:

Grant ggr_sys_role, stu_obj_role to manager;

GRANTING THE ROLE TO USER:

Syntax:

GRANT <role_name> TO <user_name>;

Eg:

Grant manager to scott;

TO VIEW THE SYSTEM DEFAULT ROLES WITH PRIVILEGES:

SELECT * FROM ROLE_SYS_RRIVS;

TO VIEW THE OBJECT PRIVILIGES OF PARTICULAR ROLE:

SELECT * FROM ROLE_TAB_PRIVS WHERE ROLE=’ROLE_NAME’;

REVOKING THE ROLES:

If we REVOKE the roles from the user we must connect owner of the role.

Syntax:

REVOKE <role_name> FROM <user_name>;

Eg:

Connect ggr/govind; (owner of the role);

Revoke manager from scott;

REVOKING THE OBJECT PRIVILEGES FROM A TABLE:

Syntax:

REVOKE ALL ON <OBJECT_NAME> FROM <ROLE_NAME>;

Eg:

Revoke all on ggr.student from stu_obj_role;

DROPPING A ROLE:

Syntax:

DROP ROLE <role_name>;

Eg:

Drop role stu_obj_role;

Drop role ggr_sys_role;

Drop role manager;

TO VIEW ALL USER_NAME FROM CURRENT DATABASE:

Select username from all_users;

TO DROP THE USER:

Syntax:

DROP USER user_name[cascade];

Eg:

Drop user ggr; // deletes the empty usre

Drop user ggr cascade; // deletes user with objects;

SYNONYMS

SYNONYMS is an alias for table, view, sequence or a program unit. Synonym is not actually an object itself, but instead is a direct reference to an object.

Synonyms are used for

¨ Mask the original name and the owner of an object

¨ Provide public access to an object

¨ Provide location transparency for tables, views.

A synonym can be a private or public. An individual user can create a private synonym which is available only to that user. Database administrators most often create public synonyms that makes the object available for general user by any database user.

Syntax:

CREATE [PUBLIC] SYNONYM syn_name FOR object;

Eg:

Create synonym stu_syn for student;

Create public synonym stu_syn for scott.student;

If we create the synonym as public that synonym can access all users. If you omit this option, the synonym is private and is accessible only with in its user.

PRIVATE SYNONYM:

Create synonym syn_stu for student;

PUBLIC SYNONYM:

Create public synonym syn_student for student;

TO VIEW ALL SYNONYMS FROM THE CURRENT USER:

Select synonym_name from user_synonyms;

TO DROP THE SYNONYM:

DROP [PUBLIC] SYNONYM synonym_name;

Eg:

Drop synonym syn_stu;

Drop public synonym syn_student;

OBJECTS (TYPES)

Types, which are used to implement the user defined data types.

User defined types are also called object types.

In oracle two types of object types are available.

1) SIMPLE TYPES

2) COMPOSITE TYPES

1) SIMPLE TYPES:

Syntax:

CREATE [OR REPLACE] TYPE type_name AS OBJECT (col1 datatype, col2 datatype,….);

Eg:

Create or replace type addr_type as object(street varchar2(20),doorno varchar2(16), city varchar2(15), state varchar2(15), country varchar2(10), pincode number(6));

To view the user types list:

Select * from user_types;

To view the structure of the user_type:

Describe user_type;

Eg:

Describe addr_type;

Using the user defined types in table creation:

CREATE TBLE table_name(col1 datatype, col2 datatype, col3 user_datatype);

Eg:

Create table student (sno number(3), sna varchar2(10), course varchar2(10), address addr_type);

Insert the data into user defined columns of the table:

INSERT INTO table_name VALUES (EXP1, EXP2, USER_TYPE(VALUES));

Eg:

Insert into student values(1,’anil’,’oracle’,addr_type(‘7/2 cm nagar’,’4-4-175’,’guntur’,’ap’,’india’,522007));

To view the rows from userdefined column table:

SELECT COL1, COL2, alias.COL3.COL1, alias.COL3.COL2 FROM table_name alias;

Eg:

Select s.sno, s.sna, s.course, s.address.street, s.address.doorno, s.address.city from student s;

2) COMPOSITE TYPES:

Once a simple type has been created, it can be used to create other user-defined types.

A type can be created by using other types is known as composite types.

Syntax:

CREATE [OR REPLACE] TYPE type_name AS OBJECT (COL1 DATATYPE, COL2 DATATYPE, COL3 USER_TYPE);

Eg:

Create or replace type stu_type as object( sna varchar2(10), address.addr_type);

Object tables:

A table which was created by using user defined type that table is called object tablel

Note:

An object table based on simple type, not containing composite type.

Syntax:

CREATE TABLE table_name OF user_type;

Eg:

Create table stu_addr of addr_type;

Data manipulations on object table:

As like ordinary table we can insert the rows into object tables and also modify and delete values in object table as like ordinary table.

Eg:

Insert into stu_addr values(‘7/2 cmnagar’,’4-4-175’, ‘guntur’, ‘ap’, ‘india’, 522007);

Update stu_addr set city=’gnt’ where street=’7/2 cmnagar’;

Delete from stu_addr where city=’gnt’;

VARYING ARRAYS

By using VARRYS we can insert the same type of no. of values into a single variable.

VARRYS is nothing but arrays which stores same type of multiple values.

Creation of varray:

Syntax:

CREATE [OR REPLACE] TYPE type_name AS VARRAY(size) OF datatype;

Eg:

Create or replace type courses as varray(5) of varchar2(20);

Create a table using varray_type:

Syntax:

CREATE TABLE table_name(col_name datatype, col_name user_type);

Eg:

Create table fac_info(fname varchar2(20), tcourses courses);

Insert the data into a varrays column:

INSERT INTO table_name VALUES(VAL1, VAL2, USER_TYPE(VAL1, VAL2, VAL3));

Eg:

Insert into fac_info values(‘anil’,courses(‘c++’,’da’,’java’))

Select data from varray columns:

In SQL:

SELECT * FROM FAC_INFO;

In PL/SQL:

DECLARE

CURSOR FACCUR IS SELECT * FROM FAC_INFO;

BEGIN

FOR FACREC IN FACCUR

LOOP

DBMS_OUTPUT.PUT(FACREC.FNAME||’ ‘);

DBMS_OUTPUT.PUT(‘TEACHING COURSES (‘ );

FOR I IN 1..FACREC.TCOURSES.COUNT

LOOP

DBMS_OUTPUT.PUT(FACREC.TCOURSES(I)||’ ‘);

END LOOP;

DBMS_OUTPUT.PUT_LINE(‘)’);

END LOOP;

END;

LARGE OBJECTS (LOBS)

Long and long raw types are stores the value upto 2gb. If we store the data in columns above 2gb then we can use LOBS.

Large objects:

LOBS, which are used to store large unstructured data such as text, graphic images, films and sound wave forms.

LOBS USED TO

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

CLOB STORE THE TEXT

BLOB STORE THE PHOTOS(IMAGES)

BFILE STORE THE MOVIES

LOBS also allow random, piece wise access to the data.
Lobs is made up of two distinct parts – value and locator
Value is the actual data that will be stored
Locater is an indicator that specifies the location of the object in the database
A column based on lob type is called as lob column
The lob column stores the locator of the object
The lobs can be stored inside the db (internal lob or inline) or outside the db (external lob or off-line) ie., in the os files.

Bfile:

Used to store large binary objects in os files. If its size is above 4gb it stored at outside database, if size is below 4gb it stored in database.

Blob:

To store large binary objects inside the db (up to 4gb).

Clob:

Clob is similar to long datatype, except that clob are used to store large blocks of single – byte character data in the database.

Nclob:

Stores the multibyte characterset upto 4gb.

Creating table with LOB columns:

Syntax:

CREATE TABLE table_name(cole_name datatype, col_name datatype, col_name BFILE, col_name CLOB);

Eg:

Create table courses (cname varchar2(20), concepts clob, cmaterial bfile);

Inserting the values in lobs:

To insert value in the bfile, the function bfilename is used. It takes the OS path of the directory and the name of the file.

Eg:

Insert into courses values(‘oracle’, ‘structure query language and procedure language using SQL language’, bfilename(‘c:\orcl_matrl’,’oracle9i.doc’));

Insert into courses values(‘c++’,empty_clob(), null);

Displaying data from lobs:

Data from lobs can’t be displayed, except for clob by using select statement.

Eg:

Select cname,concepts, from courses;

PL/SQL

PROCEDURAL LANGUAGE USING SQL

Structure of PL/SQL program:

DECLARE

VARIABLE DECLARATIONS;

BEGIN

----- STATEMENTS;

[ EXCEPTION HANDLING]

END;

Data types:

Char

Varchar2(size), string (size)

Number(size), int, integer

Real, float

Date,

Boolean ( true or false)

User defined types:

Table.col%type

Table%rowtype

Steps for executing the PL/SQL program:

1) Create a new file and type the program

Edit filename.sql

2) Save the file name

By pressing ---- ALT + F + S

3) Execute the program

@filename.sql

4) set serteroutput ON;

To view the program output

Set server output ON

Eg:

Step – 1: edit sample.sql

Step – 2: type the below program

W.A.PL/SQL block to print the message ‘welcome to PL/SQL’

BEGIN

DBMS_OUTPUT.PUT_LINE(‘WELCOME TO PL/SQL’);

END;

Step – 3: save and exit

Step – 4: sql> @sample.sql

Set serveroutput ON.

W.A.PL/SQL. Block to assign the values to A, B and print the values.

DECLARE

A INTEGER;

B INTEGER;

BEGIN

A:=10;

B:=20;

DBMS_OUTPUT.PUT_LINE(A);

DBMS_OUTPUT.PUT_LINE(B);

END;

W.A.PL/SQL block that accept two values and add that two values.

DECLARE

A INTEGER;

B INTEGER;

C INTEGER;

BEGIN

A:=&A;

B:=%B;

C:=A+B;

DBMS_OUTPUT.PUT_LINE(A);

DBMS_OUTPUT.PUT_LINE(B);

DBMS_OUTPUT.PUT_LINE(C);

END;

W.A.PL/SQL block that accept the student no, name and course from keyboard and display the values.

DELCARE

SNO NUMBER(3);

SNA VARCHAR2(20);

COURSE VARCHAR2(10);

BEGIN

SNO:=&SNO;

SNA:=’&SNA’;

COURSE:=’&COURSE’;

DBMS_OUTPUT.PUT_LINE(SNO);

DBMS_OUTPUT.PUT_LINE(SNA);

DBMS_OUTPUT.PUT_LINE(COURSE);

END;

DECLARE

SNO INTEGER;

SNA STRING(20);

COURSE STRING(10);

BEGIN

SNO:=&SNO;

SNA:=’&SNA’;

COURSE:=’&COURSE’;

DBMS_OUTPUT.PUT_LINE(‘STUDENT NO:’||SNO);

DBMS_OUTPUT.PUT_LINE(‘STUDENT NAME:’||SNA);

DBMS_OUTPUT.PUT_LINE(‘COURSE:’||COURSE);

END;

DECLARE

SNO INT;

SNA CHAR(20);

COURSE CHAR(10);

BEGIN

SNO:=&SNO;

SNA:=’&SNA’;

COURSE:=’&COURSE’;

DBMS_OUTPUT.PUT_LINE(SNO || ‘ ‘ || SNA || ‘ ‘ || COURSE);

END;

W.A.PL/SQL block that accept student no, name and course and insert that values into a table

DELCARE

SNO NUMBER(3);

SNA VARCHAR2(20);

COURSE VARCHAR2(10);

BEGIN

SNO:=&SNO;

SNA:=’&SNA’;

COURSE:=’&COURSE’;

INSERT INTO STUDENT VALUES(SNO, SAN, COURSE);

DBMS_OUTPUT.PUT_LINE(‘ 1 ROW INSERTED’);

END;

W.A.PL/SQL block that accept the employee No, and display the employees name, job and salary.

DECLARE

ENO INTEGER;

ENA EMP.ENAME%TYPE;

JOB1 EMP.JOB%TYPE;

SALARY EMP.SAL%TYPE;

BEGIN

ENO:=&ENO;

SELECT ENAME, JOB, SAL INTO ENA, JOB1, SALARY FROM EMP WHERE EMPNO=ENO;

DBMS_OUTPUT.PUT_LINE(ENA || ‘ ‘|| JOB1|| ‘ ‘|| SALARY);

END;

W.A.PL/SQL block that accept the employee No, and display the employee record.

DECLARE

ENO INTEGER;

EMP_REC EMP%ROWTYPE;

BEGIN

ENO:=&ENO;

SELECT * INTO EMP_REC FROM EMP WHERE EMPNO=ENO;

DBMS_OUTPUT.PUT_LINE(EMP_REC.EMPNO|| ‘ ‘|| EMP_REC.ENAME|| ‘ ‘||EMP_REC.JOB||’ ‘||EMP_REC.SAL);

END;

CONTROLLED STRUCTURES

1) CONDITIONAL CONTROL

2) INTERATIVE CONTROL

3) SEQUENTIAL CONTROL

1) CONDITIONAL CONTROL

a) IF STATEMENT

b) IF THEN ELSE STATEMENT

c) IF THEN ELSEIF STATEMENT

d) NESTED IF STATEMENT

2) ITERATIVE CONTROL

a) SIMPLE LOOP

b) WHILE LOOP

c) FOR LOOP

3) SEQUENTIAL CONTROL

a) GOTO STATEMENTS

1) CONDITIONAL CONTROL

A) IF… THEN STATEMENT

IF ( CONDITION) THEN

--- STATEMENTS

END IF;

W.A.PL/SQL block to accept the account no, and withdraw amount from keyboard and draw the amount from balance whenever the balance is above 500.

DECLARE

ANO INTEGER;

WITHDRAW INTEGER;

BALANCE ACCOUNT.BAL%TYPE;

AMOUNT INTEGER;

BEGIN

ANO:=&ANO;

WITHDRAW:=&WITHDRAW;

SELECT BAL INTO BALANCE FROM ACCOUNT WHERE ACNO=ANO;

AMOUNT := BAL – WITHDRAW;

IF ( AMOUNT > 500 ) THEN

UPDATE ACCOUNT SET BAL=BAL-WITHDRAW WHERE ACNO=ANO;

DBMS_OUTPUT.PUT_LINE(‘WITH DRAW SUCCESSFULLY’);

RETURN;

END IF;

DBMS_OUTPUT.PUT_LINE(‘WITH DRAW NOT POSSIBLE’);

END;

IF …. THEN… ELSE

IF (CONDITION) THEN

STATEMENTS;

ELSE

STATEMENTS;

END IF;

W.A.P to read A, B values and find the biggest number in given two values.

DECLARE

A INTEGER;

B INTEGER;

BEGIN

A:=&A;

B:=&B;

IF (A>B) THEN

DBMS_OUTPUT.PUT_LINE(‘ A IS BIG ‘);

ELSE

DBMS_OUTPUT.PUT_LINE(‘ B IS BIG ‘);

END IF;

END;

IF THEN ELSIF STATEMENT

IF (CONDITION) THEN

STATEMENTS;

ELSIF (CONDITION) THEN

STATEMENTS;

ELSE

STATEMENTS;

END IF;

W.A.PL/SQL block to accept the employee number and calculate the commission based on following conditions. If salary is 3000 or above then commission is 20% of salary else if salary is 2000 or above then commission is 10% of salary other wise commission is 5% of salary (using ELSIF)

DELCARE

ENO INTEGER;

SALARY EMP.SAL%TYPE;

COMMISSION EMP.COMM%TYPE;

BEGIN

ENO:=&ENO;

SELECT SAL INTO SALARY FROM EMP WHERE EMPNO=ENO;

IF(SALARY >=3000) THEN

COMMISSION := SALARY*20/100;

ELSIF (SALARY>=2000 ) THEN

COMMISSION :=SALARY*10/100;

ELSE

COMMISSION := SALARY*5/100;

END IF;

UPDATE EMP SET COMM=COMMISSION WHERE EMPNO=ENO;

COMMIT;

END;

NESTED IF

IF (CONDITION) THEN

STATEMENTS;

ELSE

STATEMENTS;

END IF;

ELSE

STATEMENTS;

END IF;

W.A.PL/SQL block to accept the student number and calculate the student total, avg and grade. Grade is based on following conditions.

If student get<35 in any subject No grade other wise set the grade as per average.

If student get average 80 or above then grade is’A’, if student get average above 60 then grade is ‘B’, if student get average above 50 then grade is ‘C’ otherwise grade is ‘D’.

DECLARE

NO INTEGER;

STU_REC STUDENT%ROWTYPE;

BEGIN

NO:=&NO;

SELECT * INTO STU_REC FROM STUDENT WHERE SNO=NO;

STU_REC.TOTAL := STU_REC.S1+STU_REC.S2+STU_REC.S3;

STU_REC.AVG := STU_REC.TOTAL/3;

IF(STU_REC.S1>=35 AND STU_REC.S2 >=35 AND STU_REC.S3>=35 ) THEN

IF(STU_REC.AVG>=80) THEN

STU_REC.GRADE :=’A’;

ELSIF (STU_REC.AVG>=60) THEN

STU_REC.GRADE=’B’;

ELSE (STU_REC.AVG>=50 ) THEN

STU_REC.GRADE := ‘C’;

ELSE

STU_REC.GRADE :=’D’;

END IF;

ELSE

STU_REC.GRADE :=’ – ‘;

END IF;

UPDATE STUDENT SET TOTAL=STU_REC.TOTAL, AVG=STU_REC.AVG, GRADE=STU_REC.GRADE WHERE SNO=NO;

DBMS_OUTPUT.PUT_LINE(‘RECORD UPDATED SUCCESSFULLY’);

END;

INTERATIVE CONTROLS

SIMPLE LOOP:

LOOP

-- STATEMETNS;

EXIT WHEN <CONDITION>;

END LOOP;

W.A.PL/SQL block to print the numbers from 1 to N using simple loop.

DECLARE

I INTEGER;

N INTEGER;

BEGIN

N:=&N;

I :=1;

LOOP

DBMS_OUTPUT.PUT_LINE(I);

I := I+1;

EXIT WHEN I>N;

END LOOP;

END;

W.A.PL/SQL block to print the multiplication table of the given number upto 10 steps using simple loop.

DECLARE

N INTEGER;

I INTEGER;

BEGIN

N:=&N;

I :=1;

LOOP

DBMS_OUTPUT.PUT_LINE(N || ‘ X ‘ || I || ‘ = ‘|| N*I);

I :=I+1;

EXIT WHEN I>10;

END LOOP;

END;

WHILE LOOP:

WHILE ( CONDITION )

LOOP

---- STATEMENTS;

END LOOP;

W.A.PL/SQL block to print the numbers from 1 to N and print the numbers from N to 1 using while.

DECLARE

N INTEGER;

I INTEGER;

BEGIN

N:=&N;

I :=1;

WHILE ( I <= N)

LOOP

DBMS_OUTPUT.PUT_LINE(I);

I :=I+1

END LOOP;

WHILE (N>0)

LOOP

DBMS_OUTPUT.PUT_LINE(N);

N :=N+1

END LOOP;

END;

W.A.PL/SQL block to check the given number is Armstrong or not using while loop

DECLARE

N INTEGER;

A INTEGER;

S INTEGER := 0;

M INTEGER;

BEGIN

N:=&N;

M:=N;

WHILE (N>0)

LOOP

A :=MOD(N,10);

S :=S+POWER(A,3);

N :=TRUNC(N/10);

END LOOP;

IF (S = M) THEN

DBMS_OUTPUT.PUT_LINE(M||’ IS ARMSTRONG’);

ELSE

DBMS_OUTPUT.PUT_LINE(M||’ IS NOT ARMSTRONG’);

END IF;

END;

FOR LOOP:

FOR variable IN [REVERSE] START.. END

LOOP

--- STATEMENTS;

END LOOP;

W.A.PL/SQL block to read N and print the numbers from 1 to N and N to 1 using for loop

DECLARE

N INTEGER;

BEGIN

N:=&N;

FOR I IN 1..N

LOOP

DBMS_OUTPUT.PUT_LINE(I);

END LOOP;

FOR I IN REVERSE 1..N

LOOP

DBMS_OUTPUT.PUT_LINE(I);

END LOOP;

END;

W.A.PL/SQL block to read the string and print that string in reverse using for loop

DECLARE

ST VARCHAR2(10);

REVST VARCHAR2(20);

LEN INTEGER;

BEGIN

ST :=’&ST’;

LEN :=LENGTH(ST);

FOR I IN REVERSE 1..LEN

LOOP

REVST :=REVST || SUBSTR(ST,I,1);

END LOOP;

DBMS_OUTPUT.PUT_LINE(‘ REVERSE IS : ‘||REVST);

END;

SEQUENTIAL CONTROL:

GOTO STATEMENT:

<<LABEL>>

--- STATEMENTS;

IF (CONDITION) THEN

GOTO LABEL;

END IF;

W.A.PL/SQL block to read N value and print the numbers from 1 to N and also calculate the sum using goto statement.

DECLARE

N INTEGER;

I INTEGER;

SUM INTEGER := 0;

BEGIN

N :=&N;

I :=1;

<<PRINT>>

DBMS_OUTPUT.PUT_LINE(I);

SUM :=SUM+I;

I :=I+1;

IF(I<=N) THEN

GOTO PRINT;

END IF;

DBMS_OUTPUT.PUT_LINE(‘SUM IS :’||SUM);

END;

EXCEPTION HANDLING

If there is an errors may be occurred in our program by using exception handling we can display our own error messages.

Types of exception handling:

A) PREDEFINED EXCEPTION HANDLING

B) USERDEFINED EXCEPTION HANDLING

A) Predefined exception handling:

Predefined exception handlers automatically displays the errors to corresponding errors. By using predefined exception handling we can given our own error messages to the predefined exception handlers.

Predefined exception handlers:

1) DUP_VAL_ON_INDEX:

When we try to insert duplicate values in UNIQUE INDEX constrained column the DUP_VAL_ON_INDEX predefined handler displays the error message duplicate value found.

2) NO_DATA_FOUND:

Raise exception when select statement returns zero rows.

3) LOGIN_DENIED:

Raised when an invalid username/password was used to log on oracle.

4) TOO_MANY_ROWS:

Raised when a select statement returns more than one row.

5) VALUE_ERROR:

Raise error when data is inserted into a column is larger than column size or inserted the data other than column type.

6) ZERO_DIVIDE:

Raise error when value is divided with zero.

Syntax for predefined exception handling:

DECLARE

VARIABLE DECLARATIONS;

BEGIN

STATEMENTS;

EXCEPTION

WHEN <EXCEPTION1> THEN

USER_DEFINED_ACTIONS

WHEN <EXCEPTION2> THEN

USER_DEFINED_ACTIONS

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

WHEN OTHERS THEN

HANDLE_ALL_OTHER_ERRORS;

END;

W.A.PL/SQL block to handle the error for the exception ‘NO DATA FOUND’

DECLARE

NO INTEGER;

EMPREC EMP%ROWTYPE;

BEGIN

NO :=&NO;

SELECT * INTO EMPREC FROM EMP WHERE EMPNO=NO;

DBMS_OUTPUT.PUT_LINE( EMPREC.ENAME || ‘ ‘||EMPREC.JOB||’ ‘||EMPREC.SAL);

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE(‘EMPLOYEE NOT FOUND’);

END;

W.A.PL/SQL block to handle the exception ‘TOO_MANY_ROWS’

DECLARE

DNO INTEGER;

EMPREC EMP%ROWTYPE;

BEGIN

DNO :=&DNO;

SELECT * INTO EMPREC FROM EMP WHERE DEPTNO=DNO;

DBMS_OUTPUT.PUT_LINE( EMPREC.ENAME || ‘ ‘||EMPREC.JOB||’ ‘||EMPREC.SAL);

EXCEPTION

WHEN TOO_MANY_ROWS THEN

DBMS_OUTPUT.PUT_LINE(‘MULTIPLE RECORDS FOUND’);

END;

W.A.PL/SQL block to handle the exception VALUE_ERROR, TOO_MANY_ROWS and others.

DECLARE

NAME VARCHAR2(10);

SALARY NUMBER(6,2);

BEGIN

NAME :=’&NAME’;

SELECT SAL INTO SALARY FROM EMP WHERE ENAME=NAME;

DBMS_OUTPUT.PUT_LINE(‘SALARY :’ || SALARY);

EXCEPTION

WHEN VALUE_ERROR THEN

DBMS_OUTPUT.PUT_LINE(‘VALUE IS TOO LARGE TO INSERT’);

WHEN TOO_MANY_ROWS THEN

DBMS_OUTPUT.PUT_LINE(‘MULTIPLE RECORDS FOUND’);

WHEN OTHER THEN

DBMS_OUTPUT.PUT_LINE(‘RECORD NOT FOUND’);

END;

B) User defined exception handling:

By using user define we can define our own exception handlers and also give error messages to corresponding handlers.

Syntax:

DECLARE

VARIABLE DECLARATIONS;

<EXCEPTION_HANDLER_NAME> EXCEPTION;

BEGIN

---- STATEMENTS;

IF(CONDITION) THEN

RAISE <EXCEPTION_HANDLER_NAME>;

END IF;

EXCEPTION

WHEN <EXCEPTION_HANDLER_NAME> THEN

--- HANDLE_ERROR;

END;

W.A.PL/SQL block to give an error message to the ZERO_DIVIDE using user defined exception handling.

DECLARE

A INTEGER;

B INTEGER;

C INTEGER;

BEGIN

A :=&A;

B :=&B;

IF (B = 0) THEN

RAISE ZERO_DIVIDE_ERROR;

END IF;

C :=A/B;

DBMS_OUTPUT.PUT_LINE(‘DIVISION :’||C);

EXCEPTION

WHEN ZERO_DIVIDE_ERROR THEN

DBMS_OUTPUT.PUT_LINE(‘Error: can not divide with zero’);

END;

Raise_application_error:

RAISE_APPLICATION_ERROR displays the error message as like predefined error.

Syntax:

RAISE_APPLICATION_ERROR(-errorno,’error_msg’);

Note:

Error not must be between 20000 to 20999.

W.A.PL/SQL block to give an example for userdefined exception zero divide error.

DECLARE

A INTEGER;

B INTEGER;

C INTEGER;

ZERO_DIVIDE_ERROR EXCEPTION;

BEGIN

A :=&A;

B :=&B;

IF (B = 0) THEN

RAISE ZERO_DIVIDE_ERROR;

END IF;

C :=A/B;

DBMS_OUTPUT.PUT_LINE(‘DIVISION :’||C);

EXCEPTION

WHEN ZERO_DIVIDE_ERROR THEN

RAISE_APPLICATION_ERROR(-20101,’CAN NOT DIVIDE WITH ZERO’);

END;

W.A.PL/SQL block to accept the employee number and calculate the commission based on employee salary.

If salary >=3000 then commission is 10%OF SAL

If salary >=2000 then commission is 5%OF SAL.

Other wise display the error ‘commission not available’ using ‘user defined exception handling.

DECLARE

ENO INTEGER;

EMPREC EMP%ROWTYPE;

NO_COMMISSION EXCEPTION;

BEGIN

ENO :=&ENO;

SELECT * INTO EMPREC FROM EMP WHERE EMPNO=ENO;

IF(EMPREC.SAL>=3000) THEN

UPDATE EMP SET COMM=SAL*10/100 WHERE EMPNO=ENO;

ELSIF(EMPREC.SAL>=2000) THEN

UPDATE EMP SET COMM=SAL*5/100 WHERE EMPNO=ENO;

ELSE

RAISE NO_COMMISSION;

END IF;

COMMIT;

EXCEPTION

WHEN NO_COMMISSION THEN

RAISE_APPLICATION_ERROR(-20101,’COMMISSION NOT AVAILABLE’);

END;

CURSORS

Oracle engine takes some working area as to calculate the result of sql operations is called as cursors.

Usage of cursors:

By using select statement we can insert the data into a pl/sql variable only one. We can’t insert multiple values into local variable but by using cursors we can pick multiple records and we can apply different conditions in each record.

By using CURSORS we can pick record by record in group of records and apply different operations per each record.

In each cursor there are four types of attributes are available.

THEY ARE:

1) %ISOPEN – Returns true if the cursor is opened otherwise returns false.

2) %FOUND – Returns true if the data found in cursor other wise returns false.

3) %NOTFOUND – Returns true if the data is not found in the cursor other wise returns false.

4) %ROWCOUNT – Returns records count is existed in a cursor.

Types of cursors:

1) IMPLICIT CURSORS

2) EXPLICIT CURSORS

3) FOR CURSORS

4) PARAMETERISED CURSORS

1) IMPLICIT CURSORS:

Implicit cursors are a predefined cursors which executes automatically when ever the SQL operation performed. Whenever SQL operation performed it opens a predefined cursor that is “SQL_CURSOR” and stores the corresponding result of given query in that area and returns the specified result as per cursor attribute.

Implicit cursor attributes:

1) SQL%ISOPEN

2) SQL%FOUND

3) SQL%NOTFOUND

4) SQL%ROWCOUNT

SQL is a implicit cursor which stores the records as per selected query by default.

W.A.PL/SQL block to given an example for SQL%FOUND and SQL%ROWCOUNT

DECLARE

DNO DEPT.DEPTNO%TYPE;

BEGIN

DELETE FROM EMP WHERE DEPTNO=&DNO;

IF(SQL%FOUND) THEN

DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT||’RECORDS DELETED’);

ELSE

DBMS_OUTPUT.PUT_LINE(‘RECORD NOT FOUND’);

END IF;

END;

EXPLICIT CURSORS:

User defined cursors are called explicit cursors. The cursors which are created by users that cursors are called explicit cursors.

Steps for creation of explicit cursor:

1) Create the cursor

2) Open the cursor

3) Fetch the cursor data into PL/SQL variable/record

4) Exit when data not found in cursor

5) Close the cursor

Syntax:

DECLARE

CURSOR cursor_name IS <SELECT QUERY>;

VARIABLE DECLARATIONS;

BEGIN

OPEN cursor_name;

LOOP

FETCH cursor_name INTO LOCAL VARIABLES/RECORD;

EXIT WHEN cursor_name%NOTFOUND;

--- STATEMENTS;

END LOOP;

CLOSE cursor_name;

END;

W.A.PL/SQL block to read and print the student details using explicit cursors.

DECLARE

STUREC STUDENT%ROWTYPE;

CURSOR STUCUR IS SELECT * FROM STUDENT;

BEGIN

OPEN STUCUR;

LOOP

FETCH STUCUR INTO STUREC;

EXIT WHEN STUCUR%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(STUREC.SNO||’ ‘||STUREC.SNA||’ ‘||STUREC.S1||’ ‘||STUREC.S2||’ ‘||STUREC.S3);

END LOOP;

CLOSE STUCUR;

END;

W.A.PL/SQL block to calculate the each student total, average and grade as per following conditions using explicit cursor. (%ISOPEN).

DECLARE

STUREC STUDENT%ROWTYPE;

CURSOR STUCUR IS SELECT * FROM STUDENT;

BEGIN

OPEN STUCUR;

LOOP

FETCH STUCUR INTO STUREC;

EXIT WHEN STUCUR%NOTFOUND;

STUREC.TOTAL := STUREC.S1 + STUREC.S2 + STUREC.S3;

STUREC.AVRG :=STUREC.TOTAL/3;

IF(STUREC.AVRG>=80) THEN

UPDATE STUDENT SET GRADE=’A’ WHERE SNO=STUREC.SNO;

ELSIF(STUREC.AVRG>=60) THEN

UPDATE STUDEN T SET GRADE=’B’ WHERE SNO=STUREC.SNO;

ELSE

UPDATE STUDENT SET GRADE=’C’ WHERE SNO=STUREC.SNO;

END IF;

COMMIT;

END LOOP;

END;

FOR CURSORS:

A cursor which is created by using for statement is called as for cursor.

In for cursor we can’t follow of explicit cursor steps.

For cursor automatically executes the following steps:

1) Opens the cursor automatically.

2) Fetches the record into PL/SQL block record.

3) Exit from cursor automatically when record not found in cursor.

4) Closes the cursor automatically.

Syntax:

DECLARE

CURSOR cursor_name is <SELECT query>;

Variable declarations;

BEGIN

FOR record_type IN cursor_name

LOOP

----- STATEMENTS;

END LOOP;

END;

W.A.PL/SQL block to display the employee details using FOR

CURSOR

CURSOR EMPCUR IS SELECT * FROM EMP WHERE JOB=’SALESMAN’;

BEGIN

FOR EMPREC IN EMPCUR

LOOP

DBMS_OUTPUT.PUT_LINE(EMPREC.EMPNO||’ ‘||EMPREC.ENAME);

END LOOP;

END;

W.A.PL/SQL block to display student records in reverse order using FOR cursor.

DECLARE

CURSOR STUCUR IS SELECT * FROM STUDENT ORDER BY ROWID DESC;

BEGIN

FOR STUREC IN STUCUR

LOOP

DBMS_OUTPUT.PUT_LINE(STUREC.SNO ||’ ‘||STUREC.SNA ||’ ‘|| STUREC.TOTAL ||’ ‘|| STUREC.AVRG);

END LOOP;

END;

Parameterized cursor:

A cursor which takes parameters that cursor is called parameterized cursor.

Syntax:

Cursor creation:

CURSOR cursor_name(arguments) IS <SELECT query>;

Opening cursor:

OPEN cursor_name(arguments);

W.A.PL/SQL block to add grace marks 3 to practical marks that who get the theory marks above 75 and practical less than 45 by using parameterized cursors.

DECLARE

CURSOR STUCUR IS SELECT * FROM STUDENT;

CURSOR MARKCUR( NO INTEGER) IS SELECT * FROM MARKS WHERE SNO=NO;

BEGIN

FOR STUREC IN STUCUR

LOOP

FOR MARKREC IN MARKCUR(STUREC.SNO)

LOOP

IF(MARKREC.TMARKS>75 AND MARKREC.PMARKS<45) THEN

UPDATE MARKS SET PMARKS=PMARKS+3 WHERE SNO=STUREC.SNO;

COMMIT;

END IF;

END LOOP;

END;

PROCEDURES

Procedure is a sub program which can perform particular action and it can not returns the value.

Syntax:

CREATE [OR REPLACE] PROCEDURE proc_name(col_name in|out|in out datatype,..,) is/as

VARIABLE DECLARATIONS;

BEGIN

--- procedure body

END proc_name;

Execute the procedure:

EXECUTE proc_name(arguments);

W.A. PL/SQL block to create the procedure that adds two values and call that function into main function.

CREATE OR REPLACE PROCEDURE ADDITION(A IN INTEGER, B IN INTEGER) IS

C INTEGER;

BEGIN

C :=A+B;

DBMS_OUTPUT.PUT_LINE(‘ADDITION :’||C);

END;

EXECUTE THE PROCEDURE:

EXECUTE ADDITION(10,20);

Calling the procedure in main_prg:

DECLARE

A INTEGER;

B INTEGER;

BEGIN

A :=&A;

B :=&B;

ADDITION(A,B);

END;

W.A. PROCEDURE to increment the salaries of all employees. Salary increment is based on following conditions. If salary >=4000 then increment is 20% of salary. If salary >=3000 then increment is 10% of salary. Otherwise increment is 5% of salary.

CREATE OR REPLACE PROCEDURE INCREMENT_SAL(ENO INTEGER, SALARY REAL) IS

BEGIN

IF(SALARY>=4000) THEN

UPDATE EMP SET SAL=SAL+(SAL*20/100) WHERE EMPNO=ENO;

ELSIF(SALARY>=3000) THEN

UPDATE EMP SET SAL=SAL+(SAL*10/100) WHERE EMPNO=ENO;

ELSE

UPDATE EMP SET SAL=SAL+(SAL*5/100) WHERE EMPNO=ENO;

END IF;

END;

DELCARE

CURSOR EMPCUR IS SELECT * FROM EMP;

BEGIN

FOR EMPREC IN EMPCUR

LOOP

INCREMENT_SAL(EMPREC.EMPNO, EMPREC.SAL);

END LOOP;

END;

To view errors of procedure:

SHOW ERRORS PROCEDURE proc_name;

Eg:

Show errors procedure addition;

To view procedure names:

SELECT OBJECT_NAME FROM USER_PROCEDURES;

To drop the procedure:

DROP PROCEDURE proc_name;

Eg:

Drop procedure addition;

FUNCTIONS

FUNCTION is a sub program to perform particular action which can returns the value.

Syntax:

CREATE [OR REPLACE] FUNCTION func_name(col_name in|out|in out datatype,…) return type is

VARIABLE DECLARATIONS;

BEGIN

--- FUNCTION BODY

RETURN result;

END;

Execute the FUNCTION:

SELECT func_name (arguments) from dual;

W.A.PL/SQL block to create a stored function addition that add two values

FUNCTIONS CREATION:

CREATE OR REPLACE FUNCTION ADDITION(A IN INTEGER, B IN INTEGER) RETURN INTEGER IS

C INTEGER;

BEGIN

C :=A+B;

RETURN C;

END;

EXECUTE THE FUNCTION:

SELECT ADDITION(10,20) FROM DUAL;

CALLING THE FUNCTION AT MAIN_PRG:

DECLARE

A INTEGER;

B INTEGER;

C INTEGER;

BEGIN

A :=&A;

B :=&B;

C :=ADDITION(A,B);

DBMS_OUTPUT.PUT_LINE(‘ADDITION’ ||C);

END;

W.A. STORED FUNCTION to accept the number and find the whether given number is armstrong or not.

Function creation:

CREATE OR REPLACE FUNCTION ISARMSTRONG( N INTEGER) RETURN BOOLEAN IS

NO INTEGER;

A INTEGER;

S INTEGER :=0;

BEGIN

NO :=N;

WHILE(NO >0)

LOOP

A :=MOD(NO,10);

S :=S + (A*A*A*);

NO :=TRUNC(NO/10);

END LOOP;

IF ( S=N) THEN

RETURN TRUE;

ELSE

RETURN FALSE;

END IF;

END;

Mian_program:

DECLARE

N INTEGER;

BEGIN

N :=&N;

IF (ISARMSTRONG(N) = TRUE ) THEN

DBMS_OUTPUT.PUT_LINE(‘ARMSTRONG’);

ELSE

DBMS_OUTPUT.PUT_LINE(‘IS NOT ARMSTRONG’);

END IF;

END;

TO SHOW ERRORS IN FUNCTION:

SHOW ERRORS FUNCTION fun_name;

Eg:

Show errors function isarmstrong;

To drop function:

DROP FUNCTION func_name;

Eg:

Drop function isarmstrong;

PACKAGES

Different types of procedures and functions are binding into a single set is known as package.

By using package we can create our own library code that other programmers could reuse.

Packages are typically made up of the 2 components.

They are:

1) PACKAGE SPECIFICATION

2) PACKAGE BODY

The package specification contains information about the package and lists available procedures

And functions.

The functions and procedure definitions are defined in package body.

Syntax:

PACKAGE:

CREATE [OR REPLACE] PACKAGE pack_name is/as

---- function declarations

---- procedure declarations

END pack_name;

PACKAGE BODY:

CREATE [OR REPLACE] PACKAGE BODY pack_name is/as

---- function definitions

---- procedure definitions

END pack_name;

Calling procedures and functions of a particular package:

EXECUTE pack_name.proc_name(arguments);

SELECT pack_name.func_name(arguments) FROM DUAL;

W.A.PL/SQL to create the package that apply the arithematic operations for given two values.

PACKAGE CREATION:

CREATE OR REPLACE PACKAGE CALCULATIONS IS PROCEDURE ADDITION (A INTEGER, B INTEGER);

FUNCTION SUBSTRACTION(A INTEGER, B INTEGER) RETURN INTEGER;

END;

PACKAGE BODY CREATION:

CREATE OR REPLACE PACKAGE BODY CALCULATIONS IS

PROCEDURE ADDITION(A INTEGER, B INTEGER) IS

C INTEGER;

BEGIN

C :=A+B;

DBMS_OUTPUT.PUT_LINE(‘ADDITION :’||C);

END;

FUNCTION SUBSTRACTION(A INTEGER, B INTEGER) RETURN INTEGER IS

C INTEGER;

BEGIN

C :=A – B;

RETURN C;

END;

Execute package procedure:

EXECUTE calculations.addition(10,20);

Execute package function:

SELECT calculations.substraction(30,20) from dual;

Calling package methods at main_program:

DECLARE

A INTEGER;

B INTEGER;

C INTEGER;

BEGIN

A :=&A;

B :=&B;

CALCULATIONS.ADDITION(A,B);

C :=CALCULATIONS.SUBSTRACTION(A,B);

DBMS_OUTPUT.PUT_LINE(‘SUBTRACTION : ‘||C);

END;

W.A. STORE PACKAGE that accept employee number or ename and delete the corresponding records using packages.

CREATE ORREPLACE PACKAGE DELETE_RECORDS IS

PROCEDURE EMPLOYEE(ENO IN INTEGER);

PROCEDURE EMPLOYEE(ENA IN VARCHAR2);

END;

CREATE OR REPLACE PACKAGE BODY DELETE_RECORDS IS

PROCEDURE EMPLOYEE(ENO IN INTEGER) IS

BEGIN

DELETE FROM EMP WHERE EMPNO=ENO;

IF(SQL%ROWCOUNT > 0) THEN

DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ‘ROWS DELETED’);

ELSE

RAISE_APPLICATION_ERROR(-20101,’EMPNO NOT FOUNT’);

END IF;

END;

PROCEDURE EMPLOYEE (ENA IN VARCHAR2) IS

BEGIN

DELETE FROM EMP WHERE ENAME=ENA;

IF(SQL%ROWCOUNT > 0) THEN

DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ‘ROWS DELETED’);

ELSE

RAISE_APPLICATION_ERROR(-20101,’ENAME NOT FOUNT’);

END IF;

END;

Execute the procedure of package:

EXECUTE delete_records.employee(7369);

EXECUTE delete_records.employee(‘SCOTT’);

To view the list of functions and procedures in particular package:

SELECT OBJECT_NAME, PROCEDURE_NAME FROM USER_PROCEDURES WHERE OBJECT_NAME =’package_name’;

Eg:

SELECT OBJECT_NAME, PROCEDURE_NAME FROM USER_PROCEDURES WHERE OBJECT_NAME=’DELETE_RECORDS’;

To drop the package:

DROP PACKAGE package_name;

DROP PACKAGE BODY package_name;

Eg:

DROP PACKAGE delete_records;

DROP PACKAGE BODY delete_records;

TRIGGERS

A TRIGGER is an action which can be performed automatically whenever the data manipulations are applied on the table.

Syntax:
CREATE [OR REPLACE] TRIGGER trigger_name BEFORE/AFTER INSERT/UPDATE/DELETE ON table_name FOR EACH ROW

DECLARE

VARIABLE DECLARATIONS;

BEGIN

---- TRIGGER ACTION

END;

In trigger two types of pseudo columns are available.

They are:

:OLD

:NEW

1) :OLD

By using :OLD column we can retrieve the old values of the table.

2) :NEW

By using :NEW column we can retrieve the new values of the table.

W.A.PL/SQL block to change the student name as upper case whenever the record inserted into a student table.

CREATE OR REPLACE TRIGGER trg_stu BEFORE INSERT ON STUDENT FOR EACH ROW

BEGIN

:NEW.SNA := UPPER(:NEW.SNA);

END;

W.A.PL/SQL block to store the information that what data manipulations are applied on the student table and at what time.

STU_OPR TABLE STRUCTURE:

CREATE TABLE SUT_OPR (SNO NUMBER(3), OPERATION VARCHAR2(10), OPDATE DATE, OPTIME VARCHAR2(20));

CREATE OR REPLACE TIGGER STU_TRG

AFTER INSERT OR UPDATE OR DELETE ON STUDENT FOR EACH ROW

BEGIN

IF INSERTING THEN

INSERT INTO STU_OPR VALUES( :NEW.SNO,’INSERTED’,SYSDATE,TO_CHAR(SYSDATE,’HH:MI:SS am’));

ELSIF UPDATING THEN

INSERT INTO STU_OPR VALUES( :OLD.SNO,’UPDATED’,SYSDATE,TO_CHAR(SYSDATE,’HH:MI:SS am’));

ELSE

INSERT INTO STU_OPR VALUES( :OLD.SNO,’DELETED’,SYSDATE,TO_CHAR(SYSDATE,’HH:MI:SS am’));

END IF;

END;

W.A.PL/SQL block to increment the salary of the given number and store that old_sal, new_sal and inserted date into a new table.

CREATE OR REPLACE TIGGER EMP_TRG

AFTER UPDATE ON EMP FOR EACH ROW

BEGIN

INSERT INTO EMP_INCR_SAL VALUES( :OLD.EMPNO, :OLD.ENAME, :OLD.SAL, :NEW.SAL,SYSDATE);

END;

Create a database tigger on emp table to restrict the insertion, deletion and updation operations.

CREATE OR REPLACE TRIGGER EMP_TRG

BEFORE INSERT OR UPDATE OR DELETE ON EMP FOR EACH ROW

BEGIN

IF INSERTING THEN

RAISE_APPLICATION_ERROR(-20101,’INSERTION NOT ALLOWED’);

ELSIF UPDATING THEN

RAISE_APPLICATION_ERROR(-20102,’UPDATION NOT ALLOWED’);

ELSE

RAISE_APPLICATION_ERROR(-20103,’DELETION NOT ALLOWED’);

END IF;

END;

To view the errors of the trigger:

SHOW ERRORS TRIGGER trigger_name;

Eg:

SHOW ERRORS TRIGGER emp_trg;

To view the list of trigger names in current user:

SELECT TRIGGER_NAME FROM USER_TIGGERS;

To enable or disable the triggers

ALTER TRIGGER trigger_name ENABLE/DISABLE;

Eg:

ALTER TRIGGER emp_trg disable;

ALTER TRIGGER emp_trg enable;

To drop the trigger:

DROP TRIGGER trigger_name;

Eg:

DROP TRIGGER emp_trg;

add

About Me

Thursday, October 11

DATA BASE MANAGEMENT SYSTEM

No comments:

Topics

Page Rank

Restricted to Copy