引用

1. Beethoven: Piano Sonata No.8 in C minor “Pathetique” Analysis

背景

　　c小调第八号钢琴奏鸣曲《悲怆》Op.13，贝多芬早期钢琴奏鸣曲之顶峰的杰作。在贝多芬的钢琴奏鸣曲中，《悲怆》是第一首由他本人亲自写上标题的作品。关于”悲怆”这个词汇，与贝多芬后半生那感人肺腑而又凄怆深刻的悲剧性生活还有相当的一段距离，因为这毕竟是他的早期作品。
　　在这首作于1798—1799年间的标号为Op.13的钢琴奏鸣曲初版扉页上，贝多芬写着“Grande Sonata Pathetique”（悲怆大奏鸣曲）。在他的32部钢琴奏鸣曲中，只有这一部和Op.81a“告别”是由作曲家自己加上标题的。二十八、九岁的贝多芬，正值青春年华，事业蒸蒸曰上，为什么要写上这么一个标题呢？罗曼·罗兰在他著名的《贝多芬传》中回答了我的疑问：

“……然而痛苦已在叩门；它一朝住在他的身上之后就永远不再退隐。1796年至1800年间，耳聋已开始它的酷刑。”

　　1801年，贝多芬在给韦该勒的信上写道：

“我过着一种悲惨的生活。两年以来我躲避着一切交际，因为我不可能与人说话：我聋了。要是我干着别的职业，也许还可以，但在我的行当里，这是可怕的遭遇啊。我的敌人们又将怎么说，他们的数目又是相当可观！……在戏院里，我得坐在贴近乐队的地方，才能懂得演员的说话。……人家柔和地说话时，我勉强听到一些，人家高声叫喊时，我简直痛苦难忍……我时常诅咒我的生命……普卢塔克 教我学习隐忍。我却愿和我的命运挑战，只要可能；但有些时候，我竟是上帝最可怜的造物……隐忍！多伤心的避难所！然而这是我唯一的出路。”

　　有人曾将这首奏鸣曲与莎士比亚的《罗密欧与朱丽叶》相比较，指出在这两部作品中，存在着共同的“青春的哀伤感”。 可能人们是从这个标题出发，将它和当时德国社会中普遍存在的一种情绪相对应而作出这种结论的。确实，在歌德的 《少年维特之烦恼》中，在莫扎特的g小调第40交响曲中，都弥漫着类似的情绪。但贝多芬毕竟有其独特的个性，实际上，他的作品中弥漫着的是一股蓬勃的生命力，同时也弥漫着一种对无限、对理想的渴望。他已经超越了自己的肉体上和生活中的种种痛苦、恐惧、忍让、敬畏，他感悟到了生命的升华。正如E·T·A·霍夫曼所说：“人的心在尘世之物中感悟超尘世之物。”

曲谱(前16小节)

分析

参考

1. 国务院办公厅关于印发治理高值医用耗材改革方案的通知(国办发〔2019〕37号)

概念

定义

高值医用耗材是指直接作用于人体、对安全性有严格要求、临床使用量大、价格相对较高、群众费用负担重的医用耗材[1]。

Refs

1. Node.js Worker Threads
2. Deep dive into threads and processes in Node.js
3. How do cluster and worker threads work in node.js

Multithreading: Worker thread

What and why

A thread that enables node.js to execute JavaScript in parallel. Useful to handle CPU intensive jobs.

How-to

Create a worker file => Make a promise in caller file => Define on message/error/exit hooks
worker.js file:

1
2
3
4
5
6

const { workerData, parentPort }	= require('worker_threads')

console.log('Technical Articles on ' + workerData);

parentPort.postMessage(
	{ fileName: workerData, status: 'Done' })

index.js file:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

const { Worker } = require('worker_threads')

function runService(workerData) {
	return new Promise((resolve, reject) => {
		const worker = new Worker(
				'./worker.js', { workerData });
		worker.on('message', resolve);
		worker.on('error', reject);
		worker.on('exit', (code) => {
			if (code !== 0)
				reject(new Error(
`Stopped the Worker Thread with the exit code: ${code}`));
		})
	})
}

async function run() {
	const result = await runService('GeeksForGeeks')
	console.log(result);
}

run().catch(err => console.error(err))

Key points:

1. Worker use workerData to receive data from caller and parentPort to post data to caller.
2. Worker use parentPort.postMessage to send data to caller and caller use on('message') to receive.

Multiprocess: Fork and Cluster

An example to address the problem of single-threaded node.js

Single threaded node.js will block on a time-consuming request.
Example:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

const http = require('http');
const longComputation = () => {
  let sum = 0;
  for (let i = 0; i < 1e10; i++) {
    sum += i;
  };
  return sum;
};
const server = http.createServer();
server.on('request', (req, res) => {
  if (req.url === '/compute') {
    console.info('计算开始',new Date());
    const sum = longComputation();
    console.info('计算结束',new Date());
    return res.end(`Sum is ${sum}`);
  } else {
    res.end('Ok')
  }
});

server.listen(3000);

Call localhost:3000/compute will block

Pros and cons of single-threaded node.js

Pros:

1. Simple, no creation and switching of threads.
2. Event loop and non-blocking asynchronous mechanism ensures high performance for high concurrency.
   Cons:
3. CPU intensive calculation may block entire node.js app.
4. An error may kill the thread, thus kill entire app. A daemon thread should be considered.
5. Single thread does not take advantage of a multi-core CPU.

Fork

Use child_process.fork to create new process.
Main.js:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

const http = require('http');
const fork = require('child_process').fork;

const server = http.createServer((req, res) => {
    if(req.url == '/compute'){
        const compute = fork('./compute.js');
        compute.send('开启一个新的子进程');

        // 当一个子进程使用 process.send() 发送消息时会触发 'message' 事件
        compute.on('message', sum => {
            res.end(`Sum is ${sum}`);
            compute.kill();
        });

        // 子进程监听到一些错误消息退出
        compute.on('close', (code, signal) => {
            console.log(`收到close事件，子进程收到信号 ${signal} 而终止，退出码 ${code}`);
            compute.kill();
        })
    }else{
        res.end(`ok`);
    }
});
server.listen(3000, '127.0.0.1', () => {
    console.log(`server started at http://127.0.0.1:3000`);
});

compute.js:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

const computation = () => {
    let sum = 0;
    console.info('计算开始');
    console.time('计算耗时');

    for (let i = 0; i < 1e10; i++) {
        sum += i
    };

    console.info('计算结束');
    console.timeEnd('计算耗时');
    return sum;
};

process.on('message', msg => {
    console.log(msg, 'process.pid', process.pid); // 子进程id
    const sum = computation();

    // 如果Node.js进程是通过进程间通信产生的，那么，process.send()方法可以用来给父进程发送消息
    process.send(sum);
})

Cluster

cluster can create worker process in a single file.
Example:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

const http = require('http');
const numCPUs = require('os').cpus().length;
const cluster = require('cluster');
if(cluster.isMaster){
    console.log(`Master process id is ${process.pid}, cpu number ${numCPUs}`);
    // fork workers
    for(let i= 0;i<numCPUs;i++){
        cluster.fork();
    }
    cluster.on('exit',function(worker,code,signal){
        console.log('worker process died,id',worker.process.pid)
    })
}else{
    // Worker can share the same TCP connection
    // It's an http server here
    console.log(`created worker pid ${process.pid}`)
    http.createServer(function(req,res){
        res.writeHead(200);
        res.end(String(process.pid));
    }).listen(8000);

}

Running code above, we got:

1
2
3
4
5

Master process id is 18428, cpu number 4
created slave pid 16672
created slave pid 9896
created slave pid 14676
created slave pid 1460

We can find these process in task manager:

Now send a request in browser, and id of one of the four server process is returned:

Try refresh many times and different pids may return(It depends, maybe one unlucky process shoulders all the workload).
Now kill one the worker 1460 in task manger and we got:

1

worker process died,id 1460

Refresh the browser and result is another pid other than 1460:

You see, now our server is much more robust than before. We got four worker process, killing one of them and there are still three working.

Cluster calls the same fork method from child_process module under the hood. Cluster is a master-slave model, where master manages and schedules slaves.

Why no Error: EADDRINUSE when multiple processes listens on the same port?
The child processes aren’t listening to the same port. Incoming socket connections to the master process are being delegated to the child processes. There’s special handling for clustered process in server.listen(), it calls a method named listenInCluster() in some circumstances. See explanation here

Multithreading vs multiprocess

cluster

• One process is launched on each CPU and can communicate via IPC.
• Each process has its own memory with its own Node (v8) instance. Creating tons of them may create memory issues.
• Great for spawning many HTTP servers that share the same port b/c the master process will multiplex the requests to the child processes.

worker threads

• One process total
• Creates multiple threads with each thread having one Node instance (one event loop, one JS engine). Most Node API’s are available to each thread except a few. So essentially Node is embedding itself and creating a new thread.
• Shares memory with other threads (e.g. SharedArrayBuffer)
• Great for CPU intensive tasks like processing data or accessing the file system. Because NodeJS is single threaded, synchronous tasks can be made more efficient with workers

参考

1. 基本医疗保险制度
2. 职工医保、居民医保、新农合到底哪个更划算？
3. 湖北明年合并城镇居民医保和新农合
4. 2022年度湖北城乡居民医保缴费
5. 武汉市个人医疗保障中,个人帐户每月划入金额以及比例是多少?
6. 医保到底保了个啥？
7. 影响武汉医保报销的因素有哪些？报销费用如何计算？
8. 我国的医保到底是如何报销的？生病住院到底报销百分之多少？

How it happened

Using node oracledb to develop a backend api and encountered NJS-016: buffer is too small for OUT binds exception for a simple select query: select a, b, c from tab@dblink

Debugging

1. Googled NJS-016: buffer is too small for OUT binds and found little help. None of them suits my case.
2. Maybe it has something to do with database link ? Charset for local database is AMERICAN_AMERICA.AL32UTF8 and for remote database is AMERICAN_AMERICA.ZHS16GBK. Changed charset environment variable to the same as remote database, still no luck.
3. Found that column b has some weired behavior: for chinese character value 神经阻滞 it only shows first two characters 神经 when environment variable is set to AMERICAN_AMERICA.AL32UTF8, different from database charset AMERICAN_AMERICA.ZHS16GBK. Column b definition is varchar2(8). Maybe has something to do with this ?

Reasoning

For charset AMERICAN_AMERICA.ZHS16GBK, each chinese character is 2 bytes wide.
For charset AMERICAN_AMERICA.AL32UTF8, each chinese character is 3 bytes wide.
With varchar2(8), it can hold 4 characters on AMERICAN_AMERICA.ZHS16GBK, 2 characters on AMERICAN_AMERICA.AL32UTF8. That’s why it only shows 2 characters when environment variable is AMERICAN_AMERICA.AL32UTF8.
NJS-016 indicates out binds with varhcar2(8) cannot hold 神经阻滞 as it is 4×3=12 bytes wide.

Solution

1. Expand column width from varchar2(8) to a at least varchar2(12)(Intrusive, not recommended).
2. Avoid using database links when charset is different on remote database. Create a distinct connection pool for remote database and execute query on a connection fetched from the pool.

PL/SQL Concepts

Ref

1. PL/SQL Wiki

Definition

• Procedural Language for SQL is a procedural extesion for SQL.
• Most commonly used features include:
  • Program units
    • Block
    • Function
    • Procedure
    • Package
    • Trigger
  • Data types
    • Numeric
    • Character
    • Date
    • Exceptions
    • Specifi columns
  • Conditional statements
  • Array handling
    • Associative arrays(index-by tables)
    • Nested tables
    • Varrays(variable-size arrays)
  • Cursors
  • Looping
  • Dynamic SQL

Trigger

Stored Procedure and Function

Refs

1. Oracle Procedures
2. PL/SQL Tutorial

Stored Procedure vs Function

Function vs Stored Procedure:

Functions follow the computer-science definition in that they MUST return a value and cannot alter the data they receive as parameters (the arguments). Functions are not allowed to change anything, must have at least one parameter, and they must return a value. Stored procs do not have to have a parameter, can change database objects, and do not have to return a value.

Stored Procedure

Syntax

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

-- create a procedure:
CREATE [OR REPLACE] PROCEDURE procedure_name
    [ (parameter [,parameter]) ]

IS
    [declaration_section]

BEGIN
    executable_section

[EXCEPTION
    exception_section]

END [procedure_name];

-- drop a procedure:
DROP PROCEDURE procedure_name;

• Parameter types:
  • IN: Passed into the procedure, must assign a value, immutable.
  • OUT: Not required to assign a value, mutable.
  • IN OUT: Passed into the procedure, must assign a value, mutable.

Test Data

• Initalize test data for the examples:

1
2
3
4
5
6
7
8
9
10
11
12
13
14

create table customers (
  id number primary key,
  name varchar2(50),
  age number,
  address varchar2(50),
  salary number
);

insert into customers values(1, 'Ramesh', 32, 'Abmedabad', 2000);
insert into customers values(2, 'Khilan', 25, 'Delhi', 1500);
insert into customers values(3, 'Kaushik', 23, 'Kota', 2000);
insert into customers values(4, 'Chaitali', 25, 'Mumbai', 6500);
insert into customers values(5, 'Hardik', 27, 'Bhopal', 8500);
insert into customers values(6, 'Komal', 22, 'MP', 4500);

Cursor

• A cursor is a pointer that points to the result of a query.

• There are two types of cursors:

  • Implicit cursor
    • Automatically created by Oracle whenever an SQL statement is executed.
    • Whenever a DML(INSERT, UPDATE and DELETE) statement is executed, an implicit cursor is associated with this statement.
    • For INSERT statement, the cursor holds the data to be inserted; For UPDATE and DELETE statement, the cursor identifies the rows that would be affected.
  • Explicit cursor
    • Created on a SELECT statement which returns more than one row.

• Cursor attributes

  Attribute	Description
  %FOUND	Returns TRUE if an INSERT, UPDATE, or DELETE statement affected one or more rows or a SELECT INTO statement returned one or more rows. Otherwise, it returns FALSE.
  %NOTFOUND	The logical opposite of %FOUND. It returns TRUE if an INSERT, UPDATE, or DELETE statement affected no rows, or a SELECT INTO statement returned no rows. Otherwise, it returns FALSE.
  %ISOPEN	Always returns FALSE for implicit cursors, because Oracle closes the SQL cursor automatically after executing its associated SQL statement.
  %ROWCOUNT	Returns the number of rows affected by an INSERT, UPDATE, or DELETE statement, or returned by a SELECT INTO statement.

Examples

If you run the examples in PL/SQL Developer, remember to run the code of the procedure creation in a program windows instead of a sql window. Sql window does not throw errors!

• Implicit cursor:

1
2
3
4
5
6
7
8
9
10
11
12
13

DECLARE
  total_rows number(2);
BEGIN
  UPDATE customers SET salary = salary + 500;
  IF sql%notfound THEN
    dbms_output.put_line('no customers updated');
  ELSIF sql%found THEN
    total_rows := sql%rowcount;
    dbms_output.put_line(total_rows || ' customers updated ');
  END IF;
  COMMIT;
END;
-- output: 6 customers updated 

• Explicit cursor:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

DECLARE 
   c_id customers.id%type; 
   c_name customers.name%type; 
   c_addr customers.address%type; 
   CURSOR c_customers is 
      SELECT id, name, address FROM customers; 
BEGIN 
   OPEN c_customers; 
   LOOP 
   FETCH c_customers into c_id, c_name, c_addr; 
      EXIT WHEN c_customers%notfound; 
      dbms_output.put_line(c_id || ' ' || c_name || ' ' || c_addr); 
   END LOOP; 
   CLOSE c_customers; 
END; 
-- output:
-- 1 Ramesh Abmedabad
-- 2 Khilan Delhi
-- 3 Kaushik Kota
-- 4 Chaitali Mumbai
-- 5 Hardik Bhopal
-- 6 Komal MP

• Simplest procedure:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

CREATE OR REPLACE PROCEDURE greetings 
AS 
BEGIN 
   dbms_output.put_line('Hello World!'); 
END; 

-- Run the procedure in different ways:
-- 1. CALL
call greetings();
-- 2. EXECUTE
execute greetings; -- Notice this cannot be used in IDEs like PL/SQL Developer. Only viable in sql*plus or Oracle SQL Developer. See why here: https://stackoverflow.com/questions/13722307/ora-00900-invalid-sql-statement-when-run-a-procedure-in-oracle-10g
-- 3. in a PL/SQL block:
begin 
  greetings;
  greetings(); -- For procedure without parameters, brackets are optional.
end;
-- Hello World!

• Procedure with parameters:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

DECLARE 
   a number; 
   b number; 
   c number;
PROCEDURE findMin(x IN number, y IN number, z OUT number) IS 
BEGIN 
   IF x < y THEN 
      z:= x; 
   ELSE 
      z:= y; 
   END IF; 
END; 

  
BEGIN 
   a:= 23; 
   b:= 45; 
   findMin(a, b, c); 
   dbms_output.put_line(' Minimum of (23, 45) : ' || c); 
END;
-- output: Minimum of (23, 45) : 23

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

DECLARE 
   a number; 
PROCEDURE squareNum(x IN OUT number) IS 
BEGIN 
  x := x * x; 
END;  


BEGIN 
   a:= 23; 
   squareNum(a); 
   dbms_output.put_line(' Square of (23): ' || a); 
END; 
-- output:  Square of (23): 529

• Exception handling:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

DECLARE
  c_id   customers.id%type := 8;
  c_name customerS.Name%type;
  c_addr customers.address%type;
BEGIN
  SELECT name, address INTO c_name, c_addr FROM customers WHERE id = c_id;
  DBMS_OUTPUT.PUT_LINE('Name: ' || c_name);
  DBMS_OUTPUT.PUT_LINE('Address: ' || c_addr);

EXCEPTION
  WHEN no_data_found THEN
    dbms_output.put_line('No such customer!');
  WHEN others THEN
    dbms_output.put_line('Error!');
END;
--output: No such customer!

CTE

Ref

• 1. SQL CTE

Definition

• Common Table Expression, a query defined within another query.
• It’s like a subquery, but can be assigned a name and reused many times.
• It’s also like a ‘one-shot’ view.
• Also called a SQL WITH clause as it uses the WITH keyword.

Example

Initialization data

• Initialize data:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

CREATE TABLE employee (
  emp_id NUMBER(5),
  first_name VARCHAR2(50),
  last_name VARCHAR2(50),
  dept_id NUMBER(5),
  manager_id NUMBER(5),
  office_id NUMBER(5)
);
 
CREATE TABLE department (
  dept_id NUMBER(5),
  dept_name VARCHAR2(50)
);
 
INSERT ALL
INTO employee (emp_id, first_name, last_name, dept_id, manager_id, office_id) VALUES (1, 'Sally', 'Jones', 3, 2, 5)
INTO employee (emp_id, first_name, last_name, dept_id, manager_id, office_id) VALUES (2, 'Mark', 'Smith', 2, 4, 3)
INTO employee (emp_id, first_name, last_name, dept_id, manager_id, office_id) VALUES (3, 'John', 'Andrews', 1, 4, 3)
INTO employee (emp_id, first_name, last_name, dept_id, manager_id, office_id) VALUES (4, 'Michelle', 'Johnson', 2, NULL, 5)
INTO employee (emp_id, first_name, last_name, dept_id, manager_id, office_id) VALUES (5, 'Brian', 'Grand', 2, 2, 3)
SELECT * FROM dual;
 
INSERT ALL
INTO department (dept_id, dept_name) VALUES (1, 'Sales')
INTO department (dept_id, dept_name) VALUES (2, 'IT')
INTO department (dept_id, dept_name) VALUES (3, 'Support')
SELECT * FROM dual;

This creates the following data:

Simple Example: Get employee name with department employe count

1
2
3
4
5
6

WITH d_count AS
 (SELECT dept_id, COUNT(*) AS dept_count FROM employee GROUP BY dept_id)
SELECT e.first_name, e.last_name, d.dept_count
  FROM employee e
 INNER JOIN d_count d
    ON e.dept_id = d.dept_id;

Recursive CTE Example

1
2
3
4
5
6
7
8
9
10
11
12
13
14

WITH cteEmp(emp_id,
first_name,
manager_id,
emplevel) AS
 (SELECT emp_id, first_name, manager_id, 1
    FROM employee
   WHERE manager_id IS NULL
  UNION ALL
  SELECT e.emp_id, e.first_name, e.manager_id, r.emplevel + 1
    FROM employee e, cteEmp r
   WHERE e.manager_id = r.emp_id)
SELECT emp_id, first_name, manager_id, emplevel
  FROM cteEmp
 ORDER BY emplevel;

• This produces the following result:

The above result is generated in the following process:

1. Get data from the initial query where manager_id IS NULL, which returns one row;
   Notice that a recursive CTE must have be UNION query connecting a initial qery and a recursive query.
2. The recursive query FROM employee e, cteEmp r WHERE e.manager_id = r.emp_id associates the original table with data from the initial query, returning more data;
3. Repeats step 2, until no more data is yielded.

Materialized View

Ref

1. Materialized Views in Oracle

Concept

• A materialized view, or snapshot as they were previously known, is a table segment whose contents are periodically refreshed based on a query, either against a local or remote table.

Syntax

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

-- Normal
CREATE MATERIALIZED VIEW [VIEW_NAME]
BUILD [IMMEDIATE | DEFERRED]
REFRESH [FAST | COMPLETE | FORCE ]
ON [COMMIT | DEMAND ]
[START WITH ... NEXT ...]
[[ENABLE | DISABLE] QUERY REWRITE]
AS
SELECT ...;

-- Pre-Built
CREATE MATERIALIZED VIEW [VIEW_NAME]
ON PREBUILT TABLE
REFRESH [FAST | COMPLETE | FORCE ]
ON [COMMIT | DEMAND ]
[START WITH ... NEXT ...]
[[ENABLE | DISABLE] QUERY REWRITE]
AS
SELECT ...;

• Build options:

  • IMMEDIATE: The materialized view is populated immediately.
  • DEFERRED: The materialized view is populated on the first request refresh.

• Refresh types:

  • FAST: A fast refresh is attempted. If materialized view logs are not present against the source tables in advance, the creation fails.
  • COMPLETE: The table segment supporting the materialized view is truncated and repopulated completely using the associated query.
  • FORCE: A fast refresh is attempted. If one is not possible a complete refresh is performed.
  • FAST VS COMPLETE: FAST refresh only updates rows while COMPLETE refresh completely removes all data and inserts all data from the query.

• Trigger types:

  • ON COMMIT: The refresh is triggered by a comitted data change in one of the dependent tables.
  • ON DEMAND: The refresh is initiated by a manual request or a scheduled task.
  • Refreshing on commi is very intensive on a volative base table.

• Prebuilt:

  • ON PREBUILT TABLE: use existing table which must have same name as materialized view and same column structure as the query.
  • QUERY REWRITE: tells optimizer if materilized view should be considered for qery rewrite operations.

• Schedule:

  • The START WITH ... NEXT ... specifies a schedule.

Refresh on demand

• A materialized view can be refreshed either manually or as part of a refresh group or via a schedule.

• Refresh manually:

  1 2 3 4 5

  EXEC DMBS_MVIEW.refresh('mvt1'); -- For PL/SQL, use this: begin DBMS_MVIEW.REFRESH('mvt1'); end;

• Create a refresh group:

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25

  BEGIN DBMS_REFRESH.make( name => 'SCOTT.MINUTE_REFRESH', list => '', next_date => SYSDATE, interval => '/*1:Mins*/ SYSDATE + 1/(60*24)', implicit_destroy => FALSE, lax => FALSE, job => 0, rollback_seg => NULL, push_deferred_rpc => TRUE, refresh_after_errors => TRUE, purge_option => NULL, parallelism => NULL, heap_size => NULL); END; / BEGIN DBMS_REFRESH.add( name => 'SCOTT.MINUTE_REFRESH', list => 'SCOTT.EMP_MV', lax => TRUE); END; /

• Create a schedule(must be specified at the creation of the materialized view):

  1 2 3 4 5 6

  create materialized view mvt1 build immediate refresh force on demand start with sysdate next sysdate + 1/24/60 -- Refresh every minute as select * from mvtt;

  The materialized view mvt1 will be refreshed every minute. An Oracle job is also created at the same time.
  To find out the job id, use this query:

1
2
3
4
5
6
7
8
9

select m.owner, m.mview_name, r.job
  from dba_refresh r
 inner join dba_refresh_children rc
    on rc.rowner = r.rowner
   and rc.rname = r.rname
 inner join dba_mviews m
    on m.owner = rc.owner
   and m.mview_name = rc.name
 where m.mview_name = 'MVT1'

Examples

• Create a test table mvtt:

• Create a materialized view:

1
2
3
4
5

create materialized view mvt1
build immediate
refresh force on demand
as
select * from mvtt;

Now mvt1 has the same data as mvtt. Insert a new row will not trigger an update in mvtt because its trigger type is on demand.

Analytic Function/Window Function

• Ref

Key points

• Analytic functions calculate an aggregate value based on a group of rows and return multiple rows for each group.
• Analytic functions are just like GROUP BY aggregate functions, but do not reduce rows retured.
• Syntax:
  • analytic_function([ arguments ]) OVER (analytic_clause)
    • Anytic clause can be broken down as: [ query_partition_clause ] [ order_by_clause [ windowing_clause ] ]

Example

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

CREATE TABLE emp (
  empno    NUMBER(4) CONSTRAINT pk_emp PRIMARY KEY,
  ename    VARCHAR2(10),
  job      VARCHAR2(9),
  mgr      NUMBER(4),
  hiredate DATE,
  sal      NUMBER(7,2),
  comm     NUMBER(7,2),
  deptno   NUMBER(2)
);

INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO emp VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO emp VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO emp VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO emp VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO emp VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO emp VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO emp VALUES (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20);
INSERT INTO emp VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO emp VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO emp VALUES (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20);
INSERT INTO emp VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO emp VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO emp VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
COMMIT;

• Get average department salary for each row

1
2
3

SELECT empno, deptno, sal,
       AVG(sal) OVER (PARTITION BY deptno) AS avg_dept_sal
FROM   emp;

• Get first value order by salary, put NULL values last in he sorted query results
  • Note:
    • In terms of sorting, SQL sorts NULL values to last in ASC order and first in DESC
    • MAX and MIN ignores NULL values

1
2
3
4
5

SELECT empno,
       deptno,
       sal,
       FIRST_VALUE(sal IGNORE NULLS) OVER(PARTITION BY deptno order by sal asc nulls last) AS first_sal_in_dept
  FROM emp;

• Windowing clause: an extension of the order_by_clause, can only be used when order_by_clause is present.
• When there’s order_by_clause, a default windowing clause is attached: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  • The fact it is RANGE, not ROWS, means it includes all rows with the same value as the value in the current row, even if they are further down the result set. As a result, the window may extend beyond the current row, even though you may not think this is the case.

1
2
3

RANGE BETWEEN start_point AND end_point
ROWS BETWEEN start_point AND end_point
GROUPS BETWEEN start_point AND end_point (21c onward)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

SELECT empno, deptno, sal, 
       AVG(sal) OVER (PARTITION BY deptno ORDER BY sal) AS avg_dept_sal_sofar
FROM   emp;

     EMPNO     DEPTNO        SAL AVG_DEPT_SAL_SOFAR
---------- ---------- ---------- ------------------
      7934         10       1300               1300
      7782         10       2450               1875
      7839         10       5000         2916.66667

      7369         20        800                800
      7876         20       1100                950
      7566         20       2975               1625
      7788         20       3000               2175
      7902         20       3000               2175

      7900         30        950                950
      7654         30       1250               1150
      7521         30       1250               1150
      7844         30       1500             1237.5
      7499         30       1600               1310
      7698         30       2850         1566.66667

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

SELECT empno, deptno, sal, 
       AVG(sal) OVER (PARTITION BY deptno ORDER BY sal
                      RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS range_avg,
       AVG(sal) OVER (PARTITION BY deptno ORDER BY sal
                      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rows_avg
FROM   emp;

     EMPNO     DEPTNO        SAL  RANGE_AVG   ROWS_AVG
---------- ---------- ---------- ---------- ----------
      7934         10       1300       1300       1300
      7782         10       2450       1875       1875
      7839         10       5000 2916.66667 2916.66667

      7369         20        800        800        800
      7876         20       1100        950        950
      7566         20       2975       1625       1625
      7788         20       3000       2175    1968.75
      7902         20       3000       2175       2175

      7900         30        950        950        950
      7654         30       1250       1150       1100
      7521         30       1250       1150       1150
      7844         30       1500     1237.5     1237.5
      7499         30       1600       1310       1310
      7698         30       2850 1566.66667 1566.66667

• LAG: Access the row at a given offset prior of the current row without using a self-join.
• Ref
• Syntax:

1
2
3
4
5

LAG(expression [, offset ] [, default ])
OVER (
	[ query_partition_clause ] 
	order_by_clause
)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

SELECT e.empno,
       e.deptno,
       e.sal,
       LAG(sal, 1) OVER(partition by deptno ORDER BY sal) sal_lower_closest
  FROM emp e
 order by deptno, sal


 EMPNO     DEPTNO     SAL        SAL_LOWER_CLOSEST
---------- ---------- ---------- ----------
      7934	10	1300.00	
      7782	10	2450.00	1300
      7839	10	5000.00	2450
      7369	20	800.00	
      7876	20	1100.00	800
      7566	20	2975.00	1100
      7788	20	3000.00	2975
      7902	20	3000.00	3000
      7900	30	950.00	
      7654	30	1250.00	950
      7521	30	1250.00	1250
      7844	30	1500.00	1250
      7499	30	1600.00	1500
      7698	30	2850.00	1600

• SUM also supports windowing:

1
2
3
4
5
6

SELECT e.empno,
       e.deptno,
       e.sal,
       sum(sal) OVER(partition by deptno ORDER BY sal) dept_accum_sal
  FROM emp e
 order by deptno, sal

Others

Database link

1
2
3
4
5
6
7

create public database link
  mylink
connect to
  remote_username
identified by
  mypassword
using 'tns_service_name'

1
2
3

-- user和password都不需要引号，如果passwrd有特殊字符，那就用双引号""包起来
create public database link dbl_term connect to user1
identified by "password1" using '192.168.100.100/orcl'

Find all database links:

• DBA_DB_LINKS - All DB links defined in the database
• ALL_DB_LINKS - All DB links the current user has access to
• USER_DB_LINKS - All DB links owned by current user

INSERT MULTIPLE ROWS

• Use INSERT ALL to insert multiple rows:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

CREATE TABLE employee (
  emp_id NUMBER(5),
  first_name VARCHAR2(50),
  last_name VARCHAR2(50),
  dept_id NUMBER(5),
  manager_id NUMBER(5),
  office_id NUMBER(5)
);
 
INSERT ALL
INTO employee (emp_id, first_name, last_name, dept_id, manager_id, office_id) VALUES (1, 'Sally', 'Jones', 3, 2, 5)
INTO employee (emp_id, first_name, last_name, dept_id, manager_id, office_id) VALUES (2, 'Mark', 'Smith', 2, 4, 3)
INTO employee (emp_id, first_name, last_name, dept_id, manager_id, office_id) VALUES (3, 'John', 'Andrews', 1, 4, 3)
INTO employee (emp_id, first_name, last_name, dept_id, manager_id, office_id) VALUES (4, 'Michelle', 'Johnson', 2, NULL, 5)
INTO employee (emp_id, first_name, last_name, dept_id, manager_id, office_id) VALUES (5, 'Brian', 'Grand', 2, 2, 3)
SELECT * FROM dual;

MD5

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

CREATE OR REPLACE FUNCTION MD5(

                               passwd IN VARCHAR2)

 RETURN VARCHAR2

 IS

  retval varchar2(32);

BEGIN

  retval := utl_raw.cast_to_raw(DBMS_OBFUSCATION_TOOLKIT.MD5(INPUT_STRING => passwd));

  RETURN retval;

END;

Tools

Regexper: Visulization tool for Regexp
Regex101: Write, test and debug Regexp

Syntax

参考

1. 【电池】30分钟，让你成为半个锂电池专家
2. 铁电池、三元锂、NCM都是啥？电动汽车电池，纯干货一文全知道！
3. 常见圆柱电池型号

种类

LiFePO4: LFP, 磷酸铁锂, 用磷酸铁锂作为正极材料的锂离子电池。
Ternary Lithium: 正极材料使用镍钴锰酸锂（Li（NiCoMn）O2）三元正极材料的锂电池，三元复合正极材料前驱体产品，是以镍盐、钴盐、锰盐为原料。
Cylindrical: 圆柱电池。
Prismatic: 方型电池。
LTO: lithium-titanium-oxide/lithium-titanate, 钛酸锂电池，一种用作锂离子电池负极材料-钛酸锂，可与锰酸锂、三元材料或磷酸铁锂等正极材料组成2.4V或1.9V的锂离子二次电池。
Polymer lithium: 聚合物电池。

电芯正极材料

　　锂电池正极材料，是锂离子电池构成材料的一部分，直接影响着锂离子电池的性能，占有较大比例（正负极材料的质量比为3: 1~4：1）。

　　锂离子电池是以2种不同的能够可逆地插入及脱出锂离子的嵌锂化合物分别作为电池的正极和负极的二次电池体系。充电时，锂离子从正极材料的晶格中脱出，经过电解质后插入到负极材料的晶格中，使得负极富锂，正极贫锂；放电时锂离子从负极材料的晶格中脱出，经过电解质后插入到正极材料的晶格中，使得正极富锂，负极贫锂。这样正负极材料在插入及脱出锂离子时相对于金属锂的电位的差值，就是电池的工作电压。

种类：
LCO: LiCoO2, Lithium Cobalt Oxide. 钴酸锂。
LNO: 镍酸锂。
LMO: LiMn2O4, Lithium Manganese Oxide. 锰酸锂。
NCM: 镍锰钴。
NCA: 镍钴铝。
LFP: 磷酸铁锂。

圆柱电池型号

常见证书和证明

参考：

1. 危包证是什么
2. 各国认证

各国认证

危包证

“危包证”是一种俗称，准确的讲危包证分为两个，一个是性能证，全称是《出入境货物包装性能检验结果单》，另一个是使用证，全称是《出境危险货物运输包装使用鉴定结果单》。危包证是要有了包装性能单才能去商检局做了商检之后再换包装使用单，这个使用单就是危包证。但由于相关部门监管愈发严格，很多企业可能部分资质不齐，导致办不出危包证。

《中华人民共和国进出口商品检验法》及其实施条例规定，出口危险货物包装容器生产企业应当向产地海关申请危险货物包装容器性能检验，出口危险货物的生产企业应当向产地海关申请危险货物包装容器使用鉴定。港监申报时就需要用到危包证，出口商在出口危险品时如果没有危包证，在港监申报就放行不了，柜子上不了船。

办理危包证的话，需根据你产品实际性质包装情况要各包装生产厂家提供《中华人民共和国出入境检验检疫局出境货物运输包装性能检验结果单》，也就是性能单，再去做个分类鉴定报告，按照商检局的系列程序走完即可得到危包证。具体可以去当地商检局去咨询办理流程。如果觉得流程繁杂，我们也可以代为办理危包证。办理危包证一般5到7个工作日可以出证，最迟两周。若紧急情况可以加钱进行加急，那么危包证3到5个工作日就能到手。

常见问题

• 3.2v和3.7v区别

3.2V表示的是磷酸铁锂电池，其主要正极化学原材料为LiFePO4，由于其本身原材料的化学特性，决定了其生产制造出来的单颗电芯电压就是3.2V；

3.7V表示的是聚合物锂电池（常见锂离子电池，如手机、无线无耳机、智能手表等的电池），主要正极化学原材料有LiCoO2、LiNiO2、LiMnO2和锰镍钴复合氧化物等，由于其本身的化学物质的特性，制造出来的电池电芯电压一般是3.7V。

• 什么是高倍率电池？

High rate battery, 一般指的放电率高，放电电流大的电池，适用于无人机、航模、电动工具等大功率产品。