plsql_入门基础

------goto 语句------------------
declare
  v_num emp.empno%type;
begin
  v_num :=0;
  while v_num <=10 loop
    dbms_output.put_line(v_num);
    v_num := v_num+1;
    if v_num = 5 then
      goto a;
    end if;
  end loop;
 
  <<a>>
  dbms_output.put_line('goto a');
  return;
  <<b>>
  dbms_output.put_line('goto b');
end;
---------循环  for语句------------
declare
  v_num number(12,2);
begin
  for v_num in 1.. 100 loop
--for v_num in reverse 1.. 100 loop
    dbms_output.put_line(v_num);
    exit when v_num = 50;
  end loop;
end;
---------循环 while语句------------
declare
  v_num number(12,2);
begin
  v_num :=0;
  while v_num <=100 loop
     dbms_output.put_line(v_num);
     v_num :=v_num+1;
     --exit when v_num = 50;
     if v_num = 50 then
       exit;
     end if;
  end loop;
end;
---------循环 loop语句--------------
declare
 v_no emp.empno%type;
begin
 v_no :=0;
 loop
   v_no := v_no+1;
   dbms_output.put_line(v_no);
   exit when v_no >100;
 end loop;
end;
-------条件判断 case语句------------
declare
  v_sal emp.sal%type;
begin
  v_sal :=5;
  case v_sal
    when 1 then
      dbms_output.put_line('1');
    when 2 then
      dbms_output.put_line('2');
    when 3 then
      dbms_output.put_line('3');
    else
      dbms_output.put_line('5');
   end case;
end;
-------条件判断 if语句------------
declare
  v_sal emp.sal%type;
begin
  select sal into v_sal from emp where empno='333';
  if v_sal >= 5000 then
    dbms_output.put_line('OKOK');
  elsif v_sal >= 4000 then
    dbms_output.put_line('OK'); 
  else
    dbms_output.put_line('NO'); 
  end if;
end;
---------插入值------------
declare
  v_name varchar2(20) :='xieyongqing';
  v_job varchar2(20)  :='programmer';
  v_sal number(8,2) :=4000;
begin
  insert into emp(ename,job,sal) values(v_name,v_job,v_sal);
  commit;
end;
---------查询整条记录并赋值 表一行记录-------------------------------------------
declare
   emp_rec emp%rowtype;
begin
  select * into emp_rec from emp where empno=222 ;
  dbms_output.put_line(emp_rec.ename|| ' sal is ' ||emp_rec.sal);
end;
---------查询某个字段并赋值 字段-----------------------------------------
declare
  v_name emp.ename%type;
  v_sal  emp.sal%type;
begin
  select ename,sal into v_name,v_sal from emp where empno=222;
  dbms_output.put_line(v_name|| ' sal is ' ||v_sal);
end;
----------------申明与已有表类型的变量------------------------------
declare
  myrec emp%rowtype;
begin
  myrec.ename :='xie';
  dbms_output.put_line(myrec.ename);
end;
----------------申明与已有表字段类型的变量----------------------
declare
  type rec is record(
       v_username emp.ename%type,
       v_sal      emp.sal%type
  );
myrec rec;
begin
  myrec.v_sal :=123.22;
  myrec.v_username :='yongqing';
  dbms_output.put_line(myrec.v_username||' sal is '||myrec.v_sal);
end;
---------------封装类型---------------
declare
  type rec is record(
       v_username varchar2(20),
       v_password varchar2(20),
       v_age      number(4,2)
  );
  myrec rec;
begin
  myrec.v_username :='taijizhangsanfeng';
  dbms_output.put_line(myrec.v_username);
end;