declare
nno dept.deptno%type;
temp number :=0;
begin
select min(deptno) into nno from dept;
loop
nno:=nno+1;
insert into test values(nno,'部门'||nno,'地址'||nno);
temp:=temp+1;
exit when temp= 10;
end loop;
commit;
end;
*for循环*
declare
nno dept.deptno %type;
temp number:=0;
begin
select min(deptno) into nno from dept;
for temp in 0..9
loop
nno:=nno+1;
insert into test values(nno,'部门'||nno,'地址'||nno);
end loop;
commit;
end;
*while循环*
declare
nno dept.deptno%type;
temp number:=0;
begin
select min(deptno) into nno from dept;
while temp<10
loop
nno:=nno+1 ;
insert into test values(nno,'部门'||nno,'地址'||nno);
temp:=temp+1;
end loop;
commit;
end;
*游标*
declare
cursor mycursor is select tname ,sal from teacher where deptno=10;
nrow mycursor %rowtype;
nsal number ;
tax number:=0;
total_tax number:=0;
begin
open mycursor;
loop
fetch mycursor into nrow;
exit when mycursor%notfound;
nsal:=nrow.sal;
if nsal>=5000 and nsal<10000 then tax:=nsal*0.05;end if;
if nsal>=10000 and nsal<40000 then tax:=nsal*0.1;end if;
if nsal>=40000 then tax:=nsal*0.3;end if;
total_tax:=total_tax+tax;
end loop;
dbms_output.put_line(total_tax);
close mycursor;
end;
*条件*
declare
nname teacher.tname%type;
nsal teacher.sal%type;
nrank varchar2(10);
begin
select tname ,sal into nname,nsal from teacher where teacher.tno='1040';
if nsal<2000 then nrank:='临时工';
elsif nsal>=2000 and nsal<15000 then nrank:='蓝领';
elsif nsal>=15000 and nsal<40000 then nrank:='白领';
else nrank:='金领';
end if;
dbms_output.put_line(nname||'的薪水为:'||nsal||'级别为'||nrank);
end;
动态sql
declare
sqls varchar2(200);
tname teacher.tname%type;
tgender teacher.gendar%type;
deptno teacher.deptno%type;
begin
sqls:='select tname,gendar,deptno from teacher where tno=:x';
execute immediate sqls
into tname,tgender,deptno
using 1003;
sqls:='update teacher set sal=sal+100 where 1=1';
if tname is not null then
sqls:=sqls||' and tname='''||tname||'''';
end if;
if tgender is not null then
sqls:=sqls||' and gendar='''||tgender||'''';
end if;
if deptno is not null then
sqls:=sqls||' and deptno='||deptno;
end if;
execute immediate sqls;
end;
select * from teacher where tno=1003;
Declare
type mycursor is ref cursor;
tcursor mycursor;
tname teacher.tname%type;
begin
open tcursor for 'select tname from teacher where deptno=:x'
using 10;
loop
fetch tcursor into tname;
exit when tcursor%notfound;
dbms_output.put_line(tname);
end loop;
end;
declare
type mycursor is ref cursor;
tcursor mycursor;
item mycursor;
tname teacher.tname%type;
sal teacher.sal%type;
begin
open tcursor for 'select tname,sal from teacher where deptno=:x'
using 30;
loop
fetch tcursor into item;
exit when tcursor%notfound;
dbms_output.put_line(1);
end loop;
end;
declare
sqls varchar2(200);
tgender teacher.gendar%type:='女';
mycursor number;
trow number;
e exception;
begin
sqls:='update teacher set sal=sal+100 where gendar=:x and deptno=:y';
mycursor:=dbms_sql.open_cursor;
dbms_sql.parse(mycursor,sqls,dbms_sql.native);
dbms_sql.bind_variable(mycursor,':x',tgender);
dbms_sql.bind_variable(mycursor,':y',10);
trow:=dbms_sql.execute(mycursor);
if trow>=2 then raise e;end if;
dbms_output.put_line(trow);
dbms_sql.close_cursor(mycursor);
exception
when e then dbms_output.put_line('超出200');
rollback;
end;
declare
v_cursor number;
v_sql varchar2(200);
v_id number;
v_name varchar2(50);
v_date varchar2(10);
v_stat number;
begin
v_sql := 'select n_id, v_name, to_char(d_insert_date, ''yyyy-mm-dd'') from test';
v_cursor := dbms_sql.open_cursor; --打开游标
dbms_sql.parse(v_cursor, v_sql, dbms_sql.native); --解析游标
dbms_sql.define_column(v_cursor, 1, v_id); --定义列
dbms_sql.define_column(v_cursor, 2, v_name, 50); --注意:当变量为varchar2类型时,要加长度
dbms_sql.define_column(v_cursor, 3, v_date, 10);
v_stat := dbms_sql.execute(v_cursor); --执行SQL
loop
exit when dbms_sql.fetch_rows(v_cursor) <= 0; --fetch_rows在结果集中移动游标,如果未抵达末尾,返回1。
dbms_sql.column_value(v_cursor, 1, v_id); --将当前行的查询结果写入上面定义的列中。
dbms_sql.column_value(v_cursor, 2, v_name);
dbms_sql.column_value(v_cursor, 3, v_date);
dbms_output.put_line(v_id || ':' || v_name || ':' || v_date);
end loop;
end;
select * from teacher where gendar='女' and deptno=10