PL/SQL块简介:
块结构:
Declare --定义部分
Declarations
Begin --执行部分
Executable code
Exception --异常处理部分
Exceptional handlers
End;
匿名块:
是动态生成,只能执行一次的块,没有名字,不能由其他应用程序调用;
如:
(1).编写一个块,输出 This a my block
Sql> set serveroutput on
Sql>begin
Dbms_output.put_line(‘This a my block );
End;
/
(2).输出表students中学号为103的学生姓名:
Sql> set serveroutput on
Sql>declare
v_name varchar2(10);
begin
select name into v_name from students where students_id=’103’;
dbms_output.put_line(‘学生姓名’ ||v_name );
end;
/
(3).根据输入学生的学号,输出该学生的姓名,并且考虑输入不存在的学号的情况
Sql> set serveroutput on
Sql>declare
v_name varchar2(10);
begin
select name into v_name from students
where students_id=&students_id
dbms_output.put_line(‘学生姓名’ ||v_name );
exception
when no_data_found then
dbms_output.put_line(‘输入学号不存在!’);
end;
/
命名块…….
PL/SQL基本语法要素:
字符集不区分大小写
只有PL/SQL中可以引用:-10E4,5.12e-6,7*10*2(表示为:7x10^2)
字符型文字:使用单引号如:’我是一名学生’, ’kill’
Boolean型文字:
有:True,False,Null 三个值;
注释:
单行注释:
“--注释”
多行注释:
“/*注释*/”
变量及其数据类型:
如:
LOB变量
Declare
A table.B %TYPE; --将A与表table中的B建立联系,A随B变
在PL/SQL中执行SQL语句:
执行select语句:
在表department表中查询部门编号为101的记录,并把系部名称和系部所在地显示出来,使用标量变量:
Declare
v_id departments.department_id %TYPE;
v_name departments.department_name%TYPE;
v_address departments.department_address %TYPE;
Begin
Select * into v_id,v_name,v_address from deparments
Where deparment_id=101;
Dems_output.put_line(‘系部名称’||v_name);
Dems_output.put_line(‘系部地址’||v_address);
End;
/
在表department表中查询部门编号为101的记录,并显示该生的姓名,性别,出生年月,使用记录变量:
Declare
v_student students%TYPE;
Begin
Select * into v_student
From students where student_id=101
Dbms_output.put_line(‘姓名 性别 出生年月’);
Dems_output.putline(v_student.name||v_student.sex||v_student.dob);
执行DML语句:
执行insert语句:
(1)插入一条记录(使用常量为插入记录提供数据)
Begin
Insert into students
values(101,null,’小笨’,’女’,’07-5月-1988’,’计算机’);
end;
/
(2)插入一条记录(使用变量为插入记录提供数据)
Declare
id user.id%TYPE:=1099;
name user.name%TYPE:=’小笨’;
begin
insert into user values(id,name);
end;
/
(3)插入多条记录(使用子查询为插入多条记录提供数据)
Begin
Insert into user (select * from user_two where
specialty=’计算机’);
end;
/
执行update语句:
修改一条记录(使用常量为修改记录提供数据)
Begin
Update user
Set id=101,
Name=’小笨’
Where id=103;
End;
/
修改一条记录(使用变量为修改记录提供数据)
Declare
V_id user.id%TYPE:=1099;
V_name user.name%TYPE:=’小笨’;
begin
update user
set id=v_id,
name=v_name
where id=1099;
end;
/
修改多条记录(使用子查询为修改多条记录提供数据)
如:使用子查询将奖金未定的教师的奖金更新为平均奖金
Begin
Update teachers
Set bonus=(select avg(bonus) from teachers )
Where bonus is NULL;
End;
/
执行delete语句:
删除一条记录(使用常量为插入记录提供数据)
Begin
Delete from user where id=1;
End;
/
删除一条记录(使用变量为删除记录提供数据)
Declare
v_id user.id%TYPE :=1;
Begin
Delete from user where id=v_id;
End;
/
删除多条记录(使用子查询为删除多条记录提供数据)
Begin
Delete from teachers
Where wage > (select 1.1*avg(wage) from teachers);
End;
/
执行事务处理语句:
对students表执行DML操作,在PL/SQL程序中使用commit,Rollback,savepoint等事务处理语句:
Sql>
Begin
Insert into students
values(101,null,’小笨’,’女’,’07-5月-1988’,’计算机’);
commit;
delete from students where specialty=’计算机’;
rollback;
update students set student_id=1023,
dob=’02-3月-1989’,
specialty = ‘自动化’,
where student_id = 10101;
savepoint sp1;
delete from students where student_id=10101;
savepoint sp2;
rollback to sp1;
commit;
end;
/
PL/SQL程序控制结构:
分支结构:
If语句:
IF-THEN-END IF
将讲师职称的某位教师的工资提高10%(其他教师工资不变)
Sql>declare
V_id teachers.id%Type;
V_id teachers.title%Type;
Begin
V_id : =&id;
Select title into v_title from teachers
Where id=v_id;
If v_title = ‘讲师’ then update teachers set wage=1.1*wage where id=v_id;
End if;
End;
/
IF-THEN-ELSE-END IF
将讲师职称的某位教师的工资提高10%,其他教师工资提高100元
Sql>declare
V_id teachers.id%Type;
V_id teachers.title%Type;
Begin
v_id : =&id;
Select title into v_title from teachers
Where id=v_id;
If v_title = ‘讲师’ then update teachers set wage=1.1*wage where id=v_id;
else
Update teacher
Set wage = wage + 100 where id=v_id;
End if;
End;
/
(3)IF – THEN –ELSE –THEN –ELSE-END IF
将讲师职称的某位教师的工资提高10%,其他教师职称不是
教授,而是高工或是副教授则工资提高5%,否则工资提高100
Sql>declare
V_id teachers.id%Type;
V_id teachers.title%Type;
Begin
v_id : =&id;
Select title into v_title from teachers
Where id=v_id;
If v_title = ‘讲师’ then update teachers set wage=1.1*wage where id=v_id;
elseif v_title=’高工’ or v_title=’副教授’ then
update teachers
set wage=1.05*wage where id=v_id;
esle
Update teacher
Set wage = wage + 100 where id=v_id;
End if;
End;
/
CASE语句:
1.
Declare
V_id teachers.id%Type;
V_title teachers.title%Type;
Begin
V_id : =&id;
Select title into v_title
From teachers where id=v_id;
Case v_title
When ‘教授’ then
Update teachers
Set wage=1.15*wage where id=v_id;
When ‘高工’ then
Update teachers
Set wage=1.05*wage where id=v_id;
When ‘副教授’ then
Update teachers
Set wage=1.005*wage where id=v_id;
Else
Update teachers
Set wage=wage+10 where id=v_id;
End case;
End;
/
2.
Declare
V_id teachers.id%Type;
V_title teachers.title%Type;
Begin
V_id : =&id;
Select title into v_title
From teachers where id=v_id;
Case v_title
When v_title=‘教授’ then
Update teachers
Set wage=1.15*wage where id=v_id;
When v_title=‘高工’ or v_title=’ 副教授’ then
Update teachers
Set wage=1.05*wage where id=v_id;
Else
Update teachers
Set wage=wage+100 where id=v_id;
End case;
End;
/
3.
Declare
V_id teachers.id%Type;
V_income teachers.income%Type;
Begin
V_id : =&id;
Select incmoe into v_income
From teachers where id=v_id;
Case v_income
When v_income <1000 then
Dbms_output.put_line(‘个人工资增加10%:’||v_income*1.1);
When 5000<v_income <10000 then
Dbms_output.put_line(‘个人工资减少10%:’||v_income*0.9);
end case;
End;
/
循环结构:
Loop循环:
建表:
Create table total (
V_i int,
V_sum int);
================================================
Declare
V_i int:=1;
V_sum int:=0;
Begin
Loop
V_sum :=v_sum+v_i;
Insert into total values (v_i,v_sum);
Exit when v_i = 10;
V_i : =v_i+1;
End loop;
End;
/
while循环:
declare
v_i int: =1;
v_sum int:=1;
begin
while v_i<10 loop
v_factorial : =v_factorial*v_i;
insert into total values(v_i,v_factorial);
end loop;
end;
/
3.GOTO和NULL语句:
1.goto语句:
Set serveroutput on
Declare
V_i int:=1
V_sum int: =0;
Begin
Loop
V_sum:=v_sum+v_i;
Insert into total values(v_i,v_sum);
If v_i=10 then
Goto output;
End if;
V_i := v_i+1;
End loop;
<<output>>
Dbms_output.put_line(‘v_sum’||v_sum);
End;
/
4.NULL语句
Null语句为空语句,不执行任何语句,直接将程序交给下一条语句
Set serveroutput on
Declare
V_i int:=1
V_sum int: =0;
Begin
Loop
V_sum:=v_sum+v_i;
Insert into total values(v_i,v_sum);
If v_i=10 then
Goto output;
End if;
V_i := v_i+1;
End loop;
<<output>>
Null;
End;
/
异常处理:
系统预定义异常(除数为0)ZERO_DIVIDB:
Declare
a number:=0;
b number:=100;
c number;
begin
c = b/a;
exception
when ZERO_DIVIDB then
dbms_output.put_line(‘除数为0’);
end;
/
系统预定义异常(未找到数据的错误)NO_DATA_FOUND:
Set serverout on
Declare
V_id student.id %TYPE;
V_name student.name %TYPE;
begin
v_id:=&id
select name into v_name from student where id=v_id;
dbms_output.put_line(‘姓名’||v_name);
exception
when NO_DATA_FOUND then
dbms_output.put_line(‘学号不存在!’);
end;
/
系统预定义异常(select into时返回多行数据的错误)TOO_MANY_ROWS:
Set serverout on
Declare
V_id student.id %TYPE;
V_name student.name %TYPE;
begin
v_id:=&id
select name into v_name from student where id=v_id;
dbms_output.put_line(‘姓名’||v_name);
exception
when TOO_MANY_ROWS then
dbms_output.put_line(‘返回记录多余一行!’);
end;
/
同时出现TOO_MANY_ROWS和NO_DATA_FOUND系统预定义异常:
Set serverout on
Declare
V_id student.id %TYPE;
V_name student.name %TYPE;
begin
v_id:=&id
select name into v_name from student where id=v_id;
dbms_output.put_line(‘姓名’||v_name);
Exception
when NO_DATA_FOUND then
dbms_output.put_line(‘学号不存在!’);
when TOO_MANY_ROWS then
dbms_output.put_line(‘返回记录多余一行!’);
end;
/
非预定义异常名:
在编译时,将异常名与一个Oracle错误代码相关联:
Set serverout on
Declare
E_id exception;
Pragma exception_init (E_id,-2292);
Begin
Delete from departments where id=101;
Exception
When E_id then
dbms_output.put_line(‘表中存在子目录!’);
end;
/
用户自定义异常名:
定义异常名称,安排何时抛出异常
如:当工资为负时,抛出异常
Declare
E_wage exception; --用户定义异常
V_wage teachers.wage%TYPE;
Begin
V_wage :=&wage;
Insert into teachers values(101,’小笨’,’v_wage’);
If v_wage<0 then
Raise E_wage; --抛出异常
End if;
Exception
When E_wage then
dbms_output.put_line(‘工资为负!’);
rollback;
end;
/
带when others then的异常处理:
Declare
E_wage exception; --用户定义异常
V_wage teachers.wage%TYPE;
Begin
V_wage :=&wage;
Insert into teachers values(101,’小笨’,’v_wage’);
If v_wage<0 then
Raise E_wage; --抛出异常
End if;
Exception
When E_wage then
dbms_output.put_line(‘工资为负!’);
rollback;
when others then
dbms_output.put_line(‘插入出现错误!’);
end;
/
使用异常函数:
函数SQLCODE: 获得Oracle错误代码
函数SQLERRM:获得与之相应的错误描述
Declare
E_wage exception; --用户定义异常
V_wage teachers.wage%TYPE;
Begin
V_wage :=&wage;
Insert into teachers values(101,’小笨’,’v_wage’);
If v_wage<0 then
Raise E_wage; --抛出异常
End if;
Exception
When E_wage then
dbms_output.put_line(‘工资为负!’);
rollback;
when others then
dbms_output.put_line(‘错误代码:’||SQLCODE);
dbms_output.put_line(‘错误描述:’||SQLERRM);
end;
/
块结构:
Declare --定义部分
Declarations
Begin --执行部分
Executable code
Exception --异常处理部分
Exceptional handlers
End;
匿名块:
是动态生成,只能执行一次的块,没有名字,不能由其他应用程序调用;
如:
(1).编写一个块,输出 This a my block
Sql> set serveroutput on
Sql>begin
Dbms_output.put_line(‘This a my block );
End;
/
(2).输出表students中学号为103的学生姓名:
Sql> set serveroutput on
Sql>declare
v_name varchar2(10);
begin
select name into v_name from students where students_id=’103’;
dbms_output.put_line(‘学生姓名’ ||v_name );
end;
/
(3).根据输入学生的学号,输出该学生的姓名,并且考虑输入不存在的学号的情况
Sql> set serveroutput on
Sql>declare
v_name varchar2(10);
begin
select name into v_name from students
where students_id=&students_id
dbms_output.put_line(‘学生姓名’ ||v_name );
exception
when no_data_found then
dbms_output.put_line(‘输入学号不存在!’);
end;
/
命名块…….
PL/SQL基本语法要素:
字符集不区分大小写
只有PL/SQL中可以引用:-10E4,5.12e-6,7*10*2(表示为:7x10^2)
字符型文字:使用单引号如:’我是一名学生’, ’kill’
Boolean型文字:
有:True,False,Null 三个值;
注释:
单行注释:
“--注释”
多行注释:
“/*注释*/”
变量及其数据类型:
如:
LOB变量
Declare
A table.B %TYPE; --将A与表table中的B建立联系,A随B变
在PL/SQL中执行SQL语句:
执行select语句:
在表department表中查询部门编号为101的记录,并把系部名称和系部所在地显示出来,使用标量变量:
Declare
v_id departments.department_id %TYPE;
v_name departments.department_name%TYPE;
v_address departments.department_address %TYPE;
Begin
Select * into v_id,v_name,v_address from deparments
Where deparment_id=101;
Dems_output.put_line(‘系部名称’||v_name);
Dems_output.put_line(‘系部地址’||v_address);
End;
/
在表department表中查询部门编号为101的记录,并显示该生的姓名,性别,出生年月,使用记录变量:
Declare
v_student students%TYPE;
Begin
Select * into v_student
From students where student_id=101
Dbms_output.put_line(‘姓名 性别 出生年月’);
Dems_output.putline(v_student.name||v_student.sex||v_student.dob);
执行DML语句:
执行insert语句:
(1)插入一条记录(使用常量为插入记录提供数据)
Begin
Insert into students
values(101,null,’小笨’,’女’,’07-5月-1988’,’计算机’);
end;
/
(2)插入一条记录(使用变量为插入记录提供数据)
Declare
id user.id%TYPE:=1099;
name user.name%TYPE:=’小笨’;
begin
insert into user values(id,name);
end;
/
(3)插入多条记录(使用子查询为插入多条记录提供数据)
Begin
Insert into user (select * from user_two where
specialty=’计算机’);
end;
/
执行update语句:
修改一条记录(使用常量为修改记录提供数据)
Begin
Update user
Set id=101,
Name=’小笨’
Where id=103;
End;
/
修改一条记录(使用变量为修改记录提供数据)
Declare
V_id user.id%TYPE:=1099;
V_name user.name%TYPE:=’小笨’;
begin
update user
set id=v_id,
name=v_name
where id=1099;
end;
/
修改多条记录(使用子查询为修改多条记录提供数据)
如:使用子查询将奖金未定的教师的奖金更新为平均奖金
Begin
Update teachers
Set bonus=(select avg(bonus) from teachers )
Where bonus is NULL;
End;
/
执行delete语句:
删除一条记录(使用常量为插入记录提供数据)
Begin
Delete from user where id=1;
End;
/
删除一条记录(使用变量为删除记录提供数据)
Declare
v_id user.id%TYPE :=1;
Begin
Delete from user where id=v_id;
End;
/
删除多条记录(使用子查询为删除多条记录提供数据)
Begin
Delete from teachers
Where wage > (select 1.1*avg(wage) from teachers);
End;
/
执行事务处理语句:
对students表执行DML操作,在PL/SQL程序中使用commit,Rollback,savepoint等事务处理语句:
Sql>
Begin
Insert into students
values(101,null,’小笨’,’女’,’07-5月-1988’,’计算机’);
commit;
delete from students where specialty=’计算机’;
rollback;
update students set student_id=1023,
dob=’02-3月-1989’,
specialty = ‘自动化’,
where student_id = 10101;
savepoint sp1;
delete from students where student_id=10101;
savepoint sp2;
rollback to sp1;
commit;
end;
/
PL/SQL程序控制结构:
分支结构:
If语句:
IF-THEN-END IF
将讲师职称的某位教师的工资提高10%(其他教师工资不变)
Sql>declare
V_id teachers.id%Type;
V_id teachers.title%Type;
Begin
V_id : =&id;
Select title into v_title from teachers
Where id=v_id;
If v_title = ‘讲师’ then update teachers set wage=1.1*wage where id=v_id;
End if;
End;
/
IF-THEN-ELSE-END IF
将讲师职称的某位教师的工资提高10%,其他教师工资提高100元
Sql>declare
V_id teachers.id%Type;
V_id teachers.title%Type;
Begin
v_id : =&id;
Select title into v_title from teachers
Where id=v_id;
If v_title = ‘讲师’ then update teachers set wage=1.1*wage where id=v_id;
else
Update teacher
Set wage = wage + 100 where id=v_id;
End if;
End;
/
(3)IF – THEN –ELSE –THEN –ELSE-END IF
将讲师职称的某位教师的工资提高10%,其他教师职称不是
教授,而是高工或是副教授则工资提高5%,否则工资提高100
Sql>declare
V_id teachers.id%Type;
V_id teachers.title%Type;
Begin
v_id : =&id;
Select title into v_title from teachers
Where id=v_id;
If v_title = ‘讲师’ then update teachers set wage=1.1*wage where id=v_id;
elseif v_title=’高工’ or v_title=’副教授’ then
update teachers
set wage=1.05*wage where id=v_id;
esle
Update teacher
Set wage = wage + 100 where id=v_id;
End if;
End;
/
CASE语句:
1.
Declare
V_id teachers.id%Type;
V_title teachers.title%Type;
Begin
V_id : =&id;
Select title into v_title
From teachers where id=v_id;
Case v_title
When ‘教授’ then
Update teachers
Set wage=1.15*wage where id=v_id;
When ‘高工’ then
Update teachers
Set wage=1.05*wage where id=v_id;
When ‘副教授’ then
Update teachers
Set wage=1.005*wage where id=v_id;
Else
Update teachers
Set wage=wage+10 where id=v_id;
End case;
End;
/
2.
Declare
V_id teachers.id%Type;
V_title teachers.title%Type;
Begin
V_id : =&id;
Select title into v_title
From teachers where id=v_id;
Case v_title
When v_title=‘教授’ then
Update teachers
Set wage=1.15*wage where id=v_id;
When v_title=‘高工’ or v_title=’ 副教授’ then
Update teachers
Set wage=1.05*wage where id=v_id;
Else
Update teachers
Set wage=wage+100 where id=v_id;
End case;
End;
/
3.
Declare
V_id teachers.id%Type;
V_income teachers.income%Type;
Begin
V_id : =&id;
Select incmoe into v_income
From teachers where id=v_id;
Case v_income
When v_income <1000 then
Dbms_output.put_line(‘个人工资增加10%:’||v_income*1.1);
When 5000<v_income <10000 then
Dbms_output.put_line(‘个人工资减少10%:’||v_income*0.9);
end case;
End;
/
循环结构:
Loop循环:
建表:
Create table total (
V_i int,
V_sum int);
================================================
Declare
V_i int:=1;
V_sum int:=0;
Begin
Loop
V_sum :=v_sum+v_i;
Insert into total values (v_i,v_sum);
Exit when v_i = 10;
V_i : =v_i+1;
End loop;
End;
/
while循环:
declare
v_i int: =1;
v_sum int:=1;
begin
while v_i<10 loop
v_factorial : =v_factorial*v_i;
insert into total values(v_i,v_factorial);
end loop;
end;
/
3.GOTO和NULL语句:
1.goto语句:
Set serveroutput on
Declare
V_i int:=1
V_sum int: =0;
Begin
Loop
V_sum:=v_sum+v_i;
Insert into total values(v_i,v_sum);
If v_i=10 then
Goto output;
End if;
V_i := v_i+1;
End loop;
<<output>>
Dbms_output.put_line(‘v_sum’||v_sum);
End;
/
4.NULL语句
Null语句为空语句,不执行任何语句,直接将程序交给下一条语句
Set serveroutput on
Declare
V_i int:=1
V_sum int: =0;
Begin
Loop
V_sum:=v_sum+v_i;
Insert into total values(v_i,v_sum);
If v_i=10 then
Goto output;
End if;
V_i := v_i+1;
End loop;
<<output>>
Null;
End;
/
异常处理:
系统预定义异常(除数为0)ZERO_DIVIDB:
Declare
a number:=0;
b number:=100;
c number;
begin
c = b/a;
exception
when ZERO_DIVIDB then
dbms_output.put_line(‘除数为0’);
end;
/
系统预定义异常(未找到数据的错误)NO_DATA_FOUND:
Set serverout on
Declare
V_id student.id %TYPE;
V_name student.name %TYPE;
begin
v_id:=&id
select name into v_name from student where id=v_id;
dbms_output.put_line(‘姓名’||v_name);
exception
when NO_DATA_FOUND then
dbms_output.put_line(‘学号不存在!’);
end;
/
系统预定义异常(select into时返回多行数据的错误)TOO_MANY_ROWS:
Set serverout on
Declare
V_id student.id %TYPE;
V_name student.name %TYPE;
begin
v_id:=&id
select name into v_name from student where id=v_id;
dbms_output.put_line(‘姓名’||v_name);
exception
when TOO_MANY_ROWS then
dbms_output.put_line(‘返回记录多余一行!’);
end;
/
同时出现TOO_MANY_ROWS和NO_DATA_FOUND系统预定义异常:
Set serverout on
Declare
V_id student.id %TYPE;
V_name student.name %TYPE;
begin
v_id:=&id
select name into v_name from student where id=v_id;
dbms_output.put_line(‘姓名’||v_name);
Exception
when NO_DATA_FOUND then
dbms_output.put_line(‘学号不存在!’);
when TOO_MANY_ROWS then
dbms_output.put_line(‘返回记录多余一行!’);
end;
/
非预定义异常名:
在编译时,将异常名与一个Oracle错误代码相关联:
Set serverout on
Declare
E_id exception;
Pragma exception_init (E_id,-2292);
Begin
Delete from departments where id=101;
Exception
When E_id then
dbms_output.put_line(‘表中存在子目录!’);
end;
/
用户自定义异常名:
定义异常名称,安排何时抛出异常
如:当工资为负时,抛出异常
Declare
E_wage exception; --用户定义异常
V_wage teachers.wage%TYPE;
Begin
V_wage :=&wage;
Insert into teachers values(101,’小笨’,’v_wage’);
If v_wage<0 then
Raise E_wage; --抛出异常
End if;
Exception
When E_wage then
dbms_output.put_line(‘工资为负!’);
rollback;
end;
/
带when others then的异常处理:
Declare
E_wage exception; --用户定义异常
V_wage teachers.wage%TYPE;
Begin
V_wage :=&wage;
Insert into teachers values(101,’小笨’,’v_wage’);
If v_wage<0 then
Raise E_wage; --抛出异常
End if;
Exception
When E_wage then
dbms_output.put_line(‘工资为负!’);
rollback;
when others then
dbms_output.put_line(‘插入出现错误!’);
end;
/
使用异常函数:
函数SQLCODE: 获得Oracle错误代码
函数SQLERRM:获得与之相应的错误描述
Declare
E_wage exception; --用户定义异常
V_wage teachers.wage%TYPE;
Begin
V_wage :=&wage;
Insert into teachers values(101,’小笨’,’v_wage’);
If v_wage<0 then
Raise E_wage; --抛出异常
End if;
Exception
When E_wage then
dbms_output.put_line(‘工资为负!’);
rollback;
when others then
dbms_output.put_line(‘错误代码:’||SQLCODE);
dbms_output.put_line(‘错误描述:’||SQLERRM);
end;
/