PL/sql 学习笔记备忘一

 

PL/sql
1.	PL/SQL 概述
2.	PL/SQL 基础
3.	流程控制
4.	复合数据类型
5.	游标
6.	异常
7.	子程序与触发器
8.	系统包
9.	Java调用储存过程/Java编写存储过程

第七课

Declare
定义 常量 变量 复合数据类型 游标

Begin
	执行部分
	Sql
	为变量赋值
	Plsql语句和sql语句

End;
declare
  a varchar2(100);
  b number(9):=3;
  c varchar2(100);
begin
  select ename into c from emp where empno=7369;
  
  a:='good';
  dbms_output.put_line(a);
  dbms_output.put_line(b);
  dbms_output.put_line('c:='||c);
  dbms_output.put_line('hello world!');
end;


--变量常量
name[CONSTRANT] datatype[NOT NULL]:=|default value;

--作用域


--块

--匿名/命名
--子程序：存储过程 函数
--触发器

date number char varchar2 lob 

number(n,m)  1234.56
char(n)
varchar2()

BOOLEAN true false null

date

timestamp

declare
   sal number(6,2);
begin
   select sal into sal from emp where lower(ename)=lower('&ename');
   dbms_output.put_line(sal);
end;

declare
   sal number(6,2);
begin
   select sal into sal from emp where lower(ename)=lower('&ename');
   if sal<2000 then
     dbms_output.put_line('less 2000');
   end if;
end;

declare
   sal number(6,2);
begin
   select sal into sal from emp where lower(ename)=lower('&ename');
   if sal<2000 then
     dbms_output.put_line('less 2000');
     else
       dbms_output.put_line('more 2000');
   end if;
end;

declare
   sal number(6,2);
begin
   select sal into sal from emp where lower(ename)=lower('&ename');
   if sal<2000 then
     dbms_output.put_line('less 2000');
     elsif sal>2000 and sal<4000 then
       dbms_output.put_line('between 2000 and 4000');
     elsif sal>4000 then
       dbms_output.put_line('more 4000');
       else
         dbms_output.put_line('other...');
   end if;
end;


case
  
case selector
     when expression1 then ...;
     when expression2 then ...;
     when exn         then ...;
     else ...;
end case;

declare
    a number(2):=0;
begin
    a:=&a;
    case a
      when 10 then
        dbms_output.put_line('aaaaa');
      when 20 then
        dbms_output.put_line('bbbbbb');
       else
         dbms_output.put_line('other');
     end case;
end;


LOOP
  statement;
  ..
  ..
  ..
  exit [when condition]  
end loop;

declare
    q number :=0;   
begin
  loop
    q:=q+1;
    dbms_output.put_line('q='||q);
    exit when q=10;
  end loop;
end;



declare
    q number :=0;   
begin
  loop
    q:=q+1;
    dbms_output.put_line('q='||q);
    
    if q=20 then
      exit;
    end if;
  end loop;
end;


while condition loop
  ...;
  ...;
end loop;


declare
    q number :=0;
begin
    while(q<10) loop
    q:=q+1;
    dbms_output.put_line('q='||q);
    end loop;
end;

for counter in lowerbound..upbound loop
  ...
  ...
end loop;

declare
    
begin
  for i in 1..10 loop
    dbms_output.put_line('i='||i);
    end loop;
end;

declare
    
begin
  for i in reverse 1..10 loop
    dbms_output.put_line('i='||i);
    end loop;
end;

declare 
  sal number(6,2);
begin
  select sal into sal from emp where empno = 7369;
  if sal <3500 then
    goto a;
  else
    goto b;
   end if;
   
   <<a>>
   dbms_output.put_line('aa');
   <<b>>
   null;
end;

--新建一张表，和scott.emp表一致。
然后查询平均工资
create table myemp as select * from emp;


select * from myemp

select avg(sal) from emp;
--查询得到平均工资是2073


--要求平均工资超过2500
--每个人工资涨100，直到平均工资超过2500，那么就不涨工资了。

declare
  avgsal number(6,2);  
begin
  select avg(sal)into avgsal from  myemp;
  while(avgsal<2500) loop
  update myemp set sal=sal+100;
  select avg(sal)into avgsal from  myemp;
  end loop;
end;

select avg(sal) from myemp;

第八课

%type
%rowtype
pls_integer
binary_integer


declare
  v_ename emp.empno%type;
  v_sal emp.sal%type;
  v_emp emp%rowtype;
begin
 -- select ename into v_ename from emp where empno=7369;
 -- select sal into v_sal from emp where empno=7369;
 select * into v_emp from emp where empno = 7369;
  --dbms_output.put_line(v_ename||' '||v_sal);
  dbms_output.put_line(v_emp.ename);
  dbms_output.put_line(v_emp.sal);
  dbms_output.put_line(v_emp.deptno);
end;

declare  
  v_ename emp.ename%type;  
  v_sal emp.sal%type;  

begin  
 select ename,sal into v_ename,v_sal from emp where empno=7369;  
--select sal into v_sal from emp where empno=7369;  

dbms_output.put_line(v_ename||' '||v_sal);  

end;  

record
--定义数据记录的类型


declare
  type emp_record  is record(
       v_ename emp.ename%type,
       v_sal   emp.sal%type,
       v_deptno emp.deptno%type
  );
  v_ename_record emp_record;
begin
  select ename,sal,deptno into v_ename_record from emp where empno=7369;
  v_ename_record.v_ename:='zhangsan';
    dbms_output.put_line(v_ename_record.v_ename);
  dbms_output.put_line(v_ename_record.v_sal);
  dbms_output.put_line(v_ename_record.v_deptno);
end;

--集合容器
index_by table ,索引表，
嵌套表
varray

当行当列   变量varcher2 %type
当行多列   Record
当列多行   集合(%type)
多行多列   集合(%rowtype)


索引表
typw name IS TABLE OF element_type index by  key_type;


declare
     type num_array is table of number(5) index by binary_integer;
     a num_array;
begin
  for i in 1..10 loop
    a(i):=i;
  end loop;     
  
  for i in 1..10 loop
    dbms_output.put_line(a(i));
  end loop;
end;


declare
  type emp_array is table of emp%rowtype index by binary_integer;
  a emp_array;
begin
  select * bulk collect into a from emp;
  for i in a.first..a.last loop
      dbms_output.put_line(a(i).ename||' '||a(i).job);
  end loop;
end;

--record 嵌套Record

declare 
         type record1 is record(
              vename emp.ename%type,
              vempno emp.empno%type,
              vsal   emp.sal%type
         
         );
         type record2 is record(
              v1 number(5),
              v2 varchar2(20),
              v3 record1
         );
         type record1_array is table of record1 index by binary_integer;
         a record1;
         b record2;
         c record1_array;
         
begin
         select ename,empno,sal into a from emp where empno=7369;
         b.v3:=a;
          dbms_output.put_line(b.v3.vename);
          dbms_output.put_line(b.v3.vempno);
          dbms_output.put_line(b.v3.vsal);
end;

declare
    type yy is table of varchar2(20) index by varchar2(20);
    a yy;
begin
    a('beijing'):='china';
    a('dongjing'):='japan';
    a('huashengduan'):='usa';
    dbms_output.put_line(a('beijing'));
    
end;