ALTER TABLE 表名 rename column 列名 to 新列名 /*重新命名列名*/
ALTER TABLE 表名 rename to 新表名 /*重命名錶名*/
最近整天弄mysql,oracle語法都生疏了,正好練練,複習,再學習的過程。
但願是幫你,不是害你。
1。——-
alter user sys identified by orcale;
alter user system identified by orcale;
2。——-
create tablespace ts_estore
datafile ‘filespec’ size 1m
autoextend on next 500k
extent management local uniform size 128k;
3。——-
create user estore identified by estore
default tablespace ts_estore;
4。——-
grant connect,resource 8 privs,create any table,alter any table,delete any table,
create any view,delete any view to estore;
——resource 8 privs 具有程式開發最的許可權 (??)
5。——-
connect estore/estore
create table users (
id number not null primary key,
username varchar2(30),
userpass varchar2(8),
useraddr varchar2(80),
useremail varchar2(60),
created date default sysdate);
create sequence s_users start with 1 increment by 1;
create or replace triggrt users_id_trigger
before insert on users for each now
declare
next_no number;
begin
select s_users。nextval into next_no from dual;
:new。id :=next_no;
end;
6。——-
insert into users (username,userpass,useraddr,useremail) values (“張三”,“zhang3”,“師大”,“zhang3@sina。com”);
insert into users (username,userpass,useraddr,useremail) values (“李四”,“li4”,“師大”,“li4@sina。com”);
insert into users (username,userpass,useraddr,useremail) values (“王五”,“wang5”,“師大”,“li4@sina。com”);
7。——-
set serveroutput on
declare
uid users。id%type;
uname users。username%type;
upass users。userpass%type;
uaddr users。useraddr%type;
umail users。useremail%type;
ucreatedtime users。created%type;
cursor mycur is select * from users;
begin
open mycur;
fetch mycur into uid,uname,upass,uaddr,umail,ucreatedtime;
dbms_output。put_line(“loop迴圈輸出:”);
loop
dbms_output。put_line(uid||“ ”||uname||“ ”||upass||“ ”||uaddr||“ ”||umail||“ ”||ucreatedtime);
exit when mycur%notfound;
end loop;
close mycur;
open mycur;
fetch mycur into uid,uname,upass,uaddr,umail,ucreatedtime;
dbms_output。put_line(“while迴圈輸出:”);
while mycur%found loop
dbms_output。put_line(uid||“ ”||uname||“ ”||upass||“ ”||uaddr||“ ”||umail||“ ”||ucreatedtime);
end loop;
close mycur;
end;
8。——-
set serveroutput on
declare
begin
dbms_output。put_line(“for語句隱式遊標迴圈輸出:”);
for my_record in (select * from users) loop
dbms_output。put_line(my_record。uid||“ ”||my_record。uname||“ ”||my_record。upass||“ ”||my_record。uaddr||“
”||my_record。umail||“ ”||my_record。ucreatedtime);
end loop;
end;
9。——-
set serveroutput on
create or replace p_login (userid in users。id%type,password in users。userpass%type)
as
user_count number;
user_not_exist exception;
user_pass_error exception;
begin
select count(1) into user_count from users where id = userid;
if user_count = 0 then raise user_not_exist;
elsif user_count<>0 then
select count(1) into user_count from users where id = userid and userpass = password;
if user_count = 0 then raise user_pass_error;
elsif user_count <> 0 then
dbms_output。put_line(“使用者名稱密碼正確!!”);
endif;
endif;
exception
when user_not_exist then dbms_output。put_line(“使用者不存在”);
when user_pass_error then dbms_output。put_line(“使用者密碼錯誤”);
end;
10。——-
create table user_log
(op varchar2(10),
id number,
oldid number,
newid number
updatedtime date);
create sequence s_user_log start with 1 increment by 1;
create or replace triggrt users_op_trigger
after insert or update or delete on users
for each now
begin
if inserting then
insert into user_log values (“insert”,s_user_log。nextval,old。id,new。id,sysdate);
elsif updating then
insert into user_log values (“update”,s_user_log。nextval,old。id,new。id,sysdate);
elsif deleting then
insert into user_log values (“delete”,s_user_log。nextval,old。id,old。id,sysdate);
endif;
end;
all。。
呃,手都凍僵了。誰幫我呵一下。。。