PL/SQL Tutorial : Learn PL/SQL in 7 Days

Last updated on 11th Jan 2022, Blog, Tutorials

(5.0) | 19874 Ratings 1796

Share:

• Introduction to PL/SQL
• PL/SQL architecture
• Introduction to PL/SQL data types
• Declaring variables
• PL/SQL IF THEN statement
• Introduction to PL/SQL NULL statement
• Prerequisites
• Conclusion

Introduction to PL/SQL:

• PL/SQL stands for “Procedural Language extensions to the Structured question Language”. SQL could be a well-liked language for each querying and change knowledge within the electronic database management systems (RDBMS). PL/SQL adds several procedural constructs to SQL language to beat some limitations of SQL. Besides, PL/SQL provides a additional comprehensive programing language answer for building mission-critical applications on Oracle Databases.


• PL/SQL could be a extremely structured and clear language. Its constructs categorical the intent of the code clearly. Also, PL/SQL could be a simple language to be told.


• PL/SQL could be a customary and moveable language for Oracle info development. If you develop a program that executes on associate degree Oracle info, you’ll be able to quickly move it to a different compatible Oracle info with none changes.


• PL/SQL is associate degree embedded language. PL/SQL solely will execute in associate degree Oracle info. it had been not designed to use as a standalone language like Java, C#, and C++. In alternative words, you can not develop a PL/SQL program that runs on a system that doesn’t have associate degree Oracle info.


• PL/SQL could be a superior and extremely integrated info language. Besides PL/SQL, you’ll be able to use alternative programming languages like Java, C#, and C++. However, it’s easier to put in writing economical code in PL/SQL than alternative programming languages once it involves interacting with the Oracle info. above all, you’ll be able to use PL/SQL specific constructs just like the FORALL statement that helps improve info performance.





PL/SQL architecture:

The following image illustrates the PL/SQL architecture:

PL/SQL design:

PL/SQL engine is answerable of compilation PL/SQL code into byte-code and executes the workable code. The PL/SQL engine will solely be put in in associate degree Oracle info server or associate degree application development tool like Oracle Forms.

Once you submit a PL/SQL block to the Oracle info server, the PL/SQL engine collaborates with the SQL engine to compile and execute the code. PL/SQL engine runs the procedural parts whereas the SQL engine processes the SQL statements. Now you must have a basic understanding of PL/SQL programing language and its design. Let’s produce the primary operating PL/SQL anonymous block.




PL/SQL anonymous block summary:

PL/SQL could be a block-structured language whose code is organized into blocks. A PL/SQL block consists of 3 sections: declaration, executable, and exception-handling sections. In a block, the workable section is necessary whereas the declaration and exception-handling sections area unit nonobligatory.

A PL/SQL block contains a name. Functions or Procedures is associate degree example of a named block. A named block is keep into the Oracle info server and may be reused later.

A block while not a reputation is associate degree anonymous block. associate degree anonymous block isn’t saved within the Oracle info server, therefore it’s only for one-time use. However, PL/SQL anonymous blocks may be helpful for testing functions.




1) Declaration section

A PL/SQL block contains a declaration section wherever you declare variables, portion memory for cursors, and outline knowledge varieties.




2) workable section

A PL/SQL block has associate degree workable section. associate degree workable section starts with the keyword BEGIN and ends with the keyword finish. The workable section should have a least one workable statement, notwithstanding it’s the NULL statement that will nothing.




3) Exception-handling section

A PL/SQL block has associate degree exception-handling section that starts with the keyword EXCEPTION. The exception-handling section is wherever you catch and handle exceptions raised by the code within the execution section.




Note a block itself is associate degree workable statement, thus you’ll be able to nest a block at intervals alternative blocks.

PL/SQL anonymous block example. The following example shows a straightforward PL/SQL anonymous block with one workable section.

• BEGIN
• DBMS_OUTPUT.put_line (‘Hello World!’);
• END;
• Code language: SQL (Structured question Language) (sql)
• The workable section calls the DMBS_OUTPUT.PUT_LINE procedure to show the “Hello World” message on the screen.



Execute a PL/SQL anonymous block using SQL*Plus:

Once you have got the code of associate degree anonymous block, you’ll be able to execute it using SQL*Plus, that could be a command-line interface for corporal punishment SQL statement and PL/SQL blocks provided by Oracle info.






Introduction to PL/SQL data types:

Each price in PL/SQL like a continuing, variable and parameter contains a knowledge kind that determines the storage format, valid values, and allowed operations. PL/SQL has 2 varieties of knowledge types: scalar and composite. The scalar varieties area unit varieties that store single values like variety, Boolean, character, and datetime whereas the composite varieties area unit varieties that store multiple values, as an example, record and assortment. This tutorial explains the scalar knowledge varieties that store values with no internal elements.




PL/SQL divides the scalar knowledge varieties into four families:

• Number
• Boolean
• Character
• Datetime

A scalar knowledge kind might have subtypes. A subtype could be a knowledge kind that’s a set of another knowledge kind, that is its base kind. A subtype any defines a base kind by limiting the worth or size of the bottom knowledge kind. Note that PL/SQL scalar knowledge varieties embrace SQL knowledge varieties and its own knowledge kind like Boolean.




Get JOB Oriented Oracle SQL/PLSQL Training for Beginners By MNC Experts
• Instructor-led Sessions
• Real-life Case Studies
• Assignments
Explore Curriculum

Numeric knowledge varieties:

• The numeric knowledge varieties represent real numbers, integers, and floating-point numbers. they’re keep as variety, IEEE floating-point storage varieties (BINARY_FLOAT and BINARY_DOUBLE), and PLS_INTEGER.
• The data varieties variety, BINARY_FLOAT, and BINARY_DOUBLE area unit SQL knowledge varieties.
• The PLS_INTEGER datatype is particular to PL/SQL. It represents signed thirty two bits integers that vary from -2,147,483,648 to 2,147,483,647.
• Because PLS_INTEGER datatype uses hardware arithmetic, they’re quicker than variety operations, that uses software system arithmetic.
• In addition, PLS_INTEGER values need less storage than variety. Hence, you must continually use PLS_INTEGER values for all calculation in its vary to extend the potency of programs.



Boolean knowledge kind:

The Boolean knowledgetype has 3 data values: TRUE, FALSE, and NULL. Boolean values area unit generally employed in management flow structure like IF-THEN, CASE, and loop statements like LOOP, FOR LOOP, and whereas LOOP.




SQL doesn’t have the Boolean knowledge kind, therefore, you cannot:

• Assign a BOOLEAN value to a table column.
• Select the value from a table column into a BOOLEAN variable.
• Use a BOOLEAN value in a SQL function.
• Use a BOOLEAN expression in a SQL statement.
• Use a BOOLEAN value in the DBMS_OUTPUT.PUTLINE and DBMS_OUTPUT.PUT subprograms.



Character data types:

The character data types represent alphanumeric text. PL/SQL uses the SQL character data types such as CHAR, VARCHAR2, LONG, RAW, LONG RAW, ROWID, and UROWID.

• CHAR(n) is a fixed-length character type whose length is from 1 to 32,767 bytes.
• VARCHAR2(n) is varying length character data from 1 to 32,767 bytes.



Datetime data types

The datetime data types represent dates, timestamp with or without time zone and intervals. PL/SQL datetime data types are DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, INTERVAL YEAR TO MONTH, and INTERVAL DAY TO SECOND.




Learn Advanced Oracle SQL/PLSQL Certification Training Course to Build Your Skills
Weekday / Weekend BatchesSee Batch Details

Data type synonyms

Data types have synonyms for compartibility with non-Oracle data sources such as IBM Db2, SQL Server. And it is not a good practice to use data type synonym unless you are accessing a non-Oracle Database.






Declaring variables:

The syntax for a variable declaration is as follows:

• variable_name datatype [NOT NULL] [:= initial_value];
• Code language: SQL (Structured question Language) (sql)
• In this syntax:
• First, specify the name of the variable. The name of the variable ought to be as descriptive as doable, e.g., l_total_sales, l_credit_limit, and l_sales_revenue.
• Second, opt for associate acceptable information sort for the variable, counting on the type valuable that you would like to store, as an example, number, character, Boolean, and datetime.
• By convention, native variable names ought to begin with avoirdupois unit and international variable names ought to have a prefix of gigacycle per second .
• The following example declares 3 variables l_total_sales, l_credit_limit, and l_contact_name:
• DECLARE
• l_total_sales NUMBER(15,2);
• l_credit_limit variety (10,0);
• l_contact_name VARCHAR2(255);
• BEGIN
• NULL;
• END;
• Code language: SQL (Structured question Language) (sql)
• Default values
• PL/SQL permits you to line a default worth for a variable at the declaration time. To assign a default worth to a variable, you utilize the assignment operator (:=) or the DEFAULT keyword.
• The following example declares a variable named l_product_name with associate initial worth ‘Laptop’:
• DECLARE
• l_product_name VARCHAR2( one hundred ) := ‘Laptop’;
• BEGIN
• NULL;
• END;<
• NOT NULL constraint
• If you impose the NOT NULL constraint on a worth, then the variable cannot settle for a NULL worth. Besides, a variable declared with the NOT NULL should be initialized with a non-null worth. Note that PL/SQL treats a zero-length string as a NULL worth.
• The following example initial declares a variable named l_shipping_status with the NOT NULL constraint. Then, it assigns the variable a zero-length string.
• Variable assignments
• To assign a worth to a variable, you utilize the assignment operator (:=), for example:
• DECLARE
• l_customer_group VARCHAR2(100) := ‘Silver’;
• BEGIN
• l_customer_group := ‘Gold’;
• DBMS_OUTPUT.PUT_LINE(l_customer_group);
• END;
• Code language: SQL (Structured question Language) (sql)
• You can assign a worth of a variable to a different as shown within the following example:
• DECLARE
• l_business_parter VARCHAR2(100) := ‘Distributor’;
• l_lead_for VARCHAR2(100);
• BEGIN
• l_lead_for := l_business_parter;
• DBMS_OUTPUT.PUT_LINE(l_lead_for);
• END;
• Code language: SQL (Structured question Language) (sql)
• Anchored declarations
• Typically, you declare a variable and choose a worth from a table column to the current variable. If the info sort of the table column changes, you want to change the program to form it work with the new sort.
• PL/SQL permits you to declare a variable whose information sort anchor to a table column or another variable. contemplate the subsequent example:
• DECLARE
• l_customer_name customers.name%TYPE;
• l_credit_limit customers.credit_limit%TYPE;
• BEGIN
• SELECT
• name, credit_limit
• INTO
• l_customer_name, l_credit_limit
• FROM
• customers
• WHERE
• customer_id = 38;
• DBMS_OUTPUT.PUT_LINE(l_customer_name || ‘:’ || l_credit_limit );
• END;
• /





PL/SQL IF THEN statement:

The following illustrates the structure of the IF THEN statement:

• IF condition THEN
• statements;
• END IF;
• Code language: SQL (Structured question Language) (sql)
• The condition may be a mathematician expression that forever evaluates to TRUE, FALSE, or NULL.
• If the condition evaluates to TRUE, the statements when the THEN execute. Otherwise, the IF statement will nothing.
• PL/SQL IF THEN statement example
• In the following example, the statements between THEN and finish IF execute as a result of the sales revenue is larger than a hundred,000.
• DECLARE n_sales variety := 2000000;
• BEGIN
• IF n_sales > one hundred thousand THEN
• DBMS_OUTPUT.PUT_LINE( ‘Sales revenue is larger than 100K ‘ );
• END IF;
• END;
• Code language: SQL (Structured question Language) (sql)
• Tip # 1: Avoid clumsy IF statement
• Consider the subsequent example:
• DECLARE
• b_profitable BOOLEAN;
• n_sales NUMBER;
• n_costs NUMBER;
• BEGIN
• b_profitable := false;
• IF n_sales > n_costs THEN
• b_profitable := true;
• END IF;
• END;
• Code language: SQL (Structured question Language) (sql)
• In this example, the IF statement determines whether or not the sales revenue is over the value and updates the b_profitable variable consequently.
• This IF statement known as a slipshod IF statement as a result of you’ll assign the results of a mathematician expression on to a mathematician variable as follows:
• b_profitable := n_sales > n_costs;
• Code language: SQL (Structured question Language) (sql)
• Tip #2: Avoid evaluating mathematician variables
• A mathematician variable is usually TRUE, FALSE or NULL. thus the subsequent comparison is unnecessary:
• IF b_profitable = TRUE THEN
• DBMS_OUTPUT.PUT_LINE( ‘This sales deal is profitable’ );
• END IF;
• Code language: SQL (Structured question Language) (sql)
• Instead, use:
• IF
• b_profitable THEN
• DBMS_OUTPUT.PUT_LINE( ‘This sales deal is profitable’ );
• END IF;
• Code language: SQL (Structured question Language) (sql)
• PL/SQL IF THEN ELSE statement
• The IF THEN ELSE statement has the subsequent structure:
• IF condition THEN
• statements;
• ELSE
• else_statements;
• END IF;
• Code language: SQL (Structured question Language) (sql)
• If the condition evaluates to TRUE, then the statements between THEN and ELSE execute. just in case the condition evaluates to FALSE or NULL, the else_statements between ELSE and finish IF executes.
• IF THEN ELSE statement example
• The following example sets the sales commission to 100 percent if the sales revenue is larger than two hundred,000. Otherwise, the sales commission is ready to five.
• DECLARE
• n_sales variety := 300000;
• n_commission NUMBER( ten, 2 ) := 0;
• BEGIN
• IF n_sales > 200000 THEN
• n_commission := n_sales * zero.1;
• ELSE
• n_commission := n_sales * zero.05;
• END IF;
• END;
• Code language: SQL (Structured question Language) (sql)
• PL/SQL IF THEN ELSIF statement
• The following illustrates the structure of the IF THEN ELSIF statement:
• IF condition_1 THEN
• statements_1
E
• LSIF condition_2 THEN
• statements_2
• [ ELSIF condition_3 THEN
• statements_3
• ]
• …
• [ ELSE
• else_statements
• ]
• END IF;

In this structure, the condition between IF so, that is that the 1st condition, is usually evaluated. one another condition between ELSEIF so is evaluated providing the preceding condition is fake. as an example, the condition_2 is evaluated providing the condition_1 is fake, the condition_3 is evaluated providing the condition_2 is fake, and so on.






Introduction to PL/SQL NULL statement:

The PL/SQL NULL statement has the subsequent format:

• NULL;
• Code language: SQL (Structured question Language) (sql)
• The NULL statement could be a NULL keyword followed by a punctuation ( ;). The NULL statement will nothing except that it passes management to successive statement.
• The NULL statement is helpful to:
• Improve code readability
• Provide a target for a GOTO statement
• Create placeholders for subprograms
• Improving code readability
• The following code sends associate degree email to workers whose job titles ar Sales Representative.
• IF job_title = ‘Sales Representative’ THEN
• send_email;
• END IF;
• Code language: SQL (Structured question Language) (sql)
• What ought to the program do for workers whose job title don’t seem to be Sales Representative? you may assume that it ought to do nothing. as a result of this
• logic isn’t expressly mentioned within the code, you will marvel if it misses one thing else.
• To make it additional clear, you’ll add a comment. For example:
• — Send email to solely Sales Representative,
• — for alternative workers, do nothing
• IF job_title = ‘Sales Representative’ THEN
• send_email;
• END IF;
• Code language: SQL (Structured question Language) (sql)
• Or you will add associate degree ELSE clause that consists of a NULL statement to obviously state that no action is required for alternative workers.
• IF job_title = ‘Sales Representative’ THEN
• send_email;
• ELSE
• NULL;
• END IF;
• Code language: SQL (Structured question Language) (sql)
• Similarly, you’ll use a NULL statement within the ELSE clause of an easy CASE statement as shown within the following example:
• DECLARE
• n_credit_status VARCHAR2( fifty );
• BEGIN
• n_credit_status := ‘GOOD’;
• CASE n_credit_status
• once ‘BLOCK’ THEN
• request_for_aproval;
• once ‘WARNING’ THEN
• send_email_to_accountant;
• ELSE
• NULL;
• END CASE;
• END;
• Code language: SQL (Structured question Language) (sql)
• In this example, if the credit standing isn’t blocked or warning, the program will nothing.
• Providing a target for a GOTO statement
• When employing a GOTO statement, you would like to specify a label followed by a minimum of one feasible statement.
• The following example uses a GOTO statement to quickly move to the top of the program if no additional process is required:
• DECLARE
• b_status Boolean
• BEGIN
• IF b_status THEN
• GOTO end_of_program;
• END IF;
• — additional process here
• — …
• NULL;
• END;
• Code language: SQL (Structured question Language) (sql)
• Note that a blunder can occur if you don’t have the NULL statement once the end_of_program label.
• Creating placeholders for subprograms
• The following example creates a procedure named request_for_approval that doesn’t have the code within the body however. PL/SQL needs a minimum of one feasible statement within the body of the procedure so as to compile with success, therefore, we have a tendency to add a NULL statement to the body as a placeholder. Later you’ll fill the important code.
• CREATE PROCEDURE request_for_aproval(
• customer_id range
• )
• AS
• BEGIN
• NULL;
• END;
• Code language: SQL (Structured question Language) (sql)
• Now, you must have a decent understanding of the PL/SQL NULL statement and the way to use it in your daily programming tasks.

Prerequisites:

• There are some requirements for the PL/SQL Tutorial for newbies. If you have a basic understanding of Databases, source code, text editor, basic software programming concepts, program execution, etc., you will be competent to know and comprehend all the images much quicker and easier.


• There are some requirements for the PL/SQL Tutorial for novices. If you have a fundamental knowledge of Database, source code, text editor, basic software programming concepts, program execution, etc., you will be able to learn and understand all the concepts much faster and easier.


Oracle PL/SQL Sample Resumes! Download & Edit, Get Noticed by Top Employers! Download

Conclusion:

• We hope that the fundamentals of PL/SQL, its summary, features, advantages, and variations with SQL square measure understood currently. we’ve got additionally seen the steps for the setting found out of PL/SQL at the side of its reference to the Oracle information.
• In this article, we have a tendency to mentioned the fundamental syntax of PLSQL code that has quite one section, the structure of the code, varied sorts of PL SQL delimiters, and comments.
• Read through the content and bit by bit you’ll develop a powerful understanding and information on PL/SQL. Being a very important artificial language, it’s used extensively by developers in developing contemporary applications.
• We discuss regarding the PL/SQL variables, constants, management statements, and varied different connected topics.