存儲過程trim
1. 用sql創建存儲過程的題目（SQL SERVER2000下）
關於delphi調用存儲過程，用sql
server自帶的「創建存儲過程向導」給表login建立一個更新存儲過程。
懸賞分：100
|
離問題結束還有
12
天
18
小時
|
提問者：風風我依
|
檢舉
存儲過程代碼如下：已知表login有passname和password兩個欄位。
create
procere
[update_login_1]
(@passname_1
[varchar],
@passname_2
[varchar](50),
@password_3
[varchar](50))
as
update
[wqzx].[dbo].[login]
set
[passname]
=
@passname_2,
[password]
=
@password_3
where
(
[passname]
=
@passname_1)
go
-------------------------------------------------------------------------------------
在delp調用該存儲過程，要求只該改欄位中的password，即建一個文本框將裡面內容替換一個記錄中的password，而保持passname不變，簡單講就是修改一個用戶名的密碼。怎麼寫代碼？問題補充：
"強唐華"我試過這樣寫存儲過程了，沒用的，如果可以，那在delphi中怎麼寫代碼調用這個存儲過程？
強調下：這是sql自帶建立的存儲過程，就是由「創建存儲過程向導」生成，我覺得不會錯的，問題是怎麼在delphi中調用該存儲過程，代碼呢？
我建立了一個插入的存儲過程，代碼如下：
with
storedproc1
do
begin
parambyname('@passname_1').asstring:=edit1.text;
parambyname('@password_2').asstring:=edit2.text;
execproc;
if
params[0].asinteger=0
then
messagedlg('添加新用戶名成功！',mtinformation,[mbok],0)
else
messagedlg('添加用戶失敗！',mterror,[mbok],0);
end;
這段代碼是可以成功插入一個記錄的。現在是想修改某個記錄。
回答
共1條
create
procere
[dbo].[update_login]
(@name
varchar(50),
@password
[varchar](50))
as
update
useres
set
password=@password
where
name=
@name
這么簡單的存儲過程，都不會用？
sqlconnection
conn
=
new
sqlconnection("data
source=tq-pc;initial
catalog=tq;user
id=sa;password=123");
sqlcommand
cmd
=
new
sqlcommand("update_login",
conn);
cmd.commandtype
=
commandtype.storedprocere;
cmd.parameters.addwithvalue("@name",
textbox3.text.trim());
cmd.parameters.addwithvalue("@password",
textbox4.text.trim());
conn.open();
int
i
=
cmd.executenonquery();
if
(i
>
0)
response.write("密碼修改成功！");
else
response.write("密碼修改失敗！");
2. oracle 怎麼給存儲過程建立同義詞
一般操作是：
1.create or replace synonym a1
FOR
a2@testdb;
2.grant connect to someuser
grant dba to someuser
grant resource to someuser
3.grant all on
表 to someuser
這樣存儲過程就可操作同義詞，在存儲過程中訪問公共同義詞，必須直接對用戶授權，而不能通過角色授權。
3. .net存儲過程
將這些參數封裝成一個實體類,傳遞的時候就傳遞這個實體類就可以了,這樣也不容易出錯,因為你打個點就會出提示的!!
例如:
/// <summary>
/// 用戶管理
/// </summary>
[Serializable]
public class User : AbstractReportPk
{
#region Fields, Properties
private Int32 userIDField;
/// <summary>
/// 用戶ID
/// </summary>
public Int32 UserID
{
get
{
return this.userIDField;
}
set
{
this.userIDField = value;
}
}
private String loginNameField;
/// <summary>
/// 登陸名
/// </summary>
public String LoginName
{
get
{
return this.loginNameField;
}
set
{
this.loginNameField = value;
}
}
}
這樣在業務邏輯層和數據訪問層就可以傳遞實體類了
4. 在pb8.0中怎樣調用oracle存儲過程和函數
oracle寫好的函數可以在SQL語句中直接調用，比如：你寫了一個TRIM函數，然後PB端的SQL中可以這樣調用：select col1,... from tab_name where trim(col1) ='';
進行存儲過程調用:
PowerBuilder requires a declarative statement to identify the database stored procere that is being used and a logical name that can be referenced in subsequent SQL statements. The general syntax for declaring a procere is:
DECLARE logical_procere_name PROCEDURE FOR
Oracle_procere_name(:InParam1,:InParam2, ...)
{USING transaction_object};
5. 存儲過程中 怎麼判斷char類型的結果是不是某個字元
首先，第一步，因為是char類型，你要去掉因為補位而產生的空格
selecttrim(mkt_gene_value)--這用了trim函數
intov_gene_valuefromrouter.rule_mkt_gene_definewheremkt_gene_idin
(selectmkt_gene_idfromrouter.rule_mkt_genewherecode_mkt=v_code_mkt);
--然後判斷
ifv_gene_value='YYS105'
then...
意思懂了吧？
6. VB調用存儲過程得幾種方法
???? [jifen] [decimal](18, 2) NULL????) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]????GO????????ALTER TABLE [dbo].[users] WITH NOCHECK ADD???? CONSTRAINT [PK_users] PRIMARY KEY CLUSTERED???? (???? [id]???? ) ON [PRIMARY]????GO????????????????存儲過程select_users????CREATE PROCEDURE select_users @regname char(20), @numrows int OUTPUT????AS???? Select * from users???????? SELECT @numrows = @@ROWCOUNT???????? if @numrows = 0???? return 0???? else return 1????GO????????存儲過程insert_users????CREATE PROCEDURE insert_users @truename char(20), @regname char(20),@pwd char(20),@sex char(20),@email char(20),@jifen decimal(19,2)????AS????insert into users(truename,regname,pwd,sex,email,jifen) values(@truename,@regname,@pwd,@sex,@email,@jifen)????GO????????????在VB環境中，添加DataGrid控制項，4個按鈕，6個文本框????代碼簡單易懂。 ????????『引用microsoft active data object 2.X library????Option Explicit????Dim mConn As ADODB.Connection????Dim rs1 As ADODB.Recordset????Dim rs2 As ADODB.Recordset????Dim rs3 As ADODB.Recordset????Dim rs4 As ADODB.Recordset????????Dim cmd As ADODB.Command????Dim param As ADODB.Parameter????????'這里用第一種方法使用存儲過程添加數據????Private Sub Command1_Click()???????? Set cmd = New ADODB.Command???? Set rs1 = New ADODB.Recordset???? cmd.ActiveConnection = mConn???? cmd.CommandText = "insert_users"???? cmd.CommandType = adCmdStoredProc???????? Set param = cmd.CreateParameter("truename", adChar, adParamInput, 20, Trim(txttruename.Text))???? cmd.Parameters.Append param???? Set param = cmd.CreateParameter("regname", adChar, adParamInput, 20, Trim(txtregname.Text))???? cmd.Parameters.Append param???? Set param = cmd.CreateParameter("pwd", adChar, adParamInput, 20, Trim(txtpwd.Text))???? cmd.Parameters.Append param???? Set param = cmd.CreateParameter("sex", adChar, adParamInput, 20, Trim(txtsex.Text))???? cmd.Parameters.Append param???? Set param = cmd.CreateParameter("email", adChar, adParamInput, 20, Trim(txtemail.Text))????cmd.Parameters.Append param????『下面的類型需要注意，如果不使用adSingle,會發生一個精度無效的錯誤???? Set param = cmd.CreateParameter("jifen", adSingle, adParamInput, 50, Val(txtjifen.Text))???? cmd.Parameters.Append param???? Set rs1 = cmd.Execute???????? Set cmd = Nothing???? Set rs1 = Nothing????????End Sub????????'這里用第二種方法使用存儲過程添加數據????Private Sub Command2_Click()???? Set rs2 = New ADODB.Recordset???? Set cmd = New ADODB.Command???? cmd.ActiveConnection = mConn???? cmd.CommandText = "insert_users"???? cmd.CommandType = adCmdStoredProc???????? cmd.Parameters("@truename") = Trim(txttruename.Text)???? cmd.Parameters("@regname") = Trim(txtregname.Text)???? cmd.Parameters("@pwd") = Trim(txtpwd.Text)???? cmd.Parameters("@sex") = Trim(txtsex.Text)???? cmd.Parameters("@email") = Trim(txtemail.Text)???? cmd.Parameters("@jifen") = Val(txtjifen.Text)???????? Set rs2 = cmd.Execute???????? Set cmd = Nothing???? Set rs1 = Nothing????End Sub????????'這里用第三種方法使用連接對象來插入數據????Private Sub Command4_Click()???? Dim strsql As String???? strsql = "insert_users '" & Trim(txttruename.Text) & "','" & Trim(txtregname.Text) & "','" & Trim(txtpwd.Text) & "','" & Trim(txtsex.Text) & "','" & Trim(txtemail.Text) & "','" & Val(txtjifen.Text) & "'"???? Set rs3 = New ADODB.Recordset???? Set rs3 = mConn.Execute(strsql)???????? Set rs3 = Nothing????End Sub????????'利用存儲過程顯示數據????『要處理多種參數，輸入參數，輸出參數以及一個直接返回值???? Private Sub Command3_Click()???? Set rs4 = New ADODB.Recordset???? Set cmd = New ADODB.Command???? cmd.ActiveConnection = mConn???? cmd.CommandText = "select_users"???? cmd.CommandType = adCmdStoredProc???????? '返回值???? Set param = cmd.CreateParameter("RetVal", adInteger, adParamReturnValue, 4)???? cmd.Parameters.Append param???? '輸入參數???? Set param = cmd.CreateParameter("regname", adChar, adParamInput, 20, Trim(txtregname.Text))???? cmd.Parameters.Append param???? '輸出參數???? Set param = cmd.CreateParameter("numrows", adInteger, adParamOutput)???? cmd.Parameters.Append param???????? Set rs4 = cmd.Execute()???? If cmd.Parameters("RetVal").Value = 1 Then???? MsgBox cmd.Parameters("numrows").Value???? Else???? MsgBox "沒有記錄"???? End If???????? MsgBox rs4.RecordCount???? Set DataGrid1.DataSource = rs4???? DataGrid1.「refresh」????????End Sub????????'連接資料庫????Private Sub Form_Load()???? Set mConn = New Connection???? mConn.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=Test;Data Source=yang"???? mConn.CursorLocation = adUseClient '設置為客戶端???? mConn.Open????End Sub????'關閉數據連接????Private Sub Form_Unload(Cancel As Integer)???? mConn.Close???? Set mConn = Nothing????End Sub????
7. oracle存儲過程問題（初學者簡單問題）
=====沒有declare
create or replace procere sp_crt_tbl_from_dic(p_src_tbl varchar2,
p_tgt_tbl_name varchar2,
p_prt_flg varchar2,
p_tx_date varchar2) is
v_col varchar2(500) := '';
v_col_data_type varchar2(100) := '';
v_sql varchar2(25000) := '';
v_col_list varchar2(20000) := ' ';
v_prt_sql varchar2(2000) := '';
v_grant_sql varchar2(2000) := '';
v_crt_syn_sql varchar2(2000) := '';
v_drop_tbl_sql varchar2(2000) := '';
i_tbl_cnt number(1, 0) := 0;
v_step_no varchar2(3) := '';
begin
--step 1
v_step_no := '1';
select count(1) into i_tbl_cnt from tabs where table_name = p_src_tbl;
if (i_tbl_cnt = 0) then
sp_log('sp_crt_tbl', v_step_no, '3', p_tx_date, p_src_tbl || '不存在');
return;
end if;
--step 2
v_step_no := '2';
select count(1)
into i_tbl_cnt
from tabs
where table_name = p_tgt_tbl_name;
if (i_tbl_cnt > 0) then
v_drop_tbl_sql := 'drop table ' || p_tgt_tbl_name;
execute immediate v_drop_tbl_sql;
end if;
v_step_no := '1';
sp_log('sp_crt_tbl_from_dic',v_step_no,'1',p_tx_date,p_tgt_tbl_name || '建表開始');
v_sql := 'create table ' || p_tgt_tbl_name || '(';
v_col := '';
v_col_data_type := '';
v_col_list := '';
for cur_col_name in (
SELECT T.COLUMN_NAME col_name,
CASE WHEN T.DATA_TYPE IN ('VARCHAR2', 'CHAR') THEN T.DATA_TYPE || '(' || T.DATA_LENGTH || ')'
WHEN T.DATA_TYPE IN ('NUMBER') THEN T.DATA_TYPE || '(' || T.DATA_PRECISION || ',' || T.DATA_SCALE || ')'
WHEN T.DATA_TYPE IN ('DATE') THEN T.DATA_TYPE ELSE '' END col_data_type
FROM COLS T
where T.TABLE_NAME = p_src_tbl
order by to_number(T.COLUMN_ID)) loop
v_col := trim(cur_col_name.col_name);
v_col_data_type := trim(cur_col_name.col_data_type);
v_col_list := trim(v_col_list) || trim(v_col) || ' ' || trim(v_col_data_type) || ',';
end loop;
v_sql := trim(v_sql) || trim(v_col_list) || ')';
v_sql := replace(v_sql, ',)', ')');
if (p_prt_flg = '1') then
v_prt_sql := ' partition by range (part_id) (partition prt_1 values less than(2),partition prt_2 values less than(3),partition prt_3 values less than(4),partition prt_4 values less than(5))';
v_sql := v_sql || v_prt_sql;
end if;
execute immediate v_sql;
sp_log('sp_crt_tbl_from_dic',v_step_no,'2',p_tx_date,p_tgt_tbl_name || '建表開始結束');
v_step_no := '2';
sp_log('sp_crt_tbl_from_dic', v_step_no,'1',p_tx_date,p_tgt_tbl_name || '賦許可權開始');
v_grant_sql := 'grant select,insert,update,delete on ' || p_tgt_tbl_name ||
' to iclientoopr,iclientomoia';
if (v_grant_sql is not null) then
execute immediate v_grant_sql;
end if;
--- 給OPERQRY賦許可權
v_grant_sql := 'grant select on ' || p_tgt_tbl_name || ' to OPERQRY';
if (v_grant_sql is not null) then
execute immediate v_grant_sql;
end if;
sp_log('sp_crt_tbl_from_dic',v_step_no,'2',p_tx_date,p_tgt_tbl_name || '賦許可權結束');
--創建日標同義詞
--step 4
v_step_no := '3';
v_crt_syn_sql := 'create or replace public synonym ' || p_tgt_tbl_name ||
' for iclientodata.' || p_tgt_tbl_name;
if (v_crt_syn_sql is not null) then
execute immediate v_crt_syn_sql;
end if;
sp_log('v_crt_syn_sql',v_step_no,'2',p_tx_date,p_tgt_tbl_name || '創建結束');
exception
when others then
sp_log('sp_crt_tbl_from_dic',v_step_no,'3',p_tx_date,p_tgt_tbl_name || '創建異常:' || SQLERRM);
end;
8. delphi 中如何調用sql 存儲過程
使用TADOStoredProc組件,可以，給你舉個例子好了
with ADOStoredProc1 do
begin
Close;
Parameters.Clear;
ProcereName:='SaveDate_dbzsm'; //存儲過程名稱
Parameters.CreateParameter('v_wlid', ftString, pdInput, 20, fgUnassigned);//傳入參數（數字代表長度）
Parameters.CreateParameter('v_ddid', ftString, pdInput, 20,fgUnassigned); //傳入參數
Parameters.CreateParameter('v_tm', ftString, pdInput, 500,fgUnassigned); //傳入參數
Parameters.CreateParameter('v_returnstr', ftString, pdoutput, 30,fgUnassigned); //返回值
//為參數賦值
Parameters[0].Value :=trim(edit1.text);
Parameters[1].Value := trim(edit2.text);
Parameters[2].Value := trim(edit2.text);
Parameters[3].Value := ' ';
ExecProc;
reuturn:= Trim(Parameters.ParamByName('v_returnstr').Value);
end;
reuturn就是返回值，你可以放到stringgrid 裡面。
也可以用adoquery 連接存儲過程。
with ADOQuery1 do
begin
Close;
SQL.Clear;
SQL.Add('exec SaveDate_dbzsm :wlid,:ddid,:tm,:returnstr output');
Parameters.ParamByName('wlid').Value :=trim(edit1.text);
Parameters.ParamByName('ddid').Value :=trim(edit2.text);
Parameters.ParamByName('tm').Value := trim(edit3.text);
Parameters.ParamByName('returnstr').Value := ' ';
Open;
reuturn:= Parameters.ParamByName('returnstr').Value;
end;
http://..com/question/316727588.html
9. 怎麼才oracle中查看存儲過程
可用語句查看或用第三方工具查看。
一、語句查看方法：
1、執行語句：
SELECTtext
FROMuser_source
WHERENAME='ProcereName'
ORDERBYline;--其中ProcereName為存儲過程名字，需要全部用大寫英文。
2、如要查P_TEST的存儲過程：
SELECTtext
FROMuser_source
WHERENAME='P_TEST'
ORDERBYline;
3、內容如下：
10. 字元串傳入存儲過程會trim嗎
變數直接寫，要連接字元串的話，用||這個符號，後面的字元串用單引號引！例如變數V_str字元串'selectempfromtablewhere'連接後為：------------------------------------------------'selectempfromtablewhere'||V_str------------------------------------------------希望可以幫助到你！