|
Define PL/SQL.- PL/SQL is a procedural language and is an extension to standard SQL. - It enables us to execute procedural logic on the database. - We use PL/SQL to perform processing on the server. - We can also use PL/SQL to create stored procedure and functions. PL/SQL is Procedural Language SQL that is an extension of SQL that results in a more structural language composed of blocks. It is mainly used in writing applications that needs to be structured and has error handling. Main Features of PL/SQL1) Offers conditional blocks of code having if else etc 2) Offers error handling. It can handle exceptions. 3) The blocks can be nested within each other. 4) The PL/SQL engine processes the statements in blocks. The block typically looks likeDECLARE BEGIN EXCEPTION END PL/SQL ArchitectureThe architecture consists of PL/SQL block, PL/SQL engine and an oracle server in which the PL/SQL engine is embedded. PL/SQL block of statements are sent to the PL/SQL engine for processing. The PL/SQL engine executes procedural statements but sends SQL statements to the SQL engine in the Oracle database. Basic Structure of PL/SQLA PL/SQL block consists of:- DECLARATIVE section- This is where all variables are declared. BEGIN section- This section contains the PL/SQL block. The statements of code are written in this block. EXCEPTION- Any exceptions that are anticipated are written here. Variables and TypesJust like in any other language, PL/SQL constants and variables need to be declared before using them in your statements. The variables are declared in the BEGIN section. Variables can take data types as CHAR, DATE, or NUMBER. Example for declaring variables:DECLARE
Student_id NUMBER(6);
student_name VARCHAR2(20);
|
|
Advantages of PL/SQL- PL/SQL is a development tool and is an extension to standard SQL. - We can use conditional checking, branching and looping in PL/SQL. - In PL/SQL, we can send a block of statement to the server which reduces network traffic. - PL/SQL provides rich set of error handling mechanism. - PL/SQL supports portability i.e. code written in DOS version can run on unix version. Because of the block nature, multiple statements are processed at once thereby improving performance. PL/SQL handles exceptions on catching which, action can be taken. The block can be stored and reused. PL/SQL is highly portable as it works with all procedural languages and is highly secured because of privileges. Simple PL/SQL Programsa)To give 10% hike in current salary from employee table and insert into appraisal table.
DECLARE
salary NUMBER(8,2);
emp_id NUMBER(6) := 100;
BEGIN
SELECT salary * 0.10 INTO appraisal FROM employees
WHERE employee_id = emp_id;
END;
b)To display employees with id < 100.
BEGIN
FOR someone IN (SELECT * FROM employees WHERE employee_id < 100 )
LOOP
DBMS_OUTPUT.PUT_LINE('First name = ' || someone.first_name || ', Last name = ' || someone.last_name);
END LOOP;
END;
|
|
Explain the concept of exception.An exception occurs when unwanted situation arises. The situation can be exceptional to normal functioning of the program. It can occur due to system error, user error and application error.
In PL/SQL, we can anticipate and trap these errors by means of exception handling code.
Types of Exceptions:
Predefined oracle exceptions
User-defined exceptionsDefined user defined exceptions.We use user defined exception only when oracle doesn't raise its own exception. In this procedure we raise an exception by using RAISE command.
|
|
What is a cursor? Define explicit and implicit cursor.The oracle engine opens a work area for each SQL's operations for its internal processing in order to execute SQL statements. This area is private to SQL's operations and is called as a cursor.
Implicit cursor - If the oracle engine has opened a cursor for its internal processing, then it is implicit cursor.
Explicit cursor - It is also known as user defined cursor. When a user opens a cursor for processing data, the cursor is explicit cursor.
|
|
Explain about the cursor attributes.Each cursor or cursor variable has four attributes:
%FOUND, %ISOPEN, %NOTFOUND and %ROWCOUNT
When appended to the cursor, these attributes return useful information about the execution of a data manipulation statement.What are the restrictions of using cursor variables?-PL/SQL tables cannot store cursor variables.
-Remote subprogram cannot return the value of a cursor variable.
|
|
What is a trigger in PLSQL?A trigger is a PLSQL block that is executed whenever an event occurs. It fires implicitly whenever the triggering event happens, a trigger never accepts argument. A trigger cannot be used for a SELECT statement.What are the triggers supported in oracle?-DML triggers
-Instead of triggers
-DDL triggers
-Database event triggers
DML triggers
It is defined on a table and fires in response to an event like
- When a row is inserted to a table
- When a row is updated
- When a row is deleted
Instead of trigger
This trigger is created on views. You can either use Insert or Update or Delete or all three actions.What are triggering attributes?Triggering attributes are used to catch event when you want to identify or to perform certain actions.
They are as follows:
Inserting
Updating
Deleting
|
|
What is the difference between a function and a procedure in oracle?A function always returns a value back to the calling block.What are packages?A package is an encapsulated collection of related schema objects. A package is compiled and then stored in the database's data dictionary as a schema objects. These objects can be procedure, functions, variables, constants, cursors and exceptions.
|
|
Explain the difference between GRANT and REVOKE command.GRANT command is used to allow a user to perform certain activities on the database. The REVOKE command disallows the user from performing certain activities.Explain the difference between ROLLBACK and COMMIT commands.The COMMIT command is used to save the modifications done to the database values by the DML commands.
ROLLBACK command is used to undo the changes made by the DML commands. This ensures the values that existed prior to the changes can be achieved.
|
|
Define Row level trigger.Row level trigger is fired each time a row is affected by DML statements like Insert, Update and Delete. When no rows affected, the trigger is not executed at all.Define Statement level triggers.It is fired when statement affects rows in a table but the processing required is completely independent of the number of rows affected.
|
|
Define Joins and its types.A join is a query that extracts corresponding rows from two or more tables, views or snapshots.
Types:
Equi-joins
Non-equi joins
Self joins
Outer joins
Equi-join - information from two or more tables are retrieved by using equality conditions.
Self joins - Self join is a join that relates to itself.
Outer joins - Outer join fetch the rows from two tables which matches the join condition and the rows which don't match the join condition.
|
|
Create Pl/SQL block dynamically and then execute it by calling 'DBMS_SQL.EXECUTE'DBMS_SQL.EXECUTE function is used to execute cursor. It accepts the id of the cursor and returns the number of rows processed. Syntax:
DBMS_SQL.EXECUTE ( c IN INTEGER) RETURN INTEGER;
Where c is the id of cursor.
|
|
Explain the statement provided by PL/SQL, i.e. Conditional Control Statements, Iterative Statements, Sequential Control StatementsConditional control statements: they are the typical IF/ELSE statements. IF clause checks a condition, the THEN clause defines what to do if the condition is true and the ELSE clause defines what to do if the condition is false or null. Example:IF sal < 3000 THEN sal_increment := .12;
ELSE sal_raise := .09;
END IF; Iterative statements: Iterative statements uses loops to execute statements in iterations. The FOR loop for instance, lets you specify a range and then execute the statements. Example:FOR i in 1..100 LOOP
SOME STATEMENTS
END LOOP; Sequential Control Statements:- GOTO is an example of Sequential control statements. GOTO statement transfers control to the labeled statement. Example:IF total > 25000 THEN GOTO print_total;
ELSE GOTO calc_total;
|
|
SQL vs. PL/SQLSQL is a structured query language while PL/SQL is an extension of SQL by introducing a procedural flow. PL/SQL has blocks of statements. PL/SQL works like other procedural languages and has concepts like control statements, sequential statements, exception handling etc.
|
|
Both PL/SQL and Java/.NET code can be used to create Oracle stored procedures and triggers. Which of the one should be used and why?Even though both PL/SQL and Java/.NET can be used, PL/SQL stands above these two in terms of integration overhead. This is because Java is an open source proprietary and Data manipulation is slightly faster in PL/SQL than in Java.
|
|
Explain how to see modified code in oracle, i.e. using Oracle Data DictionaryData dictionary in oracle is a read only set of tables providing information about changed schema objects, default values and lot more. The data dictionary is stored in database’s SYSTEM tablespace. We can use SQL statements (select only) to view data of data dictionary.
|
|
Explain how to keep a history of PL/SQL code changes.Using AFTER CREATE schema trigger, once can keep a history of changes to code. Example: CREATE OR REPLACE TRIGGER change_hist -- Store code in hist table
AFTER CREATE ON TEST.SCHEMA -- TEST IS schema name
DECLARE
BEGIN
IF ORA_DICT_OBJ_TYPE in ('PROCEDURE', 'FUNCTION', 'PACKAGE', 'PACKAGE BODY', 'TYPE', 'TYPE BODY')
THEN
-- Store old code in SOURCE_HIST table
INSERT INTO SOURCE_HIST
SELECT sysdate, all_source.* FROM ALL_SOURCE
WHERE TYPE = ORA_DICT_OBJ_TYPE -- DICTIONARY_OBJ_TYPE IN 8i
AND NAME = ORA_DICT_OBJ_NAME; -- DICTIONARY_OBJ_NAME IN 8i
END IF;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20000, SQLERRM);
END;
|
|
Explain the purpose of binary wrapper utility in oracle. How can we protect PL/SQL source code?PL/SQL code can be protected using the binary wrapper utility. It is located in the ORACLE_HOME/bin directory. Syntax:wrap iname=myscript.pls oname=xxxx.plb There is no way to unwrap the *.pls files and hence must be backed up.
|
|
Explain how to debug PL/SQL program.One can debug PL/SQL program by printing the output using DBMS_OUTPUT package. Put_line can be used to display a line as shown below: set serveroutput on //displays buffer begin
dbms_output.put_line(‘Sample line');
end;
|
|
How can we read and write operating system files from PL/SQL program?The UTL_FILE database package can be used to read and write operating system files. You need to have read /write access rights in that directory before the package can be used. Example to write file:
Fhandler is a variable of type UTL_FILE.FILE_TYPE
UTL_FILE.PUTF(fHandler, 'Im writing to a file\n');
Example to read file:
UTL_FILE.GET_LINE(fHandler, buf);
|
|
How can we call DDL statements like CREATE, DROP, TRUNCATE, etc. from PL/SQL? Show in an exampleEXECUTE IMMEDIATE command can be used to call DDL statements.
Example:
begin execute Immediate 'TRUNCATE TABLE employee'; end;How can we use dynamic SQL statements from PL/SQL?EXECUTE IMMEDIATE command can be used to call DDL statements. This is available after oracle 8i.
Example:
EXECUTE IMMEDIATE 'CREATE TABLE employee (id NUMBER)';
|
|
How can we execute an operating system command from PL/SQL? ExampleThere is no direct way to execute an OS command from PL/SQL. Indirect methods like database pipes, external procedure listeners can be used. The commands can be executed within the pipe and the listener picks it up and run the requests. Results are passed back on a different database pipe.
|
|
Illustrate how to loop through table in PL/SQL.Cursors can be used to loop through tables in PL/SQL. Example:Employee cursor all employees for subject. CURSOR emp_cur (v_sub_no subject.subjectno%TYPE) IS
SELECT employeename FROM emp WHERE subjectno = v_ subjectno;
|
|
What is a mutating and constraining table?A mutating table is a table that is being modified by an INSERT, UPDATE or DELETE statement. When any such table is targeted by a trigger, oracle throws an error because it is under a mutated state. Such error also occurs if a trigger attempts to change the primary, foreign or unique key columns of the table. Such tables are constraining tables.
|
|
Difference between stored procedures and functions Stored procedures don’t return any value while functions return values.
Stored procedures are mainly used to process a task while functions are used to calculate. Functions can be called from procedures while the other way is not possible.
|
