opebet体育泛泛谈PetShop之动存储过程和PLSQL批量处理(附案例)Oracle编程入门经典 第11段 过程、函数和程序包。

目录

目录

1       大概思路… 2

11.1          优势以及利益… 1

2       PetShop4的经典数据库连接代码回顾… 3

11.2          过程… 1

2.1        PrepareCommand. 3

11.2.1       语法… 2

2.2        ExecuteNoQuery. 4

11.2.2       建立或者替换… 2

3       如何勾勒好一个的OracleHelper 5

11.2.3       执行存储过程… 3

3.1        PetShop的OracleHelper 5

11.2.4       安全… 3

3.2        OracleHelper 6

考查:访问了程… 3

4       代码示例… 6

11.2.5       参数… 5

4.1        使用存储过程… 6

11.2.6       局域声明… 12

4.2        批处理的动PL/SQL. 7

试验:AUTHID DEFINER. 13

4.3        批处理的用事务… 8

行事原理… 14

5       运行效果… 10

试验:AUTHID CURRENT_USER. 14

6       小结… 10

考:使用自动事务处理进行日志记录… 15

 

11.3          函数… 17

1       大概思路

 opebet体育 1

备考:黄色呢影响参数

11.3.1       语法… 17

2       PetShop4的藏数据库连接代码回顾

PetShop4有3个函数,具体有:

ExecuteReader:可以读一个发明的记录,只能读不能够写。

ExecuteScalar:只能读一漫漫记下,一般用来判定数据库是否发生数量等,只能读不克写。

ExecuteNonQuery:可以形容为可以读。

此间介绍一下PrepareCommand、ExecuteNoQuery。

11.3.2       返回值… 17

2.1   PrepareCommand

顾:当前函数是private的,不提供被外部调用。

        /// <summary>
        /// Internal function to prepare a command for execution by the database
        /// </summary>
        /// <param name="cmd">Existing command object</param>
        /// <param name="conn">Database connection object</param>
        /// <param name="trans">Optional transaction object</param>
        /// <param name="cmdType">Command type, e.g. stored procedure</param>
        /// <param name="cmdText">Command test</param>
        /// <param name="commandParameters">Parameters for the command</param>
        private static void PrepareCommand(OracleCommand cmd, OracleConnection conn, OracleTransaction trans, CommandType cmdType, string cmdText, OracleParameter[] commandParameters) {
            //Open the connection if required
            if (conn.State != ConnectionState.Open)
                conn.Open();

            //Set up the command
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            cmd.CommandType = cmdType;

            //Bind it to the transaction if it exists
            if (trans != null)
                cmd.Transaction = trans;

            // Bind the parameters passed in
            if (commandParameters != null) {
                foreach (OracleParameter parm in commandParameters)
                    cmd.Parameters.Add(parm);
            }
        }

试验:ITE函数… 18

2.2   ExecuteNoQuery

这函数:传入连接串、执行项目、SQL、参数

       /// <summary>
        /// Execute a database query which does not include a select
        /// </summary>
        /// <param name="connString">Connection string to database</param>
        /// <param name="cmdType">Command type either stored procedure or SQL</param>
        /// <param name="cmdText">Acutall SQL Command</param>
        /// <param name="commandParameters">Parameters to bind to the command</param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters) {
            // Create a new Oracle command
            OracleCommand cmd = new OracleCommand();
            //Create a connection
            using (OracleConnection connection = new OracleConnection(connectionString)) {
                //Prepare the command
                PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
                //Execute the command
                int val = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                return val;
            }
        }

斯函数:传入事务、执行项目、SQL、参数

      /// <summary>
        /// Execute an OracleCommand (that returns no resultset) against an existing database transaction
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// e.g.: 
        ///  int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "PublishOrders", new OracleParameter(":prodid", 24));
        /// </remarks>
        /// <param name="trans">an existing database transaction</param>
        /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">the stored procedure name or PL/SQL command</param>
        /// <param name="commandParameters">an array of OracleParamters used to execute the command</param>
        /// <returns>an int representing the number of rows affected by the command</returns>
        public static int ExecuteNonQuery(OracleTransaction trans, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters) {
            OracleCommand cmd = new OracleCommand();
            PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
            int val = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            return val;
        }

 此函数:传入连接、执行项目、SQL、参数

       /// <summary>
        /// Execute an OracleCommand (that returns no resultset) against an existing database connection
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// e.g.: 
        ///  int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new OracleParameter(":prodid", 24));
        /// </remarks>
        /// <param name="conn">an existing database connection</param>
        /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">the stored procedure name or PL/SQL command</param>
        /// <param name="commandParameters">an array of OracleParamters used to execute the command</param>
        /// <returns>an int representing the number of rows affected by the command</returns>
        public static int ExecuteNonQuery(OracleConnection connection, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters) {
            OracleCommand cmd = new OracleCommand();
            PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
            int val = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            return val;
        }

11.3.3       确定性… 19

3       如何勾勒好一个之OracleHelper

11.3.4       常见错误… 19

3.1   PetShop的OracleHelper

PetShop不是描写好了吧?为什么还要好写?

eg:PetShop4的函数不足以方便我们操作数据库,如批量栽需要防注入的参数时,需要等所有安插了再交付全体业务。

eg:PetShop4的函数在拍卖存储过程里还不到家,返回值没有针对。

11.4          程序包… 20

3.2   OracleHelper

小心:PetShop4在参数上在调用OracleHelper考虑了缓存,这里少未考虑。

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data;
using System.Data.OracleClient;
using System.Collections;
namespace Util
{
    public abstract class OracleHelper
    {
        /// <summary>
        /// 准备存储过程执行查询
        /// </summary>        
        /// <param name="connectionString">数据库连接</param>
        public static OracleTransaction GetTrans(string connectionString)
        {
            OracleConnection conn = new OracleConnection(connectionString);
            conn.Open();
            OracleTransaction trans = conn.BeginTransaction();
            return trans;
        }

        /// <summary>
        /// 返回视图
        /// </summary>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public static DataView ExecuteView(String connectionString, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)
        {
            OracleCommand cmd = new OracleCommand();
            using (OracleConnection conn = new OracleConnection(connectionString))
            {
                PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
                DataSet ds = new DataSet();
                OracleDataAdapter da = new OracleDataAdapter(cmd);
                da.Fill(ds);
                DataView dv = ds.Tables[0].DefaultView;
                cmd.Parameters.Clear();
                return dv;
            }
        }
        /// <summary>
        /// 执行并返回影响行数
        /// </summary>
        /// <param name="connectionString">连接字符串</param>
        /// <param name="cmdType">执行类型</param>
        /// <param name="cmdText">执行文本</param>
        /// <param name="commandParameters">参数</param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, IList commandParameters)
        {
            OracleCommand cmd = new OracleCommand();
            using (OracleConnection connection = new OracleConnection(connectionString))
            {
                PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
                int val = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                return val;
            }
        }

        /// <summary>
        /// Execute a database query which does not include a select
        /// </summary>
        /// <param name="connString">Connection string to database</param>
        /// <param name="cmdType">Command type either stored procedure or SQL</param>
        /// <param name="cmdText">Acutall SQL Command</param>
        /// <param name="commandParameters">Parameters to bind to the command</param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)
        {
            // Create a new Oracle command
            OracleCommand cmd = new OracleCommand();

            //Create a connection
            using (OracleConnection connection = new OracleConnection(connectionString))
            {

                //Prepare the command
                PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);

                //Execute the command
                int val = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                return val;
            }
        }



        ///    <summary>
        ///    Execute    a OracleCommand (that returns a 1x1 resultset)    against    the    specified SqlTransaction
        ///    using the provided parameters.
        ///    </summary>
        ///    <param name="transaction">A    valid SqlTransaction</param>
        ///    <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
        ///    <param name="commandText">The stored procedure name    or PL/SQL command</param>
        ///    <param name="commandParameters">An array of    OracleParamters used to execute the command</param>
        ///    <returns>An    object containing the value    in the 1x1 resultset generated by the command</returns>
        public static int ExecuteNonQuery(OracleTransaction trans, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)
        {
            OracleCommand cmd = new OracleCommand();
            PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
            int val = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            return val;
        }

        /// <summary>
        /// 执行并返回影响行数,得手动关闭数据库连接
        /// </summary>
        /// <param name="connection">连接字符串</param>
        /// <param name="cmdType">执行类型</param>
        /// <param name="cmdText">执行文本</param>
        /// <param name="commandParameters">参数</param>
        /// <returns></returns>
        public static int ExecuteNonQuery(OracleConnection connection, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)
        {

            OracleCommand cmd = new OracleCommand();
            PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
            int val = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            return val;
        }

        /// <summary>
        /// Execute a select query that will return a result set
        /// </summary>
        /// <param name="connString">Connection string</param>
        //// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">the stored procedure name or PL/SQL command</param>
        /// <param name="commandParameters">an array of OracleParamters used to execute the command</param>
        /// <returns></returns>
        public static OracleDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)
        {

            //Create the command and connection
            OracleCommand cmd = new OracleCommand();
            OracleConnection conn = new OracleConnection(connectionString);

            try
            {
                //Prepare the command to execute
                PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);

                //Execute the query, stating that the connection should close when the resulting datareader has been read
                OracleDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                return rdr;

            }
            catch
            {

                //If an error occurs close the connection as the reader will not be used and we expect it to close the connection
                conn.Close();
                throw;
            }
        }


        /// <summary>
        /// Internal function to prepare a command for execution by the database
        /// </summary>
        /// <param name="cmd">Existing command object</param>
        /// <param name="conn">Database connection object</param>
        /// <param name="trans">Optional transaction object</param>
        /// <param name="cmdType">Command type, e.g. stored procedure</param>
        /// <param name="cmdText">Command test</param>
        /// <param name="commandParameters">Parameters for the command</param>
        private static void PrepareCommand(OracleCommand cmd, OracleConnection conn, OracleTransaction trans, CommandType cmdType, string cmdText, OracleParameter[] commandParameters)
        {

            //Open the connection if required
            if (conn.State != ConnectionState.Open)
                conn.Open();

            //Set up the command
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            cmd.CommandType = cmdType;

            //Bind it to the transaction if it exists
            if (trans != null)
                cmd.Transaction = trans;

            // Bind the parameters passed in
            if (commandParameters != null)
            {
                // 若参数Value值为null时,插入到数据库的值应该为DBNull.Value,且为ParameterDirection.Input
                foreach (OracleParameter parm in commandParameters)
                    if (parm.Value == null && parm.Direction == ParameterDirection.Input)
                    {
                        cmd.Parameters.Add(parm.ParameterName, parm.OracleType, parm.Size).Value = DBNull.Value;
                    }
                    else
                    {
                        cmd.Parameters.Add(parm);
                    }
            }
        }

        /// <summary>
        /// Internal function to prepare a command for execution by the database
        /// </summary>
        /// <param name="cmd">Existing command object</param>
        /// <param name="conn">Database connection object</param>
        /// <param name="trans">Optional transaction object</param>
        /// <param name="cmdType">Command type, e.g. stored procedure</param>
        /// <param name="cmdText">Command test</param>
        /// <param name="commandParameters">Parameters for the command</param>
        private static void PrepareCommand(OracleCommand cmd, OracleConnection conn, OracleTransaction trans, CommandType cmdType, string cmdText, IList commandParameters)
        {

            //Open the connection if required
            if (conn.State != ConnectionState.Open)
                conn.Open();

            //Set up the command
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            cmd.CommandType = cmdType;

            //Bind it to the transaction if it exists
            if (trans != null)
                cmd.Transaction = trans;

            // Bind the parameters passed in
            if (commandParameters != null)
            {
                // 若参数Value值为null时,插入到数据库的值应该为DBNull.Value,且为ParameterDirection.Input
                foreach (OracleParameter parm in commandParameters)
                    if (parm.Value == null && parm.Direction == ParameterDirection.Input)
                    {
                        cmd.Parameters.Add(parm.ParameterName, parm.OracleType, parm.Size).Value = DBNull.Value;
                    }
                    else
                    {
                        cmd.Parameters.Add(parm);
                    }
            }
        }
    }
}

 

11.4.1       语法… 20

4       代码示例

11.4.2       规范… 20

4.1   使用存储过程

      /// <summary>
        /// 新增
        /// </summary>
        /// <param name="v_dept">实体</param>
        /// <param name="re">返回ID</param>
        /// <param name="msg">返回消息</param>
        /// <returns></returns>
        private void executeWithOracleTrans(DEPT v_dept, ref int re, ref string msg)
        {
            try
            {
                OracleParameter[] paras = new OracleParameter[5];
                paras[0] = new OracleParameter("P_DEPTNO", OracleType.Number);
                paras[0].Value = v_dept.DEPTNO;
                paras[1] = new OracleParameter("P_DNAME", OracleType.VarChar);
                paras[1].Value = v_dept.DNAME;
                paras[2] = new OracleParameter("P_LOC", OracleType.VarChar);
                paras[2].Value = v_dept.LOC;
                paras[3] = new OracleParameter("X_RE", OracleType.Int32);
                paras[3].Direction = ParameterDirection.Output;
                paras[4] = new OracleParameter("X_MSG", OracleType.VarChar, 100);
                paras[4].Direction = ParameterDirection.Output;

               OracleHelper.ExecuteNonQuery(this.OracleConnectString, CommandType.StoredProcedure, "PKG_DEMO.Dept_Add", paras);
                re = Convert.ToInt32(paras[3].Value);
                msg = paras[4].Value.ToString();
            }
            catch (Exception ex)
            {
                re = 9;
                msg = ex.Message;
            }
        }

11.4.3       主体… 21

4.2   批处理的用PL/SQL

      /// <summary>
        /// 用PL/SQL增加
        /// </summary>
        /// <param name="list_dept"></param>
        /// <param name="re"></param>
        /// <param name="msg"></param>
        private void executeWithPLSQL(IList<DEPT> list_dept, ref int re, ref string msg)
        {
            string sql = string.Empty;
            string insert_sql = string.Empty;
            List<OracleParameter> list_parm = new List<OracleParameter>();
            try
            {
                int i = 0;
                foreach (DEPT v_dept in list_dept)
                {
                    insert_sql += "insert into DEPT (DEPTNO, DNAME, LOC) values(:P_DEPTNO" + i + ", :P_DNAME" + i + ", :P_LOC" + i + ");";
                    OracleParameter[] paras = new OracleParameter[3];
                    paras[0] = new OracleParameter("P_DEPTNO" + i, OracleType.Number);
                    paras[0].Value = v_dept.DEPTNO;
                    paras[1] = new OracleParameter("P_DNAME" + i, OracleType.VarChar);
                    paras[1].Value = v_dept.DNAME;
                    paras[2] = new OracleParameter("P_LOC" + i, OracleType.VarChar);
                    paras[2].Value = v_dept.LOC;
                    list_parm.Add(paras[0]);
                    list_parm.Add(paras[1]);
                    list_parm.Add(paras[2]);
                    i++;
                }
                sql = "begin " +
                    insert_sql +
                  ":X_RE  := 1; " +
                  ":X_MSG := '提示:新增成功!'; " +
                  "commit; " +
                "exception " +
                  "when others then " +
                    "rollback; " +
                    ":X_RE  := 9; " +
                    ":X_MSG := '操作失败:[' || sqlcode || ':' || sqlerrm || ']'; " +
                "end; ";
                OracleParameter x_re = new OracleParameter("X_RE", OracleType.Int32);
                x_re.Direction = ParameterDirection.Output;
                OracleParameter x_msg = new OracleParameter("X_MSG", OracleType.VarChar, 100);
                x_msg.Direction = ParameterDirection.Output;
                list_parm.Add(x_re);
                list_parm.Add(x_msg);
                 OracleHelper.ExecuteNonQuery(this.OracleConnectString, CommandType.Text, sql, list_parm);
                re = Convert.ToInt32(x_re.Value);
                msg = x_msg.Value.ToString();
            }
            catch (Exception ex)
            {
                re = 9;
                msg = ex.Message;
            }
        }

 

试:私有过程及函数… 22

4.3   批处理的运工作

 

       /// <summary>
        /// 用事务新增
        /// </summary>
        /// <param name="list_dept"></param>
        /// <param name="re"></param>
        /// <param name="msg"></param>
        private void executeWithTrans(IList<DEPT> list_dept, ref int re, ref string msg)
        {
            // 启用事务进行控制
            OracleTransaction myTrans = OracleHelper.GetTrans(this.OracleConnectString);
            OracleConnection conn = myTrans.Connection;
            try
            {
                string sql = string.Empty;
                foreach (DEPT o in list_dept)
                {
                    sql = "insert into DEPT(DEPTNO,DNAME,LOC) values(:P_DEPTNO,:P_DNAME,:P_LOC)";
                    OracleParameter[] paras = new OracleParameter[3];
                    paras[0] = new OracleParameter("P_DEPTNO", OracleType.Int32);
                    paras[0].Value = o.DEPTNO;
                    paras[1] = new OracleParameter("P_DNAME", OracleType.VarChar);
                    paras[1].Value = o.DNAME;
                    paras[2] = new OracleParameter("P_LOC", OracleType.VarChar);
                    paras[2].Value = o.LOC;
                    OracleHelper.ExecuteNonQuery(myTrans, CommandType.Text, sql, paras);
                }
                myTrans.Commit();
                re = 1;
            }
            catch (Exception ex)
            {
                myTrans.Rollback();
                re = 9;
                msg = ex.Message;
            }
            finally
            {
                conn.Close();
            }
        }

11.4.4       程序包变量和其它声明… 25

5       运行效果

 opebet体育 2

考:程序包变量… 25

6       小结

学好.Net,从PetShop开始。

源代码下载:

http://files.cnblogs.com/yongfeng/HowToConnectDataBase.rar

数据库存储过程下载:

http://files.cnblogs.com/yongfeng/Package.rar

PDF下载:

http://files.cnblogs.com/yongfeng/20130301.rar

拍摄下载:

http://files.cnblogs.com/yongfeng/Media.rar

 

 

实例化程序块… 27

11.4.5       重载… 28

试验:重载SWAP() 29

11.4.6       依赖性… 31

测验:依赖性实例… 31

11.4.7       程序包的优势… 33

考试:改变程序包… 33

11.5          数据词典… 34

11.5.1       列出装有的用户存储过程… 34

11.5.2       从数据库被搜索用户代码… 34

11.6          包装实用工具… 35

11.7          小结… 36

 

 

 

 

开发者能够命名他们的PL/SQL程序块,为它确定参数,将她存储于数据库中,并且由外数据库客户或实用工具中援或者运行它们,例如
SQL*Plus、Pro*C,甚至是JDBC。

马上是听PL/SQL程序名为存储过程及函数。它们的成团称为程序包。在本章中,我们且解释以过程、函数和程序包之老三怪优势、这三种植相似结构里的区别。

 

Oracle 9i产品帮助文档:

http://docs.oracle.com/cd/B10501_01/index.htm

不过根据自己需要开展查询,包含了很多之文档。

 

Sample Schemas的目录:

http://docs.oracle.com/cd/B10501_01/server.920/a96539/toc.htm

 

Sample Schemas的文档(示例模式的表及介绍):

http://docs.oracle.com/cd/B10501_01/server.920/a96539.pdf

11.1  优势与补

俺们来开分析应用存储过程、函数和次包代替匿名PL/SQL程序块的优势:

●可扩展性。使用过程及函数可以为开发者增加效益。编写用户自己的全程可以于用户灵活地壮大数据库的着力能力。

●模块化。任何可以之开发者都懂编写模块化代码的要紧。用户应经过编制小型、可管理的经过在用户使用被定义单独的处理模块,它们可团体在齐形成更扑朔迷离的应用。

●可重用性。由于这些全程命名后以保留于数据库中,所以任何利用还能够推行其(只有它更富有确切的权力到位这项工作)。这种用代码的能力在出进程被很重要。

●可维护性。继续用及有些被之演示,考虑当存储日志信息的阐明出反以后,会产出啊动静。如果用户要转移的独发生一个地方,那么就会如用户代码更易于保障。

●抽象和数据隐藏。我们设用户在调用不是由用户编写的称之为也GIVE_EMPLOYEE
RAISE()的经过,用户可通往它们提供部分音,让它来工作,换句话说,用户可据此她来员工加薪。它于用户来讲是个黑箱。它怎么贯彻并无重要,用户所急需掌握之即使是其会就的做事。所有处理过程的落实同错综复杂都针对调用者进行了影。

●安全性。就假设作为安全体制以的视图一样,过程为负有同等的功力。用户可以使为用户采取,使得访问数据库的唯一办法尽管是通过用户提供的进程与函数。这不但可以给数进一步安全,而且还好包它们的是。

11.2  过程

积存过程(stored
procedure)从根本上讲就是命名PL/SQL程序块,它可以于予以参数、存储在数据库中,然后由其余一个运用或PL/SQL例程激活(或者调用)。建立最简单易行的蕴藏过程:

SQL> create procedure my_proc as
  2  begin
  3   null;
  4  end my_proc;
  5  /

过程已创建。

 

注意:

可小心到,当起存储过程的当儿,用户要以经过的尾声一行放入一个/,来告诉SQL*Plus执行用户先输入或者载入的代码行。

 

11.2.1             语法

现行我们来谈谈起存储过程的语法:

1 [CREATE [OR REPLACE]]
2 PROCEDURE procedure_name[(parameter[,parameter]…)]
3 [AUTHID (DEFINER | CURRENT_USER)] {IS | AS}
4 [PRAGMA AUTONOMOUS_TRANSACTION;] 
5 [local declarations]
6 BEGIN
7    executable statements
8 [EXCEPTION
9 exception handlers]
10 END [name];

 

这语法的大部分代码都掩藏名PL/SQL程序块老相似。在BEGIN-EXCEPTION-END程序块之间只有为数不多之差异。

11.2.2             建立或者替换

咱来编排最初的演示,完成第一单计算机程序要形成的享有工作,打印“Hello
World”。

SQL> create procedure my_proc as
  2  begin
  3   dbms_output.put_line('Hello World');
  4  end my_proc;
  5  /
create procedure my_proc as
                 *
ERROR 位于第 1 行:
ORA-00955: 名称已由现有对象使用

 

倘我们只要采取科学的语法,我们不怕假设针对过程进展改动,如下所示:

SQL> create or replace procedure my_proc as
  2  begin
  3   dbms_output.put_line('Hello World');
  4  end my_proc;
  5  /

过程已创建。

 

11.2.3             执行存储过程

既是都拥有了蕴藏过程,接下去我们就是设查阅其的实在运作状况,确保它就了我们愿意它好的办事。为了执行用户的经过,用户可以起PL/SQL匿名程序块被对该展开调用:

SQL> set serverout on
SQL> begin
  2   my_proc;
  3  end;
  4  /
Hello World

PL/SQL 过程已成功完成

 

此外,还有雷同种快捷方式可以要调用过程更爱。用户可以当SQL*Plus中使用EXECUTE命令,或者科室为EXEC:

SQL> execute my_proc;
Hello World

PL/SQL 过程已成功完成

 

调用EXECUTE
<PROCEDURE_NAME>与于匿名PL/SQL序块被调用过程安全一样。它是一个SQL*Plus的便捷函数,而无是PL/SQL命令。

11.2.4             安全

鉴于存储过程存储于数据库中,所以她是数据库对象。就似其他对象同,可以由此特权控制对它的访问。表和视图具有SELECT、INSERT、UPDATE和DELETE这样的特权,而经过具有EXECUTE特权。将经过及之EXECUTE特权赋予用户还是角色,可以于这些实体有能力运行它们:将她赋予角色PUBLIC可以被具备用户还好下此历程。

考查:访问过程

(1)      
我们第一要树立三独数据库用户CHRIS、SEAN和MARK。用户用运用有DBA角色的账号进行连接来好这项工作。

SQL> conn system/zyf;
已连接。
SQL> create user chris identified by chris;
用户已创建

SQL> grant connect,resource to chris;
授权成功。

SQL> create user sean identified by sean;
用户已创建

SQL> grant connect,resource to sean;
授权成功。

SQL> create user mark identified by mark;
用户已创建

SQL> grant connect,resource to mark;
授权成功。

 

(2)      
现在,我们若当用户MARK建立一个过程。这是非常简单的经过。它不会见履另外工作,但是好辅助我们展示看特权:

SQL> conn mark/mark;
已连接。
SQL> create procedure marks_procedure as
  2  begin
  3   null;
  4  end;
  5  /

过程已创建。

 

(3)       现在,我们尝试当用户CHRIS执行这历程:

SQL> conn chris/chris;
已连接。
SQL> execute mark.marks_procedure
BEGIN mark.marks_procedure; END;

      *
ERROR 位于第 1 行:
ORA-06550: 第 1 行, 第 7 列:
PLS-00201: 必须说明标识符 'MARK.MARKS_PROCEDURE'
ORA-06550: 第 1 行, 第 7 列:
PL/SQL: Statement ignored

 

CHRIS不克看同履行MARK的过程。就似乎任何对象同,默认情况下,最初只有有所有者可以看它们的经过。为了允许其他用户访问他们的长河,这些所有者必须明白为其他用户给访问权。

(4)       我们回到去,为CHRIS赋予过程及的EXECUTE特权。

SQL> conn mark/mark;
已连接。
SQL> grant execute on marks_procedure to chris;
授权成功。

SQL> conn chris/chris;
已连接。
SQL> execute mark.marks_procedure
PL/SQL 过程已成功完成。

 

当CHRIS被与了EXECUTE特权之后,他就是可知推行MARK的过程。

(5)       现在,我们来作用户SEAN尝试执行是过程。

SQL> conn sean/sean;
已连接。
SQL> execute mark.marks_procedure
BEGIN mark.marks_procedure; END;

      *
ERROR 位于第 1 行:
ORA-06550: 第 1 行, 第 7 列:
PLS-00201: 必须说明标识符 'MARK.MARKS_PROCEDURE'
ORA-06550: 第 1 行, 第 7 列:
PL/SQL: Statement ignored

 

(6)       现在,我们来作为用户MARK进行连续,并且GRANT
EXECUTE给PUBLIC,接下去,当我们做到这些操作下,看看SEAN是否能够尽MARK的长河:

SQL> conn mark/mark;
已连接。
SQL> grant execute on marks_procedure to public;

授权成功。

SQL> conn sean/sean
已连接。
SQL> execute mark.marks_procedure

PL/SQL 过程已成功完成。

 

注意:

得小心到,为一个历程将EXECUTE赋予PUBLIC可以被具备的数据库用户还能够利用她。尽管尚无往SEAN赋予直接的访问权,人还可以经过PUBLIC角色继承EXECUTE特权。

11.2.5             参数

过程可以开展参数化处理。这意味着,过程的调用者传递一个值
即可使它们。参数可以是任何合法的PL/SQL类型,它可以使用三种模式:IN、OUT和IN
OUT。

●IN
参数要通过调用者传入,只会由过程读取。这就是说,它们是独自读值,不能够由过程所改变。这是参数作为遭到最常用的模式,是从未叫起参数模式指示器时的默认模式。

●OUT
参数能够由过程写入。这些参数适用于经过要往调用者返回多漫漫消息的时节。必须于OUT参数传递返回值。

●IN OUT
参数就设该称所暗示的那么,同时具有IN参数与OUT参数的特征。过程会读取和写入它们的价值。

 

  1. INT参数

咱俩来作为用户CHRIS进行连续,并且成立简单的经过INSERT_INTO_T()。就如过程名称所暗示的那样,这个进程将承受一个参数P_PARM,并以其的价插入到表T中:

SQL> conn chris/chris;
已连接。
SQL> create table t(
  2   n number
  3  )
  4  /
表已创建。

SQL> create or replace
  2  procedure insert_into_t(p_parm in number) is
  3  begin
  4   insert into t values(p_parm);
  5  end insert_into_t;
  6  /
过程已创建。

 

俺们来试着执行新历程,看看会生啊变动:

SQL> select * from t;

未选定行

SQL> exec insert_into_t(p_parm=>100);

PL/SQL 过程已成功完成。

SQL> run
  1* select * from t

         N
----------
       100

 

  1. 参数传递

我们得以运用三种方法传递参数

●使用名称表示法

●使用位置表示拟

●使用混合表示拟

 

名称表示法

于以上的言传身教中,我们强烈命名了逐条参数。这叫做名称参数。它见面以如下形式:

PROCEDURE_NAME(PARAM_NAME =>VALUE[,PARM_NAME=>VALUE])

变异过程要调用命名各个专业参数、后面紧跟着由当号(=)和大于号(>)构成=>,最后用表达式来结束。以上的示范中,用户应看到调用:

SQL> exec insert_into_t(p_parm1=>200,p_parm=>201);

 

看望当我们利用打乱次序的参数调用过程的时会并发什么状况:

SQL> create or replace procedure three_parms(
  2   p_p1 number,
  3   p_p2 number,
  4   p_p3 number
  5  ) as begin
  6   dbms_output.put_line('p_p1='||p_p1);
  7   dbms_output.put_line('p_p2='||p_p2);
  8   dbms_output.put_line('p_p3='||p_p3);
  9  end three_parms;
 10  /
过程已创建。
SQL> set serverout on
SQL> exec three_parms(p_p1=>12,p_p3=>3,p_p2=>68);
p_p1=12
p_p2=68
p_p3=3
PL/SQL 过程已成功完成。

 

 

职表示拟

命名符号可能会见格外丰富。考虑一个拥有10独或再次多参数的历程,调用例程要输入过多之始末。大多数开发者可以以称为位置表示拟参数传递的重缺乏的记。采用这种办法时,用户可因参数在经过遭到定义的顺序对它进行传递。如:

SQL> exec three_parms(12,3,68)
p_p1=12
p_p2=3
p_p3=68

PL/SQL 过程已成功完成。

 

 

掺杂表示法

用户可将称表示法参数传递和职位表示拟参数传递相交织。这名叫混合表示法参数传递。以上的过程为得使这种措施调用:

SQL> exec three_parms(12,3,p_p3=>68);
p_p1=12
p_p2=3
p_p3=68

PL/SQL 过程已成功完成。

 

默认值

交目前为止,物价普过程定义的备参数还提供了参数值,但是要是经过发生默认值,我们不是得使这样做。过程的作者可以为任何IN参数定义默认值。

注意:

OUT和IN OUT参数不能够产生默认值。

 

咱们来分析一个示范:

SQL> create or replace procedure default_values(
  2   p_parm1 varchar2,
  3   p_parm2 varchar2 default 'Chris',
  4   p_parm3 varchar2 default 'Sean') as
  5  begin
  6   dbms_output.put_line(p_parm1);
  7   dbms_output.put_line(p_parm2);
  8   dbms_output.put_line(p_parm3);
  9  end default_values;
 10  /
过程已创建。

SQL> set serverout on
SQL> exec default_values('Tom',p_parm3=>'Joel');
Tom
Chris
Joel
PL/SQL 过程已成功完成。

 

 

日增参数

当一个不怎么之实用规则,如果你而往已经部分存储过程平添参数,那么尽管当以那个增加也末段之参数。通过应用这种艺术,只要用户增加的参数有默认值,那么当前采用位置表示法调用用户过程的拥有例程就依然可干活。如果用户拥有增加的参数没有作过程定义的最后的参数,那么用户就会见招致调用用户过程的富有例程抛来荒谬,或者抱意想不到的结果。

 

广错误

当调用过程的时节,用户可能会见在用户参数传递中犯一些荒唐。最广的失实产生:

●提供了没有以经过遭到定义的名称参数

●没有供足够的参数

●提供过多之参数

●提供了有非法数据列的参数

 

  1. OUT参数

至目前为止,我们的示范过程只是见面获参数值,并且对她进行拍卖。我们还尚未看到任何机制好于例程向调用者返回值。OUT参数可以被咱成功这项工作。

SQL> create or replace procedure emp_lookup(
  2   p_empno in number,
  3   o_ename out emp.ename%type,
  4   o_sal out emp.sal%type) as
  5  begin
  6   select ename,sal into o_ename,o_sa●  7   from emp
  8   where empno=p_empno;
  9  exception
 10   when NO_DATA_FOUND then
 11    o_ename:='NULL';
 12    o_sal:=-1;
 13  end emp_lookup;
 14  /

过程已创建。

 

此地是咱们当SQL*Plus中应用VARIABLE命令调用我们的EMP_LOOKUP过程的以身作则。首先我们如果定义2独变量:

SQL> variable name varchar2(10);
SQL> variable sal number;
SQL> exec emp_lookup('7782',:name,:sal);
PL/SQL 过程已成功完成。

SQL> print name
NAME
--------------------------------
CLARK
SQL> print sa●      SAL
----------
      2450

SQL> print name,sal
NAME
--------------------------------
CLARK

 

或我们吧足以看得出这些回值,从DUAL中选择它们:

SQL> select :name,:sal from dual;

:NAME                                  :SAL
-------------------------------- ----------
CLARK                                  2450

 

起匿名PL/SQL程序块被调用相同的过程用见面如下所示:

SQL> set serverout on
SQL> declare
  2   l_ename emp.ename%type;
  3   l_sal emp.sal%type;
  4  begin
  5   emp_lookup(7782,l_ename,l_sal);
  6   dbms_output.put_line('Ename='||l_ename);
  7   dbms_output.put_line('Sal='||l_sal);
  8  end;
  9  /
Ename=CLARK
Sal=2450
PL/SQL 过程已成功完成。

 

 

  1. IN OUT参数

即使使称所暗示的那样,IN
OUT参数能够用于传入参数值,并且于存储过程返回值。它们适用于用户用往经过传递可以由过程自己修改的INPUT变量的景。

用户要这种功效的过程的天下第一示例就是互动例程:

SQL> create or replace procedure swap(
  2   p_parm1 in out number,
  3   p_parm2 in out number) as
  4   l_temp number;
  5  begin
  6   l_temp:=p_parm1;
  7   p_parm1:=p_parm2;
  8   p_parm2:=l_temp;
  9  end swap;
 10  /

过程已创建。

 

履行交互:

SQL> set serverout on
SQL> declare
  2   l_num1 number:=100;
  3   l_num2 number:=101;
  4  begin
  5   swap(l_num1,l_num2);
  6   dbms_output.put_line('l_num1='||l_num1);
  7   dbms_output.put_line('l_num2='||l_num2);
  8  end;
  9  /
l_num1=101
l_num2=100

PL/SQL 过程已成功完成。

 

 

  1. NOCOPY

当用户传递作为参数的重型数据结构时,使用NOCOPY提示通常会怪有因此。

设想一个同时所有IN参数与IN OUT参数的长河。IN参数会通过引用传递(passed
by reference)。这表示她会传送对变量实际内存位置的单独念指针。

假如留意,无论参数中的实际价值多深,指针都不过会传送少量之数额。这是盖所急需传递的单纯是依靠为位置的指针。

与此相对,OUT参数要透过价值传递(passed by
value)。这意味着其的值要复制到参数中。

NOCOPY可以被用户提示编译器,就比如IN参数一样,通过引用传递OUT和IN
OUT参数。然后以这种景象下,引用要而更新,不能够如IN参数一样是独自念之。这样就算可以省用传递给过程,以及由过程回递的数额数量。但是NOCOPY只是一个提令,而未是命令,所以编译器可能会见忽略她。即使如此,它便为会见成功,为通过OUT和IN
OUT参数传递大量数据的PL/SQL程序带来好处。

SQL> set serverout on
SQL> DECLARE
  2    l_1 NUMBER := 10;
  3    l_2 NUMBER := 20;
  4    l_3 NUMBER := 30;
  5    PROCEDURE test_out(p1 IN NUMBER,
  6                       x1 IN OUT NUMBER,
  7                       x2 IN OUT NOCOPY NUMBER) IS
  8    BEGIN
  9      x1 := p1;
 10      dbms_output.put_line('inside test_out, x1=' || x1);
 11      x2 := p1;
 12      dbms_output.put_line('inside test_out, x2=' || x2);
 13       raise_application_error(-20005, 'test NOCOPY');
 14    END;
 15  BEGIN
 16    dbms_output.put_line('before, l_1=' || l_1 || ', l_2=' || l_2 ||
 17                         ', l_3=' || l_3);
 18    BEGIN
 19      --the OUT parameter has no value at all until the program terminates successfully,
 20      --unless you have requested use of the NOCOPY hint  
 21      test_out(l_1, l_2, l_3);
 22    EXCEPTION
 23      WHEN OTHERS THEN
 24        dbms_output.put_line('SQLCODE => ' || SQLCODE || ', SQLERRM => ' ||
 25                             SQLERRM);
 26    END;
 27    dbms_output.put_line('after, l_1=' || l_1 || ', l_2=' || l_2 || ', l_3=' || l_3);
 28  END;
 29  /
before, l_1=10, l_2=20, l_3=30
inside test_out, x1=10
inside test_out, x2=10
SQLCODE => -20005, SQLERRM => ORA-20005: test NOCOPY
after, l_1=10, l_2=20, l_3=10

PL/SQL 过程已成功完成。

 

  1. 参数不好序

概念参数的主次没有限制。具有或者不具有默认值的IN、OUT和IN
OUT参数都可以动用开发者认为相当的程序进行夹。一般的老是以那些尚未默认值的不可或缺参数放在列表的初始,其后与随OUT参数,然后是IN
OUT,最后是富有默认值的IN参数。采用这种措施定义参数可以叫调用者在履行用户过程的当儿,更发生或使用位置表示法。考虑如下示例:

procedure get_balance(p_date in date defaultsysdate,
    p_value out number,
    p_name in varchar2 default user,
    p_account_number in number)

 

唯要的参数是P_VALUE和P_ACCOUNT_NUMBER。由于她是第2只与第4独参数,所以为了调用这个历程,即使用户想如果使用默认址,用户为需用名称表示拟,或者传递所有4独参数。用户定义之过程的名特优方式应该如下所示:

procedure get_balance(p_account_number in number,
    p_value out number,
    p_date in date defaultsysdate,
    p_name in varchar2 default user)

 

11.2.6             局域声明

不怕犹如匿名PL/SQL程序块一样,过程可以定义局域变量。这些概念紧依可挑选的参数列表之后。在匿名PL/SQL次块被,它应用DECLARE保留字开始。在过程声明遭,由于我们以了CREATE
OR REPLACE语法,所以无需再以这个保留字。

设若用户注意了咱们最后之演示SWAP(),用户就应该看我们利用了一个局域变量L_TEMP。

create or replace
procedure swap(
    p_parm1 in out number,
    p_parm2 in out number)as
l_temp number;
begin
end;

 

  1. AUTHID

经过的AUTHID指令可以告知Oracle,这个过程使用谁之权运行。默认情况下,存储过程会作调用者的进程运行,但是富有设计者的特权。过程的CURRENT_SCHEMA将会晤是它的设计者,也就是说,过程外援的具有目标还见面分析为经过设计者的靶子。这叫做使用设计者权利运行,理解她非常要紧。这是下过程安全策略的根基。用户可透过加AUTHID指令,明确定义过程采用设计者的权能:

create or replace
procedure foo AUTHID DEFINER as
begin
null;
end foo;

 

盖及时是默认行为,所以它并无是得的。考虑我们的用户SCOTT。他有所一个表明,取消了具备人在这表上的特权,任何普通用户(除了他自己)都无容许访问这个表。现在,SCOTT要编一个过程,向表中插所提供的价值,以及是哪位插入了此数据行。他将以此历程的EXECUTE特权赋予了PUBLIC。尽管除了SCOTT以外,没有用户拥有直接为表中展开扦插的特权。但是由于经过使用SCOTT的特权运行,所以有应用SCOTT过程的用户都可插数据行。在这个过程被援引的持有目标都见面觉得是SCOTT正在看它们。

咱俩来行使数据库账号scott和hr,实现上述之气象,即看能否通过hr访问scott的仓储过程。

试验:AUTHID DEFINER

(1)       我们先是作为用户SCOTT连接数据库,建立NUMBERS表。

SQL> conn scott/tiger
已连接。
SQL> create table numbers(
  2  n number,
  3  username varchar2(30)
  4  )
  5  /
表已创建。

 

(2)      
现在,建立存储过程INSERT_NUMBERS,它会往NUMBERS表中展开扦插操作:

SQL> create or replace
  2  procedure insert_numbers(p_num number)AUTHID DEFINER as
  3  begin
  4  insert into numbers values(p_num, user);
  5  end;
  6  /
过程已创建。

 

(3)       我们而拿经过及之EXECUTE特权赋予PUBLIC。

SQL> grant execute on insert_numbers to public;
授权成功。

 

(4)       我们当用户HR进行连续,尝试向NUMBERS表中展开同样赖栽。

SQL> conn hr/hr;
已连接。
SQL> insert into scott.numbsers values(12345,'SEAN');
insert into scott.numbsers values(12345,'SEAN')
                  *
ERROR 位于第 1 行:
ORA-00942: 表或视图不存在

 

(5)      
我们现在当HR执行INSERT_NUMBERS过程,并且查看表的情(只有SCOTT能够访问):

SQL> exec scott.insert_numbers(12345);
PL/SQL 过程已成功完成。

 

 

做事规律

当下对安全特别重要,它好吃过程的撰稿人与动用的开发者更是强劲地控制数据的顾。如果具有对基本表的访问都负限制,访问表的绝无仅有方式就是经过囤过程,那么,开发者能够确保数量的一致性。如果出现了错,他们啊得掌握问题出现在所支付之API中。

此外,用户还好定义使用调用者特权运行的长河。这称为使用调用者权利(invoker
rights)。可以运用所有CURRENT_USER的AUTHID定义用户过程来贯彻:

create or replace
procedure foo AUTHID DEFINER as
begin
null;
end;

 

这种概念有跟设计者权利相对的成效。它见面采用调用者的特权,而未是设计者的特权。当用户想如果在运转时,而不是于编译时展开特权检查的时,就得采用是选项。在动用AUTHID
CURRENT_USER过程时,如果用户没有经过所访问对象及之特权时,也得成功编译调用。这或者会见受开发者编写最终未可知进行访问的代码。如果用户若推行此进程,而并未经过所访问对象及之特权,就见面起运行错误。

咱只要用我们最终的言传身教改吧AUTHID CURRENT_USER,并且查看效果。

试验:AUTHID CURRENT_USER

咱俩将要利用与达只例完全相同的进程,只是一旦反AUTHID子句。

(1)       我们首先会见当用SCOTT重新建INSERT_NUMBERS过程。

SQL> conn scott/tiger;
已连接。
SQL> create or replace
  2  procedure insert_numbers(p_num number)AUTHID CURRENT_USER as
  3  begin
  4  insert into numbers values(p_num, user);
  5  end;
  6  /
过程已创建。

 

在意,唯一作出修改的代码行是第2履行,其中修改后经过改呢AUTHID
CURRENT_USER。

(2)       现在,我们来当用户HR执行这历程

SQL> grant execute on insert_numbers to hr;
授权成功。

SQL> conn hr/hr;
已连接。
SQL> exec scott.insert_numbers(12345);
BEGIN scott.insert_numbers(12345); END;
*
ERROR 位于第 1 行:
ORA-00942: 表或视图不存在
ORA-06512: 在"SCOTT.INSERT_NUMBERS", line 3
ORA-06512: 在line 1

 

 

做事规律

履进程SCOTT.INSERT_NUMBERS()将会晤出及齐亦然次于全不同的结果。由于经过定义为AUTHID
CURRENT_USER,所以当履行之早晚,过程被之目标还见面分析为CURRENT_USER的对象。在这例子中,当HR执行进程,应用表NUMBSERS的下,它便见面于她的CURRENT_SCHEMA中检索NUMBERS表。由于没有如此的对象,所以出现ORA-00942:
表或视图不存。

 

  1. PRAGMA AUTONOMOUS_TRANSACTION

以第12段中,我们将要讨论工作处理,以及COMMIT或者ROLLBACK的意义,和它对以前所召开工作的熏陶。从比较高之角度来拘禁,当以了COMMIT的当儿,所有以用户事务处理中推行之做事且见面“保存”在数据库被,与此相对,当使用了ROLLBACK的下,用户事务处理中之拥有工作且见面反转,将数据返回到事务处理开始前之旗帜。

经过可好插入和换代这样的办事。我们既在前的例子中看看了向阳表中INSERT的经过。这些过程得以还不行之事务处理中运用。如果我们的长河被行使了COMMIT,那么先的劳作就会见提交,这并无总是好事。考虑向数据库表记录日志的全程。我们无思不见任何日志信息,所以我们设以历次插入后采用COMMIT。但是日志只是重甚之历程遭到之挺有点的一对。如果经过遇到了不当,那么用户就是足以想如果回滚以前执行之拥有工作。但是倘若日志例程已经交付了办事,就未可能展开回滚。

为成功这工作,用户需在概念用户存储过程的早晚,包含PRAGMA
AUTONOMOUS_TRANSACTION指示。

create or replace
procedure log_message(p_message varchar2) as
pragma autonomous_transaction;
begin
    insert into log_table values(p_message);
    commit;
end;

 

夫大概的以身作则功能相当强大。用户可以以用户事务处理中之另地方调用这个历程,无论父事务处理是交由还是回滚,用户都好保证LOG_TABLE将会晤所有用户插入的履行(当然,P_MESSAGE不能够长于LOG_TABLE中的列)。

考:使用电动事务处理进行日志记录

咱来分析活动事务处理的干活。我们且利用上述的日志过程,从于第2个表插入数据行的匿名程序块被调用它,然后开展回滚。

(1)       我们先是使作用户SCOTT建立LOG_TABLE和TEMP_TABLE表。

SQL> conn scott/tiger;
已连接。
SQL> create table log_table(
  2   username varchar2(30),
  3   date_time timestamp,
  4   message varchar2(4000)
  5  );
表已创建。
SQL> create table temp_table(
  2   n number);
表已创建。

 

对这示例,为了博记录信息的程度,我们选成立一个不仅具有MESSAGE列,也发USERNAME和DATE_TIME列的日志表。TEMP_TABLE表是所运用的亚独说明。

(2)       现在,我们建立LOG_MESSAGE()过程。

SQL> create or replace
  2  procedure log_message(p_message varchar2) as
  3  pragma autonomous_transaction;
  4  begin
  5   insert into log_table(username,date_time,message)
  6   values(user,current_date,p_message);
  7   commit;
  8  end;
  9  /
过程已创建。

 

注意:

CURRENT_DATE是Oracle 9i的新力量。为了以Oracle
8i中编译这个过程,用户需要使用SYSDATE函数。

旋即就是是咱们的AUTONOMOUS_TRANSACTION过程。可以小心到第3实行遭之PRAGMA。这个历程会朝着日志表中插记录,然后交到。

(3)      
在解析了LOG_TABLE表和TMEP_TABLE表之后,我们即将执行匿名的PL/SQL程序块,向TEMP_TABLE中展开INSERT,并且在实施ROLLBACK之前调用LOG_MESSAGE();

SQL> select * from temp_table;
未选定行

SQL> select * from log_table;
未选定行

SQL> begin
  2   log_message('About to insert into temp_table');
  3   insert into temp_table(n)values(12345);
  4   log_message('Rolling back insert into temp_table');
  5   rollback;
  6  end;
  7  /

PL/SQL 过程已成功完成。

 

咱俩于插入和回滚之前调用了LOG_MESSAGE()。要记住,LOG_MESSAGE()过程会尽COMMIT。通过翻以上之代码,分析程序的逻辑,用户可能会见认为以第2实践开展的插会吃第4行调用
的LOG_MESSAGE()中之COMMIT提交,第5行之ROLLBACK不见面从其它企图,但是对表进行的分析表明,情况并非如此:

SQL> select * from log_table;
USERNAME   DATE_TIME                                                                   MESSAGE
---------- --------------------------------------------------------------------------- --------------------
SCOTT      13-6月 -13 04.11.57.000000 下午                                             About to insert into
                                                                                        temp_table

SCOTT      13-6月 -13 04.11.57.000000 下午                                             Rolling back insert
                                                                                       into temp_table

 

在TEMP_TABLE中无多少实施,而发生2行插入到了LOG_TABLE中。外边
的事务处理PL/SQL程序块进行了回滚,而子事务处理,或者机关事务处理已经交由。

机关事务处理非常适用于日志这仿佛的操作、或者其它品类的内需交给,但是不能够干涉调用者事务处理的做事。

11.3  函数

11.3.1             语法

树函数的语法要比建立过程的语法稍微复杂一点。语法的贯彻中显了大气之习性,例如PIPELINED,但是本章不见面指向它进行更加地谈论。

(略)

 

11.3.2             返回值

咱来分析一个示范:

SQL> create or replace
  2  function first_function return varchar2 as
  3  begin
  4  return 'Hello World';
  5  end;
  6  /
函数已创建。
SQL> set serverout on
SQL> declare
  2  l_str varchar2(100):=null;
  3  begin
  4  l_str:=first_function;
  5  dbms_output.put_line(l_str);
  6  end;
  7  /
Hello World

 

 

试验:ITE函数

(1)      
我们第一定义所欲的函数规范。现在,我们来填充函数的侧重点,确保所有的淡出点还用了归来VARCAHR2表达式的口舌。如果我们无克涵盖所有的退出点,函数就非能够正确工作,因此我们用检查P_EXPRESSION的值。

SQL> create or replace function ite(
  2  p_expression boolean,
  3  p_true varchar2,
  4  p_false varchar2) return varchar2 as
  5  begin
  6   if p_expression then
  7    return p_true;
  8  end if;
  9  return p_false;
 10  end;
 11  /
函数已创建。

 

(2)       接下来,我们需要测试代码,看看它是否要我辈所乐意。

SQL> set serverout on;
SQL> exec dbms_output.put_line(ite(1=2,'Equal','Not Equal'));
Not Equal
PL/SQL 过程已成功完成。

SQL> exec dbms_output.put_line(ite(2>3,'True','False'));
False
PL/SQL 过程已成功完成。

 

 

11.3.3             确定性

设对给定的输入,函数总是会回完全相同的结果,那么就算称之函数具有明确(deterministic)。UPPER()内嵌函数是明确的。如果往它们输入SaMaNtHa,它就是见面回SAMANTHA。如果函数不克每次都回来相同的值,用户就未能够以那个作为家喻户晓函数建立。为了提醒函数具有DETERMINISTIC,用户所需要好的凡事干活如下所示:

SQL> create or replace
  2  function total_compensation(
  3   p_salary number,
  4   p_commission number) return number
  5  deterministic as
  6  begin
  7   return nvl(p_salary,0)+nvl(p_commission,0);
  8  end;
  9  /

函数已创建。

 

咱清楚,如果让定任意2只数值作为输入,结果总会是其的及。

提拔函数具有DETERMINISTIC的目的是为着帮扶优化器。当提供了一样的输入时,由于她会来同样之结果,所以优化器就足以选择以确定性函数以前的结果。对于以频繁的函数处理,这可节约许多CPU周期。另外,用户须也因函数的目使用确定性函数。

 

11.3.4             常见错

此地是用户以支付函数的时也许会见遇上的一对广阔错误:

●忘记取返回值。

●试图定义不可知回去回值的函数。

●定义尚无回去数据类型的函数。

据此,即使以富有非常处理器的函数中,用户为要记住从杀处理器中回到一些情节。在概念函数的时段咖一个醒来错误是绝非定义返回数据类型:

SQL> create or replace
  2  function no_return_type as
  3  begin 
  4   return null;
  5  end;
  6  /

警告: 创建的函数带有编译错误。

SQL> show errors
FUNCTION NO_RETURN_TYPE 出现错误:

LINE/COL ERROR
-------- -----------------------------------------
1/25     PLS-00103: 出现符号 "AS"在需要下列之一时?
         ( return compress compiled
         wrapped

 

因PL/SQL函数的定义不科学,所以这边出现了PLS-00103错误。这个函数没有定义返回路。

11.4  程序包

次第包这种组织得以被用户从逻辑上组织过程、函数、对象类型、以及放入单独的数据库对象被的各种内容。

先后包通常由2部分构成:规范和重心。规范(specification)是程序包的官接口。所提供的重心(body)包含了标准的实现,以及有私出例程、数据与变量。

11.4.1             语法

11.4.2             规范

程序包规范(或者spec)是程序包的结口。在专业中定义之有着情节还足以由调用者使用,并且可由拥有此顺序包EXECUTE特权的用户引用。在正规中定义之长河可叫实践,变量可以让引用,类型会让访。这些是次包的官特性。

SQL> create or replace
  2  package employee_pkg as
  3   procedure print_ename(p_empno number);
  4   procedure print_sal(p_empno number);
  5  end;
  6  /

程序包已创建。

 

计较就执行这些过程用见面造成错误。

SQL> execute employee_pkg.print_ename(1234);
BEGIN employee_pkg.print_ename(1234); END;

*
ERROR 位于第 1 行:
ORA-04068: 已丢弃程序包  的当前状态
ORA-04067: 未执行,package body "SCOTT.EMPLOYEE_PKG" 不存在
ORA-06508: PL/SQL: 无法在调用之前找到程序单元
ORA-06512: 在line 1

 

就此,在此地我们好发现先后包主体还从来不存。这些过程还没兑现,所以还没有代码;目前只有接口存在。我们需要编制这些例程的代码,这要是于先后包主体中好。

11.4.3             主体

次第包主体是您实际编写的子例程,实现正式被定义的接口。规范中形的具备过程和函数都要以重点中实现:

create or replace
package body employee_pkg as
    procedure print_ename(p_empno number) is
        l_ename emp.ename%type;
    begin
        select ename into l_ename
        from emp
        where empno=p_empno;
        dbms_output.put_line(l_ename);
    exception
        when NO_DATA_FOUND then
            dbms_output.put_line('Invalid employee number');
    end;
    procedure print_sal(p_empno number) is
        l_sal emp.sal%type;
    begin
        select sal into l_sa●       from emp
        where empno=p_empno;
        dbms_output.put_line(l_sal);
    exception
        when NO_DATA_FOUND then
            dbms_output.put_line('Invalid employee number');
    end;
end;
/

 

如今,执行就2独经过的早晚,我们虽见面取得结果:

SQL> set serverout on
SQL> execute employee_pkg.print_ename(1234);
Invalid employee number

PL/SQL 过程已成功完成。

SQL> execute employee_pkg.print_sal(7782);
2450

PL/SQL 过程已成功完成。

 

除落实规范被所定义过程外,您还好定义程序包之个体过程。尽管程序包不能够包含其他的先后包,但是个人过程和函数可以给先后包被定义之别其它例程所引用。而先后包以外的全程则不可知针对它进行访问。

考:私有过程与函数

俺们来落实上述之2个建议。我们而:

●编写一个民用函数LOG_MESSAGES()

●从官过程中将共有功能转移到一个私房函数中

(1)       我们首先来建或者又定义程序包声明。

SQL> CREATE OR REPLACE PACKAGE EMPLOYEE_PKG AS
  2    PROCEDURE LOG_MESSAGE(P_MESSAGE VARCHAR2);
  3    FUNCTION GET_EMP_RECORD(P_EMPNO NUMBER) RETURN EMP%ROWTYPE;
  4    PROCEDURE PRINT_DATA(P_EMP_RECORD EMP%ROWTYPE, P_COLUMN VARCHAR2);
  5    PROCEDURE PRINT_ENAME(P_EMPNO NUMBER);
  6    PROCEDURE PRINT_SAL(P_EMPNO NUMBER);
  7  END;
  8  /

程序包已创建。

 

此历程以及我们以上述使用AUTONOMOUS_TRANSACTIONS的示范中见到底LOG_MESSAGE()过程基本相同。

(2)       我们今天若编写函数,定义程序包主体。

SQL> CREATE OR REPLACE PACKAGE BODY EMPLOYEE_PKG AS
  2    PROCEDURE LOG_MESSAGE(P_MESSAGE VARCHAR2) IS
  3      PRAGMA AUTONOMOUS_TRANSACTION;
  4    BEGIN
  5      INSERT INTO LOG_TABLE
  6        (USERNAME, DATE_TIME, MESSAGE)
  7      VALUES
  8        (USER, CURRENT_DATE, P_MESSAGE);
  9      COMMIT;
 10    END;
 11  
 12    FUNCTION GET_EMP_RECORD(P_EMPNO NUMBER) RETURN EMP%ROWTYPE IS
 13      L_EMP_RECORD EMP%ROWTYPE;
 14    BEGIN
 15      LOG_MESSAGE('Looking for record where EMPNO=' || P_EMPNO);
 16      SELECT * INTO L_EMP_RECORD FROM EMP WHERE EMPNO = P_EMPNO;
 17      RETURN L_EMP_RECORD;
 18    EXCEPTION
 19      WHEN NO_DATA_FOUND THEN
 20        RETURN NULL;
 21    END;
 22  
 23    PROCEDURE PRINT_DATA(P_EMP_RECORD EMP%ROWTYPE, P_COLUMN VARCHAR2) IS
 24      L_VALUE VARCHAR2(4000);
 25    BEGIN
 26      IF P_EMP_RECORD.EMPNO IS NULL THEN
 27        LOG_MESSAGE('No Data Found.');
 28        DBMS_OUTPUT.PUT_LINE('No Data Found.');
 29      ELSE
 30        CASE P_COLUMN
 31          WHEN 'ENAME' THEN
 32            L_VALUE := P_EMP_RECORD.ENAME;
 33          WHEN 'SAL' THEN
 34            L_VALUE := NVL(P_EMP_RECORD.SAL, 0);
 35          ELSE
 36            L_VALUE := 'Invalid column';
 37        END CASE;
 38        LOG_MESSAGE('About to print ' || P_COLUMN || '=' || L_VALUE);
 39        DBMS_OUTPUT.PUT_LINE(P_COLUMN || '=' || L_VALUE);
 40      END IF;
 41    END;
 42  
 43    PROCEDURE PRINT_ENAME(P_EMPNO NUMBER) IS
 44    BEGIN
 45      PRINT_DATA(GET_EMP_RECORD(P_EMPNO), 'ENAME');
 46    END;
 47  
 48    PROCEDURE PRINT_SAL(P_EMPNO NUMBER) IS
 49    BEGIN
 50      PRINT_DATA(GET_EMP_RECORD(P_EMPNO), 'SAL');
 51    END;
 52  END;
 53  
 54  /

程序包主体已创建。

 

(3)       我们来测试程序包,看看她是否可以使我们希望的那么行事。

SQL> exec employee_pkg.print_ename(7781);

PL/SQL 过程已成功完成。

SQL> set serverout on
SQL> exec employee_pkg.print_ename(7781);
No Data Found.

PL/SQL 过程已成功完成。

SQL> exec employee_pkg.print_ename(7782);
ENAME=CLARK

PL/SQL 过程已成功完成。

SQL> select * from log_table;

USERNAME                       DATE_TIME
------------------------------ --------------------------------
MESSAGE
---------------------------------------------------------------
SCOTT                          13-6月 -13 04.11.57.000000 下午
111

SCOTT                          13-6月 -13 04.11.57.000000 下午
111

SCOTT                          19-7月 -13 07.53.46.000000 下午
Looking for record where EMPNO=7781


USERNAME                       DATE_TIME
------------------------------ --------------------------------
MESSAGE
---------------------------------------------------------------
SCOTT                          19-7月 -13 07.53.46.000000 下午
No Data Found.

SCOTT                          19-7月 -13 07.53.55.000000 下午
Looking for record where EMPNO=7781

SCOTT                          19-7月 -13 07.53.55.000000 下午
No Data Found.


USERNAME                       DATE_TIME
------------------------------ --------------------------------
MESSAGE
---------------------------------------------------------------
SCOTT                          19-7月 -13 07.54.02.000000 下午
Looking for record where EMPNO=7782

SCOTT                          19-7月 -13 07.54.02.000000 下午
About to print ENAME=CLARK

已选择8行。

 

 

11.4.4             程序包变量和另声明

用户可定义称为全局变量(global
variables)的次第包级别的变量。这些变量可以定义在程序包规范中,或者为堪定义在先后包主体中。

概念在业内着的那些变量可以像专业被之长河与函数一样吃引用,它们吃叫做公共变量(public
variables)。

考查:程序包变量

(1)      
首先,我们采用一个不过发生和一个民用程序包级别之变量,以及可以安装及输出私有值的长河来扭转程序包(VARIABLES)。由于我们可以直接直接装与朗诵博公程序包变量值,所以无欲也挺值编写例程。

SQL> create or replace
  2  package variables as
  3   g_public_number number:=null;
  4   procedure set_private_number(p_num number);
  5   procedure print_private_number;
  6  end;
  7  /

程序包已创建。

SQL> create or replace
  2  package body variables as
  3   g_private_number number:=null;
  4   
  5   procedure set_private_number(p_num number)is
  6   begin
  7    g_private_number:=p_num;
  8   end;
  9   
 10   procedure print_private_number is
 11   begin
 12    dbms_output.put_line(nvl(to_char(g_private_number),'null'));
 13   end;
 14  end;
 15  /

程序包主体已创建。

 

(2)       现在,我们可以一直看公共程序包变量,重新对该初始化。

SQL> set serverout on
SQL> exec dbms_output.put_line(nvl(to_char(variables.g_public_number),'null'));
null
PL/SQL 过程已成功完成。
SQL> exec variables.g_public_number:=123;
PL/SQL 过程已成功完成。
SQL> exec dbms_output.put_line(nvl(to_char(variables.g_public_number),'null'));
123
PL/SQL 过程已成功完成。

 

(3)       试图访问私有程序包变量将会见出错误信息。

SQL> exec variables.g_private_number:=456;
BEGIN variables.g_private_number:=456; END;

                *
ERROR 位于第 1 行:
ORA-06550: 第 1 行, 第 17 列:
PLS-00302: 必须说明 'G_PRIVATE_NUMBER' 组件
ORA-06550: 第 1 行, 第 7 列:
PL/SQL: Statement ignored

 

(4)       通过公共过程看私有程序包变量可以干活可以

SQL> exec variables.set_private_number(456);

PL/SQL 过程已成功完成。

SQL> exec variables.print_private_number;
456

PL/SQL 过程已成功完成。

 

(5)      
然而,如果我们重新连接,或者使新的对话,程序包之状态就会见重置,已经装的值就是见面掉。

SQL> conn scott/tiger
已连接。
SQL> set serverout on
SQL> exec dbms_output.put_line(nvl(to_char(variables.g_public_number),'null'));
nul●PL/SQL 过程已成功完成。

SQL> exec variables.print_private_number;
nul●PL/SQL 过程已成功完成。

 

 

实例化程序块

先后包会拥有一个代码块,它可在对话第一软造访程序包元素的时光运行一浅。它好是针对性标准着所定义过程的调用,或者只是简单读博公程序包变量的值。第一潮访问为称程序包实例化,这时,程序包将执行实例化代码。它仅仅会吃调用一不行,并且会自动进行。

SQL> create or replace
  2  package variables as
  3   g_public_number number:=null;
  4   procedure set_private_number(p_num number);
  5   procedure print_private_number;
  6  end;
  7  /

程序包已创建。

SQL> create or replace
  2  package body variables as
  3   g_private_number number:=null;
  4   
  5   procedure set_private_number(p_num number)is
  6   begin
  7    g_private_number:=p_num;
  8   end;
  9   
 10   procedure print_private_number is
 11   begin
 12    dbms_output.put_line(nvl(to_char(g_private_number),'null'));
 13   end;
 14   
 15  begin
 16   select count(*) into g_public_number from emp;
 17   g_private_number:=dbms_random.random;
 18   
 19  end;
 20  /

程序包主体已创建。

 

第15履及第19行是实例化程序块。这些代码将要当率先次等引用程序包的时段实施。由于它们是PL/SQL,所以我们可以在它的扬言部分初始化G_PRIVATE_NUMBER。

SQL> exec variables.print_private_number;
2011415604

PL/SQL 过程已成功完成。

SQL> exec dbms_output.put_line(nvl(to_char(variables.g_public_number),'null'));
14

PL/SQL 过程已成功完成。

 

11.4.5             重载

重载(Overloading)是以单身的主次包吃定义的共享相同名称的少只或基本上独过程及函数。

以下声明是官方的,因为参数类型有所区别:

procedure foo(p_parm1 varchar2);
procedure foo(p_parm1 number);

procedure foo(p_parm1 number,p_parm2 varchar2);
procedure foo(p_parm1 varchar2,p_parm2 varchar2);

procedure foo;
procedure foo(p_parm1 number);
procedure foo return number;
procedure foo(p_parm1 number) return varchar2;

 

作示范,以下的2独就是是伪的,因为NUMBER和REAL都属于同一的数据类型家庭。

procedure foo(p_parm1 number);
procedure foo(p_parm2 real);

 

重载可以为编码还便于。用户可能已使用过重载函数,只是还尚未发觉及她的留存。考虑内嵌的
Oracle函数TO_CHAR()。

SQL> select to_char(sysdate,'HH24:MI:SS') "DATE" from dual;

DATE
--------
11:48:07

SQL> select to_char(111,'099.99') "NUMBER" from dual;

NUMBER
-------
 111.00

 

用户可传DATE或者NUMBER,它还足以进行拍卖。

试验:重载SWAP()

追思至IN OUT参数的讨论,我们编辑了一个SWAP()过程。

SQL> create or replace
  2  procedure swap(
  3   p_parm1 in out number,
  4   p_parm2 in out number)as
  5   l_temp number;
  6  begin
  7   l_temp:=p_parm1;
  8   p_parm1:=p_parm2;
  9   p_parm2:=l_temp;
 10  end;
 11  /

过程已创建。

 

假定用户所要好的行事是换成反复价值,那么这就是一个杀实用的历程。我们只要卷入这个历程,对那开展重载,使其吗得以换成VARCHAR2和日期。

(1)       首先,建立规范。

SQL> create or replace
  2  package utilities as
  3   procedure swap(p_parm1 in out number,p_parm2 in out number);
  4   procedure swap(p_parm1 in out varchar2,p_parm2 in out varchar2);
  5   procedure swap(p_parm1 in out date,p_parm2 in out date);
  6  end;
  7  /

程序包已创建。

SQL> create or replace
  2  package body utilities as
  3   procedure swap(p_parm1 in out number,p_parm2 in out number) is
  4    l_temp number;
  5   begin
  6    dbms_output.put_line('Swapping number');
  7    l_temp:=p_parm1;
  8    p_parm1:=p_parm2;
  9    p_parm2:=l_temp;
 10   end;
 11  
 12   procedure swap(p_parm1 in out varchar2,p_parm2 in out varchar2) is
 13    l_temp varchar2(32767);
 14   begin
 15    dbms_output.put_line('Swapping varchar2');
 16    l_temp:=p_parm1;
 17    p_parm1:=p_parm2;
 18    p_parm2:=l_temp;
 19   end;
 20  
 21   procedure swap(p_parm1 in out date,p_parm2 in out date) is
 22    l_temp date;
 23   begin
 24    dbms_output.put_line('Swapping date');
 25    l_temp:=p_parm1;
 26    p_parm1:=p_parm2;
 27    p_parm2:=l_temp;
 28   end;
 29  end;
 30  /

程序包主体已创建。

 

(2)       现在,我们尽管可以测试就编制的内容,查看她的运行状况。

SQL> set serverout on
SQL> declare
  2   l_num1 number:=1;
  3   l_num2 number:=2;
  4   l_date1 date:=sysdate;
  5   l_date2 date:=sysdate+1;
  6  begin
  7   utilities.swap(l_num1,l_num2);
  8   dbms_output.put_line('l_num1='||l_num1);
  9   dbms_output.put_line('l_num2='||l_num2);
 10   utilities.swap(l_date1,l_date2);
 11   dbms_output.put_line('l_date1='||l_date1);
 12   dbms_output.put_line('l_date2='||l_date2);
 13  end;
 14  /
Swapping number
l_num1=2
l_num2=1
Swapping date
l_date1=21-7月 -13
l_date2=20-7月 -13

PL/SQL 过程已成功完成。

 

11.4.6             依赖性

纵然好似数据库被之其它对象同,过程吧有所依赖性,会相继依靠任何的目标。具有外键的表要依赖让它所参考的表,视图依赖让她的附属表,过程要依让其所引述的数据库对象(例如表、视图、甚至其它存储过程)。

当用户将经过成功编译进数据库后,就看其行(valid)。用户可以查看数据库片辞典视图USER_OBJECTS来验证这个过程。

SQL> select object_name,status
  2  from user_objects
  3  where object_type='PROCEDURE';

OBJECT_NAME       STATUS
----------------- -----------------
DEFAULT_VALUES    VALID
EMP_LOOKUP        VALID
INSERT_NUMBERS    INVALID
LOG_MESSAGE       VALID
SWAP              VALID
THREE_PARMS       VALID

 

 

考:依赖性实例

于此地,我们设分析当存储过程依赖的目标有转移时会起啊动静。

(1)       我们率先使起名吧BAR的发明,存储过程要凭让它们。

SQL> create table bar(n number);

表已创建。

SQL> create or replace procedure foo as
  2   l_n bar.n%type;
  3  begin
  4   null;
  5  end;
  6  /

过程已创建。

 

经过要依赖让表BAR,是为FOO()会声明一个局域变量,它的花色是列N的色。

(2)       现在,我们可以履如下命令,查看过程的状态。

SQL> select object_name,status
  2  from user_objects
  3  where object_type='PROCEDURE';

OBJECT_NAME       STATUS
----------------- -----------------
FOO               VALID
INSERT_NUMBERS    VALID

已选择2行。

 

刚而我们所预期,过程FOO()编译成功,所以,它的状态是VALID

(3)       当向表BAR中多列,对那进展改动以后会来什么。

SQL> alter table bar add c char(1);

表已更改。
SQL> select object_name,status
  2  from user_objects
  3  where object_type='PROCEDURE';

OBJECT_NAME       STATUS
----------------- -----------------
FOO               INVALID
LOG_MESSAGE       VALID

已选择2行。

 

FOO()现在啊INVALID,也就是说,对所引用对象的其余变动都可使月入民的历程无效。无论改是否直接影响了经过,对象的变动会使援它的装有过程无效。

(4)       现在咱们来看看,当试图实施过程的时候,会发啊情形。

SQL> exec foo

PL/SQL 过程已成功完成。

SQL> select object_name,status
  2  from user_objects
  3  where object_type='PROCEDURE';

OBJECT_NAME       STATUS
----------------- -----------------
FOO               VALID
LOG_MESSAGE       VALID

已选择2行。

 

就不但是一个百般好之特色,而且为是少不了的风味。假如我们的长河调用SEAN的过程,它同时如果调用
MARK所所有的进程,而MARK所具有的经过要引用JOEL模式中之阐发。如果JOEL向他的表中增加一个排列,那么所有这3独存储过程还叫Oracle标记为INVALID。让抱有的历程所有者都以它的历程一直维护也VALID几乎是匪容许的。就设用户所显现,很有点之反就是会见大易涉及整个用户数据库,影响多对象,使它们无效。

11.4.7             程序包的优势

若果用户在下中利用一些低层的代码,而每次都使再编译代码,那么任何应用都见面劳而无功。用户之数据库将要消耗大量的时光来开展更编译,才方可使用户使用能够实践。对于用户CPU以及Oracle来讲,这还相当消耗时间。程序包可呢咱遮挡这个问题。

考试:改变程序包

(1)       我们将要建一个次包过程SHIELD.FOO()

SQL> create or replace
  2  package shield as
  3   procedure foo;
  4  end;
  5  /

程序包已创建。
SQL> create or replace
  2  package body shield as
  3   procedure foo is
  4    l_n bar.n%type;
  5   begin
  6    null;
  7   end;
  8  end;
  9  /

程序包主体已创建。

 

(2)       现在,我们来转表,使其包含一个新列,看看这会发出什么影响。

SQL> alter table bar add d date;

表已更改。
SQL> select object_name,status
  2  from user_objects
  3  where object_name='SHIELD';

OBJECT_NAME       STATUS
----------------- ----------------
SHIELD            VALID
SHIELD            INVALID

 

这儿,我们得知晓地察看,只有程序包主体变为INVALID。规范和援它的情节以为VALID。使用程序包得让我们开展转移,并且可避免数据库被大量可能的历程与函数变为无效。

一般说来,程序包规范非会见常转移,但是它的实现会晤生转移。

11.5  数据词典

11.5.1             列出有的用户存储过程

经过下如下查询,用户就好列出用户模式中的拥有过程、函数和程序包。

SQL> select object_name,object_type
  2  from user_objects
  3  where object_type in('PROCEDURE','FUNCTION','PACKAGE','PACKAGE BODY');

OBJECT_NAME       OBJECT_TYPE
----------------- ------------------
DEFAULT_VALUES    PROCEDURE
EMPLOYEE_PKG      PACKAGE
EMPLOYEE_PKG      PACKAGE BODY
EMP_LOOKUP        PROCEDURE
FIRST_FUNCTION    FUNCTION
FOO               PROCEDURE
INSERT_NUMBERS    PROCEDURE
ITE               FUNCTION
LOG_MESSAGE       PROCEDURE
NO_RETURN_TYPE    FUNCTION
SHIELD            PACKAGE

OBJECT_NAME       OBJECT_TYPE
----------------- ------------------
SHIELD            PACKAGE BODY
SWAP              PROCEDURE
THREE_PARMS       PROCEDURE
TOTAL_COMPENSATIO FUNCTION
N

UTILITIES         PACKAGE
UTILITIES         PACKAGE BODY
VARIABLES         PACKAGE
VARIABLES         PACKAGE BODY

已选择19行。

 

11.5.2             从数据库中搜寻用户代码

俺们不仅可找到以指定的模式面临起什么样过程,还可以得构成经过的代码。词典视图USER_SOURCE将会晤朝着我们提供这些消息:

SQL> desc user_source
 名称                                      是否为空? 类型
 ----------------------------------------- -------- --------------
 NAME                                               VARCHAR2(30)
 TYPE                                               VARCHAR2(12)
 LINE                                               NUMBER
 TEXT                                               VARCHAR2(4000)

SQL> select text from user_source where name='LOG_MESSAGE';

TEXT
------------------------------------------------------------------
procedure log_message(p_message varchar2) as
pragma autonomous_transaction;
begin
 insert into log_table(username,date_time,message)
 values(user,current_date,p_message);
 commit;
end;

已选择7行。

 

有时也得以应用SQL*Plus DESCRIBE或者DESC命令来描述程序包:

SQL> desc employee_pkg
FUNCTION GET_EMP_RECORD RETURNS RECORD
参数名称                       类型                    输入/输出默认值?
------------------------------ ----------------------- ------ --------
   EMPNO                        NUMBER(4)               OUT
   ENAME                        VARCHAR2(10)            OUT
   JOB                          VARCHAR2(9)             OUT
   MGR                          NUMBER(4)               OUT
   HIREDATE                     DATE                    OUT
   SA●                        NUMBER(7,2)             OUT
   COMM                         NUMBER(7,2)             OUT
   DEPTNO                       NUMBER(2)               OUT
 P_EMPNO                        NUMBER                  IN

 

11.6  包装实用工具

是因为PL/SQL在数据库中因纯文本方式囤,所以Oracle提供了一个实用工具来加密(或者包装)用户的PL/SQL,它见面拿用户之PL/SQL改变也单纯发数据库能够解释的代码版本。通过使用这种办法,用户就足以使另人无法知道用户代码的办事法,进而保护你的智商成果。

WRAP实用工具位于$ORACLE_HOME/bin,用于WRAP的语法为:

wrap –inname=<input_file_name> [-oname=<output_file_name>]

 

例如:

D:\oracle\ora92\bin>set NLS_LANG=USA

D:\oracle\ora92\bin>wrap iname=d:\001\wrap.sql oname=d:\001\wrap.plb

PL/SQL Wrapper: Release 9.2.0.1.0- Production on Sat Jul 20 16:31:21 2013

Copyright (c) Oracle Corporation 1993, 2001.  All Rights Reserved.

Processing d:\001\wrap.sql to d:\001\wrap.plb

D:\oracle\ora92\bin>

 

注意1:

万一以实施wrap 时出现仿佛kgepop: no error frame to pop to for error
1801的缪

吁预实行

WINDOWS OS :set NLS_LANG=USA
LINUX/UNIX OS :$ unset NLS_LANG

 

注意2:

用户必须在数据库以外维护一个来自文件之副本,包装代码只是仅仅为过程,一旦包装,就未能够破包装。要小心!

便WRAP可以加密 包/过程/函数 不克加密触发器
,加密后无法解密,请保管好源码。

11.7  小结

以本章中,我们盖介绍性的概念分析了于Oracle中开展PL/SQL编程。讨论了依据程序块的编程、以及PL/SQL怎样从逻辑上将代码块分割为职责区域,例如变量和常量声明、执行代码、以及错误控制代码。我们尚讨论了PL/SQL中数据类型与SQL中数据类型之间的分别。

游标可以让我们以SQL查询引入到PL/SQL代码中,有效地拼程序的布局以及数据库中蕴藏的数额。有大量外打之作用可助操作我们的多寡、字符、和数值变量。另外,我们尚好将PL/SQL编程结构划分为条件逻辑、循环语句、以及错误控制代码等。

 

文章根据自己懂浓缩,仅供参考。

慎选自:《Oracle编程入门经典》
清华大学出版社 http://www.tup.com.cn

 

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

*
*
Website