add

About Me

My photo
Oracle Apps - Techno Functional consultant

Thursday, October 11

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
LIKE
  • 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
                        Or
       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
                        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 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;
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;
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;

No comments: