OAuth2.0

2023-08-04 · callback queue

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;

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);

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 

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

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!

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


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!

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;

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;

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;

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 ...;

1
2
3
4
5

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

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;
/

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;

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'

1
2
3
4
5

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

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;

1
2
3

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

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;

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

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

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

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'

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;

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;

1
2
3
4
5
6
7

> <div class="poetry">满堂花醉三千客，一剑霜寒十四州。</div>

<style>
  .poetry {
    text-align: center;
  }
</style>

1

> ⚠ **If you are using mobile browser**: Be very careful here!

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

> Case 1
> 
> 00:23
> 
> 00:54
> 
> &nbsp;
> 
> Case 2
> 
> 00:21
> 
> 00:51

1
2
3

> line 1
 line 2
 line 3

1
2
3

``` diff
- I like markup
+ I like markdown

1
2

- I like markup
+ I like markdown

1
2

``` js, test.js
console.log('hello!')

1

console.log('hello!')

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

# Unordered list

* Item 1
* Item 2
* Item 3
    * Item 3a
    * Item 3b
    * Item 3c

# Ordered list

1. Step 1
2. Step 2
3. Step 3
    1. Step 3.1
    2. Step 3.2
    3. Step 3.3

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.

Syntax	Description
Special Characters
\n	New line.
\f	Form feed.
\r	Return.
\s	Space.
\t	Tab.
\v	Vertical tab.
[\b]	Backspace. As `\b` refers to `boundary`(see below), `[]` is used to differentiate backspace and boundary.
Repeats
?	Zero or one occurrence.
*	Zero or more occurrences.
{x}	X times.
{min, max}	min to max times. If max not given, then it means at least min times.
Margins
\b	Boundary. Matches a pseudo position between word character `\w` and non-word character `\W`. Often used to extract single word. Match `/\bcat\b/` in `The cat scattered his food` will not match `cat` in `scattered` as `cat` in `scattered` is surrounded by word character `s` and `t`.
\B	Non-boundary.
^	Start of string.
$	End of string.
Modifiers
/m	Multiple line.
/i	Ignore case.
/g	Global.
Sub-expression
Sub-expression	Expression within brackets `(` and `)` is a sub-expression.
Backreference	Use `\1`, `\2` in the back to reference the first, second expression in the front. Example: Use `\b(\w+)\s\1` to match two continuous same words in `Hello what what is the first thing, and I am am Tom.`.
Non-capturing group	Use `(?:)` to avoid being captured. Example: Using `(Chendongti)(?:an)` to match `Chendongtian` will only return one group `Chendongti`, `an` is not captured.
Lookahead	Use `(?=)`/`(?!)` to add a must-have/must-not-have suffix. Example: Using `happ(?=ily)` to match `happ happily` will only return `happ` in `happily`, and `happ(?!ily)` will only return `happ` in `happy`.
Lookbehind	Use `(?<=)`/`(?<!)` to add must-have/must-not-have prefix. Example: Using `(?<=ap)ple` to match `apple people` will only return `ple` in `apple`, and `(?<!ap)ple` will only return `ple` in `people`.
Logical operators
NOT	`^`. `[^abc]`.
OR	`\|`. `(a\|b)`.

Type	Description
Hash	Key-value dictionary object.
Sorted Set	Sorted set.
Set	Unsorted set.
String	Simplest type. String or number.
List	Linked list under the hood. Can add element to the head/left or the tail/right.
JSON	JSON object. Requires RedisJSON module be installed.

Element	Syntax
Heading	# Heading 1 ## Heading 2 ### Heading 3
Bold	bold text
Italic	Italic
Blockquote	> blockquote
List	1. First Item 2. Second Item 3. Third Item - First Item - Second Item - Third Item
Code	`code`
Horizontal rule	---
Link	[title](https://www.example.com)
Image	![alt text](image.jpg)

Element	Syntax
Table	\| Syntax \| Description \| \| ———– \| ———– \| \| Header \| Title \| \| Paragraph \| Text \| Note that there must be a blank line after the table.
Fenced code block	```{ firstname: ‘dongtian’, lastname: ‘chen’ }``` Use ``` [language] to specify code block language, for example ``` js to specify javascript. See supported language here
Footnote	Here’s a sentence with a footnote. [^1] [^1]: This is the footnote.
Definition List	term : definition
Strikethrough	~~The world is flat.~~
Task List	- [x] Write the press release - [ ] Update the website - [ ] Contact the media
Emoji	That is so funny! 😂
Highlight	I need to highlight these ==very important words==.
Subscript	H~2~O
Superscript	X^2^

EMP_ID	FIRST_NAME	LAST_NAME	DEPT_ID	MANAGER_ID	OFFICE_ID
1	Sally	Jones	3	2	5
2	Mark	Smith	2	4	3
3	John	Andrews	1	4	3
4	Michelle	Johnson	2		5
5	Brian	Grand	2	2	3

Refs

What is OAuth 2.0 ?

Refs

What is a memory leak ?

Refs

Why Koa ?

PL/SQL Concepts

Ref

Definition

Trigger

Stored Procedure and Function

Refs

Stored Procedure vs Function

Stored Procedure

Syntax

Test Data

Cursor

Examples

CTE

Ref

Definition

Example

Initialization data

Simple Example: Get employee name with department employe count

Recursive CTE Example

Materialized View

Ref

Concept

Syntax

Refresh on demand

Examples

Analytic Function/Window Function

Key points

Example

Others

Database link

INSERT MULTIPLE ROWS

MD5

Tools

Syntax

Vue 3

Refs

What is redis

Why redis

Install

Config

Difference between redis.windows-service.conf and redis.windows.conf

Security

Use

Different types

Commands

node redis

Reference:

Sinle-threaded Javascript

Asynchronism

Event loop and call stack

Queues

What is markdown ?

Why markdown ?

Kicking the tires

Syntax

Basic

Extended

FAQ

Links

Categories

Recents

Archives

Tags