Oracle 10g creation Stored Procedure example New

1. Create a stored procedure
SQL> Create or replace function get_sal3 (name varchar2)
2 return number is
3 v_sal number (6, 2 );
4 begin
5 select Sal into v_sal from EMP where upper (ename) = upper (name );
6 RETURN v_sal;
7 end;
8/

The function has been created.

SQL> var salary number
SQL> Exec: Salary: = get_sal3 ('Scott ')

The PL/SQL process is successfully completed.

SQL> Print salary

Salary
----------
3000

2. Create a stored procedure in the PL/SQL command window
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as Scott
 
SQL> Create procedure insert_dept (NO in number, name in varchar2)
2 is
3 begin
4 insert into dept (deptno, dname) values (No, name );
5 end;
6/
 
Procedure created
 
SQL> Create procedure insert_dept2 (no number, name varchar2)
2 is
3 begin
4 insert into dept (deptno, dname) values (No, name );
5 end;
6/
 
Procedure created
 
SQL> exec insert_dept2 (50, 'sall ');
 
PL/SQL procedure successfully completed

3. Create a stored procedure in the PL/SQL program window
Create or replace procedure insert_dept4 (NO in number, name in varchar2) is
Begin
Insert into dept (deptno, dname) values (No, name );
End insert_dept4;
Press F8 to execute