ORACLE数据库

ORACLE数据库

一、认识ORACLE

Oracle数据管理系统=Oracle数据库+Oracle实例（instance即数据库服务)

Oracle实例 = 内存结构 + 后台进程 / SGA + 后台进程

一个实例只能与一个数据库关联，访问一个数据库；同一个数据库可以由多个实例访问；

1.1逻辑存储结构

1.2物理存储结构

1.3内存结构

1.4后台进程

1.5数据字典

二、数据库的创建及连接

三、数据库存储设置与管理

四、数据库对象的创建与管理

五、数据的操纵及事务管理

六、数据查询

6.1简单查询

6.2分组查询

6.3多表查询

6.3.1交叉连接

6.3.2内连接

6.3.3外连接

6.4子查询

七、PL/SQL语言基础

7.1数据类型、变量、常量

7.2语句控制结构

7.2.1选择结构

7.2.2循环结构

7.2.3跳转结构

八、PL/SQL程序开发

8.1存储过程

​ 存储过程的创建

CREATE OR REPLACE PROCEDURE hello
IS
   BEGIN
   	DBMS_OUTPUT.PUT_LINE('HELLO WORLD');
   END hello;

​ 存储过程的调用方式

 //1. SQL PLUS中调用：
 EXEC hello;
 CALL hello();无论有无参数，必须带括号
 //2. PL SQL中调用：
 BEGIN
   hello;
 END ;

​ 参数的模式

• IN 默认参数模式，IN可省略不写； 参数可以是常量或表达式，不可修改；形参传给实参

• OUT 此形参当调用存储过程结束后将值传给实参，所以只能是变量，初始值为NULL；实参传给形参

• IN OUT 参数只能是变量

EXAMPLE1:创建存储过程scott.myproc,以员工号(number)为参数，将该员工的工资(number) 返回给主程序（out参数）

 CREATE OR REPLACE PROCEDURE myproc (vno number,vsal out number)
  IS
 BEGIN
   SELECT sal INTO vsal FROM scott.emp
   WHERE empno = vno;
 END myproc;

在PL/SQL中调用myproc返回7900号员工的工资

 DECLARE sal number;
   BEGIN
       myproc(7900,sal);
      DBMS_OUTPUT.PUT_LINE(sal);
    END;
    /

EXAMPLE2创建一个存储过程show_emp,以部门号为参数，输出该部门的平均工资

CREATE OR REPLACE PROCEDURE show_emp
(p_id scott.emp.deptno%TYPE )
IS
 avgsal scott.emp.sal%TYPE;
BEGIN
	SELECT avg(sal) INTO avgsal  FROM scott.emp 
	WHERE deptno = p_id;
	DBMS_OUTPUT.PUT_LINE(avgsal);
	EXCEPTION WHEN NO_DATA_FOUND THEN
		DBMS_OUTPUT.PUT_LINE('ERROR');
END show_emp;

8.2函数

基本格式：

CREATE OR REPLACE FUNCTION func_name
(parameter1_name [mode] datatype [DEFAULT|:=value][,parameter2_name [mode] datatype [DEFAULT|:=value]...])
RETURN return_datatype  /*指明函数返回值类型 此处无分号*/
IS|AS
/*declarative section is here*/ /*变量定义部分*/
BEGIN
/*excutable section is here*/
EXCEPTION
/*exception section is here*/
END [func_name];

函数创建与存储过程类似，但在函数体的定义中至少包含一个显式的返回值，即必须有一个RETURN语句

EXAMPLE1:创建一个以部门号为参数，返回该部门的最高工资的函数

CREATE OR REPLACE FUNCTION scott.func_dept_maxsal
(p_deptno scott.emp.deptno%TYPE)
RETURN scott.emp.sal%TYPE  /*指明函数返回值类型 此处无分号*/
IS
v_maxsal scott.emp.sal%TYPE; /*变量定义部分*/
BEGIN
	SELECT MAX(sal) INTO v_maxsal FROM scott.emp WHERE deptno = p_deptno;
	RETURN v_maxsal;
END func_dept_maxsal;

执行函数，返回10号部门的最高工资

DECLARE 
	max scott.emp.sal%TYPE;
BEGIN
	max:=scott.func_dept_maxsal(10);
	DBMS_OUTPUT.PUT_LINE(max);
	DBMS_OUTPUT.PUT_LINE(scott.func_dept_maxsal(10));
END;

执行函数，返回各部门编号和最高工资，(结合使用了游标输出多个返回值)

DECLARE
	m scott.emp.sal%TYPE;
BEGIN
/*游标的for循环检索，自动定义，打开，关闭*/
	FOR v_dept IN(SELECT DISTINCT deptno FROM scott.emp) LOOP
	 m:= scott.func_dept_maxsal(v_dept.deptno);
	 DBMS_OUTPUT.PUT_LINE(m||' '||v_dept.deptno);
	END LOOP;
END;

EXAMPLE2:编写函数scott.updatefun，根据员工部门完成工资更新，部门10工资增加150，部门29工资增加200，部门30工资增加250，其他部门工资增加300，并返回更新后工资的值

CREATE OR REPLACE FUNCTION scott.updatefun
(v_empno scott.emp.empno%TYPE)
RETURN scott.emp.sal%TYPE
AS
	v_sal scott.emp.sal%TYPE;
	v_d scott.emp.deptno%TYPE;
	v_i number;
BEGIN
	SELECT deptno INTO v_d FROM scott.emp WHERE empno=v_empno; 
	if v_d = 10 then v_i:=150;
		elsif v_d=20 then v_i:=200;
		elsif v_d=30 then v_i:=250;
		else v_i:=300;
	end if;
UPDATE scott.emp set sal=sal+v_i where empno = v_empno;
COMMIT;
SELECT sal INTO v_sal FROM scott.emp WHERE empno = v_empno;
RETURN v_sal;
END updatefun;

调用函数，对编号为7900员工的工资进行更新，输出更新后的工资。

DECLARE 
v_sal scott.emp.sal%TYPE;
BEGIN
	v_sal:=scott.updatefun(7900);
	DBMS_OUTPUT.PUT_LINE(v_sal);
END;

8.3触发器

​ 它是一种特殊的存储类型，编译后存储在数据库服务器中，当特定事件发生时，由系统自己调用

基本格式：

CREATE [OR REPLACE] TRIGGER trigger_name
BEFORE | AFTER triggerring_event[BEFORE|AFTER|INSTEAD OF] [OF coulumn_name]
ON table_name | view_name | user_name | db_name
[FOR EACH ROW]
[WHEN trigger_condition]
DECLARE
/*Declareative section is here*/
BEGIN
/*Excutable section is here*/
EXCEPTION
/*Exception section is here*/
END[trigger_name];

8.3.1DML触发器

EXAMPLE1 为employees表创建一个名为“TRG_EMP_DML_ROW”的触发器，当插入新员工时显示新员工的员工号、员工名；当更新员工工资时，显示修改前后员工工资；当删除员工时，显示被删除的员工号、员工名

CREATE OR REPLACE TRIGGER scott.TRG_EMP_DML_ROW 
BEFORE INSERT OR UPDATE OR DELETE
ON scott.emp
FOR EACH ROW
BEGIN
	IF INSERTING THEN
		DBMS_OUTPUT.PUT_LINE(:new.empno||' '||:new.ename);
	ELSIF UPDATING THEN
		DBMS_OUTPUT.PUT_LINE(:old.sal||' '||:new.sal);
	ELSE
		DBMS_OUTPUT.PUT_LINE(:old.empno||' '||:new.ename);
    END IF;
END TRG_EMP_DML_ROW;

CREATE OR REPLACE TRIGGER scott.t1
BEFORE UPDATE 
ON scott.emp
FOR EACH ROW
BEGIN
	DBMS_OUTPUT.PUT_LINE('更新前'||:old.sal||'更新后 '||:new.sal);
END t1;

插入一个新员工

INSERT INTO scott.emp(empno,ename,job,sal,deptno) VALUES(7622,'JENNY','MANAGER',5900,10);
UPDATE scott.emp SET sal=sal+100 WHERE empno=7900;

8.3.2INSTEAD OF触发器

8.3.4系统触发器

8.4包

用于将相关的PL/SQL块或元素(过程、函数、变量、常量、游标等)组织在一起，成为一个完整的单元

九、数据库启动及关闭

十、安全管理

10.1用户管理

10.2权限管理

10.3角色管理

十一、备份与恢复

十二、常见问题及解决方案

1. 如何登陆到scott用户？

   先以system的sysdba身份登录

   连接上后，将原本scott用户解锁并提交事务

   更改用户密码，再连接即可

   

2. 查看当前登陆的用户

   [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-hiaiBtV7-1625568026626)(D:\桌面文件夹\查看当前登陆的用户.PNG)]

3. 查看当前所连接上的数据库

   当不是sysdba身份登录时，因为普通用户无权限会报错
   

十三、未完待续、、、主要用于复习（思路不清晰）

​