ali raza

C#, .Net ,Asp.net and my thoughts

Category Archives: PL Sql

How to insert using Cursor in Oracle

Being a .net technologies guy I never be go on database level. but last night I have to face a small problem . I have to done 50000 record entries in one of table with relation to parent table. I used Pl Sql cursor. You already know much about Cursor they exists both in Oracle and Sql server and even My Sql. Following is a solution of insertion
Using Cusros so I insert 50000  in child table with respect to parent table..

DECLARE

/* Output variables to hold the result of the query: */

a Teacher.teacher_code %TYPE;

/* Cursor declaration: */

CURSOR T1Cursor IS
SELECT teacher_Code

FROM Teacher

FOR UPDATE;

BEGIN

OPEN T1Cursor;

LOOP

/* Retrieve each row of the result of the above query

into PL/SQL variables: */

FETCH T1Cursor INTO a;

/* If there are no more rows to fetch, exit the loop: */

EXIT WHEN T1Cursor%NOTFOUND;

/* Delete the current tuple: */

/* Insert the reverse tuple: */

INSERT INTO Teacher_ChildTable (Teacher_code,Class_code,Teaching_Mode,Period)    VALUES(a,’0324′,’01’,450);

END LOOP;

/* Free cursor used by the query. */

CLOSE T1Cursor;

END;

Have a nice day.

Pl Sql Built in Functions

hi find these build in function for orcale pl sql , These are really usefull

Function Description
String Functions
upper(s), lower(s) convert string s to upper/lower-case
initcap(s) capitalise first letter of each word
ltrim(s), rtrim(s) remove blank char. from left/right
substr(s,start,len) sub-string of length len from positionstart
length(s) length of s
Date Functions
sysdate current date (on Oracle server)
to_date(date, format) date formatting
Number Functions
round(x) round real number x to integer
mod(n,p) n modulus p
abs(x) absolute value of x
dbms_random.random() generate a random integer
Type Conversion Functions
to_char() convert to string
to_date() convert to date
to_number() convert to number
Miscellaneous Functions
user current Oracle user