OCA Oracle PL/SQL Developer Certified Associate (GL OCAD)

Das WIS.IHK ist ein etablierter, innovativer Weiterbildungsanbieter, der sich mit Energie und Leidenschaft der Aus- und Weiterbildung Erwachsener widmet. Seit über zehn Jahren steht das WIS.IHK Teilnehmern als kompetenter und starker Partner zur Seite und unterstützt sie dabei, sich erfolgreich ins Berufsleben einzugliedern und/oder weiterzuentwickeln.

Beschreibung des Seminars

Dieser Kurs zur Datenbankprogrammierung mit PL/SQL behandelt die Entwicklung von Stored Procedures, Stored Functions, Packages und Datenbanktriggern. Die Teilnehmer lernen die Verwaltung von PL/SQL-Programmeinheiten und Abhängigkeiten sowie den Einsatz einiger der von Oracle bereitgestellten Packages kennen.

Retrieving Data Using the SQL SELECT Statement
» List the capabilities of SQL SELECT statements
» Execute a basic SELECT statement

Restricting and Sorting Data
» Limit the rows that are retrieved by a query
» Sort the rows that are retrieved by a query
» Use ampersand substitution to restrict and sort output at runtime

Using Single-Row Functions to Customize Output
» Describe various types of functions available in SQL
» Use character, number, and date functions in SELECT statements

Using Conversion Functions and Conditional Expressions
» Describe various types of conversion functions that are available in SQL
» Use the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions
» Apply conditional expressions in a SELECT statement

Reporting Aggregated Data Using the Group Functions
» Identify the available group functions
» Describe the use of group functions
» Group data by using the GROUP BY clause
» Include or exclude grouped rows by using the HAVING clause

Displaying Data from Multiple Tables
» Write SELECT statements to access data from more than one table using equijoins and nonequijoins
» Join a table to itself by using a self-join
» View data that generally does not meet a join condition by using outer joins
» Generate a Cartesian product of all rows from two or more tables

Using Subqueries to Solve Queries
» Define subqueries
» Describe the types of problems that the subqueries can solve
» List the types of subqueries
» Write single-row and multiple-row subqueries

Using the Set Operators
» Describe set operators
» Use a set operator to combine multiple queries into a single query
» Control the order of rows returned

Manipulating Data
» Describe each data manipulation language (DML) statement
» Insert rows into a table
» Update rows in a table
» Delete rows from a table
» Control transactions

Using DDL Statements to Create and Manage Tables
» Categorize the main database objects
» Review the table structure
» List the data types that are available for columns
» Create a simple table
» Explain how constraints are created at the time of table creation
» Describe how schema objects work

Creating Other Schema Objects
» Create simple and complex views
» Retrieve data from views
» Create, maintain, and use sequences
» Create and maintain indexes
» Create private and public synonyms

Modul/10/Oracle Database Administration PL-SQL

Oracle Database/11/: Advanced PL/SQL

PL/SQL Programming Concepts: Review
» List restrictions on calling functions from SQL expressions
» Hand exceptions
» Manage dependencies
» Use Oracle-supplied packages

Designing PL/SQL Code
» Identify guidelines for cursor design
» Use cursor variables
» Create subtypes based on existing types

Working with Collections
» Create collections (nested table, array and associative arrays, PL SQL tables)
» Use collections methods
» Manipulate collections
» Distinguish between the different types of collections and their uses

Using Advanced Interface Methods
» Execute external C programs from PL/SQL
» Execute Java programs from PL/SQL

Implementing Fine-Grained Access Control for VPD
» Explain the process of fine-grained access control
» Implement and test fine-grained access control

Manipulating Large Objects
» Create and manage LOB data types
» Use the DBMS_LOB PL/SQL package
» Use of temporary LOBs

Administering SecureFile LOBs
» Describe SecureFile LOB features
» Enable SecureFile LOB deduplication, compression, and encryption
» Migrate BasicFile LOBs to the SecureFile LOB format

Performance and Tuning
» Use native and interpreted compilation methods
» Tune PL/SQL code
» Enable intraunit inlining

Improving Performance with Caching
» Improve memory usage by caching SQL result sets and using the DBMS_RESULT_CACHE package
» Write queries that use the result cache hint
» Set up PL/SQL functions to use PL/SQL result caching

Analyzing PL/SQL Code
» Run reports on source code
» Determine identifier types and usages
» Use DBMS_METADATA to retrieve object definitions

Profiling and Tracing PL/SQL Code
» Trace PL/SQL program execution
» Profile PL/SQL applications

Safeguarding Your Code Against SQL Injection Attacks
» Describe SQL injections
» Reduce attack surfaces
» Use DBMS_ASSERT
» Design immune code
» Test code for SQL injection flaws

Direkt zum Seminaranbieter