Cursor to Top Again in Plsql
PL/SQL stands for Procedural Language extensions to SQL (Structured Query Language). It was created past Oracle in gild to overcome the disadvantages of SQL for easier edifice and treatment of critical applications in a comprehensive way.
Following are the disadvantages of SQL:
- There is no provision of decision-making, looping, and branching in SQL.
- Since the SQL statements get passed to the Oracle engine all at the same time, the speed of execution decreases due to the nature of increased traffic.
- There is no feature of fault checking while manipulating the data.
PL/SQL was introduced to overcome the to a higher place disadvantages by retaining the ability of SQL and combining it with the procedural statements. Information technology is developed as a block-structured language and the statements of the block are passed to the oracle engine which helps to increase the speed of processing due to the decrease in traffic.
PL/SQL Basic Interview Questions
1. What are the features of PL/SQL?
Following are the features of PL/SQL:
- PL/SQL provides the feature of decision making, looping, and branching by making use of its procedural nature.
- Multiple queries tin can be candy in one cake past making use of a single control using PL/SQL.
- The PL/SQL code can be reused by applications every bit they can be grouped and stored in databases as PL/SQL units like functions, procedures, packages, triggers, and types.
- PL/SQL supports exception handling by making utilise of an exception handling block.
- Forth with exception handling, PL/SQL also supports mistake checking and validation of data earlier information manipulation.
- Applications developed using PL/SQL are portable beyond computer hardware or operating system where at that place is an Oracle engine.
2. What do yous understand by PL/SQL table?
- PL/SQL tables are nothing but objects of type tables that are modeled equally database tables. They are a way to provide arrays that are nothing simply temporary tables in memory for faster processing.
- These tables are useful for moving bulk data thereby simplifying the process.
iii. Explain the basic structure followed in PL/SQL?
- The basic structure of PL/SQL follows the Block structure. Each PL/SQL code comprises SQL and PL/SQL argument that constitutes a PL/SQL cake.
- Each PL/SQL block consists of three sections:
- The optional Declaration Section
- The mandatory Execution Department
- The optional Exception handling Section
[DECLARE] --declaration statements (optional) BEGIN --execution statements [EXCEPTION] --exception handling statements (optional) Terminate; four. What is a PL/SQL cursor?
- A PL/SQL cursor is nothing just a pointer to an area of retentivity having SQL statements and the data of statement processing. This memory area is called a context area. This special expanse makes apply of a special feature called cursor for the purpose of retrieving and processing more than i row.
- In brusk, the cursor selects multiple rows from the database and these selected rows are individually processed within a programme.
- There are two types of cursors:
- Implicit Cursor:
- Oracle automatically creates a cursor while running any of the commands - SELECT INTO, INSERT, DELETE or UPDATE implicitly.
- The execution wheel of these cursors is internally handled by Oracle and returns the data and condition of the cursor by making apply of the cursor attributes- ROWCOUNT, ISOPEN, Found, NOTFOUND.
- Explicit Cursor:
- This cursor is a SELECT statement that was declared explicitly in the proclamation block.
- The programmer has to control the execution cycle of these cursors starting from OPEN to FETCH and close.
- The execution cycle while executing the SQL statement is defined by Oracle along with associating a cursor with information technology.
- Implicit Cursor:
- Explicit Cursor Execution Cycle:
- Due to the flexibility of defining our own execution cycle, explicit cursors are used in many instances. The following diagram represents the execution menstruum of an explicit cursor:
- Cursor Annunciation:
- The first footstep to use an explicit cursor is its declaration.
- Proclamation tin can be washed in a packet or a block.
- Syntax:
CURSOR cursor_name IS query;where cursor_name is the name of the cursor, the query is the query to fetch data from any tabular array.
- Open Cursor:
- Before the process of fetching rows from cursor, the cursor has to be opened.
- Syntax to open up a cursor:
OPEN cursor_name; - When the cursor is opened, the query and the bind variables are parsed by Oracle and the SQL statements are executed.
- The execution plan is determined by Oracle and the upshot set is determined after associating the cursor parameters and host variables and post these, the cursor is set to point at the beginning row of the result set.
- Fetch from cursor:
- FETCH argument is used to place the content of the current row into variables.
- Syntax:
FETCH cursor_name INTO variable_list; - In lodge to get all the rows of a outcome set, each row needs to be fetched.
- Close Cursor:
- Once all the rows are fetched, the cursor needs to be airtight using the CLOSE statement.
- Syntax:
Close cursor_name; - The instructions tell Oracle to release the memory allocated to the cursor.
- Cursors declared in procedures or anonymous blocks are by default closed post their execution.
- Cursors declared in packages need to be closed explicitly as the scope is global.
- Closing a cursor that is not opened will result in INVALID_CURSOR exception.
v. What is the use of WHERE Current OF in cursors?
- We use this clause while referencing the current row from an explicit cursor. This clause allows applying updates and deletion of the row currently under consideration without explicitly referencing the row ID.
- Syntax:
UPDATE table_name Prepare field=new_value WHERE Electric current OF cursor_name
6. How can a proper noun be assigned to an unnamed PL/SQL Exception Block?
- This can be done by using Pragma called EXCEPTION_INIT.
- This gives the flexibility to the programmer to instruct the compiler to provide custom error messages based on the business logic by overriding the pre-defined messages during the compilation time.
- Syntax:
DECLARE exception_name EXCEPTION; PRAGMA EXCEPTION_INIT (exception_name, error_code); BEGIN // PL/SQL Logic EXCEPTION WHEN exception_name And so // Steps to handle exception Cease; vii. What is a Trigger? Proper name some instances when "Triggers" tin exist used.
- As the proper name indicates, 'Trigger' means to 'activate' something. In the instance of PL/SQL, a trigger is a stored procedure that specifies what activity has to be taken past the database when an issue related to the database is performed.
- Syntax:
TRIGGER trigger_name trigger_event [ restrictions ] BEGIN actions_of_trigger; Terminate; In the above syntax, if the trigger_name the trigger is in the enabled state, the trigger_event causes the database to fire actions_of_trigger if the restrictions are Truthful or unavailable.
- They are mainly used in the following scenarios:
- In lodge to maintain complex integrity constraints.
- For the purpose of auditing any tabular array information.
- Whenever changes are done to a table, if we need to point other actions upon completion of the change, then we utilise triggers.
- In lodge to enforce complex rules of business organization.
- It tin can also be used to prevent invalid transactions.
- You can refer https://docs.oracle.com/database/121/TDDDG/tdddg_triggers.htm for more information regarding triggers.
viii. When does a DECLARE block become mandatory?
- This statement is used past bearding blocks of PL/SQL such equally non-stored and stand up-alone procedures. When they are existence used, the statement should come first in the stand-lone file.
ix. How do you write comments in a PL/SQL code?
- Comments are those sentences that take no result on the functionality and are used for the purpose of enhancing the readability of the lawmaking. They are of ii types:
- Single Line Comment: This can be created past using the symbol
--and writing what we desire to mention every bit a comment next to it. - Multi-Line annotate: These are the comments that can exist specified over multiple lines and the syntax goes like
/* annotate information */
- Single Line Comment: This can be created past using the symbol
- Instance:
SET SERVEROUTPUT ON; DECLARE -- Hi There! I am a single line comment. var_name varchar2(40) := 'I dear PL/SQL' ; BEGIN /* Hi! I am a multi line comment. I span across multiple lines */ dbms_output.put_line(var_name); END; / Output: I dearest PL/SQL ten. What is the purpose of WHEN clause in the trigger?
- WHEN clause specifies for what condition the trigger has to be triggered.
PL/SQL Advanced Interview Questions
21. What are COMMIT, ROLLBACK and SAVEPOINT statements in PL/SQL?
- These are the three transaction specifications that are available in PL/SQL.
- COMMIT: Whenever any DML operations are performed, the data gets manipulated only in the database buffer and non the bodily database. In society to save these DML transactions to the database, there is a need to COMMIT these transactions.
- COMMIT transaction activeness does saving of all the outstanding changes since the last commit and the below steps take place:
- The release of affected rows.
- The transaction is marked as complete.
- The details of the transaction would exist stored in the data dictionary.
- Syntax: COMMIT;
- COMMIT transaction activeness does saving of all the outstanding changes since the last commit and the below steps take place:
- ROLLBACK: In social club to undo or erase the changes that were done in the current transaction, the changes demand to be rolled back. ROLLBACK statement erases all the changes since the concluding COMMIT.
- Syntax: ROLLBACK;
- SAVEPOINT: This statement gives the name and defines a point in the current transaction process where any changes occurring earlier that SAVEPOINT would be preserved whereas all the changes after that point would be released.
- Syntax: SAVEPOINT <savepoint_name>;
22. How can yous debug your PL/SQL code?
- We can employ DBMS_OUTPUT and DBMS_DEBUG statements for debugging our code:
- DBMS_OUTPUT prints the output to the standard console.
- DBMS_DEBUG prints the output to the log file.
23. What is the difference between a mutating table and a constraining table?
- A table that is being modified by the usage of the DML statement currently is known as a mutating table. Information technology tin can as well exist a table that has triggers defined on it.
- A tabular array used for reading for the purpose of referential integrity constraint is called a constraining table.
24. In what cursor attributes the outcomes of DML statement execution are saved?
- The outcomes of the execution of the DML argument is saved in the following four cursor attributes:
- SQL%FOUND: This returns TRUE if at least 1 row has been processed.
- SQL%NOTFOUND: This returns True if no rows were processed.
- SQL%ISOPEN: This checks whether the cursor is open or not and returns True if open up.
- SQL%ROWCOUNT: This returns the number of rows processed by the DML statement.
25. Is information technology possible to declare column which has the number information blazon and its scale larger than the precision? For example defining columns like: column proper name NUMBER (10,100), cavalcade proper name NUMBER (ten,-84)
- Aye, these type of declarations are possible.
- Number (9, 12) indicates that there are 12 digits after decimal indicate. Just since the maximum precision is nine, the rest are 0 padded like 0.000999999999.
- Number (9, -12) indicates in that location are 21 digits before the decimal indicate and out of that there are nine possible digits and the residuum are 0 padded like 999999999000000000000.0
PL/SQL Programs
26. Write a PL/SQL program using WHILE loop for calculating the average of the numbers entered by user. Stop the entry of numbers whenever the user enters the number 0.
DECLARE n NUMBER; average NUMBER :=0 ; sum NUMBER :=0 ; count NUMBER :=0 ; BEGIN -- Take input from user n := &input_number; WHILE(n<>0) LOOP -- Increase count to find total elements count := count+1; -- Sum of elements entered sum := sum+due north; -- Have input from user n := &input_number; END LOOP; -- Average adding boilerplate := sum/count; DBMS_OUTPUT.PUT_LINE('Average of entered numbers is '||boilerplate); END; 27. Write a PL/SQL procedure for selecting some records from the database using some parameters as filters.
- Consider that nosotros are fetching details of employees from ib_employee table where salary is a parameter for filter.
CREATE PROCEDURE get_employee_details @salary nvarchar(30) Equally Begin SELECT * FROM ib_employee WHERE bacon = @salary; END; 28. Write a PL/SQL code to count the number of Sundays between the two inputted dates.
--declare 2 dates of type Date DECLARE start_date Engagement; end_date Engagement; sundays_count Number:=0; BEGIN -- input 2 dates start_date:='&input_start_date'; end_date:='&input_end_date'; /* Returns the date of the first twenty-four hours later on the mentioned date and matching the 24-hour interval specified in second parameter. */ start_date:=NEXT_DAY(start_date-i, 'Lord's day'); --bank check the condition of dates by using while loop. while(start_date<=end_date) LOOP sundays_count:=sundays_count+one; start_date:=start_date+7; END LOOP; -- impress the count of sundays dbms_output.put_line('Total number of Sundays between the two dates:'||sundays_count); END; / Input:
start_date = '01-SEP-19'
end_date = '29-SEP-19'
Output:
Total number of Sundays between the two dates: 5
29. Write PL/SQL code block to increase the employee's salary by k whose employee_id is 102 from the given table beneath.
| EMPLOYEE_ID | FIRST_NAME | LAST_NAME | EMAIL_ID | PHONE_NUMBER | JOIN_DATE | JOB_ID | SALARY |
|---|---|---|---|---|---|---|---|
| 100 | ABC | DEF | abef | 9876543210 | 2020-06-06 | AD_PRES | 24000.00 |
| 101 | GHI | JKL | ghkl | 9876543211 | 2021-02-08 | AD_VP | 17000.00 |
| 102 | MNO | PQR | mnqr | 9876543212 | 2016-05-14 | AD_VP | 17000.00 |
| 103 | STU | VWX | stwx | 9876543213 | 2019-06-24 | IT_PROG | 9000.00 |
DECLARE employee_salary NUMBER(viii,ii); PROCEDURE update_salary ( emp NUMBER, salary IN OUT NUMBER ) IS BEGIN salary := salary + thousand; END; BEGIN SELECT salary INTO employee_salary FROM ib_employee WHERE employee_id = 102; DBMS_OUTPUT.PUT_LINE ('Earlier update_salary procedure, bacon is: ' || employee_salary); update_salary (100, employee_salary); DBMS_OUTPUT.PUT_LINE ('After update_salary procedure, salary is: ' || employee_salary); Finish; / Result:
Before update_salary process, salary is: 17000 After update_salary process, salary is: 18000 30. Write a PL/SQL code to find whether a given cord is palindrome or not.
DECLARE -- Declared variables cord, alphabetic character, reverse_string where string is the original string. cord VARCHAR2(10) := 'abccba'; letter VARCHAR2(20); reverse_string VARCHAR2(10); BEGIN FOR i IN REVERSE 1..LENGTH(string) LOOP alphabetic character := SUBSTR(string, i, 1); -- concatenate letter to reverse_string variable reverse_string := reverse_string ||''||letter; END LOOP; IF reverse_string = cord THEN dbms_output.Put_line(reverse_string||''||' is palindrome'); ELSE dbms_output.Put_line(reverse_string ||'' ||' is not palindrome'); END IF; End; 31. Write PL/SQL program to convert each digit of a given number into its corresponding word format.
DECLARE -- declare necessary variables -- num represents the given number -- number_to_word represents the discussion format of the number -- str, len and digit are the intermediate variables used for program execution num INTEGER; number_to_word VARCHAR2(100); digit_str VARCHAR2(100); len INTEGER; digit INTEGER; BEGIN num := 123456; len := LENGTH(num); dbms_output.PUT_LINE('Input: ' ||num); -- Iterate through the number one by i FOR i IN 1..len LOOP digit := SUBSTR(num, i, 1); -- Using DECODE, go the str representation of the digit SELECT Decode(digit, 0, 'Zero ', 1, 'I ', ii, 'Ii ', three, 'Three ', four, 'Iv ', five, 'Five ', 6, 'Half dozen ', 7, 'Seven ', eight, 'Eight ', 9, 'Nine ') INTO digit_str FROM dual; -- Append the str representation of digit to terminal result. number_to_word := number_to_word || digit_str; Stop LOOP; dbms_output.PUT_LINE('Output: ' ||number_to_word); END; Input: 12345
Output: 1 2 Iii Four Five
32. Write PL/SQL program to find the sum of digits of a number.
DECLARE --Declare variables num, sum_of_digits and remainder of datatype Integer num INTEGER; sum_of_digits INTEGER; remainder INTEGER; BEGIN num := 123456; sum_of_digits := 0; -- Discover the sum of digits until original number doesnt become null WHILE num <> 0 LOOP residue := MOD(num, x); sum_of_digits := sum_of_digits + remainder; num := TRUNC(num / 10); Finish LOOP; dbms_output.PUT_LINE('Sum of digits is '|| sum_of_digits); END; Input: 9874
Output: 28
PL/SQL Conclusion
33. PL SQL Interview
- PL/SQL is a programming extension of SQL adult by Oracle which combines the power of SQL in the field of information manipulation and the power of procedural language for faster and effective processing of information thereby resulting in the creation of powerful queries.
- PL/SQL enhances the security, increases the platform portability, and makes it more robust by ways of instructing the compiler 'what to do' and 'how to practice' using SQL and procedural form respectively.
- Finally, information technology gives more power over the database to the programmers due to the characteristic of determination making, filtering, and looping abilities thereby making it a more user-friendly and reliable ways for the programmers to work on information technology.
References:
https://oracle-base.com/articles/misc/introduction-to-plsql
Recommended Tutorials:
SQL Server Interview Questions
MySQL Interview Questions
MongoDB Interview Questions
DBMS Interview Questions
Pl/Sql Interview MCQs
Source: https://www.interviewbit.com/pl-sql-interview-questions/
0 Response to "Cursor to Top Again in Plsql"
Post a Comment