PLSQL小记（上）

PLSQL小记（上）

2008年09月16日 星期二 09:02

*****************************************    PLSQL基本结构 ***************************************** 基本数据类型变量     1. 基本数据类型     Number 数字型     Int 整数型     Pls_integer 整数型，产生溢出时出现错误     Binary_integer 整数型，表示带符号的整数     Char 定长字符型，最大255个字符     Varchar2 变长字符型，最大2000个字符     Long 变长字符型，最长2GB     Date 日期型     Boolean 布尔型（TRUE、FALSE、NULL三者取一）     在PL/SQL中使用的数据类型和Oracle数据库中使用的数据类型，有的含义是完全一致的，有的是有不同的含义的。     2. 基本数据类型变量的定义方法     变量名 类型标识符 [not null]:=值;     declare         age number(3):=26; --长度为3，初始值为26     begin         commit;     end;     其中，定义常量的语法格式：     常量名 constant 类型标识符 [not null]:=值;     declare         pi constant number(9):=3.1415926;--为pi的数字型常量，长度为9，初始值为3.1415926     begin         commit;     end; 表达式     变量、常量经常需要组成各种表达式来进行运算，下面介绍在PL/SQL中常见表达式的运算规则。         1. 数值表达式     PL/SQL程序中的数值表达式是由数值型常数、变量、函数和算术运算符组成的，可以使用的算术运算符包括+（加法）、-（减法）、*（乘法）、/（除法 ）和**（乘方）等。     命令窗口中执行下列PL/SQL程序，该程序定义了名为result的整数型变量，计算的是10+3*4-20+5**2的值，理论结果应该是27。     ―――――――――――――――――――――――――――――――――――――     set serveroutput on     Declare        result integer;     begin        result:=10+3*4-20+5**2;        dbms_output.put_line('运算结果是：'||to_char(result));     end;     ―――――――――――――――――――――――――――――――――――――     dbms_output.put_line函数输出只能是字符串，因此利用to_char函数将数值型结果转换为字符型。         2. 字符表达式     字符表达式由字符型常数、变量、函数和字符运算符组成，唯一可以使用的字符运算符就是连接运算符“||”。         3. 关系表达式     关系表达式由字符表达式或数值表达式与关系运算符组成，可以使用的关系运算符包括以下9种。     < 小于     > 大于     = 等于（不是赋值运算符:=）     like 类似于     in 在……之中     <= 小于等于     >= 大于等于     != 不等于 或<>     between 在……之间     关系型表达式运算符两边的表达式的数据类型必须一致。     4. 逻辑表达式     逻辑表达式由逻辑常数、变量、函数和逻辑运算符组成，常见的逻辑运算符包括以下3种。     NOT：逻辑非     OR：逻辑或     AND：逻辑与     运算的优先次序为NOT、AND和OR。 PLSQL函数     PL/SQL程序中提供了很多函数供扩展功能，除了标准SQL语言的函数可以使用外，最常见的数据类型转换函数有以下3个。     To_char：将其他类型数据转换为字符型。     To_date：将其他类型数据转换为日期型。     To_number：将其他类型数据转换为数值型。     继续追加中.. 系统输出打印     利用pl/sql在数据库服务器端打印一句话：     set serveroutput on--设置数据库输出，默认为关闭，每次重新打开窗口需要重新设置。     BEGIN         DBMS_OUTPUT.PUT_LINE('Hello PL/SQL');     END; pl/sql程序中对大小写不敏感（打印声明的变量）    ―――――――――――――――――――――――――――――――――――――     set serveroutput on     DECLARE       v_char varchar2(20):='a';       v_char1 varchar2(20):='b';     BEGIN       DBMS_OUTPUT.PUT_LINE(v_char);       DBMS_OUTPUT.PUT_LINE(v_char1);     END; pl语句块是pl/sql里最小的编程块，其中可以再嵌套begin end     begin      dbms_output.put_line('Hello World');      dbms_output.put_line('2*3='||(2*3));      dbms_output.put_line('what''s');     end;    ――――――――――――――――――――――――――――――――――――― PL/SQL中的变量声明 所有变量必须在declare中声明，程序中不允许声明。 没有初始化的变量默认值为null，屏幕上null是看不见的，命名习惯：PL/SQL中变量一般以v_开头（等同于存储过程中as和begin区域的变量定义习惯）。 注意number也能存小数，最长38位，所以以后建议整数都用binary_integer存。 long是字符类型，boolean类型不能打印。 标准变量类型：数字，字符，时间，布尔。    ―――――――――――――――――――――――――――――――――――――     declare v_number1 number; v_number2 number(3,2) ; v_number3 binary_integer :=1; v_name varchar2(20) :='kettas'; v_date date :=sysdate; v_long long :='ni hao'; v_b boolean := true;     begin if (v_number1 is null) then    dbms_output.put_line( 'hello'); end if; dbms_output.put_line(v_number1); dbms_output.put_line(v_number2); dbms_output.put_line(v_number3); dbms_output.put_line(v_name); dbms_output.put_line(v_date); dbms_output.put_line(v_long);       --dbms_output.put_line(v_b); --执行该句ORACLE提示“调用 'PUT_LINE' 时参数个数或类型错误”     end;    ―――――――――――――――――――――――――――――――――――――    备注：    关于声明number(4,3)中括号中的两个数字的意义，前面的数字叫精度，后面的叫刻度。    刻度：      当刻度为正数的时候，表示四舍五入到小数点后面的位数      当刻度为负数的时候，表示四舍五入到小数点前面的位数    精度：      从数字的最前面不为零开始到刻度精确到的位置    v_Number number(4,3):=123.12312    1、按刻度进行四舍五入得到123.123    2、确定刻度精确到的位置123123处，精度为6位（.符号不算）    2、根据精度进行判断6位（>4）精度上限值 --报错不能存储    number(3,-3):=44445    1、根据刻度－3进行四舍五入得到44000    2、小数点向前移动3位44.此位置为刻度精确到的位置    3、根据精度进行判断2位（<3）精度上限值 --不报错可存储结果为44000       DECLARE      v_Number number(4,3):=123.12312;--实际精度6位大于上限精度值4位，提示“ORA-06502: PL/SQL: 数字或值错误 : 数值精度太高”    BEGIN      DBMS_OUTPUT.PUT_LINE(v_Number);    END    ;       DECLARE      v_Number number(7,3):=4555; --实际精度7位等于上限精度值，可以存储    BEGIN      DBMS_OUTPUT.PUT_LINE(v_Number);    END    ;    *****************************************    变量赋值方式 ***************************************** oracle中变量赋值方式是值拷贝而非引用       declare        v_number1 number:=100;        v_number2 number;    begin        v_number2:=v_number1;        v_number1:=200;        dbms_output.put_line(v_number1); --200        dbms_output.put_line(v_number2); --100    end; *****************************************    PLSQL复合类型 ***************************************** 记录类型record record类型最常用，声明的时候可以加not null，但必须给初始值，如果record类型一致可以相互赋值，如果类型不同，里面的字段恰好相同，不能互相赋值。引用记录型变量的方法是“记录变量名.基本类型变量名”。 ――――――――――――――――――――――――――――――――――――― declare        type t_first is record(             id number(3),             name varchar2(20)        );        v_first t_first; begin      v_first.id:=1;      v_first.name:='cheng';      dbms_output.put_line(v_first.id);      dbms_output.put_line(v_first.name); end; record类型变量间赋值    declare        type t_first is record(          id number,          name varchar2(20)          );        v_first t_first;        v_second t_first; begin        v_first.id:=1;        v_first.name:='susu';               v_second:=v_first;--相互赋值               v_first.id:=2;        v_first.name:='kettas';        dbms_output.put_line(v_first.id);        dbms_output.put_line(v_first.name);        dbms_output.put_line(v_second.id);        dbms_output.put_line(v_second.name);    end;    ――――――――――――――――――――――――――――――――――――― 表类型变量table 语法如下：     type 表类型 is table of 类型 index by binary_integer;     表变量名 表类型; 类型可以是前面的类型定义，index by binary_integer子句代表以符号整数为索引，这样访问表类型变量中的数据方法就是“表变量名(索引符号整数)”。table类型，相当于java中的Map容器，就是一个可变长的数组，key（符号整数索引）必须是整数，可以是负数，value（类型）可以是标量，也可以是record类型。可以不按顺序赋值，但必须先赋值后使用。 1. 定义一维表类型变量    ―――――――――――――――――――――――――――――――――――――    declare         type t_tb is table of varchar2(20) index by binary_integer;         v_tb t_tb;    begin       v_tb(100):='hello';       v_tb(98):='world';       dbms_output.put_line(v_tb(100));       dbms_output.put_line(v_tb(98));    end;        类型为record的表类型变量    declare         type t_rd is record(id number,name varchar2(20));         type t_tb is table of t_rd index by binary_integer;         v_tb2 t_tb;    begin         v_tb2(100).id:=1;         v_tb2(100).name:='hello';         --dbms_output.put_line(v_tb2(100).id);         --dbms_output.put_line(v_tb2(100).name);         dbms_output.put_line(v_tb2(100).id||'    '||v_tb2(100).name);    end;    ――――――――――――――――――――――――――――――――――――― 2. 定义多维表类型变量 该程序定义了名为tabletype1的多维表类型，相当于多维数组，table1是多维表类型变量，将数据表tempuser.testtable中recordnumber为60的记录提取出来 存放在table1中并显示。    ―――――――――――――――――――――――――――――――――――――    declare       type tabletype1 is table of testtable%rowtype index by binary_integer;       table1 tabletype1;    begin        select * into table1(60) from tempuser.testtable where recordnumber=60;        dbms_output.put_line(table1(60).recordnumber||table1(60).currentdate);    end;        备注：在定义好的表类型变量里，可以使用count、delete、first、last、next、exists和prior等属性进行操作，使用方法为“表变量名.属性”，返回的是数字。        set serveroutput on    declare         type tabletype1 is table of varchar2(9) index by binary_integer;         table1 tabletype1;    begin         table1(1):='成都市';         table1(2):='北京市';         table1(3):='青岛市';         dbms_output.put_line('总记录数：'||to_char(table1.count));         dbms_output.put_line('第一条记录：'||table1.first);         dbms_output.put_line('最后条记录：'||table1.last);         dbms_output.put_line('第二条的前一条记录：'||table1.prior(2));         dbms_output.put_line('第二条的后一条记录：'||table1.next(2));     end;     ――――――――――――――――――――――――――――――――――――― *****************************************      %type和%rowtype ***************************************** 使用%type定义变量，为了让PL/SQL中变量的类型和数据表中的字段的数据类型一致，Oracle 9i提供了%type定义方法。这样当数据表的字段类型修改后，PL/SQL程序中相应变量的类型也自动修改。     ―――――――――――――――――――――――――――――――――――――     create table student(        id number,        name varchar2(20),        age number(3,0)     );     insert into student(id,name,age) values(1,'susu',23);     --查找一个字段的变量     declare        v_name varchar2(20);        v_name2 student.name%type;     begin        select name into v_name2 from student where rownum=1;        dbms_output.put_line(v_name2);     end;     --查找多个字段的变量     declare         v_id student.id%type;         v_name student.name%type;         v_age student.age%type;     begin       select id,name,age into v_id,v_name,v_age from student where rownum=1;       dbms_output.put_line(v_id||' '||v_name||' '||v_age);     end;     --查找一个类型的变量，推荐用*     declare        v_student student%rowtype;     begin        select * into v_student from student where rownum=1;        dbms_output.put_line(v_student.id||' '||v_student.name||' '||v_student.age);     end;     --也可以按字段查找，但是字段顺序必须一样，不推荐这样做     declare        v_student student%rowtype;     begin      select id,name,age into v_student from student where rownum=1;      dbms_output.put_line(v_student.id||' '||v_student.name||' '||v_student.age);     end;     declare        v_student student%rowtype;     begin      select id,name,age into v_student.id,v_student.name,v_student.age from student where id=1;      --select * into v_student.id,v_student.name,v_student.age from student where id=1;      dbms_output.put_line();     end;     ―――――――――――――――――――――――――――――――――――――     备注：insert，update，delete，select都可以，create table，drop table不行。DPL，DML，和流程控制语句可以在pl/sql里用，但DDL语句不行。         declare        v_name student.name%type:='wang';     begin        insert into student(id,name,age) values(2,v_name,26);     end;         begin        insert into student(id,name,age) values(5,'hehe',25);     end;     declare        v_name student.name%type:='hexian';     begin        update student set name=v_name where id=1;     end;     begin        update student set name='qinaide' where id=2;     end;     ――――――――――――――――――――――――――――――――――――― *****************************************     PLSQL变量的可见空间 ***************************************** 变量的作用域和可见性，变量的作用域为变量申明开始到当前语句块结束。当外部过程和内嵌过程定义了相同名字的变量的时候，在内嵌过程中如果直接写这个变量名是没有办法访问外部过程的变量的，可以通过给外部过程定义一个名字<<outername>>,通过outername变量名来访问外部过程的变量（待测试..）。     ―――――――――――――――――――――――――――――――――――――     declare             v_i1 binary_integer:=1;     begin          declare             v_i2 binary_integer:=2;          begin             dbms_output.put_line(v_i1);             dbms_output.put_line(v_i2);          end;       dbms_output.put_line(v_i1);     --dbms_output.put_line(v_i2); 解开后执行Oracle会提示“必须说明标识符 'V_I2'”     end;     ――――――――――――――――――――――――――――――――――――― *****************************************     PLSQL流程控制 ***************************************** if判断 declare        v_b boolean:=true; begin if v_b then          dbms_output.put_line('ok');       end if; end; if else判断 declare         v_b boolean:=true; begin      if v_b then         dbms_output.put_line('ok');      else         dbms_output.put_line('false');      end if; end; if elsif else判断 declare         v_name varchar2(20):='cheng'; begin      if v_name='0701' then         dbms_output.put_line('0701');      elsif v_name='cheng' then         dbms_output.put_line('cheng');      else         dbms_output.put_line('false');      end if; end; loop循环，注意推出exit是推出循环，而不是推出整个代码块 declare        v_i binary_integer:=0; begin      loop          if v_i>10 then             exit;          end if;          v_i:=v_i+1;          dbms_output.put_line('hehe');      end loop;          dbms_output.put_line('over'); end; loop简化写法 declare        v_i binary_integer :=0; begin      loop          exit when v_i>10;          v_i :=v_i+1;          dbms_output.put_line('hehe');      end loop;          dbms_output.put_line('over'); end; while循环 declare        v_i binary_integer:=0; begin        while v_i<10 loop              dbms_output.put_line('hello'||v_i );              v_i:=v_i+1;        end loop;        dbms_output.put_line('over'); end; for循环，注意不需要声明变量 begin      for v_i in 0..10 loop          dbms_output.put_line('hello'||v_i);      end loop;          dbms_output.put_line('over'); end;