Oracle

Oracle 数据库

文档链接：

基础DML

给某一列添加限制：

ALTER TABLE PERFORMANCE_PARTS
MODIFY WEIGHT NOT NULL;

SQL标准

[集合运算符](The Set Operators (oracle.com))

UNION：合并查询结果，去除重复结果、

UNION ALL：合并查询结果，不去除重复结果、

INTERSECT：只取查询结果的交集、

MINUS：只在第一个查询结果，不在第二个结果、

EXCEPT：和MINUS一样

PL/SQL

块

在PL/SQL中，一个“块”是一个基本单元，由DECLARE, BEGIN, EXCEPTION, END定义。如：

<< label >> (optional)
DECLARE    -- Declarative part (optional)
  -- Declarations of local types, variables, & subprograms

BEGIN      -- Executable part (required)
  -- Statements (which can use items declared in declarative part)

[EXCEPTION -- Exception-handling part (optional)
  -- Exception handlers for exceptions (errors) raised in executable part]
END;

子程序

一个子程序就是可重复执行的语句块。

除此之外，PL/SQL还可以调用其他语言写的外部子程序。

赋值

DECLARE
  valid_id       BOOLEAN;
BEGIN 
  valid_id := TRUE;
END;

SELECT INTO

DECLARE
  bonus   NUMBER(8,2);
BEGIN
  SELECT salary * 0.10 INTO bonus
  FROM employees
  WHERE employee_id = 100;
END;

DBMS_OUTPUT.PUT_LINE('bonus = ' || TO_CHAR(bonus));
/

Row Locks

A transaction acquires a row lock for each row modified by one of the following statements: INSERT, UPDATE, DELETE, MERGE, and SELECT ... FOR UPDATE. The row lock exists until the transaction commits or rolls back.

Table Locks

A transaction automatically acquires a table lock (TM lock) when a table is modified with the following statements: INSERT, UPDATE, DELETE, MERGE, and SELECT ... FOR UPDATE. These DML operations require table locks to reserve DML access to the table on behalf of a transaction and to prevent DDL operations that would conflict with the transaction. You can explicitly obtain a table lock using the LOCK TABLE statement, as described in "Manual Data Locking".

SQL Statement	Row Locks	Table Lock Mode	RS	RX	S	SRX	X
`SELECT` ... `FROM` `table``...`	—	none	Y	Y	Y	Y	Y
`INSERT` `INTO` `table` ...	Yes	SX	Y	Y	N	N	N
`UPDATE` `table` ...	Yes	SX	YFoot 1	YFoot 1	N	N	N
`MERGE` `INTO` `table` ...	Yes	SX	Y	Y	N	N	N
`DELETE` `FROM` `table` ...	Yes	SX	YFoot 1	YFoot 1	N	N	N
`SELECT` ... `FROM` `table` `FOR` `UPDATE` `OF` ...	Yes	SX	YFoot 1	YFoot 1	N	N	N
`LOCK` `TABLE` `table` `IN` ...	—
`ROW` `SHARE` `MODE`		SS	Y	Y	Y	Y	N
`ROW` `EXCLUSIVE` `MODE`		SX	Y	Y	N	N	N
`SHARE` `MODE`		S	Y	N	Y	N	N
`SHARE` `ROW` `EXCLUSIVE` `MODE`		SSX	Y	N	N	N	N
`EXCLUSIVE` `MODE`		X	N	N	N	N	N

查看锁表进程

select * from v$session t1, v$locked_object t2 where t1.sid = t2.SESSION_ID;

MERGE INTO

MERGE INTO schema. table alias
USING { schema. table | views | query} alias
ON {(condition) }
WHEN MATCHED THEN
  UPDATE SET {clause}
WHEN NOT MATCHED THEN
  INSERT VALUES {clause};

INTO 子句用于指定你所update或者Insert目的表。 USING 子句用于指定你要update或者Insert的记录的来源，它可能是一个表，视图，子查询。 ON Clause 用于目的表和源表（视图，子查询）的关联，如果匹配（或存在），则更新，否则插入。 merge_update_clause 用于写update语句 merge_insert_clause 用于写insert语句

WHILE...LOOP...END LOOP

DECLARE
  done    BOOLEAN;              -- Initial value is NULL by default
  counter NUMBER := 0;
BEGIN
  done := FALSE;                -- Assign literal value
  WHILE done != TRUE            -- Compare to literal value
    LOOP
      counter := counter + 1;
      done := (counter > 500);  -- Assign value of BOOLEAN expression
    END LOOP;
END;
/

字符串连接符 ||

DECLARE
  x VARCHAR2(4) := 'suit';
  y VARCHAR2(4) := 'case';
BEGIN
  DBMS_OUTPUT.PUT_LINE (x || y);
END;
/

输出结果：

suitcase

字符串连接符会无视NULL，如

BEGIN
  DBMS_OUTPUT.PUT_LINE ('apple' || NULL || NULL || 'sauce');
END;
/

输出

applesauce

逻辑与或非

x	y	x AND y	x OR y	NOT x
`TRUE`	`TRUE`	`TRUE`	`TRUE`	`FALSE`
`TRUE`	`FALSE`	`FALSE`	`TRUE`	`FALSE`
`TRUE`	`NULL`	`NULL`	`TRUE`	`FALSE`
`FALSE`	`TRUE`	`FALSE`	`TRUE`	`TRUE`
`FALSE`	`FALSE`	`FALSE`	`FALSE`	`TRUE`
`FALSE`	`NULL`	`FALSE`	`NULL`	`TRUE`
`NULL`	`TRUE`	`NULL`	`TRUE`	`NULL`
`NULL`	`FALSE`	`FALSE`	`NULL`	`NULL`
`NULL`	`NULL`	`NULL`	`NULL`	`NULL`

逻辑运算符

Operator	Meaning
`=`	equal to
`<>`, `!=`, `~=`, `^=`	not equal to
`<`	less than
`>`	greater than
`<=`	less than or equal to
`>=`	greater than or equal to

几个坑：

TRUE是大于FALSE的

字符串比的是二进制

日期，越往后越大

LIKE

下划线_匹配一个字符

百分号%匹配0或多个字符

BETWEEN

BEGIN
  print_boolean ('2 BETWEEN 1 AND 3', 2 BETWEEN 1 AND 3);
  print_boolean ('2 BETWEEN 2 AND 3', 2 BETWEEN 2 AND 3);
  print_boolean ('2 BETWEEN 1 AND 2', 2 BETWEEN 1 AND 2);
  print_boolean ('2 BETWEEN 3 AND 4', 2 BETWEEN 3 AND 4);
END;
/

结果

BETWEEN 1 AND 3 = TRUE
BETWEEN 2 AND 3 = TRUE
BETWEEN 1 AND 2 = TRUE
BETWEEN 3 AND 4 = FALSE

Data Type	Maximum Size in PL/SQL	Maximum Size in SQL
`CHAR`	32,767 bytes	2,000 bytes
`NCHAR`	32,767 bytes	2,000 bytes
`RAW`	32,767 bytes	2,000 bytes
`VARCHAR2`	32,767 bytes	4,000 bytes
`NVARCHAR2`	32,767 bytes	4,000 bytes
`LONG`	32,760 bytes	2 gigabytes (GB) - 1
`LONG` `RAW`	32,760 bytes	2 GB
`BLOB`	128 terabytes (TB)	(4 GB - 1) * `database_block_size`
`CLOB`	128 TB	(4 GB - 1) * `database_block_size`
`NCLOB`	128 TB	(4 GB - 1) * `database_block_size`

varchar放不下的，可以用clob

Blank-padding

DECLARE
  first_name  CHAR(10 CHAR);
  last_name   VARCHAR2(10 CHAR);
BEGIN
  first_name := 'John ';
  last_name  := 'Chen ';
 
  DBMS_OUTPUT.PUT_LINE('*' || first_name || '*');
  DBMS_OUTPUT.PUT_LINE('*' || last_name || '*');
END;
/

输出

*John      *
*Chen *

VARCHAR2 不会自动补齐空格，CHAR会给字符串末尾补齐空格。

注释

单行注释

-- Begin processing

多行注释

/*
  IF 2 + 2 = 4 THEN
    some_condition := TRUE;
  /* We expect this THEN to always be performed */
  END IF;
*/

Table, Array, 用户自定义类型TYPE

DECLARE
  -- Associative array indexed by string:
  
  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
  -- Add elements (key-value pairs) to associative array:
 
  city_population('Smallville')  := 2000;
  city_population('Midland')     := 750000;
  city_population('Megalopolis') := 1000000;
 
  -- Change value associated with key 'Smallville':
 
  city_population('Smallville') := 2001;
 
  -- Print associative array:
 
  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;
/

SQL练习

表1: Person

+-------------+---------+ | 列名 | 类型 | +-------------+---------+ | PersonId | int | | FirstName | varchar | | LastName | varchar | +-------------+---------+ PersonId 是上表主键

表2: Address

+-------------+---------+ | 列名 | 类型 | +-------------+---------+ | AddressId | int | | PersonId | int | | City | varchar | | State | varchar | +-------------+---------+ AddressId 是上表主键

编写一个 SQL 查询，满足条件：无论 person 是否有地址信息，都需要基于上述两表提供 person 的以下信息

FirstName, LastName, City, State

https://leetcode-cn.com/problems/combine-two-tables

标准解法：

select FirstName, LastName, City, State
from Person left join Address
on Person.PersonId = Address.PersonId

左外连接，如果右表没有数据，就为空。

Oracle 数据库​

分片​

基础DML​

给某一列添加限制：​

SQL标准​

[集合运算符](The Set Operators (oracle.com))​

PL/SQL​

块​

子程序​

赋值​

SELECT INTO​

Row Locks​

Table Locks​

查看锁表进程​

MERGE INTO​

WHILE...LOOP...END LOOP​

字符串连接符 ||​

逻辑与或非​

逻辑运算符​

LIKE​

BETWEEN​

Blank-padding​

注释​

Table, Array, 用户自定义类型TYPE​

SQL练习​