declare
name employee.fname%type;
sa employee.salary%type;
cursor c2 is select fname,salary from employee;
BEGIN
open c2;
update employee set salary=salary+(salary*0.2);
loop
fetch c2 into name,sa;
exit when c2%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(name||'having updated salary is ' ||sa);
end loop;
END;
declare
emp employee.fname%type;
ss employee.ssn%type:=&ssn;
begin
select fname into emp from employee where ssn=ss;
if sql%notfound then
DBMS_OUTPUT.PUT_LINE('no records found');
else
DBMS_OUTPUT.PUT_LINE('found');
end if;
end;
/
CREATE [OR REPLACE] FUNCTION function_name
[(parameter_name [IN | OUT | IN OUT] type [, ...])]
RETURN return_datatype
{IS | AS}
BEGIN
< function_body >
END [function_name];
CREATE OR REPLACE FUNCTION sailorscount
RETURN number IS
total number(2) := 0;
BEGIN
SELECT count(*) into total
FROM sailors;
RETURN total;
END;
/
CREATE OR REPLACE FUNCTION findMax(x IN number, y IN number)
RETURN number
IS
z number;
BEGIN
IF x > y THEN
z:= x;
ELSE
Z:= y;
END IF;
RETURN z;
END;
DECLARE
a number;
b number;
c number;
BEGIN
a:= &a;
b:= &b;
c := findMax(a, b);
dbms_output.put_line(' Maximum of (a,b): ' || c);
END;
/
DECLARE
num number;
factorial number;
FUNCTION fact(x number)
RETURN number
IS
f number;
BEGIN
IF x=0 THEN
f := 1;
ELSE
f := x * fact(x-1);
END IF;
RETURN f;
END;
BEGIN
num:= #
factorial := fact(num);
dbms_output.put_line(' Factorial '|| num || ' is ' || factorial);
END;
/
create or replace PROCEDURE findMin(x IN number, y IN number, z OUT number) IS
BEGIN
IF x < y THEN
z:= x;
ELSE
z:= y;
END IF;
END;
DECLARE
a number;
b number;
c number;
BEGIN
a:= 23;
b:= 45;
findMin(a, b, c);
dbms_output.put_line(' Minimum of (23, 45) : ' || c);
END;
/
DECLARE
a number;
PROCEDURE squareNum(x IN OUT number) IS
BEGIN
x := x * x;
END;
BEGIN
a:= 23;
squareNum(a);
dbms_output.put_line(' Square of (23): ' || a);
END;
/
CREATE [OR REPLACE] PROCEDURE procedure_name
[(parameter_name [IN | OUT | IN OUT] type [, ...])]
{IS | AS}
BEGIN
< procedure_body >
END procedure_name;
CREATE OR REPLACE PROCEDURE greetings
AS
BEGIN
dbms_output.put_line('Hello World!');
END;
/
EXECUTE greetings;
Drop procedure procedurename;
The CASE statement goes through conditions and returns a value when the first condition is met (like an if-then-else statement). So, once a condition is true, it will stop reading and return the result. If no conditions are true, it returns the value in the ELSE clause.
If there is no ELSE part and no conditions are true, it returns NULL.
CASE Syntax
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
WHEN conditionN THEN resultN
ELSE result
END;
DECLARE
grade char(1) := 'A';
BEGIN
CASE grade
when 'A' then dbms_output.put_line('Excellent');
when 'B' then dbms_output.put_line('Very good');
when 'C' then dbms_output.put_line('Well done');
when 'D' then dbms_output.put_line('You passed');
when 'F' then dbms_output.put_line('Better try again');
else dbms_output.put_line('No such grade');
END CASE;
END;
/
DECLARE
grade char(1) := 'B';
BEGIN
case
when grade = 'A' then dbms_output.put_line('Excellent');
when grade = 'B' then dbms_output.put_line('Very good');
when grade = 'C' then dbms_output.put_line('Well done');
when grade = 'D' then dbms_output.put_line('You passed');
when grade = 'F' then dbms_output.put_line('Better try again');
else dbms_output.put_line('No such grade');
end case;
END;
/
Write a PL/SQL program for generating Fibonacci series
declare
a number;
b number;
c number;
n number;
i number;
begin
n:=&n;
a:=0;
b:=1;
dbms_output.put_line(a);
dbms_output.put_line(b);
for i in 1..n-2
loop
c:=a+b;
dbms_output.put_line(c);
a:=b;
b:=c;
end loop;
end;
