1在sysdba角色下:
命令行进入
[oracle@J15 ~]$ sqlplus ' / as sysdba '
SQL>start
视图进入
[oracle@J15 ~]$ isqlplusctl start
去浏览器输入http://localhost:5560/isqlplus/
创建用户
SQL> create user test identified by test;
User created.
修改用户密码
SQL>alter user sys identified by test;
desc dba_uses;
select username,default_tablespace from dba_users;
切换或登录
conn name/passwd
创建表空间
SQL> create tablespace test_tablespace datafile '/home/oracle/oracledata/test.dbf' size 40M;
desc dba_tablespaces;
select tablespace_name from dba_tablespaces;
给用户赋默认表空间
SQL> alter user test default tablespace test_tablespace temporary tablespace temp;
查看数据文件
SQL>select file_name from dba_data_files;
--------------------------------------------------------------------------------
/home/oracle/oracle.10g/oradata/orcl/users01.dbf
/home/oracle/oracle.10g/oradata/orcl/sysaux01.dbf
/home/oracle/oracle.10g/oradata/orcl/undotbs01.dbf
/home/oracle/oracle.10g/oradata/orcl/system01.dbf
/home/oracle/oracle.10g/oradata/orcl/example01.dbf
/home/oracle/oracledata/test.dbf
将表移动到某个表空间
ALTER TABLE TABLE_NAME MOVE TABLESPACE_NAME;
将索引移动到表空间
ALTER INDEX INDEX_NAME REBUILD TABLESPACE TABLESPACE_NAME;
alter database test add datafile '路径/tset.dbf' resize 40m;
select tablespace_name,file_name from dba_data_files;
============================================================
运用文本编辑器
SQL> define _editor="vim"
SQL> edit
===================================================================
创建表
create table emp (..);
复制表
create table cp_emp as select * from emp;
insert乱码在.bash_profile中添加下列:
export NLS_LANG="SIMPLIFIED CHINESE_CHINA.ZHS16GBK"
如果修改表名
alter table old_table_name rename to new_table_name;
用户拥有的权限
SELECT * FROM dba_sys_privs ;
function 必须有返回值
procedure 可有可无返回值
================================================
1.block块
3部分组成,只有begin end 必须
declare
......
begin
.....
exception
.....
end
block 类型
3种 ?---------
============================================
二。pl/sql
1.
set serveroutput on; (显示下面的命令)
execute dbms_output.put_line('hell');
写在块里面:
SQL> begin
2 dbms_output.put_line('bye');
3 end;
4 /
2.pl/sql 变量
declare
v_com DATE;
v_long NUNBER(8,2);
eg:
SQL> set serveroutput on;
SQL> select to_char(sysdate,'YYYY-MM-DD') from dual;
TO_CHAR(SY
----------
2009-05-04
SQL> begin
2 dbms_output.put_line(to_char(sysdate,'YYYY-MM-DD'));
3 end;
4 /
2009-05-04
insert into emp(empno,hiredate) values(4567,to_date('2009-05-04','YYYY-MM-DD'));
eg2:SQL> create tablespace test_tablespace datafile '/home/oracle/oracledata/test.dbf' size 40M;
Tablespace created.
v_id Employee.id%type;(定义和Employee表中id字段相同的类型)
select nvl('','can not accepted') from emp;
全局变量:
avariable g_month NUMBER;
define p_aunual_sal=500
declare
v_sal number;
begin
:g_monthly_sal:=& p_annual_sal/12;
end;
/
define ?------------[oracle@J15 ~]$ isqlplusctl start
-
SQLCODE ?-------------
• SQLERRM ?------------SQL>
TO_CHAR
TO_DATE
TO_NUMBER
Syntax
TO_CHAR (value, fmt)
TO_DATE (value, fmt)
TO_NUMBER (value, fmt)
eg:
v_date := TO_DATE (’January 13, 2001’,
’Month DD, YYYY’);
===========================================
Example:
...
x BINARY_INTEGER;
BEGIN
...
DECLARESQL> create tablespace test_tablespace datafile '/home/oracle/oracledata/test.dbf' size 40M;
Tablespace created.
y NUMBER;
BEGIN
y:= x;
END;
...
END;
比较
<<outer>>
DECLARE
birthdate DATE;
BEGIN
DECLARE
birthdate DATE;
BEGIN
...
outer.birthdate :=
TO_DATE(’03-AUG-1976’,
’DD-MON-YYYY’);
END;
....
END;
============================================
INSERT
UPDATE
DELETE
MERGE *
eg:
Insert or update rows in the COPY_EMP table to match
the EMPLOYEES table.
DECLARE
v_empno EMPLOYEES.EMPLOYEE_ID%TYPE := 100;
BEGIN
MERGE INTO copy_emp c
USING employees e
ON (c.employee_id = v_empno)
WHEN MATCHED THEN
UPDATE SET
c.first_name = e.first_name,
c.last_name = e.last_name,
c.email = e.email,
...
WHEN NOT MATCHED THEN
INSERT VALUES(e.employee_id, e.first_name, e.last_name,
. . .,e.deparPROCEDURE1tment_id);
END;
复制表:
create table cp_emp as select * from emp;
SQL> begin
2 merge into cp_emp c using emp e on(c.emp_id=e.emp_id)
3 when matched then
4 update set c.emp_name=e.emp_name
5 when not matched then
6 insert (c.emp_id,c.emp_name) values(e.emp_id,e.emp_name);
7* end;
SQL> /
PL/SQL procedure successfully completed.
=================================
1.record:?------
2.rowtype :?--------
=========================================================
select与Cursor
select只能取出一条记录;
Cursor可以取多条记得操作;
cursor运用:
eg:
emp table;
emp_id, emp_name;
1,中国
2,香港
3,澳门
SQL>declare
2 emp_res emp%rowtype;
3 begin
4 select * into emp_res from emp where emp.emp_id=2;
5 dbms_output.put_line(emp_res.emp_name||emp_res.emp_id);
6* end;
SQL> /
PL/SQL procedure successfully completed.
香港2
eg:
SQL>declare
2 v_emp_id emp.emp_id%type;
3 v_emp_name emp.emp_name%type;
4 cursor emp_cursor is
5 select emp_id,emp_name from emp;(这里可以添加where条件)
6 begin
7 open emp_cursor;
8 loop
9 fetch emp_cursor into v_emp_id,v_emp_name;
10 exit when emp_cursor%notfound;
11 dbms_output.put_line(v_emp_id||','||v_emp_name);
12 end loop;
13 close emp_cursor;
14* end;
SQL> /
1,中国
2,香港
3,澳门
与上面的结果相同
SQL> declare
2 cursor emp_cursor is select * from emp;(这里可以添加where条件)
3 begin
4 for emp_record in emp_cursor loop
5 dbms_output.put_line(emp_record.emp_id||','||emp_record.emp_name);
6 end loop;
7* end;
8 /
1,中国
2,香港
3,澳门
SQL> begin
2 for emp_record in ( select emp_id,emp_name from emp) loop
3 dbms_output.put_line(emp_record.emp_id||','||emp_record.emp_name);
4 end loop;
5* end;
SQL> /
1,中国
2,香港
3,澳门
上面是三种Cursor的用法
oracle1-2
最新推荐文章于 2024-10-12 22:35:37 发布