PL/SQL 101

PL/SQL ก็คือภาษาอีกภาษาหนึ่งนั่นแหละ เพียงแต่ว่ามันมันเขียนบน Oracle Database โดยหน้าที่สำคัญของมันก็คือการดึงเอาข้อมูลมาแสดง และจัดการกับ Database

Basic syntax

DECLARE
   <declaration section>
BEGIN
   <executable command(s)>
EXCEPTION
   <exception handling>
END;

PL/SQL Hello World

DECLARE
   message VARCHAR2(20) := 'Hello, World!';
BEGIN
   DBMS_OUTPUT.put_line(message);
END;

>> Hello, World!
 Notice
:= ใช้ในการ define variable value

Data type

  • Scalar
    Single values with no internal components, such as a NUMBER, DATE, or BOOLEAN.
  • Large Object (LOB)
    Pointers to large objects that are stored separately from other data items, such as text, graphic images, video clips, and sound waveforms.
  • Composite
    Data items that have internal components that can be accessed individually. For example, collections and records.
  • Reference
    Pointers to other data items.

Variable Declaration

variable_name [CONSTANT] data_type [NOT NULL][:=|DEFAULT initial_value]
Variable Scope
DECLARE
   -- Global variable
   num1 number := 95;
   num2 number := 96;
BEGIN
   DECLARE
      -- Local variable
      num1 number := 195;
      num2 number := 196;
   BEGIN
      DBMS_OUTPUT.put_line('Inner variable num1: ' || num1);
      DBMS_OUTPUT.put_line('Inner variable num2: ' || num2);
   END;
   DBMS_OUTPUT.put_line('Global variable num1: ' || num1);
   DBMS_OUTPUT.put_line('Global variable num2: ' || num2);
END;

>> Inner variable num1: 195
>> Inner variable num2: 196
>> Global variable num1: 95
>> Global variable num2: 96

Notice
|| คือการเชื่อง string หรือ CONCAT ใน MySQL

Assigning SQL Query Results to PL/SQL Variables

DECLARE
   c_id   customers.id%TYPE := 1
   c_name customers.name%TYPE;
   c_addr customers.address%TYPE;
   c_sal  customers.salary%TYPE;
BEGIN
   SELECT name, address, salary INTO c_name, c_addr, c_sal
   FROM customers
   WHERE id = c_id;
   DBMS_OUTPUT.put_line('Customer ' || c_name || ' from ' || c_addr || ' earns ' || c_sal);
END;

Notice

[column_name]%TYPE เป็นการกำหนดตัวแปรจาก column คือไม่สนใจว่ามันจะเป็น type อะไร ก็ define variable ด้วย type ของ column ที่กำหนดนั่นแหละ

FOR LOOP 

DECLARE
   i number(1);
   j number(1);
BEGIN
   FOR i IN 1..3 
   LOOP
      FOR j IN 1..3 
      LOOP
         DBMS_OUTPUT.put_line('i is: ' || i || ', j is: ' || j);
      END LOOP;
   END LOOP;
END;

>> i is: 1, j is: 1
>> i is: 1, j is: 2
>> i is: 1, j is: 3
>> ...

Procedure Syntax

มันคือ function ที่ไม่มีการ return

CREATE [OR REPLACE] PROCEDURE procedure_name
[(parameter_name [IN|OUT|IN OUT] type [, ...])]
{IS|AS}
BEGIN
   <procedure_body>
END procedure_name
Notice
IN คือการรับ param เข้า
OUT คือการใส่ค่าเข้า param ที่กำหนด
IN OUT คือการกำหนดค่าทั้งเข้าและออก
Example simple procedure
CREATE OR REPLACE PROCEDURE greetings
AS
BEGIN
   DBMS_OUTPUT.put_line('Hello World!');
END;

BEGIN
   greetings;
END;
Example procedure parameter with IN, OUT
DECLARE
   a number;
   b number;
   c number;
PROCEDURE findMin(x IN number, y IN number, z OUT number) 
IS
BEGIN 
   IF x < y THEN
      z := x;
   ELSE
      z := y;
   END IF;
END;

BEGIN
   a := 23;
   b := 45;
   findMin(a, b, c);
   DBMS_OUTPUT.put_line('Minimum of (23, 45): ' || c);
END;

>> Minimum of (23, 45): 23

Example parameter with IN OUT

DECLARE 
   a number;

PROCEDURE squareNum(x IN OUT number) IS
BEGIN
   x := x*x;
END;

BEGIN
   a := 2;
   squareNum(a);
   DBMS_OUTPUT.put_line('Square of (2) is ' || a);
END;

>> Square of (2) is 4

Function
ก็คือ function นั่นแหละ = =’

CREATE [OR REPLACE] FUNCTION function_name
[(parameter_name [IN|OUT|IN OUT] type [, ...])]
RETURN return_datatype
{IS | AS}
BEGIN
   <function_body>
END [function_name];
Example
CREATE OR REPLACE FUNCTION totalCustomers
RETURN number IS
   total number(2) := 0;
BEGIN
   SELECT COUNT(*) INTO total
   FROM customers;
   RETURN total;
END;

DECLARE
   c number(2);
BEGIN
   c := totalCustomers();
   DBMS_OUT_PUT.put_line('Total No. of Customers: ' || c);
END;

>> Total No. of Customer: 2

Example with Declaring, Defining and Invoking a simple function

DECLARE
   a number;
   b number;
   c number;

FUNCTION findMax(x IN number, y IN number)
RETURN number IS
   z number
BEGIN
   IF x > y THEN
      z := x;
   ELSE
      z := y;
   END IF;
   RETURN z;
END;

BEGIN
   a := 23;
   b := 45;
   c := findMax(a, b);
   DBMS_OUTPUT.put_line('Maximum of (23, 45) is ' || c);
END;

>> Maximum of (23, 45) is 45
Example Recursive Functions
DECLARE 
   num number;
   factorial number;

FUNCTION fact(x number)
RETURN number IS
   f number
BEGIN
   IF x = 0 THEN
      f := 1;
   ELSE
      f := x * fact(x - 1);
   END IF;
   RETURN f;
END;

BEGIN
   num := 6;
   factorial := fact(num);
   DBMS_OUTPUT.put_line('Factorial ' || num || ' is ' || factorial);
END;

>> Factorial 6 is 720

Implicit Cursors

  • SQL%FOUND:
    Returns TRUE if an INSERT, UPDATE, or DELETE statement affected one or more rows or a SELECT INTO statement returned one or more rows. Otherwise, it returns FALSE.
  • SQL%NOTFOUND:
    The logical opposite of %FOUND. It returns TRUE if an INSERT, UPDATE, or DELETE statement affected no rows, or a SELECT INTO statement returned no rows. Otherwise, it returns FALSE.
  • SQL%ISOPEN:
    Always returns FALSE for implicit cursors, because Oracle closes the SQL cursor automatically after executing its associated SQL statement.
  • SQL%ROWCOUNT:
    Returns the number of rows affected by an INSERT, UPDATE, or DELETE statement, or returned by a SELECT INTO statement.

Example

DECLARE
   total_rows number(2);
BEGIN
   UPDATE CUSTOMERS
   SET salary = salary * 2;

   IF SQL%NOTFOUND THEN
      DBMS_OUTPUT.pit_line('No customer updated');
   ELSEIF SQL%FOUND THEN
      total_rows := SQL%ROWCOUNT;
      DBMS_OUTPUT.put_line(total_rows || ' Customers updated');
   END IF;
END;

>> 6 Customers updated

Notice

สำหรับ script ตัวนี้ ถ้าหากว่า SQL script มีข้อมูล (ในตัวอย่างคือตาราง customer มีข้อมูล) มันจะทำการ นับ row ที่มีการอัพเดท และ print ออกมา

ตรงกันข้าม ถ้าไม่มีข้อมูล ก็จะแสดง No customer update

Explicit Cursors

ก็คือการเก็บ cursors ของการทำงานในฐานข้อมูล ซึ่งจะแบ่งเป็น 4 action คือ

  1. Declaring the cursor for initializing in the memory
  2. Opening the cursor for allocating memory
  3. Fetching the cursor for retrieving data
  4. Closing the cursor to release allocated memory

Example declaring

CURSOR c_customers IS
   SELECT id, name, address FROM customers;

Example opening

OPEN c_customers;

Example fetching

FETCH c_customers INTO c_id, c_name, c_addr;

Example closing

CLOSE c_customers;
Example
DECLARE
   c_id customers.id%TYPE;
   c_name customers.name%TYPE;
   c_addr customers.address%TYPE;
   CURSOR c_customers IS
      SELECT id, name, address FROM customers ORDER BY id ASC;
BEGIN
   OPEN c_customers;
   LOOP
      FETCH c_customers in c_id, c_name, c_addr;
      DBMS_OUTPUT.put_line(c_id || ' ' || c_name || ' ' || c_addr);
      EXIT WHEN c_customers%NOTFOUND;
   END LOOP;
   CLOSE c_customers;
END;

Example

DECLARE
   CURSOR customer_cur IS
      SELECT id, name, address FROM customers ORDER BY id ASC;
   customer_rec customer_cur%ROWTYPE;
BEGIN
   OPEN customer_cur;
   LOOP
      FETCH customer_cur INTO customer_rec;
      EXIT WHEN customer_cur%NOTFOUND;
      DBMS_OUTPUT.put_line(customer_rec.id || ' - ' || customer_rec.name);
   END LOOP;
END;

Varray
คือ array ใน PL/SQL แตกต่างจากภาษาอื่นๆตรงที่ index มันเริ่มที่ 1 ภาษาอื่นๆเค้าเริ่มที่ 0

Example use

DECLARE
   TYPE array_name IS VARRAY(3) OF CHAR(1);
BEGIN
   name = array_name('r', 'g', 'b');
   DBMS_OUTPUT.put_line( name(1) );
END;

>> r

Example advance

DECLARE
   CURSOR c_customer IS 
      SELECT name FROM customers;
   TYPE c_list IS VARRAY(6) OF customers.name%TYPE;
   name_list c_list := c_list();
   counter integer := 0;
BEGIN
   FOR n IN c_customer
   LOOP
      counter := counter + 1;
      name_list.extend;
      name_list(counter) := n.name;
      DBMS_OUTPUT.put_line('Customer(' || counter || ') is ' || name_list(counter));
   END LOOP;
END;

Case Statement

ก็คือ switch case ในภาษาทั่วไป

Example

CASE customers.id
   WHEN 1 THEN DBMS_OUTPUT.put_line(customers.id || 'st');
   WHEN 2 THEN DBMS_OUTPUT.put_line(customers.id || 'nd');
   WHEN 3 THEN DBMS_OUTPUT.put_line(customers.id || 'rd');
   ELSE DBMS_OUTPUT.put_line(customers.id || 'th');
END CASE;

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