内容主要包含：
(1)三种循环及其简化
(2)游标的使用
(3)异常处理
(4)存储过程
(5)存储函数
(6)触发器
(7)其他pl/sql操作
---------------loop循环定义变量--------------------
declarecursor c1 is select * from emp;
#
rec emp%rowtype;
#
i numeber:=1;
#
v_count number;
begin
#
select count(*) into v_count from emp;
#(1)
open c1;
loop
fetch c1 into rec.EMPNO,rec.JOB;
insert into emp1(EMPNO,JOB) values(rec.EMPNO,rec.JOB)
i:=i+1;
exit when i>v_count;
end loop;
close c1;
end;
---------------loop循环--------------------
declare
cursor c1 is select * from emp;
#
rec emp%rowtype;
begin
#(1)
open c1;
loop
fetch c1 into rec.EMPNO,rec.JOB;
exit when c1%notfound;
insert into emp1(EMPNO,JOB) values(rec.EMPNO,rec.JOB)
end loop;
close c1;
end;
---------------loop循环简化--------------------
declare
cursor c1 is select * from emp;
#
rec c1%rowtype;
begin
#(1)
open c1;
loop
fetch c1 into rec;
exit when c1%notfound;
insert into emp1 values rec
end loop;
close c1;
end;
---------------while循环--------------------
declare
cursor c1 is select * from emp;
#
rec emp%rowtype;
begin
open c1;
fetch c1 into rec.EMPNO,rec.JOB;
while c1%found loop
insert into emp1(EMPNO,JOB) values(rec.EMPNO,rec.JOB)
fetch c1 into rec.EMPNO,rec.JOB;
end loop;
close c1;
end;
---------------for循环，最简洁，不易产生死循环，自己关闭--------------------
declare
#记录集
cursor c1 is select * from emp;
#一行记录
rec emp%rowtype;
begin
#for默认打开游标
for rec in c1 loop
insert into emp1(EMPNO,JOB) values(rec.EMPNO,rec.JOB)
end loop;
end;
---------------for循环，简化--------------------
declare
#记录集
cursor c1 is select * from emp;
begin
#for默认打开游标
#for隐含声明rec,所以不用声明了
for rec in c1 loop
insert into emp1(EMPNO,JOB) values(rec.EMPNO,rec.JOB)
end loop;
end;
---------------for循环，再简化--------------------
declare
begin
#for默认打开游标
#for隐含声明rec,所以不用声明了
for rec in (select * from emp) loop
insert into emp1 values rec
end loop;
end;
---------------带参数的游标--------------------
declare
cursor c1(p_deptno number) is select * from emp;
#
rec emp%rowtype;
begin
#(1)
open c1(10);
loop
fetch c1 into rec.EMPNO,rec.JOB;
exit when c1%notfound;
insert into emp1(EMPNO,JOB) values(rec.EMPNO,rec.JOB)
end loop;
close c1;
end;
---------------打印--------------------
dbms_output.put_line(v_val);
---------------例外--------------------
Oracle预定义例外
Oracle错误
用户定义的例外
---------------使用例外----------------
定义例外
捕获例外
处理例外
EXCEPTION
WHEN TOO_MANY_ROWS THEN
NULL;
WHEN NO_DATA_FOUND THEN
NULL;
END;
---------------添加主键----------------
alter table
add constraint EMP_PK primary key;
---------------自定义例外--------------
DECLARE
MY_EXCEPTION EXCEPTION
PRAGMA EXCEPTION_INIT(MY_EXCEPTION,-00001);
BEGIN
INSERT INTO department() values();
EXCEPTION
WHEN MY_EXCEPTION THEN
# DBMS_OUTPUT.PUT_LINE(‘ERROR MESSAGES‘);
RAISE_APPLICATION_ERROR(-1111111，‘STH‘);
END;
---------------定义变量--------------
v_1 data:=sysdata
---------------捕获例外--------------
raise el;
DECLARE
v_ddrq data:=sysdata;
v_fhrq data:=sysdata-1;
MY_EXCEPTION EXCEPTION
BEGIN
INSERT INTO item(id,ddrq,fhrq) values(v_ddrq,v_fhrq);
IF v_ddrq>v_fhrq THEN
RAISE MY_EXCEPTION;
END IF;
EXCEPTION
WHEN MY_EXCEPTION THEN
# DBMS_OUTPUT.PUT_LINE(‘ERROR MESSAGES‘);
ROLLBACK;
RAISE_APPLICATION_ERROR(-1111111，‘STH‘);
END;
---------------procedure--------------
CREATE or replace PROCEDURE get_sal(p_empno number)
is
v_sal emp.sal%type;
BEGIN
SELECT sal INTO v_sal FROM emp WHERE empno=p_empno;
DBMS_OUTPUT.PUTLINE(v_sal);
EDN;
sqlplus调用(每个语言有各自的调用方法)
execute get_sal(7269)
pl/sql调用
declare
begin
get_sal(7369);
end;
-----------过程与函数的区别------------
函数只有一个输出
过程有多个
-----------过程,输出参数------------
CREATE or replace PROCEDURE get_info
(p_empno number,
p_ename out varchar2,
p_deptno out number,
p_sal out emp.sal%type)
is
v_deptno emp.depyno%type;
v_ename emp.ename%type;
v_sal emp.sal%type;
BEGIN
SELECT sal,ename,deptno
INTO v_sal,v_ename,v_deptno
FROM emp
WHERE empno=p_empno;
p_ename:=v_ename;
p_deptno:=v_deptno;
p_sal:=v_sal;
EDN;
调用pl/sql
variable v_ename varchar2(20)
variable v_sal int;
variable v_deptno
execure get_info(7360,:v_ename,:v_deptno,:v_sal)
print v_sal; ...
-----------过程,混合参数------------
CREATE or replace PROCEDURE chang_no
(p_no in out varchar2)
is
BEGIN
p_no:=substr(p_no,1,3||‘0‘||substr(p_no,4));
EDN;
调用pl/sql
variable v_no varchar2(20)
declare
begin
:v_no:=‘1234567890‘;
execure get_no(:v_no)
print v_no;
-----------参数调用------------
顺序
名字：形参 实参
variable v_name varchar2(20)
variable v_emptno
(p_name=>:v_name,p_empno=>:v_empno)
-----------存储函数(可以在sql、pl/sql语句中调用，不能作为语句的一部分)------------
CREATE OR REPLACE FUNCTION add_sal(p_empno number)
RETURN number
IS
v_sal emp.sal%type;
BEGIN
select sal into v_sal from emp
where empno=p_empno;
RETURN v_sal*1.1;
END;
pl/sql调用
declare
v_sal:=emp.sal%type;
begin
v_sal:=add_sal(7369);
dbms_output.put_line(v_sal);
end;
sql调用
select ename,sal,add_sal(empno) from emp;
-----------触发器------------
事前校验
事后审计
语句级
行级
DML
-----------语句级触发器-----------
CREATE OR REPLACE TRIGGER trig1
BEFORE INSERT or UPDATE or DELETE ON emp
DECLARE
v_string varchar(20):="you can‘t option data";
BEGIN
IF(TO_CHAR(SYSYDATE,‘DY‘) IN (‘SAT‘,‘SUN‘)) OR (TO_CHAR(SYSDATE,‘HH24:MI‘))
NOT BETWEEN ‘08:00‘ AND ‘18:00‘)THEN
RAISE_APPLICATION_ERROE(-20500，v_error);
END IF;
END;
-----------行级触发器-----------
CREATE OR REPLACE TRIGGER trig1
BEFORE INSERT or UPDATE or DELETE ON emp
FOR EACH ROW
WHEN (OLD.depto=10)
DECLARE
v_string varchar(20):="you can‘t option data";
BEGIN
IF(TO_CHAR(SYSYDATE,‘DY‘) IN (‘SAT‘,‘SUN‘)) OR (TO_CHAR(SYSDATE,‘HH24:MI‘))
NOT BETWEEN ‘08:00‘ AND ‘18:00‘)THEN
RAISE_APPLICATION_ERROE(-20500，v_error);
END IF;
END;