--Record变量类型，相当于类 declare
type type_record_dept is record (
deptno dept.deptno%type, dname dept.dname%type, loc dept.loc%type );
v_temp type_record_dept; begin
v_temp.deptno:=50; v_temp.dname:='yugang'; v_temp.loc:='beijing';
dbms_output.put_line(v_temp.deptno || ' ' || v_temp.dname || ' ' || v_temp.loc); end;
--使用%rowtype声明record变量 declare
v_temp dept%rowtype; begin
v_temp.deptno:=50; v_temp.dname:='yugang'; v_temp.loc:='beijing';
dbms_output.put_line(v_temp.deptno || ' ' || v_temp.dname || ' ' || v_temp.loc); end;
--SQL语句的运用
--select语句必须和into语句一块使用并且只能返回一条记录 --sql%rowcount declare
v_name emp.ename%type; v_sal emp.sal%type; begin
select ename,sal into v_name,v_sal from emp where empno=7369; dbms_output.put_line(v_name || ' ' || v_sal); end;
declare
v_temp emp%rowtype; begin
select * into v_temp from emp where empno=7369; dbms_output.put_line(v_temp.ename || ' ' || v_temp.eno); end; declare
v_deptno dept.deptno%type:=50; v_dname dept.dname%type:='mm'; v_loc dept.loc%type:='bj'; begin
insert into dept values(v_deptno,v_dname,v_loc); commit; end; declare
v_deptno dept.deptno%type:=50; v_count number; begin
--update emp set sal:=sal/2 where deptno=v_deptno; --select deptno into v_deptno from emp where deptno=7369; select count(*) into v_count from emp;
dbms_output.put_line(sql%rowcount || '条记录被影响！'); commit; end;
--DDL语句，在PLSQL中使用DDL语句要加上execute immediate,两个单引号代表一个单引号 begin
execute immediate 'create table tt(name varchar2(20) default ''Army'')'; end; declare
v_sal emp.sal%type; begin
select sal into v_sal from emp where empno=7369; if(v_sal<1200) then dbms_output.put_line('low');
elsif(v_sal<2000) then
dbms_output.put_line('middle'); else
dbms_output.put_line('high'); end if; end; --循环 declare
i binary_integer:=1; begin loop
dbms_output.put_line(i); i:=i+1;
exit when (i >= 11); end loop; end; declare
j binary_integer:=1; begin
while j<11 loop
dbms_output.put_line(j); j:=j+1; end loop; end; begin
for k in 1..10 loop dbms_output.put_line(k); end loop;
for k in reverce 1..10 loop dbms_output.put_line(k); end loop; end; --错误处理 declare
v_temp number; begin
select empno into v_temp where deptno=10; exception
when too_many_rows then dbms_output.put_line('太多记录了'); when others then
dbms_output.put_line('error'); end; declare v_temp number; begin
select deptno into v_temp from emp where empno=2222; exception
when no_data_found then dbms_output.put_line('没数据'); end;
create table errorlog (
id number primary key, errcode number, errmsg varchar2(1024), errdate date );
create sequence seq_errorlog_id start with 1 increment by 1; declare
v_deptno dept.deptno%type:=10; v_errcode number; v_errmsg varchar2(1024); begin
delete from dept where deptno=v_deptno; commit; exception when others then
rollback;
v_errcode:=SQLCODE; v_errmsg:=SQLERRM;
insert into errlog values(seq_errorlog_id.nextVal,v_errcode,v_errmsg,sysdate); commit; end;
第47~48课：cursor（重点） declare cursor c is select * from emp; v_emp c%rowtype; begin open c;
fetch c into v_emp;
dbms_output.put_line(v_emp.ename); close c; end; --简单循环 declare cursor c is select * from emp; v_emp c%rowtype; begin open c; loop
fetch c into v_emp; exit when(c%notfound);
dbms_output.put_line(v_emp.ename); end loop; end; declare cursor c is select * from emp; v_emp c%rowtype; begin