SQL/PLSQL FAQ’s

1 triggers

A Database Trigger is a stored procedure that is fired when a DML operation is performed on the table.In total there are 13 types of Triggers

Sytax for creating a trigger:

CREATE OR REPLACE TRIGGER <TRIGGERNAME> before / after
[INSERT / UPDATE / DELTE ] ON <TABLE NAME>

{For each Statement / Row}
{When <condition…..>}
Types of Triggers:
Before
After
For each Row
For each Statement (default)
Instead of Trigger: This trigger is defined on a view rather than a table.

System Triggers: A new feature of Oracle8i, wherein the trigger is fired when the database startup / shutdown process.

Schema Triggers: These triggers are fired whenever a DDL statement is executed. (Creation or Deletion of any DB Objects)

 

Order of Trigger Firing:

  • Before Statement trigger (If present)
  • Each row affected by the statement
    • Execute row level trigger (If present)
    • Execute the statement itself
    • Execute the after row level trigger (If Present)
  • After statement trigger (If Present)

 

  1. What are the different types of joins available in Oracle?

Equi Join: When primary and foreign key relationship exists between the tables that are going to be joined.

Self Join : If comparision comes in a single table

Cartesian Join: When tables are joined without giving any join condition.

Inner Join: The resultant set includes all the rows that satisfy the join condition.

Outer Join: The resultant set includes the rows which doesn’t satisfy the join condition. The outer join operator Plus sign (+) will be included in the join condiiton.

Example: SELECT a. column1, a. column2, b.column3….. from a, b where

a.column1(+)=b.Column1

Here the rows from table a which doesn’t satisfy the join condition will also be fetched.

 

 

  1. What are Indexes? What are the different types of Index? If a table consists of more than one Index how to enforce the statement to use the second Index?

An Index is a DB object, which is used to improve the performance of the data retrieval.

CREATE INDEX <INDEX NAME> ON <TABLE name>.(<COLUMN name>)

Types of Indexes:

Bitmap Index (Used for Low cardinality column)

Btree Index (Used for high cardinality column)

 

  1. What is Mutating Table?

Table under transition is called Mutating Table.

 

  1. What is views? What is Inline View??

Views are window to a table. It contains no data, it is based on the actual table called the base table or a view.

Inline View means writing select statement in the Query itself instead of selecting a Column Name.

 

  1. What is a Cursor? When it is used?  What are different types of Cursors.

Cursor is a private SQL area created in SGA to do multi row operation in a PL/SQL programme

Explicit Cursor, Implicit Cursor.

Implicit Cursor: System (Oracle) automatically declares and uses for all DML SQL Statements.

Explicit Cursor: Cursor declared explicitly in the PL/SQL programme to do multi row operation

Syntax:

Declare

Cursor C1 is SELECT SAL, EMPNO FROM EMP

X number;

Y Varchar2(30);

Begin

Open C1;

Loop

Fetch C1 INTO x, y;

Exit when c1%NOTFOUND

End Loop;

End;

  1. What is for Cursor? When it is used? Is it necessary to write an explicit

exit in case for Cursor?

A Cursor for loop can be used simplify the explicit cursor, no need to explicitly

Open, fetch and close. No explicity EXIT statement is required.

 

  1. What are Cursor attributes? What is use of FOR UPDATE in Cursor?

%Found

%NotFound

%RowCount

%IsOpen

 

FOR UPDATE statement in Cursor is Used to Update a Column in the Selected table by using the CURRENT OF <cursor name>.

 

  1. What is a Package? What is the advantage of using Packages?

A Package is a PL/SQL Construct that allow related object to be stored together. Package contains 2 parts, Package Specification and Package Body, each stored separately in the Data Dicitionary.

Once the Package is called all the related Procedure and functions of the package gets compiled and stored in the memory as P-code.

How do u call a Package.

<PackageName>.<Procedure / Function Name> (Related Parameters….)

 

  1. Name some important Packages provided by Oracle?

DBMS_SQL, DBMS_JOBS, DBMS_DDL, DBMS_LOCK

 

  1. What is Overloading?

Overloading is oops concept(Object Oriented Programming)

By Using the same name we can write any number of Procedure or functions in a package but either number of parameters in the procedure/function must be vary or parameter datatype must vary.

 

  1. What is a Function?   Difference between Procedure and Function?

Function is a object that takes one or more arguments and returns only value. But in case of procedures we can return more than one parameters.

Function always returns a value, whereas procedure may or may not return a value.

 

 

  1. What is the Package used in Oracle to do the File Operation?

UTL_FILE

 

  1. What is Dynamic SQL? How Dynamic SQL can be built?

The SQL statement which are built at run time are called the Dynamic SQL. Dynamic SQL can be built by using DBMS_SQL package.

Procedure of Dynamic SQL

OPEN_CURSOR, PARSE, BIND_VARIABLE, DEFINE_COLUMN, EXECUTE, FETCH_ROWS, CLOSE_CURSOR.

Oracle8i onwards there is another built in to construct Dynamic SQL called EXECUTE_IMMEDIATE.

 

 

  1. What is an exception? What are the different types of Exception? How do u

declare a user defined exception?

The error condition in PL/SQL is termed as an exception. Two types of

Exception:

Pre-Defined Exception: Example No_Data_Found, Storage_Error,

Zero_Error, Invlid_Cursor, Too_Many_Rows

User-Defined Exception: Anything

 

Syntax:

Declare

Xyz Exception;

Begin

SELECT ENAME FROM EMP

RAISE XYZ;

End;

 

  1. what could happen if we use WHEN OTHERS before any predefined exceptions

According to the Oracle standards “ When Others “ exception must be the last exception. All the Predefined exceptions must be used before the “When others” exception.

If “ When others” exception used before any pre-defined exceptions then procedure/function shows the compilations errors

 

  1. List out some features in 8i

Bitmap Indexes, Drop a Column, Bulk Insert and Bulk Update

Materialized views, Dynamic Sql(Execute Immediate etc)

 

  1. List some 9iFeatures

External tables ( We query the data directly from a file like select * from “c:/abcd.csv” )

Multi Table Insert with single command, resumable process etc.

 

  1. What are SQLCODE and SQLERRM and why are they important for PL/SQL

developers?

 

SQLCODE returns the value of the error number for the last error encountered. The SQLERRM returns the actual error message for the last error encountered. They can be used in exception handling to report, or, store in an error log table, the error that occurred in the code. These are especially useful for the WHEN OTHERS exception

 

  1. What is the use of Pragma_Init exception

By using this we can define our messages by handling the oracle messages

 

  1. What are temporary tables? How many types?

Temporary tables are used to store the data temporarly. Mainly there are 2 types

They are transaction and Session types

Syntax: Create global temporary table <temp_tab> as select * from emp;

This temporarly table is used to store the data temparorly once you exit from session then that table will get erased

  1. Some of the System Tables
  1. User_source table will stores the information of the user defined definitions
  2. All_Source and dba_source tables will stores the system defined schema objects definitions as well as user defined.
  3. All_Tab_Columns and ben_all_tab_columns are used to list out the all the columns name and respected table names also.

 

  1. Write a query to list out the employees with their respective manager levels?

 

select lpad(‘*’, level * 2), empno, ename, mgr from emp

connect by prior empno = mgr start with empno = 7839

It results the hierarchy of the employees

Related Posts

CheapSexCams
You can leave a response, or trackback from your own site.

Leave a Reply

Powered by k2schools