PL/SQL syllabus : Latest Updated Syllabus for syllabus

Fundamentals

The small-scale characteristics of the language are the emphasis of this chapter. PL/SQL contains a character set, reserved words, punctuation, data types, hard syntax, and fixed rules of usage and statement creation, just like any other programming language. These PL/SQL fundamentals are used to represent real-world objects and operations.

Some parts of the syllabus are:

• Character Sets.
• Lexical Units.
• Declarations.
• References to Identifiers.
• Scope and Visibility of Identifiers.
• Assigning Values to Variables.
• Expressions.
• Error-Reporting Functions.

Defining Variables and Datatypes

A suitable data type specifies a storage format, constraints, and a valid range of values for PL/SQL variables, constants, and parameters. In this chapter, we'll look at the SCALAR and LOB data types. The other two forms of data will be discussed in later chapters.

Some parts of the syllabus are:

• Using Variables in PL/SQL

• Recognizing PL/SQL Lexical Units

• Recognizing Data Types

• Using Scalar Data Types

• Writing PL/SQL Executable Statements

Exception Handling

In PL/SQL, an error that happens during the execution of a programme is referred to as an exception. PL/SQL allows programmers to catch such errors using an exception block in the programme, and then take appropriate action to correct the problem. There are two types of exceptions: 1) exceptions to the rule and 2) exceptions to the rule. Exceptions that are defined by the system.

Some parts of the syllabus are:

• Handling Exceptions

• Trapping Oracle Server Exceptions

• Trapping User-Defined Exceptions

• Recognizing the Scope of Exceptions

SQL in PL/SQL

SQL is combined with procedural elements of programming languages to form PL/SQL. Oracle Corporation created it in the early 1990s to improve SQL's capabilities. Along with SQL and Java, PL/SQL is one of the three primary programming languages incorporated in the Oracle Database.

Some parts of the syllabus are:

• Review of SQL DML

• Retrieving Data in PL/SQL

• Manipulating Data in PL/SQL

• Using Transaction Control Statements

Packages

A package is a schema object that groups PL/SQL types, variables, constants, subprograms, cursors, and exceptions that are logically connected. A package is created and saved in a database, where it can be shared by multiple apps.

Triggers

Triggers are stored programmes that are executed or triggered automatically when certain events occur. Triggers can be set on the table, view, schema, or database connected with the event.

Some parts of the syllabus are:

• Introduction To Triggers

• Creating DML Triggers

• Creating DDL and Database Event Triggers

• Managing Triggers

PL/SQL Compiler

Before being executed, PL/SQL is compiled into bytecode and loaded into the server. Native compilation translates PL/SQL stored procedures into native code shared libraries that are linked to the kernel, resulting in procedural code performance gains.

Composite Data Types

Internal components of composite types, such as the elements of an array, record, or table, can be separately changed. The Oracle TimesTen In-Memory Database supports the composite data types listed below: array of associative elements (index-by table)

• User-Defined Records

• Indexing Tables of Records

Cursors and Parameters

When parameterized cursors are opened, they can accept parameter values that have been provided. A parameterized cursor is used in the following example. The cursor displays the name and salary of every employee in the EMP database whose salary is less than a parameter value supplied.

Dependencies

A dependency (in PL/SQL) is a pointer from a stored programme to an external database item. Tables, views, types, procedures, functions, sequences, synonyms, object types, package specifications, and other variables can be used in server-based PL/SQL scripts.

1

PL-SQL (Procedure Language – SQL)

PL-SQL (Procedure Language – SQL)

Oracle's procedural language extension to SQL is known as PL/SQL. SQL statements can be mixed with procedural constructs in PL/SQL. Procedures, functions, and packages are only some of the PL/SQL programme units that can be defined and executed.

2

Advanced PL/SQL

Advanced PL/SQL

The course prepares students to create stored PL/SQL applications, which include functions, procedures, packages, and triggers.

The Oracle DBMS's commonly used prepackaged packages are investigated. The use of these packages as part of in-house applications is being discussed. Overloading programmes, collections (arrays), bulk processing, and autonomous transactions are among the PL/SQL features covered.

3

EXCEPTIONS in PL/SQL

EXCEPTIONS in PL/SQL

In PL/SQL, an error that happens during the execution of a programme is referred to as an exception. PL/SQL allows programmers to catch such errors using an exception block in the programme, and then take appropriate action to correct the problem. There are two types of exceptions: 1) exceptions to the rule and 2) exceptions to the rule. Exceptions that are defined by the system.

4

Database Triggers in PL/SQL

Database Triggers in PL/SQL

When a defined event occurs, the Oracle engine immediately calls the trigger. Triggers are stored programmes that are executed or triggered automatically when a certain event happens.

5

File Input/Output

File Input/Output

PL/SQL blocks, subprograms, packages, and triggers can all use the DBMS_OUTPUT package to show output, debug information, and send messages.

6

Implementing Object Technology

Implementing Object Technology

In Oracle, PL/SQL allows you to define an object type, which aids in the creation of object-oriented databases. Objects enable you to create real-world objects with a defined data structure and ways of operating them. Attributes and methods are features of objects.

7

Using LOBS & Collections

Using LOBS & Collections

Internal and exterior LOBs are the two types of LOBs. Internal LOBs (BLOBs, CLOBs, and NCLOBs) are kept in the database and can be used in database transactions. External LOBs (BFILEs) are binary data saved outside of the database tablespaces in operating-system files.

Arrays, bags, lists, stacked tables, sets, and trees are all examples of collection types used in programming. The PL/SQL datatypes TABLE and VARRAY, which allow you to construct nested tables, associative arrays, and variable-size arrays, can be used to model these types in database systems.

8

DBA CONCEPTS

DBA CONCEPTS

The following tasks can be expected of DBAs: Oracle Database software installation, upgrades, and patching. Identifying needs, designing the logical design (conceptual model), and physical database architecture are all part of database design. Oracle database creation.

Oracle SQL

• Data Definition Language (DDL) Statements
• Data Manipulation Language (DML) Statements
• Transaction Control Statements
• Session Control Statements
• System Control Statement
• Embedded SQL Statements

The Structured Query Language (SQL) is a collection of statements that all Oracle programmes and users utilise to retrieve data. Users can access the database using application programmes and Oracle tools without using SQL directly, but these applications must use SQL to execute the user's request.

Oracle Database

• Introduction to Oracle Database. About Relational Databases
• Tables and Table Clusters
• Indexes and Index-Organised Tables
• Partitions, Views, and Other Schema Objects
• Data Integrity.
• Data Dictionary and Dynamic Performance Views.
• SQL.
• Server-Side Programming: PL/SQL and Java.

The first database was specifically designed for corporate grid computing. It is also one of the most cost-effective and versatile methods of managing data and applications. Large pools of industry-standard, modular storage and servers are created via enterprise grid computing.

Oracle Developer

• Data Definition Language (DDL) Statements.
• Data Manipulation Language (DML) Statements.
• Transaction Control Statements.
• Session Control Statements.
• System Control Statement.
• Embedded SQL Statements.

SQL Developer includes a spreadsheet for running queries and scripts, a DBA console for administering the database, a reporting interface, a complete data modelling solution, and a migration platform for migrating your third-party databases to Oracle.

Database Design

• Inventory control management Database Project.
• Student Record Keeping System Database Project.
• Online Retail Application Database Project.
• College Database Project.
• Railway System Database Project.
• Hospital Management System Database Project.

Database design can be defined as data structuring, based on a database model. The designer decides what data should be saved and how the various data parts should interact. They can start fitting the data to the database model with this information. The data is managed via a database management system.

Database Management

• Declarative languages and runtime systems
• Scalable data analysis and query processing
• Consistency, concurrency, coordination, and reliability
• Data storage and physical design
• Metadata management
• Systems for machine learning and model management
• Data cleaning, data transformation, and crowdsourcing

A database management system (DBMS) is a piece of software that aids in the organisation, storage, and retrieval of information from a database. It entails several activities that work together to ensure that data is correct, accessible, and available.

SQL Server

• Performance Baselines and Benchmarks for Microsoft SQL Server. The Challenges of Database Performance Baselines and Benchmarks
• SQL Performance Tuning
• Detect and Resolve SQL Deadlocks
• SQL Trace

Microsoft's SQL Server is a relational database management system (RDBMS). It was created primarily to compete with the MySQL and Oracle databases. The standard SQL (Structured Query Language) language is supported by SQL Server.

Oracle PL/SQL

Oracle PL/SQL Programming 6th Edition

Steven Feuerstein

Steven Feuerstein, one of the most well-known figures in the world of Oracle PL/SQL programming and a well-respected expert, wrote this book. This book serves as a comprehensive guide to Oracle PL/SQL programming. This is the book to consult if you're having trouble comprehending a certain code, syntax, or keyword.

Oracle SQL

Murach's Oracle SQL and PL/SQL for Developers, 2nd Edition

Joel Murach

By Joel Murach, The Murach's Oracle SQL and PL/SQL for Developers. This book is essential reading for any new programmer learning SQL and working with Oracle databases such as Oracle 10g or Oracle 11g. It's chock-full of non-trivial examples and extremely readable in every way.

Oracle Database

Oracle Database 12c PL/SQL Programming 1st Edition

Michael McLaughlin

Michael McLaughlin's book is another excellent resource for learning Oracle database and PL/SQL programming. It's also one of the most recent publications on Oracle PL/SQL programming, covering Oracle 12c. It is full of principles and examples; this book has the additional information provided in the appendix section. This book can be used as a textbook for learning Oracle PL/SQL programming.

Oracle PL/SQL

Oracle PL/SQL Best Practices 2nd Edition

Steven Feuerstein

This is yet another gem by Steven Feuerstein and one of the must-read books for any seasoned or intermediate programmer familiar with the Oracle PL/SQL programming language. This book is jam-packed with tips on how to get the most out of PL/SQL, how to utilise things correctly, and best practices gleaned from Steven's years of expertise with Oracle PL/SQL programming.

1

NIIT Limited, Vile Parle East, Mumbai

2

NIELIT Srinagar

3

Aptech Computer Education, Dadar West, Mumbai

4

Karmick Institute Kolkata

5

Emerging India Analytics, Noida