Test Table:

CREATE TABLE TEST (
  A  INTEGER,
  B  DATE DEFAULT sysdate
)

Create stored procedure

PL/SQL:

CREATE OR REPLACE PROCEDURE TESTP IS
BEGIN
  insert into test(a) values(1);
  COMMIT;
END;

Syntax:

CREATE [OR REPLACE] PROCEDURE procedure_name 
[(parameter_name [IN | OUT | IN OUT] type [, ...])] 
{IS | AS} 
BEGIN 
  < procedure_body > 
END procedure_name; 

• 如果 BEGIN - END 块没有嵌套，则 END 后的 name 可以省略。

Execute stored procedure

PL/SQL:

exec myproc(myparam1 => 'a', myparam2 => 2); -- named parameters
exec myproc ('a', 2); -- position parameters:

exec 也可以换成 call，两者区别：

• CALL is Oracle SQL and should work everywhere. Other DB clients that can talk to Oracle may or may not support SQL*Plus EXEC. Many do (for example, Oracle SQL Developer, SQLWorkbench/J), but some don't (Liquibase).
• The data types of the parameters passed by the CALL statement must be SQL data types. They cannot be PL/SQL-only data types such as BOOLEAN.
• EXEC could be used to execute not only an SP, but an arbitrary statement.
• If an SP does not have parameters, you can use EXEC SP; syntax, but CALL requires empty parentheses: CALL SP();
• 注意一些其它数据库（例如 IBM Db2)里的 PL/SQL 语法支持 call 但不支持 exec.