plsql教学

1、一个简单的程序 set serveroutput on;

&符号的作用：提示用户输入 begin

dbms_output.put_line('HelloWorld!'); end;

/ --执行语句

2、一个简单的程序块 declare

v_name varchar2(20); begin

v_name:='i am ynp';

dbms_output.put_line(v_name); end;

3、一个完整的简单程序 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.每一行只能声明一个变量

-------------------------------------

4、变量声明尽量以v_ 开头

5、常用变量类型

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

char ：定长字符串类型

- 1 -

varchar2 ：变长字符串类型 最大4K date： 日期

long：长字符串，最长2G

boolean：布尔类型，可以取值为 false，True，null。null参与运算值不确定，所以最好声明变量时就给它赋值。

6、变量声明 declare

v_temp number(1);

v_count binary_integer :=0; v_sal number(7,2):=4000.00; v_date date := sysdate;

v_pi constant number(3,2):=3.14; --constant相当java里的final(常量)

v_valid booolean := false; --不能打印出布尔值.

v_name varchar2(20) not null :='ynp'; begin

dbms_output.put_line('v_temp is'||v_temp); --为连接符 end;

7、变量声明，使用%type属性

声明变量的数据对应表的某个字段的数据，当表结构改变时（如把number(7,2)，改为number(8,3)时，必须修改相应程序），用%type属性可以解决该问题。 例： declare

v_empno number(4);

v_empno2 emp.empno%type; --empno2数据类型于emp表中的empno字段相同；

v_empno3 v_empno2%type;--v_empno3数据类型于v_empno2 数据类型相同 begin

dbms_output.put_line('Test'); end;

-----------------------自定义变量---------------------

1、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) := 356;

- 2 -

v_empnos(-1):=12; v_empnos(2):=111;

dbms_output.put_line(v_empnos(-1)); end;

说明：table变量类型命名规则：type（自定义变量）-table（table类型变量）-emp（表emp）-empno（empno字段）； 下标值可以为负值；

2、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.loc:='bj' ; v_temp.dname:='aaa';

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

但上述dept表变动时，此程序也得手动做相应改动，可以用下述方法自动改动：

使用%rowtype声明record变量 declare

v_temp dept%rowtype; begin

v_temp.deptno:=50; v_temp.loc:='bj' ; v_temp.dname:='aaa';

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

errcode := SQLCODE; --出错代码 errmsg := SQLERRM --出错信息

-----------------------------pl/sql中的sql语句---------------------- 1、select语句

有且只有一条返回值，且必须加into 例子： declare

- 3 -

v_ename emp.ename%type; v_sal emp.sal%type; begin

select ename,sal into v_ename,v_sal from emp where empno=7369; --有且只有一条返回值， --且必须加into

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

declare

v_emp emp%rowtype; begin

select * into v_emp from emp where empno=7369; --有且只有一条返回值， --且必须加into

dbms_output.put_line(v_emp.ename); end;

2、update 、delete、insert语句和sql中相同，可能和变量混用； declare

v_deptno emp2.deptnpo%type:=10; v_count number; begin

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

--select deptno into v_deptno from emp2 where empno = 7369; --select count(*) into v_count from emp2;

dbms_output.put_line(sql%rowcount||'条记录被修改'); --sql%rowcount 返回记录数 commit; end;

3、执行ddl或DCL语句--（记住有execute immediate） begin

execute immediate 'create table t(nn varchar(2) default ''a'')'; end;

4、if语句 declare

v_sal emp.sal%type; begin

select sal into v_sal from emp where empno =7839; if v_sal <= 800 then

dbms_output.put_line('low') ; elsif v_sal >= 2800 then --不是elseif dbms_output.put_line('high');

- 4 -

else

dbms_output.put_line('middle'); end if; --注意有这条语句 end;

5、while语句 declare

k binary_integer:=1; begin

while ( k <11 ) loop

dbms_output.put_line(k) ; k:=k+1; end loop; end;

6、do ..while语句 declare

k binary_integer:=1; begin

loop

dbms_output.put_line(k) ; k:=k+1;

exit when(k>=11); end loop; end;

7、for循环 declare

k binary_integer:=1; begin

for k in 1..10 loop

dbms_output.put_line(k) ; end loop; end;

declare

k binary_integer:=1; begin

for k in reverse 1..10 loop --加上reverse为逆序打印 dbms_output.put_line(k) ; end loop; end;

- 5 -

-------------------游标（pl/sql重点）-----------------

1、plsql中select语句只能返回一条语句，要想返回多条语句，用游标。 declare

cursor c is

select * from emp; v_emp c%rowtype; begin

open c;

fetch c into v_emp; --fetch完后自动定位到下条记录 dbms_output.put_line(v_emp.ename) ; --不能放在exit前，否则最后一条记录打印两次 close c; end;

2、简单循环 declare

cursor c is

select * from emp; v_emp c%rowtype; begin

open c; loop

fetch c into v_emp;--fetch完后自动定位到下条记录 exit when(c%notfound);

dbms_output.put_line(v_emp.ename) ; --不能放在exit前，否则最后一条记录打印两次 end loop; close c; end;

3、while循环 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;

- 6 -

close c; end;

4、for循环 （循环时最简单） declare

cursor c is

select * from emp; begin

for v_emp in c loop --不用定义v_emp，不用打开关闭游标了 dbms_output.put_line(v_emp.ename) ; end loop; end;

5、带参数的游标 declare

cursor c(v_deptno emp.deptno%type,v_job emp.job%type) is

select * from emp where deptno = v_deptno and job= v_job; begin

for v_emp in c(30,'CLERK') loop

dbms_output.put_line(v_emp.ename) ; end loop; end;

6、可更新的游标

游标一般是作为记录集读取数据用的，但有时候用游标修改记录，这就是可更新游标； declare

cursor c is

select * from emp2 for update; begin

for v_emp in c loop

if(v_emp.sal <2000) then

update emp2 set sal =sal+1 where current of c ; --修改定位到的当前记录，注意形式

elsif(v_emp.sal>=2000) then

delete from emp2 where current of c; end if; end loop;

commit; --提交 end;

-------------------------存储过程--------------------

1、把过程的declare变成 create or Replace produce p is 就行。

- 7 -

--declare

create or replace procedure p is

cursor c is

select * from emp2 for update; begin

for v_emp in c loop

if(v_emp.sal <2000) then

update emp2 set sal =sal+1 where current of c ; elsif(v_emp.sal>=2000) then

delete from emp2 where current of c; end if; end loop; commit; end;

创建了存储过程不代表运行了存储过程； 运行此存储过程 ： 方式一 exec p； 方式二 begin p; end;

2、带参数的存储过程

in 相当于程序里的参数，供传入用，在存储过程不能改变其值； out 相当于程序里的返回值，在存储过程中可以为其赋值传出； in out 既可以当参数又可以当返回值用； 不带上述说明符默认为in类型；

下例中v_a v_b 为in类型 v_c 为out类型 v_d 为in out 类型

create or replace procedure p

(v_a in number,v_b number,v_c out number,v_d in out number) is begin

if(v_a > v_b) then v_c := v_a; else

v_c := v_b;

- 8 -

end if;

v_d := v_d+1; end;

---> 调试时：

可以在命令窗口调试，出错时 用show errors 显示出错信息； 可以在plDv中调试；

---> 运行时：

可以在命令窗口运行： declare

v_a number:=3; v_b number:=4; v_c number; v_d number:=5; begin

p(v_a,v_b,v_c,v_d);

dbms_output.put_line(v_c); dbms_output.put_line(v_d); end;

可以在plDv中调试；

------------------函数-------------------

1、它有返回值

create or replace function tax_tag(sal number) return number --计算税率 is begin

if(sal > 1000)then return 0.1; elsif(sal>=2000)then return 0.15; else

return 0.2; end if; end;

select ename, tax_tag(sal) from emp ；-- 直接用函数tax_tag

------------------------触发器(trigger)------------------------------ 1.create table emp2_log (

- 9 -

uname varchar2(20), action varchar2(20), atime date );

create or replace trigger trig

after(before) insert or delete or update on emp2 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 deleting then

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

select * from emp2_log; --查询更改情况.

2. create or replace trigger tig

after update on dept2 for each row begin

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

-------------------------recursion 通过递归写树-------------------- -----》创建表并插入记录

create table article (

id number primary key,

cont varchar2(4000),--文章内容 pid number, --父类id

isleaf number(1),--0代表非叶子节点，1代表叶子节点 alevel number(2) --等级 )

insert into article values(1,'蚂蚁大战大象',0,0,0); insert into article values(2,'大象被打趴下',1,0,1); insert into article values(3,'蚂蚁也不好过',2,1,2); insert into article values(4,'瞎说',2,0,2); insert into article values(5,'没有瞎说',4,1,3); insert into article values(6,'怎么可能',1,0,1); insert into article values(7,'怎么没有可能',6,1,2); insert into article values(8,'可能行很大的',6,1,2); insert into article values(9,'大象进医院了',2,0,2);

- 10 -

insert into article values(10,'蚂蚁是护士',9,1,3);

----------》存储过程

create or replace procedure p (v_pid article.pid%type,v_level binary_integer) is

cursor c is select * from article where pid = v_pid; v_perStr varchar2(2000):=' '; begin

for i in 1..v_level loop

v_perStr := v_perStr||'***'; end loop;

for v_article in c loop

dbms_output.put_line(v_perStr||v_article.cont); if (v_article.isleaf = 0) then

p(v_article.id,v_level + 1); end if; end loop; end;

- 11 -

insert into article values(10,'蚂蚁是护士',9,1,3);

----------》存储过程

create or replace procedure p (v_pid article.pid%type,v_level binary_integer) is

cursor c is select * from article where pid = v_pid; v_perStr varchar2(2000):=' '; begin

for i in 1..v_level loop

v_perStr := v_perStr||'***'; end loop;

for v_article in c loop

dbms_output.put_line(v_perStr||v_article.cont); if (v_article.isleaf = 0) then

p(v_article.id,v_level + 1); end if; end loop; end;

- 11 -

本文来源：https://www.bwwdw.com/article/fbhp.html