常见问题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 b asp0099 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;

常见问题1000例Oracle(一)