The Data Definition Language (DDL) is used to create and destroy databases and database objects. These commands will primarily be used by database administrators during the setup and removal phases of a database project. Let's take a look at the structure and usage of four basic DDL commands:
The Data Definition Language (DDL) is used to create and destroy databases and database objects. These commands will primarily be used by database administrators during the setup and removal phases of a database project.
SYNTAX:
1. For Creating a Table:
SQL> create table
2. To describe a Table:
SQL>desc
3. To Alter a table:
SQL>alter table
4. To Drop the table:
SQL>drop table
4. Not null :
SQL>create table
5. Check:
SQL>create table
6. Primary key:
SQL>create table
7. Foreign key:
SQL>create table
8. On Delete Cascade:
SQL>create table
INSERT, SELECT, UPDATE & DELETE COMMANDS
Procedure
1) Create a table student with the following schema
Student (rollno, regno, name, address and phone number)
2) Create a table course with the following schema
Course (courseid, name)
3) Create a table faculty with the following schema
Faculty (facultyid, facultyname, dept, address)
4) Create a table class with the following schema
Class (roomno, block)
5) use insert ,select, update, delete commands on the tables
SYNTAX:
1. To insert into a Table:
(a) SQL> insert into
(b) SQL> insert into
2. To display contents of a Table:
(a) SQL> select * from
(b) SQL> select column1,column2 from
3. To update the Table:
(a) SQL> update
4. To delete the Table:
(a)SQL> delete from
HIGH-LEVEL LANGUAGE EXTENSIONS WITH TRIGGERS
PL/SQL Triggers:
Triggers are basically PL/SQL procedures that are associated with tables, and are called
whenever a certain modification (event) occurs. The modification statements may include
INSERT, UPDATE, and DELETE. The general structure of triggers is:
CREATE [OR REPLACE]
TRIGGER trigger_name
BEFORE (or AFTER)
INSERT OR UPDATE [OF COLUMNS] OR DELETE
ON tablename
[FOR EACH ROW [WHEN (condition)]]
BEGIN
...
END;
The usual CREATE OR REPLACE we have already seen with procedures and functions... TRIGGER specifies just what type of object we are creating. The BEFORE (or AFTER) in the trigger definition refers to when you want to run the trigger, either before the actual database modification (update, delete, insert) or after. The list of various statements, INSERT OR UPDATE [OF COLUMNS] OR DELETE refers to statements that trigger this trigger. You can specify all three, or just one. Let’s say you wanted the trigger to fire only when you do a delete; well, then you’d only specify a DELETE in the list. On some table specifies that the trigger is associated with such table. As we shall see
later, this does not necessarily has to be a table, but could also be a view. There are several types of triggers; ones for each row and others per statement. For example, when you’re doing an update, you can have a trigger fire once for each thing being updated (if you update 20 rows, the thing would fire 20 times), or you can have it fire just once per statement (if a single update statement is updating 20 rows, the trigger would fire just once). This is what that FOR EACH ROW in the trigger definition means. The PL/SQL block (between BEGIN and END) is a usual code block where you can place PL/SQL commands. The only limitation is that you cannot use COMMIT (or ROLLBACK) for obvious reasons.
Permissions:
Just like with procedures and functions, creating triggers requires certain privileges which
are not part of the default privilege set. If you cannot create triggers from these notes because of permissions, you (or the admin) has to GRANT CREATE TRIGGER privilege on your username. For example, to allow user ‘alex’ to create triggers, I may do something like this:
GRANT CREATE TRIGGER TO alex;
Note that if you are accessing a public Oracle server you must ask the admin to setup these things for you.
Sample Table to be Triggered
Before we begin playing with triggers, let’s create a simple table with which we can experiment:
CREATE TABLE PERSON (
ID INT,
NAME VARCHAR(30),
DOB DATE,
PRIMARY KEY(ID)
);
The above creates a PERSON table with an ID, a NAME and a DOB columns (fields). Whatever triggers we define in these notes will relate to this table.
Also, let’s not forget to setup: SET SERVEROUTPUT ON;
Special IF statements:
Inside the PL/SQL block of a trigger we can use if statements to determine what statement caused the firing of the trigger. These are generally of the form: IF inserting THEN... where besides “inserting” you can also use updating and deleting. An example would be something like:
CREATE OR REPLACE
TRIGGER PERSON_BIUD
BEFORE INSERT OR UPDATE OR DELETE ON PERSON
FOR EACH ROW
BEGIN
IF INSERTING THEN
DBMS_OUTPUT.PUT_LINE(’INSERTING PERSON: ’ || :NEW.NAME);
ELSIF UPDATING THEN
DBMS_OUTPUT.PUT_LINE(’UPDATING PERSON: ’ ||
:OLD.NAME || ’ TO ’ || :NEW.NAME);
ELSIF DELETING THEN
DBMS_OUTPUT.PUT_LINE(’DELETING PERSON: ’ || :OLD.NAME);
END IF;
END;
Working with Views:
CREATE OR REPLACE
VIEW PERSON_VIEW AS
SELECT NAME FROM PERSON;
Now, we know that updating (or inserting) into a view is kind of pointless; however, we can
provide this functionality using a trigger! For example:
CREATE OR REPLACE
TRIGGER PERSON_VIEW_INSERT
INSTEAD OF INSERT ON PERSON_VIEW
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE(’INSERTING: ’ || :NEW.NAME);
-- we can also do
-- INSERT INTO PERSON(ID,NAME,DOB) VALUES (N,:NEW.NAME,SYSDATE);
END;
Trigger Exceptions :
Triggers become part of the transaction of a statement, which implies that it causes (or
raises) any exceptions (which we’ll talk about later), the whole statement is rolled back.
Think of an exception as a flag that is raised when an error occurs. Sometimes, an error (or exception) is raised for a good reason. For example, to prevent some action that improperly modifies the database. Let’s say that our database should not allow anyone to modify their DOB (after the person is in the database, their DOB is assumed to be static). Anyway, we can create a trigger that would prevent us from updating the DOB:
CREATE OR REPLACE
TRIGGER PERSON_DOB
BEFORE UPDATE OF DOB ON PERSON
FOR EACH ROW
BEGIN
RAISE_APPLICATION_ERROR(-20000,’CANNOT CHANGE DATE OF BIRTH’);
END;
Notice the format of the trigger declaration. We explicitly specify that it will be called
BEFORE UPDATE OF DOB ON PERSON.The next thing you should notice is the procedure call RAISE APPLICATION ERROR, which accepts an error code, and an explanation string. This effectively halts our trigger execution, and raises an error, preventing our DOB from being modified. An error (exception) in a trigger stops the code from updating the DOB. When we do the actual update for example:
UPDATE PERSON SET DOB = SYSDATE;
We end up with an error, that says we CANNOT CHANGE DATE OF BIRTH.
SQL> UPDATE PERSON SET DOB = SYSDATE;
UPDATE PERSON SET DOB = SYSDATE
*
ERROR at line 1:
ORA-20000: CANNOT CHANGE DATE OF BIRTH
ORA-06512: at "PARTICLE.PERSON_DOB", line 2
ORA-04088: error during execution of trigger ’PARTICLE.PERSON_DOB’
You should also notice the error code of ORA-20000. This is our -20000 parameter to
RAISE APPLICATION ERROR.
Viewing Triggers:
You can see all your user defined triggers by doing a select statement on USER_TRIGGERS.
For example:
SELECT TRIGGER_NAME FROM USER_TRIGGERS;
Which produces the names of all triggers. You can also select more columns to get more
detailed trigger information. You can do that at your own leisure, and explore it on your
own.
Dropping Triggers:
You can DROP triggers just like anything. The general format would be something like:
DROP TRIGGER trigger_name;
NESTED QUERIES AND JOINS
NESTED QUERIES :
A subquery is a query within a query. In Oracle, you can create subqueries within your SQL statements. These subqueries can reside in the WHERE clause, the FROM clause, or the SELECT clause
JOINS:
Join is a query in which data is returned from two or more tables.
How the join will be performed:
Step 1: Make the Cartesian product of the given tables.
Step 2: Check for the equality on common attributes for the given tables.
Natural join:
It returns the matching rows from the table that are being joined.
Syntax:
>select
Eg:
> select emp.empname,city,salary from emp,empsalary where emp.empname=empsalary.empname;
Inner join:
It returns the matching rows from the table that are being joined.
Syntax:
>select
Eg:
>select emp.empname,city,salary from emp inner join empsalary on emp.empname=empsalary.empname;
Outer Join:
Left outer join:
It returns all the rows from the table1 even when they are unmatched.
Syntax:
1. select
2. select
Eg:
> select emp.empname,city,salary from emp left outer join empsalary on emp.empname=empsalary.empname;
Right outer join:
It returns all the rows from the table2 even when they are unmatched.
Syntax:
1. select
2. select
Eg:
> select emp.empname,city,salary from emp right outer join empsalary on emp.empname=empsalary.empname;
Full join:
It is the combination of both left outer and right outer join.
Syntax:
>select
Eg:
select emp.empname,city,salary from emp full outer join empsalary on emp.empname=empsalary.empname;
VIEWS
VIEWS:
In SQL, a view is a virtual table based on the result-set of an SQL statement.A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.
SQL CREATE VIEW Statement
In SQL, a view is a virtual table based on the result-set of an SQL statement.
A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.
SQL CREATE VIEW Syntax
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
SQL CREATE VIEW Examples
CREATE VIEW [Current Product List] AS
SELECT ProductID,ProductName
FROM Products
WHERE Discontinued=No
We can query the view above as follows:
SELECT * FROM [Current Product List]
Another view in the Northwind sample database selects every product in the "Products" table with a unit price higher than the average unit price:
CREATE VIEW [Products Above Average Price] AS
SELECT ProductName,UnitPrice
FROM Products
We can query the view above as follows:
SELECT * FROM [Products Above Average Price]
Another view in the Northwind database calculates the total sale for each category in 1997. Note that this view selects its data from another view called "Product Sales for 1997":
CREATE VIEW [Category Sales For 1997] AS
SELECT DISTINCT CategoryName,Sum(ProductSales) AS CategorySales
FROM [Product Sales for 1997]
GROUP BY CategoryName
We can query the view above as follows:
SELECT * FROM [Category Sales For 1997]
We can also add a condition to the query. Now we want to see the total sale only for the category "Beverages":
SELECT * FROM [Category Sales For 1997]
WHERE CategoryName='Beverages'
SQL Updating a View
You can update a view by using the following syntax:
SQL CREATE OR REPLACE VIEW Syntax
CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
Now we want to add the "Category" column to the "Current Product List" view. We will update the view with the following SQL:
CREATE VIEW [Current Product List] AS
SELECT ProductID,ProductName,Category
FROM Products
WHERE Discontinued=No
SQL Dropping a View
You can delete a view with the DROP VIEW command.
SQL DROP VIEW Syntax
DROP VIEW view_name
FUNCTION AND PROCEDURE
FUNCTION:
A function is a subprogram that computes a value. The syntax for creating a function is given below
Create or replace function
Return datatype is
(local declaration)
begin
(executable statements)
[Exception]
(exception handlers)
end
DESCRIPTION:
SQL>create table emp1(empno number,deptno number,sal number);
SQL>/
table created
SQL> desc emp1;
Name Nullf? Type
----------------------------------------- -------- ----------------------------
EMPNO NUMBER
DEPTNO NUMBER
SAL NUMBER
SQL> insert into emp1 values(&empno,&deptno,&sal);
Enter value for empno: 7
Enter value for deptno: 2
Enter value for sal: 5000
old 1: insert into emp1 values(&empno,&deptno,&sal)
new 1: insert into emp1 values(7,2,5000)
1 row created.
SQL> select * from emp1;
EMPNO DEPTNO SAL
---------- ---------- ----------
1 1 1000
2 1 5000
3 2 1500
4 1 7000
5 2 7800
6 2 2000
7 2 5000
7 rows selected.
create or replace function deptsal(dno number) return number is
totsal number;
begin
totsal :=0;
for emp_sal in(select sal from emp1 where deptno = dno and sal is not null) loop
totsal := totsal + emp_sal.sal;
end loop;
return totsal;
end deptsal;
SQL> /
Function created.
declare
a varchar(5);
b number;
begin
a := &a;
b := deptsal(a);
dbms_output.put_line('TOTAL SALARY OF DEPARTMENT : '||b);
end;
PROCEDURE:
CREATE [ORREPLACE] PROCEDURE PROCEDURENAME
[PARAMETER[IN/OUT/IN/IN OUT] DATATYPE
[:=/DEFAULT EXPRESSION]
[(PARAMETER)]
IS/AS
DECLARATION
BEGIN
PL/SQL CODES
[EXCEPTION]
END
DESCRIPTION:
The details about the holiday is being present in the table holiday and the attributes are as follows :
Depcode
Depname
Startholiday
Endholiday
Semester
The details about the department holidays according to their semester is being given in this table according to their semester.
The procedure is being written. While executing the procedure by specifying the department code,semester and the date the procedure display whether the give day is holiday for that department in that semester
Before procedure creation:
sql>desc holiday;
Name Null? Type
-----------------------------------------
DEPCODE VARCHAR2(4) DEPNAME VARCHAR2(6)
STARTHOLIDAY DATE
ENDHOLIDAY DATE
SEMESTER NUMBER(2)
Inserting holiday details:
sql>insert into holiday values('&depco','&depname','&start','&end',&sem);
Enter value for depcode:101
Enter value for depname:EEE
Enter value for Starting:21-june-2005
Enter value for Ending:4-july-2005
Enter value for sem:2
Old 1:insert into holiday values('&depco','&depname','&start','&end',&sem);
New 1:insert into mark_trig values('101','EEE','21-june-2005','4-july-2005',2);
1 row created.
Enter value for depcode:103
Enter value for depname:MCA
Enter value for Starting:25-june-2005
Enter value for Ending:14-july-2005
Enter value for sem:2
Old 1:insert into holiday values('&depco','&depname','&start','&end',&sem);
New 1:insert into mark_trig values('103','MCA','25-june-2005','14-july-2005',2);
1 row created.
sql>select * from holiday
DEPC DEPNAM STARTHOLIDAY ENDHOLIDAY SEMESTER
------- ------------ ------------------ ------------------- ----------------
101 EEE 21-june-2005 4-july-2005 2
103 MCA 25-june-2005 14-july-2005 2
Stored procedure to check for the holiday
PROGRAM:
sql>create or replace procedure holidaycheck(dcode in char,sem in number,dat date)
1 is
2 begin
3 declare
4 n1 number
5 n2 number
6 cursor cur1 is select * from holiday;
7 cr cur1 % notfound
8 begin
9 open cur1
10 loop
11 fetch cur1 into cr;
12 exit when cur1%notfound;
13 if(cr.depcode=dcode)and (cr.semester=sem)then
14 n1:months_between(dat,cr.startholiday);
15 n2:months_between(cr.endholiday,dat);
16 if((n1>=0) and(n2>=0)) then
17 dbms_output.put_line('DEPARTMENT CODE:');
18 dbms_output.put_line(cr.depcode);
19 dbms_output.put_line('DEPARTMENT NAME:');
20 dbms_output.put_line(cr.depname);
21 dbms_output.put_line('IT IS A HOLIDAY');
22 else
23 dbms_output.put_line('IT IS NOT A HOLIDAY');
24 end if;
25 end if;
26 end loop;
27 end;
28 end;
29/
procedure created
SIMPLE PL/SQL PROGRAMS
PL/SQL:
PL/SQL stands for Procedural Language extension of SQL
PL/SQL is a combination of SQL along with the procedural features of programming languages. It was developed by Oracle Corporation in the early 90’s to enhance the capabilities of SQL
PL/SQL Block
DECLARE
Variable declaration
BEGIN
Program Execution
EXCEPTION
Exception handling
END;
• A PL/SQL Block consists of three sections:
– The Declaration section (optional).
– The Execution section (mandatory).
– The Exception (or Error) Handling section (optional)
Program 1
Write a program to check greatest of two numbers :
declare
a number(3) :=20;
b number(3) :=10;
begin
if a>b then
dbms_output.put_line('a is the greatest : ' ||a);
else
dbms_output.put_line('B is the greatest : ' ||b);
end if;
end;
Program 2:
WRITE A PROGRAM TO INPUT 2 NUMBERS AND AN OPERATOR , AND DISPLAY THE RESULT.
declare
a number(3) ;
b number(3) ;
c number(3) ;
op char(1) ;
begin
a := &a ;
b := &b ;
op := &op ;
if op='+'
then
c:=a+b;
elsif op='-'
then
c:=a-b;
elsif op='*'
then
c:=a*b;
else
c:=a/b;
end if;
dbms_output.put_line('result='||c);
end;
Program 3:
Write a program to find the sum of the digits of the number:
DECLARE
N number ;
S NUMBER :=0;
R NUMBER;
begin
n:=&N;
WHILE N<>0 LOOP
R := MOD(N,10);
S := S + R;
N := TRUNC(N/10);
end loop;
dbms_output.put_line('THE SUM OF THE DIGITS = ' || S);
end;
Program 4:
Write a program to find the factorial of a number :
declare
n number(2);
i number(2);
f number(5):=1;
begin
n :=&n;
for i in 1..n loop
f := f * i;
end loop;
dbms_output.put_line(' the factorial value = '||f);
end;
Program 5:
WRITE A PL/SQL CODE TO ACCEPT THE TEXT AND
1. REVERSE THE GIVEN TEXT.
2. CHECK THE TEXT IS PALINDROME OR NOT
DECLARE
G VARchar2(20);
r VARchar2(20);
BEGIN
G:='&g';
dbms_output.put_line('THE GIVEN TEXT :'||G);
for i in REVERSE 1.. length(G) loop
R:= R||substr(G,i,1);
end loop;
dbms_output.put_line('THE REVERSED TEXT :'||R);
IF R=G THEN
dbms_output.put_line('THE GIVEN TEXT IS PALINDROME ');
ELSE
dbms_output.put_line('THE GIVEN TEXT IS NOT PALINDROME ');
END IF;
end;
d.
Program 6:
Write a program to check the given no: is prime or not:
declare
n number;
i number;
pr number(2):=1;
begin
n:= &n;
for i in 2..n/2 loop
if mod(n,i) = 0 then
pr := 0;
end if;
end loop;
if pr = 1 then
dbms_output.put_line(' the given no: is prime: '||n);
else
dbms_output.put_line(' the given no: is not prime: '||n);
end if;
end;
.
OUTPUT
SQL> set serveroutput on;
Program 1:
program to check greatest of two numbers
SQL> /
a is the greatest : 20
PL/SQL procedure successfully completed.
Program 2:
Program to input 2 numbers and an operator , and display the result.
Enter value for a: 5
old 7: a:=&a;
new 7: a:=5;
Enter value for b: 6
old 8: b:=&b;
new 8: b:=6;
Enter value for op: '*'
old 9: op:=&op;
new 9: op:='*';
result=30
PL/SQL procedure successfully completed.completed.
Program 3:
program to find the sum of the digits of the number
SQL> Enter value for n: 375
old 7: n:=&N;
new 7: n:=375;
THE SUM OF THE DIGITS = 15
Program 4:
program to find the factorial of a number
PL/SQL procedure successfully completed.
Enter value for n: 5
old 6: n:=&n;
new 6: n:=5;
the factorial value = 120
Program 5:
program to check whether the given word is palindrome or not
SQL> /
Enter value for g: MALAYALAM
old 5: G:='&g';
new 5: G:='MALAYALAM';
THE GIVEN TEXT :MALAYALAM
THE REVERSED TEXT :MALAYALAM
THE GIVEN TEXT IS PALINDROME
PL/SQL procedure successfully completed.
SQL> /
Enter value for g: HELLO
old 5: G:='&g';
new 5: G:='HELLO';
THE GIVEN TEXT :HELLO
THE REVERSED TEXT :OLLEH
THE GIVEN TEXT IS NOT PALINDROME
PL/SQL procedure successfully complete
Program 6:
program to check the given no: is prime or not:
SQL> /
Enter value for n: 7
old 6: n:=&n;
new 6: n:=7;
the given no: is prime: 7
PL/SQL procedure successfully completed.
SQL> /
Enter value for n: 25
old 6: n:=&n;
new 6: n:=25;
the given no: is not prime: 25
PL/SQL procedure successfully completed.successfully