plsql练习笔记

用于练习的表：

create sequence test_id
increment by 1
start with 1
maxvalue 99999
minvalue 1
cache 20
cycle;
create table test_table(
       testid int not null primary key,
       testname nvarchar2(20),
       testpwd nvarchar2(20),
       testbirth date
);
insert into test_table values(test_id.nextval,'admin','123456',to_date('2012-5-6 12:31:25','yyyy-mm-dd HH:mi:ss'));
insert into test_table values(test_id.nextval,'cglin','123456',to_date('2012-5-6 12:31:25','yyyy-mm-dd HH:mi:ss'));

select * from test_user.test_table;

pl_sql
◇◆列类型

declare
       tname test_user.test_table.testname%type;
begin
  select testname into tname from test_user.test_table where testid = 2;
  dbms_output.put_line('tname:'||tname);
  exception
    when too_many_rows then
      dbms_output.put_line('exception:出现异常！返回行数太多！');
    when no_data_found then
      dbms_output.put_line('exception:出现异常！没有数据返回！');
end;


declare
      tname test_user.test_table.testname%type;
begin
  select testname into tname from test_user.test_table where testid = 34;
  dbms_output.put_line('tname:'||tname);
  exception
    when others then
      dbms_output.put_line('有异常出现了！');
end;

◇◆行类型

declare
  trow test_user.test_table%rowtype;
begin
  --select * into trow from test_user.test_table where testid = 2;
  select * into trow from test_user.test_table;
  dbms_output.put_line('row:id='||trow.testid||'  name='||trow.testname||'  pwd='||trow.testpwd||'  birth='||to_char(trow.testbirth,'yyyy-mm-dd HH:mi:ss'));
  exception 
 when too_many_rows then
    dbms_output.put_line('exception:出现异常！返回行数太多！');
 when no_data_found then
      dbms_output.put_line('exception:出现异常！没有数据返回！');
end;

◇◆if条件语句

declare
      id int;
begin
  id :=&亲输入数字;
  if id < 10 then
     dbms_output.put_line('您输入的数字小于10');
  elsif id = 10 then
     dbms_output.put_line('您输入的数字等于10');
  else
     dbms_output.put_line('您输入的数字大于10');
  end if;
end;

◇◆case语句

declare
  id int;
begin
  id :=&请输入数字;
  case id
    when 1 then
      dbms_output.put_line('您输入的是1');
    when 2 then
      dbms_output.put_line('您输入的是2');
    else
      dbms_output.put_line('您输入的不是1也不是2');
  end case;
  
end;

◇◆case条件语句

declare
  id int;
begin
  id :=&请输入数字;
  case
    when id = 1 then
      dbms_output.put_line('您输入的是1');
    when id = 2 then
      dbms_output.put_line('您输入的是2');
    else
      dbms_output.put_line('您输入的不是1也不是2');
  end case;
  
end;

◇◆loop循环

declare
  id int;
begin
  id :=&请输入数字;
  loop
    dbms_output.put_line(id);
    id := id + 1;
    exit when id > 10;
  end loop;
end;

◇◆while循环

declare
  id int;
begin
  id :=&请输入数字;
  while id < 10 loop
    dbms_output.put_line(id);
    id := id + 1;
  end loop;
end;

◇◆for循环

declare
  id int;
begin
  id :=&请输入数字;
  for i in 1..id loop
    dbms_output.put_line(i);
  end loop;
end;

◇◆99乘法表

declare
  i int;
begin
  i := 9;
  for j in 1..i loop
    for a in 1..j loop
      dbms_output.put(j||'x'||a||'='||a*j||'  ');
    end loop;
    dbms_output.put_line('');
  end loop;
end;

◇◆显示游标

declare
  cursor test_cursor
  is
  select * from test_user.test_table;
  
  str_cursor test_user.test_table%rowtype;
begin
  open test_cursor;
       
       loop
         fetch test_cursor into str_cursor;
         exit when test_cursor%notfound;
         dbms_output.put_line('testname:'||str_cursor.testname||'    testpwd:'||str_cursor.testpwd);
       end loop;
  close test_cursor;
  
end;

◇◆隐示游标

declare
  cursor test_cursor
  is 
  select * from test_user.test_table;
begin
  for str_cursor in test_cursor loop
    dbms_output.put_line('testname:'||str_cursor.testname);
  end loop;
end;

◇◆参数游标

declare
  cursor test_cursor(tid int)
  is
  select * from test_user.test_table where testid=tid;
begin
  for str_cursor in test_cursor(&编号) loop
    dbms_output.put_line('testname:'||str_cursor.testname);
  end loop;
end;