PL/SQL 学习笔记2
变量声明
Syntax:
identifier [CONSTANT] datatype [NOT NULL] [:= | DEFAULT expr];
SQL> declare
2 a date;
3 b number(20) not null :=100;
4 c varchar2(10);
5 d constant number(20) default 1000;
6 begin
7 null;
8 end;
9 /
PL/SQL procedure successfully completed.
SQL> var v_bind number
SQL> exec :v_bind := 2984;
PL/SQL procedure successfully completed.
SQL> r
1 declare
2 v_a number(20);
3 v_b constant number := 1000;
4 v_c constant number default 2000;
5 v_d varchar2(20) not null default 'asf';
6 begin
7 v_a := 2003;
8 dbms_output.put_line(v_a ||' '|| v_b ||' '|| v_c ||' ' || v_d || ' ' || '&a' || ' '||:v_bind) ;
9* end;
Enter value for a: 34567
old 8: dbms_output.put_line(v_a ||' '|| v_b ||' '|| v_c ||' ' || v_d || ' ' || '&a' || ' '||:v_bind) ;
new 8: dbms_output.put_line(v_a ||' '|| v_b ||' '|| v_c ||' ' || v_d || ' ' || '34567' || ' '||:v_bind) ;
2003 1000 2000 asf 34567 2984
PL/SQL procedure successfully completed.
Scalar 标量 data type holds a single value and has no internal components.
number,
character,
date,
Boolean.
Character and number data types have subtypes that
associate a base type to a constraint. For example, INTEGERand POSITIVEare subtypes of the NUMBER
base type.
?CHAR [(maximum_length)]
? VARCHAR2(maximum_length)
? LONG
? LONG RAW
? NUMBER [(precision, scale)]
? BINARY_INTEGER
? PLS_INTEGER
? BOOLEAN
SQL> declare
2 v_job varchar2(20);
3 v_count binary_integer := 0;
4 v_total_sal number(9,2) := 0;
5 v_orderdate date := sysdate + 7;
6 c_tax_rate constant number(3,2) := 8.02;
7 v_valid boolean not null :=true;
8 begin
9 null;
10 end;
11 /
v_job: variable to store an employee job title
v_count: variable to count the iterations of a loop and initialized to 0
v_total_sal: variable to accumulate the total salary for a department and i
相关文档：
取表里n到m条纪录的几种方法:
1. 只需要查询前M条数据(0 to M),
1.1 使用 top(M) 方法:
select top(3) * from [tablename]
1.2 使用 set rowcount 方法:
http://msdn.microsoft.com/zh-cn/library/ms188774(SQL.90).aspx
set rowcount M
select * from [tablename]
set rowcount 0
权限 要求具有 public ......
SQL的优化应该从5个方面进行调整：
1.去掉不必要的大型表的全表扫描
2.缓存小型表的全表扫描
3.检验优化索引的使用
4.检验优化的连接技术
5.尽可能减少执行计划的Cost
SQL语句：
是对数据库(数据)进行操作的惟一途径；
消耗了70%~90%的数据库资源；独立于程序设计逻辑，相对于对程序源代码的优化，对SQL语句的优化� ......
SQL操作全集
下列语句部分是Mssql语句，不可以在access中使用。
SQL分类：
DDL—数据定义语言(CREATE，ALTER，DROP，DECLARE)
DML—数据操纵语言(SELECT，DELETE，UPDATE，INSERT)
DCL—数据控制语言(GRANT，REVOKE，COMMIT，ROLLBACK)
首先,简要介绍基础语句：
1、说明：创建数据库
CREATE ......
private void button2_Click(object sender, EventArgs e)//增
{
try
{
Customers ct = new Customers();
ct.CustomerID = "test";
ct.CompanyName = "test";
NorthwindDataContext nc = new NorthwindDataContex ......
