oracle pl/sql编程（1 ）

--pl/sql:分为三个部分，声明，可执行，异常处理。
declare
 avgSalary number(10);
begin
 select avg(sal)into avgSalary from emp;
 dbms_output.put_line('平均工资:'||avgSalary);
end;

--声明变量
declare
var1 表名称.字段名称%type;
var2 表名称%rowtype;

--给变量赋值
--有两种赋值方式；
--第一种：:=
--第二种：select ... into ... from table_name where ...;

declare
 x number(10) :=100;
 begin
   dbms_output.put_line('Values:'||x);
 end; 
 
 --通过键盘输入员工编号，打印员工信息；
declare

 input number(4);
 x nvarchar2(10);
 y nvarchar2(9);
 z number(7,2);
 
 begin
   input:='&no';
   select ename,job,sal into x,y,z from emp where empno=input;
   dbms_output.put_line('员工姓名：'||x||'，员工职位：'||y||'，员工工资：'||z);
 end;

-- 如果员工表薪水最高的工资是否大于3000，如果是，则在此基础上加上500，如果不是则输入错误信息；

declare
emp_no number(4);
max_salary number(7,2);
begin
               select max(sal)into max_salary from emp;
               select empno into emp_no from emp where sal=max_salary;
               if max_salary>3000 then
                   update emp set sal=sal+500 where empno=emp_no;
                   max_salary:=max_salary+500;
                   dbms_output.put_line('更新后的最高薪水：'||max_salary);
               end if;
                  commit;
               exception
               when others then
                     dbms_output.put_line('最高薪水小于了3000：'||sqlerrm);
end;

 --声明常量：
--使用：constant定义常量
--常量必须在声明时候赋值
--常量被赋值之后是不能改
declare
num number(20);
begin
    num:='12.5';
    dbms_output.put_line(num);
end;

--if() then elsif() then.....
--输入编号
--得到薪水
--小于2000加1000，小于3000或大于2000加800，大于3000的加500；

declare
emp_no number(4);
emp_salary number(7,2);
begin
               emp_no:='&empno';
               select sal into emp_salary from emp where empno=emp_no;
              
               if (emp_salary>3000) then
                   update emp set sal=sal+500 where empno=emp_no;
                   emp_salary:=emp_salary+500;
                   dbms_output.put_line('更新后的最高薪水：'||emp_salary);
          
               elsif (emp_salary<3000 and emp_salary>2000) then
                    update emp set sal=sal+800 where empno=emp_no;
                    emp_salary:=emp_salary+800;
                    dbms_output.put_line('更新后的最高薪水：'||emp_salary);

               elsif (emp_salary<2000) then
                     update emp set sal=sal+100 where empno=emp_no;
                     emp_salary:=emp_salary+1000;
                     dbms_output.put_line('更新后的最高薪水：'||emp_salary);
               end if;
                  commit;
                 
               exception
               when others then
                     dbms_output.put_line('最高薪水小于了3000：'||sqlerrm);
end;
--分别打印出员工薪金的前五名
---循环语句

--loop循环
declare
       emp_name nvarchar2(10);
       emp_job nvarchar2(9);
       emp_salary number(7,2);
       emp_rank number(1);  
begin
       emp_rank:=0;
       loop
         emp_rank:=emp_rank+1;
       if(emp_rank>=6) then  exit;
         end if;
          select ename,job,sal into emp_name,emp_job,emp_salary from (
          select ename,job,sal, row_number() over(order by sal desc) as sal_rank from emp) temp where(temp.sal_rank=emp_rank);
          dbms_output.put_line('员工编号：'||emp_name||'，员工姓名：'||emp_job||'，员工工资：'||emp_salary);
       end loop;
end;

--for循环
declare
       emp_name nvarchar2(10);
       emp_job nvarchar2(9);
       emp_salary number(7,2);
       emp_rank number(1); 
begin
       for emp_rank in reverse 1..5 loop
          select ename,job,sal into emp_name,emp_job,emp_salary from (
          select ename,job,sal, row_number() over(order by sal desc) as sal_rank from emp) temp where temp.sal_rank=emp_rank;
          dbms_output.put_line('员工编号：'||emp_name||'，员工姓名：'||emp_job||'，员工工资：'||emp_salary);
       end loop;
end;

--while 循环
declare
       emp_name nvarchar2(10);
       emp_job nvarchar2(9);
       emp_salary number(7,2);
       emp_rank number(1); 
begin
       emp_rank:=0;
       while emp_rank<5 loop
          emp_rank:=emp_rank+1;
          select ename,job,sal into emp_name,emp_job,emp_salary from (
          select ename,job,sal, row_number() over(order by sal desc) as sal_rank from emp) temp where temp.sal_rank=emp_rank;
          dbms_output.put_line('员工编号：'||emp_name||'，员工姓名：'||emp_job||'，员工工资：'||emp_salary);
       end loop;
end;
-----------------------------------goto语句
declare
emp_no emp.empno%type;
emp_name emp.ename%type;
emp_job emp.job%type;
emp_sal emp.sal%type;
begin
        emp_no:='7369';
        select sal into emp_sal from emp where empno=emp_no;
        if(emp_sal>3000) then
        goto m;
        elsif(emp_sal>1600 and emp_sal<3000)then
        goto n;
        else
              dbms_output.put_line('不做操作！');
        end if;
        <<n>>
        update emp set sal=sal+500 where empno=emp_no;
        select empno,ename,job,sal into emp_no,emp_name,emp_job,emp_sal from emp where empno=emp_no;
        dbms_output.put_line(emp_no||emp_name||emp_job||emp_sal);               
        <<m>>
            null;  
end;

-----------------------------执行动态创建表的语句
select * from emp_01;
drop table emp_01;
declare
eno emp.empno%type;
sql_01 varchar2(200);
sql_02 varchar2(200);
emp_records emp%rowtype;
begin
        eno:='&empno';
        execute immediate 'create table emp_01(
        emp_no number(4),emp_name nvarchar2(10)     
        )';
        sql_01:='select * from emp where empno=:id';      
        execute immediate sql_01 into emp_records using eno;
        sql_02:='insert into emp_01 values(:empno,:ename)';
        execute immediate sql_02 using emp_records.empno,emp_records.ename;
        exception
        when others then
        dbms_output.put_line('操作失败');  
end;


-----------------------------自定义异常处理
declare
e exception;
cash number(4);
begin
   cash:='&输入取款金额：';
   if(cash>2500) then
   raise e;
   else
   dbms_output.put_line('取款成功');
   end if;
   exception
   when e then
   dbms_output.put_line('每次最多只能取2500元');  
end;

----------------------------使用记录类型
declare
type myrecord is record(
     dept_no dept.deptno%type,
     dept_name dept.dname%type,
     dept_loc dept.loc%type
);
real_record myrecord;
begin
            select * into real_record from dept where deptno=10;
           dbms_output.put_line(real_record.dept_no||real_record.dept_name||real_record.dept_loc);  
end;

select * from dept;
select * from emp;

--练习1
--写一个块、 从界面输入一个雇员的姓名，如果该雇员是ACCOUNTING的
再判断如果是部门经理工资加2000元。项目经理加1700，软件开发工程师加1500
运行完后，查看薪水是否增加
SET SERVEROUTPUT ON

--练习2
--用%type定义一个雇员姓名的常量，根据该雇员的姓名取出该雇员中的姓名，职位，部门以及薪水并打印出来（用％rowtype类型的变量）
declare
emp_name emp.ename%type;
emp_records emp%rowtype;
dept_name dept.dname%type;

begin
            emp_name:='&name';
            select * into emp_records from emp where ename=emp_name;
            select dname into dept_name from dept where deptno=emp_records.deptno;
           
            dbms_output.put_line('雇员名称：'||emp_records.ename||'工作职位:'||emp_records.job||'部门名称：'||dept_name||'员工薪金：'||emp_records.sal);
end;

--练习3
--如果输入一个员工的编码，超过了存储的范围。捕获value_error 的异常。
declare
emp_no emp.empno%type;
begin
       emp_no:='&empno';
       exception
       when value_error then
       dbms_output.put_line('输入编号超过了存储的范围');
end;

-- 练习4
--自定义异常，如果修改员工工资时，工资超过了5000，则触发自定义异常。显示月薪不能大于5000。
declare
e exception;
emp_no emp.empno%type;
emp_sal emp.sal%type;
begin
    emp_no:='7839';
    update emp set sal=sal+500 where empno=emp_no;
    select sal into emp_sal from emp where empno=emp_no;
   
    if(emp_sal>5000) then
    raise e;
    else
    commit;
    end if;
    exception
    when e then
    rollback;
    dbms_output.put_line('显示月薪不能大于5000');
end;

--练习5
--创建一个表来存放sql语句，写一个块执行动态sql，读取表中的sql执行。

select * from sql_table;
select * from DEPT;
declare
type myrecord is record(
     rN number(4),
     sta varchar2(200)
);
re myrecord;
rowN number(4);
sql_sta varchar2(200);
sql_insert_statement varchar2(200);
sql_excute_statement varchar2(200);
cou number(20);                 
begin
                --判断表是否存在
                select count(*) into cou from user_tables where table_name='SQL_TABLE';
                if(cou<=0) then
                           execute immediate 'create table sql_table (rowNo number(4),sql_sta varchar2(200))';
                end if;  
               -- rowN:='1';
               -- sql_sta:='&sql_query';       
              -- sql_insert_statement:='insert into sql_table values(:rowNo,:statement)';
               --execute immediate sql_insert_statement using rowN,sql_sta;
               
              select * into re from sql_table where ROWNO=1;
              sql_excute_statement:=re.sta;
            
              execute immediate sql_excute_statement;
              dbms_output.put_line(re.sta);
end;