plsql笔记

----- 添加20条数据
---------------------------------------------------------------------------

-- for

declare
       V_START NUMBER := 1;
begin
    FOR V_START IN 1..20 LOOP
        INSERT INTO BONUS VALUES('KING'||V_START,'JAVA'||V_START
              ,to_number('300'||V_START),to_number('100'||V_START));
    END LOOP;
    --COMMIT;
end;

---------------------------------------------------------------------------

 

-- exit when

declare
       V_START NUMBER := 1;
       V_END CONSTANT NUMBER :=20;
begin
     LOOP
       INSERT INTO BONUS VALUES('KING'||V_START,'JAVA'||V_START
                ,to_number('300'||V_START),to_number('100'||V_START));
       V_START := V_START+1;
       EXIT WHEN V_START > V_END;
     END LOOP;
end;

---------------------------------------------------------------------------

 

-- while

declare
       V_START NUMBER := 1;
       V_END CONSTANT NUMBER :=20;
begin
     WHILE V_START <= V_END LOOP
       INSERT INTO BONUS VALUES('KING'||V_START,'JAVA'||V_START
                ,to_number('300'||V_START),to_number('100'||V_START));
       V_START := V_START+1;
     END LOOP;
end;

 

---------------------------------------------------------------------------

 

 

v_id stu.id%type;
myrecord mycur%rowtype;

--//初始化
v_num integer :=1;
v_name varchar :='name';
v_name stu.name%type default 'this is ';

 

 

---------------------------------------------------------------------------

----- 使用游标

declare
v_id number;
v_name varchar(20);
cursor c_stu is select id,name from stu ;
begin
     open c_stu; --1
     loop  -- 2
          fetch c_stu into v_id,v_name;
          dbms_output.put_line('id is '||v_id||' name is '||v_name);
          exit when c_stu%notfound;
     end loop; -- 2
     close c_stu; --1
end;

 

 

------ 使用全表

declare
v_id number;
v_name varchar(20);
cursor m_stu is select * from stu ;
stu1 m_stu%rowtype;
begin
    open m_stu;
         loop fetch m_stu into stu1;
         dbms_output.put_line('id is '||stu1.id||' name is '||stu1.name);
         exit when m_stu%notfound;
         end loop;
    close m_stu;
end;

 

----- 隐式游标
declare
cursor cur_stu is select * from stu;
begin
     for mystu in cur_stu loop
         dbms_output.put_line('id is '||mystu.id||' name is '||mystu.name);
     end loop;
end;

 

 

----- 隐式游标(有参数)
declare
cursor cur_stu(p_math number) is select * from stu where math=p_math;
begin
     for mystu in cur_stu(70) loop
         dbms_output.put_line('id is '||mystu.id||' name is '||mystu.name);
     end loop;
end;

 

---------------------------------------------------------------------------

 

----- 一个简单的存储过程 p_size要插入几条记录
create or replace procedure insertStu(p_size in integer) is
v_start integer:=1;
begin
  for v_start in 1..p_size loop
  insert into stu values(T_STU_SEQ.NEXTVAL,'name'||v_start);
  commit;
  end loop;
end insertStu;

 

 

----- 流程控制

create or replace procedure guessNum(p_guessNum in integer) is
v_resultNum number :=50;
begin
  dbms_output.put_line('programmer start');
  if(p_guessNum<0) then
      dbms_output.put_line('猜的数不能为负数!');
  elsif((p_guessNum<v_resultNum)and(p_guessNum>=0)) then 
     dbms_output.put_line('猜的数, 小了!');
  elsif(p_guessNum>v_resultNum) then
     dbms_output.put_line('猜的数, 大了!');
  else
     dbms_output.put_line('恭喜你，你猜对了!');
  end if;
     dbms_output.put_line('programmer end');
end guessNum;

 

 

if(true)语句 else 语句 end if;    

if else end if;
if.....then
if.....then else
if.....then elsif

 

 

------------------------------------------------------------------------

 

-----  一个简单的循环  exit when
declare
v_num number :=1;
begin
  loop
   dbms_output.put_line('this number is '||v_num);
   v_num:=v_num+1;
      exit when v_num>20;
  end loop;
end;

 

-----  跟上面的一样的 while
declare
v_first number:=1;
v_num number :=1;
begin
    while v_num<=20 loop
      dbms_output.put_line('this number is '||v_num);
      v_num:=v_num+1;
    end loop;
end;

 

-----  for in
declare
v_num number :=1;
begin
    for v_num in 1..10 loop
      dbms_output.put_line('this number is '||v_num);
    end loop;
end;

------------------------------------------------------------------------