PLSQL语法介绍(有例子带注释)

PLSQL语法介绍(有例子带注释)

关键字: oracle/plsql/游标/存储过程/触发器

--最简单的语句块

set serveroutput on; //用于输出显示

begin

dbms_output.put_line('HeloWorld');

end;

--一个简单的PL/SQL语句块

declare //声明变量，必须 v_ 开头

v_name varchar2(20);

begin

v_name := 'myname'; //变量的赋值格式

dbms_output.put_line(v_name);

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;

--变量声明的规则

1): 变量名不能够使用保留字，如from、select等

2): 第一个字符必须是字母

3): 变量名最多包含30个字符

4): 不要与数据库的表或者列同名

5): 每一行只能声明一个变量

--常用变量类型

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

2): number: 数字类型

3): char: 定长字符串

4): varchar2: 变长字符串

5): date: 日期

6): long: 长字符串，最长2GB

7): boolean: 布尔类型，可以取值为 true、false和null

--变量声明，可以使用 %type 属性

declare

v_empno number(4);

v_empno2 emp.empno%type;//表示该变量的类型和emp表中的empno字

段保持一致。

v_empno3 v_empno2%type;

begin

dbms_output.put_line('Test');

--Table变量类型 //类似于java中的数组

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) := 100;

v_empnos(2) := 200;

v_empnos(-1):= 300;

dbms_output.put_line(v_empnos(-1));

end;

--Record变量类型 //类似于java中的类，可以表示一整条记录

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 := 'aaa';

v_temp.loc := 'bj';

dbms_output.put_line(v_temp.deptno || '' || v_temp.dname); end;

不过当表增加了一个字段之后它就不管用了，可以选用下面的这种： %rowtype --使用%rowtype声明Record类型变量

declare

v_temp dept%rowtype;

begin

v_temp.deptno := 50;

v_temp.dname := 'aaa';

v_temp.loc := 'bj';

dbms_output.put_line(v_temp.deptno || '' || v_temp.dname);

--SQL语句的运用

1：

declare

v_ename emp.ename%type;

v_sal emp.sal%type;

begin

select ename,sal into v_ename,v_sal from emp where empno = 7369;//必须返回记录，并且只能返回一条

dbms_output.put_line(v_ename || '' || v_sal);

end;

2：

declare

v_emp emp%rowtype;

begin

select * into v_emp from emp where empno = 7396;

dbms_output.put_line(v_emp.ename);

end;

3：

declare

v_deptno dept.deptno%type := 50;

v_dname dept.dname%type := 'aaa';

v_loc dept.loc%type := 'bj';

begin

insert into dept2 values (v_deptno,v_dname,v_loc); //insert、delete、update和sql是一样的，只是可以用变量

commit;

end;

4：

declare

v_deptno emp2.deptno%type := 10;

v_count number;

begin

update emp2 set sal = sal/2 where deptno = v_deptno;

dbms_output.put_line(sql%rowcount || '条记录被影响');

commit;

end;

sql%rowcount 表示：刚执行的最后一句sql影响到了多少条记录

--执行DDL语句

begin

execute immediate 'create table T (nnn varchar2(20) default ''aaa'')';

end;

--if语句

--取出7369的薪水，如果<1200，则输出'low',如果<2000则输出'middle'，否

则'high'

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;

--循环

(1):相当于 do .. while 循环

declare

i binary_integer := 1;

begin

loop

dbms_output.put_line(i);

i := i + 1;

exit when (i >= 11); //循环结束的条件

end loop;

end;

(2):while循环

declare

j binary_integer := 1;

begin

while j < 11 loop

dbms_output.put_line(j);

j := j + 1;

end loop;

end;

(3):for循环

begin

for k in 1..10 loop

dbms_output.put_line(k);

end loop;

for k in reverse 1..10 loop //表示 k 的值从 10 到 1 dbms_output.put_line(k);

end loop;

end;

--错误处理 (too_many_rows、no_data_found 等等)

declare

v_temp number(4);

begin

select empno into v_temp from emp where empno = 10; exception

when too_many_rows then

dbms_output.put_line('太多记录了');

when no_data_found then

dbms_output.put_line('没数据');

when others then

dbms_output.put_line('error');

end;

--将错误信息存储到一张日志表中

(1):

create table errorlog

(

id number primary key,

errcode number,

errmsg varchar2(1024),

errdate date

);

(2):

create sequence seq_errorlog_id start with 1 increment by 1;

(3):

declare

v_deptno dept.deptno%type := 10;

v_errcode number;

v_errmsg varchar2(1024);

begin

delete from dept where deptno = v_deptno;

commit;

exception

when others then

rollback;

v_errcode := SQLCODE; //是关键字，错误代码

v_errmsg := SQLERRM; //是关键字，错误信息

insert into errorlog

values(seq_errorlog_id.nextval,v_errcode,v_errmsg,sysdate); commit;

end;

--游标

1：用游标取一条记录

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;

2：结合循环取出多条记录

(1):do..while循环

declare

cursor c is

select * from emp;

v_emp c%rowtype;

begin

open c;

loop

fetch c into v_emp;

exit when (c%notfound); //当最近一次 fetch 没有返回记录

dbms_output.put_line(v_emp.ename);

end loop;

close c;

end;

(2):while循环

declare

cursor c is

select * from emp;

v_emp emp%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;

(3):for循环

declare

cursor c is

select * from emp;

for v_emp in c loop //for循环会自动打开和关闭游标，还会自动fetch..into

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;

begin

for v_temp in c (30,'clerk') loop

dbms_output.put_line(v_temp.ename);

end loop;

end;

--可更新的游标

declare

cursor c

is

select * from emp2 for update;

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;

--存储过程

存储过程：带有名字的PL/SQL的程序块，没有返回值

create or replace procedure p

is //用它代替了 declare

cursor c is

select * from emp2 for update;

begin

for v_emp in c loop

if(v_emp.deptno = 10) then

update emp2 set sal = sal + 10 where current of c; elsif(v_emp.deptno = 20) then

update emp2 set sal = sal + 20 where current of c;

else

update emp2 set sal = sal + 50 where current of c; end if;

end loop;

commit;

end;

上面的程序只是定义了一个存储过程，并没有真正执行。

执行上述存储过程：

begin

p;

end;

或：exec p;

--带有参数的存储过程

//in 和没有都表示输入参数；out 表示输出参数；in out 表示既是输入又是输出参数

create or replace procedure p

(v_a in number,v_b number,v_ret out number,v_temp in out number) is

begin

if(v_a > v_b) then

v_ret := v_a;

else

v_ret := v_b;

end if;

v_temp := v_temp + 1;

end;

//调用存储过程

declare

v_a number := 3;

v_b number := 4;

v_ret number;

v_temp number := 5;

begin

p(v_a,v_b,v_ret,v_temp);

dbms_output.put_line(v_ret);

dbms_output.put_line(v_temp);

end;

查看编译错误信息：show error

--函数

create or replace function sal_tax

(v_sal number)

return number

is

begin

if(v_sal < 2000) then

return 0.10;

elsif(v_sal < 2750) then

return 0.15;

else

return 0.20;

end if;

end;

select sal_tax(sal) from emp;

--触发器

不能直接执行，必须依附在某张表上

create table emp2_log

(

uname varchar2(20),

action varchar2(10),

atime date

);

create or replace trigger trig

after insert or delete or update on emp2 for each row

//for each row 的作用：每条记录发生 insert,delete,update的时候都会执行触发器，

如果没有for each row，那么只会触发一次。

begin

if inserting then

insert into emp2_log values (USER,'insert',sysdate);

elsif updating then

insert into emp2_log values (USER,'update',sysdate);

elsif deletion then

insert into emp2_log values (USER,'delete',sysdate);

end if;

end;

update emp2 set sal = sal*2 where daptno = 30;

select * from emp2_log;

drop trigger trig;

--触发器的一个应用例子

(不能只更新dept表中的deptno，因为emp中有引用deptno)

create or replace trigger trig

after update on dept for each row

begin

update emp set deptno =: NEW.deptno where deptno =: OLD.deptno; end;