不帶參數的存儲函數實現查詢

㈠ asp.net 調用存儲過程

兩種不同的存儲過程調用方法

為了突出新方法的優點，首先介紹一下在.NET中調用存儲過程的「官方」方法。另外，本文的所有示常式序均工作於sqlServer資料庫上，其它情況類似，以後不再一一說明。本文所有例子均採用C#語言。

要在應用程序中訪問資料庫，一般性的步驟是：首先聲明一個資料庫連接SqlConnection，然後聲明一個資料庫命令SqlCommand，用來執行SQL語句和存儲過程。有了這兩個對象後，就可以根據自己的需要採用不同的執行方式達到目的。需要補充的是，不要忘記在頁面上添加如下的引用語句：using System.Data.SqlClient。

就執行存儲過程來說，如果執行的是第一類存儲過程，那麼就要用一個DataAdapter將結果填充到一個DataSet中，然後就可以使用數據網格控制項將結果呈現在頁面上了；如果執行的是第二枝巧橋和第三種存儲過程，則不需要此過程，只需要根據特定的返回判定操作是否成功完成即可。

(1)執行一個沒有參數的寬棗存儲過程的代碼如下：

SqlConnection conn=new SqlConnection(「connectionString」);
SqlDataAdapter da = new SqlDataAdapter();
da.selectCommand = new SqlCommand();
da.selectCommand.Connection = conn;
da.selectCommand.CommandText = "NameOfProcere";
da.selectCommand.CommandType = CommandType.StoredProcere;

然後只要選擇適當的方式執行此處過程，用於不同的目的即可。

(2)執行一個有參數的存儲過程的代碼如下（我們可以將調用存儲過程的函數猛猛聲明為ExeProcere(string inputdate)）：

SqlConnection conn=new SqlConnection(「connectionString」);
SqlDataAdapter da = new SqlDataAdapter();
da.selectCommand = new SqlCommand();
da.selectCommand.Connection = conn;
da.selectCommand.CommandText = "NameOfProcere";
da.selectCommand.CommandType = CommandType.StoredProcere;
（以上代碼相同，以下為要添加的代碼）
param = new SqlParameter("@ParameterName", SqlDbType.DateTime);
param.Direction = ParameterDirection.Input;
param.Value = Convert.ToDateTime(inputdate);
da.selectCommand.Parameters.Add(param);

這樣就添加了一個輸入參數。若需要添加輸出參數：

param = new SqlParameter("@ParameterName", SqlDbType.DateTime);
param.Direction = ParameterDirection.Output;
param.Value = Convert.ToDateTime(inputdate);
da.selectCommand.Parameters.Add(param);

若要獲得參儲過程的返回值：

param = new SqlParameter("@ParameterName", SqlDbType.DateTime);
param.Direction = ParameterDirection.ReturnValue;
param.Value = Convert.ToDateTime(inputdate);
da.selectCommand.Parameters.Add(param);

從上面的代碼我們可以看出，當存儲過程比較多或者存儲過程的參數比較多時，這種方法會大大影響開發的速度；另外一方面，如果項目比較大，那麼這些用於資料庫邏輯的函數在以後的維護中也是一個很大的負擔。那麼，有沒有一種改進的方法可以解決這個問題呢？想到在執行沒有參數的存儲過程時只需要傳入一個存儲過程的名字就可以調用相應的存儲過程，而且在SqlServer資料庫中我們可以直接在查詢分析器中敲入「存儲過程名（參數列表）」樣的字元串就可以執行存儲過程，那麼，是否可以把這種思想應用到應用程序中呢？

於是在編譯器中鍵入相應代碼。這些代碼是在調用不帶參數的存儲過程的代碼的基礎上改的。具體代碼如下：

SqlConnection conn=new SqlConnection(「connectionString」);
SqlDataAdapter da = new SqlDataAdapter();
da.selectCommand = new SqlCommand();
da.selectCommand.Connection = conn;
da.selectCommand.CommandText = "NameOfProcere（'para1','para2',para3）";
da.selectCommand.CommandType = CommandType.StoredProcere;

為了使代碼更具有代表性，要調用的存儲過程的第一個和第二個參數都為字元串類型，第三個參數為整型。執行以後發現，完全可以達到預期的效果！

兩種調用方法的比較

通過比較我們可以看到，第二種方法具有一個很明顯的優點，那就是可以提高開發速度，節省開發時間，而且代碼容易維護，在一定程度上也減少了系統大小。但是，由於對存儲過程參數的處理比較籠統，如果要獲取輸出參數或者得到存儲過程的返回值，這種方法就不能滿足需要了。雖然如此，但是，這種方法畢竟可以讓開發人員少些很大一部分的代碼。如果不需要獲取輸出參數和返回值，那麼幾乎可以做到「一勞永逸」。因此在實際的程序開發中，這種方法還是具有一定的實用價值的。

用ASP.NET與SQL SERVER可是緣份最好了，稍大的程序一般第一先考慮的是SQL SERVER，只是一些很考慮經濟的才使用ACCESS等了。用SQL SERVER，為了使資料庫的效率更好，一般都會才取存儲過程，因存儲過程執行速度快，並且可以實現一些高級的查詢等功能。比如傳入一些數據參數，但執行的SQL過程可能不同等。

下面就來個例子，建立一新的角色，要求角色的名字不能重復，以下是一存儲過程。

CREATE PROCEDURE sp_AccountRole_Create@CategoryID int,@RoleName nvarchar(10),@Description nvarchar(50),@RoleID int outputAS DECLARE @Count int -- 查找是否有相同名稱的記錄 SELECT @Count = Count(RoleID) FROM Account_Role WHERE RoleName = @RoleName IF @Count = 0 INSERT INTO Account_Role (CategoryID, RoleName, Description) valueS (@CategoryID, @RoleName, @Description) SET @RoleID = @@IDENTITY RETURN 1GO 執行存儲過程的C#過程：

SqlConnection DbConnection = new SqlConnection(mConnectionString);SqlCommand command = new SqlCommand( "sp_AccountRole_Create", DbConnection );DbConnection.Open(connectString);// 廢置SqlCommand的屬性為存儲過程command.CommandType = CommandType.StoredProcere;command.Parameters.Add("@CategoryID", SqlDbType.Int, 4);command.Parameters.Add("@RoleName", SqlDbType.NVarChar, 10);command.Parameters.Add("@Description", SqlDbType.NVarChar, 50);command.Parameters.Add("@RoleID", SqlDbType.Int, 4);// 返回值command.Parameters.Add("Returnvalue", SqlDbType.Int, 4, // Size ParameterDirection.Returnvalue, false, // is nullable 0, // byte precision 0, // byte scale string.Empty, DataRowVersion.Default, null );command.parameters["@CategoryID"].value = permission.CategoryID;command.parameters["@RoleName"].value = permission.PermissionName;command.parameters["@Description"].value = permission.Description;// 可以返回新的ID值command.parameters["@RoleID"].Direction = ParameterDirection.Output;int rowsAffected = command.ExecuteNonQuery();int result = command.parameters["Returnvalue"].value;int newID = command.parameters["@RoleID"].value;

㈡ PL/SQL的執行操作

過程:執行特定操作

函數:用於返回特定數據 語法:create [orreplace] procere procere_name(argument1 [model]datatype1,argment2 [mode2],...)
is [as]
pl/sql block;
1.建立過程:不帶任何參數
create or replaceprocecre out_time
is
begin
dbms_output.put_line(systimestamp);
end;
2.調用過程
set serveroutput on
exec out_time
set serveroutput on
call out_time();
3.建立過程:帶有IN參數
CREATE OR REPLACE PROCEDURE add_employee(eno NUMBER,NAME VARCHAR2,sal NUMBER,job VARCHAR2 DEFAULT 'clerk',dno NUMBER) ISe_integrity EXCEPTION;PRAGMA EXCEPTION_INIT(e_integrity, -2291);BEGININSERT intoimp(empno,ename,sal,job,deptno)VALUES(eno,NAME,sal,job,dno);EXCEPTIONWHEN p_val_on_index THENraise_application_error(-20000, '雇員號不能重復');WHEN e_integrity THENraise_application_error(-20001, '部門不存在');END add_employee;
4.建立過程:帶有OUT參數
create or replaceprocere qry_employee
(eno number,name outvarchar2,salary out number)
is
begin
selectename,sal into name,salary from emp where empno=eno;
exception
whenno_date_found then
raise_application_error(-20000,'該雇員不存在');
end;
當在應用程序中調用該過程時,必須要定義變數接受輸出參數的數據
sql>var name varchar2(10)
var salary number
exec qry_employee(7788,:name,:salary)
print name salary
5.建立過程:帶有INOUT參數(輸入輸出參數)
create or replaceprocere compute
(num1 in outnumber,num2 in out number)
is
v1number;
v2number;
begin
v1:num1/num2;
v2:mod(num1,num2);
num1:=v1;
num2:=v2;
end;
sql>var n1 number
var n2 number
exec :n1:=100
exec :n2:=30
exec ecmpute(:n1,:n2)
print n1 n2
6.為參數傳遞變數和數據
位置傳遞,名稱傳遞,組合傳遞三種
1.位置傳遞:在調用子程序時按照參數定義的順序為參數指定相應的變數或數值
exec add_dept(40,'sales','new york');
exec add_dept(10);
2.名稱傳遞:在調用子程序時指定參數名,並使用關聯符號=>為其提供相應的數值或變數
execadd_dept(dname=>'sales',dno=>50);
exec add_dept(dno=>30);
3.組合傳遞:同時使用位置傳遞和名稱傳遞
exec add_dept(50,loc=>'new york');
execadd_dept(60,dname=>'sales',loc=>'newyork');
7.查看過程原代碼
oracle會將過程名,源代碼以及其執行代碼存放到數據字典中.執行時直接按照其執行代碼執行
可查詢數據字典(user_source)
select textfrom user_source where name='add_dept';
刪除過程
dropprocere add_dept; 用於返回特定函數
語法:create [orreplace] function function_name
(argument1 [mode1] datatype1,
argument2 [mode2] datatype2,
.....)
returndatatype --函數頭部必須要帶有RETURN子句,至少要包含一條RETURN語句
is|as pl/sql block;
1.建立函數:不帶任何參數
create or replacefunction get_user
return varchar2
is
v_uservarchar2(100);
begin
selectusername into v_user from user_users;
returnv_user;
end;
2.使用變數接受函數返回值
sql>var v1 varchar2(100)
exec :v1:=get_user
print v1
在SQL語句中直接調用函數
selectget_user from d l;
使用DBMS_OUTPUT調用函數
setserveroutput on
execdbms_output.put_line('當前資料庫用戶:'||ger_user)
3.建立函數:帶有IN參數
create orreplace function get_sal(name in varchar2)
returnnumber
as
v_sal emp.sal%type;
begin
select sal into v_sal from emp where upper(ename)=upper(name);
return v_sal;
exception
when no_data_found then
raise_application_error(-20000,'該雇員不存在');
end;
4.建立函數:帶有out參數
create or replacefunction get_info(name varchar2,title out varchar2)
return varchar2
as
deptnamedept.dname%type;
begin
selecta.job,b.dname into title,deptname from emp a,dept b anda.deptno=b.deptno
anpper(a.ename)=upper(name);
returndeptname
exception
whenno_data_found then
raise_application_error(-20000,'該雇員不存在');
end;
sql>var job varchar2(20)
var dname varchar2(20)
exec :dname:=get_info('scott',:job)
print danme job
5.建立函數:帶有INOUT參數
create or replacefunction result(num1 number,num2 in out number)
return number
as
v_resultnumber(6);
v_remaindernumber;
begin
v_result:=num1/num2;
v_remainder:=mod(num1,num2);
num2:=v_remainder;
returnv_result;
exception
whenzero_divide then
raise_application_error(-20000,'不能除0');
end;
sql>var result1 number
var result2 number
exec :result2:=30
exec :result1:=result(100,:result2)
print result result2
6.函數調用限制
SQL語句中只能調用存儲函數(伺服器端),而不能調用客戶端的函數
SQL只能調用帶有輸入參數,不能帶有輸出,輸入輸出函數
SQL不能使用PL/SQL的特有數據類型(boolean,table,record等)
SQL語句中調用的函數不能包含INSERT,UPDATE和DELETE語句
7.查看函數源代碼
oracle會將函數名及其源代碼信息存放到數據字典中user_source
set pagesize 40
select text fromuser_source where name='result';
8.刪除函數
drop functionresult; 1.列出當前用戶的子程序
數據字典視圖USER_OBJECTS用於顯示當前用戶所包含的所有對象.(表,視圖,索引,過程,函數,包)
sql>col object_name format a20
select object_name,created,status from user_objects whereobject_type in ('procere','function')
2.列出子程序源代碼
select text fromuser_source where name='raise_salsry';
3.列出子程序編譯錯誤
使用SHOWERRORS命令確定錯誤原因和位置
show errorsprocere raise_salary
使用數據字典視圖USER_ERRORS確定錯誤原因和位置
col text formata50
selectline||'/'||position as line/col,text error from user_errors wherename='raise_salary';
4.列出對象依賴關系
使用數據字典視圖USER_DEPENDENCIES確定直接依賴關系
select name,typefrom user_dependencies where referenced_name='emp';
使用工具視圖DEPTREE和IDEPTREE確定直接依賴和間接依賴關系
先運行SQL腳本UTLDTREE.SQL來建立這兩個視圖和過程DEPTREE_FILL,然後調用DEPTREE_FILL填充這兩個視圖
sql>@%oracle_home% dbmsadminutldtree
exec deptree_fill('TABLE','scott','emp')
執行後會將直接或間接依賴於SCOTT.EMP表的所有對象填充到視圖DEPTREE和IDEPTREE中.
select nested_level,name,type from deptree;
select * from ideptree
5.重新編譯子程序
當修改了被引用對象的結構時,就會將相關依賴對象轉變為無效(INVALID)狀態。
alter table emp addremark varchar2(10);
selectobject_name,object_type from user_objects wherestatus='invalid';
為了避免子程序的運行錯誤,應該重新編譯這些存儲對象
alter procereadd_employee compile;
alter view dept10compile;
alter functionget_info compile; 包用於邏輯組合相關的PL/SQL類型,項和子程序,由包規范和包體組成
1.建立包規范:包規范是包與應用程序之間的介面,用於定義包的公用組件,包括常量,變數,游標,過程,函數等
create [or replace]package package_name
is|as
p lic type and item declarations
s program specificationsend package_name;
create or replacepackage emp_package is
g_deptnonumber(3):=30;
procereadd_employee(eno number,name varchar2,salary number,dno numberdefault g_deptno);
procerefire_employee(eno number);
functionget_sal(eno number) return number;
end emp_package;
2.建立包體:用於實現包規范所定義的過程和函數
create [or replace]package body package_name
is|as
private type and item declarations
s program bodies
endpackage_name;
create or repalce package body emp_package is
functionvalidate_deptno(v_deptno number)
return boolean
is
v_temp int;
begin
select 1 into v_temp from dept where deptno=v_deptno;
return tr;
exception
when no_date_found then
return false;
end;
procere add_employee(eno number,name varchar2,salary number,dnonumber default g_deptno)
is
begin
if validate_deptno(dno) then
insert into emp(empno,ename,sal,deptno)vals(eno,name,salsry,dno);
else
raise_application_error(-20010,'不存在該部門');
end if;
exception
when p_val_on_index then
raise_application_error(-20012,'該雇員已存在');
end;
procere fire_employee(eno number) is
begin
delete from emp where empno=eno;
if sql%notfound then
raise_application_error(-20012,'該雇員不存在');
end if;
end;
functionget_sal(eno number) return number
is
v_sal emp.sal%type;
begin
select sal into v_sal from emp where empno=eno;
return v_sal;
exception
whenno_data_found then
raise_application_error(-20012,'該雇員不存在');
end;
end emp_package;
3.調用包組件
3.1在同一個包內調用包組件
create or replacepackage body emp_package is
procere add_employee(eno number,name va har2,salary number,dnonumber default g_deptno)
is
begin
ifvalidate_deptno(dno) then
insert into emp(empno,ename,sal,deptno)vals(eno,name,salary,dno);
else
raise_application_error(-20010,'該部門不存在')
end if;
exception
when p_val_on_index then
raise_application_error(-20011,'該雇員已存在')
end;
.........
3.2調用包公用變數
execemp_package.g_deptno:=20
3.3調用包公用過程
execemp_package.add_employee(1111,'mary',2000)
3.4調用包公用函數
var salarynumber
exec:salary:=emp_package.get_sal(7788)
print salary
3.5以其他用戶身份調用包公用組件
connsystem/manager
execscott.emp_package.add_employee(1115,'scott',1200)
execscott.emp_package.fire_employee(1115)
3.6調用遠程資料庫包的公用組件
execemp_package.add_employee@orasrv(1116,'scott',1200)
4.查看源代碼:存放在數據字典USER_SCOURCE中
select text fromuser_source where name='emp-package' and type='package';
5.刪除包
drop packageemp_package;
6.使用包重載
重載(overload)是指多個具有相同名稱的子程序
1.建立包規范
同名的過程和函數必須具有不同的輸入參數,同名函數返回值的數據類型必須完全相同
create or replacepackage overload is
functionget_sal(eno number) return number;
functionget_sal(name varchar2) return number;
procerefile_employee(eno number);
procerefile_employee(name varchar2);
end;
2.建立包體
必須要給不同的重載過程和重載函數提供不同的實現代碼
create or replacepackage body overload is
function get_sal(eno number) return number
is
v_sal emp.sal%type;
begin
select sal into v_sal from emp where empno=eno;
return v_sal;
exception
when no_data_found then
raise_application_error(-20020,'該雇員不存在');
end;
function get_sal(name varchar2) return number
is
v_sal emp.sal%type;
begin
select sal into v_sal from emp where upper(ename)=upper(name);
return v_sal;
exception
when no_data_found then
raise_application_error(-20020,'該雇員不存在');
end;
procere fire_employee(eno number) is
begin
delete from emp where empno=no;
if sql%notfound then
raise_application_error(-20020,'該雇員不存在');
end if;
end;
procerefire_employee(name varchar2) is
begin
delete from emp where upper(ename)=upper(name);
if sql%notfound then
raise_application_error(-20020,'該雇員不存在');
end if;
end;
end;
3.調用重載過程和重載函數
var sal1 number
var sal2 number
exec:sal1:=overload.get_sal('scott')
exec:sal2:=overload.get_sal(7685)
execoverload.fire_employee(7369)
execoverload.fire_employee('scott')
7.使用包構造過程
類似於高級語言中的構造函數和構造方法
1.建立包規范
包的構造過程用於初始化包的全局變數.
create or replacepackage emp_package is
minsalnumber(6,2);
maxsalnumber(6,2);
procereadd_employee(eno number,name varchar2,salary number,dnonumber);
procereupd_sal(eno number,salary number);
procereupd_sal(name varchar2,salary number);
end;
2.建立包體
包的構造過程沒有任何名稱,它是實現了包的其他過程後,以BEGIN開始,END結束的部分
create or replacepackage body emp_package is
procereadd_employee(eno number,name varchar2,salary number,dno number)
is
begin
if salarybetween minsal and maxsal then
insert into emp (empno,ename,sal,deptno)vals(eno,name,salary,dno);
else
raise_application_error(-20001,'工資不在范圍內');
end if;
exception
when p_val_on_index then
raise_application_error(-20002,'該雇員已經存在');
end;
procereupd_sal(eno number,salary number) is
begin
if salary between minsal and maxsal then
update emp set sal=salary where empno =eno;
if sql%notfound then
raise_application_error(-20003,'不存在雇員號');
end if;
else
raise_application_errpr(-20001,'工資不在范圍內');
end if;
end;
procere upd_sal(name varchar2,salary number) is
begin
if salary between minsal and maxsal then
update emp set sal=salary where upper(ename)=upper(name);
if sql%notfound then
raise_application_error(-20004,'不存在該雇員名');
end if;
else
raise_application_error(-20001,'工資不在范圍內');
end if;
end;
begin
selectmi(sal),max(sal) into minsal,maxsal from emp ;
end;
調用包公用組件:構造過程只調用一次
execemp_package.add_employee(1111,'mary',3000,20)
execemp_package.upd_sal('mary',2000)
8.使用純度級別
在SQL中引用包的公用函數，該公用函數不能包含DML語句(insert,update,delete),也不能讀寫遠程包的變數
為了對包的公用函數加以限制,在定義包規范時,可以使用純度級別(purity level)限制公用函數
語法:pragmarestrict_references (function_name,wnds[,wnps][,rnds][,rnps]);
wnds:用於限制函數不能修改資料庫數據(禁止DML)
wnps:用於限制函數不能修改包變數(不能給包變數賦值)
rnds:用於限制函數不能讀取資料庫數據(禁止SELECT操作)
rnps:用於限制函數不能讀取包變數(不能將包變數賦值給其他變數)
1.建立包規范
create or replacepackage purity is
minsalnumber(6,2);
maxsalnumber(6,2);
functionmax_sal return number;
functionmin_sal return number;
pragmarestrict_references(max_sal,wnps);--不能修改
pragmarestrict_references(min_sal,wnps);
end;
2.建立包體
create or replacepackage body purity is
function max_sal return number
is
begin
return maxsal;
end;
functionmin_sal return number
is
begin
return minsal;
end;
begin
select min(sal),max(sal) into minsal,maxsal from emp;
end;
3.調用包的公用函數
var minsal number
var maxsal number
exec :minsal:=purity.minsal()
exec :maxsal:=purity.maxsal()
print minsal maxsal PL/SQL處理異常不同於其他程序語言的錯誤管理方法，PL/SQL的異常處理機制與ADA很相似，有一個處理錯誤的全包含方法。
PL/SQL處理異常不同於其他程序語言的錯誤管理方法，PL/SQL的異常處理機制與ADA很相似，有一個處理錯誤的全包含方法。當發生錯誤時，程序無條件轉到異常處理部分，這就要求代碼要非常干凈並把錯誤處理部分和程序的其它部分分開。oracle允許聲明其他異常條件類型以擴展錯誤/異常處理。這種擴展使PL/SQL的異常處理非常靈活。
當一個運行時錯誤發生時，稱為一個異常被拋出。PL/SQL程序編譯時的錯誤不是能被處理得異常，只有在運行時的異常能被處理。在PL/SQL程序設計中異常的拋出和處理是非常重要的內容。

㈢ sqlserver如何創建一個帶有可不用參數存儲過程

看幫助，最全面了，有例子有代碼有用法。
你的這個，根據參數值是不是默認值來判斷就可以了。

USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'au_info2' AND type = 'P')
DROP PROCEDURE au_info2
GO
USE pubs
GO
CREATE PROCEDURE au_info2
@lastname varchar(30) = 'D%',
@firstname varchar(18) = '%'
AS
SELECT au_lname, au_fname, title, pub_name
FROM authors a INNER JOIN titleauthor ta
ON a.au_id = ta.au_id INNER JOIN titles t
ON t.title_id = ta.title_id INNER JOIN publishers p
ON t.pub_id = p.pub_id
WHERE au_fname LIKE @firstname
AND au_lname LIKE @lastname
GO

au_info2 存儲過程可以用多種組合執行。下面只列出了部分組合：
EXECUTE au_info2
-- Or
EXECUTE au_info2 'Wh%'
-- Or
EXECUTE au_info2 @firstname = 'A%'
-- Or
EXECUTE au_info2 '[CK]ars[OE]n'
-- Or
EXECUTE au_info2 'Hunter', 'Sheryl'
-- Or
EXECUTE au_info2 'H%', 'S%'