oracle中sql语句块,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;