answersLogoWhite

0

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.

User Avatar

Wiki User

13y ago

What else can I help you with?

Related Questions

What is the difference between a stored database procedure and a batch of SQL statements submitted by a client application?

Stored procedures prevent unauthorized access to data


Can You Write Trigger in Stored Procedures?

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.


Difference between a local procedure and stored procedure?

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


What is the examples of Stored procedure?

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.


What are stored procedures in data bases?

Stored procedure is the pl-sql block in precomplile from and can be used to excecute plsql statement


How do you create procedures in MySQL?

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.


What is the difference between function and stored procedure?

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.


What is the difference between stored procedure and index?

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


What are the procedures in rdbms?

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.


What is the difference in English Language between a wardrobe and a cupboard?

Clothing is stored in a wardrobe. Dishes are stored in a cupboard.


What is Advantages and disadvantages of stored procedures?

1> Poor Exception handling 2>There are no debuggers available for stored procedures Siva.Pachigolla


How to draw E-R diagram for library management system?

how to draw the er diagram of library management system