例句:
CREATE OR REPLACE PROCEDURE ADD_BOUNS (emp_no INTEGER, bonus REAL) IS
old_salary REAL;
new_salary REAL;
overdrawn EXCEPTION;
BEGIN
SELECT sal INTO old_salary FROM EMP
WHERE empno = emp_no;
new_salary := old_salary + bonus;
IF new_salary < 0 THEN
RAISE overdrawn;
ELSE
UPDATE EMP SET sal = new_salary
WHERE empno = emp_no;
COMMIT;
END IF;
EXCEPTION
WHEN overdrawn THEN
NULL;
END ADD_BOUNS;如果是循环结构,若用到while:
WHILE <condition> LOOP
<sequence_of_statements>;
END LOOP;或者 用到for:
FOR <counter> IN [REVERSE] <low>..<high> LOOP
<sequence_of_statements>;
END LOOP;FOR i IN 1..3 LOOP -- assign the values 1,2,3 to i
sequence_of_statements -- executes three times
END LOOP;FOR i IN REVERSE 1..3 LOOP -- assign the values 3,2,1 to i
sequence_of_statements -- executes three times
END LOOP;FOR i IN REVERSE 1..3 LOOP -- assign the values 3,2,1 to i
dbms_output.put_line(i);
i := i + 1; -- illegal
END LOOP;
CREATE OR REPLACE PROCEDURE ADD_BOUNS (emp_no INTEGER, bonus REAL) IS
old_salary REAL;
new_salary REAL;
overdrawn EXCEPTION;
BEGIN
SELECT sal INTO old_salary FROM EMP
WHERE empno = emp_no;
new_salary := old_salary + bonus;
IF new_salary < 0 THEN
RAISE overdrawn;
ELSE
UPDATE EMP SET sal = new_salary
WHERE empno = emp_no;
COMMIT;
END IF;
EXCEPTION
WHEN overdrawn THEN
NULL;
END ADD_BOUNS;如果是循环结构,若用到while:
WHILE <condition> LOOP
<sequence_of_statements>;
END LOOP;或者 用到for:
FOR <counter> IN [REVERSE] <low>..<high> LOOP
<sequence_of_statements>;
END LOOP;FOR i IN 1..3 LOOP -- assign the values 1,2,3 to i
sequence_of_statements -- executes three times
END LOOP;FOR i IN REVERSE 1..3 LOOP -- assign the values 3,2,1 to i
sequence_of_statements -- executes three times
END LOOP;FOR i IN REVERSE 1..3 LOOP -- assign the values 3,2,1 to i
dbms_output.put_line(i);
i := i + 1; -- illegal
END LOOP;
as
beginend aa;
as
cursor g_cursor is select * from table1 where id=vid for updatel;
num number:=0;
begin
for aa in g_cursor loop
update table1 set name=vname where current of g_cursor;
if mod(num,200)=0 then
commit;
end if
num:=num+1;
end loop;
end aa;