PLSQL编程（基础）

—————————————————————————————————————格式
Declare
  V_empId employees.employee_id%type;
  V_empName employees.first_name%type;
  V_jobId employees.job_id%type;
BEGIN
  insert into employees(employee_id,first_name,job_id,location_id)
              values(600,changsha,200,1500)
  --接收返回值
  returning employee_id,last_name,job_id
  --装载变量
  into V_empId,V_empName,V_jobId;
  dbms_output.put_line(V_empId||' '||''||last_name||' '||''||job_id);
END;



Declare
  V_empId employees.employee_id%type;
  v_emp employees%rowtype;
BEGIN
  select * into v_emp from employees where employee_id='&input_empId';
  dbms_output.put_line(v_emp.first_name);
exception
  when no_data_found then
    dbms_output.put_line('该员工未找到');
END;




—————————————————————————————————————判断
Declare
  v_lemp number :='&input_lemp';
BEGIN
--if条件判断
  if v_lemp>37 then
     dbms_output.put_line('速度隔离');
   else 
     dbms_output.put_line('回家遣返');
  end if;
END;



Declare
  v_score number :='&input_lemp';
  v_result varchar2(32);
BEGIN
  --case条件判断
  v_result :=
  case
     when v_score > 90 and v_score<100 then '成绩优秀'
     when v_score > 80 and v_score<90 then '成绩良好'
     when v_score > 70 and v_score<80 then '成绩一般'
     when v_score > 60 and v_score<70 then '成绩及格'
     when v_score <60 then '成绩差'
    else'数据异常'
    END;
 --打印结果
 dbms_output.put_line(v_score ||'分为' || v_result);
end;



—————————————————————————————————————循环
Declare
  v_score number :=1;
BEGIN
  --loop循环
  LOOP
    dbms_output.put_line('score当前值：' || v_score);
    v_score := v_score+1;
    exit when v_score = 11;
  end loop;
END;



Declare
  v_score number :=1;
BEGIN
  --while loop循环
    while v_score < 11 loop
    dbms_output.put_line('score当前值：' || v_score);
    v_score := v_score+1;
  end loop;
END;



Declare

BEGIN
  --for循环
    for v_score in REVERSE 1 .. 10 loop
    dbms_output.put_line('score当前值：' || v_score);
  end loop;
END;




Declare
--定义一个数组类型
  type type_score is array(5) of varchar2(32);
--指定一个变量为该数组类型
  v_type_score type_score;
BEGIN
  --数组赋值
    v_type_score := type_score('语文','数学','英语','历史','地理');
  --数组的输出采用for循环方式，打印出来
    for score in REVERSE 1 .. v_type_score.count loop
    dbms_output.put_line('score当前值：' || v_type_score(score));
    end loop;
END;