Difference between procedure and stored procedures?
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.