文档章节

Oracle11G基本操作

liu13430
 liu13430
发布于 2016/03/11 10:17
字数 3440
阅读 35
收藏 4


[1].创建表空间

CREATE TABLESPACE SYPRO_201310 DATAFILE 'F:\ORACLE_11G\oradata\orcl\SYPRO_201310.DBF' SIZE 10M AUTOEXTEND ON 

在指定的F盘下创建一个名SYPRO_201310.DBF的文件初始空间是10M当空间不足时自动增加

[2].删除表空间同时删除表空间所有硬盘上的物理文件

DROP TABLESPACE SYPRO_201310 INCLUDING CONTENTS AND DATAFILES 

[3].查看当前用户所有表空间

SELECT * FROM DBA_TABLESPACES 

[4].创建用户liutao 密码 liutao 默认表空间 SYPRO_201310 临时表空间 TEMP

CREATE USER liutao IDENTIFIED BY liutao DEFAULT TABLESPACE SYPRO_201310 TEMPORARY TABLESPACE  TEMP 

[5].用户授权-连接数据库的权限

GRANT CONNECT TO LIUTAO WITH ADMIN OPTION

[6].用户授权-数据库管理员DBA角色

GRANT DBA TO LIUTAO WITH ADMIN OPTION

-- 创建表空间

CREATE TABLESPACE BOAICRM_201310 DATAFILE 'F:\ORACLE_11G\oradata\orcl\BOAICRM_201310.DBF' SIZE 50M AUTOEXTEND ON ;

-- 创建用户

CREATE USER BOAI IDENTIFIED BY BOAI DEFAULT TABLESPACE BOAICRM_201310 TEMPORARY TABLESPACE TEMP ;

-- 用户授权

GRANT DBA TO BOAI WITH ADMIN OPTION ;

[7].删除用户

DROP USER LIUTAO

[8].删除登录数据库的权限

REVOKE CONNECT FROM LIUTAO

=========修改数据表的一些常用操作=========

A.快速复制表结构但不复制表中的数据的方法:

CREATE TABLE TB_STUDENT_NEW AS SELECT * FROM TB_STUDENT_OLD WHERE 1 = 2 ;

B.快速复制表结构和表中的数据的方法:

CREATE TABLE TB_STUDENT_NEW AS SELECT * FROM TB_STUDENT_OLD

[9].修改已创建的表-添加字段

ALTER TABLE TB_STUDENT ADD EMAIL VARCHAR2(50)

[10].修改已创建的表-重新定义字段属性

ALTER TABLE TB_STUDNET MODIFY EMAIL VARCHAR2(100)

[11].修改已创建的表-删除字段

ALTER TABLE TB_STUDNET DROP COLUMN EMAIL

[12].修改已创建的表-字段重命名

ALTER TABLE TB_STUDNET RENAME COLUMN EMAIL TO EMAIL_163

[13].修改已创建的表-表重命名

RENAME TB_STUDNET TO TD_STUDNET

[14].给表加注释

COMMENT ON TABLE TB_STUDNET IS '学生表'

[15].给字段加注释

COMMENT ON COLUMN TB_STUDENT.EMAIL IS '邮箱'

[16].删除表

DROP TABLE TB_STUDNET 保留回滚段可恢复

DROP TABLE TB_STUDNET Purge 删除后不可恢复

[17].恢复已删除的表-10G新特性

FlashBack TABLE TB_STUDNET TO BEFORE DROP

=========数据表的约束和维护=========

[18].添加约束

ALTER TABLE TB_STUDENT ADD CONSTRAINTS TB_STUDENT_ID_PK PRIMARY KEY ID 主键约束

ALTER TABLE TB_STUDENT ADD CONSTRAINTS TB_STUDENT_EMAIL_UK UNIQUE EMAIL 唯一性约束

ALTER TABLE TB_STUDENT ADD CONSTRAINTS TB_STUDENT_CLASS_ID_FK FOREIGN KEY (CLASS_ID) REFERENCES TB_CLASS (CLASS_ID) 外键约束

[19].删除约束

ALTER TABLE TB_STUDENT DROP CONSTRAINTS TB_STUDENT_CLASS_ID_FK 

[20].禁用约束

ALTER TABLE TB_STUDENT DISABLE CONSTRAINTS TB_STUDENT_CLASS_ID_FK

[21].激活约束

ALTER TABLE TB_STUDENT ENABLE CONSTRAINTS TB_STUDENT_CLASS_ID_FK

[22].用户解锁

ALTER USER SCOTT ACCOUNT UNLOCK

[23].重新设密码

ALTER USER SCOTT IDENTIFIED BY TIGER

===========PL/SQL编程部分=========

[24].PLSQL语法规则:

DECLARE

/**声明部分在此处声明要使用的变量、游标、以及局部的存储过程及函数。**/

BEGIN 

/** 执行部分过程及SQL **/

EXCEPTION

/** 异常处理部分 **/

END 

/** 结束部分 **/

DECLARE   

STR varchar(50):='HELLOWORD' ;  

BEGIN  

  STR:=STR || 'LIUTAO' ;    

  dbms_output.put_line(STR) ;  

EXCEPTION   

  WHEN OTHERS THEN     

  dbms_output.put_line('发生错误');    

END ;

其中 := 是赋值符号 || 是字符连接符号 dbms_output.put_line()是一个输出方法

[1].--查询功能实现

DECLARE 

V_ID NUMBER ;

V_NAME VARCHAR(50) ;

BEGIN 

  SELECT STU_ID,STU_NAME 

  INTO V_ID ,V_NAME FROM TB_STUDENT 

  WHERE STU_ID = 11 ;  

  dbms_output.put_line('V_ID=' || V_ID) ;

  dbms_output.put_line('V_NAME=' || V_NAME);  

END ;

[2].--添加功能

DECLARE

BEGIN      

    INSERT INTO TB_STUDENT(STU_ID,STU_NAME,STU_AGE) VALUES(20,'刘江红',10) ;

    commit;     

END ;

%TYPE 和 %ROWTYPE 用来表示不确定类型

%TYPE 可以理解成对数据库表一个字段的副本

%ROWTYPE 可以理解成对数据库一行记录提取出来的一个副本

[3].-- %TYPE用法示例 :

DECLARE 

V_STU_ID TB_STUDENT.STU_ID%TYPE ;

V_STU_NAME TB_STUDENT.STU_NAME%TYPE ;

V_STU_AGE TB_STUDENT.STU_AGE%TYPE ;

BEGIN    

SELECT STU_ID ,STU_NAME,STU_AGE INTO V_STU_ID ,V_STU_NAME,V_STU_AGE FROM TB_STUDENT WHERE STU_ID = 14 ;

DBMS_OUTPUT.PUT_LINE('STU_ID=' || V_STU_ID);

DBMS_OUTPUT.PUT_LINE('STU_NAME=' || V_STU_NAME) ;

DBMS_OUTPUT.PUT_LINE('STU_AGE=' || V_STU_AGE) ;  

END ;

[4].--  %ROWTYPE用法示例 :

DECLARE     

    V_TB_STU_ROW TB_STUDENT%ROWTYPE ;    

BEGIN  

    SELECT * INTO V_TB_STU_ROW FROM TB_STUDENT WHERE STU_ID=10;    

    DBMS_OUTPUT.PUT_LINE('STU_ID=' || V_TB_STU_ROW.STU_ID) ;    

    DBMS_OUTPUT.PUT_LINE('STU_NAME=' || V_TB_STU_ROW.STU_NAME) ;     

    DBMS_OUTPUT.PUT_LINE('STU_AGE=' || V_TB_STU_ROW.STU_AGE) ;      

    DBMS_OUTPUT.PUT_LINE('STU_SEX=' || V_TB_STU_ROW.STU_SEX) ;    

END ;

[5].复合数据类型  特点包含多个内部组件、用于存放多个值。需要先定义类型然后用该类型可重复定义多个变量

1.数组 VARRAY 

语法:TYPE TYPE_NAME IS VARRAY (MAXINUM_SIZE) OF ELEMENT_TYPE

TYPE_NAME : 可变数组的名称

MAXINUM_SIZE : 可变数组元素的最大数目

ELEMNET_TYPE : 数组元素的类型

可变数组的下标从1开始 

用法示例 :

--  VARRAY 用法示例 :

DECLARE    

    TYPE ARY IS VARRAY (5) OF VARCHAR2(20) ;    

    V_ARY ARY := ARY('LIUTAO','MANY','KING','SAN','ABBY') ; /** 不能直接使用需赋值给一个变量 **/    

BEGIN  

    DBMS_OUTPUT.PUT_LINE(V_ARY(1)) ;

    DBMS_OUTPUT.PUT_LINE(V_ARY(2)) ;

    DBMS_OUTPUT.PUT_LINE(V_ARY(3)) ; 

    DBMS_OUTPUT.PUT_LINE(V_ARY(4)) ;

    DBMS_OUTPUT.PUT_LINE(V_ARY(5)) ;    

END ;

=======================================

2.可变数组 TABLE 与JAVA中的数组类似、可以理解成可变数组、下标无限、按照二进制进行索引

-- TABLE 用法示例 :

DECLARE 

   TYPE ARY IS TABLE OF VARCHAR(30) INDEX BY BINARY_INTEGER ;   

   V_LIST ARY ;   

BEGIN  

   V_LIST(10) := 'HELLOWORD_LIUTAO' ;

   V_LIST(12) := 'WHERE AND STU_ID' ;   

   DBMS_OUTPUT.PUT_LINE('V_LIST(10) = ' || V_LIST(10)) ;

   DBMS_OUTPUT.PUT_LINE('V_LIST(12) = ' || V_LIST(12)) ;   

END ;

=======================================

3.RECORD 可以理解成JAVA中的集合类型

-- RECORD 用法示例 :

DECLARE 

   TYPE ARY IS RECORD (        

        V_ID NUMBER ,

        V_NAME TB_STUDENT.STU_NAME%TYPE ,

        V_TB_STU TB_STUDENT%ROWTYPE  

   ) ;

   

   V_STUDENT ARY ;

BEGIN   

   SELECT STU_ID , STU_NAME , STU_AGE , STU_BIRTHDAY , STU_SEX INTO   V_STUDENT.V_ID ,  V_STUDENT.V_NAME ,   

    V_STUDENT.V_TB_STU.STU_AGE , V_STUDENT.V_TB_STU.STU_BIRTHDAY , V_STUDENT.V_TB_STU.STU_SEX  FROM TB_STUDENT WHERE STU_ID = 10 ;    

    DBMS_OUTPUT.PUT_LINE(V_STUDENT.V_ID) ;

    DBMS_OUTPUT.PUT_LINE(V_STUDENT.V_NAME) ;

    DBMS_OUTPUT.PUT_LINE(V_STUDENT.V_TB_STU.STU_AGE) ;

    DBMS_OUTPUT.PUT_LINE(V_STUDENT.V_TB_STU.STU_BIRTHDAY) ;

    DBMS_OUTPUT.PUT_LINE(V_STUDENT.V_TB_STU.STU_SEX) ;     

END ;

[6].PLSQL异常处理部分实例 :

-- 异常处理

DECLARE 

   TB_ROWS TB_STUDENT%ROWTYPE ;

   V_SQL_CODE VARCHAR2(200) ;

   V_SQL_MSG VARCHAR2(200) ;   

BEGIN   

  SELECT * INTO TB_ROWS FROM TB_STUDENT ;

  DBMS_OUTPUT.PUT_LINE('STU_NAME = ' || TB_ROWS.STU_NAME) ;  

  EXCEPTION -- 处理异常通用将异常信息记录到数据库表中保存                      

   WHEN OTHERS THEN     

  V_SQL_CODE := SQLCODE ; -- 返回错误编码

  V_SQL_MSG := SQLERRM ;  -- 返回错误信息  

  INSERT INTO TB_ERROR (ID,TABLE_NAME,SQL_CODE,SQL_MSG) VALUES (TB_ERROR_SEQ.NEXTVAL ,'TB_STUDENT',V_SQL_CODE,V_SQL_MSG) ;  

  COMMIT ;  

END ;

[7].PLSQL流程控制语句与循环语句实例 :

[7.1] ************ 流程控制分支语句 ************

-- 语法规则 

-- IF (条件) THEN 执行的语句  

-- ELSIF (条件) THEN  执行的语句  

-- ELSE   

-- END IF ;

DECLARE  

 V_STR VARCHAR2(20) := 'LIUTAO' ;

BEGIN  

 IF (V_STR = 'LIUTAO') THEN   

   DBMS_OUTPUT.PUT_LINE('V_STR = ' || 'LIUTAO') ;   

 ELSIF (V_STR = 'HELLO') THEN    

   DBMS_OUTPUT.PUT_LINE('V_STR = ' || 'HELLO') ;    

 ELSE     

   DBMS_OUTPUT.PUT_LINE('V_STR = ' || '都不满足条件') ;    

 END IF ;   

END ;

[7.2] ********** 循环语句 简单循环 LOOP **********

DECLARE 

  V_INDEX NUMBER := 10 ;

BEGIN

  LOOP 

      V_INDEX := V_INDEX + 1 ;

      DBMS_OUTPUT.PUT_LINE(V_INDEX);

      EXIT  WHEN V_INDEX = 15 ;

  END LOOP ;

END ;

[7.3] ***************** 嵌套循环 *****************

DECLARE 

 V_A NUMBER := 0 ; 

 V_B NUMBER := 0 ; 

BEGIN   

 LOOP    

    V_A := V_A + 1 ;

    DBMS_OUTPUT.PUT_LINE('V_A = ' || V_A) ;    

    EXIT WHEN V_A = 4 ; -- 外循环四次

    V_B := 0 ;     

   LOOP      

    EXIT WHEN V_B = 4 ; -- 内循环再次    

    V_B := V_B + 2 ;     

    DBMS_OUTPUT.PUT_LINE('V_B = ' || V_B) ;  

   END LOOP ;   

 END LOOP ;

    DBMS_OUTPUT.PUT_LINE('结束循环...........') ;    

END ;

[7.4] *************** 跳出循环体方法 ***************

DECLARE 

 V_A NUMBER := 0 ; 

 V_B NUMBER := 0 ; 

BEGIN   

 <<OUTER>>LOOP  -- 加上外层循环标识符 OUTER    

    V_A := V_A + 1 ;

    DBMS_OUTPUT.PUT_LINE('V_A = ' || V_A) ;    

    EXIT WHEN V_A = 4 ; -- 外循环四次

    V_B := 0 ;         

   <<INNER>>LOOP  -- 加上内层循环标识符 OUTER      

    EXIT OUTER WHEN V_B = 8 ; -- 内循环两次 当V_B = 8 时结束外层循环    

    V_B := V_B + 2 ;     

    DBMS_OUTPUT.PUT_LINE('V_B = ' || V_B) ;  

   END LOOP ;   

 END LOOP ;

    DBMS_OUTPUT.PUT_LINE('结束循环...........') ;       

END ;

[7.5] ************ FOR 循环语句 ****************

BEGIN

  FOR V_INDEX IN 1..20 LOOP

     DBMS_OUTPUT.PUT_LINE(V_INDEX) ;        

  END LOOP ;

END ; 

[7.6] ************ WHILE 循环语句 ************

DECLARE

   V_INDEX NUMBER := 1 ;

BEGIN

   WHILE V_INDEX < 10 LOOP 

     DBMS_OUTPUT.PUT_LINE(V_INDEX);

     V_INDEX := V_INDEX + 2 ;

   END LOOP ;

END ;

[7.6] ************ 游标的使用  ************

游标的作用 :提取结果集

DECLARE   

 CURSOR C_STUDENT IS SELECT * FROM TB_STUDENT ; -- 定义一个游标提取表 TB_STUDNET 中的所有数据 

 C_STUDENT_ROW TB_STUDENT%ROWTYPE ; -- 声明变量接收结果 

BEGIN 

 OPEN C_STUDENT ; -- 打开游标 

 LOOP    

    FETCH C_STUDENT INTO C_STUDENT_ROW ; -- FETCH关键字用来提取一行记录

    EXIT WHEN C_STUDENT%NOTFOUND ; -- 判断是否还有记录提取

    DBMS_OUTPUT.PUT_LINE('STU_ID = ' || C_STUDENT_ROW.STU_ID || 'STU_NAME = ' || C_STUDENT_ROW.STU_NAME) ;    

 END LOOP ; 

 CLOSE C_STUDENT ; -- 关闭游标 

END ;

-- 游标使用案例查询所有班级信息并列出对应班级的所有学生信息

-- 学习使用带参数的游标

DECLARE -- 声明部分

  CURSOR C_CLASS_CURSOR IS SELECT * FROM TB_CLASS ;-- 班级游标

  CURSOR C_STUDENT_CURSOR(V_CLASS_ID NUMBER) IS SELECT * FROM TB_STUDENT WHERE STU_CLASS_ID = V_CLASS_ID ; -- 学生游标  

  V_CLASS_RESULT TB_CLASS%ROWTYPE ; -- 保存班级信息

  V_STUDENT_RESULT TB_STUDENT%ROWTYPE ; -- 保存学生信息  

BEGIN  -- 执行部分  

  OPEN C_CLASS_CURSOR ; -- 打开游标

  LOOP 

    FETCH C_CLASS_CURSOR INTO V_CLASS_RESULT ; -- 提取结果

    EXIT WHEN C_CLASS_CURSOR%NOTFOUND ; -- 判断是否还有结果

    DBMS_OUTPUT.PUT_LINE('CLASS_ID = ' || V_CLASS_RESULT.CLASS_ID || 'CLASS_NAME = ' || V_CLASS_RESULT.CLASS_NAME) ;

      OPEN C_STUDENT_CURSOR(V_CLASS_RESULT.CLASS_ID) ; -- 打开学生游标要传参数

      LOOP

        FETCH C_STUDENT_CURSOR INTO V_STUDENT_RESULT ;

        EXIT WHEN C_STUDENT_CURSOR%NOTFOUND ; 

        DBMS_OUTPUT.PUT_LINE('STU_ID = ' || V_STUDENT_RESULT.STU_ID || 'STU_NAME' || V_STUDENT_RESULT.STU_NAME); 

      END LOOP ;

      CLOSE C_STUDENT_CURSOR ; -- 关闭游标

  END LOOP ;

  CLOSE C_CLASS_CURSOR ; -- 关闭游标  

END ; -- 结束部分

[8].PLSQL程序单元

主要有四类:过程:执行特定操作、无返回值

   函数:进行复杂计算、有返回值

   包:逻辑上相关的过程和函数组合到一起

   触发器:事件触发执行相应操作

[8.1] ************ 存储过程 ************

-- 第一个无参的存储过程

CREATE OR REPLACE PROCEDURE INSERT_TB_STUDENT

IS V_SEX VARCHAR2(4) := '女';

BEGIN

  INSERT INTO TB_STUDENT

    (STU_ID, STU_NAME, STU_AGE, STU_SEX, STU_BIRTHDAY, STU_CLASS_ID)

  VALUES

    (TB_ERROR_SEQ.NEXTVAL, 'LIUTAO', 11, V_SEX, SYSDATE, 1015,);

  COMMIT ;  

END INSERT_TB_STUDENT;

-- 有参数的存储过程

CREATE OR REPLACE PROCEDURE INSERT_TB_STUDENT_PARAMS (       

 STU_NAME IN VARCHAR2 ,

 STU_AGE IN NUMBER ,

 STU_SEX IN VARCHAR2 ,

 STU_BIRTHDAY IN DATE ,

 STU_CLASS_ID IN NUMBER 

)

IS  

BEGIN     

   INSERT INTO TB_STUDENT

    (STU_ID, STU_NAME, STU_AGE, STU_SEX, STU_BIRTHDAY, STU_CLASS_ID)

  VALUES

    (TB_ERROR_SEQ.NEXTVAL, STU_NAME ,STU_AGE, STU_SEX, STU_BIRTHDAY, STU_CLASS_ID);

  COMMIT ;    

END INSERT_TB_STUDENT_PARAMS ;

-- 测试存储过程的方法

BEGIN 

   INSERT_TB_STUDENT_PARAMS('张三',14,'男',SYSDATE,1015) ;

END ;

-- 存储过程实例应用之数据表定时备份:

-- 将TB_STUDENT表中的数据备份到TB_STUDENT_BACK表中、TB_BACK_MAXID表记录备份的表名、和已完成备份的最大ID

CREATE OR REPLACE PROCEDURE BACK_DATA(MAX_ID NUMBER) 

IS 

  CURSOR C_STUDENT_CURSOR IS 

  SELECT * FROM TB_STUDENT WHERE STU_ID > MAX_ID ; -- 定义游标提取TB_STUDENT表中的数据  

  TB_STUDENT_ROW TB_STUDENT%ROWTYPE ; -- 定义变量存储游标的数据  

  V_INDEX NUMBER := 0 ; -- 分段提交的标识

  V_MAX_ID NUMBER := 0 ; -- 最在备份ID

BEGIN   

  IF C_STUDENT_CURSOR%ISOPEN THEN -- 断送游标是否打开

    NULL ;

  ELSIF 

    OPEN C_STUDENT_CURSOR ;    -- 打开游标

  END IF ;        

  LOOP     

   INDEX := INDEX + 1 ;   

   FETCH C_STUDENT_CURSOR INTO TB_STUDENT_ROW ; -- 提取学生表中的数据

   EXIT WHEN C_STUDENT_CURSOR%NOTFOUND ;  

   INSERT INTO TB_STUDENT_BACK (STU_ID,STU_NAME,_STU_AGE,STU_SEX,STU_BIRTHDAY,STU_CLASS_ID)

   VALUES (C_STUDENT_ROW.STU_ID,C_STUDENT_ROW.STU_NAME,C_STUDENT_ROW.STU_AGE,C_STUDENT_ROW.STU_BIRTHDAY,C_STUDENT_ROW.STU_CLASS_ID) ;

   IF V_INDEX = 2000 THEN

     COMMIT ; -- 分段提交防止内存溢出

     V_INDEX := 0 ;

   END IF ;   

  END LOOP ;  

  SELECT MAX(MAX_ID) INTO V_MAX_ID FROM TB_STUDENT_BACK ;

  -- 备份完成及时更新已完成的最大备份ID

  UPDATE TB_BACK_MAXID SET MAX_ID = V_MAX_ID WHERE TABLE_NAME = 'TB_STUDENT' ;  

  COMMIT ;  

  CLOSE TB_STUDENT_CURSOR ;  

END BACK_DATA ;

-- 调用备份存储过程

CREATE OR REPLACE PROCEDURE EXECUTION_BACK 

IS

  V_MAX_ID NUMBER := 0 ;

BEGIN

  SELECT MAX_ID INTO V_MAX_ID FROM TB_BACK_MAXID WHERE TABLE_NAME='TB_STUDENT' ;

  BACK_DATA(V_MAX_ID) ;

END EXECUTION_BACK ;


--  测试备份存储过程

BEGIN

  EXECUTION_BACK ;

END ;

-- 定时完成备份工作

DECLARE

  JOBNO NUMBER  ;

BEGIN

  DBMS_JOB.SUBMIT(          

          JOBNO ,

          WHAT => 'EXECUTION_BACK ;' , -- 要执行的存储过程名称

          Interval => 'TRUNC(SYSDATE,''MI'')+3/(24*60)' -- 定义时间间隔每三分钟

  ) ;

  COMMIT ;

END ;

[8.2] ***************PLSQL函数***************

函数与过程的区别是函数有返回值

-- 计算年薪的函数

CREATE OR REPLACE FUNCTION FUN_COUNT_SAL(

                                          SAL IN NUMBER

                                         ) 

                                         RETURN NUMBER IS 

  

BEGIN  

  RETURN SAL*12 ;  

END FUN_COUNT_SAL;

-- 测试函数

DECLARE

 V_SAL NUMBER ;

BEGIN    

 SELECT SAL INTO V_SAL FROM EMP WHERE EMPNO='7521' ;

 DBMS_OUTPUT.PUT_LINE(FUN_COUNT_SAL(V_SAL)) ;

END ;

[8.3] ***************PLSQL程序包***************

A.-- 定义程序包 :

CREATE OR REPLACE PACKAGE FK_COUNT_SAL IS

  V_BOUNS NUMBER := 150 ; -- 定义变量每月150奖金 

  -- 定义一个求年薪的函数

  FUNCTION COUNT_EMP_SAL(V_SAL NUMBER) RETURN NUMBER ;  

  -- 定义一个添加学生的过程

  PROCEDURE INSERT_STUDENT ;  

END FK_COUNT_SAL;

B.--  定义包体用于实现包中定义的函数和方法:

CREATE OR REPLACE PACKAGE BODY FK_COUNT_SAL IS 

 FUNCTION COUNT_EMP_SAL(V_SAL NUMBER) RETURN NUMBER IS   

 BEGIN 

    RETURN (V_SAL+V_BOUNS)*12 ;

 END ;

 PROCEDURE INSERT_STUDENT IS   

 BEGIN

   INSERT INTO TB_STUDENT (STU_ID,STU_NAME,STU_AGE,STU_SEX) VALUES (TB_ERROR_SEQ.NEXTVAL,'POW',12,'男') ;

   COMMIT ;

 END ; 

END FK_COUNT_SAL;

C.-- 测试程序包

DECLARE

  V_SAL NUMBER ;  

BEGIN  

   SELECT SAL INTO V_SAL FROM EMP WHERE EMPNO = '7369' ;   

   DBMS_OUTPUT.PUT_LINE(FK_COUNT_SAL.COUNT_EMP_SAL(V_SAL => V_SAL)) ;   

   FK_COUNT_SAL.INSERT_STUDENT ;   

END ;

[8.4] ***************PLSQL触发器***************

[二]. ***************ORACLE表连接查询***************

普通等值连接(内连接)查询:

SELECT * FROM EMP E ,DEPT D WHERE E.DEPTNO = D.DEPTNO ; 可连接多张表、可用 AND 加多个条件限定结果。

标准SQL写法:

SELECT * FROM EMP E INNER JOIN DEPT D ON E.DEPTNO = D.DEPTNO ; 标准SQL写法等值连接称为内连接条件用ON限定。

外连接查询分为左外连接和右外连接:

左外连接:以左表为主表、右表为驱动表、左表中的数据全部列出、在右表中没有匹配的项则用NULL补齐。

右外连接:以右表为主表、左表为驱动表、右表中的数据全部列出、在左表中没有匹配的项则用NULL补齐。

普通写法:

SELECT * FROM EMP E ,DEPT D WHERE E.DEPTNO(+) = D.DEPTNO ; 加号在左边表示以右表为主表。

SELECT * FROM EMP E , DEPT D WHERE E.DEPTNO = D.DEPTNO(+) ; 加号在右边表示以左表为主表。

标准SQL写法:

SELECT * FROM EMP E RIGHT OUTER JOIN DEPT D ON E.DEPTNO = D.DEPTNO ; 右连接查询。

SELECT * FROM EMP E LEFT OUTER JOIN DEPT D ON E.DEPTNO = D.DEPTNO ; 左连接查询。

[三]. 组函数、GROUP BY 分组子句、ORDER BY 排序子句

HAVING 与 WHERE 的区别 :

WHERE是在分组前进行条件过滤 

HAVING是在分组后进行条件过滤

WHERE子句中不能存在分组函数

HAVING子句中可以使用分组函数

[四]. 多行比较运算符

IN 与列表中的任一成员相等

ANY 与子查询返回的每一个值比较

ALL 与子查询返回的所有值比较

SELECT * FROM EMP WHERE SAL > ANY (

SELECT MIN(SAL) FROM EMP GROUP BY DEPTNO ) 工资大于子查询返回结果中最小的值都满足条件 。

SELECT * FROM EMP WHERE SAL < ANY (

SELECT MIN(SAL) FROM EMP GROUP BY DEPTNO ) 工资小于子查询中返回结果最大的值都是满足条件。

SELECT * FROM EMP WHERE SAL < ALL (

SELECT MIN(SAL) FROM EMP GROUP BY DEPTNO ) 工资小于子查询中返回结果最小的值才满足条件 。

SELECT * FROM EMP WHERE SAL > ALL (

SELECT MIN(SAL) FROM EMP GROUP BY DEPTNO ) 大于所有返回值即大于最大的值才满足条件 。

案例:删除表中的重复数据保留一条、现在TB_STUDNET表假设STU_NAME和STU_SEX完全相同则认为是重复。

1.创建临时表 CREATE TABLE TB_STUDENT_TMP AS SELECT DISTINCT STU_NAME , STU_SEX FROM TB_STUDENT 

2.删除原有数据表中的数据 TRUNCATE TABLE TB_STUDENT

3.将临时表中的数据插回来 INSERT INTO TB_STUDENT SELECT * FROM TB_STUDENT_TMP 

案例:删除表中的重复数据不保留

DELETE FROM TB_STUDENT WHERE ROWID IN ( SELECT ROWID FROM TB_STUDENT GROUP BY  STU_NAME , STU_SEX HAVING COUNT(*) > 1 )

[五].DECODE 函数的用法

SELECT DECODE(ENAME,'SCOTT','LIUTAO','POW') ENAME ,DEPTNO FROM EMP 查询员工表如果姓名是SCOTT则显示LIUTAO否则显示WANGWU 。

[六].ORACLE 中的序列 SEQUENCE

-- 创建序列

CREATE SEQUENCE STUDENT_SEQ 

MINVALUE 1 -- 最小值

MAXVALUE 9999999 -- 最大值

START WITH 1 -- 从1开始

INCREMENT BY 1 -- 每次递增1

CACHE 20 -- 缓存20个值

SELECT STUDENT_SEQ.NEXTVAL FROM DUAL ; 查看下一值

SELECT STUDENT_SEQ.CURRVAL FROM DUAL ; 查看当前值必须是至少执行一次NEXTVAL才有值

-- 删除序列

DROP SEQUENCE STUDENT_SEQ 

[七].ORACLE中的同义词

-- 同义词 为简化操作有时需要对某些表或视图创建一个同义词来引用

CREATE SYNONYM TB_CLS FOR SCOTT.TB_CLASS ; 以后则查询TB_CLS就是查询表TB_CLASS 

[八].ORACLE中的索引 加快数据检索

主键约束和唯一性约束系统自动创建对应的索引。

手动创建索引:

CREATE INDEX TB_ERROR_SQL_MSG ON TB_ERROR(SQL_MSG) ; 为TB_ERROR表中字段SQL_MSG创建索引名称为TB_ERROR_SQL_MSG 

删除索引:

DROP INDEX TB_ERROR_SQL_MSG 

[九].ORACLE视图 简化查询

简单视图:

CREATE VIEW LIU_EMP AS SELECT * FROM SCOTT.EMP ;







© 著作权归作者所有

共有 人打赏支持
liu13430
粉丝 18
博文 48
码字总数 35010
作品 0
深圳
高级程序员
私信 提问
Oracle和MySQL数据库的比较

本次使用Oracle11g和MySQL5.0作比较,目的是为了整理和巩固所学的数据库知识,也为了加深印象.(本人菜鸟,请大神们多多指教) 不同点 所占内存不同 Oracle11g在官网上下载的时候显示大约2g大小的...

青椒炒盖饭
2018/12/19
0
0
Ubuntu 10.04 下安装 Oracle 11g

Ubuntu 10.04 下安装Oracle 11g步骤: 1. 安装JDK,安装方法见 Ubuntu 10.04下的搭建SUN JAVA开发环境 解决中文乱码问题,在jre目录下字体目录中建立"fallback"目录,然后copy或者link你喜欢...

绿悠悠
2010/06/06
3.5K
4
oracle11g导出数据库空表

项目中使用oracle11g的情况越来越常见,也经常会基于oracle11g数据库的数据迁移。缘于oracle11g的一个特性,默认情况下,oracle不会给空表分配表空间,对于这些空表,使用exp命令做导出的时候...

彭苏云
2014/03/26
0
0
导入dmp文件报“不是有效的导出文件,头部验证失败”

导入某人给的dmp文件时 在dos环境下 imp em/123456@orcl fromuser=em touser=em file=c:em1013.dmp 报错如标题 原因是,那人是在在oracle11g导出的 而我导入的数据库也是oracle11g,但是客户...

老崔很年轻
2018/07/02
0
0
解决oracle11g ORA-00119 ORA-00132方法记录

在window下启动oracle11g是报如下错误 在网上也有很多人遇到这种问题,大家的解决方法基本都是一致的,但是oracle11g和oracle10g有些区别。 执行该语句后在database下会生成INITxiangde.ORA...

JackFace
2016/10/26
7
0

没有更多内容

加载失败,请刷新页面

加载更多

租房软件隐私保护如同虚设

近日,苏州市民赵先生向江苏新闻广播新闻热线025-84658888反映,他在“安居客”手机应用软件上浏览二手房信息,并且使用该软件自动生成的虚拟号码向当地一家中介公司进行咨询。可电话刚挂不久...

linux-tao
今天
1
0
分布式项目(五)iot-pgsql

书接上回,在Mapping server中,我们已经把数据都整理好了,现在利用postgresql存储历史数据。 iot-pgsql 构建iot-pgsql模块,这里我们写数据库为了性能考虑不在使用mybatis,换成spring jd...

lelinked
今天
4
0
一文分析java基础面试题中易出错考点

前言 这篇文章主要针对的是笔试题中出现的通过查看代码执行结果选择正确答案题材。 正式进入题目内容: 1、(单选题)下面代码的输出结果是什么? public class Base { private Strin...

一看就喷亏的小猿
今天
2
0
cocoapods 用法

cocoapods install pod install 更新本地已经install的仓库 更新所有的仓库 pod update --verbose --no-repo-update 更新制定的仓库 pod update ** --verbose --no-repo-update...

HOrange
今天
3
0
linux下socket编程实现一个服务器连接多个客户端

使用socekt通信一般步骤 1)服务器端:socker()建立套接字,绑定(bind)并监听(listen),用accept()等待客户端连接。 2)客户端:socker()建立套接字,连接(connect)服务器,连接上后...

shzwork
昨天
3
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部