Simple Oracle Proagram
CREATE THE DATABASE
Aim:
To create the database
Syntax:
SQL> create table <table name>(column name1
datatype(size), column name2 datatype(size),...............);
Source
Code:
SQL> create table std(regno number(20),name char(25),
mark1 number(4), mark2 number(4), mark3 number
(4), mark4 number(4), mark5 number(4),
total number(5));
Output:
Table created.
SQL> desc std;
Name
Null? Type
-----------------------------------------
-------- ----------------------------
REGNO
NUMBER(20)
NAME
CHAR(25)
MARK1
NUMBER(4)
MARK2 NUMBER(4)
MARK3
NUMBER(4)
MARK4
NUMBER(4)
MARK5
NUMBER(4)
TOTAL NUMBER(5)
ALTER THE DATABASE(MODIFY
AND ADD)
Aim:
To alter the database
for modify.
Syntax:
SQL>alter table <table name> modify ( column name1
datatype(size), column name2 datatype(size),........);
SQL>alter table <table name> add ( column name1
datatype(size), column name2 datatype(size),........);
Source
Code:
SQL> alter table std modify(regno varchar(7));
SQL> alter table std add(address varchar2(40));
Output:
Table altered.
SQL> desc std;
Name
Null? Type
-----------------------------------------
-------- ----------------------------
REGNO
VARCHAR2(7)
NAME
CHAR(25)
MARK1
NUMBER(4)
MARK2
NUMBER(4)
MARK3
NUMBER(4)
MARK4
NUMBER(4)
MARK5
NUMBER(4)
TOTAL
NUMBER(5)
Table altered.
SQL> desc std;
Name
Null? Type
----------------------------------------- --------
-----------------------
REGNO
VARCHAR2(7)
NAME
CHAR(25)
MARK1
NUMBER(4)
MARK2 NUMBER(4)
MARK3
NUMBER(4)
MARK4
NUMBER(4)
MARK5
NUMBER(4)
TOTAL NUMBER(5)
ADDRESS
VARCHAR2(40)
DROP
THE DATABASE
Aim:
To create the database for the purpose of drops the table in
a database.
Syntax:
SQL>drop table <tablename>;
Source
code:
SQL> drop table std;
Output:
Table dropped.
To create the database to insert the values into the
database.
Syntax:
SQL> insert into std
values(&col.name1,&col.name2,........);
Source
code:
SQL> insert into std
values(®no,'&name',&mark1,&mark2,&mark3,&mark4,&mark5,&total,'&address');
Output:
Enter value for regno: 09524
Enter value for name: Shiva.M
Enter value for mark1: 95
Enter value for mark2: 95
Enter value for mark3: 95
Enter value for mark4: 96
Enter value for mark5: 100
Enter value for total: 481
Enter value for address:
29/1,periyarnagar,puliyur.
old
1: insert into std
values(®no,'&name',&mark1,&mark2,&mark3,&mark4,&mark5,&total,'&address')
new
1: insert into std
values(09524,'Shiva.M',95,95,95,96,100,481,'29/1,periyarnagar,puliyur.')
1 row created.
SQL> /
Enter value for regno: 09525
Enter value for name: Shankar.M
Enter value for mark1: 96
Enter value for mark2: 96
Enter value for mark3: 96
Enter value for mark4: 95
Enter value for mark5: 100
Enter value for total: 477
Enter value for address:
29/1,PeriyarNagar,Puliyur.
old
1: insert into std
values(®no,'&name',&mark1,&mark2,&mark3,&mark4,&mark5,&total,'&address')
new
1: insert into std
values(09525,'Shankar.M',96,96,96,95,100,477,'29/1,PeriyarNagar,Puliyur.')
1 row created.
SQL> /
Enter value for regno: 09526
Enter value for name: Amrin.S
Enter value for mark1: 97
Enter value for mark2: 80
Enter value for mark3: 95
Enter value for mark4: 90
Enter value for mark5: 95
Enter value for total: 457
Enter value for address:
periyakulathupalayam,vengamedu.
old
1: insert into std
values(®no,'&name',&mark1,&mark2,&mark3,&mark4,&mark5,&total,'&address')
new
1: insert into std
values(09526,'Amrin.S',97,80,95,90,95,457,'periyakulathupalayam,vengamedu.'
1 row created.
SQL> /
Enter value for regno: 09527
Enter value for name: Karthika.S
Enter value for mark1: 90
Enter value for mark2: 90
Enter value for mark3: 90
Enter value for mark4: 90
Enter value for mark5: 90
Enter value for total: 450
Enter value for address:
patrainearwatertank,Vengamedu.
old
1: insert into std
values(®no,'&name',&mark1,&mark2,&mark3,&mark4,&mark5,&total,'&address')
new
1: insert into std
values(09527,'Karthika.S',90,90,90,90,90,450,'patrainearwatertank,Vengamedu
1 row created.
SQL> /
Enter value for regno: 09528
Enter value for name: Dhivya.M
Enter value for mark1: 95
Enter value for mark2: 95
Enter value for mark3: 95
Enter value for mark4: 95
Enter value for mark5: 95
Enter value for total: 475
Enter value for address:
ElephantGodownnearwatertank,Vengamedu.
old
1: insert into std
values(®no,'&name',&mark1,&mark2,&mark3,&mark4,&mark5,&total,'&address')
new
1: insert into std
values(09528,'Dhivya.M',95,95,95,95,95,475,'ElephantGodownnearwatertank,Ven
1 row created.
SQL> /
Enter value for regno: 09529
Enter value for name: Sankari.M
Enter value for mark1: 80
Enter value for mark2: 80
Enter value for mark3: 80
Enter value for mark4: 80
Enter value for mark5: 80
Enter value for total: 400
Enter value for address:
Muttusandhu,Thanthonimalai.
old
1: insert into std
values(®no,'&name',&mark1,&mark2,&mark3,&mark4,&mark5,&total,'&address')
new
1: insert into std values(09529,'Sankar.M',80,80,80,80,80,400,'Muttusandhu,Thanthonimalai.')
1 row created.
ExNo: SELECT
THE DATABSE
Aim:
To select the database.
Syntax:
SQL>select *from <table name>;
Source
Code:
SQL> select *from std;
Output:
REGNO
NAME MARK1 MARK2
MARK3 MARK4
------- -------------------------
---------- ---------- ---------- ----------
MARK5 TOTAL ADDRESS
---------- ----------
----------------------------------------
9524
Shiva.M 95 95 95 96
100 481 29/1,periyarnagar,puliyur.
9525
Shankar.M 96 96 96 95
100 477 29/1,PeriyarNagar,Puliyur.
9526
Amrin.S 97 80 95
90
95 457 periyakulathupalayam,vengamedu.
9527
Karthika.S 90 90 90 90
90 450 patrainearwatertank,Vengamedu.
9528
Dhivya.M 95 95 95 95
95 475
ElephantGodownnearwatertank,Vengamedu.
9529
Sankari.M 80 80
80 80
80 400 Muttusandhu,Thanthonimalai.
ExNo: DELTE
THE DATABASE
Aim:
To
delete the particular row in the given database.
Syntax:
SQL> delete from <table name> where (condition);
Source
Code:
SQL> delete from std where regno=9528;
Output:
1 row deleted.
SQL> select *from std;
REGNO
NAME MARK1 MARK2
MARK3 MARK4
------- -------------------------
---------- ---------- ---------- ---------------------
MARK5 TOTAL ADDRESS
----------
--------------------------------------------------
9524
Shiva.M 95 95 95 96
100 481 29/1,periyarnagar,puliyur.
9525
Shankar.M 96 96 96 95
100 477 29/1,PeriyarNagar,Puliyur.
9526
Amrin.S 97 80 95 90
95 457 periyakulathupalayam,vengamedu.
9527
Karthika.S 90 90 90 90
90 450 patrainearwatertank,Vengamedu.
9529
Sankar.M 80 80
80 80
80 400 Muttusandhu,Thanthonimalai.
ExNo: KEY CONSTRAINTS
UNIQUE
Aim:
To
create the database to use the key constraint unique.
Syntax:
SQL>create table <table name>(col.name1
datatype(size) unique, col.name2 datatype(size));
Source
code:
SQL> create table dent (regno number(10)
unique,name char(25), vb number(3), DM number(3), DBMS number(3));
Output:
Table created.
SQL> insert into dent
values(®no,'&name',&vb,&DM,&DBMS);
SQL> /
Enter value for regno: 09524
Enter value for name: Shiva.M
Enter value for vb: 90
Enter value for dm: 90
Enter value for dbms: 95
old
1: insert into dent
values(®no,'&name',&vb,&DM,&DBMS)
new
1: insert into dent values(09524,'Shiva.M',90,90,95)
1 row created.
SQL> /
Enter value for regno: 09525
Enter value for name: Shankar.M
Enter value for vb: 95
Enter value for dm: 95
Enter value for dbms: 95
old
1: insert into dent values(®no,'&name',&vb,&DM,&DBMS)
new
1: insert into dent values(09525,'Shankar.M',95,95,95)
1 row created.
SQL> /
Enter value for regno: 09526
Enter value for name: Suren.M
Enter value for vb: 100
Enter value for dm: 100
Enter value for dbms: 100
old
1: insert into dent
values(®no,'&name',&vb,&DM,&DBMS)
new
1: insert into dent values(09526,'Suren.M',100,100,100)
1 row created.
SQL> /
Enter value for regno: 09527
Enter value for name: Hirthik
Enter value for vb: 96
Enter value for dm: 97
Enter value for dbms: 96
old
1: insert into dent
values(®no,'&name',&vb,&DM,&DBMS)
new
1: insert into dent values(09527,'Hirthik',96,97,96)
1 row created.
SQL> /
Enter value for regno: 09526
Enter value for name: renkar
Enter value for vb: 96
Enter value for dm: 97
Enter value for dbms: 95
old
1: insert into dent
values(®no,'&name',&vb,&DM,&DBMS)
new
1: insert into dent values(09526,'renkar',96,97,95)
insert into dent
values(09526,'renkar',96,97,95)
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.SYS_C002726)
violated
ExNo: KEY CONSTRAINTS PRIMARY KEY
Aim:
To create the database using key constraints primary key.
Syntax:
SQL>create table <table name>(col.name1
datatype(size) primary key, col.name2 datatype(size));
Source
code:
SQL> create table staff1(sno number(6) primary key, name
char(15),Designation char(14), salary numbe
r(6));
Output:
Table created.
SNO NAME DESIGNATION SALARY
---------- ---------------
--------------
----------
22560 Shiva.M GeneralManager 50000
22561 Shankar.M Manager 40000
22562 Suren.M Editor 55000
ExNo: CONSTRAINT
CHECK LIST
Aim:
To create the database using key constraints primary key.
Syntax:
SQL>create table <table name>(col.name1
datatype(size), col.name2 datatype(size).....constraint checklist
check(condition));
Source
code:
SQL> create table emp3 (eno number(6), ename
char(15),Designation char(14), salary number(7) constraint checklist
check(salary<50000));
Output:
EMPNO EMPNAME
DESIGNATION SALARY
--------- ------------ ------------- ----------
2315 shiva manager 49999
2316 shankar accountant 12999
2317 shivren clerk 35000
2318 renkar auditor 40000
2319 suren editor 45000
ExNo: AGGREGATE
FUNCTION
Aim:
To
select the database using aggregate function.
Syntax:
Sum:
SQL>Select
sum(col.name) from <table name>;
Avg:
SQL>Select avg(col.name) from <table name>;
Min:
SQL>Select min(col.name) from <table name>;
Max:
SQL>Select max(col.name) from <table name>;
Count:
SQL>Select count(col.name) from <table name>;
Source
code:
Sum:
SQL>Select sum(Salary)
from emp;
Avg:
SQL>Select avg(Salary) from emp;
Min:
SQL>Select min(Salary) from emp;
Max:
SQL>Select max(Salary) from emp;
Count:
SQL>Select count(Salary) from emp;
Output:
SUM(SALARY)
-----------
182998
AVG(SALARY)
-----------
36599.6
MIN(SALARY)
-----------
12999
MAX(SALARY)
-----------
49999
COUNT(EMPNO)
------------
5
ExNo: CHARACTER
FUNCTIONS
Aim:
To
select the database using character function.
Syntax:
INITCAP:
SQL> select initcap(‘string’) from dual;
LOWER
AND UPPER CASE:
SQL> select lower(‘string’) from dual;
SQL> select upper(‘string’) from dual;
LTRIM
AND RTRIM:
SQL> select ltrim (‘string’) from dual;
SQL> select rtrim (‘string’) from dual;
TRANSALATE
AND REPLACE:
SQL> select translate (‘string’, char 1, char 2) from
dual;
SQL> select replace (‘string’, char 1, char 2) from dual;
SUBSTRING:
SQL> select substring(‘string’, position, length) from
dual;
SIN:
SQL> select sin(value) from dual;
SQL> select sinh(value) from dual;
COS:
SQL> select cos(value) from dual;
SQL> select cosh(value) from dual;
TAN:
SQL> select tan(value) from dual;
SQL> select tanh(value) from dual;
LOGVALUES:
SQL> select log(value,basevalue) from dual;
EXPONENTIAL:
SQL> select exp(size) from dual;
POWER:
SQL> select power(value) from dual;
ROUND:
SQL> select round(value) from dual;
TRUNC
SQL> select trunc(value) from dual;
Source
code:
INITCAP:
SQL> select initcap(‘government arts college’) from dual;
LOWER
AND UPPER CASE:
SQL> select lower(‘SHIVREN’) from dual;
SQL> select upper(‘shivren’) from dual;
LTRIM
AND RTRIM:
SQL> select ltrim (‘sshivren’) from dual;
SQL> select rtrim (‘shivrenk’) from dual;
TRANSALATE
AND REPLACE:
SQL> select translate (‘string’, s, e) from dual;
SQL> select replace (‘string’, s, e) from dual;
SUBSTRING:
SQL> select substring(‘shivren’, 2, 4) from dual;
SIN:
SQL> select sin(30) from dual;
SQL> select sinh(30) from dual;
COS:
SQL> select cos(30) from dual;
SQL> select cosh(30) from dual;
TAN:
SQL> select tan(30) from dual;
SQL> select tanh(30) from dual;
LOGVALUES:
SQL> select log(10,2) from dual;
EXPONENTIAL:
SQL> select exp(5) from dual;
POWER:
SQL> select power(4,4) from dual;
ROUND:
SQL> select round(3.5167) from dual;
TRUNC
SQL> select trunc(3.1567) from dual;
Output:
INITCAP
-----------------------
Government Arts
College
LOWER
-------
shivren
UPPER
-------
SHIVREN
LTRIM
------
hivren
RTRIM
-------
shivren
TRANSLATE
-------
etring
REPLACE
-------
etring
SUBSTR
-------
hivr
SIN(30)
-------
-.98803162
SINH(30)
-------
5.3432E+12
COS(30)
-------
.15425145
COSH(30)
-------
5.3432E+12
TAN(30)
-------
-6.4053312
TANH(30)
-------
1
LOG(10,2)
-------
.301029996
EXP(5)
-------
148.413159
POWER(4,4)
-------
256
ROUND(3.5167)
-------
4
TRUNC(3.1567)
-------
3
ExNo: DATE
FUNCTION
Aim
To create a database using date function.
Syntax
SQL> create table <tablename>(Col name1 datatype(size),..........,startdate
date,enddate date);
Source
Code
SQL> create table shiv(pno number(5), name char(15),price
number(7,2), startdate date, enddate );
SQL> insert into shiv values (&pno, '&name',
&price, '&startdate', '&enddate');
SQL> select *from shiv;
SQL> select
enddate,add_months (enddate,4)from shiv;
SQL> select enddate,last_day(enddate)from shiv;
SQL> Select trunc(startdate,'year')from shiv;
SQL>
select round(startdate,'month')from shiv;
SQL>
select sysdate from dual;
Output
Table created.
SQL> desc shiv;
Name
Null? Type
-----------------------------------------
-------- ----------------------------
PNO
NUMBER(5)
NAME CHAR(15)
PRICE
NUMBER(7,2)
STARTDATE DATE
ENDDATE DATE
PNO
NAME PRICE STARTDATE
ENDDATE
-------- ----------- ----------
------------------ ----------------
524
wheat 47.75 17-FEB-01
17-JAN-12
525
Sugar 67.5 16-JAN-00 15-JAN-12
526
Rice
20.5 14-MAR-02 15-FEB-12
527
Badam 500.5 18-AUG-05
14-APR-12
ENDDATE
ADD_MONTH
---------------- -------------------
17-JAN-12
17-MAY-12
15-JAN-12
15-MAY-12
15-FEB-12
15-JUN-12
14-APR-12
14-AUG-12
ENDDATE
LAST_DAY
---------------- ---------------
17-JAN-12
31-JAN-12
15-JAN-12
31-JAN-12
15-FEB-12
29-FEB-12
14-APR-12
30-APR-12
TRUNC(STA
------------------
01-JAN-01
01-JAN-00
01-JAN-02
01-JAN-05
ROUND(STA
-------------------
01-MAR-01
01-FEB-00
01-MAR-02
01-SEP-05
SYSDATE
---------------
01-FEB-12
ExNo: FOREIGN KEY
AIM:
To create the database
Syntax:
SQL> create table <table name>(col.name1
datatype(size) primary key, col.name2 datatype(size));
SQL> create table <table name>(col.name1 datatype(size),
col.name2 datatype(size) constraint fk referrences filename(col.name));
Source
code:
SQL> create table department(dno number(5) primary key,
dname char(15), location varchar(18));.
SQL> create table emp12(eno number(6),
ename char(15), designation char(14), salary number(7,2), dno number(5)
constraints fk references department(dno));
Output:
Table created.
SQL> insert into department
values(&dno,'&dname','&location');
Enter value for dno: 420
Enter value for dname: com.science
Enter value for location: main block
old
1: insert into department values(&dno,'&dname','&location')
new
1: insert into department values(420,'com.science','main block')
1 row created.
SQL> /
Enter value for dno: 421
Enter value for dname: physics
Enter value for location: block A
old
1: insert into department values(&dno,'&dname','&location')
new
1: insert into department values(421,'physics','block A')
1 row created.
SQL> /
Enter value for dno: 422
Enter value for dname: chemistry
Enter value for location: block B
old
1: insert into department values(&dno,'&dname','&location')
new
1: insert into department values(422,'chemistry','block B')
1 row created.
SQL> /
Enter value for dno: 423
Enter value for dname: maths
Enter value for location: block C
old
1: insert into department values(&dno,'&dname','&location')
new
1: insert into department values(423,'maths','block C')
1 row created.
SQL> insert into emp12
values(&eno,'&ename','&designation',&salary,&dno);
Enter value for eno: 121
Enter value for ename: shiva
Enter value for designation: ass.lecture
Enter value for salary: 30000
Enter value for dno: 420
old
1: insert into emp12
values(&eno,'&ename','&designation',&salary,&dno)
new
1: insert into emp12 values(121,'shiva','ass.lecture',30000,420)
1 row created.
SQL> /
Enter value for eno: 122
Enter value for ename: shankar
Enter value for designation: ass.lecture
Enter value for salary: 30000
Enter value for dno: 421
old
1: insert into emp12
values(&eno,'&ename','&designation',&salary,&dno)
new
1: insert into emp12 values(122,'shankar','ass.lecture',30000,421)
1 row created.
SQL> /
Enter value for eno: 123
Enter value for ename: shivren
Enter value for designation: ass.lecture
Enter value for salary: 30000
Enter value for dno: 422
old
1: insert into emp12
values(&eno,'&ename','&designation',&salary,&dno)
new
1: insert into emp12 values(123,'shivren','ass.lecture',30000,422)
1 row created.
SQL> /
Enter value for eno: 125
Enter value for ename: suren
Enter value for designation: lecture
Enter value for salary: 35000
Enter value for dno: 424
old
1: insert into emp12
values(&eno,'&ename','&designation',&salary,&dno)
new
1: insert into emp12 values(125,'suren','lecture',35000,424)
insert into emp12
values(125,'suren','lecture',35000,424)
*
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.FK)
violated - parent key not found
ENO ENAME DESIGNATION SALARY DNO
----------
-------------------------------------------------------------
121
shiva ass.lecture 30000 420
122
shankar ass.lecture 30000 421
123
shivren ass.lecture 30000 422
124
renkar lecture 35000 423
ExNo: PL/SQL PROGRAM FOR CONTROL STRUCTURE
Aim:
To
create the PL/SQL program for control structure.
Source
code:
SQL>
declare
2 no number;
3 begin
4 no:=&no;
5 if no mod 2=0 then
6 dbms_output.put_line(no ||' is
even');
7 else
8 dbms_output.put_line(no ||' is
an odd');
9 end if;
10*
end;
11 /
Output:
Enter value for no: 210
old
4: no:=&no;
new
4: no:=210;
210 is even
PL/SQL procedure successfully completed.
ExNo: PL/SQL PROGRAMS FOR ADD TWO NUMBERS
Aim:
To
create the PL/SQL program for add two numbers.
Source
code:
SQL> declare
2 a number;
3 b number;
4 c number;
5 begin
6 a:=&a;
7 b:=&b;
8 c:=a+b;
9 dbms_output.put_line(c);
10 end;
11 /
Enter value for a: 30
old
6: a:=&a;
new
6: a:=30;
Enter value for b: 20
old
7: b:=&b;
new
7: b:=20;
PL/SQL procedure successfully completed.
SQL> save aa.sql
SP2-0540: File "aa.sql" already
exists.
Use "SAVE filename[.ext]
REPLACE".
SQL> save a.sql
Created file a.sql
SQL> edit a.squl
SQL> edita.sql
SP2-0042: unknown command
"edita.sql" - rest of line ignored.
SQL> edit a.sql
SQL> set serveroutput on;
SQL> run a.sql
1 declare
2 a number;
3 b number;
4 c number;
5 begin
6 a:=&a;
7 b:=&b;
8 c:=a+b;
9 dbms_output.put_line(c);
10*
end;
Output:
Enter value for a: 30
old
6: a:=&a;
new
6: a:=30;
Enter value for b: 20
old
7: b:=&b;
new
7: b:=20;
50
PL/SQL procedure successfully completed.
ExNo: PL/SQL PROGRAM FOR SUBTRACT
TWO NUMBERS
Aim:
To
create the PL/SQL program to subtract two numbers.
Source
code:
SQL>
declare
2 a number;
3 b number;
4 c number;
5 begin
6 a:=&a;
7 b:=&b;
8 c:=a-b;
9 dbms_output.put_line(c);
10*
end;
SQL> /
Output:
Enter value for a: 320
old
6: a:=&a;
new
6: a:=320;
Enter value for b: 120
old
7: b:=&b;
new
7: b:=120;
200
PL/SQL procedure successfully completed.
ExNo: PL/SQL PROGRAM
FOR MULTIPLY
TWO NUMBERS
Aim:
To create the PL/SQL program to subtract two numbers.
Source
code:
SQL> declare
2 a number;
3 b number;
4 c number;
5 begin
6 a:=&a;
7 b:=&b;
8 c:=a*b;
9 dbms_output.put_line(c);
10 end;
11 /
Output:
Enter value for a: 20
old
6: a:=&a;
new
6: a:=20;
Enter value for b: 2
old
7: b:=&b;
new
7: b:=2;
40
PL/SQL procedure successfully completed.
ExNo: PL/SQL
PROGRAM FOR DIVIDE
TWO NUMBERS
Aim:
To create the PL/SQL program to subtract two numbers.
Source
code:
SQL>
declare
2 a number;
3 b number;
4 c number;
5 begin
6 a:=&a;
7 b:=&b;
8 c:=a/b;
9 dbms_output.put_line(c);
10*
end;
11/
Output:
Enter value for a: 10
old
6: a:=&a;
new
6: a:=10;
Enter value for b: 2
old
7: b:=&b;
new
7: b:=2;
5
PL/SQL procedure successfully completed.
ExNo: UPDATE THE
DATABASE
Aim:
To
update the database.
Syntax:
Update <table name> set <operation>
Update <table name> set <operation> where <condion>
Source
code:
SQL> update stud1 set tot=m1+m2+m3;
Output:
5 rows updated.
SNO
SNAME M1 M2
M3 TOT
---------- --------------- ---------- ---------- ----------
----------
524
shiva
99 80 80 259
525
shankar 90 80 75 245
526
shivren 85 85
90 260
527
renkar 80 90 78 248
528v
suren
85 95 100 280
ExNo: NOT NULL
Aim:
To
create the database use not null.
Syntax:
SQL> create table <table name>(col.name1
datatype(size) Not Null, col.name2 datatype(size) .....);
Source
code:
SQL> create table emp111(empno number(7) notnull, name
char(15), designation varchar(10), salary num
ber(7));
Output:
EMPNO NAME DESIGNATION SALARY
----------
---------------
--------------------
----------
566
shiva manager 25000
567
shankar clerk 15000
568
shivren auditor 30000
569
renkar accountant 10000
ExNo CURSOR
Aim:
To
create a cursor program using PL/SQL.
Source
code:
SQL> declare
2 salary emp.sal % type;
3 begin
4 select sal into salary from emp
where empno=7900;
5 if SQL % Found then
6
DBMS_OUTPUT.PUT_LINE('salary='||salary);
7 else
8 DBMS_OUTPUT.PUT_LINE('salary
does not exist');
9 end if;
10 end;
11 /
salary= 1250;
PL/SQL procedure successfully completed.
ExNo: TRIGGER
Aim
To create a trigger program using PL/SQL.
Source
code:
SAMPLE TABLE
TO BE TRIGGERED:
SQL> CREATE
TABLE PERSON(
2 ID
INT,
3 NAME
VARCHAR(30),
4 DOB
DATE,
5
PRIMARY KEY(ID)
6 );
BEFORE INSERT
TRIGGER:
SQL> CREATE OR
REPLACE
2
TRIGGER PERSON_INSERT_BEFORE
3
BEFORE
4
INSERT
5 ON
PERSON
6 FOR
EACH ROW
7
BEGIN
8
DBMS_OUTPUT.PUT_LINE('BEFORE INSERT OF' || :NEW.NAME);
9 END;
INSERT THE
VALUES:
SQL> INSERT
INTO PERSON(ID,NAME,DOB) VALUES (1,'AMRIN',SYSDATE);
BEFORE INSERT
OFAMRIN
SQL> INSERT
INTO PERSON (ID,NAME,DOB) VALUES (2,'AMRINTHAJ',SYSDATE);
BEFORE INSERT
OFAMRINTHAJ
AFTER INSERT
OFAMRINTHAJ
BEFORE UPDATE STATEMENT
TRIGGER:
SQL> CREATE OR
REPLACE
2
TRIGGER PERSON_UPDATE_S_BEFORE
3
BEFORE UPDATE
4 ON
PERSON
5
BEGIN
6
DBMS_OUTPUT.PUT_LINE('BEFORE UPDATEING SOME PERSON(S)');
7 END;
8 /
SQL> UPDATE PERSON SET DOB=SYSDATE;
BEFORE UPDATEING SOME PERSON(S)
OUTPUT
Table created.
SQL> DESC PERSON;
Name
Null? Type
-----------------------------------------
-------- ----------------------------
ID NOT NULL NUMBER(38)
NAME
VARCHAR2(30)
DOB
DATE
Trigger created.
SQL> SELECT *
FROM PERSON;
ID
NAME
DOB
----------
------------------------------
---------
1
AMRIN
01-MAR-12
2
AMRINTHAJ
01-MAR-12
Comments
Post a Comment