Udemy
    •  
    •  
    •  
    •  
    •  
    •  
    •  
    •  
Turn what you know into an opportunity and reach millions around the world.
Learn More
Your cart is empty.
Keep shopping
Oracle PL/SQL Fundamentals & Database Design--3course bundle
Rating: 4.5 out of 5(468 ratings)
2,475 students

Oracle PL/SQL Fundamentals & Database Design--3course bundle

The ultimate Oracle/database course bundle with over 20 hours and 100 videos!
Created byIntellezy Trainers
Last updated 1/2018
English
English [Auto],Spanish

What you'll learn

  • Over 100 lectures and 20 hours of content
  • Understand different types of databases
  • Relate tables with IDs
  • Understand normalization
  • Introducing Database Program Units
  • Creating and Maintaining Packages
  • Work with DECLARE, BEGIN & EXCEPTION clause
  • Understand and use nested blocks
  • Use explicit cursors

Course content

3 sections • 117 lectures • 21h 34m total length

  • Introduction3:06

    Set up your system, explore SQL Plus and SQL Developer, and learn declare, begin, and exception blocks, explicit cursors, and nested blocks for level 2 readiness.

  • Course Setup37:03

    Set up the Oracle PL/SQL environment by installing Oracle XE, configuring ports and system password, and using SQL Developer or SQL Plus to create and seed sample data with scripts.

  • Selecting the Appropriate Database Tool30:45

    Explore Oracle's built-in tools, set up database connections with host, port, and SID, and use bind variables to build dynamic scripts, while comparing SQL Developer, SQL Plus, and more.

  • Understanding PL SQL117:59

    Discover how pl sql merges the declarative nature of sql with procedural language within Oracle, offering portability, simple syntax, and flexible deployment via scripts, stored programs, objects.

  • PL SQL Blocks15:19

    Explore the structure of PL/SQL blocks, including declare, begin, exception, and end. Learn to comment code, terminate statements with semicolons, and use rollback for safe error handling.

  • Working with SQL Plus11:00

    Learn to use sql plus for pl sql blocks, declare and begin, manage ampersand variable substitution, set server output on, and use dbms_output with the period terminator.

  • DECLARE Clause Objects16:25

    Explore declare clause objects in Oracle PL/SQL, including variables, constants, records, tables, and user defined types; learn data types, simple vs complex, and safe naming rules.

  • PL SQL Available Data Types16:55

    Learn the available data types in Oracle pl/sql, from scalar integers and numbers to char and varchar2. Understand performance considerations and when to use rowid, urowid, booleans, and dates.

  • Declaring Simple Data Types and Assignments12:16

    Learn how to declare a simple data type in PL/SQL, set default values, enforce not null, and declare constants, using colon equals for assignment and handling errors with exceptions.

  • Declaring Complex Data Types and Assignments10:38

    Declare a variable with the type command to pull the data type from a table field, enabling dynamic, schema-sensitive scripts when working with complex data types.

  • Declaring Complex RowTypes and Assignments7:02

    Master using the percent rowtype to capture an entire table row as one variable, then access fields with dot notation (ssn, name, salary) for concise, one-statement assignments.

  • Declaring Complex Tables7:44

    Declare a complex table in PL/SQL by defining a table type and a variable of that type, then populate management and worker lists from select statements.

  • Declaring Complex Records8:12

    Declare a custom record to combine fields from multiple tables, declare its fields, create variables, and populate from queries with max and min hours using dbms output.

  • Creating and Declaring User-Defined Types8:48

    Create and declare user defined types to build hierarchical records, such as a bonus compensation with cash, a company car, and vacation weeks, used in employee and manager records.

  • What Can Be Included in the BEGIN Clause7:02

    Explore the begin clause as the processing center for manipulating Oracle data and declared objects, using DML, string literals, and logic control for branching and data manipulation.

  • Working with CHAR and VARCHAR2 Variables7:30

    Explore working with char and varchar2 variables in Oracle PL/SQL, including string literals, apostrophes, and concatenation with two pipes to build full names and use dbms_output.put_line.

  • Handling String Literals7:41

    Master handling string literals in Oracle PL/SQL by using q-literal strings to escape apostrophes. Build robust messages with cue literals and bang delimiters for values like the value isn't valid.

  • Working with Numeric Variables6:40

    Explore numeric variables and the colon equals assignment to ensure numeric values. Use arithmetic to calculate salary raises, prompting for salary and raise percent, and display current and new salaries.

  • Working with Date Variables6:00

    Understand how dates are stored as numeric values in Oracle, then manipulate them by adding days, using system date for current dates and declaring variables with the date type.

  • Assigning and Using Boolean Variables7:44

    Explore declaring and using boolean values in PL/SQL, including true, false, and null, with a default of false, and test expressions to drive if statements.

  • Using Comparison Operators7:24

    Learn to use comparison operators in PL/SQL, including equals, not equal, is null, like with %, in, and between, to evaluate strings in if statements.

  • Using SQL Functions6:04

    Explore using SQL functions within PL/SQL scripts to manipulate and format data, including MAX, MIN, UPPER, LOWER, and ROUND for flexible output and calculations.

  • SQL DML within PL SQL115:05

    Execute pl sql dml—insert, update, delete—and manage commit, rollback, savepoint, and set transaction to locate the least productive employee with inner joins and aliases and delete related records.

  • Using SELECT6:32

    Discover how the select into clause works in PL/SQL, matching fields to a record type and handling single-row results while noting the risks of multi-row returns.

  • Exceptions and Embedded SQL11:16

    Explore how Oracle PL/SQL handles select into exceptions, ensuring a single row and using summary functions to avoid no data found or too many rows.

  • Using Sequences14:28

    Create and use sequences in Oracle to generate unique values for a table's primary key, starting at 1 and advancing via nextval; automate with triggers and inserts.

  • Logic Control and Branching6:58

    Master logic control and branching in PL/SQL by using program labels, the goto statement, and loop structures. Explore if then else and case statements for smarter, well-structured code.

  • Using GOTO5:52

    Understand the go to statement as a jump in PL/SQL, using labels to control flow; declare the label first, ensure it exists, and place a command after it, like null.

  • Working with LOOPS18:53

    Learn to use loops in pl/sql, including for next loops, exit, exit when, continue, and end loop, with fixed and reverse iterations, nested loops, inserts, commits, rollbacks, and exception handling.

  • Creating and Using the Indefinite Loop6:59

    Discover how to use the indefinite loop in PL/SQL, with loop and exit when, and a practical example that runs until only four employees remain.

  • Creating and Using the Conditional Loop8:48

    Examine conditional loop in Oracle PL/SQL: runs indefinitely while a test is true, with a pre-test condition and updates to avoid execution; compare with for next and indefinite loops.

  • Using Nested Loops11:33

    Demonstrate nested loops with outer and inner for next loops to populate a new transactions table, using x and i indices and a mod-based buy/sell condition.

  • Using the If-Then-Else18:54

    Learn to implement if-then-else logic in pl/sql, test binary conditions, use else and else if for multiple branches, handle case sensitivity with upper or lower, and build nested conditions.

  • Using the CASE Statement17:43

    Learn how to replace complex if-then-else logic with Oracle PL/SQL's case statement, including selector and searched forms, conditions, and else handling.

  • What Are the Types of Exceptions5:31

    Identify and handle exceptions in pl/sql by learning the exception clause, trapping for errors, and creating user declared exceptions, while understanding implicit cursor attributes.

  • Trapping Exceptions5:32

    Identify how to trap errors in PL/SQL by using the when statement, testing single or multiple exceptions, and implementing a global handler with when others then.

  • Identify Exception Details10:57

    Identify exception details in Oracle PL/SQL by examining common errors like no data found, too many rows, and invalid cursor, and use when others with explicit and implicit cursors.

  • Creating User-Defined Exceptions10:49

    Create user defined exceptions in Oracle PL/SQL by declaring a named exception and binding it to a specific error code with pragma exception_init, then handle it in an exception clause.

  • Using SQLCODE and SQLERRM Objects8:29

    Explore using sqlcode and sqlerrm objects to handle and debug pl/sql errors. Compare when others with user defined exceptions, and craft informative error messages for debugging and support.

  • Understand the Implicit Cursor14:37

    Understand how Oracle automatically creates an implicit cursor for each SQL DML statement, exposing attributes like %FOUND, %NOTFOUND, %ROWCOUNT, and %ISOPEN to track affected rows in the session.

  • Creating User-Defined Events12:26

    Declare an exception object and raise it to create a user defined event that enforces business rules for salary updates.

  • Understanding the Concept and Purpose of Explicit Cursors18:10

    Explore explicit cursors in Oracle PL/SQL, declare and open a cursor, fetch rows sequentially, handle found and not found, and compare with DML, while recognizing advantages and limitations.

  • Learn about Cursor Attributes10:12

    Explore cursor attributes for explicit and implicit cursors, including is open, found, not found, and row count, with practical examples of opening, fetching, and closing cursors.

  • Using the Continue Statement5:44

    Apply the continue statement in PL/SQL loops to skip remaining code and restart at the top. Compare with exit, and weigh in-loop filtering versus a where clause for efficiency.

  • Working with Updateable Cursors19:25

    Explore updateable cursors in Oracle PL/SQL, using for update of, where current of, and commit handling to safely update salary data while managing locks and performance.

  • Creating and Using Cursor Parameters14:45

    Learn how cursor parameters make explicit cursors flexible by declaring typed parameters in the cursor, using them in the where clause, and passing values on open.

  • FOR...LOOP Cursors6:53

    The for loop cursor simplifies code by automatically handling open, fetch, exit, and close through a select statement that declares the cursor type, boosting performance.

  • Understanding Nested Blocks10:46

    Understand nested blocks in PL/SQL, differentiating outer and inner blocks and their labels and begin/end. Learn to handle exceptions inside inner blocks and reuse outer-declared variables.

  • Understanding the Difference between Global vs Local Objects and Exceptions12:35

    Explore global versus local objects and exceptions in nested blocks, learning how outer declarations become global while inner ones stay local, and how to handle exceptions that bubble up.

  • What Are Subprograms and How Are They Different from Nested Blocks17:37

    Explore subprograms in PL/SQL, compare them with nested blocks and stored program units, and learn modular design, procedure versus function, and syntax for declaring and calling.

  • Course Recap3:52

    Review key PL/SQL fundamentals, compare SQL Plus and SQL Developer, and master begin and declare blocks, exception handling, and explicit cursors to build robust applications.

Requirements

  • Basic Microsoft Windows training or equivalent experience and familiarity with database and programming principles

Description

Over 20 hours and 100 videos are included in this bundle.

Save 20% by purchasing 3 Oracle/Database courses. This bundle includes:

  • Oracle PL/SQL Fundamentals vol. I
  • Oracle PL/SQL Fundamentals vol. II
  • Database Design Instruction

In this Oracle 11g PL/SQL vol I & II courses, you will receive introduction training on PL/SQL database programming language covering syntax, structure and features of the language within the context of database applications and programming. In volume II students will dive into topics such as understanding the basic form and structure of program units stored within the database, building and maintaining stored procedures, functions, packaged programs, and database triggers. Additional topics include taking advantage of advanced programming techniques such as cursor variables and cursor expressions. This Oracle 11g course will prepare students for the Oracle certification exams (OCP).

The Database Design Instruction course familiarizes the student with techniques necessary to properly create and normalize a relational database. This single course can help prevent the new database developer from creating mistakes that add hours and days to a development project.  Information in this course is a must for anyone responsible for building and maintaining a database.

Who this course is for:

  • Application designers and developers
  • Students just getting started with designing databases and those who have been designing databases but looking for tips on more effective design

Report abuse