常见问题1000例Oracle
---basic table
select * from tbaadm.gam
--group by
select ACID,DEL_FLG,ACCT_NAME,count(DR_BAL_LIM) FROM tbaadm.gam group by ACID; --wrong suach as count min max avg do not toghter with three or three blow column
select ACID, count(DR_BAL_LIM) FROM tbaadm.gam group by ACID;--right
---group by
select ACID,count(DEL_FLG),count(TOT_MOD_TIMES), sum(TOT_MOD_TIMES) FROM tbaadm.gam group by ACID;
select ACID,count(DEL_FLG),count(TOT_MOD_TIMES), sum(TOT_MOD_TIMES) FROM tbaadm.gam group by FORACID;--WRONG group by must following the select field
-- having condition group by
select ACID,count(DEL_FLG),count(TOT_MOD_TIMES) as tot_mod_count, sum(TOT_MOD_TIMES) FROM tbaadm.gam having count(TOT_MOD_TIMES)>1 group by ACID;
-- ordery by
select ACID,DEl_FLG,TOT_MOD_TIMES FROM tbaadm.gam ORDER BY ACID
select ACID,DEl_FLG,TOT_MOD_TIMES FROM tbaadm.gam ORDER BY FORACID
--MIN MAX
select ACID,DEl_FLG,MIN(TOT_MOD_TIMES) MIN,TOT_MOD_TIMES FROM tbaadm.gam ORDER BY ACID --wrong must have two column
select ACID,MIN(TOT_MOD_TIMES) MIN FROM tbaadm.gam group BY ACID
select ACID,MAX(TOT_MOD_TIMES) MAX1 FROM tbaadm.gam group BY ACID
--DISTINCT
select distinct ACID,DEl_FLG,TOT_MOD_TIMES FROM tbaadm.gam ORDER BY ACID;
select ACID,distinct DEl_FLG,TOT_MOD_TIMES FROM tbaadm.gam ORDER BY ACID --wrong the distinct must be put in the first postion.
--group by must be put in front of the order by and limit
--avg(distinct|all)
select ACID,avg(TOT_MOD_TIMES) FROM tbaadm.gam group BY ACID;
select ACID,avg(distinct TOT_MOD_TIMES) FROM tbaadm.gam group BY ACID;
select ACID,avg(all TOT_MOD_TIMES) FROM tbaadm.gam group BY ACID;
--- userenv
select userenv('language') from dual;
select userenv('sessionid') from dual;
select user from dual;
--type use guide
create or replace type bartype as object(
name varchar2(20),
addr varchar2(20)
);
/
create table Bars of BarType;
-- create a procedure to farmat the string
CREATE OR REPLACE PACKAGE basp0099 AS
TYPE ArrayType IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;
PROCEDURE formInputArr (inputStr IN VARCHAR2,
inputArr OUT ArrayType);
END basp0099;
CREATE OR REPLACE PACKAGE BODY basp0099 AS
-- ***********************************************************
PROCEDURE formInputArr (inputStr IN VARCHAR2,
inputArr OUT ArrayType
) AS
inpArr ArrayType;
tempStr VARCHAR2(500 char);
i NUMBER := 0;
delimPos NUMBER := 0;
BEGIN --{
-- DBMS_OUTPUT.PUT_LINE ('basp0099 : inside formInputArr');
-- DBMS_OUTPUT.PUT_LINE ('basp0099 : inputStr [' || inputStr || ']');
tempStr := inputStr;
LOOP -- {
delimPos := INSTR (tempStr, '!');
IF ((delimPos = 0) OR (delimPos IS NULL)) THEN
inpArr(i) := tempStr ;
EXIT;
ELSE
inpArr(i) := SUBSTR (tempStr, 1, delimPos - 1);
tempStr := SUBSTR (tempStr, delimPos + 1);
END IF;
i := i + 1;
END LOOP; -- }
-- for j in 0..i LOOP
-- DBMS_OUTPUT.PUT_LINE ('basp0099 : inpArr(' || j || ') [' || inpArr(j) || ']');
-- END LOOP;
inputArr := inpArr;
END formInputArr;
-- ***********************************************************
END basp0099 ;
--------------------------------------------------------------------------------
-- Function : getCountOfChar()
-- Desc : Returns the no of times a character exists in a string(string, char)
--------------------------------------------------------------------------------
FUNCTION getCountOfChar(lv_string VARCHAR2,lv_char CHAR )
RETURN NUMBER;
FUNCTION getCountOfChar(lv_string VARCHAR2,lv_char CHAR)
RETURN NUMBER AS
lv_input_length NUMBER;
lv_index NUMBER := 1;
lv_count_of_char NUMBER := 0;
BEGIN
BEGIN
lv_input_length := LENGTH(lv_string);
WHILE lv_index <= lv_input_length
LOOP
IF(SUBSTR(lv_string, lv_index, 1) = 'U') THEN
--{
lv_count_of_char := lv_count_of_char +1 ;
--}
END IF;
lv_index := lv_index + 1;
END LOOP;
END;
RETURN lv_count_of_char;
END getCountOfChar;
-----------------------------------------------------------------------------------------------------------
--- Function : eabBal()
--- Desc : Takes Acid and As On Date as input and returns
--- : Outstanding Balance of the Acid based on the input Dates
-----------------------------------------------------------------------------------------------------------
FUNCTION eabBal(lv_acid varchar2,
lv_as_on_date DATE,bankId varchar2) RETURN NUMBER is
outstanding_amount number;
BEGIN
--{
BEGIN
SELECT nvl(tran_date_bal,0)
INTO outstanding_amount
FROM tbaadm.EAB
WHERE EAB.acid = lv_acid
AND eod_date <= lv_as_on_date
AND end_eod_date >= lv_as_on_date
AND bank_id = bankId ;
EXCEPTION
WHEN NO_DATA
