1、在 SELECT 语句中必须有 INTO 子句
create or replace procedure w_proc_log
as
aa number;
begin
select count(*) into aa from w_tbl_log;
end;
/
过程已创建。
create or replace procedure w_proc_log
as
aa number;
begin
select count(*) into aa from w_tbl_log;
end;
/
过程已创建。
2、
create or replace procedure w_proc_log
as
aa number;
begin
select count(*) into aa from w_tbl_log;
dbms_outpub.put_line('aa='||aa);
end;
/
警告: 创建的过程带有编译错误。
SQL> show errors;
PROCEDURE GET_NEWS 出现错误:
LINE/COL ERROR
-------- -----------------------------------------
6/1 PLS-00201: 必须说明标识符 'DBMS_OUTPUB.PUT_LINE'
6/1 PL/SQL: Statement ignored
create or replace procedure w_proc_log
as
aa number;
begin
select count(*) into aa from w_tbl_log;
dbms_outpub.put_line('aa='||aa);
end;
/
警告: 创建的过程带有编译错误。
SQL> show errors;
PROCEDURE GET_NEWS 出现错误:
LINE/COL ERROR
-------- -----------------------------------------
6/1 PLS-00201: 必须说明标识符 'DBMS_OUTPUB.PUT_LINE'
6/1 PL/SQL: Statement ignored
create or replace procedure w_proc_log
as
aa number;
begin
select count(*) into aa from w_tbl_log;
dbms_output.put_line('aa='||aa);
end;
/
过程已创建。
SQL> set serverout on;
SQL> execute w_proc_log;
aa=3
PL/SQL 过程已成功完成。
3、带有参数的过程
CREATE OR REPLACE procedure w_proc_log(userId IN varchar2 ,nCount OUT number ) is
CREATE OR REPLACE procedure w_proc_log(userId IN varchar2 ,nCount OUT number ) is
begin
nCount:=0;
select count(*) into nCount from w_tbl_log where no = userId;
exception
when others then
nCount:=0;
end w_proc_log;
/
nCount:=0;
select count(*) into nCount from w_tbl_log where no = userId;
exception
when others then
nCount:=0;
end w_proc_log;
/