PL/SQL tutorial 1: PL/SQL Block Types in Oracle Database By Manish Sharma

PL/SQL tutorial 1: PL/SQL Block Types in Oracle Database By Manish Sharma


What’s up Internet? I am Manish from RebellionRider.com.
Today’s PL/SQL tutorial is about Blocks in PL/SQL programs.
So without further ado let’s get started with the quick intro of PL/SQL language.
PL/SQL stands for procedural language-standard query language. It is a significant member
of Oracle programming tool set which is extensively used to code server side programming. Similar
to SQL language PL/SQL is also a case-insensitive programming language.
Generally a program written in PL/SQL language is divided into blocks. We can say blocks
are basic programming units in PL/SQL programming language.
There are two types of blocks in PL/SQL 1. Anonymous Block
2. Named Block Both type of PL/SQL blocks are further divided
into 3 different sections which are 1. The Declaration Section
2. The Execution Section and 3. The Exception-handling Section
The Execution Section is the only mandatory section of block whereas Declaration and Exception
Handling sections are optional. And here is the basic prototypes of Anonymous
PL/SQL block DECLARE
Declaration Statements BEGIN
Executable statements Exception
Exception handling statements END;
Let’s quickly discuss each of these sections in brief. You can visit my blog for detailed
explanation and examples. The link is in the description box below.
Declaration Section This is the first section of PL/SQL block
which contains definition of PL/SQL identifiers such as variables, Constants, cursors and
so on. You can say this is the place where all local variables used in the program are
defined and documented. Also Declaration sections always start with keyword DECLARE.
Execution Section This section contains executable statements
that allow you to manipulate the variables that have been declared in the declaration
section. The execution Section of any PL/SQL block
always begins with the Keyword BEGIN and ends with the Keyword END.
This is the only mandatory section in PL/SQL block. This section supports all DML commands
and SQL*PLUS built-in functions. It also supports DDL commands using Native Dynamic SQL (NDS)
or DMBS_SQL built-in package. Exception-Handling Section
This is the last section of PL/SQL block which is optional just like the declaration section.
This section contains statements that are executed when a runtime error occurs within
the block. We can say all exception handling code goes here.
You can visit my blog where I have explained this tutorial in more detail covering all
the parts which will help you face Interview questions or Oracle Certification exam. Link
is in the description box. With this I will wind up today’s tutorial.
Please like this video & also share it with your friends and don’t forget to subscribe.
You can also follow me on twitter and Instagram. The links are on your screen.
Will see you soon with another tutorial till then take care this is Manish from RebellionRider

Danny Hutson

76 thoughts on “PL/SQL tutorial 1: PL/SQL Block Types in Oracle Database By Manish Sharma

  1. bro thanks a lot awesome videos on sql and plsql … but thing is that please upload all the topics of plsql videos such as package,procedures,functions etc….
    please i need it because im preparing for interview as well as for certification ..
    thanks ..

  2. Hi Manish, your tutorials are really understandable. Can you please make tutorials for postgres plsql.

  3. Very easy to learn. Excellent videos Manish. I recommend these videos be the first stop for anyone new to pl/sql.

  4. Hi Manish,

    First of all i would like to thank you for your wonderful videos. Really very helpful.
    I am willing to take up the OCA certification. Can you please help to know how to register and with the syllabus.
    Looking forward for your kind reply.

    Regards,

  5. I have a confusion in my mind that what is the difference between oracle 10g and 11g…Are both same which is better??plzzzzzzzzz tell me

  6. Manish – saw all your tutorials 1 -49. Excellent videos and good learning material.
    Thank you and keep positing more, appreciate it!!

  7. Hi Manish Sir,
    My friend suggested to watch videos on SQL.
    There are also video's for Plsql, I would like to see Video's on Oracle 11g Admin 1 & 2. You are explaining very well All basic & deeply explained. Sir please upload video's. I have mailed you same before 6 month ago… plz plz plz Sir, Many online trainees also thinking same…

  8. hi…i have created a project in oracle form 6i and 10g i want to make my project online plz help me how i do this.

  9. Hi Manish,

    Thanks for explaining about VARRAY .. I f u can give some examples on VARRAY that would be great…

  10. Will I be able to write queries without any hassle in my database exam if I take all the thirty lectures? The exam is in a day so I'm really short on time

  11. Thank you so much for this. I am trying to learn this from a book and it's just not as effective as this. Also your fast cadence is very much appreciated. I typically use the YouTube functionality to speed up such videos to about this speed.

  12. Good Tutorial.Really very helpful.clean Explanation on each and every topic.This is the only good destination who are wanted to learn plsql.I am searching from past one year but not other series not like this.Great job manish sir.Can you please upload he videos in other technologies(like JAVA,web development)very helpful to us.Thank you manish sir.

  13. Hi Manish…I have never seen such wonderful explanation before…it is very easy to understand each and every concept of pl/SQL.Your way of describing the things is awesome…thanks a lot for your tutorial.

  14. sir can you help me with this…..getting a compilation errors.. 🙁

    Create or replace trigger T2
    after insert or delete or update on emp_salary_details for each row
    Begin
    IF INSERTING THEN
    Insert into emp_table_audit values(:new.emp_id , NULL ,user ,systimestamp, ‘INSERTION’);
    ELSIF DELETING THEN
    Insert into emp_table_audit values(NULL , :old.emp_id ,user , systimestamp, ‘DELETION’);
    ELSIF UPDATING THEN
    Insert into emp_table_audit values(:new.emp_id , :old.emp_id , user , systimestamp, ‘UPDATION’);
    END IF;
    END;

  15. My pl SQL class will begin tomorrow.. just wanted to go lil bit prepared . Found some video's on YouTube but Ur video's are the best.
    Nice work

  16. Hey Brthr Manish. plz just answer me the question I am asking…

    My question is I'm having a table with the columns as name, id, gender having it's details entered randomly…like

    name id gender
    a 1 m
    b 2 m
    c 3 f
    d 4 m
    e 5 f
    f 6 f

    I want the answer on gender basis alternatively…like

    name id gender
    a 1 m
    c 3 f
    b 2 m
    e 5 f
    d 4 m
    f 6 f

  17. Hi Manish , Enjoying your content throughly , Please create similar videos on database administration. Thanks alot

  18. Thank you very much Mr. Manish Sharma for your videos on pl/sql.
    Please could you make a videos on real time project.

  19. You did a good job with your channel even after dota pro was released my boy. We still watching your videos and we all appreciate it!

  20. HELLO SIR, I want your videos on oracle 12c architecture, backup and recovery.. I really need it for attempting 1z0-062…. kindly help me with appropriate solutions

  21. You're the best…I undersyand Pl/SQL for the first time. Trust me I watched many good videos. Love everything about your teaching style. Very clear and to the point.

    Thank you so much.

  22. hi manish , i got error to run this,whats wrong in excepction section:
    declare

    v_first_name varchar2(30);

    v_last_name varchar2(30);

    con_flag constant number:=0;

    begin

    select first_name ,last_name into v_first_name,v_last_name from hr.employees where employee_id=100;

    dbms_output.put_line

    ('employee name'||' '||v_first_name||' '||v_last_name);

    exception

    when no_data_found then

    dbms_output.put_line('no employee found with' || employee_id);

    end;

  23. Sir I already know sql do you think that with this pl/sql course of yours I would b able to apply to a database administrator job position sir? Thanks for the courses btw

  24. are there any prerequsites? do i need to learn sql before pl/sql? if i have to,from where shall i learn?

  25. Hlo Manish can u please guide me I want to do certification called 1z0-071 how can I prepare for that??

Leave a Reply

Your email address will not be published. Required fields are marked *