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.
Stored procedures prevent unauthorized access to data
A trigger is a stored procedure. It is a special stored procedure that runs in response to some defined event, such as an insert into a table.
local procedure wont store in database. Stored procedure store in database permanently and we can use it whenever we require. Other program also can use this stored procedure. And the transaction of stored procedure take care by DBA. But the local procedure transaction is take care by manually only
sp_helpdb , sp_who2, sp_renamedb are a set of system defined stored procedures. We can also have user defined stored procedures which can be called in similar way.
Stored procedure is the pl-sql block in precomplile from and can be used to excecute plsql statement
One of the fastest ways to create a stored procedure is: In the database explorer, navigate to the Procedures node. Select New Procedure from the context menu. Specify the name of the procedure in the dialog box that opens and click "Create". dbForge Studio for MySQL is a big help while working with stored programs are stored procedures and functions, triggers and events.
we cant use DML operations in functions where as it is possible in sp. Procedure can return zero or n values whereas function can return one value which is mandatory. error handling can be done in sp, but not possible in function. functions can be called from select statements, where clause and case but not possible in sp. Procedures can have input,output parameters for it whereas functions can have only input parameters Functions can be called from procedure whereas procedures cannot be called from function. We can go for transaction management in procedure whereas we can't go in function.
As stored procedure in procedural code, which usually calculates or changes some data. An index is a database object which aids in quick location of records because it is internally ordered in a optimized way for the specific key
Procedures in an RDBMS are programs that are written to accomplish a set of functions that cannot be done using a single query. A procedure contains a sequence of SQL Queries that can be executed one after the other by invoking the procedure. There are two kinds of procedures Functions & Stored Procedures.
Clothing is stored in a wardrobe. Dishes are stored in a cupboard.
1> Poor Exception handling 2>There are no debuggers available for stored procedures Siva.Pachigolla
how to draw the er diagram of library management system