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
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:
Post a Comment