- --设置数据库输出,默认为关闭,每次新打开窗口都要重新设置
- set serveroutput on
- --调用 包 函数 参数
- execute dbms_output.put_line('hello world');
- --或者用call调用,相当于java中的调试程序打桩
- call dbms_output.put_line('hello world');
- --pl语句块是pl/sql里最小的编程块,其中可以再嵌套begin end
- begin
- dbms_output.put_line('Hello World');
- dbms_output.put_line('2*3='||(2*3));
- dbms_output.put_line('what''s');
- end;
- --如何声明变量,所有变量必须再declare中声明,程序中不允许声明
- --没有初始化的变量默认值为null,屏幕上null是看不见的,命名习惯:一般变量以v_开头
- --注意number也能存小数,最长38位,所以以后建议整数都用binary_integer存
- --long是字符类型,boolean类型补能打印
- --标准变量类型:数字,字符,时间,布尔
- declare
- v_number1 number ;
- v_number2 number(3,2) ;
- v_number3 binary_integer :=1;
- v_name varchar2(20) :='kettas';
- v_date date :=sysdate;
- v_long long :='ni hao';
- v_b boolean := true;
- begin
- if (v_number1 is null) then
- dbms_output.put_line( 'hello');
- end if;
- dbms_output.put_line(v_number1);
- dbms_output.put_line(v_number2);
- dbms_output.put_line(v_number3);
- dbms_output.put_line(v_name);
- dbms_output.put_line(v_date);
- dbms_output.put_line(v_long);
- --dbms_output.put_line(v_b);
- end;
- /
- --组合类型:record table
- --record类型最常用,声明的时候可以加not null,但必须给初始值
- --如果record类型一致可以相互赋值,如果类型不同,里面的字段恰好相同,不能互相赋值
- declare
- type t_first is record(
- id number(3),
- name varchar2(20)
- );
- v_first t_first;
- begin
- v_first.id:=1;
- v_first.name:='ws';
- dbms_output.put_line(v_first.id);
- dbms_output.put_line(v_first.name);
- end;
- /
- --考虑一下,oracle中赋值方式是拷贝还是引用方式
- declare
- --v_number1 number:=1;
- --v_number2 number;
- type t_first is record(
- id number,
- name varchar2(20)
- );
- v_first t_first;
- v_second t_first;
- begin
- --v_number2:=v_number1;
- --v_number1:=2;
- --dbms_output.put_line(v_number1);
- --dbms_output.put_line(v_number2);
- v_first.id:=1;
- v_first.name:='susu';
- --v_second:=v_first;
- v_second.id:=v_first.id;
- v_second.name:=v_first.name;
- v_first.id:=2;
- v_first.name:='kettas';
- dbms_output.put_line(v_first.id);
- dbms_output.put_line(v_first.name);
- dbms_output.put_line(v_second.id);
- dbms_output.put_line(v_second.name);
- end;
- /
- -------------------------
- --table类型,相当于java中的map,就是一个可变长的数组,key必须是整数,可以是负数,value可以是标量,也可以是record
- --可以不按顺序赋值,但必须先赋值后使用
- declare
- type t_tb is table of varchar2(20) index by binary_integer;
- v_tb t_tb;
- begin
- v_tb(100):='hello';
- v_tb(98):='world';
- dbms_output.put_line(v_tb(100));
- dbms_output.put_line(v_tb(98));
- end;
- declare
- type t_rd is record(id number,name varchar2(20));
- type t_tb is table of t_rd index by binary_integer;
- v_tb2 t_tb;
- begin
- v_tb2(100).id:=1;
- v_tb2(100).name:='hello';
- --dbms_output.put_line(v_tb2(100).id);
- --dbms_output.put_line(v_tb2(100).name);
- dbms_output.put_line(v_tb2(100).id||' '||v_tb2(100).name);
- end;
- /
- --%type 和 %rowtype 以及如何从数据库把数据取回来
- create table student33(
- id number,
- name varchar2(20),
- age number(3,0)
- );
- insert into student(id,name,age) values(1,'susu',23);
- --查找一个字段的变量
- declare
- v_name varchar2(20);
- v_name2 student.name%type;
- begin
- select name into v_name2 from student33 where rownum=1;
- dbms_output.put_line(v_name2);
- end;
- /
- --查找多个字段的变量
- declare
- v_id student33.id%type;
- v_name student33.name%type;
- v_age student33.age%type;
- begin
- select id,name,age into v_id,v_name,v_age from student33 where rownum=1;
- dbms_output.put_line(v_id||' '||v_name||' '||v_age);
- end;
- /
- --查找一个类型的变量,推荐用*
- declare
- v_student student33%rowtype;
- begin
- select * into v_student from student33 where rownum=1;
- dbms_output.put_line(v_student.id||' '||v_student.name||' '||v_student.age);
- end;
- /
- --也可以按字段查找,但是字段顺序必须一样,不推荐这样做
- declare
- v_student student33%rowtype;
- begin
- select id,name,age into v_student from student33 where rownum=1;
- dbms_output.put_line(v_student.id||' '||v_student.name||' '||v_student.age);
- end;
- /
- declare
- v_student student33%rowtype;
- begin
- select id,name,age into v_student.id,v_student.name,v_student.age from student33 where id=1;
- --select * into v_student.id,v_student.name,v_student.age from student33 where id=1;
- dbms_output.put_line();
- end;
- /
- --注意:insert,update,delete,select都可以,create table,drop table不行
- --dpl,dml,和流程控制可以在pl/sql里用,ddl不行
- declare
- v_name student33.name%type:='wang';
- begin
- insert into student33(id,name,age) values(2,v_name,26);
- end;
- /
- --下边这种方式也可以
- begin
- insert into student33(id,name,age) values(5,'hehe',25);
- end;
- /
- declare
- v_name student.name%type:='hexian';
- begin
- update student set name=v_name where id=1;
- end;
- /
- begin
- update student33 set name='qinaide' where id=2;
- end;
- /
- --delete
- begin
- delete from student33 where id=5;
- end;
- /
- --变量的可见空间
- declare
- v_i1 binary_integer:=1;
- begin
- declare
- v_i2 binary_integer:=2;
- begin
- dbms_output.put_line(v_i1);
- dbms_output.put_line(v_i2);
- end;
- dbms_output.put_line(v_i1);
- --dbms_output.put_line(v_i2);
- end;
- /
- ------------------------流程控制
- --if判断
- declare
- v_b boolean:=true;
- begin if v_b then
- dbms_output.put_line('ok');
- end if;
- end;
- /
- --if else
- declare
- v_b boolean:=true;
- begin
- if v_b then
- dbms_output.put_line('ok');
- else
- dbms_output.put_line('false');
- end if;
- end;
- /
- --if elsif else
- declare
- v_name varchar2(20):='ws';
- begin
- if v_name='0701' then
- dbms_output.put_line('0701');
- elsif v_name='ws' then
- dbms_output.put_line('ws');
- else
- dbms_output.put_line('false');
- end if;
- end;
- /
- --loop循环,注意推出exit是推出循环,而不是推出整个代码块
- declare
- v_i binary_integer:=0;
- begin
- loop
- if v_i>10 then
- exit;
- end if;
- v_i:=v_i+1;
- dbms_output.put_line('hehe');
- end loop;
- dbms_output.put_line('over');
- end;
- /
- --更简单的写法
- declare
- v_i binary_integer :=0;
- begin
- loop
- exit when v_i>10;
- v_i :=v_i+1;
- dbms_output.put_line('hehe');
- end loop;
- dbms_output.put_line('over');
- end;
- /
- --while循环
- declare
- v_i binary_integer:=0;
- begin
- while v_i<10 loop
- dbms_output.put_line('hello'||v_i );
- v_i:=v_i+1;
- end loop;
- dbms_output.put_line('over');
- end;
- /
- --for循环,注意不需要声明变量
- begin
- for v_i in 0..10 loop
- dbms_output.put_line('hello'||v_i);
- end loop;
- dbms_output.put_line('over');
- end;
- /
- --练习1用循环往student里插入30条记录
- --要求:id为0,1,2...
- -- name为kettas0,kettas1,kettas2...
- -- age为11,12,13...
- --练习2,假设不知道数据库里的数据规则和数量,
- --把所有的student33数据打印到终端
- --问题1答案
- declare
- v_student student33%rowtype;
- begin
- delete from student33;
- for v_i in 0..20 loop
- insert v_student into student33(id,name,age) values(v_i,'kettas'||v_i,10+v_i);
- end loop;
- end;
- /
- --也可以这样
- begin
- delete from student33;
- for v_i in 0..20 loop
- insert into student33 values(v_i,'susu'||v_i,18+v_i);
- end loop;
- end;
- --问题2答案
- --rownum是伪列,在表里没有
- --数据库先是执行from student33遍历student33表,如果没有where条件过滤,则先做成一个结果集,然后再看select
- --后面的条件挑出合适的字段形成最后的结果集,如果有where条件,则不符合条件的就会从第一个结果集中删除
- --后面的数据继续加进来判断,所以如果直接写rownum=2,或者rownum>10这样的语句就查不出数据
- --可以用一个子查询解决
- select rownum,id from student33 where rownum=2;
- declare
- v_number binary_integer;
- v_student student33%rowtype;
- begin
- select count(*) into v_number from student33;
- for i in 1..v_number loop
- select id,name,age into v_student from(
- select rownum rn,id,name,age from student33
- )where rn=i;
- dbms_output.put_line('id: '||v_student.id||' name:'||v_student.name);
- end loop;
- end;
- --异常的定义使用
- begin
- dbms_output.put_line(1/0);
- exception
- when others then
- dbms_output.put_line('error');
- end;
- declare
- e_myException exception;
- begin
- dbms_output.put_line('hello');
- raise e_myException;--raise抛出异常,用此关键字向外抛异常
- dbms_output.put_line('world');
- dbms_output.put_line(1/0);
- exception
- when e_myException then
- dbms_output.put_line(sqlcode);--当前会话执行状态,错误编码
- dbms_output.put_line(sqlerrm);--当前错误信息
- dbms_output.put_line('my error');
- when others then
- dbms_output.put_line('error');
- end;
- --error表,每次有异常可以加到表里,相当于log日志
- --id,code,errm,information,create date
- --目前先这么写,学完存储过程后再改成一个过程,用的时候只需要调用即可
- drop table error;
- create table error(
- id number,
- code number,
- errm varchar2(4000),
- information varchar2(4000),
- create_date date
- );
- drop sequence my_key;
- create sequence my_key;
- insert into error values(my_key.nextVal,1,'xxx','xxxxx',sysdate);
- --具体做法
- declare
- e_myException exception;
- begin
- dbms_output.put_line('hello');
- raise e_myException;
- dbms_output.put_line('world');
- dbms_output.put_line(1/0);
- exception
- when e_myException then
- declare
- v_code binary_integer;
- v_errm varchar2(4000);
- begin
- v_code:=sqlcode;
- v_errm:=sqlerrm;
- insert into error values(my_key.nextVal,v_code,v_errm,'e_myException',sysdate);
- end;
- when others then
- dbms_output.put_line('error');
- end;
- /
- --cursor 游标(结果集)用于提取多行数据
- --定义后不会有数据,使用后才有
- --一旦游标被打开,就无法再次打开(可以先关闭,再打开)
- declare
- cursor c_student is
- select * from student33;
- begin
- open c_student;
- close c_student;
- end;
- --第二种游标的定义方式,用变量控制结果集的数量
- declare
- v_id binary_integer;
- cursor c_student is select * from student33 where id>v_id;
- begin
- v_id:=10;
- open c_student;
- close c_student;
- end;
- /
- --第三种游标的定义方式,带参数的游标,用的最多
- declare
- cursor c_student(v_id binary_integer) is select * from student33 where id>v_id;
- begin
- open c_student(10);
- close c_student;
- end;
- /
- --游标的使用,一定别忘了关游标
- declare
- v_student student33%rowtype;
- cursor c_student(v_id binary_integer) is select * from student33 where id>v_id;
- begin
- open c_student(10);
- fetch c_student into v_student;
- close c_student;
- dbms_output.put_line(v_student.name);
- end;
- --如何遍历游标fetch
- --游标的属性 %found,%notfound,%isopen,%rowcount
- --%found:若前面的fetch语句返回一行数据,则%found返回true,如果对未打开的游标使用则报ORA-1001异常
- --%notfound,与%found行为相反
- --%isopen,判断游标是否打开
- --%rowcount:当前游标的指针位移量,到目前位置游标所检索的数据行的个数,若未打开就引用,返回ORA-1001
- --loop方式遍历游标
- declare
- v_student student33%rowtype;
- cursor c_student(v_id binary_integer) is select * from student33 where id>v_id;
- begin
- open c_student(10);
- loop
- fetch c_student into v_student;
- exit when c_student%notfound;
- dbms_output.put_line('name:'||v_student.name);
- end loop;
- close c_student;
- end;
- --while循环遍历游标,注意,第一次游标刚打开就fetch,%found为null,进不去循环
- --如何解决:while nvl(c_student%found,true) loop
- declare
- v_student student33%rowtype;
- cursor c_student(v_id binary_integer) is select * from student33 where id>v_id;
- begin
- open c_student(10);
- while c_student%found is null or c_student%found loop
- fetch c_student into v_student;
- dbms_output.put_line('name:'||v_student.name);
- end loop;
- close c_student;
- end;
- --for循环遍历,最简单,用的最多,不需要声明v_student,打开关闭游标,fetch
- declare
- cursor c_student(v_id binary_integer) is select * from student33 where id>v_id;
- begin
- for v_student in c_student(10) loop
- dbms_output.put_line('name:'||v_student.name);
- end loop;
- end;
- --goto例子,不推荐使用goto,会使程序结构变乱
- declare
- i binary_integer:=0;
- begin
- if i=0 then goto hello;end if;
- <<hello>>
- begin
- dbms_output.put_line('hello');
- goto over;
- end;
- <<world>>
- begin
- dbms_output.put_line('world');
- goto over;
- end;
- <<over>>
- dbms_output.put_line('over');
- end;
- --pl/sql day2
- --存储过程:把匿名块存储下来
- --匿名块运行后不会在数据库留下
- declare
- v_content varchar2(4000):='hello';
- begin
- dbms_output.put_line(v_content);
- end;
- --创建或修改一个过程,ws_println(形式参数,声明类型即可),as可以替换成is
- --变量可以声明在as和begin之间
- create or replace procedure ws_println(v_content varchar2)
- as
- begin
- dbms_output.put_line(v_content);
- end;
- --调用有参过程
- execute ws_println('ws');
- call ws_println('ws');
- begin
- ws_println('ws');
- end;
- --删除一个过程
- drop procedure ws_println;
- --查看数据库里的过程
- select * from user_procedures;
- select object_name,procedure_name from user_procedures;
- desc user_procedures;
- --procedure里可以调用其它的procedure
- create or replace procedure say_hello
- is
- begin
- ws_println('hello');
- end;
- --或者这样也可以
- create or replace procedure say_hello
- as
- begin
- ws_println('hi susu');
- end;
- --调用无参过程的方式:
- execute say_hello;
- call say_hello();
- begin
- say_hello;
- end;
- --一个参数的存储过程
- --输入参数in,输入参数不能进行赋值,默认不写就是in
- --存储过程没有重载,这个有参的say_hello会替代上面的无参say_hello
- create or replace procedure say_hello(v_name in varchar2)
- as
- begin
- --v_name:='a';
- ws_println('hello '||v_name);
- end;
- --调用有参的存储过程的两种方式
- begin
- say_hello('susu');--方式1
- say_hello(v_name=>'xianbin');--方式2
- end;
- --多个参数的存储过程
- create or replace procedure say_hello
- (v_first_name in varchar2,v_last_name in varchar2)
- as
- begin
- ws_println('hello '||v_first_name||'.'||v_last_name);
- end;
- --调用多个参数的两种方式
- --方式一,这种方式需按顺序给出值,否则出现异常
- begin
- say_hello('he','susu');
- end;
- --方式二,用指定型参名的方式调用可以不按顺序赋值
- begin
- say_hello(v_last_name=>'he',v_first_name=>'susu');
- end;
- --out输出参数,用于利用存储过程给一个或多个变量赋值,类似于返回值
- create or replace procedure say_hello
- (v_name in varchar2,v_content out varchar2)
- begin
- v_content:='hello'||v_name;
- end;
- --调用
- declare
- v_con varchar2(200);
- v_in varchar2(20):='wang';
- begin
- say_hello(v_in,v_con);
- ws_println(v_con);
- end;
- --in out参数,既赋值,又取值
- create or replace procedure say_hello(v_name in out varchar2)
- as
- begin
- v_name:='hi '||v_name;
- end;
- --调用
- declare
- v_inout varchar2(20):='wangsu';
- begin
- say_hello(v_inout);
- ws_println(v_inout);
- end;
- --缺省参数
- create or replace procedure say_hello(v_name varchar2 default 'susu',v_content varchar2 default 'hello')
- as
- begin
- ws_println(v_name||' '||v_content);
- end;
- --调用,用指明形参名的方式调用更好
- begin
- say_hello();
- end;
- begin
- say_hello(v_name=>'wangsu');
- end;
- begin
- say_hello(v_content=>'hi');
- end;
- --function函数
- --过程和函数都以编译后的形式存放在数据库中,函数可以没有参数也可以有多个参数并有一个返回值。过程
- --有零个或多个参数,没有返回值。函数和过程都可以通过参数列表接收或返回零个或多个值,函数和过程的
- --主要区别不在于返回值,而在于他们的调用方式,过程是作为一个独立执行语句调用的,函数以合法的表达
- --式的方式调用
- create or replace function func(v_name in varchar2)
- return varchar2
- is
- begin
- return(v_name||' hello');
- end;
- --调用
- declare
- v_name varchar2(20);
- begin
- v_name:=func('susu');
- ws_println(v_name);
- end;
- --out 参数的函数
- create or replace function func(v_name in varchar2,v_content out varchar2)
- return varchar2
- is
- begin
- v_content:=v_name||' hello';
- return v_content;
- end;
- --调用
- declare
- v_name varchar2(20);
- v_name1 varchar2(20);
- begin
- v_name1:=func('susu',v_name);
- ws_println(v_name);
- ws_println(v_name1);
- end;
- --in out 参数
- create or replace function func(v_name in out varchar2)
- return varchar2
- is
- begin
- v_name:=v_name||' hello';
- return 'ws';
- end;
- --调用
- declare
- v_inout varchar2(20):='world';
- v_ret varchar2(20);
- begin
- v_ret:=func(v_inout);
- ws_println(v_inout);
- ws_println(v_ret);
- end;
plsql学习
最新推荐文章于 2024-04-23 00:19:39 发布