oracle存储过错procedure.docx
《oracle存储过错procedure.docx》由会员分享，可在线阅读，更多相关《oracle存储过错procedure.docx（29页珍藏版）》请在冰豆网上搜索。
oracle存储过错procedure
1存储过程procedure
--一个任务需要多个步骤完成通常一个select语句只能完成一个步骤,而单个的select语句是没有特定的业务逻辑关系的,所以我们把多个select语句放在一个PL/SQL代码块中
--这样以来代码快就可以完成特定的任务
语法:
create procedure[（参数一,参数二....）]
as
begin
exception
end;
1-1存储过程的类型
11-1-1,无输入参数无输出参数
create or replace procedure test1
as
remp%rowtype;
begin
dbms_output.put_line（'编号 姓名 工作'）;
for r in （select * from emp）
loop
dbms_output.put_line（r.empno||' '||r.ename||' '||r.job）;
end loop;
end;
--调用存储过程
call test1（）;
------------------------
11-1--2,有输入参数无输出参数
create or replace procedure test2（name varchar2）
as
esalemp.sal%type;
begin
select sal into esal from emp where ename=name;
dbms_output.put_line（name||' 的工资为:
'||esal）;
end;
--调用
call test2（'SCOTT'）;
------------------------
11-1-3,无输入参数有输出参数
--用out来声明参数为输出参数
--out放在输出参数名和类型之间
create or replace procedure test3（tsal out number）
as
begin
select sum（sal） into tsal from emp;
end;
--在PL/SQL块中间调用存储过程,不需要写'call'调用
--调用存储过程时,输出参数无需使用out声明
declare
ts number;
begin
test3（ts）;
dbms_output.put_line（'总的工资为'||ts）;
end;
------------------------
11-1-4,有输入参数有输出参数
create or replace procedure test4（name in varchar2,salary out number）
as
begin
select sal into salary from emp where ename=name;
end;
--调用
declare
salary number;
name varchar2（20）;
begin
name:
='&员工的姓名';
test4（name,salary）;
dbms_output.put_line（name||'的工资为:
'||salary）;
end;
11-1-5输入/输出参数
--定义存储过程可以给存储过程指定参数（输入参数/输出参数）
--通过'in'来声明一个参数为输入参数（可以省略）
--通过'out'来声明一个参数为输出参数（不可以省略）
--通过'inout'来声明一个参数既是输入参数又是输出参数
create or replace procedure test5（param in out varchar2）
as
begin
select job into param from emp where ename=param;
end;
--调用
declare
p varchar2（20）:
='KING';
begin
test5（p）;
dbms_output.put_line（p）;
end;
--把系统时间转换成指定的格式
--dual为oracle的伪表
select to_char（sysdate,'yyyy-mm-dd'） from dual;
1触发器trigger
触发器:
触发器就是一种无需调用（在发生了某个事件时）就自动调用执行的存储过程
触发时间
--before:
在触发的事件执行之前执行触发器
--after:
在触发事件执行之后再执行触发器
1-1创建触发器afterupdate
--创建触发器 after修改之后执行触发器
create or replace trigger t_test1 after update on tb_user
declare
logid number:
=0;
c number;
d date;
begin
--查询tb_log表总记录条数给c
select count（*） into c from tb_log;
if（c>0）then
--查询tb_log表把最大的log_id给logid
select max（log_id） into logid from tb_log;
end if;
logid:
=logid+1;
select sysdate into d from dual;
insertintotb_log（log_id,log_date,log_oper）values（logid,d,'update'）;
end;
触发:
update tb_user set stu_name='李氏' where stu_num='1001';
1-2创建触发器afterdelete
--创建触发器 after删除之后执行触发器
create or replace trigger t_test2 after delete on tb_user
declare
logid number:
=0;
c number;
d date;
begin
--查询tb_log表总记录条数给c
select count（*） into c from tb_log;
if（c>0）then
--查询tb_log表把最大的log_id给logid
select max（log_id） into logid from tb_log;
end if;
logid:
=logid+1;
select sysdate into d from dual;
insert into tb_log（log_id,log_date,log_oper）values（logid,d,'delete'）;
end;
1-3触发器beforeupdate
--触发器befor在执行事件之前进行执行
create or replace trigger t_test3 before update on emp
declare
remp%rowtype;
begin
for r in （select * from emp）
loop
dbms_output.put_line（r.ename||'-->'||r.sal）;
end loop;
end;
1-4语句级触发器:
表中的所有列
--当前的触发器只是普通的触发器,监听表中的所有的列
--只要对emp表进行修改就会触发
--语句级触发器不管影响多少条语句都执行一次
--行级触发器影响几次就执行几次
create or replace trigger t_test4 before update on emp
declare
remp%rowtype;//多条记录
begin
dbms_output.put_line（'***********'）;
end;
------------------------------------------------------
--修改触发
update emp set job='softeem' where ename='SCOTT';
------------------------------------------------------
1-5行级触发器:
会执行多次
create or replace trigger t_test5 before update on emp for each row
declare
begin
dbms_output.put_line（'<><><><<<><><><><'）;
end;
------------------------------------------------------
触发条件
update emp set sal=sal+20 where deptmo=20;
1-6当对emp表进行修改或者删除的时候
create or replace trigger t_test6 before update or delete on emp
declare
begin
dbms_output.put_line（'&&&&&&&&&&&&'）;
end;
1-7列触发器
--列触发器:
在操作后面通过'of列名' 来指定列触发器（updateinsert）
--只监听一列数据当列进行修改的时候执行该触发器
--updatejob列时执行该触发器
update emp set job='softeem' where ename='SCOTT';
-----触发器
create or replace trigger t_test7 before update of job on emp
declare
begin
dbma_output.put_line（'%%%%%%%%'）;
end;
-------------------------------------------------
select * from tb_user;
insert into tb_user（stu_num,stu_name,stu_sex,stu_birthday）
values （1010,'张三','男',to_date（'2013-2-2','yyyy-mm-dd'））;
--:
new用在insert中表示新添加的这条数据
create trigger t_test8 before insert on tb_user for each row
declare
begin
if（:
new.stu_sex='男' or :
new.stu_sex='女'）then
dbms_output.put_line（'数据正确'）;
else
dbms_output.put_line（'数据错误'）;
end if;
end;
-----------------------------
--:
old用在delete语句中表示删除的语句
delete from tb_user where stu_num=1010;
--触发器
create or replace t_test9 after delete on tb_user for each row
declare
begin
dbms_output.put_line（:
old.stu_num||'--->'||:
old.stu_name）;
end;
-----------------------------------------------------------------
--禁用触发器
alter trigger t_test9 disable;
--解除禁用
alter trigger t_test9 enable;
2函数和游标
2-1函数function
函数:
是一个特殊的存储过程相当于java中的方法
--存储过程只有参数（输入/输出参数）
--函数总有输入参数但是有返回值
--函数的定义:
有参数function
create or replace function f_test1（m number,n number）
return number
as
a number;
begin
a:
=m+n;
return a;
end;
-------------------------------------------
2-1-1无参数的函数
create or replace function f_test2
return varchar2
as
name varchar2（20）;
begin
--into一种给变量的赋值方式,把值给name
select dname into name from dept where deptno=20;
return name;
end;
2-1-2调用函数
--1,通过dual系统表（数据字典）调用
select f_test1（3,2） from dual;
select f_test2（） from dual;
--2,在 PL/SQL块中调用
declare
s number;
begin
--调用方法并把返回值赋给s
s:
=f_test1（3,9）;
dbms_output.put_line（s）;
end;
---------------------------------------------
2-2常见的系统函数
1,instr
instr（str1,str2）:
indexof
--返回第二个字符串在第一个字符串中第一次出现的位置
--如果第一个字符串中不包含第二个字符串返回0
declare
num number;
begin
num:
=instr（'HELLOWORD','H'）;
dbms_output.put_line（num）;
end;
2,lpad和rpad
lpad（str1,len,str2）:
将str2拼接到str1的左边,直到str1的长度等于len
rpad（str1,len,str3）:
将str2拼接到str1的右边,直到str1的长度等于len
declare
str varchar2（20）;
begin
str:
=lpad（'abcd',7,'1234'）;
dbms_output.put_line（str）;
str:
=rpad（'abcd',7,'1234'）;
dbms_output.put_line（str）;
end;
3,trim
--trim:
去除字符串首尾的空格
--ltrim:
去除字符串左边（首）的空格
--rtrim:
去除字符串右边（尾）的空格
declare
str varchar2（20）;
begin
str:
=rtrim（' abc bbb '）;
dbms_output.put_line（'****'||str||'******'）;
end;
4,ceil
--ceil（num）:
返回大于等于num的最小整数（只入不舍）
begin
dbms_output.put_line（ceil（15.1））;
end;
5,floor
--floor（num）:
返回小于等于num的最小整数（只舍不入）
begin
dbms_output.put_line（floor（15.999））;
end;
6,round
--round（num）:
四舍五入
begin
dbms_output.put_line（round（15.999））;
end;
7,power
--power（num1,num2）:
返回num1的num2次方的值
select power（10,3） from dual;
8,trunc
--trunc（num1,num2）保留num1的num2位小数
select trunc（3.1415926,4） from dual;
-------------------------------------------
3游标cursor
--游标概念:
游标相当于一个结果集,用来存储和遍历
--一个查询SQL的结果集--->ResultSet
3-1图解游标
cursor my_cur<---select*fromemp
remp%rowtype;
loop
my_cur----->r
exit when my_cur;
end loop;
3-2游标的分类:
--静态游标:
在定义静态游标的时候要指定其结果集
--隐式游标:
（系统游标）无需定义其初始化,直接使用
--显示游标:
（自定义游标）用户自己定义的游标变量,并手动将结果集赋值给游标
--ref游标:
动态赋予结果集
3-2-1隐式游标
--隐式游标:
是由oracle系统提供,我们可以直接使用的
--特性:
自动创建打开和关闭
--隐式游标的属性:
--SQL%FOUND--如果SQL游标有结果集返回true
--SQL%NOTFOUND--如果SQL游标没有结果集返回true
--SQL%ROWCOUNT--返回SQL游标中的记录条数
--SQL%ISOPEN--始终返回false.
begin
update tb_user set stu_name='哈哈' where stu_num=1002;
dbms_output.put_line（SQL%ROWCOUNT）;
end;
select * from tb_user;
3-2-2显式游标
--显式游标:
用户自己定义的游标,在定义的时候同时需要通过'IS'指定结果集
--特性:
--1,在定义的时候就需要指定结果集
--2,遍历的时候
--打开游标--->取值---->关闭游标
declare
cursor my_cur IS select * from emp;
eemp%rowtype;
begin
--打开游标
open my_cur;
loop
--对游标进行抓取
fetch my_cur into e;
exit when my_cur%NOTFOUND;
dbms_output.put_line（e.empno）;
end loop;
--关闭游标
close my_cur;
end;
---for循环变量游标,无需使用手动打开和关闭游标
declare
--把查询emp表记录的值给游标
cursor my_cur is select * from emp;
begin
--对游标进行循环打印
for e in my_cur loop
dbms_output.put_line（e.ename||'-->'||e.sal）;
end loop;
end;
3-2-3ref游标
--ref游标:
动态结果集游标,在声明的时候无需指定结果集
declare
--声明一个ref游标:
1声明一个类型为refcursor
type my_type is ref cursor;
--2,声明一个变量
resmy_type; -->studernt---stu
sqlStr varchar2（200）;--存放sql查询语句
eemp%rowtype;--结果对象（存放查询出的某一条记录）
begin
sqlStr:
='select*fromemp';
--打开游标 通过for来指定结果集
open res for sqlStr;
loop
fetch res into e;
exit when res%notfound;--当游标没有值的时候就退出
dbms_output.put_line（e.empno||'-->'||e.sal）;
end loop;
close res;
end;
3-3在带有参数的存储过程中使用带有参数的游标
create or replace procedure emp1（sal1 number,sal2 number）
as
s number:
=sal1+sal2;
--声明一个游标的时候可以带参数,在is后面的select中可以使用参数
cursor my_cur（p1 number,p2 number）is
select * from emp where sal between p1 and p2;
eemp%rowtype;
begin
dbms_output.put_line（s）;
--当打开游标的时候将参数传递给游标先是SQL语句的加载参数然后
--执行SQL语句,将执行的结果赋值给游标
open my_cur（sal1,sal2）;
loop
升级会员 