Oracle PL/SQL 语句块用法

set serveroutput on;
begin
   update scott.emp set sal=800.00 where empno='7369';
   if sql%found then
     dbms_output.put_line('表已更新');
   else
      dbms_output.put_line('表没有更新');
   end if;
end;

declare
  my_emp emp%rowtype;
   cursor emp_cur is select empno,ename from emp where sal<2500.00;
   begin
     open emp_cur;
     loop
       fetch emp_cur into my_emp.empno,my_emp.ename;
       exit when emp_cur%notfound;
       dbms_output.put_line(emp_cur%rowcount||'姓名'||my_emp.ename);
     end loop;
     close emp_cur;
   end;
---7、编写一个PL/SQL程序，以接受用户输入的订单编号（orderno），然后检查订单的状态。
--如果订单状态(ostatus)为'P'，则删除订单，否则显式消息'该订单已确认，无法删除'。
declare
  orderno number(5);
  status varchar2(5);
  begin
    orderno :=&id;
    select status into status from order_master where orderno=orderno;
    if (status ='P') then
      delete order_master where orderno=orderno;
      dbms_output.put_line(sql%rowcount);
    else
      dbms_output.put_line('无法删除');
    end if;
    --编写一个PL/SQL程序，使用游标显式销售报表。如果目标销售额(tsales)大于实际销售额(asales)，
    --则显式消息"需提高销售额"。如果tsales等于asales，则显示消息"已达到销售额"，
    --否则显示消息"销售业绩出色"。（方法很多，可以考虑用循环游标)
    declare
     tsales number;
     asales number;
     cursor sales_cur is select * from salesdetails;
     begin
       for sales_rec in sales_cur
        loop
          if sales_rec.tsales > sales_rec.asales then
           dbms_output.put_line('产品'||sales.rec.pid||'要提高销售额');
          else
            if sales_rec.tsales = sales_rec.asales then
             dbms_output.put_line('产品'||sales.rec.pid||'已达到销售额');
             else
               dbms_output.put_line(('产品：' ||sales_rec.pid||'销售业绩出色);
               end if;
          end if;
        end loop;
     end;
     ---sal 800--2000一般
     -- 2000--4000好
     -- 4000-5000比较好
    
     declare
      cursor emp_cur is select * from emp;
      begin
        for sals in emp_cur
          loop
            if sals.sal>800 and sals.sal<2000 then
              dbms_output.put_line(sals.ename||'工资一般'||sals.sal);
            end if;
           
            if sals.sal>2000 and sals.sal<4000 then
              dbms_output.put_line(sals.ename||'工资好'||sals.sal);
            end if;
           
            if sals.sal>4000 then
              dbms_output.put_line(sals.ename||'工资比较好'||sals.sal);
            end if;
           
          end loop;
      end;
-----
     ---sal 800--2000  加1000
     -- 2000--4000  加2000
     -- 4000-5000加3000
    
     declare
       cursor emp_cur is select * from emp;
       begin
         for sals in emp_cur
           loop
              if sals.sal >800 and sals.sal<2000 then
                 update emp set sal =sal+1000 where sal between 800 and 2000;
              end if;
             
              if sals.sal>2000 and sals.sal<4000 then
              update emp set sal =sal+2000 where sal between 2000 and 4000;
            end if;
           
             if sals.sal>4000 then
              update emp set sal =sal+3000 where sal >4000;
            end if;
           
            end loop;
       end;
      
       ---
       ---动态SQLcreate table 动态insert data 用游标将数据查出来
      
          declare
            sqlStr varchar2(2000);
            sqlIns varchar2(5000);
            str varchar2(200);
            type cur is ref cursor;
            c cur;
           
          begin
             str :='1234';
             sqlStr :='create table test (id number,name varchar2(50))';
             sqlIns :='insert into test values(1,'||''''||str||''''||')';
             execute immediate sqlStr;
             execute immediate sqlIns;

          end;
          insert into test values(2,'123');
          ---用动态游标将数据查出来--------
          declare
            type cur is ref cursor;
            c cur;
            id number;
          begin
             open c for select id from test;
             loop
               fetch c into id;
                exit when c%notfound;
                dbms_output.put_line(id);
             end loop;
          end;