PL/SQL stands for Procedural Language extensions to the Structured Query Language (SQL).PL/SQL is a combination of SQL along with the procedural features of programming languages.
Structure of PL/SQL Block
PL/SQL extends SQL by adding constructs found in procedural languages, resulting in a structural language that is more powerful than SQL. The basic unit in PL/SQL is a block. All PL/SQL programs are made up of blocks, which can be nested within each other.
Typically, each block performs a logical action in the program. A block has the following structure:
DECLARE declaration statements; BEGIN executable statements; EXCEPTIONS exception handling statements; END;
Data Types in PL/SQL
PL/SQL %TYPE Attribute
Oracle PL/SQL provides a special data type called the %TYPE data type. The %TYPE data type allows you to declare a variable that is associated with a column in a database table.
The Oracle PL/SQL %TYPE attribute allow you to declare a constant, variable, or parameter to be of the same data type as previously declared variable, record, nested table, or database column.
To use the PL/SQL %TYPE data type, you first need to declare a variable. You can then use the variable in your PL/SQL code just like any other variable.
The %TYPE attribute can be used with variables, records, nested tables, and database columns.
Here is an example of how to declare a variable using the PL/SQL %TYPE attribute:
declare v_name employee.name%TYPE; v_depid employee.deptid%TYPE:=31; begin select name into v_name from EMPLOYEE where deptid=v_depid; DBMS_OUTPUT.PUT_LINE('v_name: '||v_name); end;
Assignment No. 2
1.What is PL/SQL?
2.Explain PL/SQL Block.
3.Explain Data Types In PL/SQL.
4.Explain Control Structure in PL/SQL.