PL/SQL developers are in huge demand these days, as the platform has nothing but great benefits to offer. For enhanced security, portability and robustness of databases, the Oracle developers prefer PL/SQL than SQL. Here are a few PL/SQL questions that can help to crack an interview:
PL/SQL stands for Procedural Language extension of Structured Query Language (SQL). It is a block-structured language having logical blocks which are made up of three sub-blocks i.e. a declarative part, an executable part and an exception building part. PL/SQL is integrated with Oracle and the functionalities of PL/SQL extend after each release of Oracle database.
It includes procedural language elements like conditions and loops and allows declaration of constants and variables, procedures and functions. It also helps the users to develop complex database applications using control structures, procedures, modules, etc. PL/SQL is not limited by any case-sensitive letters so you are free to use lower case letters or upper case letters.
%TYPE- The %TYPE lets you declare a constant, variable, collection element, etc as previously declared variable or column. It is an attribute which is used for anchoring. Example- the variable m_empno has same data type as the column empno in table emp.
%ROWTYPE- the %Rowtype attribute lets you declare a record that represents a row in the table. The fields of the row have same name and data types as column in the view. Example- dept_rec dept%ROWTYPE . This declares a record that can store an entire row for DEPT table.
Function: The PL/SQL function consists of return type and is responsible for computing so that it can return a single value. Due to a specification in return type, the value returned must be in that same type.
Procedure: Procedure is just responsible for a return statement that can stop an execution and return back to the caller. Unlike a function, the procedure does not have any specific return type and doesn’t return single but multiple values.
Package: A package, which is a schema object, is responsible for grouping PL/SQL types, subprograms and items, which are logically related. Package consists of all i.e. procedure, record type statement, function, and procedure. Being able to provide modularity, it is cable of developing an application. Using a package, you can hide information coming from an unauthorized user.
PL/SQL is Oracle’s Procedural Language SQL which allows you to write full programs to execute options like insert/ select/ updates/ delete. SQL is a query language that allows you to execute a single insert/ delete/ update. The following are the notable differences between SQL and PL/SQL:
|SQL is a data-oriented language which is very useful for interactive processing.||PL/SQL is a procedural language to create applications. It is an extension of Oracle.|
|SQL doesn’t have any procedural capabilities like condition testing, looping. It tells the database what to do and not how to do.||On the contrary, PL/SQL offers procedural capabilities like condition testing, looping, etc. it also offers high language features.|
|SQL is executed by the database one statement at a time. It is a time-consuming process.||PL/SQL is less time consuming as compared with SQL. It sends the block of codes to the database server at the same time.|
|There are very less chances of error handling in SQL.|
Each constant, value and parameter has a data type that tells us the type of data used and their associated operations in the program. The following are four data types available in PL/SQL:
Never Miss an Articles from us.