PLSQL学习笔记.docx

下面我为这个系列文章做一个索引一： 常量变量及数据类型初步 1：常量变量 2：数据类型（一）二：变量作用域及数据类型深入 1：变量作用域 2：数据类型（二）三：流程控制 1：条件控制 2：循环控制与顺序控制四：异常处理 1：预定义异常错误 2：非预定义异常错误 3：自定义异常并抛出五：约束 1：非空约束 2：唯一约束 3：主键约束 4：外键约束 5：检查约束六：索引 1：索引描述 2：索引分类 3：各类索引的应用场合七：视图 1：创建或者修改一个视图 2：删除一个视图 3：更新视图中的数据 4：视图中的约束和主键八：过程 1：创建或者修改一个过程 2：删除一个过程九：函数 1：创建或者修改一个函数 2：删除一个函数十：触发器 1：语句级触发器 2：行级触发器 3：instead of触发器(视图上的触发器) 4：删除触发器十一：程序包 1：程序包的说明 2：程序包体 3：调用程序包 4：删除程序包十二：游标 1：普通游标 2：有参数的游标 3：有返回值的游标 4：运行过程中获取游标的内容 5：隐式游标 6：for循环简化游标的操作 7：在游标中更新或删除数据一：常量和变量开始之前，还是照例做个经典的例子，如下：declare mydate varchar2(16) := hellow world;begin dbms_output.put_line(mydate);end;declare是声明部分我在这个部分声明了varchar2类型的变量，名字叫mydata，内容是hellow world，长度是16个长度。begin.end 部分是执行部分我在这里调用了oracle已有的过程dbms_output.put_line，这个过程起到了打印的目的执行完后就打印出了我刚才定义的变量。注意PL/SQL与T-SQL一样是不区分大小写的我是用pl/sql developer工具执行上述程序的（如果有必要以后介绍下oracle的安装，基本操作和相关工具）下面贴两个图，看一下：你可以通过使用constant关键字声明常量，如：mydate constant varchar2(16) := hellow world;二：数据类型（不全）1：number(p,s) 声明数字p表示精度，即：这个数字一共有几位，p取值138s表示刻度，即：这个数字的小数点位于第几位（从右向左数），s取值-84127两个参数都是可选的，刻度可以为负数，如：mydate number(6,-2) := 123456;此变量输出是为：123500。如你所见他被四舍五入了另需注意：mydate number(6,2) := 1234.123456;如上，赋值的数字明显超过了精度，但是可以成功执行，得到的变量为1234.12，精度为6。同样是四舍五入起的作用不要试图这样做：mydate number(6,2) := 123456.123456;因为系统再怎么四舍五入都满足不了精度要求2：char(n) 用于定义固定长度的字符串n表示长度，n不应超过2000，是可选参数如果你定义了n，但是存储的字符串长度比n小，那么系统会给你用空格补齐3：varchar2(n) 用于定义可变长度的字符串n是必须的，表示字符串的最大长度，取值范围132767你定义了一个varchar2的数据，长度比n小，系统不会给你补空格！ 4：boolean 定义逻辑变量取值范围是true，false，null5：date 定义日期数据只包含日期，公元前4712到公元9999年，一般不会超过限制占7个字节6：timestamp(s) 还是日期型数据存储年、月、日、时、分、秒、 上午、下午s表示秒的小数位数，在09之间取值7：LOB 存储文件BLOG：主要用于存储图像文件CLOB：主要用于存储文本文件BFILE：主要用于声音、视频文件所有文件不要超过4G一：变量作用域DECLAREcurrent_block VARCHAR2(10) := Outer;outer_block VARCHAR2(10) := Outer;BEGINdbms_output.put_line(current_block|current_block|); DECLARE current_block VARCHAR2(10) := Inner; BEGIN dbms_output.put_line(current_block|current_block|); dbms_output.put_line(outer_block|outer_block|); END;dbms_output.put_line(current_block|current_block|);END;以上为引用一本书的代码（Oracle Database 11g PL/SQL程序设计）输出为current_blockOutercurrent_blockInnerouter_blockOutercurrent_blockOuter这是两个嵌套的程序块我们在子程序块中重定义了父程序块的变量这个变量在子程序块执行完之后又恢复了父程序块中定义的值二：%type 取一个已知变量的数据类型declare mytitle labor.xland.title%type;beginselect title into mytitle from labor.xland where state = 0;dbms_output.put_line(mytitle);end;上面例子中取了表title列的数据类型（varchar2）然后检索了一行一列数据，把这个数据赋值给我们定义的变量mytitle当然你也可以定义多个变量如：select title,id into mytitle,myid from labor.xland where state = 0; 三：%rowtype 取一个已知行的数据类型，这应该是一个对象类型declare myrow labor.xland%rowtype;beginselect * into myrow from labor.xland where state = 0;dbms_output.put_line(myrow.title);dbms_output.put_line(to_char(myrow.state);end;上例中我们取了某个表的rowtype，并定义了变量myrow接着把这个表的一行数据赋值给了这个变量输出的时候只要以点来获取这一行的某个列即可四：record 这应该也是一个对象declare type myrecordtype is record ( mytitle labor.xland.title%type, mystate labor.xland.state%type ); myrecord myrecordtype;beginselect title, state into myrecord from labor.xland;dbms_output.put_line(myrecord.mytitle);dbms_output.put_line(to_char(myrecord.mystate);end;如上代码我们先定义了一种数据类型（type）这种类型包含两个属性，分别是mytitle和mystate然后我们定义了一个变量myrecord这个变量的类型就是我们刚才定义的数据类型接着我们把两个数据赋值给类这个变量的两个属性这里的赋值顺序是按属性定义的先后顺序给属性赋值的然后输出至此，数据类型仍未讲完还有table（表）,array（数组）,exception（异常）,cursor（游标）等类型等没讲异常和游标将在后面的文章提到三：流程控制一：if条件控制先看一段程序：declarev_content varchar2(66);beginselect content into v_content from xland where title=xland;if length(v_content)6 then v_content := substr(v_content,0,6)|.;else v_content := v_content|（全部数据）;end if;dbms_output.put_line(v_content);end;这段程序用到了ifthen else end if结构1.其中length()函数可以获取一个字符串的字符长度另外还有lengthb()函数，可以获取一个字符串的字节长度2.substr()函数，可以按字符长度获取一个字符串的子字符串，另外还有substrb()函数，可以按字节长度获取一个字符串的子字符串3.select 句是从一个表里获取一条数据，并把数据存储在我们定义的变量中注意一定要是一条数据，因为我们的变量不是table或者record类型至于这些类型会在后面的文章中提到另外还有ifthen elseifthen else end if结构这里就不举例子了注意elseif是连在一起写的，这很像vb的语法，与C#语法有区别二：case条件控制先看例子：declarev_content number;beginselect length(content) into v_content from xland where title=xland;case when v_content6 then dbms_output.put_line(长度为：|to_char(v_content); when v_content16; v_flag := v_flag+1; dbms_output.put_line(to_char(v_flag);end loop;end;其中exit when是跳出循环的条件，注意代码中的分号，挺重要的。没有分号会报错二：whileloop.end loop几个和循环结构相关的代码，我比较喜欢这个，如下：declarev_flag number := 1;beginwhile v_flag16 loop v_flag := v_flag+1; dbms_output.put_line(to_char(v_flag);end loop;end;注意这个示例程序和上个示例程序的输出结果是不同的具体为什么不同我就不多解释了三：forloopend loop还是类似的示例程序：declarev_flag number := 1;beginfor v_flag in 2.16 loop -v_flag := v_flag+1; dbms_output.put_line(to_char(v_flag);end loop;end;注意我用-注释掉了一句因为在这个循环体内，是不允许为哨兵变量赋值的哨兵变量不知道啥意思？hehe -是注释单行的写法/* .*/可以注释多行四：顺序控制goto看例子：declarev_flag number := 1;beginwhile v_flag16 loop v_flag := v_flag+1; dbms_output.put_line(to_char(v_flag); if mod(v_flag,6) = 0 then goto lable_1; end if;end loop;dbms_output.put_line(跳出来了);end;mod(v,n)是取余数的意思，这里写这个函数其实没意义还不如直接判断变量是否等六另外需要注意的是goto语句有很多限制以下都是不被允许的：1跳转到非执行语句前面2跳转到子块中3跳转到条件语句中4跳转到循环语句中5从条件语句的一部分跳转到另一部分6从异常处理部分跳转到执行部分一：预定义异常错误先看代码：declare mytitle labor.xland.title%type;beginselect title into mytitle from labor.xland where state = 2;dbms_output.put_line(mytitle);exceptionwhen no_data_found thendbms_output.put_line(没有找到数据);end;这段程序当检索到数据的时候就输出数据检索不到数据即输出 没有找到数据no_data_found 是一个预定义异常错误类型更多预定义异常错误，请看：/liulun/articles/1526177.html二：非预定义异常错误先看代码：declare v_sqlcode number;v_sqlerrm varchar2(2048);begininsert into labor.xland values(null,1111,1);exceptionwhen no_data_found then dbms_output.put_line(没有找到数据);when others then if sqlcode = -1400 then v_sqlcode := sqlcode; v_sqlerrm := sqlerrm; dbms_output.put_line(to_char(v_sqlcode); dbms_output.put_line(v_sqlerrm); end if;end;when 块不必跟end结束if块需跟end if结束sqlcode为错误码sqlerrm为oracle反馈的错误信息此程序输出：-1400ORA-01400: 无法将 NULL 插入 (LABOR.XLAND.TITLE)另外还有pragma exception_init(name,errcode)函数来处理非预定义异常如：declare v_exception exception;pragma exception_init(v_exception,-1400);begininsert into labor.xland values(null,1111,1);exceptionwhen no_data_found then dbms_output.put_line(没有找到数据);when v_exception then dbms_output.put_line(没有找到数据1);end;这里提到了一个新的数据类型 exceptionpragma exception_init(name,errcode)把错误号为-1400的错误赋值给v_exception在第二个when子句中不能使用sqlcode等系统变量三：自定义异常并抛出先看代码declare v_exception EXCEPTION;beginRAISE v_exception;exceptionwhen v_exception then dbms_output.put_line(捕获异常);end;先定义一个异常然后抛出这个异常然后捕获这个异常如此而已约束就那么几种1 NOT NULL 非空2 UNIQUE 唯一3 PRIMARY KEY 主键主键既是非空约束也是唯一约束4 FOREIGN KEY 外键5 CHECK 检查就这个比较灵活多变以下是一个较长用的check约束 lie is null or (lie between 100 and 250) 关于约束，有两点我觉得涉及到比较高级的话题，我这里不想多说了一个是延迟检查一个是约束激活和禁用约束的时候是否验证约束另外还有一些东西，我觉得没必要，也不讲了一：索引描述一个索引可以由一个或多个列组成对列设置索引其实就是对列的内容按一定的方式进行排序检索数据的时候，检索排过序的数据检索到最后一个有效数据之后就跳出检索这样就不必进行全表扫描了，同时可以应用很多算法提高检索效率数据库多用二分法检索数据二：索引分类1 唯一索引（Unique）不允许列内有重复的值2 非唯一索引（normal）允许列内有重复的值（默认创建非唯一索引）3 位图索引（bitmap）这类索引和上两类索引的实现逻辑不通三：各类索引的应用场合当你的列中有很多重复的数据时适合建立位图索引当查询时多用and 或or等逻辑运算符时适合建立位图索引当查询条件多用大于小于等比较运算符时适合建立唯一或非唯一索引下面看一个索引另外1 可以对函数创建索引，暂时先不谈这里的知识2 索引可以由PL/SQL编程创建，但人们一般都是用工具完成这个工作的，索引我也不多介绍了3 索引还有是否压缩和是否倒序的属性，用到的时候不多，也不介绍了一：创建或者修改一个试图create or replace view v_xland asselect title,content from labor.xland;二：删除一个视图drop view v_xland;三：更新视图中的数据如果检索一个视图里的数据没有什么限制的如果想对视图进行更新、删除、新建数据那么该视图就要满足一下条件1视图中不能有union distinct group by order by 的关键字或子句2视图中不能有子查询3视图中不能有分组函数4需要更新的列不是由列表达式定义的5表中所有NOT NULL列都在视图中四：视图中的约束和主键1：对视图中的某个列做了约束后不符合约束条件的数据不会出现在视图中2：基础表中的主键出现在视图中将成为视图的主键八：过程一：创建和修改一个过程create or replace procedure xland_proc(v_title in varchar2,v_int out number)isv_char varchar2(111);beginselect labor.xland.state,title into v_int,v_char from labor.xland where title = v_title;end xland_proc;存储过程有两个参数一个是输入参数一个是输出参数is后面begin前面是过程的声明部分如果返回一个记录集就要用到游标了执行这个存储过程declarex number;beginxland_proc(xland,x);dbms_output.put_line(to_char(x);end;输出结果0可以这样调用存储过程你的过程名(过程形参=你的变量,过程形参=你的变量)二：删除一个存储过程九：函数先看程序create or replace function get_content(v_title in xland.title%type,v_content out xland.content%type)return numberisv_state number;beginselect state,content into v_state,v_content from xland where title = v_title;return v_state;end get_content;参数可分为输入参数和输出参数函数还有返回值is和begin之间是定义部分函数其实与过程类似看调用过程declare xland varchar2(222):= xland; content varchar2(2048); out_v number;begin out_v := get_content(xland,content); dbms_output.put_line(to_char(out_v); dbms_output.put_line(content);end;定义了三个变量前两个是函数的参数后一个是返回值看输出结果0xland is my name删除一个过程drop function yourfuncname十：触发器一：语句级触发器语句级触发器是指当执行DML操作时，以语句为单位执行的触发器（注意与下面提到的行级触发器比较）先看代码create or replace trigger xland_triggerbefore insertor updateor deleteon labor.xlandbeginif(to_char(sysdate,DAY) in (星期六,星期日)or (to_char(sysdate,HH24) not between 8 and 18) thenraise_application_error(-20001,不是上班时间);end if;end;执行以下代码测试insert into labor.xland (xland.title,xland.content,xland.state) values (123,234,3);ORACLE抛出异常二：行级触发器行级触发器是指执行DML操作时，以数据行为单位执行的触发器，每一行都执行一次触发器先看代码：create or replace trigger xland_triggerbefore insert on labor.xlandfor each rowbegin if :new.title = xland then insert into labor.xland (xland.title,xland.content,xland.state) values (123,cha2,3); end if;end;执行以下代码测试insert into labor.xland (xland.title,xland.content,xland.state) values (xland,cha1,3);结果：在行级触发器中可以对列的值进行访问（很重要！）列名前加 :old. 表示变化前的值列名前加 :new. 表示变化后的值在when子句中不用冒号。三：instead of 触发器（视图上的触发器）先看代码create or replace trigger t_xlandinstead of inserton v_xlandfor each rowbegininsert into xland (title,content,state) values (1,1,1);end;其实就是取代了insert语句和其他触发器没什么大区别四：删除触发器drop trigger t_xland;十一：程序包一：程序包的说明create or replace package p_xlandispragma serially_reusable;v_val varchar2(32);function set_val(ref_val varchar2) return varchar2;end p_xland;程序包可以包含多个变量，函数，过程。函数或过程应该放在变量之后声明pragma serially_reusable;决定创建的包是否可以连续使用。有此句，调用包的时候结果将不受以前调用所影响二：程序包体create or replace package body p_xlandispragma serially_reusable;function set_val(ref_val varchar2) return varchar2isbeginv_val := ref_val;return myreturnval;end set_val;end p_xland;程序包体和程序包的名字必须相同程序包体内的过程或者函数与普通的过程函数相同总之程序包就像一个对象三：调用程序包declarev_myval varchar2(32):=xland;beginp_xland.v_val := v_myval;dbms_output.put_line(p_xland.v_val);v_myval := p_xland.set_val(xland2);dbms_output.put_line(p_xland.v_val);dbms_output.put_line(v_myval);end;下面是执行结果：xlandxland2myreturnval四：删除程序包drop package body your_pb_namedrop package your_p_name十二：游标一：普通游标declare-定义record类型变量type v_record is record(title labor.xland.title%type,state labor.xland.title%type);-定义字符串类型变量v_title labor.xland.title%type;-定义一个游标cursor c1 isselect title from labor.xland;begin open c1;-打开游标loopfetch c1 into v_title;-把游标中的数据存入变量，可以有多个变量if c1%found then-如果找到数据,找不到数据为notfound