Oracle存储过程详解(引用）+补充-mysql教程-PHP中文網

首頁

資料庫

mysql教程

Oracle存储过程详解(引用）+补充

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 07, 2016 pm 03:38 PM

oracle儲存引用詳解過程

一、过程 (存储过程) 过程是一个能执行某个特定操作的子程序。使用CREATE OR REPLACE创建或者替换保存在数据库中的一个子程序。示例1：声明存储过程，该过程返回dept表行数 DECLARE PROCEDURE getDeptCount AS deptCount INT;BEGIN SELECT COUNT(*) INTO d

一、过程 (存储过程)

过程是一个能执行某个特定操作的子程序。使用CREATE OR REPLACE创建或者替换保存在数据库中的一个子程序。
示例1：声明存储过程，该过程返回dept表行数
DECLARE
PROCEDURE getDeptCount
AS
deptCount INT;
BEGIN
SELECT COUNT(*) INTO deptCount FROM DEPT;
DBMS_OUTPUT.PUT_LINE('DEPT表的共有记录数：'||deptCount);
END getDeptCount;
BEGIN
getDeptCount[()];
END;

注意：此存储过程getDeptCount只在块运行时有效。
示例2：创建不带参数的存储过程，该过程返回dept表行数
CREATE OR REPLACE PROCEDURE getDeptCount
AS | IS
deptCount int;
BEGIN
SELECT COUNT(*) INTO deptCount FROM dept;
DBMS_OUTPUT.PUT_LINE('dept表共有'||deptCount||'行记录');
END [getDeptCount];

当我们创建的存储过程没有参数时，在存储过程名字后面不能有括号。在AS或者IS后至BEGIN之前是声明部分，存储过程中的声明不使用DECLARE关键字。同匿名PL/SQL块一样，EXCEPTION和声明部分都是可选的。
当我们创建的过程带有错误时，我们可以通过SELECT * FROM USER_ERRORS查看，或者使用SHOW ERRORS [ PROCEDURE Proc_Name]查看。
使用以下代码可以执行存储过程：
BEGIN
getDeptCount;
END;
以上存储过程还可以通过以下代码来简化调用：
EXEC getDeptCount[;] 
CALL getDeptCount();

注意：

并不是所有的存储过程都可以用这种方式来调用
定义无参存储过程时，存储过程名后不能加()
在块中或是通过EXEC调用存储过程时可以省略()
通过CALL调用无参存储过程必须加上()

示例3：创建带有输入参数的存储过程，该过程通过员工编号打印工资额

CREATE <span>OR</span> REPLACE PROCEDURE getSalaryByEmpNo(eNo NUMBER)  --参数的数据类型不能指定长度
<span>AS</span>
salary emp.sal%TYPE;
BEGIN
<span>SELECT</span> SAL INTO salary  FROM EMP WHERE EMPNO=eNo;
DBMS_OUTPUT.PUT_LINE(eNo||<span>'号员工的工资为'||salary);</span>
EXCEPTION
<span>WHEN</span> NO_DATA_FOUND <span>THEN</span>
DBMS_OUTPUT.PUT_LINE(<span>'没有找到该编号的员工');</span>
<span>END</span>;

当定义的存储过程含有参数时，参数的数据类型不能指定长度。参数还有输入和输出之分，本例中没有指定，默认情况为输入参数，也可显示的指定某个参数是输入参数，如(eNo IN NUMBER)。同示例1不同，该例中加入了异常处理。同示例1类似可以使用下面的两种方式调用存储过程：
BEGIN
getSalaryByEmpNo(7788);
END;
或者
EXEC getSalaryByEmpNo(7788); 或者
CALL getSalaryByEmpNo(7788);
但是如果传给一个存储过程的参数是变量时，必须使用BEGIN END块，如下：
DECLARE
no emp.empNo%TYPE;
BEGIN
no:=7788;
getSalaryByEmpNo(no);
END;

如果某个包中含有常量，也可以通过如下的方式调用：
EXEC getSalaryByEmpNo(ConstantPackage.no);
但这种方式不能再使用CALL调用。
示例4：创建含有输入和输出参数的存储过程，该过程通过员工编号查找工资额，工资额以输出参数返回
CREATE OR REPLACE PROCEDURE getSalaryByEmpNo(eNo IN NUMBER,salary OUT NUMBER)
AS
BEGIN
SELECT SAL INTO salary FROM EMP WHERE EMPNO=eNo;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('没有找到该编号的员工');
END;

当过程中含有输出参数时，调用时必须通过BEGIN END块，不能通过EXEC或CALL调用。如：
DECLARE
salary NUMBER(7,2);
BEGIN
getSalaryByEmpNo(7788,salary);
DBMS_OUTPUT.PUT_LINE(salary);
END;

示例5：创建参数类型既是输入参数也是输出参数的过程

CREATE <span>OR</span> REPLACE PROCEDURE getSalaryByEmpNo(noSalary <span>IN</span> OUT NUMBER)
<span>AS</span>
BEGIN
<span>SELECT</span> SAL INTO noSalary FROM EMP WHERE EMPNO=noSalary;
EXCEPTION
<span>WHEN</span> NO_DATA_FOUND <span>THEN</span>
DBMS_OUTPUT.PUT_LINE(<span>'没有找到该编号的员工');</span>
<span>END</span>;

调用如下：

<span>DECLARE</span>
<span>no</span> NUMBER(7,2);
<span>BEGIN</span>
<span>no</span>:=7788;
getSalaryByEmpNo(<span>no</span>);
DBMS_OUTPUT.PUT_LINE(<span>no</span>);
<span>END</span>;

示例6：创建带有默认值的过程

<span>CREATE</span> <span>OR</span> REPLACE <span>PROCEDURE</span> addEmp
(
empNo NUMBER,
eName VARCHAR2,
job  VARCHAR2 :=<span>'CLERK'</span>,
mgr  NUMBER,
hiredate <span>DATE</span>  <span>DEFAULT</span> SYSDATE,
sal  NUMBER  <span>DEFAULT</span> 1000,
comm  NUMBER  <span>DEFAULT</span> 0,
deptNo NUMBER  <span>DEFAULT</span> 30
)
<span>AS</span>
<span>BEGIN</span>
INSERT <span>INTO</span> emp <span>VALUES</span>(empNo,eName,job,mgr,hiredate,sal,comm,deptNo);
<span>END</span>;

调用如下：

<span>EXEC</span> addEmp(7776,<span>'zhangsan'</span>,<span>'CODER'</span>,7788,<span>'06-1月-2000'</span>,2000,0,10);  --没有使用默认值
<span>EXEC</span> addEmp(7777,<span>'lisi'</span>,<span>'CODER'</span>,7788,<span>'06-1月-2000'</span>,2000,<span>NULL</span>,10);  --可以使用NULL值
<span>EXEC</span> addEmp(7778,<span>'wangwu'</span>,mgr=>7788);  --使用默认值
<span>EXEC</span> addEmp(mgr=>7788,empNo=>7779,eName=><span>'sunliu'</span>);  --更改参数顺序

示例7：使用NOCOPY编译提示
当参数是大型数据结构时，如集合、记录和对象实例，把它们的内容全部拷贝给形参会降低执行速度，消耗大量内存。为了防止这样的情况发生，我们可以使用 NOCOPY提示来让编译器按引用传递方式给IN OUT模式的参数。
DECLARE
TYPE DeptList IS TABLE OF VARCHAR2(10);
dList DeptList:=DeptList('CORESUN','CORESUN','CORESUN','CORESUN');
PROCEDURE My_Proc(d IN OUT NOCOPY DeptList)
AS...

注意：NOCOPY只是一个提示，而不是指令。即使有时候我们使用了NOCOPY,但编译器有可能仍然会进行值拷贝。通常情况下NOCOPY是可以成功的。

二、维护过程

1、删除存储过程

<span>DROP</span> <span>PROCEDURE</span> Proc_Name;

2、查看过程状态

<span>SELECT</span> object_name,status  <span>FROM</span> USER_OBJECTS <span>WHERE</span> object_type=<span>'PROCEDURE'</span>;

3、重新编译过程

<span>ALTER</span> <span>PROCEDURE</span> Proc_Name COMPILE;

4、查看过程代码

<span>SELECT</span> * <span>FROM</span> USER_SOURCE <span>WHERE</span> TYPE=<span>'PROCEDURE'</span>;

三、参数的理解

-- 输出参数不可以修改解决的方法有两种
--1 把参数改成输入参数
--2 就是参数改成可输入输出的参数;
调用过程的三个方式
1 就是使用call
在只用call方式调用函数的时候,必须加要括号,有参数,还要加参数值

这个方式在命令窗口,调用过程,将不会出现输入的数据.
2 就是使用exec 命令,进行命令调用过程, 使用命令,就必须在命令行里面输入
过程名,这个命令窗口中,可加可不加() ,如果有参数的,就一定要加,还有参数值,参数值的类型要与
变量类型相同.
3 在语句块中进行调用过程,这个方式和命令模式类似,他们都是可要可不要(),
-- 在2 和 3 中的没有括号的情况是,过程没有参数 ,如果有,就必须要有()
输出参数的特点
1 一个过程中,如果有输出参数(OUT 参数),在调用过程的使用,也要传入一个参数, 这个参数可以不用在调用的地方
进行赋值,就直接传入一个声明好的一个变量,用来接受存储过程中的输出参数的值(OUT 参数)
2 输入参数值不可以改变在过程中,
注意: 在存储过程中,他的参数类型不可以设置它的大小 ;
例如;
CREATE OR REPLACE PROCEDURE hello(
p_name IN VARCHAR2(12),
p_age OUT NUMBER(10,2)
)
IS
BEGIN

如果有输出参数就必须有有一个参数进行接收 ;

<span>CREATE</span> <span>OR</span> REPLACE <span>PROCEDURE</span> hello(
p_name <span>IN</span> VARCHAR2,
p_age <span>OUT</span> emp.sal%TYPE
)
<span>IS</span>
<span>BEGIN</span>
<span>SELECT</span> emp.sal + 3131 <span>INTO</span> p_age <span>FROM</span> emp <span>WHERE</span> empno = 7788 ;
dbms_output.put_line( p_age);
<span>END</span> ;

--------- 块中调用方法

<span>DECLARE</span>
v_nanme varchar2(12);
v_age NUMBER (12,2);
<span>BEGIN</span>
hello (v_nanme,v_age);
dbms_output.put_line(v_age);
<span>END</span> ;

-- 在这个过程中传入的v_age 就是接受存储过程输出参数的值 ; 类似于Java的中的返回值

-- 理解 in out 参数

<span>CREATE</span> <span>OR</span> REPLACE <span>PROCEDURE</span> hello1 (
p_name <span>IN</span> <span>OUT</span> emp.ename%TYPE
)
<span>IS</span>
<span>BEGIN</span>
<span>-- SELECT emp.ename INTO p_name FROM emp ;</span>
p_name:=<span>'a;sk , '</span> || p_name ;
<span>END</span> ;
------------------------------------------------------------------------<span>--</span>
<span>DECLARE</span>
v_nanme varchar2(12);
<span>BEGIN</span> 
v_nanme:=<span>'12312'</span>;
hello1(v_nanme);

补充：sqlplus中执行含有输出参数为游标的存储过程

实例6：

sqlplus创建存储过程，使用如下：

<span>SQL</span>><span>create</span> <span>or</span> replace <span>procedure</span> test1(rcursor <span>out</span> sys_refcursor) <span>as</span>
<span>begin</span>
  <span>open</span> rcursor <span>for</span>
  <span>select</span> decode(row_number() <span>over</span>(partition <span>by</span> deptno <span>order</span> <span>by</span> ename),
                  1,
                   deptno,
                   <span>null</span>) deptno,
             t.ename
        <span>from</span> scott.emp t;
<span>end</span>;
/

--使用sqlplus执行上面创建的带有游标输出参数的存储过程

<span>SQL</span>> var cur refcursor
<span>SQL</span>> <span>exec</span> test1(:cur);
PL/<span>SQL</span> <span>procedure</span> successfully completed.
<span>SQL</span>> <span>print</span> cur;
    DEPTNO ENAME
--------<span>-- ----------</span>
        10 CLARK
           KING
           MILLER
        20 ADAMS
           FORD
           JONES
           SCOTT
           SMITH
        30 ALLEN
           BLAKE
           JAMES

    DEPTNO ENAME
--------<span>-- ----------</span>
           MARTIN
           TURNER
           WARD

14 <span>rows</span> selected.

陳述

本文內容由網友自願投稿，版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容，請聯絡admin@php.cn

說明InnoDB重做日誌和撤消日誌的作用。Apr 15, 2025 am 12:16 AM

InnoDB使用redologs和undologs確保數據一致性和可靠性。 1.redologs記錄數據頁修改，確保崩潰恢復和事務持久性。 2.undologs記錄數據原始值，支持事務回滾和MVCC。

在解釋輸出（類型，鍵，行，額外）中要查找的關鍵指標是什麼？Apr 15, 2025 am 12:15 AM

EXPLAIN命令的關鍵指標包括type、key、rows和Extra。 1）type反映查詢的訪問類型，值越高效率越高，如const優於ALL。 2）key顯示使用的索引，NULL表示無索引。 3）rows預估掃描行數，影響查詢性能。 4）Extra提供額外信息，如Usingfilesort提示需要優化。

在解釋中使用臨時狀態以及如何避免它是什麼？Apr 15, 2025 am 12:14 AM

Usingtemporary在MySQL查詢中表示需要創建臨時表，常見於使用DISTINCT、GROUPBY或非索引列的ORDERBY。可以通過優化索引和重寫查詢避免其出現，提升查詢性能。具體來說，Usingtemporary出現在EXPLAIN輸出中時，意味著MySQL需要創建臨時表來處理查詢。這通常發生在以下情況：1)使用DISTINCT或GROUPBY時進行去重或分組；2)ORDERBY包含非索引列時進行排序；3)使用複雜的子查詢或聯接操作。優化方法包括：1)為ORDERBY和GROUPB

描述不同的SQL交易隔離級別（讀取未讀取，讀取，可重複的讀取，可序列化）及其在MySQL/InnoDB中的含義。Apr 15, 2025 am 12:11 AM

MySQL/InnoDB支持四種事務隔離級別：ReadUncommitted、ReadCommitted、RepeatableRead和Serializable。 1.ReadUncommitted允許讀取未提交數據，可能導致臟讀。 2.ReadCommitted避免臟讀，但可能發生不可重複讀。 3.RepeatableRead是默認級別，避免臟讀和不可重複讀，但可能發生幻讀。 4.Serializable避免所有並發問題，但降低並發性。選擇合適的隔離級別需平衡數據一致性和性能需求。

MySQL與其他數據庫：比較選項Apr 15, 2025 am 12:08 AM

MySQL適合Web應用和內容管理系統，因其開源、高性能和易用性而受歡迎。 1)與PostgreSQL相比，MySQL在簡單查詢和高並發讀操作上表現更好。 2)相較Oracle，MySQL因開源和低成本更受中小企業青睞。 3)對比MicrosoftSQLServer，MySQL更適合跨平台應用。 4)與MongoDB不同，MySQL更適用於結構化數據和事務處理。

MySQL索引基數如何影響查詢性能？Apr 14, 2025 am 12:18 AM

MySQL索引基数对查询性能有显著影响：1.高基数索引能更有效地缩小数据范围，提高查询效率；2.低基数索引可能导致全表扫描，降低查询性能；3.在联合索引中，应将高基数列放在前面以优化查询。

MySQL：新用戶的資源和教程Apr 14, 2025 am 12:16 AM

MySQL學習路徑包括基礎知識、核心概念、使用示例和優化技巧。 1)了解表、行、列、SQL查詢等基礎概念。 2)學習MySQL的定義、工作原理和優勢。 3)掌握基本CRUD操作和高級用法，如索引和存儲過程。 4)熟悉常見錯誤調試和性能優化建議，如合理使用索引和優化查詢。通過這些步驟，你將全面掌握MySQL的使用和優化。