Oracle PLSQL语句实例

/**
 * plsql:某个项目对效率要求比较高的时候用，一般不用，大多数项目写的是夸数据库平台的，用不上。
 * pssql大多数能做的东西，java都能替代它。在某些特殊的地方要求用plsql的时候你才会用。
 * 
 * 变量声明的规则：
 * 1、变量名不能使用保留字，如from、select等
 * 2、第一个字符必须是字母
 * 3、变量名最多包含30个字符
 * 4、不要与数据库的表或者列同名
 * 5、每一行只能声明一个变量
 * 
 * 常用变量类型
 * 1、 binary_integer:整数，主要用于计数而不是用来表示字段类型
 * （Oracle本身非常注重效率，他的很多设计都是为了效率）
 * 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;--输出HelloWorld

declare
	v_name varchar2(20);
begin
	v_name:='mynaem';
	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_tem 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; --sql里面也有常量
	v_valid boolean:=false; --不能打印布尔类型的值
	v_name varchar2(20) not null:='MyName'; --定义变量还能有限制
begin
	dbms_output.put_line('v_temp value:' || v_temp);
end;

declare
	v_empno number(4);
	v_empno2 emp.empno%type;--该变量的类型跟随另一变量的类型
	v_empno3 v_empno2%type;
begin
	dbms_output.put_line('Test');
end 

--Table变量类型
declare
	/**
	 * type表示我定义了一种类型,名称叫tyep_table_emp_empno
	 * is table说明这是一个数组，这个数组里面装着emp.empno%type类型的数据
	 * index by说明他的下表是由binary_ingeger来索引
	 */
	type tyep_table_emp_empno is table of emp.empno%type index by binary_ingeger;
	v_empnos type_table_emp_empno;
begin
	v_empnos(0):=7369;
	v_empnos(2):=7839;
	v_empnos(-1):=9999;--在Oracle里面，这个下标值可以取负值
	dbms_output.put_line(v_empnos(-1));
end 

--Record变量类型
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:='aaaa';
	v_temp.loc:='bj';
	dbms_output.put_line(v_temp.deptno||' '||v_temp.dname);
end

--使用%rowtype声明record变量
declare
	v_temp dept%rowtype;--更具dept表的字段来
begin
	v_temp.deptno:=50;
	v_temp.dname:='aaaa';
	v_temp.loc:='bj';
	dbms_output.put_line(v_temp.deptno||' '||v_temp.dname);
end 

declare
	v_deptno emp2.deptno%type:=10;
	v_count number;
begin
	update emp2 set sal=sal/2 where deptno=v_deptno;--update、insert、delete可以直接用
	select deptno into v_deptno from emp2 where empno=7369;--select语句必须和into一起使用且保证有且只有一条记录
	select count(*) into v_count from emp2;
	dbms_output.put_line(sql%rowcount||'条记录被影响');--rowcount代表刚刚执行完的这条sql语句到底影响了多少条记录
													--sql代表的是刚刚执行完的那条sql语句
	commit;--用完之后要commit
end 

/**
 * plsql中的判断
 */
begin--create table语句在plsql里面不能单独执行
	execute immediate 'create table T(nnn varchar2(20) default ''aaa'')';
end 

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 --小心elsif中间没有e
		dbms_output.put_line('middle');
	else
		dbms_output.put_line('high');
	end if;
end 

/**
 * plsql 中的循环
 */
declare
	i binary_integer:=1;
begin
	loop --plsql里面的循环以loop开头，以end loop结尾
		dbms_output.put_line(i);
		i:=i+1;
		exit when(i>=11);
	end loop;
end 

declare
	j binary_integer:=1;
begin
	while j<11 loop
		dbms_output.put_line(j);
		j:=j+1;
	end loop;
end 

begin
	for k in 1..10 loop --相当于java里面增强的for循环
		dbms_output.put_line(k);
	end loop;
	
	for k in reverse 1..10 loop
		dbms_output.put_line(k);
	end loop;
end 

/**
 * plsql里面的错误处理
 * too_many_rows, no_data_found
 * 这个东西了解下就够了
 */
declare
	v_temp number(4);
begin
	select empno into v_temp from emp where deptno=10;
exception
	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;--SQLCODE代表出错的代码，Oracle里面出错代码都是负数
			v_errmsg:=SQLERRM;--SQLERRM代表出错信息
		insert into errorlog values(seq_errorlog_id.nextval,v_errcode,v_errmsg,sysdate);
	commit;
end 

/**
 * 游标
 */
declare--loop循环
	cursor c is select * from emp;
	v_emp c%rowtype; --将v_emp定义为游标c的类型
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--while循环
	cursor c is select * from emp;
	v_emp c%rowtype; --将v_emp定义为游标c的类型
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 

declare--for循环:cursor 不需要打开或者关闭，所以for循环是我们平时用得最多的循环
	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_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;
		elseif(v_temp.sal=5000)then
			delete from emp2 where current of c;
		end if;
	end loop;
	commit;
end

/**
 * 创建存储过程(存储过程创建了不代表她已经执行了)
 * Oracle的存储过程，即便我们有语法错误，依然会被创建。执行的时候会报错。
 */
create or replace procedure p
is
	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;
		elseif(v_temp.sal=5000)then
			delete from emp2 where current of c;
		end if;
	end loop;
	commit;
end

exec p;--执行存储过程

/**
 * 带参数的存储过程
 *  in是传入参数
 * 	out是传出参数（存储过程是没有返回值的）
 *  如果没有写，默认是in
 *  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 

/**
 * 创建函数
 */
create or replace function sal_tax
	(v_sal number)
	return number
is
begin
	if(v_sal < 2000) then
		return 0.10;
	elseif(v_sal<2750) then
		return 0.15;
	else
		return 0.20;
	end if
end 

--调用函数
select lower(ename),sal_tax(sal)from emp;

/**
 * 创建触发器
 */
create tabel emp2_log
(
	uname varchar2(20),
	action varchar2(10),
	atime date
);

create or replace trigger trig
	/**
	 * 触发器不能直接执行，必须依附在某张表上
	 * 可以有after也可以有before insert
	 * 如果没有for each row则整个操作会被触发一次
	 * 如果用for each row，如果一个操作更新了六行则被触发六次
	 */
	after insert or delete or update on emp2 for each row
begin
	if inserting then
		insert into emp2_log values(USER,'insert',sysdate);
	elseif updateing then
		insert into emp2_log values(USER,'update',sysdate);
	elseif deleting then
		insert into emp2_log values(USER,'delete',sysdate);
	end if;
end 

/**
 * 触发器的一个副作用（不重要，了解即可。万不得已的情况下用）
 * 从下面例子可以看出当我们update一个语句的时候
 * 是先触发触发器
 */
update dept set deptno=99 where deptno=10;--这样做会报错，违反了完整约束条件
--建立下面触发器以后就可以了（因为对参考dept表的emp表里面的值也做了修改）
create or replace trigger trig
	after update on dept
	for each row
begin
	update emp set deptno=:NEW.deptno where deptno=:OLD.deptno;
end 

/**
 * 树状结构的存储与展示
 */
create table article
(
	id number primary key,
	cont varchar2(4000),
	pid number,
	isleaf number(1), --0代表非叶子节点，1代表叶子节点
	alevel number(2)
);
--查询上表中某条记录的孩子节点
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.cont);
		if(v_article.isleaf=0) then
			p(v_article.id,v_level+1);
		end if;	
	end loop;
end