Sample PL/SQL Code For Basics- 1

Sample PL/SQL Code For Basics

declare

v_depno departments.department_id%type;
v_location departments.location_id%type;
begin
select department_id, location_id into v_depno, v_location from departments where department_id=&depno;
dbms_output.put_line ('The Deptno is' ( v_depno));
dbms_output.put_line (v_location);
end;
/

Cursor

declare

v_id employees.employee_id%type;
v_name employees.last_name%type;
cursor emp_cursor is select employee_id,last_name from employees;
begin
open emp_cursor;
for i in 1..10 loop
fetch emp_cursor into v_id,v_name;
dbms_output.put_line (to_char(v_id)' 'v_name);
end loop;
close emp_cursor;
end;
/

Cursor Parameter

DECLARE
CURSOR emp_cursor
(p_deptno NUMBER, p_job VARCHAR2) IS SELECT employee_id, last_name FROM employees WHERE department_id = p_deptno AND job_id = p_job;
i emp_cursor%rowtype;
begin
open emp_cursor('&Depno','&Jobno');
fetch emp_cursor into i;
dbms_output.put_line (i.employee_id' 'i.last_name);
close emp_cursor;
end;
/

Exceptions:

/* Prdefined Exception*/


DECLARE
v_num number;
begin
select result into v_num from messages;
exception
when no_data_found then insert into messages values(100);
end;
/

*********

/* Non-Prdefined Exception*/

DECLARE

err_num NUMBER;
err_msg VARCHAR2(100);
emp_remaining exception;
pragma exception_init (emp_remaining, -2292);

BEGIN
delete from employees where department_id=&deptno;
EXCEPTION
when emp_remaining then
dbms_output.put_line ('Cannot Remove dept' to_char(&deptno) 'Employee Exist');
WHEN others THEN err_num := SQLCODE;
err_msg := SUBSTR(SQLERRM, 1, 100);
INSERT INTO error VALUES (err_num, err_msg);
END;
/

***********

/* User defined Exception*/

DECLARE
e_exp exception;
begin
update employees set employee_id=&id where employee_id=&ent;
IF sql%notfound then raise e_exp;
end if;
exception
when e_exp then dbms_output.put_line ('Employee not exist');
end;
/

Index By Table

declare

type emp_table_type is table of employees%rowtype
index by binary_integer;
emp_table emp_table_type;
v_count number(3):=104;
begin
for i in 100..v_count
loop
select * into emp_table(i) from employees where employee_id=i;
end loop;
for i in emp_table.first..emp_table.last
loop
dbms_output.put_line (emp_table(i).last_name);
end loop;
end;

/