PL/SQL语法与实例

[size=large]PL/SQL块语法:

[DECLARE]

--declaration statements

BEGIN

--executable statements

[EXCEPTION]

--exception statements

END

PL/SQL块中的每一条语句都必须以分号结束,SQL语句是多行的,但分号表示该语句的结束.一行中可以有多条SQL语句,它们之间以分号分隔.每一个PL/SQL块由Begin或Declare开始,以End结束.注释由

--标示.

变量赋值: 用":="符号

v_num number :=10;

注:dbms_output.put_line()不能打印boolean类型的值.


第一个PL/SQL块:

SQL> begin
2 dbms_output.put_line('Hello World!');
3 end;
4 /

PL/SQL 过程已成功完成。

SQL> set serveroutput on;
SQL> begin
2 dbms_output.put_line('HelloWorld!');
3 end;
4 /
HelloWorld!

PL/SQL 过程已成功完成。

declare的实例:

SQL> declare
2 v_name varchar2(20);
3 begin
4 v_name :='myname';
5 dbms_output.put_line(v_name);
6 end;
7 /
myname

PL/SQL 过程已成功完成。

declare exception 的实例:

SQL> declare
2 v_num number :=0;
3 begin
4 v_num :=2/v_num;
5 dbms_output.put_line(v_num);
6 end;
7 /
declare
*
第 1 行出现错误:
ORA-01476: 除数为 0
ORA-06512: 在 line 4


SQL> ed
已写入 file afiedt.buf

1 declare
2 v_num number :=0;
3 begin
4 v_num :=2/v_num;
5 dbms_output.put_line(v_num);
6 exception
7 when others then
8 dbms_output.put_line('error');
9* end;
SQL> /
error

PL/SQL 过程已成功完成。

PL/SQL变量的声明规则:


常用的变量类型:

binary_integer :整数,主要用来计数而不是用来表示字段类型

number :数字类型

char :字长字符串

varchar2 :变长字符串

date :日期

long :长字符串,最长2GB

boolean :布尔类型,可以取值为true,false 和null值.


变量声明,使用%type属性

SQL> declare
2 v_empno number(4);
3 v_empno2 emp.empno%type;
4 v_empno3 v_empno2%type;
5 begin
6 dbms_output.put_line('HPJIANHUA');
7 end;
8 /
HPJIANHUA

PL/SQL 过程已成功完成。

--Table 变量类型:也是 自定义的变量类型

1 declare
2 type type_table_emp_empno is table of emp.empno%type index by binary_integer;
3 v_empnos type_table_emp_empno;
4 begin
5 v_empnos(0) :=4323;
6 v_empnos(2) :=2342;
7 v_empnos(-1) :=9999;
8 dbms_output.put_line(v_empnos(-1));
9* end;
SQL> /
9999

PL/SQL 过程已成功完成。

--Record变量类型

1 declare
2 type type_record_dept is record
3 (
4 deptno dept.deptno%type,
5 dname dept.dname%type,
6 loc dept.loc%type
7 );
8 v_temp type_record_dept;
9 begin
10 v_temp.deptno :=52;
11 v_temp.dname :='hpjianhua';
12 v_temp.loc :='HK';
13 dbms_output.put_line(v_temp.deptno ||' '||v_temp.dname);
14* end;
SQL> /
52 hpjianhua

PL/SQL 过程已成功完成。

--使用%rowtype声明record变量

1 declare
2 v_temp dept%rowtype;
3 begin
4 v_temp.dname :='hpjianhua';
5 v_temp.deptno :=50;
6 v_temp.loc :='HK';
7 dbms_output.put_line(v_temp.deptno ||' '||v_temp.dname);
8* end;
SQL> /
50 hpjianhua

--SQL语句的使用:

1 declare
2 v_ename emp.ename%type;
3 v_sal emp.sal%type;
4 begin
5 select ename,sal into v_ename,v_sal from emp where empno = 7369;
6 dbms_output.put_line(v_ename || ' ' || v_sal);
7* end;
SQL> /
SMITH 800

--sql%rowcount的使用:

1 declare
2 v_deptno emp2.deptno%type :=10;
3 v_count number;
4 begin
5 --update emp2 set sal =sal/2 where deptno =v_deptno;
6 --select deptno into v_deptno from emp2 where empno = 7369;
7 dbms_output.put_line(sql%rowcount || '条记录被影响!');
8 commit;
9* end;
SQL> /
条记录被影响!

PL/SQL 过程已成功完成。

--PL/SQL的DDL语句:

建表:

SQL> begin
2 execute immediate 'create table T(nn varchar2(20) default ''aaa'')';
3 end;
4 /

PL/SQL 过程已成功完成。

--条件的语句:

实例:

1 declare
2 v_sal emp.sal%type;
3 begin
4 select sal into v_sal from emp
5 where empno = 7369;
6 if(v_sal<1200) then
7 dbms_output.put_line('low');
8 elsif(v_sal < 2000) then
9 dbms_output.put_line('middle');
10 else
11 dbms_output.put_line('high');
12 end if;
13* end;
SQL> /
low

PL/SQL 过程已成功完成。

--循环语句:

实例:
SQL> declare
2 i binary_integer :=1;
3 begin
4 loop
5 dbms_output.put_line(i);
6 i :=i+1;
7 exit when( i>=11);
8 end loop;
9 end;
10 /
1
2
3
4
5
6
7
8
9
10

PL/SQL 过程已成功完成。

For循环:顺序与反序

实例:

SQL> begin
2 for k in 1..10 loop
3 dbms_output.put_line(k);
4 end loop;
5
6 for k in reverse 1..10 loop
7 dbms_output.put_line(k);
8 end loop;
9 end;
10 /
1
2
3
4
5
6
7
8
9
10
10
9
8
7
6
5
4
3
2
1

PL/SQL 过程已成功完成。

错误处理:

太多记录数:

1 declare
2 v_temp number(4);
3 begin
4 select empno into v_temp from emp where deptno =10;
5 exception
6 when too_many_rows then
7 dbms_output.put_line('Too many records');
8 when others then
9 dbms_output.put_line('error');
10* end;
SQL> /
Too many records

没有数据:

SQL> declare
2 v_temp number(4);
3 begin
4 select empno into v_temp from emp where empno = 2222;
5 exception
6 when no_data_found then
7 dbms_output.put_line('No data!');
8 end;
9 /
No data!

下面介绍一种DBA经常使用的方法:

创建一张表:
SQL> create table errorlog
2 (
3 id number primary key,
4 errcode number,
5 errmsg varchar2(1024),
6 errdate date
7 );

表已创建。

再创建一序列:

SQL> create sequence seq_errorlog_id start with 1 increment by 1;

序列已创建。

1 declare
2 v_deptno dept.deptno%type :=10;
3 v_errcode number;
4 v_errmsg varchar2(1024);
5 begin
6 delete from dept where deptno =v_deptno;
7 commit;
8 exception
9 when others then
10 rollback;
11 v_errcode :=SQLCODE;
12 v_errmsg :=SQLERRM;
13 insert into errorlog values (seq_errorlog_id.nextval,v_errcode,v_errmsg,sys
date);
14 commit;
15* end;
SQL> /

PL/SQL 过程已成功完成。

SQL> select * from errorlog;

ID ERRCODE
---------- ----------
ERRMSG
--------------------------------------------------------------------------------

ERRDATE
--------------
1 -2292
ORA-02292: 违反完整约束条件 (HPJIANHUA.FK_DEPTNO) - 已找到子记录
01-6月 -09


SQL> delete from dept where deptno = 10;
delete from dept where deptno = 10
*
第 1 行出现错误:
ORA-02292: 违反完整约束条件 (HPJIANHUA.FK_DEPTNO) - 已找到子记录


SQL> select to_char(errdate,'YYYY-MM-DD HH24:MI:SS') from errorlog;

TO_CHAR(ERRDATE,'YY
-------------------
2009-06-01 19:38:59

注:SQL> select to_char(errdate,'YYYY-MM-DD HH24:MI:SS') from errorlog;

是格式化输出时间的查询.

[/size]