orc的plsql和动态sql

*loop循环*
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;