Difference between procedure and stored procedures?

Answer 1

STORED PROCEDURE :

A stored procedure can be created with no parameters, IN parameters, OUT parameters, or IN/OUT parameters.There can be many parameters per stored procedure.

It is also called as a FUNCTION.

EXAMPLE:

1.

-- Hello World in Oracle PL/SQL (stored procedure)

SET SERVEROUTPUT ON

BEGIN

dbms_output.enable(10000); dbms_output.put_line('Hello World');

END;/

OUTPUT : Hello World

2.

SET SERVEROUTPUT ON

BEGINDBMS_OUTPUT.ENABLE;DBMS_OUTPUT.PUT_LINE('Hello World');END;/

SET SERVEROUTPUT OFF

3.

DECLARE

annapurna VARCHAR2(20) := &'annapurna'; -- variable with a value assignment HOURLY_WAGE NUMBER := 5.15; -- until congress changes it HOURLY_WAGE NUMBER DEFAULT 5.15; --using defaultHOURLY_WAGE NUMBER NOT NULL := 5.15; --using not null -- default n not null both r having same functionality -- but, not null requires a valueBEGINDBMS_OUTPUT.PUT_LINE('Hello ' annapurna '!!!');END;/

4.SET SERVER OUTPUT ON

BEGIN DBMS_OUTPUT.ENABLE; DBMS_OUTPUT.PUT_LINE('hello miSS'); END;/

5.

-- calling of a PL/SQL function

SET SERVEROUTPUT ON SIZE 1000000

DECLARE msg VARCHAR2(30);BEGIN msg := message_for_the_world; DBMS_OUTPUT.PUT_LINE(msg);END;/

6.

-- a simple PL/SQL function to return a string

CREATE OR REPLACE FUNCTION message_for_the_worldRETURN VARCHAR2ASBEGIN RETURN 'hello, world';END;/

SHOW ERRORS

TRIGGER :

Ii is a fragment of code that tells Oracle to fire or run BEFORE or AFTER a table is modified.

ADVANTAGE : It has the power to make sure that a column is filled in with default information make sure that an audit row is inserted into another table after finding that the new information is inconsistent with other stuff in the database.

Example :

1.

create table my_trigger ( comment_id integer primary key, on_what_id integer not null, on_which_table varchar(50), user_id not null references users, comment_date date not null, ip_address varchar(50) not null, modified_date date not null, content clob, html_p char(1) default 'f' check(html_p in ('t','f')), approved_p char(1) default 't' check(approved_p in ('t','f')));/

SHOW ERRORS

2.

Banks Transactions , updations and ONLINE SHOPPING MALLS BUSINESS transactions are the best Examples for TRIGGERS.

One more thing according to my knowledge we write the TRIGGERS in STORED PROCEDURES.

Annapurna.

Answer 2

A procedure is a specified series of actions, acts or operations which have to be executed in the same manner in order to always obtain the same result under the same circumstances

A stored procedure is a subroutine available to applications accessing a relational database system. Stored procedures (sometimes called a proc, sproc, StoPro, or SP) are actually stored in the database data dictionary.

In a procedure you have to start the transaction manually, allowing the rollback manually and stuff like that.

In a stored procedure, usually the DBA system takes care of the main transaction in case of errors. You can even use atomic transactions to keep your information consistent.

Then, A stored procedure is execute a bit faster than a single procedure because of the indexing in the dba.