Oracle Database 19c: Fundamentals of PL/SQL - course RUB 71,990. from Specialist, training 40 academic hours, date: May 7, 2023.
Miscellaneous / / December 04, 2023
This course is intended for users of Oracle Database 19c. The course introduces students to the PL/SQL language and helps them understand the basic capabilities of this powerful programming language. Students will learn how to create PL/SQL code blocks, procedures, functions, packages and database triggers, handle errors, which can occur when executing program code and embed SQL commands into program units of code PL/SQL.
Oracle is the most popular database used by most large companies, banks and government agencies. Oracle knowledgeable professionals are in demand everywhere. The course is suitable for versions of Oracle Database 12c, 18c, 19c and is intended for technical support engineers, technical administrators, data warehouse administrators familiar with the basics of working with Oracle. Training is conducted on the Linux platform, but experience with Linux is not required.
The course is intended for application developers, database administrators, report developers, and business analysts. The course is taught in Russian!
You will learn:
- Create anonymous PL/SQL blocks, stored procedures and functions.
- Declare variables and handle exceptions.
- Develop packages and triggers in the database, manage PL/SQL programs, their dependencies and use some additional Oracle packages.
- Use SQL Developer to develop program modules.
6
coursesPractical teacher, experienced Oracle DBMS specialist. He knows how to convey his wealth of experience to every listener and do it in simple and accessible language, using many practical examples. Grateful listeners note his ability to show a wide variety of nuances of working with the Oracle DBMS.
Mikhail Fokin has ten years of experience working with Oracle DBMS (versions 8, 8i, 9i, 10g, 11g), including administration experience and performance settings of the Oracle DBMS on Unix and Windows platforms, including in the Real Application configuration Clusters. Professionally fluent in SQL, PL/SQL languages. Fluent in database backup and recovery technologies - both using RMAN and manually (user managed backup and recovery). Provides reliable data storage and fault tolerance using Data Guard‚ RAC technologies. Resolves database performance issues and also performs tuning of SQL commands and PL/SQL code. In addition, he has seven years of experience teaching courses on Oracle DBMS administration, performance tuning, security, application development and reporting using Oracle Forms Developer and Oracle Reports.
Currently, he is an Oracle DBMS administrator at a large Russian bank.
4
courseExpert teacher of Oracle and Java courses. Oracle Certified Specialist, Candidate of Technical Sciences. He is distinguished by his diverse experience in practical and teaching activities. In 2003, Alexey Anatolyevich graduated with honors from MIREA. In 2006 year...
Expert teacher of Oracle and Java courses. Oracle Certified Specialist, Candidate of Technical Sciences. He is distinguished by his diverse experience in practical and teaching activities.
In 2003, Alexey Anatolyevich graduated with honors from MIREA. In 2006, he defended his PhD thesis on the topic of building secure automated information systems.
A major specialist in the field of database security, building secure java and web applications for Oracle DBMS and SQL Server, developing stored program modules in PL/SQL and T-SQL. Automated the activities of large state-owned enterprises. Provides consulting and advisory services in the development of complex distributed web applications based on the Java EE platform.
Alexey Anatolyevich’s teaching experience in the postgraduate education system exceeds 7 years. Worked with corporate clients, trained employees of the companies “BANK PSB”, “Internet University of Information Technologies (INTUIT)”, “SINTERRA”.
Author of several educational and methodological manuals on programming and working with databases. From 2003 to 2005, Alexey Anatolyevich was engaged in the adaptation and technical translation of foreign literature on web programming and working with databases. Published over 20 scientific papers.
Grateful graduates invariably note the accessible manner of presentation of even the most complex topics, detailed answers to questions from students, and the abundance of living examples from the teacher’s professional practice.
3
courseTeacher with 27 years of experience, Oracle expert, holder of international certifications Oracle, MySQL, Microsoft, IBM. Has extensive experience in programming in SQL, PL/SQL, Java, C, C++, R, Python, Unix / Linux administration,...
Teacher with 27 years of experience, Oracle expert, holder of international certifications Oracle, MySQL, Microsoft, IBM. Has extensive experience in programming in SQL, PL/SQL, Java, C, C++, R, Python, Unix / Linux administration, using the Git system, creating virtual machines (VMWare, Virtualbox, HYPER-V), administering Oracle databases, IBM Informix, configuring server hardware and RAID.
Since 1991, Ilya Andreevich has been teaching IT courses at Moscow State University, including their application in business. Its graduates include employees of the largest enterprises in the Russian Federation. He has authored many works in economic publications about trends in the use of IT in business. He also regularly conducts training to improve the skills of IT specialists and speaks at seminars.
Polite, with a sense of humor, patient, responsible - this is how students characterize their teacher. He knows how to convey his experience to every student in simple and accessible language, which makes him a truly high-quality teacher.
Module 1. Introduction (1 ac. h.)
Course Objectives
Course plan
Curriculum Charts Human Resources (HR)
Running PL/SQL code on a database server
Introduction to SQL Developer
Module 2. Introduction to PL/SQL (1 ac. h.)
What is PL/SQL
Benefits of PL/SQL Routines
Overview of PL/SQL Block Types
Benefits of PL/SQL
Overview of PL/SQL Block Types
Creating and executing a simple anonymous block
Generating output from PL/SQL blocks
Module 3. Declaring identifiers in PL/SQL (2 ac. h.)
Different types of identifiers in PL/SQL routines
Using the Declarations Section to Define IDs
Storing data in variables
Scalar data types
%TYPE attribute
Bound variables
Using Sequences in PL/SQL Expressions
Module 4. Writing executable operators (2 ac. h.)
Description of basic block syntax
Commenting code
SQL Functions in PL/SQL
Data type conversion
Nested blocks
Operators in PL/SQL
Module 5. Interaction with the Oracle database server (2 ac. h.)
Including SELECT statements in PL/SQL to retrieve data
Manipulating data on the server using PL/SQL
SQL Cursor Concepts
SQL Cursor Attributes for Feedback from DML Statements
Committing and rolling back transactions
Module 6. Writing control structures (2 ac. h.)
Conditional control using the IF statement
Conditional control using the CASE statement
Simple LOOP loop
WHILE loop
FOR Loop
CONTINUE operator
Module 7. Working with composite data types (2 ac. h.)
Using PL/SQL Records
Using the %ROWTYPE Attribute
Inserting and Updating Using PL/SQL Records
Associative tables INDEX BY and methods of working with them
Association tables INDEX BY with records
Module 8. Using explicit cursors (2 ak. h.)
Explicit cursor control
Cursor Definition
Opening the cursor
Retrieving data from the active set
Closing the cursor
Cursor FOR loops with subqueries
Explicit Cursor Attributes
Using FOR UPDATE and WHERE CURRENT OF clauses
Module 9. Exception handling (2 ac. h.)
Understanding Exceptions
Exception Handling in PL/SQL
Catching predefined Oracle server errors
Trapping non-predefined Oracle server errors
Catching user-defined exceptions
Exception Propagation
Using the RAISE_APPLICATION_ERROR procedure
Module 10. Creating stored procedures (2 ac. h.)
Modular and multi-level routine design
Modularize development with PL/SQL blocks
PL/SQL code execution environment
Benefits of Using PL/SQL Routines
Differences between anonymous blocks and subroutines
Creating, Calling, and Dropping Stored Procedures Using the CREATE Command and SQL Developer
Using parameters in procedures and different parameter modes
View procedure information in data dictionary views
Module 11. Creating stored functions (2 ac. h.)
Creating, calling, and deleting stored functions using SQL and SQL Developer commands
Benefits of Using Stored Functions in SQL Expressions
Steps to create a stored function
Using Custom Functions in SQL Expressions
Restrictions on calling functions from SQL expressions
Controlling side effects when calling functions from SQL expressions
Viewing information about functions in the data dictionary
Module 12. Creating packages (2 ac. h.)
Benefits of using packages
Description of packages
Package Components
Package development
Visibility of package components
Creating a package specification and body using SQL and SQLDeveloper commands
Calling package constructs
Viewing PL/SQL Source Code in the Data Dictionary
Module 13. Working with packages (2 ac. h.)
Overloading Batch Routines in PL/SQL
Using the STANDARD package
Using predeclaration to resolve references to program units not yet declared
Limitations on using batch functions in SQL
Packet stable state
Batch Cursor Steady State
Monitoring the purity level of stored PL/SQL functions
Using the RECORD data type with PL/SQL tables in batches
Module 14. Using standard Oracle packages in application development (2 ac. h.)
Overview of Standard Packages Provided by Oracle
Examples of some of the standard packages
How does the DBMS_OUTPUT package work?
Using the UTL_FILE package to work with operating system files
The UTL_MAIL package and the use of its routines
Module 15. Dynamic SQL and metadata (2 ac. h.)
Steps to Execute an SQL Command
What is dynamic SQL?
Declaration of cursor variables
Dynamic execution of a PL/SQL block
Using Native Dynamic SQL (NDS) to Compile PL/SQL Code
Using the DBMS_SQL package with a parameterized DML command
Functional completeness of dynamic SQL
Module 16. Recommendations for PL/SQL code design (2 ac. h.)
Standardization of Constants and Exceptions
Using Local Subroutines
Using Autonomous Transactions
Using the NOCOPY compiler hint
Using the PARALLEL_ENABLE compiler hint
Using cross-session caching of the result of PL/SQL functions
Using the DETERMINISTIC condition with functions
Using Bulk Linking to Improve Performance
Module 17. Creating database triggers (2 ac. h.)
Working with triggers
Determining the type of trigger, its response time and body
Business scenarios for using triggers
Creating DML Triggers Using the CREATE TRIGGER Command and SQL Developer
Defining the type of event that triggers, the body of the trigger, and the trigger time
Operator and line triggers
Creating INSTEAD OF triggers and disabling triggers
Maintaining, testing and removing database triggers
Module 18. Creation of combined (COMPOUND) triggers, DDL triggers and triggers triggered by system database events (2 ac. h.)
Working with Combination Triggers
Identifying the Runtime Section for a Table Combination Trigger
Combination trigger structure for tables and views
Using a combination trigger to read data from a mutating table
Comparison of Database Triggers and Stored Procedures
Creating triggers using DDL commands
Triggers triggered by events in the database
System privileges required to maintain triggers
Module 19. Using the PL/SQL compiler (2 ak. h.)
Using the PL/SQL Compiler
Setting initialization parameters that affect compilation of PL/SQL code
Categories of compiler warnings
Using Compile-Time Warnings in Subroutines
Benefits of Using Compiler Warnings
Categories of compiler warnings
Setting compiler warning levels using SQL Developer, the PLSQL_WARNINGS initialization parameter, or DBMS_WARNINGS package routines
View compiler warnings using SQL Developer, SQL*Plus, or data dictionary views
Module 20. Maintenance of PL/SQL code (2 ac. h.)
What is conditional compilation and how does it work?
Using selection directives.
Using predefined and custom polling directives.
PLSQL_CCFLAGS Parameter and Polling Directive
Using conditional compilation error directives to raise user-defined exceptions
Package DBMS_DB_VERSION
Using the DBMS_PREPROCESSOR procedure to print or retrieve source code after conditional compilation
Protecting source code in PL/SQL routines.
Module 21. Dependency maintenance (2 ac. h.)
Overview of dependencies between objects
View direct dependencies between objects using the USER_DEPENDENCIES view
Determining the status of objects
Invalidating dependent objects
View direct and indirect dependencies
Granular dependency management in Oracle 19c database
Removed dependencies
Recompiling PL/SQL Program Units