PL/SQL 安装配置与使用-EW帮帮网

变量名	变量值	备注
TNS_ADMIN	E:\PLSQL\Instantclient_11_2\network\admin	用于配置Oracle，以自己存放的位置为准
ORACLE_HOME	E:\PLSQL\Instantclient_11_2	用于配置Oracle，以自己存放的位置为准
NLS_LANG	AMERICAN_AMERICA.AL32UTF8	用于配置语言

（2）配置PLSQL Developer的环境变量

用系统管理员身份打开plsqldev.exe，找到工具栏的Configure > Preferences，配置Oracle的Connection中Oracle Home和OCI library的路径，其中，

Oracle Home的配置路径是：E:\PLSQL\Instantclient_11_2

OCI library的配置路径是：E:\PLSQL\Instantclient_11_2\oci.dll

如果未配置的话，PL/SQL连数据库时会报错报Ora-12557

3. 配置PLSQL的Oracle数据库链接

每个数据库链接，都需要在Instantclient_11_2文件夹的network\admin的tnsnames.ora文件中设置。

首次需要新建network\admin目录，并新建tnsnames.ora文件，文件路径为：

E:\PLSQL\Instantclient_11_2\network\admin\tnsnames.ora 。

文件内容如下：

以下只是配置数据库HOST和SID，而用户和密码是在打开PL/SQL软件时在登录弹出框中输入填写。

DB名=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.XX.XX.XXX)(PORT=1521)))(CONNECT_DATA=(SID=DB名)(SERVER=DEDICATED)))
DB名2=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.XX.XX.XXX)(PORT=1521)))(CONNECT_DATA=(SID=DB名2)(SERVER=DEDICATED)))

二、objects 说明

主要的objects有：

（一）Tables、Views

作用：Views是查询另一个账户中的表数据，Tables是对Views中的数据进一步处理，存入报表数据。

（二）Packages、package bodies、Procedures

作用：供代码调用包中的存储程序。

（三）Function、Types

作用：供存储程序中的SQL调用。

（四）DBMS_Jobs

作用：设置定时任务。

查看所有job任务：

SELECT * FROM all_jobs; 或 SELECT * FROM user_jobs;

终止某个job任务：

BEGIN
    DBMS_JOB.BROKEN(job => job的ID, broken => TRUE);
    COMMIT;
END;

开启某个job任务：

BEGIN
    DBMS_JOB.BROKEN(job => <job_id>, broken => FALSE);
    COMMIT;
END;

终止所有job任务：

BEGIN
    FOR job IN (SELECT job FROM all_jobs WHERE broken = 'N') LOOP
        DBMS_JOB.BROKEN(job.job, TRUE);
    END LOOP;
    COMMIT;
END;

开启所有job任务：

BEGIN
    FOR job IN (SELECT job FROM all_jobs WHERE broken = 'Y') LOOP
        DBMS_JOB.BROKEN(job.job, FALSE);
    END LOOP;
    COMMIT;
END;

注意：

针对tables创建数据的SQL语句，即，使用PL/SQL执行插入SQL语句时，一定要在insert语句后加上COMMIT;。或者执行插入SQL语句后，再点击提交按钮，如下图：

（五）查询存储过程、函数或包体中是否引用某内容

SELECT owner, name,  type, line, text
FROM   all_source
WHERE  UPPER(text) LIKE '%MUSIC%'
ORDER  BY owner, name, line;

三、基础设置

（一）快捷键

1. 快捷键设置

在工具栏的Configure > Preference > User Interface > Key Configuration中设置

2. 快捷键说明

F8：执行SQL语句

Ctrl+/：注释（需要先选中需要注释的句子）

Ctrl+/+Shift：取消注释（需要先选中句子）

（二）记住登录密码

在工具栏的Configure > Preference > Oracle > Logon History中设置，默认勾选“Store history”，还需勾选“Store with password”。

（三）关键字自动大写

在工具栏的Configure > Preference > User Interface > Editor中设置，Keyword case选择：Uppercase。

（四）SQL Windows显示行号

在工具栏的Configure > Preference > Window Types > SQL Window中设置，勾选show gutter（line numbers）。

（五）解决软件过期

若反复出现下图的问题：

解决办法如下：

首先，Windows + R ，输入 regedit，打开注册表。

再找到 HKEY_CURRENT_USER\Software\Allround Automations，删除 Allround Automations 目录。

然后找到 HKEY_CURRENT_USER\Software\Microsoft\Security，删除 Security 目录。

最后，重新打开软件即可。30天到期后，重复该操作。

（六）解决执行时的问题弹窗

如果在执行SQL查询时，出现如下弹窗，则提示权限不够。

可以授予该用户的V$session,V$sesstat、V$statname的权限，也可取消自动统计功能，可在工具栏 > Configure > Preferences > Oracle > Options > 取消勾选Auto statistics，点击OK。

四、包和包体

呈现方式：

作用：

管理SQL语句、程序单元（存储过程、函数等）。

其中包是声明接口、包体是实现接口。

使用：

1. 查看

查看Procedure具体内容时，需要在包体名（包体，而不是包）上右击，选择View（View，而不是View Spec）进行查看。

2. 拷贝

若想拷贝Procedure内容，可在存储过程名上右击，选择copy进行拷贝。

五、procedure 存储过程

（一）定义（创建）

1. 组成

参数（IN、OUT）、局部变量、逻辑代码块、异常处理。

2. 写法（未涉及包和包体）

CREATE [OR REPLACE] PROCEDURE 存储过程名(
    /* 声明参数 */
    p_id IN NUMBER,  -- 输入参数、数值类型
           p_name IN VARCHAR2, -- 输入参数、字串类型
    p_date IN DATE, -- 输入参数、时间类型
         p_res OUT VARCHAR2 -- 输出参数、字串类型
) IS
/* 声明外部变量（在下面第一个BEGIN的内部外部中，都能使用） */
// 存储过程中不能使用declare关键字。
变量名 类型; // 比如：count number;
/* 逻辑代码块 */
BEGIN
    /* 声明局部变量（只能在该BEGIN的内部中使用，不能在该BEGIN外部使用） */
    变量名 类型; // 只是声明
          变量名 类型 := 默认值; // 包括赋值
    /* 过程体 */
         NULL;  -- 至少需要一个语句
/* 异常处理 */
EXCEPTION
    WHEN NO_DATA_FOUND THEN  ---没有任何数据的异常
        --处理代码
    WHEN OTHERS THEN  --- 其他异常
        --处理代码
END 存储过程名; --也可省略存储过程名

3. 写法（涉及包和包体）

（1）创建包（内含procedure定义）

CREATE [OR REPLACE] PACKAGE 包名 IS
    TYPE refcur IS REF CURSOR; ---定义外部变量（包和包体都可用）
    -- 声明过程
    PROCEDURE procedure名(
    参数名  IN VARCHAR2,
    参数名  IN VARCHAR2,
    参数名  OUT refcur,
    参数名  OUT CLOB,
    参数名  OUT NUMBER);
END 包名;

（2）创建包体（内含procedure实现）

CREATE [OR REPLACE] PACKAGE BODY 包体名 IS
    -- 过程实现
    PROCEDURE procedure名(
    参数名 IN VARCHAR2,
    参数名 IN VARCHAR2,
    参数名  OUT refcur,
    参数名  OUT CLOB,
    参数名  OUT NUMBER) IS
    BEGIN
        DBMS_OUTPUT.PUT_LINE('OUT RES: ');
    END;
END 包体名;

4. 修改

右击包或包名 > Edit > 修改SQL语句 > 按下F8重新执行整个SQL语句，执行成功无异常即可。

（二）知识点

1. 字符串必须用单引号括起来

2. “:=”符号

变量赋值。

3.“||”符号

代表“连接字串”，而不是“或”。

4. 在后续的Oracle SQL知识点这一篇中

六、包、存储过程的SQL查询

（一）查询名称（针对包、存储过程）

---查看所有package包名
SELECT object_name
FROM user_objects --- user_objects针对当前用户。all_objects针对所有用户（若是特定用户，需在where后补充 AND owner = '用户名'的条件）
WHERE object_type = 'PACKAGE'
AND ROWNUM <= 100;
---查看所有package body包体名
SELECT object_name
FROM user_objects 
WHERE object_type = 'PACKAGE BODY' 
AND ROWNUM <= 100;
---查看所有procedure存储过程名
SELECT object_name
FROM user_procedures --- user_procedures针对当前用户，all_procedures针对所有用户
WHERE ROWNUM <= 100;

（二）查询内容（针对包、存储过程）

---查看package包内容
SELECT text
FROM user_source
WHERE name = 'PKG_REPORTMANAGE'  -- 包名全大写
AND type = 'PACKAGE'
AND ROWNUM <= 100;
---查看package body包体内容
SELECT text
FROM user_source
WHERE name = 'PKG_REPORTMANAGE'  -- 包名全大写
AND type = 'PACKAGE BODY'
AND ROWNUM <= 100;
---查看procedure存储过程内容
SELECT text
FROM user_source
WHERE name = 'AAAAA'  -- 存储过程的名称（全大写）
AND type = 'PROCEDURE'    -- 确保是存储过程
AND ROWNUM <= 100;
---查看table表内容
select * from 表名  
WHERE ROWNUM <= 100;

七、存储过程的断点调试

1. 打断点

首先查看存储过程（若存储过程放在包中，则右击包名，选择View，然后选择Procedure），在需要调试的代码处打上断点（在左侧行号显示处，打上断点，会有红色叉叉×）。

2. 打开断点调试框

右击存储过程，选择Test，会新生成一个Call the procedure脚本内容。并且下方会有调试所需的IN参数、执行结果OUT参数的表格，如下图所示。注意，冒号是紧挨着后面的变量的！

3. 输入和输出

（1）输入值

查看断点调试框下方的表格，是否有IN参数需要赋值，若有，则先将IN参数赋值。

（2）输出格式

输出的长字串的Type，最好设置为CLOB（比string存更多内容）。

4. 开始调试

图标	作用
带有搜索标志的三角	开始执行调试
绿色三角	切换断点（执行到下一个断点）
第1个正方形（蓝色）	单步进入（执行一步，若有子过程，则进入）
第2个正方形（蓝色）	单步经过（执行一步，不会进入子过程）
第3个正方形（蓝色）	单步跳出（退出到上一级）
第4个正方形（蓝色）	暂时未用
第5个正方形（红）	执行到下一个异常代码

5. 查看调试过程中的变量值

（1）鼠标放在变量上，查看当前值。

（2）在下方工具栏中的Script中输入想查看的变量值

八、SQL调试

DECLARE
    -- 声明输入输出参数
    condition_author VARCHAR2(100) := 'luobogan'; -- 查询条件
    condition_publishplace VARCHAR2(100) := '童话出版社';
    condition_booktype VARCHAR2(100) := '教育';
    TYPE refcur IS REF CURSOR;
    cur_book_table refcur; -- 输出结果
    return_sql VARCHAR2(4000);  ---如果字符太长，使用CLOB（CLOB不能打印，最好使用:变量名，通过下方的变量值输出框来查看CLOB的值，比如说=> return_sql改成=>  :return_sql）
    return_code NUMBER;
    cursor_bookid Books.BOOKID%TYPE;
    cursor_bookname Books.BOOKNAME%TYPE;
BEGIN
    -- 调用存储过程
    PKG_BOOK.PC_QueryBook(
        Condition_Author => condition_author, --- 注意是=>，没有冒号，才能使用上方declare的值（否则会用下方的变量输出框中定义的值）
        Condition_PublishPlace => condition_publishplace,
        condition_BookType => condition_booktype,
        cur_BookTable => cur_book_table,
        return_sql => return_sql, -- 如果是CLOB最好补充冒号（查看值时，在下方变量值输出框中查看执行test返回的值）
        return_code => return_code
    );
    -- 打印返回的 SQL 语句
    DBMS_OUTPUT.PUT_LINE('return_sql: ' || return_sql);
    -- 遍历游标并输出数据
    LOOP
        FETCH cur_book_table INTO cursor_bookid,cursor_bookname;
        EXIT WHEN cur_book_table%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('Fetched Data: ' || cursor_bookid || ', ' || cursor_bookname);
    END LOOP;
    -- 关闭游标
    CLOSE cur_book_table;  
EXCEPTION
    -- 异常输出
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('No data found');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('another error: ' || DBMS_UTILITY.format_error_backtrace);
END;

九、搜索查询

若想在该数据库中查找能够匹配某字串的地方，则使用Find Objects功能，如下图：

PL/SQL 安装配置与使用

一、安装与配置

（一）下载PLSQL Developer

（二）下载并配置免安装Oracle客户端

1. 下载Instantclient_11_2

2. 配置环境

（1）配置电脑的环境变量