在PL/SQL 开发中调试存储过程和函数的一般性方法数据库教程
“Ad”通过精心收集，向本站投稿了9篇在PL/SQL 开发中调试存储过程和函数的一般性方法数据库教程，以下是小编收集整理后的在PL/SQL 开发中调试存储过程和函数的一般性方法数据库教程，欢迎阅读与借鉴。
篇1：在PL/SQL 开发中调试存储过程和函数的一般性方法数据库教程
存储过程|函数
在PL/SQL 开发中调试存储过程和函数的一般性方法摘要: Oracle 在PLSQL中提供的强大特性使得数据库开发人员可以在数据库端完成功能足够复杂的任务, 本文将结合Oracle提供的相关程序包(package)以及一个非常优秀的第三方开发工具来介绍在PLSQL中开发及调试存储过程的方法，当然也适用于函数，
版权声明: 本文可以任意，转载时请务必以超链接形式标明文章原始出处和作者信息。
原文出处: www.aiview.com/notes/ora_using_proc.htm
作者: 张洋 Alex_doesAThotmail.com
最后更新: -8-2
目录 准备工作 从一个最简单的存储过程开始 调试存储过程 在存储过程中写日志文件 捕获违例
Oracle 在PLSQL中提供的强大特性使得数据库开发人员可以在数据库端完成功能足够复杂的任务, 本文将结合Oracle提供的相关程序包(package)以及一个非常优秀的第三方开发工具来介绍在PLSQL中开发及调试存储过程的方法，当然也适用于函数。
本文所采用的软件版本和环境:
服务器: Oracle 8.1.2 for Solaris 8
PL/SQL Developer 4.5
准备工作
在开始之前, 假设您已经安装好了Oracle的数据库服务, 并已经建立数据库, 设置好监听程序, 以允许客户端进行连接; 同时您已经拥有了一台设置好本地Net服务名的开发客户机, 并已经安装好PL/SQL Developer开发工具的以上版本或者更新.
在下面的示例代码中,我们使用Oracle数据库默认提供的示例表 scott.dept 和 scott.emp. 建表的语句如下：
create table SCOTT.DEPT
(
DEPTNO NUMBER(2) not null,
DNAME VARCHAR2(14),
LOC VARCHAR2(13)
)
create table SCOTT.EMP
(
EMPNO NUMBER(4) not null,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2)
)
从一个最简单的存储过程开始
我们现在需要编写一个存储过程, 输入一个部门的编号, 要求取得属于这个部门的所有员工信息, 包括员工编号和姓名. 员工的信息通过一个cursor返回给应用程序.
create or replace procedure usp_getEmpByDept(
in_deptNo in number,
out_curEmp out pkg_const.REF_CURSOR
) as
begin
open curEmp for
select empno,
ename
from scott.emp
where deptno = in_deptNo;
end usp_getEmpByDept;
上面我们定义了两个参数, 其中第二个参数需要利用cursor返回员工信息, PLSQL中提供了REF CURSOR的数据类型, 可以采用两种方式进行定义, 一种是强类型,一种是弱类型, 前者在定义时指定cursor返回的数据类型, 后者可以不指定, 由数据库根据查询语句进行动态绑定.
在使用前必须首先使用TYPE关键字进行定义, 我们把数据类型REF_CURSOR定义在自定义的程序包中: pkg_const
create or replace package pkg_const as
type REF_CURSOR is ref cursor;
end pkg_const;
注意: 这个包需要在创建上面的存储过程之前被编译, 因为存储过程用到了包中定义的数据类型.
调试存储过程
使用PL/SQL Developer 登录数据库, 用户名scott, 密码默认为: tiger. 将包和存储过程分别编译, 然后在左侧浏览器的procedure栏目下找到新建的存储过程, 点击右键, 选择“Test”/“测试”, 在下面添好需要输入的参数值, 按快捷键F8直接运行存储过程, 执行完成之后, 可以点开返回参数旁边的按钮查看结果集.
如果存储过程内部语句较复杂, 可以按F9进入存储过程进行跟踪调试. PL/SQL Developer提供与通用开发工具类似的跟踪调试功能, 分为step、step over、step out 等多种方式， 对于变量也可进行trace或者手动赋值。
在存储过程中写日志文件
以上方法可以在开发阶段对编写和调试存储过程提供最大限度的方便，但为了在系统测试或者生产环境中确认我们的代码是否正常工作时，就需要记录log。
PLSQL提供了一个UTL_FILE包，通过定义UTL_FILE包中的FILE_TYPE类型，可以获得一个文件句柄，通过此句柄可以实现一般的文件操作功能。但默认的数据库参数是不允许使用UTL_FILE包的，需要手动进行配置，使用GUI的管理工具或者手工编辑INIT.ORA文件，找到 “utl_file_dir” 参数，如果没有，则添加一行，修改成如下：
utl_file_dir='/usr/tmp'
或者
utl_file_dir=*
第一种方式限定了在UTL_FILE包中可以存取的目录，第二种方式则不进行限定。无论哪种方式，都要保证运行数据库实例的用户，一般是oracle，拥有此目录的存取权限，否则在使用包的过程中会报出错误信息。
注意等号左右不要留空格，可能会引起解析错误，导致设置无效。
下面在上面的存储过程中加入记录log的代码：
create or replace procedure usp_getEmpByDept(
in_deptNo in number,
out_curEmp out pkg_const.REF_CURSOR
) as
fi utl_file.file_type;
begin
if( pkg_const.DEBUG ) then
fi := utl_file.fopen( pkg_const.LOG_PATH, to_char( sysdate, 'yyyymmdd' ) || '.log', 'a' );
utl_file.put_line( fi, ' ****** calling usp_getEmpByDept begin at ' || to_char( sysdate, 'hh24:mi:ss mm-dd-yyyy' ) || ' ******' );
utl_file.put_line( fi, ' INPUT:' );
utl_file.put_line( fi, ' in_chID => ' || in_chID );
end if;
open curEmp for
select empno,
ename
from scott.emp
where deptno = in_deptNo;
if( pkg_const.DEBUG ) then
utl_file.put_line( fi, ' RETURN:' );
utl_file.put_line( fi, ' out_curEmp: unknown' );
utl_file.put_line( fi, ' ****** usp_getEmpByDept end at ' || to_char( sysdate, 'hh24:mi:ss mm-dd-yyyy' ) || ' ******' );
utl_file.new_line( fi, 1 );
utl_file.fflush( fi );
utl_file.fclose( fi );
end if;
exception
when others then
if( pkg_const.DEBUG ) then
if( utl_file.is_open( fi )) then
utl_file.put_line( fi, ' ERROR:' );
utl_file.put_line( fi, ' sqlcode = ' || sqlcode );
utl_file.put_line( fi, ' sqlerrm = ' || sqlerrm );
utl_file.put_line( fi, ' ****** usp_getEmpByDept end at ' || to_char( sysdate, 'hh24:mi:ss mm-dd-yyyy' ) || ' ******' );
utl_file.new_line( fi, 1 );
utl_file.fflush( fi );
utl_file.fclose( fi );
end if;
end if;
/* Raise the exception for caller. */
raise_application_error( -1, sqlcode || '|' || sqlerrm );
end usp_getEmpByDept;
在上面的代码中，我们又引用了两个新的常量：
DEBUG
LOG_PATH
分别定义了调试开关参数和文件路径参数，对此，我们需要修改我们前面定义的程序包：
create or replace package pkg_const as
type REF_CURSOR is ref cursor;
DEBUG constant boolean := true;
LOG_PATH constant varchar2(256) := '/usr/tmp/db';
end pkg_const;
在代码块的起始处，将输入参数的名称与值成对的记入log文件，在代码块的正常退出部分，将输出参数的名称和数值也成对的记录下来，如果程序非正常退出，则在exception 的处理部分，把错误代码及错误信息写入log文件，
一般使用这些信息就可以较迅速的找出程序运行中出现的大部分错误。
注意：如果返回参数的类型是cursor，是无法在存储过程内部将返回的结果集一条一条写入log文件的，此时应当结合在调用程序中记录的log信息，下面具体分析一下上述代码：
fopen 函数使用给定的路径和文件名，新建文件或者打开已有的文件，这取决于最后一个参数, 当使用'a'作为参数时，如果给定的文件不存在，则以此文件名新建文件，并以写'w'方式打开，返回一个文件句柄。
上面代码以天为单位建立日志文件，并且，不同存储过程之间共享log文件，这种方式的优点是可能通过查看log文件追溯出程序的调用顺序和逻辑。实际应用中，应根据不同的需求，具体分析，可以使用更复杂的log文件生成策略。
put_line() 函数用于写入字符到文件，并在字符串的结尾加入换行符，若不想换行，使用put()函数。
new_line() 函数用于生成指定数目的空行，上面对文件的修改写在一个缓冲区内，执行fflush() 将立即将buffer中的内容写入文件，当你希望在文件还未关闭之前就需要读取已经作出的改变时，调用此函数。
is_open() 函数用于判断一个文件句柄的状态，最后用完一定记得把打开的文件关闭，调用fclose() 函数，并且应把这个语句加入exception的处理中，防止过程非正常退出时留下未关闭的文件句柄。
捕获违例
在PLSQL中，你可以通过两个内建的函数sqlcode 和sqlerrm 来找出发生了哪类错误并且获得详细的message信息，在内部违例发生时，sqlcode返回从-1至－20000之间的一个错误号，但有一个例外，仅当内部违例no_data_found 发生时，才会返回一个正数 100。当用户自定义的违例发生时，sqlcode返回＋1，除非用户使用 pragma EXCEPTION_INIT 将自定义违例绑定一个自定义的错误号。当没有任何违例抛出时，sqlcode返回0。
下面是一个简单的捕获违例的例子：
declare
i number(3);
begin
select 100/0 into i from dual;
exception
when zero_divide then
...
end;
在上面的exception 中我们使用others 关键字捕获所有未明确指定的违例，并进行记录log处理，同时我们必须在做完这些处理之后，把违例再次抛出给调用程序，调用函数：
raise_application_error()，此函数向调用程序返回一个用户自定义的错误号码和错误信息，第一个参数指定一个错误号码，由用户自行定义，但必须限定在-20000至－20999之间，避免与Oracle内部定义exception的错误号码冲突，第二个参数需要返回一个字符串，这里我们使用它返回我们上面捕获的错误号码和错误描述。
注意：通过raise_application_error()函数抛出的违例已经不是开始在程序块内部捕获的内部违例，而是由用户自己定义的。
篇2：PL/SQL过程数据库教程
要想利用PL/SQL程序完成比较完整的数据库任务，需要进一步学习一些高级设计要素的内容，前面编写执行的PL/SQL程序，共同的特点是没有名称，只能存储为文件，然后通过执行文件的方式执行，因此称为无名块。与此对应的是在PL/SQL中也引入了高级程序设计的一些概念，其中最重要的就是过程。
过程就是高级程序设计语言中的模块的概念，将一些内部联系的命令组成一个个过程，通过参数在过程之间传递数据是模块化设计思想的重要内容。
创建过程
1. 过程的语法结构
完整的过程结构如下：
create or replace procedure 过程名 as
声明语句段;
begin
执行语句段;
exception
异常处理语句段;
end;
2. 过程的特点
过程是有名称的程序块，as关键词代替了无名块的declare。
3. 创建过程实例
在【SQLPlus Worksheet】中执行下列PL/SQL程序，该程序将创建名为tempprocedure的过程，create是创建过程的标识符，replace表示若同名过程存在将覆盖原过程。该过程定义了一个变量，其类型和testtable数据表中的currentdate字段类型相同，都是日期型，将数据表中的recordnumber字段为88的currentdate字段内容送入变量中，然后输出结果。
DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD
set serveroutput on
create or replace procedure tempuser.tempprocedure as
tempdate tempuser.testtable.currentdate%type;
begin
select currentdate
into tempdate
from testtable
where recordnumber=88;
dbms_output.put_line(to_char(tempdate));
end;
DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD
【配套程序位置】：第9章 createprocedure.sql。
执行结果如图9.41所示。
查询过程
登录【企业管理器】，在【管理目标导航树】里选择【网络】/【数据库】/【myoracle.mynet】/【方案】/【过程】/【TEMPUSER】选项，出现如图9.42所示的创建好的过程。
修改过程
（1）在【SQLPlus Worksheet】的【菜单栏】选择【文件】/【打开】菜单命令，将创建过程的createprocedure.sql文件调出进行修改，修改完毕后重新执行创建过程。
（2）在【企业管理器】里选中要修改的过程，用鼠标右键单击，在出现的快捷菜单里选择【查看/编辑详细资料】选项，如图9.43所示。
（3）出现如图9.44所示的编辑过程的【一般信息】选项卡。在【文本编辑区】可以编辑该过程，单击“确定”按钮将更新该过程，单击“编译”按钮将编译该过程的 PL/SQL 源代码，使该过程可以在数据库中存储和执行。
执行过程
要执行创建的过程，必须通过主程序来调用过程。
在【SQLPlus Worksheet】中执行下列PL/SQL程序，执行结果如图9.45所示。
DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD
set serveroutput on
begin
tempprocedure;
end;
DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD
【配套程序位置】：第9章 executeprocedure.sql，
在Oracle中，创建好的过程可以被任何程序调用。
带参数的过程
前面介绍的过程没有参数，主程序和过程没有数据的传递，下面介绍带参数的过程的设计和使用。
1. 参数类型
在PL/SQL过程中，可以有3种类型的参数。
in参数：读入参数，主程序向过程传递参数值。
out参数：读出参数，过程向主程序传递参数值。
in out 参数：双向参数，过程与主程序双向交流数据。
2. 定义带参数的过程
在下面的PL/SQL程序代码中，将创建三个调用参数。
tempdeptno：类型为in，与scott.dept.deptno的类型一致，为数值型。
tempdname：类型为out，与scott.dept.dname的类型一致，为字符型。
temploc：类型为in out，与scott.dept.loc类型一致，为字符型。
创建两个过程内参数。
loc1：与scott.dept.loc的类型一致，为字符型。
dname1：与scott.dept.dname的类型一致，为字符型。
该带参数的过程的功能是从数据表scott.dept中寻找deptno字段等于tempdeptno调用参数值的dname和loc字段，和其他字符组合，送给两个出口参数。
以system用户名、sysdba身份登录【SQLPlus Worksheet】，执行下列PL/SQL程序，执行结果如图9.46所示。
DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD
Set serveroutput on
create or replace procedure scott.tempprocedure(
tempdeptno in scott.dept.deptno%type,
tempdname out scott.dept.dname%type,
temploc in out scott.dept.loc%type)as
loc1 scott.dept.loc%type;
dname1 scott.dept.dname%type;
begin
select loc into loc1
from scott.dept
where deptno=tempdeptno;
select dname into dname1
from scott.dept
where deptno=tempdeptno;
temploc:='地址:'||loc1;
tempdname:='姓名'||dname1;
end;
DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD
【配套程序位置】：第9章 createscottprocedure.sql。
调用参数分割用“,”号。
3. 使用带参数的过程
在主程序中的实际参数和过程中的形式参数的传递有很多种办法，这里推荐读者采用一一对应的办法，按对应的位置传递参数。要求实际参数和形式参数在数据类型和位置排列上做到完全一致。
在【SQLPlus Worksheet】中执行下列PL/SQL程序，该程序调用带参数的过程scott.tempprocedure，实际参数为（10,’’,’’）
执行结果如图9.47所示。
【配套程序位置】：第9章 executescottprocedure.sql。
读者可以尝试改变参数值，然后测试过程执行结果。
篇3：SQL分隔字符串的存储过程数据库教程
存储过程|字符串
我在做一个项目时研究了查询多个关键字的技术，这是其中用到的一个技术，SQL分隔字符串的存储过程数据库教程
。“分隔字符串”存储过程的作用就是把“jiangjs,shenxy,cheng”类的字符串隔开保存到一个表中。 CREATE PROCEDURE [分隔字符串](
@String NVARCHAR(1000), -- 要分隔的字符串
@SPLITCHAR NVARCHAR(10) = ',', -- 分隔字符
@TableName NVARCHAR(50), -- 存入的表名称
@FieldName NVARCHAR(50) = '[ID]' -- 存入的字段名称
)
AS -- 将字符串分隔开放进表中 DECLARE @L INT -- 第一个分隔字符的位置
DECLARE @S INT -- 第二个分隔字符的位置
SET @L = 0
SET @S = CHARINDEX(@SPLITCHAR, @String, @L) WHILE @L <= LEN(@String)
BEGIN
DECLARE @ID NVARCHAR(50) IF @S = 0 SET @S = LEN(@String) + 1 -- 如果到最后一个字符串那么第二个分隔字符的位置就是这个字符串的长度加一
SET @ID = SUBSTRING(@String, @L, @S - @L) -- 取值
SET @L = @S + 1
SET @S = CHARINDEX(@SPLITCHAR, @String, @L) IF LTRIM(RTRIM(@ID)) = '' CONTINUE -- 如果是空字符串就跳过 DECLARE @SQL NVARCHAR(1000)
SET @SQL = 'INSERT INTO ' + @TableName + ' ('+ @FieldName +') VALUES(''' + @ID + ''')'
EXEC sp_executesql @SQL
END
GO ----------NotMSN Messenger : notking@hotmail.com
篇4：快速入门SQL定义存储过程数据库教程
SQL存储过程在SQL数据库中用途广泛，下面为您介绍如何定义SQL存储过程，如果您是刚接触SQL数据库的用户，不妨一看，希望对您学习SQL存储过程有所帮助，
CREATEPROCEDUREget_tableinfoASifnotexists (select*fromdbo.sysobjectswhereid = object_id(N'[dbo].[tablespaceinfo]')andOBJECTPROPERTY(id, N'IsUserTable') = 1)createtabletablespaceinfo--创建结果存储表(nameinfovarchar(50) ,rowsinfoint, reservedvarchar(20) ,datainfovarchar(20) ,index_sizevarchar(20) ,unusedvarchar(20) )deletefromtablespaceinfo--清空数据表declare@tablenamevarchar(255)--表名称declare@cmdsqlvarchar(500)DECLAREInfo_cursorCURSORFORselecto.namefromdbo.sysobjects owhereOBJECTPROPERTY(o.id, N'IsTable') = 1ando.namenotlikeN'#%%'orderbyo.nameOPENInfo_cursorFETCHNEXTFROMInfo_cursorINTO@tablenameWHILE @@FETCH_STATUS = 0BEGINif exists (select*fromdbo.sysobjectswhereid = object_id(@tablename)andOBJECTPROPERTY(id, N'IsUserTable') = 1)executesp_executesqlN'insert into tablespaceinfo exec sp_spaceused @tbname',N'@tbname varchar(255)',@tbname = @tablenameFETCHNEXTFROMInfo_cursorINTO@tablenameENDCLOSEInfo_cursorDEALLOCATEInfo_cursorGO
篇5：PL/SQL中的几种异常处理方法数据库教程
异常处理
这是Pona的文章，我斗胆将其贴上来，Pona不要介意哦！^_^
PL/SQL里，有三种方法可以在处理大批量数据时不会因为一条或几条数据错误而导致异常中止程序，
1、用Fetch into a cursor%TYPE把要处理的数据放到记录集里。当一条数据不符条件时，用标签<
-------------------------------------------------------------------------------
-- Function Name : CalculateImportCharge
-- Function Desc : Calculate Import Charge
-- Created by : Author
-- Created Date : -05-16
-------------------------------------------------------------------------------
FUNCTION CalculateImportCharge (
p_i_job_id IN VARCHAR2,
p_i_as_of_date_id IN VARCHAR2) RETURN NUMBER
AS
CURSOR cur_ShipBlHeader IS
SELECT import_folder_no
FROM GMY_SHIP_BL_HEADER
WHERE CANCEL_FLG = GMY_GA000_PKG.BL_CANCEL_FLG_OFF;
rec_ShipBlHeader cur_ShipBlHeader%ROWTYPE;
BEGIN
OPEN cur_ShipBlHeader;
FETCH cur_ShipBlHeader INTO rec_ShipBlHeader;
WHILE cur_ShipBlHeader%FOUND LOOP
x_num_error_code := GMY_GA000_PKG.CheckValidMasterBlNo (
p_i_job_id,
p_i_as_of_date_id,
rec_ShipBlHeader.import_folder_no,
x_vch_message);
IF x_num_error_code
IN (GMY_GA000_PKG.gn#NG, GMY_GA000_PKG.INVALID_BL_NO) THEN
x_vch_message :=
p_i_job_id
|| ' WARNING: Function CheckValidMasterBlNo @'
|| ' Import folder '
|| rec_ShipBlHeader.import_folder_no
|| ' - Invalid BL No.';
COM_LOG.PUTLINE (p_i_job_id, x_vch_message);
GOTO NEXT_RECORD;
END IF;
x_num_error_code := CheckExistsOfAccDate (
p_i_job_id,
p_i_as_of_date_id,
rec_ShipBlHeader.import_folder_no);
IF x_num_error_code = GMY_GA000_PKG.gn#NG THEN
GOTO NEXT_RECORD;
END IF;
COMMIT;
<
FETCH cur_ShipBlHeader INTO rec_ShipBlHeader;
END LOOP;
CLOSE cur_ShipBlHeader;
RETURN GMY_GA000_PKG.gn#OK;
EXCEPTION
WHEN OTHERS THEN
x_vch_message :=
p_i_job_id
|| ' ERROR: Function CalculateImportCharge @ '
|| SUBSTR (SQLERRM (SQLCODE), 1, 100);
COM_LOG.PUTLINE (p_i_job_id, x_vch_message);
RETURN GMY_GA000_PKG.gn#NG;
END CalculateImportCharge;
2、当使用the Cursor FOR Loop循环时，在Loop循环里，把会出问题的情况写进一个独立的block块中，这个块包括完整的begin、end部分及exception异常处理部分。这样即使一条数据出现异常，也会继续执行下一条。
-------------------------------------------------------------------------------
-- Function Name : GenerateInsCostInfRec
-- Function Desc : Generate records to transmit in INF table
-- Created by : SISS(AP)
-- Created Date : 2003-03-26
-- ----------------------------------------------------------------------------
FUNCTION GenerateInsCostInfRec (
p_i_job_id IN VARCHAR2,
p_i_as_of_date_id IN VARCHAR2) RETURN NUMBER
AS
CURSOR cur_cost IS
SELECT cost.ROWID costRowId,
cost.import_folder_no,,
cost.insur_trans_id
FROM GMY_COST_BL cost,
GMY_COMMON_MST mst
WHERE cost.import_folder_no=invheader.import_folder_no
AND cost.billing_amt_num IS NOT NULL
AND cost.billing_amt_num!=0
AND cost.insur_db_cr!=0;
BEGIN
FOR rec_cost IN cur_cost LOOP
BEGIN
x_num_ret_value := GMY_GA000_PKG.CheckValidMasterBlNo(
p_i_job_id,
p_i_as_of_date_id,
rec_cost.import_folder_no,
x_vch_error_msg);
IF x_num_ret_value = GMY_GA000_PKG.VALID_BL_NO THEN
INSERT INTO GMY_COST_INS_INF(
cost_trx_id,,
created_by,
program_name)
VALUES(
GMY_COST_INS_INF_S.NEXTVAL,
PRG_NAME,
PRG_NAME);
ELSIF x_num_ret_value = GMY_GA000_PKG.INVALID_BL_NO THEN
x_vch_error_msg := p_i_job_id
|| ' Import folder '
|| rec_cost.import_folder_no
|| ' has repeated BL No. with other import folder.'
|| ' Failed in insurance cost transmission.';
COM_LOG.PUTLINE(p_i_job_id, x_vch_error_msg);
END IF;
EXCEPTION
WHEN OTHERS THEN
IF SQL%ROWCOUNT > 0 THEN -- check for 'too many rows'
x_vch_error_msg := p_i_job_id||' '||
SUBSTR(SQLERRM(SQLCODE),1,100);
COM_LOG.PUTLINE(p_i_job_id, x_vch_error_msg);
ELSE
x_vch_error_msg := p_i_job_id||' '||
SUBSTR(SQLERRM(SQLCODE),1,100);
COM_LOG.PUTLINE(p_i_job_id, x_vch_error_msg);
END IF;
END;
END LOOP;
COMMIT;
RETURN GMY_GA000_PKG.gn#OK;
EXCEPTION
WHEN OTHERS THEN
x_vch_error_msg := p_i_job_id||' '||SUBSTR(SQLERRM(SQLCODE),1,100);
COM_LOG.PUTLINE(p_i_job_id, x_vch_error_msg);
ROLLBACK;
RETURN GMY_GA000_PKG.gn#NG;
END GenerateInsCostInfRec;
3、当使用the Cursor FOR Loop循环时，在Loop循环里，把会出问题的情况拆分成子函数，分别处理，
----------------------------------------------------------------------------
-- Function Name : CopyDsToActualDs
-- Function Desc : Copy the records from DS DB to Actual DS DB.
-- Created by : Author
-- Created Date : 2003-02-20
----------------------------------------------------------------------------
FUNCTION CopyDsToActualDs (
p_i_job_id IN VARCHAR2,
p_i_as_of_date_id IN VARCHAR2) RETURN NUMBER
IS
CURSOR cur_DsScc IS
SELECT *
FROM GMY_DS_SCC;
BEGIN
FOR rec_DsHead IN cur_DsScc LOOP
x_num_error_code := InsToActualScc(
p_i_job_id,
p_i_as_of_date_id,
rec_DsHead.order_by_code,
rec_DsHead.po_code,
rec_DsHead.wh);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
x_vch_error_msg := p_i_job_id
||' Function Name: CopyDsToActualDs';
COM_LOG.PUTLINE(p_i_job_id,x_vch_error_msg);
x_vch_error_msg:=p_i_job_id||' '||SUBSTR(SQLERRM(SQLCODE),1,100);
COM_LOG.PUTLINE(p_i_job_id, x_vch_error_msg);
ROLLBACK;
RETURN GMY_GA000_PKG.gn#NG;
END CopyDsToActualDs;
----------------------------------------------------------------------------
-- Function Name : InsToActualScc
-- Function Desc : Deal with insert section.
-- Created by : Author
-- Created Date : 2003-03-13
----------------------------------------------------------------------------
FUNCTION InsToActualScc(
p_i_job_id IN VARCHAR2,
p_i_as_of_date_id IN VARCHAR2,
p_i_order_by_code IN VARCHAR2,
p_i_po_code IN VARCHAR2,
p_i_wh IN VARCHAR2
) RETURN NUMBER
IS
x_vch_error_msg VARCHAR2(255);
BEGIN
INSERT INTO GMY_ACTUAL_DS_SCC(
order_by_code,
po_code,
wh )
VALUES( p_i_order_by_code,
p_i_po_code,
p_i_wh);
COMMIT;
RETURN GMY_GA000_PKG.gn#OK;
EXCEPTION
WHEN OTHERS THEN
x_vch_error_msg := p_i_job_id||' Function Name: InsToActualScc';
COM_LOG.PUTLINE(p_i_job_id,x_vch_error_msg);
x_vch_error_msg := p_i_job_id
||' The key of the record that failed to insert is: ';
COM_LOG.PUTLINE(p_i_job_id,x_vch_error_msg);
ROLLBACK;
RETURN GMY_GA000_PKG.gn#NG;
END InsToActualScc;
篇6：动态创建SQL Server数据库、表、存储过程数据库教程
server|创建|存储过程|动态|数据|数据库
下面是利用SQL语句创建数据库、表、存储过程、视图、索引、规则、修改表、查看数据等的方法，所要增加的控件如下：
Imports System.Data
Imports System.Data.SqlClient
Public Class Form1
Inherits System.Windows.Forms.Form
Private ConnectionString As String = “Data Source=.;Initial Catalog=;User Id=sa;Password=;”
Private reader As SqlDataReader = Nothing
Private conn As SqlConnection = Nothing
Private cmd As SqlCommand = Nothing
Private AlterTableBtn As System.Windows.Forms.Button
Private sql As String = Nothing
Private CreateOthersBtn As System.Windows.Forms.Button
#Region “ Windows 窗体设计器生成的代码 ”
'窗体重写处置以清理组件列表。
Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
If disposing Then
If Not (components Is Nothing) Then
components.Dispose
End If
End If
MyBase.Dispose(disposing)
End Sub
Public Sub New()
MyBase.New()
InitializeComponent()
End Sub
Private components As System.ComponentModel.IContainer
Friend WithEvents DataGrid1 As System.Windows.Forms.DataGrid
Friend WithEvents CreateDBBtn As System.Windows.Forms.Button
Friend WithEvents CreateTableBtn As System.Windows.Forms.Button
Friend WithEvents CreateSPBtn As System.Windows.Forms.Button
Friend WithEvents CreateViewBtn As System.Windows.Forms.Button
Friend WithEvents btnAlterTable As System.Windows.Forms.Button
Friend WithEvents btnCreateOthers As System.Windows.Forms.Button
Friend WithEvents btnDropTable As System.Windows.Forms.Button
Friend WithEvents btnViewData As System.Windows.Forms.Button
Friend WithEvents btnViewSP As System.Windows.Forms.Button
Friend WithEvents btnViewView As System.Windows.Forms.Button
Me.CreateDBBtn = New System.Windows.Forms.Button()
Me.CreateTableBtn = New System.Windows.Forms.Button()
Me.CreateSPBtn = New System.Windows.Forms.Button()
Me.CreateViewBtn = New System.Windows.Forms.Button()
Me.btnAlterTable = New System.Windows.Forms.Button()
Me.btnCreateOthers = New System.Windows.Forms.Button()
Me.btnDropTable = New System.Windows.Forms.Button()
Me.btnViewData = New System.Windows.Forms.Button()
Me.btnViewSP = New System.Windows.Forms.Button()
Me.btnViewView = New System.Windows.Forms.Button()
Me.DataGrid1 = New System.Windows.Forms.DataGrid()
CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).BeginInit()
Me.SuspendLayout()
'
'CreateDBBtn
'
Me.CreateDBBtn.Location = New System.Drawing.Point(19, 9)
Me.CreateDBBtn.Name = “CreateDBBtn”
Me.CreateDBBtn.Size = New System.Drawing.Size(104, 23)
Me.CreateDBBtn.TabIndex = 0
Me.CreateDBBtn.Text = “创建数据库”
'
'CreateTableBtn
'
Me.CreateTableBtn.Location = New System.Drawing.Point(139, 9)
Me.CreateTableBtn.Name = “CreateTableBtn”
Me.CreateTableBtn.TabIndex = 1
Me.CreateTableBtn.Text = “创建表”
'
'CreateSPBtn
'
Me.CreateSPBtn.Location = New System.Drawing.Point(230, 9)
Me.CreateSPBtn.Name = “CreateSPBtn”
Me.CreateSPBtn.Size = New System.Drawing.Size(104, 23)
Me.CreateSPBtn.TabIndex = 2
Me.CreateSPBtn.Text = “创建存储过程”
'
'CreateViewBtn
'
Me.CreateViewBtn.Location = New System.Drawing.Point(350, 9)
Me.CreateViewBtn.Name = “CreateViewBtn”
Me.CreateViewBtn.TabIndex = 3
Me.CreateViewBtn.Text = “创建视图”
'
'btnAlterTable
'
Me.btnAlterTable.Location = New System.Drawing.Point(441, 9)
Me.btnAlterTable.Name = “btnAlterTable”
Me.btnAlterTable.TabIndex = 4
Me.btnAlterTable.Text = “修改表”
'
'btnCreateOthers
'
Me.btnCreateOthers.Location = New System.Drawing.Point(17, 43)
Me.btnCreateOthers.Name = “btnCreateOthers”
Me.btnCreateOthers.Size = New System.Drawing.Size(104, 23)
Me.btnCreateOthers.TabIndex = 5
Me.btnCreateOthers.Text = “创建规则和索引”
'
'btnDropTable
'
Me.btnDropTable.Location = New System.Drawing.Point(138, 43)
Me.btnDropTable.Name = “btnDropTable”
Me.btnDropTable.TabIndex = 6
Me.btnDropTable.Text = “删除表”
'
'btnViewData
'
Me.btnViewData.Location = New System.Drawing.Point(351, 43)
Me.btnViewData.Name = “btnViewData”
Me.btnViewData.TabIndex = 7
Me.btnViewData.Text = “查看数据”
'
'btnViewSP
'
Me.btnViewSP.Location = New System.Drawing.Point(230, 43)
Me.btnViewSP.Name = “btnViewSP”
Me.btnViewSP.Size = New System.Drawing.Size(104, 23)
Me.btnViewSP.TabIndex = 8
Me.btnViewSP.Text = “查看存储过程”
'
'btnViewView
'
Me.btnViewView.Location = New System.Drawing.Point(443, 43)
Me.btnViewView.Name = “btnViewView”
Me.btnViewView.TabIndex = 9
Me.btnViewView.Text = “查看视图”
'
'DataGrid1
'
Me.DataGrid1.DataMember = “”
Me.DataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText
Me.DataGrid1.Location = New System.Drawing.Point(20, 76)
Me.DataGrid1.Name = “DataGrid1”
Me.DataGrid1.Size = New System.Drawing.Size(500, 183)
Me.DataGrid1.TabIndex = 10
'
'Form1
'
Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
Me.ClientSize = New System.Drawing.Size(538, 281)
Me.Controls.AddRange(New System.Windows.Forms.Control() {Me.DataGrid1, Me.btnViewView, _
Me.btnViewSP, Me.btnViewData, Me.btnDropTable, Me.btnCreateOthers, Me.btnAlterTable, _
Me.CreateViewBtn, Me.CreateSPBtn, Me.CreateTableBtn, Me.CreateDBBtn})
Me.Name = “Form1”
Me.Text = “动态创建SQL Server数据库、表、存储过程等架构信息”
CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).EndInit()
Me.ResumeLayout(False)
End Sub
#End Region
' 创建数据库
Private Sub CreateDBBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles CreateDBBtn.Click
conn = New SqlConnection(ConnectionString)
' 打开连接
If conn.State ConnectionState.Open Then
conn.Open()
End If
'MyDataBase为数据库名称
Dim sql As String = “CREATE DATABASE MyDataBase ON PRIMARY (Name=MyDataBase_data, filename = ” + _
“'D:MyDataBase.mdf', size=3,” + “maxsize=5, filegrowth=10%) log on” + “(name=MyDataBase_log, ” + _
“filename='D:MyDataBase.ldf',size=3,” + “maxsize=20,filegrowth=1)”
cmd = New SqlCommand(sql, conn)
Try
cmd.ExecuteNonQuery()
Catch ae As SqlException
MessageBox.Show(ae.Message.ToString())
End Try
End Sub
'创建表
Private Sub CreateTableBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles CreateTableBtn.Click
conn = New SqlConnection(ConnectionString)
' 打开连接
If conn.State = ConnectionState.Open Then
conn.Close()
End If
ConnectionString = “Data Source=.;Initial Catalog=MyDataBase;User Id=sa;Password=;”
conn.ConnectionString = ConnectionString
conn.Open()
sql = “CREATE TABLE myTable” + “(myId INTEGER CONSTRAINT PKeyMyId PRIMARY KEY,” + _
“myName CHAR(50) NOT Null, myAddress CHAR(255), myValues FLOAT)”
cmd = New SqlCommand(sql, conn)
Try
cmd.ExecuteNonQuery()
' 添加纪录
sql = “INSERT INTO myTable(myId, myName, myAddress, myValues) ” + _
“VALUES (1001, _'【孟宪会之精彩世界】之一', 'xml.sz.luohuedu.net/', 100 ) ”
cmd = New SqlCommand(sql, conn)
cmd.ExecuteNonQuery()
sql = “INSERT INTO myTable(myId, myName, myAddress, myValues) ” + _
“VALUES (1002, '【孟宪会之精彩世界】之二', 'www.erp800.com/net_lover/', 99) ”
cmd = New SqlCommand(sql, conn)
cmd.ExecuteNonQuery()
sql = “INSERT INTO myTable(myId, myName, myAddress, myValues) ” + _
“VALUES (1003, '【孟宪会之精彩世界】之三', 'xml.sz.luohuedu.net/', 99) ”
cmd = New SqlCommand(sql, conn)
cmd.ExecuteNonQuery()
sql = “INSERT INTO myTable(myId, myName, myAddress, myValues) ” + _
“VALUES (1004, '【孟宪会之精彩世界】之四', 'www.erp800.com/net_lover/', 100) ”
cmd = New SqlCommand(sql, conn)
cmd.ExecuteNonQuery()
Catch ae As SqlException
MessageBox.Show(ae.Message.ToString())
End Try
End Sub
'创建存储过程
Private Sub CreateSPBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles CreateSPBtn.Click
sql = “CREATE PROCEDURE myProc AS” + “ SELECT myName, myAddress FROM myTable GO”
ExecuteSQLStmt(sql)
End Sub
'创建视图
Private Sub CreateViewBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles CreateViewBtn.Click
sql = “CREATE VIEW myView AS SELECT myName FROM myTable”
ExecuteSQLStmt(sql)
End Sub
'修改表
Private Sub btnAlterTable_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles btnAlterTable.Click
sql = “ALTER TABLE MyTable ADD newCol datetime NOT NULL DEFAULT (getdate())”
ExecuteSQLStmt(sql)
End Sub
'创建规则和索引
Private Sub btnCreateOthers_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles btnCreateOthers.Click
sql = “CREATE UNIQUE INDEX ” + “myIdx ON myTable(myName)”
ExecuteSQLStmt(sql)
sql = “CREATE RULE myRule ” + “AS @myValues >= 90 AND @myValues < 9999”
ExecuteSQLStmt(sql)
End Sub
'删除表
Private Sub btnDropTable_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles btnDropTable.Click
Dim sql As String = “DROP TABLE MyTable”
ExecuteSQLStmt(sql)
End Sub
'浏览表数据
Private Sub btnViewData_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles btnViewData.Click
conn = New SqlConnection(ConnectionString)
If conn.State = ConnectionState.Open Then
conn.Close()
End If
ConnectionString = “Data Source=.;Initial Catalog=MyDataBase;User Id=sa;Password=;”
conn.ConnectionString = ConnectionString
conn.Open()
Dim da As New SqlDataAdapter(“SELECT * FROM myTable”, conn)
Dim ds As New DataSet(“myTable”)
da.Fill(ds, “myTable”)
DataGrid1.DataSource = ds.Tables(“myTable”).DefaultView
End Sub
'浏览存储过程
Private Sub btnViewSP_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles btnViewSP.Click
conn = New SqlConnection(ConnectionString)
If conn.State = ConnectionState.Open Then
conn.Close()
End If
ConnectionString = “Data Source=.;Initial Catalog=MyDataBase;User Id=sa;Password=;”
conn.ConnectionString = ConnectionString
conn.Open()
Dim da As New SqlDataAdapter(“myProc”, conn)
Dim ds As New DataSet(“SP”)
da.Fill(ds, “SP”)
DataGrid1.DataSource = ds.DefaultViewManager
End Sub
'浏览视图
Private Sub btnViewView_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles btnViewView.Click
conn = New SqlConnection(ConnectionString)
If conn.State = ConnectionState.Open Then
conn.Close()
End If
ConnectionString = “Data Source=.;Initial Catalog=MyDataBase;User Id=sa;Password=;”
conn.ConnectionString = ConnectionString
conn.Open()
Dim da As New SqlDataAdapter(“SELECT * FROM myView”, conn)
Dim ds As New DataSet()
da.Fill(ds)
DataGrid1.DataSource = ds.DefaultViewManager
End Sub
Private Sub ExecuteSQLStmt(ByVal sql As String)
conn = New SqlConnection(ConnectionString)
' 打开连接
If conn.State = ConnectionState.Open Then
conn.Close()
End If
ConnectionString = “Data Source=.;Initial Catalog=MyDataBase;User Id=sa;Password=;”
conn.ConnectionString = ConnectionString
conn.Open()
cmd = New SqlCommand(sql, conn)
Try
cmd.ExecuteNonQuery()
Catch ae As SqlException
MessageBox.Show(ae.Message.ToString())
End Try
End Sub
End Class
篇7：SQL SERVER数据库备份和恢复存储过程(加强版本)数据库教程
server|备份|存储过程|恢复|数据|数据库
SQL SERVER2000数据库备份和恢复存储过程(加强版本)
我自己写的2个过程和一个函数,用于SQL SERVER2000数据库备份和恢复
拿出来和大家交流一下,过程和函数的详细说明在代码中
谢谢
/*备份数据库的过程*/
if exists(
select * from sysobjects
where name='pr_backup_db' and xtype='p'
)
begin
drop proc pr_backup_db
end
go
create proc pr_backup_db
@flag varchar(20) out,
@backup_db_name varchar(128),
@filename varchar(1000) --路径＋文件名字
as
declare @sql nvarchar(4000),@par nvarchar(1000)
if not exists(
select * from master..sysdatabases
where name=@backup_db_name
)
begin
select @flag='db not exist' /*数据库不存在*/
return
end
else
begin
if right(@filename,1)'' and charindex('',@filename)0
begin
select @par='@filename varchar(1000)'
select @sql='BACKUP DATABASE '+@backup_db_name+' to disk=@filename with init'
execute sp_executesql @sql,@par,@filename
select @flag='ok'
return
end
else
begin
select @flag='file type error' /*参数@filename输入格式错误*/
return
end
end
GO
说明:pr_backup_db过程是备份你的数据库
/*创建函数,得到文件得路径*/
if exists(
select * from sysobjects
where name='fn_GetFilePath' and xtype='fn'
)
begin
drop function fn_GetFilePath
end
go
create function fn_GetFilePath(@filename nvarchar(260))
returns nvarchar(260)
as
begin
declare @file_path nvarchar(260)
declare @filename_reverse nvarchar(260)
select @filename_reverse=reverse(@filename)
select @file_path=substring(@filename,1,len(@filename)+1-charindex('',@filename_reverse))
return @file_path
end
GO
/*恢复数据库的过程*/
if exists(
select * from sysobjects
where name='pr_restore_db' and xtype='p'
)
begin
drop proc pr_restore_db
end
go
CREATE proc pr_restore_db
@flag varchar(20) out, /*过程运行的状态标志,是输入参数*/
@restore_db_name nvarchar(128), /*要恢复的数据名字*/
@filename nvarchar(260) /*备份文件存放的路径+备份文件名字*/
as
declare @proc_result tinyint /*返回系统存储过程xp_cmdshell运行结果*/
declare @loop_time smallint /*循环次数*/
declare @max_ids smallint /*@tem表的ids列最大数*/
declare @file_bak_path nvarchar(260) /*原数据库存放路径*/
declare @flag_file bit /*文件存放标志*/
declare @master_path nvarchar(260) /*数据库master文件路径*/
declare @sql nvarchar(4000),@par nvarchar(1000)
declare @sql_sub nvarchar(4000)
declare @sql_cmd nvarchar(100)
declare @sql_kill nvarchar(100)
/*
判断参数@filename文件格式合法性，以防止用户输入类似d: 或者 c:a 等非法文件名
参数@filename里面必须有''并且不以''结尾
*/
if right(@filename,1)'' and charindex('',@filename)0
begin
select @sql_cmd='dir '+@filename
EXEC @proc_result = master..xp_cmdshell @sql_cmd,no_output
IF (@proc_result0) /*系统存储过程xp_cmdshell返回代码值:0(成功）或1（失败）*/
begin
select @flag='not exist' /*备份文件不存在*/
return /*退出过程*/
end
/*创建临时表,保存由备份集内包含的数据库和日志文件列表组成的结果集*/
create table #tem(
LogicalName nvarchar(128), /*文件的逻辑名称*/
PhysicalName nvarchar(260) , /*文件的物理名称或操作系统名称*/
Type char(1), /*数据文件 (D) 或日志文件 (L)*/
FileGroupName nvarchar(128), /*包含文件的文件组名称*/
[Size] numeric(20,0), /*当前大小（以字节为单位）*/
[MaxSize] numeric(20,0) /*允许的最大大小（以字节为单位）*/
)
/*
创建表变量，表结构与临时表基本一样
就是多了两列,
列ids（自增编号列）,
列file_path,存放文件的路径
*/
declare @tem table(
ids smallint identity, /*自增编号列*/
LogicalName nvarchar(128),
PhysicalName nvarchar(260),
File_path nvarchar(260),
Type char(1),
FileGroupName nvarchar(128)
)
insert into #tem
execute('restore filelistonly from disk='''+@filename+'''')
/*将临时表导入表变量中,并且计算出相应得路径*/
insert into @tem(LogicalName,PhysicalName,File_path,Type,FileGroupName)
select LogicalName,PhysicalName,dbo.fn_GetFilePath(PhysicalName),Type,FileGroupName
from #tem
if @@rowcount>0
begin
drop table #tem
end
select @loop_time=1
select @max_ids=max(ids) /*@tem表的ids列最大数*/
from @tem
while @loop_time<=@max_ids
begin
select @file_bak_path=file_path
from @tem where ids=@loop_time
select @sql_cmd='dir '+@file_bak_path
EXEC @proc_result = master..xp_cmdshell @sql_cmd,no_output
/*系统存储过程xp_cmdshell返回代码值:0(成功）或1（失败）*/
IF (@proc_result0)
select @loop_time=@loop_time+1
else
BREAK /*没有找到备份前数据文件原有存放路径，退出循环*/
end
select @master_path=''
if @loop_time>@max_ids
select @flag_file=1 /*备份前数据文件原有存放路径存在*/
else
begin
select @flag_file=0 /*备份前数据文件原有存放路径不存在*/
select @master_path=dbo.fn_GetFilePath(filename)
from master..sysdatabases
where name='master'
end
select @sql_sub=''
/*type='d'是数据文件,type='l'是日志文件 */
/*@flag_file=1时新的数据库文件还是存放在原来路径，否则存放路径和master数据库路径一样*/
select @sql_sub=@sql_sub+'move '''+LogicalName+''' to '''
+case type
when 'd' then case @flag_file
when 1 then File_path
else @master_path
end
when 'l' then case @flag_file
when 1 then File_path
else @master_path
end
end
+case type
when 'd' then @restore_db_name
+'_DATA'
+convert(sysname,ids) /*给文件编号*/
+'.'
+right(PhysicalName,3) /*给文件加入后缀名,mdf or ndf*/
+''','
when 'l' then @restore_db_name
+'_LOG'
+convert(sysname,ids) /*给文件编号*/
+'.'
+right(PhysicalName,3) /*给文件加入后缀名,mdf or ndf*/
+''','
end
from @tem
select @sql='RESTORE DATABASE @db_name FROM DISK=@filename with '
select @sql=@sql+@sql_sub+'replace'
select @par='@db_name nvarchar(128),@filename nvarchar(260)'
/*关闭相关进程，把相应进程状况导入临时表中*/
select identity(int,1,1) ids, spid
into #temp
from master..sysprocesses
where dbid=db_id(@restore_db_name)
if @@rowcount>0 --找到相应进程
begin
select @max_ids=max(ids)
from #temp
select @loop_time=1
while @loop_time<=@max_ids
begin
select @sql_kill='kill '+convert(nvarchar(20),spid)
from #temp
where ids=@loop_time
execute sp_executesql @sql_kill
select @loop_time=@loop_time+1
end
end
drop table #temp
execute sp_executesql @sql,@par,@db_name=@restore_db_name,@filename=@filename
select @flag='ok' /*操作成功*/
end
else
begin
SELECT @flag='file type error' /*参数@filename输入格式错误*/
end
GO
--run
--备份数据库test_database
declare @fl varchar(10)
execute pr_backup_db @fl out,'test_database','c:test_database.bak'
select @fl
--恢复数据库,输入的参数错误
declare @fl varchar(20)
exec pr_restore_db @fl out,'sa','c:'
select @fl
--恢复数据库,即创建数据库test_database的复本test_db
declare @fl varchar(20)
exec pr_restore_db @fl out,'test_db','c:test_database.bak'
select @fl
以上过程和函数在MS SQL2000运行成功,由于MS SQL7不支持用户自定义函数和表变量,要在MS SQL7下使用可以把函数fn_GetFilePath改写成过
程,把过程pr_restore_db中的表变量改写为临时表即可运行,有兴趣的朋友可以试试!
我的Email:aierong@2118.cn
欢迎大家交流
篇8：存储过程在数据库中的作用
第一：存储过程因为SQL语句已经预编绎过了，因此运行的速度比较快，
第二：存储过程可以接受参数、输出参数、返回单个或多个结果集以及返回值。可以向程序返回错误原因。
第三：存储过程运行比较稳定，不会有太多的错误。只要一次成功，以后都会按这个程序运行。
第四：存储过程主要是在服务器上运行，减少对客户机的压力。
第五：存储过程可以包含程序流、逻辑以及对数据库的查询，
同时可以实体封装和隐藏了数据逻辑。
第六：存储过程可以在单个存储过程中执行一系列 SQL 语句。
第七：存储过程可以从自己的存储过程内引用其它存储过程，这可以简化一系列复杂语句。
其实存储过程还可以控制权限，比如一个表不直接允许用户直接访问，但要求允许用户访问和修改其中一个或多个字段，那就可以通过一个存储过程来实现并允许该用户使用该存储过程。
还有，如果多条SQL语句执行过程中，过程环节返回了数据作为后面环节的输入数据，如果直接通过SQL语句执行，势必导致大量的数据通过网络返回到客户机，并在客户机运算；如果封装在存储过程中，则将运算放在服务器进行，不但减少了客户机的压力，同时也减少了网络流量，提高了执行的效率。
篇9：编写安全的SQL Server扩展存储过程数据库教程
server|安全|存储过程
SQL Server 的扩展存储过程，其实就是一个普通的 Windows DLL,只不过按照某种规则实现了某些函数而已，编写安全的SQL Server扩展存储过程数据库教程
。近日在写一个扩展存储过程时，发现再写这类动态库时，还是有一些需要特别注意的地方。之所以会特别注意，是因为DLL运行于SQL Server的地址空间，而SQL Server到底是怎么进行线程调度的，却不是我们能了解的，即便了解也无法控制。
我们写动态库一般是自己用，即便给别人用，也很少像SQL Server这样，一个动态库很有可能加载多次，并且都是加载到一个进程的地址空间中。我们知道，当一个动态库加载到进程的地址空间时，DLL所有全局与局部变量初始化且仅初始化一次，以后再次调用 LoadLibrary函数时，仅仅增加其引用计数而已，那么很显然，假如有一全局 int ,初始化为0，调用一个函数另其自加，此时其值为1,然后再调用LoadLibray,并利用返回的句柄调用输出函数输出该值，虽然调用者觉得自己加载后立即输出，然后该值确实1而不是0。windows是进程独立的，而在线程方面，假如不注意，上面的情况很可能会程序员带来麻烦。
介绍一下我的扩展存储过程，该动态库导出了三个函数: Init,work,Final,Init读文件，存储信息于内存，work简单的只是向该内存检索信息,Final回收内存。如上所说，假如不考虑同一进程空间多次加载问题，两次调用Init将造成无谓的浪费，因为我第一次已经读进了内存，要是通过堆分配内存，还会造成内存泄露。
我使用的引用计数解决的该问题，代码很短，直接贴上来：
#include “stdafx.h”
#include
using namespace std;
extern “C” {
RETCODE __declspec(dllexport) xp_part_init(SRV_PROC *srvproc);
RETCODE __declspec(dllexport) xp_part_process(SRV_PROC *srvproc);
RETCODE __declspec(dllexport) xp_part_finalize(SRV_PROC *srvproc);
}
#define XP_NOERROR 0
#define XP_ERROR 1
HINSTANCE hInst = NULL;
int nRef = 0;
void printError (SRV_PROC *pSrvProc, CHAR* szErrorMsg);
ULONG __GetXpVersion(){ return ODS_VERSION;}
SRVRETCODE xp_part_init(SRV_PROC* pSrvProc){
typedef bool (*Func)();
if(nRef == 0){
hInst = ::LoadLibrary(“part.dll”);
if(hInst == NULL){
printError(pSrvProc,“不能加载part.dll”);
return XP_ERROR;
}
Func theFunc = (Func)::GetProcAddress(hInst,“Init”);
if(!theFunc()){
::FreeLibrary(hInst);
printError(pSrvProc,“不能获得分类号与专辑的对应表”);
return XP_ERROR;
}
}
++ nRef;
return (XP_NOERROR);
}
SRVRETCODE xp_part_process(SRV_PROC* pSrvProc){
typedef bool (*Func)(char*);
if(nRef == 0){
printError(pSrvProc,“函数尚未初始化,请首先调用xp_part_init”);
return XP_ERROR;
}
Func theFunc = (Func)::GetProcAddress(hInst,“Get”);
BYTE bType;
ULONG cbMaxLen,cbActualLen;
BOOL fNull;
char szInput[256] = {0};
if (srv_paraminfo(pSrvProc, 1, &bType, (ULONG*)&cbMaxLen, (ULONG*)&cbActualLen, (BYTE*)szInput, &fNull) == FAIL){
printError(pSrvProc,“srv_paraminfo 返回 FAIL”);
return XP_ERROR;
}
szInput[cbActualLen] = 0;
string strInput = szInput;
string strOutput = “;”;
int cur,old = 0;
while(string::npos != (cur = strInput.find(’;’,old)) ){
strncpy(szInput,strInput.c_str() + old,cur - old);
szInput[cur - old] = 0;
ld = cur + 1;
theFunc(szInput);
if(string::npos ==strOutput.find((string)“;” + szInput))
strOutput += szInput;
}
strcpy(szInput,strOutput.c_str());
if (FAIL == srv_paramsetoutput(pSrvProc, 1, (BYTE*)(szInput + 1), strlen(szInput) - 1,FALSE)){
printError (pSrvProc, “srv_paramsetoutput 调用失败”);
return XP_ERROR;
}
srv_senddone(pSrvProc, (SRV_DONE_COUNT | SRV_DONE_MORE), 0, 0);
return XP_NOERROR;
}
SRVRETCODE xp_part_finalize(SRV_PROC* pSrvProc){
typedef void (*Func)();
if(nRef == 0)
return XP_NOERROR;
Func theFunc = (Func)::GetProcAddress(hInst,“Fin”);
if((--nRef) == 0){
theFunc();
::FreeLibrary(hInst);
hInst = NULL;
}
return (XP_NOERROR);
}
我想虽然看上去不是很高明，然而问题应该是解决了的，
还有一点说明，为什么不使用Tls，老实说，我考虑过使用的，因为其实代码是有一点问题的，假如一个用户调用xp_part_init,然后另一个用户也调用xp_part_init，注意我们的存储过程可是服务器端的，然后第一个用户调用xp_part_finalize，那么会怎样，他仍然可以正常使用xp_part_process，这倒无所谓，然而第一个用户调用两次xp_part_finalize，就能够影响第二个用户了，他的xp_part_process将返回错误。
使用Tls 似乎可以解决这问题，例如再添加一个tls_index变量，调用 TlsSetValue保存用户私人数据，TlsGetValue检索私人数据，当xp_part_init时，假如该私人数据为0，执行正常的初始化过程，（即上面的xp_part_init）执行成功后存储私人数据为1，假如是1，直接返回，xp_part_finalize时，假如私人数据为1，则执行正常的xp_part_finalize，然后设私人数据为0，假如是0，直接返回。
好像想法还是不错的，这样隔离了多个用户，安全性似乎提高了不少，然而事实是不可行的。因为Tls保存的并不是私人数据，而是线程本地变量，我们不能保证一个用户的多次操作都是用同一个线程执行的，这个由SQL Server自己控制，事实上我在查询分析器里多次执行的结果显示，SQL Server内部似乎使用了一个线程池。既然如此，那这种想法也只能作罢。
【在PL/SQL 开发中调试存储过程和函数的一般性方法数据库教程】相关文章：
3.浅析Oracle和SqlServer存储过程的调试、出错处理数据库
文档为doc格式
