PL/SQL开发

最新推荐文章于 2023-06-11 19:54:05 发布

夏光芒

最新推荐文章于 2023-06-11 19:54:05 发布

阅读量285

点赞数 1

分类专栏：数据库

本文链接：https://blog.csdn.net/xiaguangmang/article/details/82811048

版权

数据库专栏收录该内容

3 篇文章 0 订阅

订阅专栏

一、ORACLE数据库基础

1. oracle数据库服务端/客户端

安装好oracle数据库后，有三个文件：①sqlnet.ora;②tnsnames.ora;③listener.ora。listener.ora文件一般存在于服务端，用于监听，sqlnet.ora，tnsnames.ora这两个文件，用于客户端配置连接。tnsnames.ora位置路径通常在：$ORACLE_HOME/network/admin。
查看监听：lsnrctl status
关闭监听：lsnrctl stop
启动监听：lsnrctl start
网络访问oracle数据库：sqlplus username/passwd@string（这里的username为用户名，passwd为密码，@string为tnsnames.ora文件中的配置连接名称）。
服务端访问oracle数据库：sqlplus username/passwd（同上）。
通常情况下oracle数据会建立两个管理员账户：sys/system
关闭（shutdown immediate）开启（startup）数据库都必须在sys用户下。
oracle的一般启动过程如下：
shutdown-------“startup”--------->open
open-------“shutdown immediate”----->shutdown
shutdown-------nomount------mount------>open
shutdown------“startup mount”------------->mount
shutdown------“startup nomount”------------->nomount

2. oracle数据库基本使用

（1）表空间（tablespace）

操作	语句
创建零时表空间 temp	create temporary tablespace …
创建表空间	create tablespace 表空间名 datafile ‘数据库表空间路径’ size 20M（表空间大小）
加表空间	alter tablespace 表空间名 add datafile ‘数据库表空间路径’ size 20M（表空间大小）
删除表空间	drop tablespace 表空间名 including contents and datafiles --删除表空间，对象和数据文件

（2）创建操作

操作	语句
创建用户	create user 用户名 identified by passwod default tablespace 表空间名
删除用户	drop user用户名 cascade --cascade:级联删除，删除该用户相关的对象
用户改密码	alter user 老用户名 identified by 新用户名密码
建新表	create table 表名(字段名字段类型等) 例如：create table mg (fid number(10),name varchar2(24),age number(3));
建索引	CREATE INDEX 索引名 ON 表名 ( 字段1, [字段2, ……] )
建视图	CREATE VIEW 视图名 AS SELECT …. FROM ……
建物化视图 (相当于一种物理表（user_tables）,也是一种段（segment）)	CREATE materialized VIEW 视图名 refresh force on demand start with sysdate next to_date( concat( to_char( sysdate+1,‘dd-mm-yyyy’),’ 22:00:00’),‘dd-mm-yyyy hh24:mi:ss’) AS SELECT …. FROM ……(指定每天自动刷新时间为22点，相当于手动刷新模式) create materialized view 视图名 refresh force on commit as select * from table_name(自动刷新，一旦有commit操作，立刻更新该视图)
建同义词(SYNONYM)	CREATE SYNONYM 同义词名 FOR 表名 CREATE SYNONYM 同义词名 FOR 表名@数据库链接名
建数据库链接（DATABASE LINK）	CREATE DATABASE LINK 数据库链接名 CONNECT TO 用户名 IDENTIFIED BY 密码 USING ‘数据库连接字符串’ create public database link link名字 connect to 所连接数据库的用户名 identified by "密码"using ‘(DESCRIPTION(ADDRESS=(PROTOCOL=TCP)(HOST=所连接数据库IP)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=所连接数据库实例名)))’;

（3）权限管理 (DCL) 语句

常用的系统权限集合	说明
CONNECT	基本的连接
RESOURCE	程序开发
DBA	数据库管理

常用的数据库对象权限	说明
ALL ON 数据对象名	所有操作权限
SELECT ON 数据对象名	查询权限
UPDATE ON 数据对象名	更新权限
DELETE ON 数据对象名	删除权限
INSERT ON 数据对象名	插入权限
ALTER ON 数据对象名	修改权限

GRANT赋予权限	REVOKE回收权限
GRANT CONNECT, RESOURCE TO 用户名	REVOKE CONNECT, RESOURCE FROM 用户名
GRANT SELECT ON 表名 TO 用户名	REVOKE SELECT ON 表名 FROM 用户名
GRANT SELECT, INSERT, DELETE ON表名 TO 用户名1, 用户名2	REVOKE SELECT, INSERT, DELETE ON表名 FROM 用户名1, 用户名2

（4）SQL分类

DDL（定义语言）：create alter drop 等；
DML(操作语言)：insert delete update select 等；
DCL(控制语言)：commit rollback 等；

（5）主键，外键和索引的区别

	主键	外键	索引
定义：	唯一标识一条记录，不能重复，不允许为空	表的外键是另一表的主键，可以有重复，可以是空值	不允许有重复，但可以有一个空值
作用：	保证数据完整性	用来和其他表建立联系	提高查询排序速度
个数：	一个表主键只能有一个	一个表可以有多个外键	一个表可以有多个唯一索引

（6）表信息

语句	含义
select * from user_tab_comments	获取所有表注释信息
select * from all_tab_columns	获取表字段，所有用户
select * from user_tab_columns	获取表字段，当前用户下
select * from all_tables	获取所有用户表
select * from user_tables	获取当前用户表
select * from dba_tables	获取所有表，包括系统表，DBA权限

3. ORACLE数据库数据表改变操作

操作	语句
增加字段	Alter Table 表名 Add (字段名称字段类型,字段名称字段类型)
修改字段	Alter Table 表名 modify （字段名称（新的）字段类型/字段长度） Alter Table 表名 rename column （旧的）字段名称 to （新的）字段名称
删除字段	alter table 表名 drop column 字段名称
修改表里字段的定义描述	ALTER TABLE表名 MODIFY 字段名字段名描述
改变表的名称	ALTER TABLE 表名1 TO 表名2
给表里的字段加上约束条件	ALTER TABLE 表名 ADD CONSTRAINT 约束名 PRIMARY KEY (字段名) ALTER TABLE 表名 ADD CONSTRAINT 约束名 UNIQUE (字段名)
把表放在或取出数据库的内存区	ALTER TABLE 表名 CACHE ALTER TABLE 表名 NOCACHE
删除表和它所有的约束条件	DROP TABLE 表名 CASCADE CONSTRAINTS
清空表里的所有记录, 保留表的结构	TRUNCATE 表名
查询数据库中的表	select * from user_tables --查询当前登陆用户下所有的表 select * from all_tables --查询当前连接数据库(实例)下所有的表

4. ORACLE数据库创建存储过程，函数，包，触发器

（1）创建存储过程

    create or replace procedure procedure_name(
                v_tempnum in varchar2,-----入参
                v_tempout out number  -----出参
            ）is --is/as 都可以
            vsal number(7,2);
            begin
            ...............    -------处理逻辑
            end procedure_name;

（2）创建函数

    create or replace function function_name(name in type,age in type ...)
              return number is -- is/as均可以
              result number;
              begin
              ...............     ------处理逻辑
              return result;
              end

（3）创建包

    # (1) 创建包规范
    create package emp_pkg is
     procedure emp_update_ename(v_empno varchar2,v_ename varchar2);
     function emp_get_sal(v_empno varchar2) return number;
     end;
    #(2)创建包体
    create or replace package body emp_pkg
    is
        procedure emp_update_ename
        (
        v_empno varchar2,
        v_ename varchar2
        )
        is
        vename varchar2(32);
        begin
        update emp set ename=v_ename where empno=v_empno;
        commit;
        select ename into vename from emp where empno=v_empno;
        dbms_output.put_line('雇员名称：'||vename);
        end;
        function emp_get_sal
        (
        v_empno varchar2
        )
        return number is
        vsal number(7,2);
        begin
        select sal into vsal from emp where empno=v_empno;
        return vsal;
        end;
    end;

（4）创建触发器

     /*
     触发器组成：
      1.触发事件：引起触发器被触发的事件。如：DML语句（INSERT,UPDATE,DELATE对表或者视图执行数据处理操作、DDL语句（如CREATE,ALTER,DROP创建，修改，删除模式对象）等
      2.触发时间：即该trigger是在触发事件发生之前（before）还是之后（after)触发
      3.触发操作：即该TRIGGER 被触发之后的目的和意图，正是触发器本身要做的事情。 例如：PL/SQL 块。
      4.触发对象：包括表、视图、模式、数据库。只有在这些对象上发生了符合触发条件的触发事件，才会执行触发操作。
      5.触发条件：由WHEN子句指定一个逻辑表达式。只有当该表达式的值为TRUE时，遇到触发事件才会自动执行触发器，使其执行触发操作。
      6.触发频率：说明触发器内定义的动作被执行的次数。即语句级(STATEMENT)触发器和行级(ROW)触发器。
    */
    create or replace trigger test_trigger
      after DELETE OR INSTER OR UPDATE ON TEST     ---对表TEST的操作
     DECLARE
      V_TYPE TEST_LOG.L_TYPE%TYPE;
      BEGIN
        IF INSERTING THEN
          V_TYPE:='INSERT';
          DBMS_OUTPUT.PUT_LINE('记录已经成功插入，并记录到日志'）
        ELSIF UPDATING THEN
          V_TYPE := 'UPDATE';
          DBMS_OUTPUT.PUT_LINE('记录已经成功更新，并记录到日志'）
        ELSIF DELETING THEN
          V_TYPE := 'DELETE'
          DBMS_OUTPUT.PUT_LINE('记录已经成功删除，并记录到日志'）
      END IF;
    
      INSERT INTO TEST_LOG VALUES(USER,V_TYPE,TO_CHAR(SYSDATE,'yyyy-mm-dd hh24:mi:ss'));
     END

（5）索引

--创建索引
create index index_name on table(column_name1,column_name2);
--创建唯一索引
create index unique index_name on table(column_name1,column_name2);
--删除索引
drop index index_name;
--重命名索引
alter index index_name rename to index_name1;
--合并索引（表使用一段时间后在索引中会产生碎片，此时索引效率会降低，可以选择重建索引或者合并索引,合并索引方式更好些，无需额外存储空间，代价较低）
alter index index_name coalesce;
--重建索引
alter index index_name rebuild;
--查看索引
select index_name,index-type, tablespace_name, uniqueness from all_indexes where table_name ='tablename';
--查询数据库下所有索引信息
select * from user_indexes;
--批量重建索引
/*按用户（表空间）将此用户（表空间）下面非临时表上面的索引全部重建，此过程建议在SYS用户下面执行：*/
CREATE OR REPLACE PROCEDURE BATCH_REBUILD_INDEX(USER_NAME IN VARCHAR2) IS
  S_SQL   VARCHAR2(500);
  ACCOUNT NUMBER := 0;
BEGIN
  FOR LINE2 IN (SELECT T.OWNER, T.INDEX_NAME
                  FROM ALL_INDEXES T
                 WHERE T.OWNER = UPPER(USER_NAME)
                   AND T.TABLE_TYPE = 'TABLE'
                   AND T.TEMPORARY = 'N'
                   AND T.INDEX_TYPE = 'NORMAL') LOOP
    S_SQL   := 'alter index ' || LINE2.OWNER || '.' || LINE2.INDEX_NAME ||
               ' rebuild';
    ACCOUNT := ACCOUNT + 1;
    EXECUTE IMMEDIATE S_SQL;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE(ACCOUNT);
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SQLERRM);
END BATCH_REBUILD_INDEX;
--执行过程实现批量更改
begin
  -- Call the procedure
  batch_rebuild_index(user_name => 'hs_user'); --输入用户名
end;

（6）创建JOB

    --建表  
    create table test_job(para_date date);  
    commit;  
     
    insert into test_job values(sysdate);  
    commit; 
     
    select * from test_job;
     
    --建立存贮过程   
    create or replace procedure test_jobproce   as 
    begin 
    insert into test_job values(sysdate);  
    end test_jobproce; 
     
    --建立job  
    --建立job后默认是执行的  
     
    declare  test_job_really number;  
    begin 
    dbms_job.submit(test_job_really,'test_jobproce;',sysdate,'sysdate+1/1440');  
    commit;  
    end;  
     
    --停止job  25是建立的job test_job_really  
    begin 
    dbms_job.broken(25,true);  
    commit;  
    end;  
     
    --启动job  
    begin 
    dbms_job.run(25);  
    commit;  
    end;  
     
    --删除job  
     
    begin  
    dbms_job.remove(25);  
    commit;  
    end;

JOB中常用的参数

字段(列)	类型	描述
JOB	NUMBER	任务的唯一标示号
LOG_USER	VARCHAR2(30)	提交任务的用户
PRIV_USER	VARCHAR2(30)	赋予任务权限的用户
SCHEMA_USER	VARCHAR2(30)	对任务做语法分析的用户模式
LAST_DATE	DATE	最后一次成功运行任务的时间
LAST_SEC	VARCHAR2(8)	如HH24:MM:SS格式的last_date日期的小时，分钟和秒
THIS_DATE	DATE	正在运行任务的开始时间，如果没有运行任务则为null
THIS_SEC	VARCHAR2(8)	如HH24:MM:SS格式的this_date日期的小时，分钟和秒
NEXT_DATE	DATE	下一次定时运行任务的时间
TOTAL_TIME	NUMBER	该任务运行所需要的总时间，单位为秒
BROKEN	VARCHAR2(1)	标志参数，Y标示任务中断，以后不会运行
INTERVAL	VARCHAR2(200)	用于计算下一运行时间的表达式
FAILURES	NUMBER	任务运行连续没有成功的次数
WHAT	VARCHAR2(2000)	执行任务的PL/SQL块
CURRENT_SESSION_LABEL	RAW	MLSLABEL 该任务的信任Oracle会话符
CLEARANCE_HI	RAW MLSLABEL	该任务可信任的Oracle最大间隙
CLEARANCE_LO	RAW	MLSLABEL 该任务可信任的Oracle最小间隙
NLS_ENV	VARCHAR2(2000)	任务运行的NLS会话设置
MISC_ENV	RAW(32)	任务运行的其他一些会话参数

JOB中常用的时间设置

描述	INTERVAL参数值
每天午夜12点	TRUNC(SYSDATE + 1)
每天早上8点30分	TRUNC(SYSDATE + 1) + （8×60+30）/(24×60)
每星期二中午12点	NEXT_DAY(TRUNC(SYSDATE ), ‘‘TUESDAY’’ ) + 12/24
每个月第一天的午夜12点	TRUNC(LAST_DAY(SYSDATE ) + 1)
每个季度最后一天的晚上11点	TRUNC(ADD_MONTHS(SYSDATE + 2/24, 3 ), ‘Q’ ) -1/24
每星期六和日早上6点10分	TRUNC(LEAST(NEXT_DAY(SYSDATE, ''SATURDAY"), NEXT_DAY(SYSDATE, “SUNDAY”))) + （6×60+10）/（24×60）
每周一凌晨2点执行	TRUNC(next_day(sysdate,2))+2/24
每分钟执行	TRUNC(sysdate,’mi’) + 1 / (24*60)
每天的凌晨2点执行	TRUNC(sysdate) + 1 +2 / (24)
每月1日凌晨2点执行	TRUNC(LAST_DAY(SYSDATE))+1+2/24
每季度的第一天凌晨2点执行	TRUNC(ADD_MONTHS(SYSDATE,3),‘Q’) + 2/24
每年7月1日和1月1日凌晨2点	ADD_MONTHS(trunc(sysdate,‘yyyy’),6)+2/24
每年1月1日凌晨2点执行	ADD_MONTHS(trunc(sysdate,‘yyyy’),12)+2/24

（7）创建序列

CREATE SEQUENCE sequence_name //创建序列名称
[INCREMENT BY n] //递增的序列值是 n 如果 n 是正数就递增,如果是负数就递减 默认是 1
[START WITH n] //开始的值,递增默认是 minvalue 递减是 maxvalue
[{MAXVALUE n | NOMAXVALUE}] //最大值  
[{MINVALUE n | NOMINVALUE}] //最小值
[{CYCLE | NOCYCLE}] //循环/不循环
[{CACHE n | NOCACHE}];//分配并存入到内存中
CREATE SEQUENCE  sequence1 MINVALUE 1 NOMAXVALUE INCREMENT BY 1 START WITH 1 NOCACHE --创建以1开始无上限自增1的序列
CREATE TABLE table_name
(
  ID NUMBER(24, 0) default "sequence1"."NEXTVAL" NOT NULL 
  )----建表时利用序列生成自增ID

（8）创建快照

/*
快照的作用主要是能够进行在线数据恢复，当存储设备发生应用故障或者文件损坏时可以进行及时数据恢复，将数据恢复成快照产生时间点的状态。快照的另一个作用是为存储用户提供了另外一个数据访问通道，当原数据进行在线应用处理时，用户可以访问快照数据，还可以利用快照进行测试等工作。
源数据库：被同步的数据库
目的数据库：要同步的数据库
*/
--1.在目的数据库上创建dblink
create public database link TestDblink  --TestDblink是创建的dblink的名字
connect to dbName identified by dbPassword --dbName：源数据库用户；dbPassword:源数据库密码
 using '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS =(PROTOCOL = TCP)(HOST = 192.168.2.158)---元数据库地址
 (PORT = 1521)))
 (CONNECT_DATA =(SERVICE_NAME = orcl)))'--源数据库的实例名
--2.在目的数据库上，测试dblink
select * from test_table@TestDblink --test_table是源数据库的表
--3.在源数据库上，创建要同步表的快照日志
create snapshot log on test_table
--4.在源数据库上，创建要同步表的快照日志
create snapshot sn_test_table as select * from test_table@TestDblink
--5.设置快照刷新时间（快速刷新，在源数据库表上有快照日志）
alter snapshot sn_test_table refresh fast start with sysdate+30/(24*60*60) next sysdate+30/(24*60*60) --30秒后自动刷新，并隔30秒刷新一次
--6.查看快照最后一次刷新时间
SELECT NAME,LAST_REFRESH FROM ALL_SNAPSHOT_REFRESH_TIMES
--7.查看快照下次执行时间
select last_date,next_date,what from user_jobs order by next_date
--8.查询快照   sn_test_table是目的数据库中的快照
select * from sn_test_table
--9.销毁快照
drop snapshot sn_test_table

5.AWR报告的获取和分析

/*使用SQL*Plus命令行的方式获取，运行awrrpt.sql脚本文件，该脚本文件凡在$ORACLE_HOME/rdbms/admin目录下*，运行该脚本的用户必须具有SELECT_CATALOG_ROLE系统权限/
sql>awrrpt.sql
--输入 report_type的值：（回车默认html）报告显示格式
--输入num_days的值：（回车默认系统中全部的快照）
--输入begin_snap的值：（开始快照ID）
--输入end_snap的值：（结束快照ID）
--输入report_name的值：（输入awr报告的名称）
--更改扩展名为.htm

夏光芒

关注

1
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
PL/SQL开发

ORACLE数据库基础oracle数据库服务端/客户端安装好oracle数据库后，有三个文件：①sqlnet.ora;②tnsnames.ora;③listener.ora。listener.ora文件一般存在于服务端，用于监听，sqlnet.ora，tnsnames.ora这两个文件，用于客户端配置连接。tnsnames.ora位置路径通常在：$ORACLE_HOME/network/ad...
复制链接

扫一扫