pl/sql 初学实例

（1）创建dept表

create table dept(

dept_id int primary key,

dept_name varchar(25) not null,

dept_leader varchar(25) not null

)

 

（2）创建employees表

create table employees(

employee_id int primary key,

last_name varchar(25) not null,

sex varchar(6) not null check(sex in('male','female')),

salary decimal(8,2),

hire_date date,

dept_id int,

foreign key(dept_id) references dept(dept_id)

)

 

（3）向两个表添加数据

 

 

 

 

（4）得到编号为1的员工姓名

declare

         v_empid number not null :=1;

         v_name varchar(25);

begin

         select last_name into v_name

         from employees

         where employee_id=v_empid;

         dbms_output.put_line(v_name);

end;

注意：给声明的变量赋初值是用“：=”

结果：king

 

（5）把7号员工的名字和工资显示出来

declare
         v_empid number not null :=5;
         v_name varchar(25);
         v_salary decimal(8,2);
begin
         select last_name,salary into v_name,v_salary
         from employees
         where employee_id=v_empid;
         dbms_output.put_line('NAME:'||v_name||chr(10)||'SALARY:'||v_salary);
end;

结果：NAME:amber
SALARY:3000

此处注意的问题是：显示多个数据中间用‘||’来分隔，且回车与换行用‘chr(13)’和‘chr(10)’

 

（6）复合类型数据

declare
         type my_type is record(
         v_empid number,
         v_name varchar(25),
         v_salary decimal(8,2)
         );
         v_type my_type;           --define v_type as a my_type
begin 
         select employee_id,last_name,salary into v_type
         from employees
         where employee_id=5;
                        dbms_output.put_line(v_type.v_empid||chr(9)||v_type.v_name||chr(9)||v_type.v_salary);
end;

注意：1。定义复合类型的方法—----type 名 is record（）

如type my_type is record(
          v_empid number,
          v_name varchar(25),
          v_salary decimal(8,2)
          );
      2．要实例一个type对象啊-----实例名 复合类型名

如v_type my_type;

      3．使用type中的变量时-----实例名。变量名

          v_type.v_empid

        4．chr（9）是空格

结果：5   amber  3000

 

（７）输入部门号，显示部门信息（声明变量要与指定表中的行/列相同类型）

declare
         v_deptid dept.dept_id%TYPE:=&x;
         v_dept dept%ROWTYPE;
begin
         select * into v_dept
         from dept
         where dept.dept_id=v_deptid;
         dbms_output.put_line(v_dept.dept_id||chr(10)||v_dept.dept_name||chr(10)||v_dept.dept_leader);
end;

注意：1。变量名  表名。列名%type    表示声明的变量和表中对应列的类型相同

          如v_deptid dept.dept_id%TYPE:=&x;

      2．变量表名  表名%ROWTYPE    表示新表变量类型与表中行类型及列名相同

          如v_dept dept%ROWTYPE;

        3．用户输入方法―――――“：=＆ｘ”

　　　　　如 v_dept:=&x

结果：执行后输入 2

      2
sale
sl_leader

 

（8）4～８号员工，如果其部门是sales，工资增加15%，部门是software，工资增加20%，部门是service则工资增加10%，之后将姓名，原工资和现在的工资显示出来（并不改变表中内容）

declare
         v_employee_id number:=4;
         v_employees employees%ROWTYPE;（声明一个和employees相同的表）
         v_dept dept%ROWTYPE;
begin
         while v_employee_id<=8
         LOOP
             select * into v_employees
             from employees
             where employee_id=v_employee_id;
        
             select * into v_dept
             from dept
             where dept_id=v_employees.dept_id;
            
             if
               v_dept.dept_name='sale'
             then
               dbms_output.put_line(v_employees.employee_id||chr(32)||v_employees.last_name||chr(32)||v_employees.salary||chr(32)||v_employees.salary*1.15);
             elsif
               v_dept.dept_name='service'
             then
               dbms_output.put_line(v_employees.employee_id||chr(32)||v_employees.last_name||chr(32)||v_employees.salary||chr(32)||v_employees.salary*1.10);
             elsif
               v_dept.dept_name='software'
             then
               dbms_output.put_line(v_employees.employee_id||chr(32)||v_employees.last_name||chr(32)||v_employees.salary||chr(32)||v_employees.salary*1.20);
             end if;
            
             v_employee_id:=v_employee_id+1;
         END LOOP;         
end;

注意：1。此题用到循环，while 条件 LOOP 。。。。end LOOP

         还有一种方式：LOOP 。。。。exit when 条件 end LOOP

      2．通过变量表v_employee来查看改变的工资信息

结果：4 victoria        2000    2200
5 amber   3000    3450
6 kara    3000    3600
7 ukiss   1000    1150
8 2pm     1500    1800

（9）将没有部门的员工的工资增加25%，并显示员工的姓名，原工资和新增加的工资，提示“Alter successfully！”

declare
         cursor cur_sal is select * from employees;
         v_employees employees%ROWTYPE;
begin
         open cur_sal;
         fetch cur_sal into v_employees;
         while cur_sal %FOUND
         loop
         if v_employees.dept_id=''
         then
             update employees set salary= salary*1.25 where employee_id=v_employees.employee_id;
             end if;
         fetch cur_sal into v_employees;
         end loop;
         dbms_output.put_line('Alter successfully!');     
         close cur_sal; 
end;

 

待续