PL_SQL

PL_SQL 学习笔记，包括游标、存储过程、函数、触发器等。

 

/*
--变量声明的规则

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值

*/

set serveroutput on;
begin
    dbms_output.put_line('HelloWorld');
end;
/

declare
   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;
/


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;
   v_valid boolean := false;
   v_name varchar2(20)  not null :='MyName';
begin
  dbms_output.put_line('v_count value:' || v_count);
end;
/
--注意：dbms_output.put_line不能直接打印boolean类型。

--变量声明，使用%type
declare
   v_empno number(4);
   v_empno2  emp.empno%type;
   v_empno3  v_empno2%type;
begin
   dbms_output.put_line('Test');
end;
/

--简单变量赋值
declare
     v_name varchar2(20);
     v_sal number(7,2);
     v_sal2 number(7,2);
     v_valid boolean :=false;
     v_date date;
begin 
     v_name := 'MyName';
     v_sal :=23.77;
     v_sal2 :=23.77;
     v_valid := (v_sal = v_sal2);
     v_date := to_date('2010-02-04 11:11:11','YYYY-MM-DD HH24:MI:SS');
end;
/

--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) := 6322;
     v_empnos(2) := 2314;
     v_empnos(-1) := 520;
     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 := '开发部';
      v_temp.loc := '1612';     
      dbms_output.put_line(v_temp.deptno || '  ' || v_temp.dname ||'  ' || v_temp.loc);
end;
/

--用%rowtype声明record变量类型
declare
      v_temp  dept%rowtype;
begin
      v_temp.deptno := 50;
      v_temp.dname := '开发部';
      v_temp.loc := '1612';     
      dbms_output.put_line(v_temp.deptno || '  ' || v_temp.dname ||'  ' || v_temp.loc);
end;
/


--SQL语句的运用。注意：要有关键字into; SQL返回的记录数有且仅有一条，
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;
/

--create table emp2 as select * from emp;

declare
      v_deptno emp2.deptno%type := 50;
      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 || '条记录被影响');
      commit;
end;
/


--DDL语句，execute immediate两个''表示' 
begin
     execute immediate 'create table T(nnn varchar2(20) default ''aaa'')';
end;


--if语句
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('lower');
	elsif(v_sal < 2000) then
		dbms_output.put_line('middle');
	else
		dbms_output.put_line('higher');
	end if;
end;
/

--循环类似do while
declare
	i binary_integer := 1;
begin
	loop
		dbms_output.put_line(i);
		i := i + 1;
		exit when (i >= 11);
	end loop;
end;
/

--循环类似while
declare
	j binary_integer := 1;
begin
	while j < 11 loop
		dbms_output.put_line(j);
		j := j + 1;
	end loop;
end;
/


--循环类似for
begin
	for k in 1..10 loop
		dbms_output.put_line(k);
	end loop;

	for k in reverse 1..10 loop
		dbms_output.put_line(k);
	end loop;
end;
/


--异常处理
declare
	v_temp number(4);
begin
	select empno into v_temp from emp where deptno = 110;
exception
	when no_data_found then
		dbms_output.put_line('无记录');
	when too_many_rows then
		dbms_output.put_line('太多记录了');
	when others then
		dbms_output.put_line('error');
end;
/


create table errorlog
(
id number primary key,
errcode number,
errmsg varchar2(1024),
errdate date
);

create sequence seq_errorlog_id start with 1 increment by 1;

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;
/



--游标
declare	
	cursor c is
		select * from emp;
	v_emp c%rowtype;
begin
	open c;
		fetch c into v_temp;
		dbms_output.put_line(v_emp.ename);
	close c;
end;	
/


declare	
	cursor c is
		select * from emp;
	v_emp c%rowtype;
begin
	open c;
	loop
		fetch c into v_emp;
		exit when (c%notfound);
		dbms_output.put_line(v_emp.ename);
	end loop;
	close c;
end;	
/



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_temp;
	end loop;
	close c;
end;	
/

--游标for循环，自动打开，关闭游标
declare	
	cursor c is
		select * from emp;
begin
	for v_emp in c loop
		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_emp in c(30,'CLERK') loop
		dbms_output.put_line(v_emp.ename);
	end loop;
end;	
/



--可更新游标
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 * 2 where current of c;
		elsif(v_emp.sal = 5000) then
			delete from emp2 where current of c;
		end if;
	end loop;
	commit;
end;	
/



--存储过程
create or replace procedure p
is
       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;	
/

--执行存储过程,方式一
exec p;

--执行存储过程,方式二
begin
	p;
end;
/


--带参数存储过程
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;
/


--函数
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 
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;
/

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

select * from emp2_log;

drop trigger trig;



create or replace trigger trig
	after  update on dept for each row 
begin
	update emp set deptno = :NEW.deptno where deptno = :OLD.deptno;
end;
/

update dept set deptno = 99 where deptno = 30;

select * from emp;

rollback;



--树状结构的存储与展示
create table article 
(
id number primary key,
content varchar2(4000),
pid number,
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);
insert into article values (10,'护士是蚂蚁',9,1,3);
commit;


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_preStr varchar2(1024) := '';
begin
	for i in 1..v_level loop
		v_preStr := v_preStr || '******';
	end loop;
	for v_article in c loop
		dbms_output.put_line(v_preStr || v_article.content);
		if(v_article.isleaf = 0) then
			p(v_article.id,v_level + 1);
		end if;
	end loop;
end;
/		

exec p(0,0);