PL / SQL is a procedural language specifically designed to include SQL assertions in its syntax. The Oracle Database server compiles PL / SQL program units and is deposited in the database. And both PL / SQL and SQL run in the same server entire process at runtime, putting optimum efficiency. PL / SQL automatically acquires the Oracle Database's robustness, security, and portability.
Below are few major features of Pl/sql
Q1. What is PL/SQL?
PL/SQL stands for Procedural Language/Structured Query Language that uses SQL as its database. PL/SQL gives high productivity to programmers as it can query, transform, and update data in a database. PL/SQL is a block-structured language that enables developers to combine the power of SQL with procedural statements.
Q2. When is PL/SQL handy?
The NULL statement is a handy way to create placeholders and stub procedures.
Q3. Differentiate between PL/SQL and SQL?
SQL - SQL stands for Structured Query Language. SQL could be a high-level language that enables the systems personnel end-users to deal with a number of database management systems where it is available. It specifies what’s needed and not however it ought to be done. It is used to write queries, DDL, and DML statements.
PL/SQL - PL/SQL stands for Procedural Language/Structured Query Language which is a block-structured language that enables developers to combine the power of SQL with procedural statements.PL/SQL is most helpful to put in writing triggers and keep procedures. It is accustomed write program blocks, functions, procedures triggers, and packages.
Q4. What is a trigger?
In PL/SQL, Triggers are stored programs, which are automatically executed or fired when some event occurs. Triggers are used to maintain the referential integrity of data by changing the data in a systematic fashion. Each trigger is attached to a single, specified table in the database.
Q5. What are some predefined exceptions in PL/SQL?
Some of the predefined exceptions in PL/SQL are as follows:
Q6. How do you declare a user-defined exception?
In PL/SQL you can easily declare a user-defined exception as it allows you to define your own exceptions according to the need of your program. You have to ensure that the user-defined exception must be declared and then raised explicitly, using either a RAISE statement or the procedure DBMS_STANDARD. RAISE_APPLICATION_ERROR.
Q7. What is the basic structure of PL/SQL?
The basic structure of PL/SQL can be determined by distinguishing it into three sections that are declaration, executable, and exception-handling sections. In a block, the executable section is mandatory while the declaration and exception-handling sections are optional. A PL/SQL block has a name.
Q8. Does PL/SQL support CREATE command?
No PL/SQL does not support DDL commands directly. Therefore the CREATE command is not directly supported by PL/SQL. Only dynamic SQL can execute the following types of statements within PL/SQL program units.
Q9. What are the PL/SQL cursors?
In PL/SQL, a cursor is a pointer to this context area. PL/SQL controls the context area through a cursor. PL/SQL allows the programmer to control the context area through the cursor. A cursor holds the rows returned by the SQL statement. A cursor holds one or more rows returned by a SQL statement.
Q10. How many types of triggers exist in PL/SQL?
There are two types of triggers that exist in PL/SQL: