--第一个PL/SQL程序declare var_empname varchar2(20) ;beginselect empname into var_empname from employee where empid=¶m_empid; dbms_output.put_line(var_empname);end;--rowtypedeclarerow_result employee%rowtype;beginselect * 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 程序计算出圆的面积，--并输出到控制台。declareconstant_PI number(3,2):=3.14;var_r number:=¶m_r;begindbms_output.put_line('圆的面积是'||round(constant_PI*var_r*var_r,3));end;--2.用户输入的某员工号查询出该员工所在的部门，--根据其所在部门的不同，修改工资。比如：如果部门编号（自己定）是10，--该部门全体员工的工资工资改为1000；如果部门编号是20，工资改为1200；--如果部门编号是30，工资定为1500；以此类推...如果都不满足，工资定为1800。--分别用if和case语句实现--casedeclarevar_deptid employee.deptid%type;row_result employee%rowtype;beginselect * 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--ifdeclarevar_deptid employee.deptid%type;row_result employee%rowtype;beginselect * 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 employeedeclare var_sal employee.salary %type;beginselect Salary into var_sal from employee where empname='武器大师';if var_sal <600 then update employee set Salary =Salary+100 where empname='武器大师'; else update employee set Salary =Salary-100 where empname='武器大师'; end if; end;--5: case..when..else--输入一个字母A、B、C分别输出对应的级别信息。具体为：--A对应“Excellent”，B对应“Very Good”, C对应"Not Bad",其他则对应“No Such Data”declarevar_zimu varchar2(20):='&ss';var_result varchar2(20);beginvar_result:=case var_zimu when 'A' then 'Excellent'when 'B' then 'Very Good'when 'C' then 'Not Bad'else 'No Such Data'end;dbms_output.put_line(var_result);end;--6:从1到100的累加 （循环结构）while+loopdeclarev_num integer :=1;v_sum integer :=0;beginwhile v_num<=100 loop v_sum := v_sum+v_num; dbms_output.put(v_num); if v_num<10 then dbms_output.put('+'); end if; v_num:=v_num+1; end loop; dbms_output.put('='); dbms_output.put_line(v_sum);end;--6:从1到100的累加 for...in.+loopdeclare var_num integer; var_sum integer:=0;begin for var_num in 1..100loop var_sum :=var_sum+var_num; dbms_output.put(var_num); if var_num<100 then dbms_output.put('+'); end if;end loop;dbms_output.put('=');dbms_output.put_line(var_sum);end;----无参存储过程create or replace procedure getempCountas empcount number; begin select count(*) into empcount from employee; dbms_output.put_line('总共有'||empcount||'个员工'); end;---call getempcount();-----只带输入参数的存储过程create or replace procedure showsalarybyempid(param_empid number)asvar_salary number;begin select salary into var_salary from employee where empid=param_empid; dbms_output.put_line('该员工的工资为：'||var_salary);end; --call showsalarybyempid(1);--带输入和输出参数的存储过程create or replace procedure showempbyempid(param_empid number,oneemp out employee%rowtype)as beginselect * into oneemp from employee where empid=param_empid;end;---declarerow_oneemp employee%rowtype;begin showempbyempid(&empid,row_oneemp);--实际参数 dbms_output.put_line(row_oneemp.empname||'--'||row_oneemp.hiredate); end;--cursor(游标)对结果集的操作declare cursor cur_emp(minSalary in number,maxSalary in number)is select * from employee where Salary>=minSalary and Salary<=maxSalary; oneemp employee%rowtype;begin open cur_emp(500,800); fetch cur_emp into oneemp; while cur_emp%found loop dbms_output.put_line(oneemp.empname||'--'||oneemp.Salary); fetch cur_emp into oneemp; end loop; close cur_emp;end;--trigger(触发器)create or replace trigger tri_department_id before insert on department for each row begin select seq_department.nextval into :new.departid from dual; end; ------------insert into department(departname,remark) values('打野部','先buff') select * from department ------------------------ select * from employee----分页 先有结果集再有rownum--考虑排序必须得有三层查询select * from (select rownum num,r.* from(select * from employee order by empid) r where rownum <=7)where num>=3;------select * from (select rownum num,e.* from employee e where rownum <=7)where num>=3