(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
Open WFAP
Place two textboxes and a button
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
- 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
Open WFAP
Open Tool box
right click inside of general tab
choose Items
Select data gridà ok
Place a button and data grid control on the form using system. Data. oleDb;
Code 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);
Data gridl. data source=ds;
(or)
Data Grid view1.Data source= ds. Tables[“d”];