oracle pl/sql 编程

                                              第一部分  基本概念

一、查询系统表
select * from user_tables  查询当前用户所有表
select * from user_triggers 查询当前用户所有触发器
select * from user_procedures 查询当前用户所有存储过程

二、分组函数的用法
max()
min()
avg()
sum()
count()
rollup()
注意：
  1)分组函数只能出现在 group by ,having,order by 子句中，并且order by 只能放在最后
  2)如果选择列表中有列，表达式和分组函数，那么列，表达式必须要出现在group by 函数中
  3)当限制分组显示结果时，必须出现在having子句中，而不能在where子句中
rollup 函数  函数用于形成小合计
Group by 只会生成列相应数据统计
select deptno,job,avg(sal) from emp group by deptno,job;
rollup 会在原来的统计基础上，生成小统计
select deptno,job,avg(sal) from emp group by rollup (deptno,job);
显示每个岗位的平均工资，每个部门的平均工资，和所有雇员的平均工资

cube 提供按多个字段汇总的功能
显示每个部门每个岗位的平均工资，每个岗位的平均工资，每个部门的平均工资，和所有雇员的平均工资
select deptno,job,avg(sal) from emp group by cube (deptno,job);

多种分组数据结果 grouping sets 操作
显示部门平均工资
select deptno,avg(sal) from emp group by emp.deptno,
显示岗位平均工资
select job,avg(sal) from emp group by emp.job
显示部门和岗位平均工资，合并上面两个结果
select deptno,job,avg(sal) from emp group by grouping sets(emp.deptno,emp.job);
等价与:
select deptno,null,avg(sal) from emp group by emp.deptno
union all
select null,job,avg(sal) from emp group by emp.job

三 (+) 连接
+ 只能用在where子句中，而且放在显示较少的行那一边，不能和outer join语法同时使用
+ 如果在where 子句中有多个条件，每个条件都必须加上+
+ 不能用于表达式，只能用于列，不能和in or一起使用。

四 字符函数
instr() substr()  ltrim()
lower('SQL server') sql server  都转换为小写字符
upper('morning')   MORNING      都转换为大写字符
select instr('monring','ng') from dual; 子字符串在字符串中的位置 返回6
ltrim() 从左端扫描去掉出现字符set的字符，直到遇到不是set的值返回
select ltrim('Mmoning' 'M') from dual;
返回moning
substr('monring',1,3) 取到子字符串,第二个参数为起始位置，3为长度  结果返回mon

                             第二部分  pl/sql 基础

一、pl/sql 数据类型
boolean 类型定义，
v_bflag boolean not null default false;
常量定义
v_tax_rate constant number(3,2):=0.03
定义数组
type arrays is table of varchar2(200);
使用 %type类型
使用这个属性时候，它会按照数据库列或其它变量来确定新变量的类型和长度
v_name emp.name%type

1)单行多列 使用pl/sql 记录 
  a.自定义
  type emp_record_type is RECORD(
      name emp.name%TYPE,--定义成emp表中name的数据类型
      sal   emp.sal ,
   );
   emp_record emp_record_type;
   使用：
   emp_record.name
   b.定义行记录
  dept_record dept%ROWTYPE
  

2)为了处理多行单列，多行多列使用pl/sql集合如： 索引表，嵌套表，varray
     a) pl/sql表(索引表)。只能用在pl/sql中同种类型的一维、无边界的同类元素稀疏集合，且只能是由一列组成。
     定义：type ename_table_type is table of varchar2(200) index by binary_integer;
          或者type ename_table_type is table of emp.ename%type index by binary_integer;
     声明：ename_table ename_table_type;
           select ename into arrays(-1) from emp where empno=7788 把员工姓名放入 下标为-1的空间。
     b) 嵌套表 也是一维、无边界稀疏集合
     定义：type ename_table_type is table of varchar2(200);
     或者type arrays is table of emp%name;
     嵌套表的下标从1开始，也是属于稀疏集合，和索引表的区别除了开始下标以为，而且嵌套表是可以做为 可以做为表列的数据类型，
     而索引表不可以。并且必须使用初始化构造函数初始化
     emp_array ename_table_type=:ename_table_type();//构造方法初始化
     eg:
       type emp_table_type is table of emp.name%type;
       emp_table emp_table_type:=emp_table_type();
       select ename into emp_table(2) from emp where empno='1779';
       dbms_output.putLine(emp_table(2));
     结果：scott
   
--定义数组类型
type arrays is table of varchar2(200);
--初始化
dataList    arrays := arrays();
--作为参数传递
procedure pro_name(dataList is arrays) is
begin

end ;

/*
用指定字符将字符串分隔为数组
str 字符串
str_split 分割字符
return 分割后的数组
*/
function splitString(sourceString varchar2, splitFlag varchar2)
return arrays is
dataList    arrays := arrays(); --一定要，构造函数初始化
temp        varchar2(200); --存储临时数据
tempSource  varchar2(1000) := sourceString; --存储需要分割的字符串()
splitIndex  number(2); --分隔符在字符串中首次出现的位置
splitLength number(2) := length(splitFlag); --分割符长度
begin
while instr(tempSource, splitFlag) > 0 loop
  splitIndex := instr(tempSource, splitFlag);
  --取出第一个分割符前的字符串
  temp := substr(tempSource, 1, splitIndex - splitLength);
  --减去第一个分隔符前的字符串
  tempSource := substr(tempSource,
                       splitIndex + splitLength,
                       length(tempSource));
  if temp is not null then
    dataList.extend(1);
    dataList(dataList.count) := temp;
  end if;
end loop;
if tempSource is not null then
  dataList.extend(1);
  dataList(dataList.count) := tempSource;
end if;
return dataList;
end splitString;

     c) varray 变长数组 做为表列的数据类型，并且必须指定数组的大小
     TYPE t_name IS VARRAY(2) OF VARCHAR2 (10); 定义了一个字符类型的数组，并且只能存储两个元素
     存储对象类型：
     create type  article_type as OBJETC(
      title varchar2(20),
      pubDate DATE
     )
     create type article_array is varray(2) of article_type
     可以当作用户自定义数据类型来引用：
     create table author(
      id number,
      name varchar2,
      arti article
     )
    
    这些集合内置的一些方法：
    COUNT  返回集合中元素的个数
    DELETE 删除集合中所有元素
    DELETE(x) 删除元素下标为x的元素  对VARRAY非法
    DELETE(x,y) 删除元素下标从X到Y的元素  对VARRAY非法
    EXIST(x)  如果集合元素x已经初始化，则返回TRUE, 否则返回FALSE
    EXTEND  在集合末尾添加一个元素  对Index_by非法
    EXTEND(x)  在集合末尾添加x个元素  对Index_by非法
    EXTEND(x,n)  在集合末尾添加元素n的x个副本 对Index_by非法
    FIRST  返回集合中的第一个元素的下标号，对于VARRAY集合始终返回1。
    LAST  返回集合中最后一个元素的下标号, 对于VARRAY返回值始终等于COUNT.

   3) 为了处理多行多列 pl/sql 记录表
   type emp_table_type is table(
   
   )
  
二、控制结构
    分支，循环，顺序
    if   then
       statement;
    else
       statement;
    end if;
   
    case 在多重分支应用
    case op
      when exp1 then;
      when exp2 then;
    
     循环
     基本循环至少执行一次
     loop
        statement;
        exit(when condition)  condition为true 退出循环
     end loop
    
     只有条件为真时才执行循环体
     while condition loop
       statement
     end loop;
   
    for int i in 1..N loop --循环变量由oracle隐式定义，不需要显示定义
       statement
    end loop;
   
三、游标
(1) 游标类型分为静态游标和动态游标
1)静态游标
    静态游标又分为隐式和显示两种
    a、隐式游标(开发人员没有为sql语句显示声明一个游标)是当你在执行DML操作返回单行记录时(update insert delete)
       会自动声明一个隐式游标。
  隐式游标属性:
     SQL%found, sql语句执行是否成功，如果作用行则为true,如果没有则为false
     SQL%notfound,
     SQL%rowcount sql语句作用的总行数。
     SQL%isopen
    b、显示游标 返回多行。开发中一般都用显示游标。
带参数的显式游标
cursor name_cur(com_id in number)
is select name from company where company_id = com_id;
使用
for temp_cur in name_cur(0) loop
end loop;
带返回值的显式游标
cursor emp_cur return employee%rowType --游标声明
is select * from employee              --游标体
where department_id =10

2)动态游标
(1)动态游标例子
strSql:=' select tvi.gap ';
strSql:=strSql||' from TEMP_VD_RP_AJUST tvi  ';
strSql:=strSql||' where tvi.week in('||nWeeks||')';
strSql:=strSql||' group by tvi.ship_to_code, tvi.product_name, tvi.week, tvi.gap ';
  open  gapCur for strSql;
  loop
      fetch gapCur into gap;
      exit when gapCur%notfound; --没有记录退出
      if(gap<0) then
         bFlag := true;
         exit;  --只要存在一个为负值，退出
      end if;
   end loop;
close gapCur;

(2) 使用游标更改数据
   使用显示游标在更改当前游标行的时候必须带for update
   cursor cur_name is select * from emp for update(of column)
   在插入或者删除子句中必须加where current of cur_name;
   eg: 给工资少于2000的员工加100工资
   cursor emp_cur is select emp.name,emp.sal form emp for update;
   for temp_emp fro emp_cur loop;
      if(emp.sal<2000) then
      update emp set emp.sal=sal+100  where current of emp_cur;
      end if;
   end loop;
   
(3) 游标变量
声明一个引用数据库中游标对象的变量，该变量可以在不同时间指向不同的sql语句，
游标变量是指向多行查游标询的结果集的当前行,游标是静态的,游标变量是动态的
游标变量并不参与与特定的查询绑定,所以可以为任何兼容的查询打开游标变量
还可以将新的值赋予游标变量 , 将它作为参数传递给本地和存储过程。
注 : 游标总是指向相同的查询工作区 , 游标变量能够指向不同的工作区 , 因此游标和游标变量不能互操作
type cur_type is ref cursor return emp%rowType;
emp_cur = cur_type;
open emp_cur for select * from emp;
loop
fetch emp_cur into v_1,v_2;
end loop;
close emp_cur;
emp_cur 可以为不同的sql查询，每次都执行查询结果集合的第一行。
注意 :
REF CURSOR 类型既可以是强类型 , 也可以是弱类型 , 区别是强类型有返回类型 , 弱类型没有
当作为函数参数传递
PROCEDURE open_emp (emp_cur  IN OUT cur_type)IS
begin
open emp_cur for select * from emp;
end

    定义游标变量的例子：
    type rc is ref cursor;
cursor c is select * from dual;
l_cursor rc;
begin
if ( to_char(sysdate,'dd') = 30 ) then
       open l_cursor for 'select * from emp';
elsif ( to_char(sysdate,'dd') = 29 ) then
       open l_cursor for select * from dept;
else
       open l_cursor for select * from dual;
end if;
open c;
end;

rc根据逻辑动态打开；而游标c定义好了只有就无法修改了。
ref cursor可以返回给客户端，cursor则不行。
cursor可以是全局的global ，ref cursor则必须定义在过程或函数中。
ref cursor可以在子程序间传递，cursor则不行。
cursor中定义的静态sql比ref cursor效率高，所以ref cursor通常用在向客户端返回结果集。

(4) 定义游标
显示游标 返回多行。开发中一般都用显示游标
   提取游标数据
   fetch emp_cur into variable1,variable2
   提取所有数据
   fetch..bulk collect into
   eg:
   type emp_table is table of varchar2(10)
   fetch emp_cur bulk collect into emp_table
   for i in 1.. emp_table.count loop
       dbms_output.putLine(emp_table(i));
   end loop;
   使用游标属性
   emp_cur%isopen,
   emp_cur%found, --是否从结果集中提取到了数据
   emp_cur%notfound
   emp_cur%rowcount  --当前已经提取到的实际行数
   隐式游标,自动做了打开关闭
   type cursor emp_cur is select * from emp_cur;
   emp_record dept%ROWTYPE
   for emp_record  in emp_cur loop
   dbms_output.putLine(emp_record.name);
  end loop;

四、动态sql
  注意：静态sql不能处理DDL操作

(1) using 子句 默认的绑定参数类型是in
可以使用参数 using param1,param2
  procedure execImmediate is
strSql varchar2(200);
begin
strSql:='select count(*)  from emp t where t.ename = :1 ';
execute immediate strSql using 'SCOTT';
end execImmediate;

(2)into 子句，单列,注意：into 子句用在using 子句之前
    procedure execImmediate is
strSql varchar2(200);
num number;
begin
strSql:='select count(*)  from emp t where t.ename = :1 ';
execute immediate strSql into num using 'SCOTT' ;
end execImmediate;

(3)into 子句，多列
procedure execImmediate is
strSql varchar2(200);
v_job varchar2(200);
v_sal number;
begin
strSql:='select t.job,t.sal from emp t where t.ename = :1 ';
execute immediate strSql into v_job,v_sal using 'SCOTT' ;
end execImmediate;

(4) returning 子句，只能是单行上的DML操作，多行则需要使用BULK
procedure execImmediate is
   v_name varchar2(10);
   v_sal number;
begin
execute immediate 'update emp set sal=3000 where empno=:1 returning  ename,sal into :v_name,:v_sal'
using in '7876'
returning into v_name,v_sal;
end execImmediate;