1.视图(view)
通过创建表的视图来表现数据的逻辑子集或数据的组合，视图是基于表或者另一个视图的逻辑表，一个视图并不包含它自己的数据，它像一个窗口，通过该窗口查看数据或改变表中的数据。
视图基于其上的表称为基表，视图是数据字典中作为一个select语句存储。
view syntax:
create or replace view view_name as
subquery;
视图有什么优势：
(1)视图能选择型显示表中的列
(2)视图可以用来构成简单的查询取回复杂的结构，用户不必知道复杂语句怎么编写
//创建视图查询雇员，薪水，电话号码
create or replace view v_emp as
select last_name || ' ' || first_name emp_name,salary+nvl(commission_pct,0)*salary salary,
phone_number from employees order by salary desc;
---查询视图v_emp
select emp_name,salary,phone_number from v_emp;
//创建视图查询部门名称，部门最低薪水，部门最高薪水，部门平均薪水
create or replace view v_salary as
select b.department_name,min(a.salary) min_salary
,max(salary) max_salary,round(avg(a.salary),2) avg_salary
from employees a
join departments b on a.department_id = b.department_id
group by b.department_name;
select * from v_salary;
2.存储过程
---tbl_name：t_errors
procedure_name:varchar2(200),主键
error_code number
error_message varchar2(200)
create_date date 默认为sysdate
syntax:
create or replace PROCEDURE procedure_name
[(parameter[,parameter,......])]
AS|IS
[local declations]
BEGIN
executable statements
[EXCEPTION
exception handlers]
END;
//输出当前系统时间
create or replace procedure pro_output_time
is
begin
dbms_output.put_line(systimestamp);
end;
---调试存储过程
SQL> BEGIN
SQL> PRO_OUTPUT_TIME();
SQL> END;
SQL> /
SQL> set serveroutput on;
SQL> call pro_output_time();
29-AUG-14 01.11.09.428895000 AM +08:00
//根据雇员编号，输出雇员姓名，薪水
create or replace procedure pro_empno(v_empno number,v_ename out varchar2,v_sal out number)
is
begin
select ename,sal into v_ename,v_sal from emp where empno=v_empno;
dbms_output.put_line('employee name:'||v_ename);
dbms_output.put_line('employee salary:'||v_sal);
exception
when no_data_found then
dbms_output.put_line('the employess is not exists');
end;
//通过雇员编号获取员工姓名(hr用户)
---错误表
create table t_errors(error_id number not null
,procedure_name varchar2(200)
,error_code number
,error_message varchar2(200)
,create_date date default sysdate,
constraint pk_errors_id primary key(error_id)
);
create or replace procedure pro_get_empname(v_employee_id number)
is
v_ename varchar2(50);
v_salary employees.salary%type;
v_code number;
v_errorm varchar2(200);
begin
select last_name || ' ' || first_name,salary into v_ename,v_salary
from employees where employee_id=v_employee_id;
dbms_output.put_line('employee name is:' || v_ename || '\n');
dbms_output.put_line('employee salary is:' || v_salary || '\n');
exception when others then
v_code := sqlcode;
v_errorm := substr(sqlerrm,1,200);
insert into t_errors(error_id,procedure_name,error_code,error_message)
values(seq_errors.nextval,'pro_get_empname',v_code,v_errorm);
end;
//输入雇员编号，雇员名，薪水，部门编号，工作，向emp表中插入一条数据，存储过程名为pro_add_employee,并且要处理异常
3.函数
函数用于返回特定值，函数可以接受一个或者多个参数(in /out/in out),但是函数可执行部分必须有一个返回子句，必须在函数头部申明返回值的数据类型
create or replace function function_name(parameter list)
return datatype
is
begin
<body>
return (return_value)
end;
//通过雇员编号获取雇员名
create or replace function fun_get_empname(v_employee_id number)
return varchar2 is
v_empname varchar2(50);
begin
select last_name || ' ' || first_name into v_empname
from employees where employee_id=v_employee_id;
return (v_empname);
end;
//调试
select fun_get_empname(198) from dual;
//获取当前数据库用户名那个
select user from user_users;
//根据雇员编号获取雇员工资
create or replace function fun_get_user
return varchar2 is
v_user varchar2(50);
begin
select user into v_user from user_users;
return v_user;
end;
4.触发器(trigger)
触发器：被隐含执行的存储过程
触发器由触发事件，触发条件和触发操作三部分组成
触发事件:指引起触发器被触发的SQL语句，数据库事件或者用户事件
具体触发事件：
启动和关闭过程
oracle错误信息
用户登陆和会话断开
指定表或视图的DML操作
在任何方案上的DDL语句
触发条件：使用when字句指定一个boolean表达式
触发操作：指包含的SQL操作或者其他执行代码的PL/SQL块，不仅使用Pl/sql开发，也可以使用java和c语言开发。
触发器限制：
(1)触发器代码的大小不能超过32,大量触发器应该先建立存储过程，然后再触发器中使用。
(2)不能使用long,long raw定义变量类型
语句触发器
行触发器
触发时机(before|after)
触发事件(insert,update,delete)
触发类型(执行几次触发操作)
语句触发器：
create or replace trigger trigger_name
timing event1[or event2 or event3]
on table_name
pl/sql block;
timing触发时机(before|after)
event1[or event2 or event3]:DML语句(insert,update,delete)
if then
end if;
before触发器
//禁止工作人员周一改变雇员信息
create or replace trigger tri_change_emp
before insert or update or delete
on emp
begin
if to_char(sysdate,'DY','nls_date_language=AMERICAN') IN ('FRI') then
raise_application_error(-20001,'you can not change employee info');
end if;
end;
after触发器
//审计表上的insert,update，delete操作次数
create table t_audit(names varchar2(50)
,insert_count number
,update_count number
,delete_count number
,start_date date
,end_date date
);
create or replace trigger tri_audit_emp
after insert or update or delete
on emp
declare v_tmp number;
begin
select count(*) INTO v_tmp from t_audit where names='EMP';
if v_tmp =0 then
insert into t_audit values('EMP',0,0,0,sysdate,null);
case when inserting then
update t_audit set insert_count=insert_count + 1 where names='EMP';
when updating then
update t_audit set update_count=update_count + 1 where names='EMP';
when deleting then
update t_audit set delete_count=delete_count + 1 where names='EMP';
end case;
end if;
end;
5.package(包)
简化应用程序设计，提高应用性能，而且可以实现信息隐藏等功能
包由包头和包体组成，首先要建立包头，再建立包体。
包头用来定义公共组件。
---包头语法
create or replace package package_name
is|as
public type item declaraions
subprogram specification
end package_name;
---包体语法
create or replace package body package_name
is|as
private and item declaraions
subprogram bodies
end package_name;
//
(1)根据雇员编号，雇员名，薪水，部门编号向emp表中插入一条数据
(2)根据雇员编号获取员工薪水
##包头
create or replace package pkg_emp
is
g_deptno number := 30;
procedure pro_add_emp(v_eno number,v_ename varchar2,v_sal number,v_dno number default g_deptno);
function fun_get_sal(v_eno number) return number;
end pkg_emp;
#包体
create or replace package body pkg_emp
is
function fun_validate_deptno(v_deptno number) return boolean
is
v_temp number;
begin
select 1 into v_temp from dept where deptno=v_deptno;
return true;
exception when no_data_found then
return false;
end;
procedure pro_add_emp(v_eno number,v_ename varchar2,v_sal number,v_dno number default g_deptno)
is
begin
if fun_validate_deptno(v_dno) then
insert into emp(empno,ename,sal,deptno)
values (v_eno,v_ename,v_sal,v_dno);
else
raise_application_error(-20000, 'it is not exist deptno');
end if;
end;
function fun_get_sal(v_eno number) return number
is
v_sal emp.sal%type;
begin
select sal into v_sal from emp where empno=v_eno;
return v_sal;
exception when no_data_found then
raise_application_error(-20001,'empno is not exist.');
end;
end pkg_emp;
==================================
---命令行调试
SQL> variable sal1 number;
SQL> var sal1 number;
SQL>
SQL> exec :sal1 := pkg_emp.fun_get_sal(7839);
PL/SQL procedure successfully completed.
SQL> print sal1;
SAL1
----------
5000
=================mysql ========================================
tianxiaoyan@tianxy:~$ sudo apt-get install mysql-server-5.5
tianxiaoyan@tianxiaoyan-HP-Pro-3380-MT:~$ sudo service mysql /?
Usage: /etc/init.d/mysql start|stop|restart|reload|force-reload|status
---mysql -uroot -pmysql -h10.10.54.58
$ mysql -uroot -p
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)
mysql> use mysql;----切换数据库
mysql> show tables; ---查看当前数据库下有那些表
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| servers |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
24 rows in set (0.01 sec)
mysql> show create table user\G; ---查看表结构
---下载mysql地址
dev.mysql.com
通过创建表的视图来表现数据的逻辑子集或数据的组合，视图是基于表或者另一个视图的逻辑表，一个视图并不包含它自己的数据，它像一个窗口，通过该窗口查看数据或改变表中的数据。
视图基于其上的表称为基表，视图是数据字典中作为一个select语句存储。
view syntax:
create or replace view view_name as
subquery;
视图有什么优势：
(1)视图能选择型显示表中的列
(2)视图可以用来构成简单的查询取回复杂的结构，用户不必知道复杂语句怎么编写
//创建视图查询雇员，薪水，电话号码
create or replace view v_emp as
select last_name || ' ' || first_name emp_name,salary+nvl(commission_pct,0)*salary salary,
phone_number from employees order by salary desc;
---查询视图v_emp
select emp_name,salary,phone_number from v_emp;
//创建视图查询部门名称，部门最低薪水，部门最高薪水，部门平均薪水
create or replace view v_salary as
select b.department_name,min(a.salary) min_salary
,max(salary) max_salary,round(avg(a.salary),2) avg_salary
from employees a
join departments b on a.department_id = b.department_id
group by b.department_name;
select * from v_salary;
2.存储过程
---tbl_name：t_errors
procedure_name:varchar2(200),主键
error_code number
error_message varchar2(200)
create_date date 默认为sysdate
syntax:
create or replace PROCEDURE procedure_name
[(parameter[,parameter,......])]
AS|IS
[local declations]
BEGIN
executable statements
[EXCEPTION
exception handlers]
END;
//输出当前系统时间
create or replace procedure pro_output_time
is
begin
dbms_output.put_line(systimestamp);
end;
---调试存储过程
SQL> BEGIN
SQL> PRO_OUTPUT_TIME();
SQL> END;
SQL> /
SQL> set serveroutput on;
SQL> call pro_output_time();
29-AUG-14 01.11.09.428895000 AM +08:00
//根据雇员编号，输出雇员姓名，薪水
create or replace procedure pro_empno(v_empno number,v_ename out varchar2,v_sal out number)
is
begin
select ename,sal into v_ename,v_sal from emp where empno=v_empno;
dbms_output.put_line('employee name:'||v_ename);
dbms_output.put_line('employee salary:'||v_sal);
exception
when no_data_found then
dbms_output.put_line('the employess is not exists');
end;
//通过雇员编号获取员工姓名(hr用户)
---错误表
create table t_errors(error_id number not null
,procedure_name varchar2(200)
,error_code number
,error_message varchar2(200)
,create_date date default sysdate,
constraint pk_errors_id primary key(error_id)
);
create or replace procedure pro_get_empname(v_employee_id number)
is
v_ename varchar2(50);
v_salary employees.salary%type;
v_code number;
v_errorm varchar2(200);
begin
select last_name || ' ' || first_name,salary into v_ename,v_salary
from employees where employee_id=v_employee_id;
dbms_output.put_line('employee name is:' || v_ename || '\n');
dbms_output.put_line('employee salary is:' || v_salary || '\n');
exception when others then
v_code := sqlcode;
v_errorm := substr(sqlerrm,1,200);
insert into t_errors(error_id,procedure_name,error_code,error_message)
values(seq_errors.nextval,'pro_get_empname',v_code,v_errorm);
end;
//输入雇员编号，雇员名，薪水，部门编号，工作，向emp表中插入一条数据，存储过程名为pro_add_employee,并且要处理异常
3.函数
函数用于返回特定值，函数可以接受一个或者多个参数(in /out/in out),但是函数可执行部分必须有一个返回子句，必须在函数头部申明返回值的数据类型
create or replace function function_name(parameter list)
return datatype
is
begin
<body>
return (return_value)
end;
//通过雇员编号获取雇员名
create or replace function fun_get_empname(v_employee_id number)
return varchar2 is
v_empname varchar2(50);
begin
select last_name || ' ' || first_name into v_empname
from employees where employee_id=v_employee_id;
return (v_empname);
end;
//调试
select fun_get_empname(198) from dual;
//获取当前数据库用户名那个
select user from user_users;
//根据雇员编号获取雇员工资
create or replace function fun_get_user
return varchar2 is
v_user varchar2(50);
begin
select user into v_user from user_users;
return v_user;
end;
4.触发器(trigger)
触发器：被隐含执行的存储过程
触发器由触发事件，触发条件和触发操作三部分组成
触发事件:指引起触发器被触发的SQL语句，数据库事件或者用户事件
具体触发事件：
启动和关闭过程
oracle错误信息
用户登陆和会话断开
指定表或视图的DML操作
在任何方案上的DDL语句
触发条件：使用when字句指定一个boolean表达式
触发操作：指包含的SQL操作或者其他执行代码的PL/SQL块，不仅使用Pl/sql开发，也可以使用java和c语言开发。
触发器限制：
(1)触发器代码的大小不能超过32,大量触发器应该先建立存储过程，然后再触发器中使用。
(2)不能使用long,long raw定义变量类型
语句触发器
行触发器
触发时机(before|after)
触发事件(insert,update,delete)
触发类型(执行几次触发操作)
语句触发器：
create or replace trigger trigger_name
timing event1[or event2 or event3]
on table_name
pl/sql block;
timing触发时机(before|after)
event1[or event2 or event3]:DML语句(insert,update,delete)
if then
end if;
before触发器
//禁止工作人员周一改变雇员信息
create or replace trigger tri_change_emp
before insert or update or delete
on emp
begin
if to_char(sysdate,'DY','nls_date_language=AMERICAN') IN ('FRI') then
raise_application_error(-20001,'you can not change employee info');
end if;
end;
after触发器
//审计表上的insert,update，delete操作次数
create table t_audit(names varchar2(50)
,insert_count number
,update_count number
,delete_count number
,start_date date
,end_date date
);
create or replace trigger tri_audit_emp
after insert or update or delete
on emp
declare v_tmp number;
begin
select count(*) INTO v_tmp from t_audit where names='EMP';
if v_tmp =0 then
insert into t_audit values('EMP',0,0,0,sysdate,null);
case when inserting then
update t_audit set insert_count=insert_count + 1 where names='EMP';
when updating then
update t_audit set update_count=update_count + 1 where names='EMP';
when deleting then
update t_audit set delete_count=delete_count + 1 where names='EMP';
end case;
end if;
end;
5.package(包)
简化应用程序设计，提高应用性能，而且可以实现信息隐藏等功能
包由包头和包体组成，首先要建立包头，再建立包体。
包头用来定义公共组件。
---包头语法
create or replace package package_name
is|as
public type item declaraions
subprogram specification
end package_name;
---包体语法
create or replace package body package_name
is|as
private and item declaraions
subprogram bodies
end package_name;
//
(1)根据雇员编号，雇员名，薪水，部门编号向emp表中插入一条数据
(2)根据雇员编号获取员工薪水
##包头
create or replace package pkg_emp
is
g_deptno number := 30;
procedure pro_add_emp(v_eno number,v_ename varchar2,v_sal number,v_dno number default g_deptno);
function fun_get_sal(v_eno number) return number;
end pkg_emp;
#包体
create or replace package body pkg_emp
is
function fun_validate_deptno(v_deptno number) return boolean
is
v_temp number;
begin
select 1 into v_temp from dept where deptno=v_deptno;
return true;
exception when no_data_found then
return false;
end;
procedure pro_add_emp(v_eno number,v_ename varchar2,v_sal number,v_dno number default g_deptno)
is
begin
if fun_validate_deptno(v_dno) then
insert into emp(empno,ename,sal,deptno)
values (v_eno,v_ename,v_sal,v_dno);
else
raise_application_error(-20000, 'it is not exist deptno');
end if;
end;
function fun_get_sal(v_eno number) return number
is
v_sal emp.sal%type;
begin
select sal into v_sal from emp where empno=v_eno;
return v_sal;
exception when no_data_found then
raise_application_error(-20001,'empno is not exist.');
end;
end pkg_emp;
==================================
---命令行调试
SQL> variable sal1 number;
SQL> var sal1 number;
SQL>
SQL> exec :sal1 := pkg_emp.fun_get_sal(7839);
PL/SQL procedure successfully completed.
SQL> print sal1;
SAL1
----------
5000
=================mysql ========================================
tianxiaoyan@tianxy:~$ sudo apt-get install mysql-server-5.5
tianxiaoyan@tianxiaoyan-HP-Pro-3380-MT:~$ sudo service mysql /?
Usage: /etc/init.d/mysql start|stop|restart|reload|force-reload|status
---mysql -uroot -pmysql -h10.10.54.58
$ mysql -uroot -p
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)
mysql> use mysql;----切换数据库
mysql> show tables; ---查看当前数据库下有那些表
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| servers |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
24 rows in set (0.01 sec)
mysql> show create table user\G; ---查看表结构
---下载mysql地址
dev.mysql.com
2293
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
