Programming in PL/SQL (Basic Concepts)

There are three parts in a PL/SQL program. They are - declaration section (where the variables are declared), begin section (where executable code is written) and exception section (where the exceptions are handled). The declaration and exception sections are optional but the begin part is compulsory. A PL/SQL program ends with an 'end' statement.

A PL/SQL record is a group of related data items. For example, the data about a student like roll no., name, date of birth are logically related to each other but have different types. A record containing a field for each item lets the user treat the data as a logical unit. Thus, making it easier to organise and represent. The syntax for creating a record is

type type_name is record (fielddeclaration1,fielddeclaration2...); 

There is no 'Do while' loop in PL/SQL. To write conditional loops, we can either use the 'simple' or the 'while' loop.

All PL/SQL 'select' statements must contain the 'into' keyword. The 'into' keyword is used to copy the values returned by a select statement into variables. For example, the statement - 'select bookno, bookname, price into bkno, bkname,bkprice from books where title='Oracle''- will copy the values of bookno, bookname and price columns in the variables bkno, bkname and bkprice, respectively. The 'into' clause is not used when the 'select' statement is used at the SQL prompt.

The %exists is not a valid cursor attribute. If used with a cursor name, it will result in compilation error.
<o:p>

A package is a collection of related PL/SQL objects like procedures, functions and triggers created as a single unit. It is used to store related objects in a single file. A package has two parts - Package specification and package body. The specification part contains the declaration of procedure, functions and triggers whereas the body part contains the actual implementation or code of all the subprogram declared in specification part.
<o:p>

A trigger is a PL/SQL block structure, which is fired when DML statements like Insert, Delete, Update are executed on a database table. 'Alter' is not a DML statement but a DDL statement that is used to alter or change the structure of a table like adding, dropping and resizing a column. We cannot write a trigger for a DDL command.

'If-else' is a condition statement that is used to write conditional statement in a PL/SQL program. For example, the following code will print a message depending on whether the value of A is greater, B is greater or both of them have the same values -
if a>b then
     dbms_output.put_line('A is greater');
elsif b>a then
     dbms_output.put_line('B is greater');
else
     dbms_output.put_line('Both are equal');
end if;

There is no OUTIN parameter in PL/SQL. Therefore, the program will return an error.

The CURSOR_ALREADY_OPEN exception is thrown when we are trying to open a cursor that is already open. A cursor cannot be opened twice without closing it first.

The difference between a function and procedure is that a function can return a value to its calling function while a procedure cannot return a value. Function returns a value using the 'return' keyword. A function cannot return more than one value.
<o:p>

The specification part does not contain the actual executable code of the functions and procedures but just their declaration. The actual code is written in the body part.

A trigger can be written on a view if it is declared using the 'instead of' keyword. The difference between a normal trigger and an instead of trigger is that when a normal trigger is fired, both the triggers and the firing DML statement like Delete, Insert or Update is also executed. On the other hand, in case of an instead of trigger the DML statements only invoke the trigger, they are not executed. A normal trigger is written against a table whereas an instead of trigger is written on a view.

A constant is a value used in a PL/SQL block that remains unchanged throughout the program. It is declared using the assignment operator ':='. We must assign a value to a constant at the time of declaration. If we do not assign a value to a constant while declaring it and try to assign a value in the execution section, we will get an error. 

The declaration section of a PL/SQL program is used to contain all variable declarations. While using a function, the declare keyword is replaced by 'is' keyword. All the variables are declared inside the 'is' keyword. For example,
CREATE OR REPLACE FUNCTION myfunc 
RETURN VARCHAR(20);
IS
sname VARCHAR(20);
BEGIN
SELECT studname INTO sname
FROM students WHERE rollno = 1;
RETURN sname;
/ 

PL/SQL is used to write individual statement in the form of a unit or program. When the program is executed, all the statements written in a complete logic are executed. On the other hand, SQL statements are executed one at a time when the user presses enter key to run the statement.

The 'case' statement is a conditional statement like the 'if-else' statement. It contains both the conditions and the statements that are processed if the condition is true. A 'case' statement can have as many 'cases' or statements as required. If all specified conditions in the 'case' statement are false, the control automatically transfers to the 'else' part. Although optional, the 'else' part will be processed if none of the conditions above is true.

Exception handling prevents the program from abnormal termination. If a program encounters an exception/run time error, the control moves to the exception part where it is handled using the 'when exception then' keyword. For example: To handle a user defined exception, the statement will be 
when no_data_found then
dbms_output.put_line('No Records Found');

PL/SQL provides a very useful feature called variable anchors. It refers to the use of keyword %TYPE to declare a variable with the same data type as the column data type in a table. For example, if we want to declare a variable named 'salary' with the same data type as of 'basic_salary' column of a table named 'employee' we can write the following statement:
salary employee.basic_salary%type;

There is no inheritance in subprogram and therefore it cannot be counted as one of its advantages.

ALTER is a DDL statement and triggers are written on execution of a DML statement. Therefore, we cannot create a trigger that fires when an Alter statement is issued.

Parameterized cursors are static cursors that can accept passed-in parameter values when they are opened. The mode of parameter passed to a cursor can only be IN. The syntax for opening a parameterized cursor is

open cursor_name(value)

Example:
open mycursor(1000);
where 1000 is the parameter value

If an implicit SELECT statement returns more than one row, it raises the TOO_MANY_ROWS exception. In this case, execution in the current block terminates and control is passed to the exception section. An implicit cursor is more prone to errors.

The name of a variable can contain $(dollar), _ (underscore) and # (pound).

The assignment operator in PL/SQL is := whereas the above code is using the '=' operator which is an equality operator. Therefore, this line will display an error.