Beginning Oracle Sql

How a Sample PL/SQL Block Looks

DECLARE       Variable declaration BEGIN       Program Execution  EXCEPTION       Exception handling END;

Place Holders:

Placeholders are temporary storage area. Placeholders can be any of Variables, Constants and Records.

Depending on the kind of data you want to store, you can define placeholders with a name and a datatype. Few of the datatypes used to define placeholders are as given below. 
Number (n,m) , Char (n) , Varchar2 (n) , Date , Long , Long raw, Raw, Blob, Clob, Nclob, Bfile

Variable declaration:

For example: The below example declares two variables, one of which is a not null.

DECLARE

salary number(4);

dept varchar2(10) NOT NULL := “HR Dept”;

Example: The below program will get the salary of an employee with id '1116' and display it on the screen.

DECLARE

 var_salary number(6);

 var_emp_id number(6) = 1116;

BEGIN

 SELECT salary

 INTO var_salary

 FROM employee

 WHERE emp_id = var_emp_id;

 dbms_output.put_line(var_salary);

 dbms_output.put_line('The employee '

        || var_emp_id || ' has  salary  ' || var_salary);

END;

/

NOTE: The backward slash '/' in the above program indicates to execute the above PL/SQL Block. 

Constant:

For example, to declare salary_increase, you can write code as follows:

DECLARE 

salary_increase CONSTANT number (3) := 10; 

RECORD DATA TYPE:

To declare a record, you must first define a composite datatype; then declare a record for that type.  The General Syntax to define a composite datatype is: TYPE record_type_name IS RECORD (first_col_name column_datatype, second_col_name column_datatype, ...); record_type_name – it is the name of the composite type you want to define. first_col_name, second_col_name, etc.,- it is the names the fields/columns within the record. column_datatype defines the scalar datatype of the fields. There are different ways you can declare the datatype of the fields.  1) You can declare the field in the same way as you declare the fieds while creating the table.  2) If a field is based on a column from database table, you can define the field_type as follows:  col_name table_name.column_name%type;

By declaring the field datatype in the above method, the datatype of the column is dynamically applied to the field.  This method is useful when you are altering the column specification of the table, because you do not need to change the code again.

NOTE: You can use also %type to declare variables and constants. 

The General Syntax to declare a record of a uer-defined datatype is:

record_name record_type_name;

The following code shows how to declare a record called employee_rec based on a user-defined type.

DECLARE TYPE employee_type IS RECORD (employee_id number(5),  employee_first_name varchar2(25),  employee_last_name employee.last_name%type,  employee_dept employee.dept%type);  employee_salary employee.salary%type;  employee_rec employee_type;

If all the fields of a record are based on the columns of a table, we can declare the record as follows:

record_name table_name%ROWTYPE;

For example, the above declaration of employee_rec can as follows:

DECLARE  employee_rec employee%ROWTYPE;