plsql

[color=red][size=x-large]PL/SQL学习[/size][/color]
[color=blue]PL/SQL块语法:[/color]
[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类型的值.
[color=blue]PL/SQL实例[/color]
SQL> set serveroutput on;
SQL> begin
2 dbms_output.put_line('HelloWorld!');
3 end;
4 /
HelloWorld!
PL/SQL 过程已成功完成。
[color=green]declare的实例:[/color]
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 过程已成功完成。
[color=green]declare exception 的实例:[/color]
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变量的声明规则:

[color=blue]
常用的变量类型:[/color]
binary_integer :整数,主要用来计数而不是用来表示字段类型
number :数字类型
char :字长字符串
varchar2 :变长字符串
date :日期
long :长字符串,最长2GB
boolean :布尔类型,可以取值为true,false 和null值.

[color=green]变量声明,使用%type属性[/color]
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 过程已成功完成。
[color=green]--Table 变量类型:也是 自定义的变量类型[/color]
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 过程已成功完成。

[color=green]--Record变量类型[/color]
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 过程已成功完成。

[color=green]--使用%rowtype声明record变量[/color]
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
[color=green]--SQL语句的使用:[/color]
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
[color=green]--sql%rowcount的使用:[/color]
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 过程已成功完成。
[color=green]--PL/SQL的DDL语句:[/color]
建表:
SQL> begin
2 execute immediate 'create table T(nn varchar2(20) default ''aaa'')';
3 end;
4 /
PL/SQL 过程已成功完成。
[color=green]--条件的语句:[/color]
实例:
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 过程已成功完成。
[color=green]--循环语句:[/color]
实例:
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 过程已成功完成。
[color=green]For循环:顺序与反序[/color]
实例:
SQL> begin
2 for k in 1..10 loop
3 dbms_output.put_line(k);
4 end loop;
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 过程已成功完成。
错误处理:
[color=green]太多记录数:[/color]
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
[color=green]没有数据:[/color]
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 );

表已创建。
[color=green]再创建一序列:[/color]
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;
是格式化输出时间的查询.
引用：http://hpjianhua.iteye.com/blog/399868