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”];