ORACLE’s database systems are among the most powerful available. PL/SQL is Oracle’s proprietary language for building queries and reports from within Oracle databases. PL/SQL has many powerful customised features to make building queries and stored procedures efficient and robust.
This course provides an overview of their powerful PL/SQL language for new users. The course will explore the PL/SQL language and how it can be used to build program blocks to extract data from within the Oracle database.
Use the PL/SQL language to develop powerful Oracle server side scripts and stored procedures.
Language Features
Why use PL/SQL? Where PL/SQL program units may be deployed. The Java alternative to PL/SQL. Structure of a PL/SQL program block. PL/SQL from interactive tools. Generating output.
Declare Clause
Datatypes. Declare example. Special datatypes. %Type variables. %Rowtype record. Type table.
Begin Clause
Data manipulation. Char and Varchar. Numeric. Date. Boolean. Comparison Operators. Functions. Date calculations. Selectinto. Logic control & branching. Goto. Loop. If-Then-Else. Case.
Exception
Handling exception. Special features. System defined exceptions. Using Sqlcode and Sqlerrm. Implicit cursors. User defined Ex_ined. Pragma exception_init.
Explicit cursors
Understanding cursors. Explaining the cursor example. Declare Cursor E. Open cursor example. Fetch cursor example. Close cursor example. Including cursor parameters. Performing updates from cursors. Using Forloop cursors.
Advanced PL/SQL
Using nested blocks. Using declared subprograms. Display_message() procedure example. Get_spouse_name() function example.
IT Managers, Software Developers and Analysts requiring an introduction to Oracle and the PL/SQL language.
This two-day course provides an introduction to the PL/SQL database programming language. Topics include declaring variables and complex datatypes, developing logic in PL/SQL program blocks, fetching data from the database into program variables, returning program output to users, handling exceptions, using database cursors and using advanced features of nested blocks and subprograms.
“The trainer’s knowledge of the subject matter was impeccable and this, matched with the very relevant course materials, ensured the course was a big success. We would certainly use Software Paths again for training in the future.”
Kainos
