powerbuilder的SQL语句

PB的sql语句以；结束，可写成多行形式而不用&

1、SELECT

一个例子：

<pre spellcheck="false" class="md-fences md-end-block ty-contain-cm modeLoaded" lang="sql" cid="n5" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: var(--monospace); font-size: 0.9em; display: block; break-inside: avoid; text-align: left; white-space: normal; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(231, 234, 237); border-radius: 3px; padding: 8px 4px 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; color: rgb(51, 51, 51); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">SELECT Employee.Emp_id,Employee.Emp_name, Employee.Emp_salary,Dept.Dept_name
FROM Employee,Dept
WHERE (Emp_name like ''''张%'''') AND (Emp_sex=''''男'''') AND Employee.Dept_id=Dept.Dept_id
ORDER BY Employee.Emp_id; </pre>

给变量的例子：

<pre spellcheck="false" class="md-fences md-end-block ty-contain-cm modeLoaded" lang="sql" cid="n7" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: var(--monospace); font-size: 0.9em; display: block; break-inside: avoid; text-align: left; white-space: normal; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(231, 234, 237); border-radius: 3px; padding: 8px 4px 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; color: rgb(51, 51, 51); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">Dec Emp_salary
SELECT max(Emp_salary) INTO :Emp_salary FROM Employee;</pre>

注意：变量前加‘:’ ，sql语句不需要加‘$’

2、INSERT

一个例子：

emp_tran为数据库事务对象，一般在主窗口就会用settransobject()函数关联这个事务对象

<pre spellcheck="false" class="md-fences md-end-block ty-contain-cm modeLoaded" lang="sql" cid="n12" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: var(--monospace); font-size: 0.9em; display: block; break-inside: avoid; text-align: left; white-space: normal; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(231, 234, 237); border-radius: 3px; padding: 8px 4px 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; color: rgb(51, 51, 51); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">Int Emp_nbr
String Emp_name
Emp_nbr=Integer(sle_number.Text)
Emp_name=sle_name.Text
INSERT INTO Employee(employee.Emp_nbr,employee.Emp_name)
VALUES(:Emp_nbr,:Emp_name) USING Emp_tran; </pre>

3、UPDATE

UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值，更新某一行中的若干列，例如：

<pre spellcheck="false" class="md-fences md-end-block ty-contain-cm modeLoaded" lang="" cid="n15" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: var(--monospace); font-size: 0.9em; display: block; break-inside: avoid; text-align: left; white-space: normal; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(231, 234, 237); border-radius: 3px; padding: 8px 4px 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; color: rgb(51, 51, 51); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">UPDATE Person
SET Address = 'Zhongshan 23', City = 'Nanjing'
WHERE LastName = 'Wilson'</pre>

一个例子：

<pre spellcheck="false" class="md-fences md-end-block ty-contain-cm modeLoaded" lang="sql" cid="n17" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: var(--monospace); font-size: 0.9em; display: block; break-inside: avoid; text-align: left; white-space: normal; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(231, 234, 237); border-radius: 3px; padding: 8px 4px 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; color: rgb(51, 51, 51); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">int Emp_num
Emp_num=Integer(sle_Number.Text )
UPDATE Employee
SET emp_name = :sle_Name.Text
WHERE Employee.emp_num = :Emp_num
USING Emp_tran ;
IF Emptran.SQLNRows > 0 THEN
COMMIT USING Emp_tran ;
messagebox('恭喜', '已经成功更新数据库!')
else
rollback using sqlca;
messagebox('提示', '更新数据库失败!')
END IF</pre>

注：有些实例没有commit，但update后也可以同步到数据库，原因如下

4、DELETE

一个例子：

<pre spellcheck="false" class="md-fences md-end-block ty-contain-cm modeLoaded" lang="sql" cid="n22" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: var(--monospace); font-size: 0.9em; display: block; break-inside: avoid; text-align: left; white-space: normal; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(231, 234, 237); border-radius: 3px; padding: 8px 4px 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; color: rgb(51, 51, 51); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">DELETE FROM Employee //从表Employee
WHERE Emp_nbr <100;删除条件满足的记录 </pre>

5、CONNECT和DISCONNECT

执行DISCONNECT前自动执行COMMIT

6、COMMIT和ROLLBACK

上述为嵌入式sql语句，更多的是用动态sql，参考：https://blog.csdn.net/ClearLoveQ/article/details/83277640