"ORACLE" plsql Create stored procedure (11g)

Stored procedures with no parameters

sql> Create or replace procedure Get_time is
2 begin
3 Dbms_output.put_line (sysdate);
4 End;
5/

Procedure created.

sql> exec get_time;

15-may-17

PL/SQL procedure successfully completed.

Stored procedure with in parameter

sql> Create or Replace procedure Add_dept
2 (v_dept_id in number, v_dept_name in varchar2) is
3 begin
4 INSERT INTO Dept (DEPTNO, Dname)
5 values (v_dept_id,v_dept_name);
6 dbms_output.put_line (' Inserted ' | | Sql%rowcount | | ' Row ');
7 End;
8/

Procedure created.

sql> var s1 number
sql> var s2 varchar2
Sql> EXEC:S1: =60

PL/SQL procedure successfully completed.

sql> exec:s2: = ' sss '

PL/SQL procedure successfully completed.

Sql> exec add_dept (: S1,:S2);
Inserted 1 row

PL/SQL procedure successfully completed.

sql> print S1 s2;

S1
----------
60

S2
--------------------------------
Sss

Sql> Select Deptno, dname from dept;

DEPTNO dname
---------- --------------
Ten ACCOUNTING
About
SALES
OPERATIONS
Sam
SSs

Stored procedures with in and out parameters

sql> Create or replace procedure compute
2 (Num1 in out number,num2 on out number)
3 is
4 v1 number;
5 v2 number;
6 begin
7 v1: = num1/num2;
8 V2: = mod (num1,num2);
9 NUM1: = v1;
Ten num2: = v2;
one end;
12/

Procedure created.

"ORACLE" plsql Create stored procedure (11g)