--标量类型
--1.varchar2(n)
--2.char(n)
--3.number(p,s)
--4.DATE
--5.TIMESTAMP
--6.BOOLEAN
select * from employees
declare
emp_name employees.last_name%TYPE;
--获取已知字段的类型
emp_sal employees.salary%type;
-- 赋值 := 标量
emp_suishou CONSTANT number(3,2) :=0.03;
emp_overMoney number(9,2);
begin
select last_name,salary
into emp_name,emp_sal
from employees
where employee_id=&employee_id;
emp_overMoney:=emp_sal-(emp_sal*emp_suishou);
DBMS_OUTPUT.put_line('员工姓名:'||emp_name);
DBMS_OUTPUT.put_line('税前工资:'||emp_sal);
DBMS_OUTPUT.put_line('税后工资:'||emp_overMoney);
exception
when NO_DATA_FOUND
THEN
DBMS_OUTPUT.put_line('没获取到任何数据');
end;
--if语句
declare
v_sal number(9,2);
begin
select salary into v_sal from employees
--trim('&empname') 可以输入汉字
where last_name=trim('&empname');
if v_sal <8000
then
update employees set
salary=v_sal+500 where last_name=trim('&empname');
elsif v_sal >18000
then
update employees set
salary=v_sal-500 where last_name=trim('&empname');
else
DBMS_OUTPUT.put_line('维持不变!');
end if;
end;
---CASE 语句
declare
v_sal number(9,2);
begin
select salary into v_sal from employees
--trim('&empname') 可以输入汉字
where last_name=trim('&empname');
CASE v_sal
when 8000
then
update employees set
salary=v_sal+500 where last_name=trim('&empname');
when 12000
then
update employees set
salary=v_sal-500 where last_name=trim('&empname');
else
DBMS_OUTPUT.put_line('维持不变!');
end CASE;
end;
--循环语句
--1.loop
declare
i int := 1;
begin
loop
DBMS_OUTPUT.put_line(i);
i:=i+1;
exit when i>10;
end loop;
end;
--2.while循环
declare
i int := 1;
begin
while i<=10 loop
DBMS_OUTPUT.put_line(i);
i:=i+1;
end loop;
end;
--3 for 循环
declare
i int := 1;
begin
for i in 1..10 loop
DBMS_OUTPUT.put_line(i);
end loop;
end;
select * from employees
--定义一个游标(在查询有多条结果的时候使用)
--declare cursor cursorname is 查询语句;
--打开游标
--open cursorname
--获取游标的值（提取数据）
--fetch cursorname into v_name,v_salay
--关闭游标
--close cursorname
--%ISOPEN 判断游标是否打开
--%FOUND 有记录返回true
--%NOTFOUND 没有记录返回true
--%rowcount 行数
--获取所有员工的姓名和工资
declare
cursor emp_namesal
is
select last_name,salary from employees;
v_name employees.last_name%type;
v_sal employees.salary%type;
begin
if not emp_namesal %isopen
then
open emp_namesal;
end if;
loop
fetch emp_namesal into
v_name,v_sal;
exit when emp_namesal%NOTFOUND;
DBMS_OUTPUT.put_line(v_name||':'||v_sal);
end loop;
DBMS_OUTPUT.put_line('数据获取结束');
close emp_namesal;
end;
--更改数据
declare
cursor emp_name
is
select last_name,salary from employees
for update of salary;
begin
for emp_record in emp_name
loop
if emp_record.salary<1000
then
update employees set salary=salary+500
where current of emp_name;
end if;
-- DBMS_OUTPUT.put_line('更改之前:'||v_salary);
-- DBMS_OUTPUT.put_line(emp_record.last_name||':'||emp_record.salary);
end loop;
end;
--参数游标
--参数游标是指具体带有参数的游标。
--在定义了参数游标之后，当使用不同参数值多次打开游标时
--会获取不同的结果
declare
cursor emp_cursor(cuo int)
is
select last_name,salary
from employees
where DEPARTMENT_ID=cuo;
v_name employees.last_name%type;
v_sal employees.salary%type;
begin
if not emp_cursor %isopen then
open emp_cursor(2);
end if;
loop
fetch emp_cursor into
v_name,v_sal;
exit when emp_cursor%NOTFOUND;
DBMS_OUTPUT.put_line(v_name||':'||v_sal);
end loop;
DBMS_OUTPUT.put_line('输出结束');
close emp_cursor;
end;
--存储过程联合游标一起使用
create or replace procedure testout
(dep_id int)
is
cursor emp_namesal(depid int)
is
select last_name,salary from employees
where DEPARTMENT_ID=depid;
v_name employees.last_name%type;
v_sal employees.salary%type;
begin
if not emp_namesal %isopen
then
open emp_namesal(dep_id);
end if;
loop
fetch emp_namesal into
v_name,v_sal;
exit when emp_namesal%NOTFOUND;
DBMS_OUTPUT.put_line(v_name||':'||v_sal);
end loop;
DBMS_OUTPUT.put_line('数据获取结束');
close emp_namesal;
end;
--输入部门
--如果部门为3 部门下的员工增加 10%的工资
--如果部门为2 部门下的员工增加 20%的工资
--如果部门为1 部门下的员工增加 30%的工资
--如果没有该部门 则显示 ‘该部门不存在’
create or replace procedure dep_inPutId
(dep_id int)
is
--游标在什么时候使用： 查询多条语句的时候使用
cursor emp_depid(depno int)
is
select * from employees where employees.department_id=depno;
begin
case dep_id
when 1
then
update employees set salary=salary+salary*0.3
where department_id=dep_id;
when 2
then
update employees set salary=salary+salary*0.2
where department_id=dep_id;
when 3
then
update employees set salary=salary+salary*0.2
where department_id=dep_id;
else
DBMS_OUTPUT.put_line('部门不存在!');
end case;
for empinfo in emp_depid(dep_id)
loop
DBMS_OUTPUT.put_line(empinfo.last_name||':'||empinfo.salary);
end loop;
end;
select * from employees
begin
dep_inPutId(5);
end;
--更改数据 与 之前的数据进行对比
create or replace procedure testEMP
is
cursor emp_name
is
select department_id,salary from employees
for update of salary;
v_depid employees.department_id%type;
v_sal employees.salary%type;
begin
if not emp_name %isopen
then
open emp_name;
end if;
loop
fetch emp_name into
v_depid,v_sal;
exit when emp_name%NOTFOUND;
DBMS_OUTPUT.put_line('旧:'||v_depid||':'||v_sal);
case v_depid
when 1
then
update employees set salary=salary+salary*0.3
where current of emp_name;
when 2
then
update employees set salary=salary+salary*0.2
where current of emp_name;
when 3
then
update employees set salary=salary+salary*0.2
where current of emp_name;
else
DBMS_OUTPUT.put_line('部门不存在!');
end case;
end loop;
close emp_name;
for emp_record in emp_name
loop
DBMS_OUTPUT.put_line(emp_record.department_id||':'||emp_record.salary);
end loop;
end;
begin
testEMP;
end;