In a database management system, a stored procedure is a set of Structured Query Language (SQL) statements with an assigned name that's stored in the database in compiled form so that it can be shared by a number of programs. The use of stored procedures can be helpful in controlling access to data, preserving data integrity and improving productivity.
The Oracle PL/SQL language allows you to write procedure to centralize the business logic and store the code in the database.
I have created the following Stored Procedure on Oracle using TOAD. This particular Stored Procedure will Insert a Customer to the Customer Table. For the sake of simplicity, Customer Table contains only 4 columns (Customer ID, Customer Name, City and Contact No).
In this post I will show you how to create Oracle stored Procedure using TOAD. Connect to Oracle Database using TOAD. Click on Database menu and open Schema Browser.
In this example I have created a InsertCustomer Procedure which takes 4 parameters (p_cust_id, p_cust_name, p_cust_city, p_contact_no). These parameters are used in SQL Insert statement. After completing procedure statement, press F5 button to create the procedure.
Copy the above Code to SQL Tab in TOAD and press F9 to execute the PL/SQL block. Give the appropriate values to bind variables i.e. used in Procedure parameters.
Procedure executed successfully and values are inserted to the customer table
The Oracle PL/SQL language allows you to write procedure to centralize the business logic and store the code in the database.
I have created the following Stored Procedure on Oracle using TOAD. This particular Stored Procedure will Insert a Customer to the Customer Table. For the sake of simplicity, Customer Table contains only 4 columns (Customer ID, Customer Name, City and Contact No).
In this post I will show you how to create Oracle stored Procedure using TOAD. Connect to Oracle Database using TOAD. Click on Database menu and open Schema Browser.
In the Schema Browser open Procedure Tab and click on New button to create Procedure. New PL/SQL Object Create Options screen will appear. Select Object Type as Procedure, enter the New Object Name, leave the other options and click on OK to create the Procedure.
Basic structure of the Procedure will create automatically and this will appear in SQL Tab as shown below
In this example I have created a InsertCustomer Procedure which takes 4 parameters (p_cust_id, p_cust_name, p_cust_city, p_contact_no). These parameters are used in SQL Insert statement. After completing procedure statement, press F5 button to create the procedure.
Now your newly created InsertCustomer Procedure and its structure is showing under the Procedure Tab in Schema Browser.
I have created a PL/SQL block and in this PL/SQL block I use the InsertCustomer procedure to insert the values to the customer table. PL/SQL block code is given below.
| DECLARE max_cust_no number; BEGIN SELECT NVL(MAX(CUSTOMER_ID),0)+1 INTO max_cust_no from customer; insertcustomer(max_cust_no,:csutname,:custcity,:custcontact); END; |
Copy the above Code to SQL Tab in TOAD and press F9 to execute the PL/SQL block. Give the appropriate values to bind variables i.e. used in Procedure parameters.
Procedure executed successfully and values are inserted to the customer table
