PL/SQL匿名块详解
摘要:PL/SQL是Oracle下的脚本开发语言,是为了解决数据下一些复杂的应用而设计的。一些复杂的应用,仅靠一条SQL语句并不能完成,为了解决这个问题,各个数据库产品都设计了自己的脚本编程语言,Oracle下叫做PL/SQL,SQL Server下叫做Transact SQL。
--HelloWorld开始
set serveroutput on;
begin
dbms_output.put_line(
'Hello World!!!'
);
end;
/
--最简单的语句块
declare
v_word varchar(
20
);
begin
v_word:=
'Hello World!!!'
;
dbms_output.put_line(v_word);
end;
/
--语句块的组成
declare
v_num number:=
0
;
begin
v_num:=
2
/v_num;
dbms_output.put_line(v_num);
exception
when others then
dbms_output.put_line(
'error'
);
end;
/
--变量的声明
declare
v_temp number(
1
);
v_count binary_integer :=
0
;
v_sal number(
7
,
2
):=
3000.00
;
v_date date:=sysdate;
v_pi_constrant number(
3
,
2
):=
3.14
;
v_valid
boolean
:=
false
;
v_name varchar2(
20
) not
null
:=
'My Name'
;
begin
dbms_output.put_line(
'v_temp value:'
|| v_temp);
end;
/
--变量声明,使用%type属性
declare
v_empno number(
4
);
v_empno2 emp.empno%type;
v_empno3 v_empno2%type;
begin
dbms_output.put_line(
'test...'
);
end;
/
--简单变量赋值
declare
v_name varchar2(
20
);
v_sal number(
7
,
2
);
v_sal2 number(
7
,
2
);
v_valid
boolean
:=
false
;
v_date date;
begin
v_name :=
'My Name'
;
v_sal :=
23.77
;
v_sal2 :=
23.77
;
v_valid := (v_sal=v_sal2);
v_date := to_date(
'2010-08-04 22:58:58'
,
'YYYY-MM-DD HH24-MI-SS'
);
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(
1
) :=
7839
;
v_empnos(-
1
) :=
9999
;
dbms_output.put_line(v_empnos(-
1
));
end;
/
--Record变量类型
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 :=
'dname'
;
v_temp.loc :=
'BJ'
;
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 :=
'dname'
;
v_temp.loc :=
'BJ'
;
dbms_output.put_line(v_temp.deptno ||
' '
|| v_temp.dname);
end;
/
--SQL语句应用
declare
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
select ename,sal into v_ename,v_sal from emp where empno=
7900
;
--select ename,sal into v_ename,v_sal from emp where deptno=
30
;
dbms_output.put_line(v_ename ||
' '
|| v_sal);
end;
/
--SQL语句应用
declare
v_emp emp%rowtype;
begin
select * into v_emp from emp where empno=
7369
;
dbms_output.put_line(v_emp.ename);
end;
/
--SQL语句应用
create table dept2(
deptno number,
dname varchar2(
50
),
loc varchar2(
50
)
);
declare
v_deptno dept.deptno%type :=
50
;
v_dname dept.dname%type :=
'My Name'
;
v_loc dept.loc%type :=
'BJ'
;
begin
insert into dept2 values(v_deptno,v_dname,v_loc);
end;
/
select * from dept2;
--drop table dept2;
--SQL语句应用
declare
v_deptno dept2.deptno%type :=
30
;
v_count number;
begin
select deptno into v_deptno from dept2 where deptno=
50
;
select count(*) into v_count from dept2;
dbms_output.put_line(
'deptno:'
|| v_deptno);
dbms_output.put_line(
'count:'
|| v_count);
dbms_output.put_line(sql%rowcount ||
'条记录被影响...'
);
end;
/
--使用DDL语句
begin
execute immediate
'create table T(nnn varchar2(20))'
;
end;
/
drop table T;
--选择语句
declare
v_sal emp.sal%type;
begin
select sal into v_sal from emp where empno=
7369
;
if
(v_sal <
1200
)then
dbms_output.put_line(
'low'
);
elsif(v_sal <
2000
) then
dbms_output.put_line(
'middle'
);
else
dbms_output.put_line(
'high'
);
end
if
;
end;
/
--循环语句
declare
i binary_integer :=
1
;
begin
loop
dbms_output.put_line(
'=='
|| i);
i := i+
1
;
exit when(i>=
11
);
end loop;
end;
/
--循环语句
declare
j binary_integer :=
1
;
begin
while
j<
11
loop
dbms_output.put_line(
'=='
|| j);
j := j+
1
;
end loop;
end;
/
--循环语句
begin
for
k in
1
..
10
loop
dbms_output.put_line(
'=='
|| k);
end loop;
for
k in reverse
1
..
10
loop
dbms_output.put_line(
'=='
|| k);
end loop;
end;
/
--错误处理
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;
/
--错误处理
declare
v_temp number(
4
);
begin
select empno into v_temp from emp where empno=
2222
;
exception
when no_data_found then
dbms_output.put_line(
'没有数据...'
);
when others then
dbms_output.put_line(
'error...'
);
end;
/
--游标
declare
cursor c is select * from emp;
v_emp c%rowtype;
begin
open c;
fetch c into v_emp;
dbms_output.put_line(v_emp.ename);
close c;
end;
/
--游标
declare
cursor c is select * from emp;
v_emp c%rowtype;
begin
open c;
loop
fetch c into v_emp;
exit when(c%notfound);
dbms_output.put_line(v_emp.ename);
end loop;
close c;
end;
/
--游标
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;
/
--游标
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;
--v_temp c%rowtype;
begin
--open c(
30
,
'clerk'
);
for
v_temp in c(
30
,
'CLERK'
) loop
dbms_output.put_line(v_temp.ename ||
' '
|| v_temp.sal);
end loop;
end;
/
--可以更新的游标
create table emp2 as(
select * from emp
);
drop table emp2;
declare
cursor c is select * from emp2
for
update;
--v_temp c%rowtype;
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
delete from emp2 where current of c;
end
if
;
end loop;
commit;
end;
/
|