PL/SQL

PL/SQL:

• PL/SQL.

• Block Structure approach. 

• A simple PL/SQL Block.

• Executing statements and PL/SQL blocks from SQL*Plus. 

• Types of blocks in PL/SQL.

• Comments in PL/SQL. 

• Variables in PL/SQL.

• Handling variables in PL/SQL.  

• Types of Variables in PL/SQL.

• Operators in PL/SQL.

• Assigning values to a variable. 

• Declaring PL/SQL Variables. 

• Debugging statement. 

• Variable scope and Nested Blocks.

• How to convert the anonymous block to label block. 

Writing Control Structures:

• Writinng Control Structures.

• IF-THEN-END IF Statement.

• IF..THEN…ELSE…END IF Statement.  

• Behaviour of NULL’s. 

• Branching with Logical connectivity’s.

• ELSIF Statements. 

• CASE Expressions. 

• Labeled CASE Statements. 

Loops (Iterations in PL/SQL):

• Loops. 

• Simple Loop. 

• EXIT Statement. 

• Nested Loops.

• Nested loops and labels. 

• GOTO and Labels.

• Restrictions on GOTO. 

• WHILE Loops. 

• FOR loop. 

• Nested FOR loops.

Cursor Management:  

• Cursor Management.

• Types of Cursors.

• Cursor Attributes.

• Explicit Cursor and Implicit FOR Loops.

• How cursor will work. 

• Write a PL/SQL Cursor to Hike the employee salary as following conditions   i).If Employee salary is 0 to 1000 hike 5%.   ii).If Employee salary is 1001 to 2000 hike 25%.   iii).Other then this two conditions hike 35% of the all employees. (Display the Employee name,hiked sal as a outpu).

• Write a PL/SQL Cursor to Hike the employee salary following conditions.  i).If Employee job is ‘CLERK’ hike the salary 100.  ii).If Employee job is ‘SALESMAN’ hike the salary 200.  iii).Other then the this two conditions hike the salary 300. (Display the output as employee name, job and updated salary).

• Write a PL/SQL Cursor to Hike the commission with the following conditions.  i).If commission is null value give the commission as 300.  ii).If commission is 0 give the commission as 250.  iii).Other then this two conditions hike the 35%. (Display the output as Employee name, Updated Commission.).

 Write a PL/SQL Cursor to display Employee number, Employee name, Salary, Basic HRA, DA, PF, Gross Salary and Department of employees and display the number of employees are eligible for the PF. (Hint: Those Employees having a salary above 4000 is eligible for the PF.).

• Write a PL/SQL Cursor to check the user authentication.

• Cursor with For loops.

• Parametric cursor. 

• Methods of Opening a parametric Cursor. 

• Write a PL/SQL Cursor to display the employee details according to the department table department number. Every department details should be separate with ‘--------------’.

• Write a PL/SQL Cursor to display the employee details according to the department table department number. Every department details should be following conditions.   i)  If Employee HRA salary is basic*0.45.  ii) If Employee  DA salary is basic*0.35.  iii)If Employee PF salary is basic*0.15.

• FOR UPDATE Clause And Using WHERE CURRENT OF clause.

• Write a PL/SQL Program to display the employee details and any employees work in 50 department remove that employees and display the removing employees with locking system.

• Write a PL/SQL Program to display the department number, department name, location and number of employees. The department which is having more than three employees. 

• Dynamic cursor Ref Cursor or Cursor Variable.

• Types of cursor variable.

• Write a PL/SQL Cursor to display the different active sets at run time according to the department number.  i).If department number 10 display employee name, job, salary of the 10 department.  ii).If department number 20 display employee name, job, salary of the 20 department.    iii).Other than this 2 departments display employee name, salary, department number of other departments. 

Exception Handling:

• Exception Handling.

• Trapping Exceptions.

• The Others Exception Handler and Exception Guidelines. 

• Types of Exceptions.

• Predefined Exception.

• User-defined Exception.

• Using a packaged procedure.

• Write a PL/SQL Program to hike the employee salary as 35% if entered employee salary not less than(<) 2000. If salary is less than(<) 2000 display the message and suspend the program process.

• Write a PL/SQL Program to hike the i.Employee salary as 35% if employee having a salary. ii.If employee not having a salary rise the user exception and give the salary 3000.

• Trapping Non-Predefined oracle server errors.

Understanding PL/SQL Collections:

• Understanding PL/SQL Collections.

• Defining PL/SQL Record.

• PL/SQL Tables.

• Referencing PL/SQL Table.

• Write a PL/SQL Program to i.Hike the Employee salary as 100 if employee salary is in between 0 to 1000.  ii.Hike the Employee salary as 200 if employee salary is in between 1001 to 2000.   iii.Other than these two condition hike 300 insert the update employee details into the those table.

• Nested type Collection.

• Clauses In PL/SQL.

• Difference between Anonymous and Named PL/SQL block.

• Subprograms in PL/SQL.