oracle中的procedure编写和使用详解

CREATE [OR REPLACE] PROCEDURE procedure_name[(parameter_list)]{IS|AS}[local_declarations]BEGINexecutable_statements[EXCEPTION exception_handlers]END [procedure_name];

 parameter_name1 [in | out | in out] type, parameter_name1 [in | out] type [,........]

outcur OUT BASIC_CURSOR: OPEN outcur FOR SELECT col1,col2 FROM tablename

create or replace procedure putNum(P_Date in date, P_year out varchar2) is  v_num number(8) := 1;  v_days number;  v_date date; begin  dbms_output.put_line('intput value:' || P_year);  v_num := 1;  v_days := 1;  v_days := to_number(to_char(P_Date, 'dd')); -- to_char(sysdate-20,'dd')  for i in 1 .. v_days Loop  v_date := to_date('2011/11/' || to_char(i), 'yyyy/MM/dd');  --dbms_output.put_line(v_date);  end loop;  P_year := '2012'; end;

[EXECUTE]|[CALL] procedure_name[(parameter,…n)]

beginProc_Insert('hello6',25,'2005-12-24');commit;end;

DBMS_OUTPUT.put_line ('Hello World!');

show serveroutput;set serveroutput on;

select object_name,object_type,status from user_objects where OBJECT_TYPE='PROCEDURE';

SELECT text FROM user_source WHERE NAME = 'procedure_name';

DROP PROCEDURE procedure_name;