46.异常
(1)
declare
v_temp number(4);
begin
select empno into v_temp from emp where deptno = 10;
exception
when too_many_rows then
dbms_output.put_line('太多记录了');
when others then
dbms_output.put_line('error');
end;(2)
declare
v_tempnumber(4);
begin
select empno into v_temp from emp where empno = 2222;
exception
when no_data_found then
dbms_output.put_line('没有该项数据');
end;
----------------错误记录日志(用表记录：将系统日志存到数据库便于以后查看) -----------
创建序列（用来处理递增的ＩＤ）：
create sequence seq_errorlog_id start with 1 increment by 1;
创建日志表:
create table errorlog
(
id number primary key,
errcode number,
errmsg varchar2(1024),
errdate date
);
示例程序：
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;
v_errmsg:= SQLERRM;
insert into errorlog values (seq_errorlog_id.nextval, v_errcode,v_errmsg, sysdate);
commit;
end;
47. PL/SQL中的重点cursor(游标)和指针的概念差不多
declare
cursorc is
select * from emp; //此处的语句不会立刻执行，而是当下面的open c的时候，才会真正去数据库中取数据
v_emp c%rowtype;
begin
open c;
fetch c into v_emp;
dbms_output.put_line(v_emp.ename); //这样会只输出一条数据 134将使用循环的方法输出每一条记录
close c;
end;
----------------------使用do while 循环遍历游标中的每一个数据---------------------
declare
cursor c is
select* from emp;
v_emp c%rowtype;
begin
open c;
loop
fetch c into v_emp;
(1) exit when(c%notfound); //notfound是oracle中的关键字，作用是判断是否还有下一条数据
(2) dbms_output.put_line(v_emp.ename); //(1)(2)的顺序不能颠倒，否则会把最后一条结果再多打印一次。
end loop;
close c;
end;
------------------------使用while循环，遍历游标---------------------
declare
cursor c is
select* from emp;
v_emp emp%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;s
------------------------使用for 循环，遍历游标（最方便快捷的方法！）-----------------
declare
cursorc is
select * from emp;
begin
for v_emp in c loop
dbms_output.put_line(v_emp.ename);
endloop;
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
forv_temp in c(30, 'CLERK') loop
dbms_output.put_line(v_temp.ename);
endloop;
end;
可更新的游标
declare
cursorc
is
select * from emp2 for update;
begin
for v_temp in c loop
if(v_temp.sal< 2000) then
update emp2 set sal = sal * 2 where current of c;
elsif (v_temp.sal =5000) then
deletefrom emp2 where current of c;
end if;
end loop;
commit;
end;
48. store procedure存储过程(带有名字的程序块)
createor replace procedure p
is--除了这两句替代declare，下面的语句全部都一样
cursor c is
select * from emp2 for update;
begin
for v_emp in c loop
if(v_emp.deptno= 10) then
updateemp2 set sal = sal +10 where current of c;
elseif(v_emp.deptno =20) then
updateemp2 set sal = sal + 20 where current ofc;
else
updateemp2 set sal = sal + 50 where current of c;
endif;
end loop;
commit;
end;
执行存储过程的两种方法：
（1）exec p;(p是存储过程的名称)
（2）begin
p;
end;
/带参数的存储过程
先创建存储过程：（in标识传入参数，out标识传出参数，默认为传入参数）
createor replace procedure p
(v_ain number, v_b number, v_ret out number, v_temp inout number)
is
begin
if(v_a> v_b) then
v_ret:= v_a;
else
v_ret:= v_b;
endif;
v_temp:= v_temp + 1;
end;
再调用：
declare
v_a number := 3;
v_b number := 4;
v_retnumber;
v_tempnumber := 5;
begin
p(v_a,v_b, v_ret, v_temp);
dbms_output.put_line(v_ret);
dbms_output.put_line(v_temp);
end;
删除存储过程
dropprocedure p;
49. 创建函数计算个人所得税的税率
createor replace function sal_tax
(v_sal number)
returnnumber
is
begin
if(v_sal< 2000) then
return0.10;
elsif(v_sal<2750) then
return0.15;
else
return0.20;
endif;
end;
50. 创建触发器（trigger）
触发器不能单独的存在，必须依附在某一张表上
写主语 谓语 宾语 游戏
创建触发器的依附表：
createtable emp2_log
(
enamevarchar2(30) ,
eactionvarchar2(20),
etimedate
);
create or replace trigger trig
after insert or delete orupdate on emp2 for each row --加上此句，每更新一行，触发一次，不加入则值触发一次
begin
ifinserting then
insertinto emp2_log values(USER, 'insert', sysdate);
elsifupdating then
insertinto emp2_log values(USER, 'update', sysdate);
elsifdeleting then
insertinto emp2_log values(USER, 'delete', sysdate);
endif;
end;
51.触发器用法之一：通过触发器更新约束的相关数据
createor replace trigger trig
afterupdate on dept
foreach row
begin
updateemp set deptno =:NEW.deptno where deptno =: OLD.deptno;
end;
--只编译不显示的解决办法 set serveroutput on;
52.通过创建存储过程完成递归
createor replace procedure p(v_pid article.pid%type,v_level binary_integer) is
cursorc is select * from article where pid = v_pid;
v_preStrvarchar2(1024) := '';
begin
for i in 0..v_leave loop
v_preStr:= v_preStr || '';
end loop;
for v_article in c loop
dbms_output.put_line(v_article.cont);
if(v_article.isleaf= 0) then
p(v_article.id);
endif;
endloop;
end;
-------------------------------查看当前用户下有哪些表---
首先，用这个用户登录然后使用语句：
select* from tab;
-----------------------------用Oracle进行分页！--------------
因为Oracle中的隐含字段rownum不支持'>'所以：
select* from (
selectrownum rn, t.* from (
select* from t_user where user_id <> 'root'
)t where rownum <6
)where rn >3
------------------------Oracle下面的清屏命令----------------
clearscreen; 或者 cle scr;
-----------将创建好的guohailong的这个用户的密码改为abc--------------
alter user guohailong identified by abc
--当密码使用的是数字的时候可能会不行
46.异常
(1)
declare
v_temp number(4);
begin
select empno into v_temp from emp where deptno = 10;
exception
when too_many_rows then
dbms_output.put_line('太多记录了');
when others then
dbms_output.put_line('error');
end;(2)
declare
v_tempnumber(4);
begin
select empno into v_temp from emp where empno = 2222;
exception
when no_data_found then
dbms_output.put_line('没有该项数据');
end;
----------------错误记录日志(用表记录：将系统日志存到数据库便于以后查看) -----------
创建序列（用来处理递增的ＩＤ）：
create sequence seq_errorlog_id start with 1 increment by 1;
创建日志表:
create table errorlog
(
id number primary key,
errcode number,
errmsg varchar2(1024),
errdate date
);
示例程序：
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;
v_errmsg:= SQLERRM;
insert into errorlog values (seq_errorlog_id.nextval, v_errcode,v_errmsg, sysdate);
commit;
end;
47. PL/SQL中的重点cursor(游标)和指针的概念差不多
declare
cursorc is
select * from emp; //此处的语句不会立刻执行，而是当下面的open c的时候，才会真正去数据库中取数据
v_emp c%rowtype;
begin
open c;
fetch c into v_emp;
dbms_output.put_line(v_emp.ename); //这样会只输出一条数据 134将使用循环的方法输出每一条记录
close c;
end;
----------------------使用do while 循环遍历游标中的每一个数据---------------------
declare
cursor c is
select* from emp;
v_emp c%rowtype;
begin
open c;
loop
fetch c into v_emp;
(1) exit when(c%notfound); //notfound是oracle中的关键字，作用是判断是否还有下一条数据
(2) dbms_output.put_line(v_emp.ename); //(1)(2)的顺序不能颠倒，否则会把最后一条结果再多打印一次。
end loop;
close c;
end;
------------------------使用while循环，遍历游标---------------------
declare
cursor c is
select* from emp;
v_emp emp%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;s
------------------------使用for 循环，遍历游标（最方便快捷的方法！）-----------------
declare
cursorc is
select * from emp;
begin
for v_emp in c loop
dbms_output.put_line(v_emp.ename);
endloop;
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
forv_temp in c(30, 'CLERK') loop
dbms_output.put_line(v_temp.ename);
endloop;
end;
可更新的游标
declare
cursorc
is
select * from emp2 for update;
begin
for v_temp in c loop
if(v_temp.sal< 2000) then
update emp2 set sal = sal * 2 where current of c;
elsif (v_temp.sal =5000) then
deletefrom emp2 where current of c;
end if;
end loop;
commit;
end;
48. store procedure存储过程(带有名字的程序块)
createor replace procedure p
is--除了这两句替代declare，下面的语句全部都一样
cursor c is
select * from emp2 for update;
begin
for v_emp in c loop
if(v_emp.deptno= 10) then
updateemp2 set sal = sal +10 where current of c;
elseif(v_emp.deptno =20) then
updateemp2 set sal = sal + 20 where current ofc;
else
updateemp2 set sal = sal + 50 where current of c;
endif;
end loop;
commit;
end;
执行存储过程的两种方法：
（1）exec p;(p是存储过程的名称)
（2）begin
p;
end;
/带参数的存储过程
先创建存储过程：（in标识传入参数，out标识传出参数，默认为传入参数）
createor replace procedure p
(v_ain number, v_b number, v_ret out number, v_temp inout number)
is
begin
if(v_a> v_b) then
v_ret:= v_a;
else
v_ret:= v_b;
endif;
v_temp:= v_temp + 1;
end;
再调用：
declare
v_a number := 3;
v_b number := 4;
v_retnumber;
v_tempnumber := 5;
begin
p(v_a,v_b, v_ret, v_temp);
dbms_output.put_line(v_ret);
dbms_output.put_line(v_temp);
end;
删除存储过程
dropprocedure p;
49. 创建函数计算个人所得税的税率
createor replace function sal_tax
(v_sal number)
returnnumber
is
begin
if(v_sal< 2000) then
return0.10;
elsif(v_sal<2750) then
return0.15;
else
return0.20;
endif;
end;
50. 创建触发器（trigger）
触发器不能单独的存在，必须依附在某一张表上
写主语 谓语 宾语 游戏
创建触发器的依附表：
createtable emp2_log
(
enamevarchar2(30) ,
eactionvarchar2(20),
etimedate
);
create or replace trigger trig
after insert or delete orupdate on emp2 for each row --加上此句，每更新一行，触发一次，不加入则值触发一次
begin
ifinserting then
insertinto emp2_log values(USER, 'insert', sysdate);
elsifupdating then
insertinto emp2_log values(USER, 'update', sysdate);
elsifdeleting then
insertinto emp2_log values(USER, 'delete', sysdate);
endif;
end;
51.触发器用法之一：通过触发器更新约束的相关数据
createor replace trigger trig
afterupdate on dept
foreach row
begin
updateemp set deptno =:NEW.deptno where deptno =: OLD.deptno;
end;
--只编译不显示的解决办法 set serveroutput on;
52.通过创建存储过程完成递归
createor replace procedure p(v_pid article.pid%type,v_level binary_integer) is
cursorc is select * from article where pid = v_pid;
v_preStrvarchar2(1024) := '';
begin
for i in 0..v_leave loop
v_preStr:= v_preStr || '';
end loop;
for v_article in c loop
dbms_output.put_line(v_article.cont);
if(v_article.isleaf= 0) then
p(v_article.id);
endif;
endloop;
end;
-------------------------------查看当前用户下有哪些表---
首先，用这个用户登录然后使用语句：
select* from tab;
-----------------------------用Oracle进行分页！--------------
因为Oracle中的隐含字段rownum不支持'>'所以：
select* from (
selectrownum rn, t.* from (
select* from t_user where user_id <> 'root'
)t where rownum <6
)where rn >3
------------------------Oracle下面的清屏命令----------------
clearscreen; 或者 cle scr;
-----------将创建好的guohailong的这个用户的密码改为abc--------------
alter user guohailong identified by abc
--当密码使用的是数字的时候可能会不行
1275
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
