Video
SQL Output from Video
SQL> alter session set nls_date_format='DD-MON-YYYY';
Session altered.
SQL> SET SERVEROUTPUT OFF
SQL> cl scr
SQL>
SQL>
/*******************************************************************************************************************************
Hello World Stored Procedure
A simple stored procedure that prints "Hello World" to the screen.
*******************************************************************************************************************************/
SQL> create table counselor(
2 COUNSELOR_ID int Primary Key,
3 FIRST_NAME varchar(20),
4 LAST_NAME varchar(20),
5 PHONE varchar(14)
6 );
Table created.
SQL> insert into counselor values(10,'Quinn','Fowler','(508) 244-1416');
1 row created.
SQL> select * from counselor;
COUNSELOR_ID FIRST_NAME LAST_NAME PHONE
------------ -------------------- -------------------- --------------
10 Quinn Fowler (508) 244-1416
SQL>
SQL>
/*******************************************************************************************************************************
The BEGIN/END Block
*******************************************************************************************************************************/
SQL> BEGIN
2 insert into counselor values(11,'Abigail','Smith','(508) 244-6931');
3 insert into counselor values(12,'Leslie','Wright','(508) 244-5586');
4 END;
5 /
PL/SQL procedure successfully completed.
SQL> select * from counselor;
COUNSELOR_ID FIRST_NAME LAST_NAME PHONE
------------ -------------------- -------------------- --------------
10 Quinn Fowler (508) 244-1416
11 Abigail Smith (508) 244-6931
12 Leslie Wright (508) 244-5586
SQL>
SQL>
/*******************************************************************************************************************************
Writing text to the console
*******************************************************************************************************************************/
SQL> BEGIN
2 DBMS_OUTPUT.put_line ('Hello world');
3 END;
4 /
PL/SQL procedure successfully completed.
SQL> SET ServerOutPut ON FORMAT WRAPPED
SQL> BEGIN
2 DBMS_OUTPUT.put_line ('Hello world');
3 END;
4 /
Hello world
PL/SQL procedure successfully completed.
SQL>
SQL>
/*******************************************************************************************************************************
Adding a Variable
*******************************************************************************************************************************/
SQL> DECLARE
2 v_message varchar(20);
3 BEGIN
4 v_message := 'Hello Earth' ;
5 DBMS_OUTPUT.put_line (v_message);
6 END;
7 /
Hello Earth
PL/SQL procedure successfully completed.
SQL>
SQL>
/*******************************************************************************************************************************
Saving the BEGIN/END Block as a Procedure
*******************************************************************************************************************************/
SQL> CREATE OR REPLACE PROCEDURE hello_world
2 AS
3 v_message varchar(20);
4 BEGIN
5 v_message := 'Hello Earth' ;
6 DBMS_OUTPUT.put_line (v_message);
7 END;
8 /
Procedure created.
SQL> EXEC hello_world;
Hello Earth
PL/SQL procedure successfully completed.
SQL>
SQL>
/*******************************************************************************************************************************
Listing all of your Stored Procedures
*******************************************************************************************************************************/
SQL> SELECT object_name
2 FROM all_objects
3 WHERE owner = 'GOVE'
4 and object_type = 'PROCEDURE';
OBJECT_NAME
------------------------------
HELLO_WORLD
SQL> exit
SQL> exit