Skip to main content

Basics of Oracle PLSQL

Basics of Oracle PLSQL

Basics of Oracle PLSQL, helps to know more about PLSQL and its importance, before going for PLSQL be clear with SQL basics.
Basics of Oracle PLSQL


What is PLSQL and its abbreviation ?

PLSQL stands for Procedural Language extensions to Structured Query Language.
In SQL we can't able to write business logics and there will be lot of limitations are there, so to overcome those limitations we go for PLSQL. So this can be proceeded by loop the list of SQL statements inside the single block and it will be executed.

Advantages of Using PLSQL

  • Performance wise it is high. As well it will reduce Network Traffic.
  • It is one of the embedded language.
  • Portable - so you can use it anywhere.
  • Easily accessible, readable and friendly 

Some Important basics of Oracle PLSQL

List of some important points to be noted in basics of Oracle PLSQL which are listed below.

PLSQL OBJECTS 

As we already know more about the SQL objects, so in the same way PLSQL objects are there that can be stored in a database and can be used for repeated usage based on our requirements.

PLSQL Objects

Purposes

Procedure

Used to write business logics, and stored procedure can be used for repeated usage 

Functions

Used to write business logics, and stored functions can be used for repeated usage, and it will return one value. 

Packages

Used to group two or many subprograms for single execution and for easy maintenance.

Triggers

Used for many real time purposes especially for Data replication

Collections

Used to store multiple values in a single variable.


We can discuss clearly about all PLSQL objects in later post, so before that basics, and other not stored objects are important to learn like cursor, exception, bulk collect.. because this all will helps to write business logics in a professional manner.

Basic Structure of PLSQL Block

It is very important to understand this basic PLSQL block structure, with the help of this we can easily write any block queries.
Oracle PLSQL basic structure

Important Points to know about basics of Oracle PLSQL Block

  1. Use DECLARE part to declaring any variables, cursors etc... (this can be detailed explained in later classes).
  2. ( / ) Backslash is important, which is used to execute PL/SQL Block.
  3. When any DML (Data manipulation Language) is used inside the PLSQL Block means it important to commit the statement, else it will be rollback.
  4. At least a block should contains one value.

DECLARE
    In this part we can declare the necessary things to use in validation block, actually it is optional part, a PLSQL block can run without this section.

why declare section is important to use in PLSQL block [Interview Question]
    In declare section we can able to declare
            - Variable
            - Cursor
            - Exception
            - Sub Program

BEGIN and END Block 
        In this part is used to write business logics it contains
            - SQL statements
            - Cursors record fetching one by one....
            - And many business logic process can be done here.
            - If we use any DML operations inside the block means it important to commit the statement.
            - This block must contains at least one value. either it can be a NULL also be a fine. check in below figure.
 
basics of oracle plsql block contains null

Exceptions
    This one is used to handle various exceptions that which are failed to process in begin and end block.

Variables in PLSQL :-

Variables are commonly used in many programming languages in the same way here variables are used.
    - It is used to hold some value ( allocated memory)
    - And it can be used for repeated usage.
    - As per the standard per line we can declare only one variable.
    - And this variable should be used by assignment operator while giving any value to this variables.
        For Example :=> v_add := 25; [here (:=) is an assignment operator ]

Types of Variables in PLSQL

  • Scalar => It stores only one value in one variable
  • Composite / Collection  => Similar to array stores multiple value in a variable.
  • Reference / Refcursor => pointer like telling the address instead of values.
  • Large Objects like CLOB, BLOB, BFILE etc.,

Basic Query

basics of Oracle plsql block query

dbms_output.put_line => It is one of the oracle supplied package, here it is used to get output for the logics, but this will be only used by the developer while processing and checking the query by themselves, so at first we will learn from this method.

oracle plsql basics in block

- Here variable is v_office is declared so that can be used for repeated usage inside the blocks, 
This variables are also called as actual parameter.

Figure: The below query will be error if we use without dbms_output.put_line. Because it will become undefined.

oracle plsqls basics exception

Conclusion

I hope that you have understand some concepts in basics of Oracle PLSQL, so if you having any queries or feedback means kindly raise a comment to us we will respond to your comments as soon as possible.

----------------------- End of basics of Oracle PLSQL

Frequently Asked Questions

1, How long it will take to complete PLSQL ?
        If you are familiar with SQL (Structured Query Language), then it will take around 15 to 20 days for basic understanding, but within 10 days you may get idea about the concepts, but to learn deeply it will take around 3 months and for correct flow process it will take around 6 months at least.
    
2, Is PLSQL hard to learn to get a Job?
        To get a job in IT, you should be familiar with any other programming languages or any other testing tools, so to become a developer, one of the easiest way to learn and get job is SQL and PLSQL. where PLSQL is more important than SQL concepts to write business logics it will be followed by many organizations.

Some Important Questions in Basics of Oracle PLSQL

1, Difference between SQL and PLSQL
2, Mention any 3 schema objects in PLSQL ?
3, What is PLSQL ?
4, What are all the advantages of PLSQL ?
5, What are the things that can be declared in PLSQL Block ?
6, Can we able to use declared variable directly to get the results without dbms_output.put_line ?

--------- Comment your answers below or share to the official mail : official.eduflee@gmail.com




Labels:
Location: India

Comments

Post a Comment

Thanks for commenting, we will always respond your comment.

Popular posts from this blog

Oracle SQL Interview Questions Series 1 [Basic]

Oracle SQL Interview Questions Series - 1 Oracle SQL Interview Questions covered in Eduflee, so complete all the series to get placed into the IT company. At present Structured Query Language is very important to enter into the Software company. After completing all the Interview series, you can crack many interviews with 100% assurance from my experience. Basic Questions for SQL interview Schema => User Records => Rows Fields => Columns Result set => Output 1, What are the Schema Objects in Oracle SQL ?     Table, View, Materialized view, Sequence, Synonyms, Index are the schema objects in SQL.     Table => Basic Unit of Storage [Rows and Columns]     View => Logical Representation of subject of records, It does not contains any data. So here data is logical.     Materialized View =>   It contains the data, it is also called as Snapshot. It contains data physically.     Sequence => It is a Numeric Value...
Post a Comment
Read more

Regular Expression in Oracle SQL

Regular Expression in Oracle SQL Regular Expression in Oracle SQL is a pattern matching technique to retrieve sort of data based on the conditions given in Oracle database. Pattern Matching is similar to functions like, replace, substring and in string which are previously seen topics. At the end of this article there will be certain task to practice for this Regular Expressions. It is very important to be very clear about this topic, this are very helpful for many interview Questions. Using regular expressions with Oracle Database: Overview Regular Expression is one of the easiest way to learn with clear understanding if one are familiar with the below Basic string matching patterns, Quantifiers, Meta characters, Functions etc. Note : The above points should understand clearly means it will be very easy to write a query. Basic String Matching with Regular Expressions | Either or  () Group \ Escape ^ Start $ Stop Literals of regular expression in oracle sql [ a b c ] a, b, c [...
Post a Comment
Read more

Class 2 Statements in Oracle DDL

Statements in Oracle Statements in Oracle is very much important topic to understand rest other topics better, so there will be lot of important interview questions will be there in this chapter. This statements in Oracle is also called as Subset of oracle statements. Before entering in to the statements in oracle, everyone should know the basic understanding about Data types in Oracle  with that clear content will be much used to understand this subset concept will be easy to learn. Overview of Statements in Oracle At the end of this chapter you will get clear Understanding about the statements of oracle With the help of this statements, you can easily create database objects like Tables Able to create tables, columns, filling the data and merging and other privileges related parts will be seen here. DDL, DML , DCL , TCL are explained very well in this post.     POINTS TO REMEMBER: Always use underscore while naming any table or columns. Example: Instead of using custom...
Post a Comment
Read more