学习写写sql/pl

if语句

declare
  v_id    teachers.teacher_id%type;
  v_title teachers.title%type;
begin
  v_id := &teacher_id;
  select title into v_title from teachers where teacher_id = v_id;
  if v_title = '教授' then
    update teachers set wage = 1.1 * wage where teacher_id = v_id;
  elsif v_title = '高工' then
    update teachers set wage = wage + 100 where teacher_id = v_id;
  end if;
end;

联合数组

declare
  type sname_tab_type is table of students.name%type index by varchar2(10);
  sname_tab sname_tab_type;
  v_id      students.student_id%type;
begin
  v_id := &student_id;
  select name
    into sname_tab('学生姓名')
    from students
   where student_id = v_id;
  dbms_output.put_line(sname_tab('学生姓名'));
end;

嵌套表

declare
  type sname_type is table of varchar2(20);
  snametable sname_type := sname_type('蛐蛐', 'jjjj', 'ikkkk', null); --必须先初始化
begin
  snametable(2) := 'jjj';
  for i in 1 .. 4 loop
    dbms_output.put_line(snametable(i));
  end loop;
end;

循环语句loop

declare
  v_i   int := 1;
  v_sum int := 0;
begin
  loop
    v_sum := v_sum + v_i;
    exit when v_i = 10;
    v_i := v_i + 1;
  end loop;
  dbms_output.put_line(v_sum);
end;

循环语句for

declare
  v_i   int := 1;
  v_sum int := 0;
begin
  for v_i in 1 .. 10 loop
    v_sum := v_sum + v_i;
  end loop;
  dbms_output.put_line(v_sum);
end;

循环while

declare
  v_i   int := 1;
  v_sum int := 0;
begin
  while v_i < 11 loop
    v_sum := v_sum + v_i;
    v_i   := v_i + 1;
  end loop;
  dbms_output.put_line(v_sum);
end;

使用记录表

declare
  type student_tab_type is table of students%rowtype index by binary_integer;
  student_tab student_tab_type;
  v_id        students.student_id%type;
begin
  v_id := &student_id;
  select * into student_tab(1) from students where student_id = v_id;
  dbms_output.put_line(student_tab(1).name, student_tab(1).sex);
end;