The PL/SQL block for Oracle Learning

1. Start simple

DECLARE
Name VARCHAR2 (20);
BEGIN
Select First_Name into name from Hr.employees where employee_id=&no;
Dbms_output.put_line (name);
EXCEPTION
When No_data_found Then
Dbms_output.put_line (' The employee does not exist! ');
END;
/

2. Simple---

DECLARE
TYPE Emp_record_type is record (
Name Emp.ename%type,
Salary Emp.sal%type
);
TYPE Ename_table_type is table of Emp_record_type;
Ename_table Ename_table_type;
BEGIN
SELECT ename,sal BULK COLLECT to ename_table from EMP;
For I in 1..ename_table. COUNT LOOP
--dbms_output.put_line (Ename_table (i). Name);
Dbms_output.put (Rpad (ename_table (i). name,20, "));
Dbms_output.put_line (Rpad (ename_table (i). salary,10, "));
END LOOP;
END;
/

The PL/SQL block for Oracle Learning