ORACLE常用SQL、存储过程和函数

--******************在command窗口执行   BEGIN****************
--连接用户
CONNECT HENING/orcl;

--显示当前连接用户
SHOW USER;

--查询表结构
DESC MYUSER;

--回滚
ROLLBACK;

--显示SQL执行时间  在SQL执行结束后会有时间显示
SET TIMING ON;
SELECT * FROM TABLE_NAME;

--执行存储过程
EXECUTE PROC_1;

--如果存储过程中要打印一句话，执行存储过程之前，先执行这句话
SET SERVEROUTPUT ON;

--*****************在command窗口执行   END****************

--创建用户
CREATE USER HENING IDENTIFIED BY orcl;
--为用户授权
GRANT CONNECT,RESOURCE,DBA TO HENING;

--查询当前用户的所有表
SELECT * FROM USER_TABLES;

--查询当前数据库中的所有表
SELECT * FROM ALL_TABLES;
--查询当前数据库的所有用户
SELECT * FROM ALL_USERS;
--查询当前数据库中所有表字段
SELECT * FROM ALL_TAB_COLUMNS;

--查询当前用户下的所有表字段
SELECT * FROM USER_TAB_COLUMNS;

--查询数据库实例  具有管理员权限能查
SELECT * FROM V$INSTANCE;


--分页查询
SELECT *
  FROM (SELECT T.*, ROWNUM RN
          FROM (SELECT * FROM AREA_DICT ORDER BY SERIAL_NO ASC) T
         WHERE ROWNUM <= 100)
  WHERE RN >= 1;
 
SELECT *
  FROM (SELECT T.*, ROWNUM RN
          FROM (SELECT * FROM AREA_DICT ORDER BY SERIAL_NO ASC) T)
  WHERE RN BETWEEN 1 AND 100;

SELECT DISTINCT OWNER FROM ALL_TABLES;
SELECT * FROM ALL_TABLES WHERE TABLE_NAME = 'ALL_USERS'

--创建一张表
CREATE TABLE MYUSER (
  ID INT PRIMARY KEY,
  NAME VARCHAR2(20) NOT NULL,
  AGE INT DEFAULT 0,
  ADDRESS VARCHAR2(60),
  PHONE VARCHAR2(11)
)
CREATE TABLE MYGROUP(
  ID INTEGER PRIMARY KEY,
  GROUP_NAME VARCHAR2(20) NOT NULL
)

--插入一条记录
INSERT INTO MYUSER VALUES(1,'何佳',24,'北京昌平区回龙观小区','13503625697',1);
INSERT INTO MYUSER(NAME,AGE,ID,ADDRESS) VALUES('王刚',38,2,'天津塘沽口');
INSERT INTO MYGROUP VALUES(3,'第三小组');
--删除一条记录
DELETE FROM MYUSER WHERE ID = 2;
--修改一条记录
UPDATE MYUSER SET ADDRESS = '北京朝阳区' WHERE ID = 1;
--查询表记录
SELECT * FROM MYUSER ORDER BY ID ASC;
--删除表结构
DROP TABLE STUDENT;


--创建主键约束
ALTER TABLE MYUSER ADD CONSTRAINT PK_MYUSER PRIMARY KEY(ID);
--创建外键约束
ALTER TABLE MYUSER ADD CONSTRAINT FK_MYUSER_MYGROUP FOREIGN KEY(GROUPID) REFERENCES MYGROUP(ID);
--删除约束
ALTER TABLE TABLE_NAME DROP CONSTRAINT CONSTRAINT_NAME


--添加字段的语法：alter table tablename add (column datatype [default value][null/not null],….);
ALTER TABLE MYUSER ADD (GROUPID INTEGER DEFAULT NULL);
--修改字段的语法：alter table tablename modify (column datatype [default value][null/not null],….);
ALTER TABLE MYUSER MODIFY (GROUPID VARCHAR2(32));
--删除字段的语法：alter table tablename drop (column);
ALTER TABLE MYUSER DROP (GROUPID);

--左外链接
SELECT U.ID,U.NAME,G.GROUP_NAME FROM MYUSER U LEFT OUTER JOIN MYGROUP G ON U.GROUPID = G.ID;
--右外链接
SELECT G.GROUP_NAME,U.NAME FROM MYUSER U RIGHT OUTER JOIN MYGROUP G ON U.GROUPID = G.ID;


--****************存储过程PROCEDURES  START**************
CREATE OR REPLACE PROCEDURE PROC_1
IS
BEGIN
  dbms_output.put_line(11);
END;

CREATE OR REPLACE PROCEDURE PROC_2(SHU IN INTEGER)
IS
BEGIN
  DBMS_OUTPUT.PUT_LINE(SHU);
END;

CREATE OR REPLACE PROCEDURE PROC_3(SHU1 IN INTEGER,SHU2 IN INTEGER)
IS
RES INTEGER DEFAULT 0;
BEGIN
  DBMS_OUTPUT.PUT_LINE(RES);
  RES := SHU1 + SHU2;
  DBMS_OUTPUT.PUT_LINE(RES);
END;

/*
使用INTO获取值，只能返回一行。
游标属性：
%FOUND：变量最后从游标中获取记录的时候，在结果集中找到了记录。
%NOTFOUND：变量最后从游标中获取记录的时候，在结果集中没有找到记录。
%ROWCOUNT：当前时刻已经从游标中获取的记录数量。
%ISOPEN：是否打开
*/
CREATE OR REPLACE PROCEDURE PROC_CUR
IS
CURSOR CUR IS SELECT NAME FROM MYUSER;
USERNAME VARCHAR2(20);
BEGIN
  OPEN CUR;
  FETCH CUR INTO USERNAME;

  LOOP
    IF CUR%FOUND THEN
      DBMS_OUTPUT.PUT_LINE(USERNAME);
      FETCH CUR INTO USERNAME;
    ELSIF CUR%NOTFOUND THEN
      EXIT;  ---exit loop, not IF
    END IF;
  END LOOP;

  IF CUR%ISOPEN THEN
    CLOSE CUR;
  END IF;

END;

--游标：遍历每行数据
CREATE OR REPLACE PROCEDURE PROC_CUR2
IS
CURSOR CUR IS SELECT * FROM MYUSER;
U MYUSER%ROWTYPE;
BEGIN
  OPEN CUR;
  FETCH CUR INTO U;

  LOOP
    IF CUR%FOUND THEN
      DBMS_OUTPUT.PUT_LINE(U.ID||':'||U.NAME);
      FETCH CUR INTO U;
    ELSIF CUR%NOTFOUND THEN
      EXIT;  ---exit loop, not IF
    END IF;
  END LOOP;

  IF CUR%ISOPEN THEN
    CLOSE CUR;
  END IF;

END;

-- FOR循环 
-- reverse大到小,跟在IN后面
CREATE OR REPLACE PROCEDURE PROC_FOR
IS
BEGIN
  FOR I IN 1..100   --后面没有分号
  LOOP
    INSERT INTO MYUSER VALUES(I,'何佳',24,'北京昌平区回龙观小区','13503625697',1);
    IF MOD(I,20) = 0 THEN 
      COMMIT;
    END IF;
  END LOOP;
  COMMIT;
END;

--IF
CREATE OR REPLACE PROCEDURE PROC_DYNAMIC(I IN INTEGER)
AS
BEGIN
  IF I>10 THEN
    DBMS_OUTPUT.PUT_LINE('I大于10');
  ELSE
   DBMS_OUTPUT.PUT_LINE('I不大于10');
  END IF;
END;

--VARCHAR2作为参数时，不用加长度
CREATE OR REPLACE PROCEDURE PROC_DYNAMIC2(STR IN VARCHAR2)
AS
BEGIN
 IF STR = 'HN' THEN
    DBMS_OUTPUT.PUT_LINE('STR=HN');
  ELSE
   DBMS_OUTPUT.PUT_LINE('STR<>HN');
  END IF;
END;

CREATE OR REPLACE PROCEDURE PROC_DYNAMIC3(STR IN VARCHAR2)
AS
BEGIN
 IF STR = 'HN' THEN
    DBMS_OUTPUT.PUT_LINE('HN');
  ELSIF STR = 'YX' THEN
   DBMS_OUTPUT.PUT_LINE('YX');
  END IF;
END;

--WHILE
CREATE OR REPLACE PROCEDURE PROC_WHILE
IS
N INTEGER;
I INTEGER;
BEGIN
  N := 0;
  I := 10;
  WHILE I>1 LOOP
    N := I*(I-1) + N;
    I := I-1;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE(N);
  
END;

--参数I不能直接使用，只能赋给另一个变量，不知道为什么
CREATE OR REPLACE PROCEDURE PROC_WHILE2(I IN INTEGER)
IS
N INTEGER;
I1 INTEGER;
BEGIN
  I1 := I;
  N := 0;
  WHILE I1>1 LOOP
    N := I1*(I1-1) + N;
    I1 := I1-1;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE(N);
  
END;

--PROC_RETURN
CREATE OR REPLACE PROCEDURE PROC_RETURN(NUM_OUT OUT INTEGER)
IS
N INTEGER;
I INTEGER;
BEGIN
  N := 0;
  I := 10;
  WHILE I>1 LOOP
    N := I*(I-1) + N;
    I := I-1;
  END LOOP;
  
  NUM_OUT := N;
  
END;

--PROC_WHILE3
CREATE OR REPLACE PROCEDURE PROC_WHILE3(I IN INTEGER)
AS
N INTEGER;
BEGIN
  N := 0;
  IF (I IS NOT NULL) THEN
    WHILE I>1 LOOP
      N := I*(I-1) + N;
      I := I-1;
    END LOOP;
  DBMS_OUTPUT.PUT_LINE(N);
  
END;

--存储过程分页查询，并返回结果集，只能用SQL PLUS测试
--创建PACKAGE
CREATE OR REPLACE PACKAGE HIS_PAGE
AS
    TYPE HIS_CURSOR IS REF CURSOR;
END;
--创建分页存储过程
CREATE OR REPLACE PROCEDURE PROC_PAGE(TABLE_NAME  IN VARCHAR2,
                                      PAGESIZE    IN NUMBER,
                                      PAGECOUNT   IN NUMBER,
                                      PAGE_CURSOR OUT HIS_PAGE.HIS_CURSOR,
                                      ALLSIZE     OUT NUMBER,
                                      ALLPAGE     OUT NUMBER) AS
  START_SIZE NUMBER;
  END_SIZE   NUMBER;
  SQLSTR     VARCHAR2(2000);
  SQLSTR2    VARCHAR2(2000);
BEGIN
  START_SIZE := (PAGECOUNT - 1) * PAGESIZE + 1;
  END_SIZE   := START_SIZE + PAGESIZE;
  SQLSTR     := 'SELECT * FROM (SELECT T.*, ROWNUM RN FROM (SELECT * FROM ' ||
                TABLE_NAME || ') T WHERE ROWNUM < ' || END_SIZE ||
                ') WHERE RN >= ' || START_SIZE;
  OPEN PAGE_CURSOR FOR SQLSTR;
  SQLSTR2 := 'select count(*) from ' || TABLE_NAME;
  EXECUTE IMMEDIATE SQLSTR2
    INTO ALLSIZE;
  IF MOD(ALLSIZE, PAGESIZE) = 0 THEN
    ALLPAGE := ALLSIZE / PAGESIZE;
  ELSE
    ALLPAGE := ALLSIZE / PAGESIZE + 1;
  END IF;

END;

--存储过程创建表
CREATE OR REPLACE PROCEDURE PROC_CREATE_TABLE AUTHID CURRENT_USER
IS
SQL_STR VARCHAR2(1000);
BEGIN
    SQL_STR := 'CREATE TABLE STUDENT (ID INT PRIMARY KEY ,NAME VARCHAR2(20),ADDRESS VARCHAR2(50))';
    DBMS_OUTPUT.PUT_LINE(SQL_STR);
    EXECUTE IMMEDIATE(SQL_STR);
END;

--查询当前用户下的存储过程
SELECT * FROM USER_PROCEDURES;
--查询当前数据库中所有存储过程
SELECT * FROM ALL_PROCEDURES;

--*************存储过程PROCEDURES  END************

--*************视图VIEWS  START*******************
--将创建视图的权限授权给HIS
GRANT CREATE ANY VIEW TO HIS;

--创建视图
CREATE OR REPLACE VIEW USER_GROUP AS
  SELECT U.NAME,U.AGE,U.ADDRESS,G.GROUP_NAME FROM MYUSER U,MYGROUP G WHERE U.GROUPID = G.ID;

--查询视图
SELECT * FROM USER_GROUP;
--删除视图
DROP VIEW USER_GROUP;

--查询当前用户下所有视图
SELECT * FROM USER_VIEWS;
--查询当前数据库中的所有视图
SELECT * FROM ALL_VIEWS;

--****************视图VIEWS  END************

--*****************索引INDEX  START*********



--*****************索引INDEX  END***********

--*****************ORACLE函数  START********
--1--ASCII  返回与指定的字符对应的十进制数
SELECT ASCII('A') A,ASCII('B') B,ASCII('b') b,ASCII(' ') SPACE FROM DUAL;

--2--CHR  给出整数,返回对应的字符
SELECT CHR(54740) ZHAO,CHR(65) A FROM DUAL;

--3--CONCAT  连接两个字符串
SELECT CONCAT('AA','bb') AABB FROM DUAL;

--4--INITCAP  返回字符串并将字符串的第一个字母变为大写
SELECT INITCAP('abcd') UPP FROM DUAL;

--5--INSTR(C1,C2,I,J)  在一个字符串中搜索指定的字符,返回发现指定的字符的位置
--C1    被搜索的字符串
--C2    希望搜索的字符串
--I     搜索的开始位置,默认为1
--J     出现的位置,默认为1
SELECT INSTR('oracle traning','ra',1,2) INSTRING FROM DUAL;

--6--LENGTH  返回字符串的长度
SELECT LENGTH('ORACLE') LE FROM DUAL;

--7--LOWER 返回字符串,并将所有的字符小写
SELECT LOWER('AaBbCc') abc FROM DUAL;

--8--UPPER  返回字符串,并将所有的字符大写
SELECT UPPER('ABC') ABC FROM DUAL;

--9--RPAD和LPAD(粘贴字符)  不够字符则用*来填满
--RPAD  在列的右边粘贴字符
--LPAD  在列的左边粘贴字符
SELECT RPAD('HING',10,'*') FROM DUAL;
SELECT LPAD('HING',10,'*') FROM DUAL;

--10--LTRIM和RTRIM
--LTRIM  删除左边出现的字符串
--RTRIM  删除右边出现的字符串
SELECT LTRIM(RTRIM('   CAI YUN ZHI NAN   ',' '),' ') CYZN FROM DUAL;

--11--SUBSTR(string,start,count)  取子字符串,从start开始,取count个
SELECT SUBSTR('13088888888',3,8) FROM DUAL;

--12--REPLACE('string','s1','s2')
--string   希望被替换的字符或变量 
--s1       被替换的字符串
--s2       要替换的字符串
SELECT REPLACE('He love you!','He','I') ILY FROM DUAL;

--13--SOUNDEX  返回一个与给定的字符串读音相同的字符串
create table table1(xm varchar(8));
insert into table1 values('weather');
insert into table1 values('wether');
insert into table1 values('gao');
select xm from table1 where soundex(xm)=soundex('weather');

--14--TRIM('s' from 'string')  
--LEADING   剪掉前面的字符
--TRAILING  剪掉后面的字符
--如果不指定,默认为空格符

--15--ABS 返回指定值的绝对值
SELECT ABS(100),ABS(-100) FROM DUAL;

--16--ACOS 给出反余弦的值
SELECT ACOS(-1) FROM DUAL;

--17--ASIN 给出反正弦的值
SELECT ASIN(0.5) FROM DUAL;

--18--ATAN 返回一个数字的反正切值
SELECT ATAN(1) FROM DUAL;

--19--CEIL 返回大于或等于给出数字的最小整数
SELECT CEIL(3.1415) FROM DUAL;

--20--COS 返回一个给定数字的余弦
SELECT COS(-3.141592653) FROM DUAL;

--21--COSH 返回一个数字反余弦值
SELECT COSH(10) FROM DUAL;

--22--EXP 返回一个数字e的n次方根
SELECT EXP(10) FROM DUAL;

--23--FLOOR 对给定的数字取整数
SELECT FLOOR(3.1415) FROM DUAL;

--24--LN	返回一个数字的对数值
SELECT LN(1) FROM DUAL;

--25--LOG(n1,n2) 返回一个以n1为底n2的对数
SELECT LOG(2,4) FROM DUAL;

--26--MOD(n1,n2) 返回一个n1除以n2的余数
SELECT MOD(10,3) FROM DUAL;

--27--POWER(n1,n2) 返回n1的n2次方根
SELECT POWER(2,3) FROM DUAL;

--28--ROUND和TRUNC 按照指定的精度进行舍入
SELECT ROUND(3.14),TRUNC(3.14) FROM DUAL;

--29--SIGN	取数字n的符号,大于0返回1,小于0返回-1,等于0返回0
SELECT SIGN(10),SIGN(-10) FROM DUAL;

--30--SIN 返回一个数字的正弦值
SELECT SIN(1.57079) FROM DUAL;

--31--SIGH 返回双曲正弦的值
SELECT SIN(20),SINH(20) FROM DUAL;

--32--SQRT 返回数字n的根
SELECT SQRT(16) FROM DUAL;

--33--TAN 返回数字的正切值

--34--TANH 返回数字n的双曲正切值

--35--TRUNC 按照指定的精度截取一个数

--36--ADD_MONTHS 增加或减去月份
select to_char(add_months(to_date('199912','yyyymm'),2),'yyyymm') from dual;

--37--LAST_DAY 返回日期的最后一天
select to_char(sysdate,'yyyy.mm.dd'),to_char((sysdate)+1,'yyyy.mm.dd') from dual;

--38--MONTHS_BETWEEN(date2,date1) 给出date2-date1的月份
select months_between('19-12月-1999','19-3月-1999') mon_between from dual;

--39--NEW_TIME(date,'this','that') 给出在this时区=other时区的日期和时间
 select to_char(sysdate,'yyyy.mm.dd hh24:mi:ss') bj_time,to_char(new_time
  (sysdate,'PDT','GMT'),'yyyy.mm.dd hh24:mi:ss') los_angles from dual;

--40--NEXT_DAY(date,'day') 给出日期date和星期x之后计算下一个星期的日期
select next_day('18-5月-2001','星期五') next_day from dual;

--41--SYSDATE 用来得到系统的当前日期
select to_char(sysdate,'dd-mm-yyyy day') from dual;

--42--CHARTOROWID 将字符数据类型转换为ROWID类型
select rowid,rowidtochar(rowid),ename from scott.emp;

--43--CONVERT(c,dset,sset) 将源字符串 sset从一个语言字符集转换到另一个目的dset字符集
select convert('strutz','we8hp','f7dec') "conversion" from dual;

--44--HEXTORAW 将一个十六进制构成的字符串转换为二进制

--45--RAWTOHEXT 将一个二进制构成的字符串转换为十六进制

--46--ROWIDTOCHAR 将ROWID数据类型转换为字符类型

--47--TO_CHAR(date,'format')
select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') from dual;

--48--TO_DATE(string,'format') 将字符串转化为ORACLE中的一个日期

--49--TO_MULTI_BYTE 将字符串中的单字节字符转化为多字节字符
select to_multi_byte('高') from dual;

--50--TO_NUMBER 将给出的字符转换为数字
select to_number('1999') year from dual;

--51--BFILENAME(dir,file) 指定一个外部二进制文件
insert into file_tb1 values(bfilename('lob_dir1','image1.gif'));

--52--CONVERT('x','desc','source') 将x字段或变量的源source转换为desc

--53--GREATEST 返回一组表达式中的最大值,即比较字符的编码大小.
select greatest('AA','AB','AC') from dual;

--56--LEAST 返回一组表达式中的最小值 
select least('啊','安','天') from dual;

--*****************ORACLE函数  END**********


--解决：ORA-01536: 超出表空间 'USERS' 的空间限额
ALTER USER HIS QUOTA UNLIMITED ON USERS;

SELECT SYS_CONTEXT('USERENV','TERMINAL') MASTER_NAME FROM DUAL;