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.
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 ‘/* */’.
3. Defining Variables: Variables can be defined in the declaration section of a program. The syntax to define a variable is variable_name datatype;
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.
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.
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.
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.
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.
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.
10. Arithmetic Operators: All arithmetic operators are supported by PL/SQL. A user can add, subtract, multiply and divide.
Intermediate PL/SQL Commands
The intermediate commands are as explained below.
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.
