PL/SQL语法与实例

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;

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