Attributes of Explicit CursorsIn Oracle Every cursor has a four attributes .%NOTFOUND%FOUND%ROWCOUNT%ISOPEN%NOTFOUNDevaluates to TRUE if last FETCH failed because no more rows were availableevaluates to FALSE if last FETCH returned a row%FOUNDevaluates to TRUE if last FETCH returned a rowevaluates to FALSE if last FETCH failed because no more...
Monday, June 10, 2013
For Loop
--Write a PL_SQL block to insert numbers into student table Using For LoopFirst You need to create table student.sql> create table student(results number);Example :-Insert the numbers 1..10 excludining 5BEGINFOR i in 1..10 LOOP IF i = 5 THEN null; ELSE INSERT INTO student(results) ...
Merging Rows
Merging RowsIn 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...
Cursors In Pl Sql
What is a cursor in OracleA 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 statementsExplicit: Explicitly...
Friday, June 7, 2013
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 onSet serveroutput on Example.SQL> set serveroutput onSQL> DECLARE eno number(5) NOT NULL...
Saturday, June 1, 2013
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...
