plsql

set serveroutput on

declare

--a1 varchar(20);

--a2 varchar(20);

--a3 varchar(20);

cnt integer:=1;

cursor v_cur is select f1,f2,f3 from a;

s integer;

begin

--open v_cur;

--loop

--exit when v_cur%NOTFOUND;

--fetch v_cur into a1,a2,a3;

select count(1) into s froma;

dbms_output.put_line('total:'||s);

for vvc in v_cur loop

dbms_output.put_line('cnt:'||cnt||',vf1:'||vvc.f1||',vf2:'||vvc.f2||',vf3:'||vvc.f3);

cnt:= cnt+1;

end loop;

--close v_cur;

end;

/

 

----------------

spool a.txt

select  * from a;

 spool off

---------------

set serveroutput on

declare

i integer;

msg varchar2(20);

begin

select count(1) into i from a;

if i=1 then

msg:='hehe1 '||i;

elsif i=2then

msg:='hehe2 '||i;

else

msg:='hehe_oth '||i;

end if;

dbms_output.put_line(msg);

end;

 /

-----------------

BEGIN

        《PL/SQL块》;

        Exception

                whenno_data_foundthen       --没有找到数据

                        《响应命令》;

                whentoo_many_rowsthen       --返回多行，隐式光标每次只能检索一行数据

                        《响应命令》;

                wheninvalid_numberthen       --字符向数字转换失败

                        《响应命令》;

                whenzero_dividethen               --被零除

                        《响应命令》;

                whendup_val_on_indexthen       --向唯一索引中插入重复数据

                        《响应命令》;

                wheninvalid_cursorthen       --非法游标操作

                        《响应命令》;

                whenvalue_errorthen               --数字的,数据转换,截字符串或强制性的错误

                        《响应命令》;

                whenothersthen               --发生其它任何错误

                null;                       --选择一：什么也不做，就当错误没发生

                raise form_trigger_failure;       --选择二：挂起当前程序

        END;

/

--------------

create or replace trigger tg_insert
before insert  on a
for each row
begin
insert into b(i,time) values (:new.f1,sysdate);
end tg_insert;
/






create or replace trigger tg_update
before update  on a
for each row
begin
insert into b(i,time) values (:new.f1,sysdate);
end tg_update;
/










create or replace trigger tg_update_no_each_row
before update  on a
--for each row
begin
insert into b(i,time) values ('update op',sysdate);
end tg_update_no_each_row;
/






create or replace trigger tg_delete
before delete  on a
for each row
begin
insert into b(i,time) values ('delete:'||:old.f1,sysdate);
end tg_delete;
/