ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle Select Statements

Oracle Select Statements

原创 Linux操作系统 作者:zjh_strike 时间:2008-09-03 17:55:56 0 删除 编辑
Version 11.1
 
Basic Select Statements
Select All Columns and All Records in a Single Table or View SELECT *
FROM ;
SELECT *
FROM all_tables;
Select Named Columns SELECT
FROM ;
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 AS
SELECT
FROM ;
CREATE TABLE t AS
SELECT *
FROM all_tables;
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
SAMPLE (1);
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
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 ,
FROM
GROUP BY ;
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 ,
FROM
GROUP BY
HAVING ;
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   FROM
FROM ;
SELECT (SELECT 1 FROM dual) FROM dual;
 
Select Unique Values
Distinct SELECT DISTINCT
FROM ;
SELECT DISTINCT object_type
FROM all_objects
WHERE SUBSTR(object_name,1,1) BETWEEN 'A' AND 'W';
Unique SELECT UNIQUE
FROM ;
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 ))
FROM ;
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 ))
FROM ;
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 ))
FROM ;
desc all_objects

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

Lock Record(s)
SELECT FROM
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
FROM PARTITION ();
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);
 
PL/SQL Select Into

Selecting In PL/SQL Objects
SELECT
INTO
FROM
WHERE
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;
/

/

/

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/8775919/viewspace-441072/,如需转载,请注明出处,否则将追究法律责任。

下一篇: Oracle Views
请登录后发表评论 登录
全部评论

注册时间:2008-07-22

  • 博文量
    13
  • 访问量
    14190