Peculiarly, Oracle uses the double pipe ( || ) as a concatenation operator. Most programming languages use || for the or operator. Example:
insert into mytable (id, name) values (12, 'Mr. ' || lastname);
The field lastname is concatenated to the string Mr.
All columns of your SELECT must be listed in the GROUP BY expression. Alternately you can use functions MIN() or MAX() which compress results.
A package is an encapsulated collection of related PL/SQL types, objects, procedures, and other program objects. Packages have a package specification and a package body. Package specification declares constants, variables, types, cursors, exceptions and procedures. Package definition defines cursors and procedures.
Packages provide the advantages of modularity, information hiding, and encapsulation.
Package commands To create a package
CREATE PACKAGE package_name
To create package body
NVL function can be used to substitute a value when null in encountered. It has the following syntax:
NVL(string,replacement_string)
If string has a value, it will be displayed. If the string is null, the replacement string will be displayed.
select NVL(salary,'unpaid volunteer') from staff
This query prints staff salaries. Volunteers do not have a salary so the value for their salary is null. In the query, when the salary is null, replacement string 'unpaid volunteer' is printed.
If you want to list tables in MySQL, you simply type the following command:
show tables
Oracle does not provide such a short cut. You have to type the following command:
select * from user_objects where object_type = 'TABLE'
The word TABLE must be uppercase. This would lisl all tables in the database. The same query could be modified slightly to list functions, procedures, sequences, view and more.
List functions
The most commonly used set operators in Oracle are:
Union Union combine results returned by two or queries into a single table. In a union, all tables must have matching columns. It shows all rows from the first query and all rows from the second query while removing duplicate entries. It has the syntax:
query1 union query2
For example, we have a query1
select name, salary from employee where gender = 'M' order by salary
and a query2
Decode function has the same functionality as an IF-THEN-ELSE statement. It is present in Oracle 9i and above. It has the syntax
decode(column/expression, value, substitute, default value)
For example,
decode(gender, 'M', 'Male', 'F', 'Female', gender) gndr
This is like saying the following in PL/SQL:
IF gender = 'M' THEN
gndr := 'Male';
ELSIF gender = 'F' THEN
gndr := 'Female';
ELSE
gndr := gender;
END IF;
An SQL example,
There are two ways of adding a leading dollar sign to numbers in Oracle. You can either define teh column with a number format which display number with a dollar sign or you can define it in you select statement as follows:
select name, to_char(salary, '$9,999,990,99') from salarytable
to_char is used to change string formatting. Here we add $ and a comma after every three digits left of the decimal. We also specified that there are two decimal places. Without the 0 in the middle, you would get $.00 instead of $0.00 when the salary is 0.
The following three operators have the identical meaning: !=, ^=, <>
select name from worker where gender <> 'male';
Another way to test for inequality it to use "not in"
select name from worker where type not in ('manager','contractor');
MySQL and Postgres support the limit clause which limits the number of entries returned when a query is executed:
select * from member limit 5
This query will return at most 5 entries. To do the same in Oracle, you need to use the rownum clause: