Yasser Oracle Blog Dive in Oracle with Yasser, Oracle Developer Courses
| Primary Key | Unique Constraint |
|---|---|
| None of the fields that are part of the primary key can contain a null value. | Some of the fields that are part of the unique constraint can contain null values as long as the combination of values is unique. |
| Format | Description | Example |
| 9 | Number (suppress leading/trailing zeros) Includes a leading - for negative no's or one leading space for pos no's | 9999=' 123' |
| 0 | Number including leading/trailing zeros | 09999=00123 9999D00=123.00 |
| FM | Supress all leading /trailing blanks | FM9999=123 |
| $ | Include a leading $ | $999=$123 |
| B | Over-ride the '0' format and replace leading 0's with blank spaces ' ' | B9999=' 123' |
| S | Include poth positive+ and negative - signs | S999 =+123 999S =123+ |
| PR | Indicate SIGN with <brackets> | PR999=<123> PR999=' 123 ' |
| MI | Indicate Minus SIGN | 999MI=123- |
| RN or rn | Roman numeral (upper or lower case) | RN99=XI |
| D | Position of decimal point(.) | 99D99=12.34 |
| G | Group separator (often a comma) in desired position | 9G999=1,234 |
| , | Return a comma in desired position | 9,999=1,234 |
| . | Return a period in desired position | 99.99=12.34 |
| C | ISO currency symbol | C99 |
| L | Local currency symbol | L99 |
| EEEE |
Return a value using in scientific notation
| 9.9EEEE= 1.2E+02 |
| TM | Text Minimum TM9(the default) or TME (Scientific notation) | TM9=12345 TME=1.2E+02 |
| U | Returns in the specified position the Euro (or other) dual currency symbol. (NLS_DUAL_CURRENCY) | U9999 = €1234 |
| V | Return a value multiplied by 10n , where n is the number of 9's after the V. (value will be rounded up if necessary) | 999V99= 123 x 1045 |
| X | Returns the hexadecimal value of the specified number of digits. | select to_char('64','xx') from dual; |
PI CONSTANT NUMBER := 3.141592654;
DECLARE
-- constant declaration
pi constant number := 3.141592654;
-- other declarations
radius number(5,2);
dia number(5,2);
circumference number(7, 2);
area number (10, 2);
BEGIN
-- processing
radius := 9.5;
dia := radius * 2;
circumference := 2.0 * pi * radius;
area := pi * radius * radius;
-- output
dbms_output.put_line('Radius: ' || radius);
dbms_output.put_line('Diameter: ' || dia);
dbms_output.put_line('Circumference: ' || circumference);
dbms_output.put_line('Area: ' || area);
END;
/
When the above code is executed at SQL prompt, it produces the following result:
CREATE PACKAGE cust_sal AS PROCEDURE find_sal(c_id customers.id%type); END cust_sal; /When the above code is executed at SQL prompt, it produces the following result:
CREATE OR REPLACE PACKAGE BODY cust_sal AS PROCEDURE find_sal(c_id customers.id%TYPE) IS c_sal customers.salary%TYPE; BEGIN SELECT salary INTO c_sal FROM customers WHERE id = c_id; dbms_output.put_line('Salary: '|| c_sal); END find_sal; END cust_sal; /When the above code is executed at SQL prompt, it produces the following result:
package_name.element_name;Consider, we already have created above package in our database schema, the following program uses the find_sal method of the cust_sal package:
DECLARE code customers.id%type := &cc_id; BEGIN cust_sal.find_sal(code); END; /When the above code is executed at SQL prompt, it prompt to enter customer ID and when you enter an ID, it displays corresponding salary as follows:
Enter value for cc_id: 1 Salary: 3000 PL/SQL procedure successfully completed.
Select * from customers; +----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 1 | Ramesh | 32 | Ahmedabad | 3000.00 | | 2 | Khilan | 25 | Delhi | 3000.00 | | 3 | kaushik | 23 | Kota | 3000.00 | | 4 | Chaitali | 25 | Mumbai | 7500.00 | | 5 | Hardik | 27 | Bhopal | 9500.00 | | 6 | Komal | 22 | MP | 5500.00 | +----+----------+-----+-----------+----------+
CREATE OR REPLACE PACKAGE c_package AS -- Adds a customer PROCEDURE addCustomer(c_id customers.id%type, c_name customers.name%type, c_age customers.age%type, c_addr customers.address%type, c_sal customers.salary%type); -- Removes a customer PROCEDURE delCustomer(c_id customers.id%TYPE); --Lists all customers PROCEDURE listCustomer; END c_package; /When the above code is executed at SQL prompt, it creates the above package and displays the following result:
Package created.
CREATE OR REPLACE PACKAGE BODY c_package AS PROCEDURE addCustomer(c_id customers.id%type, c_name customers.name%type, c_age customers.age%type, c_addr customers.address%type, c_sal customers.salary%type) IS BEGIN INSERT INTO customers (id,name,age,address,salary) VALUES(c_id, c_name, c_age, c_addr, c_sal); END addCustomer; PROCEDURE delCustomer(c_id customers.id%type) IS BEGIN DELETE FROM customers WHERE id = c_id; END delCustomer; PROCEDURE listCustomer IS CURSOR c_customers is SELECT name FROM customers; TYPE c_list is TABLE OF customers.name%type; name_list c_list := c_list(); counter integer :=0; BEGIN FOR n IN c_customers LOOP counter := counter +1; name_list.extend; name_list(counter) := n.name; dbms_output.put_line('Customer(' ||counter|| ')'||name_list(counter)); END LOOP; END listCustomer; END c_package; /Above example makes use of nested table which we will discuss in the next chapter. When the above code is executed at SQL prompt, it produces the following result:
Package body created.
DECLARE code customers.id%type:= 8; BEGIN c_package.addcustomer(7, 'Rajnish', 25, 'Chennai', 3500); c_package.addcustomer(8, 'Subham', 32, 'Delhi', 7500); c_package.listcustomer; c_package.delcustomer(code); c_package.listcustomer; END; /When the above code is executed at SQL prompt, it produces the following result:
Customer(1): Ramesh Customer(2): Khilan Customer(3): kaushik Customer(4): Chaitali Customer(5): Hardik Customer(6): Komal Customer(7): Rajnish Customer(8): Subham Customer(1): Ramesh Customer(2): Khilan Customer(3): kaushik Customer(4): Chaitali Customer(5): Hardik Customer(6): Komal Customer(7): Rajnish PL/SQL procedure successfully completed
COUNT as an aggregate function:SELECT COUNT(*) "Total"
FROM employees;
Total
----------
107
SELECT COUNT(*) "Allstars"
FROM employees
WHERE commission_pct > 0;
Allstars
---------
35
SELECT COUNT(commission_pct) "Count"
FROM employees;
Count
----------
35
SELECT COUNT(DISTINCT manager_id) "Managers"
FROM employees;
Managers
----------
18
employees table, the moving count of employees earning salaries in the range 50 less than through 150 greater than the employee's salary.SELECT last_name, salary,
COUNT(*) OVER (ORDER BY salary RANGE BETWEEN 50 PRECEDING AND
150 FOLLOWING) AS mov_count
FROM employees
ORDER BY salary, last_name;
LAST_NAME SALARY MOV_COUNT
------------------------- ---------- ----------
Olson 2100 3
Markle 2200 2
Philtanker 2200 2
Gee 2400 8
Landry 2400 8
Colmenares 2500 10
Marlow 2500 10
Patel 2500 10
hr.employees table:SELECT AVG(salary) "Average"
FROM employees;
Average
--------------
6461.83178
employees
table, the average salary of the employees reporting to the same
manager who were hired in the range just before through just after the
employee:SELECT manager_id, last_name, hire_date, salary,
AVG(salary) OVER (PARTITION BY manager_id ORDER BY hire_date
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS c_mavg
FROM employees
ORDER BY manager_id, hire_date, salary;
MANAGER_ID LAST_NAME HIRE_DATE SALARY C_MAVG
---------- ------------------------- --------- ---------- ----------
100 De Haan 13-JAN-01 17000 14000
100 Raphaely 07-DEC-02 11000 11966.6667
100 Kaufling 01-MAY-03 7900 10633.3333
100 Hartstein 17-FEB-04 13000 9633.33333
100 Weiss 18-JUL-04 8000 11666.6667
100 Russell 01-OCT-04 14000 11833.3333
100 Partners 05-JAN-05 13500 13166.6667
100 Errazuriz 10-MAR-05 12000 11233.3333
. . .
|
Variable
|
Description
|
|
host_name
|
is the name of the machine on which the gateway is
installed.
|
|
port_number
|
specifies the port number used by the Oracle Net Listener.
If you have other listeners running on the same machine, then the value of port_number
must be different from the other listeners' port numbers.
|
|
Variable
|
Description
|
|
gateway_sid
|
specifies the SID of the gateway and matches the gateway
SID specified in the connect descriptor entry in the tnsnames.ora
file.
|
|
oracle_home_directory
|
specifies the Oracle home directory where the gateway
resides.
|
|
dg4odbc
|
specifies the executable name of the Oracle Database
Gateway for ODBC.
|
|
Variable
|
Description
|
|
connect_descriptor
|
is the description of the object to connect to as
specified when creating the database link, such as dg4odbc.
Check the sqlnet.ora file for the following parameter setting:
names.directory_path
= (TNSNAMES)
Note: The sqlnet.ora file is typically stored in ORACLE_HOME\network\admin.
|
|
TCP
|
is the TCP protocol used for TCP/IP connections.
|
|
host_name
|
specifies the machine where the gateway is running.
|
|
port_number
|
matches the port number used by the Oracle Net Listener
that is listening for the gateway. The Oracle Net Listener's port number can
be found in the listener.ora file used by the Oracle Net Listener..
|
|
gateway_sid
|
specifies the SID of the gateway and matches the SID
specified in the listener.ora file of the Oracle Net Listener that is listening for the
gateway. See
|
|
(HS=OK)
|
specifies that this connect descriptor connects to a
non-Oracle system.
|
|
Variable
|
Description
|
|
dblink
|
is the complete database link name.
|
|
tns_name_entry
|
specifies the Oracle Net connect descriptor specified in
the tnsnames.ora file that identifies the gateway
|