SQL PLSQL SERVER PROGRAMMES

Example: 2

Code a function to return the square of a given number.

Step 1: Open the editor

Step 2: Type the code below in a file named, SQR.

Program

CREATE OR REPLACE FUNCTION SQR

(NO NUMBER)

RETURN NUMBER

BEGIN

return no*no;

END;

Step 3: To test the function:

a. At SQL prompt type:

SQL> select sqr(10) from dual;

b. At SQL prompt, type the following

SQL> variable sq number

SQL> execute :sq := sqr(10)

c. In the editor, type the following

set serveroutput on

begin

dbms_output.put_line(‘Square of 10 is ‘ || sqr(10));

end;

set serveroutput off

Example: 3

Code a function to return the net salary of a given employee.

Program

CREATE OR REPLACE FUNCTION NETSAL

(id in salary.emp_no%type)

RETURN NUMBER

netsal salary.basic%type;

BEGIN

select sum(basic) + sum(commission) – sum(deduction)

into netsal

from salary

where emp_no = id;

return (netsal);

end;

To test the function:

At SQL prompt, type

SQL> variable sal number

SQL> execute :sal := netsal(1001)

SQL> print sal

Example: 4

Code a package

Step 1: Open the editor and create the package

Step 2: Type the code below in a file named, MAHEPACK.

Program

CREATE OR REPLACE PACKAGE MAHEPACK

function netsal

(id in salary.emp_no%type)

return number;

procedure tax

(id in salary.emp_no%type, tax out number);

procedure totsales

(cid in cstmast.cstid%type, sal out number);

END;

Step 3: Save the above file and open the editor to create the package body

Program

CREATE OR REPLACE PACKAGE BODY MAHEPACK

function netsal

(id in salary.emp_no%type)

return number

netsal salary.basic%type;

begin

select sum(basic) + sum(commission) – sum(deduction)

into netsal

from salary

where emp_no = id;

return (netsal);

end;

procedure tax

(id in salary.emp_no%type, tax out number)

netsalary number(10, 2);

begin

netsalary := netsal(id);

if netsalary < 2000

then

tax := netsalary * 0.02;

elsif netsalary < 4000

then

tax := netsal * 0.04;

else

tax := netsalary * 0.01;

end if;

end;

procedure totsales

(cid in cstmast.cstid%type, sal out number)

id TRN.ITMID%TYPE;

qty TRN.TRNQTY%TYPE;

price ITMMAST.ITMPRICE%TYPE;

sales NUMBER(10, 2) := 0;

cursor cur_tr is

select trn,itmid, trnqty, itmprice

from trn, itmmast

where trn.cstid = cid

and trn.itmid = itmmast.itmid;

begin

sales := 0;

open cur_tr;

loop

fetch cur_tr into id, qty, price;

if cur_tr%rowcount = 0

then

raise_application_error(-20020, ‘ERREOR!!!THERE IS NO DATA’);

end if;

exit when cur_tr%notfound;

sales := sales + qty * price;

end loop;

close cur_tr;

sal := sales;

end;

END;

Step 4: Save the above file and to create the package, at SQL prompt type

SQL> start mpack

SQL> start mpackb

Step 5: To execute, at SQL prompt type

SQL> variable tx number

SQL> execute mahepack.tax(1001, :tx)

SQL> print tx

SQL> variable nsal number

SQL> execute :nsal := mahepack.netsal(1001)

SQL> print nsal

PS:

Show Errors:

SHOW ERRORS is used to display the line number and error of the most recent compilation errors.

SQL> SHOW ERRORS

Raise Application Error:

RAISE_APPLICATION_ERROR procedure is one of Oracles utilities which help the user to manage the error conditions in the applications by specifying user-defined error numbers and messages.

It takes 2 input parameters – the error number (which must be between -20000 and -20999) and the error message to display.

It terminates the procedure execution, rolls back any effects of the procedure, returns any user-specified error number and error message.

WEEK 12 (PL/SQL)

6) TheTk(i) Creation of simple PL/SQL program which includes declaration section, executable section and exception handling section ( ex: Student marks can be selected from the table and printed for those who secured first class and an exception can be raised if no records were found).

(ii) Insert data into student table and use COMMIT, ROLLBACK and SAVEPOINT in SQL block.

7) Develop a program that includes the features NESTED IF, CASE and CASE expression. The program can be extended using the NULLIFTheTkjfdkjlkdfjskjlkfd and COALESCE functions.

8) Program development using WHILE LOOPS, numeric FOR LOOPS, nested loops using ERROR handling, BUILT IN exceptions, USER defined exceptions, RAISE APPLICATION ERROR.

9) Program development using creation of procedure, passing parameters IN and OUT procedures.

10) Program development using creation of stored function, invoke functions in SQL statements and write complex functions.

11) Program development using creation of package specification, package bodies, private objects, package variables and cursors and calling stored packages.

12) Develop programs using features of parameters in a CURSOR, FOR UPDATE CURSOR, WHERE CURRENT of clause and CURSOR variables.

Example: 1

Write a row trigger to insert the existing values of the salary table into a new table when the salary table is updated.

(Salary < emp_no, basic, commission, deduction, salary_date, department>

Salaryaud < emp_no, basic, commission, deduction, salary_date, department>)

Step 1: Open the editor

Step 2: Type the code below in a file named, TRSAL.

Program

CREATE TRIGGER UPDSAL

BEFORE UPDATE ON SALARY

FOR EACH ROW

BEGIN

insert intosalaryaud

values (:old.emp_no, :old.basic, :old.commission, :old.deduction, :old.salary_date, :old.department);

END

Step 3: Save the above file

Step 4: To create the trigger, at SQL prompt type

SQL> start trsal

Step 5: To test the trigger, update values in salary table and see if data is inserted in salaryaud table.

Example: 2

Write a trigger to restrict the user from using the emp table on Tuesday.

Program

create or replace trigger tr2

before insert or update or delete

on emp

begin

if (rtrim(to_char(sysdate, 'day')) = 'tuesday')

then

raise_application_error(-20121, 'Cannot delete on Tuesday');

end if;

end;

Example: 3

Write a PL/SQL block of code that first inserts a record in an Emp table. Update salaries of emp 1001 and emp 1002 by Rs 2000 and Rs 1500. Then check to see that the total salary does not exceed Rs 20000. If total salary is greater than Rs 20000 then undo the updates made to emp 1001 and emp 1002.

Program

DECLARE

total_sal number(9);

BEGIN

insert into emp

values (‘1009’, ‘Ram’, 1000);

SAVEPOINT no_update;

update emp

set sal = sal + 2000

where emp_id = 1001;

update emp

set sal = sal + 1500

where emp_id = 1002;

select sum(sal)

into total_sal

from emp;

if total_sal > 20000

then

ROLLBACK TO SQVEPOINT no_update;

end if;

COMMIT;

END;

PS:

The above program first inserts a record into emp table. It then marks and saves the current position in the transaction by using the SAVEPOINT. It updates the salaries, if the salaries are exceeding 20000 it rollsback to the save point, ie ignores the 2 updates and only commits the insert. If the salaries do not exceed 20000 then the insert and 2 updates are committed.

Example: 4

Write a PL/SQL code of block, to calculate the area of the circle for the values of radius varying from 1 to 10. Store the odd radius values and the corresponding areas in a table.

Program

declare

pi constant number(4,2) := 3.14;

radius number(5);

areaa number(14,2);

begin

radius := 1;

while radius <= 10 loop

areaa := pi*power(radius,2);

case

when radius = 1

then

insert into area values (radius, areaa);

when radius = 3

then

insert into area values (radius, areaa);

when radius = 53

then

insert into area values(radius,areaa);

when radius = 7

then

insert into area values(radius,areaa);

when radius = 9

then

insert into area values(radius,areaa);

else

dbms_output.put_line(‘EVEN RADIUS, NOT INSERTING!!!’);

end case;

radius:=radius+1;

end loop;

end;

Overview of SQL DDL, DML and DCL Commands.

DDL is Data Definition Language statements. Some examples:

CREATE - to create objects in the database

ALTER - alters the structure of the database

DROP - delete objects from the database

TRUNCATE - remove all records from a table, including all spaces allocated for the records ar

removed

COMMENT - add comments to the data dictionary

GRANT - gives user's access privileges to database

REVOKE - withdraw access privileges given with the GRANT command

DML is Data Manipulation Language statements. Some examples:

SELECT - retrieve data from the a database

INSERT - insert data into a table

UPDATE - updates existing data within a table

DELETE - deletes all records from a table, the space for the records remain

CALL - call a PL/SQL or Java subprogram

EXPLAIN PLAN - explain access path to data

LOCK TABLE - control concurrency

DCL is Data Control Language statements. Some examples:

COMMIT - save work done

SAVEPOINT - identify a point in a transaction to which you can later roll back

ROLLBACK - restore database to original since the last COMMIT

SET TRANSACTION - Change transaction options like what rollback segment to use

Basic SQL DDL Commands.

To practice basic SQL DDL Commands such as CREATE, DROP, etc.

1. SQL - CREATE TABLE

Syntax: CREATE TABLE tablename (column_name data_ type constraints, …)

Example:

INPUT:

SQL> CREATE TABLE Emp ( EmpNo short CONSTRAINT PKey PRIMARY KEY,

EName VarChar(15), Job Char(10) CONSTRAINT Unik1 UNIQUE,

Mgr short CONSTRAINT FKey1 REFERENCES EMP (EmpNo),

Hiredate Date, DeptNo short CONSTRAINT FKey2 REFERENCES DEPT(DeptNo));

RESULT: Table created.

SQL>Create table prog20 (pname varchar2(20) not null), doj date not null,dob date not null, sex varchar(1) not null, prof1 varchar(20),prof2 varchar(20),salary number(7,2) not null);

RESULT:

Table created.

SQL>desc prog20;

Name Null? Type

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

PNAME NOT NULL VARCHAR2(20)

DOJ NOT NULL DATE

DOB NOT NULL DATE

SEX NOT NULL VARCHAR2(1)

PROF1 VARCHAR2(20)

PROF2 VARCHAR2(20)

SALARY NOT NULL NUMBER(7,2)

2. SQL - ALTER TABLE

INPUT:

SQL>ALTER TABLE EMP ADD CONSTRAINT Pkey1 PRIMARY KEY (EmpNo);

RESULT: Table Altered.

Similarly, ALTER TABLE EMP DROP CONSTRAINT Pkey1;

3. SQL - DROP TABLE

– Deletes table structure – Cannot be recovered – Use with caution

INPUT:

SQL> DROP TABLE EMP; Here EMP is table name

RESULT: Table Dropped.

4. TRUNCATE TRUNCATE TABLE <TABLE NAME>;

Basic SQL DML Commands.

To practice basic SQL DML Commands such as INSERT, DELETE, etc.

1. SQL - INSERT INTO

Syntax: INSERT INTO tablename VALUES (value list)

l Single-row insert

INSERT INTO S VALUES(‘S3’,’SUP3’,’BLORE’,10)

l Inserting one row, many columns at a time

INSERT INTO S (SNO, SNAME) VALUES (‘S1’, ‘Smith’);S1’ Smith’

l Inserting many rows, all/some columns at a time.

INSERT INTO NEW_SUPPLIER (SNO, SNAME)

SELECT SNO, SNAME FROM S

WHERE CITY IN (‘BLORE’,’MADRAS’)

Other Examples:

INPUT:

SQL>Insert into prog values (‘kkk’,’05-may-56’);

RESULT: 1 row created.

INPUT:

SQL>Insert into prog20 values(‘Hema’,’25-sept-01’28-jan-85’,’f’,’c’,’c++’,’25000’);

RESULT: 1 row created.

INPUT:

SQL>Insert into prog values(‘&pname’,’&doj’);

SQL> Insert into prog values('&pname','&doj');

Enter value for pname: ravi

Enter value for doj: 15-june-81

RESULT:

old 1: Insert into prog values('&pname','&doj')

new 1: Insert into prog values('ravi','15-june-81')

1 row created.

2. SQL - UPDATE

Syntax: UPDATE tablename SET column_name =value [ WHERE condition]

Examples:

UPDATE S SET CITY = ‘KANPUR’ WHERE SNO=‘S1’

UPDATE EMP SET SAL = 1.10 * SAL

SQL> update emp set sal=20000 where empno=7369;

1 row updated.

3. SQL - DELETE FROM

Syntax: DELETE FROM tablename WHERE condition

Examples:

DELETE FROM SP WHERE PNO= ‘P1’

DELETE FROM SP

INPUT:

SQL>Delete from emp where empno=7369;

RESULT: 1 row deleted.

Basic SQL DCL Commands.

To practice basic SQL DCL Commands such as COMMIT, ROLLBACK etc.

1. COMMIT

Save changes (transactional).

Syntax:

COMMIT [WORK] [COMMENT 'comment_text']

COMMIT [WORK] [FORCE 'force_text' [,int] ]

FORCE - will manually commit an in-doubt distributed transaction
force_text - transaction identifier (see the DBA_2PC_PENDING view)
int - sets a specific SCN.
If a network or machine failure prevents a distributed transaction from committing properly, Oracle will store any commit comment in the data dictionary along with the transaction ID.

INPUT:

SQL>commit;

RESULT: Commit complete.

2. ROLLBACK

Undo work done (transactional).
Syntax:

ROLLBACK [WORK] [TO [SAVEPOINT]'savepoint_text_identifier'];

ROLLBACK [WORK] [FORCE 'force_text'];

FORCE - will manually rollback an in-doubt distributed transaction

INPUT:

SQL>rollback;

RESULT:Rollback complete.

3. SAVEPOINT

Save changes to a point (transactional).

Syntax:

SAVEPOINT text_identifier

Example:

UPDATE employees

SET salary = 95000

WHERE last_name = 'Smith';

SAVEPOINT justsmith;

UPDATE employees

SET salary = 1000000;

SAVEPOINT everyone;

SELECT SUM(salary) FROM employees;

ROLLBACK TO SAVEPOINT justsmith;

COMMIT;

Writing and Practice of Simple Queries.

To write simple queries and practice them.

1. Get the description of EMP table.

SQL>desc emp;

RESULT:

Name Null? Type

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

EMPNO NOT NULL NUMBER(4)

ENAME VARCHAR2(10)

JOB VARCHAR2(9)

MGR NUMBER(4)

HIREDATE DATE

SAL NUMBER(7,2)

COMM NUMBER(7,2)

DEPTNO NUMBER(3)

AGE NUMBER(3)

ESAL NUMBER(10)

2. Get the description DEPT table.

SQL>desc dept;

RESULT:

Name Null? Type

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

DEPTNO NOT NULL NUMBER(2)

DNAME VARCHAR2(14)

LOC VARCHAR2(13)

3.List all employee details.

SQL>select * from emp;

RESULT:

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO AGE ESAL

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

7369 SMITH CLERK 7902 17-DEC-80 800 0 20 25 0

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 25 0

7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 25 0

7566 JONES MANAGER 7839 02-APR-81 2975 500 20 25 0

7698 BLAKE MANAGER 7839 01-MAY-81 2850 1400 30 25 0

DBMS LAB MANUAL

SQL PLSQL SERVER PROGRAMMES

INPUT:

PLSQL C