Oracle programming:
Sample Function and Sql procedure:
=====
CREATE OR REPLACE Procedure UpdateCourse ( name_in IN varchar2 )
IS cnumber number;
cursor c1 is
SELECT course_number
FROM courses_tbl
WHERE course_name = name_in;
BEGIN open c1;
fetch c1 into cnumber;
if c1%notfound then cnumber := 9999;
end if;
INSERT INTO student_courses ( course_name, course_number )
VALUES ( name_in, cnumber );
commit;
close c1;
EXCEPTION WHEN OTHERS THEN raise_application_error(-20001,'An error
was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;
==============
CREATE OR REPLACE PROCEDURE friends_analysis
AS BEGIN FOR i IN
(SELECT COUNT(*) cnt, gender FROM friend_name GROUP BY gender) LOOP
IF i.gender = 'M' THEN dbms_output.put_line('I have '||i.cnt||' male
friends.');
ELSIF i.gender = 'F' THEN
dbms_output.put_line('I have '||i.cnt||' female friends.'); END IF;
END LOOP; /* Assume the value in friend_name.friend_id represents the
order in which we became friends. */
FOR i IN
(SELECT first_name, middle_name, last_name FROM friend_name WHERE
friend_id = (SELECT MIN(friend_id) FROM friend_name ) )
LOOP dbms_output.put_line('Our oldest friend is '||i.first_name||'
'||i.middle_name||' '||i.last_name);
END LOOP;
FOR i IN
(SELECT first_name, middle_name, last_name FROM friend_name WHERE
friend_id = (SELECT MAX(friend_id) FROM friend_name ) ) LOOP
dbms_output.put_line('Our newest friend is '||i.first_name||'
'||i.middle_name||' '||i.last_name); END LOOP; END friends_analysis;
BEGIN -- Assign our function to a variable.
v_joey_phone := et_friend_phone_number('Joey','Tribiani');
dbms_output.put_line('Joey''s phone number is '||v_joey_phone);
-- Use our function in a select statement
FOR i IN (SELECT first_name, last_name,
get_friend_phone_number(first_name, last_name) telno FROM
friend_name) LOOP dbms_output.put_line(i.first_name||':
'||i.telno); END LOOP; END;
===================
select * from T318362.debit_29jun1 where account_num in(
select a.account_num from T318362.debit_29jun2 a, T318362.debit_29jun2 b
where a.account_num=b.account_num
and a.event_seq=b.event_seq
and a.event_attr_34!=b.event_attr_34)
union
select * from T318362.debit_29jun2 where account_num in(
select a.account_num from T318362.debit_29jun2 a, T318362.debit_29jun2 b
where a.account_num=b.account_num
and a.event_seq=b.event_seq
and a.event_attr_34!=b.event_attr_34)
============
SELECT BS.ACCOUNT_NUM||bs.event_seq,BILL_DTM,REVENUE_START_DAT PERIOD_FRM,REVENUE_END_DAT PERIOD_TO,SUM(REVENUE_MNY/1000) free_calls
FROM BILLSUMMARY BS,BILLDETAILS BD, REVENUECODE RC,ACCOUNTATTRIBUTES AA,T318362.debit_29jun1 d2
WHERE BD.REVENUE_CODE_ID=RC.REVENUE_CODE_ID
AND BS.BILL_SEQ=BD.BILL_SEQ
and bs.event_seq=d2.event_seq
AND BS.ACCOUNT_NUM=BD.ACCOUNT_NUM
AND BS.ACCOUNT_NUM=AA.ACCOUNT_NUM
and d2.account_num=bs.account_num
and bs.account_num='1013940876'
and flag is null
AND RC.REVENUE_CODE_NAME LIKE 'LL_DS%'
AND RC.REVENUE_CODE_DESC like 'Landline_Discount_Default_LL-FREE-CALLS%'
and bs.bill_version=(select max(bill_version) from billsummary bs1 where bs1.account_num=bs.account_num and bs1.bill_dtm=bs.bill_dtm)
and bs.bill_version=bd.bill_version
and bs.bill_status in ('1','7','8')
GROUP BY BS.ACCOUNT_NUM||bs.event_seq, BILL_DTM,REVENUE_START_DAT ,REVENUE_END_DAT
=====================
SELECT BS.ACCOUNT_NUM||bs.event_seq,BILL_DTM,REVENUE_START_DAT PERIOD_FRM,REVENUE_END_DAT PERIOD_TO,SUM(REVENUE_MNY/1000) free_calls
FROM BILLSUMMARY BS,BILLDETAILS BD, REVENUECODE RC,ACCOUNTATTRIBUTES AA,T318362.debit_29jun2 d2
WHERE BD.REVENUE_CODE_ID=RC.REVENUE_CODE_ID
AND BS.BILL_SEQ=BD.BILL_SEQ
and bs.event_seq=d2.event_seq
AND BS.ACCOUNT_NUM=BD.ACCOUNT_NUM
AND BS.ACCOUNT_NUM=AA.ACCOUNT_NUM
and d2.account_num=bs.account_num
and flag is null
AND RC.REVENUE_CODE_DESC LIKE 'Landline_Usage_%'
and bs.bill_version=(select max(bill_version) from billsummary bs1 where bs1.account_num=bs.account_num and bs1.bill_dtm=bs.bill_dtm)
and bs.bill_version=bd.bill_version
and bs.bill_status in ('1','7','8')
GROUP BY BS.ACCOUNT_NUM||bs.event_seq, BILL_DTM,REVENUE_START_DAT ,REVENUE_END_DAT
======================
---chkING OF DEBIT AMT for GJ-99 ACCOUNTS
select cde.event_type_id, cde.event_attr_24, sum(cde.event_cost_mny/1000) from costedevent cde
where cde.account_num='1014190563'
and cde.event_seq='2'
and cde.event_type_id not in('5','6')
and cde.event_ref not in
(select distinct cde1.event_ref from costedevent cde1 where
(cde1.event_attr_24 in ('Local LL BSNL','Local Landline BSNL','STD Intra Circle GT50 BSNL','STD Intra Circle LT50 BSNL'))/* or
(cde.event_attr_24 ='Local Cellular' and cde.event_attr_9 ='T')*/)
group by cde.event_type_id, cde.event_attr_24
=================
select cde.event_type_id, cde.event_attr_24, sum(cde.event_cost_mny/1000) from costedevent cde
where cde.account_num='1012948172'
and cde.event_seq='6'
and cde.event_type_id not in('5','6')
and cde.event_ref not in
(select distinct cde1.event_ref from costedevent cde1 where
(cde1.event_attr_24 in ('Local LL BSNL','Local Landline BSNL','STD Intra Circle GT50 BSNL','STD Intra Circle LT50 BSNL')) or
(cde.event_attr_24 ='Local Cellular' and cde.event_attr_9 ='T'))
group by cde.event_type_id, cde.event_attr_24
======================
Oracle Database Tools :
SQL*Plus (SQLPLUS)
Oracle Enterprise Manager
Oracle Data Migration Assistant
Export Utility (EXP)
Import Utility (IMP)
Oracle Net Manager
Choosing a Database Tool
Starting Database Tools
Using SQL*Loader
Using Windows Tools
Optional Windows Diagnostic and Tuning Utilities
Choosing a Database Tool
Export UtilityFoot 1
C:\> exp username password
EXP starts and prompts you for parameters. To obtain a list of these parameters, enter:
C:\> exp help=y
Import UtilityFoot 2
C:\> imp username password
IMP starts and prompts you for parameters. To obtain a list of these parameters, enter:
C:\> imp help=y
C:\> mig
To obtain a list of parameters, enter:
C:\> mig help=y
SQL*Loader (SQLLDR)
C:\> sqlldr
SQL*Loader displays
load data
infile 'example.dat' "fix 11"
into table example
fields terminated by ',' optionally enclosed by '"'
(col1, col2)
example.dat:
001, cd, 0002,fghi,
00003,lmn,
1, "pqrs",
0005,uvwx,
.png)
.png)
No comments:
Post a Comment