个人的尚学堂数据库oracle笔记（3）

pl/sql
create index  ename_emp_sal on emp(sal);
drop index  ename_emp_sal ;
/*索引*/
 
/*范式：不存在冗余数据，就是信息重复*/
第一范式：a.要有主键， b.列不可分
第二范式：a.当一张表存在多个主键时，b.不存在部分函数依赖。c.存在于多对多的关系
第三范式：a.不存在传递依赖。

begin
	dbms_output.put_line('HelloWorld!');
	end;
/	
set serveroutput on ;/*系统环境默认变量关闭*/
begin
	dbms_output.put_line('HelloWorld!');
	end;
/
/*输出 HelloWorld!*/

declare  /*声明变量*/
	v_name varchar(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;
/

--声明变量类型
	变量名不能够使用保留字，如from、select等
	第一个字符必须是字母
	变量名最多包含30个字符
	不要与数据库的表或者列同名
	每一行只能声明一个变量

--常见数据类型
	binary_integer:整型
	number:数字类型
	char：定长字符串
	varchar2:边长字符串
	date:日期
	long:长字符串
	boolean:布尔类型，可以取值为true、false和null值
	
--变量声明

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_pi value :' || v_pi);
end ;
/
/* || 变量输出*/



--变量声明，使用%type属性
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 type_table_emp_empno is table of emp.empno%type index by binary_integer;
		v_empnos type_table_emp_empno;
begin
	v_empnos(0) :=7369;
	v_empnos(2) :=7839;
	v_empnos(-1) :=9999;
	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_name);
end;
/


--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;
/
/*只能返回一条记录*/
 
DDL语句
begin
	execute immediate  'create table  T( nnn varchar2(20) default ''aaa'')';
end;
/
/*两个单引号代表一个单引号*/

declare 
	v_sal emp.sal%type; /*声明v_sal为emp.sal列*/
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;
/

declare 
	i binary_integer :=1;
begin
	loop
		dbms_output.put_line(i);
		i := i+1;
		exit when(i >= 11);
		end loop;
end;
/
/*打印1.2.3...10相当于do while循环*/

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;
/
/*for循环*/

declare
	v_temp number(4);
begin
	


end;

create table errorlog
(
id number primary key,
error number,
errmsg varchar2(1024),
errdate date
);
	
create sequence seq_errlog_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_errlog_id.nextval,v_errcode,v_errmsg,
		sysdate);
			commit;
end;
/
select * from errorlog;


--重中之重游标
 declare
	cursor c is  /*指定游标c */
		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;
/


declare
	cursor c is  /*指定游标c */
		select * from emp;
	v_emp c%rowtype; /**/
begin
	open c;
	loop
		fetch c into v_emp;  /*游标自动移到下一格*/
		dbms_output.put_line(v_emp.ename);
		exit when (c%notfound);
	end loop;
	close c ;
end;
/
/*游标遍历*/

declare
	cursor c is  /*指定游标c */
		select * from emp;
		v_emp c%rowtype; /**/
begin
	for v_emp in c loop
		dbms_output.put_line(v_emp.ename);
	end loop;
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 ;
/


--function
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 lower(ename),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 emp
begin 
	if inserting then
		insert into emp2_log values(USER,'insert',sysdate);
	elsif updating then 
		insert into emp2_log values(USER,'update',sysdate);
	else deleting then
		insert into emp2_log values(USER,'delete',sysdate);
	end if ;
end;

--递归 recursion，树状结构

create table article
(
id number primary key,
cont varchar2(4000),
pid number,
isleaf number(1),
alevel number
) ;
--isleaf0代表非叶子结点，1代表叶子节点
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)；

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

exec p(0,0)