set serveroutput on
declare var number;
begin
SQL语句;
dbms_output.put_line('Column name: '|| ci);
end;
------------------------------------------------------
1.基本结构
CREATE OR REPLACE PROCEDURE 存储过程名字
(
参数1 IN NUMBER,
参数2 IN NUMBER
) IS
变量1 INTEGER :=0;
变量2 DATE;--注意分号
BEGIN
END 存储过程名字
---------------------------------------------------------------------
--实例：
CREATE OR REPLACE PROCEDURE
SJ_CKSYS_EMP(TREV IN VARCHAR2, TRES OUT VARCHAR2) IS
C_EMP VARCHAR2(25);--注意分号
BEGIN
SELECT COUNT('21') into C_EMP FROM DUAL;
TRES:=C_EMP ;
EXCEPTION
WHEN OTHERS THEN
TRES:='EMP ERROR';
END;
--调试有输出参数的存储过程
set serveroutput on
declare vars varchar2(50);
begin
SJ_CKSYS_EMP('1', vars);
dbms_output.put_line('Column type :'||vars);
end;