Oracle PL SQL basics as well as tips and tricks

Lexical Units” are what make up pl sql code
Lets break up some code and identify the 4 categories of lexical units (Delimeters, Literals, Identifiers and Comments)

DECLARE
 count PLS_INTEGER := 1;
 BEGIN
 LOOP
 DBMS_OUTPUT.PUT_LINE('a');
 count := count + 1;
 EXIT WHEN count = 2;
 END LOOP;
 -- end of processing
 END;

Some Delimeters (symbols like + divide / equals =) are =, +
Some Literals (numbers characters strings or booleans) are a, 1, 2
Some Identifiers (variables and reserved words) are DECLARE, count, PLS_INTEGER, BEGIN, LOOP
Some Comments (anything that is not executed) are — end of processing

%TYPE attribute

The type attribute allows you to assign column data types to your variable by referencing the data type of columns already in use within your database tables.

Here are some examples of how to do this, first using table column to assign data type

var_name table.column_name%TYPE
var_lname employees.last_name%TYPE

and now using another variable as the reference to assign the data type

v_balance NUMBER(7,2);
v_another_balance v_balance%TYPE;

UNION AND UNION ALL
Union all does not remove duplicates it returns the combination of two or more select queries.
Union removes duplicate rows by performing a disticnt sort implicitly and therefore is slower than the union all to execute.
Think about Unions as a way of returning all of the rows from the given tables (combining all results).
Things to remember … data types, order of columns and number of columns have to match. The database will try to do an implicit conversion if data types do not match exactly however it is advised that the data types match explicitly (check your database schema to ensure this)

select id, name, address from tbl1 UNION ALL select id, name, address from tbl2 

If you want to sort the results you have to add the order by command only at the end of the expression as union all takes the rows from the first table and combines them with the results from the second table. Putting the order by clause after the first select statement will result in an error.

JOIN

Think about Joins as a way of retrieving values from columns in different database tables based on logic (joining columns of tables which were designed to interact using primary keys and foreign keys). Joins are a way of extracting information from normalized tables.

There are 3 categories of outer join, left outer join, right outer join and full outer join. You can just say left join, right join and full join.
LEFT OUTER JOIN, or just LEFT JOIN
This is used in the same way as an INNER JOIN (see syntax below). The difference is … we get all results returned from the first table specified (left table) regardless of whether there is a match with the right table based on id.
Q1

select name, gender, salary, departmentName from tbl1 LEFT JOIN tbl2 ON tbl1.id = tbl2.id;

The results returned are all matching results from both tables as well as non matching rows from the left table.
RIGHT OUTER JOIN, or just RIGHT JOIN
We can do the reverse and get all matching results from both tables as well as non matching records from the right table by using RIGHT JOIN.
Q2

select name, gender, salary, departmentName from tbl1 RIGHT JOIN tbl2 ON tbl1.id = tbl2.id;

FULL OUTER JOIN, or just FULL JOIN
Q4

select name, gender, salary, departmentName from tbl1 FULL JOIN tbl2 ON tbl1.id = tbl2.id;

IMG_1524[1]

INNER JOIN
The matching values between tables are returned. Non matching rows are eliminated. Type INNER JOIN explicitly to be clear. If we want to combine values from tbl1 with values from tbl2 there must be a common column (primary key/foriegn key mapping). We use the ON command to specify the common column. Notice that we don’t report on the id of each table, we just use the id’s as the tie between the two tables.
Q3

select name, gender, salary, departmentName from tbl1 INNER JOIN tbl2 ON tbl1.id = tbl2.id;

We are retrieving the name, gender and salary from tbl1 and the departmentName from tbl2 only if the id in tbl1 matches the id in table 2 for each given instance.

CROSS JOIN
Each value from the right table is returned in combination with each value from the left table. When the first value from the right table has been paired with every value from the left table the next value from the right table is hit; returning itself paired with every value from the left table and so on. Below is an example. There is no on clause for this join because the output is not logical it is just pumping out every permutations of the two tables.

bob M 10 Science
mary F 20 Science
sue F 10 Science
Tim M 20 Science
bob M 10 Maths
and so on ...

Cursors in Oracle
I will be explaining explicit and implicit cursors here really soon, will get some code examples as the results I am getting are quite interesting.

GROUP BY
Group by is returning aggregate data not individual rows per Se. All values selected must be results of aggregate functions (can’t select id, count(daysWorked), sum(wages) because id is referring to a single record. If you returned count(id) this would give you the number of rows in the table with an id (number of workers).
There are two additional syntax options when using the “group by” clause. “where” and “having”. The “where” option is used before the “group by” and the “having” is used after the “group by” as shown below. Where clause can be used with select insert and update statements but the having clause can only be used with select statement. The where statement filters the rows returned before the aggregation takes place and is generally more efficient as we are processing less. In general it is best to stick with where clause.

select city, sum(salary) as totalSalary
from tblEmployees
where city = 'Toowoomba'
group by city
select city, sum(salary) as totalSalary
from tblEmployees
group by city
having city = 'Toowoomba'
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