PL/SQL学习笔记(一)

1.在声明变量(declare)时,vatchar2需要指定长度,number无需指定,因为number默认长度为38。

declare
	v_i number := 4;
	v_a varchar2(200) := '哈哈哈哈哈';
	v_aa constant varchar2(200) :='常量';
	v_d date := sysdate;--系统时间
	
	v_ename emp.ename%type;
	--动态追踪表中某个字段类型,就意味着v_ename类型永远与emp表中的ename类型保持一致。

	v_emp_row emp%rowtype;--动态追踪表中整行字段类型
	--必须指定长度,并且字符串需要用单引号,不能用双引号,用双引号报错。常量也得指定长度。
varchar2表示不定长度,varchar为固定长度。一般选用varchar2
变量名前加上constant,就表示常量。常量不能再次赋值。

2.plsql中连接符为“||”,类似于java中的“+”
在查询数据库时,最好使用别名。因为这样可以提高效率。

3.关系型数据库三范式
第一范式:确保每列都保持原子性。每个表都必须遵守
第二范式:确保表中的每列都和主键相关
第三范式:确保每列都和主键列直接相关,而不是间接相关。即消除传递依赖。

4.赋值语句

select 查询结果 into 赋值变量 from  ;
需要注意的是 必须保证只能查询出一条结果
select e.ename into v_ename from emp e where e.deptno=7369;
select e.ename,e.deptno into v_ename,v_deptno from emp e where e.deptno=7369;
5.table类似于数组(只是类似),这部分只要了解,并不常用。
声明类型,格式为

type 类型名字 is table of table元素的类型 index by binary_integer;
--index by binary_integer不是必须写,可以省略。
赋值时包括零散赋值和集中赋值
①零散赋值

--当然,声明是在declare中
type tab_type is table of varchar2(100) index by binary_integer;
--这只是声明了类型,并不是定义table
v_tab1 tab_type;--这才是定义table型数据
begin
	v_tab1(0) := 'aaa';
	v_tab1(1) := 'bbb';
	v_tab1(2) := 'ccc';
②集中赋值,索引从1开始

v_tab2 := tab_type('aaa','bbb','ccc');
dbms_output.put_line(v_tab2(1));
6.唯一键(unique)可以为空,并不是主键(primary).唯一键一般和not null连用。

7.exist是用来替代in的,因为exist查询效率高。exist可以使用索引,而in不能。

where column in(select * from ... where ...);
where exist(select * from ... where ...);
8.在定义时,变量需要写长度(变量必须在declare语句块中声明),而参数不需要写长度。如:

--变量
declare
	v_a varchar2(100) := 'aaa';

--参数
create  or replace  procedure p_emp_copy(
        l_result1 out varchar2 --没有指定长度,也不用加分号
)
9.在plsql循环中,和html的标签一样,需要有结束标签。loop和end loop,if和end if(if需和then连用)。for的格式为

for 变量名(不需要声明,自动匹配in后集合的单个元素类型) in 集合 loop
end loop
一个简单地例子就是,求1到100的和

for v_r  in 1..100  loop
        v_sum := v_sum+ v_r; --v_r无需提前声明
end loop;
如下求100之内的奇数和,这就需要提前声明变量

declare
	v_begin number := 1;--这个没必要声明,只是为了整齐
	v_sum number := 0; --sum必须赋初值,否则报错
begin
	for v_begin in 1..100 loop--1..100表示1到100
		if v_begin mod 2<>0 then --注意不要丢then
			v_sum := v_sum + v_begin;
		end if;--if的结束标签
	end loop;--loop的结束标签
end;
while的例子为(也是求1到100的和)

declare
	v_begin number := 1;--这个没必要声明,只是为了整齐
	v_sum number := 0; --sum必须赋初值,否则报错
begin
	while v_sum<100 loop
		v_sum := v_sum + v_begin;
		v_begin := v_begin + 1;
	end loop;
end;

10.游标就是指在某个结果集上的指针,通过这个指针的移动,我们得以遍历整个结果集。即游标就是一个结果集list
游标的属性有4种,%found  %notfound  %rowcount(用来记录遍历到的总行数)  %isopen
游标需与commit或rollback连用
简单画下游标的分类

静态游标分为无参和 有参,推荐 for 遍历

动态 游标则是 ①先定义一个游标类型  ②搞游标类型对应的一个变量 。它还分为弱类型和强类型

弱类型   :  type my_cursor is ref cursor;   就是定义行变量时  必须指定具体的类型

强类型   : type my_cursor is ref cursor return emp%rowtype;  定义行变量可以动态匹配游标的rowtype;

使用步骤为:①声明游标 ②打开游标 ③处理游标中的数据 ④关闭游标
遍历游标最常用的是for循环,因为它不需要打开游标,也不需要关闭,甚至不用声明循环变量,非常简单。如:

begin
	delete from emp e where e.deptno = 20;--无需声明游标,直接begin。这为隐式游标
	if sql%found then
		dnms_output.put_line('游标自动关闭');
	end if;
	commit; --commit不能丢
end;
静态游标,声明格式为:

cursor 名字(参数名 参数类型) is select 语句 ;--注意参数无需加长度
使用for循环是最简单的遍历,loop是标准的遍历
declare
	cursor c_emp(l_job varchar2) is select * from emp e where e.job = l_job;
	--游标c_emp中存储的是sql语句的结果集,l_job只是一个变量名,可以换位其它值
begin
	for emp_row in c_emp('CLERK') loop
	--使用for循环变量emp_row无需声明,但在loop循环中就需要声明了
		dbms_output.put_line(emp_row.ename);
	end loop;
end;
标准的游标操作,loop是标准的遍历

declare
	cursor c_emp(l_job varchar2) is select * from emp e where e.job = l_job;
	v_emp_row c_emp%rowtype;--v_emp_row在loop循环中需要声明,在for循环中无需声明
begin
	--打开游标
	open c_emp('CLERK');
	--处理数据
	loop
		--读数据
		fetch c_emp into v_emp_row;
	exit when c_emp%notfound;
		dbms_output.put_line(v_emp_row.ename);--注意是v_emp_row.name,而不是c_emp.ename
	end loop;
	--关闭游标
	close c_emp;
end;
从上边可以看出for循环和loop循环遍历cursor的区别。v_emp_row在loop循环中需要声明,在for循环中无需声明

11.使用游标也可以更新结果集。select语句后边要加for update来锁定记录用来更新。用where current of 来指明操作是添加在当前游标所指向的记录上。如

declare
	cursor c_emp is select * from emp for update;
begin
	for v_t in c_emp loop
	if v_t.deptno = 10 then
		delete from emp e where current of v_t;
	end if;
	end loop;
end;
12.存数过程可以看做是带名字的plsql的语句块,格式为

create [or replace] procedure 存储过程名 (参数名 in 类型…………参数名 out 类型)
is  --用来定义变量
begin
	exception
end;
需要注意的是可以不带参数,in类型的参数不能被赋值,out类型的可以。一个参数既可以是in,也可以是out。在工作中,最好不用replace。
如写一个存储过程,可以完成对emp的增删改(没有查);

create or replace procude p_emp_cud(
	l_remark in number,--0:add 1:update 2:delete
	l_hiredate in varchar2,
	l_deptno in number,
	l_result out varchar2
	--l_result可以不写,但一般用来判断结果是否正确
)

is
begin
	l_result := '00000';
	if l_remark=0 then 
		insert into emp values(
			to_date(l_hiredate,'yyyy-mm-dd'),
			l_deptno
		);
	else if l_remark=1 then
		update emp e
		set
		e.hiredate = to_date(l_hiredate,'yyyy-mm-dd')
		where emp.deptno = l_deptno;
	else
		delete from emp e where emp.deptno = l_deptno;
	end if;
	commit;

	exception
		when others then
			rollback;
			l_result := '1111111';
end;
注意l_result的应用。 只要有事务commit,就写一个exception

13.在嵌套循环中,外层循环控制列,内层循环控制行。

14.复制表的内容,可以先建一个与原表结构相同的表,再用类似于

insert into emp_copy select * from emp;
这样的语句。

15.oracle查询有两种形式,索引查询和全表扫描。索引类似于书的目录,主要用于查询频率高、增删改频率低的地方。并不是索引查询就比较快,还得分情况,当有大量数据时,索引查询就比较快。当数据量较小时,全表扫描就比较快。

16.plsql中处理结果集只能用游标。

下面写一个常用的存储过程实例
17.写一个存储过程 ,传入表名和重复值的字段名 实现删除重复数据功能.
先说一下正常的删除重复数据的sql语句为

delete from emp e where rowid <>(select min(rowid) from emp c where c.empno= e.empno);

rowid是一个伪列,一般不常用,但在要锁定某一列时就需要用到这。因为它的值不会重复,在plsql常用在删除重复数据中。

为成固定格式,很重要。

ceeate procedure p_del(
	l_tab_name in varchar2,
	l_col_name in varchar2
)
is
	v_sql varchar2(2000);--声明sql
begin
	v_sql := 'delete from '||l_tab_name||' t1 where rowid <> (select min(rowid) from '||l_tab_name||' t2 where t2.'||l_col_name||'=t1.'||l_col_name);
	execute immediate v_sql;
	commit;
	--这儿其实可以定义一个out类型的result的,只是省略了
	exception
		when others then
			rollback;
end;

  • 1
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值