sql 笔记（转）

转载自：https://gitee.com/Lanmengye/SQL_Learning/blob/master/PLSQL%E5%9F%BA%E7%A1%80.md#

PLSQL变量与类型

替代变量

在PLSQL块中可以使用替代变量来提示用户输入参数，相当于占位符，在程序运行过程中将直接用输入值替换内容，当需要输入字符串类型参数时需要用单引号将替代变量引起来，数值型不需要加单引号，替代变量举例：

    select &columns from employees;

绑定变量

    -- execute immediate [带绑定变量的目标sql] using [对应绑定变量的具体输入值];
    declare 
      last_name employees.last_name%type;
    begin
      execute immediate 'select last_name from employees where employee_id = :i' into last_name using 100;
      dbms_output.put_line(last_name);
    end;

类型

属性类型

属性类型是一种可以直接引用数据库中列的数据类型来描述变量类型的类型。Oracle 提供了两种属性类型，分别是%TYPE和%ROWTYPE
- %TYPE：该属性允许在声明中引用数据库中的列或先前声明的变量数据类型，而不是硬编码类型名称。
- %ROWTYPE：该属性可以表示数据库中表或游标的行的记录类型。
```
v_name employees.last_name %type; -- 动态类型，与employees的last_name字段类型保持一致
v_emp employees%rowtype; -- 定义包含employees表中所有列的变量
```
记录类型

记录类型是由单行多列标量构成的复合结构。可以看做是一种用户自定义的数据类型，提供了将一个或多个标量封装成一个对象进行操作的能力。在使用记录数据类型的变量时，需要在声明部分先定义记录的成员变量，然后在执行部分引用该记录变量本身或其中的成员。但不可以对记录做整体性的比较运算，如判断记录类型的变量是否为 NULL。
```
type emp_record is record(   -- 定义记录类型
	v_name employees.last_name %type,
    v_salary employees.salary %type
);
v_emp_record emp_record;  -- 定义类型为 emp_record 的变量
```

集合类型 VARRAY 和 TABLE（待补充）

declare
  type emp_name_tab is table of employees.last_name%type index by binary_integer;           -- 定义表变量类型
  type emp_department_tab is table of employees.department_id%type index by binary_integer; -- 定义表变量类型

  emp_name_t emp_name_tab;             -- 定义表变量
  emp_department_t emp_department_tab; -- 定义表变量

  cursor emp_cur is select last_name, department_id from employees where employee_id <=114;
  i number :=0;

begin
  for emp_record in emp_cur loop
    emp_name_t(i) := emp_record.last_name;               -- 表变量赋值
    emp_department_t(i) := emp_record.department_id;     -- 表变量赋值
    i := i+1;
  end loop;

  for i in 0..14 loop
     dbms_output.put_line('Employee Name:' || emp_name_t(i) ||
                         ' Department_id: ' || emp_department_t(i)); --使用表变量
  end loop;
end;

PLSQL流程控制

判断语句

if...elsif... else

declare 
  v_emp number;
begin
 select t.employee_id into v_emp from employees t where t.employee_id=&emp;
 if v_emp=100 then					-- if 布尔表达式 then 语句1;
    DBMS_OUTPUT.PUT_LINE('员工ID1：'||v_emp);	
 elsif v_emp=101 then				-- elsif 布尔表达式 then 语句2;
    DBMS_OUTPUT.PUT_LINE('员工ID2：'||v_emp);
 else								-- else 语句3
    DBMS_OUTPUT.PUT_LINE('其他情况');
 end if;							-- end if;
end;

case when

-- SQL语句中的case when
-- 连续值判断： case when 布尔表达式 then ......
select e.employee_id,
       e.salary old_salary,
       case
         when e.salary >= 15000 and e.salary < 20000 then 0.05
         when e.salary >= 10000 and e.salary < 15000 then 0.08
         when e.salary >= 8000 and e.salary < 10000 then 0.1
         else 0.15
       end raise1_percent
  from employees e;
  
-- 等值（不连续值）判断： case 列名/变量名 when 值 then ...
select 
case employee_id when 100 then 'Steven King'
                 when 101 then 'Neena Kochhar'
                 when 102 then 'Lex De Haan'
                 else 'unkown' end 
from employees;

-- 在存储过程中，case when 语句最后以 end case 结尾
declare  
 grade char(1) := 'B'; 
BEGIN 
   case  
      when grade = 'A' then dbms_output.put_line('Excellent'); 
      when grade = 'B' then dbms_output.put_line('Very good'); 
      when grade = 'C' then dbms_output.put_line('Well done'); 
      when grade = 'D' then dbms_output.put_line('You passed'); 
      when grade = 'F' then dbms_output.put_line('Better try again'); 
      else dbms_output.put_line('No such grade'); 
   end case; 
END;

decode函数

-- decode 函数只能用来做等值判断
-- decode(列名/变量名，值1，结果1，值2，结果2……)
select decode(employee_id,
              100,'Steven King',
              101,'Neena Kochhar',
              102,'Lex De Haan')
  from employees;

循环语句

loop基本循环

-- loop循环
declare
  cursor employee_ids is select employee_id from employees;
  v_i employees.employee_id%type;
begin
  open employee_ids;                     
  loop                                    -- 执行循环
    fetch employee_ids into v_i; 
    dbms_output.put_line(v_i);
    exit when employee_ids%rowcount >= 10;-- 当条件成立时退出循环
  end loop;                               -- 结束循环
end;

declare
  v_i    int := 1;                       -- 定义循环变量    
  v_id   employees.employee_id%type;
  v_name employees.last_name%type;
begin
  loop                                   -- 开始循环
    select tab.employee_id, tab.last_name
      into v_id, v_name
      from (select e.employee_id, e.last_name, rownum rn
              from employees e
             order by e.employee_id asc) tab
     where rn = v_i;
    dbms_output.put_line(v_id || ':' || v_name);
    exit when v_i >= 10;                 -- 条件满足时退出循环
    v_i := v_i + 1;                      -- 改变循环变量   
  end loop;                              -- 结束循环
end;

while循环

declare
  v_i    int := 1;                       -- 定义循环变量    
  v_id   employees.employee_id%type;
  v_name employees.last_name%type;
begin
  while v_i <= 10 loop                   -- 满足条件开始循环
    select tab.employee_id, tab.last_name
      into v_id, v_name
      from (select e.employee_id, e.last_name, rownum rn
              from employees e
             order by e.employee_id asc) tab
     where rn = v_i;
    dbms_output.put_line(v_id || ':' || v_name);
    v_i := v_i + 1;                      -- 改变循环变量   
  end loop;                              -- 结束循环
end;

for循环

declare 
  v_id   employees.employee_id%type;
  v_name employees.last_name%type;
begin
  for v_i in 1..10 loop                 -- 开始循环
    select tab.employee_id, tab.last_name
      into v_id, v_name
      from (select e.employee_id, e.last_name, rownum rn
              from employees e
             order by e.employee_id asc) tab
     where rn = v_i;
    dbms_output.put_line(v_id || ':' || v_name);  
  end loop;                              -- 结束循环
end;

PLSQL游标

显式游标

/*
游标使用注意事项：
	(1)使用完成之后一定要关闭游标； 
	(2)不能关闭已经被关闭的游标； 
    (3)能不使用游标就不要使用游标，因为游标效率十分低下。
*/

-- 显式游标
-- loop循环方式
declare
   cursor dept_cursor is select department_id, department_name from test_cursor; --定义游标
   v_dept_id test_cursor.department_id%type;
   v_dept_name test_cursor.department_name%type;
begin
  open dept_cursor; -- 2.打开游标
  loop
    fetch dept_cursor into v_dept_id,v_dept_name; -- 3. 提取当前行数据
    dbms_output.put_line(v_dept_id||': '||v_dept_name);
    exit when dept_cursor%rowcount > 10;
    end loop;
    close dept_cursor;  -- 4. 关闭游标
end;

-- for循环方式
-- 使用子查询形式遍历
begin
  for v_record in ( select * from employees )
    loop
    dbms_output.put_line(v_record.employee_id||': '||v_record.last_name||': '||v_record.salary);
  end loop;
end;
-- 使用游标方式遍历
declare 
  cursor emp_cursor is select * from employees;
begin
  for v_record in emp_cursor
    loop
    dbms_output.put_line(v_record.employee_id||': '||v_record.last_name||': '||v_record.salary);
  -- exit when emp_cursor%rowcount > 10; --只打印10行
  end loop;
end;

隐式游标

-- 隐式游标：默认名称sql
-- 游标的四个重要属性：found, notfound, rowcount,isopen
-- 隐式游标的isopen属性永远为false
declare 
begin
  update test_cursor set manager_id = 108 where department_id between 1000 and 2000;
  
  if sql%found then
    dbms_output.put_line('共更新数据'||sql%rowcount||'条');
  else
    dbms_output.put_line('未找到要更新的数据');
  end if;    
end;

带参游标

-- 带参游标：与显示游标的区别是可以带参数，从而根据where条件划定范围 
declare
   cursor dep_by_id_cursor(p_id number default 10) is 
          select t.* from test_cursor t where t.department_id <= p_id;  
begin
  dbms_output.put_line('游标不传参测试：');
  for v_dep in dep_by_id_cursor loop --不传参，取默认值10
    dbms_output.put_line(v_dep.department_id||': '||v_dep.department_name);
  end loop;  
  dbms_output.put_line('游标传参测试：');
  for v_dep in dep_by_id_cursor(100) loop --传参
      dbms_output.put_line(v_dep.department_id||': '||v_dep.department_name);
  end loop;
end;

REF游标

弱类型REF游标

/*
    REF游标和游标变量用于处理运行时动态执行的SQL查询，创建游标变量需要两个步骤：
    1）声明REF游标类型
    2）声明REF游标类型变量 
*/
-- REF游标：打开游标时动态指定具体结果集，不能使用for循环遍历
-- 弱类型REF游标
declare 
   type t_ref_cursor is ref cursor; -- 声明ref游标类型
   v_cursor t_ref_cursor; -- 声明ref游标类型变量
   v_dept departments%rowtype;
   v_emp employees%rowtype;
begin
  -- 打印departments表10前行数据
  dbms_output.put_line('打印departments表前10行数据:');
  open v_cursor for select * from departments; --打开游标时指定

  loop
    fetch v_cursor into v_dept;
    dbms_output.put_line(v_dept.department_id ||': '|| v_dept.department_name);
  exit when v_cursor%rowcount > 10;
  end loop;
  close v_cursor;
  
  -- 打印employees表前10行数据
  dbms_output.put_line('打印employees表前10行数据:');
  open v_cursor for select * from employees;
  loop
    fetch v_cursor into v_emp;
    dbms_output.put_line(v_emp.employee_id ||': '|| v_emp.last_name);
  exit when v_cursor%rowcount > 10;
  end loop;
  close v_cursor;
 
end;

强类型REF游标

-- 强类型REF游标【预先指定查询类型与返回类型】
-- 参考 https://www.cnblogs.com/nick-huang/p/4609100.html
declare

    Type ref_cur_emp IS REF CURSOR RETURN scott.emp%RowType;
    cur_emp ref_cur_emp;
    rec_emp cur_emp%RowType;

    v_sql varchar2(100) := 'select * from scott.emp t';

begin
    -- xxx Open cur_emp For v_sql;
    Open cur_emp For
        select * from scott.emp t;
    Loop
        fetch cur_emp
            InTo rec_emp;
        Exit When cur_emp%NotFound;
        dbms_output.put_line(cur_emp%rowcount || ' -> ' || rec_emp.empno ||
                             '   ' || rec_emp.sal);
    End Loop;
    Close cur_emp;

end;

知识点补充

-- 游标补充点：更新删除数据
select * from test_cursor for update;
select t.*, rowid from test_cursor t;

SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID = 80;

DECLARE
  CURSOR emp_cursor IS
    (SELECT employee_id, last_name, department_name
      FROM employees, departments
     WHERE employees.department_id = departments.department_id
       AND employees.department_id = 80)
       FOR UPDATE OF salary NOWAIT; -- 当没有获得资源报错
BEGIN
  for v_emp in emp_cursor loop
    update employees set salary = salary * 1.1 where current of emp_cursor; 
    --where current of 条件指定游标所指的当前行（多表时可能会匹配不到）
  END LOOP; 
END;

PLSQL块

匿名块

-- 匿名块只能执行一次，不能被调用
declare -- 声明变量部分
  v_test employees%rowtype;
  
begin -- 逻辑处理主体部分 
  select * into v_test from employees where EMPLOYEE_ID = &empid;
  
  dbms_output.put_line('EMPLOYEE_ID :'|| v_test.EMPLOYEE_ID);
  dbms_output.put_line('LAST_NAME :' || v_test.LAST_NAME);
  
  -- 异常处理部分
exception
  when NO_DATA_FOUND then dbms_output.put_line('无效的员工ID');
end;

存储过程

/*
一组为了完成特定功能的SQL 语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存储过程。
*/
-- 创建存储过程
CREATE OR REPLACE PROCEDURE RAISE_SALARY(BASE_SALARY NUMBER)
 -- 存储过程声明部分（包括存储过程名[(参数1 类型1，……)]）
IS
 -- 变量声明部分
BEGIN --逻辑处理主体部分 
  UPDATE PLSQL_EMPLOYEES SET RAISE_SALARY = SALARY * 1.1 WHERE SALARY < BASE_SALARY;
  COMMIT;
  --可能会有异常处理部分
END RAISE_SALARY;  

-- 调用存储过程
begin
  RAISE_SALARY(24000);
end;

函数

/*
函数用于返回特定数据。执行时需要有一个变量接收函数的返回值。存储在数据库中，
经过第一次编译后再次调用不需要再次编译，用户通过指定函数的名字并给出参数（如果该函数带有参数）来执行它。
*/
create or replace function get_sal(p_id in employees.employee_id%type)
  return number 
  -- 存储函数声明部分（包括存储函数名[(参数1 类型1，……)] return 返回类型）
  is
  -- 变量声明部分
  v_salary employees.salary%type := 0;

begin --逻辑处理主体部分 

  select salary into v_salary from employees where employee_id = p_id;
  return v_salary;
  
  --可能会有异常处理部分
end get_sal;

知识点补充

过程与函数的区别

in、out 和 in out模式参数区别
- in: 用于接受调用程序的值，不能赋值；默认的参数模式；可以是常量、变量、表达式。
- out: 用于向调用程序返回值；必须显式指定值，接收不到传入的值；必须是变量。
- in out: 用于接受调用程序的值，并向调用程序返回更新后的值；必须显式指定，必须是变量。
存储过程和函数中不能直接使用DML语句可以转化为
```
execute immediate '要执行的sql语句';
```

PLSQL异常

在运行程序时出现的错误叫做异常。发生异常后，语句将停止执行，控制权转移到PL/SQL块的异常处理部分。异常有两种类型：

预定义异常：当PL/SQL程序违反Oracle规则或超越系统限制时隐式引发。
用户定义异常：用户可以在PL/SQL块的声明部分定义异常，自定义的异常通过RAISE语句显式引发。

预定义异常

异常	说明
ACCESS_INTO_NULL	在未初始化对象时出现
CASE_NOT_FOUND	在case语句中的选项与用户输入的数据不匹配时出现
COLLECTION_IS_NULL	在给尚未初始化的表或数据赋值时出现
CURSOR_ALREADY_OPEN	在用户试图打开已经打开的游标时出现。
INVALID_CURSOR	执行非法游标运算时出现
TOO_MANY_ROWS	在执行select into语句后返回多行时出现。
ZERO_DIVIDE	以零作除数时出现
VALUE_ERROR	产生大小限制错误时出现。如：变量中的列值超出变量的大小。
NO_DATA_FOUND	在表中不存在请求的行时出现。

BEGIN
. . .
EXCEPTION
WHEN NO_DATA_FOUND THEN     -- 总是需要考虑的两种异常：NO_DATA_FOUND | TOO_MANY_ROWS
statement1;
WHEN TOO_MANY_ROWS THEN
statement1;
WHEN OTHERS THEN
statement1;
statement2;
END;

用户自定义异常

DECLARE
e_invalid_department EXCEPTION;					-- 定义异常
BEGIN
UPDATE departments
SET department_name = &p_department_desc
WHERE department_id = &p_department_number;
IF SQL%NOTFOUND THEN
RAISE e_invalid_department;						-- 使用raise关键字抛出异常
END IF;
COMMIT;
EXCEPTION
WHEN e_invalid_department THEN					-- 处理异常
DBMS_OUTPUT.PUT_LINE('No such department id.');
END;

/*
RAISE_APPLICATION_ERROR() 函数：对于用户自定义的业务错误，如果觉得先定义再使用很麻烦，那么也可以简单的使用raise_application_error() 来简化处理。它可以无需预先定义错误，而在需要抛出错误的地方直接使用此函数抛出例外，例外可以包含用户自定义的错误吗和错误描述。
*/
-- 执行部分
BEGIN
...
DELETE FROM employees
WHERE manager_id = v_mgr;
IF SQL%NOTFOUND THEN
RAISE_APPLICATION_ERROR(-20202,
'This is not a valid manager');
END IF;
...
-- 异常处理部分
...
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR (-20201,
'Manager is not a valid employee.');
END;

PLSQL包与JOB

包

CREATE OR REPLACE PACKAGE EMP_SALARY_ADJ IS 
-- 包声明部分（包含包中的存储函数、存储过程的声明）
-- 在包声明部分声明的存储函数和存储过程必须在包主体中定义
-- 只有在包声明部分声明了的函数或过程才能在外部使用 【包名.函数名/过程名】进行调用
  FUNCTION GET_RAISE_PER_BY_JOB_CHG(P_JOB_CHG NUMBER) RETURN NUMBER;
  PROCEDURE SALARY_ADJ_BASE;

END EMP_SALARY_ADJ;

CREATE OR REPLACE PACKAGE BODY EMP_SALARY_ADJ IS
-- 包主体部分（包含包声明的函数和过程的具体定义以及包中未声明的函数和存储过程）
  FUNCTION GET_RAISE_PER_BY_JOB_CHG(P_JOB_CHG NUMBER) RETURN NUMBER IS
    RESULT NUMBER := 0;
  BEGIN
    ......
  END GET_RAISE_PER_BY_JOB_CHG;

  PROCEDURE SALARY_ADJ_BASE IS
  BEGIN
    ......
  END SALARY_ADJ_BASE;

  PROCEDURE SALARY_ADJ_JOB_CHG IS
  BEGIN
    MERGE INTO EMP_SALARY_ADJUST S
    USING (SELECT H.EMPLOYEE_ID, COUNT(1) AS JOB_CHG
             FROM JOB_HISTORY H
            GROUP BY H.EMPLOYEE_ID) Q
    ON (S.EMPLOYEE_ID = Q.EMPLOYEE_ID)
    WHEN MATCHED THEN
      UPDATE
         SET S.CHANGED_COUNT  = Q.JOB_CHG,
             S.RAISE2_PERCENT = GET_RAISE_PER_BY_JOB_CHG(Q.JOB_CHG);
    COMMIT;
  END SALARY_ADJ_JOB_CHG;

END EMP_SALARY_ADJ;

JOB

-- job的创建
declare
  jobno number;
begin
    dbms_job.submit(
    jobno,
    'p_dosomething', --what
     to_date(), --next_date，可以不填
     'Interval时间字符串' --interval，关键设置
);
commit;
end;

declare
  testjobno number;
begin
  dbms_job.submit(
  testjobno,
  'ADD_DEPT;', --what
  SYSDATE, --next_date，可以不填
  interval => 'ADD_MONTHS(SYSDATE , 6)' --interval，关键设置
);
commit;
DBMS_JOB.RUN(jobno);
end;

PLSQL触发器

触发器概念

触发器是当特定事件出现时自动执行的存储过程，特定事件可以是执行更新的DML语句和DDL语句，触发器不能被显式调用。

触发器的功能：

自动生成数据
自定义复杂的安全权限
提供审计和日志记录
启用复杂的业务逻辑

触发器组成部分

触发器类型

DML触发器示例

CREATE TABLE test_trigger(id number(4), name varchar2(20));

-- 创建触发器
create or replace trigger hello_trigger
       after insert or delete or update on test_trigger for each row --什么时候在对哪张表进行什么操作时触发
begin
  IF INSERTING  --执行INSERT操作时 自动运行以下语句
   THEN 
      DBMS_OUTPUT.put_line ('I'); 
      DBMS_OUTPUT.put_line ('insert batch_id='||:new.id); 
   ELSIF UPDATING --执行UPDATE操作时 自动运行以下语句
   THEN 
      DBMS_OUTPUT.put_line ('U'); 
      DBMS_OUTPUT.put_line ('update old last_name='||:old.name); 
      DBMS_OUTPUT.put_line ('update new last_name='||:new.name); 
   ELSIF DELETING --执行DELETE操作时 自动运行以下语句
   THEN 
      DBMS_OUTPUT.put_line ('D'); 
      DBMS_OUTPUT.put_line ('delete employee_id ='||:old.id);    
   END IF; 
END;   

-- 测试触发器
insert into test_trigger(id, name) values(1111, 'aaaa');
insert into test_trigger(id, name) values(2222, 'bbbb');
update test_trigger set name = 'cccc' where id = 2222;
delete from test_trigger where id = 2222;

插入或修改数据

insert

-- insert into 后面可以接select子句，从而将某些表的数据插入到目标表中
insert into emp_salary_adjust 
    select s.employee_id, s.salary, raise_per_by_salary(s.salary) raise1_percent,
           0 changed_count,
           0 raise2_precent,
           0 raise3_precent,
           0 raise_salary,
           0 new_salary
    from employees s
    where s.salary < 20000;

merge into

merge into emp_salary_adjust t1    -- 需要更新的表
using(							   -- 数据来源（用什么数据去更新）	 	
  select distinct h.employee_id,
       count(1) over(partition by h.employee_id) changed_count,
       case when count(1) over(partition by h.employee_id)>=3 then 0.2
        when count(1) over(partition by h.employee_id) = 2 then 0.1
        when count(1) over(partition by h.employee_id) = 1 then 0.05 end raise2_percent   
  from job_history h 
 ) t2
on (t1.employee_id = t2.employee_id) -- 根据什么键去匹配更新  
when matched then					 -- 匹配到该进行什么操作（一般是update）
update  
   set t1.raise2_percent  = t2.raise2_percent
when not matched then				 -- 匹配不到该进行什么操作（一般是insert）
   insert (t1.EMPLOYEE_ID) values(1000);

关于存储过程或函数不能写DDL语句的解决办法

execute immediate

-- 在过程或函数中不能直接编写DDL语句（create/alter/drop/truncate）
-- 可以使用execute immediate sqlstr的方式在过程或函数中执行DDL语句
execute immediate 'truncate table test';

使用trunc函数处理日期类型

select trunc(sysdate) from dual  --2011-3-18  今天的日期为2011-3-18 
select trunc(sysdate, 'mm')   from   dual  --2011-3-1    返回当月第一天. 
select trunc(sysdate,'yy') from dual  --2011-1-1       返回当年第一天 
select trunc(sysdate,'dd') from dual  --2011-3-18    返回当前年月日 
select trunc(sysdate,'yyyy') from dual  --2011-1-1   返回当年第一天 
select trunc(sysdate,'d') from dual  --2011-3-13 (星期天)返回当前星期的第一天 
select trunc(sysdate, 'hh') from dual   --2011-3-18 14:00:00   当前时间为14:41    
select trunc(sysdate, 'mi') from dual  --2011-3-18 14:41:00   TRUNC()函数没有秒的精确

相关阅读:
在独立的文件里定义WPF资源
 Irrlicht 3D Engine 笔记系列之教程6- 2D Graphics
Java实现二叉树的创建、递归/非递归遍历
 NDK在windows下的开发环境搭建及开发过程
 硬件路由转发原理浅析
 ubuntu下vim中内容拷贝到浏览器
 python调用Java代码，完毕JBPM工作流application
C++组合通信
 linux杂谈（十八）:DNSserver的配置（一）
Codeforces 550D. Regular Bridge 构造
原文地址：https://www.cnblogs.com/iupoint/p/14010482.html