请选择 进入手机版 | 继续访问电脑版
 找回密码
 立即注册

QQ登录

只需一步,快速开始

查看: 2832|回复: 18

pl/sql简介及代码块

[复制链接]

70

主题

70

帖子

246

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
246
发表于 2019-5-31 16:13:27 | 显示全部楼层 |阅读模式
pl/sql procedural language 过程化语言,是Oracle在标准的SQL基础之上的扩展,可以定义变量、使用逻辑控制语句。默认Oracle一次只能处理一条语句,如果使用PL/SQL,可以使用块组织多条语句,作为一个整体执行,只向数据库服务器发送一次请求调用。块,是pl/sql最基本的单元,由块可组成不同的程序形式,如匿名块、存储过程、函数、包、触发器等。本文将介绍块的使用。
1、系统、软件和约束条件
  • win10
  • oracle11g
  • sql developer
2、块
  • 块的组成:定义部分【可选】、执行部分【必选】、异常处理部分【可选】
  • 1 声明一个变量,打印
  1. <span class="hljs-built_in">set</span> serveroutput on;--打开输出开关【每个终端执行一次即可】
  2. <span class="hljs-built_in">declare</span> --申明部分
  3.   v_name varchar(20); --声明一个变量v_name
  4. begin --执行部分开始
  5.   v_name := <span class="hljs-string">'zhangli'</span>; --给v_name赋值为zhangli
  6.   dbms_output.put_line(v_name);--打印变量到控制台
  7. end; --执行部分结束
复制代码
  • 2 使用type声明标量,接受键盘输入,打印
  1. <span class="hljs-built_in">declare</span>
  2.   v_name emp.ename%<span class="hljs-built_in">type</span>; --声明一个简单变量v_name,以<span class="hljs-built_in">type</span>方式赋予类型
  3. begin --执行部分开始
  4.   select ename into v_name from emp <span class="hljs-built_in">where</span> empno=&no;--接受键盘输入,将用户名称赋予v_name
  5.   dbms_output.put_line(v_name);--打印变量到控制台
  6. end; --执行部分结束
复制代码
  • 3 使用rowtype声明变量,接受键盘输入,打印
  1. declare
  2.   v_emp_record emp%rowtype; --声明一个简单变量v_emp_record,以rowtype方式赋予类型
  3. <span class="hljs-keyword">begin</span> --执行部分开始
  4.   select * into v_emp_record from emp where empno=&no;--接受键盘输入,将用户名称赋予v_emp_record
  5.   dbms_output.put_line(v_emp_record .ename<span class="hljs-params">||</span>v_emp_record.sal);--打印变量到控制台
  6. <span class="hljs-keyword">end</span>; --执行部分结束
复制代码
  • 4 申明复合类型,接受键盘输入,打印
  1. <span class="hljs-built_in">declare</span>
  2.   <span class="hljs-built_in">type</span> emp_record_type is record (name emp.ename%<span class="hljs-built_in">type</span>,sal emp.sal%<span class="hljs-built_in">type</span>); --定义记录类型
  3.   v_emp_record emp_record_type; --定义记录类型的变量
  4. begin --执行部分开始
  5.   select ename,sal into v_emp_record from emp <span class="hljs-built_in">where</span> empno=&no;--接受键盘输入,将用户名称赋予v_emp_record
  6.   dbms_output.put_line(v_emp_record .name||v_emp_record.sal);--打印变量到控制台
  7. end; --执行部分结束
复制代码
  • 5 pl/sql索引表
  1. <span class="hljs-built_in">declare</span>
  2.   <span class="hljs-built_in">type</span> emp_table_type is table of emp.ename%<span class="hljs-built_in">type</span> index by binary_integer;  --定义表类型
  3.   v_emp_table emp_table_type;  --定义表类型的变量
  4. begin
  5.   select ename into v_emp_table(-20) from emp <span class="hljs-built_in">where</span> empno=7369;--下表是整型,随意指定
  6.   select ename into v_emp_table(13) from emp <span class="hljs-built_in">where</span> empno=7654;
  7.   dbms_output.put_line(<span class="hljs-string">'7369姓名:'</span>||v_emp_table(-20)||<span class="hljs-string">',7654姓名:'</span>||v_emp_table(13));
  8. end;
复制代码
  • 6 pl/sql嵌套表
  1. <span class="hljs-built_in">declare</span>
  2.   <span class="hljs-built_in">type</span> name_table is table of emp.ename%<span class="hljs-built_in">type</span>;--声明一个表类型
  3.   v_name_table name_table;
  4. begin
  5.   v_name_table:=name_table(<span class="hljs-string">'abc'</span>,<span class="hljs-string">'efg'</span>);
  6.   dbms_output.put_line(v_name_table(1));
  7. end;
复制代码
  • 7 varray数组
  1. declare
  2.   type a_array is varray(<span class="hljs-number">3</span>) of varchar2(<span class="hljs-number">10</span>);  --定义数组类型
  3.   v_names a_array; --定义数组类型的变量
  4. <span class="hljs-keyword">begin</span>
  5.   v_names <span class="hljs-symbol">:</span>=a_array(<span class="hljs-string">'zhangli'</span>,<span class="hljs-string">'ali'</span>,<span class="hljs-string">'xiaoli'</span>); --为数组元素赋值
  6.   dbms_output.put_line(v_names (<span class="hljs-number">1</span>)<span class="hljs-params">||</span><span class="hljs-string">','</span><span class="hljs-params">||</span>v_names (<span class="hljs-number">2</span>)<span class="hljs-params">||</span><span class="hljs-string">','</span><span class="hljs-params">||</span>v_names (<span class="hljs-number">3</span>));
  7. <span class="hljs-keyword">end</span>;
复制代码
  • 8 if表达式 ,输入雇员编号,查询工资,如果工资大于3000,显示白领;如果工资大于1500小于3000,显示蓝领,否则显示灰领
  1. declare
  2.   v_sal emp.sal%type;
  3. <span class="hljs-keyword">begin</span>
  4.   select sal into v_sal from emp where empno=&no;
  5.   <span class="hljs-keyword">if</span> v_sal>=<span class="hljs-number">3000</span> <span class="hljs-keyword">then</span>
  6.     dbms_output.put_line(<span class="hljs-string">'白领'</span>);
  7.   <span class="hljs-keyword">elsif</span> v_sal>=<span class="hljs-number">1500</span> <span class="hljs-keyword">and</span> v_sal<<span class="hljs-number">3000</span> <span class="hljs-keyword">then</span>
  8.     dbms_output.put_line(<span class="hljs-string">'蓝领'</span>);
  9.   <span class="hljs-keyword">else</span>
  10.     dbms_output.put_line(<span class="hljs-string">'灰领'</span>);
  11.   <span class="hljs-keyword">end</span> <span class="hljs-keyword">if</span>;
  12. <span class="hljs-keyword">end</span>;
复制代码
  • 9 case表达式,输入成绩等级,如果是A,输出perfect,如果是B输出very good,如果是C输出good,否则输出bad
  1. declare
  2.   v_grade varchar2(<span class="hljs-number">1</span>);
  3.   v_comm varchar2(<span class="hljs-number">20</span>);
  4. <span class="hljs-keyword">begin</span>
  5.   <span class="hljs-symbol">v_grade:</span>=upper(<span class="hljs-string">'&grade'</span>);
  6.   <span class="hljs-keyword">case</span> v_grade
  7.     <span class="hljs-keyword">when</span> <span class="hljs-string">'A'</span> <span class="hljs-keyword">then</span> <span class="hljs-symbol">v_comm:</span>=<span class="hljs-string">'perfect'</span>;
  8.     <span class="hljs-keyword">when</span> <span class="hljs-string">'B'</span> <span class="hljs-keyword">then</span> <span class="hljs-symbol">v_comm:</span>=<span class="hljs-string">'very good'</span>;
  9.     <span class="hljs-keyword">when</span> <span class="hljs-string">'C'</span> <span class="hljs-keyword">then</span> <span class="hljs-symbol">v_comm:</span>=<span class="hljs-string">'good'</span>;
  10.     <span class="hljs-keyword">else</span>  <span class="hljs-symbol">v_comm:</span>=<span class="hljs-string">'bad'</span>;
  11.   <span class="hljs-keyword">end</span> <span class="hljs-keyword">case</span>;
  12.   dbms_output.put_line(v_comm);
  13. <span class="hljs-keyword">end</span>;   
复制代码
  • 10 loop循环,从1打印到20
  1. declare
  2.   v_i number(<span class="hljs-number">2</span>)<span class="hljs-symbol">:</span>=<span class="hljs-number">1</span>;
  3. <span class="hljs-keyword">begin</span>
  4.   loop
  5.     dbms_output.put_line(v_i);
  6.     <span class="hljs-symbol">v_i:</span>=v_i+<span class="hljs-number">1</span>;
  7.     exit <span class="hljs-keyword">when</span> v_i><span class="hljs-number">20</span>;
  8.   <span class="hljs-keyword">end</span> loop;
  9. <span class="hljs-keyword">end</span>;
复制代码
  • 11 while循环,从1打印到20
  1. declare
  2.   v_i number(<span class="hljs-number">2</span>);
  3. <span class="hljs-keyword">begin</span>
  4.   <span class="hljs-symbol">v_i:</span>=<span class="hljs-number">1</span>;
  5.   <span class="hljs-keyword">while</span> v_i<=<span class="hljs-number">20</span>
  6.   loop
  7.     dbms_output.put_line(v_i);
  8.     <span class="hljs-symbol">v_i:</span>=v_i+<span class="hljs-number">1</span>;
  9.   <span class="hljs-keyword">end</span> loop;
  10. <span class="hljs-keyword">end</span>;
复制代码
  • 12 for循环,从1打印到20
  1. <span class="hljs-keyword">begin</span>
  2.   <span class="hljs-keyword">for</span> i <span class="hljs-keyword">in</span> <span class="hljs-number">1</span>..<span class="hljs-number">20</span>
  3.   loop
  4.     dbms_output.put_line(i);
  5.   <span class="hljs-keyword">end</span> loop;
  6. <span class="hljs-keyword">end</span>;
复制代码
  • 13 使用循环完成乘法口诀
  1. declare
  2.   v_i number(<span class="hljs-number">10</span>) <span class="hljs-symbol">:</span>= <span class="hljs-number">1</span>;
  3.   v_line number(<span class="hljs-number">10</span>) <span class="hljs-symbol">:</span>=<span class="hljs-number">1</span>;
  4.   v_abc varchar(<span class="hljs-number">200</span>) default <span class="hljs-string">''</span>;
  5. <span class="hljs-keyword">begin</span>
  6.   loop
  7.     <span class="hljs-keyword">for</span> v_i <span class="hljs-keyword">in</span> <span class="hljs-number">1</span>..v_line
  8.     loop
  9.       <span class="hljs-symbol">v_abc:</span>= v_abc <span class="hljs-params">||</span> v_i<span class="hljs-params">||</span><span class="hljs-string">'*'</span><span class="hljs-params">||</span>v_line<span class="hljs-params">||</span><span class="hljs-string">'='</span><span class="hljs-params">||</span>v_i*v_line<span class="hljs-params">||</span><span class="hljs-string">' '</span>;
  10.       --dbms_output.put_line(v_i*v_line);
  11.     <span class="hljs-keyword">end</span> loop;
  12.     dbms_output.put_line(v_abc);
  13.     v_abc <span class="hljs-symbol">:</span>=<span class="hljs-string">''</span>;
  14.     <span class="hljs-symbol">v_line:</span>=v_line+<span class="hljs-number">1</span>;
  15.     exit <span class="hljs-keyword">when</span> v_line><span class="hljs-number">9</span>;
  16.   <span class="hljs-keyword">end</span> loop;
  17. <span class="hljs-keyword">end</span>;
复制代码
至此,我们熟悉了pl/sql编程中的块,为我们下面的学习打下了基础。





回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

用户反馈
客户端