Group Discounts available for 3+ students and Corporate Clients

Syntax to write a stored procedure in oracle

Create or replace procedure pname

(variable Direction data type, variable direction data type,….)  as number varchar2

Beginàkeyword

Statement;                         create procedure abc

Statement;

Statement;

End;àkey word

/(execute)

 

Sp Ex:-1

A SP for adding two numbers

àopen oracle software

àtype as follows at SQL Prompt

Create or replace procedure add 2(a in number, b in number, c out number)as

Begin

Bi direction. Operator

C:=a+b;

End;

[:=B1-Directional operator]

/

SP EX:-2

A Procedure for turning the spelling of a given number

Create or replace procedure Get spell(n in number, X out vachar2)as

Begin

To_ data(n, ’J’)X

Select  to – char(to-date(n,’J’),’JSP’) in to x

Upper case

From dual;

End;          logical  table which is created and destroyed at runtime

A Query to display all procedure names

Select* from user-object where

Object-type=’PROCEDURE’

 

Steps to execute a procedure at SQL prompt

Variable a varchar2(100)

Exec Get spell(98765,:a)

Print a;

Copy it to the sql prompt

 

Calling the stored procedure from c#.NET

 

Screenshot_6

 

 

Open WFAP

Place two textboxes and a button

 

Screenshot_7

 

Using system. data.ole Ob;

Code for button1_click

{

OleDb connection cn=new oleDb connection

(“Prorider = msdaora.1;user id = scott; password = tiger”);

Cn. Open();

oleDb command cmd= new oleDb command();

cmd. connection=();

cmd. command text=”Get spell”;

Procedure name

cmd. command type= command type stored procedure;

oleDb parameter P1,P2;

P1= cmd. Parameter Add(“a”, oleDb type. Integer);

P1.Direction = Parameter Direction. Input;

P1. Value = Int. parle(text box1. text);

P2= cmd. Parameter Add(“b”, oleDb type. Varchar,100);

P2.Direction = Parameter Direction. out put;

//for out put Parameter value is not allowed.

cmd. Execute non Query();

text box2.text = p2. Value. To string();

}

F5

 

 

 

Working with data relations

Screenshot_1

 

 

  • Data relation is a class, which is the part of dataset
  • Data relation class, is used to create the relation in between two tables with the belD 001 primary and foreign keys
  • Logical Link .between tow tables is called as relation
  • To create a Relation, fire things are required:-

(a) Parent table

(b) primary key

(c)Child table

(d)Foreign key

(e)Relation name

  • Data grid view control of .NET 3.5 is capable to display only one table at a time

Data grid control of  .NET 1.1 is capable to display more than one table also at a time

  • Data grid control is not available in the tool box by default, hence it need to be added

Screenshot_24 Open WFAP

Screenshot_24Open Tool boxScreenshot_24 right click inside of general tabScreenshot_24 choose ItemsScreenshot_24Select data gridà ok

Screenshot_24Place a button and data grid control on the form using system. Data. oleDb;

Screenshot_24Code for button 1 _click

{

oleDb connection cn = new oleDb connection

(“user id =scott; pass word = tiger; provider = msdaora.1”);

oleDb Data adapter dl=new oleDb Data Adapter

(“Select*from dept”,cn);

oleDb Data Adapter d2= new oleDb Data Adapter

(“Select*from emp”,cn);

Data set ds=new dataset();

dl.fill(ds,”d”);

d2.fill(ds,”e”)                     Allas names

Data Column prk = ds. Tables[“d”].Columns[“deptno”];

Data Column fk = ds. Tables[“e”].Columns[“dept no”];

Data relation dr=new data relation(“rel”, prk, fk);

Alias name for the relation

 

 

Ds. Relation. Add(dr);

 

Screenshot_8

 

Data gridl. data source=ds;

(or)

Data Grid view1.Data source= ds. Tables[“d”];

 

 

 

 

 

“At TekSlate, we are trying to create high quality tutorials and articles, if you think any information is incorrect or want to add anything to the article, please feel free to get in touch with us at info@tekslate.com, we will update the article in 24 hours.”

0 Responses on Syntax to write a stored procedure in oracle"

    Leave a Message

    Your email address will not be published. Required fields are marked *

    Support


    Please Enter Your Details and Query.
    Three + 6