学习写写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;