plsql编程语法

--普通变量
/*
declare
  u_name varchar2(20):='zhang san';
begin
  dbms_output.put_line(u_name);
end;
*/
--获取用户输入
/*
declare
  u_name varchar2(20):='&aaa';
begin
  dbms_output.put_line('hello '||u_name);
end;
*/
--根据用户输入的年龄判断
/*
declare
  u_age number :=&age;
  msg varchar2(100);
begin
   if u_age<18 then
     msg:='年龄太小';
   elsif u_age <30 then
     msg:='您可以注册';
   else
     msg:='年龄太大';
   end if;
   dbms_output.put_line(msg);
end;
*/
--判断范围
/*
declare
  u_age number :=&age;
  msg varchar2(100);
begin
   case
     when u_age<18 then msg:='太小';
     when u_age<50 then msg:='正好';
     else
       msg:='太大';
    end case;
   dbms_output.put_line(msg);
end;
*/
/*
declare
  u_age number :=&age;
  msg varchar2(100);
begin
   case u_age
     when 18 then msg:='太小';
     when 50 then msg:='正好';
     else
       msg:='太大';
    end case;
   dbms_output.put_line(msg);
end;
*/
--根据用户输入数字，循环输出
/*
declare
   u_num number:=&nums;
   i number:=0;
begin
  loop
    dbms_output.put_line(i);
    i:=i+1;
    if i>u_num then
      exit;
    end if;
  end loop;
end;
*/
/*
declare
   u_num number:=&nums;
   i number:=0;
begin
  while i<u_num loop
    dbms_output.put_line(i);
    i:=i+1;
  end loop;
end;
*/
/*
declare
   u_num number:=&nums;
begin
  for i in 0..u_num loop
    dbms_output.put_line(i);
  end loop;
end;
*/
--游标
/*
select * from emp;

declare
      ename varchar2(10);
      job varchar2(10);
      cursor cur_emp is
            select ename,job from emp where empno=7369;
begin
  open cur_emp;
  fetch cur_emp into ename,job;
  if cur_emp%notfound then
    dbms_output.put_line('没有数据');
  else
    dbms_output.put_line(ename||'====='||job);
  end if;
end;          
*/
--loop
declare
  ename varchar2(10);
  job varchar2(10);
  cursor cur_emp is
         select ename,job from emp
                where sal>2000;
begin
  open cur_emp;
  loop
    fetch cur_emp into ename,job;
    if cur_emp%notfound then
      exit;
    end if;
    dbms_output.put_line(ename||'  '||job);
  end loop;
  close cur_emp;
end;
---while
declare
  ename varchar2(10);
  job varchar2(10);
  cursor cur_emp is
         select ename,job from emp
                where sal>2000;
begin
  open cur_emp;
  fetch cur_emp into ename,job;
  while cur_emp%fount loop
    dbms_output.put_line(ename||'  '||job);
    fetch cur_emp into ename,job;
  end loop;
  close cur_emp;
end;

 

---for

DECLARE
     fname varchar2(20);
     lname varchar2(20);
     CURSOR t_student is select firstname, LASTNAME  from students where id<=1005 ;
BEGIN
    FOR t_students IN t_student LOOP
         fname:=t_students.firstname;
         lname:=t_students.lastname;
         dbms_output.put_line('学员姓名:'||fname||''||lname);
    END LOOP;
END;

 

declare
    emp_count number;
begin
    select count(empno) into emp_count from emp;
    dbms_output.put_line(emp_count);
end;