PL/sql_procedure_简单样例

CREATE TABLE xjytab(col1 number,col2 varchar2(20),col3time date,d date);
--procedure
--if meet mistakes,use 'show error'could check the error position;
----------------------------------------------------------------
--create insert procedure 
create or replace procedure xjyp
   ( c1 in number,c2 in varchar2,c3 in varchar2 ,c4 in varchar2) is
    begin
    insert into xjytab(col1,col2,col3time,d)
    values(c1,c2,to_date(c3,'yyyymmdd'),to_date(c4,'yyyymmdd'));
    end xjyp;
/
--insert procedure execute method1;
exec xjyp(7,'procedure Test','20160803','20160903')
--insert procedure execute method2;
declare
    cmdstr varchar(500);
    cc1 number:=9;
    cc2 varchar(20):='proceT2';
    cc3 varchar(8):='20160825';
    cc4 varchar(8):='20160902';
    begin
    cmdstr:='begin xjyp(:x,:ss,:a,:e); end;';
    execute immediate cmdstr
    using cc1,cc2,cc3,cc4;
    end;
/
--------------------------------------------------
--create select procedure
create or replace procedure xjyp is
mstr varchar(20);
begin 
select col2 into mstr
from xjytab where col1=5;
dbms_output.put_line(mstr);--数据不是一行报错
end;
/
--select procedure execute
exec xjyp
-------


-----------------------------------------------
--dbms_output procedure
create or replace procedure xjyp is
begin
dbms_output.put_line('test');
end;
/
--execute in screen or not
---in screan
SQL> set serverout on;
SQL> exec xjyp;
test

PL/SQL procedure successfully completed
---not in screen
SQL> set serverout off;
SQL> exec xjyp;

PL/SQL procedure successfully completed
-------------------------------------------------