gistfile1.txt

PL-SQL


-----------------------------------------------------------------
General

Be able to define a declare/begin/end block

DECLARE  
   a integer := 30;  
   b integer := 40;  
   c integer;  
   f real;  
BEGIN  
   c := a + b;  
   dbms_output.put_line('Value of c: ' || c);  
   f := 100.0/3.0;  
   dbms_output.put_line('Value of f: ' || f);  
END; 


Be able to define local variables (including %rowtype)

    DECLARE  
       a integer := 30;  
       b integer := 40;  
       c integer;  
       f real;  
    BEGIN  
       c := a + b;  
       dbms_output.put_line('Value of c: ' || c);  
       f := 100.0/3.0;  
       dbms_output.put_line('Value of f: ' || f);  
    END;  



Be able to define records


Be able to assign query results to variables (select into)

     SELECT id, name into r_id, r_name from customers;

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

Cursors
Be able to define a cursor

DECLARE  
   c_id customers.id%type;  
   c_name customers.name%type;  
   c_addr customers.address%type;  
   CURSOR c_customers is  
      SELECT id, name, address FROM customers;  
BEGIN  
   OPEN c_customers;  
   LOOP  
      FETCH c_customers into c_id, c_name, c_addr;  
      EXIT WHEN c_customers%notfound;  
      dbms_output.put_line(c_id || ' ' || c_name || ' ' || c_addr);  
   END LOOP;  
   CLOSE c_customers;  
END; 

Know about cursor lifecycle (open/fetch/close)
    OPEN cursor_name;
    FETCH cursor_name INTO variable_list;  
    Close cursor_name;  

-----------------------------------------------------------------
Control statements
Be able to write a IF

    DECLARE  
       a number(3) := 500;  
    BEGIN  
       -- check the boolean condition using if statement   
       IF( a < 20 ) THEN  
          -- if condition is true then print the following    
          dbms_output.put_line('a is less than 20 ' );  
       ELSE  
          dbms_output.put_line('a is not less than 20 ' );  
       END IF;  
       dbms_output.put_line('value of a is : ' || a);  
    END;  

Be able to write a FOR loop

    BEGIN  
    FOR k IN 1..10 LOOP  
    -- note that k was not declared  
    DBMS_OUTPUT.PUT_LINE(k);  
    END LOOP;  
    END;   

Be able to write a WHILE loop

    DECLARE  
      i INTEGER := 1;  
    BEGIN  
      WHILE i <= 10 LOOP  
        DBMS_OUTPUT.PUT_LINE(i);  
        i := i+1;  
      END LOOP;  
    END;  

-----------------------------------------------------------------
Exceptions
Be able to raise and catch exceptions

Exception 	Oracle Error 	SQL Code 	Description
    ACCESS_INTO_NULL 	06530 	-6530 	It is raised when a NULL object is automatically assigned a value.
    CASE_NOT_FOUND 	06592 	-6592 	It is raised when none of the choices in the ?WHEN? clauses of a CASE statement is selected, and there is no else clause.
    COLLECTION_IS_NULL 	06531 	-6531 	It is raised when a program attempts to apply collection methods other than exists to an uninitialized nested table or varray, or the program attempts to assign values to the elements of an uninitialized nested table or varray.
    DUP_VAL_ON_INDEX 	00001 	-1 	It is raised when duplicate values are attempted to be stored in a column with unique index.
    INVALID_CURSOR 	01001 	-1001 	It is raised when attempts are made to make a cursor operation that is not allowed, such as closing an unopened cursor.
    INVALID_NUMBER 	01722 	-1722 	It is raised when the conversion of a character string into a number fails because the string does not represent a valid number.
    LOGIN_DENIED 	01017 	-1017 	It is raised when s program attempts to log on to the database with an invalid username or password.
    NO_DATA_FOUND 	01403 	+100 	It is raised when a select into statement returns no rows.
    NOT_LOGGED_ON 	01012 	-1012 	It is raised when a database call is issued without being connected to the database.
    PROGRAM_ERROR 	06501 	-6501 	It is raised when PL/SQL has an internal problem.
    ROWTYPE_MISMATCH 	06504 	-6504 	It is raised when a cursor fetches value in a variable having incompatible data type.
    SELF_IS_NULL 	30625 	-30625 	It is raised when a member method is invoked, but the instance of the object type was not initialized.
    STORAGE_ERROR 	06500 	-6500 	It is raised when PL/SQL ran out of memory or memory was corrupted.
    TOO_MANY_ROWS 	01422 	-1422 	It is raised when a SELECT INTO statement returns more than one row.
    VALUE_ERROR 	06502 	-6502 	It is raised when an arithmetic, conversion, truncation, or size-constraint error occurs.
    ZERO_DIVIDE 	01476 	1476 	It is raised when an attempt is made to divide a number by zero.

Be able to define user exceptions

    DECLARE  
       exception_name EXCEPTION;  
    BEGIN  
       IF condition THEN  
          RAISE exception_name;  
       END IF;  
    EXCEPTION  
       WHEN exception_name THEN  
       statement;  
    END;  

Functions and procedures
Be able to define a procedure or a function

FUNCTION findMax(x IN number, y IN number)   
RETURN number  
IS  
    z number;  
BEGIN  
   IF x > y THEN  
      z:= x;  
   ELSE  
      Z:= y;  
   END IF;


create or replace procedure "INSERTUSER"    
(id IN NUMBER,    
name IN VARCHAR2)    
is    
begin    
insert into user values(id,name);    
end;  

Know about function parameters as a result (inout parameters)