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 "); |
Database Technologies > Oracle >