PL SQL BASICS

Declaring PL/SQL Variables

v_hiredate DATE;
v_deptno NUMBER(2) NOT NULL := 10;
v_location VARCHAR2(13) := ‘Atlanta’;
c_comm CONSTANT NUMBER := 1400;

Scalar Variable Declarations

v_job VARCHAR2(9);
v_count BINARY_INTEGER := 0;
v_total_sal NUMBER(9,2) := 0;
v_orderdate DATE := SYSDATE + 7;
c_tax_rate CONSTANT NUMBER(3,2) := 8.25;
v_valid BOOLEAN NOT NULL := TRUE;

Declaring Variables with %TYPE %ROWTYPE Attributes

%TYPE

identifier Table.column_name%TYPE;

v_name employees.last_name%TYPE;
v_balance NUMBER(7,2);
v_min_balance v_balance%TYPE := 10;

%ROWTYPE

The %ROWTYPE attribute provides a record type that represents a row in a table. The record can store an entire row of data selected from the table or fetched from a cursor or cursor variable.

dept_rec departments%ROWTYPE;

DECLARE
  CURSOR c1 IS
    SELECT last_name, salary, hire_date, job_id FROM employees 
       WHERE employee_id = 120;
--declare record variable that represents a row fetched from the employees table
   employee_rec c1%ROWTYPE; 
BEGIN
-- open the explicit cursor and use it to fetch data into employee_rec
  OPEN c1;
  FETCH c1 INTO employee_rec;
  DBMS_OUTPUT.PUT_LINE('Employee name: ' || employee_rec.last_name);
END;

 

 

 

CONTROL STRUCTURES

IF THEN ELSIF Stataments

IF v_start > 100 THEN

v_start := 0.2 * v_start;

ELSIF v_start >= 50 THEN

v_start := 0.5 * v_start;

ELSE

v_start := 0.1 * v_start;

END IF;

 

CASE Statements

CASE v_grade

WHEN ‘A’ THEN ‘Excellent’

WHEN ‘B’ THEN ‘Very Good’

WHEN ‘C’ THEN ‘Good’

ELSE ‘No such grade’

END;

CASE

   WHEN x = 1 THEN sequence_of_statements_1;

   WHEN x = 2 THEN sequence_of_statements_2;

   WHEN x = 3 THEN sequence_of_statements_3;

   WHEN (x = 4 && y <10) THEN sequence_of_statements_4;

   WHEN x = 5 THEN sequence_of_statements_5;

   ELSE sequence_of_statements_N;

END CASE;

BASIC LOOPS

LOOP

statement1;

. . .

EXIT [WHEN condition];

END LOOP;

 

LOOP

INSERT INTO locations(location_id, city, country_id)

VALUES((v_location_id + v_counter),v_city, v_country_id);

v_counter := v_counter + 1;

EXIT WHEN v_counter > 3;

END LOOP;

 

WHILE LOOP

WHILE v_counter <= 3 LOOP

INSERT INTO locations(location_id, city, country_id)

VALUES((v_location_id + v_counter), v_city, v_country_id);

v_counter := v_counter + 1;

END LOOP;

 

FOR LOOP

FOR RecordIndex IN (SELECT person_code FROM people_table)
LOOP
  DBMS_OUTPUT.PUT_LINE(RecordIndex.person_code);
END LOOP;
FOR var IN 0 .. 10 LOOP 
          DBMS_OUTPUT.put_line(var);
     END LOOP;
DECLARE
  CURSOR cursor_person IS
    SELECT person_code FROM people_table;
BEGIN
  FOR RecordIndex IN cursor_person
  LOOP
    DBMS_OUTPUT.PUT_LINE(RecordIndex.person_code);
  END LOOP;
END;
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: