原创：oracle PL/SQL编程基础 上(十一)

PL/SQL是oracle的专用语言,它对标准的SQL语言的扩展.SQL语句可以嵌套在PL/SQL语言中，并结合处理语句。 PL/SQL程序结构 使用了程序块的结构组织的代码.最简单的程序块是一种被称为”匿名块”的程序块,匿名块是指不会被oracle存储并且不能够重用程序块。 PL/SQL程序通常包括3部分:Declare部分、exception部分、Begin和end部分. Begin和end是PL/SQL的必须部分,总是begin开始end结束. Declare部分包含定义变量、常量和游标等类型的代码. Begin与end之间可以嵌套begin与end及exception. //打开服务器的输出 SQL> set serveroutput on; SQL> begin //开始 2 dbms_output.put_line('Hello World'); 3 end; 4 / 变量与常量的声明： 变量名 类型(字符，日期，数字，布尔)[:=缺省值]; 常量名 constant 类型(字符，日期，数字，布尔) [:=缺省值]; 变量与常量的区别： 变量可以在任何部分进行赋值. 而常量在declare中声明并且赋值. 注意：都是在declare中声明 正确的操作： declare pagesize constant number(2) :=10; begin dbms_output.put_line(pagesize); end; / 作用域： 是能够引用变量名称这样的标识符的程序块。 declare pagesize number(2):=10; //在整个中都有效 begin dbms_output.put_line(pagesize); declare pagesize number(2):=99; //只是在内容声明部分到end结束有效 begin dbms_output.put_line(pagesize); end; end; / //声明一个整型的变量 SQL> declare 2 age number(2); 3 begin 4 age:=90; 5 dbms_output.put_line(age); 6 end; 7 / //声明后直接赋值 SQL> declare 2 age number(2) :=90; 3 begin 4 dbms_output.put_line(age); 5 end; 6 / //捕获异常处理 SQL> declare 2 age number(2); 3 begin 4 age :='qwe' 5 ; 6 exception 7 when others then 8 dbms_output.put_line('赋值失败'); 9 end; 10 / 条件判断语句 有if与case语句 If语句 If语法 If（expression1）then pl/sql_statement Else Pl/sql_statement; End if; If（expression1）then pl/sql_statement Else if（expression2） Pl/sql_statement; Else Pl/sql_statement; End if; Case语句 从oracle9i以后引入 Case <selector> When <expression1> then pl/sql_statement1; When <expression2> then pl/sql_statement2; ……. [else pl/sql_statement;] End; //案例 SQL> declare score number(2) :=80; begin if score>70 then dbms_output.put_line('成绩合格'); end if; end; / SQL> declare score number(2) :=80; begin if score>90 then dbms_output.put_line('成绩合格'); else dbms_output.put_line('成绩不合格'); end if; end; / 成绩不合格 SQL> declare score number(2):=8; begin case score when 9 then dbms_output.put_line('成绩优秀'); when 8 then dbms_output.put_line('成绩亮'); end case; end; / 循环语句 最基本的循环称为无条件循环,如果没有指定exit语句,循环将无条件执行，这种循环称为死循环,死循环尽量避免。 语法格式如下： Loop ---statement--- Exit when condition End loop; 案例： SQL> declare i number(2):=1; begin loop dbms_output.put_line(i); i:=i+1; exit when i>10; end loop; end; / While循环 语法： While condition Loop Statement; End loop; SQL> declare i number(2):=1; begin while i<10 loop dbms_output.put_line(i); i:=i+1; end loop; end; / For循环 For loop_control_variable in [reverse] lower upper loop Statement; End loop; SQL> begin for i in 1..10 loop dbms_output.put_line(i); end loop; end; / PL/SQL语句中的SQL语句 declare countnum number(2); //声明变量 begin select count(*) into countnum from emp; //into的作用把从数据中检测出的数据赋值给哪个变量 dbms_output.put_line(countnum); end; / 注意：如果在select中没有into语句会出现bug declare empno number(4); ename varchar2(10); job varchar2(9); begin select empno,ename,job into empno,ename,job from emp; dbms_output.put_line(empno||'/'||ename||'/'||job); end; / 以上代码中会出现bug bug原因是因为定义的变量只可以存储一个单独的值,而你返回的是多个值.所以应该为如下： SQL> declare 2 empno number(4); 3 ename varchar2(10); 4 job varchar2(9); 5 begin 6 select empno,ename,job into empno,ename,job from emp where empno='7369'; //在这加上where条件了 7 dbms_output.put_line(empno||'/'||ename||'/'||job); 8 end; 9 / 7369/SMITH/CLERK 是用%TYPE和%ROWTYPE类型的变量 在定义变量时处理可以使用oracle规定的数据类型外,还可以使用%TYPE和%ROWTYPE来定义变量. %TYPE类型的变量是专门为存储在数据列中检索到的值而创建的.对于使用%TYPE创建的变量，其数据类型由系统根据检索的数据列的数据类型决定. %ROWTYPE类型的变量,它可以一次存储从数据库检索的一行数据。 当你不知道表中的数据类型是什么时候?那你就考虑%TYPE来解决此问题。 SQL> declare 2 empno emp.empno%TYPE; //声明的时候一定要加上表的中列 3 ename emp.ename%TYPE; 4 job emp.job%TYPE; 5 begin 6 select empno,ename,job into empno,ename,job from emp where empno='7369'; 7 dbms_output.put_line(empno||'/'||ename||'/'||job); 8 end; 9 / 7369/SMITH/CLERK emp.empno%TYPE;解析：首先它到emp表中去查找empno列 %TYPE返回其数据的数据类型。 %TYPE的好处： 1、 可移植性高(当我们对表的数据类型发生改变时,用户不必考虑定义变量类型) 2、 用户不必查看数据类型就可以定义变量能够存储检索的数据。 %ROWTYPE SQL> declare 2 row_data emp%ROWTYPE; 3 begin 4 select * into row_data from emp where empno='7369'; 5 dbms_output.put_line(row_data.empno||'/'||row_data.ename||'/'||row_data.job); 6 end; 7 / 注意：如果定义了％rowtype类型的变量,该变量的结构与定义表的结构完全相同,查询时必须使用*或者列举全部的字段信息。 复合变量 复合变量可以将不同的数据类型的多个值存储在一个单元中.由于复合数据类型可以有用户根据需要定义其结构,所以复合数据类型也称为自定义数据类型。 PL/SQL提供了两种类型的复合数据类型: 1、 记录类型 在记录类型中可以存储多个标量值,与数据库中的行相似 2、 记录表类型 允许用户在程序代码中使用”表”,以便存储多个行的数据。它只在程序运行期间有效。类似于程序代码中集合|数组。 记录类型： 必须使用TYPE语句定义记录类型的语法声明如下： TYPE record_name is record( Field_name data_type[not null， :=default value] ……… )； 与%Rowtype不同之处是 1、%rowtype查询的是全部数据 2、记录类型必须使用type语法格式声明 //案例 SQL> declare 2 type empinfo is record( //声明一个记录类型 3 empno number(4), 4 ename varchar2(10), 5 job varchar2(9) 6 ); 7 8 emp_data empinfo; //声明一个记录类型的变量 9 begin 10 select empno,ename,job into emp_data from emp where empno=7369; //查询的结果赋值给定义的变量 11 dbms_output.put_line(emp_data.empno||'/'||emp_data.ename||'/'||emp_data.job); 12 end; 13 / 记录表类型 它可以处理多个记录或多个行记录。 1、 为什么使用记录表类型呢? 因为我们查询的数据的往往需要返回多行记录,所以需要记录表类型。 2、 定义记录表类型 TYPE table_name is table of data_type[not null] Index by binary_integer;//主键的索引 declare type table_emp is table of emp%rowtype //创建一个表 此表的类型与emp表的类型一致 index by binary_integer; type table_text is table of varchar2(20) //创建一个表 此表具有一个varchar2列的简单表 index by binary_integer; empt table_emp; //声明记录表类型的变量 tabtext table_text; begin 案例： SQL> declare 2 type table_emp is table of emp%rowtype 3 index by binary_integer; 4 empt table_emp; 5 begin 6 empt(1).ename:='wangyi'; 7 dbms_output.put_line(empt(1).ename); 8 end; 9 / //返回总记录 SQL> declare 2 type table_emp is table of emp%rowtype 3 index by binary_integer; 4 empt table_emp; 5 begin 6 dbms_output.put_line(empt.count); 7 end; 8 / 0 //没有记录 //删除的操作 表名.Delete(记录数); //检索记录变量 First：获取第一个的索引 Next：下一个的索引 但是必须有参数 Last：最后一个的索引 SQL> declare 2 type table_emp is table of emp%rowtype 3 index by binary_integer; 4 empt table_emp; 5 i number(2):=1; 6 begin 7 while i<10 8 loop 9 empt(i).ename:='wangyi'; 10 i:=i+1; 11 end loop; 12 13 dbms_output.put_line(empt.count); 14 15 empt.delete(2); 16 17 dbms_output.put_line(empt.count); 18 19 dbms_output.put_line(empt.first); 20 dbms_output.put_line(empt.next(2)); 21 dbms_output.put_line(empt.last); 22 end; 23 / 9 8 1 3 9 //查询数据库的数据赋值给你创建的记录表类型 分析一： SQL> declare 2 type table_emp is table of emp%rowtype 3 index by binary_integer; 4 empt table_emp; 5 i number(10):=0; 6 begin 7 8 for rec in (select * from emp) loop 9 i:=i+1; 10 empt(i).ename:=rec.ename; 11 end loop; 12 13 dbms_output.put_line(empt.count); 14 end; 15 / 15 具体实现： //把查询的的数据全部赋值给记录表类型 declare type table_emp is table of emp%rowtype index by binary_integer; empt table_emp; i number(10):=0; j number(10):=1; begin for rec in (select * from emp) loop i:=i+1; empt(i).empno:=rec.empno; empt(i).ename:=rec.ename; empt(i).job:=rec.job; empt(i).mgr:=rec.mgr; empt(i).hiredate:=rec.hiredate; empt(i).sal:=rec.sal; empt(i).comm:=rec.comm; empt(i).deptno:=rec.deptno; end loop; while j<=empt.count loop dbms_output.put_line(empt(j).empno||'/'||empt(j).ename||'/'||empt (j).job||'/'||empt(j).mgr||'/'||empt(j).hiredate||'/'||empt(j).sal||'/'||empt (j).comm||'/'||empt(j).deptno); j:=j+1; end loop; end; / SQL> declare 2 type table_emp is table of emp%rowtype 3 index by binary_integer; 4 empt table_emp; 5 i number(10):=0; 6 j number(10):=1; 7 begin 8 9 for rec in (select * from emp) loop 10 i:=i+1; 11 empt(i).empno:=rec.empno; 12 empt(i).ename:=rec.ename; 13 empt(i).job:=rec.job; 14 empt(i).mgr:=rec.mgr; 15 empt(i).hiredate:=rec.hiredate; 16 empt(i).sal:=rec.sal; 17 empt(i).comm:=rec.comm; 18 empt(i).deptno:=rec.deptno; 19 end loop; 20 21 dbms_output.put_line(empt.count); 22 23 while j<=empt.count loop 24 dbms_output.put_line(empt(j).empno||'/'||empt(j).ename||'/'||empt 25 26 (j).job||'/'||empt(j).mgr||'/'||empt(j).hiredate||'/'||empt(j).sal||'/'||empt 27 28 (j).comm||'/'||empt(j).deptno); 29 j:=j+1; 30 end loop; 31 end; 32 / 以上内容归redarmy_chen所有,如需转载请附带出处,如有疑问请发送至redarmy_chen@qq.com