EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 600+ Courses All in One Bundle
  • Login
Home Software Development Software Development Tutorials PL/SQL Tutorial PL/SQL Commands
Secondary Sidebar
How to Install Python on Linux

Java NIO Scatter/Gather

Java 11

Java NIO File

Bootstrap 4 Datepicker

Java Project Maven

PL/SQL Commands

By Aanchal SinghAanchal Singh

PL/SQL Commands

Introduction of PL/SQL Commands

PL/SQL is very similar to SQL. It stands for Procedural Language extension to Structured Query Language. It is very helpful in querying any kind of data in the table and also updating that data. PL/SQL is introduced for PL/SQL block structure and helps in proper development. It also provides variables which help in declaring, naming and assigning different variables. Let us have a look at all different commands that are present in PL/SQL.

Basic PL/SQL Commands

The basic commands are as explained below.

1. PL/SQL Block Structure: The block structure has four types of code block sections. These will be header, declaration, executable and exceptions. The code block is terminated by End Keyword. The code should be written in Begin and End keywords.

Basic

Start Your Free Software Development Course

Web development, programming languages, Software testing & others

2. Comments: Comments can be added at any time. These can be notes or piece of information in the code which need to be referred to in the future. It can be represented by ‘—‘ for a line or a block of comments can be represented by ‘/* */’.

comments

3. Defining Variables: Variables can be defined in the declaration section of a program. The syntax to define a variable is variable_name datatype;

Variables

It is treated as a variable definition similar to other statements. It should have a name and a datatype. These are mandatory.

4. Constraint definitions- Constraints can be applied on variables which are defined in any code block. A constraint can be called as a condition that is to be applied to a particular variable. The commonly used constraints are constant and not null. Constant is used to make sure that value does not change after a value is initially passed. Not null makes sure that variable has a value always. If any value is attempting to assign a null value then an error will occur. Other constraints include unique, check, primary key, foreign key, etc.

Constraint definitions

5. Data Insertion: In this PL/SQL Command, data can be inserted into any table by making use of ‘insert’ command. This command will require table name, column name, and column values. Values can be inserted into any base table by making use of this statement. The table name and values are mandatory. Also the keyword ‘values’ should be used.

Pl/sql command Data Insertion

6. Data Update: Data in a table can be updated easily by making use of ‘update’ statement. It can update any value of any column in a table. It requires to have table name, column name and value as the input and updates the data.

Pl/sql command Data Update

The keyword set helps in updating the value of a column with the specified value. The where clause mentioned in optional and if it is not specified the value will be updated for the entire column.

7. Data Deletion: Data can be deleted in Pl/SQL by making use of delete command. It can help delete the entire data or provide conditions to delete a particular data. This condition can be provided by ‘where’ clause.

Data Deletion

8. Selecting data: To fetch particular data needs that it is retrieved from a particular database table. This is made possible in Pl/SQL by the ‘select’ statement.

Pl/sql command Selecting data

9. Handling exceptions: This PL/SQL command can be used to detect error conditions. It handles the errors in a smart way and also enables the user to give suitable user-defined messages.

Pl/sql command Handling exceptions

10. Arithmetic Operators: All arithmetic operators are supported by PL/SQL. A user can add, subtract, multiply and divide.

Pl/sql command Arithmetic Operators

Intermediate PL/SQL Commands

The intermediate commands are as explained below.

All in One Software Development Bundle(600+ Courses, 50+ projects)
Python TutorialC SharpJavaJavaScript
C Plus PlusSoftware TestingSQLKali Linux
Price
View Courses
600+ Online Courses | 50+ projects | 3000+ Hours | Verifiable Certificates | Lifetime Access
4.6 (86,328 ratings)

1. Currval and Nextval: A sequence is used to generate sequential numbers. An initial value can be specified. This value can be incremented. The currval function helps the user to get the current value of the variable. The nextval function increments this sequence and returns the value that is stored.

2. Rowid: This function returns the rowid or binary address of a row in any database table. This rowid can be stored in a readable format. In addition to this if a user wants to fetch any physical rowid into a variable you can use ROWIDTOCHAR which will convert the binary value to a character string.

3. Rownum: The rownum returns a number which indicates the order in which a row may get selected from a table.

  • The first row that is selected it has the rownum set to
  • The second row likewise is set to rownum
  • This function can be used in queries to retrieve data and can also be used in update statements.

4. Comparison operators: These functions help for comparing expressions and yield values like True, false or null. These operators include Between, exists, in, is null and like operators.

5. Set Operator: This operator is used when the results require that two queries are combined. Intersect operator checks for distinct values from both queries, while Union returns all distinct rows selected by either of the queries. Union all returns all rows that are selected by the queries, including all duplicates.

6. %ISOPEN: This closes the SQL cursor automatically after executing the associated SQL statement.

7. Taking input from the user: User can take input from the user by making use of & an operator. This input can be taken and stored in a variable.

8. Index-By table: An index by the table is a set of key-value pairs. All keys must be unique and can be used to locate the corresponding value. This key can be either an integer or string.

9. Calling a Function: A function can be created to perform a defined task and return the results when the statement is executed.

10. %ROWCOUNT: This PL/SQL command helps in yielding the number of rows that are affected by an insert, update or delete statement.

Advanced PL/SQL Commands

The advanced commands are as explained below.

1. Procedures: Whenever a user wants to accept the input from the user then the procedure can be used. These parameters can be defined along with different subprograms. These are IN, OUT and IN OUT parameters. The IN parameter takes the input for subprograms. OUT parameter is used for getting output from the subprograms. IN OUT can take both inputs and give outputs.

2. Cursors: Cursors control the context area through a cursor. It has the ability to hold the rows that are returned by any SQL statement. This set of rows are known as the active set.

Tips and Tricks to use

1. Not Null constraint can be used with only a column.

2. SYSDATE returns the current date.

3. Values specified in ‘between’ clause are inclusive of those values.

4. MIN and MAX functions can be used for any data type.

5. Only stored functions are callable and not stored procedures.

Conclusion

PL/SQL commands are one of the oldest and most used languages to retrieve data from a database. It provides all ways for getting data, storing it and manipulating it. With all these different functions managing the data is very easy.

Recommended Articles

This has been a guide to PL/SQL Commands. Here we have discussed basic, intermediate as well as advanced PL/SQL Commands along with tips and tricks to use. You may also look at the following article to learn more.

  1. How To Use Pig Commands
  2. MongoDB Commands
  3. Top Mosts Use Spark Commands
  4. You Should Try Hive Commands
Popular Course in this category
PL SQL Training (4 Courses, 2+ Projects)
  4 Online Courses |  2 Hands-on Projects |  17+ Hours |  Verifiable Certificate of Completion
4.5
Price

View Course
3 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Java Tutorials
  • Python Tutorials
  • All Tutorials
Certification Courses
  • All Courses
  • Software Development Course - All in One Bundle
  • Become a Python Developer
  • Java Course
  • Become a Selenium Automation Tester
  • Become an IoT Developer
  • ASP.NET Course
  • VB.NET Course
  • PHP Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Software Development Course

C# Programming, Conditional Constructs, Loops, Arrays, OOPS Concept

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Software Development Course

Web development, programming languages, Software testing & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more