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

...

巴特西

Oracle基础

Oracle数据库与实例区分

事务、保存点、回滚

数据字典

Oracle函数

包含、模糊查询

字符函数

数值函数

日期函数

转换函数

通用函数

条件表达式

分组函数

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

分组排序函数

存储过程、函数

创建存储过程、函数

调用存储过程、函数

删除存储过程、函数

包

触发器

其他

oracle 变量

distinct 去处重复

oracle序列实现自增

游标

PlSQL循环

异常处理

最新文章

热门文章

巴特西

Oracle基础

Oracle数据库与实例区分

事务、保存点、回滚

数据字典

Oracle函数

包含、模糊查询

字符函数

数值函数

日期函数

转换函数

通用函数

条件表达式

分组函数

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

分组排序函数

存储过程、函数

创建存储过程、函数

调用存储过程、函数

删除存储过程、函数

包

触发器

其他

oracle 变量

distinct 去处重复

oracle序列 实现自增

游标

PlSQL循环

异常处理

最新文章

热门文章

oracle序列实现自增