ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = Chenshuai-PC)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
----
select * from global_name;
--第一步
create public database link IP20_to_69 connect to train identified by cape using 'IP20to69';
--其中tnsname_20to69就是到tnsname.ora中远程主机的连接字符串。
--第二步 plsql 帮助信息中找到TNS路径
--TNS File E:\app\Chenshuai\product\11.2.0\dbhome_1\Network\Admin\tnsnames.ora
--第三步 配置tns IP20to69;
select * from emp@ip20_to_69
--从69库里边复制一张表
create table emp as
select * from emp@IP20_TO_69
-----建包
/************************************************
功能:查询用户信息
编写人:XX
编写日期:2014-04-03
************************************************/
create or replace package tain_common_query is
procedure queryEmp(v_empno in emp.eno%type,v_empname out emp.ename%type,v_sal out emp.sal%type);
end;
-----建立包体
create or replace package body tain_common_query is
procedure queryEmp(v_empno in emp.eno%type,v_empname out emp.ename%type,v_sal out emp.sal%type)
is
begin
select emp.ename,emp.sal
INTO v_empname,v_sal
from emp where emp.eno=v_empno;
dbms_output.put_line('编码为'||v_empno||'的员工已经查到!');
exception
when no_data_found then
dbms_output.put_line('没有查到');
when others then
dbms_output.put_line('sqlexception');
end queryEmp;
end;
----
---计算制定部门的工资之和,并统计其中的职工数量
--建包
/************************************************
功能:计算指定部门的 工资之和
编写人:xx
编写日期:2014-04-03
************************************************/
procedure proc_demo(dept_no number default 3,sal_sum out number,emp_count out number);
---建包体
procedure proc_demo(dept_no number default 3,sal_sum out number,emp_count out number)
is
begin
select sum(sal),count(*) into sal_sum,emp_count from emp where deptno=dept_no;
exception
when no_data_found then
dbms_output.put_line('您需要的数据不存在');
when others then
dbms_output.put_line('SQLERROR');
end;
----------
--把存储过程执行的权限给予相关的角色或用户
create synonym dd for ff
grant all on ff to dd with grant option;
---
select * from dept
----
--创建的包为demo_pack, 该包中包含一个记录变量DeptRec、两个函数和一个过程
create or replace package demo_pack
is
DeptRec dept%rowtype;
function add_dept(dept_no number,dept_name varchar2,location varchar2)
return number;
function remove_dept(dept_no number)
return number;
procedure query_dept(dept_no in number);
end demo_pack;
------------创建包体
create or replace package body demo_pack is
function add_dept(dept_no number, dept_name varchar2, location varchar2)
return number is
empno_remaining exception;
pragma exception_init(empno_remaining, -1);
/*-1 是违反唯一性约束的错误代码*/
begin
INSERT INTO dept VALUES (dept_no, dept_name, location);
IF SQL%FOUND THEN
RETURN 1;
END IF;
EXCEPTION
WHEN empno_remaining THEN
RETURN 0;
WHEN OTHERS THEN
RETURN - 1;
end add_dept;
function remove_dept(dept_no number) return number is
begin
DELETE FROM dept WHERE deptno = dept_no;
IF SQL%FOUND THEN
RETURN 1;
ELSE
RETURN 0;
END IF;
EXCEPTION
WHEN OTHERS THEN
RETURN - 1;
end remove_dept;
procedure query_dept(dept_no in number) is
begin
select * into deptRec from dept where deptno = dept_no;
exception
when no_data_found then
dbms_output.put_line('数据库中没有该部门');
when too_many_rows then
dbms_output.put_line('请使用游标处理');
when others then
dbms_output.put_line('SQL ERROR');
end query_dept;
end demo_pack;
-----------------------------------
--调用存储过程执行,增加,查询,和删除数据操作
declare
var number;
begin
var :=demo_pack.add_dept(90,'administration','BeiJing');
if var = -1 then
dbms_output.put_line('sql error');
elsif var=0 then
dbms_output.put_line('该部门记录已经存在');
else
dbms_output.put_line('添加记录成功');
demo_pack.query_dept(90);
dbms_output.put_line('部门编码 '||demo_pack.DeptRec.deptno||'部门名称 '||demo_pack.DeptRec.name);
var :=demo_pack.remove_dept(90);
if var =-1 then
dbms_output.put_line('sql error');
elsif var =0 then
dbms_output.put_line('该部门记录不存在!');
else
dbms_output.put_line('删除成功');
end if;
end if;
end;
-----------------------------
create or replace package emp_package
is
type emp_table_type is table of emp%rowtype
index by binary_integer;
procedure read_emp_table(p_emp_table out emp_table_type);
end emp_package;
-----------------
create or replace package body emp_package
is
procedure read_emp_table(p_emp_table out emp_table_type)
is
I binary_integer :=0;
begin
for emp_record in (select * from emp) loop
p_emp_table(i) :=emp_record;
I :=I+1;
end loop;
end read_emp_table;
end emp_package;
-----调用存储过程
declare
e_table emp_package.emp_table_type;
begin
emp_package.read_emp_table(e_table);
for I in e_table.first..e_table.last loop
dbms_output.put_line(e_table(i).ENO||' '||e_table(i).ENAME);
end loop;
end;
-----------------------
select sys_guid() from dual
---------------
create or replace package emp_mgmt
as
function hire(ename VARCHAR2, job VARCHAR2, mgr NUMBER, sal NUMBER,
comm NUMBER, deptno NUMBER) return number;
function create_dept(dname varchar2,loc varchar2) return number;
procedure remove_emp(empno number);
procedure remove_dept(deptno number);
PROCEDURE increase_sal(empno NUMBER, sal_incr NUMBER);
PROCEDURE increase_comm(empno NUMBER, comm_incr NUMBER);
end emp_mgmt;
---------------------
--建立触发器
select * from emp_his
--复制一张员工信息表,作为日志记录
create table emp_his as select * from emp where 1=2
----生成触发器,在删除操作的时候记录日志
create or replace trigger del_emp
before delete on train.emp for each row
begin
INSERT INTO emp_his(ENAME,SAL,ENO,DEPTNO)
VALUES(:old.ENAME,:old.SAL,:old.ENO,:old.DEPTNO);
end;
DELETE emp WHERE emp.eno='2'
drop trigger del_emp
----查询程序包的源代码
SELECT * FROM USER_SOURCE
-----------------
---查看链接用户
--查看当前连接的用户
select username,sid,serial# from v$session;
---关闭连接的进程
alter system kill session '194,3';