ORACLE PL/SQL AND analyse fuction

PLSQL开发笔记和小结
 

*****************************************
  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;

*****************************************
     PLSQL异常处理
*****************************************
1、声明异常
 异常名 EXCEPTION；
2、抛出异常
 RAISE 异常名
3、处理异常
 抛出异常后的逻辑代码不会被继续执行

异常的定义使用
    ―――――――――――――――――――――――――――――――――――――
    begin
          dbms_output.put_line(1/0);
    exception
            when others then
                dbms_output.put_line('error');
    end;

    declare
            e_myException exception;
    begin
            dbms_output.put_line('hello');
            raise e_myException; --raise抛出异常,用此关键字，抛出后转到自定义的e_myException ，执行其里面的putline函数后，再跳到end处，结束PL/SQL块，raise接下面的2句不会继续执行。
            dbms_output.put_line('world');
            dbms_output.put_line(1/0);
    exception
            when e_myException then
                dbms_output.put_line(sqlcode); --当前会话执行状态，错误编码
                dbms_output.put_line(sqlerrm); --当前错误信息
                dbms_output.put_line('my error');
            when others then
                dbms_output.put_line('error');
    end;
    ―――――――――――――――――――――――――――――――――――――
*****************************************
  PLSQL游标和goto语句
*****************************************
备注：下面提到的游标为静态cursor，包括显示和隐式。
游标，从declare、open、fetch、close是一个完整的生命旅程。当然了一个这样的游标是可以被多次open进行使用的，显式cursor是静态cursor，她的作用域是全局的，但也必须明白，静态cursor也只有pl/sql代码才可以使用它。静态游标变量是在定义时就必须指定SQL语句。

cursor 游标(结果集)用于提取多行数据，定义后不会有数据，使用后才有。一旦游标被打开，就无法再次打开(可以先关闭，再打开)。
    declare
          cursor c_student is  select * from book;
    begin
          open c_student;
          close c_student;
    end;

第二种游标的定义方式，用变量控制结果集的数量。
    declare
          v_id binary_integer;
          cursor c_student is select * from book where id>v_id;
    begin
          v_id:=10;
          open c_student;
          close c_student;
    end;

第三种游标的定义方式，带参数的游标，用的最多。
    declare
          cursor c_student(v_id binary_integer) is select * from book where id>v_id;
    begin
          open c_student(10);
          close c_student;
    end;

游标的使用，一定别忘了关游标。
    declare
          v_student book%rowtype;
          cursor c_student(v_id binary_integer) is select * from book where id>v_id;
    begin
          open c_student(10);
          fetch c_student into v_student;
          close c_student;
          dbms_output.put_line(v_student.name);
    end;


如何遍历游标fetch
  游标的属性 %found，%notfound,%isopen,%rowcount。
  %found:若前面的fetch语句返回一行数据，则%found返回true，如果对未打开的游标使用则报ORA-1001异常。
  %notfound，与%found行为相反。
  %isopen，判断游标是否打开。
  %rowcount:当前游标的指针位移量，到目前位置游标所检索的数据行的个数，若未打开就引用，返回ORA-1001。

注：
no_data_found和%notfound的用法是有区别的，小结如下
1）SELECT . . . INTO 语句触发 no_data_found；
2）当一个显式光标(静态和动态)的 where 子句未找到时触发 %notfound；
3）当UPDATE或DELETE 语句的where 子句未找到时触发 sql%notfound；
4）在光标的提取(Fetch)循环中要用 %notfound 或%found 来确定循环的退出条件，不要用no_data_found。

下面是几个实例：
create table BOOK
(
  ID       VARCHAR2(10) not null,
  BOOKNAME VARCHAR2(10) not null,
  PRICE    VARCHAR2(10) not null,
  CID      VARCHAR2(10) not null
);

--insert
create or replace procedure say_hello(
i_name in varchar2,
o_result_msg out varchar2
)
     as
     v_price varchar2(100);
     e_myException exception;

     begin
        insert into book(id,bookname,price) values (1,2,3);
        o_result_msg := 'success';
     exception
        when others then
             rollback;
             o_result_msg := substr(sqlerrm, 1, 200);
     end;

--update or delete
create or replace procedure say_hello(
i_name in varchar2,
o_result_msg out varchar2
)
     as
     v_price varchar2(100);
     e_myException exception;

     begin
        update book set price = '55' where bookname = i_name;
        delete from book where bookname = i_name;
        if sql%notfound then
           raise e_myException;
        end if;
        /*
 if sql%rowcount = 0 then--写法2
           raise e_myException;
        end if;
 */
        o_result_msg := 'success';
     exception
        when e_myException then
             rollback;
             o_result_msg := 'update or delete dail';
     end;

--select
create or replace procedure say_hello(
i_name in varchar2,
o_result_msg out varchar2
)
     as
     v_price varchar2(100);
     e_myException exception;

     begin
        select price into v_price from book where bookname = i_name;
        o_result_msg := 'success';
     exception
        when no_data_found then
             rollback;
             o_result_msg := 'select into dail';
     end;

loop方式遍历游标
    declare
          v_bookname  varchar2(100);
          cursor c_book(i_id number) is select bookname from book where id = i_id;
    begin
        Open  c_book(i_id);
        Loop
            Fetch c_book into v_bookname;
            exit when c_student%notfound;
              update book set price = '33' where bookname = v_bookname;
        End Loop;
        Close c_book;
    end;
    或
    declare
          v_bookname  varchar2(100);
          cursor c_book(i_id number) is select bookname from book where id = i_id;
    begin
        Open  c_book(i_id);
          Fetch c_book into v_bookname;
          While c_book%Found
          Loop
              update book set price = '33' where bookname = v_bookname;
          Fetch  c_book into v_bookname;
          End Loop;
        Close c_book;
    end;
       

while循环遍历游标，注意，第一次游标刚打开就fetch，%found为null，进不去循环
解决方法：while nvl(c_student%found,true) loop
    declare
         v_bookname  varchar2(100);
         cursor c_book(i_id number) is select bookname from book where id = i_id;
    begin
         Open  c_book(i_id);
         while nvl(c_book%found,true) --或这种写法：while c_book%found is null or c_book%found loop   
             Fetch c_book into v_bookname;
             update book set price = '33' where bookname = v_bookname;
         End Loop;
         Close c_book;
    end;

for循环遍历，最简单，用的最多，不需要 声明v_student,Open和Close游标和fetch操作(不用打开游标和关闭游标，实现遍历游标最高效方式)
    declare
         cursor c_book(i_id number) is select bookname from book where id = i_id;
    begin
         for cur in c_book(i_id) --直接将入参i_id传入cursor即可
         loop
             update book set price = '53' where bookname = cur.bookname;
         end loop;
    end;

goto例子，一般不推荐使用goto，会使程序结构变乱
    declare
         i number:=0;
    begin
         if i=0 then
             goto hello;
         end if;
         <<hello>>
         begin
             dbms_output.put_line('hello');
             goto over;
         end;
         <<world>>
         begin
             dbms_output.put_line('world');
             goto over;
         end;
         <<over>>
             dbms_output.put_line('over');
     end;
*****************************************
   Oracle存储过程
*****************************************
在谈存储过程书写中的一些规则时，先看一下执行它的规则，在命令窗口执行存储过程sp_get_product_prompt
     set serveroutput on
     var ret1 varchar2(200);
     var ret2 varchar2(200);
     exec sp_get_product_prompt(83,:ret1,:ret2); --或execute
     print ret1;
     print ret2;
     或
     set serveroutput on
     declare
        ret1 varchar2(200);
        ret2 varchar2(200);
     begin
        sp_get_product_prompt(83,ret1,ret2);
        dbms_output.put_line(ret1);
        dbms_output.put_line(ret2);
     end;

存储过程入参，不论类型，缺省情况下值都为null,入参和出参不能有长度,其中关键字as可以替换成is,存储过程中变量声明在as和begin之间，同时，存储过程中可以再调用其它的存储过程，如果要保证存储过程之间的事务处理不受影响，可以定义为自治事务。
     create or replace procedure say_hello(
       v_name in varchar2,
       v_flag number,
       o_ret out number
     )
     as
     begin
       if v_name is null and v_flag is null then --v_name和v_flag都等于null
           o_ret := 10;
       else
           o_ret := 100;
       end if;
     end;

对于入参为null情况下给予缺省值
     create or replace procedure say_hello(
       i_name in varchar2,
       i_flag number,
       o_ret out number
     )
     as
       v_name  varchar2(100);
     begin
       if i_name is null then
          v_name := '0';
       else
          v_name := i_name;
       end if;
       insert into phone(..,wname..,) values(..,v_name,..); 
    
     end;
或直接在insert语句中调用nvl函数赋缺省值
     insert into phone(..,wname..,) values(..,nvl(v_name,' '),..); ----如果将' '写成'',则insert进来的v_name值还是为''等价于null值

带一个参数的存储过程
   输入参数in，输入参数不能进行:=赋值，但可以将它赋给as后面定义的变量；
   输入参数in，可以作为变量进行条件判断；
   默认不写就是in；
   存储过程没有重载，这个有参的say_hello会替代已经存在的无参say_hello。

     create or replace procedure say_hello(v_name in varchar2)
     as
     begin
        --v_name:='a'; --存储过程入参v_name不能做为赋值目标
        dbms_output.put_line('hello '||v_name);
     end;

存储过程输入参数作为变量进行条件判断
     create or replace procedure say_hello(
        i_opFlag in number
     )
     as
        v_name varchar2(100);
     begin
        if i_opFlag = 1 then
    v_name :='0';
        else
    v_name :='haha';
        end if;
        dbms_output.put_line('hello '||v_name);
     end;


利用存储过程中定义的变量对入参的空值处理：
     create or replace procedure say_hello(
        i_name in varchar2
     )
     as
        v_name varchar2(100);
     begin
        if i_name is null then
    v_name :='0';
        else
    v_name :=i_name;--将入赋值给定义变量
        end if;
        dbms_output.put_line('hello '||v_name);
     end;

多个参数的存储过程
     create or replace procedure say_hello(
        v_first_name in varchar2,
        v_last_name in varchar2)
     as
     begin
        dbms_output.put_line('hello '||v_first_name||'.'||v_last_name);
     end;

out输出参数，用于利用存储过程给一个或多个变量赋值，类似于返回值
     create or replace procedure say_hello(
        v_name in varchar2,
        v_content out varchar2
     )
     begin
        v_content:='hello'||v_name;
     end;

     调用：
     declare
        v_con varchar2(200);
        v_in varchar2(20):='wang';
     begin
        say_hello(v_in,v_con);
        dbms_output.put_line(v_con);
     end;

in out参数，既赋值又取值
     create or replace procedure say_hello(v_name in out varchar2)
     as
     begin
        v_name:='hi '||v_name;
     end;

     调用：
     declare
        v_inout varchar2(20):='wangsu';
     begin
        say_hello(v_inout);
        dbms_output.put_line(v_inout);
     end;

对存储过程入参赋缺省值
     create or replace procedure say_hello(
        v_name varchar2 default 'susu',
        v_content varchar2 default 'hello'
     )
     as
     begin
        dbms_output.put_line(v_name||' '||v_content);
     end;

     调用：(用指明形参名的方式调用更好)
     begin
        say_hello();
     end;
     或
     begin
        say_hello('cheng');
     end;
     或
     begin
     say_hello(v_name=>'cheng');
    end;

*****************************************
 PLSQL中的function
*****************************************
FUNCTION和PROCEDURE的区别
1、函数有返回值，过程没有
2、函数调用在一个表达式中，过程则是作为pl/sql程序的一个语句
  过程和函数都以编译后的形式存放在数据库中，函数可以没有参数也可以有多个参数并有一个返回值。过程
  有零个或多个参数，没有返回值。函数和过程都可以通过参数列表接收或返回零个或多个值，函数和过程的
  主要区别不在于返回值，而在于他们的调用方式，过程是作为一个独立执行语句调用的，函数以合法的表达
  式的方式调用
    create or replace function func(v_name in varchar2)
    return varchar2
    is
    begin
       return(v_name||' hello');
    end;

    调用：
    declare
       v_name varchar2(20);
    begin
       v_name:=func('cheng');
       dbms_output.put_line(v_name);
    end;

带out参数的函数
    create or replace function func(
       v_name in varchar2,
       v_content out varchar2
    )
    return varchar2
    is
    begin
       v_content:=v_name||' hello';
       return v_content;
    end;

    调用：
    declare
       v_name varchar2(20);
       v_name1 varchar2(20);
    begin
       v_name1:=func('susu',v_name);--返回v_name值
       dbms_output.put_line(v_name1);--打印func结果
       dbms_output.put_line(v_name);--打印v_name结果
    end;

带in out 参数的函数
    create or replace function func(
       v_name in out varchar2)
    return varchar2
    is
    begin
       v_name:=v_name||' hello';
       return 'cheng';
    end;

    调用：
    declare
       v_inout varchar2(20):='world';
       v_ret varchar2(20);
    begin
       v_ret:=func(v_inout);--返回调用v_inout值(作为出参)
       dbms_output.put_line(v_ret);--打印func结果    
       dbms_output.put_line(v_inout);--返回v_name结果
    end;



    部分内容参考至CSDN：http://download.csdn.net/source/405714
    文章内容继续更新中..欢迎大家指点。
    

分析函数简述  
 

 

注：N表示数字型，C表示字符型，D表示日期型，[]表示内中参数可被忽略，fmt表示格式。

 

　　分析函数计算基于group by的列，分组查询出的行被称为"比照(window)"，在根据over()执行过程中，针对每一行都会重新定义比照。比照为"当前行(current row)"确定执行计算的行的范围。这点一定要理解清楚。它是分析函数生成数据的原理。如果此处模糊，那么你在应用分析函数时恐就不会那么得心应手了。

 

　　分析函数与前面章节中讲到的聚合函数非常相似，不同于聚合函数的地方在于它们每个分组序列均返回多行。在本节示例中会同时应用两种函数做对比，以更好体现二者的差异。通过本章节练习相信大家就会注意到，部分聚合函数和分析函数是同一个命令，事实确实如此。如果从语法格式上区分的话，没加over()的即是聚合函数，加了over()即是分析函数：）

 

　　有一点需要注意哟，除了order by子句的运算外，分析函数在SQL语句中将会最后执行。因此，分析函数只能应用于select的列或order by子句中(记住喽，千万别扔到什么where、group by、having之类的地方了)。也正因此，同名的函数在做为聚合函数和分析函数时得出的结果可能不相同，就是因为此处运算逻辑不同造成的。

 

　　同时，部分分析函数在选择列时支持distinct，如果你指定了该参数，则over条件中就只能指定partition子句，而不能再指定order by 子句了。

 

　　分析函数的语法结构比较复杂，但多数函数都具有相同的语法结构，所以先在之前进行统一介绍，后续单个函数介绍时就不过多说明函数语法结构了。

 

　　基本上所有的分析函数均是这种格式：

　　函数名称 ([参数]) OVER (analytic_clause)

analytic_clause包含：[partition 子句][ order 子句 [window子句]]

l         Partition 子句：Partition by exp1[ ,exp2]...

Partition没啥说的，功能强大参数少，主要用于分组，可以理解成select中的group by。不过它跟select语句后跟的group by 子句并不冲突。

 

l         Order子句：Order by exp1[asc|desc] [ ,exp2 [asc|desc]]... [nulls first|last]。部分函数支持window子句。

Order by的参数基本与select中的order by相同。大家按那个理解就是了。Nulls first|last是用来限定nulls在分组序列中的所在位置的，我们知道oracle中对于null的定义是未知，所以默认ordery的时候nulls总会被排在最前面。如果想控制值为null的列的话呢，nulls first|last参数就能派上用场了。

 

l         Window子句：En,贴个图吧

 

　　       看起来复杂其实简单，而且应用的机率相当的低，不详细介绍了。

 

l         AVG([DISTINCT|ALL] expr) OVER(analytic_clause) 计算平均值。

例如：

--聚合函数

SELECT col, AVG(value) FROM tmp1 GROUP BY col ORDER BY col;

--分析函数

SELECT col, AVG(value) OVER(PARTITION BY col ORDER BY col)

  FROM tmp1

 ORDER BY col;

 

l         SUM ( [ DISTINCT | ALL ] expr ) OVER ( analytic_clause )

例如：

--聚合函数

SELECT col, sum(value) FROM tmp1 GROUP BY col ORDER BY col;

--分析函数

SELECT col, sum(value) OVER(PARTITION BY col ORDER BY col)

  FROM tmp1

 ORDER BY col;

 

l         COUNT({* | [DISTINCT | ALL] expr}) OVER (analytic_clause) 查询分组序列中各组行数。

例如：

--分组查询col的数量

SELECT col,count(0) over(partition by col order by col) ct FROM tmp1;

 

l         FIRST() 从DENSE_RANK返回的集合中取出排在第一的行。


例如：

--聚合函数

SELECT col,

       MIN(value) KEEP(DENSE_RANK FIRST ORDER BY col) "Min Value",

       MAX(value) KEEP(DENSE_RANK LAST ORDER BY col) "Max Value"

  FROM tmp1

 GROUP BY col;

--分析函数

SELECT col,

       MIN(value) KEEP(DENSE_RANK FIRST ORDER BY col) OVER(PARTITION BY col),

       MAX(value) KEEP(DENSE_RANK LAST ORDER BY col) OVER(PARTITION BY col)

  FROM tmp1

 ORDER BY col;

可以看到二者结果基本相似，但是ex1的结果是group by后的列，而ex2则是每一行都有返回。

 

l         LAST()与上同，不详述。

例如：见上例。

 

l         FIRST_VALUE (col) OVER ( analytic_clause ) 返回over()条件查询出的第一条记录

例如：

insert into tmp1 values ('test6','287');

SELECT col,

       FIRST_VALUE(value) over(partition by col order by value) "First",

       LAST_VALUE(value) over(partition by col order by value) "Last"

  FROM tmp1;

 

l         LAST_VALUE (col) OVER ( analytic_clause ) 返回over()条件查询出的最后一条记录

例如：见上例。

 

l         LAG(col[,n][,n]) over([partition_clause] order_by_clause) lag是一个相当有意思的函数，其功能是返回指定列col前n1行的值(如果前n1行已经超出比照范围，则返回n2，如不指定n2则默认返回null)，如不指定n1，其默认值为1。

例如：

SELECT col,

       value,

       LAG(value) over(order by value) "Lag",

       LEAD(value) over(order by value) "Lead"

  FROM tmp1;

 

l         LEAD(col[,n][,n]) over([partition_clause] order_by_clause) 与上函数正好相反，本函数返回指定列col后n1行的值。

例如：见上例

 

l         MAX (col) OVER (analytic_clause) 获取分组序列中的最大值。

例如：

--聚合函数

SELECT col,

       Max(value) "Max",

       Min(value) "Min"

  FROM tmp1

 GROUP BY col;

--分析函数

SELECT col,

       value,

       Max(value) over(partition by col order by value) "Max",

       Min(value) over(partition by col order by value) "Min"

  FROM tmp1;

l         MIN (col) OVER (analytic_clause) 获取分组序列中的最小值。

例如：见上例。

 

l         RANK() OVER([partition_clause] order_by_clause) 关于RANK和DENSE_RANK前面聚合函数处介绍过了，这里不废话不，大概直接看示例吧。

例如：

insert into tmp1 values ('test2',120);

SELECT col,

       value,

       RANK() OVER(order by value) "RANK",

       DENSE_RANK() OVER(order by value) "DENSE_RANK",

       ROW_NUMBER() OVER(order by value) "ROW_NUMBER"

  FROM tmp1;

 

l         DENSE_RANK () OVER([partition_clause] order_by_clause)

例如：见上例。

 

l         ROW_NUMBER () OVER([partition_clause] order_by_clause) 这个函数需要多说两句，通过上述的对比相信大家应该已经能够看出些端倪。前面讲过，dense_rank在做排序时如果遇到列有重复值，则重复值所在行的序列值相同，而其后的序列值依旧递增，rank则是重复值所在行的序列值相同，但其后的序列值从+重复行数开始递增，而row_number则不管是否有重复行，(分组内)序列值始终递增

例如：见上例。

 

 

l         CUME_DIST() OVER([partition_clause] order_by_clause) 返回该行在分组序列中的相对位置，返回值介于0到1之间。注意哟，如果order by的列是desc，则该分组内最大的行返回列值1，如果order by为asc，则该分组内最小的行返回列值1。

例如：SELECT col, value, CUME_DIST() OVER(ORDER BY value DESC) FROM tmp1;

 

l         NTILE(n) OVER([partition_clause] order_by_clause) 

ntile是个很有意思的统计函数。它会按照你指定的组数(n)对记录做分组

例如：SELECT t.*,ntile(5) over(order by value desc) FROM tmp1 t;

 

l         PERCENT_RANK() OVER([partition_clause] order_by_clause) 与CUME_DIST类似，本函数返回分组序列中各行在分组序列的相对位置。其返回值也是介于0到1之间，不过其起始值始终为0而终结值始终为1。

例如：SELECT col, value, PERCENT_RANK() OVER(ORDER BY value) FROM tmp1;

 

l         PERCENTILE_CONT(n) WITHIN GROUP (ORDER BY col [DESC|ASC]) OVER(partition_clause)

本函数功能与前面聚合函数处介绍的完全相同，只是一个是聚合函数，一个是分析函数。

例如：

--聚合函数

SELECT col, max(value), min(value), sum(value),

       PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY value) a,

       PERCENTILE_CONT(0.8) WITHIN GROUP(ORDER BY value) b

  FROM TMP1

 group by col;

--分析函数

SELECT col,

       value,

       sum(value) over(partition by col) "Sum",

       PERCENTILE_CONT(0.5) WITHIN GROUP( ORDER BY value) OVER(PARTITION BY col) "CONTa",

       PERCENTILE_CONT(0.8) WITHIN GROUP( ORDER BY value) OVER(PARTITION BY col) "CONTb"

  FROM TMP1;

 

l         PERCENTILE_DISC(n) WITHIN GROUP (ORDER BY col [DESC|ASC]) OVER(partition_clause)

本函数功能与前面聚合函数处介绍的完全相同，只是一个是聚合函数，一个是分析函数。

例如：

--聚合函数

SELECT col, max(value), min(value), sum(value),

       PERCENTILE_DISC(0.5) WITHIN GROUP(ORDER BY value) a,

       PERCENTILE_DISC(0.8) WITHIN GROUP(ORDER BY value) b

  FROM TMP1

 group by col;

--分析函数

SELECT col,

       value,

       sum(value) over(partition by col) "Sum",

       PERCENTILE_DISC(0.5) WITHIN GROUP( ORDER BY value) OVER(PARTITION BY col) "CONTa",

       PERCENTILE_DISC(0.8) WITHIN GROUP( ORDER BY value) OVER(PARTITION BY col) "CONTb"

  FROM TMP1;

 

l         RATIO_TO_REPORT(col) over ([partition_clause]) 本函数计算本行col列值在该分组序列sum(col)中所占比率。如果col列为空，则返回空值。

例如：

SELECT col, value,

       RATIO_TO_REPORT(value) OVER(PARTITION BY col) "RATIO_TO_REPORT"

  FROM TMP1

 

l         STDDEV ([distinct|all] col) OVER (analytic_clause) 返回列的标准偏差。

例如：

--聚合函数

SELECT col, STDDEV(value) FROM TMP1 GROUP BY col;

--分析函数

SELECT col, value,

       STDDEV(value) OVER(PARTITION BY col ORDER BY value) "STDDEV"

  FROM TMP1;

 

l         STDDEV_SAMP(col) OVER (analytic_clause) 功能与上相同，与STDDEV不同地方在于如果该分组序列只有一行的话，则STDDEV_SAMP函数返回空值，而STDDEV则返回0。

例如：

--聚合函数

SELECT col, STDDEV(value),STDDEV_SAMP(value) FROM TMP1 GROUP BY col;

--分析函数

SELECT col, value,

       STDDEV(value) OVER(PARTITION BY col ORDER BY value) "STDDEV",

       STDDEV_SAMP(value) OVER(PARTITION BY col ORDER BY value) "STDDEV_SAMP"

  FROM TMP1;

 

l         STDDEV_POP(col) OVER (analytic_clause) 返回该分组序列总体标准偏差

例如：

--聚合函数

SELECT col, STDDEV_POP(value) FROM TMP1 GROUP BY col;

--分析函数

SELECT col, value,

       STDDEV_POP(value) OVER(PARTITION BY col ORDER BY value) "STDDEV_POP"

  FROM TMP1;

 

l         VAR_POP(col) OVER (analytic_clause) 返回分组序列的总体方差，VAR_POP进行如下计算：(SUM(expr2) - SUM(expr)2 / COUNT(expr)) / COUNT(expr)

例如：

--聚合函数

SELECT col, VAR_POP(value) FROM TMP1 GROUP BY col;

--分析函数

SELECT col, value,

       VAR_POP(value) OVER(PARTITION BY col ORDER BY value) "VAR_POP"

  FROM TMP1;

 

l         VAR_SAMP(col) OVER (analytic_clause) 与上类似，该函数返回分组序列的样本方差，，其计算公式为：(SUM(expr2) - SUM(expr)2 / COUNT(expr)) / (COUNT(expr) - 1)

例如：

--聚合函数

SELECT col, VAR_SAMP(value) FROM TMP1 GROUP BY col;

--分析函数

SELECT col, value,

       VAR_SAMP(value) OVER(PARTITION BY col ORDER BY value) "VAR_SAMP"

  FROM TMP1;

 

l         VARIANCE(col) OVER (analytic_clause) 该函数返回分组序列方差，Oracle计算该变量如下：

如果表达式中行数为1，则返回0，如果表达式中行数大于1，则返回VAR_SAMP

例如：

--聚合函数

SELECT col, VAR_SAMP(value),VARIANCE(value) FROM TMP1 GROUP BY col;

--分析函数

SELECT col, value,

       VAR_SAMP(value) OVER(PARTITION BY col ORDER BY value) "VAR_SAMP",

       VARIANCE(value) OVER(PARTITION BY col ORDER BY value) "VARIANCE"

  FROM TMP1;