方便复习
=====
drop table testTable;
/
commit ;
/
-- 1.创建表
-- ==========================================================================================
create table testtable (sid integer ,course nvarchar2( 30 ), score integer );
/
commit ;
/
create or replace procedure InsertTestData
(
mStart integer
)
as
i int : = 1 ;
begin
i: = mStart;
while i < 10 loop
insert into testTable(sid,Course,score) values (i, ' 课程 ' || i,i * 10 );
i: = i + 1 ;
end loop;
end ;
/
commit ;
/
-- 2.新增数据
-- ==========================================================================================
Declare
i integer : = 1 ;
v_sql varchar ( 1000 );
begin
i: = 1 ;
while i < 10 loop
v_sql: = ' insert into testTable(sid,Course,score) values(:1,:2,:3) ' ;
execute immediate v_sql using i, ' 课程 ' || i,i * 10 ;
commit ;
i: = i + 1 ;
end loop;
end ;
/
commit ;
/
-- 3.调用存储过程
-- ==========================================================================================
begin
InsertTestData( 10 );
end ;
/
-- 或在PL/SQL COMMAND WINDOWS中执行
-- ==========================================================================================
/*
set serveroutput on
declare
i int:=1;
begin
while i<10 loop
insert into testTable(sid,Course,score) values(i,'课程'||i,i*10);
i:=i+1;
end loop;
end;
/
commit;
*/
select * from TestTable;
/
-- 4.通过rowid更新表中相同纪录的第一条纪录,rowid每次生成时都为固定的有序编号
-- ==========================================================================================
UPDATE TestTable A SET A.sid = 1
where a.rowid = ( SELECT rowid FROM TestTable b where rownum < 2 and a.rowid = b.rowid)
and rownum < 2 ;
/
commit ;
/
select * from TestTable;
/
-- 5.对表中成绩累乘
-- ==========================================================================================
-- loga+logb=logab
-- 10logad=ab
Select power ( 10 , Sum ( Log ( 10 ,score))) from TestTable;
/
-- 6.对表中成绩累加
-- ==========================================================================================
select a.sid, sum (a.score),
( select sum (b.score)
from testtable b
where b.sid <= a.sid)
from testtable a
group by a.sid
order by a.sid;
/
commit ;
/
--
一次插入1000万条纪录
insert into testTable select rownum sid, rownum + 1 score from dual connect by level <= 10000000
commit ;
/
insert into testTable select rownum sid, rownum + 1 score from dual connect by level <= 10000000
commit ;
/