更新：感谢@littlefoot，我运行了它，但现在我有另一个问题，它让我得到了负数和字段“balance final”的结尾，当它应该说0时，这里是结果，这样你就可以明白我的意思了。希望通过下面的完整代码，有人能发现错误所在，因为我自己看不到。
更新：我发现了这个问题，它是函数中参数的顺序，它是monto数，plazo数，tasa float的时候应该是monto数，tasa float，plazo数
谢谢大家帮助我！
User created.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Connected.
Table created.
Table created.
Sequence created.
Function created.
Procedure created.
Enter value for prestamo: 2000000
Enter value for tasa: 12
Enter value for tiempo_en_a±os: 15
Enter value for fecha: 26/jul/19
Enter value for pago_extraordinario: 0
old 2: amortizacion_proc(&prestamo,&tasa,&tiempo_en_a±os,'&fecha',&Pago_extraordinario);
new 2: amortizacion_proc(2000000,12,15,'26/jul/19',0);
$30,017.54
ID PrÚstamo: 1
Monto: $2,000,000.00
Tasa :12%
Plazo :15 A±os
Fecha Prestamo: 06-AGO-20
Fecha Saldo: 06-AGO-35
Pago Extra: $.00
Cuota Mensual: $30,017.54
PL/SQL procedure successfully completed.
PmtNo Fecha Balance Inici Cuota a pagar Pago extra Total pago Capital Intereses Balance Final
---------- --------- ------------- ------------- ---------- ---------- ---------- ---------- -------------
1 26-AGO-19 2,000,000.00 30017.54 0 30017.54 10017.53 20000 1989982.46
2 26-SEP-19 1,989,982.46 30017.54 0 30017.54 10117.71 19899.82 1979864.75
3 26-OCT-19 1,979,864.75 30017.54 0 30017.54 10218.88 19798.64 1969645.86
4 26-NOV-19 1,969,645.86 30017.54 0 30017.54 10321.07 19696.45 1959324.78
5 26-DIC-19 1,959,324.78 30017.54 0 30017.54 10424.28 19593.24 1948900.5
6 26-ENE-20 1,948,900.50 30017.54 0 30017.54 10528.53 19489 1938371.97
7 26-FEB-20 1,938,371.97 30017.54 0 30017.54 10633.81 19383.71 1927738.15
8 26-MAR-20 1,927,738.15 30017.54 0 30017.54 10740.15 19277.38 1916998
9 26-ABR-20 1,916,998.00 30017.54 0 30017.54 10847.55 19169.98 1906150.44
10 26-MAY-20 1,906,150.44 30017.54 0 30017.54 10956.03 19061.5 1895194.41
11 26-JUN-20 1,895,194.41 30017.54 0 30017.54 11065.59 18951.94 1884128.82
PmtNo Fecha Balance Inici Cuota a pagar Pago extra Total pago Capital Intereses Balance Final
---------- --------- ------------- ------------- ---------- ---------- ---------- ---------- -------------
12 26-JUL-20 1,884,128.82 30017.54 0 30017.54 11176.24 18841.28 1872952.57
13 26-AGO-20 1,872,952.57 30017.54 0 30017.54 11288 18729.52 1861664.56
14 26-SEP-20 1,861,664.56 30017.54 0 30017.54 11400.88 18616.64 1850263.67
15 26-OCT-20 1,850,263.67 30017.54 0 30017.54 11514.89 18502.63 1838748.78
16 26-NOV-20 1,838,748.78 30017.54 0 30017.54 11630.04 18387.48 1827118.73
17 26-DIC-20 1,827,118.73 30017.54 0 30017.54 11746.34 18271.18 1815372.38
18 26-ENE-21 1,815,372.38 30017.54 0 30017.54 11863.81 18153.72 1803508.57
19 26-FEB-21 1,803,508.57 30017.54 0 30017.54 11982.44 18035.08 1791526.12
20 26-MAR-21 1,791,526.12 30017.54 0 30017.54 12102.27 17915.26 1779423.84
21 26-ABR-21 1,779,423.84 30017.54 0 30017.54 12223.29 17794.23 1767200.55
22 26-MAY-21 1,767,200.55 30017.54 0 30017.54 12345.52 17672 1754855.02
PmtNo Fecha Balance Inici Cuota a pagar Pago extra Total pago Capital Intereses Balance Final
---------- --------- ------------- ------------- ---------- ---------- ---------- ---------- -------------
23 26-JUN-21 1,754,855.02 30017.54 0 30017.54 12468.98 17548.55 1742386.03
24 26-JUL-21 1,742,386.03 30017.54 0 30017.54 12593.67 17423.86 1729792.36
25 26-AGO-21 1,729,792.36 30017.54 0 30017.54 12719.61 17297.92 1717072.75
26 26-SEP-21 1,717,072.75 30017.54 0 30017.54 12846.8 17170.72 1704225.94
27 26-OCT-21 1,704,225.94 30017.54 0 30017.54 12975.27 17042.25 1691250.66
28 26-NOV-21 1,691,250.66 30017.54 0 30017.54 13105.02 16912.5 1678145.63
29 26-DIC-21 1,678,145.63 30017.54 0 30017.54 13236.07 16781.45 1664909.56
30 26-ENE-22 1,664,909.56 30017.54 0 30017.54 13368.43 16649.09 1651541.12
31 26-FEB-22 1,651,541.12 30017.54 0 30017.54 13502.12 16515.41 1638038.99
32 26-MAR-22 1,638,038.99 30017.54 0 30017.54 13637.14 16380.38 1624401.85
33 26-ABR-22 1,624,401.85 30017.54 0 30017.54 13773.51 16244.01 1610628.33
PmtNo Fecha Balance Inici Cuota a pagar Pago extra Total pago Capital Intereses Balance Final
---------- --------- ------------- ------------- ---------- ---------- ---------- ---------- -------------
34 26-MAY-22 1,610,628.33 30017.54 0 30017.54 13911.25 16106.28 1596717.08
35 26-JUN-22 1,596,717.08 30017.54 0 30017.54 14050.36 15967.17 1582666.71
36 26-JUL-22 1,582,666.71 30017.54 0 30017.54 14190.86 15826.66 1568475.84
37 26-AGO-22 1,568,475.84 30017.54 0 30017.54 14332.77 15684.75 1554143.07
38 26-SEP-22 1,554,143.07 30017.54 0 30017.54 14476.1 15541.43 1539666.96
39 26-OCT-22 1,539,666.96 30017.54 0 30017.54 14620.86 15396.66 1525046.1
40 26-NOV-22 1,525,046.10 30017.54 0 30017.54 14767.07 15250.46 1510279.02
41 26-DIC-22 1,510,279.02 30017.54 0 30017.54 14914.74 15102.79 1495364.28
42 26-ENE-23 1,495,364.28 30017.54 0 30017.54 15063.89 14953.64 1480300.39
43 26-FEB-23 1,480,300.39 30017.54 0 30017.54 15214.53 14803 1465085.85
44 26-MAR-23 1,465,085.85 30017.54 0 30017.54 15366.67 14650.85 1449719.18
PmtNo Fecha Balance Inici Cuota a pagar Pago extra Total pago Capital Intereses Balance Final
---------- --------- ------------- ------------- ---------- ---------- ---------- ---------- -------------
45 26-ABR-23 1,449,719.18 30017.54 0 30017.54 15520.34 14497.19 1434198.83
46 26-MAY-23 1,434,198.83 30017.54 0 30017.54 15675.54 14341.98 1418523.29
47 26-JUN-23 1,418,523.29 30017.54 0 30017.54 15832.3 14185.23 1402690.98
48 26-JUL-23 1,402,690.98 30017.54 0 30017.54 15990.62 14026.9 1386700.36
49 26-AGO-23 1,386,700.36 30017.54 0 30017.54 16150.53 13867 1370549.83
50 26-SEP-23 1,370,549.83 30017.54 0 30017.54 16312.03 13705.49 1354237.79
51 26-OCT-23 1,354,237.79 30017.54 0 30017.54 16475.15 13542.37 1337762.63
52 26-NOV-23 1,337,762.63 30017.54 0 30017.54 16639.9 13377.62 1321122.72
53 26-DIC-23 1,321,122.72 30017.54 0 30017.54 16806.3 13211.22 1304316.42
54 26-ENE-24 1,304,316.42 30017.54 0 30017.54 16974.37 13043.16 1287342.04
55 26-FEB-24 1,287,342.04 30017.54 0 30017.54 17144.11 12873.42 1270197.93
PmtNo Fecha Balance Inici Cuota a pagar Pago extra Total pago Capital Intereses Balance Final
---------- --------- ------------- ------------- ---------- ---------- ---------- ---------- -------------
56 26-MAR-24 1,270,197.93 30017.54 0 30017.54 17315.55 12701.97 1252882.37
57 26-ABR-24 1,252,882.37 30017.54 0 30017.54 17488.71 12528.82 1235393.66
58 26-MAY-24 1,235,393.66 30017.54 0 30017.54 17663.59 12353.93 1217730.06
59 26-JUN-24 1,217,730.06 30017.54 0 30017.54 17840.23 12177.3 1199889.83
60 26-JUL-24 1,199,889.83 30017.54 0 30017.54 18018.63 11998.89 1181871.19
61 26-AGO-24 1,181,871.19 30017.54 0 30017.54 18198.82 11818.71 1163672.37
62 26-SEP-24 1,163,672.37 30017.54 0 30017.54 18380.81 11636.72 1145291.56
63 26-OCT-24 1,145,291.56 30017.54 0 30017.54 18564.61 11452.91 1126726.94
64 26-NOV-24 1,126,726.94 30017.54 0 30017.54 18750.26 11267.26 1107976.67
65 26-DIC-24 1,107,976.67 30017.54 0 30017.54 18937.76 11079.76 1089038.9
66 26-ENE-25 1,089,038.90 30017.54 0 30017.54 19127.14 10890.38 1069911.76
PmtNo Fecha Balance Inici Cuota a pagar Pago extra Total pago Capital Intereses Balance Final
---------- --------- ------------- ------------- ---------- ---------- ---------- ---------- -------------
67 26-FEB-25 1,069,911.76 30017.54 0 30017.54 19318.41 10699.11 1050593.34
68 26-MAR-25 1,050,593.34 30017.54 0 30017.54 19511.6 10505.93 1031081.74
69 26-ABR-25 1,031,081.74 30017.54 0 30017.54 19706.71 10310.81 1011375.02
70 26-MAY-25 1,011,375.02 30017.54 0 30017.54 19903.78 10113.75 991471.23
71 26-JUN-25 991,471.23 30017.54 0 30017.54 20102.82 9914.71 971368.41
72 26-JUL-25 971,368.41 30017.54 0 30017.54 20303.85 9713.68 951064.56
73 26-AGO-25 951,064.56 30017.54 0 30017.54 20506.88 9510.64 930557.67
74 26-SEP-25 930,557.67 30017.54 0 30017.54 20711.95 9305.57 909845.71
75 26-OCT-25 909,845.71 30017.54 0 30017.54 20919.07 9098.45 888926.63
76 26-NOV-25 888,926.63 30017.54 0 30017.54 21128.26 8889.26 867798.36
77 26-DIC-25 867,798.36 30017.54 0 30017.54 21339.55 8677.98 846458.81
PmtNo Fecha Balance Inici Cuota a pagar Pago extra Total pago Capital Intereses Balance Final
---------- --------- ------------- ------------- ---------- ---------- ---------- ---------- -------------
78 26-ENE-26 846,458.81 30017.54 0 30017.54 21552.94 8464.58 824905.87
79 26-FEB-26 824,905.87 30017.54 0 30017.54 21768.47 8249.05 803137.39
80 26-MAR-26 803,137.39 30017.54 0 30017.54 21986.16 8031.37 781151.23
81 26-ABR-26 781,151.23 30017.54 0 30017.54 22206.02 7811.51 758945.21
82 26-MAY-26 758,945.21 30017.54 0 30017.54 22428.08 7589.45 736517.12
83 26-JUN-26 736,517.12 30017.54 0 30017.54 22652.36 7365.17 713864.76
84 26-JUL-26 713,864.76 30017.54 0 30017.54 22878.88 7138.64 690985.87
85 26-AGO-26 690,985.87 30017.54 0 30017.54 23107.67 6909.85 667878.19
86 26-SEP-26 667,878.19 30017.54 0 30017.54 23338.75 6678.78 644539.44
87 26-OCT-26 644,539.44 30017.54 0 30017.54 23572.14 6445.39 620967.3
88 26-NOV-26 620,967.30 30017.54 0 30017.54 23807.86 6209.67 597159.44
PmtNo Fecha Balance Inici Cuota a pagar Pago extra Total pago Capital Intereses Balance Final
---------- --------- ------------- ------------- ---------- ---------- ---------- ---------- -------------
89 26-DIC-26 597,159.44 30017.54 0 30017.54 24045.94 5971.59 573113.5
90 26-ENE-27 573,113.50 30017.54 0 30017.54 24286.4 5731.13 548827.1
91 26-FEB-27 548,827.10 30017.54 0 30017.54 24529.26 5488.27 524297.83
92 26-MAR-27 524,297.83 30017.54 0 30017.54 24774.55 5242.97 499523.27
93 26-ABR-27 499,523.27 30017.54 0 30017.54 25022.3 4995.23 474500.97
94 26-MAY-27 474,500.97 30017.54 0 30017.54 25272.52 4745 449228.45
95 26-JUN-27 449,228.45 30017.54 0 30017.54 25525.25 4492.28 423703.2
96 26-JUL-27 423,703.20 30017.54 0 30017.54 25780.5 4237.03 397922.69
97 26-AGO-27 397,922.69 30017.54 0 30017.54 26038.3 3979.22 371884.38
98 26-SEP-27 371,884.38 30017.54 0 30017.54 26298.69 3718.84 345585.69
99 26-OCT-27 345,585.69 30017.54 0 30017.54 26561.67 3455.85 319024.01
PmtNo Fecha Balance Inici Cuota a pagar Pago extra Total pago Capital Intereses Balance Final
---------- --------- ------------- ------------- ---------- ---------- ---------- ---------- -------------
100 26-NOV-27 319,024.01 30017.54 0 30017.54 26827.29 3190.24 292196.72
101 26-DIC-27 292,196.72 30017.54 0 30017.54 27095.56 2921.96 265101.15
102 26-ENE-28 265,101.15 30017.54 0 30017.54 27366.52 2651.01 237734.63
103 26-FEB-28 237,734.63 30017.54 0 30017.54 27640.18 2377.34 210094.44
104 26-MAR-28 210,094.44 30017.54 0 30017.54 27916.59 2100.94 182177.85
105 26-ABR-28 182,177.85 30017.54 0 30017.54 28195.75 1821.77 153982.09
106 26-MAY-28 153,982.09 30017.54 0 30017.54 28477.71 1539.82 125504.38
107 26-JUN-28 125,504.38 30017.54 0 30017.54 28762.49 1255.04 96741.88
108 26-JUL-28 96,741.88 30017.54 0 30017.54 29050.11 967.41 67691.77
109 26-AGO-28 67,691.77 30017.54 0 30017.54 29340.61 676.91 38351.15
110 26-SEP-28 38,351.15 30017.54 0 30017.54 29634.02 383.51 8717.13
PmtNo Fecha Balance Inici Cuota a pagar Pago extra Total pago Capital Intereses Balance Final
---------- --------- ------------- ------------- ---------- ---------- ---------- ---------- -------------
111 26-OCT-28 8,717.13 30017.54 0 30017.54 29930.36 87.17 -21213.23
112 26-NOV-28 -21,213.23 30017.54 0 30017.54 30229.66 -212.13 -51442.89
113 26-DIC-28 -51,442.89 30017.54 0 30017.54 30531.96 -514.42 -81974.86
114 26-ENE-29 -81,974.86 30017.54 0 30017.54 30837.28 -819.74 -112812.14
115 26-FEB-29 -112,812.14 30017.54 0 30017.54 31145.65 -1128.12 -143957.8
116 26-MAR-29 -143,957.80 30017.54 0 30017.54 31457.11 -1439.57 -175414.91
117 26-ABR-29 -175,414.91 30017.54 0 30017.54 31771.68 -1754.14 -207186.6
118 26-MAY-29 -207,186.60 30017.54 0 30017.54 32089.4 -2071.86 -239276
119 26-JUN-29 -239,276.00 30017.54 0 30017.54 32410.29 -2392.76 -271686.29
120 26-JUL-29 -271,686.29 30017.54 0 30017.54 32734.39 -2716.86 -304420.69
121 26-AGO-29 -304,420.69 30017.54 0 30017.54 33061.74 -3044.2 -337482.43
PmtNo Fecha Balance Inici Cuota a pagar Pago extra Total pago Capital Intereses Balance Final
---------- --------- ------------- ------------- ---------- ---------- ---------- ---------- -------------
122 26-SEP-29 -337,482.43 30017.54 0 30017.54 33392.35 -3374.82 -370874.79
123 26-OCT-29 -370,874.79 30017.54 0 30017.54 33726.28 -3708.74 -404601.08
124 26-NOV-29 -404,601.08 30017.54 0 30017.54 34063.54 -4046.01 -438664.62
125 26-DIC-29 -438,664.62 30017.54 0 30017.54 34404.18 -4386.64 -473068.81
126 26-ENE-30 -473,068.81 30017.54 0 30017.54 34748.22 -4730.68 -507817.03
127 26-FEB-30 -507,817.03 30017.54 0 30017.54 35095.7 -5078.17 -542912.73
128 26-MAR-30 -542,912.73 30017.54 0 30017.54 35446.66 -5429.12 -578359.4
129 26-ABR-30 -578,359.40 30017.54 0 30017.54 35801.12 -5783.59 -614160.53
130 26-MAY-30 -614,160.53 30017.54 0 30017.54 36159.14 -6141.6 -650319.67
131 26-JUN-30 -650,319.67 30017.54 0 30017.54 36520.73 -6503.19 -686840.4
132 26-JUL-30 -686,840.40 30017.54 0 30017.54 36885.93 -6868.4 -723726.34
PmtNo Fecha Balance Inici Cuota a pagar Pago extra Total pago Capital Intereses Balance Final
---------- --------- ------------- ------------- ---------- ---------- ---------- ---------- -------------
133 26-AGO-30 -723,726.34 30017.54 0 30017.54 37254.79 -7237.26 -760981.14
134 26-SEP-30 -760,981.14 30017.54 0 30017.54 37627.34 -7609.81 -798608.48
135 26-OCT-30 -798,608.48 30017.54 0 30017.54 38003.62 -7986.08 -836612.1
136 26-NOV-30 -836,612.10 30017.54 0 30017.54 38383.65 -8366.12 -874995.76
137 26-DIC-30 -874,995.76 30017.54 0 30017.54 38767.49 -8749.95 -913763.25
138 26-ENE-31 -913,763.25 30017.54 0 30017.54 39155.16 -9137.63 -952918.42
139 26-FEB-31 -952,918.42 30017.54 0 30017.54 39546.71 -9529.18 -992465.14
140 26-MAR-31 -992,465.14 30017.54 0 30017.54 39942.18 -9924.65 -1032407.3
141 26-ABR-31 -1,032,407.33 30017.54 0 30017.54 40341.6 -10324.07 -1072748.9
142 26-MAY-31 -1,072,748.94 30017.54 0 30017.54 40745.02 -10727.48 -1113494
143 26-JUN-31 -1,113,493.96 30017.54 0 30017.54 41152.47 -11134.93 -1154646.4
PmtNo Fecha Balance Inici Cuota a pagar Pago extra Total pago Capital Intereses Balance Final
---------- --------- ------------- ------------- ---------- ---------- ---------- ---------- -------------
144 26-JUL-31 -1,154,646.44 30017.54 0 30017.54 41563.99 -11546.46 -1196210.4
145 26-AGO-31 -1,196,210.44 30017.54 0 30017.54 41979.63 -11962.1 -1238190.1
146 26-SEP-31 -1,238,190.08 30017.54 0 30017.54 42399.43 -12381.9 -1280589.5
147 26-OCT-31 -1,280,589.51 30017.54 0 30017.54 42823.43 -12805.89 -1323412.9
148 26-NOV-31 -1,323,412.94 30017.54 0 30017.54 43251.66 -13234.12 -1366664.6
149 26-DIC-31 -1,366,664.61 30017.54 0 30017.54 43684.18 -13666.64 -1410348.8
150 26-ENE-32 -1,410,348.79 30017.54 0 30017.54 44121.02 -14103.48 -1454469.8
151 26-FEB-32 -1,454,469.81 30017.54 0 30017.54 44562.23 -14544.69 -1499032.1
152 26-MAR-32 -1,499,032.05 30017.54 0 30017.54 45007.85 -14990.32 -1544039.9
153 26-ABR-32 -1,544,039.90 30017.54 0 30017.54 45457.93 -15440.39 -1589497.8
154 26-MAY-32 -1,589,497.84 30017.54 0 30017.54 45912.51 -15894.97 -1635410.4
PmtNo Fecha Balance Inici Cuota a pagar Pago extra Total pago Capital Intereses Balance Final
---------- --------- ------------- ------------- ---------- ---------- ---------- ---------- -------------
155 26-JUN-32 -1,635,410.35 30017.54 0 30017.54 46371.63 -16354.1 -1681782
156 26-JUL-32 -1,681,781.99 30017.54 0 30017.54 46835.35 -16817.81 -1728617.3
157 26-AGO-32 -1,728,617.34 30017.54 0 30017.54 47303.7 -17286.17 -1775921.1
158 26-SEP-32 -1,775,921.05 30017.54 0 30017.54 47776.74 -17759.21 -1823697.8
159 26-OCT-32 -1,823,697.80 30017.54 0 30017.54 48254.51 -18236.97 -1871952.3
160 26-NOV-32 -1,871,952.31 30017.54 0 30017.54 48737.05 -18719.52 -1920689.4
161 26-DIC-32 -1,920,689.37 30017.54 0 30017.54 49224.42 -19206.89 -1969913.8
162 26-ENE-33 -1,969,913.80 30017.54 0 30017.54 49716.67 -19699.13 -2019630.5
163 26-FEB-33 -2,019,630.47 30017.54 0 30017.54 50213.84 -20196.3 -2069844.3
164 26-MAR-33 -2,069,844.31 30017.54 0 30017.54 50715.97 -20698.44 -2120560.3
165 26-ABR-33 -2,120,560.29 30017.54 0 30017.54 51223.13 -21205.6 -2171783.4
PmtNo Fecha Balance Inici Cuota a pagar Pago extra Total pago Capital Intereses Balance Final
---------- --------- ------------- ------------- ---------- ---------- ---------- ---------- -------------
166 26-MAY-33 -2,171,783.43 30017.54 0 30017.54 51735.36 -21717.83 -2223518.8
167 26-JUN-33 -2,223,518.80 30017.54 0 30017.54 52252.72 -22235.18 -2275771.5
168 26-JUL-33 -2,275,771.52 30017.54 0 30017.54 52775.25 -22757.71 -2328546.8
169 26-AGO-33 -2,328,546.77 30017.54 0 30017.54 53303 -23285.46 -2381849.8
170 26-SEP-33 -2,381,849.78 30017.54 0 30017.54 53836.03 -23818.49 -2435685.8
171 26-OCT-33 -2,435,685.81 30017.54 0 30017.54 54374.39 -24356.85 -2490060.2
172 26-NOV-33 -2,490,060.20 30017.54 0 30017.54 54918.13 -24900.6 -2544978.3
173 26-DIC-33 -2,544,978.34 30017.54 0 30017.54 55467.31 -25449.78 -2600445.7
174 26-ENE-34 -2,600,445.66 30017.54 0 30017.54 56021.99 -26004.45 -2656467.7
175 26-FEB-34 -2,656,467.65 30017.54 0 30017.54 56582.21 -26564.67 -2713049.9
176 26-MAR-34 -2,713,049.86 30017.54 0 30017.54 57148.03 -27130.49 -2770197.9
PmtNo Fecha Balance Inici Cuota a pagar Pago extra Total pago Capital Intereses Balance Final
---------- --------- ------------- ------------- ---------- ---------- ---------- ---------- -------------
177 26-ABR-34 -2,770,197.90 30017.54 0 30017.54 57719.51 -27701.97 -2827917.4
178 26-MAY-34 -2,827,917.41 30017.54 0 30017.54 58296.7 -28279.17 -2886214.1
179 26-JUN-34 -2,886,214.12 30017.54 0 30017.54 58879.67 -28862.14 -2945093.8
180 26-JUL-34 -2,945,093.80 30017.54 0 30017.54 59468.47 -29450.93 -3004562.3
180 rows selected.整个代码是这样的
--PASO 1, crear el usuario y asignar roles y privilegios
DROP USER amortiz CASCADE;
CREATE USER amortiz IDENTIFIED BY "amortiz";
GRANT CONNECT TO amortiz;
GRANT RESOURCE TO amortiz;
GRANT UNLIMITED TABLESPACE TO amortiz;
--PASO 2, conectarse con el usuario nuevo y crear las tablas
CONNECT amortiz/amortiz;
CREATE TABLE param_amortizacion (
amortiz_id number(10),
monto number(9,2),
tasa number(5,2),
plazo number(3),
fecha_inicio date,
pago_extra number(9,2),
cuota_programada number(9,2),
CONSTRAINT param_amortiz_pk PRIMARY KEY (amortiz_id)
);
CREATE TABLE amortizacion (
ID number(6),
fecha_pago date,
balance_inicial number(9,2),
pago_extra number(9,2),
capital number(9,2),
interes number(9,2),
balance_final number(9,2),
amortiz_id number(10),
CONSTRAINT amortiz_pk PRIMARY KEY (ID),
CONSTRAINT amortiz_fk FOREIGN KEY (amortiz_id) REFERENCES param_amortizacion(amortiz_id)
);
--PASO 3 crear la secuencia
CREATE SEQUENCE amortiz_seq
START WITH 1
INCREMENT BY 1;
--PASO 4, crear la funcion calc_cuota_prog para calcular la cuota programada, parametros a introducir son monto, tasa y plazo
CREATE OR REPLACE FUNCTION calc_cuota_prog (monto NUMBER, plazo NUMBER, tasa FLOAT)
RETURN NUMBER
IS
cuota_v NUMBER;
m NUMBER;
t NUMBER;
p NUMBER;
BEGIN
m:=monto;
t:=(tasa/12)/100;
p:=plazo*12;
cuota_v:=m*((power(1+t,p)*t)/(power(1+t,p)-1));
DBMS_OUTPUT.PUT_LINE(TO_CHAR(cuota_v, '$9,999,999.99'));
RETURN cuota_v;
END;
/
--PASO 5, crear el procedure amortizacion_proc
/*
Dicho procedimiento debe hacer lo siguente:
reciba como parámetros de entrada el monto total del préstamo,
la tasa expresada en %, el plazo del préstamo expresado en años,
la fecha de inicio del préstamo y si va a realizar un pago extraordinario (0 defecto).
Esta información guárdela en la tabla param_amortizacion de parámetros del préstamo.
Guarde en la tabla amortizacion las transacciones mensuales de la amortización del préstamo hasta que se termine de pagar el préstamo en el plazo pautado.
Dentro del procedimiento muestre por pantalla los datos generales del préstamo.
* /
CREATE OR REPLACE PROCEDURE amortizacion_proc (TOTAL NUMBER,TASA NUMBER,PLAZO NUMBER,FECHA_INICIO DATE,PAGO_EXTRA NUMBER)
IS
MES NUMBER := PLAZO*12;
INTERESES NUMBER;
CAPITAL NUMBER;
INICIO NUMBER := TOTAL;
FINAL1 NUMBER;
AM_ID NUMBER;
PROMEDIO NUMBER :=calc_cuota_prog(TOTAL,TASA,PLAZO);
TASA1 number:=(TASA/12)/100;
BEGIN
INSERT INTO param_amortizacion
VALUES (amortiz_seq.NEXTVAL,TOTAL,TASA,PLAZO,TO_CHAR(fecha_inicio, 'dd-MON-RR'),pago_extra,PROMEDIO);
FOR i IN 1..MES LOOP
INTERESES := INICIO*TASA1;
CAPITAL := PROMEDIO-INTERESES;
FINAL1 := INICIO-CAPITAL-pago_extra;
SELECT amortiz_id INTO AM_ID FROM param_amortizacion WHERE ROWNUM = (SELECT MAX(ROWNUM) FROM param_amortizacion);
INSERT INTO amortizacion
VALUES (i,ADD_MONTHS(TO_CHAR(fecha_inicio,'dd-MON-RR'),i), TRUNC(INICIO,2),pago_extra, TRUNC(CAPITAL, 2), TRUNC(INTERESES, 2), TRUNC(FINAL1, 2), AM_ID);
INICIO := FINAL1;
EXIT WHEN FINAL1 = 0;
END LOOP;
--PASO 6, mostrar datos generales del prestamo dentro del procedure
DBMS_OUTPUT.PUT_LINE('ID Préstamo: '|| AM_ID||chr(10));
DBMS_OUTPUT.PUT_LINE('Monto: '||TO_CHAR(TOTAL,'$9,999,999.99')||chr(10));
DBMS_OUTPUT.PUT_LINE('Tasa :' ||TASA||'%' ||chr(10));
DBMS_OUTPUT.PUT_LINE('Plazo :' ||PLAZO||' Años' ||chr(10));
DBMS_OUTPUT.PUT_LINE('Fecha Prestamo: '||TO_CHAR(SYSDATE, 'dd-MON-RR')|| chr(10));
DBMS_OUTPUT.PUT_LINE('Fecha Saldo: '||ADD_MONTHS(TO_CHAR(SYSDATE, 'dd-MON-RR'),PLAZO*12)||chr(10));
DBMS_OUTPUT.PUT_LINE('Pago Extra: '||TO_CHAR(0,'$999,999.99')||chr(10));
DBMS_OUTPUT.PUT_LINE('Cuota Mensual: ' ||TO_CHAR(PROMEDIO,'$999,999.99')||chr(10));
END;
--PASO 7, llamar al procedure y pedir los datos por pantalla
/
SET SERVEROUTPUT ON
BEGIN
amortizacion_proc(&prestamo,&tasa,&tiempo_en_años,'&fecha',&Pago_extraordinario);
END;
/
--PASO 8, Hacer un select para el reporte
SELECT v.id "PmtNo", v.fecha_pago "Fecha", TO_CHAR(v.balance_inicial,'9,999,999.99') "Balance Inicial ", c.cuota_programada "Cuota a pagar", c.pago_extra "Pago extra", c.cuota_programada "Total pago",v.capital "Capital",v.interes "Intereses",v.BALANCE_FINAL "Balance Final"
FROM amortizacion v INNER JOIN param_amortizacion c ON v.amortiz_id=c.amortiz_id;
3条答案
按热度按时间w3nuxt5m1#
执行pl/sql过程
下面显示了执行过程的语法：
执行过程名称（参数）；
或
exec程序名称（参数）；
https://www.oracletutorial.com/plsql-tutorial/plsql-procedure/
iibxawm42#
请在上面加上
有时我们在错误的数据库中执行查询。如果我错了，请原谅。
wwwo4jvm3#
我发现了问题，是函数中参数的顺序，是
蒙托数，普拉佐数，塔萨浮点数
应该什么时候
蒙托数，塔萨浮点数，普拉佐数