Oracle基础学习笔记(二)-基于马士兵Oracle视频

青旅半醒 2021-12-09 07:47 263阅读 0赞

041. PL\_SQL PL/SQL PLSQL

*--PL/SQL块包含三部分：声明部分(可选)、执行部分(必选)、异常处理部分(可选)。*

*--set serveroutput on; --打开控制台输出*

*--PL/SQL中的select语句,必须和 into 关键字一起使用,而且必须且只能返回一个值;*

*--PL/SQL中,sql%rowcount 表示执行的sql语句影响了多少行数据,select语句始终为1;insert、update、delete语句为实际影响的行数;*

格式如下：

declare *--声明变量*

...

begin *--PL/SQL开始*

...

exception *--捕获异常*

when others then

...

end; *--PL/SQL结束*

*--例子*

declare

v\_num number := 0;

v\_name varchar2(10);

begin

v\_name := 'zhangsan';

v\_num := 2/v\_num;

dbms\_output.put\_line(v\_name);

dbms\_output.put\_line(v\_num);

exception

when others then

dbms\_output.put\_line('error');

end;

*--变量声明的规则：*

1.变量名称不能使用保留字;

2.第一个字符必须是字母;

3.变量名最多包含30个字符;

4.不要与数据库的表或者列同名;

5.每一行只能声明一个变量;

6.开发过程中，推荐的变量命名规则如下:

定义变量时，使用 v\_ 作为前缀，如 v\_name，v\_job;

定义常量时，使用 c\_ 作为前缀，如 c\_rate;

定义游标时，使用 \_cursor 作为后缀，如 emp\_cursor;

定义例外时，使用 e\_ 作为前缀，如 e\_integer\_error;

*--大小写规则：为了提高程序的可读性和性能，Oracle建议的大小写编码如下:*

*--SQL 关键字采用大写，如 SELECT UPDATE FROM WHERE AND;*

*--PL/SQL 关键字采用大写，如 DECLARE BEGIN END;*

*--数据类型采用大写，如 INT VARCHAR2 DATE;*

*--标示符 和参数 采用小写，如 v\_name;*

*--数据库对象和字段采用小写，如 emp,sal;*

*--常用变量类型*

1. binary\_integer： 整数，主要用来计数而不是用来表示字段类型;

2. number： 数字类型;

3. char： 定长字符长类型;

4. varchar2： 变长字符串;

5. date：日期;

6. long： 长字符串，最长2GB;

7. boolean ：布尔类型，可以取值为true，false和null值;(boolean类型变量声明时,需要赋初值,不然默认为null)

*--变量声明，使用%type属性*

declare

v\_empno number(4);

v\_empno2 emp.empno%type;

v\_empno3 v\_empno2%type;

begin

dbms\_output.put\_line('test');

end;

*--table变量类型(数组)*

declare

type type\_table\_emp\_empno is table of emp.empno%type index by binary\_integer;

v\_empnos type\_table\_emp\_empno;

begin

v\_empnos(0) := 7369;

v\_empnos(2) := 7839;

v\_empnos(-1) := 9999;

dbms\_output.put\_line(v\_empnos(-1));

end;

*--record变量类型(近似java中的类)*

declare

type type\_record\_dept is record (

deptno dept.deptno%type,

dname dept.dname%type,

loc dept.loc%type

);

v\_temp type\_record\_dept;

begin

v\_temp.deptno := 50;

v\_temp.dname := 'zhangsan';

v\_temp.loc := 'beijing';

dbms\_output.put\_line(v\_temp.deptno || ' ' || v\_temp.dname);

end;

*--使用%rowtype声明record变量*

declare

v\_temp dept%rowtype;

begin

v\_temp.deptno := 50;

v\_temp.dname := 'zhangsan';

v\_temp.loc := 'beijing';

dbms\_output.put\_line(v\_temp.deptno || ' ' || v\_temp.dname);

end;

*--PL\_SQL语句中执行select语句,必须有返回结果,且只有一条返回结果!*

*--如果select语句中没有游标，就必须加上into关键字;*

如：

declare

v\_ename emp.ename%type;

v\_sal emp.sal%type;

begin

select ename, sal into v\_ename, v\_sal   *-- 将 ename 和 sal 值放到变量 v\_ename 和 v\_sal 中*

from emp

where empno = 7369;

dbms\_output.put\_line(v\_ename || ' ' || v\_sal);

end;

declare

v\_emp emp%rowtype;

begin

select \* into v\_emp from emp where empno = 7369;

dbms\_output.put\_line(v\_emp.ename);

end;

*--PL\_SQL中执行DDL语句,必须在前面加 execute immediate '...',而且要将sql语句放在单引号内部;*

如：

begin

execute immediate 'create table T (name varchar2(20))';

end;

PL\_SQL 中的条件分支语句：if...elsif...else...end if; *-- 注意是 elsif 而不是 elseif*

declare

v\_sal emp.sal%type;

begin

select sal into v\_sal from emp where empno = 7369;

if(v\_sal<2500) then

update emp set(sal = v\_sal\*2) where empno = 7369;

elsif(v\_sal>2500) then

update emp set(sal = v\_sal/2) where empno = 7369;

else *-- else后面没有then*

dbms\_output.put\_line(v\_sal);

end if;   *--end if 后面必须要有;号*

end;

PL\_SQL中执行循环语句：

declare

i binary\_integer := 1;

begin

loop

dbms\_output.put\_line(i);

i := i + 1;

exit when (i >= 11);

end loop;

end;

*-- 相当于java中的do...while循环*

declare

j binary\_integer := 1;

begin

while j < 11 loop

j := j + 1;

end loop;

end;

*-- 相当于java中while循环*

begin

for k in 1..10 loop

dbms\_output.put\_line(k);

end loop;

end;

*-- 顺序执行从一到十, 相当于java中的for循环*

begin

for k in reverse 1..10 loop

dbms\_output.put\_line(k);

end loop;

end;

*-- 逆序执行从十到一, 相当于java中的for循环*

*--错误处理：*

declare

v\_temp number(4);

begin

select empno into v\_temp from emp where deptno = 10;

exception

when no\_data\_found then *-- no\_data\_found是Oracle提供的一种异常类型，相当于java中具体的异常类*

dbms\_output.put\_line('没有数据');

when too\_many\_rows then *-- too\_many\_rows是Oracle提供的一种异常类型，相当于java中具体的异常类*

dbms\_output.put\_line('太多记录了');

when others then

dbms\_output.put\_line('error');

end;

其实我们可以把数据库中执行操作时产生的异常的相关信息放在一张表中，以便以后查询，具体步骤如下：

1\->创建一张表存放错误的表:

create table errorlog(

id number primary key,

errorcode number,

errormsg varchar2(1024);

errdate date

);

2\->创建一个序列以用来给errorlog表的id赋值:

create sequence seq\_errorlog\_id start with 1 increment 1;

3\->写一个PL\_SQL语句来记录发生的错误信息:

declare

v\_deptno dept.deptno%type := 10;

v\_errcode number;

v\_errmsg varchar2(1024);

begin

delete from dept where deptno = v\_deptno;

commit;

exception

when others then

rollback;

v\_errcode := SQLCODE;        *--SQLCODE是系统中出现异常后的异常代码是一个数字类型，它是一个关键字*

v\_errmsg := SQLERRM;         *--SQLERRM是系统中出现异常后的异常信息是一个字符串类型，它是一个关键字*

insert into errorlog values (seq\_errorlog\_id.nextval, v\_errcoe, v\_errmsg, to\_char(sysdate,'YYYY-MM-DD HH24-MI-SS'));

commit;

end;

042. SQLCODE SQLERRM

Oracle内置函数SQLCODE和SQLERRM, 用在OTHERS处理器中,分别用来返回Oracle的错误代码和错误消息.

SQLCODE返回Oracle错误的序号，而SQLERRM返回的是相应的错误消息，错误消息首先显示的是错误代码。

一个错误消息最多只能包含512个字节的错误代码。

如果没有异常被触发，则SQLCODE返回0，SQLERRM返回“ORA\-0000：normal， successful completion”。

043. 游标 cursor

和java中的迭代器Itaroter的意思相似、它起初指向一个子查询所查出的结果集的第一条记录上

它有四个属性：

1、found 能找到记录返回true，没找到返回false;

2、notfound 没找到记录返回true，找到了返回false;

3、isopen 代表游标是否打开;

4、rowcount 指的是当前已经 fetch 到了多少条记录

格式如下：

declare

cursor c is select \* from emp; *-- 将一个游标指向子查询所查出结果集的第一个记录上*

v\_emp c%rowtype;  *-- 意思是声明一个变量，它的类型和游标c所指向的子查询*

begin

open c; *-- 将游标打开*

fetch c into v\_emp; *-- 将游标c所指向的记录取出来存储到变量v\_emp中*

dbms\_output.put\_line(v\_emp.name);

close c; *-- 关闭游标c*

end;

利用游标(cursor)来执行循环：

*-- do..while类型的循环：*

declare

cursor c is select \* from emp;

v\_emp c%rowtype;

begin

open c;

loop

fetch c into v\_emp;

dbms\_output.put\_line(v\_emp.ename);

exit when (c%notfound);

end loop;

close c;

end;

*-- while类型的循环：*

declare

cursor c is select \* from emp;

v\_emp c%rowtype;

begin

open c;

fetch c into v\_emp;

while (c%found) loop

dbms\_output.put\_line(v\_emp.ename);

fetch c into v\_emp;

end loop;

close c;

end;

*-- for循环类型的循环，*

*-- 这种循环不需要我们去open 游标，不需要我们手动的去close游标，*

*-- 也不需要我们主动的去fetch,也不需要我们主动的去创建一个变量v\_emp*

*-- for循环自动帮我们创建以上这些东西。*

declare

cursor c is select \* from emp;

begin

for v\_emp in c loop

dbms\_output.put\_line(v\_emp.ename);

end loop;

end;

*-- 带参数的游标(非重点)*

declare

cursor c(v\_deptno emp.deptno%type,v\_job emp.job%type)

is select ename, sal from emp where deptno = v\_deptno and job = v\_job;

begin

for v\_temp in c(30, 'clerk') loop

dbms\_output.put\_line(v\_emp.ename);

end loop;

end;

*-- 可更新的游标:*

declare

cursor c is select \* from emp for update; *-- 可更新的游标,只要在子查询后加上 for update 就行*

begin

for v\_temp in c loop

if(v\_temp.sal<2000) then

update emp set sal = sal\*2 where current of c; *-- current of c 指的是当前游标所指的这条记录、*

elsif(v\_temp.sal = 5000) then  *-- PL\_SQL中比较两个值的大小使用“=”而不是双等号，另外PL\_SQL中的赋值号是:=而不是=;*

delete from emp where current of c;

end if ;

end loop;

commit;

end;

044. procedure 存储过程

*--存储过程其实是一个带有名称的PL/SQL语句块;*

格式如下：

create or replace procedure p (String a,Date b)

is   *--存储过程就是比PL/SQL多了这以上两行语句,底下的都跟PL\_SQL一样*

cursor c is select \* from emp for update;

begin

for v\_emp in c loop

if(v\_emp.deptno = 10 ) then

update emp set sal = sal + 10 where current of c;

elsif(v\_emp.deptno =20) then

update emp set sal = sal + 20 where current of c;

else

update emp set sal = sal + 50 where current of c;

end if;

end loop;

commit;

end;

存储过程的执行有两种方式：

1、exec p；*-- 其中p为存储过程名称*

2、begin

end;

*-- 带参数的存储过程：*

create or replace precedure p (

v\_a in number,

v\_b in number,

v\_ret out number,

v\_temp in out number )

begin

if (v\_a > v\_b) then

v\_ret := v\_a;

else

v\_ret := v\_b;

end if;

v\_temp := v\_temp + 1;

end;

*-- 参数类型中的in是指传入参数,out是指传出参数;*

*-- 因为存储过程没有返回值,故用传出参数来获取结果,它在存储过程内部赋值;*

*-- in和out共同修饰的参数叫传入传出参数,它既可被赋值也可使用它的值;*

如何调用这个存储过程?：

declare

v\_a number := 3;

v\_b number := 4;

v\_ret number;     *--v\_ret不赋值，是因为它是传出参数，是用来存储值的，*

v\_temp number := 5;   *--既可以赋值，也可不赋值*

begin

p(v\_a,v\_b,v\_ret,v\_temp);

dbms\_output.put\_line(v\_ret);

dbms\_output.put\_line(v\_temp);

end;

*--编写存储过程出现错误代码,编译时只提示编译性错误,不会指出错误的详细信息,可使用命令show erro;来显示出错误的位置。*

045. function 函数

*--函数和存储过程一样,只是函数有返回值,而存储过程没有返回值;*

*--和Oracle内置函数用法相同;*

create or replace function sal\_tax(v\_sal number)

return number;  *-- 声明函数有返回值，且返回值类型是number*

begin

if(v\_sal < 2000) then

return 0.10;

elsif(v\_sal < 2750) then

return 0.15;

else

return 0.20;

end if;

end;

046. trigger 触发器

*--触发器是指执行某种操作之前或之后自动触发执行某种动作的sql语句;*

*--定义触发器时,必须要指定触发事件和触发操作;*

*--常用的触发事件包括INSERT、UPDATE、DELETE语句,触发操作实际是一个PL/SQL块;*

*--触发器不能单独执行,必须依附在表中;*

*--比如银行的存取款,在用户存取款后必须要把这些操作信息记录下来,以便查询.*

demo:

create table emp\_log(

uname varchar2(20),

action varchar2(10),

atime date

);

create or replace trigger trig after insert or update or delete on emp for each row

*-- 关键字有两个before和after分别表示在执行某种操作之前或之后*

*-- for each row 表示所作的操作在数据库表中、每更新一行，触发一次触发器*

begin

if inserting then

insert into emp\_log values (USER, 'insert', sysdate);

elsif updating then

insert into emp\_log values (USER, 'update', sysdate);

elsif deleting then

insert into emp\_log values (USER, 'delete', sysdate);

end if;

end;

*--不提倡使用*

create or replace trigger trig after update on dept for each row

begin

update emp2 set deptno = :NEW.deptno where deptno = :OLD.deptno;

end;

047. 包 包体 Package Package Body

*--Oracle的包用于逻辑组合相关的过程和函数,由包声明和包体两部分组成;*

*--包声明用于定义公用的常量、变量、过程和函数;包声明只有函数和存储过程的声明,没有具体实现;*

*--包体用于实现包规范中声明的函数和存储过程;*

CREATE PACKAGE emp\_pkg IS

FUNCTION annual\_income(name varchar2) RETURN NUMBER;

PROCEDURE update\_sal(name varchar2,new\_sal NUMBER);

END;

CREATE PACKAGE BODY emp\_pkg IS

FUNCTION annual\_income(name varchar2) RETURN NUMBER

annual\_sal NUMBER(7,2);

BEGIN

SELECT sal\*12 INTO annual\_sal

FROM emp

WHERE enmae = name;

RETURN annual\_sal;

END;

PROCEDURE update\_sal(name varchar2, new\_sal NUMBER)

BEGIN

UPDATE emp SET sal = new\_sal

WHERE enmae = name;

END;

*----------------------*

*--树状结构的存储与展现*

drop table article;

create table article

(

id number primary key,

cont varchar2(4000),

pid number,

isleaf number(1), *--0代表非叶子节点，1代表叶子节点*

alevel number(2)

)

*-------------*

insert into article values (1, '蚂蚁大战大象', 0, 0, 0);

insert into article values (2, '大象被打趴下了', 1, 0, 1);

insert into article values (3, '蚂蚁也不好过', 2, 1, 2);

insert into article values (4, '瞎说', 2, 0, 2);

insert into article values (5, '没有瞎说', 4, 1, 3);

insert into article values (6, '怎么可能', 1, 0, 1);

insert into article values (7, '怎么没可能', 6, 1, 2);

insert into article values (8, '可能性是很大的', 6, 1, 2);

insert into article values (9, '大象进医院了', 2, 0, 2);

insert into article values (10, '护士是蚂蚁', 9, 1, 3);

commit;

*---------*

蚂蚁大战大象

大象被打趴下了

蚂蚁也不好过

瞎说

没有瞎说

大象进医院了

护士是蚂蚁

怎么可能

怎么不可能

可能性是很大的

*--------------------------*

create or replace procedure p (v\_pid article.pid%type, v\_level binary\_integer) is

cursor c is select \* from article where pid = v\_pid;

v\_preStr varchar2(1024) := '';

begin

for i in 1..v\_level loop

v\_preStr := v\_preStr || '\*\*\*\*';

end loop;

for v\_article in c loop

dbms\_output.put\_line(v\_preStr || v\_article.cont);

if (v\_article.isleaf = 0)

then

p (v\_article.id, v\_level + 1);

end if;

end loop;

end;

Oracle基础学习笔记(二)-基于马士兵Oracle视频

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 zookeeper --马士兵

相关 Spring 马士兵

相关 nio-马士兵

相关 Oracle学习基础（二）总结

相关 Oracle基础学习笔记(二)-基于马士兵Oracle视频

相关 Oracle基础学习笔记(一)-基于马士兵Oracle视频

相关 Oracle基础学习笔记（2）

相关 Oracle基础学习笔记（3）

相关 Oracle基础学习笔记（4）

相关 Oracle基础学习笔记（1）