Oracle存储过程:
创建 procedure的语法:
CREATE [ OR REPLACE ] PROCEDURE [ schema.]procedure
[(argument [IN | OUT | IN OUT ] [NO COPY] datatype
[, argument [IN | OUT | IN OUT ] [NO COPY] datatype]...
)]
[ authid { current_user | definer }]
{ is | as } { pl/sql_subprogram_body |
language { java name 'String' | c [ name, name] library lib_name
}]
- CREATE PROCEDURE sam.credit (acc_no IN NUMBER, amount IN NUMBER) AS
- BEGIN
- UPDATE accounts
- SET balance = balance + amount
- WHERE account_id = acc_no;
- END;
语法示例如下:
create or replace procedure get_news
2 as
3 aa number;
4 begin
5 select count(*) into aa from cf_news;
6 dbms_output.put_line('aa='||aa);
7 end;
8 /
在10g企业管理器em,创建存储过程:
as
begin
for i in 1..1000 loop
insert into wq values('nihao','mate','colors',i);
end loop
commit;
end;
在isql*plus中执行创建的存储过程tt,为计算时间执行如下:
select to_char(sysdate,'hh24:mi:ss')t from dual;
exec tt;
select to_char(sysdate,'hh24:mi:ss')t from dual;
但为计算到毫秒级,进行如下的修改:
1、利用dbms_utility.get_time,修改存储过程如下:
as
time_before BINARY_INTEGER;
time_after BINARY_INTEGER;
begin
time_before := DBMS_UTILITY.GET_TIME;
for i in 1..5000 loop
insert into wq values('nihao','mate','colors',i);
end loop
commit;
time_after := DBMS_UTILITY.GET_TIME;
DBMS_OUTPUT.PUT_LINE (time_after - time_before);
end;
或者直接在isql*plus中编写执行存储过程如下:
DECLARE
time_before BINARY_INTEGER;
time_after BINARY_INTEGER;
BEGIN
time_before := DBMS_UTILITY.GET_TIME;
for i in 1..1000 loop
insert into wq values('nihao','mate','colors',i);
end loop
commit;
time_after := DBMS_UTILITY.GET_TIME;
DBMS_OUTPUT.PUT_LINE (time_after - time_before);
END;
但执行后无法显示所用时间。
2、设置isql*plus首选项中的计时选项为 开,则执行未修改的存储过程即显示所用时间
3 以下面的方式获得毫秒级数值:
select to_char(current_timestamp(5),'DD-MON-YYYY HH24:MI:SSxFF') from dual;