Oracle基础

Oracle数据库与实例区分

Oracle数据库是存在电脑磁盘中的文件 实例是存在内存中的进程 我们是通过操作实例间接操作数据库的

我们操作结果都存在内存缓存中，当我们提交事务时，才将修改数据记录到位于磁盘中的数据库文件中

事务、保存点、回滚

事务是一系列操作 保存点(savepoint)在这系列操作中设置节点 在事务未提交前可以通过回滚(rollback)操作将数据恢复到节点前的状态

数据字典

可以根据数据字典查看数据库相关信息

Oracle函数

包含、模糊查询

SELECT * FROM student WHERE CLASS BETWEEN 2 AND 3;
SELECT * FROM Student WHERE CLASS IN(2,3);
SELECT * FROM student WHERE NAME LIKE '%o_';

字符函数

SELECT UPPER(NAME) FROM student;
SELECT * FROM student WHERE NAME=INITCAP('tom');
SELECT * FROM student WHERE LOWER(NAME)='tom';
--连接、截取
SELECT CONCAT(NAME,CLASS) FROM student;
SELECT SUBSTR(CONCAT(NAME,CLASS),1,3) FROM student;
-- 填充
SELECT LPAD(NAME,10,'#') FROM student;
SELECT RPAD(NAME,10,'#') FROM student;

数值函数

SELECT ROUND(age,-1) FROM student;--四舍五入
SELECT TRUNC(age,-1) FROM student;--截取 通通舍
SELECT MOD(age,4) FROM student;--求模

日期函数

SELECT t.*,to_char(sysdate-1,'yyyy-MM-dd') FROM student t;--系统时间
SELECT ROUND(months_between(SYSDATE,DATA)) FROM student;--日期间相差月数

转换函数

SELECT to_date('2018-10-09','yyyy-MM-dd') AS DATA FROM student;
SELECT to_char(sysdate-1,'dd"日"MM"月"yy"年"') AS DATA FROM student;
SELECT to_number(age) AS DATA FROM student;

通用函数

SELECT age+NVL(CLASS,0) FROM student;--nvl(a,xx)a为空 取XX
SELECT NVL2(CLASS,age+CLASS,age) FROM student;--nvl2(a,b,c) a为空取c 不为空取b
SELECT NULLIF(ID,CLASS) FROM student;--NULLIF(a,b) a、b相同取空 不同取a

条件表达式

SELECT NAME,
CASE to_char(CLASS)
  WHEN '2' THEN '二'
  WHEN '3' THEN '三'
  ELSE 'it'
END
FROM student;
SELECT NAME,
CASE
  WHEN CLASS='2' THEN '二'
  WHEN CLASS='3' THEN '三'
  ELSE 'it'
END
FROM student;
SELECT DECODE(CLASS,'2','二','3','三','it') FROM student

分组函数

avg() 平均值 sum()求和 min() 最小值 max()最大值 count() 统计 wm_concat() 行转列

层级树查询 connect by...start with

select level,t.* FROM test_bom t connect by prior t.sub_id=t.master_id start with t.master_name='V1卡车'

分组排序函数

row_number()over(partition by col1 order by col2)表示根据col1分组，在分组内部根据col2排序

存储过程、函数

存储过程没有返回值、存储函数有返回值

无参存储函数和存储过程可直接调用

创建存储过程、函数

--带输入参数的存储过程
CREATE OR REPLACE  PROCEDURE procl
(v_stuid IN student.id%TYPE)
IS
BEGIN
  DELETE FROM student WHERE ID=1;
  IF SQL%NOTFOUND THEN 
    raise_application_error(-2008,'指定学生不存在');
  ELSE
    dbms_output.put_line('删除成功');
  END IF;
END;

--带输出参数的存储过程
CREATE OR REPLACE PROCEDURE procl2
(v_class IN student.class%TYPE,v_age OUT student.age%TYPE)
IS
BEGIN
  SELECT AVG(age)
  INTO v_age --将值赋给参数
  FROM student
  WHERE CLASS=v_class;
EXCEPTION --异常处理
  WHEN NO_data_found THEN
    dbms_output.put_line('没有此班级');
  WHEN OTHERS THEN
    dbms_output.put_line(Sqlerrm);
END;

--带输入参数的存储函数
CREATE OR REPLACE FUNCTION func
(v_class IN student.class%TYPE) --参数列表
RETURN NUMBER--返回值类型
IS
  v_sum NUMBER; --局部变量
BEGIN
  SELECT SUM(age) INTO v_sum FROM student WHERE CLASS=v_class;
  RETURN v_sum;
EXCEPTION
  WHEN NO_data_found THEN
    dbms_output.put_line('没有此班级');
  WHEN OTHERS THEN
    dbms_output.put_line(Sqlerrm);
END;

--带输入输出参数的存储过程
CREATE OR REPLACE PROCEDURE proc3
(v_test IN OUT NUMBER)
IS
BEGIN
  v_test:=v_test+1;
END;

调用存储过程、函数

--调用带输出参数的存储过程
BEGIN
  procl('2');
END;

--调用带输入参数和输出参数的存储过程
DECLARE
  v_age NUMBER;
BEGIN
  procl2('2',v_age);
  Dbms_output.put_line('平均年龄'||v_age);
END;

--调用带输入输出参数的存储过程
DECLARE
  v_test NUMBER :=1;
BEGIN
  proc3(v_test);
  Dbms_output.put_line(v_test);
END;

删除存储过程、函数

drop function|procedure [user].名称

包

包相当于类 存储过程、函数相当于方法 包中可以声明变量和存储过程

包规范(Packages)相当于接口 给出了方法和变量的声明 而没有方法体

包体(Package bodies)相当于重写了接口 实现了方法

--创建包规范
CREATE OR REPLACE PACKAGE PKG_TEST
IS
       v_class student.class%TYPE:='2';
       PROCEDURE query_stu(v_cla IN student.class%TYPE DEFAULT v_class,v_avg OUT NUMBER);
END PKG_TEST;

--创建包体
CREATE OR REPLACE PACKAGE BODY PKG_TEST
IS
       PROCEDURE query_stu(v_cla IN student.class%TYPE DEFAULT v_class,v_avg OUT NUMBER)
         IS
         BEGIN
           SELECT AVG(age)
           INTO v_avg
           FROM student
           WHERE CLASS=v_cla;
         EXCEPTION
           WHEN NO_DATA_FOUND THEN
             DBMS_OUTPUT.put_line('没有此班级');
           WHEN OTHERS THEN
             DBMS_OUTPUT.put_line(SQLERRM);
           END;
END PKG_TEST;

--调用
DECLARE
 v_avg NUMBER;
BEGIN
  PKG_TEST.query_stu('2',v_avg);
  DBMS_OUTPUT.put_line(v_avg);
END;

触发器

CREATE TRIGGER FIRST_TIRRGER
AFTER DELETE --触发事件
ON student   --触发对象
[when]       --触发条件
BEGIN
  dbms_output.put_line('这是查询操作');
END;

当我们对触发器中操作数据执行指定触发操作时，触发器自动执行
如上面定义的触发器,我们对student表执行删除操作时就会触发触发器FIRST_TIRRGER

多个操作用or隔开
事件发生之前（BEFORE）还是之后(AFTER)触发
on database 针对整个数据库

复杂的视图是无法进行dml操作的，使用替代触发器就可以实现
替代触发器(instead of)只适用于视图
不能指定before、after
必须指定For each row

create or replace trigger name
instead of
instert
on viewname
for each row
declare v_temp int;
Begin
...
end;

其他

oracle 变量

声明
declare name type;
声明且赋值
declare name type :=value;

distinct 去处重复

oracle序列 实现自增

游标

利用游标，程序可以逐个地处理和遍历一次检索返回的整个记录集
DECLARE
  --定义游标
  CURSOR stu_cursor IS SELECT NAME,age FROM student;
  v_name student.name%TYPE;
  v_age student.age%TYPE;
BEGIN
  --打开游标
  OPEN stu_cursor;
  LOOP
    --提取游标数据
    FETCH stu_cursor INTO v_name,v_age;
    Dbms_Output.put_line(v_name||v_age);
    --显游标的四个属性:%FOUND是否存在数据，%NOTFOUND是否不存在在数据，%ISOPEN是否打开，%RowCount已检索行数
    EXIT WHEN stu_cursor%NOTFOUND;--当游标中没有记录退出循环
  END LOOP;
  --关闭游标
  CLOSE stu_cursor;
END;

--游标For循环
DECLARE 
CURSOR stu_cursor IS SELECT NAME,age FROM student;
BEGIN
  FOR stu IN stu_cursor
  LOOP
    Dbms_Output.put_line(stu.name||stu.age);
  END LOOP;
END;

隐式游标
SQL%FOUND、SQL%NOTFOUND、SQL%ROWCOUNT、SQL%ISOPEN

PlSQL循环

DECLARE
  v_cnt INT :=1;
BEGIN
  LOOP
    Dbms_Output.put_line(v_cnt);
    EXIT WHEN v_cnt=10;--退出条件
    v_cnt:=v_cnt+1;
  END LOOP;
END;

--While循环
DECLARE
  v_cnt INT :=1;
BEGIN
  WHILE v_cnt<=10 LOOP
    Dbms_Output.put_line(v_cnt);
    v_cnt:=v_cnt+1;
  END LOOP;
END;

--For循环
BEGIN 
  FOR i IN REVERSE 1..10 LOOP
    Dbms_Output.put_line(i);
  END LOOP;
END;

异常处理

EXCEPTION
WHEN NO_DATA_FOUND THEN
...
WHEN OTHERS THEN
...