YouOracle: July 2010

Simple PL/SQL examples

#1 Multiplication table

/*
Typer: youoracle.blogspot.com
Date: 30.07.2010
Note: maximum dimension is 1000 for normal output
*/
CREATE OR replace PROCEDURE zeynal."Proceduremultiplicationtable" (
dimension IN NUMBER DEFAULT 1)
IS
  iterrow    NUMBER;
  itercolumn NUMBER;
  result     VARCHAR2 (100);
BEGIN
  FOR iterrow IN 1 .. dimension LOOP
      FOR itercolumn IN 1 .. dimension LOOP
          result := Cast (iterrow * itercolumn AS VARCHAR2);

          dbms_output.Put (Rpad (result, 15 - Length (result) - 1));
      END LOOP;

      dbms_output.Put_line ('');
  END LOOP;
END;

/

Runing procedure:

DECLARE
    dimension NUMBER;
BEGIN
    dimension := 10;

    zeynal."Proceduremultiplicationtable" (dimension);

    COMMIT;
END;

Output:

1    2    3
2    4    6
3    6    9

Cool Oracle SQLs

#1 SELECT 4 + + 4 FROM dual

Result: 8

How to use ORDER BY?

ORDER BY operator is used to sort result of a SELECT statement.
It is used with 2 options:

ASC - ascending order (it is default, you can ignore writing it)
DESC - descending order

Example: Select employees from employee table and sort employee names in alphabetical order.

SELECT * FROM tableEmployee ORDER BY employeeName ASC

Select employees sort them by employee names in ascending order, but by birthday in descending order.

SELECT * FROM tableEmployee ORDER BY employeeName ASC, employeeBirthday DESC

How to use LIKE?

LIKE operator is used to search for patterns in string.
It is used with 2 wild-cards:

_ means just a character
% means any number of character (it also means empty word)

Example: Select all employee records where employee name start with 'a' character and rest can be any thing.

SELECT * FROM tableEmployees WHERE employeeName LIKE 'a%'

Select all employee records where employee name 1st character is 'a', 2nd character is any thing, 3rd character is 'b' and the rest is any character sequence.

SELECT * FROM tableEmployees WHERE employeeName LIKE 'a_b%'

If we need to find '_' or '%' in a string we must put escape character before them.

Example: Select all image records from images table where image name start with 'IMAGE_' and the rest is anything.

SELECT * FROM images WHERE employeePhoto LIKE 'IMAGEID\_%' ESCAPE '\'

How to use DISTINCT?

DISTINCT is used to select unique rows from a table.

Example: SELECT DISTINCT employeeName, employeeBirthday FROM tableEmployee

Which are SQL operators?

SQL have lots of operators in order to manipulate numeric and alphabetic datatypes.

Arithmetic operators:

unary + and -
+
-
*
/

Comparison operators:

= equal to
!=, ^=, <> not equal to
>, <, <=, >= greater/less than
IN, NOT IN
ANY, SOME, ALL
BETWEEN, NOT BETWEEN
EXISTS, NOT EXISTS
LIKE, NOT LIKE
IS NULL, IS NOT NULL

Logical operators

AND binary
OR binary
NOT unary

Set operators

UNION
UNION ALL
INTERSECT
MINUS

How to start Oracle?

ou can install Oracle Database 10g Express Edition to your workstation to test your SQLs.

Steps to install Oracle Database Express Edition.

1. Download it from
http://www.oracle.com/technetwork/database/express-edition/downloads/index.html

2. After installation start it as shown below.

3. You will be faced with web based interface of Oracle DB XE:

4. Login and get started...

CHAR(size)
NCHAR(size)
VARCHAR2(size)
NVARCHAR2(size)

Number

NUMBER(precision=total number of digits, scale=number of digits after decimal point)

Long and raw

LONG
RAW(size)
LONG RAW

Date and time

DATE - year-month-day-hour-minute-second
TIMESTAMP(fractional_seconds_precision) - year-month-day-hour-minute-second-fractional second
TIMESTAMP WITH {LOCAL} TIME ZONE(fractional_seconds_precision)
INTERVAL YEAR(year_precision) TO MONTH
INTERVAL DAY(day_precision) TO SECOND(fractional_seconds_precision)

Larges

CLOB - character large object
NCLOB - national ...
BLOB - binary large object
BFILE - pointer of binary file on the disk

Row IDs

ROWID - hexadecimal string representing address of the row
UROWID - universal row id: hexadecimal string representing logical address of the row

XML

XMLType

1
2

What is DCL?

DCL means Data Control Language

GRANT
REVOKE

are DCL statements.

What is TCO?

TCO means Transaction Control Operations

COMMIT
ROLLBACK
SAVEPOINT
SET TRANSACTION

are TCO statements.

What is DDL?

DDL means Data Definition Language.

CREATE
ALTER
DROP
RENAME
TRUNCATE

are DDL statements.

What is DML?

DML means Data Manipulation Language.

SELECT
INSERT
UPDATE
DELETE
MERGE

statements are DML statements.

YouOracle

Pages