sql 存储过程

最新推荐文章于 2023-07-23 10:16:51 发布

原创最新推荐文章于 2023-07-23 10:16:51 发布 · 710 阅读

0 ·

CC 4.0 BY-SA版权

oracle 专栏收录该内容

17 篇文章

订阅专栏

本文介绍了使用PL/SQL进行数据库操作，包括查询、计数、条件判断、循环执行及错误处理，同时演示了如何根据查询结果创建新的数据库表。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

declare
s_row number;
username varchar2(200);
vsql varchar2(2000);
t_count number;
x array;
cursor cur is select * from dba_users where rownum<200;
begin
  select count(*) into s_row from all_tables where rownum<200;
  select count(*) into t_count from all_tables where owner='LHY' and table_name='T_LOG';
  dbms_output.put_line(s_row);
  if t_count=0 then
    vsql := 'create table t_log(con varchar2(200), indate date)';
    execute immediate vsql;
    /*vsql :='drop table t_log';
    execute immediate vsql;*/
  end if;
  for c in cur loop
    dbms_output.put_line(c.username || ' ' || c.password);
    /*insert into t_log(con,indate) values(c.username || ' ' || c.password, sysdate);*/
    vsql:='insert into t_log(con,indate) values(''' || c.username || ' ' || c.password || ''',to_date(''' ||  sysdate || '''))';
    dbms_output.put_line(vsql);
    execute immediate vsql;
  end loop;
  commit;
  exception
  when others then
    dbms_output.put_line(sqlerrm);
end;

/*select * from t_log;*/

/*select username,lockwait,status,machine,program from v$session where sid in
(select session_id from v$locked_object);


select A.SQL_TEXT, B.USERNAME, C.OBJECT_ID, C.SESSION_ID, 
       B.SERIAL#, C.ORACLE_USERNAME,C.OS_USER_NAME,C.Process,
       ''''||C.Session_ID||','||B.SERIAL#||''''
from v$sql A, v$session B, v$locked_object C
where A.HASH_VALUE = B.SQL_HASH_VALUE and
B.SID = C.Session_ID*/


declare
  type array_type is array(5) of number(2);
  a array_type := array_type(5,5,5,5,5);
  begin
    for i in 1..a.count loop
  DBMS_OUTPUT.PUT_LINE(a(i));
end loop;
end;