Attributes of Explicit Cursors

Attributes of Explicit Cursors

In Oracle Every cursor has  a four attributes .

%NOTFOUND
%FOUND
%ROWCOUNT
%ISOPEN


%NOTFOUND
evaluates to TRUE if last FETCH failed because no more rows were available
evaluates to FALSE if last FETCH returned a row

%FOUND
evaluates to TRUE if last FETCH returned a row
evaluates to FALSE if last FETCH failed because no more rows were available

%ROWCOUNT
returns the number of rows FETCHed from the active set so far

%ISOPEN
evaluates to TRUE if an explicit cursor is open
evaluates to FALSE if an explicit cursor is closed

Read More

For Loop

--Write a PL_SQL block to insert numbers into student table
 Using For Loop
First You need to create table student.
sql> create table student(results number);

Example :-
Insert the numbers 1..10 excludining 5

BEGIN
FOR i in 1..10 LOOP
  IF i = 5  THEN
    null;
  ELSE
    INSERT INTO  student(results)
    VALUES (i);
 END IF;
END LOOP;
 COMMIT;
END;

To see the output Execute a  PLSQL statement
to check  that your PLSQL BLock Worked.

Select * from temp;

Read More

Merging Rows

Merging Rows

In Oracle The MERGE statement inserts or updates rows in one table by using data from another table. Each row is inserted or updated in the target table depending on an equijoin condition.

This example matches the employee_id in the tab_new table to the employee_id in the employees table. If a match is found, the row is updated to match the row in the employees table.
If the row is not found, it is  inserted into the tab_new table.

The complete example for using MERGE in a PL/SQL block is shown on the next notes page.


Now let us create new table tab_new.

SQL> create table tab_new as select employee_id eid,first_name fname,last_name lname from employees;

Table created.

Describe the structure of tab_new table.

SQL> desc tab_new;

 Name                                      Null?    Type
 ----------------------------------------- -------- -----------------
 EID                                                NUMBER(6)
 FNAME                                              VARCHAR2(20)
 LNAME                                     NOT NULL VARCHAR2(25)

Delete all records from tab_new table.

SQL> truncate table tab_new;

Table truncated.
--------
After deleting records from tab_new , now you can insert  new records using in tabl_new table with the follwing command.

SQL> insert into tab_new values(101,'shweta','Deshpande');

1 row created.

SQL> insert into tab_new values(102,'shalina','khan');

1 row created.

SQL> commit;

Commit complete.

SQL> insert into tab_new values(103,'rafat','shaikh');

1 row created.

SQL> commit;

Commit complete.

Now view all the records from tab_new table.

SQL> select * from tab_new;

       EID FNAME                LNAME
---------- -------------------- -------------------------
       101 shweta                Deshpande
       102 shalina               khan
       103 rafat                shaikh


Now write an Annonymous block to inserts or updates rows in one table by using  merge statement.

SQL>   merge into tab_new t
  2    using employees e
  3    on (t.eid=e.employee_id)
  4    when matched then
  5    update set
  6    t.fname=e.first_name,
  7    t.lname=e.last_name
  8   when not matched then
  9  insert values (e.employee_id,e.first_name,e.last_name)
 10  /

107 rows merged.

You can see that If a match is found, the row is updated to match the row in the employees table. If the row is not found, it is inserted into the tab_new table
SQL> select * from tab_new;

       EID FNAME                LNAME
---------- -------------------- -------------------------
       101 Neena                Kochhar
       102 Lex                  De Haan
       103 Alexander            Hunold
       198 Donald               OConnell
       199 Douglas              Grant
:
:
:
:
107 rows inserted

Read More

Cursors In Pl Sql

What is a cursor in Oracle

A cursor is a pointer to the private memory area allocated by the Oracle server.
There are two types of cursors:

Implicit: Created and managed internally by the Oracle server to process SQL statements

Explicit: Explicitly declared by the programmer using cursor name .

The syntax to declare a cursor is shown in the slide. In the syntax:
cursor_name Is a PL/SQL identifier


select_statement Is a SELECT statement without an INTO clause

There are four steps in we can use an Explicit Cursor.

1. DECLARE in this section we define cursor name.
2. OPEN the cursor in the Execution Section.
3. FETCH the data from cursor into PL/SQL variables or records in the Execution Section.

4  Finally CLOSE the cursor in the Execution Section before you end the PL/SQL Block.



Syntax to open a cursor is:OPEN cursor_name;

Syntax to fetch records from a cursor is:


FETCH cursor_name INTO record_name;

OR


FETCH cursor_name INTO variable_list;

CLOSE cursor_name;


Syntax to close a cursor is:


Write a program to accept a deptno and display who are working in that dept?
DECLARE
Dno EMP.DePartment_id%TYPE:=&DEPartment_id;
CURSOR cur IS
SELECT * FROM EMP WHERE DEPTNO=Dno;
recemp cur%ROWTYPE;
BEGIN
OPEN cur;
LOOP
FETCH cur INTO B;
EXIT WHEN A%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('EMP NO IS ' || emprec.EMPloyee_id);
DBMS_OUTPUT.PUT_LINE('ENAME IS ' || emprec.last_name);
DBMS_OUTPUT.PUT_LINE('SAL IS ' || emprec.SALary);
DBMS_OUTPUT.PUT_LINE('MGR NO IS ' || emprec.Manager_id);
DBMS_OUTPUT.PUT_LINE('COMM IS ' || emprec.COMMission_pct);
DBMS_OUTPUT.PUT_LINE('HIREDATE IS ' || emprec.HIRE_DATE);
DBMS_OUTPUT.PUT_LINE('DEPTNO IS ' || emprec.DEPartment_id);
DBMS_OUTPUT.PUT_LINE('EMP JOB IS ' || emprec.JOB_id);
DBMS_OUTPUT.PUT_LINE('*************************');
END LOOP;
CLOSE A;
END;

Read More

set serveroutput on

PL/SQL is a block  always required to server output result is to be  show on  the screen
 otherwise output will not display .
Whenever you will work on sql prompt  type  "set serveroutput on". command.

SQL> set serveroutput on

Set serveroutput on Example.

SQL> set serveroutput on
SQL> DECLARE
 eno number(5) NOT NULL := 2
 ename varchar2(15) := Jay Patel;
 edept CONSTANT varchar2(15) := Web Developer;
BEGIN
 dbms_output.put_line('Declared Value:');
 dbms_output.put_line(' Eno: ' || eno || ' EName: ' || ename);
 dbms_output.put_line('Constant Declared:');
 dbms_output.put_line(' EDept: ' || edept); 
END;

Read More

Basic Pl sql

1 Views 
2.Conversion_Functions
3.Bind Variables
4.Constraints
4 Exit Loop

Read More

Statement Level Trigger

Statement Triggers A statement triggers is fired once on behalf of the triggering statement, in spite of of the number of rows in the table that the triggering statement affects (even if no rows are affected). For example, if a DELETE statement deletes several rows from a table, a statement-level DELETE trigger is fired only once, regardless of how many rows are deleted from the table.               

Statement triggers are valuable if the code in the trigger action does not depend on the data provided by the triggering statement or the rows affected. For example, if a trigger makes a composite security audit record based on the type of triggering statement, a statement trigger is used.

             .  AFTER row trigger After modifying each row affected by the triggering statement and possibly applying right integrity constraints, the trigger action is executed for the current row provided the trigger constraint was not violated. Unlike BEFORE row triggers, AFTER row triggers lock rows.

You can have many triggers of the same type for the same statement for any given table. For example you may have two BEFORE STATEMENT triggers for  UPDATE statements on the EMP table. Multiple triggers of the same type allow modular installation of applications that have triggers on the same tables. Also, Oracle snapshot logs use AFTER ROW triggers, so you can design your own AFTER ROW trigger in addition to the Oracle-defined AFTER ROW trigger.

You can create as many triggers of the preceding different types as you need for each type of DML statement (INSERT, UPDATE, or DELETE). For example, suppose you have a table, Salary , and you want to know when the table is being accessed and the types of queries being issued. Figured 1.1 contains a sample package and trigger that tracks this information by hour and type of action (for example, UPDATE, DELETE, or INSERT) on table SAL. A global session variable, STAT.ROWCNT, is initialized to zero by a BEFORE statement trigger, then it is increased each time the row trigger is executed, and finally the statistical information is saved in the table STAT_TAB by the AFTER statement trigger.

Read More