1600 PMP mock questions 1400 CAPM mock questions 800 SCJP 6 mock questions 600 OCAJP 7 mock questions 590 OCPJP 7 mock questions 556 SCWCD 5 mock questions 500 OCEJWCD 6 mock questions pdfDownload (java,struts, hibernet etc)

ORACLE Tutorials

PL/SQL Tutorials ? CONCEPTS

PL/SQL Tutorials ? CONCEPTS
PL/SQL is an acronym to Procedural Language/Structured Query Language.
When the business logic to be attained is not achievable easily using SQL which is 4th Generation Language, Oracle turns to the Procedural Language structures and achieves that business logic and achieves what business is looking for.
In the process of evolution of PL/SQL it has become more robust and multi-faceted.
a. DML to insert, update, delete the data into Oracle Tables
b. DDL to create, alter, drop objects in a schema
c. DCL to create save point and to rollback or commit the work done to a certain desired state. And also enables to take control of the situation in case one or other process within the PLSQL fails and there is a need to rollback to a position where the data integrity maintained and before process state for data is restored.
d. DQL to save data for a given process into variables
e. To call other functions, procedures, packages from within the procedures, functions, packages, triggers.
f. PLSQL owned security mechanism to protect the intrusions
g. To use all the utilities and built in packages as provided by Oracle
h. Triggers, even though are part of PLSQL are of different gene and they are event driven in their nature. They are the backbone of Oracle Forms and they are used in RDBMS to achieve data integrity, data auditing, database auditing, to create a source safe mechanism and many more requirements. But they are the most dangerous creatures that reside within Oracle RDBMS and work behind backs of DBA, users and consumers of data. Some RDBMS have used triggers for data integrity and validation like constraints. In Oracle RDBMS maintaining the data integrity and validation is the job of constraints and using triggers for that purpose leads to performance. There is an issue mutating triggers in Oracle as a result of recursive dependency of one activity on the other recursively. This is an issue with the developers and this, they are to handle carefully.
i. PL/SQL collections, records and object types enable development of Object Oriented Relational Database Management System more comfortably getting out of many procedural issues.
Understanding main features of PLSQL in the words of Oracle:
PLSQL is in Blocks. Block structure look like as under:
declare
begin
conditions
statements
end;
/
The above is an example of an anonymous PLSQL block. This means that this PLSQL has no name and can not be stored in the database as an PLSQL Object.
Between ?declare? and ?begin? key words the user has been given the opportunity to create all types of variables as permitted by PLSQL.
VARIABLES
Variables are a type of containers that allow you to identify the container with a PLSQL identifiable datatypes, attributes, constants, cursors, also with assigned values. The values can be assigned to these variables with in the BEGIN and END control blocks.

Variable datatypes:
Here is listing of the data types that are identifiable within PLSQL:

DATATYPE    MEMBER     SUB-MEMBER    SIZE/LENGTH    REMARKS    
NUMBER    BINARY        -2**31 to 2**31        
    BINARY_INTEGER            Less storage than NUMBER. Operations are slower than pls_integer    
        NATURAL        non-negative or positive values only    
        NATURALN        prevent the assigning of nulls to an integer variable    
        POSITIVE        non-negative or positive values only    
        POSITIVEN        prevent the assigning of nulls to an integer variable    
        SIGNTYPE        values -1, 0, and 1    
    NUMBER        Its magnitude range is 1E-130 thru 10E1        
        DEC    38        
        DECIMAL    38        
        DOUBLE PRECISION    126 binary digits (roughly 38 digits)        
        FLOAT    126 binary digits (roughly 38 digits)        
        INTEGER            
        INT            
        NUMERIC    38        
        REAL    63 binary digits        
    PLS_INTEGER        values range from 2**31 to 2**31    Less storage than NUMBER.     
CHARACTER            Maximum size up to 32767 bytes. This is fixed length        
    CHAR        maximum width 2000 bytes        
    LONG and LONG RAW        size of a LONG and LONG RAW value is 32760 bytes        
    RAW        Max size up to 32767 bytes        
    VARCHAR2        Max size up to 32767 bytes        
        STRING        For compatibility with IBM and ANSI    
        VARCHAR        For compatibility with IBM and ANSI    
NATIONAL CHARACTER TYPES    NCHAR            NCHAR maximum size Because the physical limit is 32767 bytes, the maximum value you can specify for the length is 32767/2 in the AL16UTF16 encoding, and 32767/3 in the UTF8 encoding.    
    NVARCHAR2                
    BFILE        The size of a BFILE is system dependent but cannot exceed four gigabytes (2**32 - 1 bytes).    BFILEs are read-only, so you cannot modify them.     
    BLOB        The size of a BLOB cannot exceed four gigabytes        
    CLOB        The size of a CLOB cannot exceed four gigabytes.        
    NCLOB        The size of an NCLOB cannot exceed four gigabytes.        
BOOLEAN TYPE    BOOLEAN        Values are TRUE, FALSE, and NULL.    You cannot insert the values TRUE and FALSE into a database column. Also, you cannot select or fetch column values into a BOOLEAN variable.    
DATE and INTERVAL TYPES    DATE            Valid dates range from January 1, 4712 BC to December 31, 9999 AD.    
    TIMESTAMP                
    TIMESTAMP WITH TIME ZONE                
    TIMESTAMP WITH LOCAL TIME ZONE                
    INTERVAL YEAR TO MONTH                
    INTERVAL DAY TO SECOND                
Note:
VARCHAR2
Small VARCHAR2 variables are optimized for performance, and larger ones are optimized for efficient memory use. The cutoff point is 2000 bytes. For a VARCHAR2 that is 2000 bytes or longer, PL/SQL dynamically allocates only enough memory to hold the actual value. For a VARCHAR2 variable that is shorter than 2000 bytes, PL/SQL pre-allocates the full declared length of the variable. For example, if you assign the same 500-byte value to a VARCHAR2 (2000 BYTE) variable and to a VARCHAR2(1999 BYTE) variable, the former takes up 500 bytes and the latter takes up 1999 bytes.
ATTRIBUTES for the Variables
Apart from these datatypes, the variables can be declared using ATTRIBUTES
%rowtype which means that an entire row in a table has been identified as a variable
Example emprec scott.emp%rowtype; -- declare record variable
%type which means that the variable?s datatype is to be that of the referenced table.column datatype.
Example var_ename scott.emp.ename%type;
CONSTANT variable
credit_limit CONSTANT REAL := 5000.00;
CURSORS in the declare section
Oracle uses work areas to execute SQL statements and store processing information. A PL/SQL construct called a cursor lets you name a work area and access its stored information. There are two kinds of cursors: implicit and explicit. PL/SQL implicitly declares a cursor for all SQL data manipulation statements, including queries that return only one row. For queries that return more than one row, you can explicitly declare a cursor to process the rows individually. An example follows:
DECLARE
CURSOR c1 IS
SELECT empno, ename, job FROM emp WHERE deptno = 20;

The set of rows returned by a multi-row query is called the result set. Its size is the number of rows that meet your search criteria
Cursor FOR Loops
In most situations that require an explicit cursor, you can simplify coding by using a cursor FOR loop instead of the OPEN, FETCH, and CLOSE statements. A cursor FOR loop implicitly declares its loop index as a record that represents a row fetched from the database. Next, it opens a cursor, repeatedly fetches rows of values from the result set into fields in the record, then closes the cursor when all rows have been processed. In the following example, the cursor FOR loop implicitly declares emp_rec as a record:
DECLARE
CURSOR c1 IS
SELECT ename, sal, hiredate, deptno FROM emp;
...
BEGIN
FOR emp_rec IN c1 LOOP
...
salary_total:= salary_total + emp_rec.sal;
END LOOP;

To reference individual fields in the record, you use dot notation, in which a dot (.) serves as the component selector.
Cursor Variables
Like a cursor, a cursor variable points to the current row in the result set of a multi-row query. But, unlike a cursor, a cursor variable can be opened for any type-compatible query. It is not tied to a specific query. Cursor variables are true PL/SQL variables, to which you can assign new values and which you can pass to subprograms stored in an Oracle database. This gives you more flexibility and a convenient way to centralize data retrieval.
Typically, you open a cursor variable by passing it to a stored procedure that declares a cursor variable as one of its formal parameters. The following procedure opens the cursor variable generic_cv for the chosen query:
PROCEDURE open_cv (generic_cv IN OUT GenericCurTyp, choice NUMBER) IS
BEGIN
IF choice = 1 THEN
OPEN generic_cv FOR SELECT * FROM emp;
ELSIF choice = 2 THEN
OPEN generic_cv FOR SELECT * FROM dept;
ELSIF choice = 3 THEN
OPEN generic_cv FOR SELECT * FROM salgrade;
END IF;
...
END;

Reviews and Comments


PMP, CAPM, PMI is a registered certification mark of the Project Management Institute, Inc

Copyright © www.techfaq360.com 2016


About US | Contact US | Privacy Policy | Terms and Conditions  | Website disclaimer  | Cancellation and Refund Policy  | Shipping & Delivery Policy