FANDOM


PL/SQL stands for "Procedural Language extensions to SQL", and is a procedural language used while working with Oracle databases. PL/SQL is directly related to classic SQL; however it adds useful iterative, selective and sequential programming constructs that can be used in combination with traditional SQL statements. The combination of structural programming methods and SQL creates a powerful application development and data management utility.

PL/SQL programs are based on blocks. Each programmatic block can be stand-alone or can contain other blocks. Blocks are structured and sequenced to perform logical operations in programs. Blocks are ordered: Declaration of working memory, Execution section using working memory, and Exception handling. PL/SQL can be used to create ad-hawk data retrieval or manipulation scripts, or as procedures and functions in a more consistent implementation.

A typical PL/SQL (script-style) program is structured as follows:

Declare… Declaration section used for working single-value variables, SQL Cursors, and user-defined data types. (Optional block) Begin… Program logic execution section: This section can contain selection constructs like IF-THEN-ELSE-ELSIF-END IF and SWICH, and iterative constructs such as LOOP-END LOOP and FETCH-[until]-NOTFOUND. This section can call and pass parameters to stored functions and procedures. (Required block) Exception… Exception handling section, can be nested (optional block) End;

A typical PL/SQL (stored-procedure/function-style) program is the same with a different opening and closing as follows:

Procedure/function [name] (pram1 in type, parm2 in type) [return is type] is…

Begin Exception... End;

End [name];

Notice that only the executable section is required for scripts, however the opening and closing decelerations are required to compile the procedure and store it in the database. PL/SQL procedures are marked either as Valid if there are no errors or Invalid if there is any error during compilation. If any complied procedure is invalid then all dependent procedures are inherently invalid also. It is also worth mentioning that DDL commands are not allowed in PL/SQL and the only directly allowed SQL statements are SELECT, UPDATE, INSERT, and DELETE.


Variables in PL/SQL

THE DECLARE STATEMENT

 •To use variables, cursors, classes, or user data structures in PS/SQL they first must 
  be declared. This is accomplished using the declare statement.

Example: declare numeric ID and a string name variables

DECLARE

   ID   NUMBER; 
   NAME VARCHAR2(50); 

BEGIN…


NUMERIC TYPES

 •NUMBER
 •BINARY_FLOAT
 •BINARY_DOUBLE
 •DEC
 •DECIMAL
 •DOUBLE PRECISION
 •FLOAT
 •INTEGER
 •INT
 •NUMERIC
 •REAL
 •SMALLINT
 •BINARY_INTEGER
 •PLS_INTEGER

STRING/CHARACTER TYPES

 •VARCHAR2
 •VARCHAR
 •CHAR
 •LONG
 •RAW
 •NCHAR2
 •NCHAR

OTHER TYPES

 •BOOLEAN
 •DATE
 •USER DEFINED TYPES
 •ADVANCED USER DEFINED TYPES

CURSORS

 •Cursors are used for iterative dataset processing. Cursors allow for row-by-row processing of a ‘Select
  query’ result set. The Fetch command allow for the variable assignment of each selected field. Cursors
  are designed to be used in one of several for-each-row style looping constructs. 

Example: Get Person ID, Name, and IQ from the person table and use that information to determine which intelligence category to place each person into and then insert their identity into the corresponding table. We will commit the additional records at a frequency of every 250 inserts.

   DECLARE
   RCNT     NUMBER := 1;
   ID       NUMBER;
   NAME     VARCHAR2(30);
   IQ       NUMBER;
   CURSOR CUR IS 
   SELECT 
   PERSON_ID, 
   PERSON_NAME,
   PERSON_IQ 
   FROM
   PERSON
   WHERE 
   PERSON_CHANGE_IND 
   IS 
   NULL
   BEGIN
   OPEN CUR;
   LOOP
   FETCH CUR INTO ID,NAME,IQ; 
   EXIT WHEN (CUR%NOTFOUND);
   IF IQ > 150
       INSERT INTO GENIUSES(GENIUS _ID, GENIUS_NAME, GENIUS_IQ) VALUES(ID,NAME,IQ);
   ELSEIF IQ > 115
       INSERT INTO SMARTIES(SMARTY_ID, SMARTY_NAME, SMARTY_IQ) VALUES(ID,NAME,IQ);
   ELSEIF IQ > 75
       INSERT INTO NORMALS(PERSON_ID, PERSON_NAME, PERSON_IQ) VALUES(ID,NAME,IQ);
   ELSE
       INSERT INTO DUMMYS(DUMMY_ID,DUMMY_NAME,DUMMY_IQ) VALUES(ID,NAME,IQ);
   END IF;
   RCNT:=RCNT+1;
   IF RCNT = 250 THEN
       COMMIT;
       CCNT:= CCNT+1;
   RCNT:=0;
   END IF;
   END LOOP;
   CLOSE CUR;
   COMMIT;
   END;

Ad blocker interference detected!


Wikia is a free-to-use site that makes money from advertising. We have a modified experience for viewers using ad blockers

Wikia is not accessible if you’ve made further modifications. Remove the custom ad blocker rule(s) and the page will load as expected.