最近在看书,看到动态sql这章,做了个练习,但怎么执行都不过了,请大哥哥们回复
如下:
create or replace procedure dynamicsql as
t_sql varchar2(200);
t_classno varchar(10);
t_stuid varchar(10);
t_infos infos%rowtype;
t_user varchar(20);
t_password varchar(20);cursor cur_infos is select * from infos;
begin
for t_infos in cur_infos loop
t_stuid:=t_infos.stuid;
t_classno:=t_infos.classno;
t_sql:='create user :1 identified by :2 account unlock';
t_user:=CONCAT(t_stuid,t_classno);
t_password:=CONCAT(t_stuid,t_classno);
dbms_output.put_line(t_user);
execute immediate t_sql using t_user,t_password; t_sql:='grant :1 to :2';
execute immediate t_sql using 'connect',t_user;
execute immediate t_sql using 'resource',t_user; end loop;end;动态sql 建用户
如下:
create or replace procedure dynamicsql as
t_sql varchar2(200);
t_classno varchar(10);
t_stuid varchar(10);
t_infos infos%rowtype;
t_user varchar(20);
t_password varchar(20);cursor cur_infos is select * from infos;
begin
for t_infos in cur_infos loop
t_stuid:=t_infos.stuid;
t_classno:=t_infos.classno;
t_sql:='create user :1 identified by :2 account unlock';
t_user:=CONCAT(t_stuid,t_classno);
t_password:=CONCAT(t_stuid,t_classno);
dbms_output.put_line(t_user);
execute immediate t_sql using t_user,t_password; t_sql:='grant :1 to :2';
execute immediate t_sql using 'connect',t_user;
execute immediate t_sql using 'resource',t_user; end loop;end;动态sql 建用户
解决方案 »
- redhat linux 6.3 下安装oracle10G
- 关于仓库移库位的数据表如何建立
- 关于数据库不完全恢复遇到的问题
- oracle开发JAVA存储过程,怎么引入外部的JAR包??
- oracle 问题 等待解决...??
- How to retrieve "Create Table" scripts from existing Oracle database via SQL*Plus or some other utilities?
- 关于sql连接查表的问题
- RBS表空间如何释放,急!
- 请教一游标实例,在先守候!
- 求助!!oracle 11.2.0.3 RAC 问题 node connectivity
- 怎么把数据库中的一个表的数据存到一个数据库自定义类型中
- 行转列
t_sql:='create user :1 identified by :2 account unlock';如果改成t_sql:='create user test identified by test account unlock';
s可以成功,但用参数就不行,很奇怪
用户名和密码打印出来是这样的s1001021001
create user xx identified by xx
这样的语句中不能使用using参数
改为如下问题解决
t_stuid:=t_infos.stuid;
t_classno:=t_infos.classno; t_user:=CONCAT(t_stuid,t_classno);
t_password:=CONCAT(t_stuid,t_classno); t_sql:='create user '||t_user||' identified by '||t_user||' account unlock';
dbms_output.put_line(t_user);
execute immediate t_sql;-- using 's100102','1001';
t_sql:='grant connect to '||t_user;--||t_user;
execute immediate t_sql; t_sql:='grant resource to '||t_user;--||t_user;
execute immediate t_sql;