This Course is for those who know SQL and some knowledge of Oracle database and want to learn and understand PL/SQL using Oracle database basic and advanced. At the end of each lesson, the students will be provided with hands-on exercises which will help increase their learning and analytical skills. I can assure that after completing this course, students can feel confident on writing simple as well as complex PL/SQL queries and how to approach in understanding the requirements of any data related operation within databases which is to be implemented and approach for the coding logic.

Students need to bring their laptops for hands-on exercises.

What You will learn at the end of this Course?

1. Explain the benefits of PL/SQL
2. Explain the need for PL/SQL
3. Identify the different types of PL/SQL blocks
4. Output messages in PL/SQL
5. Recognize valid and invalid identifiers
6. List the uses of variables
7. Declare and initialize variables
8. List and describe various data types
9. Identify the benefits of using the %TYPE attribute
10. Declare, use, and print bind variables
11. Identify lexical units in a PL/SQL block
12. Use built-in SQL functions in PL/SQL
13. Describe when implicit conversions take place and when explicit conversions have to be dealt with
14. Write nested blocks and qualify variables with labels
15. Write readable code with appropriate indentation
16. Use sequences in PL/SQL expressions
17. Determine the SQL statements that can be directly included in a PL/SQL executable block
18. Manipulate data with DML statements in PL/SQL
19. Use transaction control statements in PL/SQL
20. Make use of the INTO clause to hold the values returned by a SQL statement
21. Differentiate between implicit cursors and explicit cursors
22. Use SQL cursor attributes
23. Identify the uses and types of control structures
24. Construct an IF statement
25. Use CASE statements and CASE expressions
26. Construct and identify loop statements
27. Use guidelines when using conditional control structures
28. Create user-defined PL/SQL records
29. Create a record with the %ROWTYPE attribute
30. Create an INDEX BY table
31. Create an INDEX BY table of records
32. Describe the differences among records, tables, and tables of records
33. Distinguish between implicit and explicit cursors
34. Discuss the reasons for using explicit cursors
35. Declare and control explicit cursors
36. Use simple loops and cursor FOR loops to fetch data
37. Declare and use cursors with parameters
38. Lock rows with the FOR UPDATE clause
39. Reference the current row with the WHERE CURRENT OF clause
40. Define PL/SQL exceptions
41. Recognize unhandled exceptions
42. List and use different types of PL/SQL exception handlers
43. Trap unanticipated errors
44. Describe the effect of exception propagation in nested blocks
45. Customize PL/SQL exception messages
46. Differentiate between anonymous blocks and subprograms
47. Create a simple procedure and invoke it from an anonymous block
48. Create a simple function
49. Create a simple function that accepts a parameter
50. Differentiate between procedures and functions
51. REF Cursors
52. Working with Collections
53. Performance Tuning of PL/SQL codes.