BEGIN UPDATE person SET name='zhangsan'; where id=1 IF SQL%FOUND THEN COMMIT; dbms_output.put_line('success'); ELSE dbms_output.put_line('failed'); END IF END; -- 判断影响的行数 DECLARE rowCount INT;
BEGIN UPDATE person SET name='aaa'; rowRount:=SQL%ROWCOUNT;
将一个表中的数据放到两个数据表中 DECLARE t_id int; t_name varchar2; t_sex char(1); --定义游标 CURSOR teacher_cur IS SELECT tid,name,sex FROM teacher WHERE tid<=26; BEGIN --打开游标 OPEN teacher_cur; LOOP --提取游标 FETCH teacher_cur INTO t_id, t_name,t_sex; --一定要退出循环 EXIT WHEN NOT teacher_cur%FOUND; IF teacher_sex='M' THEN 一插入数据到表 COMMIT; ELSE -- 插入数据到表二 COMMIT; END IF; END LOOP; IF teacher_cur%ISOPEN THEN --关闭游标 CLOSE teacher_cur; END IF END;
--指定动态游标类型 TYPE cursor_type IS REF CURSOR; myCursor cursor_type;--游标类型 v_sql VARCHAR2(200); v_person person%ROWTYPE; BEGIN --定义动态的sql语句 v_sql:='select * from person where pname LIKE ‘‘%m%’’'; --打开游标 OPEN myCursor FOR v_sql; LOOP --提取游标 FETCH myCursor INTO v_person; --停止循环 EXIT WHEN myCursor%NOTFOUND; dbms_output.put_line (v_person.id); END LOOP; EXCEPTION WHEN OTHERS THEN CLOSE myCursor; IF myCursor%ISOPEN THEN CLOSE myCursor; END IF; END;