--plsql
set serveroutput on;
begin
dbms_output.put_line('Hello');
end;
--赋值
declare
v_name varchar2(20);
begin
v_name := 'myname';
dbms_output.put_line(v_name);
end;
--异常
declare
v_num number :=0;
begin
v_num := 2/v_num;
dbms_output.put_line(v_num);
exception
--其他所有的情况
when others then
dbms_output.put_line('error');
end;
-- 变量声明
declare
v_temp number(1);
v_count binary_integer := 0;
v_sal number(7,2) := 4000.00;
v_pi constant number(3,2) := 3.14;
v_valid boolean := false;
v_name varchar2(20) not null := 'MyName';
begin
-- boolean类型不能输出
dbms_output.put_line('v_temp value:' || v_valid);
end;
-- 使用%type属性 变量的属性
declare
v_empno number(4);
v_empno2 emp.empno%type;
v_empno v_empno2%type;
begin
dbms_output.put_line('Test');
end;
-- Table变量类型 类似java里的数组
declare
-- 定义一种新的类型
type type_table_emp_empno is table of emp.empno%type index by binary_integer;
v_empnos type_table_emp_empno;
begin
v_empnos(0) :=7369;
v_empnos(-1) :=9999;
v_empnos(2) :=7369;
dbms_output.put_line(v_empnos(-1));
end
--Record变量类型 类似java里的class
declare
type type_record_dept is record;
(
deptno dept.deptno%type,
dname dept.dname%type,
loc dept.loc%type
);
v_temp type_record_dept;
begin
v_temp.deptno := 50;
v_temp.dname := 'aaaa';
v_temp.loc := 'bj';
end;
-- 使用%rowtype声明record变量
declare
v_temp dept%rowtype
begin
v_temp.deptno := 50;
v_temp.dname := 'aaaa';
v_temp.loc := 'bj';
end;
-- sql语句的运用
-- plsql里的sql语句必须返回一条且只有一条结果记录
declare
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
select ename, sal into v_ename, v_sal from emp where empno = 7369;
dbms_output.put_line(v_ename || ' ' || v_sal);
end;
declare
v_ename emp%rowtype;
begin
select * into v_emp from emp where empno = 7369;
dbms_output.put_line(v_emp.ename);
end;
declare
v_deptno dept.deptno%type := 50;
v_dname dept.dname%type := 'aaa';
v_loc dept.loc%type := 'bj';
begin
insert into dept values (v_deptno, v_dname, v_loc);
commit;
end
declare
v_deptno emp.deptno%type := 50;
v_count number;
begin
-- (跟新了的条数)被影响
update emp set sal = sal/2 where deptno = v_deptno;
-- 1条记录被影响(最后产生了一个值)
select deptno into v_deptno from emp where empno = 7369;
-- 1条记录被影响(最后产生了一个值)
select count(*) into v_count from emp;
-- sql关键字 rowcount 刚刚影响的行数
dbms_output.put_line(sql%rowcount || '条记录被影响');
dbms_output.put_line(v_count || '条记录');
commit;
end;
--ddl语句
begin
execute immediate 'create table T (
nnn varchar2(20) default ''aaa''
)';
end;
-- if语句
declare
v_sal emp.sal%type;
begin
select sal into v_sal from emp where empno = 7369;
if (v_sal < 1200) then
dbms_output.put_line('low');
elsif (v_sal < 2000) then
dbms_output.put_line('middle');
else
dbms_output.put_line('high');
end if
end;
-- 循环
declare
i binary_integer := 1;
begin
loop
dbms_output.put_line(i);
i := i + 1;
exit when (i >= 11);
end loop;
end;
begin
for k in 1..10 loop
dbms_output.put_line(k);
end loop;
for k in reverse 1..10 loop
dbms_output.put_line(k);
end loop;
end;
-- 异常
declare
v_temp number(4);
begin
select empno into v_temp from emp where deptno = 10;
exception
when too_many_rows then
dbms_output.put_line('to many');
when others then
dbms_output.put_line('error');
end;
declare
v_temp number(4);
begin
select empno into v_temp from emp where empno = 2222;
exception
when no_data_found then
dbms_output.put_line('empty');
end;
create table errorlog
(
id number primary key,
errcode number,
errmsg varchar2(1024),
errdate date
);
create sequence seq_errorlog_id start with 1 increment by 1;
declare
v_deptno dept.deptno%type := 10;
v_errcode number;
v_errmsg varchar2(1024);
begin
delete from dept where deptno = v_deptno;
commit;
exception
when others then
rollback;
v_errcode := SQLCODE;
v_errmsg := SQLERRM;
insert into errorlog values(seq_errorlog_id.nextval, v_errcode, v_errmsg, sysdate);
commit;
end;
-- 游标
-- 只在查询结果集上的游标 RS
declare
cursor c is
select * from emp;
v_emp c%rowtype;
begin
-- open时才去查询
open c;
-- 拿出c指的记录,放到v_emp中,游标自动往下移一条
fetch c into v_emp;
dbms_output.put_line(v_emp.ename);
close c;
end;
-- 带循环的游标
declare
cursor c is
select * from emp;
v_emp c%rowtype;
begin
open c;
loop
fetch c into v_emp;
exit when (c%notfound);
dbms_output.put_line(v_emp.ename);
end loop;
close c;
end;
-- 如果dbms_output.put_line(v_emp.ename);在先,最后打出两便一样的结果
declare
cursor c is
select * from emp;
v_emp c%rowtype;
begin
open c;
fetch c into v_emp;
while (c%found) loop
dbms_output.put_line(v_emp.ename);
fetch c into v_emp;
end loop;
close c;
end;
-- c不用打开关闭fetch
declare
cursor c is
select * from emp;
begin
for v_emp in c loop
dbms_output.put_line(v_emp.ename);
end loop;
end;
-- 带参数的游标
declare
cursor c(v_deptno emp.deptno%type, v_job emp.job%type)
is
select ename, sal from emp where deptno = v_deptno and job = v_job;
begin
for v_temp in c(30, 'CLERK') loop
dbms_output.put_line(v_temp.ename);
end loop;
end;
--可更新的游标
declare
cursor c
is
select * from emp for update;
begin
for v_temp in c loop
if(v_temp.sal < 2000) then
update emp set sal = sal * 2 where current of c;
elsif (v_temp.sal = 5000) then
delete from emp where current of c;
end if;
end loop;
commit;
end;
-- 存储过程
create or replace procedure p
is
cursor c is
select * from emp for update;
begin
for v_emp in c loop
if (v_emp.deptno = 10) then
update emp set sal = sal + 10 where current of c;
elsif (v_emp.deptno = 20) then
update emp set sal = sal + 20 where current of c;
else
update emp set sal = sal + 50 where current of c;
end if;
end loop;
commit;
end;
exec p;
begin
p;
end;
show error
-- 带参数的存储过程,没写in默认是in
create or replace procedure p
(v_a in number, v_b number, v_ret out number, v_temp in out number)
is
begin
if(v_a > v_b) then
v_ret := v_a;
else
v_ret := v_b;
end if;
v_temp := v_temp + 1;
end;
declare
v_a number := 3;
v_b number := 4;
v_ret number;
v_temp number := 5;
begin
p(v_a, v_b, v_ret, v_temp);
dbms_output.put_line(v_ret);
dbms_output.put_line(v_temp);
end;
-- 函数
create or replace function sal_tax
(v_sal number)
return number
is
begin
if(v_sal < 2000) then
return 0.10;
elsif(v_sal < 2750) then
return 0.15;
else
return 0.20;
end if;
end;
--触发器
create table emp_log
(
nuame varchar2(20),
action varchar2(10),
ation date
);
-- after在事件后触发触发器
create or replace trigger trig
after insert or delete or update on emp
-- after insert or delete or update on emp for each row
begin
if inserting then
insert into emp_log values (USER, 'insert', sysdate);
elsif updating then
insert into emp_log values (USER, 'update', sysdate);
elsif deleting then
insert into emp_log values (USER, 'delete', sysdate);
end if;
end;
create or replace trigger tirg
after update on dept
for each row
begin
update emp set deptno = :NEW.deptno where deptno = :OLD.deptno;
end;
update dept set deptno = 99 where deptno = 10;
分享到:
相关推荐
PL/SQL, Oracle's procedural extension of SQL, is an advanced fourth-generation programming language (4GL). It offers modern features such as data encapsulation, overloading, collection types, ...
pl/sql例题代码pl/sql例题代码pl/sql例题代码
pl/sql developer11.0下载 pl/sql developer11.0下载 pl/sql developer11.0下载
PL/SQL Developer是Oracle数据库当前最流行的开发工具之一,它在ORACLE数据库开发设计方面功能强大,使用方便,但是数据库管理方面一直比较欠缺。 DBATools For PL/SQL Developer 是一款PL/SQL Developer的辅助插件...
PL/SQL是Oracle对标准数据库语言的扩展,Oracle公司已经将PL/SQL整合到Oracle 服务器和其他工具中了,近几年中更多的开发人员和DBA开始使用PL/SQL,本教程将以循速渐进的方式讲述PL/SQL基础语法,结构和组件、以及...
《Oracle PL/SQL程序设计(第5版)(套装上下册)》基于Oracle数据库11g,从PL/SQL编程、PL/SQL程序结构、PL/SQL程序数据、PL/SQL中的SQL、PL/SQL应用构建、高级PL/SQL主题这6个方面详细系统地讨论了PL/SQL以及如何...
1、PL/SQL简介 2、PL/SQL基础 3、记录和表 4、在PL/SQL中使用SQL 5、内置SQL函数 6、游标 7、过程和函数 ...
PL/SQL Developer 8.0.3 1510 含注册机 PL/SQL Developer is an Integrated Development Environment that is specifically targeted at the development of stored program units for Oracle Databases. Over ...
一个对数据库的操作工具PL/SQL,能够对ORACLE\SQL进行很好的帮助操作!
PL/SQL Developer是一种集成的开发环境,专门用于开发、测试、调试和优化Oracle PL/SQL存储程序单元,比如触发器等。PL/SQL Developer功能十分全面,大大缩短了程序员的开发周期。强大的PL/SQL编辑器,完善的Debugger...
用PL/SQL 是 Oracle公司在标准 SQL 语言上进行一定的扩展而形成的一种数据库语言。它寄托于传统的 SQL 语句,同时又在功能上做了不少的扩充。PL/SQL 有着太多的优势,甚至让SQL 在它面前也黯然失色。 PL/SQL ...
PL/SQL 程序设计 本章主要重点: PL/SQL概述 PL/SQL块结构 PL/SQL流程 运算符和表达式 游标 异常处理 数据库存储过程和函数 包 触发器
Get PL/SQL programs up and running quickly, with clear instructions for executing, tracing, testing, debugging, and managing PL/SQL code Optimize PL/SQL performance with the aid of a brand-new ...
pl/sql插件,下载,解压,将一个文件夹和一个dll文件直接放在pl/sql安装目录的 plugin目录下,打开pl/sql便可在工具栏看到plugin,然后可以按照自己的需求设置
PL/SQL本身涉及的知识点浩瀚、庞杂,初学者根本无法依靠自身能力理清头绪,学习成本极高.本书对知识点进行了体系化的梳理,化繁杂为有序,突出重点,直指核心,循序渐进,尽可能为学习者提供“捷径”,仅仅只是这...
PL/SQL Developer是一个集成开发环境,专门开发面向Oracle数据库的应用。PL/SQL也是一种程序语言,叫做过程化SQL语言(Procedural Language/SQL)。PL/SQL是Oracle数据库对SQL语句的扩展。在普通SQL语句的使用上增加...
pl/sql存储过程,函数,游标,以及存储过程中的基础知识,绝对值得你收藏的经典题目,让你的pl/sql得到最大的锻炼。让你的数据库逻辑更加灵活。
SQL Exporter did not export very old dates in date format - SQL Exporter could export floats with comma as decimal separator <br>PL/SQL Developer主要特性: PL/SQL编辑器,功能强大——该编辑器...
很多时候你是不是为了32为的plsql的各种复杂配置烦恼,不要紧,现在下载64位的pl/sql,不需要繁琐的配置,让你更轻松
PL/SQL Developer是一个集成开发环境,专门开发面向Oracle数据库的应用。PL/SQL也是一种程序语言,叫做过程化SQL语言(Procedural Language/SQL)。PL/SQL是Oracle数据库对SQL语句的扩展。在普通SQL语句的使用上增加...