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 Generator     Synonyms => used to provide alternate nam
Post a Comment
Read more

Class 3 Functions in Oracle SQL

Functions in Oracle SQL Functions is one of the important features in Oracle SQL, with the help of this functions we can able to get the result as per our requirement. Functions in Oracle SQL contains Single Row Functions, Multiple Row Functions. Mainly those functions are used to returns value based on the input that which we given. And practicing this functions in oracle SQL, one can able to master in query writing. Functions is used to perform some actions like doing any calculations, grouping the data, conversion in type, date conversion etc. Types of Functions in Oracle SQL Functions in oracle is classified on the basis of the input which we are giving and getting the output, and they are: - Single Row Functions - Multiple Row Functions Single Row Functions in Oracle SQL Single Row Functions works on the single rows which will shows the result for that single rows only. If "n" number of inputs to give "n" number of outputs. From the above figure explains about
Post a Comment
Read more

Learn SQL From Scratch to Advanced topics

Learn SQL from Scratch to Advanced  Nowadays, many IT company moreover depend on SQL  only. So this is one of the basic query language to start for new developers. So track or to be master in SQL, the below topics should be covered. List of Basic Topics :- 1, Should know what is frontend and backend developer  2, What is DBMS, RDBMS ? 3, What is SQL ? 4, What are the database are there ? Beginner Level SQL topics  This are some beginner level SQL topics, if you are new to SQL this are the topics that should be covered first. 1, Oracle Basics  2, List of Objects in SQL   3 , Clause and its usage   4, Functions  5, Sub Query  6, Joins  7, Decode and Case 8, Constraints 9, Important Data Dictionary Table like user_source, all_source, user_tables etc.., Intermediate Level SQL topics (Creating Objects)  When we are entering into objects this below topics should be covered. In  this topics that we are creating database objects like tables, views, sequences, synonyms etc. 1, Data Definitio
Post a Comment
Read more