给每个变量一个默认值(null也可以),直接insert所有的字段
解决方案 »
- 关于oracle面试题
- 关于GATHER_STATS_JOB的schedule
- 怎么判断数据库表在过去的5分钟之内有没有插入新的数据?
- RMAN
- isqlplusctl start .操作系统找不到已输入的环境选项?
- SQL中多字段主键的两个表对比
- oracle 新手提高,高手指点一下! 关于通过ODBC导入数据!
- 自动共享内存管理
- SYSTEM我知道用在SQLPLUS上,可是SYS我怎么连接不上去,她用在哪里啊,我想建立个新的管理员帐号怎么建立啊?用OEM说什么包不能访问,但
- regexp_substr匹配字符串
- 能不能分析Share Pool为什么老是说不够呢
- 9i备份的数据能不能在8.05恢复?
p_aaa IN varchar2,
p_bbb IN varchar2,p_RowsInsert OUT Integer
)
AS
v_CursorID INTEGER;
v_InsertStr VARCHAR2(100);
v_ValuesStr varchar2(100);
BEGIN
-- 为处理打开光标
v_CursorID := DBMS_SQL.OPEN_CURSOR;
-- 确定 SQL 字串.
if p_aaa is not null then
begin
v_InsertStr := 'insert into test(AAA ';
v_ValuesStr := 'values(:AAA ';
if p_bbb is not null then
begin
v_InsertStr := v_InsertStr || ',BBB ';
v_valuesStr := v_valuesStr || ',:BBB ';
end if;
end;
elsif p_bbb is not null then
begin
v_InsertStr := 'insert into test(BBB ';
v_ValuesStr := 'values(:BBB ';
end if;
v_InsertStr := v_InsertStr || ')';
v_ValuesStr := v_valuesStr || ')';
v_InsertStr := v_InsertStr || v_ValuesStr;
-- 分析语句.
DBMS_SQL.PARSE(v_CursorID, v_InsertStr, DBMS_SQL.NATIVE);
--绑定变量
if p_aaa is not null then
begin
DBMS_SQL.BIND_VARIABLE(v_CursorID, ':AAA', p_aaa);
end if;
if p_bbb is not null then
begin
DBMS_SQL.BIND_VARIABLE(v_CursorID, ':BBB', p_bbb);
end if;
-- 执行语句.
p_RowsInsert := DBMS_SQL.EXECUTE(v_CursorID);
--关闭光标.
DBMS_SQL.CLOSE_CURSOR(v_CursorID); /*EXCEPTION
WHEN OTHERS THEN
--关闭光标,然后激活错误.
DBMS_SQL.CLOSE_CURSOR(v_CursorID);
RAISE;*/
END;
这是我写的存储过程,请哪位高手帮我看看写的对不对,我是oralce8I的,它老是报编译错误,不知道是哪里错了???????????????????????????
p_aaa IN VARCHAR2, p_bbb IN VARCHAR2, p_RowsInsert OUT NUMBER)
AS
v_CursorID INTEGER;
v_InsertStr VARCHAR2(100);
v_ValuesStr VARCHAR2(100);
BEGIN
p_RowsInsert := 0;
v_CursorID := DBMS_SQL.OPEN_CURSOR; v_InsertStr := 'INSERT INTO test (';
v_ValuesStr := ' VALUES ('; IF (p_aaa IS NOT NULL) THEN
v_InsertStr := v_InsertStr || 'AAA,';
v_ValuesStr := v_ValuesStr || ':AAA,';
END IF;
IF (p_bbb IS NOT NULL) THEN
v_InsertStr := v_InsertStr || 'BBB,';
v_ValuesStr := v_ValuesStr || ':BBB,';
END IF; v_InsertStr := SUBSTR(v_InsertStr,1,LENGTH(v_InsertStr)-1);
v_ValuesStr := SUBSTR(v_ValuesStr,1,LENGTH(v_ValuesStr)-1);
v_InsertStr := v_InsertStr || ')';
v_ValuesStr := v_valuesStr || ')';
v_InsertStr := v_InsertStr || v_ValuesStr;
DBMS_OUTPUT.PUT_LINE('output:'||v_InsertStr ); --監視SQL語句 DBMS_SQL.PARSE(v_CursorID, v_InsertStr, DBMS_SQL.NATIVE); IF (p_aaa IS NOT NULL) THEN
DBMS_SQL.BIND_VARIABLE(v_CursorID, ':AAA', p_aaa);
END IF;
IF (p_bbb IS NOT NULL) THEN
DBMS_SQL.BIND_VARIABLE(v_CursorID, ':BBB', p_bbb);
END IF; p_RowsInsert := DBMS_SQL.EXECUTE(v_CursorID);
DBMS_SQL.CLOSE_CURSOR(v_CursorID);EXCEPTION
WHEN OTHERS THEN
DBMS_SQL.CLOSE_CURSOR(v_CursorID);
p_RowsInsert := 0;
END;(注:可能是SQL語句有問題)
tmpsql := 'select sysdate from dual';
EXECUTE IMMEDIATE tmpsql;