Tuesday 19 June 2012

Fledgling PL/SQL Devleoper

PL/SQL : Procedure Language for SQL

nice step by step guide for running procedure from sql developer is here


PL/SQL code blocks comprise of statements. Each statement ends with a semi colon. PL/SQL code blocks are followed by a forward slash / in the first position of the following line.  This causes the code block statements to be executed. The End keyword is the only PL/SQL code block keyword that is followed by a semi colon.

PL/SQL procedures are wrapped in Packages for reusability.

Anonymous block:  PL/SQL block that is contained in an external file are called anonymous block procedures and can be executed from the SQL prompt by using START keyword, which causes SQL*Plus to load, compile and execute the code block in the file.
e.g.

START c:\plsql\code.sql

Note: to start sqlplus type sqlplus from command prompt.


PL/SQL  Code Block has following sections:

0. Header: This is optional first block. It is used to identify the type of code block and its name. The code block types are:
          a. Anonymous procedure
          b. Named procedure
          c. function

1. Declaration(optional)-  This is an optional section and contains the names of the local objects that will be        used in the code block. These include variables, cursor definitions and exceptions. This section begins with the keyword DECLARE.

2. Executable(mandatory): This section starts with keyword BEGIN and is terminated by keyword END. This is the only mandatory section and contains the statements that will be executed i.e.  SQL statements, DML statements, procedures(PL/SQL code blocks returning a value) , and build in subprograms.  This section starts with the word BEGIN.

3. Exception(optional): An optional section to handle any errors that occur during the execution of the statements and commands in the executable section.

Code block is terminated by the END keyword, the only construct keyword that is followed by a semicolon.  Executable is the only required section, which means the code block must have BEGIN and END. The code block is executed by /

declare
   local_variable varchar2(30);
begin
   select 'Num of employees'|| to_char(count(last_name), '999')
       into local_variable
  from employee;
 dbms_output.put_line(local_variable);
exception
   when others then dbms_output.put_line('ERROR OCCURED');
end;
/

The select command is used to compute the number of employees in the database, concatenate this value with another string and assign the value to variable defined in declare section.  The final section is the output section. It contains a catchall error handler, the statement which is executed when a database error occurs and when an error handler is not available.


Variable Definition:


Variables are defined as :

Variable_Name   Datatype(precision) ;

in Declaration section. Oracle treats variable definition as any other statement so a semi colon is mandatory at the end. Value may be assigned to a variable during definition as well. The variable may also be constrained.

 Character Definitions:

 Variables that contain alphanumeric values are normally defined as CHAR or VARCHAR2.
VARCHAR2 : contains null spaces in each position that a character does not occupy. Defining length is mandatory.  Maximum length can be 32767.
CHAR: contain white space in the unoccupied positions.  Whitespace and null are not the same and are not
comparable.  Length when not specified is 1. maximum can be 32767.

Note: PL/SQL length of CHAR and VARCHAR2 datatypes is larger than the length allowed in the Oracle database.

Numeric Definitions:

Numeric data definitions can include two parameters: precision and scale. Precision defines overall length of the value. Scale determines the number of digits to the left or the right of the decimal point. The range of the scale is -84 to 127.  When a scale is specified, rounding will occur at the end. Following rules apply:

  a. Positive scale definitions cause rounding to the right of the decimal point.
  b. Negative scale definitions cause rounding to the left of the decimal point.
  c. Zero scale definitions cause rounding to the nearest whole number.

The default precision of a number is 38.

e.g. age  integer(3); :  age must be a whole number less than 999
 galloons number(3);  the scale is not specified, the value can contain upto three places to the left of decimal or upto three positions. The overall length of the value can't be more than three positions.

salary  number(8, 2) ;  The overall length of value can't be more than 8 positions including decimal values. At max the variable will have two positions to the left of decimal.

Other datatypes: Several other datatypes including Boolean, Date, Exception can be used.

Constrained  Definitions:
 Constraints can be applied on the variables defined in the code block. Two common constraints are :

CONSTANT:    ensures that the value is not changed after a value is initially assigned to the variable.  If a statement tries to change the variable value, an error will occur.

NOT NULL:  ensures that the variable will always contain a not null value. If a statement tries to assign a null value to the variable, an error will occur.

e.g

febonaci  constant number(9,8): =12.333223;
resonate not null date:='08-APR-53'


Aggregate and PL/SQL Record Definitions

Aggregate variable definition is based on a database of PL/SQL object. It has following advantages:

a. The developer can define a variable with the same data specifications as a table column or cursor variable,
    without actually knowing the specifications.
b. The developers can set up an array of variables for a cursor or table record with one statement. The
    variables will have the same specifications as the table or cursor variables.

The first aggregate definition tool is %TYPE which allows developer to define a variable with the same specifications as the indicated table or cursor variable. Syntax:

VARIABLE_NAME  table_cursor_name.columns_name%type;

e.g.
lname    employee.last_name%type;

the second keyword is used to establish an array of variables based on the columns in a cursor or table. This aggregate is called a PL/SQL record. The keyword will create a variable for each column in the identified table/cursor.  Each variable in the array will have the same name and specification as its counterpart in the table/cursor.

syntax: ARRAY_NAME     table/cursor_name%rowtype;

Here is an e.g. for aggregate array definition:
 DEPT_VAR department%rowtype;

variables are acccessed as :

DEPT_VAR.department
DEPT_VAR.department_name

Assigning values to Variables


PL/SQL has two ways to assign values to variables:

:= assignment operator           assigns the argument of the left of the operator to the argument of variable on
                                             the right of the sign.

INTO                                   keyword is used in a SELECT or FETCH statement. When used in a
                                            SELECT statement, it assigns the values in the SELECT clause to the variables
                                            following the INTO keyword. When used with the FETCH statement, it
                                            assigns the cursor values to the variables that follow the INTO keyword.

When using the INTO keyword in a SELECT clause, be sure that only one record is retrieved. If more than one record is retrieved an, error will occur.






















No comments:

Post a Comment