注册
登录
| 关键词： 游标 SQL Oracle 类型 emp nbsp 触发器 变量 过程 ename |
SQL*PLUS常用命令： conn system/rr:登陆数据库 disc:断开连接 show user：显示当前用户 exit：退出系统 clear：清屏 start d:\aa.sql [arg1 arg2 arg3]：运行一个脚本，指定参数，脚本中的参数用&1 &2 $3来代替，参数依次代替。 select &a from scott.emp where deptno=20 and job='&b';$a个&b为交互式参数，接下来会输入a和b的值 SQL> define b=clerk;/*事先定义好交互变量的值*/ SQL> define a=ename; SQL> select &a from scott.emp where deptno=20 and job='&b'; SQL> UNDEFINE a;;/* 清除交互变量的值*/ 绑定变量的使用： SQL> variable num number;/*定义一个绑定变量*/ SQL> execute :num=7788;/*设定绑定变量的值*/ SQL> select * from scott.emp where empno=:num;/*使用绑定变量*/ SQL> print num;/*输出绑定变量的值*/ SQL> alter user hr identified by hrpassword account unlock;;/*解锁hr用户*/ SQL>SAVE E:\orasql\kc.sql注意：如果文件已存在，原有文件将被替换。 SQL>GET e:\orasql\kc.sql将保存在磁盘上的文件kc.sql调入缓冲区。 SQL>START e:\ orasql\kc.sql 运行磁盘上的命令文件。 Oracle中的数据类型 1.Nvarchar2（n）:n最大为4000,n代表能存储的字符串数，如nvarchar2（50）类型的字段能存放50个字符，不足50个的后边没有空格补齐。 2.Char（n）:n最大为2000,n代表能存储的字符串数，如char（50）类型的字段能存放50个字符，不足50个的后边有空格补齐。 3.Number（p,s）数字类型，p为数字的总位数，s为小数点后的位数。 4.BOOLEAN：布尔类型，取值为TRUE,FALSE,NULL，该类型只能应用到PL/SQL中，不能应用到列类型中。 5.DATE：日期类型，ORACLE中默认的日期格式是'07-10月-82'，我们要想修改默认的日期格式000（基于本次回话）为’YYYY-MM-DD’，则可以用命令：alter session set nls_date_format='yyyy-mm-dd'; 6.LOB数据类型（存储最大为4G的无结构大文件）： 1）BLOB：一般为图片，图像 2）CLOB：只有文本的大文件 3）BFILE:存储声音视频等文件 7.%Type:使一个变量的类型参照其他类型，一般为列的类型。 如： DECLARE V_ename emp.ename%type;--参照emp表的ename类型。 如： declare v_ename emp.ename%type;--参照emp表中的ename该列的类型。 v_sal emp.sal%type; v_tax_rate constant number（3,2） :=0.03; v_sal_tax v_sal%type;--参照v_sal的类型。 begin select ename,sal into v_ename,v_sal from emp where empno=&eno;--交互式变量eno v_sal_tax:=v_sal*v_tax_rate; DBMS_OUTPUT.PUT_LINE（'雇员名'||v_ename）; DBMS_OUTPUT.PUT_LINE（'工资'||v_sal）; DBMS_OUTPUT.PUT_LINE（'所得税'||v_sal_tax）; end; 8.rowtype类型，用于参照一个表的记录类型，该类型的变量能存储一条记录。 如： declare v_emp emp%ROWTYPE; begin select * into v_emp from emp where empno=&eno; DBMS_OUTPUT.PUT_LINE（'雇员名'||v_emp.ename）; DBMS_OUTPUT.PUT_LINE（'工资'||v_emp.sal）; DBMS_OUTPUT.PUT_LINE（'所得税'||v_emp.job）; end; 9.表类型（即相当于数组）： DECLARE type emp_table_type is table of emp.ename%type index by BINARY_INTEGER;--声明一个数组类型，参照emp.ename emptable emp_table_type;--声明一个数组 BEGIN select ename into emptable（-1） from emp where empno=&eno; emptable（0）:='jack'; dbms_output.put_line（'雇员名1'||emptable（-1））; dbms_output.put_line（'雇员名1'||emptable（0））; end; ORACLE单行函数： 字符函数，接受字符参数，输出字符或者数字 Lower（）：转换小写 Upper（）:转换大写 InitCap（）:首字母变大写函数 Contac（‘a’，’b’）:连接字符串，ab Length（‘abc’）:获取字符串的长度 instr（‘ename’，'a'）获取ename中a的位置，返回3 substr（‘job’，1,2）:截取字符串：jo TRIM（'S' FROM 'SSMITH'）:MITH（从SSMITH中除去S） 数字函数 ROUND（45.926, 2） 45.93 截取四舍五入 TRUNC（45.926, 2） 45.92截取不四舍五入 MOD（1600, 300） 100：取余 日期函数 select ename,（ MONTHS_BETWEEN （'01-9月-95','01-2月-95'）） m from emp;m为7 ADD_MONTHS （'11-1月-94',6）：1994/07/11 LAST_DAY（'01-9月-95'）：1995/09/30 日期转换： select to_char（hiredate,'YYYY-MM-DD'） a from emp;将日期转换为指定的格式（2011-07-03） PL/SQL基础 PL/SQL是ORACLE对标准数据库语言的扩展，ORACLE公司已经将PL/SQL整合到ORACLE 服务器和其他工具中了，近几年中更多的开发人员和DBA开始使用PL/SQL，本文将讲述PL/SQL基础语法，结构和组件、以及如何设计并执行一个PL/SQL程序。 从版本6开始PL/SQL就被可靠的整合到ORACLE中了，一旦掌握PL/SQL的优点以及其独有的数据管理的便利性，那么你很难想象ORACLE缺了PL/SQL的情形。PL/SQL 不是一个独立的产品，它是一个整合到ORACLE服务器和ORACLE工具中的技术，可以把PL/SQL看作ORACLE服务器内的一个引擎，sql语句执行者处理单个的sql语句，PL/SQL引擎处理PL/SQL程序块。当PL/SQL程序块在PL/SQL引擎处理时，ORACLE服务器中的SQL语句执行器处理pl/sql程序块中的SQL语句。 PL/SQL的优点如下： . PL/SQL是一种高性能的基于事务处理的语言，能运行在任何ORACLE环境中，支持所有数据处理命令。通过使用PL/SQL程序单元处理SQL的数据定义和数据控制元素。 . PL/SQL支持所有SQL数据类型和所有SQL函数，同时支持所有ORACLE对象类型 . PL/SQL块可以被命名和存储在ORACLE服务器中，同时也能被其他的PL/SQL程序或SQL命令调用，任何客户/服务器工具都能访问PL/SQL程序，具有很好的可重用性。 . 可以使用ORACLE数据工具管理存储在服务器中的PL/SQL程序的安全性。可以授权或撤销数据库其他用户访问PL/SQL程序的能力。 . PL/SQL代码可以使用任何ASCII文本编辑器编写，所以对任何ORACLE能够运行的操作系统都是非常便利的 . 对于SQL，ORACLE必须在同一时间处理每一条SQL语句，在网络环境下这就意味作每一个独立的调用都必须被oracle服务器处理，这就占用大量的服务器时间，同时导致网络拥挤。而PL/SQL是以整个语句块发给服务器，这就降低了网络拥挤。 PL/SQL块结构 PL/SQL是一种块结构的语言，组成PL/SQL程序的单元是逻辑块，一个PL/SQL 程序包含了一个或多个逻辑块，每个块都可以划分为三个部分（生命部分，执行部分，异常处理部分）。与其他语言相同，变量在使用之前必须声明，PL/SQL提供了独立的专门用于处理异常的部分，下面描述了PL/SQL块的不同部分： 声明部分（Declaration section） 声明部分包含了变量和常量的数据类型和初始值。这个部分是由关键字DECLARE开始，如果不需要声明变量或常量，那么可以忽略这一部分；需要说明的是游标的声明也在这一部分。 执行部分（Executable section） 执行部分是PL/SQL块中的指令部分，由关键字BEGIN开始，所有的可执行语句都放在这一部分，其他的PL/SQL块也可以放在这一部分。 异常处理部分（Exception section） 这一部分是可选的，在这一部分中处理异常或错误，对异常处理的详细讨论我们在后面进行。 PL/SQL块语法 [DECLARE] ---declaration statements BEGIN ---executable statements [EXCEPTION] ---exception statements END PL/SQL块中的每一条语句都必须以分号结束，SQL语句可以使多行的，但分号表示该语句的结束。一行中可以有多条SQL语句，他们之间以分号分隔。每一个PL/SQL块由BEGIN或DECLARE开始，以END结束。注释由--标示。 Oracle中过程介绍 oracle中的过程是一种命名的快，存储在oracle数据库中，相当于sql server中的存储过程。 下面是一个过程的定义： create or replace procedure query_emp--先删除重名的，后新建 （ v_no in emp.empno%type,--该参数是接受外来实参赋值的（in） v_name out emp.ename%type,--该参数是在过程内部赋值的（out） v_sal out emp.sal%type--该参数是在过程内部赋值的（out） ） is e_sal_error exception;--声明了一个异常 begin select ename,sal into v_name,v_sal from emp where empno=v_no; if v_sal >2500 then dbms_output.put_line（'雇员工资'||v_sal）; raise e_sal_error; else null; end if; exception when no_data_found then dbms_output.put_line（'该雇员不存在'）; when e_sal_error then dbms_output.put_line（'该雇员工资高于2500了'）; end query_emp; 过程的调用： a.在sql*plus中： SQL> variable a1 nvarchar2（16）; SQL> variable a2 number; SQL> execute query_emp（7788,:a1,:a2）; b.在其他pl/sql块中 declare v_al emp.ename%type; v_a1 emp.sal%type; begin query_emp（v_ename=>v_al,v_sal=>v_a1,v_no=>5678）; end; 我们创建的过程都是存放在数据字典中的，可以用以下语句来查询我们创建好的块： select * from user_source where name='QUERY_EMP'; 将过程的执行权限授予其他用户： Grant execute procname to username; 删除过程： Drop procedure procname; Oracle中函数的介绍 函数和过程一样也是一种命名的块，其主要作用是返回值 如： create or replace function get_salary_by_deptno （ v_dept_no in emp.deptno%type,--输入部门号 v_emp_cnt out number--输出部门人数 ） return number is Result number;--要返回的工资总数 begin select sum（sal）,count（*） into Result,v_emp_cnt from emp where deptno =v_dept_no; return（Result）; end get_salary_by_deptno; 在sql中调用函数以及在PL/SQL中调用函数和调用过程是一样的，只不过是调用函数必须放在表达式中： 删除函数 drop function get_salary_by_deptno Oracle游标介绍 简单说，Oracle游标提供了以一种对多行数据的查询结果集中的每行数据单独处理的方式，是设计交互式应用程序常用的编程接口。 游标分为显示游标和隐式游标，隐式游标是oracle提供的游标，显示游标是用户自定义的游标。 游标的使用步骤： 1）声名游标 2）打开游标 3）提取游标 4）关闭游标 使用游标的几种方式 1.一般调用方式 declare cursor c1（v_ss nvarchar2）--声名了一个个带参数的游标 is select * from stu where ss=v_ss;--游标的select语句 v_stu c1%rowtype;--基于游标定义的变量，可以存储游标取出的每一行数据 begin open c1（224）; loop fetch c1 into v_stu; exit when c1%notfound; dbms_output.put_line（'name'||v_stu.name||' '||'ss'||v_stu.ss）; end loop; close c1; end Cursor1; 拓展：（过程中使用游标） create or replace procedure Cursor1 （ v_ss nvarchar2 ） is cursor c1 is select * from stu where ss=v_ss; v_stu c1%rowtype; begin open c1; loop fetch c1 into v_stu; exit when c1%notfound; dbms_output.put_line（'name'||v_stu.name||' '||'ss'||v_stu.ss）; end loop; close c1; end Cursor1; 2.使用游标for循环来操作游标 create or replace procedure Cursor2 （ v_ss nvarchar2 ） is cursor c1 is select * from stu where ss=v_ss; begin for c1_rec in c1--c1_rec为能存储游标单行的记录变量 loop dbms_output.put_line（'name'||c1_rec.name||' '||'ss'||c1_rec.ss）; end loop; end Cursor2; 使用游标for循环，自动完成打开关闭游标的功能 3.使用游标来更新和删除数据。 create or replace procedure Cursor3 （ v_ss nvarchar2 ） is cursor c1 is select * from stu where ss=v_ss for update;--加一个for update begin for c1_rec in c1 loop update stu set name='nb' where current of c1; --逐行更新。 end loop; end Cursor3; Oracle触发器介绍 触发器是一种特殊的过程，与普通过程不同的是，过程需要用户显式的调用才能执行，而触发器则是当事件发生时oracle服务器触发执行。 要点： 触发时机：before和after，在动作前触发还是动作后触发 触发事件：insert update delete或是三个的逻辑组合 条件谓词：inserting（insert 触发时为true） updating [column x]（更新具体列是为true）deleting（删除时为true） 语法： CREATE [OR REPLACE] TRIGGER trigger_name AFTER | BEFORE | INSTEAD OF [INSERT] [[OR] UPDATE [OF column_list]] [[OR] DELETE] ON table_or_view_name [REFERENCING {OLD [AS] old / NEW [AS] new}] [FOR EACH ROW]—行级触发器，每操作一行都会触发一次 [WHEN （condition）] pl/sql_block; 如： create or replace trigger test1 before update on stud declare begin DBMS_OUTPUT.PUT_LINE（'更新了'）; if updating（'NAME'） THEN DBMS_OUTPUT.PUT_LINE（'更新了名字'）; end if; end test1; 当执行：update stud set sex='男' where ID=3;时会输出“更新了” 当执行update stud set NAME='测试' where ID=3;时会输出“更新了”和“更新了名字” 再如： create or replace trigger test1 before update on stud declare begin raise_application_error（-20001,'不让更新'）;--该触发器的作用是在更新之前告诉用户不让其更新。 end test1; 在数据字典中查看某个表中的触发器 select * from user_triggers where table_name='STUD'; 禁用触发器 Alter trigger test1 disable 激活触发器 Alter trigger test1 enable 禁用表上所有的触发器 Alter table dept（表名） disable all triggers 启用表上所有的触发器 Alter table dept（表名） enable all triggers 当改变表结构时重新编译触发器 Alter trigger test1 compile; 删除触发器 drop trigger test1; 包： 包类似于java中的类，用于将pl/sql中的函数或者过程以及变量进行封装 包分为两部分 1）包说明：里边放置的包的公有组件，如变量，常量，过程等 创建语法： create or replace package dd is -- Author : RR -- Created : 2011/7/15 20:38:53 -- Purpose : -- Public type declarations type <TypeName> is <Datatype>; -- Public constant declarations <ConstantName> constant <Datatype> := <Value>; -- Public variable declarations <VariableName> <Datatype>; -- Public function and procedure declarations function <FunctionName>（<Parameter> <Datatype>） return <Datatype>; end dd; 2）包体 放置具体的实现代码 删除包 Drop package package_name |
|
声明：文章版权归原作者所有 部分文章转自互联网 如有侵权请联系
[邮箱地址] 删除
|
