CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle Select Statements
Version 11.1
 
Basic Select Statements
Select All Columns and All Records in a Single Table or View SELECT *
FROM <table_name>;
SELECT *
FROM all_tables;
Select Named Columns SELECT <column_name, column_name, ..., <column_name>
FROM <table_name>;
SELECT table_name, tablespace_name, num_rows
FROM all_tables;

Create Table As  (CTAS)

Note: Redo only created when in ARCHIVE LOG mode
CREATE TABLE <table_name> AS
SELECT <column_name, column_name, ..., <column_name>
FROM <table_name>;
CREATE TABLE t AS
SELECT *
FROM all_tables;

SELECT * FROM t;

SELECTs can go anywhere
SELECT DECODE((SELECT 'x' FROM DUAL), (SELECT 'x' FROM DUAL), (SELECT 'y' FROM DUAL)) AS  RESULT
FROM (SELECT 'm' FROM DUAL)
WHERE (SELECT 1 FROM DUAL) = (SELECT 1 FROM DUAL)
AND (SELECT 2 FROM DUAL) BETWEEN (SELECT 1 FROM DUAL) AND (SELECT 3 FROM DUAL)
AND NVL((SELECT NULL FROM DUAL ), (SELECT 'z' FROM DUAL)) = (SELECT 'z' FROM DUAL)
ORDER BY (SELECT 1 FROM DUAL);
 
Select Statement With SAMPLE Clause

Sample Clause Returning 1% Of Records
SELECT *
FROM <table_name>
SAMPLE (percentage_of_rows);
CREATE TABLE t AS
SELECT object_name
FROM all_objects
WHERE SUBSTR(object_name,1,1) BETWEEN 'A' AND 'W';

SELECT COUNT(*)
FROM t;

SELECT COUNT(*) * 0.1
FROM t;

SELECT *
FROM t
SAMPLE(1);

SELECT *
FROM t
SAMPLE(1);

SELECT *
FROM t
SAMPLE(1);
 
Select Statement With WHERE Clause

Sample Clause Returning 35% Of Records After Filtering With A WHERE Clause
SELECT *
FROM <table_name>
SAMPLE (3.5)
WHERE ....
SELECT COUNT(*)
FROM t
WHERE object_name LIKE '%J%';

SELECT COUNT(*) * 0.35
FROM t
WHERE object_name LIKE '%J%';

SELECT *
FROM t
SAMPLE(35)
WHERE object_name LIKE '%J%';

SELECT *
FROM t
SAMPLE(35)
WHERE object_name LIKE '%J%';

SELECT *
FROM t
SAMPLE(35)
WHERE object_name LIKE '%J%';
 
Select Statement With GROUP BY Clause

Select with Group By Clause
SELECT <column_name>, <aggregating_operation>
FROM <table_name>
GROUP BY <column_name>;
SELECT object_type, COUNT(*)
FROM all_objects
WHERE SUBSTR(object_name,1,1) BETWEEN 'A' AND 'W'
GROUP BY object_type;
 
Select Statement With HAVING Clause

Select With Having Clause
SELECT <column_name>, <aggregating_operation>
FROM <table_name>
GROUP BY <column_name>
HAVING <aggregating_op_result> <condition> <value>;
SELECT object_type, COUNT(*)
FROM all_objects
WHERE SUBSTR(object_name,1,1) BETWEEN 'A' AND 'W'
GROUP BY object_type
HAVING COUNT(*) < 6;

SELECT object_type, COUNT(*)
FROM all_objects
WHERE SUBSTR(object_name,1,1) BETWEEN 'A' AND 'W'
GROUP BY object_type
HAVING COUNT(*) > 5;
 
Scalar Select
Select In Select Clause SELECT (
  SELECT <single_value
  FROM <table_name>
FROM <table_name>;
SELECT (SELECT 1 FROM DUAL) FROM DUAL;
 
Select Unique Values
Distinct SELECT DISTINCT <column_name_list>
FROM <table_name>;
SELECT DISTINCT object_type
FROM all_objects
WHERE SUBSTR(object_name,1,1) BETWEEN 'A' AND 'W';
Unique SELECT UNIQUE <column_name_list>
FROM <table_name>;
SELECT UNIQUE object_type
FROM all_objects
WHERE SUBSTR(object_name,1,1) BETWEEN 'A' AND 'W';
 
Select Statement Using Functions

Date Function Example
SELECT <date_function(<column_name>))
FROM <table_name>;
desc all_objects

SELECT object_name, TO_DATE(timestamp, 'YYYY-MM-DD:HH24:MI:SS')
FROM all_objects
WHERE ROWNUM < 11;

Numeric Function Example
SELECT <numeric_function(<column_name>))
FROM <table_name>;
desc user_extents

SELECT SUM(bytes)/1024/1024 USED_MB
FROM user_extents;

SELECT segment_type, SUM(bytes)/1024/1024 USED_MB
FROM user_extents
GROUP BY segment_type;

String Function Example
SELECT <string_function(<column_name>))
FROM <table_name>;
desc all_objects

SELECT object_name, LOWER(object_name) LOWER_ONAME
FROM all_objects
WHERE ROWNUM < 11;
 
Select For Update

Lock Record(s)
SELECT <column_name_list)
FROM <table_name_list>
FOR UPDATE;
CREATE TABLE parents (
pid  NUMBER(10),
cash NUMBER(10,2));

CREATE TABLE children
(cid NUMBER(10),
fid  NUMBER(10),
fin_level VARCHAR2(35));


DECLARE
 CURSOR x_cur IS
 SELECT pid
 FROM parents;

 x_rec x_cur%ROWTYPE;

 x NUMBER(10,2) := 18000.64;
 y NUMBER(10,2) := 100;
BEGIN
  DELETE FROM parents;
  DELETE FROM children;

  FOR i IN 1..25
  LOOP
    INSERT INTO parents
    VALUES (y, x);

    x := x+1235.31;
    y := y-1;
  END LOOP;

  y := 0;

  OPEN x_cur;
  LOOP
    FETCH x_cur INTO x_rec;
    EXIT WHEN x_cur%NOTFOUND;

    y := y+1;

    INSERT INTO children (cid, fid)
    VALUES (y, x_rec.pid);

    y := y+1;

    INSERT INTO children (cid, fid)
    VALUES (y, x_rec.pid);
  END LOOP;
  CLOSE x_cur;
  COMMIT;
END;
/


CREATE OR REPLACE PROCEDURE cursor_loop3 IS
 CURSOR x_cur IS
 SELECT pid, cash
 FROM parents
  WHERE cash < 35000
 FOR UPDATE
;
BEGIN
   FOR x_rec IN x_cur
   LOOP
      UPDATE parents
      SET cash = FLOOR(cash)

      WHERE CURRENT OF x_cur;
   END LOOP;
   COMMIT;
END cursor_loop
3;
/
FOR UPDATE with NOWAIT See Deadlocks Demo page
FOR UPDATE with WAIT See Deadlocks Demo page
FOR UPDATE with SKIP LOCKED See Deadlocks Demo page
 
Partition Select

Select From Named Partition
SELECT DISTINCT <column_name_list>
FROM <table_name> PARTITION (<partition_name>);
CREATE TABLE pt (
deptno NUMBER(10),
state  VARCHAR2(2))
PARTITION BY LIST (state) (
PARTITION nw VALUES ('OR', 'WA'),
PARTITION sw VALUES ('AZ', 'CA', 'NM'));

INSERT INTO pt VALUES (1, 'WA');
INSERT INTO pt VALUES (1, 'OR');
INSERT INTO pt VALUES (1, 'CA');

SELECT COUNT(*) FROM pt;

SELECT COUNT(*) FROM pt PARTITION(nw);
SELECT COUNT(*) FROM pt PARTITION(sw);
 
CASE Insensitive Select

Select From Named Partition
SELECT DISTINCT <column_name_list>
FROM <table_name> PARTITION (<partition_name>);
conn / as sysdba

GRANT select ON v_$nls_parameters TO uwclass;

SELECT * FROM sys.v_$nls_parameters WHERE parameter LIKE '%SORT%';

conn uwclass/uwclass

CREATE TABLE cis_test (
col1 VARCHAR2(10));

INSERT INTO cis_test VALUES ('one');
INSERT INTO cis_test VALUES ('TWO');
INSERT INTO cis_test VALUES ('thRee');
INSERT INTO cis_test VALUES ('FouR');
INSERT INTO cis_test VALUES ('fiVE');

SELECT * FROM cis_test;

SELECT col1 FROM cis_test ORDER BY 1;

ALTER SESSION SET nls_sort=binary_ci;

SELECT * FROM sys.v_$nls_parameters WHERE parameter LIKE '%SORT%';

SELECT col1 FROM cis_test ORDER BY 1;
 
PL/SQL Select Into

Selecting In PL/SQL Objects
SELECT <clause>
INTO <clause>
FROM <clause>
WHERE <clause>
CREATE TABLE t (
testcol NUMBER(3));

CREATE SEQUENCE seq;

SELECT seq.NEXTVAL FROM DUAL;

/

/

INSERT INTO t
(testcol)
VALUES
(seq.NEXTVAL);

/

/

SELECT * FROM t;

BEGIN
  SELECT seq.NEXTVAL FROM DUAL;
END;
/

set serveroutput on

DECLARE
 x INTEGER;
BEGIN
  SELECT seq.NEXTVAL
  INTO x
  FROM DUAL;

  dbms_output.put_line(x);
END;
/

/

/
 
Related Topics
Analytic Functions
Conditions
Conversion Functions
Date Functions
Deadlocks
Delete
Group By & Having Clause
Hints
Insert
Joins
Nested Tables
Numeric Functions
Order By
Partitions
Pivot
Pseudocolumns
String Functions
Types
Unpivot
Update
Where
Wildcards
With Clause
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [59 users online]    © 2010 psoug.org