PROCEDURE

If you have any queries please leave a message here
Your Message
×


A procedure is a module that performs one or more actions.

Syntax :

CREATE [OR REPLACE] PROCEDURE procedure_name
[(parameter_name [ IN | OUT | IN OUT ] type , parameter_name [ IN | OUT | IN OUT ] type , . . . . . )]
{ IS | AS }
BEGIN
< procedure_body >
END;
/

Run the procedure in oracle using the following command -
SQL> @ File_Location\File_name;

Execute the procedure in oracle using the following command -
SQL> exec Procedure_Name;

To display the output of a procedure in oracle use the following command at once -
SQL> set serveroutput on;


Example 1

The following example creates a simple procedure that displays the string 'Hello World!' on the screen when executed.

message.sql
CREATE OR REPLACE PROCEDURE greetings AS
BEGIN
dbms_output.put_line('Hello World!');
END;
/

Run the procedure "message.sql" using the following command -
SQL> @D:\procedures\message.sql

Execute this procedure using the following command -
SQL> exec greetings

Example 2

Create a procedure to transfer amount from account A to account B.

TABLE
account (account_number, customer_id, branch_id, balance)

transaction.sql
CREATE OR REPLACE PROCEDURE amount_transfer
(debit_account IN NUMBER, credit_account IN NUMBER, amount IN NUMBER) AS
BEGIN
UPDATE account SET balance=balance-amount WHERE account_number=debit_account;
UPDATE account SET balance=balance+amount WHERE account_number=credit_account;
COMMIT;
END;
/

Run the procedure "transaction.sql" using the following command -
SQL> @D:\procedures\transaction.sql

Execute this procedure using the following command -
SQL> exec amount_transfer(1005508022,1005540032,5000)


Example 3

Create a procedure to update "product" and "product_order" tables after supply a order by a supplier.

TABLE
product (product_id, product_name, unit_price, quantity)
product_order (order_number, product_id, supplier_id, quantity, order_status)

stockupdate.sql
CREATE OR REPLACE PROCEDURE stock_update
(orderno IN NUMBER, qty IN NUMBER) AS
id NUMBER(10)
BEGIN
SELECT product_id INTO id FROM product_order WHERE order_number=orderno;
UPDATE product SET quantity=quantity+qty WHERE product_id=id;
UPDATE product_order SET order_status='Complete' WHERE order_number=orderno;
COMMIT;
END;
/


Example 4

Create a procedure to print all details of an employee.

TABLE
employee (employee_id, employee_name, department_id, salary)

showdetails.sql
CREATE OR REPLACE PROCEDURE row_print
(empid IN VARCHAR) AS
erow employee%ROWTYPE;
BEGIN
SELECT * INTO erow FROM employee WHERE employee_id=empid;
dbms_output.put_line(erow.employee_id || '   ' || erow.employee_name || '   ' || erow.department_id || '   ' || erow.salary);
COMMIT;
END;
/

Run the procedure "showdetails.sql" using the following command -
SQL> @D:\procedures\showdetails.sql

Execute this procedure using the following command -
SQL> exec row_print('E-1020')


Example 5

Create a procedure to store all the SC, ST and OBC student details into a tempurary table.

TABLE
student(student_id, student_name, phone, degree, category)
temptable(student_id, student_name, phone, degree, category)

categorydivision.sql
CREATE OR REPLACE PROCEDURE category_division AS
strow student%ROWTYPE;
CURSOR stcursor is SELECT * FROM student;
BEGIN
OPEN stcursor;
LOOP
FETCH stcursor INTO strow;
EXIT WHEN stcursor%NOTFOUND;
IF (strow.category='SC') OR (strow.category='ST') OR (strow.category='OBC') THEN
INSERT INTO temptable VALUES(strow.student_id, strow.student_name, strow.phone, strow.degree, strow.category);
END IF;
END LOOP;
CLOSE stcursor;
COMMIT;
END;
/



ABOUT US

QuestionSolves.com is an educational website that helps worldwide students in solving computer education related queries.

Also, different software like Visual Studio, SQL Server, Oracle etc. are available to download in different versions.

Moreover, QuestionSolves.com provides solutions to your questions and assignments also.


MORE TOPIC


Windows Command

UNIX Command

IGNOU Assignment Solution

IGNOU Question Paper Solution

Solutions of Different Questions


WHAT WE DO


Website Devlopment

Training

Home Learning

Provide BCA, MCA Projects

Provide Assignment & Question Paper Solution


CONTACT US


Follow Us