Question

oracle存储过程插入一条数据，运行之后，数据不能插入

create or replace procedure CREATE_TABLE(TAB_NAME in varchar2,COLUMN_NAME in varchar2)
is TEMP_NUM NUMBER :=0;
begin
select count(*) into TEMP_NUM from user_tables where table_name=TAB_NAME;
if TEMP_NUM=0 then
execute immediate 'CREATE TABLE '||TAB_NAME||' ( '||COLUMN_NAME||' varchar2(30))';
else
INSERT INTO TAB_NAME ( COLUMN_NAME ) values ( 'qqq' );
end if;
end CREATE_TABLE;

然后运行存储过程
call CREATE_TABLE('TEST1','TIME1');--表格TEST1已经是存在的
commit;
运行下面的语句，表格test1没有插入数据
select * from test1;

Answer 1

这种情况属于插入数据后，没有提交（commit），导致表被锁，所以需要进行提交（commit）或者回滚（rollback）操作。

如果当前session已关闭，可通过如下方法杀掉进程。

1、生成Kill Session语句

select 'alter system kill session ''' || SID ||',' || SERIAL# || ''';' from
(
select distinct a.sid, a.Serial#, status, machine, LOCKWAIT, logon_time
  from v$session a, v$locked_object b
 where (a.status = 'ACTIVE' or a.status = 'INACTIVE')
   and a.sid = b.session_id
   and b.ORACLE_USERNAME='XYHISTEST'--加上用户名是避免把其他系统的会话也关闭，以免伤及无辜

)

2、批量执行第一步生成的语句

alter system kill session 'sid,serial#';

Answer 2

CREATE OR REPLACE PROCEDURE CREATE_TABLE(TAB_NAME    IN VARCHAR2,
                                         COLUMN_NAME IN VARCHAR2) IS
  TEMP_NUM NUMBER := 0;
BEGIN
  SELECT COUNT(*)
    INTO TEMP_NUM
    FROM USER_TABLES
   WHERE TABLE_NAME = TAB_NAME;
  IF TEMP_NUM = 0 THEN
    EXECUTE IMMEDIATE 'CREATE TABLE ' || TAB_NAME || ' ( ' || COLUMN_NAME ||
                      ' varchar2(30))';
  ELSE
  --全部都改为动态执行吧
    EXECUTE IMMEDIATE'INSERT INTO ' || TAB_NAME || '(COLUMN_NAME) VALUES (''qqq'')';
  END IF;
  --记得在执行数据操作语句后 提交
  COMMIT;
END CREATE_TABLE;

追问

为什么这里是两个单引号 ''qqq''， 一个单引号不行吗？

追答

汗，自己试试不就知道了么

CREATE OR REPLACE PROCEDURE CREATE_TABLE(TAB_NAME    IN VARCHAR2,
                                         COLUMN_NAME IN VARCHAR2) IS
  TEMP_NUM NUMBER := 0;
BEGIN
  SELECT COUNT(*)
    INTO TEMP_NUM
    FROM USER_TABLES
   WHERE TABLE_NAME = TAB_NAME;
  IF TEMP_NUM = 0 THEN
    EXECUTE IMMEDIATE 'CREATE TABLE ' || TAB_NAME || ' ( ' || COLUMN_NAME ||
                      ' varchar2(30))';
  ELSE
  --全部都改为动态执行吧
    EXECUTE IMMEDIATE'INSERT INTO ' || TAB_NAME || '(COLUMN_NAME) VALUES ('qqq')';
  END IF;
  --记得在执行数据操作语句后 提交
  COMMIT;
END CREATE_TABLE;

 直接就可以看出会出错

两个单引号在动态sql中可以表示一个单引号

Answer 3

你那段sql是往TAB_NAME 表里查数据，test1表当然没数据了