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;
Oracle PL/SQL 语句块用法
最新推荐文章于 2022-05-27 13:30:30 发布