首页 > Oracle, PL/SQL > Oracle SQL 基础培训

Oracle SQL 基础培训

2019年1月1日 发表评论 阅读评论

1.SQL基础

1.1 基础语法

  • create table
  1. 普通表
 create table ANGU_TEST1
(
  id NUMBER
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
  1. 临时表
 --基于回话
create global temporary table ANGU_TEMP
(
  id NUMBER
)
on commit preserve rows;

--基于事务
create global temporary table ANGU_TEMP_1
(
  id NUMBER
)
on commit delete rows;
  • select
SELECT last_name, department_name
   FROM employees@remote,
        departments
   WHERE
   employees.department_id =   departments.department_id;
--@dblink
  • group by
--聚合函数
SELECT department_id, MIN(salary), MAX (salary)
     FROM employees
     GROUP BY department_id
   ORDER BY department_id;

SELECT department_id, MIN(salary), MAX (salary)
   FROM employees
   GROUP BY department_id
   HAVING MIN(salary) < 5000
   ORDER BY department_id;
  • order by
SELECT t.*  FROM angu_test1 t ORDER BY ID;
SELECT t.*  FROM angu_test1 t ORDER BY 1;
SELECT t.*  FROM angu_test1 t ORDER BY 1 DESC;
SELECT t.*  FROM angu_test1 t ORDER BY 1  NULLS FIRST;
SELECT t.*  FROM angu_test1 t ORDER BY 1  NULLS LAST;
  • update
UPDATE employees SET
    job_id = 'SA_MAN', salary = salary + 1000, department_id = 120
    WHERE first_name||' '||last_name = 'Douglas Grant';

UPDATE employees a
    SET department_id =
        (SELECT department_id
            FROM departments
            WHERE location_id = '2100'),
        (salary, commission_pct) =
        (SELECT 1.1*AVG(salary), 1.5*AVG(commission_pct)
          FROM employees b
          WHERE a.department_id = b.department_id)
    WHERE department_id IN
        (SELECT department_id
          FROM departments
          WHERE location_id = 2900
              OR location_id = 2700);
  • delete
DELETE FROM product_descriptions
   WHERE language_id = 'AR';
  • insert
INSERT INTO departments
   VALUES (280, 'Recreation', DEFAULT, 1700);

INSERT INTO bonuses
   SELECT employee_id, salary*1.1
   FROM employees
   WHERE commission_pct > 0.25;
  • merge
MERGE INTO bonuses D
   USING (SELECT employee_id, salary, department_id FROM employees
   WHERE department_id = 80) S
   ON (D.employee_id = S.employee_id)
   WHEN MATCHED THEN UPDATE SET D.bonus = D.bonus + S.salary*.01
   WHEN NOT MATCHED THEN INSERT (D.employee_id, D.bonus)
     VALUES (S.employee_id, S.salary*.01);

1.2 常用处理

  • 对number的理解
Actual Data Specified As Stored As
123.89 NUMBER 123.89
123.89 NUMBER(3) 124
123.89 NUMBER(3,2) exceeds precision
123.89 NUMBER(4,2) exceeds precision
123.89 NUMBER(5,2) 123.89
123.89 NUMBER(6,1) 123.9
123.89 NUMBER(6,-2) 100
0.01234 NUMBER(4,5) 0.01234
0.00012 NUMBER(4,5) 0.00012
0.000127 NUMBER(4,5) 0.00013
0.0000012 NUMBER(2,7) 0.0000012
0.00000123 NUMBER(2,7) 0.0000012
  • 日期的处理
    1. 系统日
      select sysdate from daul;
    1. 格式化日期 
      select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as nowTime from dual;
select to_char(sysdate,'yyyy') as nowYear   from dual;
select to_char(sysdate,'mm')    as nowMonth from dual;
select to_char(sysdate,'dd')    as nowDay    from dual;
select to_char(sysdate,'hh24') as nowHour   from dual;
select to_char(sysdate,'mi')    as nowMinute from dual;
select to_char(sysdate,'ss')    as nowSecond from dual;
    2. 字符串转日期格式

      SELECT TO_DATE('2009/03/02', 'YYYY/MM/DD')  FROM DUAL;

    1. 日期计算

      SELECT SYSDATE + 1,SYSDATE FROM dual;
SELECT SYSDATE - 1,SYSDATE FROM dual;

SELECT to_date('20180703','yyyymmdd') -
to_date('20180701','yyyymmdd') FROM dual;
    2. 常用日期函数 
      SELECT add_months(SYSDATE,1),SYSDATE FROM dual;
SELECT add_months(SYSDATE,-1),SYSDATE FROM dual;

SELECT add_months(SYSDATE,12),SYSDATE FROM dual;
SELECT add_months(SYSDATE,-12),SYSDATE FROM dual;

SELECT SYSDATE, LAST_DAY(SYSDATE) "Last" FROM dual;
SELECT NEXT_DAY(SYSDATE,'星期一') "NEXT DAY" FROM DUAL;

SELECT MONTHS_BETWEEN (SYSDATE + 31,SYSDATE) "Months" FROM DUAL;

SELECT TRUNC(SYSDATE,'yyyy') FROM dual;
SELECT TRUNC(SYSDATE,'mm') FROM dual;
SELECT TRUNC(SYSDATE,'dd') FROM dual;
  • 字符串处理 
    SELECT LOWER('ABC') FROM dual;
SELECT UPPER('abc') FROM dual;
SELECT INSTR('CORPORATE FLOOR','OR', 2, 2) "Instring" FROM DUAL;
SELECT Substr('CORPORATE FLOOR',3,3) FROM dual;
SELECT LENGTH('abc') FROM dual;
  • DUAL虚拟表 
    SELECT * FROM dual;
select 1 + 1 from dual;

1.3 常用函数

  • max
SELECT MAX(salary) "Maximum"  FROM employees;
  • min
SELECT MIN(salary) "Maximum"  FROM employees;
  • avg
--Null 值
SELECT AVG(salary) "Maximum"  FROM employees;
  • count
SELECT COUNT(*) FROM employees t;
  • table
CREATE OR REPLACE TYPE strings_t IS TABLE OF VARCHAR2 (100);
CREATE OR REPLACE FUNCTION random_strings (
   count_in IN INTEGER)
   RETURN strings_t
IS
   l_strings   strings_t := strings_t ();
BEGIN
   l_strings.EXTEND (count_in);

   FOR indx IN 1 .. count_in
   LOOP
      l_strings (indx) := DBMS_RANDOM.string ('u', 10);
   END LOOP;

   RETURN l_strings;
END;
/
SELECT * FROM TABLE(random_strings(5));

1.4 分析函数

SELECT manager_id, last_name, salary,
       MAX(salary) OVER (PARTITION BY manager_id) AS mgr_max
  FROM employees;


 SELECT
    department_id, first_name, last_name, salary,
    ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary desc) rn
  FROM employees

  SELECT hire_date, last_name, salary,
       LAG(salary, 1, 0) OVER (ORDER BY hire_date) AS prev_sal
  FROM employees
  WHERE job_id = 'PU_CLERK'
  ORDER BY hire_date;

SELECT LISTAGG(last_name, '; ')
         WITHIN GROUP (ORDER BY hire_date, last_name) "Emp_list",
       MIN(hire_date) "Earliest"
  FROM employees
  WHERE department_id = 30;

1.5 中级语法

  • 如何理解rownum

For each row returned by a query, the ROWNUM pseudocolumn returns a number indicating the order in which Oracle selects the row from a table or set of joined rows. The first row selected has a ROWNUM of 1, the second has 2, and so on. 

SELECT *
  FROM employees
  WHERE ROWNUM < 11;

SELECT *
  FROM (SELECT * FROM employees ORDER BY employee_id)
  WHERE ROWNUM < 11;

SELECT *
  FROM employees
  WHERE ROWNUM > 1;
  • 如何理解null
Condition Value of A Evaluation
a IS NULL 10 FALSE
a IS NOT NULL 10 TRUE
a IS NULL NULL TRUE
a IS NOT NULL NULL FALSE
a = NULL 10 UNKNOWN
a != NULL 10 UNKNOWN
a = NULL NULL UNKNOWN
a != NULL NULL UNKNOWN
a = 10 NULL UNKNOWN
a != 10 NULL UNKNOWN

null 常用函数

  SELECT NVL(NULL,'abc') FROM dual;
  SELECT NVL('a','abc') FROM dual;
  • 如何理解connect by 语法及其简单应用
SELECT t.id,t.name,
LEVEL
/*CONNECT_BY_ROOT NAME*/
/*SYS_CONNECT_BY_PATH(NAME, ';')*/
 FROM t_distinct t
START WITH ID = 1
CONNECT BY PRIOR ID = pid;

SELECT t.id,t.name,LEVEL
/*CONNECT_BY_ROOT NAME*/
/*SYS_CONNECT_BY_PATH(NAME, ';')*/
FROM t_distinct t
START WITH ID = 12
CONNECT BY PRIOR pid = ID;

1.6 Oracle常用的视图

SELECT * FROM all_tables t;
SELECT * FROM all_views t;
SELECT * FROM all_tab_cols;
SELECT * FROM all_indexes t;
SELECT * FROM All_Dependencies t;
SELECT * FROM user_source t;

1.7 如何看Oracle的语法图

2. PL/SQL语法基础

2.1 变量

命名规范
1. 以字母开头
2. 可以包含$,#,_
3. 非保留字

--保留字数据字典
SELECT * FROM v$RESERVED_WORDS;
--定义变量
DECLARE
  part_number       NUMBER(6);     -- SQL data type
  part_name         VARCHAR2(20);  -- SQL data type
  in_stock          BOOLEAN;       -- PL/SQL-only data type
  part_price        NUMBER(6,2);   -- SQL data type
  part_description  VARCHAR2(50);  -- SQL data type
  credit_limit     CONSTANT REAL    := 5000.00;  -- SQL data type
  max_days_in_year CONSTANT INTEGER := 366;      -- SQL data type
  urban_legend     CONSTANT BOOLEAN := FALSE;    -- PL/SQL-only data type
  surname  employees.last_name%TYPE;--推荐
BEGIN
  NULL;
END;
/

2.2 分支与循环

  1. 分支
      IF condition THEN
        statements
      END IF;
      -----------------------------------------------
      IF condition THEN
        statements
      ELSE
        else_statements
      END IF;
      -------------------------------------------
      IF  condition_1 THEN
        statements_1
      ELSIF condition_2 THEN
        statements_2
      ELSE
          else_statements
      END IF;
  1. 基本循环
        DECLARE
          x NUMBER := 0;
        BEGIN
          LOOP
            DBMS_OUTPUT.PUT_LINE ('Inside loop:  x = ' || TO_CHAR(x));
            x := x + 1;
            IF x > 3 THEN
              EXIT;
            END IF;
          END LOOP;
          -- After EXIT, control resumes here
          DBMS_OUTPUT.PUT_LINE(' After loop:  x = ' || TO_CHAR(x));
        END;
        /
        -------------------------------------------------
        DECLARE
          x NUMBER := 0;
        BEGIN
          LOOP
            DBMS_OUTPUT.PUT_LINE('Inside loop:  x = ' || TO_CHAR(x));
            x := x + 1;  -- prevents infinite loop
            EXIT WHEN x > 3;
          END LOOP;
          -- After EXIT statement, control resumes here
          DBMS_OUTPUT.PUT_LINE('After loop:  x = ' || TO_CHAR(x));
        END;
        /
        -----------------------------------------------------
        DECLARE
          x NUMBER := 0;
        BEGIN
          LOOP -- After CONTINUE statement, control resumes here
            DBMS_OUTPUT.PUT_LINE ('Inside loop:  x = ' || TO_CHAR(x));
            x := x + 1;
            IF x < 3 THEN
              CONTINUE;
            END IF;
            DBMS_OUTPUT.PUT_LINE
              ('Inside loop, after CONTINUE:  x = ' || TO_CHAR(x));
            EXIT WHEN x = 5;
          END LOOP;

          DBMS_OUTPUT.PUT_LINE (' After loop:  x = ' || TO_CHAR(x));
        END;
        /
        -----------------------------------------------------------------
        DECLARE
          x NUMBER := 0;
        BEGIN
          LOOP -- After CONTINUE statement, control resumes here
            DBMS_OUTPUT.PUT_LINE ('Inside loop:  x = ' || TO_CHAR(x));
            x := x + 1;
            CONTINUE WHEN x < 3;
            DBMS_OUTPUT.PUT_LINE
              ('Inside loop, after CONTINUE:  x = ' || TO_CHAR(x));
            EXIT WHEN x = 5;
          END LOOP;
          DBMS_OUTPUT.PUT_LINE (' After loop:  x = ' || TO_CHAR(x));
        END;
        /
  1. for loop 循环
    BEGIN
      DBMS_OUTPUT.PUT_LINE ('lower_bound < upper_bound');

      FOR i IN 1..3 LOOP
        DBMS_OUTPUT.PUT_LINE (i);
      END LOOP;

      DBMS_OUTPUT.PUT_LINE ('lower_bound = upper_bound');

      FOR i IN 2..2 LOOP
        DBMS_OUTPUT.PUT_LINE (i);
      END LOOP;

      DBMS_OUTPUT.PUT_LINE ('lower_bound > upper_bound');

      FOR i IN 3..1 LOOP
        DBMS_OUTPUT.PUT_LINE (i);
      END LOOP;
    END;
    /

2.3 游标的使用

A cursor is a pointer to a private SQL area that stores information about processing a specific SELECT or DML statement. 

DECLARE
  CURSOR c1 IS
    SELECT t1.department_id, department_name, staff
    FROM departments t1,
         ( SELECT department_id, COUNT(*) AS staff
           FROM employees
           GROUP BY department_id
         ) t2
    WHERE (t1.department_id = t2.department_id) AND staff >= 5
    ORDER BY staff;

BEGIN
   FOR dept IN c1
   LOOP
     DBMS_OUTPUT.PUT_LINE ('Department = '
       || dept.department_name || ', staff = ' || dept.staff);
   END LOOP;
END;
/

2.4 函数

CREATE FUNCTION get_bal(acc_no IN NUMBER)
   RETURN NUMBER
   IS
   acc_bal NUMBER(11,2);
   BEGIN
      SELECT order_total
      INTO acc_bal
      FROM orders
      WHERE customer_id = acc_no;
      RETURN(acc_bal);
    END;
/

2.5 存储过程

2.5.1 语法

CREATE PROCEDURE remove_emp
(employee_id NUMBER)
AS
tot_emps NUMBER;--定义变量
   BEGIN
   --业务逻辑
      DELETE FROM employees
      WHERE employees.employee_id = remove_emp.employee_id;
   tot_emps := tot_emps - 1;
 END;

2.5.2 参数

  • IN
  • OUT
  • INT OUT
--OUT 参数默认会被置空
CREATE OR REPLACE
PROCEDURE P_PARAMETER(A VARCHAR2,
                    B IN VARCHAR2,
                    C OUT VARCHAR2,
                  D IN OUT VARCHAR2) IS
BEGIN
  dbms_output.put_line('a:'||a);
  dbms_output.put_line('b:'||b);
  dbms_output.put_line('c:'||c);
  dbms_output.put_line('d:'||d);
  c := 'return_c';
  d := 'return_d';
END P_PARAMETER;

2.5.3 默认惨

  • DEFAULT
create or replace procedure
P_DEFAULT(
a VARCHAR2 DEFAULT 'default value'
) is
begin
  dbms_output.put_line('a:'||a);
end P_DEFAULT;

2.5.4 参数传递方式

  • 位置参数
  • 命名参数
  • 混合参数
DECLARE
  emp_num NUMBER(6) := 120;
  bonus   NUMBER(6) := 50;

  PROCEDURE raise_salary (
    emp_id NUMBER,
    amount NUMBER
  ) IS
  BEGIN
    UPDATE employees
    SET salary = salary + amount
    WHERE employee_id = emp_id;
  END raise_salary;

BEGIN
  -- Equivalent invocations:

  raise_salary(emp_num, bonus);                      -- positional notation
  raise_salary(amount => bonus, emp_id => emp_num);  -- named notation
  raise_salary(emp_id => emp_num, amount => bonus);  -- named notation
  raise_salary(emp_num, amount => bonus);            -- mixed notation
END;
/

2.6 包

2.6.1 包声明

CREATE OR REPLACE PACKAGE trans_data  AS
  TYPE TimeRec IS RECORD (
    minutes SMALLINT,
    hours   SMALLINT);
  TYPE TransRec IS RECORD (
    category VARCHAR2(10),
    account  INT,
    amount   REAL,
    time_of  TimeRec);
  minimum_balance     CONSTANT REAL := 10.00;
  number_processed    INT;
  insufficient_funds  EXCEPTION;
  PRAGMA EXCEPTION_INIT(insufficient_funds, -4097);
END trans_data;
/

2.6.2 包体

包声明中函数存储过程必须在包体中实现
包声明中不存在的也可以在包体中，属于私有程序 

CREATE PACKAGE emp_bonus AS
  PROCEDURE calc_bonus (date_hired employees.hire_date%TYPE);
END emp_bonus;
/
CREATE PACKAGE BODY emp_bonus AS
  -- DATE does not match employees.hire_date%TYPE
  PROCEDURE calc_bonus (date_hired DATE) IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE
      ('Employees hired on ' || date_hired || ' get bonus.');
  END;
END emp_bonus;
/

2.7 集合类型

2.7.1 集合类型

  • Associative array (or index-by table)
  • VARRAY (variable-size array)
  • Nested table

2.7.2 Associative array

DECLARE
  TYPE population IS TABLE OF NUMBER  -- Associative array type
    INDEX BY VARCHAR2(64);            --  indexed by string
  city_population  population;        -- Associative array variable
  i  VARCHAR2(64);                    -- Scalar variable

BEGIN
  city_population('Smallville')  := 2000;
  city_population('Midland')     := 750000;
  city_population('Megalopolis') := 1000000;
  i := city_population.FIRST;  -- Get first element of array
  WHILE i IS NOT NULL LOOP
    DBMS_Output.PUT_LINE
      ('Population of ' || i || ' is ' || city_population(i));
    i := city_population.NEXT(i);  -- Get next element of array
  END LOOP;
END;

2.7.3 VARRAY

DECLARE
  TYPE Foursome IS VARRAY(4) OF VARCHAR2(15);
   team Foursome := Foursome('John', 'Mary', 'Alberto', 'Juanita');
  -- team Foursome := Foursome();
  PROCEDURE print_team (heading VARCHAR2) IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE(heading);
    FOR i IN 1..4 LOOP
      DBMS_OUTPUT.PUT_LINE(i || '.' || team(i));
    END LOOP;

    DBMS_OUTPUT.PUT_LINE('---');
  END;

BEGIN
   /* team.extend();
    team(1) := 'aa';
    team.extend();
    team(2) := 'aa';
    team.extend();
    team(3) := 'aa';
    team.extend();
    team(4) := 'aa';*/

  print_team('2001 Team:');
  team(3) := 'Pierre';
  team(4) := 'Yvonne';
  print_team('2005 Team:');
  team := Foursome('Arun', 'Amitha', 'Allan', 'Mae');
  print_team('2009 Team:');
END;

2.8.3 Nested table

DECLARE
  TYPE Roster IS TABLE OF VARCHAR2(15);

  names Roster := Roster('D Caruso', 'J Hamil', 'D Piro', 'R Singh');
  --  names Roster := Roster();
  PROCEDURE print_names (heading VARCHAR2) IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE(heading);

    FOR i IN names.FIRST .. names.LAST LOOP
      DBMS_OUTPUT.PUT_LINE(names(i));
    END LOOP;

    DBMS_OUTPUT.PUT_LINE('---');
  END;

BEGIN
 /* names.extend();
  names(1) :='aaa';
  names.extend();
  names(2) :='bbb';
  names.extend();
  names(3) :='ccc';
  names.extend();
  names(4) :='ddd';*/
  print_names('Initial Values:');

  names(3) := 'P Perez';
  print_names('Current Values:');

  names := Roster('A Jansen', 'B Gupta');
  print_names('Current Values:');
END;

2.8 异常处理

2.8.1 基本语法

declare
begin
  NULL;
EXCEPTION
  WHEN ex_name_1 THEN statements_1                 -- Exception handler
  WHEN ex_name_2 OR ex_name_3 THEN statements_2  -- Exception handler
  WHEN  NO_DATA_FOUND  THEN statements_3
  WHEN OTHERS THEN statements_4                      -- Exception handler
END;

2.8.2 获取异常的基本信息

  • SQLCODE
  • Sqlerrm
declare
 v_result NUMBER;
begin
 v_result := 1/0;
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line(Sqlcode);
    dbms_output.put_line(sqlerrm);
END;

2.8.3 获取异常的详细信息

declare
 v_result NUMBER;
begin
 v_result := 1/0;
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line(DBMS_UTILITY.format_error_backtrace);
END;
---------------------------------
declare
 v_result NUMBER;
begin
  P_EXCEPTION;
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line(DBMS_UTILITY.format_error_backtrace);
END;

2.8.4 如何调试PL/SQL

ALTER PROCEDURE p_default COMPILE DEBUG;
ALTER PROCEDURE p_default COMPILE;
演示:查看变量和在Debug窗口执行SQL语句

2.9 事务的ACID属性

  • Atomicity

All tasks of a transaction are performed or none of them are. There are no partial transactions. For example, if a transaction starts updating 100 rows, but the system fails after 20 updates, then the database rolls back the changes to these 20 rows.

  • Consistency

The transaction takes the database from one consistent state to another consistent state. For example, in a banking transaction that debits a savings account and credits a checking account, a failure must not cause the database to credit only one account, which would lead to inconsistent data.

  • Isolation

he effect of a transaction is not visible to other transactions until the transaction is committed. For example, one user updating the hr.employees table does not see the uncommitted changes to employees made concurrently by another user. Thus, it appears to users as if transactions are executing serially.

  • Durability

Changes made by committed transactions are permanent. After a transaction completes, the database ensures through its recovery mechanisms that changes from the transaction are not lost.

分类: Oracle, PL/SQL 标签:
  1. 本文目前尚无任何评论.
  1. 本文目前尚无任何 trackbacks 和 pingbacks.