PLSQL--动态SQL和存储过程、函数
- 1.8动态SQL
- 1.8.1动态sql的概念
- 1.9存储过程（procedure）
- 1.9.1存储过程与匿名块的区别
- 1.10 函数（function）
- 1.10.1函数和过程的区别
- 1.10.2.函数编写的注意事项
1.8动态SQL
1.8.1动态sql的概念
本质上来说动态sql就是将符合sql语法的字符串当作sql来运行
动态sql相对于静态sql来说更灵活，并且可以突破一些PL/SQL对静态sql的限制
由于在编译时sql的全文还不确定，所以你可以使用动态sql构建灵活的，多用途的应用程序
1.动态sql的分类
对应DML/DDL/SELECT INTO 语句我们使用EXECUTE IMMEDIATE 语句来执行；
对于多行查询我们使用OPEN CURSOR FOR 语句来执行
1）EXECUTE IMMEDIATE ‘ dml语句/ddl语句 ‘
单行注释为-- 多行注释为 /内容/
2）OPEN 游标名 FOR 语句
只能打开REF cursor 类型的游标，后面跟随多行的查询语句
2.绑定变量
没有执行绑定变量前，2个语句是不同的，都是硬链接，需要占用2块内存空间
通过下面的例子我们可以发现，一样类似的语句由于传入的部门编号不同，被Oracle定义为2个不同的sql语句，拥有不同的sql id号，在share pool中2个sql各占一份空间，游标不能被共享，会造成硬解析。
当这种sql被大量的调用时，会产生大量的硬解析，产生资源争用，占用大量的cup，发生多次物理io等一系列问题。Oracle建议使用绑定变量来避免下述问题的发生。
DAO===DATA ACCESS OBJECT
alter system flush shared_pool;----》清空shared_pool 可以暂时解决shared_pool中的碎片问题，大量的不能共享的SQL很快又会使碎片出现。解决办法是优化SQL，Keep 经常使用的包，cursor_sharing参数，在程序中注意共享cursor等等。这个命令不会影响DB的可用性吧，顶多造成短时间的性能下降，因为parse过的SQL都给清出去了
-----》：B1表示单个占位符
-----》‘：B1 and sal> ：B2‘ 多个占位符----》对应的变量就是using（v1，3000）
1.9存储过程（procedure）
主要是针对DML语句的
1.9.1存储过程与匿名块的区别
存储过程是命名块，可以存储在数据库中，存储过程创建成功后，以后想要执行存储过程中的语句，直接调用存储过程名即可，而命名块需要重新编写或者粘贴执行。
1.存储过程与匿名块的写法不同的两点
1）存储过程需要创建，所以需要执行create procedure 语句
2）存储过程可以有0-N个参数，匿名块没有参数
2.存储过程的参数的类型
1）IN 输入参数 不可以赋值
2）OUT 输出参数 可以赋值
3）IN OUT 输入输出参数 可以赋值
3.练习
1)在plsql里怎么使用存储过程?
------》将写好的存储过程，点击执行，然后在过程中寻找生成的存储过程名，然后右击测试，就能调用这个存储过程，在dbms输出查看结果。
------》a+b的值在存储过程中怎么看，在调用过程的下方输入a和b的值，然后在点击执行，就可以在dbms中查看结果
2）无参数的存储过程
匿名块不能存在于数据块里，因此需要使用存储过程
3）IN类型参数与OUT类型参数
4）IN OUT类型参数
1.10 函数（function）
单行函数一次处理一行，所以返回多少行，单行行数就被调用多少次
如果单行函数被多行函数包围，单行函数执行的次数为分组前的行数
1.10.1函数和过程的区别
-----》函数可以出现在sql中，过程不能出现在sql中，
-----》函数必须有一个返回值RETURN（哪怕是空值），
-----》一般来说函数不包含dml操作，dml操作多集中于存储过程中
-----》函数默认的参数都是输入参数（IN）,语法上支持输出参数（out），但是实际上不允许出现out
1.10.2.函数编写的注意事项
函数必须执行RETURN 语句，不能光写，不执行
如果函数存在DML语句则不能在SQL中调用
1.大部分的开发规范都要求：
1）DML语句放在存储过程中
2）查询语句可以放在函数中
