A PL/SQL procedure with no parameter : Procedure Call « Stored Procedure Function « Oracle PL / SQL






A PL/SQL procedure with no parameter

   
SQL>
SQL> CREATE TABLE MyTable (
  2    num_col    NUMBER,
  3    char_col   VARCHAR2(60)
  4    );

Table created.

SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE InsertIntoTemp AS
  2    v_Num1      NUMBER := 1;
  3    v_Num2      NUMBER := 2;
  4    v_String1   VARCHAR2(50) := 'Hello World!';
  5    v_String2   VARCHAR2(50) := '-- This message brought to you by PL/SQL!';
  6    v_OutputStr VARCHAR2(50);
  7  BEGIN
  8    INSERT INTO MyTable (num_col, char_col)
  9      VALUES (v_Num1, v_String1);
 10    INSERT INTO MyTable (num_col, char_col)
 11      VALUES (v_Num2, v_String2);
 12
 13    SELECT char_col
 14      INTO v_OutputStr
 15     FROM MyTable
 16     WHERE num_col = v_Num1;
 17    DBMS_OUTPUT.PUT_LINE(v_OutputStr);
 18
 19    SELECT char_col
 20      INTO v_OutputStr
 21     FROM MyTable
 22     WHERE num_col = v_Num2;
 23    DBMS_OUTPUT.PUT_LINE(v_OutputStr);
 24
 25    ROLLBACK;
 26
 27  END InsertIntoTemp;
 28  /

Procedure created.

SQL>
SQL> BEGIN
  2    InsertIntoTemp;
  3  END;
  4  /
Hello World!
-- This message brought to you by PL/SQL!

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> select * from MyTable;

no rows selected

SQL>
SQL>
SQL> drop table MyTable;

Table dropped.

SQL>

   
    
  








Related examples in the same category

1.Call a stored procedure in a PL/SQL block
2.Call a stored procedure then other statements
3.Dependency Example
4.Forward Declarations
5.Exceptions in Subprograms
6.Exception throwed out of procedure call
7.Default parameter value
8.Pass parameter by data type
9.Calling ParameterLength illegally (ORA-6502)...
10.Use named notation
11.Use named notation, but with a different order of the formal parameters
12.This script demonstrates how to create and call a simple procedure.