Database Technologies‎ > ‎Oracle‎ > ‎

Oracle PL/SQL

Procedures
Function
Anonymous Blocks
Packages
Triggers




Anonymous Blocks

set serveroutput on           --Print out line

DECLARE
eid NUMBER :-120;
F employees.first_name%TYPE;
l employees.last_name%TYPE;

BEGIN
SELECT first_name,last_name into f,l
FROM employees
WHERE employee_id=eid;
dbms_output.put_line('Employee ' || eid|| ':' ||f||'' || l);
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('No record for employee' ||eid||' found.');
END;


variable Declaration                      
<variable_name> <data_type>;              \\ Declaration.
<variable_name> <data_type> := <value> ;  \\ Initialization
<varable_name> <table_name>.<column_name>%TYPE \\Anchored data Type


Select first_name,last_name into f,l
from employees
where employee_id=eid;
dbms_output.put_line('Employee 



Above was anonymous block

Proceduer : 

PROCEDURE give_raises(r NUMBER)
AS
BEGIN
FOR  x  in 1..11 LOOP
update employees SET salary = salary*r
WHERE employee_id = (SELECT manager_id FROM departments
                     WHERE department_id=x*10)
END loop;
EXECPTION
WHEN no_data_found then
null;
END;


==

BEGIN
execute give_raises(1.15)
END;
==

DECLARE
r employee%rowtype                //Anchored row type, same columsn as employee table
BEGIN
SELECT * 
INTO R 
FROM employees
WHERE employee_id=206;
dbms_output.put_line(r.first_name||''||r.last_name);
END;
/

==

DECLARE
TYPE num_arr_type IS TABLE OF NUMBER;
eids num_arr_type;
BEGIN
eids := num_arr_type();
for x in 1..5 loop
eids.extend;
eids(x):=x.10;
dbms_output.put_line(eids(x));
end loop;
END;
/

===

DECLARE
TYPE num_arr_type IS TABLE OF NUMBER;
eids num_arr_type;

CURSOR emp_cur IS
SELECT employee_id
FROM employees
WHERE department_id in (50,80);

BEGIN
eids := num_arr_type();
FOR rec in emp_cur LOOP
eids.extend;
eids(eids.count):=rec.employee_id;
dbms_output.put_line(eids(eids.count));
END LOOP;
END;
/


=
Beging
for x in 1..10 reverse loop

end loop;
end;

declare
x number :=1;
begin
loop
--code--
x:=x+1;
exit when x=10;
end loop;
end;

--

declare 
x number :=1;
beging
while x<=10 loop
--some code--
x:=x+1;
end loop;
end;


==Exception Handling====

 

EXCEPTION
when no_data_found
dbms_output.putline("No data ");






Comments