存储过程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------------------------------------------------希望可以帮助到你！