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