关闭

循环($ROWTYPE,%TYPE,SQL%ROWCOUNT....)

1171人阅读 评论(0) 收藏 举报

create or replace procedure insert_student
is
 i number:=0;
begin
   while i<=5000 loop
   insert into student values(student_seq.nextval,'bbb','d','1236547');
   i:=i+1;
   end loop;
   commit;
end;

begin
  insert_student;
end;

create or replace procedure hire_date(
empid in number
)
is
  name emp.ename%TYPE;
  sal  emp.sal%TYPE;
begin
  select ename,sal
  into name,sal
  from emp
  where empno=empid;
  dbms_output.put_line(name);
  dbms_output.put_line(sal);
end;


begin
  hire_date(7876);
end;

 create or replace procedure hire_date1(
 empid in number
 )
 is
   emp_record emp%ROWTYPE;
 begin
   select *
   into emp_record
   from emp
   where empno=empid;
   dbms_output.put_line(emp_record.ename);
   dbms_output.put_line(emp_record.sal);
   dbms_output.put_line(emp_record.empno);
 end;

create or replace procedure rowcount (
empid number
)
is
delenum number;
begin
  delete emp
  where empno=empid;
  delenum:=SQL%ROWCOUNT;
  commit;
  dbms_output.put_line(delenum);
end;

set serveroutput on;
begin
  rowcount(7369);
end;


begin
  insert into student values(student_seq.nextval,'a','m','1223565');
  savepoint a;
  insert into student values(student_seq.nextval,'a','m','1223565');
  savepoint b;
  insert into student values(student_seq.nextval,'a','m','1223565');
  savepoint c;
  insert into student values(student_seq.nextval,'a','m','1223565');
  savepoint d;
   rollback to savaepoint b;
  commit;
end;

begin
  insert into student values(0000,'a','m','1223565');
  savepoint a;
  insert into student values(1111,'a','m','1223565');
  savepoint b;
  insert into student values(2222,'a','m','1223565');
  savepoint c;
  insert into student values(3333,'a','m','1223565');
  savepoint d;
  rollback to savaepoint b;
  commit;
end;


set serveroutput on;
declare
  i number:=1;
begin
  loop
  dbms_output.put_line(i);
  i:=i+1;
  exit when i>=20;
  end loop;
end;

set serveroutput on;
declare
i number:=0;
begin
  for i in 1..10  loop
  dbms_output.put_line(i);
  end loop;
end;

set serveroutput on;
begin
  for i in 1..10  loop
  dbms_output.put_line(i);
  end loop;
end;

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:207714次
    • 积分:3275
    • 等级:
    • 排名:第10417名
    • 原创:111篇
    • 转载:26篇
    • 译文:0篇
    • 评论:42条
    论坛