plsql练习题经典
时间: 2023-10-05 19:06:13 浏览: 66
经典的PL/SQL练习题常常涉及到条件判断、循环和数据操作等方面的知识。以下是一些经典的PL/SQL练习题:
1. 给定三个数值,返回其中较大的那个数:
```sql
DECLARE
A INT := &输入1;
B INT := &输入2;
C INT := &输入3;
BEGIN
IF A > B AND A > C THEN
DBMS_OUTPUT.PUT_LINE(A);
ELSIF B > A AND B > C THEN
DBMS_OUTPUT.PUT_LINE(B);
ELSIF C > A AND C > B THEN
DBMS_OUTPUT.PUT_LINE(C);
END IF;
END;
```
[引用1]
2. 给定三个数值,按照从大到小的顺序返回:
```sql
DECLARE
A INT := &输入1;
B INT := &输入2;
C INT := &输入3;
BEGIN
IF A > B AND A > C THEN
IF B > C THEN
DBMS_OUTPUT.PUT_LINE(A || ',' || B || ',' || C);
ELSIF C > B THEN
DBMS_OUTPUT.PUT_LINE(A || ',' || C || ',' || B);
END IF;
ELSIF B > A AND B > C THEN
IF A > C THEN
DBMS_OUTPUT.PUT_LINE(B || ',' || A || ',' || C);
ELSIF C > A THEN
DBMS_OUTPUT.PUT_LINE(B || ',' || C || ',' || A);
END IF;
ELSIF C > A AND C > B THEN
IF A > B THEN
DBMS_OUTPUT.PUT_LINE(C || ',' || A || ',' || B);
ELSIF B > A THEN
DBMS_OUTPUT.PUT_LINE(C || ',' || B || ',' || A);
END IF;
END IF;
END;
```
[引用1]
3. 编写一个存储过程,将STUDENT_INFO_01表中的数据拷贝到STUDENT_INFO_02表中:
```sql
CREATE OR REPLACE PROCEDURE COPY_STUDENT_INFO AS
BEGIN
INSERT INTO STUDENT_INFO_02
SELECT *
FROM STUDENT_INFO_01
WHERE AGE BETWEEN 15 AND 20
AND FLG = 0;
END COPY_STUDENT_INFO;
```
[引用2]
4. 编写一个存储过程,将STUDENT_INFO_01表中的性别字段由Code转换为具体的性别:
```sql
CREATE OR REPLACE PROCEDURE CONVERT_GENDER AS
BEGIN
UPDATE STUDENT_INFO_01
SET GENDER = CASE
WHEN GENDER = 'M' THEN 'Male'
WHEN GENDER = 'F' THEN 'Female'
ELSE 'Unknown'
END;
END CONVERT_GENDER;
```
[引用2]
5. 给定一个数N,计算2^1 + 2^2 + ... + 2^N 的值:
```sql
DECLARE
N NUMBER := &输入;
S NUMBER := 0;
A NUMBER := 0;
BEGIN
FOR B IN 1 .. N LOOP
A := A || 2;
S := S + A;
END LOOP;
DBMS_OUTPUT.PUT_LINE(S);
END;
```
[引用3]
这些是一些经典的PL/SQL练习题,涉及到了条件判断、循环和数据操作等方面的知识。可以通过编写相应的PL/SQL代码来解决这些问题。