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 ");