create tablespace PraMing datafile 'G:\PraMing' size 100m/*创建表空间*/
/*创建用户并且设定权限*/
create user cng identified by ming /*用户名+密码*/
default tablespace PraMing/*指定默认表空间*/
temporary tablespace temp/*指定临时表空间*/
/*权限设置*/
grant connect,resource to cng/*设置用户使用权限*/
---获取当前的毫秒数
dbms_utility.get_time()
----------------------------------------------------------------------------------------------
create table student(
stuid number(1) primary key,
stuname varchar(10)
)tablespace PraMing;
insert into student(stuid,stuname) values(1,'zhangsan')
insert into student(stuid,stuname) values(2,'wangwu')
select * from student
/*变量声明*/
declare
s_stuname varchar(10);
begin
dbms_output.put_line('今天是个好日子');/*输出*/
select stuname into s_stuname from student
where stuid = &no;
dbms_output.put_line('学生名'||s_stuname);
exception when NO_DATA_FOUND then /*异常的处理*/
dbms_output.put_line('输入正确的编号');
end;
--视图 从一个或多个表中通过查询语句生成的一种虚表,可以认为是查询语句的结果
创建视图
create or replace view 视图名
as
sql查询语句;
--删除视图
drop view 视图名
-------------------------------------------------------------------------------------------
--同义词 即数据库对象的别名 避免当DBA对数据库对象进行修改变动后,就必须更新并重新编译应用程序
--创建同义词
--创建公有同义词
create public synonym 同义词名称 for 数据库对象名;
--创建私有同义词
create synonym 同义词名称 for 数据库对象名;
--删除同义词
私有
drop synonym 数据库对象名
公有
drop public synonym 数据库对象名
----------------------------------------------------------------------------------------------
--序列 一种特殊对象 给应用程序提供基本连续唯一的数值,用于实现数据库表的主键列的自增长
不设置增量最大值
create sequence 序列名 increment by 增量种子 start with 起始数字 nomaxvalue;
设置增量最大值
create sequence 序列名 increment by 增量种子 start with 起始数字 maxvalue 最大数
--------------------------------------------------------------------------------------------
--存储过程 用于执行特定操作
--创建存储过程
create or replace procedure addinfo(sid number,sname varchar2,smoney number,sadd varchar2 )
is
begin
insert into students values(sid,sname,smoney,sadd);
end;
--调用存储过程
1.
call addinfo(10,'八戒',599,'高老庄');
2.
begin
addinfo(11,'沙',998,'通天河');
s;
end;
--输出参数的存储过程
create or replace procedure findinfo(sid1 number,s_name out varchar2,
s_add out varchar2)
is
begin
select sname,sadd into s_name,s_add from students where sid=sid1;
end;
declare
s_name varchar2(20);
s_add varchar2(20);
begin
findinfo(&sid1,s_name,s_add );
dbms_output.put_line(s_name||s_add);
end;
-------------------------------------------------------------------------------------------
--函数
create or replace function show(m number) return number
is
begin
return m*10;
end;
--调用函数
select show(5) from dual
-----------------------------------
--创建方法
create or replace function findadd(num1 number)
return varchar2
as
s_add students.sadd%type;
begin
select sadd into s_add from students where sid=num1;
return s_add;
end;
--调用方法
declare
s_add students.sadd%type;
begin
s_add:=findadd(5);
dbms_output.put_line(s_add);
end;
-------------------------------------------------------------------------------------------
--包的创建
create or replace package my is
function show(m number) return number;
end;
--包体的创建 对包的实现
create or replace package body my is
create or replace function show(m number) return number
is
begin
return m*10;
end;
end;
//
--创建包
create or replace package package_add
is
function findadd1(num1 number)
return varchar2;
end package_add;
--创建包体
create or replace package body package_add
is
function findadd1(num1 number)
return varchar2
as
s_add students.sadd%type;
begin
select sadd into s_add from students where sid=num1;
return s_add;
end;
end package_add;
--调用包体
declare
s_add students.sadd%type;
begin
s_add:=package_add.findadd1(5);
dbms_output.put_line(s_add);
end;
--------------------------------------------------------------------------------------------
type emp_record_type is record (
ename scott.emp.ename%type,
ejob scott.emp.job%type.
esal scott.emp.sal%type);
emp_type emp_record_type;
-------循环
create table people(
id number(2) primary key
) tablespace PraMing;
--loop基本循环
declare
a number :=1;
begin
loop
insert into people values(a);
exit when a=10;
a:=a+1;
end loop;
end;
select * from people;
delete from people;
--while循环
declare
a number :=1;
begin
while a=10 loop
insert into people values(a);
a:=a+1;
end loop;
end;
select * from people;
delete from people;
--for循环
declare
begin
for a in 1..10 loop
insert into people values(a);
end loop;
end;
select * from people;
delete from people;
declare
begin
for a in reverse 1..10 loop
insert into people values(a);
end loop;
end;
/*创建用户并且设定权限*/
create user cng identified by ming /*用户名+密码*/
default tablespace PraMing/*指定默认表空间*/
temporary tablespace temp/*指定临时表空间*/
/*权限设置*/
grant connect,resource to cng/*设置用户使用权限*/
---获取当前的毫秒数
dbms_utility.get_time()
----------------------------------------------------------------------------------------------
create table student(
stuid number(1) primary key,
stuname varchar(10)
)tablespace PraMing;
insert into student(stuid,stuname) values(1,'zhangsan')
insert into student(stuid,stuname) values(2,'wangwu')
select * from student
/*变量声明*/
declare
s_stuname varchar(10);
begin
dbms_output.put_line('今天是个好日子');/*输出*/
select stuname into s_stuname from student
where stuid = &no;
dbms_output.put_line('学生名'||s_stuname);
exception when NO_DATA_FOUND then /*异常的处理*/
dbms_output.put_line('输入正确的编号');
end;
--视图 从一个或多个表中通过查询语句生成的一种虚表,可以认为是查询语句的结果
创建视图
create or replace view 视图名
as
sql查询语句;
--删除视图
drop view 视图名
-------------------------------------------------------------------------------------------
--同义词 即数据库对象的别名 避免当DBA对数据库对象进行修改变动后,就必须更新并重新编译应用程序
--创建同义词
--创建公有同义词
create public synonym 同义词名称 for 数据库对象名;
--创建私有同义词
create synonym 同义词名称 for 数据库对象名;
--删除同义词
私有
drop synonym 数据库对象名
公有
drop public synonym 数据库对象名
----------------------------------------------------------------------------------------------
--序列 一种特殊对象 给应用程序提供基本连续唯一的数值,用于实现数据库表的主键列的自增长
不设置增量最大值
create sequence 序列名 increment by 增量种子 start with 起始数字 nomaxvalue;
设置增量最大值
create sequence 序列名 increment by 增量种子 start with 起始数字 maxvalue 最大数
--------------------------------------------------------------------------------------------
--存储过程 用于执行特定操作
--创建存储过程
create or replace procedure addinfo(sid number,sname varchar2,smoney number,sadd varchar2 )
is
begin
insert into students values(sid,sname,smoney,sadd);
end;
--调用存储过程
1.
call addinfo(10,'八戒',599,'高老庄');
2.
begin
addinfo(11,'沙',998,'通天河');
s;
end;
--输出参数的存储过程
create or replace procedure findinfo(sid1 number,s_name out varchar2,
s_add out varchar2)
is
begin
select sname,sadd into s_name,s_add from students where sid=sid1;
end;
declare
s_name varchar2(20);
s_add varchar2(20);
begin
findinfo(&sid1,s_name,s_add );
dbms_output.put_line(s_name||s_add);
end;
-------------------------------------------------------------------------------------------
--函数
create or replace function show(m number) return number
is
begin
return m*10;
end;
--调用函数
select show(5) from dual
-----------------------------------
--创建方法
create or replace function findadd(num1 number)
return varchar2
as
s_add students.sadd%type;
begin
select sadd into s_add from students where sid=num1;
return s_add;
end;
--调用方法
declare
s_add students.sadd%type;
begin
s_add:=findadd(5);
dbms_output.put_line(s_add);
end;
-------------------------------------------------------------------------------------------
--包的创建
create or replace package my is
function show(m number) return number;
end;
--包体的创建 对包的实现
create or replace package body my is
create or replace function show(m number) return number
is
begin
return m*10;
end;
end;
//
--创建包
create or replace package package_add
is
function findadd1(num1 number)
return varchar2;
end package_add;
--创建包体
create or replace package body package_add
is
function findadd1(num1 number)
return varchar2
as
s_add students.sadd%type;
begin
select sadd into s_add from students where sid=num1;
return s_add;
end;
end package_add;
--调用包体
declare
s_add students.sadd%type;
begin
s_add:=package_add.findadd1(5);
dbms_output.put_line(s_add);
end;
--------------------------------------------------------------------------------------------
type emp_record_type is record (
ename scott.emp.ename%type,
ejob scott.emp.job%type.
esal scott.emp.sal%type);
emp_type emp_record_type;
-------循环
create table people(
id number(2) primary key
) tablespace PraMing;
--loop基本循环
declare
a number :=1;
begin
loop
insert into people values(a);
exit when a=10;
a:=a+1;
end loop;
end;
select * from people;
delete from people;
--while循环
declare
a number :=1;
begin
while a=10 loop
insert into people values(a);
a:=a+1;
end loop;
end;
select * from people;
delete from people;
--for循环
declare
begin
for a in 1..10 loop
insert into people values(a);
end loop;
end;
select * from people;
delete from people;
declare
begin
for a in reverse 1..10 loop
insert into people values(a);
end loop;
end;