Subject name:oracle

Part: plsql: Introduction

What Is Oracle SQL Developer?
• Oracle SQL Developer is a free graphical tool that enhances productivity and simplifies
database development tasks.

Chapter one
1:About PL/SQL
PL/SQL:
• Stands for “Procedural Language extension to SQL”
• Is Oracle Corporation’s standard data access language for relational databases
• Seamlessly integrates procedural constructs with SQL
• Provides a block structure for executable units of code. Maintenance of code is made
easier with such a well-defined structure.
• Provides procedural constructs such as:
• Variables, constants, and data types
• Control structures such as conditional statements and loops
• Reusable program units that are written once and executed many times

2:Benefits of PL/SQL
• Integration of procedural constructs with SQL
• Improved performance
• Modularized program development
• Integration with Oracle tools
• Portability
• Exception handling

3:PL/SQL Block Structure

• DECLARE (optional)
• Variables, cursors, user-defined exceptions
• BEGIN (mandatory)
• SQL statements
• PL/SQL statements
• EXCEPTION (optional)

1
 • Actions to perform
when exceptions occur
• END; (mandatory)

4:Block Types
• Procedure
• Function
• Anonymous

Chapter two: Declaring PL/SQL Variables

1: Use of Variables
Variables can be used for:
• Temporary storage of data
• Manipulation of stored values
• Reusability

2: Requirements for Variable Names

A variable name:
• Must start with a letter
• Can include letters or numbers
• Can include special characters (such as $, _, and #)
• Must contain no more than 30 characters
• Must not include reserved words

3: Handling Variables in PL/SQL

Variables are:
• Declared and (optionally) initialized in the declarative section
• Used and assigned new values in the executable section
• Passed as parameters to PL/SQL subprograms
• Used to hold the output of a PL/SQL subprogram

4: Types of Variables
• PL/SQL variables:
– Scalar
– Reference
– Large object (LOB)
– Composite
• Non-PL/SQL variables: Bind variables

5: Scalar Data Types

2
 • Hold a single value
• Have no internal components

6: Base Scalar Data Types

• CHAR [(maximum_length)]
• VARCHAR2 (maximum_length)
• NUMBER [(precision, scale)]
• BINARY_INTEGER
• PLS_INTEGER
• BOOLEAN
• BINARY_FLOAT
• BINARY_DOUBLE

7: TYPE Attribute
• Is used to declare a variable according to:
– A database column definition
– Another declared variable
• Is prefixed with:
– The database table and column name
– The name of the declared variable

8: Bind Variables
Bind variables are:
• Created in the environment
• Also called host variables
• Created with the VARIABLE keyword*
• Used in SQL statements and PL/SQL blocks
• Accessed even after the PL/SQL block is executed
• Referenced with a preceding colon
Values can be output using the PRINT command.
* Required when using SQL*Plus and SQL Developer

Chapter three: Writing Executable Statements

1: Lexical Units in a PL/SQL Block
Lexical units:
• Are building blocks of any PL/SQL block
• Are sequences of characters including letters, numerals, tabs, spaces, returns, and
symbols
• Can be classified as:

3
 – Identifiers: v_fname, c_percent
– Delimiters: ; , +, -
– Literals: John, 428, True
– Comments: --, /* */

2: Commenting Code
• Prefix single-line comments with two hyphens (--).
• Place a block comment between the symbols /* and */.
SQL Functions in PL/SQL
• Available in procedural statements:
– Single-row functions
• Not available in procedural statements:
– DECODE
Group functions

3: Data Type Conversion

• Converts data to comparable data types
• Is of two types:
– Implicit conversion
– Explicit conversion
• Functions:
– TO_CHAR
– TO_DATE
– TO_NUMBER
– TO_TIMESTAMP

4: Nested Blocks
PL/SQL blocks can be nested.
• An executable section (BEGIN … END) can contain nested blocks.
• An exception section can contain
nested blocks.

5: Operators in PL/SQL
• Logical
• Arithmetic
• Concatenation
• Parentheses to control order
of operations
Exponential operator (**)

6: Programming Guidelines
4
 Make code maintenance easier by:
• Documenting code with comments
• Developing a case convention for the code
• Developing naming conventions for identifiers and other objects
• Enhancing readability by indenting

Chapter four: Interacting with Oracle Database Server:

SQL Statements in PL/SQL Programs

1: SQL Statements in PL/SQL

• Retrieve a row from the database by using the SELECT command.
• Make changes to rows in the database by using DML commands.
• Control a transaction with the COMMIT, ROLLBACK, or SAVEPOINT command.
2:Example : SQL Statements in PL/SQL

DECLARE

v_fname VARCHAR2(25);

BEGIN

SELECT first_name INTO v_fname

FROM employees WHERE employee_id=200;

DBMS_OUTPUT.PUT_LINE(' First Name is : '||v_fname);

END;

DECLARE

3: Retrieving Data in PL/SQL: Example

v_sum_sal NUMBER(10,2);

v_deptno NUMBER NOT NULL := 60;

BEGIN

SELECT SUM(salary) -- group function

INTO v_sum_sal FROM employees

WHERE department_id = v_deptno;

DBMS_OUTPUT.PUT_LINE ('The sum of salary is ' || v_sum_sal);

END;

4: Using PL/SQL to Manipulate Data

Make changes to database tables by using DML commands:
INSERT,UPDATE,DELETE,MERGE
5: Inserting Data: Example

5
 6: Updating Data: Example

DECLARE

sal_increase employees.salary%TYPE := 800;

BEGIN

UPDATE employees

SET salary = salary + sal_increase

WHERE job_id = 'ST_CLERK';

END;

7: Deleting Data: Example

DECLARE

deptno employees.department_id%TYPE := 10;

BEGIN

DELETE FROM employees

WHERE department_id = deptno;

END;

8: Merging Rows Example

6
 9: SQL Cursor
• A cursor is a pointer to the private memory area allocated by the Oracle Server. It is
used to handle the result set of a SELECT statement.
• There are two types of cursors: implicit and explicit.
– Implicit: Created and managed internally by the Oracle Server to process SQL
statements
– Explicit: Declared explicitly by the programmer

10: SQL Cursor Attributes for Implicit Cursors

DECLARE

v_rows_deleted VARCHAR2(30)

v_empno employees.employee_id%TYPE := 176;

BEGIN

DELETE FROM employees

WHERE employee_id = v_empno;

v_rows_deleted := (SQL%ROWCOUNT ||

' row deleted.');

DBMS_OUTPUT.PUT_LINE (v_rows_deleted);
DECLARE
END; v_myage number:=31;
BEGIN
IF v_myage < 11
THEN
Chapter 5:Writing Control Structures
DBMS_OUTPUT.PUT_LINE(' I am a child
1: IF Statement ');
Syntax: ELSE
DBMS_OUTPUT.PUT_LINE(' I am not a
IF condition THEN
child ');
statements; END IF;
[ELSIF condition THEN END;
statements;] /
7
[ELSE
statements;]
END IF;

2: CASE Expressions
A CASE expression selects a result and returns it.
To select the result, the CASE expression uses expressions. The value returned by these
expressions is used to select one of several alternatives.
CASE selector
WHEN expression1 THEN result1
WHEN expression2 THEN result2
...
WHEN expressionN THEN resultN
[ELSE resultN+1]
END;
SET VERIFY OFF
3:CASE Expressions: Example
DECLARE
v_grade CHAR(1) := UPPER('&grade');
v_appraisal VARCHAR2(20);
BEGIN
v_appraisal := CASE v_grade
WHEN 'A' THEN 'Excellent'
WHEN 'B' THEN 'Very Good'
WHEN 'C' THEN 'Good'
ELSE 'No such grade'
END;
DBMS_OUTPUT.PUT_LINE ('Grade: '|| v_grade || '
Appraisal ' || v_appraisal);
END;
/

8
 4: Handling Nulls
When you are working with nulls, you can avoid some common mistakes by keeping in mind the
following rules:
• Simple comparisons involving nulls always yield NULL.
• Applying the logical operator NOT to a null yields NULL.
• If the condition yields NULL in conditional control statements, its associated sequence of
statements is not executed.
5: Iterative Control: LOOP Statements
• Loops repeat a statement (or a sequence of statements) multiple times.
• There are three loop types:
– Basic loop
– FOR loop
– WHILE loop
5: Basic Loop: Example 6: WHILE Loops: Example

DECLARE DECLARE

v_countryid locations.country_id%TYPE := 'CA'; v_countryid locations.country_id%TYPE := 'CA';

v_loc_id locations.location_id%TYPE; v_loc_id locations.location_id%TYPE;

v_counter NUMBER(2) := 1; v_new_city locations.city%TYPE := 'Montreal';

v_new_city locations.city%TYPE := 'Montreal'; v_counter NUMBER := 1;

BEGIN BEGIN

SELECT MAX(location_id) INTO v_loc_id FROM locations SELECT MAX(location_id) INTO v_loc_id FROM locations

WHERE country_id = v_countryid; WHERE country_id = v_countryid;

LOOP WHILE v_counter <= 3 LOOP

INSERT INTO locations(location_id, city, country_id) INSERT INTO locations(location_id, city, country_id)

VALUES((v_loc_id + v_counter), v_new_city, v_countryid); VALUES((v_loc_id + v_counter), v_new_city,

v_countryid);
v_counter := v_counter + 1;
v_counter := v_counter + 1;
EXIT WHEN v_counter > 3;
END LOOP;
END LOOP;
END;
END;
/
/

FOR Loops
• Use a FOR loop to shortcut the test for the number of iterations.
• Do not declare the counter; it is declared implicitly.
FOR
DECLARE Loops: Example
v_countryid locations.country_id%TYPE := 'CA';

v_loc_id locations.location_id%TYPE;

v_new_city locations.city%TYPE := 'Montreal';

BEGIN

SELECT MAX(location_id) INTO v_loc_id

FROM locations
9
WHERE country_id = v_countryid;

FOR i IN 1..3 LOOP

FOR Loop Rules
• Reference the counter only within the loop; it is undefined outside the loop.
• Do not reference the counter as the target of an assignment.
• Neither loop bound should be NULL.

Suggested Use of Loops

• Use the basic loop when the statements inside the loop must execute at least once.
• Use the WHILE loop if the condition must be evaluated at the start of each iteration.
• Use a FOR loop if the number of iterations is known.
Nested Loops and Labels
• You can nest loops to multiple levels.
• Use labels to distinguish between blocks and loops.
• Exit the outer loop with the EXIT statement that references the label.
Chapter 6: Working with Composite Data Types
Composite Data Types

• Can hold multiple values (unlike scalar types)

• Are of two types:

– PL/SQL records

– PL/SQL collections

— Associative array (INDEX BY table)

— Nested table

— VARRAY

Associative Arrays (INDEX BY Tables)

An associative array is a PL/SQL collection with two columns:

• Primary key of integer or string data type

• Column of scalar or record data type

Chapter 7: Using Explicit Cursors

Cursors

Every SQL statement that is executed by the Oracle Server has an associated individual cursor:

• Implicit cursors: declared and managed by PL/SQL for all DML and PL/SQL SELECT statements

• Explicit cursors: declared and managed by the programmer

Declaring the Cursor

10
 DECLARE

CURSOR c_emp_cursor IS

SELECT employee_id, last_name FROM employees

WHERE department_id =30;

Opening the Cursor

DECLARE

CURSOR c_emp_cursor IS

SELECT employee_id, last_name FROM employees

WHERE department_id =30;

...

BEGIN

OPEN c_emp_cursor;

Chapter 9: Introducing Stored Procedures and Functions

Procedures and Functions
• Are named PL/SQL blocks
• Are called PL/SQL subprograms
• Have block structures similar to anonymous blocks:
– Optional declarative section (without the DECLARE keyword)
– Mandatory executable section
– Optional section to handle exceptions

11
 Creating a Procedur

...
CREATE TABLE dept AS SELECT * FROM
departments;
CREATE PROCEDURE add_dept IS
v_dept_id dept.department_id%TYPE;
v_dept_name dept.department_name%TYPE;
BEGIN
v_dept_id:=280;
v_dept_name:='ST-Curriculum';
INSERT INTO
dept(department_id,department_name)
VALUES(v_dept_id,v_dept_name);
DBMS_OUTPUT.PUT_LINE(' Inserted '||
SQL%ROWCOUNT ||' row ');
END;

Create function

CREATE FUNCTION check_sal RETURN Boolean IS

v_dept_id employees.department_id%TYPE;

v_empno employees.employee_id%TYPE;

v_sal employees.salary%TYPE;

v_avg_sal employees.salary%TYPE;

BEGIN

v_empno:=205;

SELECT salary,department_id INTO v_sal,v_dept_id FROM employees

WHERE employee_id= v_empno;

SELECT avg(salary) INTO v_avg_sal FROM employees WHERE department_id=v_dept_id;

IF v_sal > v_avg_sal THEN

RETURN TRUE; , ELSE , RETURN FALSE;

END IF;

EXCEPTION

WHEN NO_DATA_FOUND THEN

RETURN NULL;

END;

12