SQLSERVER的CTE递归查询

SQLSERVERCTE递归查询,虽然语法上比Oracle的麻烦一些,但本身比较简单,下面这个例子主要是取出顶层节点、层级数,额外增加了一点代码

创建测试表:

CREATE TABLE tmp_t1 (id VARCHAR(10), name VARCHAR(30), pid VARCHAR(10));

插入测试数据

INSERT INTO tmp_t1 VALUES('A', 'XX集团', NULL);
INSERT INTO tmp_t1 VALUES('A1', '公司1', 'A');
INSERT INTO tmp_t1 VALUES('A11', '公司1-部门1', 'A1');
INSERT INTO tmp_t1 VALUES('A111', '公司1-部门1-模块1', 'A11');
INSERT INTO tmp_t1 VALUES('A12', '体系1-部门2', 'A1');
INSERT INTO tmp_t1 VALUES('A2', '公司2', 'A');
INSERT INTO tmp_t1 VALUES('A21', '公司2-部门1', 'A2');
INSERT INTO tmp_t1 VALUES('A22', '公司2-部门2', 'A2');

查询语句:

WITH org AS (
 SELECT id AS start_id, id, name, pid, 1 AS level
 FROM tmp_t1
 WHERE pid = 'A'
 UNION ALL
 SELECT t2.start_id, t1.id, t1.name, t1.pid, t2.level + 1 AS level
 FROM tmp_t1 t1 INNER JOIN org t2
   ON t1.pid = t2.id
)
SELECT
*
FROM org
where level <=2

运行结果:

start_id id name pid level
A1 A1 公司1 A 1
A2 A2 公司2 A 1
A2 A21 公司2-部门1 A2 2
A2 A22 公司2-部门2 A2 2
A1 A11 公司1-部门1 A1 2

 

PL/SQL的常用设置

一、显示所有打开的窗口,并永久生效
菜单:Tools -> Window List
菜单:Window -> Save Layout

二、默认显示My Objects,提高数据库对象的展开速度
菜单:Tools -> Browser Filters,将My Objects设置为默认

三、调整数据库对象的显示顺序,最常用的放在前面
菜单:Tools -> Browser Folders
建议显示顺序为:Recent Objects | Tables | Sequences | Views | Tablespaces | Fucntions | Procedures | Tiggers | Database Links | Jobs | Users | Recycle bin,其他顺序不变,可以根据自己的习惯调整。

四、编辑器设置:关键字自动转化为大写
菜单:Tools -> Preferences -> User Interface -> Editor -> Keyword case

五、编辑器设置:用缩写完成常用的语句
菜单:Tools -> Preferences -> User Interface -> Editor -> AutoReplace

个人配置为:
i=INSERT INTO
s=SELECT
u=UPDATE
d=DELETE
sf = SELECT * FROM
sc = SELECT COUNT(*) FROM
cnt = COUNT(*)

六、为常用操作设置快捷键
菜单:Tools -> Preferences -> User Interface -> Key Congfiguration

个人配置为:
新建 Sql Windows Shfit + Ctrl + s
新建 Command Windows Shfit + Ctrl + w
关闭当前窗口 Ctrl+ w

七、记录历史登录的口令,这样就不用每次都输入口令了
菜单:Tools -> Preferences -> Oracle -> Logon History

[转]TCP的三次握手和四次分手

原文地址:简析TCP的三次握手与四次分手

下面是最核心的一张图:

三次握手过程

  1. 第一次握手:建立连接。客户端发送连接请求报文段,将SYN位置为1,Sequence Number为x;然后,客户端进入SYN_SEND状态,等待服务器的确认;
  2. 第二次握手:服务器收到SYN报文段。服务器收到客户端的SYN报文段,需要对这个SYN报文段进行确认,设置Acknowledgment Number为x+1(Sequence Number+1);同时,自己自己还要发送SYN请求信息,将SYN位置为1,Sequence Number为y;服务器端将上述所有信息放到一个报文段(即SYN+ACK报文段)中,一并发送给客户端,此时服务器进入SYN_RECV状态;
  3. 第三次握手:客户端收到服务器的SYN+ACK报文段。然后将Acknowledgment Number设置为y+1,向服务器发送ACK报文段,这个报文段发送完毕以后,客户端和服务器端都进入ESTABLISHED状态,完成TCP三次握手。

四次分手过程

当客户端和服务器通过三次握手建立了TCP连接以后,当数据传送完毕,肯定是要断开TCP连接的啊。那对于TCP的断开连接,这里就有了神秘的“四次分手”。

  1. 第一次分手:主机1(可以使客户端,也可以是服务器端),设置Sequence Number和Acknowledgment Number,向主机2发送一个FIN报文段;此时,主机1进入FIN_WAIT_1状态;这表示主机1没有数据要发送给主机2了;
  2. 第二次分手:主机2收到了主机1发送的FIN报文段,向主机1回一个ACK报文段,Acknowledgment Number为Sequence Number加1;主机1进入FIN_WAIT_2状态;主机2告诉主机1,我也没有数据要发送了,可以进行关闭连接了;
  3. 第三次分手:主机2向主机1发送FIN报文段,请求关闭连接,同时主机2进入CLOSE_WAIT状态;
  4. 第四次分手:主机1收到主机2发送的FIN报文段,向主机2发送ACK报文段,然后主机1进入TIME_WAIT状态;主机2收到主机1的ACK报文段以后,就关闭连接;此时,主机1等待2MSL后依然没有收到回复,则证明Server端已正常关闭,那好,主机1也可以关闭连接了。

按照SQL的方式操作PLSQL中的集合变量

直接上程序代码,唯一感觉不爽的地方就是集合类型要在数据库级别定义。根据ORACLE PL/SQL Programming书上的介绍,在Oracle 12c中,table语句支持在package中定义的集合变量,因无12c的环境尚未实际验证。

定义数据库级别的对象和集合类型

CREATE OR REPLACE TYPE obj_tj30t AS OBJECT(
  estat VARCHAR2(15),
  txt04 VARCHAR2(12),
  txt30 VARCHAR2(90)
);
CREATE OR REPLACE TYPE list_of_tj30t IS TABLE OF obj_tj30t;

使用table语句,让oracle将集合在系统内容转换为虚拟的数据库表,然后可以用sql的方式操作集合变量

DECLARE
  vt_tj30t list_of_tj30t := list_of_tj30t();
BEGIN
  vt_tj30t.extend(3);
  vt_tj30t(1) := obj_tj30t('E0001', 'STATUS01', '状态01');
  vt_tj30t(2) := obj_tj30t('E0003', 'STATUS03', '状态03');
  vt_tj30t(3) := obj_tj30t('E0005', 'STATUS05', '状态05');

-- 人工插入一条重复记录,在后面使用set函数去重
  vt_tj30t.extend();
  vt_tj30t(4) := obj_tj30t('E0005', 'STATUS05', '状态05');

  FOR v_row IN (
    SELECT *
-- 使用set函数去重
    FROM TABLE(set(vt_tj30t))
    WHERE estat > 'E0002'
    ORDER BY txt30 DESC
  ) LOOP
    DBMS_OUTPUT.PUT_LINE(v_row.txt30);
  END LOOP;
END;

Oracle存储中的集合类型测试

主要测试了Associative array和Nested Table两种类型。

create or replace procedure sp_test_collection
IS
    v_index PLS_INTEGER;
    v_string_index VARCHAR2(20);

--Associative array的作用,类似于Java中的Map对象
    TYPE ty_ht_name IS TABLE OF VARCHAR2(20) INDEX BY PLS_INTEGER;
    ht_name ty_ht_name;

--Associative array的下标,可以使字符串
    TYPE ty_ht_string IS TABLE OF VARCHAR2(20) INDEX BY VARCHAR2(10);
    ht_string ty_ht_string;

-- Nested Table定义方式和Associative array类似,只是缺少了INDEX BY部分的定义
    TYPE ty_lt_name IS TABLE OF VARCHAR2(20);
    lt_name ty_lt_name;

    TYPE ty_at_name IS VARRAY(5) OF VARCHAR2(20);
    at_name ty_at_name;
BEGIN
--Associative array的下标,可以使负值,且不连续
    ht_name(-1) := 'Unknown NAM';
    ht_name(1) := 'NAME1';
    ht_name(9) := 'NAME9';

    v_index := ht_name.FIRST;
--因下标不连续,不能使用FOR的遍历方式
    WHILE (v_index IS NOT NULL)
    LOOP
        DBMS_OUTPUT.put_line(v_index || '--' || ht_name(v_index));
        v_index := ht_name.NEXT(v_index);
    END LOOP;
    DBMS_OUTPUT.put_line('---------');

--访问字符串下标的Associative array
    ht_string('john') := 'John Kennedy';
    ht_string('obama') := 'Barack Obama ';

    DBMS_OUTPUT.put_line('记录数量:' || ht_string.COUNT);
    v_string_index := ht_string.FIRST;
    WHILE (v_string_index IS NOT NULL)
    LOOP
        DBMS_OUTPUT.put_line(v_string_index || '--' || ht_string(v_string_index));
        v_string_index := ht_string.NEXT(v_string_index);
    END LOOP;

--判断是否包含某个元素
    IF ht_string.EXISTS('john') THEN
       DBMS_OUTPUT.put_line('john is in ht_string');
    END IF;
    DBMS_OUTPUT.put_line('---------');

--Nested table, 必须初始化且Extend扩展后才能使用
    lt_name := ty_lt_name('NAME1', 'NAME2', 'NAME3');
    lt_name.extend(4);
    lt_name(4) := 'NAME4';
    lt_name(6) := 'NAME6';

    DBMS_OUTPUT.put_line('Nested table最大下标为:' || lt_name.LAST);
    DBMS_OUTPUT.put_line('Nested table当前个数为:' || lt_name.COUNT);

--Nested table extend后未赋值的数组元素,其值为NULL
    IF (lt_name(5) IS NULL) THEN
        DBMS_OUTPUT.put_line('第5个元素IS NULL');
    END IF;

    lt_name.DELETE(4);
--Nested table的某个下标的元素,赋值后再删除,不能再使用下标访问
    BEGIN
      FOR v_index1 IN lt_name.FIRST .. lt_name.LAST LOOP
          v_index := v_index1;
          DBMS_OUTPUT.put_line(v_index || '--' || lt_name(v_index));
      END LOOP;
    EXCEPTION
      WHEN OTHERS THEN
           DBMS_OUTPUT.put_line('访问第' || v_index || '个元素出错: ' || SQLERRM);
    END;
    DBMS_OUTPUT.put_line('---------');

    v_index := lt_name.FIRST;
    WHILE (v_index IS NOT NULL)
    LOOP
        DBMS_OUTPUT.put_line(v_index || '--' || lt_name(v_index));
        v_index := lt_name.NEXT(v_index);
    END LOOP;
    DBMS_OUTPUT.put_line('---------');

--varray, 必须初始化且Extend扩展后才能使用
    at_name := ty_at_name();
    at_name.EXTEND(4);
    at_name(1) := 'NAME1';
    at_name(3) := 'NAME3';

--varray extend后未赋值的数组元素,其值为NULL
    IF (at_name(2) IS NULL) THEN
        DBMS_OUTPUT.put_line('第2个元素IS NULL');
    END IF;

    DBMS_OUTPUT.put_line('varray最大个数为:' || at_name.LIMIT);
    DBMS_OUTPUT.put_line('varray当前个数为:' || at_name.COUNT);

--varray 有删除操作后,其索引仍然是连续的
    FOR v_index IN at_name.FIRST .. at_name.LAST LOOP
        DBMS_OUTPUT.put_line(v_index || '--' || at_name(v_index));
    END LOOP;
end sp_test_collection;

Oracle存储过程中的ORA-01031错误分析

在调用存储过程中,需要使用EXECUTE IMMEDIATE的方式动态创建临时表,运行的时候发生了下面的错误:

ORA-01031:insufficient privileges

但奇怪的是,同样的语句,在SQL窗口执行正常。

这种现象的原因,涉及到Oracle对用户权限的处理,在Oracle中用户的权限分为三类:
1. 系统权限:对于数据库某一类对象的权限,如创建表、更改表等操作,具体的权限清单可以在SYSTEM_PRIVILEGE_MAP表中查询到。
说明:权限的名称中如含有ANY,表示权限不限当前的Schema。
2. 对象权限:对于特定数据库对象(如表、视图、、触发器、函数、存储过程等)的权限(如SELECT、INSERT、UPDATE、DELETE等),授权方式为:

GRANT SELECT ON TABLE TO USERA;

3. 角色权限:用户的系统权限和对象权限都是直接赋予用户的,为了简化权限的管理,可以把相同的权限赋予某个角色,然后把角色赋予用户,这样用户就间接的拥有了角色的权限。Oracle默认已经设置了几个角色,常用的是CONNECT、RESOURCE、DBA等。

调用存储过程中的权限机制,根据网上的资料分析总结,在存储过程的内部,用户所具有的权限是存储过程的Owner的系统权限和对象权限,不包括角色权限
在最前面所说的问题,就是用户创建表的权限是在角色权限中,所以在SQL窗口中可以执行,但在存储过程中无法执行。

处理方法:
1. 在存储过程的声明语句增加AUTHID CURRENT_USER语句,Oracle判断存储过程权限变为调用者的所有权限,不再是存储过程Owner的权限。
2. 给用户增加CREATE TABLE的系统权限

权限相关的表:
用户拥有的系统权限 USER_SYS_PRIVS
用户拥有的对象权限 USER_TAB_PRIVS
用户拥有的角色 USER_ROLE_PRIVS

系统内的角色 DBA_ROLES
角色拥有的系统权限 ROLE_SYS_PRIVS
角色拥有的角色权限 ROLE_TAB_PRIVS

权限清单 SYSTEM_PRIVILEGE_MAP

参考资料:
Oracle 用户、对象权限、系统权限

Sqlserver版本的wm_concat

表定义:

create table test(
  date date,
  flag int
)

按照日期的年月日汇总,flag列用逗号分隔,类似Oracle中的wm_concat和listagg功能,可以使用下面的语句:

select
  CONVERT(VARCHAR(100), date, 23),
  flags=stuff((select ','+convert(varchar(10),flag) from test t where CONVERT(VARCHAR(100), date, 23)=CONVERT(VARCHAR(100),test.date,23) for xml path('')), 1, 1, '')
from test
group by CONVERT(VARCHAR(100), date, 23)

Oracle登录触发器应用

项目使用一个新建的用户开发应用,但应用的SQL语句中没有写SCHEMA的名字。
项目上线后这个用户权限太大,能够创建和修改表和结构和数据,因此新建了一个查询用户,使用后发现原有的程序因为没有SCHEMA的名字无法正常运行。为了不对已经完工的程序、视图、存储过程等做大范围的修改,在登录后将用户当前的SCHEMA进行了修改。

创建查询用户

GRANT CONNECT TO USERA;
GRANT SELECT ANY TABLE TO USERA;
GRANT EXECUTE ANY PROCEDURE TO USERA;

更改登录后的SCHEMA

create or replace trigger set_default_schema
after logon on DATABASE
begin
   IF (SYS_CONTEXT ('USERENV', 'SESSION_USER') = 'USERA')
   THEN
     EXECUTE IMMEDIATE 'alter session set current_schema=USERB';
   END IF;
end;

数据库设计原则–不要删除数据

摘自Segment的问题回复,个人认为是对数据库中删除操作最好的诠释,大部分情况下我们是滥用删除或删除标记,真实情况中大部分情况我们只需修改记录的状态即可。因此在做数据库设计中,我们决定使用删除或删除标记的时候,我们需要再思考一次:在业务上,这真的是删除操作吗?

Udi Dahan 强烈建议完全避免数据删除。

所谓软删除主张在表中增加一个 IsDeleted 列以保持数据完整。如果某一行设置了IsDeleted标志列,那么这一行就被认为是已删除的。Ayende 觉得这种方法“简单、容易理解、容易实现、容易沟通”,但“往往是错的”。问题在于:

删除一行或一个实体几乎总不是简单的事件。它不仅影响模型中的数据,还会影响模型的外观。所以我们才要有外键去确保不会出现“订单行”没有对应的父“订单”的情况。而这个例子只能算是最简单的情况。……

当采用软删除的时候,不管我们是否情愿,都很容易出现数据受损,比如谁都不在意的一个小调整,就可能使“客户”的“最新订单”指向一条已经软删除的订单。

如果开发者接到的要求就是从数据库中删除数据,要是不建议用软删除,那就只能硬删除了。为了保证数据一致性,开发者除了删除直接有关的数据行,还应该级联地删除相关数据。可Udi
Dahan提醒读者注意,真实的世界并不是级联的:

假设市场部决定从商品目录中删除一样商品,那是不是说所有包含了该商品的旧订单都要一并消失?再级联下去,这些订单对应的所有发票是不是也该删除?这么一步步删下去,我们公司的损益报表是不是应该重做了?

没天理了。

问题似乎出在对“删除”这词的解读上。Dahan 给出了这样的例子:

我说的“删除”其实是指这产品“停售”了。我们以后不再卖这种产品,清掉库存以后不再进货。以后顾客搜索商品或者翻阅目录的时候不会再看见这种商品,但管仓库的人暂时还得继续管理它们。“删除”是个贪方便的说法。

他接着举了一些站在用户角度的正确解读:

  • 订单不是被删除的,是被“取消”的。订单取消得太晚,还会产生花费。
  • 员工不是被删除的,是被“解雇”的(也可能是退休了)。还有相应的补偿金要处理。
  • 职位不是被删除的,是被“填补”的(或者招聘申请被撤回)。

在上面这些例子中,我们的着眼点应该放在用户希望完成的任务上,而非发生在某个
实体身上的技术动作。几乎在所有的情况下,需要考虑的实体总不止一个。

为了代替 IsDeleted 标志,Dahan 建议用一个代表相关数据状态的字段:有效、停用、取消、弃置等等。用户可以借助这样一个状态字段回顾过去的数据,作为决策的依据。

删除数据除了破坏数据一致性,还有其它负面的后果。Dahan建议把所有数据都留在数据库里:“别删除。就是别删除。”

—— 《NoSQL数据库笔谈》

 

Oracle分析函数三(first,last,first_value,last_value)

一、创建表,初始化数据

CREATE TABLE t_emp (
  emp_no VARCHAR2(10),
  emp_name VARCHAR2(30),
  dept_no VARCHAR2(10),
  emp_salary NUMBER
);

INSERT INTO t_emp VALUES('001', '张三',   '10', 2000);
INSERT INTO t_emp VALUES('002', '李四',   '10', 3000);
INSERT INTO t_emp VALUES('003', '王五',   '10', 1500);
INSERT INTO t_emp VALUES('004', '赵六',   '10', 5000);
INSERT INTO t_emp VALUES('005', '王麻子', '20', 4000);
INSERT INTO t_emp VALUES('006', '陈中华', '20', 8000);
INSERT INTO t_emp VALUES('007', '赵无极', '20', 6000);
INSERT INTO t_emp VALUES('008', '田丰',   '10', 5000);

二、取部门工资最小和最大的人

SELECT dept_no,
  MIN(emp_name) keep (dense_rank FIRST ORDER BY emp_salary) min_salary_person,
  MIN(emp_name) keep (dense_rank LAST ORDER BY emp_salary)  max_salary_person,
  wm_concat(emp_name) keep (dense_rank FIRST ORDER BY emp_salary) min_salary_person,
  wm_concat(emp_name) keep (dense_rank LAST ORDER BY emp_salary)  max_salary_person
FROM t_emp
GROUP BY dept_no

运行结果:

DEPT_NO MIN_SALARY_PERSON MAX_SALARY_PERSON MIN_SALARY_PERSON MAX_SALARY_PERSON
10 王五 田丰 王五 赵六,田丰
20 王麻子 陈中华 王麻子 陈中华

三、列出部门所有的人,并增加本部门工资最小和最大人的名字
注意last_value函数,使用order by会加上一个默认窗口RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW,必须修改为窗口的全部记录,否则

SELECT dept_no, emp_name, emp_salary,
  first_value(emp_name) over (PARTITION BY dept_no ORDER BY emp_salary) min_salary_person,
  last_value(emp_name) over (PARTITION BY dept_no ORDER BY emp_salary rows between unbounded preceding and unbounded following) max_salary_person
FROM t_emp

运行结果:

DEPT_NO EMP_NAME EMP_SALARY MIN_SALARY_PERSON MAX_SALARY_PERSON
10 王五 1500 王五 赵六
10 张三 2000 王五 赵六
10 李四 3000 王五 赵六
10 田丰 5000 王五 赵六
10 赵六 5000 王五 赵六
20 王麻子 4000 王麻子 陈中华
20 赵无极 6000 王麻子 陈中华
20 陈中华 8000 王麻子 陈中华