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; / |