Mastering PL/SQL Through Illustrations

From Learning Fundamentals to Developing Efficient PL/SQL Blocks: From Learning Fundamentals to Developing Efficient PL/SQL Blocks (English Edition)

B Chandra

• Publisher: BPB Publications
• Year: 2020
• ISBN:
  9789389898484
• Topic: Computer Science
• Subtopic: Programming in SQL
• Index: Computer Science

Understand how to adopt and implement AI in your organization Key Features

• 7 Principles of an AI Journey
• The TUSCANE Approach to Become Data Ready
• The FAB-4 Model to Choose the Right AI Solution
• Major AI Techniques & their Applications:
• - CART & Ensemble Learning
  - Clustering, Association Rules & Search
  - Reinforcement Learning
  - Natural Language Processing
  - Image Recognition Description
  Most AI initiatives in organizations fail today not because of a lack of good AI solutions, but because of a lack of understanding of AI among its end users, decision makers and investors. Today, organizations need managers who can leverage AI to solve business problems and provide a competitive advantage. This book is designed to enable you to fill that need, and create an edge for your career. The chapters offer unique managerial frameworks to guide an organization's AI journey. The first section looks at what AI is; and how you can prepare for it, decide when to use it, and avoid pitfalls on the way. The second section dives into the different AI techniques and shows you where to apply them in business. The final section then prepares you from a strategic AI leadership perspective to lead the future of organizations. By the end of the book, you will be ready to offer any organization the capability to use AI successfully and responsibly - a need that is fast becoming a necessity. What will you learn
  
• Understand the major AI techniques & how they are used in business.
• Determine which AI technique(s) can solve your business problem.
• Decide whether to build or buy an AI solution.
• Estimate the financial value of an AI solution or company.
• Frame a robust policy to guide the responsible use of AI.
• 
  Who this book is for
  This book is for Executives, Managers and Students on both Business and Technical teams who would like to use Artificial Intelligence effectively to solve business problems or get an edge in their careers. Table of Contents
  1. Preface
  2. Acknowledgement
  3. About the Author
  4. Section 1: Beginning an AI Journey
  a. AI Fundamentals
  b. 7 Principles of an AI Journey
  c. Getting Ready to Use AI
  5. Section 2: Choosing the Right AI Techniques
  a. Inside the AI Laboratory
  b. How AI Predicts Values & Categories
  c. How AI Understands and Predicts Behaviors & Scenarios
  d. How AI Communicates & Learns from Mistakes
  e. How AI Starts to Think Like Humans
  6. Section 3: Using AI Successfully & Responsibly
  a. AI Adoption & Valuation
  b. AI Strategy, Policy & Risk Management
  7. Epilogue About the Authors
  Malay A. Upadhyay is a Customer Journey executive, certified in Machine Learning. Over the course of his role heading the function at a N. American AI SaaS firm in Toronto, Malay trained 150+ N. American managers on the basics of AI and its successful adoption, held executive thought leadership sessions for CEOs and CHROs on AI strategy & IT modernization roadmaps, and worked as the primary liaison to realize AI value on unique customer datasets. It was here that he learnt the growing need for greater knowledge and awareness of how to use AI both responsibly and successfully.
  Malay was also one of 25 individuals chosen globally to envision the industrial future for the Marzotto Group, Italy, on its 175th anniversary. Your Website/Blog links: www.TheUpadhyays.com
  

CHAPTER 1

Introduction to PL/SQL

PL/SQL stands for Procedural Language extensions to the Structured Query Language. PL/SQL can be easily integrated with SQL which is the most popular database language. PL/SQL adds procedural constructs to SQL for overcoming the limitations of SQL. It is a highly structured language and the PL/SQL blocks can be executed on systems that have an Oracle database. A block of statements can be directly stored in the database, which reduces traffic between the application and the database. PL/SQL provides an operating system independent programming environment.

List of topics

• PL/SQL blocks
• Variable declarations
• Data types in PL/SQL
• Online display statement
• Constants in PL/SQL
• Records in PL/SQL
• Retrieving data from database table and storing in a PL/SQL record
• % TYPE, % ROWTYPE and SELECT INTO statement
• Illustrative PL/SQL blocks

Objective

This chapter gives the description of data types in PL/SQL, variable declarations, and different forms of online display statement. All these have been illustrated through codes or blocks in PL/SQL. Record declaration has been illustrated through a PL/SQL block. Retrieval of data from a database table and storing in a record with the help of SELECT INTO statement is also given. %TYPE and %ROWTYPE have been vividly described and illustrated through PL/SQL blocks.

1.1 PL/SQL block

PL/SQL is extremely useful since it is developed by adding constructs of procedural languages in SQL. It is a structured procedural language. The basic unit in PL/SQL is a block. All PL/SQL programs are made up of blocks; the block-like structure is depicted below:

Figure 1.1

Variables are declared in the DECLARATION section. EXECUTION section has one or more executable statements and is enclosed between the keywords BEGIN and END. It can also have a section for handling errors called EXCEPTION SECTION which will be discussed in Chapter 5: Exceptions in PL/SQL. A typical PL/SQL block is given below:

DECLARATION SECTION

BEGIN

EXECUTION SECTION

EXCEPTION

EXCEPTION SECTION

END

Code 1.1 illustration of a simple PL/SQL BLOCK:

/*1.1*/

BEGIN

NULL;

END;

There are no declarations in this block. Declaration section is not mandatory in a PL/SQL block. There is only one executable statement NULL in this block. A NULL statement does nothing except that it passes control to the next statement. The output is as follows:

Output 1.1

1.2 Comments in PL/SQL

A single-line comment starts with a double hyphen (--) that can appear anywhere on a line and extends to the end of the line in a PL/SQL block and multiline comments in PL/SQL start with /* and end with */.

1.3 DBMS_OUTPUT statement for online display

DBMS_OUTPUT is a built-in package that provides a way for displaying information from a PL/SQL block to the screen. A package in PL/SQL consists of procedures and functions bundled together. User-defined packages have been explained in detail in Chapter 9: Packages. The most commonly used procedure of the built-in package DBMS_OUTPUT for display is PUT_LINE(). However, PUT() and NEW_LINE is also used.

The entire line of information is placed into the buffer by calling the PUT_LINE() procedure. One can build a line of information part by part by making multiple calls to PUT(). If the procedure PUT() is called, the end of line marker is added by calling NEW_LINE. Prior to all these procedures, it is necessary to mention the name of the package DBMS_OUTPUT.

A limit on the number of characters in the output can be set using the statement:

SET SERVEROUTPUT ON SIZE 10000;

In the above statement, a limit of 10000 bytes is set and if the output size is more than 10000 characters, an exception is raised. This may be given at the beginning of a PL/SQL block.

Code 1.3.1 PL/SQL block to illustrate the use of DBMS_OUTPUT.PUT_LINE()

/*1.3.1*/

DECLARE

BEGIN

--online display

Dbms_output.put_line(‘PL SQL Through Examples’);

END;

In the above block, there are no declarations but there is only one executable statement. The output is shown below:

Output 1.3.1

Code 1.3.2 PL/SQL block to illustrate the usage of DBMS_OUTPUT.PUT(), DBMS_OUTPUT.PUT_LINE() and DBMS_OUTPUT.NEW_LINE.

/*1.3.2*/

BEGIN

DBMS_OUTPUT.PUT(‘PL/’);

DBMS_OUTPUT.PUT(‘SQL’);

DBMS_OUTPUT.NEW_LINE;

DBMS_OUTPUT.PUT_LINE(‘Through’);

DBMS_OUTPUT.PUT_LINE(‘Examples’);

END;

PL/ and SQL are displayed on the same line since DBMS_OUTPUT.PUT()is used for online display of these. DBMS_OUTPUT.NEWLINE enables to pass control to a new line. On the next line, Through and Examples are displayed on...