PLSQL SQL SERVER PROGRAMMES
OUTPUT:-
This is a demo of WHILE loop
loop counter : 1
loop counter : 2
loop counter : 3
loop counter : 4
loop counter : 5
loop counter : 6
loop counter : 7
loop counter : 8
loop counter : 9
loop counter : 10
loop counter : 4
loop counter : 5
loop counter : 6
loop counter : 7
loop counter : 8
loop counter : 9
loop counter : 10
PS:
while syntax:
while <condition> loop
<statement(s);>
End loop;
Example: 4
Write a program EMPDATA.SQL, to retrieve the employee details of an employee whose number is input by the user .
Program
-- PROGRAM TO RETRIEVE EMP DETAILS
set serveroutput on
prompt Enter Employee Number:
accept n
declare
dname emp.emp_name%type;
dbasic emp.emp_basic%type;
ddesig emp.desig%type;
begin
select emp_name, basic, design
into dname, dbasic, ddesig
from emp
where emp_no = &n;
dbms_ouput.put_line(‘Employee Details:);
dbms_output.put_line(‘Name: ‘ || dname);
dbms_output.put_line(‘Basic: ‘ || dbasic);
dbms_output.put_line(‘Designation: ‘ || ddesig);
end;
/
OUTPUT:-
enter employee number:
13
old 9:where eno =&n;
new 9:where eno=13;
employee details
Name:allen
basic:9500
desig:mech
set serveroutput off
PS:
Similarly you can use other SQL statements in the PL/SQL block
Exercises:
1) Write a PL/SQL code, EX_INVNO.SQL, block for inverting a number using all forms of loops.
ANSWER:-
declare
n number(20):=123;
s number(13):=0;
d number(3):=1;
r number(3):=10;
begin
dbms_output.put_line('the number is :' || n);
while n>0 loop
d:=mod(n,10);
s:=(s*r)+d;
n:=n/r;
end loop;
dbms_output.put_line('inverted values' || s);
end;
/
OUTPUT:-
the number is:123
inverted value is:321
2) Write a PL/SQL code, EX_SUMNO.SQL that prints the sum of ‘n’ natural numbers.
ANSWER:-
prompt enter number:
accept number n
declare
isum number(2):=0;
i number;
n number:=&n;
begin
for i in 1..n loop
isum:=isum+i;
end loop;
dbms_output.put_line('sum is ' || isum);
end;
/
OUTPUT:-
enter the number:7
sum is 28
3) Write a PL/SQL code, EX_AREA.SQL, of block to calculate the area of the circle for the values of radius varying from 3 to 7. Store the radius and the corresponding values of calculated area in the table AREA_VALUES.
ANSWER:-
set serveroutput on
declare
area number(5);
rad number(3);
pi number(4):=3.14;
begin
for rad in 3..7 loop
area:=pi*rad*rad;
dbms_output.put_line('area is' || area);
insert into area_values values(area,rad);
end loop;
end;
/
OUTPUT:-
area is :27
area is :48
area is :75
area is :108
area is :147
SQL>select * from area_values;
area rad
____ ____
27 3
48 4
75 5
108 6
147 7
area is :48
area is :75
area is :108
area is :147
SQL>select * from area_values;
area rad
____ ____
27 3
48 4
75 5
108 6
147 7
WEEK 9 (PL/SQL)
6) (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 NULLIF 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
Create a file (NEWINS.SQL), to insert into a new table, NEWEMP, the record of any employee whose number is input by the user.
1. Create the table NEWEMP <emp_no, emp_name, join_date, basic).
2. Open an editor and type the following program.
Program
prompt Enter Employee Number:
accept userno number
declare
dno number(4);
dname varchar2(30);
ddate date;
dbasic number(10);
begin
select emp_no, emp_name, join_date, basic
into dno, dname, ddate, dbasic
from emp
where emp_no = &userno;
if sql%rowcount > 0
then
insert into newemp
values (dno, dname, ddate, dbaisc);
end if;
end;
/
3. Save the file as NEWINS
4. Execute the program as
SQL> start newins
Example: 2
Create a file (NEWINS2.SQL), to insert into a new table, NEWEMP, the record of any employee whose number is input by the user. Also display on the screen the employee details and to handle errors like user entering a number which does not exist in the table.
Program
prompt Enter Employee Number:
accept userno number
declare
dno number(4);
dname varchar2(30);
ddate date;
dbasic number(10);
begin
select emp_no, emp_name, join_date, basic
into dno, dname, ddate, dbasic
from emp
where emp_no = &userno;
if sql%rowcount > 0
then
insert into newemp
values (dno, dname, ddate, dbasic);
dbms_output.put_line(‘Record inserted into NEWEMP’);
dbms_output.put_line(DNO || ‘ ‘ || DNAME || ‘ ‘ || DDATE || ‘ ‘ || DBASIC);
end if;
exception
when no_data_found then
dbms_output.put_line (‘Record ‘ || &userno || ‘ does not exist’);
end;
/
Example: 3
Create a file (CALCTAX.SQL), to calculate tax for a specific employee and display name and tax.
Program
prompt Enter Employee Number:
accept userno number
declare
tot_basic number(10, 2);
tax number(10, 2);
name varchar2(30);
begin
select emp_name, basic
into name, tot_basic
from emp
where emp_no = &userno;
if tot_basic = 0 or tot_basic is null
then
dbms_output.put_line(‘NO BASIC’);
elsif tot_basic <= 2000
then
tax := tot_basic * .02;
dbms_output.put_line(NAME || ‘ TOTAL BASIC: ‘ || TOT_BASIC);
dbms_output.put_line(NAME || ‘ TOTAL TAX: ‘ || TAX);
else
tax := tot_basic * .04;
dbms_output.put_line(NAME || ‘ TOTAL BASIC: ‘ || TOT_BASIC);
dbms_output.put_line(NAME || ‘ TOTAL TAX: ‘ || TAX);
end if;
exception
when no_data_found then
dbms_output.put_line (‘Record ‘ || &userno || ‘ does not exist’);
end;
/
PS:
EXECPTIONS
When a program is executed certain errors are automatically recognized and certain error situations must be recognized by the program itself. Errors in general are referred to as Exceptions.
Exceptions can be either System defined or User defined.
Certain system exceptions raise the following flags:
CURSOR_ALREADY_OPEN – Displayed when the user tries to open a cursor that is already open
DUP_VAL_ON_INDEX – when user tries to insert a duplicate value into a unique column
INVALID_CURSOR – when user references an invalid cursor or attempts an illegal cursor operation
INVALID_NUMBER – when user tries to use something other than a number where one is called for
LOGIN_DENIED – when connect request for user has been denied
NO_DATA_FOUND – this flag becomes TRUE when SQL select statement failed to retrieve any rows
NOT_LOGGED_ON – user is not connected to ORACLE
PROGRAM_ERROR – user hits a PL/SQL internal error
STORAGE_ERROR – user hits a PL/SQL memory error
TIMEOUT_ON_RESOURCE – user has reached timeout while waiting for an Oracle resource
TRANSACTION_BACKED_OUT – a remote server has rolled back the transaction
TOO_MANY_ROWS – the flag becomes TRUE when SQL select statement retrieves more than one row and it was supposed to retrieve only 1 row
VALUE_ERROR – user encounters an arithmetic, conversion, truncation or constraint error
ZERO_DIVIDE – flag becomes TRUE if SQL select statement tries to divide a number by 0
OTHERS – this flag is used to catch any error situations not coded by the programmer
In the exception section and must appear last in the exception section
User defined exceptions must be declared in the declare section with the reserved word, EXCEPTION.
Syntax for user defined exception:
<exception-name> EXCEPTION;
This exception can be brought into action by the command,
RAISE <exception-name>
When the exception is raised, processing control is passed to the EXCEPTION section of the PL/SQL block.
The code for the exception must be defined in the EXCEPTION section of the PL/SQL block.
WHEN <exception-name> THEN
<action>;
Exercises:
1) Write a PL/SQL code block that will accept an account number from the user and debit an amount of RS2000 from the account. If the account has a minimum balance of 500 after amount is debited the process should set a freeze on the account by setting the status to F.
(use table schema Accounts (acno, balance, status)
2) Write a PL/SQL block of code to achieve the following:
If the price of the product is >4000 then change the price to 4000. The price change is to be recorded in the old price table along with product number and date on which the price was last changed.
(use table schemas Product(pno, price) and Old_Price(pno, date_of_change, oldprice)
WEEK 10 (PL/SQL)
13) (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.
14) Develop a program that includes the features NESTED IF, CASE and CASE expression. The program can be extended using the NULLIF and COALESCE functions.
15) Program development using WHILE LOOPS, numeric FOR LOOPS, nested loops using ERROR handling, BUILT IN exceptions, USER defined exceptions, RAISE APPLICATION ERROR.
16) Program development using creation of procedure, passing parameters IN and OUT procedures.
17) Program development using creation of stored function, invoke functions in SQL statements and write complex functions.
18) Program development using creation of package specification, package bodies, private objects, package variables and cursors and calling stored packages.
19) Develop programs using features of parameters in a CURSOR, FOR UPDATE CURSOR, WHERE CURRENT of clause and CURSOR variables.
Example: 1
Create a PL/SQL program using cursors, to retrieve first tuple from the department relation.
(use table dept(dno, dname, loc))
Program
declare
vdno dept.deptno%type;
vdname dept.dname%type;
vloc dept.loc%type;
cursor c1 is select * from dept;
or // cursor c1 is select * from dept where rowno = 1;
begin
open c1;
fetch c1
into vdno,vdname,vloc;
dbms_output.put_line('vdno = ' ||vdno|| ' vdname = '||vdname||' vloc = '||vloc);
close c1;
end;
/
PS:
Cursors are used when the SQL select statement is expected to return more than 1 row.
A cursor must be declared and its definition contains a query and is defined in the DECLARE section of the program.
A cursor must be opened before processing and closed after processing. (Similar to how files are opened and closed in a C program).
Syntax to define a cursor:
CURSOR <CURSOR-NAME> IS <SELECT STATEMENT>
Syntax to open the cursor:
OPEN <CURSOR-NAME>
Syntax to store data in the cursor:
FETCH <CURSOR-NAME> INTO <VAR1>, <VAR2>, <VAR3>….
OR
FETCH <CURSOR-NAME> INTO <RECORD-NAME>
Syntax to close the cursor:
CLOSE <CURSOR-NAME>
Example: 2
Create a PL/SQL program using cursors, to retrieve each tuple from the department relation.
(use table dept(dno, dname, loc))
Program
declare
vdept dept%rowtype;
cursor c1 is select * from dept;
begin
for vdept in c1 loop
dbms_output.put_line('vdno = ' ||vdept.deptno|| ' vdname = '||vdept.dname||' vloc = '||vdept.loc);
end loop;
end;
/
PS:
The cursor for loop can be used to process multiple records. The advantage of cursor for loop is that the loop itself will open the cursor, read the records into the cursor from the table until end of file and close the cursor.
Syntax for cursor FOR LOOP:
FOR <VARIABLE> IN <CURSOR-NAME> LOOP
<STATEMENTs>
END LOOP;
Example: 3
Create a PL/SQL program using cursors, to display the number, name, salary of the three highest paid employees.
(use table emp(empno, ename,sal))
Program
declare
no emp.empno%type;
name emp.ename%type;
salary emp.sal%type;
cursor c1 is select empno, ename, sal from emp order by sal desc;
begin
open c1;
loop
fetch c1 into no,name,salary;
exit when c1 %notfound;
exit when c1 %rowcount >3;
dbms_output.put_line(no||name||salary);
end loop;
close c1;
end;
/
PS:
Cursors Attributes:
There are 4 cursor attributes used to provide information on the status of a cursor.
%NOTFOUND – To determine if a row was retrieved
Used after FETCH
NOTFOUND is TRUE if row is not retrieved
NOTFOUND is FALSE if row is retrieved
%FOUND – To determine if a row was retrieved.
Used after FETCH
FOUND is TRUE if row is retrieved
FOUND is FALSE if row is not retrieved
%ROWCOUNT – To determine the number of rows retrieved
ROWCOUNT is 0 when cursor is opened
ROWCOUNT returns the number of rows retrieved
%ISOPEN – To determine the cursor is open
ISOPEN is TRUE if a cursor is open
ISOPEN is FALSE if a cursor is not open
Example: 4
Create a PL/SQL program using cursors, to delete the employees whose salary is more than 3000.
Program
declare
vrec emp%rowtype;
cursor c1 is select * from emp where sal>3000 for update;
begin
open c1;
loop
fetch c1 into vrec;
exit when c1 %notfound;
delete from emp where current of c1;
dbms_output.put_line('Record deleted');
end loop;
close c1;
end;
/
PS:
In order to DELETE or UPDATE rows, the cursor must be defined with the FOR UPDATE clause.
Example: 5
Create a PL/SQL program using cursors, to update the salary of each employee by the avg salary if their salary is less than avg salary.
Program
declare
vrec emp%rowtype;
avgsal number(10,2);
cursor c1 is select * from emp for update;
begin
select avg(sal) into avgsal from emp;
for vrec in c1 loop
if vrec.sal < avgsal then
vrec.sal := avgsal;
update emp set sal = vrec.sal where current of c1;
dbms_output.put_line('Record updated');
end if;
end loop;
end;
/
PS:
Variable Attributes:
%TYPE - is used in PL/SQL to declare a variable to be of the same type as a previously declared variable or to be of the same type as a column in a table.
TOTBASIC SALARY.BASIC%TYPE;
will declare TOTBASIC of the same type as BASIC column from the table SALARY.
%ROWTYPE – declares a variable which is actually a record which has the same structure as a row from a table.
SALREC SALARY%ROWTYPE;
will declare SALREC as a record variable equivalent to the row from the table SALARY.
Example: 6
Create a PL/SQL program using cursors, to insert into a table, NEWEMP, the record of ALL MANAGERS. Also DISPLAY on the screen the NO, NAME, JOIN_DATE. Handle any user defined exceptions.
(use table emp(emp_no, emp_name, join_date, desig))
Program
set serveroutput on
declare
ctr number(2) := 2;
dno number(4);
dname varchar2(30);
ddate date;
cursor cur_mgr is
select emp_no, emp_name, join_date
from emp
where upper(desig) = ‘MGR’;
no_manager_found exception;
begin
open cur_mgr;
loop
fetch cur_mgr
into dno, dname, ddate;
exit when cur_mgr%notfound;
ctr := ctr + 1;
dbms_output.put_line(ctr || ‘Record inserted into NEWEMP’);
dbms_output.put_line(dno || ‘ ‘ || dname || ‘ ‘ ddate);
insert into new emp
values (dno, dname, ddate);
end loop;
if cur_mgr%rowcount = 0
then
close cur_mgr;
raise no_manager_found;
end if;
dbms_output.put_line(‘TOTAL number of records’ || ctr);
close cur_mgr;
exception
when no_manager_found then
dbms_output.put_line(‘NO RECORS FOUND’);
end;
/
Exercises:
1) Create a PL/SQL program using cursors, to insert into a table, NEWEMP, for any designation input by the user from the keyboard. Handle any user defined exceptions.
2) Code a program to calculate Tax for any employee whose number is input from the keyboard. Display appropriate error message if data does not exist in the table.
WEEK 11 (PL/SQL)
20) (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.
21) Develop a program that includes the features NESTED IF, CASE and CASE expression. The program can be extended using the NULLIF and COALESCE functions.
22) Program development using WHILE LOOPS, numeric FOR LOOPS, nested loops using ERROR handling, BUILT IN exceptions, USER defined exceptions, RAISE APPLICATION ERROR.
23) Program development using creation of procedure, passing parameters IN and OUT procedures.
24) Program development using creation of stored function, invoke functions in SQL statements and write complex functions.
25) Program development using creation of package specification, package bodies, private objects, package variables and cursors and calling stored packages.
26) Develop programs using features of parameters in a CURSOR, FOR UPDATE CURSOR, WHERE CURRENT of clause and CURSOR variables.
Example: 1
Code a procedure to calculate the sales made to a particular customer.
{ create table trn (itmid number(10),
cstid number(10),
trnqty number(10));
create table itmmast (itmid number(10),
itmprice number(10,2));
create table cstmast ( cstid number(10),
name varchar2(30));}
Step 1: Open the editor
Step 2: Type the code below in a file named, TOTSALES.
Program
CREATE OR REPLACE PROCEDURE TOTSALES
(CID IN CSTMAST.CSTID%TYPE, SAL OUT NUMBER)
IS
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
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;
/
Step 3: Save the TOTSALES.SQL file.
Step 4: Return to SQL Prompt and compile as
SQL> start TOTSALES; (press enter)
Step 5: On the screen you will get the message Procedure created. If you have errors type
SQL> show errors
Step 6: To execute the procedure at SQL prompt type
SQL> variable sl number
SQL> execute totsales(2001, :sl)
SQL> print sl
PS:
Procedural Objects
Groups of SQL and PL/SQL statements can be stored in the database. The code stored once in the database can be used by multiple applications. Since the code is in the database, which is in the server, processing is faster.
Procedures and functions are also referred to as sub-programs as they can take parameters and be invoked.
Various types of procedural objects are: Procedures, Functions, Packages.
Procedures:
Procedures are sub-programs, which will perform an action and functions are subprograms that are generally coded to compute some value.
The clients execute the procedure or function and the processing is done in the server.
Procedures can receive and return values from and to the caller.
Communication is passed to a procedure through a parameter and communication is passed out of a procedure through a parameter.
When calling a procedure, the parameters passed can be declared to be IN, OUT or IN OUT.
The IN parameter is used to pass values to the procedure being called. It behaves like a constant inside the procedure, i.e., cannot be assigned values inside the procedure.
The OUT parameter is used to pass values out of a procedure to the caller of the procedure. It behaves like a uninitialized variable inside the procedure.
The IN OUT parameter is used to pass values to the procedure being called and it is used to pass values to the caller of the procedure. The IN OUT variable behaves like a regular variable inside the procedure.
Functions:
Functions are also a collection of SQL and PL/SQL code which can return a value to the caller.
Unlike procedures, functions can return a value to the caller. This value is returned through the use of the RETURN keyword within the function. A function can return a single value to the caller. Functions do not allow the OUT and IN OUT arguments.
Packages:
Packages are groups of procedures, functions, variables and SQL statements in a single unit.
It consists of the package definition/specification and package body.
A package specification consists of the list of functions, procedures, variables, constants, cursors and exceptions that will be available to users of the package.
A package body consists of the PL/SQL blocks and specifications for all of the public objects listed in the package specification. It may also include code that is run every time the package is invoked, regardless of the part of the package that is executed.
The name of the package body should be the same as the name of the package specification.
To delete procedural objects:
SQL> drop procedure <procedure-name>
SQL> drop function <function-name>
SQL> drop package<package-name>