Oracle数据库—— PL/SQL进阶编程

一、涉及内容

　　1.掌握PL/SQL程序块的结构

　　2.理解并熟练掌握各种变量的应用。

二、具体操作

1、创建一个表messages，该表只有一个字段results 类型是number(2)，编写一个块，向messages表中添加数字1到10，但不包括6和8。

（1）创建表

 

（2）添加数字

   语句：

DECLARE
 v_num NUMBER(2):=0;
 BEGIN
  FOR i IN 1..10 loop
      v_num:=v_num+1;
      if i <> 6 and i <> 8 then
         insert into messages values(v_num);
      end if;
  END LOOP;
 dbms_output.put_line('results:'||v_num);
end;

截图：

 

查看表：

 

2、创建一个块，按照emp表中员工的工资sal修改他的奖金comm的值。具体要求（块的要求是（2）-（7））如下：

（1）给emp表添加一行数据，其中工资sal和奖金comm都为NULL空值。

（2）通过替代变量读取员工编号。

（3）如果该员工的工资小于1000美元，则他的奖金为工资的10%

（4）如果该员工的工资在1000~1500美元，则奖金为工资的15%

（5）如果该员工的工资大于1500美元，则奖金为工资的20%

（6）如果该员工的工资为NULL，则奖金为0

（7）运行PL/SQL块，输入不同的员工编号，验证更新后的奖金值

首先，插入一行数据：

 

语句：

DECLARE
  v_empno scott.emp.empno%type;
  v_sal NUMBER(7,2);
  v_comm scott.emp.comm%type;

BEGIN
  v_empno:=&no;
  SELECT sal INTO v_sal
  FROM scott.emp WHERE empno=v_empno;
  case  
  when v_sal<1000  THEN
     UPDATE scott.emp SET comm=v_sal*0.1 WHERE empno=v_empno;

  when v_sal<=1500 THEN
     UPDATE scott.emp SET comm=v_sal*0.15 WHERE empno=v_empno;

  when v_sal>1500 THEN
     UPDATE scott.emp SET comm=v_sal*0.2 WHERE empno=v_empno;

  when v_sal is null THEN
     UPDATE scott.emp SET comm=0 WHERE empno=v_empno;

   else
     dbms_output.put_line('error');

END case;

 SELECT comm INTO v_comm FROM scott.emp WHERE empno=v_empno;

dbms_output.put_line('empno:'||v_empno||'   comm:'||v_comm);

END;

截图：

 

3、为emp表增加一个列stars，类型为VARCHAR2(100)。

 

4、创建一个PL/SQL块，通过替代变量读取员工编号，根据员工的工资计算他能获得的星号“*”数量，每100美元奖励一个星号，按四舍五入处理。并根据员工所获得的星号数量n，形成由n个星号组成的字符串，写入emp表的stars列。

语句：

declare
  v_no number(6):=&no;
  v_sal NUMBER(7,2);
  v_star  varchar2(200);
  v_n  integer;
begin
  select round(sal/100) into v_n
  from scott.emp where empno=v_no;
  for i in 1..v_n loop
      v_star:= v_star || '*';
     end loop;
  update scott.emp set stars = v_star where empno = v_no;
  dbms_output.put_line('empno:'||v_no||' stars:'||v_star);
end; 

截图：

 

5、创建一个PL/SQL块，从dept表中查询每个部门的名称。具体要求如下：

（1）声明一个记录表类型的变量my_tab，用来存放部门名称。

（2）用循环语句从dept表中查询每个部门名称，放入my_tab中。

（3）再使用一个循环将my_tab中的部门名称输出到屏幕上。

语句：

DECLARE
  type dept_name_table_type is table of scott.dept.dname%type
  index by binary_integer;
  my_tab dept_name_table_type;
BEGIN
  for i in 1..4 loop
     select dname into my_tab(i)
     from scott.dept
     where deptno=i*10;
    end loop;
  for i in 1..4 loop
     dbms_output.put_line('dname:'||my_tab(i));
  end loop;
END; 

截图：

 

6、修改上一题，从dept表中查询各个部门的所有信息，并输出到屏幕上。

语句：

DECLARE
  type dept_record_type is record(
  no scott.dept.deptno%type,
  name scott.dept.dname%type,
  location scott.dept.loc%type);
  type dept_table_type is table of dept_record_type
   index by binary_integer;
  my_tab dept_table_type;
BEGIN
  for i in 1..4 loop
     select * into my_tab(i)
     from scott.dept
     where deptno=i*10;
     dbms_output.put_line('deptno:'||my_tab(i).no||
'  dname:'||my_tab(i).name||'    loc:'||my_tab(i).location);
  end loop;
END;

 截图：