PL/SQL Triggers

Database Trigger


A database trigger is a block of code that is automatically executed in response to certain events. Triggers are executed implicitly whenever the triggering event happens.

The triggering event is either a INSERT, DELETE, or UPDATE command.

The timing can be either BEFORE or AFTER, INSTEAD OF trigger

The trigger can be either row-level or statement-level, where the former fires once for each row affected by the triggering statement and the latter fires once for the whole statement.

Example

Example of creating a trigger based on the following two tables:

CREATE TABLE T1 (a INTEGER);
CREATE TABLE T2 (b INTEGER);

We will create a trigger that may insert a tuple into T2 when a tuple is inserted into T1. The trigger checks if the inserted row in T1 is has a value less than 5 only then a tuple is inserted in T2.

CREATE TRIGGER tr1
AFTER INSERT ON T1
REFERENCING NEW AS newRow
FOR EACH ROW
WHEN (newRow.a <= 5)
BEGIN
INSERT INTO T2 VALUES(:newRow.a);
END tr1;
.
run;

Displaying Trigger Errors

If we get a message Warning: Trigger created with compilation errors. you can check the error messages with: show errors trigger ; You can also type, SHO ERR (SHOW ERRORS) to see the most recent compilation error.

Viewing Defined Triggers


To view all the defined triggers, use:
select name_of_trigger from user_triggers;
For more details on a particular trigger:
select trigger_type, triggering_event, table_name, referencing_names, trigger_body
from user_triggers
where trigger_name = '<>';


Disabling Triggers

To disable or enable a trigger:
alter trigger <> {disableenable};

Mutating Table Errors


Permissions

For creating triggers you should have create trigger privilege :

Example

Grant create trigger to John; Sample table:
create table t1 (
id int,
name varchar(10),
primary key(id)
);

Before insert trigger:

Create or replace
Trigger tr1
Before
Insert
On t1
For each row
Begin
Dbms_output.put_line(’before insert of ’ :new.name);
End;

We will insert some data to see if our triggers work or not:

insert into t1(id,name) values (1,’sam’);
Result:
before insert of sam

After insert trigger:
Create or replace
Trigger tr1
After
Insert
On t1
For each row
Begin
Dbms_output.put_line(’After insert of ’ :new.name);
End;

We will insert some data to see if our triggers work or not:

insert into t1(id,name) values (1,’sam’);
Result:
After insert of sam
Before Update Statement Trigger:
create or replace
trigger tr1
before update
on t1
begin
dbms_output.put_line(’before updating some names(s)’);
end;

create or replace
trigger tr1
before update
on t1
for each row
begin
dbms_output.put_line(’before updating ’
(:old.name) ’ to ’
to_char(:new.name));
end;

IF statements

create or replace
trigger tr1
before insert or update or delete on t1
for each row
begin
if inserting then
dbms_output.put_line(’inserting : ’ :new.name);
elsif updating then
dbms_output.put_line(’updating : ’
:old.name ’ to ’ :new.name);
elsif deleting then
dbms_output.put_line(’deleting : ’ :old.name);
end if;
end;

We will insert some data to see if our triggers work or not:


insert into t1(id,name) values (1,’sam’);
Result:
inserting : sam
update t1 set name = ’g’ where name = ’a’;
updating: g to s

Working with Views

we will create a view (of t1 table):
CREATE OR REPLACE
VIEW t1_VIEW AS
SELECT NAME FROM t1;

Now, we know that updating (or inserting) into a view is kind of pointless; however, we can provide this functionality using a trigger!

Example:

CREATE OR REPLACE
TRIGGER t1_VIEW_INSERT
INSTEAD OF INSERT ON t1_VIEW
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE(’INSERTING: ’ :NEW.NAME);
-- we can also do
-- INSERT INTO t1(ID,NAME) VALUES (N,:NEW.NAME);
END;
When we do an insert statement on t1_VIEW:
INSERT INTO t1_VIEW(NAME) VALUES (’ poly’);
Which produces the result:
INSERTING: poly

The trigger will be fired when someone will try to insert a value into a VIEW.



QTP-7
61) D, 62) A, 63) D, 64) C, 65) A, 66) C, 67) A, 68) B, 69) D, 70) D

SQL PL/SQL Interview Questions

SQL PL/SQL – Questions

SQL PL/SQL Question 1

How to display row number with records?

Select rownum, ename from emp;

SQL PL/SQL Question 2

How to view version information in Oracle?

Select banner from v$version;

SQL PL/SQL Question 3

How to find the second highest salary in emp table?

select min(sal) from emp a
where 1 = (select count(*) from emp b where a.sal <>

SQL PL/SQL Question 4

How to delete the duplicate rows from a table?

create table t1 ( col1 int, col2 int, col3 char(1) );
insert into t1 values(1,50, ‘a’);
insert into t1 values(1,50, ‘b’);
insert into t1 values(1,89, ‘x’);
insert into t1 values(1,89, ‘y’);
insert into t1 values(1,89, ‘z’);

select * from t1;


Col1Col2Col2
150a
150b
289x
289y
289z

delete from T1
where rowid <> ( select max(rowid)
from t1 b
where b.col1 = t1.col1
and b.col2 = t1.col2 ) 3 rows deleted.

select * from t1;

Col1Col2Col2
150a
289z
will do it.

SQL PL/SQL Question 5

How to select a row using indexes?

You have to specify the indexed columns in the WHERE clause of query.

SQL PL/SQL Question 6

How to select the first 5 characters of FIRSTNAME column of EMP table?

select substr(firstname,1,5) from emp

SQL PL/SQL Question 7

How to concatenate the firstname and lastname from emp table?

select firstname ‘ ‘ lastname from emp

SQL PL/SQL Question 8

What's the difference between a primary key and a unique key?
Primary key does not allow nulls,

Unique key allow nulls.

SQL PL/SQL Question 9

What is a self join?

A self join joins a table to itself.

Example

SELECT a.last_name Employee, b.last_name Manager
FROM employees a, employees b
WHERE b.employee_id = a.manager_id;

SQL PL/SQL Question 10

What is a transaction and ACID?

Transaction - A transaction is a logical unit of work. It must be commited or rolled back.

ACID - Atomicity, Consistency, Isolation and Duralbility, these are properties of a transaction.

SQL PL/SQL Question 11

How to add a column to a table?

alter table t1 add sal number;

alter table t1 add middle_name varchar(20);

SQL PL/SQL Question 12

Is it possible for a table to have more than one foreign key ?

A table can have any number of foreign keys. It can have only one primary key .

SQl PL/SQL Question 13

How to display number value in words?
SQL> select sal, (to_char(to_date(sal,'j'), 'jsp')) from emp;

SQL PL/SQL Question 14

What is candidate key, alternate key, composite key.
Candidate Key A candidate key is one that can identify each row of a table uniquely. Generally a candidate key becomes the primary key of the table.

Alternate KeyIf the table has more than one candidate key, one of them will become the primary key, and the rest are called alternate keys.

Composite Key: - A key formed by combining at least two or more columns is called composite key.

SQL PL/SQL Question 15

What's the difference between DELETE TABLE and TRUNCATE TABLE commands? Explain drop command.
Both Delete and Truncate will leave the structure of the table. Drop will remove the structure also.

  1. Example

    If tablename is T1.

    To remove all the rows from a table t1.

    Delete t1

    Truncate table t1

    Drop table t1.

  2. Truncate is fast as compared to Delete. DELETE will generate undo information, in case of rollback, but TRUNCATE will not.


  3. Full Table scan and index fast scan read data blocks up to high water mark and truncate resets high water mark but delete does not.So full table scan after Delete will not improve but after truncate it will be fast.

  4. Delete is DML. Because truncate is a DDL, it performs implicit commit. You cannot rollback a truncate. Any uncommitted DML changes will also be committed with the TRUNCATE.

  5. You cannot specify a WHERE clause in the TRUNCATE statement, but you can specify that in Delete.

  6. When you truncate a table the storage for the table and all the indexes can be reset back to its initial size,but a Delete will never shrink the size of the a table or its indexes.

About Dropping

Dropping a table removes the data and definition of the table. The indexes, constraints, triggers, and privileges on the table are also dropped. The action of dropping a table cannot be undone. The views, materialized views or other stored programs that reference the table are not dropped but they are marked as invalid.

SQL PL/SQL Question 16

Explain the difference between a FUNCTION, PROCEDURE and PACKAGE.

Procedures and functions are stored in compiled form in database.

Functions take zero or more parameters and return a value. Procedures take zero or more parameters and return no values.

Both functions and procedures can take or return zero or more values through their parameter lists.

Another difference between procedures and functions, other than the return value, is how they are called. Procedures are called as stand-alone executable statements:

my_procedure(parameter1,parameter2...);

Functions can be called anywhere in an valid expression :

e.g
1) IF (tell_salary(empno) < 500 ) THEN … 2) var1 := tell_salary(empno); 3) DECLARE var1 NUMBER DEFAULT tell_salary(empno); BEGIN …

Packages contain function , procedures and other data structures.

There are a number of differences between packaged and non-packaged PL/SQL programs.

Package The data in package is persistent for the duration of the user’s session.The data in package thus exists across commits in the session.

If you grant execute privilege on a package, it is for all functions and procedures and data structures in the package specification. You cannot grant privileges on only one procedure or function within a package.

You can overload procedures and functions within a package, declaring multiple programs with the same name. The correct program to be called is decided at runtime, based on the number or datatypes of the parameters.

SQL PL/SQL Question 17

Describe the use of %ROWTYPE and %TYPE in PL/SQL

%ROWTYPE associates a variable to an entire table row.

The %TYPE associates a variable with a single column type.

SQ PL/SQL Question 18

What are SQLCODE and SQLERRM and why are they important for PL/SQL developers?

SQLCODE returns the current database error number. These error numbers are all negative, except NO_DATA_FOUND, which returns +100.

SQLERRM returns the textual error message.. These are used in exception handling.

SQL PL/SQL Question 19

How can you find within a PL/SQL block, if a cursor is open?

By the Use of %ISOPEN cursor variable.

SQL PL/SQL Question 20

How do you debug output from PL/SQL?

By the use the DBMS_OUTPUT package.

By the use of SHOW ERROR command, but this only shows errors.

The package UTL_FILE can also be used.

SQL PL/SQL Question 21

What are the types of triggers?
  • Use Row and Statement Triggers
  • Use INSTEAD OF Triggers

    SQL PL/SQL Question 22

    Explain the usage of WHERE CURRENT OF clause in cursors ?

    It refers to the latest row fetched from a cursor in an update and delete statement.

    SQL PL/SQL Question 23

    Name the tables where characteristics of Package, procedure and functions are stored ?

    User_objects, User_Source and User_error.

    SQL PL/SQL Question 24

    What are two parts of package ?

    They consist of package specification, which contains the function headers, procedure headers, and externally visible data structures. The package also contains a package body, which contains the declaration, executable, and exception handling sections of all the bundled procedures and functions.

    SQL PL/SQL Question 25

    What are two virtual tables available during database trigger execution ?

    The table columns are referred as OLD.column_name and NEW.column_name.

    For INSERT only TRIGGERS NEW.column_name values ARE only available.

    For UPDATE only TRIGERS OLD.column_name NEW.column_name values ARE only available.

    For DELETE only TRIGGERS OLD.column_name values ARE only available.v

    SQL PL/SQL Question 26

    What is Overloading of procedures ?

    REPEATING OF SAME PROCEDURE NAME WITH DIFERENT PARAMETER LIST.

    SQL PL/SQL Question 27

    What are the return values of functions SQLCODE and SQLERRM ?

    SQLCODE returns the latest code of the error that has occurred.

    SQLERRM returns the relevant error message of the SQLCODE.

    SQL PL/SQL Question 28

    Is it possible to use Transaction control Statements such a ROLLBACK or COMMIT in Database Trigger ? Why ?

    It is not possible.,because of the side effect to transactions. You can use them indirectly by calling procedures or functions .

    SQL PL/SQL Question 29

    What are the modes of parameters that can be passed to a procedure ?

    IN, OUT, IN-OUT parameters.



  • QC -7
    1) A, 2) C, 3) D, 4) B, 5) A, 6) A, 7) B, 8) A, 9) C, 10) C

    Oracle PL/SQL Implicit Cursors

    Implicit Cursors

    Implicit cursors are automatically created and used by Oracle every time you issue a select statement in PL/SQL. If you use an implicit cursor, Oracle will perform the open, fetches, and close for you automatically. Implicit cursors are used in statements that return only one row. If the SQL statement returns more than one row, an error will occur.

    For a long time there have been debates over the relative merits of implicit cursorsand explicit cursors. The short answer is that implicit cursors are faster and result in much neater code so there are very few cases where you need to resort to explicit cursor.

    The process of an implicit cursor is as follows:

    implicit cursors Whenever an SQL statement is executed, any given PL/SQL block issues an implicit cursor, as long as an explicit cursor does not exist for that SQL statement.
    implicit cursors A cursor is automatically associated with every DML statement (UPDATE, DELETE, and INSERT).
    implicit cursors All UPDATE and DELETE statements have cursors that recognize the set of rows that will be affected by the operation.
    implicit cursors An INSERT statement requires a place to accept the data that is to be inserted in the database; the implicit cursor fulfills this need.
    implicit cursors The most recently opened cursor is called the “SQL%” Cursor.

    The implicit cursor is used to process INSERT, UPDATE, DELETE, and SELECT INTO statements. Oracle automatically performs the OPEN, FETCH, and CLOSE operations, during the processing of an implicit cursor.

    In the following PL/SQL code block, the select statement makes use of an implicit cursor:

    Begin
    Update emp Where 1=2;
    Dbms_output.put_line(sql%rowcount ||’ ‘|| ‘ rows are affected by the update statement’);
    End;

    SELECT SUM (sal) INTO TOTAL
    FROM emp
    WHERE depno = 10;

    Oracle PL/SQL Explicit Cursors

    Explicit cursors are created by the programmer, and with these you can do operations on a set of rows, which can be processed one by one. You use explicit cursors when you are sure that the SQL statement will return more than one row. You have to declare an explicit cursor in the declare section at the beginning of the PL/SQL block. Once you declare your cursor, the explicit cursor will go through these steps:

    Explicit cursors Declare This clause initializes the cursor into memory.

    Explicit cursors Open The previously declared cursor is now open and memory is allotted.

    Explicit cursors Fetch The previously declared and opened cursor can now access data;

    Explicit cursors Close The previously declared, opened, and fetched cursor is closed, which also releases memory allocation.

    SQL> set serveroutput on;
    SQL> Declare
    2 Cursor empcursor(empn in number)
    3 Is select * from emp where empno=empn;
    4
    5 Empvar emp%rowtype;
    6 Begin
    7 Dbms_output.put_line('Getting records for employee no. 7521');
    8 Open empcursor(7521);
    9 Loop
    10 Fetch empcursor into empvar;
    11 Exit when empcursor%notfound;
    12 Dbms_output.put_line('emp name : ' || empvar.ename);
    13 Dbms_output.put_line('emp salary : ' || empvar.sal);
    14 End loop;
    15 Close empcursor;
    16 End;
    17 /
    Getting records for employee no. 7521
    emp name : WARD
    emp salary : 1250

    PL/SQL procedure successfully completed.

    Cursor Attributes

    Here are the main cursor attributes:

    %ISOPEN It returns TRUE if cursor is open, and FALSE if it is not.

    %FOUND It returns TRUE if the previous FETCH returned a row and FALSE if it did not

    %NOTFOUND It returns TRUE if the previous FETCH did not return a row and FALSE if it did.

    %ROWCOUNT It gives you the number of rows the cursor fetched so far.

    Some more examples of Explicit Cursors:

    Declare

    CURSOR cur_emp IS

    select empno, ename from emp;

    var_empno emp.empno%type;

    var_ename emp.ename%type;

    Begin

    OPEN cur_emp;

    FETCH cur_emp into var_empno,var_ename;

    WHILE cur_emp%FOUND

    Loop

    Dbms_output.put_line(var_empno || ', ' || var_ename);

    FETCH cur_emp into var_empno,var_ename;

    End loop;

    CLOSE cur_emp;

    End;

    Another approach:

    Declare

    CURSOR cur1_emp IS

    select empno, ename from emp;

    Begin

    FOR r1_emp in cur1_emp

    Loop

    Dbms_output.put_line(r1_emp.empno || ', ' || r1_emp.ename);

    End loop;

    End;

    Oracle PL/SQL Cursor For Loops

    The following procedure is followed in most of the situations in PL/SQL:

    1. Open a cursor

    2. Start a loop

    3. Fetch the cursor

    4. Check whether rows are returned

    5. Process

    6. Close the loop

    7. Close the cursor

    Cursor FOR loop allows us to simplify this procedure by letting PL/SQL do most of the things for us.

    Let us rewrite the previous example (used in Cursors with Parameters) again using Cursor FOR loop

    Using a cursor FOR loop

    DECLARE

    CURSOR cur1_dept IS SELECT deptno, dname

    FROM dept ORDER BY deptno;

    CURSOR cur1_emp (par1_dept VARCHAR2) IS

    SELECT ename, salary

    FROM emp

    WHERE deptno = par1_dept

    ORDER BY ename;

    var_total_salary NUMBER (10,2);

    BEGIN

    FOR r1_dept IN cur1_dept LOOP

    DBMS_OUTPUT.PUT_LINE ('Department : ' || r1_dept.deptno|| ' - '|| r1_dept.dname);

    var_total_salary := 0;

    FOR r1_emp IN cur1_emp (r1_dept.deptno) LOOP

    DBMS_OUTPUT.PUT_LINE ('Name: ' ||r1_emp.ename || 'Salary:'||r1_emp.salary);

    var_total_salary := var_total_salary + r1_emp.salary;

    END LOOP;

    DBMS_OUTPUT.PUT_LINE ('Total Salary for Dept: ' || var_total_salary);

    END LOOP;

    END;

    /

    Oracle PL/SQL Cursors With Parameters

    We can pass parameters into a cursor and use them in the query.

    We can only pass values to the cursor; and cannot pass values out of the cursor through parameters. Only the datatype of the parameter is defined, not its length. Optionally, we can also give a default value for the parameter, which will take effect if no value is passed to the cursor.

    The following cursor prints department number and name in one line followed by employees working in that department (name and salary) and total salary.

    Cursors with parameters

    DECLARE

    CURSOR cur_dept IS SELECT * FROM dept ORDER BY deptno;

    CURSOR cur_emp (par_dept VARCHAR2) IS

    SELECT ename, salary

    FROM emp

    WHERE deptno = par_dept

    ORDER BY ename;

    r_dept DEPT%ROWTYPE;

    var_ename EMP.ENAME%TYPE;

    var_salary EMP.SALARY%TYPE;

    var_tot_salary NUMBER (10,2);

    BEGIN

    OPEN cur_dept;

    LOOP

    FETCH cur_dept INTO r_dept;

    EXIT WHEN cur_dept%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE ('Department : ' || r_dept.deptno || ' - '|| r_dept.dname);

    var_tot_salary := 0;

    OPEN cur_emp (r_dept.deptno);

    LOOP

    FETCH cur_emp INTO var_ename, var_salary;

    EXIT WHEN cur_emp%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE ('Name: ' ||var_ename || ' Salary:'||var_salary);

    var_tot_salary := var_tot_salary + var_salary;

    END LOOP;

    CLOSE cur_emp;

    DBMS_OUTPUT.PUT_LINE ('Total Salary for Dept: ' || var_tot_salary);

    END LOOP;

    CLOSE cur_dept;

    END;

    /

    Important points to keep in mind for parameters in cursors are:

    The mode of the parameters can only be IN.

    Cursor becomes more reusable with Cursor parameters.

    Default values can be assigned to Cursor parameters.

    The scope of the cursor parameters is local to the cursor.

    Oracle PL/SQL REF Cursors

    A REF CURSOR or cursor variable is just a reference or a handle to a static cursor. It allows a user to pass this “reference to the same cursor” among all the programs that need access to the cursor. Cursor variables give you easy access to centralized data retrieval.

    There are two types of cursor variables; one is called strong and the other is called weak.

    What is the difference between cursor and ref cursor, and when would you appropriately use each of these?

    Technically, under the covers, at the most "basic level", they are the same.

    A "normal" plsql cursor is static in defintion.

    Ref cursors may be dynamically opened or opened based on logic.



    Declare

    type rc is ref cursor;

    cursor c is select * from dual;

    l_cursor rc;

    begin

    if ( to_char(sysdate,'dd') = 30 ) then

    open l_cursor for 'select * from emp';

    elsif ( to_char(sysdate,'dd') = 29 ) then

    open l_cursor for select * from dept;

    else

    open l_cursor for select * from dual;

    end if;

    open c;

    end;

    /

    * Given that block of code -- you see perhaps the most "salient" difference -- no matter how many times you run that block -- cursor C will always be select * from dual. The ref cursor can be anything.

    * Another difference is a ref cursor can be returned to a client. A plsql "cursor cursor" cannot be returned to a client.

    * Another difference is a cursor can be global -- a ref cursor cannot (you cannot define them OUTSIDE of a procedure / function)

    * Another difference is a ref cursor can be passed from subroutine to subroutine -- a cursor cannot be.

    * Another difference is that static sql (not using a ref cursor) is much more efficient then using ref cursors and that use of ref cursors should be limited to

    - returning result sets to clients

    - when there is NO other efficient/effective means of achieving the goal

    * That is, you want to use static SQL (with implicit cursors really) first and use a ref cursor only when you absolutely have to.


    A ref cursor is a variable, defined as a cursor type, which will point to, or reference a cursor result. The advantage that a ref cursor has over a plain cursor is that is can be passed as a variable to a procedure or a function. The REF CURSOR XE "REF CURSOR" can be assigned to other REF CURSOR variables. This is a powerful capability in that the cursor can be opened, then passed to another block for processing, then returned to the original block to be closed. The cursor variable can also be returned by a function and assigned to another variable. The REF CURSOR variable is not a cursor, but a variable that points to a cursor. Before assigning a cursor variable, a cursor type must be defined.



    type author_cursor is ref cursor;

    This REF CURSOR XE "REF CURSOR" is a weak typed cursor variable because it does not define the datatype the cursor will return. Below is the same cursor that is strongly typed.

    type author_cursor is ref cursor
    returning author%rowtype;

    By strongly typing the cursor variable, you define what the cursor can return. If a strongly typed cursor returns something other that its return type, a ROWTYPE_MISMATCH XE "ROWTYPE_MISMATCH" exception is raised. A strongly typed cursor type is less flexible but less prone to programming errors. The PL/SQL compiler will verify that the FETCH clause XE "FETCH clause" has the correct variable/record for the cursor return type at compile time.

    Once the cursor type is defined, the actual variable can be defined as the cursor type.



    c1 author_cursor;

    Now c1 is a variable of a cursor type. It is opened using a SQL statement.

    open c1 for select * from authors;

    Now c1 has all the attributes of the actual cursor. As with any cursor it is important to close the cursor as soon as you have completed processing.



    SQL> declare
    2 type auth_cursor is ref cursor;
    3 c1 auth_cursor;
    4 r_c1 author%rowtype;
    5 begin
    6 open c1 for select * from author;
    7 fetch c1 into r_c1;
    8 if c1%isopen then
    9 dbms_output.put_line(
    'The Cursor is open.');
    10 end if;
    11 dbms_output.put_line(
    'Row Count is '||c1%rowcount);
    12 close c1;
    13 if not c1%isopen then
    14 dbms_output.put_line(
    'The Cursor is closed.');
    15 end if;
    16 end;
    17 /


    The Cursor is open.
    Row Count is 1
    The Cursor is closed.

    Here some of the cursor attributes are used to process the cursor. Notice that the record used to hold a fetched cursor row is defined as an author table %rowtype XE "%rowtype" . Even though the example cursor variable is defined, the record can not use it because the cursor variable return type is not defined. The example below attempts to create a cursor %rowtype variable for processing.



    SQL> declare
    2 type auth_cursor is ref cursor;
    3 c1 auth_cursor;
    4 r_c1 c1%rowtype;
    5 begin
    6 null;
    7 end;
    8 /
    r_c1 c1%rowtype;
    *
    ERROR at line 4:
    ORA-06550: line 4, column 8:
    PLS-00320: the declaration of the type of this
    expression is incomplete or malformed
    ORA-06550: line 4, column 8:
    PL/SQL: Item ignored

    However, a strongly typed cursor can use the cursor variable to define the cursor record.



    SQL> declare
    2 type auth_cursor is ref cursor
    return author%rowtype;
    3 c1 auth_cursor;
    4 r_c1 c1%rowtype;
    5 begin
    6 null;
    7 end;
    8 /

    In this example, the auth_cursor type returns an author%rowtype. Because this is defined in line 2, the record defined in line 4 can now use it.

    Cursor variables that are weakly typed can be defined to return any values. In the example below, the cursor variable c1 is defined as three different statements.



    SQL> declare
    2 type auth_cursor is ref cursor;
    3 c1 auth_cursor;
    4 r_c1 author%rowtype;
    5 r2_c1 book%rowtype;
    6 r3_c1 number;
    7 begin
    8 open c1 for select * from author;
    9 fetch c1 into r_c1;
    10 close c1;
    11 open c1 for select * from book;
    12 fetch c1 into r2_c1;
    13 close c1;
    14 open c1 for select sum(quantity)
    15 from store join sales
    using (store_key)
    16 group by store_name;
    17 fetch c1 into r3_c1;
    18 close c1;
    19 end;
    20 /

    Although the block does not do anything but open and close the cursor variable, it does demonstrate that weakly typed variables can be defined differently each time the variable is opened. But what happens when a cursor variable is defined with a SQL statement but returns unexpected values? Below is an example of defining a cursor variable of one type and the record as another. The cursor variable is returning all columns in the book table, but the receiving variable is defined as a record of columns from the author table.



    SQL> declare
    2 type auth_cursor is ref cursor;
    3 c1 auth_cursor;
    4 r_c1 author%rowtype;
    5 begin
    6 open c1 for select * from book;
    7 fetch c1 into r_c1;
    8 close c1;
    9 end;
    10 /
    declare
    *
    ERROR at line 1:
    ORA-06504: PL/SQL: Return types of Result Set
    variables or query do not match
    ORA-06512: at line 7

    Notice that the error message is pointing to the DECLARE clause. That is because the block successfully compiled and only threw the error when it was executed. The PL/SQL compiler can not catch the error because the cursor type did not define a return type. By changing the definition of the cursor type to a strongly typed definition, the compiler will catch this error when the code is compiled.



    SQL> declare
    2 type auth_cursor is ref cursor
    3 return book%rowtype;
    4 c1 auth_cursor;
    5 r_c1 author%rowtype;
    6 begin
    7 open c1 for select * from book;
    8 fetch c1 into r_c1;
    9 close c1;
    10 end;
    11 /

    fetch c1 into r_c1;
    *
    ERROR at line 8:
    ORA-06550: line 8, column 5:
    PLS-00394: wrong number of values in the INTO
    list of a FETCH statement
    ORA-06550: line 8, column 5:
    PL/SQL: SQL Statement ignored

    Now the compiler catches the error. So far the examples have been using the cursor variables as regular cursors. The real advantage of using a cursor variable is the ability to pass it as a parameter. In the example below a local function is used to open a cursor called c1 and return it.

    The block body calls the function to assign the cursor to the cursor variable c2. The block body then processes the cursor and closes it.



    SQL> declare
    2 type auth_cursor is ref cursor
    return author%rowtype;
    3 c2 auth_cursor;
    4 r_c2 c2%rowtype;
    5
    6 function get_auth return auth_cursor
    7 is
    8 c1 auth_cursor;
    9 begin
    10 open c1 for select * from author;
    11 return c1;
    12 end;
    13
    14 begin
    15 c2 := get_auth;
    16 loop
    17 fetch c2 into r_c2;
    18 exit when c2%notfound;
    19 dbms_output.put_line(initcap(
    r_c2.author_last_name));
    20 end loop;
    21 close c2;
    22 end;
    23 /

    Jones
    Hester
    Weaton
    Jeckle
    Withers
    Petty
    Clark
    Mee
    Shagger
    Smith

    Line 2 defines the cursor type. Lines 3 and 4 define the cursor and return record used in the body. Line 6 declares a local function called get_auth that returns an auth_cursor type. Inside this local function, cursor c1 is defined as an auth_cursor type, opened and returned to the calling code. The function is actually executed on line 15 when c2 is assigned the return value of the get_auth function. The cursor c2 is processed and finally closed on line 21. Note that c1 opened the cursor and c2 closed it. This is an important point.


    The example contains only ONE cursor. When c2 is assign the value of c1, both variables point to the same cursor. Remember that c1 and c2 are variables that point to or reference the actual cursor.

    The same basic example is shown below except, the output is generated by a local procedure. Note that the procedure print_name gets passed the cursor c2 and then processes it. It then passes the cursor backup to be closed by the body of the PL/SQL block.



    SQL> declare
    2 type auth_cursor is ref cursor
    return author%rowtype;
    3 c2 auth_cursor;
    4 r_c2 c2%rowtype;
    5
    6 function get_auth return auth_cursor
    7 is
    8 c1 auth_cursor;
    9 begin
    10 open c1 for select * from author;
    11 return c1;
    12 end;
    13
    14 procedure print_name (c3 in out auth_cursor)
    15 as
    16 r_c3 author%rowtype;
    17 begin
    18 loop
    19 fetch c3 into r_c3;
    20 exit when c3%notfound;
    21 dbms_output.put_line(
    initcap(r_c3.author_last_name));
    22 end loop;
    23 end;
    24
    25 begin
    26 c2 := get_auth;
    27 print_name(c2);
    28 close c2;
    29 end;
    30 /

    Jones
    Hester
    Weaton
    Jeckle
    Withers
    Petty
    Clark
    Mee
    Shagger
    Smith

    There are three items you should note about this PL/SQL block. First, the body is pretty simple to understand.

    Get the authors, print the names, and close the cursor. Second, you can pass cursor variables to procedures and functions, and functions can return the variables. Lastly, it can become confusing about when a cursor variable should be closed.

    Oracle PL/SQL WHERE CURRENT OF & FOR UPDATE

    Oracle PL/SQL WHERE CURRENT OF & FOR UPDATE

    The WHERE CURRENT OF clause is used in some UPDATE and DELETE statements.

    The WHERE CURRENT OF clause in an UPDATE or DELETE statement states that the most recent row fetched from the table should be updated or deleted.We must declare the cursor with the FOR UPDATE clause to use this feature.

    When the session opens a cursor with the FOR UPDATE clause, all rows in the return set will hold row-level exclusive locks. Other sessions can only query the rows, but they cannot update, delete, or select with FOR UPDATE.

    Oracle provides the FOR UPDATE clause in SQL syntax to allow the developer to lock a set of Oracle rows for the duration of a transaction.


    The syntax of using the WHERE CURRENT OF clause in UPDATE and DELETE statements follows:

    WHERE [CURRENT OF cursor_name | search_condition]

    The following example opens a cursor for employees and updates the commission, if there is no commission assigned based on the salary level.

    DECLARE

    CURSOR c_1 IS SELECT empno, ename, salary
    FROM emp
    WHERE comm IS NULL
    FOR UPDATE OF comm;

    var_comm NUMBER(10,2);

    BEGIN

    FOR r_1 IN c_1 LOOP
    IF r_1.salary < 5000 THEN
    var_comm := r_1.salary * 0.25;
    ELSIF r_1.salary < 10000 THEN
    var_comm := r_1.salary * 0.20;
    ELSIF r_1.salary < 30000 THEN
    var_comm := r_1.salary * 0.15;
    ELSE
    var_comm := r_1.salary * 0.12;
    END IF;

    UPDATE emp
    SET comm = var_comm
    WHERE CURRENT OF c_1;

    END LOOP;
    END;
    /

    The FOR UPDATE clause in the SELECT statement can only be specified in the top level; subqueries cannot have this clause.

    Examples of Oracle PL/SQL Cursors

    Examples of Oracle PL/SQL Cursors:

    1)

    Declare

    cursor c_1 is

    select * from emp;

    BEGIN

    for emp_rec in c_1 loop

    dbms_output.put_line(emp_rec.ename);

    end loop;

    END;

    2)

    create or replace procedure sum_of_salary IS

    cursor c_1 is

    select * from emp;

    salary_sum integer;

    BEGIN

    salary_sum := 0;

    for emp_rec in c_1 loop

    salary_sum := salary_sum+ emp_rec.sal;

    end loop;

    dbms_output.put_line('Salary sum: ' || salary_sum);

    END;

    3)

    Retrieving the records from the emp table and displaying them one by one using cursors:

    CREATE OR REPLACE PROCEDURE my_proc IS

    var_empno emp.empno%type;

    var_ename emp.ename%type;

    var_sal emp.sal%type;

    //declaring a cursor//


    CURSOR EMP_CURSOR IS

    select empno, ename, sal from emp;

    BEGIN

    //opening a cursor//

    open EMP_CURSOR;

    LOOP

    //fetching records from a cursor//

    fetch EMP_CURSOR into var_empno, var_ename, var_sal;

    //testing exit conditions//

    EXIT when EMP_CURSOR%NOTFOUND;

    IF (var_sal > 1000) then

    DBMS_OUTPUT.put_line(var_empno || ' ' || var_ename || ' ' || var_sal);

    ELSE

    DBMS_OUTPUT.put_line(var_ename || ' sal is less then 1000');

    END IF;

    END LOOP;

    //closing the cursor//

    close EMP_CURSOR;

    DBMS_OUTPUT.put_line('DONE');

    END;

    4)

    Declare

    CURSOR EMP_CURSOR IS

    select * from emp;

    variable_cur EMP%ROWTYPE;

    BEGIN

    open EMP_CURSOR;

    LOOP

    fetch EMP_CURSOR into variable_cur;

    EXIT when EMP_CURSOR%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE(variable_cur.empno || ' ' || variable_cur.ename ||' ' ||

    variable_cur.job || ' ' ||variable_cur.mgr || ' ' ||

    variable_cur.hiredate|| ' ' ||variable_cur.sal || ' ' ||

    variable_cur.deptno);

    END LOOP;

    close EMP_CURSOR;

    DBMS_OUTPUT.put_line('DONE');

    END;

    /

    5)

    Declare

    CURSOR MY_CUR IS

    select * from emp;

    v_cur EMP%ROWTYPE;

    BEGIN

    open my_cur;

    LOOP

    fetch my_cur into v_cur;

    EXIT when my_cur%ROWCOUNT = 5;

    DBMS_OUTPUT.PUT_LINE(v_cur.empno || ' ' || v_cur.ename ||' ' ||

    v_cur.job || ' ' ||v_cur.mgr || ' ' ||

    v_cur.hiredate|| ' ' ||v_cur.sal || ' ' ||

    v_cur.deptno);

    END LOOP;

    close my_cur;

    DBMS_OUTPUT.put_line('DONE');

    END;

    Oracle PL/SQL Records

    Table Based Records

    You define and declare records either in the declaration section of a PL/SQL block, or globally, via a package specification.

    %TYPE and %ROWTYPE

    %TYPE is used to declare a variable that is of the same type as a specified table’s column.

    Emp_number emp.empno%type;

    %ROWTYPE is used to declare a record(variable that represents the entire row of a table).

    Emp_record emp%rowtype

    Declare table-based record for employee table.

    emp_rec employee%ROWTYPE

    Cursor Based Records

    CURSOR c IS

    SELECT beer, price

    FROM Sells

    WHERE bar = 'Joe''s bar';

    Bp1 c%ROWTYPE;


    Programmer Defined Records

    It is a collection of variables:

    Type my_first_record is record (

    Name varchar2(20);

    Age number;

    Salary number;

    );

    Var_of_myfirstrecord my_first_record;

    Important points on records

    1) Individual fields are referenced via dot notation:

    record_name.field_name

    Example:

    Emp_rec.first_name

    2) Individual fields within a record can be read from or written to. They can appear on either the left or right side of the assignment operator:

    BEGIN

    policy_start_date:= new_emp_rec.hire_date + 30;

    new_emp_rec.present:= FALSE;

    Oracel PL/SQL Tables

    These are one dimensional arrays and are indexed by integers.

    Example:

    Type my_first_table is table of varchar2(10)

    Index by binary_integer;

    Var_of_table my_first_table;

    Var_of_table(1) := ‘hello world’;

    Var_of_table(2) := ‘bye’;

    Example:

    Type my_emp_table is table of emp%rowtype

    Index by binary_integer:

    Var_of_emp my_emp_table;

    Var1_of_emp my_emp_table;

    Var_of_emp(1).ename := ‘sachin’;

    Var_of_emp(1).empno := 20;

    Var_of_emp(1).sal := 3000;

    To delete individual records:

    Var_of_emp .delete(1);

    To remove all entries from a table:

    Var_of_emp.delete;

    Or

    Var_of_emp := var1_of_emp

    Where var1_of_emp does not contain any value, it is empty.

    COUNT method can be used to return number of records in a PL/SQL Table.

    Var_of_table.count

    First, Next and Last methods of PL/SQL Tables.

    First is for first index in the PL/SQL Tables.

    Last is for last index in the PL/SQL Tables.

    Next is for next index in the PL/SQL Tables.

    Example:

    SQL> set serveroutput on

    SQL> Declare

    2

    3 Type my_dept_table is table of varchar2(20)

    4 Index by binary_integer;

    5

    6 Var_of_dept my_dept_table;

    7 Index_value number;

    8

    9 Begin

    10

    11 For dept_rec in (select * from dept) loop

    12 Var_of_dept(dept_rec.deptno) := dept_rec.dname;

    13 End loop;

    14

    15 Index_value := var_of_dept.first;

    16 Loop

    17 Exit when index_value is null;

    18 Dbms_output.put_line (index_value || ' ' ||var_of_dept(index_value));

    19 Index_value := var_of_dept.next(index_value);

    20 End loop;

    21 End;

    22 /

    10 ACCOUNTING

    20 RESEARCH

    30 SALES

    40 OPERATIONS

    PL/SQL procedure successfully completed.

    Oracle PL/SQL Varrays

    These can be stored in the columns of your tables. When you create them you must provide the maximum size for them. These are dense and Not sparse, which means there is no way to delete individual elements of a varrays

    Example:

    Create type project_work_type as varray(20) of varchar2(30);

    Create table student_projects(

    Name varchar2(20),

    Id number,

    --each student can have upto 20 projects

    Projects project_work_type,

    );

    Example:

    Create type emp_type as object(

    Id number,

    Name varchar2(20)

    );

    Create type emp_varray_type as varray(5) of emp_type;

    Create table t1(

    T1_id number,

    T1_name varchar2(10),

    Employee emp_varray_type);

    Insert into t1(t1_id, t1_name, employee)

    Values (1, ’one’,emp_varray_type( emp_type(1,’a’),

    Emp_type(2,’b’))

    )

    Oracle PL/SQL Nested Tables

    These are similar to index by table but these can be stored in database columns but index by tables cannot be stored in database columns.

    A nested table can be considered as a single-column table that can either be in memory, or as a column in a database table. A nested table is quite similar to a VARRAY with the exception that the order of the elements is not static. Elements can be deleted or added anywhere in the nested table where as a VARRAY can only add or delete elements from the end of the array. Nested Table is known as a sparse collection because a nested table can contain empty elements.

    Nested tables are a superior choice when:

  • You need to delete or update some elements, but not all the elements at once.
  • The index values are not consecutive.
  • We don’t have any predefined upper bound for index values.

  • Nested TableVarraysIndex-by-tables
    Declare
    Cursor name_cur IS

    Select last_name
    From student
    Where rownum <= 10; Type last_name_type Is Table Of student.last_name%Type; last_name_tab
    last_name_type :=last_name_type();
    v_counter INTEGER := 0;
    BEGIN
    FOR name_rec IN name_cur
    LOOP
    v_counter :=
    v_counter + 1;
    last_name_tab.EXTEND;
    last_name_tab(v_counter)
    := name_rec.last_name;
    DBMS_OUTPUT.PUT_LINE
    ('last_name('||v_counter||'):
    '||
    last_name_tab(v_counter));
    END LOOP;
    END;
    Declare
    Cursor name_cur
    IS

    Select last_name
    From student
    Where rownum <= 10; Type last_name_type Is Varray(10) OF student. last_name%TYPE; last_name_varray last_name_type := last_name_type(); v_counter INTEGER := 0; BEGIN FOR name_rec IN name_cur LOOP v_counter := v_counter + 1; last_name_varray. EXTEND; last_name_varray(v_counter) := name_rec.last_name; DBMS_OUTPUT.PUT_LINE ('last_name('||v_counter||'): '|| last_name_varray(v_counter)); END LOOP; END;
    Declare

    Cursor name_cur IS
    Select last_name
    From student
    Where rownum <= 10; Type last_name_type Is Table Of student.last_name%TYPE Index By Binary_Integer; last_name_tab last_name_type; v_counter INTEGER := 0; Begin For name_rec IN name_cur Loop v_counter := v_counter + 1; last_name_tab(v_counter) := name_rec.last_name; Dbms_Output.Put_Line ('last_name('||v_counter||'): '|| last_name_tab(v_counter)); END LOOP; END;
    Type number_tab
    as table of number;
    numb_list2 number_tab := number_tab(23,56,34,890,21);
    SQL> declare
    2 type number_tab is table of number;
    3 numb_list number_tab := number_tab(23,56,34,890,21);

    4 begin
    5 for indx in numb_list.first..numb_list.last loop
    6 dbms_output.put_line(numb_list(indx));
    7 end loop;
    8 numb_list.delete(2);
    9 numb_list.delete(4);
    10 for indx in numb_list.first..numb_list.last loop
    11 if numb_list.exists(indx) then
    12 dbms_output.put_line(numb_list(indx));
    13 end if;
    14 end loop;
    15 end;
    16 /

    CREATE TYPE BeerType AS OBJECT (

    name CHAR(20),
    kind CHAR(10),
    color CHAR(10)
    );

    CREATE TYPE BeerTableType AS

    TABLE OF BeerType;
    CREATE TABLE Manfs (
    name CHAR(30),
    addr CHAR(50),
    beers BeerTableType
    )NESTED TABLE beers STORE AS
    BeerTable;
    Create Type
    varray_address_typ
    AS Varray(2) OF
    Varchar2(50);

    /

    Create Table
    customers_with_varray
    (
    id
    Integer Primary key,
    first_name Varchar2(10),

    last_name Varchar2(10),
    addresses varray_address_typ
    );

    Insert Into customers_with_varray Values (
    1, 'Steve', 'Brown',
    varray_address_typ(

    '2 State Street, Beantown, MA, 12345',
    '4 Hill Street, Lost Town, CA, 54321'

    )
    );
    Update
    customers_with_varray
    SET addresses = varray_address_typ(

    '3 New Street, Middle Town, CA, 123435',
    '4 Hill Street, Lost Town, CA, 54321'

    )
    Where id = 1;



    Nested tableVarraysIndex-by-tables
    No maximum Length(unbounded)Maximum lengthNo maximum Length(unbounded)
    A nested table is similar to a VARRAY except that it is a sparse collection, meaning that it can have deleted elements contained in the collection.
    (Sparsity means whether there can be gaps in between the subscripts.)
    A VARRAY is a dense collection, meaning that you can only add or remove objects from the end.It can always be sparse
    Can be stored in DatabaseCan be stored in DatabaseCannot be stored in Database
    Initialization:
    Via constructor, fetch, assignment
    Via constructor, fetch, assignmentAutomatic, when declared

    SQL Outer Join

    SQL OUTER Joins:

    A NORMAL join finds values from two tables that are in a relation to each other. Usually, this relation is equality (=), but it can also be all sorts of operations that either return true or false. The important thing is that a NORMAL join only returns matching rows from both (joined) tables. Obviously, a row whose column-value is not found in the other table's joined column is not returned at all. But, sometimes, we need to show these rows as well.

    The purpose of an outer join is to include non-matching rows, and the outer join returns these missing columns as NULL values.

    A LEFT OUTER JOIN results in a table which includes joined rows and any unmatched rows from the table listed to the left. The keyword LEFT in a LEFT OUTER JOIN tells you that the resulting table will include unmatched rows from the table to the LEFT of the keyword JOIN in the FROM clause of the query. In other words when we use LEFT OUTER JOIN clause we point out that we want to get all rows from the left table listed in our FROM clause, even if they don’t have a match in the right table.

    A RIGHT OUTER JOIN is just reverse of LEFT OUTER JOIN.

    According to old oracle syntax(which uses ‘+’ for outer join operations) :

    An outer join uses a (+) on the side of the operator (equality operator) where we want to have nulls returned if no value matches.

    The syntax for performing an outer join in SQL is database-dependent. For example, in Oracle, we will place an "(+)" in the WHERE clause on the other side of the table for which we want to include all the rows.

    According to http://www.oracle.com/technology/products/oracle9i/
    daily/may31.html

    An outer join extends the result of a simple join. An outer join returns all rows that satisfy the join condition and also returns some or all of those rows from one table for which no rows from the other satisfy the join condition.

    • To write a query that performs an outer join of tables A and B and returns all rows from A (a left outer join), use the LEFT [OUTER] JOIN syntax in the FROM clause, or apply the outer join operator (+) to all columns of B in the join condition in the WHERE clause. For all rows in A that have no matching rows in B, Oracle returns null for any select list expressions containing columns of B.
    • To write a query that performs an outer join of tables A and B and returns all rows from B (a right outer join), use the RIGHT [OUTER] JOIN syntax in the FROM clause, or apply the outer join operator (+) to all columns of A in the join condition in the WHERE clause. For all rows in B that have no matching rows in A, Oracle returns null for any select list expressions containing columns of A.
    • To write a query that performs an outer join and returns all rows from A and B, extended with nulls if they do not satisfy the join condition (a full outer join), use the FULL [OUTER] JOIN syntax in the FROM clause.

    Full outer joins – They are like a left or right outer join, but all the rows from both row sources are returned - if they "join" - fine - they will be joined - but if they don't have a match in the other table by the join key(s) they are returned anyway.



    Employee Table:
    LND_Id
    Kamal31
    Jones33
    Singh33
    Smith34
    Robinson34
    Jasper36

    Department Table:
    Department NameD_Id
    Sales31
    Engineering33
    Clerical34
    Marketing35

    Example left outer join (ANSI 92 standard syntax):
    SELECT distinct *

    FROM employee

    LEFT OUTER JOIN

    department

    ON employee.D_id = department.D_id

    Example left outer join (non-standard syntax):

    SELECT *

    FROM employee

    WHERE employee.D_id = department.D_id(+)

    Example right outer join (ANSI 92 standard syntax):

    SELECT *

    FROM employee

    RIGHT OUTER JOIN

    department

    ON employee.D_id = department.D_id

    Example right outer join (non-standard syntax):

    SELECT *

    FROM employee

    WHERE employee.D_id )+) = department.D_id




    LND_IdDepartment NameD_Id
    Smith34Clerical34
    Jones33Mathematicsg33
    Robinson34Clerical34
    Singh33mathematics33
    Kamal31Sales31
    NULLNULLMarketing35

    Example full outer join (ANSI 92 standard syntax):
    SELECT *

    FROM employee

    FULL OUTER JOIN

    department

    ON employee.D_id = department.D_id


    LND_IdDepartment NameD_Id
    Smith34Clerical34
    Jones33Engineering33
    Robinson34Clerical34
    Jasper36NULLNULL
    Singh33Engineering33
    Kamal31Sales31
    NULL
    NULLMarketing35

    SQL Inner Join

    SQL Inner Join/ SQL Natural Join:

    Inner Join = Equi Join = Natural Join is the usually same.

    An inner join has an ON clause that specifies the join conditions. Rather than having a huge rowset in memory and filtering the data, this join extracts only the data that meets the join conditions. The keyword INNER is optional because a JOIN clause will be INNER by default. An inner join is called equi-join when all the columns are selected with a *, or natural join otherwise.

    inner or natural joins - just a "regular" join.



    Employee table:
    LND_Id
    Kamal31
    Jones33
    Singh33
    Smith34
    Robinson34
    Jasper36

    Department Table:
    Department NameD_Id
    Sales31
    Engineering33
    Clerical34
    Marketing35

    Example inner join (ANSI 92 standard syntax):

    SELECT * FROM employee
    INNER JOIN department
    ON employee.D_id = department.D_id

    Example inner join (non-standard syntax):

    SELECT * FROM employee, department
    WHERE employee.D_id = department.D_id


    Inner join result :


    LND_IdDepartment NameD_Id
    Smith34Clerical34
    Jones33Engineering33
    Robinson34Clerical34
    Singh33Engineering33
    Kamal31Sales31

    SQL Self Join

    SQL Self Join:

    Joining a table to itself.

    select a.ename, b.ename

    from emp a, emp b

    where a.mgr = b.empno;

    SQL Cross Join

    SQL Cartesian product:

    You will be able to find Cartesian product with a Cartesian join. When we join every row of a table to every row of another table we get Cartesian join

    SQL Cross Join:

    Cross joins, where every row from one table is matched with every row from another

    Cartesian join and Cross join are one and the same thing.

    If T1 and T2 are two sets then cross join = T1 X T2.

    Examples of a cross join:

    SELECT *
    FROM emp CROSS JOIN dept

    SELECT *
    FROM emp, dept;

    In the first example above it is explicitly written that it is a CROSS JOIN but in the second one it is implicit.

    Join Using Multiple Tables

    Join Using Multiple Tables(more than 2)

    Questions: I am looking for the resources/examples on using Oracle9i ANSI joins on multiple tables.

    Most of the examples i found are using just two tables to explain the join. I'd appreciate if you could give the examples of writing complex multitable joins for Oracle9i.

    I want to join tables A,B,C,D,E in such a way that tables C,D & E will have OUTER join with table A on a key column. I have used this type of join in Informix and now trying to convert it into Oracle9i.


    and we said...

    The same syntax you used in Informix, given that it was "ansi" style is supported in Oracle9i. There are not "advanced resources" on this cause you are making it harder then it is. It really is as straight forward as it looks.

    Just use parens and keep nesting the joins:



    ops$tkyte@ORA9I.WORLD> create table a ( x int );

    Table created.

    ops$tkyte@ORA9I.WORLD> create table b ( x int );

    Table created.

    ops$tkyte@ORA9I.WORLD> create table c ( x int );

    Table created.

    ops$tkyte@ORA9I.WORLD> create table d ( x int );

    Table created.

    ops$tkyte@ORA9I.WORLD> create table e ( x int );

    Table created.

    ops$tkyte@ORA9I.WORLD>
    ops$tkyte@ORA9I.WORLD> insert into a values ( 1 );

    1 row created.

    ops$tkyte@ORA9I.WORLD> insert into b values ( 1 );

    1 row created.

    ops$tkyte@ORA9I.WORLD>
    ops$tkyte@ORA9I.WORLD> select *
    2 from (((( a inner join b on a.x = b.x ) left outer join c on a.x = c.x )
    3 left outer join d on a.x = d.x ) left outer join e on a.x = e.x )

    SQL Equijoin

    SQL Equijoin:

    The join condition determines whether the join is an equijoin or a non equijoin. when we relate two tables on a join condition by equating the columns from the tables, it is an equijoin. when we relate two tables on a join condition by an operator other than equality it is an non-equijoin. A query may contain equijoins as well as non-equijoins.

    Examples of Equijoin:

    Select emp.deptno, bonus.comm
    from emp bonus
    where emp.ename = bonus.ename

    SELECT * FROM emp
    INNER JOIN dept
    ON emp.DeptID = dept.DeptID

    SQL Functions

    Please refer the below mentioned link for SQL FUNCTIONS. This is an excellent link for getting deep insight of all available SQL FUNCTIONS.

    http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14200/functions001.htm#i88893

    There are different types of SQL functions:

    • single_row_function

      1. numeric_function

      2. character_function

      3. data_mining_function

      4. datetime_function

      5. conversion_function

      6. collection_function

      7. XML_function

      8. miscellaneous_single_row_function

    • aggregate_function

    • analytic_function

    • object_reference_function

    • model_function

    • user_defined_function

    Some of the SQL Functions with examples are as below:

    LOWER(string) Converts a string to all lowercase characters

    SELECT LOWER('MCMILLAN') "Lowercase" FROM DUAL;

    INITCAP(string) Converts a string to initial capital letters

    SELECT INITCAP('the apple') "Capitals" FROM DUAL;

    UPPER(string) Converts a string to all uppercase characters

    SELECT UPPER(last_name) "Uppercase" FROM emp;
    LENGTH(string) Returns the number of characters in a string
    SELECT LENGTH('SACHIN') "Length in characters" FROM DUAL;

    AVG(expression) Returns the average of the values in a set of rows

    SELECT AVG(salary) "Average" FROM emp;
    Average

    --------

    6400


    COUNT(expression) or COUNT(*)

    Returns the number of rows in the set

    If you include an expression, COUNT returns only the number of rows in which the expression is not null.

    COUNT(*) counts all rows.

    MAX(expression) Returns the largest value from a set of rows

    SELECT MAX(salary) "Maximum" FROM emp;

    Maximum

    ----------

    29000

    MIN(expression) Returns the smallest value from a set of rows

    SELECT MAX(salary) "Maximum" FROM emp;

    SUM(expression) Adds the value for all rows in the query or for all rows with the same values for columns listed in the GROUP BY clause

    SELECT SUM(salary) "Total" FROM emp;
    Total

    ----------

    67140

    ABS(number) Removes the sign, if any, returning a positive value

    The following example returns the absolute value of -15:

    SELECT ABS(-5) "Absolute" FROM DUAL;

    Absolute

    ----------

    5

    GREATEST(value1,value2, …)

    Returns the largest of the values in the list

    SELECT GREATEST ('HARRY', 'HARRIOT', 'HAROLD')
    "Greatest" FROM DUAL;

    This function is used for multiple values in the same row.


    Greatest

    --------

    HARRY

    LEAST(value1,value2, …)

    Returns the smallest of the values in the list. This function is used for multiple values in the same row.

    SELECT LEAST('HARRY','HARRIOT','HAROLD') "LEAST" FROM DUAL;
    LEAST

    ------

    HAROLD

    ROUND(number, decimal places)

    Rounds a value to the specified number of decimal places

    The following example rounds a number to one decimal point:

    SELECT ROUND(18.193,1) "Round" FROM DUAL;
    Round

    ----------

    18.2

    The following example rounds a number one digit to the left of the decimal point:

    SELECT ROUND(15.193,-1) "Round" FROM DUAL;
    Round

    ----------

    20

    The following examples illustrate the difference between rounding NUMBER and floating-point number values. NUMBER values are rounded up (for positive values), whereas floating-point numbers are rounded toward the nearest even value:

    SELECT ROUND(1.5), ROUND(2.5) FROM DUAL;
    ROUND(1.5) ROUND(2.5)

    ---------- ----------

    2 3

    SELECT ROUND(1.5f), ROUND(2.5f) FROM DUAL;

    ROUND(1.5F) ROUND(2.5F)

    ----------- -----------

    2.0E+000 2.0E+000

    TRUNC(number,decimal places)

    Cuts off a value at the specified number of decimal places

    The following examples truncate numbers:

    SELECT TRUNC(15.79,1) "Truncate" FROM DUAL;

    Truncate

    ----------

    15.7
    SELECT TRUNC(15.79,-1) "Truncate" FROM DUAL;

    Truncate

    ----------

    10

    SUBSTR(string, starting value, number of characters)

    Extracts a portion of a string

    If the starting value is 0, it is treated as 1. If the starting-value is negative, Oracle counts backward from the end of the string. If the starting value is positive, Oracle counts forward from the beginning of the string.

    SELECT SUBSTR('ABCDEFG',3,4) "Substring" FROM DUAL;
    Substring

    ---------

    CDEF

    SELECT SUBSTR('ABCDEFG',-5,4) "Substring" FROM DUAL;

    Substring

    ---------

    CDEF

    Assume a double-byte database character set:


    SELECT SUBSTRB('ABCDEFG',5,4.2) "Substring with bytes" FROM DUAL;

    Substring with bytes

    --------------------

    CD

    ADD_MONTHS(date, number of months)


    Adds the specified number of months to the date value

    (subtracts months if the number of months is negative)

    If the result would be a date beyond the end of the month, Oracle returns the last day of the resulting month.

    The following example returns the month after the hire_date in the sample table employees:

    SELECT TO_CHAR(

    ADD_MONTHS(hire_date,1),

    'DD-MON-YYYY') "Next month"

    FROM employees

    WHERE last_name = 'Baer';

    Next Month

    -----------

    07-JUL-1994

    LAST_DAY(date) Returns the last day of the month that contains the date

    The following statement determines how many days are left in the current month.

    SELECT SYSDATE,

    LAST_DAY(SYSDATE) "Last",

    LAST_DAY(SYSDATE) - SYSDATE "Days Left"

    FROM DUAL;

    SYSDATE Last Days Left

    --------- --------- ----------

    30-MAY-01 31-MAY-01 1

    MONTHS_BETWEEN(date1,date2)

    Returns the difference between two dates expressed as whole

    and fractional months

    If date1 is earlier than date2, the result is negative.

    The result also takes into account time differences between the two values.

    The following example calculates the months between two dates:

    SELECT MONTHS_BETWEEN

    (TO_DATE('02-02-1995','MM-DD-YYYY'),

    TO_DATE('01-01-1995','MM-DD-YYYY') ) "Months"

    FROM DUAL;

    Months

    ----------

    1.03225806


    NEXT_DAY(date, day name)

    Returns the date of the first day of the specified name that is

    later than the date supplied

    This example returns the date of the next Tuesday after February 2, 2001:

    SELECT NEXT_DAY('02-FEB-2001','TUESDAY') "NEXT DAY"

    FROM DUAL;

    NEXT DAY

    -----------

    06-FEB-2001

    ROUND (datetime, format)

    Returns the date-time rounded to the unit specified by the

    format, or to the nearest day if no format is supplied

    Note: For details on available formats, see the full

    description of functions (below).

    The following example rounds a date to the first day of the following year:

    SELECT ROUND (TO_DATE ('27-OCT-00'),'YEAR')

    "New Year" FROM DUAL;

    New Year

    ---------

    01-JAN-01

    SYSDATE Returns the current date-time from the server where the database is located

    The following example returns the current operating system date and time:

    SELECT TO_CHAR

    (SYSDATE, 'MM-DD-YYYY HH24:MI:SS') "NOW"

    FROM DUAL;

    NOW

    -------------------

    04-13-2001 09:45:51


    TRUNC(datetime) Removes the time component from a date-time value. The following example truncates a date:

    SELECT TRUNC(TO_DATE('27-OCT-92','DD-MON-YY'), 'YEAR')

    "New Year" FROM DUAL;

    New Year

    ---------

    01-JAN-92

    TO_CHAR(date, format)

    Converts a date to a string in the specified format

    TO_CHAR(number, format)

    Converts a number to a string in the specified format.

    The following statement uses implicit conversion to combine a string and a number into a number:

    SELECT TO_CHAR('01110' + 1) FROM dual;

    TO_C

    ----

    1111

    TO_DATE(string, format)

    Converts a string to a date using the specified format.

    The following example converts a character string into a date:


    SELECT TO_DATE(

    'January 15, 1989, 11:00 A.M.',

    'Month dd, YYYY, HH:MI A.M.',

    'NLS_DATE_LANGUAGE = American')

    FROM DUAL;

    TO_DATE('

    ---------

    15-JAN-89

    TO_NUMBERstring, format)

    Converts a string to a number using the optional format if specified.

    The following examples convert character string data into a number:

    UPDATE employees SET salary = salary +

    TO_NUMBER('100.00', '9G999D99')

    WHERE last_name = 'Perkins';

    SQL Introduction

    SQL stands for Structured Query Language. and it is generally referred to as SEQUEL. SQL is simple language to learn. SQL is a Nonprocedural language, as compared to the procedural or third generation languages (3GLs) such as COBOL and C. SQL was developed by IBM in the 1970s.

    The American National Standards Institute (ANSI) published its first SQL standard in 1986 and a second widely adopted standard in 1989. ANSI released updates in 1992, known as SQL92 and SQL2, and again in 1999, termed both SQL99 and SQL3. Each time, ANSI added new features and incorporated new commands and capabilities into the language.

    SQL is a simple, yet powerful, language used to create, access, and manipulate data and structure in the database.

    SQL Statements categories: DDL - Data Definition Language.

    DDL is used to define, alter, or drop database objects and their privileges. DDL statements will implicitly perform a commit.

    DDL Statements:

    CreateIt is used to create objects(tables, views) in the database.
    AlterIt is used to alter the structure of the database objects.
    Drop delete database objects (It will invalidate the dependent objects ,it also drops indexes, triggers and referential integrity constraints ).
    Truncate remove all records from a table, including all spaces allocated for the records are removed (It is fast as compared to Delete and does not generate undo information as Delete does. It performs an implicit commit as it is a DDL. It resets the high water mark.)
    Grant assigning privileges

    DML - Data Manipulation Language.

    DML is used to access, create, modify or delete data in the structures of the database.

    DML Statements:

    Select Select data from the database
    Insert It is used to insert data into a table
    Update It is used to update existing data within a table
    Delete It removes rows from the table.

    DCL - Data Control Language

    Following are the examples of Data control Statements.

    DCL Statements:

    CommitIt will end the current transaction making the changes permanent and visible to all users..
    SavepointIt will identify a point(named SAVEPOINT) in a transaction to which you can later roll back
    RollbackIt will undo all the changes made by the current transaction.
    Set- Transaction It is used to define the properties of a transaction.

    Creating a Calendar in a single SQL statement

    A few days ago, somebody asked if it is possible to write a Calendar in a single SQL statement.

    Here you go...

    SELECT LPAD( MONTH, 20-(20-LENGTH(MONTH))/2 ) MONTH,"Sun", "Mon", "Tue",
    "Wed", "Thu", "Fri", "Sat"
    FROM (SELECT TO_CHAR(dt,'fmMonthfm YYYY') MONTH,TO_CHAR(dt+1,'iw') week,
    MAX(DECODE(TO_CHAR(dt,'d'),'1',LPAD(TO_CHAR(dt,'fmdd'),2))) "Sun",
    MAX(DECODE(TO_CHAR(dt,'d'),'2',LPAD(TO_CHAR(dt,'fmdd'),2))) "Mon",
    MAX(DECODE(TO_CHAR(dt,'d'),'3',LPAD(TO_CHAR(dt,'fmdd'),2))) "Tue",
    MAX(DECODE(TO_CHAR(dt,'d'),'4',LPAD(TO_CHAR(dt,'fmdd'),2))) "Wed",

    hierarchical queries

    A relational database does not store data in a hierarchical way. Then how do I get the data in a hierarchical manner? Here we get to know about how to use the hierarchical querying feature which Oracle has given. This article talks about how you can interpret the hierarchical query conceptually and build hierarchical queries catering your needs.

    Using hierarchical queries, you can retrieve records from a table by their natural relationship. Be it a family tree or a employee/manager tree or what ever.

    Recent Tutorials