|
--第一个PL/SQL程序
declare
var_empname varchar2(20) ;
begin
select empname into var_empname from employee
where empid=¶m_empid;
dbms_output.put_line(var_empname);
end;
--rowtype
declare
row_result employee%rowtype;
begin
select * into row_result from employee where empid=¶m_empid;
dbms_output.put_line(row_result.empid||'-'||row_result.empname||'--'||row_result.Salary);
end;
---------------------------Day1203练习-------------------------------------
---1. 圆周率PI 假定取常量3.14 ,让用户输入圆的半径,用PL/SQL 程序计算出圆的面积,
--并输出到控制台。
declare
constant_PI number(3,2):=3.14;
var_r number:=¶m_r;
begin
dbms_output.put_line('圆的面积是'||round(constant_PI*var_r*var_r,3));
end;
--2.用户输入的某员工号查询出该员工所在的部门,
--根据其所在部门的不同,修改工资。比如:如果部门编号(自己定)是10,
--该部门全体员工的工资工资改为1000;如果部门编号是20,工资改为1200;
--如果部门编号是30,工资定为1500;以此类推...如果都不满足,工资定为1800。
--分别用if和case语句实现
--case
declare
var_deptid employee.deptid%type;
row_result employee%rowtype;
begin
select * into row_result from employee where empid=¶m_empid;
var_deptid:=row_result.deptid;
case var_deptid
when 110 then update employee set Salary=1000 where deptid=110;
when 120 then update employee set Salary=1200 where deptid=120;
when 130 then update employee set Salary=1500 where deptid=130;
else
update employee set Salary=1800 ;
end case;
end;
select * from employee
--if
declare
var_deptid employee.deptid%type;
row_result employee%rowtype;
begin
select * into row_result from employee where empid=¶m_empid;
var_deptid:=row_result.deptid;
if var_deptid=110 then
update employee set Salary=180 where deptid=110;
elsif var_deptid=120 then
update employee set Salary=1200 where deptid=120;
elsif var_deptid=130 then update employee set Salary=1500 where deptid=130;
else
update employee set Salary=1800 ;
end if;
end;
-------------------
select * from employee
---4:属性数据类型type,if..then..else....end if
--根据用户用户输入的员工号,查询某个员工的工资,
--如果工资大于某个数(比如1000),将该员工工资减100,否则加100。
select * from employee
declare
var_sal employee.salary %type;
begin
select Salary into var_sal from employee where empname='武器大师';
if var_sal |
|
|
|
|
|
|