plsql笔记
----- 添加20条数据---------------------------------------------------------------------------
-- for
declare
V_START NUMBER := 1;
begin
FOR V_START IN 1..20 LOOP
INSERT INTO BONUS VALUES('KING'||V_START,'JAVA'||V_START
,to_number('300'||V_START),to_number('100'||V_START));
END LOOP;
--COMMIT;
end;
---------------------------------------------------------------------------
-- exit when
declare
V_START NUMBER := 1;
V_END CONSTANT NUMBER :=20;
begin
LOOP
INSERT INTO BONUS VALUES('KING'||V_START,'JAVA'||V_START
,to_number('300'||V_START),to_number('100'||V_START));
V_START := V_START+1;
EXIT WHEN V_START > V_END;
END LOOP;
end;
---------------------------------------------------------------------------
-- while
declare
V_START NUMBER := 1;
V_END CONSTANT NUMBER :=20;
begin
WHILE V_START <= V_END LOOP
INSERT INTO BONUS VALUES('KING'||V_START,'JAVA'||V_START
,to_number('300'||V_START),to_number('100'||V_START));
V_START := V_START+1;
END LOOP;
end;
---------------------------------------------------------------------------
v_id stu.id%type;
myrecord mycur%rowtype;
--//初始化
v_num integer :=1;
v_name varchar :='name';
v_name stu.name%type default 'this is ';
---------------------------------------------------------------------------
----- 使用游标
declare
v_id number;
v_name varchar(20);
cursor c_stu is select id,name from stu ;
begin
open c_stu; --1
loop -- 2
fetch c_stu into v_id,v_name;
dbms_output.put_line('id is '||v_id||' name is '||v_name);
exit when c_stu%notfound;
end loop; -- 2
close c_stu; --1
end;
------ 使用全表
declare
v_id number;
v_name varchar(20);
cursor m_stu is select * from stu ;
stu1 m_stu%rowtype;
begin
open m_stu;
loop fetch m_stu into stu1;
dbms_output.put_line('id is '||stu1.id||' name is '||stu1.name);
exit when m_stu%notfound;
end loop;
close m_stu;
end;
----- 隐式游标
declare
cursor cur_stu is select * from stu;
begin
for mystu in cur_stu loop
dbms_output.put_line('id is '||mystu.id||' name is '||mystu.name);
end loop;
end;
----- 隐式游标(有参数)
declare
cursor cur_stu(p_math number) is select * from stu where math=p_math;
begin
for mystu in cur_stu(70) loop
dbms_output.put_line('id is '||mystu.id||' name is '||mystu.name);
end loop;
end;
---------------------------------------------------------------------------
----- 一个简单的存储过程 p_size要插入几条记录
create or replace procedure insertStu(p_size in integer) is
v_start integer:=1;
begin
for v_start in 1..p_size loop
insert into stu values(T_STU_SEQ.NEXTVAL,'name'||v_start);
commit;
end loop;
end insertStu;
----- 流程控制
create or replace procedure guessNum(p_guessNum in integer) is
v_resultNum number :=50;
begin
dbms_output.put_line('programmer start');
if(p_guessNum<0) then
dbms_output.put_line('猜的数不能为负数!');
elsif((p_guessNum<v_resultNum)and(p_guessNum>=0)) then
dbms_output.put_line('猜的数, 小了!');
elsif(p_guessNum>v_resultNum) then
dbms_output.put_line('猜的数, 大了!');
else
dbms_output.put_line('恭喜你，你猜对了!');
end if;
dbms_output.put_line('programmer end');
end guessNum;
if(true)语句 else 语句 end if;
if else end if;
if.....then
if.....then else
if.....then elsif
------------------------------------------------------------------------
----- 一个简单的循环 exit when
declare
v_num number :=1;
begin
loop
dbms_output.put_line('this number is '||v_num);
v_num:=v_num+1;
exit when v_num>20;
end loop;
end;
----- 跟上面的一样的 while
declare
v_first number:=1;
v_num number :=1;
begin
while v_num<=20 loop
dbms_output.put_line('this number is '||v_num);
v_num:=v_num+1;
end loop;
end;
----- for in
declare
v_num number :=1;
begin
for v_num in 1..10 loop
dbms_output.put_line('this number is '||v_num);
end loop;
end;
------------------------------------------------------------------------
页:
[1]